ecology 常用查询

1.查询节点信息

SELECT
	b.nodename,
	a.* 
FROM
	workflow_flownode a,
	workflow_nodebase b 
WHERE
	a.nodeid= b.id 
	AND a.workflowid= 170;

2.获取主表的自定义下拉框选项

SELECT
	selectname 
FROM
	workflow_SelectItem 
WHERE
	fieldid IN ( SELECT id FROM workflow_billfield WHERE billid = ( SELECT id FROM workflow_bill WHERE tablename = 'uf_xxx_dict' ) AND fieldname = 'xt' )

获取明细表的下拉框选项

SELECT
	selectname 
FROM
	workflow_SelectItem 
WHERE
	fieldid IN ( SELECT id FROM workflow_billfield WHERE billid = ( SELECT id FROM workflow_bill WHERE tablename = 'formtable_main_227' ) AND fieldname = 'bxlbgl' )

SELECT 
	selectname 
FROM
	workflow_SelectItem 
WHERE
	fieldid IN ( SELECT id FROM workflow_billfield WHERE billid = ( SELECT billid FROM Workflow_billdetailtable WHERE tablename = 'formtable_main_227_dt1' ) AND fieldname = 'bxlbgl' )
	

3.人员卡片字段定义中增加的自定义字段，与hrmresource关联

cus_fielddata cus_fielddata.id=hrmresource.id

4.查询未归档流程

SELECT
    A.REQUESTID '请求ID',
    A.REQUESTNAME '请求标题',
    NODE.NODENAME '当前节点名称',
    (CASE WHEN A.CREATER=1 THEN '系统管理员' ELSE RES.LASTNAME END) '创建人姓名',
    A.CREATEDATE + ' ' + A.CREATETIME '创建时间',
    CR.LASTNAME '接收人姓名',
    CR.RECEIVEDATE + ' ' + CR.RECEIVETIME '接收时间',
    BASE.WORKFLOWNAME '工作流名称',
    A.STATUS '出口名称',
    (CASE WHEN A.CURRENTNODETYPE=0 THEN '创建节点' WHEN A.CURRENTNODETYPE=1 THEN '审批节点' WHEN A.CURRENTNODETYPE=2 THEN '处理节点' ELSE '其他节点' END) '节点类型'
FROM WORKFLOW_REQUESTBASE A
INNER JOIN WORKFLOW_NODEBASE NODE ON NODE.ID=A.CURRENTNODEID
LEFT JOIN HRMRESOURCE RES ON RES.ID=A.CREATER
INNER JOIN WORKFLOW_BASE BASE ON BASE.ID=A.WORKFLOWID
INNER JOIN (SELECT REQUESTID, R1.LASTNAME, RECEIVEDATE, RECEIVETIME FROM WORKFLOW_CURRENTOPERATOR C1
INNER JOIN HRMRESOURCE R1 ON R1.ID=C1.USERID
WHERE C1.ID IN (SELECT MAX(ID) FROM WORKFLOW_CURRENTOPERATOR GROUP BY REQUESTID)) CR ON CR.REQUESTID=A.REQUESTID
WHERE CURRENTNODETYPE != 3
AND (
    (
        EXISTS (
            SELECT 1
            FROM WORKFLOW_BASE WB
            WHERE A.WORKFLOWID = WB.ID
            AND WB.ISVALID IN ('0', '1', '2', '3')
            AND WB.ID IN (
                SELECT ID
                FROM WORKFLOW_BASE WORKBASE, WORKFLOW_VERSIONINFO WORKVERSION
                WHERE WORKBASE.ID = WORKVERSION.WFID
                AND WFVERSIONID IN (
                    SELECT WORKFLOWID FROM WORKFLOW_MONITOR_DETAIL WHERE INFOID = 0
                )
            )
        )
    )
)
AND A.DELETED < 1
ORDER BY A.REQUESTID DESC

5.查询流程各个节点审批时间

