USP_DATALIST_SALESORDER

            CREATE procedure dbo.USP_DATALIST_SALESORDER
                (@CONTEXTID uniqueidentifier)
            as
                set nocount on; 

                declare @SALESTYPECODE tinyint;
                declare @ORDERSTATUSCODE tinyint;
                declare @SALESMETHODID uniqueidentifier;

                select 
                    @SALESTYPECODE = SALESORDER.SALESMETHODTYPECODE,
                    @ORDERSTATUSCODE = SALESORDER.STATUSCODE
                from dbo.SALESORDER 
                where SALESORDER.ID = @CONTEXTID

                declare @ISBASICCMS bit = 0
                if 
                    dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('e5e0494b-ba0f-4e23-b8fb-a59112dbf3c8') = 1 and -- Check for BasicCMS

                    dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('f238e8fe-06ae-4fdc-beaf-fdf6637e1982') = 0 -- Check for not CMS

                        set @ISBASICCMS = 1

                select @SALESMETHODID = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(@SALESTYPECODE);

                declare @SALESMETHODFLATFEESPERITEM money
                select @SALESMETHODFLATFEESPERITEM = SUM(FEE.AMOUNT) 
                from dbo.SALESMETHODFEE 
                inner join dbo.FEE on SALESMETHODFEE.FEEID = FEE.ID 
                inner join dbo.SALESMETHOD on SALESMETHOD.ID = SALESMETHODFEE.SALESMETHODID
                where FEE.APPLIESTOCODE = 1 --item 

                    and FEE.TYPECODE = 0 
                    and FEE.ISACTIVE = 1
                    and SALESMETHOD.TYPECODE = @SALESTYPECODE

                declare @HASUNRESTRICTEDDELIVERYMETHOD bit = 0
                declare @EARLIESTVALIDEVENTDATETIMEWITHOFFSET datetimeoffset = null
                declare @DELIVERYMETHODEXISTS bit = 0
                exec dbo.USP_DELIVERYMETHOD_INFO_BYSALESMETHODID 
                    @SALESMETHODID, 
                    @DELIVERYMETHODEXISTS output, 
                    @HASUNRESTRICTEDDELIVERYMETHOD output, 
                    @EARLIESTVALIDEVENTDATETIMEWITHOFFSET output

                declare @MAXINT int = 2147483647

                declare @MAXDATETIMEOFFSETFORDIFFFROMEARLIESTVALIDEVENT datetimeoffset = dateadd(second, @MAXINT, @EARLIESTVALIDEVENTDATETIMEWITHOFFSET)

                declare @CURRENTDATE datetime = getdate();
                declare @MAXDATETIMEFORDIFFFROMCURRENT datetime = dateadd(second, @MAXINT, @CURRENTDATE)

                declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);
                declare @MAXDATETIMEOFFSETFORDIFFFROMCURRENT datetimeoffset = dateadd(second, @MAXINT, @CURRENTDATETIMEOFFSET)

                select
                    SALESORDERITEM.ID,
                    SALESORDERITEM.DESCRIPTION,
                    SALESORDERITEM.PRICE,
                    SALESORDERITEM.QUANTITY,
                    SALESORDERITEM.TOTAL,
                    SALESORDERITEM.TYPECODE,
                    SALESORDERITEM.DATA,
                    SALESORDERITEM.OPTIONS,
                    SALESORDERITEM.CALLBACKURL,
                    SALESORDERITEM.EXPIREDCALLBACKURL,
                    SALESORDERITEM.SYSTEMTYPENAME,
                    SALESORDERITEM.ASSEMBLYNAME,
                    SALESORDERITEM.ATTRIBUTES,
                    SALESORDERITEM.CATEGORYNAME,
                    SALESORDERITEM.ACKNOWLEDGEMENT,
                    SALESORDERRESERVEDITEM.DATEADDED,
                    SALESORDERRESERVEDITEM.EXPIRATIONDATE,
                    [EXPIRATIONSUMMARY].[RESERVATIONHASEXPIRED] as ISEXPIRED,
                    [EXPIRATIONSUMMARY].[DELIVERYHASEXPIRED] as [HASNOVALIDDELIVERYMETHOD],
                    [EXPIRATIONSUMMARY].[EXPIRATIONDELTA]
                from dbo.SALESORDERITEM
                left join dbo.SALESORDERITEMTICKET on
                    SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                left join dbo.EVENT on
                    SALESORDERITEMTICKET.EVENTID = EVENT.ID
                left join dbo.SALESORDERITEMFEE on
                    SALESORDERITEM.ID = SALESORDERITEMFEE.ID
                left join dbo.SALESORDERRESERVEDITEM on
                    SALESORDERITEM.ID = SALESORDERRESERVEDITEM.ID    
                outer apply (
                    select
                        case 
                            when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] is not null then
                                case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] 
                                    when 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
                                    when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                                    when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                                    when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                                end
                            else [EVENT].[STARTDATETIMEWITHOFFSET]
                        end as [DATETIMEWITHOFFSET]
                        from dbo.[PROGRAMSALESMETHOD]
                        where 
                            [PROGRAMSALESMETHOD].[PROGRAMID] = [EVENT].[PROGRAMID] and
                            [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
                ) [EVENTOFFSALE]
                outer apply (
                    select
                        case when [SALESORDERRESERVEDITEM].[ID] is null then 0 else 1 end [RESERVATION],
                        case when (SALESORDERITEM.TYPECODE <> 0) or (EVENT.ID is null and @DELIVERYMETHODEXISTS = 1) then 0 else 1 end [DELIVERY],
                        case when SALESORDERITEM.TYPECODE <> 0 or EVENT.ID is null then 0 else 1 end as [ONSALE]
                ) [CANEXPIRE]
                outer apply (
                    select
                        case when [CANEXPIRE].[RESERVATION] = 1 then 
                                case 
                                    when @CURRENTDATE > SALESORDERRESERVEDITEM.EXPIRATIONDATE then -1 --Already expired

                                    when @MAXDATETIMEFORDIFFFROMCURRENT <= SALESORDERRESERVEDITEM.EXPIRATIONDATE then @MAXINT --Beyond comparison(tm)

                                    else datediff(second,@CURRENTDATE,SALESORDERRESERVEDITEM.EXPIRATIONDATE) 
                                end
                            else null 
                        end as [RESERVATION],
                        case 
                            when [CANEXPIRE].[DELIVERY] = 1 and @DELIVERYMETHODEXISTS = 0 then -1
                            when [CANEXPIRE].[DELIVERY] = 1 then 
                                case 
                                    when @EARLIESTVALIDEVENTDATETIMEWITHOFFSET > EVENT.STARTDATETIMEWITHOFFSET then -1 --Already expired

                                    when @MAXDATETIMEOFFSETFORDIFFFROMEARLIESTVALIDEVENT <= EVENT.STARTDATETIMEWITHOFFSET then @MAXINT --Beyond datediff comparison

                                    else datediff(second,@EARLIESTVALIDEVENTDATETIMEWITHOFFSET, EVENT.STARTDATETIMEWITHOFFSET) 
                                end
                            else null
                        end as [DELIVERY],
                        case when [CANEXPIRE].[ONSALE] = 1 then
                                case
                                    when @CURRENTDATETIMEOFFSET > [EVENTOFFSALE].[DATETIMEWITHOFFSET] then -1 --Already expired

                                    when @MAXDATETIMEOFFSETFORDIFFFROMCURRENT <= [EVENTOFFSALE].[DATETIMEWITHOFFSET] then @MAXINT --Beyond datediff comparison

                                    else datediff(second,@CURRENTDATETIMEOFFSET, [EVENTOFFSALE].[DATETIMEWITHOFFSET]) 
                                end
                            else null 
                        end as [ONSALE]
                ) as [EXPIRATIONDELTA]
                outer apply (
                    select
                        case when [EXPIRATIONDELTA].[RESERVATION] <= 0 then 1 else 0 end as [RESERVATIONHASEXPIRED],
                        case when [EXPIRATIONDELTA].[DELIVERY] <= 0 or [EXPIRATIONDELTA].[ONSALE] <= 0 then 1 else 0 end [DELIVERYHASEXPIRED],
                        coalesce((
                            select min(EXPIRATION)
                            from (
                                select [EXPIRATIONDELTA].[DELIVERY] as [EXPIRATION]
                                union all
                                select [EXPIRATIONDELTA].[ONSALE] as [EXPIRATION]
                                union all
                                select [EXPIRATIONDELTA].[RESERVATION] as [EXPIRATION]
                            ) [DELTAS]
                        ),0) as [EXPIRATIONDELTA]        
                ) as [EXPIRATIONSUMMARY]
                where 
                    SALESORDERITEM.SALESORDERID = @CONTEXTID and
                    SALESORDERITEM.TYPECODE <> 3 and 
                    SALESORDERITEM.TYPECODE <> 4 and
                    not
                    (
                        SALESORDERITEM.TYPECODE = 16 and
                        @SALESTYPECODE = 2 and
                        @ISBASICCMS = 1 and
                        @ORDERSTATUSCODE = 0
                    )

                union all

                select
                    SALESORDERITEM.ID,
                    SALESORDERITEM.DESCRIPTION,
                    SALESORDERITEM.PRICE,
                    SALESORDERITEM.QUANTITY,
                    SALESORDERITEM.TOTAL,
                    SALESORDERITEM.TYPECODE,
                    SALESORDERITEM.DATA,
                    SALESORDERITEM.OPTIONS,
                    SALESORDERITEM.CALLBACKURL,
                    SALESORDERITEM.EXPIREDCALLBACKURL,
                    SALESORDERITEM.SYSTEMTYPENAME,
                    SALESORDERITEM.ASSEMBLYNAME,
                    SALESORDERITEM.ATTRIBUTES,
                    SALESORDERITEM.CATEGORYNAME,
                    SALESORDERITEM.ACKNOWLEDGEMENT,
                    null DATEADDED,
                    null EXPIRATIONDATE,
                    0 ISEXPIRED,
                    0 as [HASNOVALIDDELIVERYMETHOD],
                    0 as [EXPIRATIONDELTA]
                from dbo.SALESORDERITEM
                left join dbo.SALESORDERITEMFEE on
                    SALESORDERITEM.ID = SALESORDERITEMFEE.ID
                where 
                    SALESORDERITEM.SALESORDERID = @CONTEXTID and
                    SALESORDERITEM.TYPECODE = 3 and
                    SALESORDERITEMFEE.APPLIESTOCODE = 0

                union all

                select
                    SALESORDERITEM.ID,
                    SALESORDERITEM.DESCRIPTION,
                    SALESORDERITEM.PRICE,
                    SALESORDERITEM.QUANTITY,
                    SALESORDERITEM.TOTAL,
                    SALESORDERITEM.TYPECODE,
                    SALESORDERITEM.DATA,
                    SALESORDERITEM.OPTIONS,
                    SALESORDERITEM.CALLBACKURL,
                    SALESORDERITEM.EXPIREDCALLBACKURL,                    
                    SALESORDERITEM.SYSTEMTYPENAME,
                    SALESORDERITEM.ASSEMBLYNAME,
                    SALESORDERITEM.ATTRIBUTES,
                    SALESORDERITEM.CATEGORYNAME,
                    SALESORDERITEM.ACKNOWLEDGEMENT,
                    null DATEADDED,
                    null EXPIRATIONDATE,
                    0 ISEXPIRED,
                    0 as [HASNOVALIDDELIVERYMETHOD],
                    0 as [EXPIRATIONDELTA]
                from dbo.SALESORDERITEM
                left join dbo.SALESORDERITEMFEE on
                    SALESORDERITEM.ID = SALESORDERITEMFEE.ID
                where 
                    SALESORDERITEM.SALESORDERID = @CONTEXTID and
                    SALESORDERITEM.TYPECODE = 4