dbo.QER_FGIPwoValidDateRangeAssign

1   create   function dbo.QER_FGIPwoValidDateRangeAssign(@ObjectKeyAssignment varchar(138)  , @ValidFrom datetime   , @ValidUntil datetime   ) returns
2 bit as begin declare @wantedFrom datetime declare @wantedUntil datetime declare @erg bit = 1 declare @heute datetime = getutcdate() declare @ist QBM_YDateTimePair
3 declare @Luecke QBM_YDateTimePair  declare @GapFitting int = 0 declare @GapDefinition int = 0  select @wantedFrom = ISNULL(@ValidFrom, @heute) select 
4@wantedUntil = ISNULL(@ValidUntil, '2200-01-01') if @wantedFrom < @heute begin select @wantedFrom = @heute end if @wantedUntil < @wantedFrom begin select
5 @erg = 0 goto EndLabel end if @wantedUntil < @heute begin select @erg = 0 goto EndLabel end  select @GapFitting = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue
6('QER\ITShop\GapBehavior\GapFitting'), 0) if @GapFitting = 2  begin goto endlabel end if exists (select top 1 1 from PersonWantsOrg pwo where pwo.ObjectKeyAssignment
7 = @ObjectKeyAssignment      and pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') ) begin
8  select @GapDefinition = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\GapBehavior\GapDefinition'), 0) insert into @ist (FromDate , ToDate
9) select case when isnull(pwo.ValidFrom, '1899-12-30') < @heute then @heute else pwo.ValidFrom end , dbo.QBM_FCVDatetimeToDatetimeEve(isnull(pwo.ValidUntil
10, '2200-01-01')) from PersonWantsOrg pwo where pwo.ObjectKeyAssignment = @ObjectKeyAssignment        and (pwo.OrderState in ('Assigned', 'Granted', 'New'
11, 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and @GapDefinition = 0  or pwo.OrderState in ('Assigned', 'Granted', 'OrderProlongate'
12, 'OrderUnsubscribe', 'Waiting') and @GapDefinition = 1  or pwo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) and @GapDefinition =
13 2  )  insert into @Luecke(FromDate , ToDate ) select g.GapStart, g.GapEnd from dbo.QBM_FTDateTimeGaps(@ist) g  if @ValidFrom is null and @ValidUntil is
14 null and exists (select top 1 1 from @Luecke ) begin goto endLabel end if @GapFitting = 1  begin if not exists (select top 1 1 from @Luecke l where l.FromDate
15  <= @wantedFrom and @wantedUntil <= l.ToDate  ) begin select @erg = 0 end end if @GapFitting = 0  begin if not exists (select top 1 1 from @Luecke l where
16 dbo.QBM_FGIDateTimeSpanOverlap(@wantedFrom, @wantedUntil, l.FromDate , l.ToDate , 0) = 1 ) begin select @erg = 0 end end    end endLabel: return (@erg
17) end 
18