NETCTOSS/src/netctoss.sql at master · projectLib/NETCTOSS · GitHub

242 lines (196 loc) · 9.46 KB

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

select * from cost_chenqf;

select * from account_chenqf;

select * from service_chenqf;

select * from service_chenqf_update_bak;

update service_chenqf set status=1;

--建表时注意，表名、约束名都要增加后缀，避免重复

--资费信息表

create table cost_chenqf(

cost_id number(4) constraint cost_chenqf_id_pk primary key,

name varchar(50) not null,

base_duration number(11),

base_cost number(7,2),

unit_cost number(7,4),

status char(1),

descr varchar2(100),

creatime date default sysdate ,

startime date,

cost_type char(1)

);

create sequence cost_chenqf_seq start with 100;

INSERT INTO cost_chenqf VALUES (1,'5.9元套餐',20,5.9,0.4,0,'5.9元20小时/月,超出部分0.4元/时',DEFAULT,DEFAULT,NULL);

INSERT INTO cost_chenqf VALUES (2,'6.9元套餐',40,6.9,0.3,0,'6.9元40小时/月,超出部分0.3元/时',DEFAULT,DEFAULT,NULL);

INSERT INTO cost_chenqf VALUES (3,'8.5元套餐',100,8.5,0.2,0,'8.5元100小时/月,超出部分0.2元/时',DEFAULT,DEFAULT,NULL);

INSERT INTO cost_chenqf VALUES (4,'10.5元套餐',200,10.5,0.1,0,'10.5元200小时/月,超出部分0.1元/时',DEFAULT,DEFAULT,NULL);

INSERT INTO cost_chenqf VALUES (5,'计时收费',null,null,0.5,0,'0.5元/时,不使用不收费',DEFAULT,DEFAULT,NULL);

INSERT INTO cost_chenqf VALUES (6,'包月',null,20,null,0,'每月20元,不限制使用时间',DEFAULT,DEFAULT,NULL);

COMMIT;

--帐务信息表

create table account_chenqf(

account_id number(9) constraint account_chenqf_id_pk primary key,

recommender_id number(9),

login_name varchar2(30) not null,

login_passwd varchar2(30) not null,

status char(1),

create_date date default sysdate,

pause_date date,

close_date date,

real_name varchar2(20) not null,

idcard_no char(18) not null,

birthdate date,

gender char(1),

occupation varchar2(50),

telephone varchar2(15) not null,

email varchar2(50),

mailaddress varchar2(200),

zipcode char(6),

qq varchar2(15),

last_login_time date,

last_login_ip varchar2(15)

);

create sequence account_chenqf_seq start with 2000;

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy mm dd hh24:mi:ss';

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1005,NULL,'taiji001','256528',1,'2008 03 15','zhangsanfeng','19430225','410381194302256528',13669351234);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1010,NULL,'xl18z60','190613',1,'2009 01 10','guojing','19690319','330682196903190613',13338924567);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1011,1010,'dgbf70','270429',1,'2009 03 01','huangrong','19710827','330902197108270429',13637811357);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1015,1005,'mjjzh64','041115',1,'2010 03 12','zhangwuji','19890604','610121198906041115',13572952468);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1018,1011,'jmdxj00','010322',1,'2011 01 01','guofurong','199601010322','350581200201010322',18617832562);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1019,1011,'ljxj90','310346',1,'2012 02 01','luwushuang','19930731','320211199307310346',13186454984);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1020,NULL,'kxhxd20','012115',1,'2012 02 20','weixiaobao','20001001','321022200010012115',13953410078);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1021,NULL,'kxhxd21','012116',1,'2012 02 20','zhangsan','20001002','321022200010012116',13953410079);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1022,NULL,'kxhxd22','012117',1,'2012 02 20','lisi','20001003','321022200010012117',13953410080);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1023,NULL,'kxhxd23','012118',1,'2012 02 20','wangwu','20001004','321022200010012118',13953410081);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1024,NULL,'kxhxd24','012119',1,'2012 02 20','zhouliu','20001005','321022200010012119',13953410082);

INSERT INTO ACCOUNT_chenqf(ACCOUNT_ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,

REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)

VALUES(1025,NULL,'kxhxd25','012120',1,'2012 02 20','maqi','20001006','321022200010012120',13953410083);

COMMIT;

--业务信息表

create table service_chenqf(

service_id number(10) constraint service_chenqf_id_pk primary key,

account_id number(9) not null,

unix_host varchar2(15) not null ,

os_username varchar2(8) not null,

login_passwd varchar2(30) not null,

status char(1),

create_date date default sysdate,

pause_date date,

close_date date,

cost_id number(4) not null

);

create sequence service_chenqf_seq start with 3000;

