jn0801.sql

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

'网点管理员';

comment on column clubInfoManage.servicetype is
'网点服务类型';

comment on column clubInfoManage.function is
'网点服务功能';

comment on column clubInfoManage.levels is
'网点级别';

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

insert into clubInfoManage(nid,clubname,address,phone,manage,servicetype,function,levels)
values(SEQ_CLUB_NET.nextval,'东方俱乐部','福建省福州市','83888888','张三','运动/健身','全能','初出茅庐');

insert into clubInfoManage(nid,clubname,address,phone,manage,servicetype,function,levels)
values(SEQ_CLUB_NET.nextval,'工人俱乐部','福建省泉州市','82888881','王五','娱乐/休闲','全能','初出茅庐');

insert into clubInfoManage(nid,clubname,address,phone,manage,servicetype,function,levels)
values(SEQ_CLUB_NET.nextval,'天天俱乐部','福建省福州市','83888888','李四','郊游/旅游','全能','初出茅庐');

insert into clubInfoManage(nid,clubname,address,phone,manage,servicetype,function,levels)
values(SEQ_CLUB_NET.nextval,'多多俱乐部','福建省福州市','83888888','小二','郊游/旅游','全能','初出茅庐');

insert into clubInfoManage(nid,clubname,address,phone,manage,servicetype,function,levels)
values(SEQ_CLUB_NET.nextval,'哦哦俱乐部','福建省福州市','83888888','林一','郊游/旅游','全能','初出茅庐');

insert into clubInfoManage(nid,clubname,address,phone,manage,servicetype,function,levels)
values(SEQ_CLUB_NET.nextval,'乖乖俱乐部','福建省福州市','83888888','老六','郊游/旅游','全能','初出茅庐');

insert into clubInfoManage(nid,clubname,address,phone,manage,servicetype,function,levels)
values(SEQ_CLUB_NET.nextval,'哈哈俱乐部','福建省福州市','83888888','吴七','郊游/旅游','全能','初出茅庐');

commit;

