USP_CONSTITUENTUPDATEBATCH_APPLYCONSTITUENTMATCHINGRULES

CREATE procedure dbo.USP_CONSTITUENTUPDATEBATCH_APPLYCONSTITUENTMATCHINGRULES(
@ID uniqueidentifier,
@PRIMARYRECORDID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
begin
    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @FIRSTNAME nvarchar(100)
    declare @KEYNAME nvarchar(200)
    declare @MIDDLENAME nvarchar(100)
    declare @SUFFIXCODEID uniqueidentifier
    declare @TITLECODEID uniqueidentifier
    declare @EXISTINGFIRSTNAME nvarchar(100)
    declare @EXISTINGKEYNAME nvarchar(200)
    declare @EXISTINGMIDDLENAME nvarchar(100)
    declare @EXISTINGSUFFIXCODEID uniqueidentifier
    declare @EXISTINGTITLECODEID uniqueidentifier

    -- Get existing constituent names

    select 
        @EXISTINGFIRSTNAME = FIRSTNAME,
        @EXISTINGKEYNAME = KEYNAME,
        @EXISTINGMIDDLENAME = MIDDLENAME,
        @EXISTINGSUFFIXCODEID = SUFFIXCODEID,
        @EXISTINGTITLECODEID = TITLECODEID 
    from dbo.CONSTITUENT
    where ID = @PRIMARYRECORDID

    -- Get names in batch

    select 
        @FIRSTNAME = FIRSTNAME,
        @KEYNAME = KEYNAME,
        @MIDDLENAME = MIDDLENAME,
        @SUFFIXCODEID = SUFFIXCODEID,
        @TITLECODEID = TITLECODEID 
    from dbo.BATCHCONSTITUENTUPDATE
    where ID = @ID

    declare @UPDATENAMES tinyint = 0;

    -- check to see if there are any conflicts.  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.


    select top 1 
        @UPDATENAMES=CONSTITUENTUPDATENAMESGLOBALSETTINGCODE 
    from dbo.CONSTITUENTBUSINESSRULESSETTINGS;

    -- 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 @UPDATENAMES IN (0,3) 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 @UPDATENAMES = 1


    --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.      


    if @UPDATENAMES = 1
        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),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID  

    --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 @UPDATENAMES in (0,3)
     begin
         update dbo.BATCHCONSTITUENTUPDATE set
            FIRSTNAME    = @EXISTINGFIRSTNAME,
            KEYNAME      = @EXISTINGKEYNAME,
            MIDDLENAME   = @EXISTINGMIDDLENAME,
            SUFFIXCODEID = @EXISTINGSUFFIXCODEID,
            TITLECODEID  = @EXISTINGTITLECODEID,
            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 @UPDATENAMES = 3
            exec dbo.USP_DATAFORMTEMPLATE_ADD_INDIVIDUALALIAS                                          
                            @CHANGEAGENTID = @CHANGEAGENTID,    
                            @CONSTITUENTID = @PRIMARYRECORDID,                                    
                            @KEYNAME = @KEYNAME,
                            @FIRSTNAME = @FIRSTNAME,
                            @MIDDLENAME = @MIDDLENAME, 
                            @TITLECODEID = @TITLECODEID,                                        
                            @SUFFIXCODEID = @SUFFIXCODEID;
     end
end