UFN_DISCOUNT_AVAILABLEFORORDER

CREATE function dbo.UFN_DISCOUNT_AVAILABLEFORORDER
(
  @DISCOUNTID uniqueidentifier,
  @SALESORDERID uniqueidentifier
)
returns bit
with execute as caller
as begin
  if (select ISACTIVE from dbo.DISCOUNT where ID = @DISCOUNTID) = 0
  begin
    return 0
  end
  else
  begin

    declare @SALESMETHODID uniqueidentifier
    select @SALESMETHODID = ID
    from dbo.SALESMETHOD
    where TYPECODE = (select SALESMETHODTYPECODE from dbo.SALESORDER where ID = @SALESORDERID)

    declare @CURRENTUTCTIME time
    set @CURRENTUTCTIME = getutcdate()

    declare @CURRENTSYSTEMDEFAULTDATE datetime
    set @CURRENTSYSTEMDEFAULTDATE = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate())

    declare @TIMEZONEENTRYID uniqueidentifier
    declare @UTCOFFSET integer
    set @TIMEZONEENTRYID = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULT()
    set @UTCOFFSET = -1 * dbo.UFN_TIMEZONEENTRY_GETTIMEZONEOFFSETFROMUTC(@TIMEZONEENTRYID, getutcdate(), 1)

    declare @VALIDTIMESTABLE table 
    (
      DISCOUNTAVAILABILITYID uniqueidentifier, 
      [FROM] time, 
      [TO] time, 
      GMTDAYSPAN bit 
    )

    insert into @VALIDTIMESTABLE
    (
      DISCOUNTAVAILABILITYID,
      [FROM],
      [TO],
      [GMTDAYSPAN]
    )
    select 
      DA.ID,
      case when DA.ALLOWDISCOUNTDATEFROM = ''
      then
        dateadd(minute, @UTCOFFSET, cast(dbo.UFN_HOURMINUTE_DISPLAYTIME('0000') as time))
      else
        dateadd(minute, @UTCOFFSET, cast(dbo.UFN_HOURMINUTE_DISPLAYTIME(DA.ALLOWDISCOUNTTIMEFROM) as time))

      end,
      case when DA.ALLOWDISCOUNTDATETO = ''
      then
        dateadd(minute, @UTCOFFSET, cast(dbo.UFN_HOURMINUTE_DISPLAYTIME('2359') as time))
      else
        dateadd(minute, @UTCOFFSET, cast(dbo.UFN_HOURMINUTE_DISPLAYTIME(DA.ALLOWDISCOUNTTIMETO) as time))
      end,
      0
    from dbo.DISCOUNTAVAILABILITY DA
    inner join dbo.DISCOUNTAVAILABILITYSALESMETHOD DASM on DASM.DISCOUNTAVAILABILITYID = DA.ID
    where
      DA.DISCOUNTID = @DISCOUNTID and
      DASM.SALESMETHODID = @SALESMETHODID

    update @VALIDTIMESTABLE
      set GMTDAYSPAN = 1
    where [TO] < [FROM]

    if exists
    (
      select DA.ID
      from dbo.DISCOUNTAVAILABILITY DA
      inner join dbo.DISCOUNTAVAILABILITYSALESMETHOD DASM on DASM.DISCOUNTAVAILABILITYID = DA.ID
      inner join @VALIDTIMESTABLE on [@VALIDTIMESTABLE].DISCOUNTAVAILABILITYID = DA.ID
      where
        DA.DISCOUNTID = @DISCOUNTID and
        DASM.SALESMETHODID = @SALESMETHODID and
        case (@@datefirst + datepart(dw, @CURRENTSYSTEMDEFAULTDATE)) % 7
          when 1 then DA.ALLOWSUNDAY
          when 2 then DA.ALLOWMONDAY
          when 3 then DA.ALLOWTUESDAY
          when 4 then DA.ALLOWWEDNESDAY
          when 5 then DA.ALLOWTHURSDAY
          when 6 then DA.ALLOWFRIDAY
          when 7 then DA.ALLOWSATURDAY
          when 0 then DA.ALLOWSATURDAY
        end = 1 and
        (DA.ALLOWDISCOUNTDATEFROM is null or @CURRENTSYSTEMDEFAULTDATE >= DA.ALLOWDISCOUNTDATEFROM) and
        (DA.ALLOWDISCOUNTDATETO is null or cast(@CURRENTSYSTEMDEFAULTDATE as date) <= DA.ALLOWDISCOUNTDATETO) and
        (
          ([@VALIDTIMESTABLE].GMTDAYSPAN = 1 and (@CURRENTUTCTIME >= [@VALIDTIMESTABLE].[FROM] or @CURRENTUTCTIME <= [@VALIDTIMESTABLE].[TO])) or 
          ([@VALIDTIMESTABLE].GMTDAYSPAN = 0 and @CURRENTUTCTIME between [@VALIDTIMESTABLE].[FROM] and [@VALIDTIMESTABLE].[TO]) or
          ([@VALIDTIMESTABLE].[FROM] = [@VALIDTIMESTABLE].[TO])
        )
    )
    begin
      return 1
    end

    return 0
  end

  return 0
end