Back to OIM Explorer

dbo.QBM_FCVDatetimeToNextWork

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 5.610 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVObjectkeyToElement source text reference
references source dbo.QBM_FCVStringToInt source text reference
references source dbo.QBM_FGIConfigparmValue source text reference
references source dbo.QBM_FGIHolidayInAdminUnit source text reference

References

Referenced By

dbo.QER_FCVTimeToWorkTimePerson

Complete Source

1CREATE FUNCTION dbo.QBM_FCVDatetimeToNextWork(2  @BaseTimeUTC datetime,3  @CountMinutes int,4  @ObjectkeyAdminUnit varchar(138)5) RETURNS datetime6AS7BEGIN8  DECLARE @erg datetime9  DECLARE @AdminUnitIsState BIT10  DECLARE @UTCOffset int11  DECLARE @WorkingHoursWeek varchar(168)12  DECLARE @WorkingHoursWeekT QBM_YParameterList13  DECLARE @emptyDay varchar(24) = '000000000000000000000000'14  DECLARE @NineToFive varchar(24) = '000000000111111110000000'15  DECLARE @BaseTimeLocal datetime16  DECLARE @ZielString varchar(64)17  DECLARE @SecondsRest int18  DECLARE @Stunde int19  DECLARE @WorkingHoursDay varchar(24)20  DECLARE @isodate varchar(10)21  DECLARE @dayOfWeek varchar(16)22  DECLARE @lauf int23  DECLARE @IstFirstHour BIT24  DECLARE @IsDayLightSaving BIT25  DECLARE @IgnoreHoliday int = 026  DECLARE @IgnoreWeekend int = 027  DECLARE @ElementBuffer QBM_YCursorBuffer28  DECLARE @ElementCount int29  DECLARE @ElementIndex int30  SELECT31    @IgnoreHoliday = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QBM\WorkingHours\IgnoreHoliday'),32    0)33  SELECT34    @IgnoreWeekend = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QBM\WorkingHours\IgnoreWeekend'),35    0)36  IF @ObjectkeyAdminUnit IS NULL37  BEGIN38    SELECT TOP 1 @ObjectkeyAdminUnit = c.XObjectKey39    FROM DialogDatabase d40      WITH(readpast)41    JOIN DialogCountry c42      WITH(readpast)43      ON c.UID_DialogCountry = d.UID_DialogCountryDefault44  END45  IF @ObjectkeyAdminUnit IS NULL46  BEGIN47    SELECT48      @ObjectkeyAdminUnit = '<Key><T>DialogCountry</T><P>QBM-29A70004FED64C4EBAE3FAC8D6258999</P></Key>'49  END50  SELECT @IstFirstHour = 151  SELECT @AdminUnitIsState = 052  IF dbo.QBM_FCVObjectkeyToElement('TableName',53  @ObjectkeyAdminUnit) = 'DialogState'54  BEGIN55    SELECT @AdminUnitIsState = 156    SELECT57      TOP 1 @UTCOffset = AvgUTCOffset,58      @WorkingHoursWeek = DefaultWorkingHours,59      @IsDayLightSaving = IsDayLightSaving60    FROM DialogState61    WHERE62      XObjectKey = @ObjectkeyAdminUnit63    IF len(isnull(@WorkingHoursWeek, '')) <> 16864    BEGIN65      SELECT TOP 1 @WorkingHoursWeek = c.DefaultWorkingHours66      FROM DialogCountry c67      JOIN dialogState s68        ON c.uid_dialogCountry = s.uid_dialogCountry69      WHERE70        s.XObjectKey = @ObjectkeyAdminUnit71    END72  END73  ELSE74  BEGIN75    SELECT76      TOP 1 @UTCOffset = AvgUTCOffset,77      @WorkingHoursWeek = DefaultWorkingHours,78      @IsDayLightSaving = IsDayLightSaving79    FROM DialogCountry80    WHERE81      XObjectKey = @ObjectkeyAdminUnit82  END83  SELECT84    @BaseTimeLocal = dateadd(ss,85    @UTCOffset,86    @BaseTimeUTC)87  IF @IsDayLightSaving = 1 AND month(@BaseTimeUTC) IN(4,88  5,89  6,90  7,91  8,92  9,93  10)94  BEGIN95    SELECT96      @BaseTimeLocal = dateadd(ss,97      3600,98      @BaseTimeLocal)99  END100  SELECT101    @SecondsRest = @CountMinutes * 60 + datepart(mi,102    @BaseTimeLocal) * 60 + 1103  IF datepart(ss,104  @BaseTimeLocal) > 0105  BEGIN106    SELECT @SecondsRest = @SecondsRest + 60107  END108  IF len(isnull(@WorkingHoursWeek, '')) <> 168109  BEGIN110    INSERT INTO @WorkingHoursWeekT(Parameter1,111    Parameter2)112    VALUES('Sunday',113    @emptyDay),114    ('Monday',115    @NineToFive),116    ('Tuesday',117    @NineToFive),118    ('Wednesday',119    @NineToFive),120    ('Thursday',121    @NineToFive),122    ('Friday',123    @NineToFive),124    ('Saturday',125    @emptyDay)126  END127  ELSE128  BEGIN129    INSERT INTO @WorkingHoursWeekT(Parameter1,130    Parameter2)131    VALUES('Sunday',132    substring(@WorkingHoursWeek, 1, 24)),133    ('Monday',134    substring(@WorkingHoursWeek, 1 + 24 * 1, 24)),135    ('Tuesday',136    substring(@WorkingHoursWeek, 1 + 24 * 2, 24)),137    ('Wednesday',138    substring(@WorkingHoursWeek, 1 + 24 * 3, 24)),139    ('Thursday',140    substring(@WorkingHoursWeek, 1 + 24 * 4, 24)),141    ('Friday',142    substring(@WorkingHoursWeek, 1 + 24 * 5, 24)),143    ('Saturday',144    substring(@WorkingHoursWeek, 1 + 24 * 6, 24))145  END146  IF @IgnoreWeekend = 1147  BEGIN148    IF EXISTS(149      SELECT TOP 1 1150      FROM @WorkingHoursWeekT w151      WHERE152        w.Parameter2 = @emptyDay)153    BEGIN154      UPDATE @WorkingHoursWeekT155      SET Parameter2 =(156      SELECT max(m.Parameter2) AS wert157      FROM @WorkingHoursWeekT m)158      FROM @WorkingHoursWeekT w159      WHERE160        Parameter2 = @emptyDay161    END162  END163  SELECT164    @Stunde = datepart(hh,165    @BaseTimeLocal)166  SELECT @lauf = 1167  INSERT INTO @ElementBuffer(Ident1,168  Ident2)169  SELECT170    isodate,171    DayOfWeek172  FROM dialogcalendar173  WHERE174    isodate >= convert(nvarchar(10),175  @BaseTimeLocal,176  121)177  ORDER BY isodate178  SELECT @ElementCount = @@ROWCOUNT179  SELECT @ElementIndex = 1180  WHILE @ElementIndex <= @ElementCount AND @lauf > 0181  BEGIN182    SELECT183      TOP 1 @isodate = bu.Ident1,184      @dayOfWeek = bu.Ident2185    FROM @ElementBuffer bu186    WHERE187      bu.ElementIndex = @ElementIndex188    SELECT TOP 1 @WorkingHoursDay = w.Parameter2189    FROM @WorkingHoursWeekT w190    WHERE191      Parameter1 = @DayOfWeek192    IF @WorkingHoursDay > @emptyDay AND @IgnoreHoliday = 0193    BEGIN194      IF dbo.QBM_FGIHolidayInAdminUnit(@isodate,195      @ObjectkeyAdminUnit) = 1196      BEGIN197        SELECT @WorkingHoursDay = @emptyDay198      END199    END200    IF @WorkingHoursDay = @emptyDay201    BEGIN202      SELECT @Stunde = 24203      IF @IstFirstHour = 1204      BEGIN205        SELECT @IstFirstHour = 0206        SELECT @SecondsRest = @CountMinutes * 60207      END208    END209    WHILE @Stunde < 24 AND @lauf > 0210    BEGIN211      IF Substring(@WorkingHoursDay,212      @stunde+1,213      1) = '1'214      BEGIN215        SELECT @IstFirstHour = 0216        SELECT @SecondsRest = @SecondsRest - 3600217        IF @SecondsRest < 0218        BEGIN219          SELECT @lauf = 0220          SELECT @SecondsRest = @SecondsRest - 1221          SELECT @ZielString = @isodate + ' ' +222          RIGHT('00' + convert(nvarchar(16), @stunde),223          2) + ':' +224          RIGHT('00' + convert(nvarchar(16), CASE225          WHEN abs(@SecondsRest) > 59 THEN226          60 +(@SecondsRest / 60)227          ELSE 0228          END),229          2)230        END231      END232      ELSE233      BEGIN234        IF @IstFirstHour = 1235        BEGIN236          SELECT @IstFirstHour = 0237          SELECT @SecondsRest = @CountMinutes * 60238        END239      END240      SELECT @Stunde = @stunde +1241    END242    SELECT @Stunde = 0243    SELECT @ElementIndex += 1244  END245  SELECT246    @erg = dateadd(ss,247    @UTCOffset *(-1),248    convert(datetime, @ZielString, 121))249  IF @IsDayLightSaving = 1 AND month(@erg) IN(4,250  5,251  6,252  7,253  8,254  9,255  10)256  BEGIN257    SELECT258      @erg = dateadd(ss,259      -3600,260      @erg)261  END262  ende:263  RETURN(@erg)264END

