USP_SOCIALMEDIAACCOUNT_ADD

CREATE procedure dbo.USP_SOCIALMEDIAACCOUNT_ADD (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @WEALTHSOURCE nvarchar(100),
        @CONSTITUENTID uniqueidentifier,
        @EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
        @SOCIALMEDIASERVICEID uniqueidentifier,
        @INFORMATIONSOURCEID uniqueidentifier = null,
        @URL dbo.UDT_WEBADDRESS = '',
        @USERID nvarchar(100) = '',
        @MC nvarchar(3) = '',
        @FULLHASH nvarchar(32) = '',
                @PARTIALHASH nvarchar(32) = ''
        ) as begin
        set nocount on;

                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.SOCIALMEDIAACCOUNT
                    where 
                        CONSTITUENTID = @CONSTITUENTID and PARTIALHASH=@PARTIALHASH;

          declare @CONFIRMED bit;    
                    declare @CONFIRMEDBYAPPUSERID uniqueidentifier;
                    declare @DATECONFIRMED datetime;
                    declare @REJECTED bit;
                    declare @REJECTEDBYAPPUSERID uniqueidentifier; 
                    declare @DATEREJECTED datetime;    

                    set @CONFIRMED = 0;
                    set @REJECTED = 0;

                    if @CURRENTAPPUSERID is not null
                    begin
                        declare @CONFIDENCE int

                        select 
                            @CONFIDENCE = coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE)
                        from
                            dbo.MATCHCODE MC
                            left outer join
                                dbo.WEALTHSOURCE WS on @WEALTHSOURCE = WS.SOURCE
                            left outer join 
                                dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and CR.WEALTHSOURCEID = WS.ID
                        where
                            MC.MATCHCODE = @MC

                        select                   
                            @REJECTED =                case @CONFIDENCE
                                                        when 5 then 0
                                                        when 0 then 1
                                                    end,
                            @DATEREJECTED =            case @CONFIDENCE
                                                        when 5 then NULL
                                                        when 0 then @CURRENTDATE 
                                                    end,
                            @REJECTEDBYAPPUSERID =     case @CONFIDENCE
                                                        when 5 then NULL
                                                        when 0 then @CURRENTAPPUSERID 
                                                    end,
                            @CONFIRMED =            case @CONFIDENCE
                                                        when 5 then 1
                                                        when 0 then 0
                                                    end,
                            @DATECONFIRMED =        case @CONFIDENCE
                                                        when 5 then @CURRENTDATE 
                                                        when 0 then    NULL
                                                    end,
                            @CONFIRMEDBYAPPUSERID = case @CONFIDENCE
                                                        when 5 then  @CURRENTAPPUSERID 
                                                        when 0 then NULL
                                                    end 
                        where
                            @CONFIDENCE in (0,5);                    
                    end        


          if @ID is not null
          begin
                        if not exists(select 1 from dbo.SOCIALMEDIAACCOUNT where CONSTITUENTID=@CONSTITUENTID and FULLHASH=@FULLHASH)
            begin
              update
                                dbo.SOCIALMEDIAACCOUNT
                            set
                                FULLHASH = @FULLHASH,
                                WPMATCHCODE = @MC,
                USERID = @USERID,
                INFOSOURCECODEID = @INFORMATIONSOURCEID,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE,
                                CONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then 1 else CONFIRMED end,
                                CONFIRMEDBYAPPUSERID = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @CONFIRMEDBYAPPUSERID else CONFIRMEDBYAPPUSERID end,
                                DATECONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @DATECONFIRMED else DATECONFIRMED end
                            where
                                CONSTITUENTID=@CONSTITUENTID and PARTIALHASH=@PARTIALHASH;
            end
          end
          else
          begin
            --Record is new and needs to be Added


              if @ID is null
                            set @ID = newid();

              declare @SEQUENCE int;

            select
                @SEQUENCE = coalesce(max(SEQUENCE),0)+1
            from
                dbo.SOCIALMEDIAACCOUNT
            where
                CONSTITUENTID=@CONSTITUENTID;

            insert into dbo.SOCIALMEDIAACCOUNT(
              ID,
              CONSTITUENTID,
              SOCIALMEDIASERVICEID,
              USERID,
              URL,
              SOCIALMEDIAACCOUNTTYPECODEID,
              INFOSOURCECODEID,
              DONOTCONTACT,
              CONFIRMED,
              DATECONFIRMED,
              CONFIRMEDBYAPPUSERID,
              REJECTED,
              DATEREJECTED,
              REJECTEDBYAPPUSERID,
              EMAILADDRESS,
              WEALTHSOURCE,
              WPMATCHCODE,
              PARTIALHASH,
              FULLHASH,
              SEQUENCE,
              ADDEDBYID,
              CHANGEDBYID,
              DATEADDED,
              DATECHANGED
            )
            values (
              @ID,
              @CONSTITUENTID,
              @SOCIALMEDIASERVICEID,
              @USERID,
              @URL,
              null,
              @INFORMATIONSOURCEID,
              0,
              @CONFIRMED,
              @DATECONFIRMED,
              @CONFIRMEDBYAPPUSERID,
              @REJECTED,
              @DATEREJECTED,
              @REJECTEDBYAPPUSERID,
              @EMAILADDRESS,
              @WEALTHSOURCE,
              @MC,
              @PARTIALHASH,
              @FULLHASH,
              @SEQUENCE,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            )
          end

        end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                end catch

                return 0;

end