USP_CONSTITUENTUPDATEBATCH_APPLYNAMERULES

CREATE procedure dbo.USP_CONSTITUENTUPDATEBATCH_APPLYNAMERULES (
    @ID uniqueidentifier,
    @PRIMARYRECORDID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @NAMECODE tinyint = 1
    )
as
begin
    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    declare @UNKNOWNGENDERCODEID uniqueidentifier = (select dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(0))

    set @CURRENTDATE = getdate()

    declare @FIRSTNAME nvarchar(100)
    declare @KEYNAME nvarchar(200)
    declare @MIDDLENAME nvarchar(100)
    declare @ORIGINAL_KEYNAME nvarchar(100)
    declare @ORIGINAL_FIRSTNAME nvarchar(50)
    declare @SUFFIXCODEID uniqueidentifier
    declare @TITLECODEID uniqueidentifier
    declare @EXISTINGFIRSTNAME nvarchar(100)
    declare @EXISTINGKEYNAME nvarchar(200)
    declare @EXISTINGMIDDLENAME nvarchar(100)
    declare @EXISTINGSUFFIXCODEID uniqueidentifier
    declare @EXISTINGTITLECODEID uniqueidentifier
    -- secondary name fields for existing and incoming constituents

    declare @GENDERCODE tinyint
    declare @MARITALSTATUSCODEID uniqueidentifier
    declare @MAIDENNAME nvarchar(100)
    declare @NICKNAME nvarchar(50)
    declare @DECEASED bit
    declare @DECEASEDDATE dbo.UDT_FUZZYDATE
    declare @GIVESANONYMOUSLY bit
    declare @WEBSITE dbo.UDT_WEBADDRESS
    declare @EXISTINGGENDERCODE tinyint
    declare @EXISTINGMARITALSTATUSCODEID uniqueidentifier
    declare @EXISTINGMAIDENNAME nvarchar(100)
    declare @EXISTINGNICKNAME nvarchar(50)
    declare @EXISTINGDECEASED bit
    declare @EXISTINGDECEASEDDATE dbo.UDT_FUZZYDATE
    declare @EXISTINGGIVESANONYMOUSLY bit
    declare @EXISTINGWEBSITE dbo.UDT_WEBADDRESS
    declare @UPDATESECONDARYDATA bit = 0
    declare @NOCONFLICTS bit = 0
    declare @GENDERCODEID uniqueidentifier
    declare @EXISTINGGENDERCODEID uniqueidentifier

    -- Get existing constituent names

    select @EXISTINGFIRSTNAME = FIRSTNAME,
        @EXISTINGKEYNAME = KEYNAME,
        @EXISTINGMIDDLENAME = MIDDLENAME,
        @EXISTINGSUFFIXCODEID = SUFFIXCODEID,
        @EXISTINGTITLECODEID = TITLECODEID,
        @EXISTINGGENDERCODE = GENDERCODE,
        @EXISTINGMARITALSTATUSCODEID = MARITALSTATUSCODEID,
        @EXISTINGMAIDENNAME = MAIDENNAME,
        @EXISTINGNICKNAME = NICKNAME,
        @EXISTINGDECEASED = case when DC.ID is not null then 1 else 0 end,
        @EXISTINGDECEASEDDATE = case when DC.DECEASEDDATE is not null then DC.DECEASEDDATE else '00000000' end,
        @EXISTINGGIVESANONYMOUSLY = GIVESANONYMOUSLY,
        @EXISTINGWEBSITE = WEBADDRESS,
        @EXISTINGGENDERCODEID = GENDERCODEID
    from dbo.CONSTITUENT C
    left join dbo.DECEASEDCONSTITUENT DC on DC.ID = C.ID
    where C.ID = @PRIMARYRECORDID;

    -- Get names in batch

    select @FIRSTNAME = FIRSTNAME,
        @KEYNAME = KEYNAME,
        @MIDDLENAME = MIDDLENAME,
        @SUFFIXCODEID = SUFFIXCODEID,
        @TITLECODEID = TITLECODEID,
        @GENDERCODE = GENDERCODE,
        @MARITALSTATUSCODEID = MARITALSTATUSCODEID,
        @MAIDENNAME = MAIDENNAME,
        @NICKNAME = NICKNAME,
        @DECEASED = DECEASED,
        @DECEASEDDATE = DECEASEDDATE,
        @GIVESANONYMOUSLY = GIVESANONYMOUSLY,
        @WEBSITE = WEBADDRESS,
        @GENDERCODEID = GENDERCODEID,
        @ORIGINAL_KEYNAME = ORIGINAL_KEYNAME,
        @ORIGINAL_FIRSTNAME = ORIGINAL_FIRSTNAME
    from dbo.BATCHCONSTITUENTUPDATE
    where ID = @ID;

    -- If the original names match those in the database case-sensitive

    -- then we should store the original values

    if @EXISTINGKEYNAME <> @ORIGINAL_KEYNAME COLLATE Latin1_General_CS_AS set @ORIGINAL_KEYNAME = null
    if @EXISTINGFIRSTNAME <> @ORIGINAL_FIRSTNAME COLLATE Latin1_General_CS_AS set @ORIGINAL_FIRSTNAME = null

    update dbo.BATCHCONSTITUENTUPDATE
    set
        KEYNAME = isnull(@ORIGINAL_KEYNAME, KEYNAME),
        FIRSTNAME = isnull(@ORIGINAL_FIRSTNAME, FIRSTNAME)
    where
        ID = @ID

    -- check to see if there are any conflicts in the core fields.  If none, we are allowed to apply any new information.

    -- If there are conflicts we don't want to apply new information.  So if 'Tim J Littleton' is coming in

    -- and 'Tim Jones' is the existing name, we don't want to add the J to the existing record.

    -- if its an update or ignore or Alias, see if there are any conflicts.  If not set to replace to fill in any new data to current record.

    if @NAMECODE <> 1 and (@EXISTINGFIRSTNAME = '' or @EXISTINGFIRSTNAME = @FIRSTNAME or @FIRSTNAME = '') and (@EXISTINGKEYNAME = '' or @EXISTINGKEYNAME = @KEYNAME or @KEYNAME = '') and (@EXISTINGMIDDLENAME = '' or @EXISTINGMIDDLENAME = @MIDDLENAME or @MIDDLENAME = '') and (@EXISTINGSUFFIXCODEID is null or @SUFFIXCODEID is null or @EXISTINGSUFFIXCODEID = @SUFFIXCODEID) and (@EXISTINGTITLECODEID is null or @TITLECODEID is null or @EXISTINGTITLECODEID = @TITLECODEID)
        set @NOCONFLICTS = 1

    if @NAMECODE = 1 or @NOCONFLICTS = 1
    begin
        if @NAMECODE <> 0
            set @UPDATESECONDARYDATA = 1
                --check for conflicts in secondary fields

        else
            if (@EXISTINGGENDERCODE = 0 or @EXISTINGGENDERCODE = @GENDERCODE or @GENDERCODE = 0 or @EXISTINGGENDERCODEID = null or @EXISTINGGENDERCODEID = @GENDERCODEID or @EXISTINGGENDERCODEID = @UNKNOWNGENDERCODEID ) and (@EXISTINGMARITALSTATUSCODEID is null or @EXISTINGMARITALSTATUSCODEID = @MARITALSTATUSCODEID or @MARITALSTATUSCODEID is null) and (@EXISTINGMAIDENNAME = '' or @EXISTINGMAIDENNAME = @MAIDENNAME or @MAIDENNAME = '') and (@EXISTINGNICKNAME = '' or @EXISTINGNICKNAME = @NICKNAME or @NICKNAME = '') and (@EXISTINGDECEASED = @DECEASED or @DECEASED = 0) and (@EXISTINGDECEASEDDATE = '00000000' or @EXISTINGDECEASEDDATE = @DECEASEDDATE or @DECEASEDDATE = '00000000') and (@EXISTINGGIVESANONYMOUSLY = @GIVESANONYMOUSLY or @GIVESANONYMOUSLY = 0) and (@EXISTINGWEBSITE = '' or @EXISTINGWEBSITE = @WEBSITE or @WEBSITE = '')
                set @UPDATESECONDARYDATA = 1
            else
                set @UPDATESECONDARYDATA = 0

        if @UPDATESECONDARYDATA = 1 --  update core and secondary fields      

            update dbo.BATCHCONSTITUENTUPDATE
            set FIRSTNAME = coalesce(NULLIF(FIRSTNAME, ''), @EXISTINGFIRSTNAME),
                KEYNAME = coalesce(NULLIF(KEYNAME, ''), @EXISTINGKEYNAME),
                MIDDLENAME = coalesce(NULLIF(MIDDLENAME, ''), @EXISTINGMIDDLENAME),
                SUFFIXCODEID = coalesce(SUFFIXCODEID, @EXISTINGSUFFIXCODEID),
                TITLECODEID = coalesce(TITLECODEID, @EXISTINGTITLECODEID),
                GENDERCODE = coalesce(NULLIF(GENDERCODE, 0), @EXISTINGGENDERCODE),
                GENDERCODEID = case when GENDERCODEID is null or (GENDERCODEID = @UNKNOWNGENDERCODEID and @EXISTINGGENDERCODEID is not null) 
                                                then @EXISTINGGENDERCODEID
                                                else GENDERCODEID end,
                MARITALSTATUSCODEID = coalesce(MARITALSTATUSCODEID, @EXISTINGMARITALSTATUSCODEID),
                MAIDENNAME = coalesce(NULLIF(MAIDENNAME, ''), @EXISTINGMAIDENNAME),
                NICKNAME = coalesce(NULLIF(NICKNAME, ''), @EXISTINGNICKNAME),
                DECEASED = coalesce(NULLIF(DECEASED, 0), @EXISTINGDECEASED),
                DECEASEDDATE = coalesce(NULLIF(DECEASEDDATE, '00000000'), @EXISTINGDECEASEDDATE),
                GIVESANONYMOUSLY = coalesce(NULLIF(GIVESANONYMOUSLY, 0), @EXISTINGGIVESANONYMOUSLY),
                WEBADDRESS = coalesce(NULLIF(WEBADDRESS, ''), @EXISTINGWEBSITE),
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID
        else -- there are secondary conflicts so only update core fields and ignore secondary fields

            -- If the setting is replace we want to populate any name conflicts with the newly entered value.

            -- If there is no new value, we want to use the existing value.           

            update dbo.BATCHCONSTITUENTUPDATE
            set FIRSTNAME = coalesce(NULLIF(FIRSTNAME, ''), @EXISTINGFIRSTNAME),
                KEYNAME = coalesce(NULLIF(KEYNAME, ''), @EXISTINGKEYNAME),
                MIDDLENAME = coalesce(NULLIF(MIDDLENAME, ''), @EXISTINGMIDDLENAME),
                SUFFIXCODEID = coalesce(SUFFIXCODEID, @EXISTINGSUFFIXCODEID),
                TITLECODEID = coalesce(TITLECODEID, @EXISTINGTITLECODEID),
                GENDERCODE = @EXISTINGGENDERCODE,
                GENDERCODEID = @EXISTINGGENDERCODEID,
                MARITALSTATUSCODEID = @EXISTINGMARITALSTATUSCODEID,
                MAIDENNAME = @EXISTINGMAIDENNAME,
                NICKNAME = @EXISTINGNICKNAME,
                DECEASED = @EXISTINGDECEASED,
                DECEASEDDATE = @EXISTINGDECEASEDDATE,
                REMOVESPOUSE = 0,
                GIVESANONYMOUSLY = @EXISTINGGIVESANONYMOUSLY,
                WEBADDRESS = @EXISTINGWEBSITE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID
    end
            --If the setting is Do not replace we want to populate any name conflicts with what already exists in the constituent table.

            --In this case we can blank out values in the Batch line if the existing value in the database table is blank.

    else
        if @NAMECODE in (
                0,
                3
                ) -- ignore core and secondary fields also here     

            update dbo.BATCHCONSTITUENTUPDATE
            set FIRSTNAME = @EXISTINGFIRSTNAME,
                KEYNAME = @EXISTINGKEYNAME,
                MIDDLENAME = @EXISTINGMIDDLENAME,
                SUFFIXCODEID = @EXISTINGSUFFIXCODEID,
                TITLECODEID = @EXISTINGTITLECODEID,
                GENDERCODE = @EXISTINGGENDERCODE,
                GENDERCODEID = @EXISTINGGENDERCODEID,
                MARITALSTATUSCODEID = @EXISTINGMARITALSTATUSCODEID,
                MAIDENNAME = @EXISTINGMAIDENNAME,
                NICKNAME = @EXISTINGNICKNAME,
                DECEASED = @EXISTINGDECEASED,
                DECEASEDDATE = @EXISTINGDECEASEDDATE,
                REMOVESPOUSE = 0,
                GIVESANONYMOUSLY = @EXISTINGGIVESANONYMOUSLY,
                WEBADDRESS = @EXISTINGWEBSITE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID

    -- Handle Alias.  Insert into Alias table and ignore name in batch since we don't want to replace during commit.

    if @NAMECODE = 3
    begin try
        declare @IGNOREDUPLICATE bit = 1;--if we are here we want to ignore the duplicate error message and continue on.                


        exec dbo.USP_ADD_INDIVIDUAL_ALIAS @CHANGEAGENTID = @CHANGEAGENTID,
            @CONSTITUENTID = @PRIMARYRECORDID,
            @KEYNAME = @KEYNAME,
            @FIRSTNAME = @FIRSTNAME,
            @MIDDLENAME = @MIDDLENAME,
            @TITLECODEID = @TITLECODEID,
            @SUFFIXCODEID = @SUFFIXCODEID,
            @IGNOREDUPLICATE = @IGNOREDUPLICATE;
    end try

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