Sync Spouse Attributes

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
BEGIN
  DROP TABLE #temptable;
END;

IF OBJECT_ID('tempdb..#update_table') IS NOT NULL
BEGIN
  DROP TABLE #update_table;
END;

DECLARE
  @AttributeIDList VARCHAR(1000) = --'1,2';

DECLARE
  @AdultRole INT = 29;

DECLARE
  @UpdateDateTime DATETIME = GETDATE();

DECLARE
  @OrganizationID INT = 1;

DECLARE
  @HistoryTypeLUID INT = 9457; -- Person Attribute Updates

SELECT CPA.person_id,
       person2 = CPA2.person_id,
       spouce_id = CFM2.person_id,
       CFM.family_id,
       CPA.attribute_id,
       Attb_Mod_Date = CPA.date_modified,
       CFM2.person_id AS Spouce_per_id,
       Attb_val_1 = CPA.attribute_value,
       Attb_val_2 = CPA2.attribute_value,
       Update_Person_ID = CASE WHEN CPA2.date_modified IS NULL
                                    OR CPA.date_modified > CPA2.date_modified THEN CPA2.person_id
                               ELSE CPA.person_id
                          END,
       Insert_Person_ID = CASE WHEN CPA2.date_modified IS NULL THEN CFM2.person_id
                               ELSE NULL
                          END,
       new_int_value = CASE WHEN CPA2.date_modified IS NULL
                                 OR CPA.date_modified > CPA2.date_modified THEN CPA.int_value
                            ELSE CPA2.int_value
                       END,
       new_decimal_value = CASE WHEN CPA2.date_modified IS NULL
                                     OR CPA.date_modified > CPA2.date_modified THEN CPA.decimal_value
                                ELSE CPA2.decimal_value
                           END,
       new_varchar_value = CASE WHEN CPA2.date_modified IS NULL
                                     OR CPA.date_modified > CPA2.date_modified THEN CPA.varchar_value
                                ELSE CPA2.varchar_value
                           END,
       new_datetime_value = CASE WHEN CPA2.date_modified IS NULL
                                      OR CPA.date_modified > CPA2.date_modified THEN CPA.datetime_value
                                 ELSE CPA2.datetime_value
                            END,
       new_attribute_value = CASE WHEN CPA2.date_modified IS NULL
                                       OR CPA.date_modified > CPA2.date_modified THEN CPA.attribute_value
                                  ELSE CPA2.attribute_value
                             END,
       CPA.attribute_type,
       attb_type_2 = CPA2.attribute_type,
       CPA.attribute_name
INTO #temptable
FROM   cust_CCIW_v_person_attribute CPA
       JOIN core_family_member CFM ON CFM.person_id = CPA.person_id
       JOIN core_family_member CFM2 ON CFM.family_id = CFM2.family_id
       LEFT JOIN cust_CCIW_v_person_attribute CPA2 ON CPA2.person_id = CFM2.person_id
                                                      AND CPA2.attribute_id = CPA.attribute_id
WHERE  CPA.attribute_id IN
(
    SELECT *
    FROM   dbo.fnSplit(@AttributeIDList)
)
       AND CFM2.role_luid = @AdultRole
       AND CFM.role_luid = @AdultRole
       AND CFM.person_id != CFM2.person_id
       AND -- Do update where the values are null, != comparison will fail for null values
       ( CPA.attribute_value != CPA2.attribute_value
         OR CPA.attribute_value IS NULL
         OR CPA2.attribute_value IS NULL
                                        )
       AND -- Don't update attributes where both values are null
       ( CPA.attribute_value IS NOT NULL
         OR CPA2.attribute_value IS NOT NULL
                                            )
       AND CFM.family_id NOT IN -- Don't include families that have 3+ Adults, this can lead to a scenario where the script tries to insert duplicate primary keys
(
    SELECT f.family_id
    FROM   core_family_member fm
           JOIN core_family f ON f.family_id = fm.family_id
    GROUP BY f.family_id,
             role_luid,
             f.family_name
    HAVING role_luid = @AdultRole
           AND COUNT(*) > 2
);


SELECT *
FROM   #temptable t
ORDER BY person_id;

SELECT DISTINCT
       Update_Person_ID,
       Insert_Person_ID,
       attribute_id,
       new_int_value,
       new_decimal_value,
       new_varchar_value,
       new_datetime_value,
       new_attribute_value,
       attribute_type,
       attribute_name
