UFN_SALESMETHOD_AVAILABLEDISCOUNTS

CREATE function dbo.UFN_SALESMETHOD_AVAILABLEDISCOUNTS (
    @SALESMETHODID uniqueidentifier,
    @APPLIESTOCODE tinyint
)
returns @AVAILABLEDISCOUNTS table (
    ID uniqueidentifier,
    APPLICATIONTYPECODE tinyint,
    APPLIESTOCODE tinyint,
    CALCULATIONTYPECODE tinyint,
    AMOUNT money,
    [PERCENT] decimal(5,2),
    APPLIESTOMERCHANDISE bit,
    APPLIESTOTICKETS bit
)
as begin
    declare @CURRENTUTCDATETIME datetime;
    declare @CURRENTUTCTIME time(0);

    set @CURRENTUTCDATETIME = getutcdate();
    set @CURRENTUTCTIME = @CURRENTUTCDATETIME;

    declare @CURRENTSYSTEMDEFAULTDATE date;
    declare @TIMEZONEENTRYID uniqueidentifier;
    declare @UTCOFFSET integer;

    set @TIMEZONEENTRYID = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULT();
    set @UTCOFFSET = dbo.UFN_TIMEZONEENTRY_GETTIMEZONEOFFSETFROMUTC(@TIMEZONEENTRYID, @CURRENTUTCDATETIME, 1);
    set @CURRENTSYSTEMDEFAULTDATE = dateadd(minute, @UTCOFFSET, @CURRENTUTCDATETIME);
    set @UTCOFFSET = -@UTCOFFSET;

    -- Bug 174017 MDC

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

    insert into @VALIDTIMESTABLE (
        DISCOUNTAVAILABILITYID,
        [FROM],
        [TO],
        [GMTDAYSPAN]
    )
    select
        DA.ID,
        case
            when DA.ALLOWDISCOUNTTIMEFROM = '' then
                dateadd(minute, @UTCOFFSET, cast('0:00' as time(0)))
            else
                dateadd(minute, @UTCOFFSET, cast(left(DA.ALLOWDISCOUNTTIMEFROM, 2) + ':' + right(DA.ALLOWDISCOUNTTIMEFROM, 2) as time(0)))
        end,
        case
            when DA.ALLOWDISCOUNTTIMETO = '' then
                dateadd(minute, @UTCOFFSET, cast('23:59' as time(0)))
            else
                dateadd(minute, @UTCOFFSET, cast(left(DA.ALLOWDISCOUNTTIMETO, 2) + ':' + right(DA.ALLOWDISCOUNTTIMETO, 2) as time(0)))
        end,
        0
    from dbo.DISCOUNTAVAILABILITY DA
    inner join dbo.DISCOUNTAVAILABILITYSALESMETHOD DASM on DASM.DISCOUNTAVAILABILITYID = DA.ID
    where DASM.SALESMETHODID = @SALESMETHODID

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

    declare @CURRENTDAY tinyint;
    set @CURRENTDAY = (@@datefirst + datepart(dw, @CURRENTSYSTEMDEFAULTDATE)) % 7;

    insert into @AVAILABLEDISCOUNTS (
        ID,
        APPLICATIONTYPECODE,
        APPLIESTOCODE,
        CALCULATIONTYPECODE,
        AMOUNT,
        [PERCENT],
        APPLIESTOMERCHANDISE,
        APPLIESTOTICKETS
    )
    select
        DISCOUNT.ID,
        DISCOUNT.APPLICATIONTYPECODE,
        DISCOUNT.APPLIESTOCODE,
        DISCOUNT.CALCULATIONTYPECODE,
        DISCOUNT.AMOUNT,
        DISCOUNT.[PERCENT],
        DISCOUNT.APPLIESTOMERCHANDISE,
        DISCOUNT.APPLIESTOTICKETS
    from
        dbo.DISCOUNT
    inner join
        dbo.DISCOUNTAVAILABILITY DA on DA.DISCOUNTID = DISCOUNT.ID
    inner join
        dbo.DISCOUNTAVAILABILITYSALESMETHOD DASM on DASM.DISCOUNTAVAILABILITYID = DA.ID
    inner join
        @VALIDTIMESTABLE on [@VALIDTIMESTABLE].DISCOUNTAVAILABILITYID = DA.ID
    where
        DISCOUNT.ISACTIVE = 1
        and DISCOUNT.APPLIESTOCODE = @APPLIESTOCODE
        and DASM.SALESMETHODID = @SALESMETHODID
        and
            case @CURRENTDAY
                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 0 then DA.ALLOWSATURDAY
            end = 1
        and (DA.ALLOWDISCOUNTDATEFROM is null or @CURRENTSYSTEMDEFAULTDATE >= DA.ALLOWDISCOUNTDATEFROM) 
        and (DA.ALLOWDISCOUNTDATETO is null or @CURRENTSYSTEMDEFAULTDATE <= 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])
        )

    return;
end