create or replace procedure up_mid_66880002(v_rq number, –计算日期
rc1 out bssys.tp_cursor,
rc2 out bssys.tp_cursor)
as
nb_rq number;
nb_ExecTime date;
check_rq date;
nb_rows number(10);
v_status int;
v_msg varchar2(200);
begin
nb_ExecTime := sysdate;
–当处理日期为空时，取系统日期作为处理日期
if v_rq is null then
nb_rq := to_number(to_char(sysdate, ‘yyyymmdd’));
else
nb_rq := v_rq;
end if;
–校验日期格式
begin
select to_date(nb_rq, ‘yyyymmdd’) into check_rq from dual;
exception
when others then
v_status := -2;
v_msg := ‘日期格式错误’;
open rc1 for
select v_status as errorcode, v_msg as errormsg from dual;
return;
end;
–根据员工角色、员工角色分配查询出所有“工作平台坐席人员”、“质检人员”
–初始化坐席统计汇总表数据
/*merge into t_zxtjhz a
using(
select t2.ygbh, decode(t3.jsbh,4,1,30,1,409,2,1) as mtlx
from t_yg t1, t_yg_ygjsfp t2, t_yg_js t3
where t1.ygbh = t2.ygbh and t2.jsbh = t3.jsbh and t1.ygzt = 1
and t2.zt = 1 and (t3.jsbh = 4 or t3.jsbh = 40)
) b
on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)
when matched then update set
zxsc = 0,hrcs = 0,hrsc = 0,pjhrsc = 0,hccs = 0,hcsc = 0,pjhcsc = 0,fwcs = 0,fwsc = 0,
pjfwsc = 0,zjs = 0,zjsc = 0,fds = 0,pxs = 0,pjdf = 0,sscgs = 0,cjbhg = 0,bzjs = 0,bzjpjf = 0
when not matched then insert(a.rq, a.ygbh, a.mtlx) values(nb_rq, b.ygbh, b.mtlx);
nb_rows := sql%rowcount ;
commit;
p_cc_log(nb_rq, ‘up_mid_66880002’, nb_ExecTime, sysdate, ‘0’, nb_rows,’0-初始化计算员工成功!’);*/
–1.计算在线时长(只统计了1：话务，2：全媒体,91：UC签出，21%：话务签出)
nb_ExecTime := sysdate;
merge into t_zxtjhz a
using (select t1.ygbh, sum(t1.hs) zxsc, 1 as mtlx
from t_hwczlsk t1
where t1.rq = nb_rq
and t1.czlx like ‘21%’
and t1.sfcg = 1
group by t1.ygbh
union all
select t1.ygbh, sum(t1.hs) zxsc, 2 as mtlx
from t_hwczlsk t1
where t1.rq = nb_rq
and t1.czlx = 91
and t1.sfcg = 1
group by t1.ygbh) b
on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)
when matched then
update set a.zxsc = b.zxsc
when not matched then
insert
(a.rq, a.ygbh, a.mtlx, a.zxsc)
values
(nb_rq, b.ygbh, b.mtlx, b.zxsc);
nb_rows := sql%rowcount;
commit;
p_cc_log(nb_rq,
‘up_mid_66880002’,
nb_ExecTime,
sysdate,
‘0’,
nb_rows,
‘1-计算在线时长成功!’);
–2.计算呼入次数,时长,平均呼入时长
nb_ExecTime := sysdate;
merge into t_zxtjhz a
using (select t1.ygbh,
count(t1.lsh) hrcs,
sum(t1.thsc) hrsc,
round(avg(t1.thsc), 2) pjhrsc,
1 mtlx
from t_zxthlsk t1
where t1.rq = nb_rq
and mtlx = 0
and t1.hjlx = 0
group by t1.ygbh) b
on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)
when matched then
update set a.hrcs = b.hrcs, a.hrsc = b.hrsc, a.pjhrsc = b.pjhrsc
when not matched then
insert
(a.rq, a.ygbh, a.mtlx, a.hrcs, a.hrsc, a.pjhrsc)
values
(nb_rq, b.ygbh, b.mtlx, b.hrcs, b.hrsc, b.pjhrsc);
nb_rows := sql%rowcount;
commit;
p_cc_log(nb_rq,
‘up_mid_66880002’,
nb_ExecTime,
sysdate,
‘0’,
nb_rows,
‘2.计算呼入次数、时长、平均呼入时长成功!’);
–3.计算呼出次数,时长,平均呼出时长
nb_ExecTime := sysdate;
merge into t_zxtjhz a
using (select t1.ygbh,
count(t1.lsh) hccs,
sum(t1.thsc) hcsc,
avg(t1.thsc) pjhcsc,
1 mtlx
from t_zxthlsk t1
where t1.rq = nb_rq
and mtlx = 0
and t1.hjlx <> 0
group by t1.ygbh) b
on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)
when matched then
update set a.hccs = b.hccs, a.hcsc = b.hcsc, a.pjhcsc = b.pjhcsc
when not matched then
insert
(a.rq, a.ygbh, a.mtlx, a.hccs, a.hcsc, a.pjhcsc)
values
(nb_rq, b.ygbh, b.mtlx, b.hccs, b.hcsc, b.pjhcsc);
nb_rows := sql%rowcount;
commit;
p_cc_log(nb_rq,
‘up_mid_66880002’,
nb_ExecTime,
sysdate,
‘0’,
nb_rows,
‘3.计算呼出次数、时长、平均呼出时长成功!’);
–4.计算服务次数,服务时长,平均服务时长
nb_ExecTime := sysdate;
merge into t_zxtjhz a
using (select t1.ygbh,
count(t1.lsh) fwcs,
sum(t1.thsc) fwsc,
round(avg(t1.thsc), 2) pjfwsc,
2 mtlx
from t_zxthlsk t1
where t1.rq = nb_rq
and mtlx != 0 –全媒体
group by t1.ygbh) b
on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)
when matched then
update set a.fwcs = b.fwcs, a.fwsc = b.fwsc, a.pjfwsc = b.pjfwsc
when not matched then
insert
(a.rq, a.ygbh, a.mtlx, a.fwcs, a.fwsc, a.pjfwsc)
values
(nb_rq, b.ygbh, b.mtlx, b.fwcs, b.fwsc, b.pjfwsc);
nb_rows := sql%rowcount;
commit;
p_cc_log(nb_rq,
‘up_mid_66880002’,
nb_ExecTime,
sysdate,
‘0’,
nb_rows,
‘4.计算服务次数,服务时长,平均服务时长成功!’);
–5.计算质检个数,质检录音时长,辅导座席数,培训数,平均得分,申诉成功数,抽检不合格数
nb_ExecTime := sysdate;
merge into t_zxtjhz a
using (select t1.zjyg as ygbh,
count(t1.lsh) zjs,
sum(tt.zjsc) zjsc,
sum(fd.fds) fds,
count(px.zjlsh) pxs,
avg(t1.zjzpf) pjdf,
count(zjss.zjlsh) sscgs,
(select count(rwk.lsh) cjbhg
from t_zj_rwk yy
inner join (select a.*
from t_zj_rwk a, t_zj_djk c
where a.bzbh = c.bzbh
and c.djmc = ‘不合格’
and a.zjzpf <= c.djzgf) rwk
on yy.swlsh = rwk.swlsh
where yy.zjrq = nb_rq
and yy.sfpf != 0
group by yy.zjyg) cjbhg
,
4 as mtlx
from t_zj_rwk t1
inner join (select t1.swlsh as swlsh, sum(t2.thsc) zjsc
from t_zj_rwk t1, t_zxthlsk t2
where t1.swlsh = t2.order_id
and t1.dxbh in (1, 5)
and t1.zjrq = nb_rq
and t1.sfpf != 0
group by t1.swlsh
union all –呼出
select t1.swlsh as swlsh, nvl(sum(t4.thsc), 0) zjsc
from t_zj_rwk t1,
t_hcgl_khmddy t2,
t_hcgl_hcxw t3,
t_zxthlsk t4
where t1.swlsh = t2.id
and t1.swlsh = t3.rwid(+)
and t3.thlsh = t4.lsh(+)
and t1.zjrq = nb_rq
and t1.sfpf != 0
and t1.dxbh = 2
group by t1.swlsh) tt
on t1.swlsh = tt.swlsh
left join (select k.fqrbh, k.swlsh, count(k.jsrbh) fds
from t_xxtsk k
where xxdx = 210
and scrq = nb_rq
group by k.fqrbh, k.swlsh) fd
on t1.zjyg = fd.fqrbh
and t1.lsh = fd.swlsh
left join t_gzl_px px
on t1.lsh = px.zjlsh
left join (select a.zjlsh
from t_gzl_zjss a
where exists
(select 1
from (select t1.processkey,
t1.processinstanceid,
t1.doresult
from t_nodetask_handle t1
where rowid =
(select max(rowid)
from t_nodetask_handle t2
where t1.processkey =
t2.processkey
and t1.processinstanceid =
t2.processinstanceid)) b
where a.process_key = b.processkey
and a.processinstanceid = b.processinstanceid
and a.process_key = ‘qualityinspectionflow’
and b.doresult = ‘0’ –质检申诉通过
)) zjss
on t1.lsh = zjss.zjlsh
where t1.zjrq = nb_rq
and t1.sfpf != 0
group by t1.zjyg) b
on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)
when matched then
update
set a.zjs = b.zjs,
a.zjsc = b.zjsc,
a.fds = b.fds,
a.pxs = b.pxs,
a.pjdf = b.pjdf,
a.sscgs = b.sscgs,
a.cjbhg = b.cjbhg
when not matched then
insert
(a.rq,
a.ygbh,
a.mtlx,
a.zjsc,
a.fds,
a.pxs,
a.pjdf,
a.sscgs,
a.cjbhg)
values
(nb_rq,
b.ygbh,
b.mtlx,
b.zjsc,
b.fds,
b.pxs,
b.pjdf,
b.sscgs,
b.cjbhg);
nb_rows := sql%rowcount;
commit;
p_cc_log(nb_rq,
‘up_mid_66880002’,
nb_ExecTime,
sysdate,
‘0’,
nb_rows,
‘5.计算质检个数、质检录音时长、辅导座席数!’);
–6.计算坐席被质检通话数、质检平均分
nb_ExecTime := sysdate;
merge into t_zxtjhz a
using (
–呼入被质检
select t4.bzjyg ygbh, count(t4.swlsh) bzjs, avg(t4.zjzpf) bzjpjf
from t_cc_order t2, t_zxthlsk t3, t_zj_rwk t4
where t2.order_id = t3.order_id
and t2.order_id = t4.swlsh
and t3.rq = nb_rq
and t4.sfpf != 0
group by t4.bzjyg
union all
–呼出被质检
select a3.bzjyg ygbh, count(a3.swlsh) bzjs, avg(a3.zjzpf) bzjpjf
from t_hcgl_khmddy a1, t_hcgl_hcxw a2, t_zj_rwk a3, t_zxthlsk a4
where a1.id = a2.rwid
and a1.id = a3.swlsh
and a2.thlsh = a4.lsh
and a4.rq = nb_rq
and a3.sfpf != 0
group by a3.bzjyg) b
on (a.rq = nb_rq and a.ygbh = b.ygbh)
when matched then
update set a.bzjs = b.bzjs, a.bzjpjf = b.bzjpjf;
nb_rows := sql%rowcount;
commit;
p_cc_log(nb_rq,
‘up_mid_66880002’,
nb_ExecTime,
sysdate,
‘0’,
nb_rows,
‘5.计算质检个数、质检录音时长、辅导座席数!’);
v_status := 0;
v_msg := ‘success’;
open rc1 for
select v_status as errorcode, v_msg as errormsg from dual;
open rc2 for
select nvl(v_rq, to_number(to_char(sysdate, ‘yyyymmdd’))) as dealdate
from dual;
exception
when others then
v_status := Sqlcode;
v_msg := Sqlerrm;
p_cc_log(nb_rq,
‘up_mid_66880002’,
nb_ExecTime,
sysdate,
‘1’,
nb_rows,
‘坐席指标计算失败:’ || to_char(Sqlcode) || ‘;’ || Sqlerrm);
open rc1 for
select v_status as errorcode, v_msg as errormsg from dual;
open rc2 for
select nvl(v_rq, to_number(to_char(sysdate, ‘yyyymmdd’))) as dealdate
from dual;
end up_mid_66880002;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/37093.html
