[MSSQL]单级审批

--================================================== --用途：单级审批 --作者: 龚德辉 --日期：2008-03-15 --================================================== Create Procedure [dbo].[ApproveTask]( @ApproveUserID as int=null ,@TaskCode as nvarchar(11)=null ,@Result as nchar(1)=null ,@Ref as nvarchar(50)=null ,@Back as nchar(1)=null ,@BackLevel as int ) as declare @NameVerify varchar(2000),@Name varchar(20) begin if(@Result='Y') begin --更新当层TaskLine的信息 Update TaskLine set Result=@Result,Ref=@Ref,FinishDate=Getdate() where TaskCode=@TaskCode and UserID=@ApproveUserID --更新当层的进度信息和当前进度 select @Name=rtrim(U.[name]), @NameVerify=CASE when Result='Y'THEN '已批准' else '未批准' end from TaskLine TL left join users U on TL.UserID=U.[id] where TL.TaskCode=@TaskCode and TL.UserID=@ApproveUserID update Task set Progress=Progress+'->'+@Name+@NameVerify,Location=@Name+@NameVerify where TaskCode=@TaskCode --检查单层是否完成--(未完成)--->注明还需要谁批准 --完成是否有下层--(没有下层)-->完成单-->(有下层)更新字段:TurnTo declare @temp nvarchar(15),@Progress varchar(2000) if exists(select * from TaskLine where TaskCode=@TaskCode and TurnTo='1' and Result is null) begin---注明当前层还需谁审批 -- declare MyCursor1 cursor local static for -- select rtrim(U.[name]) as [name] from TaskLine TL left join users U on TL.UserID=U.id where TL.TaskCode=@TaskCode and TL.TurnTo='1' and Result is null -- open MyCursor1 -- while 1=1 -- begin -- fetch MyCursor1 into -- @temp -- if @@fetch_status<>0 -- break -- begin -- set @Progress=rtrim(ISNULL(@Progress,'')+isnull(@temp,'')) -- end -- -- end -- close MyCursor1 -- deallocate MyCursor1 select @Progress=ltrim(rtrim(isnull(U.[name],''))) from TaskLine TL left join users U on TL.UserID=U.id where TL.TaskCode=@TaskCode and TL.TurnTo='1' and Result is null set @Progress='待'+@Progress+'审批' update Task set Progress=Progress+'->'+rtrim(@Progress) where TaskCode=@TaskCode end else begin ---处理下层 declare @tmp nvarchar(15),@Pro varchar(200),@FlowLevel int, @TaskLine int,@UserID int,@FlowCode varchar(15) select @FlowCode=FL.FlowCode,@FlowLevel=FL.FlowLevel+1 from ( select Task.FlowID from Task left join TaskLine TL on Task.TaskCode=TL.TaskCode where TL.TaskCode=@TaskCode and TL.UserID=@ApproveUserID) T left JOIN Flow F on F.ID=T.FlowID left join FlowLine FL on FL.FlowCode=F.FlowCode where FL.UserID=@ApproveUserID if exists(select U.[name],U.[id] from FlowLine FL left join users U on FL.UserID=U.[id] WHERE FL.FlowLevel=@FlowLevel and FL.FlowCode=@FlowCode and FL.TranType='A') ---@ApproveUserID为审核人的ID begin --取得下层，更新字段 Declare MyCursor2 CURSOR local static FOR select rtrim(U.[name]) as [name],U.[id] from FlowLine FL left join users U on FL.UserID=U.id WHERE FL.FlowLevel=@FlowLevel and FL.FlowCode=@FlowCode and FL.TranType='A' Open MyCursor2 while 1=1 begin fetch MyCursor2 into @tmp ,@UserID if @@fetch_status<>0 break begin set @Pro=rtrim(isnull(@Pro,'')+isnull(@tmp,'')) --+' ' update TaskLine set TurnTo='1',StartDate=getdate() where TaskCode=@TaskCode and UserID=@UserID end end set @Pro='待'+rtrim(@Pro)+'审批' update Task set Progress=Progress+'->'+rtrim(@Pro),Location=rtrim(@Pro)---,SendUserID=@ApproveUserID,SendDate=getdate()// 是否需要发送呢？ where TaskCode=@TaskCode close MyCursor2 deallocate MyCursor2 end else begin --完成事务 还需要做何处理？ 如何确定最后完成？ update Task set Stat='A' where TaskCode=@TaskCode end end end if(@Result='N') begin select @Name=rtrim(U.[name]) from users U where U.[id]=@ApproveUserID if (@Back='N')--不退至哪一层级 begin update Task set Stat='N' where TaskCode=@TaskCode --更新当层的进度信息和当前进度 update Task set Progress=Progress+'->'+@Name+'未批准',Location=@Name+'未批准' where TaskCode=@TaskCode update TaskLine set Result='N',Ref=@Ref,StartDate=getdate(),FinishDate=getdate() where TaskCode=@TaskCode AND TurnTo='1' and UserID=@ApproveUserID end else begin --退回至制定层级 update TaskLine set Ref=null,Result=null,TurnTo=null,StartDate=null,FinishDate=null where TaskCode=@TaskCode and TaskLine>@BackLevel-1 update TaskLine set TurnTo=1,StartDate=getdate() where TaskCode=@TaskCode and TaskLine=@BackLevel --更新任务头 update Task set Progress=Progress+'->'+@Name+'退回',Location=@Name+'退回' where TaskCode=@TaskCode end end end