您的位置: 首页  >  文章  >  在线审批流实现

在线审批流实现

分类: 文章 • 2024-01-22 17:02:04

平时的请假、合同申请、采购单申请、发货单申请等流程如果均可实现在线审批，将会很大程度上节省资源，下面就是一个实现流程。

一、审批流数据模型设计（使用power designer）

在线审批流实现

二、实现的存储过程

审批通过功能：

-- =============================================
-- Notes:        流程审批--通过
-- =============================================
Create proc [dbo].[WorkFlowToDoAudit]
@WTDId int,
@UId int,
@Operation int,
@OperationWord nvarchar(200)
as 
begin
	declare @sql NVARCHAR(1000),@TableName nvarchar(100),@TableField NVARCHAR(100),@FormId int,@TableIdName NVARCHAR(50),@WFId int,@WFNId int,@WFATId int,@WFARId int,@NextNodeId int,@WFNName nvarchar(50),@YWUId int
	select @WFId=WFId,@WFNId=CurrentNodeId,@WFNName=CurrentNodeName,@TableName=TableName,@FormId=FormId,@YWUId=[UId] from WorkToDo where [email protected]
	select @WFARId=WFARId,@WFATId=WFATId from WorkFlowNode where [email protected]
	--获取表单的id名称,获取表单条件字段名称
	select @TableIdName=TableIdName,@TableField=TableField from WorkFlowForm where [TabelName][email protected]
	declare @Count int
	---开始一个事务
	begin transaction T
	[email protected]检测是否插入成功的变量
	set @Count=0
	--写入审批词
	update WorkNodeApprover set [email protected],[email protected],AddDate=getdate() where [email protected] and [email protected] and [email protected]
	set @[email protected][email protected]@error
	----添加抄送人
	--select count(1) from WorkViewUser where [email protected] and [email protected]
	--insert into WorkViewUser (WTDId,[UId],UName)
	--select @WTDId,Id,name from users where department>0 and charindex(','+ltrim(Id)+',',','[email protected]+',')>0
	--set @[email protected][email protected]@error
	--添加工作流程节点（日志）
	insert into WorkToDoNode values(@WTDId,@WFNId,@WFNName,@WFATId,@WFARId,@UId,@Operation)
	set @[email protected][email protected]@error
	if(@WFARId=1)
	begin
		--其他审批人状态为3，即表示当前结点别人已审批通过
		update WorkNodeApprover set Operation=3 where [email protected] and [email protected] and Operation=0
		set @[email protected][email protected]@error
		--查询下一个节点
		exec WorkFlowGetNextNote @WFId,@WFNId,@TableName,@TableIdName,@FormId,@NextNodeId output
		if(@NextNodeId>0)
		begin
			exec WorkFlowNextAdd @WFId,@WTDId,@NextNodeId,@YWUId,@Count out
		end
		else--结束
		begin
			update WorkToDo set WTDStatus=4,CurrentNodeId=0,CurrentNodeName='结束' where [email protected]
			set @[email protected][email protected]@error
		end
	end
	else
	begin
		declare @NoApprCount int
		set @NoApprCount=0
		select @NoApprCount=COUNT(0) FROM WorkNodeApprover WHERE [email protected] AND Operation=0
		if(@NoApprCount=0)
		begin
			--查询下一个节点
			exec WorkFlowGetNextNote @WFId,@WFNId,@TableName,@TableIdName,@FormId,@NextNodeId output
			if(@NextNodeId>0)
				exec WorkFlowNextAdd @WFId,@WTDId,@NextNodeId,@YWUId,@Count out
			else--结束
				update WorkToDo set WTDStatus=4,CurrentNodeId=0,CurrentNodeName='结束' where [email protected]
		end
		else
			update WorkToDo set WTDStatus=2 where [email protected]
	end
	if(@Count>0)
		RollBack transaction T
	else
		commit transaction T
