USP_DATALIST_BATCHTICKETPRINTJOBTICKETS

CREATE procedure dbo.USP_DATALIST_BATCHTICKETPRINTJOBTICKETS (
    @BATCHTICKETPRINTJOBID uniqueidentifier,
    @LOCATIONID uniqueidentifier = null,
    @EXCLUDEREFUNDED bit = 0
)
as
    set nocount on;

    select
        BATCHTICKETPRINTJOBTICKETLINK.ID,
        TICKETPRINTINFO.SEQUENCEID as TICKETNUMBER,
        isnull(EVENT.NAME, PROGRAM.NAME) as DESCRIPTION,
        EVENT.STARTDATETIME,
        case
            when TICKET.EVENTID is not null then
                dbo.UFN_EVENT_GETLOCATIONNAME(TICKET.EVENTID)
            else
                dbo.UFN_PROGRAM_GETLOCATIONNAME(TICKET.PROGRAMID)
        end as LOCATIONNAME,
        NF.NAME as CONSTITUENTNAME,
        BATCHTICKETPRINTJOBTICKETLINK.BATCHTICKETPRINTJOBID,
        SALESORDERITEM.SALESORDERID,
        case
            when TICKET.STATUSCODE = 2 then 1  -- Refunded
            else 0
        end as ISREFUNDED
    from
        dbo.BATCHTICKETPRINTJOBTICKETLINK
    inner join
        dbo.TICKETPRINTINFO on TICKETPRINTINFO.ID = BATCHTICKETPRINTJOBTICKETLINK.ID
    inner join
        dbo.TICKET on TICKET.ID = TICKETPRINTINFO.TICKETID
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
    inner join
        dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    left outer join
        dbo.PROGRAM on PROGRAM.ID = TICKET.PROGRAMID
    left outer join
        dbo.EVENT on EVENT.ID = TICKET.EVENTID
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(isnull(SALESORDER.RECIPIENTID, SALESORDER.CONSTITUENTID)) as NF
    where
        BATCHTICKETPRINTJOBTICKETLINK.BATCHTICKETPRINTJOBID = @BATCHTICKETPRINTJOBID
        and (@EXCLUDEREFUNDED = 0 or TICKET.STATUSCODE in (0,1))  -- Active, Reserved
        and (
            @LOCATIONID is null
            or (
                (
                    TICKET.EVENTID is null  -- Daily admission ticket
                    and @LOCATIONID in (
                        select PROGRAMLOCATION.EVENTLOCATIONID
                        from dbo.PROGRAMLOCATION
                        where PROGRAMLOCATION.PROGRAMID = PROGRAM.ID
                    )
                )
                or (
                    TICKET.EVENTID is not null  -- Scheduled event ticket
                    and (
                        EVENT.EVENTLOCATIONID = @LOCATIONID
                        or (
                            @LOCATIONID in (
                                select PROGRAMEVENTLOCATION.EVENTLOCATIONID
                                from dbo.PROGRAMEVENTLOCATION
                                where PROGRAMEVENTLOCATION.EVENTID = EVENT.ID
                            )
                        )
                    )
                )
            )
        )
    order by
        TICKETPRINTINFO.SEQUENCEID;

    return 0;