create or replace procedure ---- is
COLLECTDATE varchar2(8) := to_char(sysdate-1,'yyyymmdd'); -- 统计日期 yyyymmdd
INIT_STATE VARCHAR2(2) ;--当天初始化状态
END_STATE VARCHAR2(2); --当天结束状态
FAULT_TIME NUMBER(10) DEFAULT 0; --当天故障时间
EFFECT_TIME NUMBER(10) DEFAULT 0; --当天有效时间
--4
THIRD_FAULT_TIME NUMBER(10) DEFAULT 0; --当天第三方故障时间
--8
third_party_flag VARCHAR2(2) default 0; -- 最后一条数据的第三方标识
OUTSIDE_FAULT_TIME NUMBER(10) DEFAULT 0; --外场设备故障时间
OUTSIDE_EFFECT_TIME NUMBER(10) DEFAULT 0; --外场设备有效时间
BEFORECOLLECTDATE varchar2(8) := to_char(sysdate-2,'yyyymmdd'); --统计日期 yyyymmdd
FAULT_START_DATE DATE DEFAULT TRUNC(SYSDATE)-1;
-- EFFECT_START_DATE DATE DEFAULT TRUNC(SYSDATE)-1;
v_update_sql varchar2(1000);
DATA_INIT_FLAG number default 0 ;
begin
begin
--前端设备
select count(*) into DATA_INIT_FLAG from EQUIP_DATA_ANALYSIS where collect_date=COLLECTDATE and EQUIP_SERVICE_FLAG='1';
if DATA_INIT_FLAG=0 then
FOR C IN (SELECT ei.DEVICEID,ei.DEVICESTATE,ei.DEVICETYPE,ei.devicename, ei.pointid,
ei.belongtoproj,ei.direction,ei.departmentid,ei.area,ei.thirdsyscode,ei.VOLUMNTYPE
FROM EQUIPMENT_INFO ei
--1-- and ei.devicetype = '10' and ei.deviceid = '370203000000101060'
WHERE ei.DEVICESTATE IN('1','2','3','4','5')) LOOP
INSERT INTO EQUIP_DATA_ANALYSIS
(id, deviceid, collect_date, device_type, device_state, pointid, departmentid, direction, devicename, area, projectid,thirdsyscode, sub_type,EQUIP_SERVICE_FLAG)
VALUES
(sys_guid(),c.deviceid, COLLECTDATE, c.devicetype, c.devicestate, c.pointid, c.departmentid, c.direction, c.devicename, c.area, c.belongtoproj, c.thirdsyscode, c.volumntype,'1');
END LOOP;
DBMS_OUTPUT.PUT_LINE('前端设备基础数据初始化！');
commit;
end if;
--2
-- DBMS_OUTPUT.PUT_LINE('DATA_INIT_FLAG:'||DATA_INIT_FLAG);
FOR P IN (SELECT e.id, e.deviceid, e.collect_date, e.device_type, e.device_state, e.pointid, e.departmentid, e.direction,
e.devicename, e.area, e.projectid
FROM EQUIP_DATA_ANALYSIS e WHERE e.collect_date=COLLECTDATE AND E.EQUIP_SERVICE_FLAG='1') LOOP
begin
--获取设备的初始化状态和结束状态
END_STATE:=P.DEVICE_STATE;
INIT_STATE:='1';
--SELECT e.device_state INTO INIT_STATE FROM EQUIP_DATA_ANALYSIS e WHERE E.DEVICEID=P.DEVICEID AND E.COLLECT_DATE=BEFORECOLLECTDATE;
for B in (SELECT e.device_state FROM EQUIP_DATA_ANALYSIS e WHERE E.DEVICEID=P.DEVICEID AND E.COLLECT_DATE=BEFORECOLLECTDATE) loop
begin
INIT_STATE:=B.DEVICE_STATE;--昨天的结束状态等于今天的初始状态
end;
end loop;
FAULT_START_DATE:=trunc(sysdate)-1;
FAULT_TIME:=0;
EFFECT_TIME:=0;
--5
THIRD_FAULT_TIME:=0;
--9
third_party_flag:='0';
--3
--逐条数据分析，累积故障时间和有效时间
FOR HIS IN (SELECT S.DEVICESTATE, s.updatetime,ee.third_party_flag FROM EQUIPMENT_INFO_STATE_HISTORY S left join equip_maintain ee on s.maintainid = ee.maintainid WHERE s.deviceid = P.DEVICEID and s.DEVICETYPE=P.Device_Type
and s.deviceorserver = '1' and s.updatetime between trunc(sysdate) - 1 and trunc(sysdate) order by s.updatetime) LOOP
begin
IF INIT_STATE = '1' THEN
EFFECT_TIME:=EFFECT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
elsif INIT_STATE = '4' OR INIT_STATE = '5' then
EFFECT_TIME:=EFFECT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
FAULT_TIME:=FAULT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
end if;
--6
IF (INIT_STATE = '4' OR INIT_STATE = '5') and his.third_party_flag='1' then
THIRD_FAULT_TIME:=THIRD_FAULT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
end if;
INIT_STATE:=HIS.DEVICESTATE;
FAULT_START_DATE:= HIS.updatetime;
--10
-- if his.third_party_flag is not null then
third_party_flag:=his.third_party_flag;
-- elsif his.third_party_flag is null then
-- third_party_flag:='0';
-- end if;
end;
END LOOP;
-- 最后一条数据 或状态未改变的情况
IF INIT_STATE = '1' THEN
EFFECT_TIME:=EFFECT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
elsif INIT_STATE = '4' OR INIT_STATE = '5' then
EFFECT_TIME:=EFFECT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
FAULT_TIME:=FAULT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
end if;
--7正式修改，最后一条的处理
IF (INIT_STATE = '4' OR INIT_STATE = '5') and third_party_flag='1' then
THIRD_FAULT_TIME:=THIRD_FAULT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
end if;
--11
-- DBMS_OUTPUT.PUT_LINE('--THIRD_FAULT_TIME:'||THIRD_FAULT_TIME);
v_update_sql :=' update EQUIP_DATA_ANALYSIS eda set eda.FAULT_TIME= :1,eda.EFFECT_TIME=:2,eda.THIRD_FAULT_TIME=:3 where eda.id=:4 ';
execute immediate v_update_sql
using FAULT_TIME,EFFECT_TIME,THIRD_FAULT_TIME,P.ID;
commit;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('系统可用率分析错误，'||sqlcode || sqlerrm );
ROLLBACK;
end;
END LOOP;
end;
begin
--中心服务
delete from EQUIP_DATA_ANALYSIS where collect_date=COLLECTDATE and EQUIP_SERVICE_FLAG='2';
FOR C IN (SELECT ei.ID DEVICEID,ei.Server_State DEVICESTATE,ei.server_type DEVICETYPE,ei.server_name devicename,
--3-- and ei.SERVER_TYPE='01' and ei.id='370200000000011034'
ei.Project_Code belongtoproj,ei.departmentid departmentid FROM SERVER_INFO ei WHERE ei.Server_State IN('1','2','3','4','5')) LOOP
INSERT INTO EQUIP_DATA_ANALYSIS
(id, deviceid, collect_date, device_type, device_state, departmentid, devicename , projectid,EQUIP_SERVICE_FLAG)
VALUES
(sys_guid(),c.deviceid, COLLECTDATE, c.devicetype, c.devicestate, c.departmentid, c.devicename, c.belongtoproj, '2');
END LOOP;
DBMS_OUTPUT.PUT_LINE('中心服务基础数据初始化！');
commit;
---系统可用率分析 分析每个设备的可用时间和故障时间
FOR P IN (SELECT e.id, e.deviceid, e.collect_date, e.device_type, e.device_state, e.pointid, e.departmentid, e.direction,e.devicename, e.area, e.projectid
FROM EQUIP_DATA_ANALYSIS e WHERE e.collect_date=COLLECTDATE AND E.EQUIP_SERVICE_FLAG='2') LOOP
begin
--获取设备的初始化状态和结束状态
END_STATE:=P.DEVICE_STATE;
INIT_STATE:='1';
for B in (SELECT e.device_state FROM EQUIP_DATA_ANALYSIS e WHERE E.DEVICEID=P.DEVICEID AND E.COLLECT_DATE=BEFORECOLLECTDATE) loop
begin
INIT_STATE:=B.DEVICE_STATE;
end;
end loop;
FAULT_START_DATE:=trunc(sysdate)-1;
FAULT_TIME:=0;
EFFECT_TIME:=0;
--4
THIRD_FAULT_TIME:=0;
--5
third_party_flag:='0';
--6
-- DBMS_OUTPUT.PUT_LINE('P.DEVICEID:'||P.DEVICEID||'-P.Device_Type'||P.Device_Type);
--逐条数据分析，累积故障时间和有效时间
FOR HIS IN (SELECT S.DEVICESTATE, s.updatetime,ee.third_party_flag FROM EQUIPMENT_INFO_STATE_HISTORY S left join equip_maintain ee on s.maintainid = ee.maintainid WHERE s.deviceid = P.DEVICEID and s.DEVICETYPE=P.Device_Type
and s.deviceorserver = '2' and s.updatetime between trunc(sysdate) - 1 and trunc(sysdate) order by s.updatetime) LOOP
begin
IF INIT_STATE = '1' THEN
EFFECT_TIME:=EFFECT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
elsif INIT_STATE = '4' OR INIT_STATE = '5' then
EFFECT_TIME:=EFFECT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
FAULT_TIME:=FAULT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
end if;
--7正式修改，第三方故障时间
IF (INIT_STATE = '4' OR INIT_STATE = '5') and his.third_party_flag='1' then
THIRD_FAULT_TIME:=THIRD_FAULT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
end if;
INIT_STATE:=HIS.DEVICESTATE;
FAULT_START_DATE:= HIS.updatetime;
--8
third_party_flag:=his.third_party_flag;
end;
END LOOP;
-- 最后一条数据 或状态未改变的情况
IF INIT_STATE = '1' THEN
EFFECT_TIME:=EFFECT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
elsif INIT_STATE = '4' OR INIT_STATE = '5' then
EFFECT_TIME:=EFFECT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
FAULT_TIME:=FAULT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
end if;
--9正式修改，最后一条的处理
IF (INIT_STATE = '4' OR INIT_STATE = '5') and third_party_flag='1' then
THIRD_FAULT_TIME:=THIRD_FAULT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
end if;
--10
-- DBMS_OUTPUT.PUT_LINE('--THIRD_FAULT_TIME:'||THIRD_FAULT_TIME);
v_update_sql :=' update EQUIP_DATA_ANALYSIS eda set eda.FAULT_TIME= :1,eda.EFFECT_TIME=:2,eda.THIRD_FAULT_TIME=:3 where eda.id=:4 ';
execute immediate v_update_sql
using FAULT_TIME,EFFECT_TIME,THIRD_FAULT_TIME,P.ID;
commit;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('系统可用率分析错误，'||sqlcode || sqlerrm );
ROLLBACK;
end;
END LOOP;
end;
BEGIN
FOR pj in (select p.project_id from faci_project p ) loop
select sum(faultdevice),sum(alldevice) into FAULT_TIME,EFFECT_TIME from(select sum(d.fault_time) faultdevice ,sum(d.effect_time) alldevice from equip_data_analysis d WHERE d.projectid=pj.project_id and D.collect_date=COLLECTDATE union (select 0 faultdevice,0 alldevice from dual));
select sum(faultdevice),sum(alldevice) into OUTSIDE_FAULT_TIME,OUTSIDE_EFFECT_TIME from ( (select (sum(r.inspection_record_count) - sum(r.inspection_success_count))*24*60*60 as faultdevice,sum(r.inspection_record_count)*24*60*60 alldevice
from equip_inspection_task t left join equip_inspection_record r on t.taskid = r.taskid where t.projectid = pj.project_id and t.start_date <= (to_date(COLLECTDATE,'yyyymmdd')+1) and t.end_date >= (to_date(COLLECTDATE,'yyyymmdd')+1) and trunc(r.inspection_time) <= (to_date(COLLECTDATE,'yyyymmdd')+1) and t.device_source = '2' group by t.projectid) union (select 0 faultdevice,0 alldevice from dual));
DBMS_OUTPUT.PUT_LINE('inside data :'||FAULT_TIME || ' '||EFFECT_TIME ||' project id:'||pj.project_id||' time:'|| (to_date(COLLECTDATE,'yyyymmdd')+1));
FAULT_TIME := FAULT_TIME+OUTSIDE_FAULT_TIME;
EFFECT_TIME := EFFECT_TIME+OUTSIDE_EFFECT_TIME;
DBMS_OUTPUT.PUT_LINE('outside data :'||OUTSIDE_FAULT_TIME || ' '||OUTSIDE_EFFECT_TIME ||' project id:'||pj.project_id||' time:'|| (to_date(COLLECTDATE,'yyyymmdd')+1));
v_update_sql :=' update maintain_timely set FAULT_TIME= :1,EFFECT_TIME=:2 where projectid=:3 and statisticsDate =:4 and ismerge=:5';
execute immediate v_update_sql
using FAULT_TIME,EFFECT_TIME,pj.project_id,TO_DATE(COLLECTDATE,'YYYYMMDD'),'0';
commit;
end loop;
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('可用率汇总统计错误，'||sqlcode || sqlerrm );
ROLLBACK;
end SERVER_Data_ANALYSIS_PRO;
扫一扫
769
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
