CREATE proc [dbo].PreventivePlan
@CompanyID int, --公司ID
@StartTime datetime, --开始时间 e.g. '2005-1-1'
@EndTime datetime --结束时间 e.g. '2007-1-1'
as
declare @CurrentDateTime datetime --当前时间
set @CurrentDateTime=GetDate()
--临时变量
declare @IsPeriod bit --找到匹配的周期
declare @TempStartTime datetime,@TempEndTime datetime --临时时间变量
--试验项目编号
declare @ExpSubTypeID int --次类ID
declare @ExpProjectID int --试验项目ID
declare @Status int,@StartDate datetime, @EndDate datetime
declare @PeriodID int, @Period int, @PeriodUnit int, @Vol int, @AfterYear int --周期时间,周期单位,电压等级,运行年份
declare @count int
--查询设备公共参数
declare @RuningYear int --设备的运行年份
declare @EquipmentID int,@EquipmentVol int, @EquipmentBuyDate datetime, @EquipmentStartDate datetime --设备ID,电压等级,设备购买时间,开始使用日期
declare Equip cursor local for
select 设备编号,电压等级,购置日期,开始使用日期 from gdzhdb.dbo.设备索引 a inner join jsjdDB.dbo.expequipment b on a.[设备编号]=b.[ID] where 归口单位编号 = @CompanyID
open Equip --打开设备表
fetch next from Equip into @EquipmentID,@EquipmentVol,@EquipmentBuyDate,@EquipmentStartDate
while @@fetch_status=0 --获取到一个设备
begin
set @RuningYear=year(@CurrentDateTime)-year(@EquipmentStartDate)
--查询设备试验次类
select @ExpSubTypeID=ExpSubTypeID from expequipment where [ID] = @EquipmentID
--查询试验设备所拥有的试验项目,遍历试验项目
declare project cursor local for
select [ID] from ExpProject where ExpSubTypeID = @ExpSubTypeID
open project
--遍历试验项目
fetch next from project into @ExpProjectID
while @@fetch_status = 0 --获取到一个试验项目
begin
--print '项目ID为'
--print @ExpProjectID
--试验计划表
set @Status = null
set @StartDate = null
set @EndDate = null
--试验周期 --循环遍历试验周期
declare subProject cursor local for
select [ID],Period, PeriodUnit, Vol, AfterYear from ExpPeriod where ExpProjectID = @ExpProjectID
open subProject
--判断试验周期是否符合要求,
fetch next from subProject into @PeriodID,@Period, @PeriodUnit, @Vol, @AfterYear
while @@fetch_status = 0
begin
--print '周期ID为'
--print @PeriodID
--更新
if @PeriodUnit = 1 --月
begin
--select @Status=Status, @StartDate=StartDate, @EndDate=EndDate from expplan where ExpEquipmentID = @EquipmentID and ExpProjectID = @ExpProjectID and EndDate = (select max(EndDate) from expplan where ExpEquipmentID = @EquipmentID and ExpProjectID = @ExpProjectID)
set @IsPeriod=dbo.GetPeriod(@Period, @PeriodUnit, @Vol, @AfterYear,@RuningYear,@EquipmentVol)
if @IsPeriod = 1
begin
set @TempStartTime = @StartTime
set @TempEndTime = DateAdd(day,-1,DateAdd(month,@Period,@TempStartTime))
while(@TempEndTime<@EndTime)
begin
select @count=count(ID) from expplan where ExpEquipmentID = @EquipmentID and @ExpProjectID = ExpProjectID and @TempStartTime=StartDate and @TempEndTime=EndDate and ExpProperty=3
if @count=0
begin
insert into ExpPlan (ExpEquipmentID,ExpProjectID,ExpPeriodID,StartDate,EndDate,Creator,CreateDate,ExpProperty,Status) values (@EquipmentID,@ExpProjectID,@PeriodID, @TempStartTime, @TempEndTime,'系统',GetDate(),3,3)
end
set @TempStartTime = DateAdd(month,@Period,@TempStartTime)
set @TempEndTime = DateAdd(day,-1,DateAdd(month,@Period,@TempStartTime))
end
break --找到符合条件的周期,插入数据后退出
end
end
else if @PeriodUnit = 0 --年
begin
select @Status=Status, @StartDate=StartDate, @EndDate=EndDate from expplan where ExpEquipmentID = @EquipmentID and ExpProjectID = @ExpProjectID and EndDate = (select max(EndDate) from expplan where ExpEquipmentID = @EquipmentID and ExpProjectID = @ExpProjectID)
if (@Status >= 4 and @Status < 7) or @EndDate is null --是否需要生成试验计划。当状态为4已试验,5已填报,6已取消 时,或者EndDate =null (没有生成过试验计划)时,生成新试验计划。
begin
set @IsPeriod=dbo.GetPeriod(@Period, @PeriodUnit, @Vol, @AfterYear,@RuningYear,@EquipmentVol)
if @IsPeriod = 1
begin
--print '添加年周期'
insert into ExpPlan (ExpEquipmentID,ExpProjectID,ExpPeriodID,StartDate,EndDate,Creator,CreateDate,ExpProperty,Status) values (@EquipmentID,@ExpProjectID,@PeriodID, @StartTime, DateAdd(day,-1,DateAdd(year,@Period,@StartTime)),'系统',GetDate(),3,3)
break --找到符合条件的周期,插入数据后退出
end
end
end
fetch next from subProject into @PeriodID,@Period, @PeriodUnit, @Vol, @AfterYear--下一条
end
close subProject
deallocate subProject
fetch next from project into @ExpProjectID --下一条
end
close project
deallocate project
fetch next from Equip into @EquipmentID,@EquipmentVol,@EquipmentBuyDate,@EquipmentStartDate
end
close Equip
deallocate Equip
if @@error <>0
return 0
else
return 1
GO