【应用场景】

查询已超时的待办任务（对应执行计划中的任务【工作流超时处理服务】）

【案例演示】

<1>未启用工作流日历

SELECT  A.FAPPROVALASSIGNID ,
        A.FASSIGNID ,
        A.FOBJECTTYPEID ,
        A.FKEYVALUE ,
        A.FPOLICYXML ,
        A.FAPPROVALACTION ,
        A.FVARIABLENAME ,
        A.FDEFAULTRESULT ,
        A.FRESULT ,
        A.FISLOCKED ,
        A.FLOCKEDUSERID ,
        A.FISALLOWLOCK ,
        A.FISALLOWFORWARD ,
        A.FISALLOWBATCHDEAL ,
        A.FCANSPECIFYNEXT ,
        A.FTIMEOUTDEAL ,
        A.FTIMEOUTHOURS ,
        A.FTIMEOUTSTRATEGY ,
        A.FAUTOAPPROVAL ,
        A.FTIMEOUTDEALWITH
FROM    T_WF_APPROVALASSIGN A
        INNER JOIN ( SELECT T.FAPPROVALASSIGNID
                     FROM   T_WF_APPROVALASSIGN T
                            INNER JOIN T_WF_ASSIGN B ON B.FASSIGNID = T.FASSIGNID
                     WHERE  B.FSTATUS = '0'
                            AND T.FTIMEOUTDEAL = '1'
                            AND T.FDEALTIME < GETDATE()
                            - ( 1.0 * T.FTIMEOUTHOURS / 24 + 1.0
                                * T.FTIMEOUTMINUTE / 60 / 24 )
                            AND EXISTS ( SELECT 1
                                         FROM   T_WF_PROCINST C
                                         WHERE  B.FPROCINSTID = C.FPROCINSTID
                                                AND C.FSTATUS IN ( '0', '2' ) )
                   ) U ON U.FAPPROVALASSIGNID = A.FAPPROVALASSIGNID

<2>启用了工作流日历

SELECT  A.FAPPROVALASSIGNID ,
        A.FASSIGNID ,
        A.FOBJECTTYPEID ,
        A.FKEYVALUE ,
        A.FPOLICYXML ,
        A.FAPPROVALACTION ,
        A.FVARIABLENAME ,
        A.FDEFAULTRESULT ,
        A.FRESULT ,
        A.FISLOCKED ,
        A.FLOCKEDUSERID ,
        A.FISALLOWLOCK ,
        A.FISALLOWFORWARD ,
        A.FISALLOWBATCHDEAL ,
        A.FCANSPECIFYNEXT ,
        A.FTIMEOUTDEAL ,
        A.FTIMEOUTHOURS ,
        A.FTIMEOUTSTRATEGY ,
        A.FAUTOAPPROVAL ,
        A.FTIMEOUTDEALWITH
FROM    T_WF_APPROVALASSIGN A
        INNER JOIN ( SELECT T.FAPPROVALASSIGNID
                     FROM   T_WF_APPROVALASSIGN T
                            INNER JOIN T_WF_ASSIGN B ON B.FASSIGNID = T.FASSIGNID
                     WHERE  B.FSTATUS = '0'
                            AND T.FTIMEOUTDEAL = '1'
                            AND ( T.FCALENDARID > 0
                                  AND EXISTS ( SELECT   1
                                               FROM     T_WF_CALENDAR tc
                                               WHERE    tc.FID = T.FCALENDARID
                                                        AND tc.FFORBIDSTATUS = 'A' )
                                )
                            AND EXISTS ( SELECT 1
                                         FROM   T_WF_PROCINST C
                                         WHERE  B.FPROCINSTID = C.FPROCINSTID
                                                AND C.FSTATUS IN ( '0', '2' ) )
                            AND ( ISNULL(FDEALDATEHOURS, 0) --当天提交单据时剩余工作小时数（比如当天工作8小时，8点上班，9点提交单据，那么剩余工作小时是7小时）
                                  + ( SELECT    ISNULL(SUM(FTEMPCOSTHOURS), 0)
                                      FROM      T_WF_CALENDAR
                                      WHERE     FID = T.FCALENDARID
                                    )     -- 截止当前当天已耗工时（比如当天工作8小时，8点上班，10点的时候去查询是否超时，那么当前已使用工作小时数是2小时）
                                  - ( CASE WHEN DATEDIFF(DD, FDEALTIME,
                                                         GETDATE()) <= 0
                                           THEN ( SELECT    ISNULL(SUM(FWORKHOURS),
                                                              0)
                                                  FROM      T_WF_CALENDARDATA
                                                  WHERE     FID = T.FCALENDARID
                                                            AND FISWORKTIME = '1'
                                                            AND DATEDIFF(DD,
                                                              FDAY, GETDATE()) = 0
                                                )
                                           ELSE 0
                                      END-- 如果提交日期和当前查询日期是同一天，还需要减去当天工作小时数（按上述例子，当天工作8小时，7小时+2小时-8小时=1小时，即得出已耗工作时间）
                                           )
                                  + ( SELECT    ISNULL(SUM(FWORKHOURS), 0)
                                      FROM      T_WF_CALENDARDATA
                                      WHERE     FID = T.FCALENDARID
                                                AND FISWORKTIME = '1'
                                                AND FDAY > FDEALTIME
                                                AND FDAY <= GETDATE() - 1
                                    )-- 如果提交日期和当前查询日期不是同一天，还需要加上期间所有工作小时数，即得出已耗工作时间
                                  ) * 1.0 / 24 > ( 1.0 * T.FTIMEOUTHOURS / 24
                                                   + 1.0 * T.FTIMEOUTMINUTE
                                                   / 60 / 24 )
                     UNION
                     SELECT T.FAPPROVALASSIGNID
                     FROM   T_WF_APPROVALASSIGN T
                            INNER JOIN T_WF_ASSIGN B ON B.FASSIGNID = T.FASSIGNID
                     WHERE  B.FSTATUS = '0'
                            AND T.FTIMEOUTDEAL = '1'
                            AND T.FDEALTIME < GETDATE() - ( 1.0
                                                            * T.FTIMEOUTHOURS
                                                            / 24 + 1.0
                                                            * T.FTIMEOUTMINUTE
                                                            / 60 / 24 )
                            AND EXISTS ( SELECT 1
                                         FROM   T_WF_PROCINST C
                                         WHERE  B.FPROCINSTID = C.FPROCINSTID
                                                AND C.FSTATUS IN ( '0', '2' ) )
                            AND ( T.FCALENDARID <= 0
                                  OR EXISTS ( SELECT    1
                                              FROM      T_WF_CALENDAR tc
                                              WHERE     tc.FID = T.FCALENDARID
                                                        AND tc.FFORBIDSTATUS = 'B' )
                                )
                   ) U ON U.FAPPROVALASSIGNID = A.FAPPROVALASSIGNID

---------------------------------------------------------------------------------------------------------

【金蝶云星空BOS二次开发案例演示】https://vip.kingdee.com/article/94751030918525696