④内部公开请勿外传--execprc_GenWorkCal'2018-12-31'--指定日期新增工厂日历ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[prc_GenWorkCal]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropPROCEDURE[dbo].[prc_GenWorkCal]GOCREATEPROCEDUREdbo.prc_GenWorkCal(@dateEndasdatetime)AS--ver1:byargin_chen2007-07-19BEGINsetnocountondeclare@dateBeginasdatetime--declare@dateEndasdatetimedeclare@dateasdatetime--,@date1asdatetimedeclare@FInterID0asint,@FInterID1asintdeclare@FDayIDasint--,@FDayID1asintdeclare@FWeekIDasint--,@FWeekID1asintdeclare@FMonthIDasint--,@FMonthID1asintdeclare@FYearIDasint--,@FYearID1asintselect@FInterID0=max(FInterID),@date=max(Fday),@dateBegin=Max(Fday)+1,@FDayID=max(FDayID),@FWeekID=max(FWeekID),@FMonthID=Max(FMonthID),@FYearID=Max(FYearID)fromt_WorkCalendar--declare@dateEndasdatetime--select@dateEnd='2010-12-31'--判断传入的日期if@dateEndisnullor@dateEnd@dateEndbeginprint'~$~新增工厂日历截至日期参数不能早于当前日期或者早于现有工厂日历最大日期'RETURNendwhile@date<@dateEndbeginselect@FDayID=@FDayID+1,@FInterID0=casewhendatepart(weekday,@date+1)in(1,7)then@FInterID0else@FInterID0+1end,@FWeekID=casewhendatepart(week,@date)=datepart(week,@date+1)then@FWeekIDelse@FWeekID+1end,@FMonthID=casewhendatepart(Month,@date)=datepart(Month,@date+1)then@FMonthID1/5④内部公开请勿外传else@FMonthID+1end,@FYearID=casewhendatepart(Year,@date)=datepart(Year,@date+1)then@FYearIDelse@FYearID+1endSELECT@FInterID1=casewhendatepart(weekday,@date+1)in(1,7)then0else@FInterID0endINSERTINTOt_WorkCalendar(FInterID,FDay,FDayID,FWeekID,FMonthID,FYearID)VALUES(@FInterID1,@date+1,@FDayID,@FWeekID,@FMonthID,@FYearID)select@date=@date+1end--更新工厂日历CreateTable#TMP(FInterIDint,FDaysmalldatetime,FPreIDint,FNxtIDint,FPreDaysmalldatetime,FNxtDaysmalldatetime)InsertINTO#TMPSelectFInterID,FDay,FPreID,FNxtID,FPreDay,FNxtDayFROMt_WorkCalendarORDERBYFDayDECLARE@InterIDasintDECLARE@FDayassmalldatetimeselect@InterID=Min(FInterID),@FDay=Min(FDay)fromt_WorkCalendarWHEREFInterID>0UPDATEuSET@InterID=caseWhenFInterID>0thenFInterIDelse@InterIDend,FPreID=@InterID,@FDay=caseWhenFInterID>0thenFDayelse@FDayend,FPreDay=@FDayFrom#TMPuUpdateuSETu.FPreID=v.FPreID,u.FPreDay=v.FPreDayFromt_WorkCalendaruInnerjoin#TMPvonu.FDay=v.FDayTRUNCATETABLE#TMPInsertINTO#TMPSelectFInterID,FDay,FPreID,FNxtID,FPreDay,FNxtDayFROMt_WorkCalendarORDERBYFDaydescselect@InterID=Max(FInterID),@FDay=MAX(FDay)fromt_WorkCalendarWHEREFInterID>0UPDATEuSET@InterID=caseWhenFInterID>0thenFInterIDelse@InterIDend,FNxtID=@InterID,@FDay=caseWhenFInterID>0thenFDayelse@FDayend2/5④内部公开请勿外传,FNxtDay=@FDayFrom#TMPuUpdateuSETu.FNxtID=v.FNxtID,u.FNxtDay=v.FNxtDayFromt_WorkCalendaruInnerjoin#TMPvonu.FDay=v.FDayDropTable#TMP--go---------------------------------处理多工厂日历---------------------------------Declare@countintDeclare@FCalIDintDeclare@qintset@FCalID=999select@count=max(FCalID)+1fromt_relmutiworkcalwhile@FCalID<@Countbeginset@q=0Select@q=max(finterid)fromt_mutiworkcalwherefinterid>0andFCalID=@FCalIDinsertt_Mutiworkcal(finterid,fday,fdayid,fweekid,fmonthid,fyearid,fcalid)selectt1.finterid,t1.fday,t1.fdayid,t1.fweekid,t1.fmonthid,t1.fyearid,t2.Fcalidfromt_workcalendart1,t_relmutiworkcalt2wheret1.fdaybetween@dateBeginand@dateEndandt2.FCalID=@FCalIDorderbyfcalidUpdatet_mutiworkcalset@q=finterid=casewhenfinterid>0then@Q+1endWherefinterid>0andFCalID=@FCalIDandFdaybetween@dateBeginand@dateEndset@FCalID=@FCalID+1end----------------------------------------------------------------------------更新多工厂日历CreateTable#TMP_M(FIDint,FInterIDint,FDaysmalldatetime,FCalIDint,FPreIDint,FNxtIDint,FPreDaysmalldatetime,FNxtDaysmalldatetime)InsertINTO#TMP_MSelectFID,FInterID,FDay,FCalID,FPreID,FNxtID,FPreDay,FNxtDayFROMt_MutiWorkCalORDERBYFCalID,FDay3/5④内部公开请勿外传--1DECLARE@CalIDasintdeclare@FInterIDasint--declare@FDayassmalldatetimedeclare@MinIDasintDeclare@MaxIDasintdeclare@MinDayassmalldatetimedeclare@MaxDayassmalldatetimeSELECT@MinID=Min(FInterID),@MaxID=Max(FInterID),@MinDay=Min(FDay),@MaxDay=Max(FDay)fromt_MutiWorkCalWHEREFInterID>0andFCalID=999select@CalID=999,@FInterID=@MinID,@FDay=@MinDayUPDATEuSET@FInterID=casewhenFCalID=@CalIDthencaseWhenFInterID>0thenFInterIDelse@FInterIDendelsecaseWhenFInterID>0thenFInterIDelse@MinIDendend,@FDay=casewhenFCalID=@CalIDthencaseWhenFInterID>0thenFDayelse@FDayendelsecaseWhenFInterID>0thenFDayelse@MinDayendend,@CalID=FCalID,FPreID=@FInterID,FPreDay=@FDayFrom#TMP_MuUpdateuSETu.FPreID=v.FPreID,u.FPreDay=v.FPreDayFromt_MutiWorkCaluInnerjoin#TMP_Mvonu.FCalID=v.FCalIDandu.FDay=v.FDay--2Truncatetable#TMP_MInsertINTO#TMP_MSelectFID,FInterID,FDay,FCalID,FPreID,FNxtID,FPreDay,FNxtDayFROMt_MutiWorkCalORDERBYFCalID,FDaydesc--DECLARE@CalIDasint--declare@FInterIDasint--declare@FDayassmalldatetime--declare@MinDayassmalldatetime4/5④内部公开请勿外传--declare@MaxDayassmalldatetime--select@CalID=0,@FInterID=0,@FDay=Min(FDay),@MinDay=Min(FDay),@MaxDay=Max(FDay)fromt_MutiWorkCalWHEREFInterID>0andFCalID=999select@CalID=999,@FInterID=@MaxID,@FDay=@MaxDayUPDATEuSET@FInterID=casewhenFCalID=@CalIDthencaseWhenFInterID>0thenFInterIDelse@FInterIDendelsecaseWhenFInterID>0thenFInterIDelse@MaxIDendend,@FDay=casewhenFCalID=@CalIDthencaseWhenFInterID>0thenFDayelse@FDayendelsecaseWhenFInterID>0thenFDayelse@MaxDayendend,@CalID=FCalID,FNxtID=@FInterID,FNxtDay=@FDayFrom#TMP_MuUpdateuSETu.FNxtID=v.FNxtID,u.FNxtDay=v.FNxtDayFromt_MutiWorkCaluInnerjoin#TMP_Mvonu.FCalID=v.FCalIDandu.FDay=v.FDayDropTable#TMP_MENDgo5/5
