金蝶云星空·常用SQL语句

• 业务流程路线

SELECT INST.FNUMBER,
       LASTASSIGN.FASSIGNID,
       LASTASSIGN_L.FASSIGNNAME,
       LASTASSIGN.FRECEIVERNAMES,
       LASTASSIGN.FSTATUS,
       REC.FRECEIVERID,
       PIB.FOBJECTTYPEID,
       INST.FPROCINSTID,
       INST.FCREATETIME,  -- 流程开始节点的创建时间
       ACTINS.FACTINSTID,
       ACTINS.FCREATETIME,
       ACTINS.FCOMPLETEDTIME,  -- 实例节点的完成时间
       LASTASSIGN.FASSIGNID,
       LASTASSIGN.FCREATETIME,
       LASTASSIGN.FCOMPLETEDTIME,  -- 待办任务完成时间
       WAA.FASSIGNID,  -- 待办任务ID，一个待办任务有多个审批待办任务
       WAA.FAPPROVALASSIGNID,  -- 审批待办任务ID，主键
       WAI.FCOMPLETEDTIME  -- 处理时间
FROM   T_WF_PROCINST INST
       INNER JOIN T_WF_PIBIMAP PIB ON INST.FPROCINSTID = PIB.FPROCINSTID
       LEFT JOIN T_WF_ASSIGN LASTASSIGN ON INST.FPROCINSTID = LASTASSIGN.FPROCINSTID
       LEFT JOIN T_WF_ASSIGN_L LASTASSIGN_L ON LASTASSIGN.FASSIGNID = LASTASSIGN_L.FASSIGNID
       LEFT JOIN T_WF_RECEIVER REC ON LASTASSIGN.FASSIGNID = REC.FASSIGNID
       LEFT JOIN T_WF_ACTINST ACTINS ON ACTINS.FACTINSTID = LASTASSIGN.FACTINSTID
       LEFT JOIN T_WF_APPROVALASSIGN WAA ON WAA.FASSIGNID = LASTASSIGN.FASSIGNID
       LEFT JOIN T_WF_APPROVALITEM WAI ON WAA.FAPPROVALASSIGNID = WAI.FAPPROVALASSIGNID
WHERE  1 = 1
       --AND (INST.FSTATUS = 0 OR INST.FSTATUS = 2) AND LASTASSIGN.FSTATUS = 0
       --AND PIB.FOBJECTTYPEID = 'PUR_Requisition'
       --AND PR.FBILLNO = 'FKSQ016145'
       AND PIB.FKEYVALUE = (SELECT tcp.FID
                            FROM   T_CN_PAYAPPLY tcp
                            WHERE  tcp.FBILLNO = 'FKSQ016145')

• 用户未登录清空

SELECT FUSERID,
       FNAME AS 用户名,
       FDATETIME AS 最后登陆时间,
       DATEDIFF(DAY, '1900-01-01 00:00:00', wdlsc) AS duringdays,
       IIF(Datepart(YEAR, wdlsc) > 1900, CONVERT(NVARCHAR(4), Datepart(YEAR, wdlsc) - 1900) + '年', '')
       + IIF(Datepart(MONTH, wdlsc) > 1, CONVERT(NVARCHAR(2), Datepart(MONTH, wdlsc) - 1) + '月', '')
       + IIF(Datepart(DAY, wdlsc) > 1, CONVERT(NVARCHAR(2), Datepart(DAY, wdlsc) - 1) + '天', '')
       + DateName(HOUR, wdlsc) + '时'
       + DateName(MINUTE, wdlsc) + '分'
       + DateName(SECOND, wdlsc) + '秒' AS 未登录时长
FROM   (SELECT a.FUSERID,
               b.FNAME,
               MAX(a.FDATETIME) AS FDATETIME,
               CONVERT(VARCHAR(21), GETDATE() - MAX(a.FDATETIME), 120) AS wdlsc
        FROM   t_SEC_UserLoginSituation a
               LEFT JOIN T_SEC_USER b ON a.FUSERID = b.FUSERID
        WHERE  b.FFORBIDSTATUS = 'A'
               AND b.FAPPGROUP != ''
        GROUP  BY a.FUSERID,
                  b.FNAME) a
WHERE  tmp.duringdays > 60