cust_CCIW_copy_attributes_to_children

CREATE PROCEDURE cust_CCIW_copy_attributes_to_children
  @AttributeIDList VARCHAR(1000)
AS

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
  @AdultRole INT = 29;
DECLARE
  @ChildRole INT = 31;

DECLARE
  @UpdateDateTime DATETIME = GETDATE();

DECLARE
  @OrganizationID INT = 1;

DECLARE
  @HistoryTypeLUID INT = 9457;


SELECT CPA.person_id,
       person2 = CPA2.person_id,
       child_id = CFM2.person_id,
       CFM.family_id,
       CPA.attribute_id,
       Attb_Mod_Date = CPA.date_modified,
       CFM2.person_id AS Child_per_id,
       Attb_val_1 = CPA.attribute_value,
       Update_Person_ID = CASE WHEN CPA2.date_modified IS NULL
                                    OR CPA.date_modified > CPA2.date_modified THEN CPA2.person_id
                          END,
       Insert_Person_ID = CASE WHEN CPA2.date_modified IS NULL THEN CFM2.person_id
                               ELSE NULL
                          END,
       new_int_value =  CPA.int_value,
       new_decimal_value = CPA.decimal_value,
       new_varchar_value = CPA.varchar_value,
       new_datetime_value = CPA.datetime_value,
       new_attribute_value = CPA.attribute_value,
       CPA.attribute_type,
       CPA.attribute_name,
       CPA.date_modified,
       [date_modified_2] = CPA2.date_modified
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
       INNER JOIN cust_CCIW_v_head H ON H.person_id = CPA.person_id
       INNER JOIN cust_CCIW_v_person_attribute M on M.person_id = H.person_id and M.attribute_id = 244 --Head of House Missionary Status
WHERE
M.int_value in (11570, 11571, 11586) --Career, Midterm, Missions Associate (Prayer Only)
AND CPA.attribute_id IN
(
    SELECT *
    FROM   dbo.fnSplit(@AttributeIDList)
)
       AND CFM2.role_luid = @ChildRole
       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 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 Child_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,
       U.attribute_id
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 Copy Script',
                'Attribute Copy 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 Copy Script',
                'Attribute Copy 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 Copy Script to ' + new_attribute_value
                     ELSE 'Attribute ' + attribute_name + ' created via Attribute Copy Script'
                END,
                @OrganizationID,
                'false',
                0,
                'False'
         FROM   #update_table U;
END;
--

COMMIT TRAN Child_Attb_Update;