UFN_REPORT_REGISTRANTREGISTRATIONINFORMATION

CREATE function dbo.UFN_REPORT_REGISTRANTREGISTRATIONINFORMATION
(
    @REGISTRANTID uniqueidentifier,
    @REGISTRATIONSECTIONSTOINCLUDE xml = null
)
returns @RESULTS table
(
    RESPONSETYPECODE tinyint,
    NAME nvarchar(100),
    VALUE nvarchar(max)
)
begin    
    declare @EVENTID uniqueidentifier;
    declare @PROGRAMID uniqueidentifier;
    declare @HASCUSTOMREGISTRATIONINFORMATION bit;

    select
        @PROGRAMID = EVENT.PROGRAMID,
        @EVENTID = EVENT.ID,
        @HASCUSTOMREGISTRATIONINFORMATION = EVENT.HASCUSTOMREGISTRATIONINFORMATION
    from dbo.REGISTRANT
    inner join dbo.EVENT
        on REGISTRANT.EVENTID = EVENT.ID
    inner join dbo.PROGRAM
        on EVENT.PROGRAMID = PROGRAM.ID
    where REGISTRANT.ID = @REGISTRANTID;

    declare @SECTIONSTOINCLUDE table (
        REGISTRATIONSECTIONCODEID uniqueidentifier
    )

    if @REGISTRATIONSECTIONSTOINCLUDE is not null
        insert into @SECTIONSTOINCLUDE (REGISTRATIONSECTIONCODEID)
            select
                T.c.value('(REGISTRATIONSECTIONCODEID)[1]','uniqueidentifier')
            from @REGISTRATIONSECTIONSTOINCLUDE.nodes('/REGISTRATIONSECTIONSTOINCLUDE/ITEM') T(c);

    else
        insert into @SECTIONSTOINCLUDE (REGISTRATIONSECTIONCODEID)
            select
                ID
            from dbo.REGISTRATIONSECTIONCODE;

    declare @REGINFOSECTIONS table (
        REGISTRATIONINFORMATIONID uniqueidentifier,
        SECTIONSEQUENCE int,
        SEQUENCE int
    );

    if @HASCUSTOMREGISTRATIONINFORMATION = 1 begin
        insert into @REGINFOSECTIONS (REGISTRATIONINFORMATIONID, SECTIONSEQUENCE, SEQUENCE)
            select 
                PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID, 
                PROGRAMEVENTREGISTRATIONSECTION.SEQUENCE as SECTIONSEQUENCE, 
                PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.SEQUENCE
            from dbo.PROGRAMEVENTREGISTRATIONSECTION
            inner join @SECTIONSTOINCLUDE SECTIONSTOINCLUDE
                on PROGRAMEVENTREGISTRATIONSECTION.REGISTRATIONSECTIONCODEID = SECTIONSTOINCLUDE.REGISTRATIONSECTIONCODEID
            inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION
                on PROGRAMEVENTREGISTRATIONSECTION.ID = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.PROGRAMEVENTREGISTRATIONSECTIONID            
            where PROGRAMEVENTREGISTRATIONSECTION.EVENTID = @EVENTID;

    end else begin
        insert into @REGINFOSECTIONS (REGISTRATIONINFORMATIONID, SECTIONSEQUENCE, SEQUENCE)
            select 
                PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID, 
                PROGRAMEVENTREGISTRATIONSECTION.SEQUENCE as SECTIONSEQUENCE, 
                PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.SEQUENCE
            from dbo.PROGRAMEVENTREGISTRATIONSECTION
            inner join @SECTIONSTOINCLUDE SECTIONSTOINCLUDE
                on PROGRAMEVENTREGISTRATIONSECTION.REGISTRATIONSECTIONCODEID = SECTIONSTOINCLUDE.REGISTRATIONSECTIONCODEID
            inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION
                on PROGRAMEVENTREGISTRATIONSECTION.ID = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.PROGRAMEVENTREGISTRATIONSECTIONID
            where PROGRAMEVENTREGISTRATIONSECTION.PROGRAMID = @PROGRAMID;
    end

    declare @REGISTRANTREGISTRATIONINFORMATION table (
        RESPONSETYPECODE tinyint,
        NAME nvarchar(100),
        VALUE nvarchar(max),
        SECTIONSEQUENCE int,
        SEQUENCE int
    )

    insert into @REGISTRANTREGISTRATIONINFORMATION (RESPONSETYPECODE, NAME, VALUE, SECTIONSEQUENCE, SEQUENCE)
        select
            REGISTRATIONINFORMATION.RESPONSETYPECODE,
            REGISTRATIONINFORMATION.NAME,
            case 
                when REGISTRATIONINFORMATION.RESPONSETYPECODE in (0, 1) and REGISTRANTREGISTRATIONINFORMATION.TEXTVALUE <> '' then REGISTRANTREGISTRATIONINFORMATION.TEXTVALUE
                when REGISTRATIONINFORMATION.RESPONSETYPECODE = 2 then REGISTRATIONINFORMATIONOPTION.NAME
                when REGISTRATIONINFORMATION.RESPONSETYPECODE = 3 then 
                        case REGISTRANTREGISTRATIONINFORMATION.BOOLEANVALUE
                            when 1 then 'Yes'
                            else 'No'
                        end
            end as VALUE,
            coalesce(REGINFOSECTIONS.SECTIONSEQUENCE, 999) as SECTIONSEQUENCE,
            coalesce(REGINFOSECTIONS.SEQUENCE, 999) as SEQUENCE
        from dbo.REGISTRANTREGISTRATIONINFORMATION
        inner join dbo.REGISTRATIONINFORMATION
            on REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID

        inner join @REGINFOSECTIONS REGINFOSECTIONS
            on REGISTRATIONINFORMATION.ID = REGINFOSECTIONS.REGISTRATIONINFORMATIONID

        left outer join dbo.REGISTRATIONINFORMATIONOPTION
            on REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONOPTIONID = REGISTRATIONINFORMATIONOPTION.ID

        where REGISTRANTREGISTRATIONINFORMATION.REGISTRANTID = @REGISTRANTID
            and REGISTRATIONINFORMATION.RESPONSETYPECODE <> 4;  -- Name/phone/email


    insert into @REGISTRANTREGISTRATIONINFORMATION (RESPONSETYPECODE, NAME, VALUE, SECTIONSEQUENCE, SEQUENCE)
        select
            4,
            REGISTRATIONINFORMATION.NAME,
            nullif(dbo.UDA_BUILDLISTWITHDELIMITER(nullif(REGISTRANTREGISTRATIONINFORMATION.TEXTVALUE, ''), ', '), ''),
            coalesce(REGINFOSECTIONS.SECTIONSEQUENCE, 999) as SECTIONSEQUENCE,
            coalesce(REGINFOSECTIONS.SEQUENCE, 999) as SEQUENCE
        from dbo.REGISTRANTREGISTRATIONINFORMATION
        inner join dbo.REGISTRATIONINFORMATION
            on REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID

        inner join @REGINFOSECTIONS REGINFOSECTIONS
            on REGISTRATIONINFORMATION.ID = REGINFOSECTIONS.REGISTRATIONINFORMATIONID

        left outer join dbo.REGISTRATIONINFORMATIONOPTION
            on REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONOPTIONID = REGISTRATIONINFORMATIONOPTION.ID

        where REGISTRANTREGISTRATIONINFORMATION.REGISTRANTID = @REGISTRANTID
            and REGISTRATIONINFORMATION.RESPONSETYPECODE = 4  -- Name/phone/email

        group by REGISTRATIONINFORMATION.ID, REGISTRATIONINFORMATION.NAME, REGINFOSECTIONS.SECTIONSEQUENCE, REGINFOSECTIONS.SEQUENCE;

    insert into @RESULTS (RESPONSETYPECODE, NAME, VALUE)
        select
            RESPONSETYPECODE, 
            NAME, 
            VALUE
        from @REGISTRANTREGISTRATIONINFORMATION
        order by SECTIONSEQUENCE, SEQUENCE;

    return;
end