UFN_TICKET_TEMPLATEINFORMATION

CREATE function dbo.UFN_TICKET_TEMPLATEINFORMATION
(
    @ORDERID uniqueidentifier = null,
    @PROGRAMID uniqueidentifier = null,
    @REPRINTJOB uniqueidentifier = null,
    @TICKETPRINTINFOID uniqueidentifier = null
)
returns @TICKETS table (
    PROGRAMNAME nvarchar(100),
    EVENTNAME nvarchar(100),
    LOCATIONNAME nvarchar(100),
    STARTDATETIME datetime,
    PRICETYPE nvarchar(100),
    PRICE money,
    TICKETNUMBER integer,
    SALESMETHODTYPE nvarchar(13),
    DISPLAYTIME nvarchar(8),
    ORGANIZATIONNAME nvarchar(100)
)
with execute as caller
as begin

    declare @ORGANIZATIONNAME nvarchar(100);

    select 
        @ORGANIZATIONNAME = NAME 
    from dbo.ORGANIZATIONINFORMATION;

    if @TICKETPRINTINFOID is not null
    begin
        insert into @TICKETS (PROGRAMNAME, EVENTNAME, LOCATIONNAME, STARTDATETIME, PRICETYPE, PRICE, TICKETNUMBER, SALESMETHODTYPE, DISPLAYTIME, ORGANIZATIONNAME)
        select
            PROGRAM.NAME as PROGRAMNAME,
            isnull(EVENT.NAME, PROGRAM.NAME) as EVENTNAME,
            case
                when TICKET.EVENTID is not null then
                (
                    select top 1 EVENTLOCATION.NAME
                    from dbo.PROGRAMEVENTLOCATION 
                    inner join dbo.EVENTLOCATION EVENTLOCATION 
                        on PROGRAMEVENTLOCATION.EVENTLOCATIONID = EVENTLOCATION.ID
                    where 
                        PROGRAMEVENTLOCATION.EVENTID = TICKET.EVENTID
                    order by 
                        PROGRAMEVENTLOCATION.SEQUENCE asc
                )
                when TICKET.PROGRAMID is not null then 
                (
                    select top 1 EVENTLOCATION.NAME
                    from dbo.PROGRAMLOCATION
                    inner join dbo.EVENTLOCATION 
                        on EVENTLOCATION.ID = PROGRAMLOCATION.EVENTLOCATIONID
                    where 
                        PROGRAMLOCATION.PROGRAMID = TICKET.PROGRAMID
                    order by 
                        PROGRAMLOCATION.SEQUENCE asc
                )
            end as LOCATIONNAME,
            EVENT.STARTDATETIME ,
            PRICETYPECODE.DESCRIPTION as PRICETYPE,
            TICKET.PRICE,
            TICKETPRINTINFO.SEQUENCEID as TICKETNUMBER,
            SALESORDER.SALESMETHODTYPE,
            dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENT.STARTTIME) as DISPLAYTIME,
            @ORGANIZATIONNAME as ORGANIZATIONNAME
        from dbo.TICKET
        inner join dbo.TICKETPRINTINFO
            on TICKET.ID = TICKETPRINTINFO.TICKETID
        inner join dbo.PRICETYPECODE 
            on TICKET.PRICETYPECODEID = PRICETYPECODE.ID
        inner join dbo.SALESORDERITEMTICKET 
            on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
        inner join dbo.SALESORDERITEM 
            on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
        inner join dbo.SALESORDER 
            on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        left outer join dbo.PROGRAM 
            on PROGRAM.ID = TICKET.PROGRAMID
        left outer join dbo.EVENT 
            on EVENT.ID = TICKET.EVENTID
        where
            TICKETPRINTINFO.ID = @TICKETPRINTINFOID
    end
    else
    begin
        if @ORDERID is null
        begin
            declare @PROGRAMNAME nvarchar(100);
            declare @LOCATIONNAME nvarchar(100);
            declare @ISDAILYADMISSION bit = 0;

            select
                @PROGRAMNAME = PROGRAM.NAME,
                @LOCATIONNAME = EVENTLOCATION.NAME,
                @ISDAILYADMISSION = PROGRAM.ISDAILYADMISSION
            from
                dbo.PROGRAM
                left outer join dbo.PROGRAMLOCATION on PROGRAMLOCATION.PROGRAMID = PROGRAM.ID
                left outer join dbo.EVENTLOCATION EVENTLOCATION on PROGRAMLOCATION.EVENTLOCATIONID = EVENTLOCATION.ID
            where
                PROGRAM.ID = @PROGRAMID;

            insert into @TICKETS (PROGRAMNAME, EVENTNAME, LOCATIONNAME, STARTDATETIME, PRICETYPE, PRICE, TICKETNUMBER, SALESMETHODTYPE, DISPLAYTIME, ORGANIZATIONNAME)
            select
                coalesce(@PROGRAMNAME, 'Program Name') as PROGRAMNAME,
                coalesce(@PROGRAMNAME, 'Event Name') as EVENTNAME,
                coalesce(@LOCATIONNAME, 'Event location') as LOCATIONNAME,
                case @ISDAILYADMISSION
                    when 1 then null
                    when 0 then getdate()
                end as EVENTTIME,
                'Adult' as PRICETYPE,
                12.34 as PRICE,
                99999999 as TICKETNUMBER,
                'Sales Method' as SALESMETHODTYPE,
                case @ISDAILYADMISSION
                    when 1 then null
                    when 0 then dbo.UFN_HOURMINUTE_DISPLAYTIME(dbo.UFN_HOURMINUTE_GETFROMDATE(getdate()))
                end as EVENTDISPLAYTIME,
                @ORGANIZATIONNAME as ORGANIZATIONNAME
        end
        else
        begin
            -- Ugly branch because @REPRINTJOB is optional (MAY HAVE TO FIX REPORT BUGS IN MULTIPLE PLACES)

            if @REPRINTJOB is null begin
                insert into @TICKETS (PROGRAMNAME, EVENTNAME, LOCATIONNAME, STARTDATETIME, PRICETYPE, PRICE, TICKETNUMBER, SALESMETHODTYPE, DISPLAYTIME, ORGANIZATIONNAME)
                select
                    PROGRAM.NAME as PROGRAMNAME,
                    isnull(EVENT.NAME, PROGRAM.NAME) as EVENTNAME,
                    case
                        when TICKET.EVENTID is not null then dbo.UFN_EVENT_GETLOCATIONNAME(TICKET.EVENTID)
                        else dbo.UFN_PROGRAM_GETLOCATIONNAME(TICKET.PROGRAMID)
                    end as LOCATIONNAME,
                    case
                        when TICKET.EVENTID is not null then EVENT.STARTDATETIME
                    end as EVENTTIME,
                    PRICETYPECODE.DESCRIPTION as PRICETYPE,
                    TICKET.PRICE,
                    TICKET.TICKETNUMBER,
                    SO.SALESMETHODTYPE,
                    case
                        when TICKET.EVENTID is not null then dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENT.STARTTIME)
                    end as EVENTDISPLAYTIME,
                    @ORGANIZATIONNAME as ORGANIZATIONNAME
                from
                    dbo.TICKET
                    inner join dbo.PROGRAM on PROGRAM.ID = TICKET.PROGRAMID
                    inner join dbo.PRICETYPECODE on TICKET.PRICETYPECODEID = PRICETYPECODE.ID
                    inner join dbo.SALESORDERITEMTICKET SOIT on TICKET.SALESORDERITEMTICKETID = SOIT.ID
                    inner join dbo.SALESORDERITEM SOI on SOIT.ID = SOI.ID
                    inner join dbo.SALESORDER SO on SOI.SALESORDERID = SO.ID
                    left join dbo.EVENT on TICKET.EVENTID = EVENT.ID
                where
                    SO.ID = @ORDERID
                    and TICKET.PROGRAMID = @PROGRAMID;
            end else begin
                insert into @TICKETS (PROGRAMNAME, EVENTNAME, LOCATIONNAME, STARTDATETIME, PRICETYPE, PRICE, TICKETNUMBER, SALESMETHODTYPE, DISPLAYTIME, ORGANIZATIONNAME)
                select
                    PROGRAM.NAME as PROGRAMNAME,
                    isnull(EVENT.NAME, PROGRAM.NAME) as EVENTNAME,
                    case
                        when TICKET.EVENTID is not null then dbo.UFN_EVENT_GETLOCATIONNAME(TICKET.EVENTID)
                        else dbo.UFN_PROGRAM_GETLOCATIONNAME(TICKET.PROGRAMID)
                    end as LOCATIONNAME,
                    case
                        when TICKET.EVENTID is not null then EVENT.STARTDATETIME
                    end as EVENTTIME,
                    PRICETYPECODE.DESCRIPTION as PRICETYPE,
                    TICKET.PRICE,
                    TICKET.TICKETNUMBER,
                    SO.SALESMETHODTYPE,
                    case
                        when TICKET.EVENTID is not null then dbo.UFN_HOURMINUTE_DISPLAYTIME(EVENT.STARTTIME)
                        when TICKET.PROGRAMID is not null then null
                    end as EVENTDISPLAYTIME,
                    @ORGANIZATIONNAME as ORGANIZATIONNAME
                from
                    dbo.TICKET
                    inner join dbo.PROGRAM on PROGRAM.ID = TICKET.PROGRAMID
                    inner join dbo.PRICETYPECODE on TICKET.PRICETYPECODEID = PRICETYPECODE.ID
                    inner join dbo.SALESORDERITEMTICKET SOIT on TICKET.SALESORDERITEMTICKETID = SOIT.ID
                    inner join dbo.SALESORDERITEM SOI on SOIT.ID = SOI.ID
                    inner join dbo.SALESORDER SO on SOI.SALESORDERID = SO.ID
                    left join dbo.EVENT on TICKET.EVENTID = EVENT.ID
                    inner join dbo.TICKETPRINTINFO on TICKETPRINTINFO.TICKETID = TICKET.ID
                where
                    SO.ID = @ORDERID
                    and TICKET.PROGRAMID = @PROGRAMID
                    and TICKETPRINTINFO.REPRINTJOB = @REPRINTJOB;
            end
        end
    end

    return;
end