USP_DATALIST_EVENTLODGINGLOCATIONS

    CREATE procedure dbo.USP_DATALIST_EVENTLODGINGLOCATIONS(
        @EVENTID uniqueidentifier,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @INCLUDESUBEVENTS bit = 0
    )
    as
        set nocount on;

        declare @EVENTS table
        (
            ID uniqueidentifier,
            EVENTNAME nvarchar(100)
        );

        insert into @EVENTS
        select 
            ID, NAME 
        from 
              dbo.UFN_CHILDEVENTSWITHSITEACCESS(@EVENTID, @CURRENTAPPUSERID) EVENTS
        where
            EVENTS.ID = @EVENTID
        or
            @INCLUDESUBEVENTS = 1

        declare @TOTALS table
        (
            EVENTLODGINGID uniqueidentifier,
            ALLOCATED int,
            ASSIGNED int
        );

        insert into @TOTALS
        (
            EVENTLODGINGID,
            ALLOCATED,
            ASSIGNED
        )
        (
            select
                EVENTLODGING.ID as EVENTLODGINGID,
                (select SUM(EVENTLODGINGROOM.ALLOCATED) from EVENTLODGINGROOM where EVENTLODGINGROOM.EVENTLODGINGID = EVENTLODGING.ID) as ALLOCATED,
                (select 
                    COUNT(EVENTLODGINGROOMINSTANCE.ID) 
                from
                    EVENTLODGINGROOMINSTANCE
                where 
                    EVENTLODGINGROOMINSTANCE.EVENTLODGINGID = EVENTLODGING.ID
                    -- Only count rooms with occupants

                    and EVENTLODGINGROOMINSTANCE.ID in (select EVENTLODGINGROOMINSTANCEID from dbo.REGISTRANTLODGING)
                ) as ASSIGNED
            from
                dbo.EVENTLODGING
            where 
                EVENTLODGING.EVENTID in (select ID from @EVENTS)
        );

        declare @ROOMTYPETOTALS table (EVENTLODGINGROOMID uniqueidentifier, ASSIGNED int);
        insert into @ROOMTYPETOTALS
        (EVENTLODGINGROOMID, ASSIGNED)
        (
            select
                EVENTLODGINGROOMINSTANCE.EVENTLODGINGROOMID as EVENTLODGINGID,
                COUNT(EVENTLODGINGROOMINSTANCE.ID) as ASSIGNED
            from
                dbo.EVENTLODGINGROOMINSTANCE
            inner join
                dbo.EVENTLODGING on EVENTLODGING.ID = EVENTLODGINGROOMINSTANCE.EVENTLODGINGID
            where 
                EVENTLODGING.EVENTID in (select ID from @EVENTS)
                -- Only count rooms with occupants

                and EVENTLODGINGROOMINSTANCE.ID in (select EVENTLODGINGROOMINSTANCEID from dbo.REGISTRANTLODGING)
            group by
                EVENTLODGINGROOMINSTANCE.EVENTLODGINGROOMID
        );

        select EVENTLODGING.ID as ID,
            null as PARENTID,
            EVENTLODGINGLOCATION.ID as EVENTLODGINGLOCATIONID,
            EVENTLODGINGLOCATION.NAME,
            TOTALS.ALLOCATED as TOTALALLOCATED,
            TOTALS.ASSIGNED as TOTALASSIGNED,
            case when TOTALS.ALLOCATED < TOTALS.ASSIGNED then 0
                else TOTALS.ALLOCATED - TOTALS.ASSIGNED 
                end as TOTALREMAINING,
            case when TOTALS.ASSIGNED < TOTALS.ALLOCATED then 0
                else TOTALS.ASSIGNED - TOTALS.ALLOCATED
                end as TOTALOVERASSIGNED,
            case when TOTALS.ASSIGNED <= TOTALS.ALLOCATED then 'RES:lv_spacer'
                else 'RES:warning'
                end as IMAGEKEY,
            EVENTLODGING.ID as DETAILID,
            0 as ROWTYPE,
            EVENTS.EVENTNAME
        from 
            dbo.EVENTLODGING
        inner join 
            dbo.EVENTLODGINGLOCATION on EVENTLODGINGLOCATION.ID = EVENTLODGING.EVENTLODGINGLOCATIONID
        inner join
            @TOTALS as TOTALS on TOTALS.EVENTLODGINGID = EVENTLODGING.ID
        inner join
            @EVENTS EVENTS on EVENTLODGING.EVENTID = EVENTS.ID 

        union all

        select
            newid() as ID,
            EVENTLODGING.ID as PARENTID,
            EVENTLODGING.EVENTLODGINGLOCATIONID as EVENTLODGINGLOCATIONID,
            EVENTLODGINGROOMTYPECODE.DESCRIPTION as NAME,
            EVENTLODGINGROOM.ALLOCATED as TOTALALLOCATED,
            coalesce(TOTALS.ASSIGNED, 0) as TOTALASSIGNED,
            case when EVENTLODGINGROOM.ALLOCATED < coalesce(TOTALS.ASSIGNED, 0) then 0
                else EVENTLODGINGROOM.ALLOCATED - coalesce(TOTALS.ASSIGNED, 0)
                end as TOTALREMAINING,
            case when coalesce(TOTALS.ASSIGNED, 0) < EVENTLODGINGROOM.ALLOCATED then 0
                else coalesce(TOTALS.ASSIGNED, 0) - EVENTLODGINGROOM.ALLOCATED
                end as TOTALOVERASSIGNED,
            case when coalesce(TOTALS.ASSIGNED, 0) <= EVENTLODGINGROOM.ALLOCATED then 'RES:lv_spacer'
                else 'RES:warning'
                end as IMAGEKEY,
            EVENTLODGING.ID as DETAILID,
            1 as ROWTYPE,
            EVENTS.EVENTNAME
        from
            dbo.EVENTLODGINGROOM
        inner join    
            dbo.EVENTLODGINGROOMTYPECODE on EVENTLODGINGROOMTYPECODE.ID = EVENTLODGINGROOM.ROOMTYPECODEID
        inner join
            dbo.EVENTLODGING on EVENTLODGING.ID = EVENTLODGINGROOM.EVENTLODGINGID
        left join
            @ROOMTYPETOTALS as TOTALS on TOTALS.EVENTLODGINGROOMID = EVENTLODGINGROOM.ID
        inner join
            @EVENTS EVENTS on EVENTLODGING.EVENTID = EVENTS.ID 

        union all

        select
            newid() as ID,
            EVENTLODGING.ID as PARENTID,
            EVENTLODGING.EVENTLODGINGLOCATIONID as EVENTLODGINGLOCATIONID,
            'Room type not specified' as NAME,
            0 as TOTALALLOCATED,
            count(EVENTLODGINGROOMINSTANCE.ID) as TOTALASSIGNED,
            0 as TOTALREMAILING,
            0 as TOTALOVERASSIGNED,
            case when count(EVENTLODGINGROOMINSTANCE.ID) > 0 then 'RES:warning' else 'RES:lv_spacer' end as IMAGEKEY,
            EVENTLODGING.ID as DETAILID,
            2 as ROWTYPE,
            EVENTS.EVENTNAME
        from
            dbo.EVENTLODGING
        left join
            dbo.EVENTLODGINGROOMINSTANCE on EVENTLODGINGROOMINSTANCE.EVENTLODGINGID = EVENTLODGING.ID
        -- Only for room instances that have an occupant.

        inner join
            dbo.REGISTRANTLODGING on REGISTRANTLODGING.EVENTLODGINGROOMINSTANCEID = EVENTLODGINGROOMINSTANCE.ID
        inner join
            @EVENTS EVENTS on EVENTLODGING.EVENTID = EVENTS.ID 
        where
            EVENTLODGINGROOMINSTANCE.EVENTLODGINGROOMID is null
        group by
            EVENTLODGING.ID, EVENTLODGING.EVENTLODGINGLOCATIONID, EVENTNAME 

        order by
            ROWTYPE, NAME