dbo.QBM_ZCalendarFill

1     create   procedure QBM_ZCalendarFill (@SlotNumber int , @dummy1 varchar(38) , @dummy2 varchar(38) , @GenProcIDDummy varchar(38) ) as begin 
2  declare @Past int declare @Future int declare @Startdatum datetime declare @Endedatum datetime declare @CalendarYear int declare @CalendarQuarter int
3 declare @CalendarMonth int declare @CalendarDay int declare @isYear bit declare @isQuarter bit declare @isMonth bit declare @DayOfWeek nvarchar(32)  declare
4 @DayOfYear int declare @WeekOfYear int declare @isWeek bit declare @IsoDate nvarchar(10)  declare @h nvarchar(256) declare @Debugswitch int = 0 declare
5 @GenProcID varchar(38) = newid() declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') BEGIN TRY select @Future = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue
6('Common\Calendar\YearsFuture'), 1) select @Past = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Calendar\YearsPast'), 5 )  select @StartDatum
7 = convert(nvarchar(16), year(GetUTCDate()) - @past ) + N'-01-01' select @EndeDatum = convert(nvarchar(16), year(GetUTCDate()) + @future ) + N'-12-31' 
8  exec QBM_PSessionContextSet 'GenProcID', @GenProcID  while @startdatum <= @endedatum begin select @CalendarYear = year(@startdatum) select @CalendarMonth
9 = month(@startdatum) select @CalendarDay = day(@startdatum) select @IsoDate = convert(nvarchar(16), @CalendarYear) + N'-' + right(N'00' + convert(nvarchar
10(16), @CalendarMonth),2) + N'-' + right(N'00' +convert(nvarchar(16), @CalendarDay),2) select @CalendarQuarter = case @CalendarMonth when 1 then 1 when 
112 then 1 when 3 then 1 when 4 then 2 when 5 then 2 when 6 then 2 when 7 then 3 when 8 then 3 when 9 then 3 when 10 then 4 when 11 then 4 when 12 then 4
12 end  select @h = datename(dw, @startdatum) select @DayOfWeek = @h  select @DayOfYear = datepart(dy, @startdatum) select @isYear = 0  if @CalendarMonth
13 = 1 and @CalendarDay = 1 begin select @isYear = 1  end   select @WeekOfYear = datepart(isowk, @startdatum)  select @isWeek = 0 if @DayOfWeek = N'Monday'
14 begin select @isWeek = 1 end select @isMonth = 0 if @CalendarDay = 1 and @CalendarDay = 1 begin select @isMonth = 1 end select @isQuarter = 0 if @CalendarDay
15 = 1 and @CalendarMonth in (1,4,7,10) begin select @isQuarter = 1 end if exists (select top 1 1 from DialogCalendar x where x.CalendarYear = @CalendarYear
16 and x.CalendarMonth = @CalendarMonth and x.CalendarDay = @CalendarDay ) begin update DialogCalendar set CalendarQuarter = @CalendarQuarter, isYear = @isYear
17, isQuarter = @isQuarter, isMonth = @isMonth, DayOfWeek = @DayOfWeek, DayOfYear = @DayOfYear, WeekOfYear = @WeekOfYear, isWeek = @isWeek, IsoDate = @IsoDate
18 where CalendarYear = @CalendarYear and CalendarMonth = @CalendarMonth and CalendarDay = @CalendarDay and ( isnull(isYear,0) <> @isYear or isnull(isQuarter
19,0) <> @isQuarter or isnull(isMonth,0) <> @isMonth or isnull(CalendarQuarter,0) <> @CalendarQuarter or isnull(DayOfWeek,N'') <> @DayOfWeek or isnull(DayOfYear
20,0) <> @DayOfYear or isnull(WeekOfYear,0) <> @WeekOfYear or isnull(isWeek,0) <> @isWeek or isnull(IsoDate,N'') <> @IsoDate ) if @@ROWCOUNT > 0 and @Debugswitch
21 > 0 begin print 'updated for ' + @isodate end end else begin insert into DialogCalendar(uid_DialogCalendar, CalendarYear, CalendarQuarter, CalendarMonth
22, CalendarDay, isYear, isQuarter, isMonth , DayOfWeek, DayOfYear, WeekOfYear, isWeek, IsoDate) select newid(), @CalendarYear, @CalendarQuarter, @CalendarMonth
23, @CalendarDay, @isYear, @isQuarter, @isMonth , @DayOfWeek, @DayOfYear, @WeekOfYear, @isWeek, @IsoDate if @Debugswitch > 0 begin print 'updated for ' +
24 @isodate end end   select @startdatum = dateadd(dd, 1, @startdatum) end   update DialogCalendar set displayMonth = x.Bezeichnung from DialogCalendar join
25 ( select y.nummer, y.Bezeichnung from ( values (1 , datename (mm,'2200-01-01')) , (2 , datename (mm,'2200-02-01')) , (3 , datename (mm,'2200-03-01')) 
26, (4 , datename (mm,'2200-04-01')) , (5 , datename (mm,'2200-05-01')) , (6 , datename (mm,'2200-06-01')) , (7 , datename (mm,'2200-07-01')) , (8 , datename
27 (mm,'2200-08-01')) , (9 , datename (mm,'2200-09-01')) , (10 , datename (mm,'2200-10-01')) , (11 , datename (mm,'2200-11-01')) , (12 , datename (mm,'2200-12-01'
28)) ) as y (nummer, Bezeichnung) ) as x on x.nummer = DialogCalendar.CalendarMonth where isnull(DialogCalendar.DisplayMonth,N'') <> x.Bezeichnung END TRY
29 BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  ende: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R
30 return end 
31