jn0801.sql

自己制作的联通CRM,支持客户分类,管理,升级,积分管理等等..

comment on column companyfee.mdate is
'记录时间';



create sequence seq_companyfee
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;


declare

cursor cs_company is select companyid,companyname,deputy from companytable;
var_companyid varchar2(200);
var_companyname varchar2(200);
var_sql varchar2(1000);
var_deputy varchar2(200);
begin

for var_i in cs_company loop
	var_companyid := var_i.companyid;
	var_companyname := var_i.companyname;
	var_deputy := var_i.deputy;
	insert into companyfee (nid,companyid,companyname,groupfee,usernumber,mdate) values (seq_companyfee.nextval,var_companyid,var_companyname,trunc(dbms_random.value(1000,2000)),trunc(dbms_random.value(50,200)),'2008-08');
	insert into companyfee (nid,companyid,companyname,groupfee,usernumber,mdate) values (seq_companyfee.nextval,var_companyid,var_companyname,trunc(dbms_random.value(1000,2000)),trunc(dbms_random.value(50,200)),'2008-07');
	insert into companyfee (nid,companyid,companyname,groupfee,usernumber,mdate) values (seq_companyfee.nextval,var_companyid,var_companyname,trunc(dbms_random.value(1000,2000)),trunc(dbms_random.value(50,200)),'2008-06');
	insert into companyfee (nid,companyid,companyname,groupfee,usernumber,mdate) values (seq_companyfee.nextval,var_companyid,var_companyname,trunc(dbms_random.value(1000,2000)),trunc(dbms_random.value(50,200)),'2008-05');
	insert into companyfee (nid,companyid,companyname,groupfee,usernumber,mdate) values (seq_companyfee.nextval,var_companyid,var_companyname,trunc(dbms_random.value(1000,2000)),trunc(dbms_random.value(50,200)),'2008-04');
	insert into companyfee (nid,companyid,companyname,groupfee,usernumber,mdate) values (seq_companyfee.nextval,var_companyid,var_companyname,trunc(dbms_random.value(1000,2000)),trunc(dbms_random.value(50,200)),'2008-03');
	insert into companyfee (nid,companyid,companyname,groupfee,usernumber,mdate) values (seq_companyfee.nextval,var_companyid,var_companyname,trunc(dbms_random.value(1000,2000)),trunc(dbms_random.value(50,200)),'2008-02');
	insert into companylinkmaninfo (nid,companyid,companyname,linkman,linkmanduty,contacttype ) values (SEQ_linkmaninfo.nextval,var_companyid,var_companyname,var_deputy,'经理','131638'||trunc(dbms_random.value(10000,99999)));
end loop;
commit;

end;
/

commit;




--创建集团联系人信息日志表
create table companylinkmanlog  (
   nid                NUMBER                          not null,
   operatetype        VARCHAR2(100),
   optionuser         VARCHAR2(50),
   optiondate         VARCHAR2(50),
   constraint PK_companylinkmanlog primary key (nid)
);

comment on table companylinkmanlog is
'集团联系人信息日志表';

comment on column companylinkmanlog.nid is
'编号';

comment on column companylinkmanlog.operatetype is
'操作类型';

comment on column companylinkmanlog.optionuser is
'操作人';

comment on column companylinkmanlog.optiondate is
'操作时间';


create sequence SEQ_linkmanlog
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

commit;


--创建在线用户表
create table userinfo  (
   userid             NUMBER                          not null,
   phone              VARCHAR2(15),
   integralsign       VARCHAR2(10),
   username           VARCHAR2(200),
   papertype          VARCHAR2(200),
   paperid            VARCHAR2(200),
   servetype          VARCHAR2(200),
   area               VARCHAR2(200),
   city               VARCHAR2(200),
   businessroom       VARCHAR2(200),
   postaddress		  VARCHAR2(1000),
   tel				  VARCHAR2(100),
   postalcode		  NUMBER,
   optiontype         VARCHAR2(100),
   mainoption         VARCHAR2(100),
   regtime            VARCHAR2(200),
   oldoption          VARCHAR2(100),
   isgroupclient      NUMBER,
   usergrade	      VARCHAR2(200),
   bootstrap          VARCHAR2(200),
   ismember			  NUMBER,
   getmembertime      VARCHAR2(200),
   clientmanagerid    NUMBER,
   addintegral        NUMBER,
   integrallosttime   VARCHAR2(200),
   islock			  NUMBER,
   constraint PK_USERINFO primary key (userid)
);