INTO #update_table
FROM   #temptable;

BEGIN TRAN Spouse_Attb_Update;
-- Do Updates

IF
(
    SELECT COUNT(*)
    FROM   #update_table
    WHERE  Update_Person_ID IS NOT NULL
) > 0
BEGIN
  UPDATE CPA
    SET  int_value = CASE WHEN T.attribute_type IN(0, 3, 4, 9) THEN T.new_int_value
                          ELSE int_value
                     END, varchar_value = CASE WHEN T.attribute_type IN(1, 7, 8) THEN T.new_varchar_value
                                               ELSE varchar_value
                                          END, datetime_value = CASE WHEN T.attribute_type IN(2) THEN T.new_datetime_value
                                                                     ELSE datetime_value
                                                                END, decimal_value = CASE WHEN T.attribute_type IN(5, 6) THEN T.new_decimal_value
                                                                                          ELSE decimal_value
                                                                                     END, date_modified = @UpdateDateTime
  FROM   core_person_attribute CPA
         JOIN #update_table T ON CPA.person_id = T.Update_Person_ID
                                 AND CPA.attribute_id = T.attribute_id;
END;
--

SELECT
       Change_Type = CASE WHEN Update_Person_ID IS NULL THEN 'Add New Attribute Value' ELSE 'Change Existing Attribute Value' END,
       P.person_id,
       [Person to Update] =P.first_name + ' ' + P.last_name,
       U.attribute_name,
       U.new_attribute_value
FROM   #update_table U
       JOIN core_person P ON P.person_id = COALESCE(Update_Person_ID, Insert_Person_ID)
ORDER BY Update_Person_ID DESC;

-- Do Inserts

IF
(
    SELECT COUNT(*)
    FROM   #update_table
    WHERE  Insert_Person_ID IS NOT NULL
) > 0

BEGIN
  INSERT INTO                      core_person_attribute(person_id,
                                                         attribute_id,
                                                         int_value,
                                                         varchar_value,
                                                         datetime_value,
                                                         decimal_value,
                                                         date_created,
                                                         date_modified,
                                                         created_by,
                                                         modified_by,
                                                         organization_id)
         SELECT U.Insert_Person_ID,
                U.attribute_id,
                CASE WHEN U.attribute_type IN(0, 3, 4, 9) THEN U.new_int_value
                     ELSE NULL
                END,
                CASE WHEN U.attribute_type IN(1, 7, 8) THEN U.new_varchar_value
                     ELSE NULL
                END,
                CASE WHEN U.attribute_type IN(2) THEN U.new_datetime_value
                     ELSE NULL
                END,
                CASE WHEN U.attribute_type IN(5, 6) THEN U.new_decimal_value
                     ELSE NULL
                END,
                @UpdateDateTime,
                @UpdateDateTime,
                'Attribute Sync Script',
                'Attribute Sync Script',
                @OrganizationID
         FROM   #update_table U
         WHERE  U.Insert_Person_ID IS NOT NULL;
END;


--Add history

IF
(
    SELECT COUNT(*)
    FROM   #update_table
) > 0
BEGIN
  INSERT INTO                    core_person_history(date_created,
                                                     date_modified,
                                                     created_by,
                                                     modified_by,
                                                     person_id,
                                                     history_type_luid,
                                                     history_qualifier_id,
                                                     system_history,
                                                     history,
                                                     organization_id,
                                                     display_flag,
                                                     display_expiration,
                                                     private_flag)
         SELECT @UpdateDateTime,
                @UpdateDateTime,
                'Attribute Sync Script',
                'Attribute Sync Script',
                COALESCE(U.Update_Person_ID, U.Insert_Person_ID),
                @HistoryTypeLUID,
                U.attribute_id,
                'True',
                CASE WHEN U.Update_Person_ID IS NOT NULL THEN 'Updated ' + attribute_name + ' via Attribute Sync Script to ' + new_attribute_value
                     ELSE 'Attribute ' + attribute_name + ' created via Attribute Sync Script'
                END,
                @OrganizationID,
                'false',
                0,
                'False'
         FROM   #update_table U;
END;
--

-- Change to COMMITT TRAN when you are ready to commit changes
ROLLBACK TRAN Spouse_Attb_Update;