USP_SYNCTEMPSOCIALMEDIAACCOUNTS

CREATE procedure dbo.USP_SYNCTEMPSOCIALMEDIAACCOUNTS
as
begin

declare @userConstitIDs table
(
  ID uniqueidentifier,
  CSMAID uniqueidentifier   
);

declare @INFOSOURCECODEID uniqueidentifier
    select @INFOSOURCECODEID = [INFOSOURCECODEID] from [NETCOMMUNITYDEFAULTCODEMAP]

declare @CHANGEAGENTID uniqueidentifier;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

--Adding all users which are linked to constituents with data in CMSUSERSOCIALMEDIAACCOUNT to temp table

insert into @userConstitIDs (ID, CSMAID)
    select C.ID as ID, CSMA.ID as CSMAID
    from dbo.clientusers CU
        inner join dbo.backofficesystemusers BOSU on BOSU.ClientUsersID = CU.ID
        inner join dbo.backofficesystempeople BOSP on BOSP.ID = BOSU.Backofficepeopleid
        inner join dbo.CONSTITUENT C on C.SEQUENCEID = BOSP.BackOfficeRecordID
        inner join dbo.CMSUSERSOCIALMEDIAACCOUNT CSMA on CU.ID = CSMA.ClientUserID
    where CU.deleted = 0 and BOSU.[Current] = 1 and BOSP.BackOfficeSystemID = 0;

    if @INFOSOURCECODEID is not null
    begin
        --Merging temp table data with Constituent SOCIALMEDIAACCOUNT

        merge dbo.SOCIALMEDIAACCOUNT AS target
        using (
                select UCID.ID as CONSTITUENTID, CSMA.SOCIALMEDIASERVICEID, CSMA.USERID, CSMA.URL, @INFOSOURCECODEID as INFOSOURCECODEID
                from @userConstitIDs UCID
                inner join dbo.CMSUSERSOCIALMEDIAACCOUNT CSMA on UCID.CSMAID = CSMA.ID
                ) as source 
        on (target.CONSTITUENTID = source.CONSTITUENTID and target.SOCIALMEDIASERVICEID = source.SOCIALMEDIASERVICEID and target.INFOSOURCECODEID = source.INFOSOURCECODEID)
        when matched then 
            update set USERID = source.USERID, URL = source.URL
        when not matched then    
            INSERT (CONSTITUENTID, SOCIALMEDIASERVICEID, USERID, URL, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            VALUES (source.CONSTITUENTID, source.SOCIALMEDIASERVICEID, source.USERID, source.URL, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    end
    else
    begin
        merge dbo.SOCIALMEDIAACCOUNT AS target
        using (
                select UCID.ID as CONSTITUENTID, CSMA.SOCIALMEDIASERVICEID, CSMA.USERID, CSMA.URL, @INFOSOURCECODEID as INFOSOURCECODEID
                from @userConstitIDs UCID
                inner join dbo.CMSUSERSOCIALMEDIAACCOUNT CSMA on UCID.CSMAID = CSMA.ID
                ) as source 
        on (target.CONSTITUENTID = source.CONSTITUENTID and target.SOCIALMEDIASERVICEID = source.SOCIALMEDIASERVICEID and target.INFOSOURCECODEID is null and target.URL = source.URL and target.USERID = source.USERID)
        when not matched then    
            INSERT (CONSTITUENTID, SOCIALMEDIASERVICEID, USERID, URL, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            VALUES (source.CONSTITUENTID, source.SOCIALMEDIASERVICEID, source.USERID, source.URL, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);    
    end

--Removing merged data from temp storage table

delete dbo.CMSUSERSOCIALMEDIAACCOUNT
from dbo.CMSUSERSOCIALMEDIAACCOUNT CSMA
inner join @userConstitIDs UCID on UCID.CSMAID = CSMA.ID
end