comment on table userinfo is
'在线用户表';

comment on column userinfo.userid is
'用户编号';

comment on column userinfo.phone is
'用户手机号码';

comment on column userinfo.integralsign is
'积分申请标志';

comment on column userinfo.username is
'用户姓名';

comment on column userinfo.papertype is
'证件类型';

comment on column userinfo.paperid is
'证件号码';

comment on column userinfo.servetype is
'服务类型';

comment on column userinfo.postaddress is
'邮寄地址';

comment on column userinfo.tel is
'联系电话';

comment on column userinfo.postalcode is
'邮编';

comment on column userinfo.area is
'地区';

comment on column userinfo.city is
'县市';

comment on column userinfo.businessroom is
'营业厅';

comment on column userinfo.optiontype is
'业务类型';

comment on column userinfo.mainoption is
'主套餐';

comment on column userinfo.regtime is
'开户时间';

comment on column userinfo.oldoption is
'上月套餐';

comment on column userinfo.isgroupclient is
'是否集团客户';

comment on column userinfo.usergrade is
'会员等级';

comment on column userinfo.bootstrap is
'用户开机时间';

comment on column userinfo.ismember is
'是否是会员';

comment on column userinfo.getmembertime is
'成为会员日期';

comment on column userinfo.clientmanagerid is
'所属客户经理ID号';

comment on column userinfo.addintegral is
'累计积分';

comment on column userinfo.integrallosttime is
'积分失效期';

comment on column userinfo.islock is
'是否冻结';

create sequence SEQ_USERINFO
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;


--创建系统用户表
create table systemuserinfo  (
   nid                NUMBER                  not null,
   username           VARCHAR2(200),
   realname           NUMBER,
   realnametwo        NUMBER,
   loginname           NUMBER  ,
   password           VARCHAR2(200),
   roleid             NUMBER,
   constraint PK_SYSTEMUSERINFO primary key (nid)
);

comment on table systemuserinfo is
'系统用户表';

comment on column systemuserinfo.nid is
'用户编号';

comment on column systemuserinfo.username is
'用户名称';

comment on column systemuserinfo.realname is
'员工ID';

comment on column systemuserinfo.realnametwo is
'员工ID2';

comment on column systemuserinfo.loginname is
'登陆ID';

comment on column systemuserinfo.password is
'密码';

comment on column systemuserinfo.roleid is
'所属角色编号';

create sequence SEQ_SYSTEMUSERINFO
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'林小明',10000,10000,10000,'888888',1);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'吴老高',10001,10001,10001,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'黄小鸭',10002,10002,10002,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'张三丰',10003,10003,10003,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'李克勤',10004,10004,10004,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'刘德华',10005,10005,10005,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'吴忠宪',10006,10006,10006,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'蔡依林',10007,10007,10007,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'黄家驹',10008,10008,10008,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'姚明',10009,10009,10009,'888888',2);
insert into systemuserinfo(nid,username,realname,realnametwo,loginname,password,roleid) values (SEQ_SYSTEMUSERINFO.nextval,'林志颖',0,20001,20001,'888888',3);

commit;

--创建角色表
create table roleinfo  (
   nid                NUMBER                          not null,
   rolename           VARCHAR2(200),
   powerid            VARCHAR2(200),
   constraint PK_ROLEINFO primary key (nid)
);

comment on table roleinfo is
'角色表';

comment on column roleinfo.nid is
'角色编号';

comment on column roleinfo.rolename is
'角色名称';

comment on column roleinfo.powerid is
'权限编号';

create sequence seq_roleinfo
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1;


insert into roleinfo (nid,rolename,powerid) values (seq_roleinfo.nextval,'系统管理员','3,5,6,10,14,26,27,28,29');
insert into roleinfo (nid,rolename,powerid) values (seq_roleinfo.nextval,'大客户经理','3,26,31,32');
insert into roleinfo (nid,rolename,powerid) values (seq_roleinfo.nextval,'客户经理','1,2,4,7,8,9,11,12,13,15,16,17,18,19,20,21,22,23,24,25');


