泛微OA_Ecology9查询节点操作耗时时间、以及OA所有路径类型下的流程

OA查询节点接受时间、耗时以及操作时间的sql语句

• 涉及到的数据库表有：

1. workflow_nodebase：工作流节点基本信息表
2. workflow_requestlog：工作流请求签字日志表
3. workflow_requestbase：工作流请求基本信息表
4. workflow_base：工作流基本信息表
5. workflow_node_fix_flowtime：归档流程节点耗时
6. workflow_node_flowtime：未归档流程节点耗时
7. workflow_request_fix_flowtime：归档流程耗时记录（查询的是流程总的耗时时长）
   字段：flowtime（单位秒）耗时的单位是秒

--归档流程耗时记录 
select a.requestid '请求id',a.workflowid '流程id',a.creator '创建人id',b.lastname '创建人名称',a.createdate '创建日期',
a.createtime '创建时间',a.lastoperatedate '最后操作日期',a.currentnodeid '当前节点id',a.flowtime '流程耗时',a.status '流程专题图' 
from workflow_request_fix_flowtime a left join hrmresource b on a.creator = b.id
where a.workflowid = '#{对应的流程id}' and a.requestid = '#{对应的请求id}'

--未归档流程节点耗时
select * from (
  select a.requestid '请求id',a.workflowid '流程id',a.nodeid '节点id',c.nodename '节点名称',a.nodeoperator '操作人id',
  b.lastname '操作人名称',a.createdate '流程创建日期',a.receivedate '接受日期',a.receivetime '接受时间',a.flowtime/3600 '耗时',
  DATEADD(ss,a.flowtime,CONVERT(DATETIME, CONVERT(VARCHAR, a.receivedate) + ' ' + CONVERT(VARCHAR, a.receivetime))) '操作日期时间'
  from workflow_node_flowtime a left join hrmresource b on a.nodeoperator = b.id
  left join workflow_nodebase c on a.nodeid = c.id
  --去除申请节点、抛单节点、归档节点
  where a.workflowid = '#{对应的流程id}' and a.nodeid not in ('18589','18592','18593') 
  union all
  --归档流程节点耗时
  select a.requestid '请求id',a.workflowid '流程id',a.nodeid '节点id',c.nodename '节点名称',a.nodeoperator '操作人id',
  b.lastname '操作人名称',a.createdate '流程创建日期',a.receivedate '接受日期',a.receivetime '接受时间',a.flowtime/3600 '耗时',
  DATEADD(ss,a.flowtime,CONVERT(DATETIME, CONVERT(VARCHAR, a.receivedate) + ' ' + CONVERT(VARCHAR, a.receivetime))) '操作日期时间'
  from workflow_node_fix_flowtime a left join hrmresource b on a.nodeoperator = b.id
  left join workflow_nodebase c on a.nodeid = c.id
  --去除申请节点、抛单节点、归档节点、如果不需要排除直接删掉即可
  where a.workflowid = '#{对应的流程id}' and a.nodeid not in ('#{对应的节点id}') --and a.requestid = '2021473'
) aa order by aa.请求id,aa.节点id,aa.耗时

select * from workflow_base where id = '2016'

select * from workflow_requestbase where workflowid = '2016' and requestid = '1970672'

select * from workflow_requestlog where  workflowid = '2016' and requestid = '1970672'

select * from workflow_nodebase 

sql日期时间相关操作

SELECT CONVERT(DATETIME, CONVERT(VARCHAR, '2024-02-23') + ' ' + CONVERT(VARCHAR, '19:58:09')) AS datetime_field

--sql加秒数
DATEADD(ss,a.flowtime,CONVERT(DATETIME, CONVERT(VARCHAR, a.receivedate) + ' ' + CONVERT(VARCHAR, a.receivetime))) '操作日期时间'

--sql取上个月的第一天
select convert(varchar(10),dateadd(month,1,concat(FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyy-MM'),'-01')),120)
--sql取下个月的第一天
select concat(FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyy-MM'), '%')
--sql取上周周一
select FORMAT(DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) - 1, 0), 'yyyy-MM-dd')
--sql取上周周日
select FORMAT(DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) - 1, 0)), 'yyyy-MM-dd')

查询OA流程流转的平均耗时

-未归档流程节点耗时
SELECT 操作人id,操作人名称,COUNT(1) 处理流程数量,SUM(耗时) 总耗时,SUM(耗时)/COUNT(1) 平均耗时
FROM
        (
        SELECT
                a.requestid '请求id',
                a.workflowid '流程id',
                a.nodeid '节点id',
                c.nodename '节点名称',
                a.nodeoperator '操作人id',
                b.lastname '操作人名称',
                a.createdate '流程创建日期',
                a.receivedate '接受日期',
                a.receivetime '接受时间',
                a.flowtime/ 3600 '耗时',
                DATEADD(
                        ss,
                        a.flowtime,
                        CONVERT ( DATETIME, CONVERT ( VARCHAR, a.receivedate ) + ' ' + CONVERT ( VARCHAR, a.receivetime ) ) 
                ) '操作日期时间' --                 a.isremark,
--                 a.viewdate,
--                 a.viewtime,
--                 a.preisremark
                
        FROM
                workflow_node_flowtime a
                LEFT JOIN hrmresource b ON a.nodeoperator = b.id
                LEFT JOIN workflow_nodebase c ON a.nodeid = c.id --去除申请节点、抛单节点、归档节点

--where a.workflowid = '1504' and a.nodeid not in ('14300','14302') and a.createdate >= '2024-01-01'

                
        where a.workflowid = '1881' and a.nodeid not in ('17364','17368','18357') and a.createdate >= '2024-01-01' and a.isremark != 0
        UNION ALL--归档流程节点耗时
        SELECT
                a.requestid '请求id',
                a.workflowid '流程id',
                a.nodeid '节点id',
                c.nodename '节点名称',
                a.nodeoperator '操作人id',
                b.lastname '操作人名称',
                a.createdate '流程创建日期',
                a.receivedate '接受日期',
                a.receivetime '接受时间',
                a.flowtime/ 3600 '耗时',
                DATEADD(
                        ss,
                        a.flowtime,
                        CONVERT ( DATETIME, CONVERT ( VARCHAR, a.receivedate ) + ' ' + CONVERT ( VARCHAR, a.receivetime ) ) 
                ) '操作日期时间' --                 a.isremark,
--                 a.viewdate,
--                 a.viewtime,
--                 a.preisremark
                
        FROM
                workflow_node_fix_flowtime a
                LEFT JOIN hrmresource b ON a.nodeoperator = b.id
                LEFT JOIN workflow_nodebase c ON a.nodeid = c.id --去除申请节点、抛单节点、归档节点

--where a.workflowid = '1504' and a.nodeid not in ('14300','14302') and a.createdate >= '2024-01-01'--and a.requestid = '2021473'

                
        where a.workflowid = '1881' and a.nodeid not in ('17364','17368','18357') and a.createdate >= '2024-01-01' and a.isremark != 0
        ) aa where aa.操作人id in (271,272,631,44,6593,631,611,631,631,631)
        GROUP BY 操作人id,操作人名称
        ORDER BY--aa.请求id,aa.节点id,
        平均耗时 DESC

查询OA流程所有路径类型下的流程

表:
workflow_base
workflow_type

sql语句
select a.id,a.typename ,b.id,b.workflowname,b.activeversionid from workflow_type a left join workflow_base b on a.id = b.workflowtype 
where b.isvalid = '1' --and b.activeversionid = b.id 
and a.id != '39' order by b.workflowname 
--order by a.id --and b.workflowname not like ''