USP_DATALIST_ITINERARYCALENDAR

            CREATE procedure dbo.USP_DATALIST_ITINERARYCALENDAR(@ITINERARYID uniqueidentifier)
            as
                set nocount on;

                declare @ARRIVALDATE datetime;

                select @ARRIVALDATE = RESERVATION.ARRIVALDATE
                from dbo.ITINERARY
                    inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
                where ITINERARY.ID = @ITINERARYID

                select ITINERARYITEM.ID,
                       ITINERARYITEM.NAME,
                       ITINERARYITEM.STARTTIME,
                       ITINERARYITEM.ENDTIME,
                       ITINERARYITEM.PROGRAMID,
                       ITINERARYITEM.EVENTID,
                       ITINERARYITEM.NOTES,
                       ITINERARYITEM.STARTDATE,
                       ITINERARYITEM.ENDDATE,
                       ITINERARYITEM.ITEMTYPECODE,
                       case ITINERARYITEM.ITEMTYPECODE when 0 then 'DarkSeaGreen'
                        when 1 then 'CornFlowerBlue'
                        when 2 then 'PaleVioletRed'
                        when 3 then 'DarkRed'
                        else 'Black'
                        end as BACKCOLOR,
                       case ITINERARYITEM.ITEMTYPECODE when 3 then 'White'
                       else 'Black'
                       end as CAPTIONCOLOR,
                       ITINERARYITEM.INVALIDREASON,
                       COALESCE(EVENTLOCATION.NAME, dbo.UFN_EVENT_GETLOCATIONNAME(ITINERARYITEM.EVENTID)) as LOCATION,
                       dbo.UFN_ITINERARYITEMRESOURCE_GETRESOURCESTRING(ITINERARYITEM.ID) as RESOURCES,
                       dbo.UFN_ITINERARYITEMSTAFFRESOURCE_GETSTAFFRESOURCESTRING(ITINERARYITEM.ID) as STAFFRESOURCES,
                       ITINERARYITEM.ITEMTYPE as ITEMTYPE,
                       ITINERARYITEM.INVALIDREASONCODE,
                       case ITINERARYITEM.INVALIDREASONCODE 
                        when 10 then
                            coalesce(stuff((select '; ' + RESOURCE.NAME + ': ' + cast(CONFLICTRESOURCES.QUANTITYNEEDED as nvarchar(50))
                                               from dbo.UFN_EVENTCONFLICT_GETRESOURCESINCONFLICT(ITINERARYITEM.STARTDATETIME,
                                                                                                 ITINERARYITEM.ENDDATETIME,
                                                                                                 dbo.UFN_ITINERARYITEMRESOURCE_GETRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID),
                                                                                                 ITINERARYITEM.ITINERARYID,
                                                                                                 ITINERARYITEM.ID) as CONFLICTRESOURCES 
                                               inner join RESOURCE
                                                   on CONFLICTRESOURCES.RESOURCEID = RESOURCE.ID
                                               for XML PATH('')),1, 2, ''), '')
                        else
                         ''
                       end as RESOURCESINCONFLICT,
                       case  
                        when (ITINERARYITEM.INVALIDREASONCODE = 10) and (not ITINERARYITEM.EVENTLOCATIONID is null) then
                            coalesce(stuff((select ', ' + EVENTLOCATION.NAME 
                                               from dbo.UFN_EVENTCONFLICT_GETLOCATIONSINCONFLICT(ITINERARYITEM.STARTDATETIME,
                                                                                                 ITINERARYITEM.ENDDATETIME,
                                                                                                 '<LOCATIONS><ITEM><LOCATIONID>' + cast(ITINERARYITEM.EVENTLOCATIONID as nvarchar(36)) + '</LOCATIONID></ITEM></LOCATIONS>',
                                                                                                 ITINERARYITEM.ITINERARYID,
                                                                                                 ITINERARYITEM.ID) as CONFLICTLOCATIONS
                                               inner join EVENTLOCATION
                                                   on CONFLICTLOCATIONS.LOCATIONID = EVENTLOCATION.ID
                                               for XML PATH('')),1, 2, ''), '')
                        else
                         ''
                       end as LOCATIONSINCONFLICT,
                       case ITINERARYITEM.INVALIDREASONCODE
                        when 10 then
                            coalesce(stuff((select '; ' + VOLUNTEERTYPE.NAME + ': ' + cast(CONFLICTSTAFFRESOURCES.QUANTITYNEEDED as nvarchar(50))
                                               from dbo.UFN_EVENTCONFLICT_GETSTAFFRESOURCESINCONFLICT(ITINERARYITEM.STARTDATETIME,
                                                                                                        ITINERARYITEM.ENDDATETIME,
                                                                                                        dbo.UFN_ITINERARYITEM_GETSTAFFRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID),
                                                                                                        ITINERARYITEM.ITINERARYID,
                                                                                                        ITINERARYITEM.ID) as CONFLICTSTAFFRESOURCES 
                                               inner join dbo.VOLUNTEERTYPE
                                                   on CONFLICTSTAFFRESOURCES.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                                               for XML PATH('')),1, 2, ''), '')
                        else
                         ''
                       end as STAFFRESOURCESINCONFLICT,
                       case when (exists (select ITINERARYITEMSTAFFRESOURCE.ID 
                                            from dbo.ITINERARYITEMSTAFFRESOURCE 
                                            where ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID)) then 
                            1 
                        else 
                            0 
                        end as HASSTAFFRESOURCES
                from dbo.ITINERARYITEM
                    left join dbo.EVENTLOCATION on ITINERARYITEM.EVENTLOCATIONID = EVENTLOCATION.ID
                where ITINERARYID = @ITINERARYID