--==================================================
--用途：单级审批
--作者: 龚德辉
--日期：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
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
