USP_WPSOCIAL_ADD

CREATE procedure dbo.USP_WPSOCIAL_ADD (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @WEALTHID uniqueidentifier,
                @SOURCE nvarchar(100),
                @ORIGINALID int = 0,
                @FULLHASH nvarchar(32) = '',
                @PARTIALHASH nvarchar(32) = '',
                @MC nvarchar(3) = '',
                @RECURSIVEMATCH bit = 0,
        @FULLNAME nvarchar(1200) = '',
        @GENDERCODE tinyint = 0,
        @LOCATION nvarchar(4000) = '',
        @OCCUPATION nvarchar(2048) = '',
        @COMPANY nvarchar(4000) = '',
        @SOCIALMEDIARATING int = -1,
        @INFLUENCERATING int = -1,
        @OUTREACHRATING int = -1,
                @SOCIALMEDIARATINGATTRIBUTEID nvarchar(32) = '',
                @INFLUENCERATINGATTRIBUTEID nvarchar(32) = '',
                @OUTREACHRATINGATTRIBUTEID nvarchar(32) = ''
            )
as
begin
  set nocount on;

  -- do work


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

  begin try

        declare @CURRENTDATE datetime;
        set @CURRENTDATE = getdate();

        --Get any existing ID

        select 
            @ID = ID 
        from 
            dbo.WPSOCIAL
        where 
            ID=@WEALTHID;

        -- Record exists

        if @ID is not null 
        begin
      if not exists(select 1 from dbo.WPSOCIAL where @ID = ID and FULLHASH=@FULLHASH) 
            begin
                --Record exists but needs to be updated


                update
                    dbo.WPSOCIAL
        set
                    SOURCE = coalesce(nullif(@SOURCE, ''), SOURCE),
          FULLNAME = coalesce(@FULLNAME, ''),
          GENDERCODE = coalesce(@GENDERCODE, 0),
          SOCIALMEDIARATING = coalesce(@SOCIALMEDIARATING, 0),
          INFLUENCERATING = coalesce(@INFLUENCERATING, 0),
          OUTREACHRATING = coalesce(@OUTREACHRATING, 0),
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE,
          FULLHASH = coalesce(@FULLHASH, '')
        where @ID = ID
      end

        end    else begin

      --Record is new and needs to be Added

            if @ID is null
                set @ID = @WEALTHID;

      insert into WPSOCIAL (
          ID,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED, 
          DATECHANGED,
          SOURCE,
          FULLNAME,
          GENDERCODE,
          LOCATION,
          OCCUPATION,
          COMPANY,
          SOCIALMEDIARATING,
          INFLUENCERATING,
          OUTREACHRATING,
        FULLHASH
      ) values
      (
          @ID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE, 
          @CURRENTDATE,
          coalesce(@SOURCE, ''),
          coalesce(@FULLNAME, ''),
                coalesce(@GENDERCODE, 0),
          coalesce(@LOCATION, ''),
          coalesce(@OCCUPATION, ''),
          coalesce(@COMPANY, ''),
          coalesce(@SOCIALMEDIARATING, 0),
          coalesce(@INFLUENCERATING, 0),
          coalesce(@OUTREACHRATING, 0),
                coalesce(@FULLHASH, '')
      )
    end

    Update dbo.WEALTH set SOCIALMEDIADATE = @CURRENTDATE where @ID = ID;

    -- Add/Edit social attribute scores

    -- This part expects that a value was provided (> -1) and the ID for this attribute was already populated.

    -- We mainly chose this for performance reasons, where calling each attribute once per screen was far better than once per constituent.


    declare @ADDRATINGS bit = 0;
        declare @PARAMETERDEFINITION nvarchar(500);
    set @PARAMETERDEFINITION = N'@PARENTID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @VALUE int, @STARTDATE datetime';

    if @SOCIALMEDIARATING > -1 and isnull(@SOCIALMEDIARATINGATTRIBUTEID, '') <> ''
    begin
      set @ADDRATINGS = 1;
          declare @SQLTOEXECSMR nvarchar(max);
      set @SQLTOEXECSMR = 
          N'
          declare @ATTRIBUTEID uniqueidentifier;
        select @ATTRIBUTEID = ID from dbo.ATTRIBUTE' + @SOCIALMEDIARATINGATTRIBUTEID + ' where ID = @PARENTID;

        if @ATTRIBUTEID is not null begin
          exec dbo.USP_ATTRIBUTE' + @SOCIALMEDIARATINGATTRIBUTEID + '_EDIT @ID = @PARENTID, @VALUE = @VALUE, @STARTDATE = @STARTDATE, @CHANGEAGENTID = @CHANGEAGENTID;          
        end else begin
          exec dbo.USP_ATTRIBUTE' + @SOCIALMEDIARATINGATTRIBUTEID + '_ADD @ID = @PARENTID, @VALUE = @VALUE, @STARTDATE = @STARTDATE, @CHANGEAGENTID = @CHANGEAGENTID;
        end
      ';

      exec sp_executesql @SQLTOEXECSMR, @PARAMETERDEFINITION,  @PARENTID = @WEALTHID, @CHANGEAGENTID = @CHANGEAGENTID, @VALUE = @SOCIALMEDIARATING, @STARTDATE = @CURRENTDATE;

    end

    if @INFLUENCERATING > -1 and isnull(@INFLUENCERATINGATTRIBUTEID, '') <> ''
    begin
      set @ADDRATINGS = 1;
          declare @SQLTOEXECSMI nvarchar(max);
      set @SQLTOEXECSMI = 
          N'
          declare @ATTRIBUTEID uniqueidentifier;
        select @ATTRIBUTEID = ID from dbo.ATTRIBUTE' + @INFLUENCERATINGATTRIBUTEID + ' where ID = @PARENTID;

        if @ATTRIBUTEID is not null begin
          exec dbo.USP_ATTRIBUTE' + @INFLUENCERATINGATTRIBUTEID + '_EDIT @ID = @PARENTID, @VALUE = @VALUE, @STARTDATE = @STARTDATE, @CHANGEAGENTID = @CHANGEAGENTID;          
        end else begin
          exec dbo.USP_ATTRIBUTE' + @INFLUENCERATINGATTRIBUTEID + '_ADD @ID = @PARENTID, @VALUE = @VALUE, @STARTDATE = @STARTDATE, @CHANGEAGENTID = @CHANGEAGENTID;
        end
      ';

      exec sp_executesql @SQLTOEXECSMI, @PARAMETERDEFINITION,  @PARENTID = @WEALTHID, @CHANGEAGENTID = @CHANGEAGENTID, @VALUE = @INFLUENCERATING, @STARTDATE = @CURRENTDATE;

    end

    if @OUTREACHRATING > -1 and isnull(@OUTREACHRATINGATTRIBUTEID, '') <> ''
    begin
      set @ADDRATINGS = 1;
          declare @SQLTOEXECSMO nvarchar(max);
      set @SQLTOEXECSMO = 
          N'
          declare @ATTRIBUTEID uniqueidentifier;
        select @ATTRIBUTEID = ID from dbo.ATTRIBUTE' + @OUTREACHRATINGATTRIBUTEID + ' where ID = @PARENTID;

        if @ATTRIBUTEID is not null begin
          exec dbo.USP_ATTRIBUTE' + @OUTREACHRATINGATTRIBUTEID + '_EDIT @ID = @PARENTID, @VALUE = @VALUE, @STARTDATE = @STARTDATE, @CHANGEAGENTID = @CHANGEAGENTID;          
        end else begin
          exec dbo.USP_ATTRIBUTE' + @OUTREACHRATINGATTRIBUTEID + '_ADD @ID = @PARENTID, @VALUE = @VALUE, @STARTDATE = @STARTDATE, @CHANGEAGENTID = @CHANGEAGENTID;
        end
      ';

      exec sp_executesql @SQLTOEXECSMO, @PARAMETERDEFINITION,  @PARENTID = @WEALTHID, @CHANGEAGENTID = @CHANGEAGENTID, @VALUE = @OUTREACHRATING, @STARTDATE = @CURRENTDATE;

    end

    if @ADDRATINGS = 1
    begin
      --Add a modeling and propensity record to be associated with the M&P attributes we just added.

      exec dbo.USP_MODELINGANDPROPENSITY_ADDIFDOESNOTEXIST @WEALTHID, @CHANGEAGENTID;
    end

  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
  end catch

  return 0;

end