在线审批流实现

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

一、审批流数据模型设计（使用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 WTDId=@WTDId
	select @WFARId=WFARId,@WFATId=WFATId from WorkFlowNode where WFNId=@WFNId
	--获取表单的id名称,获取表单条件字段名称
	select @TableIdName=TableIdName,@TableField=TableField from WorkFlowForm where [TabelName]=@TableName
	declare @Count int
	---开始一个事务
	begin transaction T
	--@Count检测是否插入成功的变量
	set @Count=0
	--写入审批词
	update WorkNodeApprover set Operation=@Operation,OperationWord=@OperationWord,AddDate=getdate() where WTDId=@WTDId and WFNId=@WFNId and UId=@UId
	set @Count=@Count+@@error
	----添加抄送人
	--select count(1) from WorkViewUser where WTDId=@WTDId and UId=@UId
	--insert into WorkViewUser (WTDId,[UId],UName)
	--select @WTDId,Id,name from users where department>0 and charindex(','+ltrim(Id)+',',','+@ViewUsers+',')>0
	--set @Count=@Count+@@error
	--添加工作流程节点（日志）
	insert into WorkToDoNode values(@WTDId,@WFNId,@WFNName,@WFATId,@WFARId,@UId,@Operation)
	set @Count=@Count+@@error
	if(@WFARId=1)
	begin
		--其他审批人状态为3，即表示当前结点别人已审批通过
		update WorkNodeApprover set Operation=3 where WTDId=@WTDId and WFNId=@WFNId and Operation=0
		set @Count=@Count+@@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 WTDId=@WTDId
			set @Count=@Count+@@error
		end
	end
	else
	begin
		declare @NoApprCount int
		set @NoApprCount=0
		select @NoApprCount=COUNT(0) FROM WorkNodeApprover WHERE WTDId=@WTDId 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 WTDId=@WTDId
		end
		else
			update WorkToDo set WTDStatus=2 where WTDId=@WTDId
	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 WFNId=@NodeId
	if(@CondCount>0)
	begin
		declare cursor1 cursor for
		select ConditionSign,ConditionValue,JumpNode,TiaoJian,TableField from WorkFlowCondition where WFNId=@NodeId and TableName=@TableName 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='+@TableField+' FROM '+@TableName+' where '+@TableIdName+'='+cast(@FormId as NVARCHAR(50))
				exec sp_executesql @sql,N'@DataVal NVARCHAR(100) out',@DataVal out
				select @NodeIndex=CHARINDEX(',',NextNodes) from WorkFlowNode where WFNId=@NodeId and IsDelete=0
				if(@ConditionSign='>' and cast(@DataVal as int)>cast(@ConditionValue as int))
				begin
					set @Flag=@Flag+1
					if(@NodeIndex>0)--多个跳转节点
						break
				end
				else if(@ConditionSign='>=' and cast(@DataVal as int)>=cast(@ConditionValue as int))
				begin
					set @Flag=@Flag+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='<' and cast(@DataVal as int)<cast(@ConditionValue as int))
				begin
					set @Flag=@Flag+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='<=' and cast(@DataVal as int)<=cast(@ConditionValue as int))
				begin
					set @Flag=@Flag+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='=' and cast(@DataVal as int)=cast(@ConditionValue as int))
				begin
					set @Flag=@Flag+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='!=' and cast(@DataVal as int)!=cast(@ConditionValue as int))
				begin
					set @Flag=@Flag+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='包含' and CHARINDEX(cast(@ConditionValue as nvarchar(50)),cast(@DataVal as nvarchar(50)))>-1)
				begin
					set @Flag=@Flag+1
					if(@NodeIndex>0)
						break
				end
				else if(@ConditionSign='不包含' and CHARINDEX(cast(@ConditionValue as nvarchar(50)),cast(@DataVal as nvarchar(50)))<1)
				begin
					set @Flag=@Flag+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 @CondCount=@Flag)
			set @JumpNode1=@JumpNode
		else if(@TiaoJian='OR' and @Flag>0)
			set @JumpNode1=@JumpNode
	end
	else
	begin
		select @JumpNode1=WFNId from WorkFlowNode where WFId=@WFId and NodeNum=cast((select NextNodes from WorkFlowNode where WFNId=@NodeId 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 WFNId=@NextNodeId
	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)+',',','+@Agent+',')>0 and a.department=@YWDeptId
		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)+',',','+@Agent+',')>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)+',',','+@Agent+',')>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)+',',','+@Agent+',')>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)+',',','+@Agent+',')>0	
	end
	set @Count=@Count+@@error
	update WorkToDo set CurrentNodeId=@NextNodeId,CurrentNodeName=@NextNodeName where WTDId=@WTDId
	set @Count=@Count+@@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 WTDId=@WTDId
	select @WFATId=WFATId,@WFARId=WFARId from WorkFlowNode where WFNId=@WFNId
	---开始一个事务
	begin transaction T
	--@Count检测是否插入成功的变量
	set @Count=0
	update WorkNodeApprover set Operation=2,OperationWord=@OperationWord,AddDate=getdate() where WTDId=@WTDId and WFNId=@WFNId and UId=@UId and Operation=0
	set @Count=@Count+@@error
	update WorkNodeApprover set Operation=3 where WTDId=@WTDId and WFNId=@WFNId and Operation=0
	set @Count=@Count+@@error
	insert into WorkToDoNode values(@WTDId,@WFNId,@WFNName,@WFATId,@WFARId,@UId,2)
	set @Count=@Count+@@error
	update WorkToDo set WTDStatus=5 where WTDId=@WTDId
	set @Count=@Count+@@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 WTDId=@WTDId
	--获取审批流程的第一个节点
	select top 1 @NodeId=WFNId from WorkFlowNode where WFId=@WFId order by NodeNum asc
	--获取表单的id名称,获取表单条件字段名称
	select @TableIdName=TableIdName,@TableField=TableField from WorkFlowForm where [TabelName]=@TableName
	exec WorkFlowGetNotes @WFId,@NodeId,@TableName,@TableIdName,@FormId,@JumpNode1,@returnVal output
	set @NodeList=@returnVal
	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 WFNId=@NodeId
		set @returnVal=@WFNName
	end
	if(@JumpNode1>0)
	begin
		select @WFNName=WFNName from WorkFlowNode where WFNId=@JumpNode1
		set @returnVal=@returnVal+' → '+@WFNName
		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 Id=@Id
		else if(@TableName='Leave')
			update Leave set IsApprove=1 where Id=@Id
	end
	else if(@Status=5)--退回
	begin
		if(@TableName='Agreement')--合同
		begin
			update Agreement set CState=3 where Id=@Id
		end
		else if(@TableName='PurchaseOrder')--采购单
		begin
			update PurchaseOrder set OrderState=7 where Id=@Id
		end
		else if(@TableName='DeliveryInformation')--发货单
		begin
			update DeliveryInformation set [State]=0 where Id=@Id
		end
		else if(@TableName='Leave')--请假
			update Leave set IsApprove=2 where Id=@Id
	end
end

三、相关界面