UFN_EVENT_GETREGISTRATIONINFORMATION

CREATE function dbo.UFN_EVENT_GETREGISTRATIONINFORMATION
(
    @EVENTID uniqueidentifier,
    @PRICETYPECODEID uniqueidentifier = null
)
returns @REGISTRATIONINFORMATION table (REGISTRATIONINFORMATIONID uniqueidentifier, REQUIRED bit, NAME nvarchar(100), RESPONSETYPECODE tinyint, PERSONDETAILTYPECODE tinyint, TEXTVALUE nvarchar(max), REGISTRATIONINFORMATIONOPTIONID uniqueidentifier, SECTIONSEQUENCE int, SEQUENCE int, ALLPRICETYPES bit, REGISTRATIONINFORMATIONPRICETYPES xml)
as begin
    declare @PROGRAMID uniqueidentifier;
    declare @HASCUSTOMREGISTRATIONINFORMATION bit;

    select
        @PROGRAMID = EVENT.PROGRAMID,
        @HASCUSTOMREGISTRATIONINFORMATION = EVENT.HASCUSTOMREGISTRATIONINFORMATION
    from dbo.EVENT
    where ID = @EVENTID;

    declare @EVENTSECTIONS table (
        ID uniqueidentifier,
        SEQUENCE int
    );

    if @HASCUSTOMREGISTRATIONINFORMATION = 1 begin
        insert into @EVENTSECTIONS (ID, SEQUENCE)
        select ID, SEQUENCE
        from dbo.PROGRAMEVENTREGISTRATIONSECTION
        where EVENTID = @EVENTID;
    end else begin
        insert into @EVENTSECTIONS (ID, SEQUENCE)
        select ID, SEQUENCE
        from dbo.PROGRAMEVENTREGISTRATIONSECTION
        where PROGRAMID = @PROGRAMID;
    end

    insert into @REGISTRATIONINFORMATION
        (REGISTRATIONINFORMATIONID, REQUIRED, NAME, RESPONSETYPECODE, PERSONDETAILTYPECODE, TEXTVALUE, REGISTRATIONINFORMATIONOPTIONID, SECTIONSEQUENCE, SEQUENCE, ALLPRICETYPES, REGISTRATIONINFORMATIONPRICETYPES)
    select
        REGISTRATIONINFORMATION.ID,
        PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REQUIRED,
        REGISTRATIONINFORMATION.NAME,
        REGISTRATIONINFORMATION.RESPONSETYPECODE,
        0,
        '',
        case REGISTRATIONINFORMATION.RESPONSETYPECODE when 3 then '00000000-0000-0000-0000-000000000000' else null end,
        EVENTSECTIONS.SEQUENCE,
        PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.SEQUENCE,
        REGISTRATIONINFORMATION.ALLPRICETYPES,
        dbo.UFN_REGISTRATIONINFORMATION_GETPRICETYPES_TOITEMLISTXML_NOROOT(REGISTRATIONINFORMATION.ID) as REGISTRATIONINFORMATIONPRICETYPES
    from @EVENTSECTIONS as EVENTSECTIONS
    inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION
        on EVENTSECTIONS.ID = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.PROGRAMEVENTREGISTRATIONSECTIONID
    inner join dbo.REGISTRATIONINFORMATION
        on PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID
            and REGISTRATIONINFORMATION.TYPECODE = 1                                                                                            -- Questions only

    where REGISTRATIONINFORMATION.RESPONSETYPECODE <> 4                                                                                        -- Name/phone/email

        and (
            @PRICETYPECODEID is null 
            or (REGISTRATIONINFORMATION.ALLPRICETYPES = 1 or exists (select ID from dbo.REGISTRATIONINFORMATIONPRICETYPE where REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID and PRICETYPECODEID = @PRICETYPECODEID))
        );

    with REGISTRATIONINFO_CTE as (
        select
            REGISTRATIONINFORMATION.ID,
            REGISTRATIONINFORMATION.NAME,
            REGISTRATIONINFORMATION.RESPONSETYPECODE,
            REGISTRATIONINFORMATION.ASKPERSONPHONE2,
            REGISTRATIONINFORMATION.ASKPERSONEMAIL,
            EVENTSECTIONS.SEQUENCE as SECTIONSEQUENCE,
            PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.SEQUENCE,
            PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REQUIRED,
            REGISTRATIONINFORMATION.ALLPRICETYPES,
            dbo.UFN_REGISTRATIONINFORMATION_GETPRICETYPES_TOITEMLISTXML_NOROOT(REGISTRATIONINFORMATION.ID) as REGISTRATIONINFORMATIONPRICETYPES
        from @EVENTSECTIONS as EVENTSECTIONS
        inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION
            on EVENTSECTIONS.ID = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.PROGRAMEVENTREGISTRATIONSECTIONID
        inner join dbo.REGISTRATIONINFORMATION
            on PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID
                and REGISTRATIONINFORMATION.TYPECODE = 1                                                                                            -- Questions only        

        where REGISTRATIONINFORMATION.RESPONSETYPECODE = 4                                                                                            -- Name/phone/email

            and (
                @PRICETYPECODEID is null 
                or (REGISTRATIONINFORMATION.ALLPRICETYPES = 1 or exists (select ID from dbo.REGISTRATIONINFORMATIONPRICETYPE where REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID and PRICETYPECODEID = @PRICETYPECODEID))
            )
    )
    insert into @REGISTRATIONINFORMATION
        (REGISTRATIONINFORMATIONID, REQUIRED, NAME, RESPONSETYPECODE, PERSONDETAILTYPECODE, TEXTVALUE, REGISTRATIONINFORMATIONOPTIONID, SECTIONSEQUENCE, SEQUENCE, ALLPRICETYPES, REGISTRATIONINFORMATIONPRICETYPES)    
    select
        REGINFO.ID,
        REGINFO.REQUIRED,
        REGINFO.NAME,
        REGINFO.RESPONSETYPECODE,
        PERSONDETAIL.TYPECODE as PERSONALDETAILTYPECODE,
        '',
        null,
        REGINFO.SECTIONSEQUENCE,
        REGINFO.SEQUENCE,
        REGINFO.ALLPRICETYPES,
        REGINFO.REGISTRATIONINFORMATIONPRICETYPES
    from REGISTRATIONINFO_CTE REGINFO
    outer apply (
        select 1 as TYPECODE  -- First name


        union all
        select 2 as TYPECODE  -- Last name


        union all
        select 3 as TYPECODE  -- Phone


        union all
        select 4 as TYPECODE  -- Phone 2

        where REGINFO.ASKPERSONPHONE2 = 1

        union all
        select 5 as TYPECODE  -- Email

        where REGINFO.ASKPERSONEMAIL = 1
    ) as PERSONDETAIL;

    return;

end