CREATE OR REPLACE FUNCTION f_get_next_onduty
/*
根据科室ID获取下一值班人
*/
(
p_id NUMBER --科室ID
)
RETURN VARCHAR2
IS
l_return VARCHAR2(4000);
l_tmp VARCHAR2(200);
l_task_type trsdba.watch_monitorteam_record.task_type%TYPE;
l_first_processor trsdba.watch_manage.first_processor%TYPE;
l_second_processor trsdba.watch_manage.second_processor%TYPE;
l_third_processor trsdba.watch_manage.third_processor%TYPE;
l_first_processor_name trsdba.wcmuser.username%TYPE;
l_second_processor_name trsdba.wcmuser.username%TYPE;
l_third_processor_name trsdba.wcmuser.username%TYPE;
l_watch_type trsdba.watch_manage.watch_type%TYPE;
l_flag1 VARCHAR2(1):='N'; --为Y则代表满足条件
l_flag2 VARCHAR2(1):='N';
l_flag3 VARCHAR2(1):='N';
l_sql VARCHAR2(4000):='';
l_count NUMBER;
l_cur_watch_date trsdba.watch_monitorteam_record.watch_date%TYPE;
TYPE t_cur IS REF CURSOR;
l_cur t_cur;
CURSOR l_c IS
SELECT
NVL(t.first_processor,0),
NVL(t.second_processor,0),
NVL(t.third_processor,0),
t.watch_type
FROM trsdba.watch_manage t
WHERE TO_DATE(TO_CHAR(t.watch_date,'yyyy-mm-dd')||' '||NVL(t.start_time,'00:00')||':00','yyyy-mm-dd hh24:mi:ss') <= SYSDATE
AND TO_DATE(TO_CHAR(t.watch_date,'yyyy-mm-dd')||' '||NVL(t.end_time,'23:59')||':59','yyyy-mm-dd hh24:mi:ss') >= SYSDATE
AND TO_CHAR(t.watch_date,'yyyy-mm-dd') = TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND t.watch_type <> 1
UNION ALL
SELECT
NVL(t.first_processor,0),
NVL(t.second_processor,0),
NVL(t.third_processor,0),
t.watch_type
FROM trsdba.watch_manage t
WHERE TO_CHAR(t.watch_date,'yyyy-mm-dd') = TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND t.watch_type = 1;
BEGIN
IF p_id = 11 THEN --监控管理组
SELECT
NVL(COUNT(*),0) INTO l_count
FROM trsdba.watch_monitorteam_record t
WHERE TO_DATE(TO_CHAR(t.watch_date,'yyyy-mm-dd')||' '||NVL(t.start_time,'00:00')||':00','yyyy-mm-dd hh24:mi:ss') <= SYSDATE
AND TO_DATE(TO_CHAR(t.watch_date+decode(task_type,2,1,0),'yyyy-mm-dd')||' '||NVL(t.end_time,'23:59')||':59','yyyy-mm-dd hh24:mi:ss') >= SYSDATE;
IF l_count <> 1 THEN
l_return:='';
ELSE
SELECT
NVL(t.task_type,0) INTO l_task_type
FROM trsdba.watch_monitorteam_record t
WHERE TO_DATE(TO_CHAR(t.watch_date,'yyyy-mm-dd')||' '||NVL(t.start_time,'00:00')||':00','yyyy-mm-dd hh24:mi:ss') <= SYSDATE
AND TO_DATE(TO_CHAR(t.watch_date+decode(task_type,2,1,0),'yyyy-mm-dd')||' '||NVL(t.end_time,'23:59')||':59','yyyy-mm-dd hh24:mi:ss') >= SYSDATE ;
SELECT t.watch_date INTO l_cur_watch_date
FROM trsdba.watch_monitorteam_record t
WHERE TO_DATE(TO_CHAR(t.watch_date,'yyyy-mm-dd')||' '||NVL(t.start_time,'00:00')||':00','yyyy-mm-dd hh24:mi:ss') <= SYSDATE
AND TO_DATE(TO_CHAR(t.watch_date+decode(task_type,2,1,0),'yyyy-mm-dd')||' '||NVL(t.end_time,'23:59')||':59','yyyy-mm-dd hh24:mi:ss') >= SYSDATE ;
IF l_task_type = 0 THEN
l_return:='';
ELSIF l_task_type = 1 THEN
SELECT
NVL(COUNT(*),0) INTO l_count
FROM trsdba.watch_monitorteam_record t
WHERE t.task_type = 2
AND TO_CHAR(t.watch_date,'yyyy-mm-dd') = TO_CHAR(l_cur_watch_date,'yyyy-mm-dd');
IF l_count = 1 THEN
SELECT
TO_CHAR(a.userid)||','||a.username INTO l_return
FROM trsdba.wcmuser a, trsdba.watch_monitorteam_record b
WHERE b.task_type = 2
AND a.userid = b.watch
AND TO_CHAR(b.watch_date,'yyyy-mm-dd') = TO_CHAR(l_cur_watch_date,'yyyy-mm-dd');
ELSE
l_return:='';
END IF;
ELSIF l_task_type = 2 THEN
SELECT
NVL(COUNT(*),0) INTO l_count
FROM trsdba.watch_monitorteam_record t
WHERE t.task_type = 1
AND TO_CHAR(t.watch_date,'yyyy-mm-dd') = TO_CHAR(l_cur_watch_date+1,'yyyy-mm-dd');
IF l_count = 1 THEN
SELECT
TO_CHAR(a.userid)||','||a.username INTO l_return
FROM trsdba.wcmuser a, trsdba.watch_monitorteam_record b
WHERE b.task_type = 1
AND a.userid = b.watch
AND TO_CHAR(b.watch_date,'yyyy-mm-dd') = TO_CHAR(l_cur_watch_date+1,'yyyy-mm-dd');
ELSE
l_return:='';
END IF;
END IF;
END IF;
ELSE
OPEN l_c;
LOOP
FETCH l_c INTO l_first_processor,l_second_processor,l_third_processor,l_watch_type;
EXIT WHEN l_c%NOTFOUND;
IF l_first_processor <> 0 THEN
SELECT
NVL(COUNT(*),0) INTO l_count
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND t.userid = l_first_processor;
IF l_count = 1 THEN
l_flag1:='Y';
ELSE
l_flag1:='N';
END IF;
END IF;
IF l_second_processor <> 0 THEN
SELECT
NVL(COUNT(*),0) INTO l_count
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND t.userid = l_second_processor;
IF l_count = 1 THEN
l_flag2:='Y';
ELSE
l_flag2:='N';
END IF;
END IF;
IF l_third_processor <> 0 THEN
SELECT
NVL(COUNT(*),0) INTO l_count
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND t.userid = l_third_processor;
IF l_count = 1 THEN
l_flag3:='Y';
ELSE
l_flag3:='N';
END IF;
END IF;
IF l_flag1 = 'Y' OR l_flag2 = 'Y' OR l_flag3 = 'Y' THEN
IF l_watch_type = 1 THEN
l_sql:='
SELECT
t.first_processor,
t.second_processor,
t.third_processor
FROM trsdba.watch_manage t
WHERE t.watch_type = 1
AND TO_CHAR(t.watch_date,''yyyy-mm-dd'') = TO_CHAR(SYSDATE + 1,''yyyy-mm-dd'')';
ELSIF l_watch_type = 2 THEN
l_sql:='
SELECT
t.first_processor,
t.second_processor,
t.third_processor
FROM trsdba.watch_manage t
WHERE t.watch_type = 3
AND TO_CHAR(t.watch_date,''yyyy-mm-dd'') = TO_CHAR(SYSDATE,''yyyy-mm-dd'')';
ELSIF l_watch_type = 3 THEN
l_sql:='
SELECT
t.first_processor,
t.second_processor,
t.third_processor INTO
l_first_processor,
l_second_processor,
l_third_processor
FROM trsdba.watch_manage t
WHERE t.watch_type = 4
AND TO_CHAR(t.watch_date,''yyyy-mm-dd'') = TO_CHAR(SYSDATE,''yyyy-mm-dd'')';
ELSIF l_watch_type = 4 THEN
l_sql:='
SELECT
t.first_processor,
t.second_processor,
t.third_processor INTO
l_first_processor,
l_second_processor,
l_third_processor
FROM trsdba.watch_manage t
WHERE t.watch_type = 2
AND TO_CHAR(t.watch_date,''yyyy-mm-dd'') = TO_CHAR(SYSDATE + 1,''yyyy-mm-dd'')';
ELSE
l_return:='null,null;null,null;null,null';
END IF;
IF l_sql IS NOT NULL THEN
OPEN l_cur FOR l_sql;
LOOP
FETCH l_cur INTO l_first_processor,l_second_processor,l_third_processor;
EXIT WHEN l_cur%NOTFOUND;
SELECT
NVL(COUNT(*),'') INTO l_count
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND b.userid = l_first_processor;
IF l_count = 1 THEN
SELECT
NVL(b.username,'') INTO l_first_processor_name
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND b.userid = l_first_processor;
END IF;
SELECT
NVL(COUNT(*),'') INTO l_count
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND b.userid = l_second_processor;
IF l_count = 1 THEN
SELECT
NVL(b.username,'') INTO l_second_processor_name
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND b.userid = l_second_processor;
END IF;
SELECT
NVL(COUNT(*),'') INTO l_count
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND b.userid = l_third_processor;
IF l_count = 1 THEN
SELECT
NVL(b.username,'') INTO l_third_processor_name
FROM trsdba.wcmgrpuser t,
trsdba.wcmuser b
WHERE t.userid = b.userid
AND t.groupid = p_id
AND b.userid = l_third_processor;
END IF;
IF l_first_processor_name IS NOT NULL OR l_second_processor_name IS NOT NULL OR l_third_processor_name IS NOT NULL THEN
SELECT
NVL(TO_CHAR(l_first_processor),'null')||','||NVL(TO_CHAR(l_first_processor_name),'null')||';'||
NVL(TO_CHAR(l_second_processor),'null')||','||NVL(TO_CHAR(l_second_processor_name),'null')||';'||
NVL(TO_CHAR(l_third_processor),'null')||','||NVL(TO_CHAR(l_third_processor_name),'null')||';|' INTO l_tmp
FROM dual;
l_return:=l_return||l_tmp;
END IF;
END LOOP;
CLOSE l_cur;
IF l_return IS NOT NULL THEN
l_return:=SUBSTR(l_return,1,LENGTH(l_return)-1);
END IF;
END IF;
ELSE
l_return:='';
END IF;
END LOOP;
CLOSE l_c;
END IF;
RETURN l_return;
END;