USP_ITINERARY_CALENDARITEMS

CREATE procedure dbo.USP_ITINERARY_CALENDARITEMS
(
  @ITINERARYID uniqueidentifier,
  @TYPECODE tinyint
)
as
begin
    set nocount on;

                select ID,
             ITEMNAME +
             case when INVALIDREASONCODE <> 0
              then '; NOT ADDED: '
              else ''
             end +
             case 
              when INVALIDREASONCODE = 0
              then ''
              when INVALIDREASONCODE <> 10
              then INVALIDREASON
              else 'Conflict exists - '
             end +
             case when LOCATIONSINCONFLICT is not null and len(LOCATIONSINCONFLICT) > 0
              then LOCATIONSINCONFLICT
              else ''
             end +
             case when RESOURCESINCONFLICT is not null and len(RESOURCESINCONFLICT) > 0 and LOCATIONSINCONFLICT is not null and len(LOCATIONSINCONFLICT) > 0
              then '; ' + RESOURCESINCONFLICT 
              when RESOURCESINCONFLICT is not null and len(RESOURCESINCONFLICT) > 0
              then RESOURCESINCONFLICT
              else ''
             end +
             case when STAFFRESOURCESINCONFLICT is not null and len(STAFFRESOURCESINCONFLICT) > 0 and LOCATIONSINCONFLICT is not null and len(LOCATIONSINCONFLICT) > 0
              then '; ' + STAFFRESOURCESINCONFLICT 
              when STAFFRESOURCESINCONFLICT is not null and len(STAFFRESOURCESINCONFLICT) > 0
              then STAFFRESOURCESINCONFLICT
              else ''
             end as NAME,
                       STARTTIME,
                       ENDTIME,
                       STARTDATE,
                       ENDDATE,
             case when LOCATION is not null and len(LOCATION) > 0
                    then LOCATION + char(10)
                    else ''
               end +
               case when RESOURCES is not null and len(RESOURCES) > 0
                    then RESOURCES + char(10)
                    else ''
               end +
               case when STAFFRESOURCES is not null and len(STAFFRESOURCES) > 0
                    then STAFFRESOURCES
                    else ''
               end as DESCRIPTION,
             INVALIDREASONCODE
          from
          (select
             ITINERARYITEM.ID,
                       ITINERARYITEM.NAME as ITEMNAME,
                       ITINERARYITEM.STARTTIME,
                       ITINERARYITEM.ENDTIME,
                       ITINERARYITEM.STARTDATE,
                       ITINERARYITEM.ENDDATE,
                       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.INVALIDREASONCODE,
             ITINERARYITEM.INVALIDREASON,
                       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
                from dbo.ITINERARYITEM
                    left join dbo.EVENTLOCATION on ITINERARYITEM.EVENTLOCATIONID = EVENTLOCATION.ID
                where ITINERARYID = @ITINERARYID and
              ITEMTYPECODE = @TYPECODE) T 
    end