[MSSQL]多人群组审批

 

--==================================================--用途：多人审批--作者: 龚德辉--日期：2008-03-15--==================================================Create    Procedure [dbo].[ApproveTaskMutiType](@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 )asbegin declare @temp nvarchar(15), @Progress nvarchar(2000), @currEndLevel int, @NameVerify nvarchar(2000), @Name nvarchar(20), @nowstatus nchar(1), @turnto intdeclare @nextLevel int    --取本层EndLevel   select @currEndLevel=EndLevel,@nowstatus=rtrim(Result),@turnto=TurnTo from TaskLine where TaskCode=@TaskCode and UserID=@ApproveUserID and TranType<>'W'   if @turnto<>1       return   --if @nowstatus is not null    --   return   --if @currEndLevel is null or @currEndLevel=''       --return --审阅行不处理    if(@Result='Y')   begin  --更新当前审批者的审批信息  Update TaskLine set  Result=@Result,Ref=@Ref,FinishDate=Getdate() where TaskCode=@TaskCode and UserID=@ApproveUserID  --更新当前审批者所在层级的审批信息  update TaskLine  set TurnTo='1',Result=@Result,Ref='他人已审批'  where  TaskCode=@TaskCode and FlowLevel=@currEndLevel 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  --如果存在待审核对象任务结束,更新任务状态  if not exists(select * from TaskLine where TaskCode=@TaskCode and TurnTo is null and TranType='A')beginupdate Task set Stat='A' where TaskCode=@TaskCodeend   elsebegin--判定不存EndLevel之前未审批的,则往下走if not exists(select * from TaskLine where TaskCode=@TaskCode and TurnTo<>'1' and TranType='A' and EndLevel<=@currEndLevel)begin--取得下层，更新字段select @nextLevel=min(FlowLevel) FROM TaskLine WHERE FlowLevel>=@currEndLevel+1update TaskLineset TurnTo='1',StartDate=getdate()where TaskCode=@TaskCode and FlowLevel=@nextLevelend       end   end   else        beginif(@Result='N') begin    select @Name=rtrim(U.[name]) from  users U where U.[id]=@ApproveUserIDif (@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 endelse begin --退回至定层级declare @FlowLevel as intselect @FlowLevel=FlowLevel FROM TaskLine  WHERE  TaskCode=@TaskCode and TaskLine=@BackLevel --清空后续的已审批对象update TaskLineset Ref=null,Result=null,TurnTo=null,StartDate=null,FinishDate=nullwhere TaskCode=@TaskCode and TaskLine>@BackLevel-1--设置指定层级的待审对象update TaskLineset TurnTo=1,StartDate=getdate()where TaskCode=@TaskCode and FlowLevel=@FlowLevel   --更新任务单头进度update Task        set Progress=Progress+'->'+@Name+'退回',Location=@Name+'退回'    where  TaskCode=@TaskCodeend                          end      endend