create table ADRESS
(
  AID   NUMBER not null,
  ANAME VARCHAR2(100),
  PRAID NUMBER
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table ADRESS
  add constraint ADDRESSP primary key (AID)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
insert into ADRESS (AID, ANAME, PRAID)
values (1, '福建', 0);
insert into ADRESS (AID, ANAME, PRAID)
values (2, '广东', 0);
insert into ADRESS (AID, ANAME, PRAID)
values (3, '江西', 0);
insert into ADRESS (AID, ANAME, PRAID)
values (4, '四川', 0);
insert into ADRESS (AID, ANAME, PRAID)
values (5, '莆田', 1);
insert into ADRESS (AID, ANAME, PRAID)
values (6, '福州', 1);
insert into ADRESS (AID, ANAME, PRAID)
values (7, '厦门', 1);
insert into ADRESS (AID, ANAME, PRAID)
values (8, '漳州', 1);
insert into ADRESS (AID, ANAME, PRAID)
values (9, '南平', 1);
insert into ADRESS (AID, ANAME, PRAID)
values (10, '龙岩', 1);
insert into ADRESS (AID, ANAME, PRAID)
values (11, '宁德', 1);
insert into ADRESS (AID, ANAME, PRAID)
values (12, '广州', 2);
insert into ADRESS (AID, ANAME, PRAID)
values (13, '惠州', 2);
insert into ADRESS (AID, ANAME, PRAID)
values (14, '深圳', 2);
insert into ADRESS (AID, ANAME, PRAID)
values (15, '佛山', 2);
insert into ADRESS (AID, ANAME, PRAID)
values (16, '好地方', 2);
insert into ADRESS (AID, ANAME, PRAID)
values (17, '重庆', 3);
insert into ADRESS (AID, ANAME, PRAID)
values (18, '达州', 3);
insert into ADRESS (AID, ANAME, PRAID)
values (19, '文村', 3);
insert into ADRESS (AID, ANAME, PRAID)
values (20, '不知道', 3);

--创建分页函数
--包头的定义
create or replace package package_test

is
--定义游标
type dynamic_cs is ref cursor;
--定义分页函数
function page_info(var_sql varchar2,currentPage number,pageSize number) return dynamic_cs;


--定义动态分级处理
procedure gradedeal(var_sql varchar2,currentPage number,pageSize number,var_usergrade varchar2);


--定义会员等级定期检查
procedure checkmember;

end package_test;
/
commit;

--包体的定义
create or replace package body package_test
is

--分页函数
function page_info(var_sql varchar2,currentPage number,pageSize number) return dynamic_cs

is
var_firstResult number;

cs_userinfo dynamic_cs;

var_tempsql varchar2(2000);
begin

if currentPage != -1 and pageSize != -1 then

  var_firstResult:= (currentPage-1)*pageSize + 1;

  var_tempsql := 'select * from (' || var_sql || ') where myrow>=' || var_firstResult;

end if;

open cs_userinfo for var_tempsql;

return cs_userinfo;

end page_info;



--动态分级处理
procedure gradedeal(var_sql varchar2,currentPage number,pageSize number,var_usergrade varchar2)
is

var_firstResult number;


var_tempsql varchar2(2000);

begin

if currentPage != -1 and pageSize != -1 then

   var_firstResult := (currentPage-1)*pageSize + 1;

   var_tempsql :=  'update userinfo set usergrade =''' || var_usergrade || ''' where userid in (select userid from (' || var_sql || ') where myrow>=' || var_firstResult || ')';

end if;

execute immediate var_tempsql;
end gradedeal;




--定期检查会员级别
procedure checkmember
is

var_sql varchar2(1000);

var_tempsql varchar2(1000);

--定义一个检查会员的游标
type checkmember_cs is ref cursor;
cs_checkmember checkmember_cs;

--定义一个规则的游标
type rule_cs is ref cursor;
cs_rule rule_cs;

type fee_cs is ref cursor;
cs_fee fee_cs;


--定义一个记录集
type tp_intergral is Record
(
     userid       number,
     intergral    number,
     arpu         number,
     servetype    varchar2(100),
     usergrade    varchar2(100)
);

--定义一个存放不符合规则的用户编号数组
type emp_ssn_array is table of number  --NUMBER为所存数据类型
index by binary_integer;               --索引下标类型
fail_user emp_ssn_array;

--定义一个已根据积分分级的用户编号数组
type intergral_grade is table of number  --NUMBER为所存数据类型
index by binary_integer;               --索引下标类型
grade_intergral intergral_grade;


var_intergral tp_intergral;

rule_sql varchar2(1000);

var_rule score_rule%rowtype;

var_intergralmax number := 100000;

var_intergralmin number;

var_arpumax number := 100000;

var_arpumin number;

var_flog number;

var_fee number;

i number := 1;
j number := 1;

score number;
fee number;

begin

var_sql := 'select a.intergral,a.userid,b.usergrade,b.servetype from (';
var_sql := var_sql || 'select min(baseintegral-consumeintergral+onlineintergral+honorintergral+adjustintergral) as intergral,userid from intergralinfo';
var_sql := var_sql || ' where recordtime>=to_char(add_months(sysdate,-6),''yyyy-MM'') and recordtime<=to_char(add_months(sysdate,-1),''yyyy-MM'')';
var_sql := var_sql || ' and userid in (select userid from membercardinfo where substr(mdate,1,7) = to_char(add_months(sysdate,-12),''yyyy-MM'') and islock=0) group by userid';
var_sql := var_sql || ') a inner join userinfo b on a.userid= b.userid';


rule_sql := 'select * from score_rule where countgene=''消费积分''';
dbms_output.put_line(rule_sql);

open cs_checkmember for var_sql;
     loop
       fetch cs_checkmember into var_intergral.intergral,var_intergral.userid,var_intergral.usergrade,var_intergral.servetype;
       exit when cs_checkmember%Notfound;
         var_flog := 0;
            open cs_rule for rule_sql;
                 loop
                   fetch cs_rule into var_rule;
                   exit when cs_rule%Notfound;
                      if var_rule.numtwo is null then
                         var_intergralmax := 10000;
                      else
                         var_intergralmax := var_rule.numtwo;
                      end if;
                      var_intergralmin := var_rule.numone;
                      if var_intergral.intergral>=var_intergralmin and var_intergral.intergral<var_intergralmax and var_intergral.servetype=var_rule.rulebrand then
                         var_flog :=1;
                         if  var_intergral.usergrade<>var_rule.cardtype then
                           execute immediate 'update userinfo set usergrade='''|| var_rule.cardtype || ''' where userid=' || var_intergral.userid || '';
                           execute immediate 'update membercardinfo set usergrade='''|| var_rule.cardtype||''',mdate=to_char(sysdate,''yyyy-MM'')  where userid='|| var_intergral.userid ||'';
                         else
                           execute immediate 'update membercardinfo set mdate=to_char(sysdate,''yyyy-MM'') where userid='|| var_intergral.userid ||'';
                         end if;
                         grade_intergral(j) := var_intergral.userid;
                         j := j+1;
                      end if;
                end loop;
            close cs_rule;
       if var_flog = 0 then
          fail_user(i) := var_intergral.userid;
          i := i+1;
       end if;
     end loop;
close cs_checkmember;


var_sql :='select a.ARPU,a.userid,b.usergrade,b.servetype from(';
var_sql := var_sql || 'select avg(money) as ARPU,userid from phonefeeinfo where userid in';
var_sql := var_sql || ' (select userid from membercardinfo where substr(mdate,1,7) = to_char(add_months(sysdate,-12),''yyyy-MM'') and islock=0)';
var_sql := var_sql || ' and mdate>=to_char(add_months(sysdate,-6),''yyyy-MM'') and mdate<=to_char(add_months(sysdate,-1),''yyyy-MM'')';
var_sql := var_sql || ' group by userid) a inner join userinfo b on a.userid=b.userid';


rule_sql := 'select * from score_rule where countgene=''话费和ARPU''';
dbms_output.put_line(rule_sql);

open cs_checkmember for var_sql;
     loop
       fetch cs_checkmember into var_intergral.arpu,var_intergral.userid,var_intergral.usergrade,var_intergral.servetype;
       exit when cs_checkmember%Notfound;
           var_flog := 0;
            open cs_rule for rule_sql;
                 loop
                   fetch cs_rule into var_rule;
                   exit when cs_rule%Notfound;
                      if var_rule.numtwo is null then
                        var_intergralmax := 100000;
                      else
                        var_intergralmax := var_rule.numtwo;
                      end if;
                      var_intergralmin := var_rule.numone;
                      if var_rule.numfour is null then
                         var_arpumax := 100000;
                      else
                         var_arpumax := var_rule.numfour;
                      end if;
                      var_arpumin := var_rule.numthree;
                      if var_intergral.arpu>=var_arpumin  and var_intergral.arpu<var_arpumax and var_intergral.servetype=var_rule.rulebrand then
                         var_tempsql := 'select prepay from phonefeeinfo where userid=' || var_intergral.userid;
                         var_tempsql := var_tempsql || ' and mdate>=to_char(add_months(sysdate,-6),''yyyy-MM'') and mdate<=to_char(add_months(sysdate,-1),''yyyy-MM'')';
                         open cs_fee for var_tempsql;
                               loop
                               fetch cs_fee into var_fee;
                               exit when cs_fee%Notfound;
                                if var_fee>=var_intergralmin and var_fee<var_intergralmax then
                                   for i in 1..grade_intergral.count loop
                                       if grade_intergral(i) = var_intergral.userid then
                                          var_flog := 1;
                                          execute immediate 'select distinct countmodulus from score_rule where cardtype=''' || var_intergral.usergrade || '''' into score;
                                          execute immediate 'select distinct countmodulus from score_rule where cardtype=''' || var_rule.countmodulus || '''' into fee;
                                          if score < fee then
                                             execute immediate 'update userinfo set usergrade='''|| var_rule.cardtype || ''' where userid=' || var_intergral.userid || '';
                                             execute immediate 'update membercardinfo set usergrade='''|| var_rule.cardtype||''' where userid='|| var_intergral.userid ||'';
                                          end if;
                                          exit;