jn0801.sql

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

-- 创建集团资料
create table companytable  (
   nid                NUMBER                          not null,
   companyid		  VARCHAR(50),
   companyname        VARCHAR(50),
   callingtype        VARCHAR(50),
   companylevel       VARCHAR(50),
   address            VARCHAR(50),
   phone              VARCHAR(50),
   deputy             VARCHAR(50),
   companystate       VARCHAR(50),
   islogout           VARCHAR(50),
   ultimouser         VARCHAR(50),
   ultimoearning	  VARCHAR(50),	
   servicemanager 	  VARCHAR(50),
   turntime			  VARCHAR(50),	
   constraint PK_COMPANYTABLE primary key (nid)
);

comment on table companytable is
'集团信息表';

comment on column companytable.nid is
'集团ID号';

comment on column companytable.companyid is
'集团编号';

comment on column companytable.companyname is
'集团名称';

comment on column companytable.callingtype is
'行业类别';

comment on column companytable.companylevel is
'企业级别';

comment on column companytable.address is
'企业地址';

comment on column companytable.phone is
'企业电话';

comment on column companytable.deputy is
'企业法人';

comment on column companytable.companystate is
'集团状态值(录入,潜在,正式)';

comment on column companytable.islogout is
'是否被注销';

comment on column companytable.ultimouser is
'上月用户数';

comment on column companytable.ultimoearning is
'上月收入';

comment on column companytable.servicemanager is
'所属客户经理';

comment on column companytable.turntime is
'转正时间';


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

   

insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591001005','东南（福建）汽车工业有限公司',
						'00','省级','福州市闽侯县青口镇','0591-8797888','台湾佬','正式','0','5000','250000','10005','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591101006','东南（福建）眼科',
						'10','省级','福州市闽侯县青口镇','0591-8794888','台湾佬','潜在','0','7000','150000','10005','0');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj595141007','福建泉州恒泉化妆品有限公司',
						'14','省级','泉州市洛江区万安塘西工业园二期','0591-8797888','台湾佬','潜在','0','5000','250000','10006','0');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591001008','福建省公路工程试验检测中心站',
						'00','省级','福建省福州市五一中路104号','0591-3351624','共XX','正式','0','5000','250000','10006','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj592101009','厦门市药品检验所',
						'10','省级','福建省厦门市海山路33号','0592-5619839','共XX','潜在','0','5000','250000','10007','0');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041010','福建省电力试验研究院',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','10007','2008-09-01');		
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041011','福建省电力试验研究院2所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','潜在','0','5000','250000','10008','0');													
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041012','福建省电力试验研究院3所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','10008','2008-09-01');	
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041013','福建省电力试验研究院4所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041014','福建省电力试验研究院5所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041015','福建省电力试验研究院6所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041016','福建省电力试验研究院7所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041017','福建省电力试验研究院8所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');	
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041018','福建省电力试验研究院9所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041019','福建省电力试验研究院10所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041020','福建省电力试验研究院11所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');
insert into companytable (nid,companyid,companyname,callingtype,companylevel,address,phone,deputy,
						companystate,islogout,ultimouser,ultimoearning,servicemanager,turntime
						) values (SEQ_COMPANYTABLE.nextval,'0808fj591041021','福建省电力试验研究院12所',
						'04','省级','福建省福州市仓山区复园支路48号','0591-83042666','共XX','正式','0','5000','250000','0','2008-09-01');																																																																																								
						

--创建省市县代码表

create table ProvinceCitycode  (
   nid                NUMBER                          not null,
   sname              VARCHAR2(100),
   scode              VARCHAR2(100),
   npid               VARCHAR2(100),
   constraint PK_PROVINCECITYCODE primary key (nid)
);

comment on table ProvinceCitycode is
'省市县代码表';

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

comment on column ProvinceCitycode.sname is
'名称';

comment on column ProvinceCitycode.scode is
'代码';

comment on column ProvinceCitycode.npid is
'父集编号';

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

insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'福建省','fj','0');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'广东省','gd','0');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'福州市','591','1');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'厦门市','592','1');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'宁德市','593','1');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'泉州市','595','1');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'南平市','599','1');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'东莞市','769','2');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'汕头市','754','1');
insert into ProvinceCitycode (nid,sname,scode,npid) values (SEQ_ProvinceCitycode.nextval,'广州市','020','1');





commit;

--行业类型代码表

create table typecode  (
   nid                NUMBER                          not null,
   sname              VARCHAR2(100),
   scode              VARCHAR2(100),
   constraint PK_TYPECODE primary key (nid)
);

comment on table typecode is
'行业类型代码表';

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

comment on column typecode.sname is
'行业名称';

comment on column typecode.scode is
'对应的代码';


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

insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'政府机关','01');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'公安系统','02');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'金融系统','03');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'电力/电气/煤炭/能源','04');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'军队系统','05');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'工商/税务','06');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'教育/科研','07');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'旅游/宾馆/酒店/餐饮行业','08');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'IT/通信行业','09');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'医药/医疗/卫生','10');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'烟草系统','11');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'建筑/房地/装潢/设计行业','12');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'建筑/房地/装潢/设计行业','13');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'商业/批发/零售/贸易','14');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'生产/制造/加工','15');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'畜牧业/渔业/农业/林业','16');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'供水/水利系统','17');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'交通(交通.铁路.航空.运输.物流)','18');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'社会服务业(娱乐.运动.休闲)','19');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'新闻/出版/媒体','20');
insert into typecode (nid,sname,scode) values (SEQ_typecode.nextval,'其他','00');

commit;

--创建集团日志表

create table companylog  (
   nid                NUMBER                          not null,
   companyid          VARCHAR2(500),
   logtype            VARCHAR2(100),
   logcon             VARCHAR2(500),
   logtime            VARCHAR2(100),
   userid             VARCHAR2(100),
   constraint PK_COMPANYLOG primary key (nid)
);

comment on column companylog.companyid is
'集团ID号';

comment on column companylog.logtype is
'日志类型';

comment on column companylog.logcon is
'日志内容';

comment on column companylog.logtime is
'日志添加时间';

comment on column companylog.userid is
'操作人ID号';


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


--创建集团联系人资料表
create table companylinkmaninfo  (
   nid                NUMBER                          not null,
   companyid          VARCHAR2(100),
   companyname        VARCHAR2(100),
   linkman            VARCHAR2(50),
   linkmanduty        VARCHAR2(100),
   contacttype        VARCHAR2(200),
   constraint PK_companylinkmaninfo primary key (nid)
);

comment on table companylinkmaninfo is
'集团联系人资料表';

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

comment on column companylinkmaninfo.companyid is
'集团ID号';

comment on column companylinkmaninfo.companyname is
'集团名称';

comment on column companylinkmaninfo.linkman is
'联系人姓名';

comment on column companylinkmaninfo.linkmanduty is
'联系人职务';

comment on column companylinkmaninfo.contacttype is
'具体联系方式';


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

commit;



--创建集团用户话费表
create table companyfee  (
   nid                NUMBER                          not null,
   companyid          VARCHAR2(200),
   companyname        VARCHAR2(200),
   groupfee          NUMBER,
   usernumber         NUMBER,
   mdate              VARCHAR2(200),
   constraint PK_COMPANYFEE primary key (nid)
);

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

comment on column companyfee.companyid is
'集团编号';

comment on column companyfee.companyname is
'集团名称';

comment on column companyfee.groupfee is
'集团本月话费';

comment on column companyfee.usernumber is
'集团本月用户数';