set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [tri_inspect_warn_update]
on [dbo].[projectReport_inspectInfo] after update
as
declare @id nvarchar(50),@moduleId nvarchar(50),@projectId nvarchar(50),@status nvarchar(50),@createPerson nvarchar(50),@inspectCompleteRate float,@sumScheduleRate float,@sumFundRate float,@warnType nvarchar(10)
declare @description nvarchar(500),@topicId nvarchar(50),@schedule nvarchar(50),@insecptName nvarchar(50)
--删除前不是暂存,删除后不为暂存的
--状态不是暂存退回和通过(经信委操作)
if ((select inspect.status from Inserted inspect) not in('408080b52b895b4a012b89d840e10012','408080b52b895b4a012b89d8b15e0014','408080b52b895b4a012b89d8dcc30015'))
begin
select @moduleId=inspect.id,@projectId=inspect.projectInfoId,@status=inspect.status,@inspectCompleteRate=inspect.inspectCompleteRate,@sumScheduleRate=inspect.sumScheduleRate@sumFundRate=inspect.sumFundRate,@createPerson=inspect.fillPerson from Inserted inspect
if(@inspectCompleteRate<100)--该进度小于100
begin
set @id=CONVERT(varchar(50), newid())
--insert into dbo.projectWarn_warn(id,projectInfoId,moduleId,warnName,warnType,warnTime,planValue,status,createPerson)
-- values(@id,@projectId,@moduleId,'0','0',getdate(),@opValue,'0',@createPerson)
select @topicId=project.projectType from dbo.establish_projectInfo as project where project.id=@projectId
if(@topicId='408080b52b2e4df1012b2e530aac0003')--课题类编号
begin
select @schedule=inspect.planSchedule from dbo.projectReport_inspectInfo as inspect where inspect.projectInfoId=@projectId and inspect.id=@moduleId
select @insecptName=iset.name from dbo.projectReport_inspectSet iset where iset.id =(select inspectSetId from dbo.establish_topicPlanSchedule schedule where schedule.id=@schedule)
end
else
begin
select @schedule=inspect.planSchedule from dbo.projectReport_inspectInfo as inspect where inspect.projectInfoId=@projectId and inspect.id=@moduleId
select @insecptName=iset.name from dbo.projectReport_inspectSet iset where iset.id =(select inspectSetId from dbo.establish_nonTopicPlanSchedule schedule where schedule.id=@schedule)
end
set @description=@insecptName+'阶段';
if exists(select * from dbo.projectWarn_warn where moduleId=@moduleId and warnType=1)
update dbo.projectWarn_warn set warnTime=getdate(),factValue=@inspectCompleteRate,createPerson=@createPerson where projectInfoId=@projectId and moduleId=@moduleId and warnType='1'
else
insert into dbo.projectWarn_warn(id,projectInfoId,moduleId,warnName,warnType,warnTime,factValue,description,status,createPerson)
values(@id,@projectId,@moduleId,'1','1',getdate(),@inspectCompleteRate,@description,'0',@createPerson)
end
-- else
-- begin
-- delete from dbo.projectWarn_warn where projectInfoId=@projectId and moduleId=@moduleId and warnType='1'
-- end
if(@sumFundRate>100)--总体资金百分比
begin
set @id=CONVERT(varchar(50), newid())
--insert into dbo.projectWarn_warn(id,projectInfoId,moduleId,warnName,warnType,warnTime,planValue,status,createPerson)
-- values(@id,@projectId,@moduleId,'0','0',getdate(),@opValue,'0',@createPerson)
select @topicId=project.projectType from dbo.establish_projectInfo as project where project.id=@projectId
if(@topicId='408080b52b2e4df1012b2e530aac0003')--课题类编号
begin
select @schedule=inspect.planSchedule from dbo.projectReport_inspectInfo as inspect where inspect.projectInfoId=@projectId and inspect.id=@moduleId
select @insecptName=iset.name from dbo.projectReport_inspectSet iset where iset.id =(select inspectSetId from dbo.establish_topicPlanSchedule schedule where schedule.id=@schedule)
end
else
begin
select @schedule=inspect.planSchedule from dbo.projectReport_inspectInfo as inspect where inspect.projectInfoId=@projectId and inspect.id=@moduleId
select @insecptName=iset.name from dbo.projectReport_inspectSet iset where iset.id =(select inspectSetId from dbo.establish_nonTopicPlanSchedule schedule where schedule.id=@schedule)
end
set @description=@insecptName+'阶段';
if exists(select * from dbo.projectWarn_warn where moduleId=@moduleId and warnType=0)
update dbo.projectWarn_warn set warnTime=getdate(),factValue=@sumFundRate,createPerson=@createPerson where projectInfoId=@projectId and moduleId=@moduleId and warnType='0'
else
insert into dbo.projectWarn_warn(id,projectInfoId,moduleId,warnName,warnType,warnTime,factValue,description,status,createPerson)
values(@id,@projectId,@moduleId,'1','0',getdate(),@sumFundRate,@description,'0',@createPerson)
end
-- else
-- begin
-- delete from dbo.projectWarn_warn where projectInfoId=@projectId and moduleId=@moduleId and warnType='0'
-- end
end
--if ((select inspect.status from Inserted inspect) in('408080b52b895b4a012b89d8b15e0014','408080b52b895b4a012b89d8dcc30015'))--如果为暂存和通过,删除预警.
--begin
-- select @moduleId=inspect.id,@projectId=inspect.projectInfoId,@status=inspect.status,@inspectCompleteRate=inspect.inspectCompleteRate,@sumFundRate=inspect.sumFundRate,@createPerson=inspect.fillPerson from Inserted inspect
-- delete from dbo.projectWarn_warn where projectInfoId=@projectId and moduleId=@moduleId
--end