shh.sql

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

comment on column datadictionary.nid is '编号';
comment on column datadictionary.sname is '名称';
comment on column datadictionary.npid is '父类编号';

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

insert into DATADICTIONARY (NID, SNAME, NPID) values (1, '会员等级', 0);
insert into DATADICTIONARY (NID, SNAME, NPID) values (2, '钻石卡', 1);
insert into DATADICTIONARY (NID, SNAME, NPID) values (3, '金卡', 1);
insert into DATADICTIONARY (NID, SNAME, NPID) values (4, '银卡', 1);
insert into DATADICTIONARY (NID, SNAME, NPID) values (5, '品牌类型', 0);
insert into DATADICTIONARY (NID, SNAME, NPID) values (6, '世界风', 5);
insert into DATADICTIONARY (NID, SNAME, NPID) values (7, '新势力', 5);
insert into DATADICTIONARY (NID, SNAME, NPID) values (8, '新时空', 5);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1001, '积分等级', 0);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1002, '0-1000', 1001);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1003, '1000-2000', 1001);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1004, '2000-5000', 1001);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1005, '5000-10000', 1001);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1101, '话费等级', 0);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1102, '1-100', 1101);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1103, '100-300', 1101);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1104, '300-500', 1101);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1105, '500-1000', 1101);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1106, '1000-5000', 1101);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1201, '地区', 0);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1203, '福建省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1204, '广东省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1205, '浙江省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1206, '江西省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1207, '安徽省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1208, '湖南省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1209, '湖北省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1210, '广西省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1211, '黑龙江省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1212, '北京市', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1213, '上海市', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1214, '重庆市', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (1215, '四川省', 1201);
insert into DATADICTIONARY (NID, SNAME, NPID) values (2001, '福州市', 1203);
insert into DATADICTIONARY (NID, SNAME, NPID) values (2002, '泉州市', 1203);
insert into DATADICTIONARY (NID, SNAME, NPID) values (2003, '厦门市', 1203);
insert into DATADICTIONARY (NID, SNAME, NPID) values (2004, '漳州市', 1203);
insert into DATADICTIONARY (NID, SNAME, NPID) values (2005, '莆田市', 1203);

commit;

-------------------------------------- initional data 用户初始化 --------------------------------------

DECLARE
    v_loop number:=1;
BEGIN
    FOR v_loop IN 1..200 LOOP
        insert into USERINFO 
        (USERID, PHONE, INTEGRALSIGN, USERNAME, PAPERTYPE, PAPERID, SERVETYPE, AREA, CITY, 
        BUSINESSROOM, OPTIONTYPE, MAINOPTION, REGTIME, OLDOPTION, ISGROUPCLIENT,USERGRADE,
        ISMEMBER,GETMEMBERTIME, CLIENTMANAGERID, ADDINTEGRAL,INTEGRALLOSTTIME,ISLOCK)
        values (seq_userinfo.nextval, '130638'||trunc(dbms_random.value(10000,99999)), '1', 'user'||seq_userinfo.nextval, '身份证', '35010219860101'||trunc(dbms_random.value(1000,9999)), '世界风', '福建省', '福州市', 
        '鼓楼区', '', '', to_char(sysdate,'yyyy-mm-dd'), '','', '银卡',
        1,to_char(sysdate,'yyyy-mm-dd'), 2, null, '', 0);
        insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-08',trunc(dbms_random.value(100,500)),trunc(dbms_random.value(100,500)));
        insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-07',trunc(dbms_random.value(100,800)),trunc(dbms_random.value(100,800)));
        insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-06',trunc(dbms_random.value(100,500)),trunc(dbms_random.value(100,800)));
        insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-05',trunc(dbms_random.value(100,500)),trunc(dbms_random.value(900,1800)));
        insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-04',trunc(dbms_random.value(100,500)),trunc(dbms_random.value(3000,4000)));
        insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-03',trunc(dbms_random.value(100,500)),trunc(dbms_random.value(1800,3000)));
        insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2007-12',trunc(dbms_random.value(100,500)),trunc(dbms_random.value(3000,4000)));
        insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2007-11',trunc(dbms_random.value(100,500)),trunc(dbms_random.value(1800,3000)));
        
        
    END LOOP;
    FOR v_loop IN 1..200 LOOP
        insert into USERINFO 
        (USERID, PHONE, INTEGRALSIGN, USERNAME, PAPERTYPE, PAPERID, SERVETYPE, AREA, CITY, 
        BUSINESSROOM, OPTIONTYPE, MAINOPTION, REGTIME, OLDOPTION, ISGROUPCLIENT,USERGRADE,
        ISMEMBER,GETMEMBERTIME, CLIENTMANAGERID, ADDINTEGRAL, INTEGRALLOSTTIME,ISLOCK)
        values (seq_userinfo.nextval, '131638'||trunc(dbms_random.value(10000,99999)), '0', 'user'||seq_userinfo.nextval, '身份证',  '35010219860101'||trunc(dbms_random.value(1000,9999)), '新势力', '福建省', '福州市', 
        '仓山区', '', '', to_char(sysdate,'yyyy-mm-dd HH:MM'), '','', '金卡', 
        1, '2007-08-27', 3, null, '', 0);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-08',trunc(dbms_random.value(100,300)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-07',trunc(dbms_random.value(100,300)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-06',trunc(dbms_random.value(100,300)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-05',trunc(dbms_random.value(100,300)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-04',trunc(dbms_random.value(100,300)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-03',trunc(dbms_random.value(100,300)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-02',trunc(dbms_random.value(100,200)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-01',trunc(dbms_random.value(100,200)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2007-12',trunc(dbms_random.value(100,200)),300);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2007-11',trunc(dbms_random.value(100,200)),300);
	
	
	
    END LOOP;
    FOR v_loop IN 1..200 LOOP
        insert into USERINFO 
        (USERID, PHONE, INTEGRALSIGN, USERNAME, PAPERTYPE, PAPERID, SERVETYPE, AREA, CITY, 
        BUSINESSROOM, OPTIONTYPE, MAINOPTION, REGTIME, OLDOPTION, ISGROUPCLIENT,USERGRADE,
        ISMEMBER,GETMEMBERTIME, CLIENTMANAGERID, ADDINTEGRAL, INTEGRALLOSTTIME,ISLOCK)
        values (seq_userinfo.nextval, '132638'||trunc(dbms_random.value(10000,99999)), '1', 'user'||seq_userinfo.nextval, '身份证',  '35010219860101'||trunc(dbms_random.value(1000,9999)), '新时空', '福建省', '泉州市', 
        '仓山区', '', '', to_char(sysdate,'yyyy-mm-dd HH:MM'), '','', '钻石卡', 
        1, '2007-09-10', 4, null, '', 0);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-07',trunc(dbms_random.value(50,200)),200);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-06',trunc(dbms_random.value(50,200)),200);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-05',trunc(dbms_random.value(50,200)),200);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2007-12',trunc(dbms_random.value(50,200)),200);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2007-11',trunc(dbms_random.value(50,200)),200);
	
	
	
    END LOOP;
    FOR v_loop IN 1..200 LOOP
        insert into USERINFO 
        (USERID, PHONE, INTEGRALSIGN, USERNAME, PAPERTYPE, PAPERID, SERVETYPE, AREA, CITY, 
        BUSINESSROOM, OPTIONTYPE, MAINOPTION, REGTIME, OLDOPTION, ISGROUPCLIENT,USERGRADE,
        ISMEMBER,GETMEMBERTIME, CLIENTMANAGERID, ADDINTEGRAL, INTEGRALLOSTTIME,ISLOCK)
        values (seq_userinfo.nextval, '133638'||trunc(dbms_random.value(10000,99999)), '0', 'user'||seq_userinfo.nextval, '身份证',  '35010219860101'||trunc(dbms_random.value(1000,9999)), '如意通', '福建省', '厦门市', 
        '胡里区', '', '', to_char(sysdate,'yyyy-mm-dd HH:MM'), '','', '',
        0, '', 5, null, '', 0);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-07',trunc(dbms_random.value(50,100)),150);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2008-06',trunc(dbms_random.value(50,200)),150);
    insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2007-12',trunc(dbms_random.value(50,100)),150);
	insert into phonefeeinfo(phonefeeinfoid,userid,mdate,money,prepay) values (seq_phonefeeinfo.nextval,seq_userinfo.currval,'2007-11',trunc(dbms_random.value(50,200)),150);
    END LOOP;
    
    commit;
