USP_MODELINGANDPROPENSITYDELIVERY_SYNCHRONIZE

CREATE procedure dbo.USP_MODELINGANDPROPENSITYDELIVERY_SYNCHRONIZE
(
    @CONSTITUENTID uniqueidentifier,
    @ANALYTICSPROJECTID uniqueidentifier,
    @ANNUALGIFTLIKELIHOOD smallint,
    @ANNUITYLIKELIHOOD smallint,
    @BEQUESTLIKELIHOOD smallint,
    @CRTLIKELIHOOD smallint,
    @MAJORGIVINGLIKELIHOOD smallint,
    @MEMBERSHIPLIKELIHOOD smallint,
    @ONLINEGIVINGLIKELIHOOD smallint,
    @PATIENTRESPONSELIKELIHOOD smallint,
    @PLANNEDGIFTLIKELIHOOD smallint,
    @SUGGESTEDMEMBERSHIPLEVELID uniqueidentifier,
    @NCOAMOVEDINDICATOR bit,
    @TARGETGIFTRANGEID uniqueidentifier,
    @WEALTHESTIMATORRATINGID uniqueidentifier
    --@WPSCREENINGINDICATOR is no longer used

)
as
begin

    if @ANALYTICSPROJECTID is not null and @ANALYTICSPROJECTID <> N'00000000-0000-0000-0000-000000000000' begin
        with
        -- Target all of the existing join rows for this constituent and delivery.

        EXISTINGSCORECATEGORYLINK as
        (
            select
                MODELINGANDPROPENSITYDELIVERY.MODELINGANDPROPENSITYID,
                MODELINGANDPROPENSITYDELIVERY.ANALYTICSMODELID,
                MODELINGANDPROPENSITYDELIVERY.ANALYTICSPROJECTID
            from
                dbo.MODELINGANDPROPENSITYDELIVERY
            where
                MODELINGANDPROPENSITYDELIVERY.MODELINGANDPROPENSITYID = @CONSTITUENTID
                and MODELINGANDPROPENSITYDELIVERY.ANALYTICSPROJECTID = @ANALYTICSPROJECTID
        ),
        -- In the source, include a row for each score category that has a value in this import.

        UPDATEDSCORECATEGORY as
        (
            select cast('3CF4EC5A-63FE-42EA-B70B-7F6CAAE286F1' as uniqueidentifier) as ANALYTICSMODELID where @ANNUALGIFTLIKELIHOOD <> -1
            union all select '3788BA7E-E7DC-4A6E-93C9-F2245B26A13B' where @ANNUITYLIKELIHOOD <> -1
            union all select 'DEF85E09-E51A-4EED-B8AF-0763272A5593' where @BEQUESTLIKELIHOOD <> -1
            union all select 'FEB5BE0E-3B4F-45E1-A3E2-26F4372DEE96' where @CRTLIKELIHOOD <> -1
            union all select 'CE8C3F19-6B9A-4852-AB3C-3765F48578F7' where @MAJORGIVINGLIKELIHOOD <> -1
            union all select 'B2C12F11-3C63-4077-ADD0-C976A905B07C' where @MEMBERSHIPLIKELIHOOD <> -1
            union all select '06776DBB-E33A-45A2-B1DE-972B626169DC' where @ONLINEGIVINGLIKELIHOOD <> - 1
            union all select '64111AFB-850D-4407-B403-948F0CD8BE25' where @PATIENTRESPONSELIKELIHOOD <> -1
            union all select '2018E9DB-F837-4CC0-BFC9-E72E5E63ACB5' where @PLANNEDGIFTLIKELIHOOD <> -1
            union all select 'A4BACAA3-8EE4-4476-B359-428E582C42AE' where @SUGGESTEDMEMBERSHIPLEVELID is not null
            union all select 'CA48F28A-DBE6-40E5-907A-D29D40F9B588' where @NCOAMOVEDINDICATOR is not null
            union all select 'CA0A6B61-2201-482F-AE8D-C6AE6852C318' where @TARGETGIFTRANGEID is not null
            --union all select '50748064-56D0-4BA1-811D-6F0DA2EF3586' where len(@WPSCREENINGINDICATOR) > 0

            union all select '4D03C579-7461-42AE-8445-442D510B6151' where @WEALTHESTIMATORRATINGID is not null
        )
        merge into EXISTINGSCORECATEGORYLINK
        using
            UPDATEDSCORECATEGORY on EXISTINGSCORECATEGORYLINK.ANALYTICSMODELID = UPDATEDSCORECATEGORY.ANALYTICSMODELID
        --when matched then

            -- Do nothing when matched, because the row already exists. It was probably added by a previous

            -- run of the import with this same file and delivery.

        when not matched by target then
            -- Add join rows for the score categories that were updated. 

            insert
            (
                MODELINGANDPROPENSITYID,
                ANALYTICSMODELID,
                ANALYTICSPROJECTID
            )
            values
            (
                @CONSTITUENTID,
                UPDATEDSCORECATEGORY.ANALYTICSMODELID,
                @ANALYTICSPROJECTID
            )
        when not matched by source then
            -- Remove existing join rows for this constituent and delivery that are for a score category that is not updated by

            -- this import. It was probably added by a previous run of the import linked to an incorrect delivery.

            delete;
    end

end