USP_GLOBALCHANGE_CONSTITUENTDATASTANDARDIZATION

CREATE procedure dbo.USP_GLOBALCHANGE_CONSTITUENTDATASTANDARDIZATION
(
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,    
  @IDSETREGISTERID uniqueidentifier,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output  
)
as            
        set nocount off;

    declare @CURRENTDATE datetime, @SHOULDCAPITALIZE bit = 0, @SHOULDTRANSLATE bit = 0;
    set @CURRENTDATE = getdate();
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0;   

  select @SHOULDCAPITALIZE = FIELDCAPITALIZATIONENABLED, @SHOULDTRANSLATE = ADDRESSSTANDARDIZATIONENABLED from dbo.CONSTITUENTBUSINESSRULESSETTINGS;

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    begin try
      -- Capitalize and translate

    if @SHOULDCAPITALIZE = 1 and @SHOULDTRANSLATE = 1
      begin
        update DONORS set 
              KEYNAME = CAPITALIZE.LASTNAME,
              FIRSTNAME = CAPITALIZE.FIRSTNAME,
              MIDDLENAME = CAPITALIZE.MIDDLENAME,
              MAIDENNAME = CAPITALIZE.MAIDENNAME,
              NICKNAME = CAPITALIZE.NICKNAME,
              DATECHANGED = @CURRENTDATE,
              CHANGEDBYID = @CHANGEAGENTID
        from dbo.CONSTITUENT as DONORS     
        inner join dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID) IDSET on DONORS.ID = IDSET.ID
        cross apply dbo.UFN_CONSTITUENT_CAPITALIZENAMES(DONORS.KEYNAME,DONORS.FIRSTNAME,DONORS.MIDDLENAME,DONORS.MAIDENNAME,DONORS.NICKNAME) as CAPITALIZE
        set @NUMBEREDITED = @@ROWCOUNT;    

        update ALIAS set 
              KEYNAME = CAPITALIZE.LASTNAME,
              FIRSTNAME = CAPITALIZE.FIRSTNAME,
              MIDDLENAME = CAPITALIZE.MIDDLENAME,             
              DATECHANGED = @CURRENTDATE,
              CHANGEDBYID = @CHANGEAGENTID
        from dbo.ALIAS   
        inner join dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID) IDSET on ALIAS.CONSTITUENTID = IDSET.ID
        cross apply dbo.UFN_CONSTITUENT_CAPITALIZENAMES(ALIAS.KEYNAME,ALIAS.FIRSTNAME,ALIAS.MIDDLENAME, null, null) as CAPITALIZE
        set @NUMBEREDITED = @@ROWCOUNT;    

            update DONORADDRESS set 
                      DONORADDRESS.CITY = STANDARDIZE.LASTNAME,
                      DONORADDRESS.ADDRESSBLOCK = dbo.UFN_ADDRESS_STANDARDIZE(STANDARDIZE.FIRSTNAME,DONORADDRESS.COUNTRYID),
                      DATECHANGED = @CURRENTDATE,
                      CHANGEDBYID = @CHANGEAGENTID
          from dbo.ADDRESS as DONORADDRESS
          inner join dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID) IDSET on DONORADDRESS.CONSTITUENTID = IDSET.ID
          cross apply dbo.UFN_CONSTITUENT_CAPITALIZENAMES(DONORADDRESS.CITY,DONORADDRESS.ADDRESSBLOCK,null,null,null) as STANDARDIZE         
    end

    -- Only capitalize  

      else if @SHOULDCAPITALIZE = 1 and @SHOULDTRANSLATE = 0   
        begin
        update DONORS set 
              KEYNAME = CAPITALIZE.LASTNAME,
              FIRSTNAME = CAPITALIZE.FIRSTNAME,
              MIDDLENAME = CAPITALIZE.MIDDLENAME,
              MAIDENNAME = CAPITALIZE.MAIDENNAME,
              NICKNAME = CAPITALIZE.NICKNAME,
              DATECHANGED = @CURRENTDATE,
              CHANGEDBYID = @CHANGEAGENTID
        from dbo.CONSTITUENT as DONORS     
        inner join dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID) IDSET on DONORS.ID = IDSET.ID
        cross apply dbo.UFN_CONSTITUENT_CAPITALIZENAMES(DONORS.KEYNAME,DONORS.FIRSTNAME,DONORS.MIDDLENAME,DONORS.MAIDENNAME,DONORS.NICKNAME) as CAPITALIZE
        set @NUMBEREDITED = @@ROWCOUNT;    

        update ALIAS set 
              KEYNAME = CAPITALIZE.LASTNAME,
              FIRSTNAME = CAPITALIZE.FIRSTNAME,
              MIDDLENAME = CAPITALIZE.MIDDLENAME,             
              DATECHANGED = @CURRENTDATE,
              CHANGEDBYID = @CHANGEAGENTID
        from dbo.ALIAS   
        inner join dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID) IDSET on ALIAS.CONSTITUENTID = IDSET.ID
        cross apply dbo.UFN_CONSTITUENT_CAPITALIZENAMES(ALIAS.KEYNAME,ALIAS.FIRSTNAME,ALIAS.MIDDLENAME, null, null) as CAPITALIZE
        set @NUMBEREDITED = @@ROWCOUNT;            

            update DONORADDRESS set 
                      DONORADDRESS.CITY = STANDARDIZE.LASTNAME,
                      DONORADDRESS.ADDRESSBLOCK = STANDARDIZE.FIRSTNAME,
                      DATECHANGED = @CURRENTDATE,
                      CHANGEDBYID = @CHANGEAGENTID
            from dbo.ADDRESS as DONORADDRESS
            inner join dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID) IDSET on DONORADDRESS.CONSTITUENTID = IDSET.ID
            cross apply dbo.UFN_CONSTITUENT_CAPITALIZENAMES(DONORADDRESS.CITY,DONORADDRESS.ADDRESSBLOCK,null,null,null) as STANDARDIZE                     
        end

      -- Translate only

      else if @SHOULDCAPITALIZE = 0 and @SHOULDTRANSLATE = 1
          begin
            update DONORADDRESS set 
                  DONORADDRESS.ADDRESSBLOCK = dbo.UFN_ADDRESS_STANDARDIZE(DONORADDRESS.ADDRESSBLOCK,DONORADDRESS.COUNTRYID),
                  DATECHANGED = @CURRENTDATE,
                  CHANGEDBYID = @CHANGEAGENTID
            from dbo.ADDRESS as DONORADDRESS
            inner join dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID) IDSET on DONORADDRESS.CONSTITUENTID = IDSET.ID

            select @NUMBEREDITED = count(ID) from dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID);    
          end    
    end try

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