USP_REGISTRANT_GETREGISTRATIONINFORMATION_UPDATEFROMXML

CREATE procedure dbo.USP_REGISTRANT_GETREGISTRATIONINFORMATION_UPDATEFROMXML
(
    @REGISTRANTID uniqueidentifier,
    @REGISTRANTREGISTRATIONINFORMATION xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as begin
    set nocount on;

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CHANGEDATE is null 
        set @CHANGEDATE = getdate();

    begin try
        declare @contextCache varbinary(128) = CONTEXT_INFO();

        set CONTEXT_INFO @CHANGEAGENTID;

        -- Text, Multi-line text

        merge into dbo.REGISTRANTREGISTRATIONINFORMATION as target
            using (
                select
                    ID,
                    REGISTRATIONINFORMATIONID,
                    TEXTVALUE,
                    PERSONDETAILTYPECODE
                from dbo.UFN_REGISTRANT_GETREGISTRATIONINFORMATION_FROMITEMLISTXML(@REGISTRANTREGISTRATIONINFORMATION)
                where RESPONSETYPECODE in (0,1,4)            -- Text, Multi-line text

            ) as source
                on target.REGISTRANTID = @REGISTRANTID
                    and target.REGISTRATIONINFORMATIONID = source.REGISTRATIONINFORMATIONID
                    and target.PERSONDETAILTYPECODE = source.PERSONDETAILTYPECODE
        --            and target.ID = source.ID

            when matched and target.TEXTVALUE <> source.TEXTVALUE then
                update
                    set
                        target.TEXTVALUE = source.TEXTVALUE,
                        target.CHANGEDBYID = @CHANGEAGENTID,
                        target.DATECHANGED = @CHANGEDATE
            when not matched by target then
                insert (ID, REGISTRANTID, REGISTRATIONINFORMATIONID, REGISTRATIONINFORMATIONOPTIONID, TEXTVALUE, BOOLEANVALUE, PERSONDETAILTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values (newID(), @REGISTRANTID, source.REGISTRATIONINFORMATIONID, null, source.TEXTVALUE, 0, source.PERSONDETAILTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE)
            when not matched by source and target.REGISTRANTID = @REGISTRANTID and (select RESPONSETYPECODE from dbo.REGISTRATIONINFORMATION where ID = target.REGISTRATIONINFORMATIONID) in (0,1,4) then
                delete;

        -- Dropdown list

        merge into dbo.REGISTRANTREGISTRATIONINFORMATION as target
        using (
            select
                ID,
                REGISTRATIONINFORMATIONID,
                REGISTRATIONINFORMATIONOPTIONID,
                PERSONDETAILTYPECODE
            from dbo.UFN_REGISTRANT_GETREGISTRATIONINFORMATION_FROMITEMLISTXML(@REGISTRANTREGISTRATIONINFORMATION)
            where RESPONSETYPECODE = 2            -- Dropdown list

        ) as source
            on target.REGISTRANTID = @REGISTRANTID
                and target.REGISTRATIONINFORMATIONID = source.REGISTRATIONINFORMATIONID
                and target.PERSONDETAILTYPECODE = source.PERSONDETAILTYPECODE
    --            and target.ID = source.ID

        when matched and ( 
                    (source.REGISTRATIONINFORMATIONOPTIONID is null and target.REGISTRATIONINFORMATIONOPTIONID is not null)
                    or (source.REGISTRATIONINFORMATIONOPTIONID is not null and target.REGISTRATIONINFORMATIONOPTIONID is null)
                    or (source.REGISTRATIONINFORMATIONOPTIONID <> target.REGISTRATIONINFORMATIONOPTIONID)
                ) then
            update
                set
                    target.REGISTRATIONINFORMATIONOPTIONID = source.REGISTRATIONINFORMATIONOPTIONID,
                    target.CHANGEDBYID = @CHANGEAGENTID,
                    target.DATECHANGED = @CHANGEDATE
        when not matched by target then
            insert (ID, REGISTRANTID, REGISTRATIONINFORMATIONID, REGISTRATIONINFORMATIONOPTIONID, TEXTVALUE, BOOLEANVALUE, PERSONDETAILTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (newID(), @REGISTRANTID, source.REGISTRATIONINFORMATIONID, source.REGISTRATIONINFORMATIONOPTIONID, '', 0, source.PERSONDETAILTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE)
        when not matched by source and target.REGISTRANTID = @REGISTRANTID and (select RESPONSETYPECODE from dbo.REGISTRATIONINFORMATION where ID = target.REGISTRATIONINFORMATIONID) = 2 then
            delete;

        -- Checkbox (Yes/No)

        merge into dbo.REGISTRANTREGISTRATIONINFORMATION as target
        using (
            select
                ID,
                REGISTRATIONINFORMATIONID,
                case isnull(REGISTRATIONINFORMATIONOPTIONID, '00000000-0000-0000-0000-000000000000')
                    when '00000000-0000-0000-0000-000000000000' then 0
                    when '11111111-1111-1111-1111-111111111111' then 1
                end as BOOLEANVALUE,
                PERSONDETAILTYPECODE
            from dbo.UFN_REGISTRANT_GETREGISTRATIONINFORMATION_FROMITEMLISTXML(@REGISTRANTREGISTRATIONINFORMATION)
            where RESPONSETYPECODE = 3            -- Checkbox (Yes/No)

        ) as source
            on target.REGISTRANTID = @REGISTRANTID
                and target.REGISTRATIONINFORMATIONID = source.REGISTRATIONINFORMATIONID
                and target.PERSONDETAILTYPECODE = source.PERSONDETAILTYPECODE
    --            and target.ID = source.ID

        when matched and target.BOOLEANVALUE <> source.BOOLEANVALUE then
            update
                set
                    target.BOOLEANVALUE = source.BOOLEANVALUE,
                    target.CHANGEDBYID = @CHANGEAGENTID,
                    target.DATECHANGED = @CHANGEDATE
        when not matched by target then
            insert (ID, REGISTRANTID, REGISTRATIONINFORMATIONID, REGISTRATIONINFORMATIONOPTIONID, TEXTVALUE, BOOLEANVALUE, PERSONDETAILTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (newID(), @REGISTRANTID, source.REGISTRATIONINFORMATIONID, null, '', source.BOOLEANVALUE, source.PERSONDETAILTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE)
        when not matched by source and target.REGISTRANTID = @REGISTRANTID and (select RESPONSETYPECODE from dbo.REGISTRATIONINFORMATION where ID = target.REGISTRATIONINFORMATIONID) = 3 then
            delete;

        if @contextCache is not null
            set CONTEXT_INFO @contextCache;
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end