P_CalcPlan

alter proc P_CalcPlan
(
@JobID int
)
/*
 --计算Job执行周期
*/
as 
begin 


set nocount on;


declare @LastTime datetime,@cycle varchar(50),@CycleNum int,@Freq int,@RunGroupID int,@ns nvarchar(4000)
,@interval varchar(50),@interval_num int,@StartTime datetime,@StartTimeCur datetime,@EndTime datetime,@EndTimeCur datetime,@ID int;










select @cycle=
(case PlanCycleName 
when '年' then 'yy' 
when '季' then 'qq' 
when '月' then 'mm' 
when '周' then 'wk' 
when '日' then 'dd' 
when '时' then 'hh' 
end )
,@interval=
(case PlanCycleName 
when '年' then 'dd' 
when '季' then 'dd' 
when '月' then 'dd' 
when '周' then 'dd' 
when '日' then 'hh' 
when '时' then 'mi' 
end )
,@interval_num=
(case PlanCycleName 
when '年' then isnull(PlanCycleNum,1)*365/(case when PlanFreqName='次' then PlanFreqNum else 1 end) 
when '季' then isnull(PlanCycleNum,1)*90/(case when PlanFreqName='次' then PlanFreqNum else 1 end) 
when '月' then isnull(PlanCycleNum,1)*30/(case when PlanFreqName='次' then PlanFreqNum else 1 end) 
when '周' then isnull(PlanCycleNum,1)*7/(case when PlanFreqName='次' then PlanFreqNum else 1 end) 
when '日' then isnull(PlanCycleNum,1)*24/(case when PlanFreqName='次' then PlanFreqNum else 1 end) 
when '时' then isnull(PlanCycleNum,1)*60/(case when PlanFreqName='次' then PlanFreqNum else 1 end)
end )
,@CycleNum=isnull(PlanCycleNum,1)
,@Freq=(case when PlanFreqName='次' then PlanFreqNum else 1 end)
from Task_Plan 
where JobID=@JobID ;






--每个执行组最大设为10个串行任务
set @RunGroupID=
isnull(
(select RunGroupID from (select max(RunGroupID)RunGroupID from Task_PlanCalc where JobID=@JobID) aa
where RunGroupID in (select RunGroupID from Task_PlanCalc group by RunGroupID having count(distinct JobID)<10))
,
(select isnull(max(RunGroupID),1) from Task_PlanCalc)
) ;


--定义表暂存但周期内本Job的所有执行信息
declare @t table (id int identity(1,1),RunGroupID int, JobID int , PlanRunTime datetime, PlanEndTime datetime, IsV bit);


--初始开始时间
select @StartTime=PlanStartTime from Task_Plan where JobID=@JobID;


--初始截止时间
select @EndTime=PlanEndTime from Task_Plan where JobID=@JobID;


--周期内的开始和结束时间
set @ns='
set @StartTimeCur=@StartTime;
set @EndTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@StartTime);
while not(@StartTimeCur<=''2016-11-07'' and @EndTimeCur>''2016-11-07'')
begin
set @StartTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@StartTimeCur);
set @EndTimeCur=dateadd('+@cycle+','+convert(varchar,@CycleNum)+',@EndTimeCur);
end
';


exec sp_executesql @ns,
N'@StartTime datetime,@EndTime datetime,@StartTimeCur datetime output,@EndTimeCur datetime output'
,@StartTime,@EndTime,@StartTimeCur output,@EndTimeCur output;




--修复结束时间
select @EndTimeCur=min(t) from (select @EndTime as t union all select @EndTimeCur as t) as aa;


set @LastTime=@StartTimeCur;


--将每次的信息写入
while @Freq>0 and @LastTime<@EndTimeCur
begin 


--设置当前
insert into @t(RunGroupID, JobID, PlanRunTime, IsV)
select @RunGroupID, @JobID, @LastTime, 1;
select @ID=@@identity;
--设置上次
update @t set PlanEndTime=isnull(@LastTime,@EndTime) where RunGroupID=@RunGroupID and ID=@ID-1;


--条件更新
set @ns=N'select @LastTime=dateadd('+@interval+','+convert(varchar,@interval_num)+','''+convert(varchar,@LastTime)+''') 
,@EndTime=dateadd('+@interval+','+convert(varchar,@interval_num+1)+','''+convert(varchar,@LastTime)+''') ';
exec sp_executesql @ns,N'@LastTime datetime output,@EndTime datetime output',@LastTime output,@EndTime output;
select @EndTime=min(t) from (select @EndTimeCur as t union all select @EndTime as t) as aa;
set @Freq=@Freq-1;
end


update @t set PlanEndTime=@EndTime where RunGroupID=@RunGroupID and ID=@ID;


merge into Task_PlanCalc m
using 
(
select RunGroupID, @JobID as JobID, PlanRunTime, PlanEndTime from @t
) as s
on m.JobID=s.JobID and m.PlanRunTime=s.PlanRunTime 
when matched
then 
update set m.PlanEndTime=s.PlanEndTime,m.RunGroupID=s.RunGroupID
when not matched 
then 
insert(RunGroupID, JobID, PlanRunTime, PlanEndTime, IsV)
values(s.RunGroupID, s.JobID, s.PlanRunTime, s.PlanEndTime, 1)
when not matched by source  and m.JobID=@JobID --限定JobID防止多删除
then delete;
end