shh.sql

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

-------------------------------------- create table 物品表 --------------------------------------

create table productdetail
(
  productid      number not null,
  productname    varchar2(100),
  productintroduce   varchar2(1000),
  productimage   varchar2(1000),
  point		number,
  typeid         number,
  productor      varchar2(100),
  productstorage number,
  constraint pk_productdetail primary key (productid)
);

comment on column productdetail.productid  is '物品id';
comment on column productdetail.productname  is '物品名';
comment on column productdetail.productintroduce  is '物品介绍';
comment on column productdetail.productimage   is '物品图片';
comment on column productdetail.point  is '所需积分';
comment on column productdetail.typeid  is '类型id';
comment on column productdetail.productor  is '厂家';
comment on column productdetail.productstorage  is '库存';
  
create sequence seq_productdetail
minvalue 1
maxvalue 99999999999999
start with 1
increment by 1;

insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '50元面额充值卡', '50元面额充值卡', null, 150, 1, '联通', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '100元面额充值卡', '100元面额充值卡', null, 300, 1, '联通', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '300条短信', '300条短信', null, 100, 2, '联通', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '100条彩信', '100条彩信', null, 300, 2, '联通', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '月租', '免月租×6个月', null, 500, 2, '联通', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '月租', '免月租×12个月', null, 1000, 2, '联通', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '福州-香港机票', '福州-香港经济舱3折机票,每周二,周五两次航班', null, 600, 3, '中国国航', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '福州-北京机票', '福州-北京经济舱3折机票,每周一,周三,周五三次航班', null, 600, 3, '中国国航', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '福州-曼谷往返机票', '福州-曼谷商务舱5折机票,每周二,周四两次航班', null, 500, 3, '厦门航空', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '福州-首尔机票', '福州-首尔商务舱5折机票,每周一,周三两次航班', null, 500, 3, '厦门航空', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '福州-东京机票', '福州-东京商务舱5折,每周二,周五两次航班', null, 1000, 3, '厦门航空', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '福州-纽约机票', '福州-纽约商务舱5折,每周五一次航班', null, 1000, 3, '厦门航空', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '上海金茂大酒店', '双人商务套房8.8折,往返接送', null, 1000, 4, '上海金茂大酒店', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '北京香山饭店', '双人商务套房8.8折,往返接送', null, 2000, 4, '北京香山饭店', -1);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '纽约希尔顿饭店', '双人经济房8.8折', null, 2000, 4, '纽约希尔顿饭店', 100000000);
insert into productdetail (productid, productname, productintroduce, productimage, point, typeid, productor, productstorage)
values (seq_productdetail.nextval, '电影票', '50元等额电影票', null, 500, 6, '联通', 100000000);

commit;

-------------------------------------- create table 物品类型 --------------------------------------

create table producttype
(
  typeid   number not null,
  typename varchar2(100),
  sortname varchar2(100),
  constraint pk_producttype primary key (typeid)
);

comment on column producttype.typeid  is '类型id';
comment on column producttype.typename  is '类型名称';
comment on column producttype.sortname  is '类别';
  
create sequence seq_producttype
minvalue 1
maxvalue 99999999999999
start with 1
increment by 1;

insert into producttype (typeid, typename, sortname)
values (seq_producttype.nextval, '通信类', '充值卡');
insert into producttype (typeid, typename, sortname)
values (seq_producttype.nextval, '通信类', '业务优惠');
insert into producttype (typeid, typename, sortname)
values (seq_producttype.nextval, '非通信类', '机票打折');
insert into producttype (typeid, typename, sortname)
values (seq_producttype.nextval, '非通信类', '酒店打折');
insert into producttype (typeid, typename, sortname)
values (seq_producttype.nextval, '非通信类', '高尔夫俱乐部会员');
insert into producttype (typeid, typename, sortname)
values (seq_producttype.nextval, '非通信类', '热门电影');
insert into producttype (typeid, typename, sortname)
values (seq_producttype.nextval, '非通信类', '健身俱乐部会员');

commit;

-------------------------------------- create table 物品操作日志 --------------------------------------

create table productlog
(
  logid      number not null,
  productid  number,
  productnum	number,
  logtype    varchar2(100),
  logdetail  varchar2(100),
  logtime    varchar2(100),
  operatorid number,
  constraint pk_productlog primary key (logid)
);

comment on column productlog.logid  is '日志id';
comment on column productlog.productid  is '产品id';
comment on column productlog.productnum  is '产品数量';
comment on column productlog.logtype  is '日志类型';
comment on column productlog.logdetail  is '日志详细';
comment on column productlog.logtime  is '日志记录时间';
comment on column productlog.operatorid  is '操作员';
  
create sequence seq_productlog
minvalue 1
maxvalue 99999999999999
start with 1
increment by 1;

-------------------------------------- create table 积分兑换规则 --------------------------------------

create table intergralrule(
    ruleid        number,
    location    varchar2(20),
    servertype    varchar2(20),
    minintergral    number,
    rate            number,
    constraint pk_ruleid primary key(ruleid)
);

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

-------------------------------------- create table 积分兑换记录 --------------------------------------

create table pointconsumerecord
(
  consumeid        number not null,
  userid           number,
  productlogid	number,
  phone            varchar2(50),
  consumedate      varchar2(50),
  consumetype      varchar2(50),
  consumepoint  number,
  consumelocation  varchar2(50),
  consumecity      varchar2(50),
  operatorid       number,
  constraint pk_intergralconsume primary key (consumeid)
);

comment on column pointconsumerecord.consumeid  is '兑换id';
comment on column pointconsumerecord.userid  is '用户id';
comment on column pointconsumerecord.productlogid  is '物品操作id';
comment on column pointconsumerecord.phone  is '电话号码';
comment on column pointconsumerecord.consumedate  is '兑换时间';
comment on column pointconsumerecord.consumetype  is '兑换类型';
comment on column pointconsumerecord.consumepoint  is '兑换积分';
comment on column pointconsumerecord.consumelocation  is '兑换地区';
comment on column pointconsumerecord.consumecity  is '兑换县市';
comment on column pointconsumerecord.operatorid  is '操作员';
  
create sequence seq_intergralconsume
minvalue 1
maxvalue 99999999999999
start with 1
increment by 1;

-------------------------------------- create table 创建数据字典表 --------------------------------------

create table datadictionary  (
   nid                NUMBER                          not null,
   sname              VARCHAR2(200),
   npid               NUMBER,
   constraint PK_DATADICTIONARY primary key (nid)
);

comment on table datadictionary is '数据字典表';