单位提交监控点报告时产生预警v0.1（资金超资）

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