end
-- =============================================
-- Notes:        根据条件查询某个流程的下一个节点
-- =============================================
ALTER proc [dbo].[WorkFlowGetNextNote]
@WFId int,
@NodeId int,
@TableName nvarchar(100),
@TableIdName NVARCHAR(50),
@FormId int,
@JumpNode1 int out
as
begin
	declare @sql NVARCHAR(1000),@ConditionSign NVARCHAR(50),@ConditionValue NVARCHAR(50),@JumpNode int,@TiaoJian nvarchar(50),@TableField NVARCHAR(100),@DataVal NVARCHAR(100),@CondCount int,@Flag int,@NodeIndex int
	set @JumpNode1=0
	set @Flag=0
	select @CondCount=COUNT(0) from WorkFlowCondition where [email protected]
	if(@CondCount>0)
	begin
		declare cursor1 cursor for
		select ConditionSign,ConditionValue,JumpNode,TiaoJian,TableField from WorkFlowCondition where [email protected] and [email protected] order by OrderID asc
		open cursor1                       
			fetch next from cursor1 into @ConditionSign,@ConditionValue,@JumpNode,@TiaoJian,@TableField
			while @@fetch_status=0
			begin
				--获取对应的表对应字段的值@DataVal
				set @sql='select @DataVal='[email protected]+' FROM '[email protected]+' where '[email protected]+'='+cast(@FormId as NVARCHAR(50))
				exec sp_executesql @sql,N'@DataVal NVARCHAR(100) out',@DataVal out
				select @NodeIndex=CHARINDEX(',',NextNodes) from WorkFlowNode where [email protected] and IsDelete=0
				if(@ConditionSign='>' and cast(@DataVal as int)>cast(@ConditionValue as int))
				begin
					set @[email protected]+1
					if(@NodeIndex>0)--多个跳转节点
						break
				end
				else if(@ConditionSign='>=' and cast(@DataVal as int)>=cast(@ConditionValue as int))
				begin
					set @[email protected]+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='<' and cast(@DataVal as int)<cast(@ConditionValue as int))
				begin
					set @[email protected]+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='<=' and cast(@DataVal as int)<=cast(@ConditionValue as int))
				begin
					set @[email protected]+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='=' and cast(@DataVal as int)=cast(@ConditionValue as int))
				begin
					set @[email protected]+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='!=' and cast(@DataVal as int)!=cast(@ConditionValue as int))
				begin
					set @[email protected]+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='包含' and CHARINDEX(cast(@ConditionValue as nvarchar(50)),cast(@DataVal as nvarchar(50)))>-1)
				begin
					set @[email protected]+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='不包含' and CHARINDEX(cast(@ConditionValue as nvarchar(50)),cast(@DataVal as nvarchar(50)))<1)
				begin
					set @[email protected]+1
					if(@NodeIndex>0)
						break
				end
				fetch next from cursor1 into @ConditionSign,@ConditionValue,@JumpNode,@TiaoJian,@TableField
			end
		close cursor1  
		deallocate cursor1
		if(@TiaoJian='AND' and @[email protected])
			set @[email protected]
		else if(@TiaoJian='OR' and @Flag>0)
			set @[email protected]
	end
	else
	begin
		select @JumpNode1=WFNId from WorkFlowNode where [email protected] and NodeNum=cast((select NextNodes from WorkFlowNode where [email protected] and IsDelete=0) as int)
	end	
end

 

-- =============================================
-- Notes:        跳到下一节点时插入流程相关表
-- =============================================
ALTER PROC [dbo].[WorkFlowNextAdd]
@WFId int,
@WTDId int,
@NextNodeId int,
@YWUId int,
@Count int out
as
begin
	declare @NextNodeName nvarchar(50),@WFARId int,@WFATId int,@Agent nvarchar(max),@YWDeptId int,@AgentNames nvarchar(max)
	select @NextNodeName=WFNName,@WFARId=WFARId,@WFATId=WFATId,@Agent=Agent,@AgentNames=AgentNames from WorkFlowNode where [email protected]
	set @YWDeptId=dbo.GetDeptIdByUId(@YWUId)
	--查询添加节点审批人
	if(@WFATId=1)--角色
	begin
		if(charindex('业务组长',@AgentNames)>0)
		begin
			insert into WorkNodeApprover (WFId,WFNId,WFNName,WTDId,UId,UName,Operation,AddDate)
			select @WFId,@NextNodeId,@NextNodeName,@WTDId,id,name,0,getdate() from dbo.users a left join dbo.CRM_UserRole b on a.id=b.[UID] where a.department>0 and charindex(','+ltrim(b.RID)+',',','[email protected]+',')>0 and [email protected]
		end
		else
		begin
			insert into WorkNodeApprover (WFId,WFNId,WFNName,WTDId,UId,UName,Operation,AddDate)
			select @WFId,@NextNodeId,@NextNodeName,@WTDId,id,name,0,getdate() from dbo.users a left join dbo.CRM_UserRole b on a.id=b.[UID] where a.department>0 and charindex(','+ltrim(b.RID)+',',','[email protected]+',')>0
		end			
	end
	else if(@WFATId=2)--部门
	begin
		insert into WorkNodeApprover (WFId,WFNId,WFNName,WTDId,UId,UName,Operation,AddDate)
		select @WFId,@NextNodeId,@NextNodeName,@WTDId,id,name,0,getdate() from users where department>0 and charindex(','+ltrim(department)+',',','[email protected]+',')>0	
	end
	else if(@WFATId=3)--人员
	begin
		insert into WorkNodeApprover (WFId,WFNId,WFNName,WTDId,UId,UName,Operation,AddDate)
		select @WFId,@NextNodeId,@NextNodeName,@WTDId,id,name,0,getdate() from users where department>0 and charindex(','+ltrim(id)+',',','[email protected]+',')>0	
	end
	else if(@WFATId=4)--职务
	begin
		insert into WorkNodeApprover (WFId,WFNId,WFNName,WTDId,UId,UName,Operation,AddDate)
		select @WFId,@NextNodeId,@NextNodeName,@WTDId,id,name,0,getdate() from users where department>0 and charindex(','+ltrim(duty)+',',','[email protected]+',')>0	
	end
	set @[email protected][email protected]@error
	update WorkToDo set [email protected],[email protected] where [email protected]
	set @[email protected][email protected]@error
