P_B_DLG_PBH_OPERATORSTAT
CREATE OR REPLACE PROCEDURE P_PUB_OPERAT
/*
Description : 功能描述
Author : 方孝科
Date : YYYY-MM-DD
Version : 版本
Caller : 调用者
Callee : 被调用者
Comments : 注释
*/
AS
v_LogLimit INT; -- 日结每次执行时最多可以结多长时间的数据，单位天，默认10天
v_BeginTime DATE; -- 本次日结开始时间
v_EndTime DATE; -- 本次日结结束时间
v_Minute INT; -- 分钟偏置变量
v_Num INT; -- 日志用到的计数器
v_TimeTemp VARCHAR2(20); -- 存放时间转换为字符串用
v_RollDay INT; -- 回滚天数
V_VALIDFLAG INT;
v_BeginTrans INTEGER;
v_StartDate DATE; -- 日结开始时间
v_Canceled INT; -- 判断取消日结临时变量
v_ErrCode INT;
v_ErrMsg VARCHAR2(200);
v_count INT;
i_StartTime DATE := NULL; -- 结算开始时间
i_EndTime DATE := NULL; -- 结算结束时间
i_RollDay INT := 0; -- 回滚天数
v_PROCEDURE_NAME VARCHAR2(40) := 'P_PUB_OPERAT';
BEGIN
-- 判断日结是否已启用
BEGIN
SELECT t.VALIDFLAG INTO v_VALIDFLAG
FROM T_B_DLG_PBH_RUNNING_CONFIG t
WHERE UPPER(t.DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
-- 日结未启用
IF v_VALIDFLAG = 1 THEN
RETURN; -- 直接返回
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO T_B_DLG_PBH_RUNNING_CONFIG(DAYLOGSPNAME,DAYLOGDSCRIPTION,VALIDFLAG)
VALUES(UPPER(v_PROCEDURE_NAME),'工作流操作日志结算',0);
COMMIT;
WHEN OTHERS THEN
RETURN;
END;
-- 对于回滚缺省30天的日结，最多不能超过100天
IF i_RollDay > 100 THEN
v_RollDay := 100;
ELSIF i_RollDay < 0 THEN
v_RollDay := 0;
ELSE
v_RollDay := i_RollDay;
END IF;
-- 判断日结运行状态和取消标志，决定是否退出该次日结
SELECT COUNT(DAYLOGSPNAME)
INTO v_Num
FROM T_B_DLG_PBH_CONFIG
WHERE (UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME) AND CANCELFLAG = 1)
OR UPPER(STATUS) = 'RUNNING';
IF v_Num >= 1 THEN
RETURN;
END IF;
-- 写日结运行标志
SELECT COUNT(DAYLOGSPNAME)
INTO v_Num
FROM T_B_DLG_PBH_CONFIG
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
IF v_Num = 1 THEN
UPDATE T_B_DLG_PBH_CONFIG
SET Status = 'RUNNING'
WHERE UPPER(DayLogSpName) = UPPER(v_PROCEDURE_NAME);
ELSE
IF v_Num > 1 THEN
DELETE FROM T_B_DLG_PBH_CONFIG
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
END IF;
INSERT INTO T_B_DLG_PBH_CONFIG
(DayLogSpName, DayLogDscription, Status, LogLimit, CancelFlag)
VALUES
(UPPER(v_PROCEDURE_NAME), '工作流操作日志结算', 'RUNNING', 10, 0);
END IF;
COMMIT;
-- 自动/手工日结
IF i_StartTime IS NULL OR i_EndTime IS NULL THEN
-- 取日结表中最后成功的日期，加上1天作为本次日结开始时间
SELECT (MAX(LOGDATE) + 1)
INTO v_BeginTime
FROM T_B_DLG_PBH_OPERATORSTAT;
-- 日结表为空，从业务原始数据表中取最小时间
IF v_BeginTime IS NULL THEN
SELECT MIN(COMPLETETIME) INTO v_BeginTime
FROM T_PBH_PROBLEMWORKITEM;
END IF;
-- 业务原始数据表为空，记日志退出
IF v_BeginTime IS NULL THEN
INSERT INTO T_B_DLG_PBH_LOG
(DAYLOGSPNAME, STATUS, JOBSTARTTIME, JOBFINISHTIME, ERRORMSG)
VALUES
(UPPER(v_PROCEDURE_NAME), 'EXCEPTION', SYSDATE, SYSDATE, 'No data to log!');
UPDATE T_B_DLG_PBH_CONFIG
SET STATUS = 'EXCEPTION'
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
COMMIT;
RETURN;
END IF;
-- 将起始时间回滚 v_RollDay天
v_BeginTime := v_BeginTime - v_RollDay;
-- 计算本轮统计的开始时间
v_BeginTime := TRUNC(v_BeginTime, 'DD');
-- 取日结配置信息，为空时给缺省值，并判断是否越界
SELECT NVL(LOGLIMIT, 10)
INTO v_LogLimit
FROM T_B_DLG_PBH_CONFIG
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
IF v_LogLimit < 1 THEN
v_LogLimit := 1;
END IF;
-- 计算本轮统计的结束时间
v_EndTime := TRUNC(SYSDATE, 'DD');
-- 日结需回滚时 ： 取得日结结束时间，使日结跨度不超过LogLimit+v_RollDay天
IF (v_EndTime - v_BeginTime) > (v_LogLimit + v_RollDay) THEN
v_EndTime := v_BeginTime + v_LogLimit + v_RollDay;
END IF;
ELSE
-- 手工日结
v_BeginTime := TRUNC(i_StartTime, 'DD');
v_EndTime := TRUNC(i_EndTime, 'DD');
END IF;
v_StartDate := SYSDATE;
-- 写日结运行标志
INSERT INTO T_B_DLG_PBH_LOG
(DAYLOGSPNAME, STATUS, LOGBEGINTIME, LOGENDTIME, JOBSTARTTIME)
VALUES
(UPPER(v_PROCEDURE_NAME), 'RUNNING', v_BeginTime, v_EndTime, v_StartDate);
COMMIT;
-- 循环结算数据
WHILE v_BeginTime < v_EndTime LOOP
v_BeginTrans := 1;
-- 删除原来的数据
DELETE FROM T_B_DLG_PBH_OPERATORSTAT
WHERE LOGDATE >= v_BeginTime
AND LOGDATE < (v_BeginTime + 1);
-- 插入操作日结数据
INSERT INTO T_B_DLG_PBH_OPERATORSTAT
(
LOGDATE ,
STAFFID ,
HANDLINGDEPT ,
HANDLINGORGACODE,
HANDLINGROLE ,
OPERATETYPE ,
SRTYPEID ,
OPERATECOUNT ,
INTIMECOUNT
)
SELECT
V_BEGINTIME,
W.HANDLINGSTAFF,
W.HANDLINGDEPT,
W.HANDLINGORGACODE,
W.HANDLINGROLE,
W.OPERATETYPE,
P.SRTYPEID,
COUNT(*),
NVL(SUM(CASE WHEN (W.DEADLINETIME - https://www.wendangku.net/doc/826739301.html,PLETETIME) > 0 THEN 1 ELSE 0 END),0)
FROM T_PBH_PROBLEMWORKITEM W,
T_PBH_PROBLEMPROCESS P
WHERE https://www.wendangku.net/doc/826739301.html,PLETETIME >= V_BEGINTIME
AND https://www.wendangku.net/doc/826739301.html,PLETETIME < (V_BEGINTIME + 1)
AND W.SERIALNO = P.SERIALNO
GROUP BY
W.HANDLINGSTAFF,
W.HANDLINGDEPT,
W.HANDLINGORGACODE,
W.HANDLINGROLE,
W.OPERATETYPE,
P.SRTYPEID;
COMMIT;
v_BeginTrans := 0;
-- 写日结最后运行时间
UPDATE T_B_DLG_PBH_LOG
SET LASTLOGBEGINTIME = v_BeginTime
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME)
AND JOBSTARTTIME = v_StartDate;
UPDATE T_B_DLG_PBH_CONFIG
SET LASTLOGTIME = v_BeginTime
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
COMMIT;
-- 取系统日结的中断开关，缺省为0
SELECT CANCELFLAG
INTO v_Canceled
FROM T_B_DLG_PBH_CONFIG
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
-- 写日结取消信息
IF v_Canceled = 1 THEN
UPDATE T_B_DLG_PBH_LOG
SET STATUS = 'CANCELED',
JOBFINISHTIME = SYSDATE,
LASTLOGBEGINTIME = v_BeginTime
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME)
AND JOBSTARTTIME = v_StartDate;
UPDATE T_B_DLG_PBH_CONFIG
SET STATUS = 'CANCELED'
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
COMMIT;
RETURN;
END IF;
-- 以天为步长进行事务控制
v_BeginTime := v_BeginTime + 1;
END LOOP;
-- 写日结日志完成标志
UPDATE T_B_DLG_PBH_LOG
SET STATUS = 'FINISHED', JOBFINISHTIME = SYSDATE
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME)
AND JOBSTARTTIME = v_StartDate;
UPDATE T_B_DLG_PBH_CONFIG
SET STATUS = 'FINISHED'
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
COMMIT;
RETURN;
EXCEPTION
WHEN OTHERS THEN
v_ErrCode := SQLCODE;
v_ErrMsg := SUBSTRB(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE(v_ErrMsg);
IF v_BeginTrans = 1 THEN
ROLLBACK;
END IF;
UPDATE T_B_DLG_PBH_LOG
SET STATUS = 'EXCEPTION',
JOBFINISHTIME = SYSDATE,
LASTLOGBEGINTIME = V_BEGINTIME - 1,
ERRORCODE = V_ERRCODE,
ERRORMSG = V_ERRMSG
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME)
AND JOBSTARTTIME = v_StartDate;
UPDATE T_B_DLG_PBH_CONFIG
SET STATUS = 'EXCEPTION'
WHERE UPPER(DAYLOGSPNAME) = UPPER(v_PROCEDURE_NAME);
COMMIT;
END P_PUB_OPERAT