cust_CCIW_sp_auto_adults

USE [ArenaDB]
GO

/****** Object:  StoredProcedure [dbo].[cust_CCIW_sp_auto_adults]    Script Date: 2/17/2022 2:07:33 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Modified By:     Tony Visconti
-- Original Author: anicoletti?, Bob Brown
-- Create date: 4/29/19
-- Description: Move Individuals with Child Family Role and minimum specifed Age to Adult Role within their own family
-- 1.1
--   Fixed missing space aftere word via in history note
--   Added check to allow for parent child relationship if child's lastname contains parent's last name

-- =============================================
CREATE PROCEDURE [dbo].[cust_CCIW_sp_auto_adults] @PersonIDList VARCHAR(8000) = ''
   , @IgnoreDOB BIT = 0
   , @MinimumAge INT = -1
   , @ParentRelationship INT = -1 -- Parent
   , @ChildRelationship INT = -1
   , @ProfileID INT =  -1
   , @ExcludeStatusIDList VARCHAR(8000) = '10419,10434,10398' --Stars Participant, Former Stars Participant, Deceased
   , @EventRegistrationStatusID INT = 10470 --Event Registrant
   , @Debug BIT = 1 -- Set to 1 to add print statements to messages regarding program flow
   , @OrganizationID INT = 1
AS
IF @MinimumAge = - 1
   SET @MinimumAge = dbo.orgn_funct_organizationSetting(@OrganizationID, 'AutoAdult_MinAge', '-1')

IF @ParentRelationship = - 1
   SET @ParentRelationship = dbo.orgn_funct_organizationSetting(@OrganizationID, 'AutoAdult_ParentRelationship', '-1')

IF @ChildRelationship = - 1
   SET @ChildRelationship = dbo.orgn_funct_organizationSetting(@OrganizationID, 'AutoAdult_ChildRelationship', '-1')

IF @ProfileID = - 1
   SET @ProfileID = dbo.orgn_funct_organizationSetting(@OrganizationID, 'AutoAdult_IgnoreProfile', '-1') --4425 production

IF (
      @MinimumAge <> - 1
      AND @ParentRelationship <> - 1
      AND @ChildRelationship <> - 1
      )
BEGIN
   DECLARE @PersonID INT
   DECLARE @OldFamily INT
   DECLARE @NewFamily INT
   DECLARE @NewAddress INT
   DECLARE @RecordChanges BIT = 1;-- Set to 1 to return a list of changes to the results
   DECLARE @AllChanges TABLE ([Adult Child ID] INT, [Adult Child Name] VARCHAR(100), [Person Updated] VARCHAR(100), [Person's Status] VARCHAR(50), [Change Description] VARCHAR(500), [Changed Value] VARCHAR(500))
   DECLARE @ProcessName VARCHAR(100) = 'Child to Adult Automation';
   DECLARE @AdultChildName VARCHAR(100) = '';
   DECLARE @AdultChildLastName VARCHAR(50) = '';
   DECLARE @AdultChildMemberStatus VARCHAR(100) = '';
   DECLARE @Parent1ID INT;
   DECLARE @Parent1Name VARCHAR(100) = '';
   DECLARE @Parent1LastName VARCHAR(50) = '';
   DECLARE @Parent1MemberStatus VARCHAR(50) = '';
   DECLARE @Parent2ID INT;
   DECLARE @Parent2Name VARCHAR(100) = '';
   DECLARE @Parent2LastName VARCHAR(50) = '';
   DECLARE @Parent2MemberStatus VARCHAR(50) = '';
   DECLARE @TempValue VARCHAR(100) = '';
   DECLARE @AdultCount TINYINT;
   DECLARE @ChildCount TINYINT;

   DECLARE PersonCursor CURSOR READ_ONLY
   FOR
   SELECT P.person_id
   FROM core_person P
   LEFT JOIN core_family_member CFM ON CFM.person_id = P.person_id
   WHERE CFM.role_luid = dbo.core_funct_luid_familyChild(@OrganizationID)
      AND (P.birth_date > '1901' or @IgnoreDOB = 1)
      AND (
         P.birth_date <= (DATEADD(yy, - 1 * @MinimumAge, GETDATE()))
         OR P.date_created <= (DATEADD(yy, - 1 * @MinimumAge, GETDATE()))
         OR @IgnoreDOB = 1
         ) -- Record was created more than x years ago
      AND P.organization_id = @OrganizationID
      AND P.person_id NOT IN (
         SELECT person_id
         FROM core_profile_member
         WHERE profile_id = @ProfileID
         )
      AND (
         @PersonIDList = ''
         OR P.person_id IN (
            SELECT *
            FROM dbo.fnSplit(@PersonIDList)
            )
         )
      AND (
         @ExcludeStatusIDList = ''
         OR P.member_status NOT IN (
            SELECT *
            FROM dbo.fnSplit(@ExcludeStatusIDList)
            )
         )
      AND NOT (
         P.member_status = @EventRegistrationStatusID
         AND P.record_status = 2
         ) -- Ignore pending event registrants, these could be STAR

   OPEN PersonCursor

   FETCH NEXT
   FROM PersonCursor
   INTO @PersonID

   WHILE (@@FETCH_STATUS <> - 1)
   BEGIN
      IF (@@FETCH_STATUS = 0)
      BEGIN
         IF @Debug = 1
            PRINT 'Processsing Adult Child with ID:' + CONVERT(VARCHAR(10), @PersonID)

         SET @Parent1ID = dbo.core_funct_familyHead(@PersonID)
         SET @Parent2ID = dbo.core_funct_spouse(@Parent1ID, @OrganizationID)
         -- These variables are used to document changes
         SET @AdultChildName = (
               SELECT P.first_name + ' ' + P.last_name
               FROM core_person P
               WHERE P.person_id = @PersonID
               )
         SET @AdultChildLastName = (
               SELECT P.last_name
               FROM core_person P
               WHERE P.person_id = @PersonID
               )
         SET @AdultChildMemberStatus = (
               SELECT dbo.cust_CCIW_funct_luid_to_value(P.member_status)
               FROM core_person P
               WHERE P.person_id = @PersonID
               )
         SET @Parent1Name = (
               SELECT P.first_name + ' ' + P.last_name
               FROM core_person P
               WHERE P.person_id = @Parent1ID
               )
         SET @Parent1LastName = (
               SELECT P.last_name
               FROM core_person P
               WHERE P.person_id = @Parent1ID
               )
         SET @Parent1MemberStatus = (
               SELECT dbo.cust_CCIW_funct_luid_to_value(P.member_status)
               FROM core_person P
               WHERE P.person_id = @Parent1ID
               )
         SET @Parent2Name = (
               SELECT P.first_name + ' ' + P.last_name
               FROM core_person P
               WHERE P.person_id = @Parent2ID
               )
         SET @Parent2LastName = (
               SELECT P.last_name
               FROM core_person P
               WHERE P.person_id = @Parent2ID
               )
         SET @Parent2MemberStatus = (
               SELECT dbo.cust_CCIW_funct_luid_to_value(P.member_status)
               FROM core_person P
               WHERE P.person_id = @Parent2ID
               )
         ---
         SET @NewFamily = NULL
         SET @NewAddress = NULL
         SET @OldFamily = (
               SELECT TOP 1 family_id
               FROM core_family_member
               WHERE person_id = @PersonID
               )
         SET @AdultCount = (
               SELECT COUNT(*)
               FROM core_family_member
               WHERE role_luid = dbo.core_funct_luid_familyAdult(@OrganizationID)
                  AND family_id = @OldFamily
               )
         SET @ChildCount = (
               SELECT COUNT(*)
               FROM core_family_member
               WHERE role_luid = dbo.core_funct_luid_familyChild(@OrganizationID)
                  AND family_id = @OldFamily
               )

         IF (
               @AdultCount > 0
               OR @ChildCount > 1
               )
         BEGIN
            IF @Debug = 1
               PRINT 'Creating New Family'

            /**** Create new family ****/
            INSERT INTO core_family (created_by, modified_by, family_name, foreign_key, organization_id)
            SELECT @ProcessName, @ProcessName, P.last_name, NULL, P.organization_id
            FROM core_person P
            WHERE P.person_id = @PersonID

            SET @NewFamily = SCOPE_IDENTITY()

            IF @RecordChanges = 1
               INSERT INTO @AllChanges
               VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Created New Family', CONVERT(VARCHAR(10), @NewFamily))

            /**** If no main/home address tied to Adult Child, add HoH main/home address ****/
            IF @Debug = 1
               PRINT 'Checking if Address needs to be copied from HoH to Child'

            IF NOT EXISTS (
                  SELECT person_id
                  FROM core_person_address
                  WHERE person_id = @PersonID
                     AND address_type_luid = dbo.core_funct_luid_addressMain(@OrganizationID)
                  )
            BEGIN
               /**** If Adult Child has no main/home address, add Head of Household’s (HoH) main/home address if one exists ****/
               IF EXISTS (
                     SELECT person_id
                     FROM core_person_address
                     WHERE person_id = @Parent1ID
                        AND address_type_luid = dbo.core_funct_luid_addressMain(@OrganizationID)
                     )
               BEGIN
                  IF @Debug = 1
                     PRINT 'Copying Main Address from Head of Household to Adult Child'

                  SET @TempValue = (
                        SELECT street_address_1
                        FROM core_address CA
                        LEFT JOIN core_person_address AS CPA ON CPA.address_id = CA.address_id
                        WHERE address_type_luid = dbo.core_funct_luid_addressMain(@OrganizationID)
                           AND person_id = @Parent1ID
                        )

                  IF @RecordChanges = 1
                     INSERT INTO @AllChanges
                     VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Copying Main Address from Head of Household to Adult Child', @TempValue)

                  INSERT INTO core_address (created_by, modified_by, street_address_1, street_address_2, city, STATE, postal_code, Latitude, Longitude, standardize_code, standardize_msg, foreign_key, XAxis, YAxis, ZAxis, date_geocoded, date_standardized, area_id, geocode_service, geocode_status, organization_id)
                  SELECT @ProcessName, @ProcessName, street_address_1, street_address_2, city, STATE, postal_code, Latitude, Longitude, standardize_code, standardize_msg, NULL, XAxis, YAxis, ZAxis, date_geocoded, date_standardized, area_id, geocode_service, geocode_status, CA.organization_id
                  FROM core_address CA
                  LEFT JOIN core_person_address AS CPA ON CPA.address_id = CA.address_id
                  WHERE address_type_luid = dbo.core_funct_luid_addressMain(@OrganizationID)
                     AND person_id = @Parent1ID

                  SET @NewAddress = SCOPE_IDENTITY()

                  /**** Create person:address relationship ****/
                  INSERT INTO core_person_address (person_id, address_id, address_type_luid, primary_address, notes, organization_id)
                  SELECT @PersonID, @NewAddress, dbo.core_funct_luid_addressMain(1), 1, '', 1
               END
            END --Copying Main Address from Head of Household to Adult Child

            IF @Debug = 1
               PRINT 'Checking if phone # needs to be copied from HoH to Adult Child'

            /**** If Adult Child has no home phone or cell phone, add HoH home phone (if no HoH home phone, add HoH cell phone) ****/
            IF NOT EXISTS (
                  SELECT person_id
                  FROM core_person_phone
                  WHERE person_id = @PersonID
                     AND (
                        phone_luid = dbo.core_funct_luid_homePhone(@OrganizationID)
                        OR phone_luid = dbo.core_funct_luid_cellPhone(@OrganizationID)
                        )
                  )
            BEGIN
               IF EXISTS (
                     SELECT person_id
                     FROM core_person_phone
                     WHERE person_id = @Parent1ID
                        AND phone_luid = dbo.core_funct_luid_homePhone(@OrganizationID)
                     )
               BEGIN
                  IF @Debug = 1
                     PRINT 'Copying Home Phone from HoH to Adult Child'

                  SET @TempValue = (
                        SELECT phone_number_stripped
                        FROM core_person_phone
                        WHERE person_id = @Parent1ID
                           AND phone_luid = dbo.core_funct_luid_homePhone(@OrganizationID)
                        )

                  IF @RecordChanges = 1
                     INSERT INTO @AllChanges
                     VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Copying Home Phone from HoH to Adult Child', @TempValue)

                  INSERT INTO core_person_phone (person_id, phone_luid, phone_number, phone_ext, unlisted, phone_number_stripped, organization_id)
                  SELECT @PersonID, phone_luid, phone_number, phone_ext, unlisted, phone_number_stripped, organization_id
                  FROM core_person_phone
                  WHERE person_id = @Parent1ID
                     AND phone_luid = dbo.core_funct_luid_homePhone(@OrganizationID)
               END
               ELSE IF EXISTS (
                     SELECT person_id
                     FROM core_person_phone
                     WHERE person_id = @Parent1ID
                        AND phone_luid = dbo.core_funct_luid_cellPhone(@OrganizationID)
                     )
               BEGIN
                  IF @Debug = 1
                     PRINT 'Copying Cell Phone from HoH to Adult Child'

                  SET @TempValue = (
                        SELECT phone_number_stripped
                        FROM core_person_phone
                        WHERE person_id = @Parent1ID
                           AND phone_luid = dbo.core_funct_luid_cellPhone(@OrganizationID)
                        )

                  IF @RecordChanges = 1
                     INSERT INTO @AllChanges
                     VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Copying Cell Phone from HoH to Adult Child', @TempValue)

                  INSERT INTO core_person_phone (person_id, phone_luid, phone_number, phone_ext, unlisted, phone_number_stripped, organization_id)
                  SELECT @PersonID, phone_luid, phone_number, phone_ext, unlisted, phone_number_stripped, organization_id
                  FROM core_person_phone
                  WHERE person_id = @Parent1ID
                     AND phone_luid = dbo.core_funct_luid_cellPhone(@OrganizationID)
               END
            END

            /**** Add Parent Relationship ****/
            IF @Debug = 1
               PRINT 'Adding HoH Parent Relationship to Adult Child'

            IF CHARINDEX(@AdultChildLastName, @Parent1LastName) > 0 OR CHARINDEX(@Parent1LastName,@AdultChildLastName) > 0 -- check if the child last's name is contained in the parents name
            BEGIN
               IF @RecordChanges = 1
                  INSERT INTO @AllChanges
                  VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Parent Relationship', @Parent1Name + ' is Parent')

               INSERT INTO core_relationship (created_by, modified_by, relationship_type_id, person_id, related_person_id)
               SELECT @ProcessName, @ProcessName, @ParentRelationship, @PersonID, @Parent1ID
            END
            ELSE -- Parent has different last name
            BEGIN
               IF @RecordChanges = 1
                  INSERT INTO @AllChanges
                  VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Skipping Parent 1 Relationship', @Parent1Name + ' has different last name from ' + @AdultChildName)
            END

            /**** Add Second Parent Relationship ****/
            IF @Debug = 1
               PRINT 'Checking if second Parent Relationship is need'

            IF EXISTS (
                  SELECT TOP 1 person_id
                  FROM core_person
                  WHERE person_id = @Parent2ID
                  )
            BEGIN
               IF @Debug = 1
                  PRINT 'Adding second Parent Relationship to Adult Child'

               IF CHARINDEX(@AdultChildLastName, @Parent2LastName) > 0 OR CHARINDEX(@Parent2LastName, @AdultChildLastName ) > 0
               BEGIN
                  IF @RecordChanges = 1
                     INSERT INTO @AllChanges
                     VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Parent Relationship', @Parent2Name + ' is Parent')

                  INSERT INTO core_relationship (created_by, modified_by, relationship_type_id, person_id, related_person_id)
                  SELECT @ProcessName, @ProcessName, @ParentRelationship, @PersonID, @Parent2ID
               END
               ELSE -- Parent has different last name
               BEGIN
                  IF @RecordChanges = 1
                     INSERT INTO @AllChanges
                     VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Skipping Parent 2 Relationship', @Parent2Name + ' has different last name from ' + @AdultChildName)
               END
            END

            /**** Add Adult Child Relationship ****/
            IF @Debug = 1
               PRINT 'Adding Adult Child relationship to HoH'

            IF CHARINDEX(@AdultChildLastName, @Parent1LastName) > 0
            BEGIN
               IF @RecordChanges = 1
                  INSERT INTO @AllChanges
                  VALUES (@PersonID, @AdultChildName, @Parent1Name, @Parent1MemberStatus, 'Adding Adult Child Relationship', @AdultChildName + ' is Adult Child (>18)')

               INSERT INTO core_relationship (created_by, modified_by, relationship_type_id, person_id, related_person_id)
               SELECT @ProcessName, @ProcessName, @ChildRelationship, @Parent1ID, @PersonID
            END
            ELSE
            BEGIN
               INSERT INTO @AllChanges
               VALUES (@PersonID, @AdultChildName, @Parent1Name, @Parent1MemberStatus, 'Skipping Adult Child Relationship', @Parent1Name + ' has different last name from ' + @AdultChildName)
            END

            /**** Add Second Adult Child Relationship ****/
            IF @Debug = 1
               PRINT 'Checking if second Adult Child Relationship is needed'

            IF EXISTS (
                  SELECT TOP 1 person_id
                  FROM core_person
                  WHERE person_id = dbo.core_funct_spouse(dbo.core_funct_familyHead(@PersonID), @OrganizationID)
                  )
            BEGIN
               IF CHARINDEX(@AdultChildLastName, @Parent2LastName) > 0
               BEGIN
                  IF @RecordChanges = 1
                     INSERT INTO @AllChanges
                     VALUES (@PersonID, @AdultChildName, @Parent2Name, @Parent2MemberStatus, 'Adding Adult Child Relationship', @AdultChildName + ' is Adult Child (>18)')

                  INSERT INTO core_relationship (created_by, modified_by, relationship_type_id, person_id, related_person_id)
                  SELECT @ProcessName, @ProcessName, @ChildRelationship, @Parent2ID, @PersonID
               END
               ELSE
               BEGIN
                  INSERT INTO @AllChanges
                  VALUES (@PersonID, @AdultChildName, @Parent2Name, @Parent2MemberStatus, 'Skipping Adult Child Relationship', @Parent2Name + ' has different last name from ' + @AdultChildName)
               END
            END

            /**** Move person to new family (needs to be last, for HoH functions to work in previous statements)****/
            IF @Debug = 1
               PRINT 'Moving person to new family'

            IF @RecordChanges = 1
               INSERT INTO @AllChanges
               VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Adult Child to New Family', CONVERT(VARCHAR(10), @NewFamily))

            IF @Debug = 1
               PRINT 'Adding Person History'

            DECLARE @Parent2Note VARCHAR(200)
            DECLARE @NoAdultsInfo VARCHAR(200) = ''

            IF (@Parent2Name IS NOT NULL)
               SET @Parent2Note = ' & ' + @Parent2Name
            ELSE
               SET @Parent2Note = ''

            IF (@AdultCount = 0)
               SET @NoAdultsInfo = ', no adults were present in family'

            IF @RecordChanges = 1
               INSERT INTO @AllChanges
               VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Note', 'Removed from Family ' + ISNULL(@Parent1Name, '') + @Parent2Note + ' (' + CONVERT(VARCHAR(200), @OldFamily) + ') via ' + @ProcessName + @NoAdultsInfo)

            INSERT INTO core_person_history
            SELECT GETDATE(), GETDATE(), @ProcessName, @ProcessName, @PersonID, 366, - 1, 1, 'Removed from Family ' + ISNULL(@Parent1Name, '') + @Parent2Note + ' (' + CONVERT(VARCHAR(200), @OldFamily) + ') via ' + @ProcessName, @OrganizationID, 0, '1900-01-01', 0
         END -- Changes when @AdultCount > 0 or @ChildCount > 1
               -- Change family role to adult

         IF @Debug = 1
            PRINT 'Changing Childs Family Role to Adult'

         IF @RecordChanges = 1
            INSERT INTO @AllChanges
            VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Changing Childs Family Role to Adult', '')

         IF (@NewFamily IS NULL)
            SET @NewFamily = @OldFamily -- we didn't need to move the person into a new family

         UPDATE core_family_member
         SET family_id = @NewFamily, role_luid = dbo.core_funct_luid_familyAdult(@OrganizationID), modified_by = @ProcessName, date_modified = GETDATE()
         WHERE person_id = @PersonID

         /**** Remove Relationships where there were no adults in family ****/
         IF @Debug = 1
            PRINT 'Remove invalid Relationships that were added because there were no adults in family'

         DELETE
         FROM core_relationship
         WHERE person_id = related_person_id

         IF (
               @@ROWCOUNT > 0
               AND @RecordChanges = 1
               )
            INSERT INTO @AllChanges
            VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Relationship values deleted because there were no adults in family', '')

         /**** Adding Person History ****/
         IF (
               @AdultCount = 0
               AND @ChildCount = 1
               )
         BEGIN
            IF @Debug = 1
               PRINT 'Adding Person History'

            IF @RecordChanges = 1
               INSERT INTO @AllChanges
               VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Note', 'Child family role changed to Adult via ' + @ProcessName + ', no other adults present in family record')

            INSERT INTO core_person_history
            SELECT GETDATE(), GETDATE(), @ProcessName, @ProcessName, @PersonID, 366, - 1, 1, 'Child family role changed to Adult via ' + @ProcessName + ', no other adults present in family record', @OrganizationID, 0, '1900-01-01', 0
         END
      END

      FETCH NEXT
      FROM PersonCursor
      INTO @PersonID
   END

   CLOSE PersonCursor

   DEALLOCATE PersonCursor

   IF @Debug = 1
      PRINT 'Fixing Giving Ids'

   EXEC core_sp_save_person_giving_id - 1, @OrganizationID

   SELECT *
   FROM @AllChanges
END
GO