jn0801.sql

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

--创建分级结果表

create table graderesultinfo  (
   nid                NUMBER                          not null,
   userphone          VARCHAR2(15),
   oldsign            VARCHAR2(200),
   oldgrade           VARCHAR2(200),
   newsign            VARCHAR2(200),
   newgrade           VARCHAR2(200),
   optionuser         VARCHAR2(200),
   optiondate         VARCHAR2(200),
   constraint PK_GRADERESULTINFO primary key (nid)
);

comment on table graderesultinfo is
'客户分级结果表';

comment on column graderesultinfo.nid is
'分级结果编号';

comment on column graderesultinfo.userphone is
'用户手机号码';

comment on column graderesultinfo.oldsign is
'分级前品牌';

comment on column graderesultinfo.oldgrade is
'分级前等级';

comment on column graderesultinfo.newsign is
'分级后品牌';

comment on column graderesultinfo.newgrade is
'分级后等级';

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

comment on column graderesultinfo.optiondate is
'操作日期';

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


--创建客户分级手动审核日志表
create table auditinguserlog  (
   nid                NUMBER                          not null,
   userid			  NUMBER,
   beauduser          VARCHAR2(100),
   auduser            VARCHAR2(200),
   audtime            VARCHAR2(200),
   constraint PK_auditinguserlog primary key (nid)
);

comment on table auditinguserlog is
'客户分级手动审核日志表';

comment on column auditinguserlog.nid is
'日志ID号';

comment on column auditinguserlog.userid is
'被审核的用户ID号';

comment on column auditinguserlog.beauduser is
'被审核的用户名';

comment on column auditinguserlog.auduser is
'审核人';

comment on column auditinguserlog.audtime is
'审核时间';

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

--创建联盟商家基本资料表
create table businessBasicInfoTable  (
   nid                NUMBER                          not null,
   partnerType        VARCHAR2(100),
   bsLevel            NUMBER,
   bsName             VARCHAR2(100),
   bsShortName        VARCHAR2(100),
   bsKind             VARCHAR2(100),
   bsAddress          VARCHAR2(100),
   corporation        VARCHAR2(100),
   bsEmployee         NUMBER,
   bsLoginFund        VARCHAR2(100),
   bsLinkman          VARCHAR2(100),
   bsWorkState        VARCHAR2(100),
   rulerName          VARCHAR2(50),
   rulerSex           VARCHAR2(10),
   rulerPosition      VARCHAR2(100),
   relationType       VARCHAR2(50),
   bsAddTime          VARCHAR2(20),
   logout             NUMBER,
   constraint PK_BUSINESSBASICINFOTABLE primary key (nid)
);

comment on table businessBasicInfoTable is
'联盟商家基本资料表';

comment on column businessBasicInfoTable.nid is
'商家ID号';

comment on column businessBasicInfoTable.partnerType is
'合作伙伴类型';

comment on column businessBasicInfoTable.bsLevel is
'商家级别';

comment on column businessBasicInfoTable.bsName is
'商家全称';

comment on column businessBasicInfoTable.bsShortName is
'商家简称';

comment on column businessBasicInfoTable.bsKind is
'商家性质';

comment on column businessBasicInfoTable.bsAddress is
'商家地址';

comment on column businessBasicInfoTable.corporation is
'商家法人代表';

comment on column businessBasicInfoTable.bsEmployee is
'商家员工数量';

comment on column businessBasicInfoTable.bsLoginFund is
'商家注册资金';

comment on column businessBasicInfoTable.bsLinkman is
'商家联系人';

comment on column businessBasicInfoTable.bsWorkState is
'商家经营状况';

comment on column businessBasicInfoTable.rulerName is
'管理者姓名';

comment on column businessBasicInfoTable.rulerSex is
'管理者性别';

comment on column businessBasicInfoTable.rulerPosition is
'管理者职位';

comment on column businessBasicInfoTable.relationType is
'联系方式';

comment on column businessBasicInfoTable.bsAddTime is
'商家添加时间';

