UFN_REGISTRANT_RELATEDREGISTRATIONS_TOITEMLISTXML

CREATE function dbo.UFN_REGISTRANT_RELATEDREGISTRATIONS_TOITEMLISTXML
(
    @REGISTRANTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
returns xml
with execute as caller
as begin
    declare @XML xml;
    declare @CONFLICTS as dbo.UDT_EVENTPAIR;

    insert into @CONFLICTS
    select
        RELATEDEVENTPAIRS.EVENTID1,
        RELATEDEVENTPAIRS.EVENTID2
    from
        dbo.UFN_EVENT_RELATEDEVENTPAIRSWITHSCHEDULECONFLICT(@REGISTRANTID) as RELATEDEVENTPAIRS;

    declare @CONSTITUENTID uniqueidentifier;
    declare @HOSTID uniqueidentifier;
    select
        @CONSTITUENTID = CONSTITUENTID,
        @HOSTID = GUESTOFREGISTRANTID
    from
        dbo.REGISTRANT
    where
        REGISTRANT.ID = @REGISTRANTID;

    declare @EVENTID uniqueidentifier;
    select @EVENTID = EVENTID from dbo.REGISTRANT where REGISTRANT.ID = @REGISTRANTID;
    declare @MAINEVENTID uniqueidentifier;
    select @MAINEVENTID = coalesce(EVENT.MAINEVENTID, EVENT.ID) from dbo.EVENT where EVENT.ID = @EVENTID;

    declare @EVENTS table
    (
        ID uniqueidentifier,
        NAME nvarchar(100),
        LEVEL int
    );

    insert into @EVENTS
        select
            EVENTSINHIERARCHY.ID,
            EVENTSINHIERARCHY.NAME,
            EVENTSINHIERARCHY.LEVEL
        from
            dbo.UFN_EVENT_GETALLEVENTSINHIERARCHY(@MAINEVENTID) as EVENTSINHIERARCHY
        where
            dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENTSINHIERARCHY.ID) = 1;


    declare @RELATEDREGISTRANTS table
    (
        ID uniqueidentifier, 
        WILLNOTATTEND bit, 
        ONLINEREGISTRANT bit, 
        ATTENDED bit, 
        GUESTOFREGISTRANTID uniqueidentifier,
        EVENTNAME nvarchar(100), 
        LEVEL int,
        BASECURRENCYID uniqueidentifier,
        ISWALKIN bit,
        ISPROCESSED bit
    );

    insert into @RELATEDREGISTRANTS
        select 
            REGISTRANT.ID,
            REGISTRANT.WILLNOTATTEND,
            REGISTRANT.ONLINEREGISTRANT,
            REGISTRANT.ATTENDED,
            REGISTRANT.GUESTOFREGISTRANTID,
            EVENTS.NAME,
            EVENTS.LEVEL,
            EVENT.BASECURRENCYID,
            REGISTRANT.ISWALKIN,
            EVENT.ISPROCESSED
        from
            dbo.REGISTRANT
            inner join @EVENTS EVENTS on REGISTRANT.EVENTID = EVENTS.ID
            inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
        where
            -- constitID is not null; i.e., not an unnamed guest

            REGISTRANT.CONSTITUENTID = @CONSTITUENTID;

    with HOST_CTE as
    (
        select 
            HOST.ID,
            EVENTS.NAME EVENTNAME,
            EVENTS.LEVEL,
            EVENT.BASECURRENCYID
        from
            dbo.REGISTRANT HOST
            inner join dbo.REGISTRANT on HOST.CONSTITUENTID = REGISTRANT.CONSTITUENTID
            inner join @EVENTS EVENTS on HOST.EVENTID = EVENTS.ID
            inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
        where
            @CONSTITUENTID is null
            and REGISTRANT.ID = @HOSTID
    )
    insert into @RELATEDREGISTRANTS
        select
            UNNAMEDGUEST.ID,
            UNNAMEDGUEST.WILLNOTATTEND,
            UNNAMEDGUEST.ONLINEREGISTRANT,
            UNNAMEDGUEST.ATTENDED,
            UNNAMEDGUEST.GUESTOFREGISTRANTID,
            HOST_CTE.EVENTNAME,
            HOST_CTE.LEVEL,
            HOST_CTE.BASECURRENCYID,
            UNNAMEDGUEST.ISWALKIN,
            EVENT.ISPROCESSED
        from 
            dbo.REGISTRANT UNNAMEDGUEST
            inner join dbo.EVENT on EVENT.ID = UNNAMEDGUEST.EVENTID
            inner join HOST_CTE on
                UNNAMEDGUEST.GUESTOFREGISTRANTID = HOST_CTE.ID
                and UNNAMEDGUEST.CONSTITUENTID is null;

    declare @UNIQUERELATEDREGISTRANTS table
    (
        STATUS nvarchar(16),
        ISONLINEREGISTRANT nvarchar(3),
        BALANCE money,
        HOSTNAME nvarchar(124),
        ATTENDED nvarchar(7),
        SCHEDULECONFLICTEXISTS nvarchar(3),
        EVENTNAME nvarchar(100),
        EVENTLEVEL int,
        BASECURRENCYID uniqueidentifier,
        REGISTRATIONTYPE nvarchar(16)
    );

    insert into @UNIQUERELATEDREGISTRANTS
        select distinct -- must use "distinct" to avoid having multiple unnamed guests at one event show up as multiple rows

            case 
                when dbo.UFN_REGISTRANT_ISCANCELLED(REG.ID) = 1 then 'Canceled'
                when REG.WILLNOTATTEND = 1 then 'Will not attend'
                else 'Registered'
            end as [STATUS],
            case REG.ONLINEREGISTRANT
                when 1 then 'Yes'
                else ''
            end as ISONLINEREGISTRANT,
            dbo.UFN_EVENTREGISTRANT_GETBALANCE(REG.ID) as BALANCE,
            dbo.UFN_REGISTRANT_GETHOSTNAME(REG.ID, REG.GUESTOFREGISTRANTID) as HOSTNAME,
            case 
                when REG.ATTENDED = 1 then 'Yes'
                when REG.ISPROCESSED = 1 then 'No show'
                else 'No'
            end as ATTENDED,
            case dbo.UFN_REGISTRANT_HASCONFLICT(REG.ID, @CONFLICTS)
                when 1 then 'Yes'
                else ''
            end as SCHEDULECONFLICTEXISTS,
            REG.EVENTNAME,
            REG.LEVEL,
            REG.BASECURRENCYID,
            case
                when REG.ISWALKIN = 1 then 'Walk-in'
                else 'Preregistration'
            end as REGISTRATIONTYPE
        from
            @RELATEDREGISTRANTS REG;

    set @XML = (
        select 
            STATUS,
            ISONLINEREGISTRANT,
            BALANCE,
            HOSTNAME,
            ATTENDED,
            SCHEDULECONFLICTEXISTS,
            EVENTNAME,
            BASECURRENCYID,
            REGISTRATIONTYPE
        from
            @UNIQUERELATEDREGISTRANTS
        order by
            EVENTLEVEL,
            EVENTNAME
        for xml raw('ITEM'),type,elements,root('RELATEDREGISTRATIONS'),BINARY BASE64
    );

    return @XML;
end