泛微OA E9开发总结

1、查询流程节点及审批为批准的节点

-- 质量管理部A为审批节点名称，logtype='0'为批准，MAX(operatedate)为最后一次审批批准的日期
SELECT requestid,MAX(operatedate) shdate
FROM dbo.ods_ecology_workflow_requestlog 
WHERE logtype='0'  -- and nodeid in (19869,16844)
and nodeid in (SELECT id FROM ods_ecology_workflow_nodebase WHERE nodename='质量管理部A')
group by requestid

2、审批详情

select a.requestid
,a.LOGID
,a.nodeid 操作节点ID
,a.operator 操作者ID
,a.receivedpersonids 接收人ID
,b.lastname 操作者
,a.operatorDept 操作部门ID
,c.departmentname 操作者部门
,a.receivedPersons 接收人
,a.operatedate 操作日期
,a.operatetime 操作时间
,a.operatedate+' '+a.operatetime 操作日期时间
,a.logtype 操作类型ID
,a.remark 审核意见,
case 
   when a.logtype='0' then '批准' 
   when a.logtype='2' then '提交' 
   when a.logtype='3' then '退回' 
   when a.logtype='7' then '转发' 
   when a.logtype='9' then '批注' 
   when a.logtype='t' then '抄送' 
else '' end 操作类型
from workflow_requestlog a 
LEFT JOIN hrmresource b on a.operator=b.id
LEFT JOIN hrmdepartment c on c.id=a.operatorDept 
where a.requestid=401537
order by 操作日期时间 desc

3、创建人首次提交时间与末次提交时间

--logtype = '0' THEN '批准' 
--logtype = '2' THEN '提交' 
--logtype = '3	' THEN '退回' 
--logtype = '7' THEN '转发' 
--logtype = '9' THEN '批注' 
--logtype = 't' THEN '抄送' 
--说明：由于流程ID会随着版本号的发布而变动，所以不能写死workflowid

SELECT requestid,min(operatedate) mindate,MAX(operatedate) maxdate
FROM dbo.ods_ecology_workflow_requestlog 
WHERE logtype='2'
and nodeid in (SELECT id FROM ods_ecology_workflow_nodebase WHERE nodename='创建人')
group by requestid

4、在途OA单

select c.id,c.xmdh031,c.djxh,c.xmdh023,a.xmdg011/100 xmdg011  
from  axmt520Maintable  a 
inner join workflow_requestbase b on b.requestid = a.requestid and b.status != '归档'
inner join axmt520Maintable_dt1 c on c.mainid=a.id

5、查询流程节点审批耗时

-- 方案一(未验证)，方案二已验证可行
SELECT 
    a.workflowid AS 工作流ID,
    a.requestid AS 流程ID,
                c.workflowname AS 单据名称,
                d.requestmark AS 单据编号,
                d.createdate AS 创建日期,
                d.createtime AS 创建时间,
                d.lastoperatedate AS 结束日期,
                d.lastoperatetime AS 结束时间,
                e.lastname AS 创建人,
                b.id,                                                
    b.nodename AS 节点名称,
    a.receivedate AS 接收日期,
    a.receivetime AS 接收时间,
    a.operatedate AS 操作日期,
    a.operatetime AS 操作时间,
    -- 计算耗时（分钟）
    DATEDIFF(MINUTE, 
        CONCAT(a.receivedate, ' ', a.receivetime),
        CONCAT(a.operatedate, ' ', a.operatetime)
    ) AS 耗时分钟,
                a.showorder AS 操作顺序,
                d.requestnamenew AS 单据标题,
                                                                a.*
FROM workflow_currentoperator a
LEFT JOIN workflow_nodebase b ON a.nodeid = b.id
LEFT JOIN workflow_base c ON a.workflowid = c.id
LEFT JOIN workflow_requestbase d ON a.requestid = d.requestid
LEFT JOIN hrmresource e ON d.creater = e.id
WHERE e.subcompanyid1 = 1 -- 过滤公司
AND a.isremark IN ('2','4') -- 过滤已处理节点
AND a.showorder IN ('-1','0','1')
AND a.requestid = '492460' -- 替换具体流程ID
-- AND d.createdate >= '2024-01-01'
-- AND d.createdate <= '2024-12-31'

