USP_MERGETASK_MATCHINGGIFTCONDITIONS

CREATE procedure dbo.USP_MERGETASK_MATCHINGGIFTCONDITIONS
(
    @SOURCEID uniqueidentifier,
    @TARGETID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @PRESERVEORGANIZATIONDETAILS bit
)
as
begin
    set nocount on;

    declare @DATECHANGED datetime = getdate();

    --Move source MG criteria to target if target does not have duplicate condition type (this is prevented in the one-off add MG criteria form)

    --MATCHINGGIFTCONDITIONRELATIONSHIP only has a FK to MATCHINGGIFTCONDITION so doesn't need to be merged

    update dbo.MATCHINGGIFTCONDITION
    set
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @DATECHANGED,
        ORGANIZATIONID = @TARGETID
    where
        ORGANIZATIONID = @SOURCEID
        and not exists
        (
            select
                1
            from
                dbo.MATCHINGGIFTCONDITION as TARGETMGC
            where
                TARGETMGC.ORGANIZATIONID = @TARGETID
                and TARGETMGC.MATCHINGGIFTCONDITIONTYPECODEID = MATCHINGGIFTCONDITION.MATCHINGGIFTCONDITIONTYPECODEID
        )
        and not exists
        (
            select
                1
            from
                dbo.MATCHINGGIFTCONDITIONRELATIONSHIP
            where
                MATCHINGGIFTCONDITIONRELATIONSHIP.MATCHINGGIFTCONDITIONID = MATCHINGGIFTCONDITION.ID
                and dbo.UFN_MATCHINGGIFTCONDITIONRELATIONSHIP_UNIQUERELATIONSHIPFORORGANIZATION_2(MATCHINGGIFTCONDITION.ID, MATCHINGGIFTCONDITIONRELATIONSHIP.RELATIONSHIPTYPECODEID,
                    MATCHINGGIFTCONDITIONRELATIONSHIP.JOBSCHEDULECODEID, MATCHINGGIFTCONDITIONRELATIONSHIP.CAREERLEVELCODEID, @TARGETID, MATCHINGGIFTCONDITION.REVENUETYPECODE) = 0
        )

    --There were duplicate condition types

    if exists (select top 1 1 from dbo.MATCHINGGIFTCONDITION where ORGANIZATIONID = @SOURCEID)
    begin

        --If the matching gift conditions match exactly, update any MGC revenue and put the target condition ID on them.

        declare @DUPLICATECONDITIONS table (SOURCEID uniqueidentifier, TARGETID uniqueidentifier);
        insert into @DUPLICATECONDITIONS (SOURCEID, TARGETID)
        select
            SOURCEMGC.ID,
            TARGETMGC.ID
        from
            dbo.MATCHINGGIFTCONDITION as SOURCEMGC
            inner join dbo.MATCHINGGIFTCONDITION as TARGETMGC on TARGETMGC.MATCHINGGIFTCONDITIONTYPECODEID = SOURCEMGC.MATCHINGGIFTCONDITIONTYPECODEID
        where
            SOURCEMGC.ORGANIZATIONID = @SOURCEID
            and TARGETMGC.ORGANIZATIONID = @TARGETID
            and SOURCEMGC.MATCHINGFACTOR = TARGETMGC.MATCHINGFACTOR
            and SOURCEMGC.MAXMATCHANNUAL = TARGETMGC.MAXMATCHANNUAL
            and SOURCEMGC.MAXMATCHPERGIFT = TARGETMGC.MAXMATCHPERGIFT
            and SOURCEMGC.MAXMATCHTOTAL = TARGETMGC.MAXMATCHTOTAL
            and SOURCEMGC.MINMATCHPERGIFT = TARGETMGC.MINMATCHPERGIFT
            and SOURCEMGC.MATCHTYPECODE = TARGETMGC.MATCHTYPECODE
            and SOURCEMGC.REVENUETYPECODE = TARGETMGC.REVENUETYPECODE
            and SOURCEMGC.BASECURRENCYID = TARGETMGC.BASECURRENCYID
            and SOURCEMGC.ORGANIZATIONMAXMATCHANNUAL = TARGETMGC.ORGANIZATIONMAXMATCHANNUAL
            and SOURCEMGC.ORGANIZATIONMAXMATCHPERGIFT = TARGETMGC.ORGANIZATIONMAXMATCHPERGIFT
            and SOURCEMGC.ORGANIZATIONMAXMATCHTOTAL = TARGETMGC.ORGANIZATIONMAXMATCHTOTAL
            and SOURCEMGC.ORGANIZATIONMINMATCHPERGIFT = TARGETMGC.ORGANIZATIONMINMATCHPERGIFT
            and
                (
                    SOURCEMGC.ORGANIZATIONEXCHANGERATEID = TARGETMGC.ORGANIZATIONEXCHANGERATEID
                    or
                    (
                        SOURCEMGC.ORGANIZATIONEXCHANGERATEID is null
                        and TARGETMGC.ORGANIZATIONEXCHANGERATEID is null
                    )
                )
            --same number of relationships

            and (select count(*) from dbo.MATCHINGGIFTCONDITIONRELATIONSHIP where MATCHINGGIFTCONDITIONID = SOURCEMGC.ID) = (select count(*) from dbo.MATCHINGGIFTCONDITIONRELATIONSHIP where MATCHINGGIFTCONDITIONID = TARGETMGC.ID)
            --relationships match exactly

            and
            (
                select
                    count(*)
                from
                    dbo.MATCHINGGIFTCONDITIONRELATIONSHIP as SOURCERELATIONSHIP
                    inner join dbo.MATCHINGGIFTCONDITIONRELATIONSHIP as TARGETRELATIONSHIP on
                        TARGETRELATIONSHIP.RELATIONSHIPTYPECODEID = SOURCERELATIONSHIP.RELATIONSHIPTYPECODEID
                        and TARGETRELATIONSHIP.JOBSCHEDULECODEID = SOURCERELATIONSHIP.JOBSCHEDULECODEID
                        and TARGETRELATIONSHIP.CAREERLEVELCODEID = SOURCERELATIONSHIP.CAREERLEVELCODEID
                where
                    SOURCERELATIONSHIP.MATCHINGGIFTCONDITIONID = SOURCEMGC.ID
                    and TARGETRELATIONSHIP.MATCHINGGIFTCONDITIONID = TARGETMGC.ID
            ) = (select count(*) from dbo.MATCHINGGIFTCONDITIONRELATIONSHIP where MATCHINGGIFTCONDITIONID = TARGETMGC.ID)

        -- update NOTES on target if they are blank in target and not blank in source

        if @PRESERVEORGANIZATIONDETAILS = 1
        begin
            update TARGETMGC
            set
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @DATECHANGED,
                TARGETMGC.NOTES = SOURCEMGC.NOTES
            from
                dbo.MATCHINGGIFTCONDITION as TARGETMGC
                inner join @DUPLICATECONDITIONS as DUPES on DUPES.TARGETID = TARGETMGC.ID
                inner join dbo.MATCHINGGIFTCONDITION as SOURCEMGC on DUPES.SOURCEID = SOURCEMGC.ID
            where
                TARGETMGC.NOTES is not null and TARGETMGC.NOTES = N''
                and SOURCEMGC.NOTES is not null and SOURCEMGC.NOTES <> N'';
        end

        update dbo.REVENUEMATCHINGGIFT
        set
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @DATECHANGED,
            MATCHINGGIFTCONDITIONID = DUPES.TARGETID
        from
            dbo.REVENUEMATCHINGGIFT
            inner join @DUPLICATECONDITIONS as DUPES on DUPES.SOURCEID = REVENUEMATCHINGGIFT.MATCHINGGIFTCONDITIONID;

        update dbo.BATCHREVENUE
        set
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @DATECHANGED,
            MGCONDITIONID = DUPES.TARGETID
        from
            dbo.BATCHREVENUE
            inner join @DUPLICATECONDITIONS as DUPES on DUPES.SOURCEID = BATCHREVENUE.MGCONDITIONID;

        update dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS
        set
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @DATECHANGED,
            MATCHINGGIFTCONDITIONID = DUPES.TARGETID
        from
            dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS
            inner join @DUPLICATECONDITIONS as DUPES on DUPES.SOURCEID = BATCHREVENUEENHANCEDMATCHINGGIFTS.MATCHINGGIFTCONDITIONID;

        if exists
        (
            select top 1
                1
            from
                dbo.MATCHINGGIFTCONDITION
                left join dbo.REVENUEMATCHINGGIFT on REVENUEMATCHINGGIFT.MATCHINGGIFTCONDITIONID = MATCHINGGIFTCONDITION.ID
                left join dbo.BATCHREVENUE on BATCHREVENUE.MGCONDITIONID = MATCHINGGIFTCONDITION.ID
                left join dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS on BATCHREVENUEENHANCEDMATCHINGGIFTS.MATCHINGGIFTCONDITIONID = MATCHINGGIFTCONDITION.ID
            where
                MATCHINGGIFTCONDITION.ORGANIZATIONID = @SOURCEID
                and
                (
                    REVENUEMATCHINGGIFT.ID is not null
                    or BATCHREVENUE.ID is not null
                    or BATCHREVENUEENHANCEDMATCHINGGIFTS.ID is not null
                )
        )
        begin
            --we have already moved all MGC with duplicate criteria (if any existed)

            --other MGC created using this criteria exist, and the criteria is not exactly the same

            --we don't know what to do here, throw an error

            raiserror('Cannot merge organization information because there are duplicate matching gift conditions.',13,1);
        end

        --no MGC revenue associated, okay to delete criteria


        declare @contextCache varbinary(128);
        --cache current context information

        set @contextCache = CONTEXT_INFO();
        --set CONTEXT_INFO to @CHANGEAGENTID

        set CONTEXT_INFO @CHANGEAGENTID;

        delete dbo.MATCHINGGIFTCONDITION
        where ORGANIZATIONID = @SOURCEID;

        --reset CONTEXT_INFO to previous value

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;

    end
end