金蝶K3 WISE工厂日历SQL操作指南

--exec prc_GenWorkCal '2018-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: by argin_chen 2007-07-19
BEGIN
    set nocount on
    declare @dateBegin as datetime
    --declare @dateEnd 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
    
    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
    
    
    --更新工厂日历
    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