CREATE OR REPLACE PROCEDURE RPT_SVR_USR_STAT_REPAIRMONTH(
I_TIMETYPE in VARCHAR2, --时间类型
I_STARTTIME IN VARCHAR2, --开始时间
I_ENDTIME IN VARCHAR2, --结束时间
I_UNITTREECODE IN VARCHAR2, --施工单位/岗/人
I_SPECIALTY IN VARCHAR2, --专业类型(多个时用';'号隔开)
I_SUBCLASS IN VARCHAR2, --用户等级
I_STATTYPE IN VARCHAR2 DEFAULT 'UNIT', --统计类型
p_cursor out CIDA_DT.CurType
)
is
t_dBeginDate DATE;
t_dEndDate DATE;
T_RANDOMCODE NUMBER;
v_length int;
i_index int;
v_treecode varchar2(512);
T_ERROR VARCHAR2(2000);
BEGIN
i_index := instr(I_UNITTREECODE,',');
if i_index>0 then
v_length := i_index-1;
else
v_length := length(I_UNITTREECODE);
end if;
v_treecode := ','||I_UNITTREECODE||',';
if I_STARTTIME is null then
t_dBeginDate:=sysdate;
t_dEndDate:=sysdate;
else
t_dBeginDate := TO_DATE(I_STARTTIME || ' 00:00:00','YYYY-MM-DD HH24:MI:SS');
t_dEndDate := TO_DATE(I_ENDTIME || ' 23:59:59','YYYY-MM-DD HH24:MI:SS');
end if;
RANDOM_NUM(T_RANDOMCODE);
begin
if i_index<1 then --单选
insert into rpt_svr_usr_tab_repairmonth
(RANDOMCODE ,
unitid ,
USERNUM ,
RECEPTNUM ,
CLOGNUM ,
RECLOGNUM ,
HASTENNUM ,
DOOROUTNUM ,
TOTALDURATIONNUM,
assessdurationum,
AVEREPAIRTIME ,
avgassrepairtime,
MAXREPAIRTIME ,
REPAIRNUM4 ,
REPAIRNUM6 ,
REPAIRNUM8 ,
REPAIRNUM24 ,
REPAIRNUM48 ,
REPAIRNUM482 ,
betclognum ,
BOOKNUM
)
select
T_RANDOMCODE,
decode(i_stattype,'OPER',repairopercode,substr(repairopercode,1,v_length+4)),
NULL as USERNUM,
count(1) , --申告数
SUM(CASE
WHEN b.DISPATCHTIME IS NOT NULL THEN
1
ELSE
0
END), --障碍申告量
SUM(CASE
WHEN b.REPCLOGNUM > 0 AND b.DISPATCHTIME IS NOT NULL THEN
1
ELSE
0
END), --重复障碍数
sum(case
when b.hastenflag>0 then 1
else
0
end), --催修
SUM(TO_NUMBER(ISDOOR)), --户闭
sum(b.totalduration), --总历时
sum(assessduration), --考核总历时
trunc(sum(b.totalduration)/count(1)/60,2), -- 平均修复时长
trunc(sum(b.ASSESSDURATION)/count(1)/60,2), --平均考核时长
trunc(max(totalduration)/60,2), --最长修复时长
SUM(CASE
WHEN ASSESSDURATION <= 240 THEN
1
ELSE
0
END), --4小时内修复量
SUM(CASE
WHEN ASSESSDURATION <= 360 THEN
1
ELSE
0
END), --6小时内修复量
SUM(CASE
WHEN (ASSESSDURATION > 360 AND
ASSESSDURATION <= 720) THEN
1
ELSE
0
END), --6-12小时内修复量
SUM(CASE
WHEN (ASSESSDURATION > 720 AND
ASSESSDURATION <= 1440) THEN
1
ELSE
0
END), --12-24小时内修复量
SUM(CASE
WHEN (ASSESSDURATION > 1440 AND
ASSESSDURATION <= 2880) THEN
1
ELSE
0
END), --24-48小时内修复量
SUM(CASE
WHEN ASSESSDURATION > 2880 THEN
1
ELSE
0
END), --48小时以上修复量
SUM(CASE
WHEN ISTIMEOUT = '0' THEN
1
ELSE
0
END), --及时处理工单总数
sum(case
when not getstrforsymbol(b.remark9,'|',1) is null then
1
else
0
end) --预约工单数
from rpt_svr_usr_base b
where --substr(REPAIROPERCODE,0,v_length+4)=ot.treecode
(I_SUBCLASS is null or I_SUBCLASS like '%'||nvl(b.subclass,9)||'%')
and (I_SPECIALTY is null or I_SPECIALTY like '%'||b.specialtycode||'%')
--and v_treecode like '%,'||substr(REPAIROPERCODE,0,v_length)||',%'
and b.repairopercode like I_UNITTREECODE||'%'
and not REPAIROPERCODE is null --派障
and DECODE(I_TIMETYPE,'RECEPTTIME',FIRSTRECEPTTIME,'ARCHIVETIME',archivetime,reverttime) between t_dBeginDate and t_dEndDate
group by decode(i_stattype,'OPER',repairopercode,substr(repairopercode,1,v_length+4));
else --多选
--得到施工单位
rpt_usr_result_area(T_RANDOMCODE,I_UNITTREECODE,i_stattype);
insert into rpt_svr_usr_tab_repairmonth
(RANDOMCODE ,
unitid ,
USERNUM ,
RECEPTNUM ,
CLOGNUM ,
RECLOGNUM ,
HASTENNUM ,
DOOROUTNUM ,
TOTALDURATIONNUM,
assessdurationum,
AVEREPAIRTIME ,
avgassrepairtime,
MAXREPAIRTIME ,
REPAIRNUM4 ,
REPAIRNUM6 ,
REPAIRNUM8 ,
REPAIRNUM24 ,
REPAIRNUM48 ,
REPAIRNUM482 ,
betclognum ,
booknum
)
select
T_RANDOMCODE,
decode(I_STATTYPE,'OPER',REPAIROPERCODE,OT.AREAID),
NULL as USERNUM,
count(1) , --申告数
SUM(CASE
WHEN b.DISPATCHTIME IS NOT NULL THEN
1
ELSE
0
END), --障碍申告量
SUM(CASE
WHEN b.REPCLOGNUM > 0 AND b.DISPATCHTIME IS NOT NULL THEN
1
ELSE
0
END), --重复障碍数
sum(case
when b.hastenflag>0 then 1
else
0
end), --催修
SUM(TO_NUMBER(ISDOOR)), --户闭
sum(b.totalduration), --总历时
sum(b.assessduration), --考核总历时
trunc(sum(b.totalduration)/count(1)/60,2), -- 平均修复时长
trunc(sum(b.ASSESSDURATION)/count(1)/60,2), --平均考核时长
trunc(max(totalduration)/60,2), --最长修复时长
SUM(CASE
WHEN ASSESSDURATION <= 240 THEN
1
ELSE
0
END), --4小时内修复量
SUM(CASE
WHEN ASSESSDURATION <= 360 THEN
1
ELSE
0
END), --6小时内修复量
SUM(CASE
WHEN (ASSESSDURATION > 360 AND
ASSESSDURATION <= 720) THEN
1
ELSE
0
END), --6-12小时内修复量
SUM(CASE
WHEN (ASSESSDURATION > 720 AND
ASSESSDURATION <= 1440) THEN
1
ELSE
0
END), --12-24小时内修复量
SUM(CASE
WHEN (ASSESSDURATION > 1440 AND
ASSESSDURATION <= 2880) THEN
1
ELSE
0
END), --24-48小时内修复量
SUM(CASE
WHEN ASSESSDURATION > 2880 THEN
1
ELSE
0
END), --48小时以上修复量
SUM(CASE
WHEN ISTIMEOUT = '0' THEN
1
ELSE
0
END), --及时处理工单总数
sum(case
when not getstrforsymbol(b.remark9,'|',1) is null then
1
else
0
end) --预约工单数
from rpt_svr_usr_base b ,rpt_temp_org_tree ot
where b.repairopercode=ot.treecode
and (I_SUBCLASS is null or I_SUBCLASS like '%'||nvl(b.subclass,9)||'%')
and (I_SPECIALTY is null or I_SPECIALTY like '%'||b.specialtycode||'%')
--and v_treecode like '%,'||substr(REPAIROPERCODE,0,v_length)||',%'
--and b.repairopercode like I_UNITTREECODE||'%'
and not REPAIROPERCODE is null
and DECODE(I_TIMETYPE,'RECEPTTIME',FIRSTRECEPTTIME,'ARCHIVETIME',archivetime,reverttime) between t_dBeginDate and t_dEndDate
AND OT.RANDNUM=T_RANDOMCODE
group by decode(I_STATTYPE,'OPER',REPAIROPERCODE,OT.AREAID);
end if;
/* 统计用户数*/
begin
if instr(I_UNITTREECODE,'00010016')>0 then --金华使用通用_局
--获取关联的局向
INSERT INTO RPT_SVR_USR_ORG_BUREAU
(BUREAUID, treecode, butreecode, RANDOMCODE)
SELECT DISTINCT R.RIGHTVALUE,
rpt.unitid,
p.treecode,
T_RANDOMCODE
FROM RIG_SDATA_AUTHOR_JH R,
(select distinct unitid
from rpt_svr_usr_tab_repairmonth
where randomcode = T_RANDOMCODE) rpt,
org_tree ot,
pub_bureau p
WHERE --instr(ot.treecode, rpt.areaid) > 0
ot.isvalid='Y'
and ot.treecode like rpt.unitid||'%'
--AND R.SUBFACTORID = RPT_GET_BUSINESSID_BYCODE('IDB_SVR_TEL97')
and r.RIGHTVALUE = p.bureauid
AND R.OWNERID = ot.objectid
and R.RIGHTTYPEID = '6D1F47F2477D9F0F408FB9E4F99BC3A6' ;
else --其它本地网用服务保障_局
--获取关联的局向
INSERT INTO RPT_SVR_USR_ORG_BUREAU
(BUREAUID, treecode, butreecode, RANDOMCODE)
SELECT DISTINCT R.RIGHTVALUE,
rpt.unitid,
p.treecode,
T_RANDOMCODE
FROM RIG_SDATA_AUTHOR R,
(select distinct unitid
from rpt_svr_usr_tab_repairmonth
where randomcode = T_RANDOMCODE) rpt,
org_tree ot,
pub_bureau p
WHERE --instr(ot.treecode, rpt.areaid) > 0
ot.isvalid='Y'
and ot.treecode like rpt.unitid||'%'
--AND R.SUBFACTORID = RPT_GET_BUSINESSID_BYCODE('IDB_SVR_TEL97')
and r.RIGHTVALUE = p.bureauid
AND R.OWNERID = ot.objectid
and R.RIGHTTYPEID = 'D916070C5839E1E3056D54544A026C63'
AND R.SUBFACTORID = '9DE2EA4695DCADE1E16A0B4A4A283671';
end if;
--根据关联的局向得到子局向
insert into RPT_SVR_USR_ORG_BUREAU2(BUREAUID,treecode,RANDOMCODE)
select distinct b.BUREAUID,a.treecode,T_RANDOMCODE
from RPT_SVR_USR_ORG_BUREAU a ,pub_bureau b,org_tree ot
where --instr(b.treecode,a.butreecode)>0
b.treecode like a.butreecode||'%'
and a.treecode=ot.treecode
and ot.objecttype='U' --组织单元类型
and RANDOMCODE =T_RANDOMCODE;
--统计用户数
insert into rpt_svr_usr_tab_repairmonth
(RANDOMCODE ,
unitid ,
USERNUM )
select T_RANDOMCODE,
b.treecode,
sum(u.usrnum)
from RPT_SVR_USR_USRNUM_BUREAU u,
RPT_SVR_USR_ORG_BUREAU2 b
where u.bureauid=b.bureauid
and (I_SUBCLASS is null or I_SUBCLASS like '%'||nvl(u.subclass,9)||'%')
and (I_SPECIALTY is null or I_SPECIALTY like '%'||u.specialty||'%')
and b.RANDOMCODE = T_RANDOMCODE
group by b.treecode;
EXCEPTION
WHEN OTHERS THEN
T_ERROR:=sqlerrm;
end;
--合计:
insert into rpt_svr_usr_tab_repairmonth
(RANDOMCODE ,
unitname ,
USERNUM ,
RECEPTNUM ,
CLOGNUM ,
RECLOGNUM ,
HASTENNUM ,
DOOROUTNUM ,
TOTALDURATIONNUM,
assessdurationum,
AVEREPAIRTIME ,
avgassrepairtime,
MAXREPAIRTIME ,
REPAIRNUM4 ,
REPAIRNUM6 ,
REPAIRNUM8 ,
REPAIRNUM24 ,
REPAIRNUM48 ,
REPAIRNUM482 ,
betclognum,
booknum)
select T_RANDOMCODE,
'$合计:',
sum(USERNUM) ,
sum(RECEPTNUM) ,
sum(CLOGNUM) ,
sum(RECLOGNUM) ,
sum(HASTENNUM) ,
sum(DOOROUTNUM) ,
sum(totaldurationnum),
sum(assessdurationum),
trunc(sum(totaldurationnum)/sum(CLOGNUM)/60,2) ,
trunc(sum(assessdurationum)/sum(CLOGNUM)/60,2) ,
trunc(max(MAXREPAIRTIME),2) ,
sum(REPAIRNUM4) ,
sum(REPAIRNUM6) ,
sum(REPAIRNUM8) ,
sum(REPAIRNUM24) ,
sum(REPAIRNUM48) ,
sum(REPAIRNUM482) ,
sum(betclognum) ,
sum(booknum)
from rpt_svr_usr_tab_repairmonth
where randomcode=T_RANDOMCODE;
--更新区域名称
update rpt_svr_usr_tab_repairmonth a
set unitname=(select objectname
from org_tree ot
where a.unitid=ot.treecode
)
where exists(select objectname
from org_tree ot
where a.unitid=ot.treecode
)
and randomcode=T_RANDOMCODE;
if i_stattype='OPER' then
update rpt_svr_usr_tab_repairmonth a
set unitid=(select min(unitid)
from rpt_svr_usr_tab_repairmonth b
where RANDOMCODE = T_RANDOMCODE
and a.unitname=b.unitname)
where exists(select *
from rpt_svr_usr_tab_repairmonth b
where a.unitid<>b.unitid
and a.unitname=b.unitname
and RANDOMCODE = T_RANDOMCODE)
and RANDOMCODE = T_RANDOMCODE;
end if;
commit;
open p_cursor for
select
unitid,
unitname,
sum(USERNUM) as USERNUM ,
sum(RECEPTNUM) as RECEPTNUM ,
getrate(sum(RECEPTNUM),sum(USERNUM)) as receptrate ,
sum(CLOGNUM) as CLOGNUM ,
getrate(sum(clognum),sum(usernum)) as clograte,
sum(RECLOGNUM) as RECLOGNUM ,
getrate(sum(reclognum),sum(clognum)) as reclograte,
sum(HASTENNUM) as HASTENNUM ,
getrate(sum(HASTENNUM),sum(clognum)) as hastenrate,
sum(DOOROUTNUM) as DOOROUTNUM ,
getrate(sum(DOOROUTNUM),sum(clognum)) as doorrate,
sum(AVEREPAIRTIME) as AVEREPAIRTIME ,
sum(avgassREPAIRTIME) as avgassREPAIRTIME,
max(MAXREPAIRTIME) as MAXREPAIRTIME ,
getrate(sum(betclognum),sum(clognum)) as betclognumrate,
sum(REPAIRNUM4) as REPAIRNUM4 ,
getrate(sum(REPAIRNUM4),sum(clognum)) as REPAIRNUM4RATE,
sum(REPAIRNUM6) as REPAIRNUM6, --增加6小时
getrate(sum(REPAIRNUM6),sum(clognum)) as REPAIRNUM6RATE,
sum(REPAIRNUM8) as REPAIRNUM8 , --已改为12小时
getrate(sum(REPAIRNUM8),sum(clognum)) as REPAIRNUM8RATE,
sum(REPAIRNUM24) as REPAIRNUM24 ,
getrate(sum(REPAIRNUM24),sum(clognum)) as REPAIRNUM24RATE,
sum(REPAIRNUM48) as REPAIRNUM48 ,
getrate(sum(REPAIRNUM48),sum(clognum)) as REPAIRNUM48RATE,
sum(REPAIRNUM482) as REPAIRNUM482 ,
getrate(sum(REPAIRNUM482),sum(clognum)) as REPAIRNUM482RATE,
sum(booknum) as booknum,
getrate(sum(BOOKNUM),sum(clognum)) as BOOKRATE
from rpt_svr_usr_tab_repairmonth a
where randomcode=T_RANDOMCODE
group by unitid,unitname
order by (case when instr(I_UNITTREECODE,'00010016')>0 then unitname
else
unitid end),unitname;
EXCEPTION
WHEN OTHERS THEN
null;
end;
delete rpt_svr_usr_tab_repairmonth where randomcode=T_RANDOMCODE;
delete rpt_temp_org_tree where randnum=T_RANDOMCODE;
delete RPT_SVR_USR_ORG_BUREAU where Randomcode=T_RANDOMCODE;
delete RPT_SVR_USR_ORG_BUREAU2 where Randomcode=T_RANDOMCODE;
commit;
exception
WHEN OTHERS THEN
CP_SYS_EXCEPTION('RPT_SVR_USR_STAT_REPAIRMONTH', '区公司周报统计表：Error is:' || SQLERRM(SQLCODE), SQLCODE);
END;