ORDER BY a.id ASC


-- 方案二

select
scdxh 工单单号
,cpmc 品名 
,t2.shdate as 业务提交时间
,t3.shdate as 业务经理审核时间
,datediff(minute,t2.shdate,t3.shdate)/60.0 as 业务经理耗时
,t4.shdate 工程师A审核时间
,datediff(minute,t3.shdate,t4.shdate)/60.0 as 工程师A耗时
,t5.shdate 菲林审核时间
,datediff(minute,t4.shdate,t5.shdate)/60.0 as 菲林耗时
,t6.shdate 工程师B审核时间
,datediff(minute,t5.shdate,t6.shdate)/60.0 as 工程师B耗时
,t7.shdate 工程主管审核时间
,datediff(minute,t6.shdate,t7.shdate)/60.0 as 工程主管耗时
,t1.djddzbdh
,CHARINDEX('-',lcbh)
,t1.xmbht100
,COALESCE(t1.djddzbdh,t1.xmbht100)
from ods_ecology_formtable_main_115 t1
left join
(
	SELECT requestid,MAX(operatedate+' '+operatetime) shdate
	FROM dbo.ods_ecology_workflow_requestlog 
	WHERE logtype='2'  -- and nodeid in (19869,16844)
	and nodeid in (SELECT id FROM ods_ecology_workflow_nodebase WHERE nodename='发起人')
	group by requestid
) t2 on t2.requestid=t1.requestId
left join
(
	SELECT requestid,MAX(operatedate+' '+operatetime) shdate
	FROM dbo.ods_ecology_workflow_requestlog 
	WHERE logtype='0'
	and nodeid in (SELECT id FROM ods_ecology_workflow_nodebase WHERE nodename='业务经理')
	group by requestid
) t3 on t3.requestid=t1.requestId
-- left join ods_ecology_workflow_currentoperator t2 on t2.requestid=t1.requestId and t2.isremark='2' and t2.nodeid=26232
-- left join ods_ecology_workflow_currentoperator t3 on t3.requestid=t1.requestId and t3.isremark='2' and t3.nodeid=26238
-- left join ods_ecology_workflow_currentoperator t4 on t4.requestid=t1.requestId and t4.isremark='2' and t4.nodeid=26239 and t4.islasttimes=0
left join
(
	SELECT requestid,MAX(operatedate+' '+operatetime) shdate
	FROM dbo.ods_ecology_workflow_requestlog 
	WHERE logtype='0'
	and nodeid in (SELECT id FROM ods_ecology_workflow_nodebase WHERE nodename='规划工程师A')
	group by requestid
) t4 on t4.requestid=t1.requestId
left join
(
	SELECT requestid,MAX(operatedate+' '+operatetime) shdate
	FROM dbo.ods_ecology_workflow_requestlog 
	WHERE logtype='0'
	and nodeid in (SELECT id FROM ods_ecology_workflow_nodebase WHERE nodename='菲林制作师')
	group by requestid
) t5 on t5.requestid=t1.requestId
left join
(
	SELECT requestid,MAX(operatedate+' '+operatetime) shdate
	FROM dbo.ods_ecology_workflow_requestlog 
	WHERE logtype='0'
	and nodeid in (SELECT id FROM ods_ecology_workflow_nodebase WHERE nodename='规划工程师B ')
	group by requestid
) t6 on t6.requestid=t1.requestId
left join
(
	SELECT requestid,MAX(operatedate+' '+operatetime) shdate
	FROM dbo.ods_ecology_workflow_requestlog 
	WHERE logtype='0'
	and nodeid in (SELECT id FROM ods_ecology_workflow_nodebase WHERE nodename='工程主管 ')
	group by requestid
) t7 on t7.requestid=t1.requestId
-- left join dbo.ods_ecology_formtable_main_367 t8 on t8.
where t1.requestId in(492460,487981)

6、查询表字段：308为表名ID

select  
a.billid 单据ID,
a.id 字段ID,
a.fieldname 字段名称,
b.indexdesc 显示名称,
a.fielddbtype 字段类型,
a.detailtable 明细表名称
from workflow_billfield a
left join htmllabelindex b on a.fieldlabel=b.id
where a.billid='-308' and a.detailtable=''