USP_MERGE_GROUPMEMBERS

CREATE procedure dbo.USP_MERGE_GROUPMEMBERS
(
    @SOURCEID uniqueidentifier,
    @TARGETID uniqueidentifier,
    @CONTACTCRITERIA bit,
    @CHANGEAGENTID uniqueidentifier,
    @DATECHANGED datetime
)
as
begin
    set nocount on;

    /* BEGIN ERROR HANDLING */

    declare @SOURCEGROUPTYPE uniqueidentifier;
    declare @TARGETGROUPTYPE uniqueidentifier;
    select @SOURCEGROUPTYPE = GROUPTYPEID from dbo.GROUPDATA where ID = @SOURCEID;
    select @TARGETGROUPTYPE = GROUPTYPEID from dbo.GROUPDATA where ID = @TARGETID;

    -- GROUPTYPEID is null when the group is a household

    if (@SOURCEGROUPTYPE is null and @TARGETGROUPTYPE is not null)
       or (@SOURCEGROUPTYPE is not null and @TARGETGROUPTYPE is null)
    begin
        raiserror('These constituent groups were not merged because one is a household and the other is not a household.', 16, 1);
    end

    if (@SOURCEGROUPTYPE <> @TARGETGROUPTYPE)
    begin
        raiserror('These constituent groups were not merged because they have different group types.', 16, 1);
    end

    /* END ERROR HANDLING */

    --Retrieve and save the primary contacts for both the source and target groups before merging


    declare @TARGETPRIMARYCONTACTID uniqueidentifier;
    declare @SOURCEPRIMARYCONTACTID uniqueidentifier;

    select
        @TARGETPRIMARYCONTACTID = MEMBERID
    from
        dbo.GROUPMEMBER
    where
        GROUPID = @TARGETID
        and ISPRIMARY = 1
        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(ID) = 1;

    select
        @SOURCEPRIMARYCONTACTID = MEMBERID
    from
        dbo.GROUPMEMBER
    where
        GROUPID = @SOURCEID
        and ISPRIMARY = 1
        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(ID) = 1;

    --Determine who will be the primary contact after the merge, 0=target, 1=source


    declare @PRIMARYMEMBER uniqueidentifier;

    if @CONTACTCRITERIA = 0
    begin
        set @PRIMARYMEMBER = coalesce(@TARGETPRIMARYCONTACTID, @SOURCEPRIMARYCONTACTID);
    end
    else if @CONTACTCRITERIA = 1
    begin
        set @PRIMARYMEMBER = coalesce(@SOURCEPRIMARYCONTACTID, @TARGETPRIMARYCONTACTID);
    end

    declare @STARTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    --Add all members of the source group to the target group, unless the already exist in the target group

    update
        SOURCEGROUPMEMBER
    set
        SOURCEGROUPMEMBER.ISPRIMARY = 0, -- Set primary to false to avoid primary count check constraint. ISPRIMARY will be updated later.

        SOURCEGROUPMEMBER.GROUPID = @TARGETID,
        SOURCEGROUPMEMBER.CHANGEDBYID = @CHANGEAGENTID,
        SOURCEGROUPMEMBER.DATECHANGED = @DATECHANGED
    from
        dbo.GROUPMEMBER as SOURCEGROUPMEMBER
        left outer join dbo.GROUPMEMBER as TARGETGROUPMEMBER on (TARGETGROUPMEMBER.GROUPID = @TARGETID and SOURCEGROUPMEMBER.MEMBERID = TARGETGROUPMEMBER.MEMBERID)
    where
        SOURCEGROUPMEMBER.GROUPID = @SOURCEID
        and TARGETGROUPMEMBER.ID is null
        and SOURCEGROUPMEMBER.MEMBERID <> @TARGETID;

    --Update the DATEFROM and DATETO fields for any members that were merged into the target


    declare @GROUPSTART datetime;
    select @GROUPSTART = STARTDATE from dbo.GROUPDATA where GROUPDATA.ID = @TARGETID;

    update
        TARGETGROUPMEMBERDATERANGE
    set
        -- Do not keep a start date if one does not have a start date, otherwise keep oldest start date. This date will be fixed in the next update statement if it precedes the group's inception date.

        TARGETGROUPMEMBERDATERANGE.DATEFROM = case when (TARGETGROUPMEMBERDATERANGE.DATEFROM is null or SOURCEGROUPMEMBERDATERANGE.DATEFROM is null) then null
                                                when (TARGETGROUPMEMBERDATERANGE.DATEFROM < SOURCEGROUPMEMBERDATERANGE.DATEFROM) then TARGETGROUPMEMBERDATERANGE.DATEFROM
                                                else SOURCEGROUPMEMBERDATERANGE.DATEFROM
                                                end,
        -- Do not keep an end date if one does not have an end date, otherwise most recent end date.

        TARGETGROUPMEMBERDATERANGE.DATETO = case when (TARGETGROUPMEMBERDATERANGE.DATETO is null or SOURCEGROUPMEMBERDATERANGE.DATETO is null) then null
                                            when (TARGETGROUPMEMBERDATERANGE.DATETO < SOURCEGROUPMEMBERDATERANGE.DATETO) then SOURCEGROUPMEMBERDATERANGE.DATETO
                                            else TARGETGROUPMEMBERDATERANGE.DATETO
                                            end,
        TARGETGROUPMEMBERDATERANGE.COMMENTS = case when (nullif(TARGETGROUPMEMBERDATERANGE.COMMENTS, '') is null) then SOURCEGROUPMEMBERDATERANGE.COMMENTS
                                                else TARGETGROUPMEMBERDATERANGE.COMMENTS
                                                end,
        TARGETGROUPMEMBERDATERANGE.CHANGEDBYID = @CHANGEAGENTID,
        TARGETGROUPMEMBERDATERANGE.DATECHANGED = @DATECHANGED
    from
        dbo.GROUPMEMBERDATERANGE as TARGETGROUPMEMBERDATERANGE
        inner join dbo.GROUPMEMBER as TARGETGROUPMEMBER on TARGETGROUPMEMBERDATERANGE.GROUPMEMBERID = TARGETGROUPMEMBER.ID
        inner join dbo.GROUPMEMBER as SOURCEGROUPMEMBER on SOURCEGROUPMEMBER.MEMBERID = TARGETGROUPMEMBER.MEMBERID
        inner join dbo.GROUPMEMBERDATERANGE as SOURCEGROUPMEMBERDATERANGE on SOURCEGROUPMEMBERDATERANGE.GROUPMEMBERID = SOURCEGROUPMEMBER.ID
    where
        TARGETGROUPMEMBER.GROUPID = @TARGETID
        and SOURCEGROUPMEMBER.GROUPID = @SOURCEID;

    update 
        dbo.GROUPMEMBERDATERANGE
    set
        -- Set start date to group's start date if it was before group was formed

        GROUPMEMBERDATERANGE.DATEFROM = case when DATEFROM < @GROUPSTART then @GROUPSTART else DATEFROM end,
        -- Set end date to group's start date if it was before group was formed

        GROUPMEMBERDATERANGE.DATETO = case when DATETO < @GROUPSTART then @GROUPSTART else DATETO end,
        GROUPMEMBERDATERANGE.CHANGEDBYID = @CHANGEAGENTID,
        GROUPMEMBERDATERANGE.DATECHANGED = @DATECHANGED
    from
        dbo.GROUPMEMBER
    where
        GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
        and GROUPMEMBER.GROUPID = @TARGETID;

    --Update any group member role dates that may have conflicting dates with the group.

    --Households have no roles but this shouldn't matter since there will be no entries in the table.

    exec dbo.USP_GROUPMEMBERROLE_UPDATEDATESFORGROUP @TARGETID, @CHANGEAGENTID;

    update
        dbo.GROUPMEMBER
    set
        ISPRIMARY = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @DATECHANGED
    where
        GROUPID = @TARGETID
        and ISPRIMARY = 1;

    --Set primary contact

    update
        dbo.GROUPMEMBER
    set
        ISPRIMARY = 1,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @DATECHANGED
    where
        GROUPID = @TARGETID
        and MEMBERID = @PRIMARYMEMBER;

    --Get rid of any members of the source group that could not be merged into the target group

    declare @GROUPGROUPMEMBERIDS table (ID uniqueidentifier);

    insert into
        @GROUPGROUPMEMBERIDS (ID)
    select
        ID
    from
        dbo.GROUPMEMBER
    where
        GROUPID = @SOURCEID;

    delete
        dbo.GROUPMEMBERROLE
    where
        GROUPMEMBERID in (select ID from @GROUPGROUPMEMBERIDS);

    delete
        dbo.GROUPMEMBERDATERANGE
    where
        GROUPMEMBERID in (select ID from @GROUPGROUPMEMBERIDS);

    delete
        dbo.GROUPMEMBER
    where
        GROUPID = @SOURCEID;
end