【泛微E9】查询流程当前所在节点，以及节点的ID

workflow_flownode（流程节点对应表）
workflow_nodebase（工作流节点基本信息表）
workflow_requestbase （工作流请求基本信息表）

-- 通过流程ID查找对应流程节点ID
SELECT workflowid,nodeid,nodename 
FROM workflow_flownode A LEFT JOIN workflow_nodebase B ON A.nodeid=B.id WHERE workflowid='383'

SELECT * FROM workflow_flownode

SELECT * FROM workflow_nodebase

SELECT currentnodetype FROM workflow_requestbase WHERE REQUESTID = 271803;

-- 查询流程当前所在节点，以及节点的ID
SELECT 
  R.requestid,
  R.WORKFLOWID AS 工作流ID,
  R.LASTNODEID AS 最后操作节点ID,
  last_node.nodename AS 最后操作节点名称,
  R.currentnodeid AS 当前节点ID,
  current_node.nodename AS 当前节点名称
FROM workflow_requestbase R   
LEFT JOIN workflow_flownode F_last ON R.WORKFLOWID = F_last.workflowid AND R.LASTNODEID = F_last.nodeid
LEFT JOIN workflow_nodebase last_node ON F_last.nodeid = last_node.id
LEFT JOIN workflow_flownode F_current ON R.WORKFLOWID = F_current.workflowid AND R.currentnodeid = F_current.nodeid
LEFT JOIN workflow_nodebase current_node ON F_current.nodeid = current_node.id
WHERE R.REQUESTID = 271773;



-- 节点耗时查询视图
CREATE VIEW view_ProcessEfficiency AS
SELECT
 ROW_NUMBER() OVER (ORDER BY a.workflowname DESC) AS PrimaryKey,
 a.workflowname AS "流程名称",
 b.typename AS "流程类型",
 d.REQUESTID AS "流程ID",
 d.REQUESTNAMENEW AS "流程标题",
 e.nodename AS "当前节点",
 c.NODEID AS "当前操作节点ID",
CASE
  d.CURRENTNODETYPE 
  WHEN 0 THEN '创建' 
  WHEN 1 THEN '批准' 
  WHEN 2 THEN '提交' 
  WHEN 3 THEN '归档' 
  ELSE NULL 
 END AS "当前节点类型",
 d.CREATER AS "创建人ID",
 f.lastname AS "创建人",
 d.CREATEDATE AS "创建日期",
 d.CREATETIME AS "创建时间",
 d.LASTOPERATOR AS "最后操作人ID",
 g.lastname AS "最后操作人",
 d.LASTOPERATEDATE AS "最后操作日期",
 d.LASTOPERATETIME AS "最后操作时间",
CASE
  WHEN d.LASTOPERATEDATE IS NOT NULL THEN
  TIMESTAMPDIFF(
   MINUTE,
   CAST(CONCAT(d.CREATEDATE, ' ', d.CREATETIME) AS DATETIME),
   CAST(CONCAT(d.LASTOPERATEDATE, ' ', d.LASTOPERATETIME) AS DATETIME)
  ) 
  ELSE NULL 
 END AS "流程时效(分钟)",
 e.NODENAME AS "节点名称",
 c.USERID AS "节点操作人ID",
 h.lastname AS "节点操作人",
 c.receivedate AS "节点到达日期",
 c.receivetime AS "节点到达时间",
 c.operatedate AS "节点操作日期",
 c.operatetime AS "节点操作时间",
CASE
  WHEN c.operatedate IS NOT NULL THEN
  TIMESTAMPDIFF(
   MINUTE,
   CAST(CONCAT(c.receivedate, ' ', c.receivetime) AS DATETIME),
   CAST(CONCAT(c.operatedate, ' ', c.operatetime) AS DATETIME)
  ) 
  ELSE NULL 
 END AS "节点时效(分钟)" 
FROM
 workflow_base a
 JOIN workflow_type b ON a.WORKFLOWTYPE = b.id
 JOIN workflow_requestbase d ON a.ID = d.WORKFLOWID
 JOIN workflow_currentoperator c ON d.REQUESTID = c.REQUESTID
 JOIN workflow_nodebase e ON c.NODEID = e.ID
 JOIN hrmresource f ON d.CREATER = f.id
 JOIN hrmresource g ON d.LASTOPERATOR = g.id
 JOIN hrmresource h ON c.USERID = h.id
WHERE d.WORKFLOWID IN (91,300,367,370);


-- 查询流程在某一结点的被退回次数
SELECT COUNT(*) AS 退回次数
FROM workflow_requestoperatelog 
WHERE requestid = ？ AND NODEID = ？ AND OPERATECODE = 2;

-- 查询流程的处理人和处理日期时间
SELECT requestid,OPERATORID,CAST(CONCAT(OPERATEDATE,' ',OPERATETIME) AS DATETIME) AS 处理日期时间, nodeid, OPERATECODE from workflow_requestoperatelog 
where requestid = '${requestid}'  AND OPERATECODE = 1
-- AND NODEID = ?