USP_MERGETASK_NETCOMMUNITY

CREATE procedure dbo.USP_MERGETASK_NETCOMMUNITY
(  
    @SOURCEID uniqueidentifier,  
    @TARGETID uniqueidentifier,  
    @CHANGEAGENTID uniqueidentifier  
)  
as  
    set nocount on;

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

    declare @ALTRUMERGE bit = 0
    declare @FAFMERGE bit = 0
    if    dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('a919502c-a2f6-4a56-9183-28e3f667916e') = 1
    begin
        set @FAFMERGE = 1
    end
    else if    dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('e5e0494b-ba0f-4e23-b8fb-a59112dbf3c8') = 1 and --Basic CMS

                dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('f238e8fe-06ae-4fdc-beaf-fdf6637e1982') = 0 -- CMS

    begin
        set @ALTRUMERGE = 1
    end

  declare @SOURCENETCOMMUNITYCLIENTUSERID as uniqueidentifier;
    declare @SOURCECLIENTUSERID int
    select @SOURCECLIENTUSERID = [BBNCUSERID],@SOURCENETCOMMUNITYCLIENTUSERID = ID
    from dbo.NETCOMMUNITYCLIENTUSER 
    where CONSTITUENTID = @SOURCEID

  declare @TARGETNETCOMMUNITYCLIENTUSERID as uniqueidentifier;
    declare @TARGETCLIENTUSERID int
    select @TARGETCLIENTUSERID = [BBNCUSERID],@TARGETNETCOMMUNITYCLIENTUSERID = ID
    from dbo.NETCOMMUNITYCLIENTUSER 
    where CONSTITUENTID = @TARGETID

  declare @SOURCESEQUENCEID int
  select @SOURCESEQUENCEID = SEQUENCEID
  from dbo.CONSTITUENT 
  where ID = @SOURCEID

  declare @TARGETSEQUENCEID int
  select @TARGETSEQUENCEID = SEQUENCEID
  from dbo.CONSTITUENT 
  where ID = @TARGETID

    if @SOURCECLIENTUSERID is not null and @TARGETCLIENTUSERID is null
    begin
        update dbo.NETCOMMUNITYCLIENTUSER  
        set 
            CONSTITUENTID = @TARGETID, 
            CHANGEDBYID = @CHANGEAGENTID, 
            DATECHANGED = @NOW
        where CONSTITUENTID = @SOURCEID;
    end
    else if @SOURCECLIENTUSERID > @TARGETCLIENTUSERID and @ALTRUMERGE = 1
    begin 
        --For CMS, we don't want to replace the target's current user account, so we're not doing this for them

        --In altru, we want the most recent user account if both the source and target have one

        update dbo.NETCOMMUNITYCLIENTUSER --Knock off the target's current user account

        set 
            CONSTITUENTID = null,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @NOW  
        where CONSTITUENTID = @TARGETID;

        update dbo.NETCOMMUNITYCLIENTUSER
        set 
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @NOW
        where BBNCUSERID = @SOURCECLIENTUSERID;

        --Switch target and client users since the target cannot have two and the old account has to point somewhere

        --If the constituent is not deleted in the merge, there needs to be a good link to show that the source is a net community member

        update dbo.NETCOMMUNITYCLIENTUSER
        set 
            CONSTITUENTID = @SOURCEID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @NOW
        where BBNCUSERID = @TARGETCLIENTUSERID;
    end

    --Update target and source constituent records to reflect that they are "NETCOMMUNITYMEMBER"s

    update dbo.CONSTITUENT
    set
        NETCOMMUNITYMEMBER = [NETCOMMUNITYMEMBER].[IS],
        DATECHANGED = @NOW,
        CHANGEDBYID = @CHANGEAGENTID
    from dbo.CONSTITUENT
    cross apply (
        select 
            case 
                when exists(select 1 from dbo.NETCOMMUNITYCLIENTUSER where [CONSTITUENTID] = CONSTITUENT.ID)
                    then 1
                else 0
            end as [IS]
    ) as [NETCOMMUNITYMEMBER]
    where [CONSTITUENT].[ID] in (@SOURCEID, @TARGETID)

    --Email to the source should now be shown as sent to the target regardless of if the target already had email.

  --Originally this was only done for Altru or if there were no emails for the target, but since this doesn't

  --impact CMS users it should be safe to migrate these over.

  update dbo.NETCOMMUNITYEMAILJOBRECIPIENT  
    set 
        CONSTITUENTID = @TARGETID, 
        CHANGEDBYID = @CHANGEAGENTID, 
        DATECHANGED = @NOW  
    where CONSTITUENTID = @SOURCEID;

   -- Merging Web Traffic 

    -- Copying all the page visited by source to Target

      update DBO.[NETCOMMUNITYWEBTRAFFIC]
        set NETCOMMUNITYCLIENTUSERID = @TARGETNETCOMMUNITYCLIENTUSERID,
            CHANGEDBYID = @CHANGEAGENTID, 
                  DATECHANGED = @NOW  
                where NETCOMMUNITYCLIENTUSERID = @SOURCENETCOMMUNITYCLIENTUSERID;

  declare @SOURCEBOSPID int
    select top 1 @SOURCEBOSPID = ID 
    from dbo.BackOfficeSystemPeople 
  where BACKOFFICERECORDID = @SOURCESEQUENCEID

    declare @TARGETBOSPID int
    select top 1 @TARGETBOSPID = ID 
    from dbo.BackOfficeSystemPeople   
    where BACKOFFICERECORDID = @TARGETSEQUENCEID

    if @SOURCEBOSPID is not null and @TARGETBOSPID is null
    begin
        update dbo.BackOfficeSystemPeople
        set BACKOFFICERECORDID = @TARGETSEQUENCEID
        where BACKOFFICERECORDID = @SOURCESEQUENCEID

        update dbo.ProfileUpdateTransactions 
        set BACKOFFICEID = @TARGETSEQUENCEID 
        where BACKOFFICEID = @SOURCESEQUENCEID

        --2.27.12 Note: Leaving this in for NC, but I'm not sure that this is doing anything since the source's BackOfficeSystemPeople record was just given to the target

        UPDATE dbo.EMAILLIST_SUBSCRIPTION set BACKOFFICESYSTEMPEOPLEID = (SELECT ID from dbo.BACKOFFICESYSTEMPEOPLE where BACKOFFICERECORDID = @TARGETSEQUENCEID)
        WHERE BACKOFFICESYSTEMPEOPLEID = (SELECT ID from dbo.BACKOFFICESYSTEMPEOPLE where BACKOFFICERECORDID = @SOURCESEQUENCEID)
    end
    else if @SOURCEBOSPID is not null and @TARGETBOSPID is not null and @ALTRUMERGE = 1
    begin
        --@SOURCECLIENTUSERID and @TARGETCLIENTUSERID should already have the correct values from NETCOMMUNITYCLIENTUSER

        --Still want to do this for sanity

        select @SOURCECLIENTUSERID = [ClientUsersID] from dbo.BackOfficeSystemUsers where [BackofficePeopleID] = @SOURCEBOSPID 
        select @TARGETCLIENTUSERID = [ClientUsersID] from dbo.BackOfficeSystemUsers where [BackofficePeopleID] = @TARGETBOSPID 

        --Switch target and client users since the target cannot have two and the old account has to point somewhere

        if @SOURCECLIENTUSERID > @TARGETCLIENTUSERID 
        begin
            update dbo.BackOfficeSystemUsers
            set BackofficePeopleID = @TARGETBOSPID
            where [ClientUsersID] = @SOURCECLIENTUSERID

            update dbo.BackOfficeSystemUsers
            set BackofficePeopleID = @SOURCEBOSPID
            where [ClientUsersID] = @TARGETCLIENTUSERID
        end
    end
    else if 
        @TARGETBOSPID is not null and
        (
            --For CMS, again, we don't want to replace the target's current user account

            not exists(select 1 from dbo.BackOfficeSystemUsers where BackofficePeopleID = @TARGETBOSPID) or
            --For FAF, associate the target with any back office system users the source is currently linked to regardless of whether the target already has one(s)

            @FAFMERGE = 1
        )
    begin
        update BOSU 
        set BOSU.BackofficePeopleID = @TARGETBOSPID
        from dbo.BackOfficeSystemUsers BOSU
        where BOSU.BackofficePeopleID= @SOURCEBOSPID
    end


    if @FAFMERGE = 1
    begin
        declare @FN as nvarchar(100), @LN as nvarchar(100)
        select @FN=FIRSTNAME, @LN=KEYNAME from dbo.CONSTITUENT (nolock) where ID=@TARGETID

        update dbo.ClientUsers
        set FirstName = @FN,
                LastName = @LN
        where ID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@TARGETID))      
    end

    return 0;