用友U8流程审批效率-SQLServer+SSRS

文章目录

• • @[TOC]
  • 1、 需求及效果
  • • 1.1 需求
    • 1.2 效果
  • 2、 思路及SQL语句
  • 3、实现折叠明细表
  • 4、结语

1、 需求及效果

1.1 需求

想要查看U8的审批流程，查看流程在哪个节点或人停留的时间，这个单据整个流程走下来需要的时间。可以更加直观方便的查看审批效率

1.2 效果

采用了SSRS上面的明细表折叠，点击+可以进行展开

2、 思路及SQL语句

1、在U8数据库中审批相关的表有WFAudit和Table_Task。根据这两张表中的内容观察，可以从Table_Task中取待审批的消息，从WFAudit中取其他的内容
2、VoucherType的含义： 01为销售发货单、0301为其他入库单、0302为其他出库单、0304为调拨单、0305为形态转换单、0324为调拨申请单、0412为材料出库单、17为销售订单、24为采购入库单、26为采购到货单、27为请购单、88为采购订单、AP40为应付付款申请单、BO11为物料清单、BO19为委外商物料清单、MO21为生产订单、om01为委外订单、OM26为委外到货单、PU01为供应商资格审批表、pu03为供应商存货调价单、SA19为客户调价单、SR01为服务单、U870_0003为存货申请
3、Action审批动作的意义：0为提交，1为批准，3为转签，5为撤销，6为不同意（可以退回给任何人），7为弃审，8为重新提交，11为会签给谁、12为会签同意、13为会签不同意
4、有MessageId代表的是在提交人节点
5、Table_Task表中creatime和endtime相同为待操作
6、流程状态、制单人、制单时间、当前审批人需要关联以上23种单据类型（通过单据编号进行关联）
7、VoucherCode一样代表是一个完整的审批过程

/*
U8审批效率
VoucherCode是工单
1.VoucherType的含义： 01为销售发货单、0301为其他入库单、0302为其他出库单、0304为调拨单、0305为形态转换单、0324为调拨申请单
0412为材料出库单、17为销售订单、24为采购入库单、26为采购到货单、27为请购单、88为采购订单、AP40为应付付款申请单
BO11为物料清单、BO19为委外商物料清单、MO21为生产订单、om01为委外订单、OM26为委外到货单、PU01为供应商资格审批表
pu03为供应商存货调价单、SA19为客户调价单、SR01为服务单、U870_0003为存货申请
2.Action审批动作0、1、3、5、6、7、8、11、12、13
0为提交，1为批准，3为转签，5为撤销，6为不同意（可以退回给任何人），7为弃审，8为重新提交，11为会签给谁、12为会签同意、13为会签不同意
会签给别人，是需要会签的人同意才能进入下一步，转签和会签有来源和来自哪里
有MessageId代表的是在提交人节点
关联各个单子对应的iverifystateex：-1是终审不同意，0是未提交，1是已提交，2是终审同意（终审同意就是归档）
3.VoucherCode一样代表是一个完整的审批过程，order by时间则为具体的时间
4.table_task中creatime和endtime相同为待操作
5.cMaker制单人.dcreatesytime制单时间,cCurrentAuditor当前审批人
*/

