USP_DATALIST_STAFFRESOURCESCHEDULE

                CREATE procedure dbo.USP_DATALIST_STAFFRESOURCESCHEDULE
                (
                    @VOLUNTEERTYPEID uniqueidentifier,
                    @TYPEFILTER tinyint = 0,
                    @PROGRAMID uniqueidentifier = null,
                    @DATERANGE tinyint = 10
                )
                as
                    set nocount on;

                    declare @STARTDATE datetime = null;
                    declare @ENDDATE datetime = null;

                    exec dbo.USP_RESOLVEDATEFILTER @DATERANGE, @STARTDATE output, @ENDDATE output

                    -- Create a return table

                    declare @RESULTS table
                    (
                        EVENTID uniqueidentifier,
                        ITINERARYID uniqueidentifier,
                        NAME nvarchar(100),
                        TYPE nvarchar(100),
                        TYPECODE tinyInt,
                        LOCATION nvarchar(500),
                        CAPACITY nvarchar(50),
                        STARTDATE datetime,
                        STARTTIME UDT_HOURMINUTE,
                        ENDDATE datetime,
                        ENDTIME UDT_HOURMINUTE
                    )

                    -- First process events

                    if @TYPEFILTER = 0 or @TYPEFILTER = 1
                    begin
                        insert into @RESULTS
                        (
                            EVENTID,
                            NAME,
                            TYPE,
                            TYPECODE,
                            LOCATION,
                            CAPACITY,
                            STARTDATE,
                            STARTTIME,
                            ENDDATE,
                            ENDTIME
                        )
                        select 
                            EVENT.ID,
                            EVENT.NAME,
                            'Event',
                            0,
                            dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATIONNAME,
                            cast(EVENT.CAPACITY as nvarchar(20)),
                            EVENT.STARTDATE,
                            EVENT.STARTTIME,
                            EVENT.ENDDATE,
                            EVENT.ENDTIME
                        from dbo.EVENTSTAFFRESOURCE
                        inner join dbo.EVENT on 
                            EVENTSTAFFRESOURCE.EVENTID = EVENT.ID
                        where 
                            EVENTSTAFFRESOURCE.VOLUNTEERTYPEID = @VOLUNTEERTYPEID and
                            (@PROGRAMID is null or @PROGRAMID = EVENT.PROGRAMID) and
                            (
                                EVENT.STARTDATE between @STARTDATE and @ENDDATE or
                                EVENT.ENDDATE between @STARTDATE and @ENDDATE or
                                (
                                    EVENT.STARTDATE >= @STARTDATE and 
                                    EVENT.ENDDATE <= @ENDDATE
                                )
                            )
                    end

                    -- Next process itineraries

                    if (@TYPEFILTER = 0 or @TYPEFILTER = 2) and @PROGRAMID is null
                        begin
                        insert into @RESULTS
                        (
                            ITINERARYID,
                            NAME,
                            TYPE,
                            TYPECODE,
                            LOCATION,
                            CAPACITY,
                            STARTDATE,
                            STARTTIME,
                            ENDDATE,
                            ENDTIME
                        )
                        select 
                            ITINERARY.ID,
                            ITINERARY.NAME,
                            'Itinerary',
                            1,
                            '',
                            '',
                            ITINERARY.STARTDATETIME,
                            dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.STARTDATETIME),
                            ITINERARY.ENDDATETIME,
                            dbo.UFN_HOURMINUTE_GETFROMDATE(ITINERARY.ENDDATETIME)
                        from dbo.ITINERARYSTAFFRESOURCE
                        inner join dbo.ITINERARY on 
                            ITINERARYSTAFFRESOURCE.ITINERARYID = ITINERARY.ID
                        inner join dbo.SALESORDER on
                            ITINERARY.RESERVATIONID = SALESORDER.ID
                        where 
                            SALESORDER.STATUSCODE <> 5 and
                            ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = @VOLUNTEERTYPEID and
                            exists (
                                select ITINERARYITEM.ID from dbo.ITINERARYITEM 
                                where ITINERARYITEM.ITINERARYID = ITINERARY.ID
                            ) and
                            (
                                (ITINERARY.STARTDATETIME is null and @DATERANGE = 10) or 
                                ITINERARY.STARTDATETIME between @STARTDATE and @ENDDATE or
                                ITINERARY.ENDDATETIME between @STARTDATE and @ENDDATE or
                                (
                                    ITINERARY.STARTDATETIME >= @STARTDATE and 
                                    ITINERARY.ENDDATETIME <= @ENDDATE
                                )
                            )
                    end

                    -- Next process itinerary items

                    if (@TYPEFILTER = 0 or @TYPEFILTER = 3) and @PROGRAMID is null
                    begin
                        insert into @RESULTS
                        (
                            ITINERARYID,
                            NAME,
                            TYPE,
                            TYPECODE,
                            LOCATION,
                            CAPACITY,
                            STARTDATE,
                            STARTTIME,
                            ENDDATE,
                            ENDTIME
                        )
                        select 
                            ITINERARY.ID,
                            ITINERARY.NAME + ' - ' + ITINERARYITEM.NAME,
                            'Itinerary item',
                            2,
                            coalesce(EVENTLOCATION.NAME, ''),
                            '',
                            ITINERARYITEM.STARTDATE,
                            ITINERARYITEM.STARTTIME,
                            ITINERARYITEM.ENDDATE,
                            ITINERARYITEM.ENDTIME
                        from dbo.ITINERARYITEMSTAFFRESOURCE
                        inner join dbo.ITINERARYITEM on 
                            (ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID) and 
                            (ITINERARYITEM.ITEMTYPECODE <> 3)
                        inner join dbo.ITINERARY on 
                            ITINERARYITEM.ITINERARYID = ITINERARY.ID
                        inner join dbo.SALESORDER on
                            ITINERARY.RESERVATIONID = SALESORDER.ID
                        left outer join dbo.EVENTLOCATION on 
                            ITINERARYITEM.EVENTLOCATIONID = EVENTLOCATION.ID
                        where 
                            SALESORDER.STATUSCODE <> 5 and
                            ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = @VOLUNTEERTYPEID and
                            (
                                ITINERARYITEM.STARTDATE between @STARTDATE and @ENDDATE or
                                ITINERARYITEM.ENDDATE between @STARTDATE and @ENDDATE or
                                (
                                    ITINERARYITEM.STARTDATE >= @STARTDATE and 
                                    ITINERARYITEM.ENDDATE <= @ENDDATE
                                )
                            )
                    end

                    -- Return the results

                    select 
                        EVENTID,
                        ITINERARYID,
                        NAME,
                        TYPE,
                        TYPECODE,
                        LOCATION,
                        CAPACITY,
                        STARTDATE,
                        STARTTIME,
                        ENDDATE,
                        ENDTIME
                    from @RESULTS