--select * from t_WorkCalendar order by fday
--select * from t_Mutiworkcal order by fcalid,fday
--例如延长工厂日历到'2012-12-31'
--exec prc_GenWorkCal '2016-12-31'
--指定日期新增工厂日历
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prc_GenWorkCal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop PROCEDURE [dbo].[prc_GenWorkCal]
GO
CREATE PROCEDURE dbo.prc_GenWorkCal
(
@dateEnd as datetime
)
AS
--ver1: k3v11 by argin_chen 2007-07-19
--ver2: 添加对V11之前版本的处理
BEGIN
set nocount on
declare @dateBegin as datetime
declare @date as datetime --,@date1 as datetime
declare @FInterID0 as int,@FInterID1 as int
declare @FDayID as int --,@FDayID1 as int
declare @FWeekID as int --,@FWeekID1 as int
declare @FMonthID as int --,@FMonthID1 as int
declare @FYearID as int --,@FYearID1 as int
--v11 3.0.01.09
declare @FVersion nvarchar(20)
select @FVersion=FValue from t_systemprofile where FKEY='ServicePack'
select @FInterID0=max(FInterID), @date=max(Fday),@dateBegin=Max(Fday)+1,@FDayID=max(FDayID),@FWeekID=max(FWeekID),@FMonthID=Max(FMonthID),@FYearID=Max(FYearID) from t_WorkCalendar
--declare @dateEnd as datetime
--select @dateEnd='2010-12-31'
--判断传入的日期
if @dateEnd is null or @dateEnd<GetDate() or @dateBegin >@dateEnd
begin
print '~$~新增工厂日历截至日期参数不能早于当前日期或者早于现有工厂日历最大日期'
RETURN
end
---------------------------------处理工厂日历---------------------------------
while @date <@dateEnd
begin
select
@FDayID=@FDayID +1,
@FInterID0=case when datepart(weekday,@date+1) in (1,7) then @FInterID0 else @FInterID0+1 end,
@FWeekID=case when datepart(week,@date)=datepart(week,@date+1) then @FWeekID else @FWeekID+1 end,
@FMonthID=case when datepart(Month,@date)=datepart(Month,@date+1) then @FMonthID else @FMonthID+1 end,
@FYearID=case when datepart(Year,@date)=datepart(Year,@date+1) then @FYearID else @FYearID+1 end
SELECT @FInterID1=case when datepart(weekday,@date+1) in (1,7) then 0 else @FInterID0 end
INSERT INTO t_WorkCalendar(FInterID,FDay,FDayID,FWeekID,FMonthID,FYearID) VALUES(
@FInterID1,@date +1,@FDayID ,@FWeekID,@FMonthID,@FYearID)
select @date=@date +1
end
---------------------------------处理多工厂日历---------------------------------
Declare @count int
Declare @FCalID int
Declare @q int
set @FCalID=999
select @count=max(FCalID)+1 from t_relmutiworkcal
while @FCalID<@Count
begin
set @q=0
Select @q=max(finterid) from t_mutiworkcal where finterid>0 and FCalID=@FCalID
insert t_Mutiworkcal (finterid,fday,fdayid,fweekid,fmonthid,fyearid,fcalid)
select t1.finterid,t1.fday,t1.fdayid,t1.fweekid,t1.fmonthid,t1.fyearid,t2.Fcalid from t_workcalendar t1,t_relmutiworkcal t2
where t1.fday between @dateBegin and @dateEnd
and t2.FCalID=@FCalID
order by fcalid
Update t_mutiworkcal set @q=finterid=case when finterid>0 then @Q+1 end
Where finterid > 0 and FCalID=@FCalID and Fday between @dateBegin and @dateEnd
set @FCalID=@FCalID+1
end
--------------------------------------------------------------------------
if @FVersion >='3.0.01.09'
begin
EXEC(N'
--更新工厂日历
Create Table #TMP (FInterID int,FDay smalldatetime, FPreID int, FNxtID int,FPreDay smalldatetime,FNxtDay smalldatetime)
Insert INTO #TMP Select FInterID,FDay,FPreID,FNxtID,FPreDay,FNxtDay FROM t_WorkCalendar ORDER BY FDay
DECLARE @InterID as int
DECLARE @FDay as smalldatetime
select @InterID=Min(FInterID), @FDay=Min(FDay) from t_WorkCalendar WHERE FInterID >0
UPDATE u SET
@InterID=case When FInterID >0 then FInterID else @InterID end
,FPreID=@InterID
,@FDay=case When FInterID >0 then FDay else @FDay end
,FPreDay=@FDay
From #TMP u
Update u SET u.FPreID=v.FPreID, u.FPreDay=v.FPreDay
From t_WorkCalendar u
Inner join #TMP v on u.FDay=v.FDay
TRUNCATE TABLE #TMP
Insert INTO #TMP Select FInterID,FDay,FPreID,FNxtID,FPreDay,FNxtDay FROM t_WorkCalendar ORDER BY FDay desc
select @InterID=Max(FInterID), @FDay=MAX(FDay) from t_WorkCalendar WHERE FInterID >0
UPDATE u SET
@InterID=case When FInterID >0 then FInterID else @InterID end
,FNxtID=@InterID
,@FDay=case When FInterID >0 then FDay else @FDay end
,FNxtDay=@FDay
From #TMP u
Update u SET u.FNxtID=v.FNxtID, u.FNxtDay=v.FNxtDay
From t_WorkCalendar u
Inner join #TMP v on u.FDay=v.FDay
Drop Table #TMP
--更新多工厂日历
Create Table #TMP_M (FID int,FInterID int,FDay smalldatetime,FCalID int,FPreID int, FNxtID int, FPreDay smalldatetime ,FNxtDay smalldatetime)
Insert INTO #TMP_M Select FID,FInterID,FDay,FCalID,FPreID,FNxtID,FPreDay,FNxtDay FROM t_MutiWorkCal ORDER BY FCalID,FDay
--1
DECLARE @CalID as int
declare @FInterID as int
--declare @FDay as smalldatetime
declare @MinID as int
Declare @MaxID as int
declare @MinDay as smalldatetime
declare @MaxDay as smalldatetime
SELECT @MinID=Min(FInterID),@MaxID=Max(FInterID), @MinDay=Min(FDay),@MaxDay=Max(FDay) from t_MutiWorkCal WHERE FInterID >0 and FCalID=999
select @CalID=999,@FInterID=@MinID,@FDay=@MinDay
UPDATE u SET
@FInterID=
case when FCalID=@CalID then
case When FInterID >0 then FInterID else @FInterID end
else
case When FInterID >0 then FInterID else (SELECT Min(FInterID) FROM t_MutiWorkCal WHERE FCalID=u.FCalID AND FInterID >0) end
end
,@FDay=
case when FCalID=@CalID then
case When FInterID >0 then FDay else @FDay end
else
case When FInterID >0 then FDay else @MinDay end
end
,@CalID=FCalID
, FPreID=@FInterID
, FPreDay=@FDay
From #TMP_M u
Update u SET u.FPreID=v.FPreID, u.FPreDay=v.FPreDay
From t_MutiWorkCal u
Inner join #TMP_M v on u.FCalID=v.FCalID and u.FDay=v.FDay
--2
Truncate table #TMP_M
Insert INTO #TMP_M Select FID,FInterID,FDay,FCalID,FPreID,FNxtID,FPreDay,FNxtDay FROM t_MutiWorkCal ORDER BY FCalID,FDay desc
--DECLARE @CalID as int
--declare @FInterID as int
--declare @FDay as smalldatetime
--declare @MinDay as smalldatetime
--declare @MaxDay as smalldatetime
--select @CalID=0,@FInterID=0,@FDay=Min(FDay),@MinDay=Min(FDay),@MaxDay=Max(FDay) from t_MutiWorkCal WHERE FInterID >0 and FCalID=999
select @CalID=999,@FInterID=@MaxID,@FDay=@MaxDay
UPDATE u SET
@FInterID=
case when FCalID=@CalID then
case When FInterID >0 then FInterID else @FInterID end
else
case When FInterID >0 then FInterID else (SELECT Max(FInterID) FROM t_MutiWorkCal WHERE FCalID=u.FCalID AND FInterID >0) end
end
,@FDay=
case when FCalID=@CalID then
case When FInterID >0 then FDay else @FDay end
else
case When FInterID >0 then FDay else @MaxDay end
end
,@CalID=FCalID
, FNxtID=@FInterID
, FNxtDay=@FDay
From #TMP_M u
Update u SET u.FNxtID=v.FNxtID, u.FNxtDay=v.FNxtDay
From t_MutiWorkCal u
Inner join #TMP_M v on u.FCalID=v.FCalID and u.FDay=v.FDay
Drop Table #TMP_M
')
END --if @version >='3.0.01.09'
END
go
赞 7
打赏