USP_DATALIST_QUICKITINERARYPROGRAMEVENT

CREATE procedure dbo.USP_DATALIST_QUICKITINERARYPROGRAMEVENT
(
    @DATE date
)
as
    set nocount on;

    declare @CURRENTDATE datetime = getdate()
    declare @SALESMETHODID uniqueidentifier;

    select @SALESMETHODID = ID from dbo.SALESMETHOD where TYPECODE = 3;  -- Group Sales


    select 
        ID,
        1,
        NAME,
        null,
        null,
        null,
        DEFAULTRATESCALEID,
        DEFAULTPRICINGSTRUCTURECODE
    from
    (
        select top 100
            PROGRAM.ID, 
            PROGRAM.NAME,
            PROGRAM.DEFAULTRATESCALEID,
            PROGRAM.DEFAULTPRICINGSTRUCTURECODE
        from
            dbo.PROGRAM
        inner join
            dbo.PROGRAMSALESMETHOD on PROGRAMSALESMETHOD.PROGRAMID = PROGRAM.ID
        where 
            PROGRAM.ISACTIVE = 1 and
            PROGRAM.ISDAILYADMISSION = 1 and
            PROGRAMSALESMETHOD.SALESMETHODID = @SALESMETHODID
        order by NAME
    ) DA

    union all

    select
        ID,
        0,
        NAME,
        STARTTIME,
        ENDTIME,
        QUANTITY,
        DEFAULTRATESCALEID,
        DEFAULTPRICINGSTRUCTURECODE
    from
    (
        select top 100
            EVENT.ID as ID,
            EVENT.NAME as NAME,
            EVENT.STARTTIME as STARTTIME,
            EVENT.ENDTIME as ENDTIME, 
            cast(EVENTAVAILABILITY.QUANTITY as int) as QUANTITY,
            PROGRAM.DEFAULTRATESCALEID,
            PROGRAM.DEFAULTPRICINGSTRUCTURECODE
        from
            dbo.EVENT
        inner join
            dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
        inner join
            dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS() as EVENTAVAILABILITY on EVENTAVAILABILITY.EVENTID = EVENT.ID
        where
            EVENT.ISACTIVE = 1 and
            PROGRAM.ISACTIVE = 1 and
            PROGRAM.ISPREREGISTERED = 0 and
            EVENT.STARTDATE = @DATE and
            dbo.UFN_EVENT_ISONSALE(EVENT.ID, @CURRENTDATE, 3) = 1 and
            not exists
            (
                select 1 
                from dbo.ITINERARYITEM
                inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                inner join dbo.SALESORDER on ITINERARY.RESERVATIONID = SALESORDER.ID
                where SALESORDER.STATUSCODE <> 5 -- MDC - Ignore cancelled

                    and ITINERARYITEM.BLOCKEVENT = 1 
                    and ITINERARYITEM.EVENTID = EVENT.ID
            ) and
            EVENTAVAILABILITY.QUANTITY > 0
        order by EVENT.STARTTIME
    ) SE
    where @DATE is not null