`
cleaneyes
  • 浏览: 342531 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

function nextOnduty

阅读更多
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;

 

分享到:
评论

相关推荐

    MATLAB的S-Function教程

    ### MATLAB的S-Function教程 #### S-Function概述 S-Function(系统函数)是MATLAB Simulink中一种强大的工具,它允许用户通过自定义的编程方式来扩展Simulink的功能。S-Function可以使用多种语言编写,包括MATLAB...

    S-function模块案例详解(MATLAB程序)

    在MATLAB环境中,Simulink是用于系统建模和仿真的一种强大的工具,而S-function则是Simulink中的自定义功能模块,允许用户扩展Simulink的功能,以满足特定的计算需求。本篇将深入探讨S-function模块在蹦极系统案例中...

    s-function函数,s-function函数定义放置错误,matlab源码.zip

    在MATLAB环境中,S-Function(System Function)是一种高级的编程接口,用于创建自定义的仿真组件,可以扩展Simulink的功能。S-Functions允许用户深入到Simulink的内部,构建自己的动态系统模型,满足特定的计算需求...

    通过 S-Function 集成 C 代码进行仿真

    S-Function(系统函数)是Simulink中一个强大的功能,允许用户使用C、C++、Fortran或其他编程语言来编写自定义模块,以便在Simulink模型中使用。 1. S-Function概念及仿真原理: S-Function是Simulink中的一个模块...

    使用S-Function函数实现离散PID控制器,并建立simulink仿真模型

    在MATLAB的Simulink环境中,S-Function是一种强大的工具,允许用户自定义系统行为,以满足特定的仿真需求。本教程将详细讲解如何利用S-Function实现一个离散PID控制器,并建立相应的Simulink仿真模型。 首先,理解S...

    S-Function使用手册.pdf

    S-Function 使用手册 S-Function 是 Simulink 中的一种自定义驱动库,允许用户创建自定义的模型和算法,以满足特定的仿真和设计需求。以下是 S-Function 使用手册的知识点总结: 一、S-Function 概念 * S-...

    VC6.0的__FUNCTION__宏支持

    **VC6.0的__FUNCTION__宏支持** 在C++编程中,`__FUNCTION__`是一个预定义的宏,它代表了当前函数的名称。在Visual C++ 6.0(简称VC6.0)环境下,这个宏尤其有用,因为它可以帮助开发者在调试和日志记录时获取函数...

    理解Javascript Function与Object

    理解Javascript Function与Object 在JavaScript中,Function和Object是两个非常重要的概念,它们之间存在着紧密的关系。在这篇文章中,我们将深入探讨Function和Object的关系,了解它们之间的联系和区别。 ...

    《MATLAB的S-Function编写指导》——完整版.pdf

    MATLAB的S-Function是一种强大的工具,用于在Simulink环境中创建自定义的动态系统模块。它允许用户利用C、C++或Fortran等底层语言的性能,来实现Simulink无法直接处理的复杂算法或特定硬件接口。这份《MATLAB的S-...

    MATLAB中的S-function中文教程

    S-function分为两种主要类型:Legacy S-functions(旧版S-function)和Blockset S-functions(Blockset型S-function)。Legacy S-functions是基于M文件的,通常用C或C++编写,适用于MATLAB R2016b及更早版本。...

    简谈FPGA verilog中的function用法与例子

    其中,`result`是接收function返回值的变量,`function_id`是被调用的function的名称,`expr1, expr2, ..., exprN`是传递给function的实际参数,必须按照function定义时声明输入参数的顺序来排列。 ### Function的...

    MATLAB的S-Function编写指导

    ### MATLAB的S-Function编写指导 #### 第一章:S-Function概述 S-Function(系统函数)是一种强大的工具,用于扩展Simulink的功能。它允许用户通过自定义的算法来增强模型的能力。本章首先介绍了S-Function的基本...

    S-Function编写指导

    ### S-Function编写指导 #### S-Function概述 **S-Function**(系统函数)是Simulink中一种强大的机制,允许用户通过自定义代码来扩展Simulink的功能。S-Function可以用来创建复杂的模块,这些模块能够实现...

    C语言实现s-function源码,含全局变量

    在MATLAB/Simulink环境中,S-Function(Simulink Function)是一种用户自定义的模块,用于扩展Simulink库的功能。S-Functions可以用多种编程语言编写,包括C语言,因为C语言具有高效和灵活性,是实现复杂计算逻辑的...

    s-function编写指导

    ### S-Function编写指导 #### 第一章:S-Function概述 **S-Function**(系统函数)是一种强大的工具,用于扩展Simulink®的功能。它允许用户自定义Simulink块的行为,从而实现更复杂的功能。本章节将详细介绍S-...

    s-function函数,s-function函数定义放置错误,matlab

    在MATLAB环境中,s-function(系统函数)是一种高级的编程工具,用于构建定制的Simulink模块,以处理复杂的系统模型或实现特定的算法。在本案例中,s-function被应用来解决一个倒立摆的非线性问题。倒立摆是一个经典...

    《MATLAB的S-Function编写指导》_matble_S-function_

    《MATLAB的S-Function编写指导》是一本专为初学者设计的教程,旨在帮助读者掌握MATLAB环境下的S-Function编程技术。S-Function,全称为Simulink Simulation Function,是MATLAB Simulink仿真环境中的一种特殊模块,...

    S-FUNCTION 编写指导

    这些示例覆盖了不同类型的S-Function,包括简单的M文件S-Function、连续状态S-Function、离散状态S-Function、混合系统S-Function、变步长S-Function等。 #### 使用C语言编写S-FUNCTION 使用C语言编写S-Function时...

Global site tag (gtag.js) - Google Analytics