SELECT *,
DATEDIFF(MINUTE, MIN(节点操作时间) OVER (PARTITION BY 单据类型, 单据编号),
MAX(节点操作时间) OVER (PARTITION BY 单据类型, 单据编号)) AS 流程总耗时 FROM(
SELECT DISTINCT * FROM(
	SELECT 
	CASE WHEN VoucherType='01' THEN '销售发货单'
	  WHEN VoucherType='0301' THEN '其他入库单'
	  WHEN VoucherType='0302' THEN '其他出库单'
	  WHEN VoucherType='0304' THEN '调拨单'
	  WHEN VoucherType='0305' THEN '形态转换单'
	  WHEN VoucherType='0324' THEN '调拨申请单'
	  WHEN VoucherType='0412' THEN '材料出库单'
	  WHEN VoucherType='17' THEN '销售订单'
	  WHEN VoucherType='24' THEN '采购入库单'
	  WHEN VoucherType='26' THEN '采购到货单'
	  WHEN VoucherType='27' THEN '请购单'
	  WHEN VoucherType='88' THEN '采购订单'
	  WHEN VoucherType='AP40' THEN '应付付款申请单'
	  WHEN VoucherType='BO11' THEN '物料清单'
	  WHEN VoucherType='BO19' THEN '委外商物料清单'
	  WHEN VoucherType='MO21' THEN '生产订单'
	  WHEN VoucherType='om01' THEN '委外订单'
	  WHEN VoucherType='OM26' THEN '委外到货单'
	  WHEN VoucherType='PU01' THEN '供应商资格审批表'
	  WHEN VoucherType='pu03' THEN '供应商存货调价单'
	  WHEN VoucherType='SA19' THEN '客户调价单'
	  WHEN VoucherType='SR01' THEN '服务单'
	  WHEN VoucherType='U870_0003' THEN '存货申请'
	  END AS 单据类型,
	 Vouchercode AS 单据编号,
	 OperatorName AS 节点操作人,
	--操作类型
	--0为提交，1为批准，3为转签，5为撤销，6为不同意（可以退回给任何人），7为弃审，8为重新提交，11为会签给谁、12为会签同意、13为会签不同意
	CASE WHEN Action='0' THEN '提交'
		 WHEN Action='1' THEN '同意'
		 WHEN Action='3' THEN '转签'
		 WHEN Action='5' THEN '撤销'
		 WHEN Action='6' THEN '不同意'
		 WHEN Action='7' THEN '弃审'
		 WHEN Action='8' THEN '重新提交'
		 WHEN Action='11' THEN '会签'
		 WHEN Action='12' THEN '会签同意'
		 WHEN Action='13' THEN '会签不同意'
		 END AS 操作类型,
	-- 使用LAG函数获取同一张单子的上一个审批操作时间
    LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS 接收时间,   
	OperationDate AS 节点操作时间,
	DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(OperationDate AS DATETIME) ) AS 节点耗时,
	dbo.CONCAT_ALL_6(
		(CONVERT(NVARCHAR(10),(DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(COALESCE(OperationDate, GETDATE()) AS DATETIME) ))) / 1440), '天',
		(CONVERT(NVARCHAR(10),((DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(COALESCE(OperationDate, GETDATE()) AS DATETIME) ))) % 1440) / 60),'小时',
		(CONVERT(NVARCHAR(10),((DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(COALESCE(OperationDate, GETDATE()) AS DATETIME) ))))% 60) ,'分钟') AS 操作时长,

	CASE 
		WHEN Action<>'0' THEN 
		CASE 
		 WHEN DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(OperationDate AS DATETIME) ) BETWEEN 0 AND 1440 THEN '1天之内'  
    WHEN DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(OperationDate AS DATETIME) ) BETWEEN 1441 AND 4320 THEN '2-3天'  
    WHEN DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(OperationDate AS DATETIME) ) BETWEEN 4321 AND 10080 THEN '4-7天'  
    WHEN DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(OperationDate AS DATETIME) ) BETWEEN 10081 AND 20160 THEN '8-14天'  
    WHEN DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(OperationDate AS DATETIME) ) BETWEEN 20161 AND 43200 THEN '15-30天'  
    WHEN DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(OperationDate AS DATETIME) ) BETWEEN 43201 AND 86400 THEN '31-60天'  
    WHEN DATEDIFF(MINUTE, CAST(LAG(OperationDate) OVER (PARTITION BY VoucherType, VoucherCode ORDER BY OperationDate) AS DATETIME), 
	CAST(OperationDate AS DATETIME) ) BETWEEN 86401 AND 129600 THEN '61-90天'  
    ELSE '90天以上'
	END 
	END AS 标识,
		--是否归档,需要关联所有的表
	--iverifystat：-1是终审不同意，0是未提交，1是已提交，2是终审结束
	CASE 
			--A为销售发货单
		WHEN dbo.WFAudit.VoucherType ='01' THEN 
			CASE WHEN A.iverifystate='-1' THEN '终审不同意'
				 WHEN A.iverifystate='0' THEN '未提交'
				 WHEN A.iverifystate='1' THEN '已提交'
				 WHEN A.iverifystate='2' THEN '终审结束'
				 ELSE 'NULL'
				 END 
			--B为其他入库单
		WHEN dbo.WFAudit.VoucherType='0301' THEN 
			CASE WHEN B.iverifystate='-1' THEN '终审不同意'
				 WHEN B.iverifystate='0' THEN '未提交'
				 WHEN B.iverifystate='1' THEN '已提交'
				 WHEN B.iverifystate='2' THEN '终审结束'
			   ELSE 'NULL'
				 END 
			--C为其他出库单
		WHEN WFAudit.VoucherType='0302' THEN 
			CASE WHEN C.iverifystate='-1' THEN '终审不同意'
				 WHEN C.iverifystate='0' THEN '未提交'
				 WHEN C.iverifystate='1' THEN '已提交'
				 WHEN C.iverifystate='2' THEN '终审结束'
				 ELSE 'NULL'
				 END 
			--VoucherType=0304关联调拨单TransVouch 作为D
		WHEN WFAudit.VoucherType='0304' THEN 
			CASE WHEN D.iverifystate='-1' THEN '终审不同意'
				 WHEN D.iverifystate='0' THEN '未提交'
				 WHEN D.iverifystate='1' THEN '已提交'
				 WHEN D.iverifystate='2' THEN '终审结束'
				 ELSE 'NULL'
				 END 		
			--VoucherType=0305关联形态转换单AssemVouch
		WHEN dbo.WFAudit.VoucherType='0305' THEN
			CASE WHEN E.iverifystate='-1' THEN '终审不同意'
				 WHEN E.iverifystate='0' THEN '未提交'
				 WHEN E.iverifystate='1' THEN '已提交'
				 WHEN E.iverifystate='2' THEN '终审结束'
				 ELSE 'NULL'
				 END 	
			--VoucherType=0324关联调拨申请单ST_AppTransVouch 
		WHEN dbo.WFAudit.VoucherType='0324' THEN 
			CASE WHEN F.iverifystate='-1' THEN '终审不同意'
				 WHEN F.iverifystate='0' THEN '未提交'
				 WHEN F.iverifystate='1' THEN '已提交'
				 WHEN F.iverifystate='2' THEN '终审结束'
				 ELSE 'NULL'
				 END 	
			--VoucherType=0412关联材料出库单rdrecord11 
		WHEN dbo.WFAudit.VoucherType='0412' THEN 
			CASE WHEN G.iverifystate='-1' THEN '终审不同意'
				 WHEN G.iverifystate='0' THEN '未提交'
				 WHEN G.iverifystate='1' THEN '已提交'
				 WHEN G.iverifystate='2' THEN '终审结束'
				 ELSE 'NULL'
				 END 	
			--VoucherType=17关联销售订单SO_SOMain 
		WHEN dbo.WFAudit.VoucherType='17' THEN 
			CASE WHEN H.iverifystate='-1' THEN '终审不同意'
				 WHEN H.iverifystate='0' THEN '未提交'
				 WHEN H.iverifystate='1' THEN '已提交'
				 WHEN H.iverifystate='2' THEN '终审结束'
				 ELSE 'NULL'
				 END 	
		--VoucherType=24关联采购入库单RdRecord01