END;
/

declare
    var_sql varchar2(1000);     --sql语句
    var_userid userinfo.userid%type;       --用户表id
    var_phone userinfo.phone%type;  		--用户手机号码
    var_servetype userinfo.servetype%type;    --用户品牌
	var_usergrade userinfo.usergrade%type;		--用户等级
    var_phonefee number:=0;    --用户话费
    var_mdate varchar2(100);    --日期
    var_phonefeeinfo phonefeeinfo%rowtype;      --话费行变量
    type dynamic_cursor is ref cursor;      --动态游标
    cursor cur_userinfo  is select userid,phone,servetype,usergrade from userinfo where ismember=1; --用户表游标
    cur_phonefeeinfo dynamic_cursor;    --话费表游标
begin
    for var_i in cur_userinfo loop
        var_userid := var_i.userid;
        var_phone := var_i.phone;
        var_sql := 'select * from phonefeeinfo where userid='||var_userid;  --根据用户id取出该用户的话费记录
        open cur_phonefeeinfo for var_sql;
        loop
            fetch cur_phonefeeinfo into var_phonefeeinfo;   
            exit when cur_phonefeeinfo%notfound;
            var_phonefee := var_phonefeeinfo.money; --该月话费
            var_mdate := var_phonefeeinfo.mdate;
            insert into intergralinfo (nid,userid,phone,recordtime,phonefree,lastfree,baseintegral,consumeintergral,onlineintergral,honorintergral,relativesign,guerdonintergral,adjustintergral) values
            (seq_intergralinfo.nextval,var_userid,var_phone,var_mdate,var_phonefee,0,2000,0,100,100,2,100,1000);
        end loop;
        close cur_phonefeeinfo;
    end loop;
    
    for var_i in cur_userinfo loop
        var_userid := var_i.userid;
        var_phone := var_i.phone;
        var_servetype := var_i.servetype;
        var_usergrade := var_i.usergrade;
        insert into membercardinfo(cardid,userid,memberphone,servetype,usergrade,mdate,islock) values 
        (seq_membercardinfo.nextval,var_userid,var_phone,var_servetype,var_usergrade,'2007-08-27',0);
    end loop;
    
    commit;
end;
/

UPDATE intergralinfo 
SET consumeintergral = trunc(phonefree*trunc(dbms_random.value(1.1,1.3),2))
where userid between 1 and 200;

UPDATE intergralinfo 
SET consumeintergral = trunc(phonefree*trunc(dbms_random.value(1.1,1.3),2))
where userid between 201 and 400;

UPDATE intergralinfo 
SET consumeintergral = trunc(phonefree*trunc(dbms_random.value(1.1,1.3),2))
where userid between 401 and 600;

UPDATE intergralinfo 
SET consumeintergral = trunc(phonefree*trunc(dbms_random.value(1.1,1.3),2))
where userid >600;

commit;