comment on column businessBasicInfoTable.logout is
'是否注销商家';

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

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',1,'贵州丽华集团有限公司','丽华',null,'贵州省贵阳市省府路1号','吴本人',500,10000000,'李美人','很好','吴本人','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',null,'贵州神奇制药','神奇',null,'贵州省贵阳市省府路2号','由此人',500,10000000,'杨妹妹','很好','李四','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',4,'贵州黄果树集团',null,null,'贵州省贵阳市省府路3号','张三',500,10000000,'林妹妹','很好','周四','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',null,'福州某某集团',null,null,'贵州省贵阳市省府路3号','张三',500,10000000,'林妹妹','很好','周四','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',5,'福州电力公司',null,null,'贵州省贵阳市省府路3号','张三',500,10000000,'林妹妹','很好','周四','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',null,'贵州黄果树集团2',null,null,'贵州省贵阳市省府路3号','张三',500,10000000,'林妹妹','很好','周四','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',3,'贵州黄果树集团3',null,null,'贵州省贵阳市省府路3号','张三',500,10000000,'林妹妹','很好','周四','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',null,'贵州黄果树集团5',null,null,'贵州省贵阳市省府路3号','张三',500,10000000,'林妹妹','很好','周四','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',null,'贵州黄果树集团6',null,null,'贵州省贵阳市省府路3号','张三',500,10000000,'林妹妹','很好','周四','男','董事长','电话','2008-08-20',1);

insert into businessBasicInfoTable(nid,partnerType,bsLevel,bsName,bsShortName,bsKind,bsAddress,corporation,bsEmployee,bsLoginFund,bsLinkman,bsWorkState,rulerName,rulerSex,rulerPosition,relationType,bsAddTime,logout)
values(seq_bsinfo.nextval,'战略合作伙伴',2,'贵州黄果树集团7',null,null,'贵州省贵阳市省府路3号','张三',500,10000000,'林妹妹','很好','周四','男','董事长','电话','2008-08-20',1);


--创建积分总表
create table intergralinfo  (
   nid                NUMBER                          not null,
   userid             NUMBER,
   phone              VARCHAR2(200),
   recordtime         VARCHAR2(200),
   phonefree          NUMBER,
   lastfree           NUMBER,
   baseintegral       NUMBER,
   consumeintergral   NUMBER,
   onlineintergral    NUMBER,
   honorintergral     NUMBER,
   relativesign       VARCHAR2(200),
   guerdonintergral   NUMBER,
   adjustintergral    NUMBER,
   constraint PK_intergralinfo primary key (nid)
);

comment on column intergralinfo.nid is
'ID号';

comment on column intergralinfo.userid is
'用户ID号';

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

comment on column intergralinfo.recordtime is
'积分记录时间';

comment on column intergralinfo.phonefree is
'总实际话费';

comment on column intergralinfo.lastfree is
'扣非积分部分费用';

comment on column intergralinfo.baseintegral is
'基本积分';

comment on column intergralinfo.consumeintergral is
'当月消费积分';

comment on column intergralinfo.onlineintergral is
'当月在网积分';

comment on column intergralinfo.honorintergral is
'当月信用积分';

comment on column intergralinfo.relativesign is
'对应权重';

comment on column intergralinfo.guerdonintergral is
'当月奖励积分';

comment on column intergralinfo.adjustintergral is
'当月调整积分';

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


commit;

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


--创建俱乐部会员卡表
create table membercardinfo  (
   cardid             NUMBER                          not null,
   userid             NUMBER,
   memberphone        VARCHAR2(20),
   servetype		  VARCHAR2(100),
   usergrade		  VARCHAR2(100),
   mdate              VARCHAR2(100),
   islock             NUMBER,
   constraint PK_MEMBERCARDINFO primary key (cardid)
);

comment on table membercardinfo is
'俱乐部会员卡表';

comment on column membercardinfo.cardid is
'会员卡编号';

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

comment on column membercardinfo.memberphone is
'手机号码';

comment on column membercardinfo.servetype is
'品牌';

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

comment on column membercardinfo.mdate is
'发卡日期';

comment on column membercardinfo.islock is
'是否注销';


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

commit;


--创建俱乐部网点管理表
create table clubInfoManage  (
   nid                 NUMBER     not null,
   clubname          VARCHAR(50),
   address           VARCHAR(100),
   phone             VARCHAR(20),
   manage            VARCHAR(100),
   servicetype       VARCHAR(50),
   function           VARCHAR(50),
   levels             VARCHAR(50),
   constraint PK_CLUBINFOMANAGE primary key (nid)
);

comment on table clubInfoManage is
'联通总部俱乐部表';

comment on column clubInfoManage.nid is
'俱乐部ID号';

comment on column clubInfoManage.clubname is
'俱乐部名称';

comment on column clubInfoManage.address is
'网点地址';

comment on column clubInfoManage.phone is
'电话';

comment on column clubInfoManage.manage is