USP_MERGETASK_CONSTITUENTFOUNDATION

create procedure dbo.USP_MERGETASK_CONSTITUENTFOUNDATION (
    @SOURCEID uniqueidentifier
    ,@TARGETID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier
    ,@TYPECRITERIA int = 0
    ,@CONTACTCRITERIA int = 0
    )
as
set nocount on;

declare @CHANGEDATE datetime = getdate();

if exists (select GRANTOR.ID from dbo.GRANTOR where GRANTOR.ID = @TARGETID)
begin

    if exists (
        select T.TITLE
        from dbo.GRANTS S
        cross join dbo.GRANTS T
        where S.GRANTORID = @SOURCEID
        and T.GRANTORID = @TARGETID
        and S.TITLE = T.TITLE)
        raiserror ('These constituents were not merged because they have grant programs with the same name.  Please resolve this conflict and then run the merge again.',16,1);

    update dbo.GRANTS
    set GRANTORID = @TARGETID
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CHANGEDATE
    where GRANTORID = @SOURCEID;

    update dbo.GRANTOR
    set GRANTORTYPECODEID = case when @TYPECRITERIA = 1 then
            (select S.GRANTORTYPECODEID
            from dbo.GRANTOR S
            where S.ID = @SOURCEID) else
            (select T.GRANTORTYPECODEID
            from dbo.GRANTOR T
            where T.ID = @TARGETID) end
        ,CONTACTID = case when @CONTACTCRITERIA = 1 then
            (
                coalesce(
                    (-- if source and target have same relationship types with some individual and it's the source's contact then the source's duplicate relationship information is deleted later on and we must fall back to target's relationship even though they specified source's contact

                        select TARGET_ORG_RELATIONSHIP.ID from GRANTOR SG
                        inner join RELATIONSHIP SOURCE_ORG_RELATIONSHIP on SOURCE_ORG_RELATIONSHIP.ID = SG.CONTACTID
                        inner join RELATIONSHIP SOURCE_CONTACT_RELATIONSHIP on SOURCE_ORG_RELATIONSHIP.RECIPROCALCONSTITUENTID = SOURCE_CONTACT_RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                        inner join RELATIONSHIP TARGET_ORG_RELATIONSHIP on TARGET_ORG_RELATIONSHIP.RECIPROCALCONSTITUENTID = SOURCE_ORG_RELATIONSHIP.RECIPROCALCONSTITUENTID
                        inner join RELATIONSHIP TARGET_INDIVIDUAL_RELATIONSHIP on TARGET_ORG_RELATIONSHIP.RECIPROCALCONSTITUENTID = TARGET_INDIVIDUAL_RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                        where SG.ID = @SOURCEID 
                        and TARGET_ORG_RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @TARGETID
                        and SOURCE_ORG_RELATIONSHIP.RELATIONSHIPCONSTITUENTID = SOURCE_CONTACT_RELATIONSHIP.RECIPROCALCONSTITUENTID
                        and TARGET_ORG_RELATIONSHIP.RELATIONSHIPCONSTITUENTID = TARGET_INDIVIDUAL_RELATIONSHIP.RECIPROCALCONSTITUENTID
                        and SOURCE_ORG_RELATIONSHIP.RECIPROCALTYPECODEID = TARGET_ORG_RELATIONSHIP.RECIPROCALTYPECODEID
                        and SOURCE_CONTACT_RELATIONSHIP.RECIPROCALTYPECODEID = TARGET_INDIVIDUAL_RELATIONSHIP.RECIPROCALTYPECODEID
                    )
                ,
                    (
                        select S.CONTACTID
                        from dbo.GRANTOR S
                        where S.ID = @SOURCEID
                    )
                )
            ) else
            (select T.CONTACTID
            from dbo.GRANTOR T
            where T.ID = @TARGETID) end
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CHANGEDATE
    where ID = @TARGETID;

    exec USP_GRANTOR_DELETE @ID=@SOURCEID,@CHANGEAGENTID=@CHANGEAGENTID;
end
else
begin
    insert into dbo.GRANTOR (
        ID
        ,GRANTORTYPECODEID
        ,CONTACTID
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
        )
    select @TARGETID
        ,GRANTORTYPECODEID
        ,CONTACTID
        ,@CHANGEAGENTID ADDEDBYID
        ,@CHANGEAGENTID CHANGEDBYID
        ,@CHANGEDATE DATEADDED
        ,@CHANGEDATE DATECHANGED
    from dbo.GRANTOR
    where GRANTOR.ID = @SOURCEID;

    update dbo.GRANTS
    set GRANTORID = @TARGETID
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CHANGEDATE
    where GRANTORID = @SOURCEID;

    exec USP_GRANTOR_DELETE @ID=@SOURCEID,@CHANGEAGENTID=@CHANGEAGENTID;
end

return 0;