USP_REPORT_ITINERARY

create procedure dbo.USP_REPORT_ITINERARY 
(
    @RESERVATIONID uniqueidentifier = null,
    @ITINERARYID uniqueidentifier = null, 
    @INCLUDERESOURCES bit = null 
)
as
set nocount on;

begin
    if @INCLUDERESOURCES = 0
    begin
        select
            case
                when LEADERID is null then
                    ITINERARY.NAME
                else ITINERARY.NAME + ' - ' + CONSTITUENTNAMEFORMAT.NAME
            end ITINERARYNAME,
            RESERVATION.NAME RESERVATIONNAME,
            ITINERARYITEM.NAME ITEMNAME,
            case ITINERARYITEM.ITEMTYPECODE 
                when 0 then 
                    dbo.UFN_EVENT_GETLOCATIONNAME(ITINERARYITEM.EVENTID) 
                when 1 then 
                    dbo.UFN_PROGRAM_GETLOCATIONNAME(ITINERARYITEM.PROGRAMID) 
                when 2 then 
                    dbo.UFN_EVENTLOCATION_GETNAME(ITINERARYITEM.EVENTLOCATIONID) 
                else 
                    ''
            end LOCATIONS,
            ITINERARYITEM.STARTDATETIME as ITEMSTARTTIME,
            ITINERARYITEM.ENDDATETIME as ITEMENDTIME,
            dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID) VISITORCOUNT,
            ITINERARYITEM.STARTDATE as ITINERARYARRIVALDATE,
            ITINERARY.ID as ITINERARYID,
            null as QUANTITY,
            ITINERARYITEM.ID as ITINERARYITEMID,
            0 as ISITEMRESOURCE,
            null as RESOURCEID,
            null as RESOURCENAME,
            null as ASSIGNEDRESOURCES,
            ITINERARYITEM.ENDDATE as ITEMENDDATE,
            ITINERARYITEM.STARTDATE as ITEMSTARTDATE
        from 
            dbo.RESERVATION
        inner join 
            dbo.ITINERARY on RESERVATION.ID = ITINERARY.RESERVATIONID
        inner join 
            dbo.ITINERARYITEM on ITINERARY.ID = ITINERARYITEM.ITINERARYID
        outer apply 
            dbo.UFN_CONSTITUENT_DISPLAYNAME(ITINERARY.LEADERID) as CONSTITUENTNAMEFORMAT
        where 
            RESERVATION.ID = @RESERVATIONID 
            and (ITINERARY.ID = @ITINERARYID or @ITINERARYID is null)
            and ITINERARYITEM.ITEMTYPECODE <> 3
        order by 
            ITINERARY.NAME, ITINERARYARRIVALDATE, ITEMSTARTTIME
    end

    else
    begin
        declare @RESERVATIONNAME nvarchar(100);

        select 
            @RESERVATIONNAME = NAME
        from 
            dbo.RESERVATION
        where 
            ID = @RESERVATIONID

        declare @ITINERARY table
        (
            ITINERARYNAME nvarchar(255),
            RESERVATIONNAME nvarchar(100),
            ITEMNAME nvarchar(255),
            LOCATIONS nvarchar(255),
            ITEMSTARTTIME datetime,
            ITEMENDTIME datetime,
            VISITORCOUNT integer,
            ITINERARYARRIVALDATE datetime,
            ITINERARYID uniqueidentifier,
            QUANTITY integer,
            ITINERARYITEMID uniqueidentifier,
            ISRESOURCE bit,
            RESOURCEID uniqueidentifier,
            RESOURCENAME nvarchar(255),
            ASSIGNEDRESOURCES nvarchar(max),
            ITEMENDDATE date,
            ITEMSTARTDATE date
        );

        insert into @ITINERARY
        select
            case
                when LEADERID is null then
                    ITINERARY.NAME
                else 
                    ITINERARY.NAME + ' - ' + dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARY.LEADERID) 
            end ITINERARYNAME,
            RESERVATION.NAME RESERVATIONNAME,
            ITINERARYITEM.NAME ITEMNAME,
            case ITINERARYITEM.ITEMTYPECODE 
                when 0 then 
                    dbo.UFN_EVENT_GETLOCATIONNAME(ITINERARYITEM.EVENTID) 
                when 1 then 
                    dbo.UFN_PROGRAM_GETLOCATIONNAME(ITINERARYITEM.PROGRAMID) 
                when 2 then 
                    dbo.UFN_EVENTLOCATION_GETNAME(ITINERARYITEM.EVENTLOCATIONID) 
                else 
                    ''
            end LOCATIONS,
            ITINERARYITEM.STARTDATETIME as ITEMSTARTTIME,
            ITINERARYITEM.ENDDATETIME as ITEMENDTIME,
            dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID) VISITORCOUNT,
            ITINERARYITEM.STARTDATE as ITINERARYARRIVALDATE,
            ITINERARY.ID as ITINERARYID,
            null,
            ITINERARYITEM.ID,
            0,
            null as RESOURCEID,
            null as RESOURCENAME,
            null as ASSIGNEDRESOURCES,
            ITINERARYITEM.ENDDATE as ITEMENDDATE,
            ITINERARYITEM.STARTDATE as ITEMSTARTDATE
        from 
            dbo.RESERVATION
        inner join 
            dbo.ITINERARY on RESERVATION.ID = ITINERARY.RESERVATIONID
        inner join 
            dbo.ITINERARYITEM on ITINERARY.ID = ITINERARYITEM.ITINERARYID
        where 
            RESERVATION.ID = @RESERVATIONID 
            and (ITINERARY.ID = @ITINERARYID or @ITINERARYID is null)
            and ITINERARYITEM.ITEMTYPECODE <> 3

        union all

        select
            case
                when ITINERARY.LEADERID is null then
                    ITINERARY.NAME
                else 
                    ITINERARY.NAME + ' - ' + dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARY.LEADERID) 
            end ITINERARYNAME,
            @RESERVATIONNAME,
            NULL ITEMNAME,
            null,
            ITINERARY.STARTDATETIME,
            ITINERARY.ENDDATETIME,
            dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID) VISITORCOUNT,
            null,
            ITINERARYSTAFFRESOURCE.ITINERARYID,
            ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
            NULL,
            1,
            ITINERARYSTAFFRESOURCE.ID as RESOURCEID,
            dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID) RESOURCENAME,
            case when (ITINERARYSTAFFRESOURCE.FILLEDBYCODE != 0) or (ITINERARY.STARTDATETIME is null) then 
                dbo.UFN_ITINERARYSTAFFRESOURCE_GETASSIGNMENTSTRING(ITINERARYSTAFFRESOURCE.ID) 
            else 
                dbo.UFN_JOBOCCURRENCE_GETASSIGNMENTSTRING(ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID) 
            end as ASSIGNEDRESOURCES,
            null as ITEMENDDATE,
            null as ITEMSTARTDATE
        from 
            dbo.SALESORDERITEM
        inner join 
            dbo.SALESORDERITEMITINERARYSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID
        inner join 
            dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
        inner join 
            dbo.ITINERARY on ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
        where 
            SALESORDERITEM.SALESORDERID = @RESERVATIONID
            and (ITINERARYSTAFFRESOURCE.ITINERARYID = @ITINERARYID or @ITINERARYID is null)
            and SALESORDERITEM.TYPECODE = 10

        union all

        select
            case
                when ITINERARY.LEADERID is null then
                    ITINERARY.NAME
                else 
                    ITINERARY.NAME + ' - ' + dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARY.LEADERID) 
            end ITINERARYNAME,
            @RESERVATIONNAME,
            NULL ITEMNAME,
            null,
            ITINERARY.STARTDATETIME,
            ITINERARY.ENDDATETIME,
            dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID) VISITORCOUNT,
            null,
            ITINERARY.ID,
            case RESOURCE.ISPERTICKETITEM
                when 0 then
                    ITINERARYRESOURCE.QUANTITYNEEDED
                else
                    dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID) * ITINERARYRESOURCE.PERTICKETQUANTITY, ITINERARYRESOURCE.PERTICKETDIVISOR)
            end as QUANTITY,
            null,
            1,
            ITINERARYRESOURCE.ID as RESOURCEID,
            RESOURCE.NAME as RESOURCENAME,
            null as ASSIGNEDRESOURCES,
            null as ITEMENDDATE,
            null as ITEMSTARTDATE
        from 
            dbo.SALESORDERITEM
        inner join 
            dbo.SALESORDERITEMITINERARYRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID
        inner join 
            dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
        inner join 
            dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
        inner join 
            dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
        where 
            SALESORDERITEM.SALESORDERID = @RESERVATIONID
            and (ITINERARY.ID = @ITINERARYID or @ITINERARYID is null)
            and SALESORDERITEM.TYPECODE = 8

        declare @ITEMRESOURCES table
        (
            ITINERARYNAME nvarchar(255),
            RESERVATIONNAME nvarchar(100),
            ITEMNAME nvarchar(255),
            LOCATIONS nvarchar(255),
            ITEMSTARTTIME datetime,
            ITEMENDTIME datetime,
            VISITORCOUNT integer,
            ITINERARYARRIVALDATE datetime,
            ITINERARYID uniqueidentifier,
            QUANTITY integer,
            ITINERARYITEMID uniqueidentifier,
            ISRESOURCE bit,
            RESOURCEID uniqueidentifier,
            RESOURCENAME nvarchar(255),
            ASSIGNEDRESOURCES nvarchar(max)
        );

        insert into @ITEMRESOURCES
        select 
            case
                when ITINERARY.LEADERID is null then
                    ITINERARY.NAME
                else ITINERARY.NAME + ' - ' + dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARY.LEADERID) 
            end ITINERARYNAME,
            @RESERVATIONNAME,
            null,
            null,
            null,
            null,
            dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID) VISITORCOUNT,
            ITINERARYITEM.STARTDATE as ITINERARYARRIVALDATE,
            ITINERARYITEM.ITINERARYID,
            ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
            ITINERARYITEM.ID,
            1,
            ITINERARYITEMSTAFFRESOURCE.ID as RESOURCEID,
            dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID) RESOURCENAME,
            case when (ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE != 0) or (ITINERARY.STARTDATETIME is null) then 
                dbo.UFN_ITINERARYITEMSTAFFRESOURCE_GETASSIGNMENTSTRING(ITINERARYITEMSTAFFRESOURCE.ID) 
            else 
                dbo.UFN_JOBOCCURRENCE_GETASSIGNMENTSTRING(ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID) 
            end as ASSIGNEDRESOURCES
        from 
            dbo.SALESORDERITEM
        inner join 
            dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID
        inner join 
            dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
        inner join 
            dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
        inner join 
            dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
        where 
            SALESORDERITEM.SALESORDERID = @RESERVATIONID
            and (ITINERARY.ID = @ITINERARYID or @ITINERARYID is null)
            and SALESORDERITEM.TYPECODE = 11
            and ITINERARYITEM.ITEMTYPECODE <> 3

        union all

        select 
            case
                when ITINERARY.LEADERID is null then
                    ITINERARY.NAME
                else ITINERARY.NAME + ' - ' + dbo.UFN_CONSTITUENT_BUILDNAME(ITINERARY.LEADERID) 
            end ITINERARYNAME,
            @RESERVATIONNAME,
            null,
            null,
            null,
            null,
            dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID) VISITORCOUNT,
            ITINERARYITEM.STARTDATE as ITINERARYARRIVALDATE,
            ITINERARY.ID,
            case RESOURCE.ISPERTICKETITEM
                when 0 then
                    ITINERARYITEMRESOURCE.QUANTITYNEEDED
                else
                    dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(dbo.UFN_ITINERARY_TOTALVISITORCOUNT(ITINERARY.ID) * ITINERARYITEMRESOURCE.PERTICKETQUANTITY, ITINERARYITEMRESOURCE.PERTICKETDIVISOR)
            end as QUANTITY,
            ITINERARYITEM.ID,
            1,
            ITINERARYITEMRESOURCE.ID as RESOURCEID,
            RESOURCE.NAME as RESOURCENAME,
            null as ASSIGNEDRESOURCES
        from 
            dbo.SALESORDERITEM
        inner join 
            dbo.SALESORDERITEMITINERARYITEMRESOURCE on SALESORDERITEM.ID = SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID
        inner join 
            dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
        inner join 
            dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
        inner join 
            dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
        inner join 
            dbo.RESOURCE on RESOURCE.ID = ITINERARYITEMRESOURCE.RESOURCEID
        where 
            SALESORDERITEM.SALESORDERID = @RESERVATIONID
            and (ITINERARY.ID = @ITINERARYID or @ITINERARYID is null)
            and SALESORDERITEM.TYPECODE = 9    
            and ITINERARYITEM.ITEMTYPECODE <> 3

        select
            IT.ITINERARYNAME ITINERARYNAME,
            IT.RESERVATIONNAME RESERVATIONNAME,
            IT.ITEMNAME ITEMNAME,
            IT.LOCATIONS LOCATIONS,
            IT.ITEMSTARTTIME ITEMSTARTTIME,
            IT.ITEMENDTIME ITEMENDTIME,
            IT.VISITORCOUNT VISITORCOUNT,
            IT.ITINERARYARRIVALDATE ITINERARYARRIVALDATE,
            IT.ITINERARYID ITINERARYID,
            coalesce(IT.QUANTITY, R.QUANTITY, null) QUANTITY,
            IT.ITINERARYITEMID ITINERARYITEMID,
            IT.ISRESOURCE ISITEMRESOURCE,
            R.RESOURCEID RESOURCEID,
            isnull(IT.RESOURCENAME, R.RESOURCENAME) RESOURCENAME,
            IT.RESOURCEID ITINERARYRESOURCEID,
            isnull(IT.ASSIGNEDRESOURCES, R.ASSIGNEDRESOURCES) ASSIGNEDRESOURCES,
            IT.ITEMENDDATE,
            IT.ITEMSTARTDATE
        from 
            @ITINERARY IT
        left join 
            @ITEMRESOURCES R on IT.ITINERARYITEMID = R.ITINERARYITEMID
        order by 
            ITINERARYNAME, ITINERARYARRIVALDATE, ITEMSTARTTIME
    end
end