UFN_REGISTRANT_GETREGISTRATIONINFORMATION

CREATE function dbo.UFN_REGISTRANT_GETREGISTRATIONINFORMATION
(
    @REGISTRANTID uniqueidentifier
)
returns @REGISTRANTREGISTRATIONINFORMATION table (ID uniqueidentifier, REGISTRATIONINFORMATIONID uniqueidentifier, REQUIRED bit, NAME nvarchar(100), RESPONSETYPECODE tinyint, PERSONDETAILTYPECODE tinyint, TEXTVALUE nvarchar(max), REGISTRATIONINFORMATIONOPTIONID uniqueidentifier, SECTIONSEQUENCE int, SEQUENCE int)
as begin
    declare @PROGRAMID uniqueidentifier;
    declare @EVENTID uniqueidentifier;
    declare @PRICETYPECODEID uniqueidentifier;
    declare @HASCUSTOMREGISTRATIONINFORMATION bit;

    select 
        @PROGRAMID = EVENT.PROGRAMID,
        @EVENTID = REGISTRANT.EVENTID,
        @HASCUSTOMREGISTRATIONINFORMATION = EVENT.HASCUSTOMREGISTRATIONINFORMATION,
        @PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID
    from dbo.REGISTRANT
    inner join dbo.EVENT
        on REGISTRANT.EVENTID = EVENT.ID
    inner join dbo.SALESORDERITEMTICKETREGISTRANT
        on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
    inner join dbo.SALESORDERITEMTICKET
        on SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
    where REGISTRANT.ID = @REGISTRANTID;

    with SECTIONINFORMATION_CTE as (
            select
                PROGRAMEVENTREGISTRATIONSECTION.ID,
                PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID,
                PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REQUIRED,
                PROGRAMEVENTREGISTRATIONSECTION.SEQUENCE as SECTIONSEQUENCE,
                PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.SEQUENCE

            from dbo.PROGRAMEVENTREGISTRATIONSECTION
            inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION
                on PROGRAMEVENTREGISTRATIONSECTION.ID = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.PROGRAMEVENTREGISTRATIONSECTIONID            
            where 
                (@HASCUSTOMREGISTRATIONINFORMATION = 1 and PROGRAMEVENTREGISTRATIONSECTION.EVENTID = @EVENTID)
                or (@HASCUSTOMREGISTRATIONINFORMATION = 0 and PROGRAMEVENTREGISTRATIONSECTION.PROGRAMID = @PROGRAMID)

        )
    insert into @REGISTRANTREGISTRATIONINFORMATION
            (ID, REGISTRATIONINFORMATIONID, REQUIRED, NAME, RESPONSETYPECODE, PERSONDETAILTYPECODE, TEXTVALUE, REGISTRATIONINFORMATIONOPTIONID, SECTIONSEQUENCE, SEQUENCE)        
        select
            REGISTRANTREGISTRATIONINFORMATION.ID,
            REGISTRATIONINFORMATION.ID,
            SECTIONINFO.REQUIRED,
            REGISTRATIONINFORMATION.NAME,
            REGISTRATIONINFORMATION.RESPONSETYPECODE,
            REGISTRANTREGISTRATIONINFORMATION.PERSONDETAILTYPECODE,
            REGISTRANTREGISTRATIONINFORMATION.TEXTVALUE,        
            case REGISTRATIONINFORMATION.RESPONSETYPECODE
                when 3 then
                    case isnull(REGISTRANTREGISTRATIONINFORMATION.BOOLEANVALUE, 0)
                        when 1 then '11111111-1111-1111-1111-111111111111'        -- Yes

                        else '00000000-0000-0000-0000-000000000000'                    -- No

                    end
                else REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONOPTIONID        
            end as REGISTRATIONINFORMATIONOPTIONID,
            coalesce(SECTIONINFO.SECTIONSEQUENCE, 100),                                                                            -- If question no longer on program/event, try to include answer at end

            coalesce(SECTIONINFO.SEQUENCE, 100)
        from dbo.REGISTRANTREGISTRATIONINFORMATION
        inner join dbo.REGISTRATIONINFORMATION
            on REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID
        left outer join SECTIONINFORMATION_CTE SECTIONINFO
            on REGISTRATIONINFORMATION.ID = SECTIONINFO.REGISTRATIONINFORMATIONID
        where REGISTRANTREGISTRATIONINFORMATION.REGISTRANTID = @REGISTRANTID;

    insert into @REGISTRANTREGISTRATIONINFORMATION
        (ID, REGISTRATIONINFORMATIONID, REQUIRED, NAME, RESPONSETYPECODE, PERSONDETAILTYPECODE, TEXTVALUE, REGISTRATIONINFORMATIONOPTIONID, SECTIONSEQUENCE, SEQUENCE)
    select
        null,
        EVENTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID,
        EVENTREGISTRATIONINFORMATION.REQUIRED,
        EVENTREGISTRATIONINFORMATION.NAME,
        EVENTREGISTRATIONINFORMATION.RESPONSETYPECODE,
        EVENTREGISTRATIONINFORMATION.PERSONDETAILTYPECODE,
        EVENTREGISTRATIONINFORMATION.TEXTVALUE,
        EVENTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONOPTIONID,
        EVENTREGISTRATIONINFORMATION.SECTIONSEQUENCE,
        EVENTREGISTRATIONINFORMATION.SEQUENCE
    from dbo.UFN_EVENT_GETREGISTRATIONINFORMATION(@EVENTID, @PRICETYPECODEID) EVENTREGISTRATIONINFORMATION
    left outer join @REGISTRANTREGISTRATIONINFORMATION as REGISTRANTREGISTRATIONINFORMATION
        on EVENTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID = REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID
        and EVENTREGISTRATIONINFORMATION.PERSONDETAILTYPECODE = REGISTRANTREGISTRATIONINFORMATION.PERSONDETAILTYPECODE
    where REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID is null;

    return;
end