UFN_RESERVATION_GETRESOURCESSTRING

            CREATE function dbo.UFN_RESERVATION_GETRESOURCESSTRING(@ID uniqueidentifier)
            returns nvarchar(max)
            as begin
                declare @RESOURCESTABLE table
                (
                    RESOURCEID uniqueidentifier,
                    DESCRIPTION nvarchar(255),
                    ITINERARYITEMID uniqueidentifier,
                    QUANTITY int,
                    PRICINGSTRUCTURECODE tinyint,
                    PRICE money,
                    TOTALPRICE money,
                    NUMBEROFHOURS decimal(20, 2)
                );

                insert into @RESOURCESTABLE
                (
                    RESOURCEID,
                    DESCRIPTION,
                    ITINERARYITEMID,
                    QUANTITY,
                    PRICINGSTRUCTURECODE,
                    PRICE,
                    TOTALPRICE,
                    NUMBEROFHOURS
                )
                select
                    RESOURCES.RESOURCEID,
                    RESOURCES.DESCRIPTION,
                    RESOURCES.ITINERARYITEMID,
                    case RESOURCES.QUANTITYNEEDED
                        when 0 then  -- Per ticket resource

                            case
                                when RESOURCES.NUMBEROFHOURS > 0 then
                                    -- If this is an hourly resource, divide to get quantity needed per hour

                                    SALESORDERITEM.QUANTITY / RESOURCES.NUMBEROFHOURS
                                else
                                    SALESORDERITEM.QUANTITY
                            end
                        else
                            RESOURCES.QUANTITYNEEDED
                    end as [QUANTITY],
                    SALESORDERITEM.PRICINGSTRUCTURECODE,
                    SALESORDERITEM.PRICE,
                    SALESORDERITEM.TOTAL as [TOTALPRICE],
                    RESOURCES.NUMBEROFHOURS
                from (
                    select
                        ITINERARYRESOURCE.RESOURCEID,
                        SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID,
                        dbo.UFN_RESOURCE_GETNAME(ITINERARYRESOURCE.RESOURCEID) as [DESCRIPTION],
                        null as [ITINERARYITEMID],
                        ITINERARYRESOURCE.QUANTITYNEEDED,
                        case ITINERARYRESOURCE.PRICINGSTRUCTURECODE
                            when 2 then
                                datediff(minute, dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYRESOURCE.ITINERARYID), dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYRESOURCE.ITINERARYID)) / 60.0
                            else
                                null
                        end as [NUMBEROFHOURS]
                    from
                        dbo.SALESORDERITEMITINERARYRESOURCE
                    inner join
                        dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID

                    union all

                    select
                        ITINERARYITEMRESOURCE.RESOURCEID,
                        SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID,
                        dbo.UFN_RESOURCE_GETNAME(ITINERARYITEMRESOURCE.RESOURCEID) as [DESCRIPTION],
                        ITINERARYITEMRESOURCE.ITINERARYITEMID,
                        ITINERARYITEMRESOURCE.QUANTITYNEEDED,
                        case ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE
                            when 2 then
                                datediff(minute, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME) / 60.0
                            else
                                null
                        end as [NUMBEROFHOURS]
                    from
                        dbo.SALESORDERITEMITINERARYITEMRESOURCE
                    inner join
                        dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
                    inner join
                        dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID

                    union all

                    select
                        ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID as [RESOURCEID],
                        SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID,
                        dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID) as [DESCRIPTION],
                        null as [ITINERARYITEMID],
                        ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                        case ITINERARYSTAFFRESOURCE.PRICINGSTRUCTURECODE
                            when 2 then
                                datediff(minute, dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID), dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID)) / 60.0
                            else
                                null
                        end as [NUMBEROFHOURS]
                    from
                        dbo.SALESORDERITEMITINERARYSTAFFRESOURCE
                    inner join
                        dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID

                    union all

                    select
                        ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID as [RESOURCEID],
                        SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID,
                        dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID) as [DESCRIPTION],
                        ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID,
                        ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
                        case ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
                            when 2 then
                                datediff(minute, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME) / 60.0
                            else
                                null
                        end as [NUMBEROFHOURS]
                    from
                        dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE
                    inner join
                        dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
                    inner join
                        dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
                ) as [RESOURCES]
                inner join
                    dbo.SALESORDERITEM on RESOURCES.SALESORDERITEMID = SALESORDERITEM.ID
                where
                    SALESORDERITEM.SALESORDERID = @ID;

                declare @GROUPEDRESOURCESTABLE table
                (
                    DESCRIPTION nvarchar(255),
                    QUANTITY int,
                    PRICINGSTRUCTURECODE tinyint,
                    PRICE money,
                    TOTALPRICE money,
                    NUMBEROFHOURS decimal(20, 2)
                );

                insert into @GROUPEDRESOURCESTABLE
                (
                    DESCRIPTION,
                    QUANTITY,
                    PRICINGSTRUCTURECODE,
                    PRICE,
                    TOTALPRICE
                )
                select
                    DESCRIPTION,
                    sum(QUANTITY),
                    PRICINGSTRUCTURECODE,
                    PRICE,
                    sum(TOTALPRICE)
                from
                    @RESOURCESTABLE
                where
                    NUMBEROFHOURS is null
                group by
                    RESOURCEID,
                    DESCRIPTION,
                    PRICINGSTRUCTURECODE,
                    PRICE;

                insert into @GROUPEDRESOURCESTABLE
                (
                    DESCRIPTION,
                    QUANTITY,
                    PRICINGSTRUCTURECODE,
                    PRICE,
                    TOTALPRICE,
                    NUMBEROFHOURS
                )
                select
                    case
                        when ITINERARYITEMID is null then
                            DESCRIPTION
                        else
                            DESCRIPTION + ' - ' + dbo.UFN_ITINERARYITEM_GETNAME(ITINERARYITEMID)
                    end,
                    QUANTITY,
                    PRICINGSTRUCTURECODE,
                    PRICE,
                    TOTALPRICE,
                    NUMBEROFHOURS
                from
                    @RESOURCESTABLE
                where
                    NUMBEROFHOURS is not null;

                declare @RESOURCES nvarchar(max);
                declare @RESOURCEDESCRIPTION nvarchar(100);
                declare @QUANTITY int;
                declare @PRICINGSTRUCTURECODE tinyint;
                declare @PRICE money;
                declare @TOTALPRICE money;
                declare @NUMBEROFHOURS decimal(20, 2);
                declare @CRLF char(2);

                set @CRLF = char(13) + char(10);

                set @RESOURCES = '';

                declare RESOURCES_CURSOR cursor local fast_forward for
                select
                    DESCRIPTION,
                    QUANTITY,
                    PRICINGSTRUCTURECODE,
                    PRICE,
                    TOTALPRICE,
                    NUMBEROFHOURS
                from
                    @GROUPEDRESOURCESTABLE
                order by
                    DESCRIPTION;

                open RESOURCES_CURSOR;

                fetch next from RESOURCES_CURSOR
                into @RESOURCEDESCRIPTION, @QUANTITY, @PRICINGSTRUCTURECODE, @PRICE, @TOTALPRICE, @NUMBEROFHOURS;

                while @@FETCH_STATUS = 0
                begin
                    if len(@RESOURCES) > 0
                        set @RESOURCES = @RESOURCES + @CRLF;

                    set @RESOURCES = @RESOURCES + @RESOURCEDESCRIPTION + ' ? ' + cast(@QUANTITY as nvarchar(50));

                    if @NUMBEROFHOURS > 0
                    begin
                        set @RESOURCES = @RESOURCES + ' for ' + cast(@NUMBEROFHOURS as nvarchar(50)) + ' hour';

                        if @NUMBEROFHOURS <> 1
                            set @RESOURCES = @RESOURCES + 's';

                        if @TOTALPRICE > 0
                        begin
                            if @PRICINGSTRUCTURECODE = 0
                                set @RESOURCES = @RESOURCES + ' @ ' + cast(@PRICE as nvarchar(50)) + ' / hour:  ' + cast(@TOTALPRICE as nvarchar(50));
                            else if @PRICINGSTRUCTURECODE = 2
                                set @RESOURCES = @RESOURCES + ':  ' + cast(@TOTALPRICE as nvarchar(50));
                        end
                    end
                    else if @TOTALPRICE > 0
                    begin
                        if @PRICINGSTRUCTURECODE = 0
                            set @RESOURCES = @RESOURCES + ' @ ' + cast(@PRICE as nvarchar(50)) + ':  ' + cast(@TOTALPRICE as nvarchar(50));
                        else if @PRICINGSTRUCTURECODE = 2
                            set @RESOURCES = @RESOURCES + ':  ' + cast(@TOTALPRICE as nvarchar(50));
                    end

                    fetch next from RESOURCES_CURSOR
                    into @RESOURCEDESCRIPTION, @QUANTITY, @PRICINGSTRUCTURECODE, @PRICE, @TOTALPRICE, @NUMBEROFHOURS;
                end

                close RESOURCES_CURSOR;
                deallocate RESOURCES_CURSOR;

                return @RESOURCES;
            end