UFN_DELIVERYMETHOD_VALIDFORORDER

CREATE function dbo.UFN_DELIVERYMETHOD_VALIDFORORDER
(
    @DELIVERYMETHODID uniqueidentifier,
    @ORDERID uniqueidentifier
)
returns bit
with execute as caller
as begin

    declare @ISOKAY bit;
    set @ISOKAY = 0;

    declare @BLOCKDELIVERY bit;
    declare @TIMEVALUE smallint;
    declare @TIMEUNIT tinyint;
    declare @EARLIESTEVENTDATETIMEWITHOFFSET datetimeoffset;

    declare @SALESMETHODID uniqueidentifier
    select @SALESMETHODID = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(SALESMETHODTYPECODE)
    from dbo.SALESORDER
    where ID = @ORDERID;

    select
        @BLOCKDELIVERY = BLOCKDELIVERY,
        @TIMEVALUE = BLOCKTIMEVALUE,
        @TIMEUNIT = BLOCKTIMEUNITCODE
    from
        dbo.DELIVERYMETHOD
    where
        ID = @DELIVERYMETHODID;

    select @EARLIESTEVENTDATETIMEWITHOFFSET = EVENT.STARTDATETIMEWITHOFFSET
    from dbo.EVENT
    where
        ID = (
            select top(1) E.ID
            from dbo.SALESORDERITEM as SOI
            inner join dbo.SALESORDERITEMTICKET as SOIT on SOIT.ID = SOI.ID
            inner join dbo.EVENT as E on SOIT.EVENTID = E.ID
            where SOI.SALESORDERID = @ORDERID
            order by E.STARTDATETIME asc
        )

    if not exists(
        select 1 
        from dbo.SALESMETHODDELIVERYMETHOD 
        inner join dbo.DELIVERYMETHOD
            on SALESMETHODDELIVERYMETHOD.DELIVERYMETHODID = DELIVERYMETHOD.ID
        where 
            SALESMETHODID = @SALESMETHODID and 
            DELIVERYMETHODID = @DELIVERYMETHODID and
            DELIVERYMETHOD.ISACTIVE = 1
    )
        set @ISOKAY = 0
    else if @BLOCKDELIVERY = 0
        set @ISOKAY = 1;
    else begin
        declare @CALCULATEDBLOCKDATETIMEWITHOFFSET datetimeoffset;
        declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);

        set @CALCULATEDBLOCKDATETIMEWITHOFFSET = case @TIMEUNIT
            when 0 then dateadd(hh, @TIMEVALUE, @CURRENTDATETIMEOFFSET)
            when 1 then dateadd(dd, @TIMEVALUE, @CURRENTDATETIMEOFFSET)
        end

        set @ISOKAY = case
            when @EARLIESTEVENTDATETIMEWITHOFFSET is null then 1
            when @CALCULATEDBLOCKDATETIMEWITHOFFSET < @EARLIESTEVENTDATETIMEWITHOFFSET then 1
            else 0
        end
    end

    return @ISOKAY;
end