UFN_EVENT_GETAVAILABILITYWITHEVENTS_BYDATE

CREATE function dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS_BYDATE
(
    @DATESELECTTYPE tinyint = 0,
    @STARTDATETIMEWITHOFFSET datetimeoffset = null,
    @ENDDATETIMEWITHOFFSET datetimeoffset = null,
    @CURRENTDATETIMEWITHOFFSET datetimeoffset = null,
    @SALESMETHODID uniqueidentifier = null,
    @SHOWPAST bit = null,
    @STARTDATE date,
    @ENDDATE date,
    @CURRENTDATE datetime
) 
returns table
as return
    with ORDERED_CTE 
    as
    (
        select
            SALESORDERITEMTICKET.EVENTID,
            sum(SALESORDERITEM.QUANTITY) as ORDEREDQUANTITY
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        inner join dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        where SALESORDER.STATUSCODE not in (1, 5) -- exclude complete and cancelled

        group by SALESORDERITEMTICKET.EVENTID
    ),
    TICKET_CTE as
    (
        select
            EVENTID,
            count(ID) as TICKETQUANTITY
        from dbo.TICKET
        where
            TICKET.STATUSCODE in (0,1)
        group by EVENTID
    ),
    EXPIRED_CTE as
    (
        select SALESORDERITEMTICKET.EVENTID,
            sum(SALESORDERITEM.QUANTITY) as EXPIREDQUANTITY
        from dbo.SALESORDERRESERVEDITEM
        inner join dbo.SALESORDERITEMTICKET
            on SALESORDERITEMTICKET.ID = SALESORDERRESERVEDITEM.ID
        inner join dbo.SALESORDERITEM
            on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
        inner join dbo.SALESORDER
            on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        where 
            EXPIRATIONDATE <= @CURRENTDATE and
            SALESORDER.STATUSCODE <> 7
        group by SALESORDERITEMTICKET.EVENTID
    )
    select
        EVENT.ID as EVENTID,
        EVENT.CAPACITY - 
            (
                coalesce(TICKET_CTE.TICKETQUANTITY,0) 
                + coalesce(ORDERED_CTE.ORDEREDQUANTITY,0)
                - coalesce(EXPIRED_CTE.EXPIREDQUANTITY, 0)
            ) as QUANTITY,
        [EVENTSTATUSCODE].[CODE] as [EVENTSTATUSCODE],
        case 
            when (@CURRENTDATETIMEWITHOFFSET between [EVENTONSALESDATETIMEWITHOFFSET].[TIME] and [EVENTOFFSALESDATETIMEWITHOFFSET].[TIME]) then 1
            else 0
        end AVAILABLEFORSALE
    from dbo.EVENT
    inner join dbo.EVENTSALESMETHOD 
        on 
            [EVENT].ID = EVENTSALESMETHOD.EVENTID and 
            [EVENTSALESMETHOD].[SALESMETHODID] = @SALESMETHODID and
            [EVENTSALESMETHOD].[ONSALEDATETIMEWITHOFFSET] <= @CURRENTDATETIMEWITHOFFSET and
            (
                @DATESELECTTYPE = 0
                or EVENT.STARTDATE between @STARTDATE and @ENDDATE
            )
    left join dbo.PROGRAMSALESMETHOD 
        on 
            [EVENT].PROGRAMID = PROGRAMSALESMETHOD.PROGRAMID and 
            [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
    cross apply (
        select
            case 
                when [PROGRAMSALESMETHOD].[ONSALETYPECODE] is not null then
                    case [PROGRAMSALESMETHOD].[ONSALETYPECODE] 
                        when 0 then [EVENT].DATEADDED  -- Immediately available

                        when 1 then [PROGRAMSALESMETHOD].ONSALEDATE
                        when 2 then dateadd(dd, -1 * [PROGRAMSALESMETHOD].[ONSALETIMEBEFORE], [EVENT].[STARTDATETIMEWITHOFFSET])
                        when 3 then dateadd(ww, -1 * [PROGRAMSALESMETHOD].[ONSALETIMEBEFORE], [EVENT].[STARTDATETIMEWITHOFFSET])
                        when 4 then dateadd(mm, -1 * [PROGRAMSALESMETHOD].[ONSALETIMEBEFORE], [EVENT].[STARTDATETIMEWITHOFFSET])
                    end
                else [EVENT].DATEADDED
            end as [TIME]
    ) [EVENTONSALESDATETIMEWITHOFFSET]
    cross 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 [TIME]
    ) [EVENTOFFSALESDATETIMEWITHOFFSET]
    cross apply (
        select 
            case 
                when (@CURRENTDATETIMEWITHOFFSET between [EVENT].[STARTDATETIMEWITHOFFSET] and [EVENTOFFSALESDATETIMEWITHOFFSET].[TIME]) 
                    then 1 --In progress event

                when @CURRENTDATETIMEWITHOFFSET < [EVENTOFFSALESDATETIMEWITHOFFSET].[TIME] then 0 --Future Event

                else 2 --Past Event

            end as [CODE]
    ) as [EVENTSTATUSCODE]
    left join TICKET_CTE on
        TICKET_CTE.EVENTID = EVENT.ID
    left join ORDERED_CTE on
        ORDERED_CTE.EVENTID = EVENT.ID
    left join EXPIRED_CTE on
        EXPIRED_CTE.EVENTID = EVENT.ID
    where
        (@SHOWPAST = 1) or
        [EVENTSTATUSCODE].[CODE] in (0,1)