Open raw exported source

1    create   function dbo.QBM_FCVDatetimeToNextWork (@BaseTimeUTC datetime , @CountMinutes int , @ObjectkeyAdminUnit varchar(138)  ) returns datetime2 as begin  declare @erg datetime  declare @AdminUnitIsState bit declare @UTCOffset int declare @WorkingHoursWeek varchar(168)  declare @WorkingHoursWeekT3 QBM_YParameterList   declare @emptyDay varchar(24) = '000000000000000000000000' declare @NineToFive varchar(24) = '000000000111111110000000' declare @BaseTimeLocal4 datetime declare @ZielString varchar(64) declare @SecondsRest int declare @Stunde int declare @WorkingHoursDay varchar(24) declare @isodate varchar(105) declare @dayOfWeek varchar(16) declare @lauf int declare @IstFirstHour bit  declare @IsDayLightSaving bit declare @IgnoreHoliday int = 0 declare @IgnoreWeekend6 int = 0 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int select @IgnoreHoliday = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue7('QBM\WorkingHours\IgnoreHoliday'), 0) select @IgnoreWeekend = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QBM\WorkingHours\IgnoreWeekend'), 0) 8if @ObjectkeyAdminUnit is null begin select top 1 @ObjectkeyAdminUnit = c.XObjectKey from DialogDatabase d with (readpast) join DialogCountry c with (readpast9) on c.UID_DialogCountry = d.UID_DialogCountryDefault end if @ObjectkeyAdminUnit is null  begin select @ObjectkeyAdminUnit = '<Key><T>DialogCountry</T><P>QBM-29A70004FED64C4EBAE3FAC8D6258999</P></Key>'10  end  select @IstFirstHour = 1 select @AdminUnitIsState = 0 if dbo.QBM_FCVObjectkeyToElement('TableName', @ObjectkeyAdminUnit) = 'DialogState' begin  11select @AdminUnitIsState = 1 select top 1 @UTCOffset = AvgUTCOffset , @WorkingHoursWeek = DefaultWorkingHours , @IsDayLightSaving = IsDayLightSaving from12 DialogState where XObjectKey = @ObjectkeyAdminUnit  if len(isnull(@WorkingHoursWeek, '')) <> 168 begin  select top 1 @WorkingHoursWeek = c.DefaultWorkingHours13 from DialogCountry c join dialogState s on c.uid_dialogCountry = s.uid_dialogCountry where s.XObjectKey = @ObjectkeyAdminUnit end end else begin  select14 top 1 @UTCOffset = AvgUTCOffset , @WorkingHoursWeek = DefaultWorkingHours , @IsDayLightSaving = IsDayLightSaving from DialogCountry where XObjectKey =15 @ObjectkeyAdminUnit end  select @BaseTimeLocal = dateadd(ss, @UTCOffset, @BaseTimeUTC) if @IsDayLightSaving = 1 and month(@BaseTimeUTC) in (4,5,6,7,8,916,10) begin select @BaseTimeLocal = dateadd(ss, 3600, @BaseTimeLocal)  end  select @SecondsRest = @CountMinutes * 60 + datepart(mi, @BaseTimeLocal) * 6017 + 1 if datepart(ss, @BaseTimeLocal) > 0 begin select @SecondsRest = @SecondsRest + 60 end if len(isnull(@WorkingHoursWeek, '')) <> 168 begin insert into18 @WorkingHoursWeekT(Parameter1, Parameter2) values( 'Sunday' , @emptyDay) , ( 'Monday' , @NineToFive) , ( 'Tuesday' , @NineToFive) , ( 'Wednesday' , @NineToFive19) , ( 'Thursday' , @NineToFive) , ( 'Friday' , @NineToFive) , ( 'Saturday' , @emptyDay) end else begin insert into @WorkingHoursWeekT(Parameter1, Parameter220) values( 'Sunday' , substring(@WorkingHoursWeek, 1, 24)) , ( 'Monday' , substring(@WorkingHoursWeek, 1 + 24 * 1 , 24)) , ( 'Tuesday' , substring(@WorkingHoursWeek21, 1 + 24 * 2 , 24)) , ( 'Wednesday' , substring(@WorkingHoursWeek, 1 + 24 * 3 , 24)) , ( 'Thursday' , substring(@WorkingHoursWeek, 1 + 24 * 4 , 24)) , 22( 'Friday' , substring(@WorkingHoursWeek, 1 + 24 * 5 , 24)) , ( 'Saturday' , substring(@WorkingHoursWeek, 1 + 24 * 6 , 24)) end if @IgnoreWeekend = 1 begin23 if exists (select top 1 1 from @WorkingHoursWeekT w where w.Parameter2 = @emptyDay ) begin update @WorkingHoursWeekT set Parameter2 = (select max(m.Parameter224) as wert from @WorkingHoursWeekT m ) from @WorkingHoursWeekT w where Parameter2 = @emptyDay end end  select @Stunde = datepart(hh, @BaseTimeLocal)  select25 @lauf = 1  insert into @ElementBuffer (Ident1, Ident2) select isodate, DayOfWeek from dialogcalendar where isodate >= convert(nvarchar(10), @BaseTimeLocal26, 121) order by isodate select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount and @lauf > 0 begin select top 271 @isodate = bu.Ident1 , @dayOfWeek = bu.Ident2 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex  select top 1 @WorkingHoursDay = w.Parameter228 from @WorkingHoursWeekT w where Parameter1 = @DayOfWeek  if @WorkingHoursDay > @emptyDay  and @IgnoreHoliday = 0 begin  if dbo.QBM_FGIHolidayInAdminUnit29(@isodate, @ObjectkeyAdminUnit ) = 1 begin  select @WorkingHoursDay = @emptyDay end end  if @WorkingHoursDay = @emptyDay begin  select @Stunde = 24 if 30@IstFirstHour = 1 begin  select @IstFirstHour = 0  select @SecondsRest = @CountMinutes * 60 end end    while @Stunde < 24 and @lauf > 0 begin  if Substring31(@WorkingHoursDay, @stunde+1, 1) = '1' begin  select @IstFirstHour = 0 select @SecondsRest = @SecondsRest - 3600 if @SecondsRest < 0 begin select @lauf32 = 0 select @SecondsRest = @SecondsRest - 1   select @ZielString = @isodate + ' ' + right('00' + convert(nvarchar(16), @stunde), 2) + ':' + right('00' 33+ convert(nvarchar(16), case when abs(@SecondsRest) > 59 then 60 + (@SecondsRest / 60) else 0 end ), 2 ) end end else begin  if @IstFirstHour = 1 begin34  select @IstFirstHour = 0  select @SecondsRest = @CountMinutes * 60 end end select @Stunde = @stunde +1 end   select @Stunde = 0 select @ElementIndex 35+= 1 end   select @erg = dateadd(ss, @UTCOffset * (-1) , convert(datetime, @ZielString, 121))  if @IsDayLightSaving = 1 and month(@erg) in (4,5,6,7,8,936,10) begin select @erg = dateadd(ss, -3600 , @erg) end  ende: return(@erg) end 37