UFN_COMBINATION_AVAILABLEFORORDER

CREATE function dbo.UFN_COMBINATION_AVAILABLEFORORDER
(
    @COMBINATIONID uniqueidentifier,
    @SALESMETHODID uniqueidentifier
)
returns bit
with execute as caller
as begin
    if 0 = (select top(1) ISACTIVE from dbo.COMBINATION where ID = @COMBINATIONID)
    begin
        return 0
    end
    else
    begin

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

        declare @CURRENTUTCTIME time
        set @CURRENTUTCTIME = getutcdate()

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

        if exists ( select DA.ID
                    from dbo.COMBINATIONAVAILABILITY DA
                        inner join dbo.COMBINATIONAVAILABILITYSALESMETHOD DASM on DASM.COMBINATIONAVAILABILITYID = DA.ID
                    where
                        DA.COMBINATIONID = @COMBINATIONID 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 0 then DA.ALLOWSATURDAY
                        end = 1 and
                        (DA.ALLOWCOMBINATIONDATEFROM is null or @CURRENTSYSTEMDEFAULTDATE >= dbo.UFN_DATE_GETEARLIESTTIME(DA.ALLOWCOMBINATIONDATEFROM)) and
                        (DA.ALLOWCOMBINATIONDATETO is null or @CURRENTSYSTEMDEFAULTDATE <= dbo.UFN_DATE_GETLATESTTIME(DA.ALLOWCOMBINATIONDATETO)) and
                        --convert time 'from' and 'to' to UTC based on time zone 1 - cast 'from' and 'to' to time, 2 - add offset from UTC, 

                        (DA.ALLOWCOMBINATIONTIMEFROM = '' or @CURRENTUTCTIME >= dateadd(minute, @UTCOFFSET, cast(dbo.UFN_HOURMINUTE_DISPLAYTIME(DA.ALLOWCOMBINATIONTIMEFROM) as time))) and
                        (DA.ALLOWCOMBINATIONTIMETO = '' or @CURRENTUTCTIME <= dateadd(minute, @UTCOFFSET, cast(dbo.UFN_HOURMINUTE_DISPLAYTIME(DA.ALLOWCOMBINATIONTIMETO) as time))))                        
                        --DA.ALLOWCOMBINATIONTIMEFROM = '' or dbo.UFN_HOURMINUTE_GETFROMDATE(@CURRENTDATE) >= DA.ALLOWCOMBINATIONTIMEFROM) and

                        --(DA.ALLOWCOMBINATIONTIMETO = '' or dbo.UFN_HOURMINUTE_GETFROMDATE(@CURRENTDATE) <= DA.ALLOWCOMBINATIONTIMETO) )

        begin
            return 1
        end

        return 0

    end

    return 0

end