end

审批不通过功能：

-- =============================================
-- Notes:        流程审批--驳回
-- =============================================
ALTER proc [dbo].[WorkFlowToDoNotThrough]
@WTDId int,
@UId int,
@OperationWord nvarchar(200)
as 
begin
	declare @Count int,@WFNId int,@WFNName nvarchar(50),@WFATId int,@WFARId int
	select @WFNId=CurrentNodeId,@WFNName=CurrentNodeName from WorkToDo where [email protected]
	select @WFATId=WFATId,@WFARId=WFARId from WorkFlowNode where [email protected]
	---开始一个事务
	begin transaction T
	[email protected]检测是否插入成功的变量
	set @Count=0
	update WorkNodeApprover set Operation=2,[email protected],AddDate=getdate() where [email protected] and [email protected] and [email protected] and Operation=0
	set @[email protected][email protected]@error
	update WorkNodeApprover set Operation=3 where [email protected] and [email protected] and Operation=0
	set @[email protected][email protected]@error
	insert into WorkToDoNode values(@WTDId,@WFNId,@WFNName,@WFATId,@WFARId,@UId,2)
	set @[email protected][email protected]@error
	update WorkToDo set WTDStatus=5 where [email protected]
	set @[email protected][email protected]@error
	if(@Count>0)
		RollBack transaction T
	else
		commit transaction T
end

当前工作所走的流程图例：

-- =============================================
-- Notes:        查询某个流程的节点列表
-- =============================================
ALTER proc [dbo].[WorkFlowGetNodeList]
@WTDId int,
@NodeList nvarchar(500) out
as 
begin
	declare @TableName nvarchar(100),@TableField NVARCHAR(100),@WFNId int,@JumpNode1 int,@returnVal nvarchar(500),@NodeId int,@WFId int,@TableIdName NVARCHAR(50),@FormId int
	set @JumpNode1=0	
	--获取工作文档内容	
	select @TableName=TableName,@FormId=FormId,@WFId=WFId from WorkToDo where [email protected]
	--获取审批流程的第一个节点
	select top 1 @NodeId=WFNId from WorkFlowNode where [email protected] order by NodeNum asc
	--获取表单的id名称,获取表单条件字段名称
	select @TableIdName=TableIdName,@TableField=TableField from WorkFlowForm where [TabelName][email protected]
	exec WorkFlowGetNotes @WFId,@NodeId,@TableName,@TableIdName,@FormId,@JumpNode1,@returnVal output
	set @[email protected]
	print @NodeList
end
-- =============================================
-- Notes:        根据条件查询某个流程的流转节点（递归）
-- =============================================
ALTER proc [dbo].[WorkFlowGetNotes]
@WFId int,
@NodeId int,
@TableName nvarchar(100),
@TableIdName NVARCHAR(50),
@FormId int,
@JumpNode1 int,
@returnVal nvarchar(500) output
as
begin
	exec WorkFlowGetNextNote @WFId,@NodeId,@TableName,@TableIdName,@FormId,@JumpNode1 output
	declare @WFNName nvarchar(50)	
    if(@returnVal is null)
    begin
		select @WFNName=WFNName from WorkFlowNode where [email protected]
		set @[email protected]
	end
	if(@JumpNode1>0)
	begin
		select @WFNName=WFNName from WorkFlowNode where [email protected]
		set @[email protected]+' → '[email protected]
		exec WorkFlowGetNotes @WFId,@JumpNode1,@TableName,@TableIdName,@FormId,@JumpNode1,@returnVal output
	end
end

工作流程表触发器：

-- =============================================
-- Notes:        流程审批完成触发器
-- =============================================
ALTER trigger [dbo].[WorkToDo_trg]
on [dbo].[WorkToDo]
after update 
as 
begin
	declare @Id int,@Status int,@TableName nvarchar(200)
	select @Status=WTDStatus,@TableName=TableName,@Id=FormId from inserted
	if(@Status=4)--完成
	begin
		if(@TableName='Agreement')
			update Agreement set CState=2 where [email protected]
		else if(@TableName='Leave')
			update Leave set IsApprove=1 where [email protected]
	end
	else if(@Status=5)--退回
	begin
		if(@TableName='Agreement')--合同
		begin
			update Agreement set CState=3 where [email protected]
		end
		else if(@TableName='PurchaseOrder')--采购单
		begin
			update PurchaseOrder set OrderState=7 where [email protected]
		end
		else if(@TableName='DeliveryInformation')--发货单
		begin
			update DeliveryInformation set [State]=0 where [email protected]
		end
		else if(@TableName='Leave')--请假
			update Leave set IsApprove=2 where [email protected]
	end
end

三、相关界面

在线审批流实现

在线审批流实现

 在线审批流实现

 

 

相关推荐