commit;



--创建权限表
create table powerinfo  (
   nid                NUMBER                          not null,
   sname              VARCHAR2(200),
   npid               NUMBER,
   slink              VARCHAR2(500),
   constraint PK_POWERINFO primary key (nid)
);

comment on table powerinfo is
'权限表';

comment on column powerinfo.nid is
'权限编号';

comment on column powerinfo.sname is
'权限名称';

comment on column powerinfo.npid is
'父级编号';

comment on column powerinfo.slink is
'权限链接';


create sequence seq_powerinfo
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;


insert into powerinfo(nid,sname,npid,slink) values(1,'客户分级管理',0,'0');	 	 
insert into powerinfo(nid,sname,npid,slink) values(2,'俱乐部管理',0,'0');		 
insert into powerinfo(nid,sname,npid,slink) values(3,'集团管理',0,'0');		 
insert into powerinfo(nid,sname,npid,slink) values(4,'积分管理',0,'0');		 
insert into powerinfo(nid,sname,npid,slink) values(5,'系统管理',0,'0');		
insert into powerinfo(nid,sname,npid,slink) values(6,'数据字典管理',5,'0');	 
insert into powerinfo(nid,sname,npid,slink) values(7,'客户分级规则管理',1,'ruleManage.do?tag=showRule');	 
insert into powerinfo(nid,sname,npid,slink) values(8,'客户分级处理',1,'/gradeDealAction?tag=list');	 
insert into powerinfo(nid,sname,npid,slink) values(9,'客户分级手工调整',1,'/manualAudUserAction?tag=enterPage');	 
insert into powerinfo(nid,sname,npid,slink) values(10,'客户经理的分配',5,'/managerAssign.do?task=list');		 
insert into powerinfo(nid,sname,npid,slink) values(11,'业务受理登记',1,'/operation.do');	 	 
insert into powerinfo(nid,sname,npid,slink) values(12,'业务受理处理',1,'/dealWithOperation.do?task=SearchOperationDeal');	 
insert into powerinfo(nid,sname,npid,slink) values(13,'客户资料维护',1,'/clintmanage.do?tag=getalluser');	
insert into powerinfo(nid,sname,npid,slink) values(14,'系统用户管理',5,'/systemUserAtion.do?tag=show'); 
insert into powerinfo(nid,sname,npid,slink) values(15,'查询分级结果',1,'/manualAudUserAction?tag=logPage');
insert into powerinfo(nid,sname,npid,slink) values(16,'联盟商家质量级别评定',2,'businessGrade.do?task=show');
insert into powerinfo(nid,sname,npid,slink) values(17,'俱乐部服务体验网点',2,'clubmanageAction.do?tag=showclub');
insert into powerinfo(nid,sname,npid,slink) values(18,'俱乐部活动管理',2,'clubAction.do?tag=show');
insert into powerinfo(nid,sname,npid,slink) values(19,'所有联盟商家基本资料',2,'businessInfoAction.do?tag=enterPage');
insert into powerinfo(nid,sname,npid,slink) values(20,'俱乐部会员卡管理',2,'clubmanageAction.do?tag=showmember');
insert into powerinfo(nid,sname,npid,slink) values(21,'积分明细查询',4,'intergralDetailSearch.do?task=linked');
insert into powerinfo(nid,sname,npid,slink) values(22,'积分申请',4,'intergralRequisition.do?task=showIntergralRequisition');
insert into powerinfo(nid,sname,npid,slink) values(23,'积分消费',4,'intergralConsume.do?task=showIntergralConsume');
insert into powerinfo(nid,sname,npid,slink) values(24,'积分规则管理',4,'intergralRule.do?task=listIntergralRule');
insert into powerinfo(nid,sname,npid,slink) values(25,'积分物品管理',4,'product.do?task=managerProduct');
insert into powerinfo(nid,sname,npid,slink) values(26,'集团维护',3,'/latencyGroup.do?task=listgroup');