INSERT INTO SERVICE_chenqf VALUES (2001,1010,'192.168.0.26','guojing','guo1234','0',sysdate,null,null,1);

INSERT INTO SERVICE_chenqf VALUES (2002,1011,'192.168.0.26','huangr','huang234','0',sysdate,null,null,1);

INSERT INTO SERVICE_chenqf VALUES (2003,1011,'192.168.0.20','huangr','huang234','0',sysdate,null,null,3);

INSERT INTO SERVICE_chenqf VALUES (2004,1011,'192.168.0.23','huangr','huang234','0',sysdate,null,null,6);

INSERT INTO SERVICE_chenqf VALUES (2005,1019,'192.168.0.26','luwsh','luwu2345','0',sysdate,null,null,4);

INSERT INTO SERVICE_chenqf VALUES (2006,1019,'192.168.0.20','luwsh','luwu2345','0',sysdate,null,null,5);

INSERT INTO SERVICE_chenqf VALUES (2007,1020,'192.168.0.20','weixb','wei12345','0',sysdate,null,null,6);

INSERT INTO SERVICE_chenqf VALUES (2008,1010,'192.168.0.20','guojing','guo09876','0',sysdate,null,null,6);

COMMIT;

--业务资费更新备份表

CREATE TABLE SERVICE_chenqf_UPDATE_BAK(

ID NUMBER(10) PRIMARY KEY,

SERVICE_ID NUMBER(9) NOT NULL,

cost_ID NUMBER(4) NOT NULL

);

create sequence service_chenqf_bak_seq;

--模块表

create table module_info_chenqf(

module_id number(4) constraint module_info_chenqf_id_pk primary key,

name varchar2(50) not null

);

create sequence module_chenqf_seq start with 100;

--角色表

create table role_info_chenqf(

role_id number(4) constraint role_info_id_pk primary key,

name varchar2(50) not null

);

create sequence role_chenqf_seq start with 1000;

--角色模块表

create table role_module_chenqf(

role_id number(4) not null,

module_id number(4) not null,

constraint role_module_chenqf_pk primary key(role_id,module_id)

);

--管理员表

create table admin_info_chenqf(

admin_id number(8) primary key not null,

admin_code varchar2(30) not null,

password varchar2(30) not null,

name varchar2(30) not null,

telephone varchar2(15),

email varchar2(50),

enrolldate date default sysdate not null

);

create sequence admin_chenqf_seq start with 10000;

--管理员角色表

create table admin_role_chenqf(

admin_id number(8) not null,

role_id number(4) not null,

constraint admin_role_chenqf_pk primary key(admin_id,role_id)

);

--模块表

insert into MODULE_INFO_chenqf values(1,'角色管理');

insert into MODULE_INFO_chenqf values(2,'管理员');

insert into MODULE_INFO_chenqf values(3,'资费管理');

insert into MODULE_INFO_chenqf values(4,'账务账号');

insert into MODULE_INFO_chenqf values(5,'业务账号');

insert into MODULE_INFO_chenqf values(6,'账单管理');

insert into MODULE_INFO_chenqf values(7,'报表');

commit;

--角色表

insert into role_info_chenqf values(100,'管理员');

insert into role_info_chenqf values(200,'营业员');

insert into role_info_chenqf values(300,'经理');

insert into role_info_chenqf values(400,'aaa');

insert into role_info_chenqf values(500,'bbb');

insert into role_info_chenqf values(600,'ccc');

commit;

--角色模块表

insert into role_module_chenqf values(100,1);

insert into role_module_chenqf values(100,2);

insert into role_module_chenqf values(200,3);

insert into role_module_chenqf values(200,4);

insert into role_module_chenqf values(200,5);

insert into role_module_chenqf values(200,6);

insert into role_module_chenqf values(300,7);

commit;

--管理员表

insert into admin_info_chenqf values(2000,'admin','123','ADMIN','123456789','[email protected]',sysdate);

insert into admin_info_chenqf values(3000,'zhangfei','123','ZhangFei','123456789','[email protected]',sysdate);

insert into admin_info_chenqf values(4000,'liubei','123','LiuBei','123456789','[email protected]',sysdate);

insert into admin_info_chenqf values(5000,'caocao','123','CaoCao','123456789','[email protected]',sysdate);

insert into admin_info_chenqf values(6000,'aaa','123','AAA','123456789','[email protected]',sysdate);

insert into admin_info_chenqf values(7000,'bbb','123','BBB','123456789','[email protected]',sysdate);

commit;

--管理员角色表

insert into admin_role_chenqf values(2000,100);

insert into admin_role_chenqf values(3000,200);

insert into admin_role_chenqf values(4000,300);

insert into admin_role_chenqf values(5000,100);

insert into admin_role_chenqf values(5000,200);

insert into admin_role_chenqf values(5000,300);

commit;