ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 呼叫中心数据入库脚本

呼叫中心数据入库脚本

原创 Linux操作系统 作者:skuary 时间:2011-04-05 19:14:13 0 删除 编辑
CREATE OR REPLACE PROCEDURE "M_XA_WH_DAILY_COUNT" is
 cursor cur_28424 is
       select * from item_28424_sp@cm_xa 
       where conclusion in (61673,61680,61695) --61694 拒收
             AND (qc_first !=0 or qc_first is null)
             and done_flag<>7
             and substr(donetime,1,10) <=to_char(sysdate-2,'yyyy-mm-dd')
             and nvl(recycle_result,0)=0;
             
             
    rec_28424 cur_28424%rowtype;
              
    nMemberID number(10);
    vName varchar2(30);
    vAreaID number(10);
    vAddress varchar2(200);
    vState varchar2(10);
    vStatDate varchar2(20);
  
    nCount number(10);
    vMobile varchar2(20);
    vMemberName varchar2(255);
  
  --  sID number(10);
    v_name varchar2(255);
  --  v_areacode varchar2(64);
  --  nParentID  number(10);
  --  Cnt        number(10);
    v_passwd number(10);
    v_passwdid number(10);
    v_ccnum1 number(10);
    v_ccnum2 number(10);
    v_ccid number(10);
    v_id number(10);
    vregsource  varchar2(50);
    vcometime varchar2(20);
    begin
    --************************************************************************************************************************************
        select COUNT(*) INTO v_ccnum1  from item_28424_sp@cm_xa 
             where (qc_first !=0 or qc_first is null)
             and done_flag<>7
             and substr(donetime,1,10) <=to_char(sysdate-2,'yyyy-mm-dd')
             and nvl(recycle_result,0)=0;
  
  open cur_28424;
    loop
      fetch cur_28424 into rec_28424;
      exit when cur_28424%notfound;
  
      vStatDate:=substr(rec_28424.DoneTime,1,10);
  
      vState := '合格';
  
              ---名字规范
      v_name:='X';
  
      if jay_is_chinesename(regexp_replace(to_single_byte(replace(rec_28424.col_0,' ')),'\(.*\)')) =1 then
         v_name:=regexp_replace(to_single_byte(trim(rec_28424.col_0)),'\(.*\)');
      end if;
  
      if jay_is_chinesename(regexp_replace(to_single_byte(replace(rec_28424.col_8,' ')),'\(.*\)')) =1 then
         v_name:=regexp_replace(to_single_byte(trim(rec_28424.col_8)),'\(.*\)');
      end if;
  
  
      if v_name='X' then
        vState := '姓名不规范'; --vState := '拒收';
      end if;
      ---------------------------------------------------------------------------------------------------
  
      if rec_28424.Col_12 is null then --地址更新为空
        vState := '地址不规范';
      end if;
  
  
      if (rec_28424.Col_11 is null or lengthb(trim(rec_28424.Col_11))<>6 or substr(trim(rec_28424.Col_11),-4)='0000' or length(trim(rec_28424.Col_11))!=6) then  --邮编更新为空
        vState := '邮编不规范';
      end if;
  
     /*if (fun_IsNumber(rec_28424.col_11)!=1 or length(rec_28424.col_11)>12) AND rec_28424.col_11 IS NOT NULL then
       vState := '手机不规范'; 
     end if;*/     
     /*if (fun_IsNumber(rec_28424.Col_10)!=1 or length(rec_28424.Col_10)>12 or length(rec_28424.Col_10)<11) AND rec_28424.Col_10 IS NOT NULL then
       vState := '手机不规范';
     end if; */  
        if rec_28424.Col_2 is null and rec_28424.Col_10 is null then
     vState := '手机不规范';
     end if;
     if  instr(rec_28424.Col_10,'*')>0 or (length(trim(rec_28424.Col_10))>0 and fun_IsNumber(rec_28424.col_10)!=1) or rec_28424.col_10 is null or length(trim(rec_28424.Col_10))<>11 then
         if instr(rec_28424.Col_2,'*')>0 or (length(trim(rec_28424.Col_2))>0 and fun_IsNumber(rec_28424.col_2)!=1) or rec_28424.col_2 is null or length(trim(rec_28424.Col_2))<>11 then
         vState := '手机不规范';
         else 
         vmobile := rec_28424.Col_2;
         end if;
     --elsif instr(rec_28424.Col_2,'*')>0 or (length(trim(rec_28424.Col_2))>0 and fun_IsNumber(rec_28424.col_2)!=1) then 
    -- vState := '手机不规范';
     else vmobile := rec_28424.Col_10;
     end if;
     
    
   --     if length(trim(rec_28424.Col_105)) >64 then
   --     vState := 'email不规范';
   --   end if; 
      
      vAddress := to_single_byte(trim(rec_28424.Col_12));
  
      --如果地址止于数字,则补上“室”
      if substr(vAddress,length(vAddress),1) in ('0','1','2','3','4','5','6','7','8','9') then
        vAddress := vAddress || '室';
      end if;
  
      --地址准确性
     /* if (instr(vAddress,'(地址可收)')=0 or instr(vAddress,'(地址可收)')=0 or instr(vAddress,'(地址可收)')=0 or instr(vAddress,'(地址可收)')=0) then
        if rec_28424.Col_15='公司地址' then
          if GetDigitalCount(vAddress)=0 or (substr(vAddress,length(vAddress))='号' or substr(vAddress,length(vAddress))='楼') then
            vState := '地址不规范';
          end if;
        else                     --家庭地址
          if GetDigitalCount(vAddress)<2 or substr(vAddress,length(vAddress),1) not in ('室','座') then
            vState := '地址不规范';
          end if;
        end if;
      else
        vAddress := replace(vAddress,'地址可收','');
      end if;*/
      
       vAddress := replace(vAddress,'地址可收','');
       
      if instr(vaddress,')')>0 then 
      vaddress := replace(vaddress,')','');
      end if;
      
      if instr(vaddress,'(')>0 then 
      vaddress := replace(vaddress,'(','');
      end if;
      
      if instr(vaddress,'(')>0 then 
      vaddress := replace(vaddress,'(','');
      end if;
      
      if instr(vaddress,')')>0 then 
      vaddress := replace(vaddress,')','');
      end if; 
      
  
      --匹配邮编(外地数据不匹配邮编)
  
  
      if vState='合格' and v_name!='X' then
        select Name
          into vName
          from userinfo@cm_xa
         where id=rec_28424.oper_site;
  
        --武汉
        select count(*)
          into nMemberID
          from dic_area
         where parentid=2954 and substr(areaname,1,2)=substr(rec_28424.col_16,1,2) and cancelflag=0;
  
        if nMemberID>0 then
          select AreaID
            into vAreaID
            from dic_area
           where parentid=2954 and substr(areaname,1,2)=substr(rec_28424.col_16,1,2) and cancelflag=0;
        else
          vAreaID := null;
        end if;
  
        --姓名、手机号整理
          vMemberName := v_name;
  
       /* if rec_28424.Col_10 is null or instr(rec_28424.Col_10,'*')>0 or
        (fun_IsNumber(rec_28424.Col_10)=1  and length(rec_28424.Col_10)<>11 and length(rec_28424.Col_10)<>12 and length(rec_28424.Col_10)<20) then
          if rec_28424.col_11 is null or instr(rec_28424.col_11,'*')>0 or
        (fun_IsNumber(rec_28424.col_2)=1  and length(rec_28424.col_2)<>11 and length(rec_28424.col_2)<>12 and length(rec_28424.col_2)<20) then
         if substr(rec_28424.col_2,1,1)='0' then
         vMobile := substr(rec_28424.Col_2,2);
          else
            vMobile := rec_28424.col_2;
          end if;
          end if;
        else
          if substr(rec_28424.Col_10,1,1)='0' then
            vMobile := substr(rec_28424.Col_10,2);
          else
            vMobile := rec_28424.Col_10;
          end if;
        end if;*/
        
     --   vmobile := nvl(rec_28424.Col_10,rec_28424.Col_2);
         vmobile := substr(vmobile,-11);
        --end of姓名、手机号整理
  
        select count(*)
          into nMemberID
          from Memberinfo@yesmynet
         where LogID=vMobile or Mobile=vMobile ;
       --     or email=(case when instr(replace(trim(rec_28424.Col_105),'无'),'@') > 0 then replace(trim(rec_28424.Col_105),'无') else null end)
          --  or name||address=v_name||vAddress;
  
        if nMemberID=0 then
  
          select seq_m_member_account_id.nextval@yesmynet
            into nMemberID
            from dual;
            
           /* if instr(vaddress,rec_28424.col_10)>0 and instr(vaddress,rec_28424.col_9)>0 then 
              vaddress := vaddress;
              end if;
            if instr(vaddress,rec_28424.col_10)>0 and instr(vaddress,rec_28424.col_9)<0 then 
              vaddress := rec_28424.col_9||vaddress;
              end if;
            if instr(vaddress,rec_28424.col_10)<0 and instr(vaddress,rec_28424.col_9)<0 then 
             if  instr(vaddress,rec_28424.col_16)>0 then  
             vaddress := rec_28424.col_9||rec_28424.col_10||vaddress;
             else vaddress := rec_28424.col_9||rec_28424.col_10||rec_28424.col_16||vaddress;
             end if;
             end if;
            if instr(vaddress,rec_28424.col_10)<0 and instr(vaddress,rec_28424.col_9)>0 then 
              vaddress := rec_28424.col_9||rec_28424.col_10||substr(vaddress,4);
            else vaddress := vaddress;
            end if;*/
            vaddress := '湖北省武汉市'||rec_28424.col_16||vaddress;
            
         -- end if;
            
  
         /* if substr(vAddress,1,length(rec_28424.col_16))=rec_28424.col_16 then
            vAddress := substr(vAddress,length(rec_28424.Col_16)+1);
          end if;
  
          if substr(rec_28424.Col_11,length(rec_28424.Col_11),1)='市' then
            vAddress := '福建省'||rec_28424.Col_11||vAddress;
          else
            vAddress := '福建省厦门市'||rec_28424.col_16||vAddress;
          end if;  */
          
    SELECT trunc(dbms_random.value(100000,999999)) INTO v_passwd FROM dual ;
  
                                 
          insert into memberinfo@yesmynet(MemberID,LogID,LogPassword,MemberTypeID,name,sex,address,postcode,
                                 Mobile,RegSourceID,ComeSource,ComeAgent,ComeAgentNo,
                                 SubmitDate,AreaID,
                                 AddressType,AreaCode,come_time,goodsline,exchange_point,total_point,cancelflag,rank_id)
              values(nMemberID,vMobile,v_passwd,'MEMBER_TYPE_PERSONAL',vMemberName,decode(nvl(rec_28424.col_9,rec_28424.col_1),'女','f','男','m'),vAddress,trim(rec_28424.Col_11),
                     vMobile,'MEMBER_REG_SOURCE_OB','西安',vName,rec_28424.oper_site,
                     sysdate,vAreaID,substrb(rec_28424.Col_15,1,10),
                     '027',to_date(rec_28424.DoneTime,'yyyy-mm-dd hh24:mi:ss'),'GOODS_TYPE_WINE',100,100,0,'MEMBER_LEVEL_NORMAL');
  
  
   SELECT max(id)+1  INTO v_passwdid from m_user_password_notify;
           INSERT INTO m_user_password_notify(user_id,mobile,TRUE_NAME,PASSWORD,SUBMIT_TIME,ID)  
           VALUES (nMemberID,vMobile,vMemberName,v_passwd,SYSDATE,v_passwdid);
           COMMIT;      
             
  
          --2009增加积分表信息
          insert into m_Score_History@yesmynet(score_his_id,member_id,cha_reason,cha_score,
                                            total_score,exchange_score,is_gain,version,CHA_DATE)
        select seq_m_score_history_id.nextval@yesmynet,nMemberID,'SCORE_CHANGE_REASON_REG',100,
               100,100,1,0,sysdate from dual;
  
           /*execute immediate 'select SEQ_MW_RANK_HISTORY_ID.nextval from dual' into sID;
  
          insert into MW_RANK_HISTORY(ID,USER_ID,RANK_ID,RANK_TYPE,RANK_REASON,TOTAL_POINT,
                                      EXCHANGE_POINT,RANK_POINT)
             values(sID,nMemberID,0,1,'注册',100,
                    100,100);*/
          --end of 2009增加积分表信息
  
          --插入记录表
          insert into REDWINE_TOMATO_TAB values (rec_28424.cid,rec_28424.oper_site,rec_28424.donetime,vMobile,'西安','武汉',sysdate,28424);
  
          update item_28424_sp@cm_xa
             set done_flag=7,recycle_result=1,recycle_date=sysdate
           where cid=rec_28424.cid;
         commit; 
           else
  
  -------------------如果重复需要看重复日期
           select count(*)
          into  nCount
          from memberinfo@yesmynet
         where (LogID=vMobile or Mobile=vMobile 
        --    or email=(case when instr(replace(trim(rec_28424.Col_105),'无'),'@') > 0 then replace(trim(rec_28424.Col_105),'无') else null end)
            or name||address=v_name||vAddress) and to_char(submitdate,'yyyy-mm-dd')>=substr(rec_28424.DoneTime,1,10);
            
         
  --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
          if nCount>=1 then        --挖掘成功后用户自行注册
  
       /* if substr(vAddress,1,length(rec_28424.Col_100))=rec_28424.Col_100 then
            vAddress := substr(vAddress,length(rec_28424.Col_100)+1);
          end if;
  
          vAddress := '北京市'||rec_28424.Col_100||vAddress;
  update memberinfo@yesmynet
               set name=nvl(name,vMemberName),
                   sex=nvl(sex,decode(nvl(rec_28424.col_18,rec_28424.col_1),'女',0,'男',1)),
                   address=nvl(address,vAddress),
                   PostCode=nvl(postcode,trim(rec_28424.Col_11)),
                   --Mobile=nvl(mobile,vMobile),
                   email=nvl(email,(case when instr(replace(trim(rec_28424.Col_105),'无'),'@') > 0 then replace(trim(rec_28424.Col_105),'无') else null end)),
                   ComeAgent=vName,
                   ComeAgentNo=rec_28424.oper_site,
                   ComeCallCenterID=2,
                   addresstype=nvl(addresstype,substrb(rec_28424.Col_19,1,10)),
                   Areacode=nvl(areacode, '010')
            where LogID=vMobile or Mobile=vMobile 
            or email=(case when instr(replace(trim(rec_28424.Col_105),'无'),'@') > 0 then replace(trim(rec_28424.Col_105),'无') else null end)
            or name||address=v_name||vAddress;
            COMMIT;*/
            
             insert into REDWINE_TOMATO_TAB values (rec_28424.cid,rec_28424.oper_site,rec_28424.donetime,vMobile,'西安','武汉',sysdate,28424);
  
          update item_28424_sp@cm_xa
             set done_flag=7,recycle_result=1,recycle_date=sysdate
           where cid=rec_28424.cid;
           COMMIT;
           
            
          else --拨打前注册,重复,拒收   ID 61694
            update item_28424_sp@cm_xa
               set done_flag=7,conclusion=61694,recycle_reason='拨打前注册'
            where cid=rec_28424.cid;  --设定座席不可见               
          end if;
        END IF ;
      else
  
  INSERT INTO item_28424_problem VALUES (rec_28424.cid,vMobile,to_date(rec_28424.donetime,'yyyy-mm-dd hh24:mi:ss'));
  
        --拒收ID: 61694
        update item_28424_sp@cm_xa
           set conclusion=61694,recycle_reason=vState
         where cid=rec_28424.cid;
      end if;
  
      commit;
    end loop;
    close cur_28424;
    
 /*   select COUNT(*) INTO v_ccnum2  from item_28424_sp@cm_xa a,projectresultinfo@cm_xa b
           where conclusion in (59605,59612,59627) --59626
             AND (qc_first !=0 or qc_first is null)
             and done_flag=7
             and substr(donetime,1,10) <=to_char(sysdate-2,'yyyy-mm-dd')
             and nvl(recycle_result,0)=1
             and to_char(recycle_date,'yyyymmdd')=to_char(SYSDATE,'yyyymmdd');
             
    SELECT SEQ_mw_accept_cc_data_ID.nextval INTO   v_ccid FROM dual;
    SELECT COUNT(*) INTO v_id FROM mw_accept_cc_data t WHERE to_char(t.ACCEPT_TIME,'yyyymmdd')=to_char(SYSDATE,'yyyymmdd') AND t.CC_PROJECT_ID=28424;
    IF v_id =0 THEN
    INSERT INTO mw_accept_cc_data VALUES(v_ccid,2,28424,v_ccnum1,v_ccnum2,SYSDATE);
    COMMIT;           
    ELSE 
    UPDATE  mw_accept_cc_data t SET t.SUCCESS_NUM=v_ccnum1,accept_num=v_ccnum2,accept_time=SYSDATE WHERE to_char(t.ACCEPT_TIME,'yyyymmdd')=to_char(SYSDATE,'yyyymmdd') AND t.CC_PROJECT_ID=28424;
    COMMIT;
    END IF;*/
    end;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-714084/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 一些有用的函数
下一篇: oracle备份脚本
请登录后发表评论 登录
全部评论

注册时间:2011-03-31

  • 博文量
    88
  • 访问量
    317173