Back to OIM Explorer

dbo.QBM_ZCalendarFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.554 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVStringToInt source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL208 lines
1CREATE PROCEDURE QBM_ZCalendarFill(2  @SlotNumber int,3  @dummy1 varchar(38),4  @dummy2 varchar(38),5  @GenProcIDDummy varchar(38)6)7AS8BEGIN9  DECLARE @Past int10  DECLARE @Future int11  DECLARE @Startdatum datetime12  DECLARE @Endedatum datetime13  DECLARE @CalendarYear int14  DECLARE @CalendarQuarter int15  DECLARE @CalendarMonth int16  DECLARE @CalendarDay int17  DECLARE @isYear BIT18  DECLARE @isQuarter BIT19  DECLARE @isMonth BIT20  DECLARE @DayOfWeek nvarchar(32)21  DECLARE @DayOfYear int22  DECLARE @WeekOfYear int23  DECLARE @isWeek BIT24  DECLARE @IsoDate nvarchar(10)25  DECLARE @h nvarchar(256)26  DECLARE @Debugswitch int = 027  DECLARE @GenProcID varchar(38) = newid()28  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')29  BEGIN TRY30    SELECT31      @Future = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Calendar\YearsFuture'),32      1)33    SELECT34      @Past = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Calendar\YearsPast'),35      5)36    SELECT37      @StartDatum = convert(nvarchar(16),38      year(GetUTCDate()) - @past) + N '-01-01'39    SELECT40      @EndeDatum = convert(nvarchar(16),41      year(GetUTCDate()) + @future) + N '-12-31'42    EXEC QBM_PSessionContextSet 'GenProcID',43      @GenProcID44    WHILE @startdatum <= @endedatum45    BEGIN46      SELECT @CalendarYear = year(@startdatum)47      SELECT @CalendarMonth = month(@startdatum)48      SELECT @CalendarDay = day(@startdatum)49      SELECT50        @IsoDate = convert(nvarchar(16),51        @CalendarYear) + N '-' +52        RIGHT(N '00' + convert(nvarchar(16), @CalendarMonth),53        2) + N '-' +54        RIGHT(N '00' +convert(nvarchar(16), @CalendarDay),55        2)56      SELECT @CalendarQuarter = CASE @CalendarMonth57      WHEN 1 THEN58      159      WHEN 2 THEN60      161      WHEN 3 THEN62      163      WHEN 4 THEN64      265      WHEN 5 THEN66      267      WHEN 6 THEN68      269      WHEN 7 THEN70      371      WHEN 8 THEN72      373      WHEN 9 THEN74      375      WHEN 10 THEN76      477      WHEN 11 THEN78      479      WHEN 12 THEN80      481      END82      SELECT83        @h = datename(dw,84        @startdatum)85      SELECT @DayOfWeek = @h86      SELECT87        @DayOfYear = datepart(dy,88        @startdatum)89      SELECT @isYear = 090      IF @CalendarMonth = 1 AND @CalendarDay = 191      BEGIN92        SELECT @isYear = 193      END94      SELECT95        @WeekOfYear = datepart(isowk,96        @startdatum)97      SELECT @isWeek = 098      IF @DayOfWeek = N 'Monday'99      BEGIN100        SELECT @isWeek = 1101      END102      SELECT @isMonth = 0103      IF @CalendarDay = 1 AND @CalendarDay = 1104      BEGIN105        SELECT @isMonth = 1106      END107      SELECT @isQuarter = 0108      IF @CalendarDay = 1 AND @CalendarMonth IN(1,109      4,110      7,111      10)112      BEGIN113        SELECT @isQuarter = 1114      END115      IF EXISTS(116        SELECT TOP 1 1117        FROM DialogCalendar x118        WHERE119          x.CalendarYear = @CalendarYear AND x.CalendarMonth = @CalendarMonth AND x.CalendarDay = @CalendarDay)120      BEGIN121        UPDATE DialogCalendar122        SET CalendarQuarter = @CalendarQuarter,123        isYear = @isYear,124        isQuarter = @isQuarter,125        isMonth = @isMonth,126        DayOfWeek = @DayOfWeek,127        DayOfYear = @DayOfYear,128        WeekOfYear = @WeekOfYear,129        isWeek = @isWeek,130        IsoDate = @IsoDate131        WHERE132          CalendarYear = @CalendarYear AND CalendarMonth = @CalendarMonth AND CalendarDay = @CalendarDay AND(isnull(isYear,133        0) <> @isYear OR isnull(isQuarter, 0) <> @isQuarter OR isnull(isMonth, 0) <> @isMonth OR isnull(CalendarQuarter,134        0) <> @CalendarQuarter OR isnull(DayOfWeek, N '') <> @DayOfWeek OR isnull(DayOfYear, 0) <> @DayOfYear OR isnull(WeekOfYear,135        0) <> @WeekOfYear OR isnull(isWeek, 0) <> @isWeek OR isnull(IsoDate, N '') <> @IsoDate)136        IF @@ROWCOUNT > 0 AND @Debugswitch > 0137        BEGIN138          print 'updated for ' + @isodate139        END140      END141      ELSE142      BEGIN143        INSERT INTO DialogCalendar(uid_DialogCalendar,144        CalendarYear,145        CalendarQuarter,146        CalendarMonth,147        CalendarDay,148        isYear,149        isQuarter,150        isMonth,151        DayOfWeek,152        DayOfYear,153        WeekOfYear,154        isWeek,155        IsoDate)156        SELECT157          newid(),158          @CalendarYear,159          @CalendarQuarter,160          @CalendarMonth,161          @CalendarDay,162          @isYear,163          @isQuarter,164          @isMonth,165          @DayOfWeek,166          @DayOfYear,167          @WeekOfYear,168          @isWeek,169          @IsoDate170        IF @Debugswitch > 0171        BEGIN172          print 'updated for ' + @isodate173        END174      END175      SELECT176        @startdatum = dateadd(dd,177        1,178        @startdatum)179    END180    UPDATE DialogCalendar181    SET displayMonth = x.Bezeichnung182    FROM DialogCalendar183    JOIN(184    SELECT185      y.nummer,186      y.Bezeichnung187    FROM(188    VALUES(1, datename(mm, '2200-01-01')),(2, datename(mm, '2200-02-01')),(3, datename(mm, '2200-03-01')),189    (4, datename(mm, '2200-04-01')),(5, datename(mm, '2200-05-01')),(6, datename(mm, '2200-06-01')),(7, datename(mm,190    '2200-07-01')),(8, datename(mm, '2200-08-01')),(9, datename(mm, '2200-09-01')),(10, datename(mm, '2200-10-01')),191    (11, datename(mm, '2200-11-01')),(12, datename(mm, '2200-12-01'))) AS y(nummer, Bezeichnung)) AS x192      ON x.nummer = DialogCalendar.CalendarMonth193    WHERE194      isnull(DialogCalendar.DisplayMonth,195    N '') <> x.Bezeichnung196  END TRY197  BEGIN CATCH198    EXEC QBM_PSessionErrorAdd DEFAULT199    RAISERROR('',200    18,201    1)202      WITH NOWAIT203  END CATCH204  ende:205  EXEC QBM_PSessionContextSet 'GenProcID',206    @GenProcID_R207  RETURN208END
Open raw exported source
SQL ยท Raw31 lines
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 int3 declare @CalendarMonth int declare @CalendarDay int declare @isYear bit declare @isQuarter bit declare @isMonth bit declare @DayOfWeek nvarchar(32)  declare4 @DayOfYear int declare @WeekOfYear int declare @isWeek bit declare @IsoDate nvarchar(10)  declare @h nvarchar(256) declare @Debugswitch int = 0 declare5 @GenProcID varchar(38) = newid() declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') BEGIN TRY select @Future = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue6('Common\Calendar\YearsFuture'), 1) select @Past = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\Calendar\YearsPast'), 5 )  select @StartDatum7 = 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 @CalendarMonth9 = month(@startdatum) select @CalendarDay = day(@startdatum) select @IsoDate = convert(nvarchar(16), @CalendarYear) + N'-' + right(N'00' + convert(nvarchar10(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 412 end  select @h = datename(dw, @startdatum) select @DayOfWeek = @h  select @DayOfYear = datepart(dy, @startdatum) select @isYear = 0  if @CalendarMonth13 = 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 @CalendarDay15 = 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 = @CalendarYear16 and x.CalendarMonth = @CalendarMonth and x.CalendarDay = @CalendarDay ) begin update DialogCalendar set CalendarQuarter = @CalendarQuarter, isYear = @isYear17, isQuarter = @isQuarter, isMonth = @isMonth, DayOfWeek = @DayOfWeek, DayOfYear = @DayOfYear, WeekOfYear = @WeekOfYear, isWeek = @isWeek, IsoDate = @IsoDate18 where CalendarYear = @CalendarYear and CalendarMonth = @CalendarMonth and CalendarDay = @CalendarDay and ( isnull(isYear,0) <> @isYear or isnull(isQuarter19,0) <> @isQuarter or isnull(isMonth,0) <> @isMonth or isnull(CalendarQuarter,0) <> @CalendarQuarter or isnull(DayOfWeek,N'') <> @DayOfWeek or isnull(DayOfYear20,0) <> @DayOfYear or isnull(WeekOfYear,0) <> @WeekOfYear or isnull(isWeek,0) <> @isWeek or isnull(IsoDate,N'') <> @IsoDate ) if @@ROWCOUNT > 0 and @Debugswitch21 > 0 begin print 'updated for ' + @isodate end end else begin insert into DialogCalendar(uid_DialogCalendar, CalendarYear, CalendarQuarter, CalendarMonth22, CalendarDay, isYear, isQuarter, isMonth , DayOfWeek, DayOfYear, WeekOfYear, isWeek, IsoDate) select newid(), @CalendarYear, @CalendarQuarter, @CalendarMonth23, @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 join25 ( 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 , datename27 (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 TRY29 BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  ende: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R30 return end 31