---流程耗时统计:节点审批周期
select wc.requestid as 请求id,
      (select requestname 
          from workflow_requestbase wr 
          where wr.requestid = wc.requestid ) as 请求标题,
          
        (select createdate+' '+ createtime
          from workflow_requestbase wr2 
          where wr2.requestid = wc.requestid ) as 流程创建时间,
					(select lastname from HrmResource where id = userid) as 操作者姓名,
        nodeid as 节点id,
         isremark 操作类型id,
         case isremark when  0 then '未操作'
                       when  1 then '转发'
                       when  2 then '已操作'
                       when  4 then '归档'
                       when  5 then '超时'
                       when  8 then '抄送(不需提交) '
                       when  9 then '抄送(需提交) '
                       when  'a' then '意见征询'
                       when  'b' then '回复'
                       when  'h' then '转办'
                       when  'j' then '转办提交'
          end as 操作类型,
         
        (select nodename 
             from workflow_nodebase 
               where id = nodeid ) as 节点名称,
							 userid as 操作者id,
receivedate+ ' '+receivetime as 接收时间,
operatedate+' '+ operatetime  as 操作时间,
  CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime  ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + 
  CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + 
  CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + 
  CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) % 60 AS INT ) AS VARCHAR ) + '秒' 操作耗时分钟

from workflow_currentoperator wc  
where wc.requestid = 229270
order by receivedate+ ' '+receivetime  ,

  CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime  ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + 
  CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + 
  CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + 
  CAST ( CAST ( DATEDIFF ( ss, receivedate+ ' '+receivetime, operatedate+' '+ operatetime ) % 60 AS INT ) AS VARCHAR ) + '秒'

6.流程归档时间统计

-- 流程归档时间统计
SELECT LEFT
	( Minoperatetime, 4 ) AS mYear,
	LEFT ( Minoperatetime, 7 ) mYearMonth,
	creater,
	createdate,
	createtime,
	workflow_requestbase.requestid,
	requestmark,
	requestname,
	status,
	Minoperatetime,
	Maxoperatetime,
	CAST ( CAST ( DATEDIFF( ss, Minoperatetime, Maxoperatetime ) / ( 60 * 60 * 24 ) AS INT ) AS VARCHAR ) + '天' + CAST ( CAST ( DATEDIFF( ss, Minoperatetime, Maxoperatetime ) % 86400 / 3600 AS INT ) AS VARCHAR ) + '小时' + CAST ( CAST ( DATEDIFF( ss, Minoperatetime, Maxoperatetime ) % 3600 / 60 AS INT ) AS VARCHAR ) + '分' + CAST ( CAST ( DATEDIFF( ss, Minoperatetime, Maxoperatetime ) % 60 AS INT ) AS VARCHAR ) + '秒' LengthOfTime 
FROM
	workflow_requestbase
	LEFT JOIN (
	SELECT
		requestid,
		MIN ( operatedate + ' ' + operatetime ) AS Minoperatetime,
		MAX ( operatedate + ' ' + operatetime ) AS Maxoperatetime 
	FROM
		workflow_requestlog 
	GROUP BY
		requestid 
	) a ON workflow_requestbase.requestid= a.requestid 
WHERE
	workflow_requestbase.currentnodetype = 3 
	AND LEFT ( Minoperatetime, 4 ) >= 2024
and workflow_requestbase.requestid = 229104

7.获取当前节点未操作人

SELECT
	a.creater '发起人ID',
	c.receivedate + ' ' + c.receivetime '接收时间',
	b.nodename '节点名称',
	d.lastname '接收人姓名' 
FROM
	WORKFLOW_REQUESTBASE a
	LEFT JOIN workflow_nodebase b ON a.currentnodeid = b.id
	LEFT JOIN workflow_currentoperator c ON a.requestid = c.requestid 
	AND a.currentnodeid = c.nodeid
	LEFT JOIN HrmResource d ON c.userid = d.id 
WHERE
	a.requestid = '289404'