Example Analysis of call Center data Storage script
This article mainly introduces the example analysis of the call center data storage script, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.
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 rejected
AND (qc_first! = 0 or qc_first is null)
And done_flag7
And substr (donetime,1,10) 12 or length (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: = 'mobile phone is not standard'
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: = 'Mobile phone is not standard'
Else vmobile: = rec_28424.Col_10
End if
-- if length (trim (rec_28424.Col_105)) > 64 then
-- vState: = 'email is not standard'
-- end if
VAddress: = to_single_byte (trim (rec_28424.Col_12))
If the address ends with a number, fill in the "room".
If substr (vAddress,length (vAddress), 1) in (then)
VAddress: = vAddress | | 'Room'
End if
-- address accuracy
/ * if (instr (vAddress,')') = 0 or instr (vAddress,')') = 0) then
If rec_28424.Col_15=' company address' then
If GetDigitalCount (vAddress) = 0 or (substr (vAddress,length (vAddress)) ='no.'or substr (vAddress,length (vAddress)) = 'building') then
VState: = 'address is not standard'
End if
Else-Home address
If GetDigitalCount (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
-- match the zip code (non-local data do not match the zip code)
If vState=' qualified 'and vault nameplate' qualified X' then
Select Name
Into vName
From userinfo@cm_xa
Where id=rec_28424.oper_site
-- Wuhan
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
-- sorting out names and mobile phone numbers
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) 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) 0 then replace (trim (rec_28424.Col_105), 'none') 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) = substr (rec_28424.DoneTime,1,10)
-- +
If nCount > = 1 then-users register after successful mining
/ * 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: = 'Beijing' | | 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), 'female', 0 'male', 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), 'none'),'@') > 0 then replace (trim (rec_28424.Col_105), 'none') 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), 'none'),'@') > 0 then replace (trim (rec_28424.Col_105), 'none') 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,' Xi'an', 'Wuhan', 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-register before dialing, repeat, reject ID 61694
Update item_28424_sp@cm_xa
Register before set done_flag=7,conclusion=61694,recycle_reason=' dialing'
Where cid=rec_28424.cid;-sets the agent invisible
End if
END IF
Else
INSERT INTO item_28424_problem VALUES (rec_28424.cid,vMobile,to_date (rec_28424.donetime,'yyyy-mm-dd hh34:mi:ss'))
-- reject 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 meme projectresultinfoothcmroomxa b
Where conclusion in (59605, 59612)-- 59626
AND (qc_first! = 0 or qc_first is null)
And done_flag=7
And substr (donetime,1,10)
