--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