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;

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;

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;

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;

呼叫中心資料入庫指令碼

相關文章