· 3 years ago · Feb 17, 2022, 08:20 PM
1USE [ArenaDB]
2GO
3
4/****** Object: StoredProcedure [dbo].[cust_CCIW_sp_auto_adults] Script Date: 2/17/2022 2:07:33 PM ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11
12-- =============================================
13-- Modified By: Tony Visconti
14-- Original Author: anicoletti?, Bob Brown
15-- Create date: 4/29/19
16-- Description: Move Individuals with Child Family Role and minimum specifed Age to Adult Role within their own family
17-- 1.1
18-- Fixed missing space aftere word via in history note
19-- Added check to allow for parent child relationship if child's lastname contains parent's last name
20
21-- =============================================
22CREATE PROCEDURE [dbo].[cust_CCIW_sp_auto_adults] @PersonIDList VARCHAR(8000) = ''
23 , @IgnoreDOB BIT = 0
24 , @MinimumAge INT = -1
25 , @ParentRelationship INT = -1 -- Parent
26 , @ChildRelationship INT = -1
27 , @ProfileID INT = -1
28 , @ExcludeStatusIDList VARCHAR(8000) = '10419,10434,10398' --Stars Participant, Former Stars Participant, Deceased
29 , @EventRegistrationStatusID INT = 10470 --Event Registrant
30 , @Debug BIT = 1 -- Set to 1 to add print statements to messages regarding program flow
31 , @OrganizationID INT = 1
32AS
33IF @MinimumAge = - 1
34 SET @MinimumAge = dbo.orgn_funct_organizationSetting(@OrganizationID, 'AutoAdult_MinAge', '-1')
35
36IF @ParentRelationship = - 1
37 SET @ParentRelationship = dbo.orgn_funct_organizationSetting(@OrganizationID, 'AutoAdult_ParentRelationship', '-1')
38
39IF @ChildRelationship = - 1
40 SET @ChildRelationship = dbo.orgn_funct_organizationSetting(@OrganizationID, 'AutoAdult_ChildRelationship', '-1')
41
42IF @ProfileID = - 1
43 SET @ProfileID = dbo.orgn_funct_organizationSetting(@OrganizationID, 'AutoAdult_IgnoreProfile', '-1') --4425 production
44
45IF (
46 @MinimumAge <> - 1
47 AND @ParentRelationship <> - 1
48 AND @ChildRelationship <> - 1
49 )
50BEGIN
51 DECLARE @PersonID INT
52 DECLARE @OldFamily INT
53 DECLARE @NewFamily INT
54 DECLARE @NewAddress INT
55 DECLARE @RecordChanges BIT = 1;-- Set to 1 to return a list of changes to the results
56 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))
57 DECLARE @ProcessName VARCHAR(100) = 'Child to Adult Automation';
58 DECLARE @AdultChildName VARCHAR(100) = '';
59 DECLARE @AdultChildLastName VARCHAR(50) = '';
60 DECLARE @AdultChildMemberStatus VARCHAR(100) = '';
61 DECLARE @Parent1ID INT;
62 DECLARE @Parent1Name VARCHAR(100) = '';
63 DECLARE @Parent1LastName VARCHAR(50) = '';
64 DECLARE @Parent1MemberStatus VARCHAR(50) = '';
65 DECLARE @Parent2ID INT;
66 DECLARE @Parent2Name VARCHAR(100) = '';
67 DECLARE @Parent2LastName VARCHAR(50) = '';
68 DECLARE @Parent2MemberStatus VARCHAR(50) = '';
69 DECLARE @TempValue VARCHAR(100) = '';
70 DECLARE @AdultCount TINYINT;
71 DECLARE @ChildCount TINYINT;
72
73 DECLARE PersonCursor CURSOR READ_ONLY
74 FOR
75 SELECT P.person_id
76 FROM core_person P
77 LEFT JOIN core_family_member CFM ON CFM.person_id = P.person_id
78 WHERE CFM.role_luid = dbo.core_funct_luid_familyChild(@OrganizationID)
79 AND (P.birth_date > '1901' or @IgnoreDOB = 1)
80 AND (
81 P.birth_date <= (DATEADD(yy, - 1 * @MinimumAge, GETDATE()))
82 OR P.date_created <= (DATEADD(yy, - 1 * @MinimumAge, GETDATE()))
83 OR @IgnoreDOB = 1
84 ) -- Record was created more than x years ago
85 AND P.organization_id = @OrganizationID
86 AND P.person_id NOT IN (
87 SELECT person_id
88 FROM core_profile_member
89 WHERE profile_id = @ProfileID
90 )
91 AND (
92 @PersonIDList = ''
93 OR P.person_id IN (
94 SELECT *
95 FROM dbo.fnSplit(@PersonIDList)
96 )
97 )
98 AND (
99 @ExcludeStatusIDList = ''
100 OR P.member_status NOT IN (
101 SELECT *
102 FROM dbo.fnSplit(@ExcludeStatusIDList)
103 )
104 )
105 AND NOT (
106 P.member_status = @EventRegistrationStatusID
107 AND P.record_status = 2
108 ) -- Ignore pending event registrants, these could be STAR
109
110 OPEN PersonCursor
111
112 FETCH NEXT
113 FROM PersonCursor
114 INTO @PersonID
115
116 WHILE (@@FETCH_STATUS <> - 1)
117 BEGIN
118 IF (@@FETCH_STATUS = 0)
119 BEGIN
120 IF @Debug = 1
121 PRINT 'Processsing Adult Child with ID:' + CONVERT(VARCHAR(10), @PersonID)
122
123 SET @Parent1ID = dbo.core_funct_familyHead(@PersonID)
124 SET @Parent2ID = dbo.core_funct_spouse(@Parent1ID, @OrganizationID)
125 -- These variables are used to document changes
126 SET @AdultChildName = (
127 SELECT P.first_name + ' ' + P.last_name
128 FROM core_person P
129 WHERE P.person_id = @PersonID
130 )
131 SET @AdultChildLastName = (
132 SELECT P.last_name
133 FROM core_person P
134 WHERE P.person_id = @PersonID
135 )
136 SET @AdultChildMemberStatus = (
137 SELECT dbo.cust_CCIW_funct_luid_to_value(P.member_status)
138 FROM core_person P
139 WHERE P.person_id = @PersonID
140 )
141 SET @Parent1Name = (
142 SELECT P.first_name + ' ' + P.last_name
143 FROM core_person P
144 WHERE P.person_id = @Parent1ID
145 )
146 SET @Parent1LastName = (
147 SELECT P.last_name
148 FROM core_person P
149 WHERE P.person_id = @Parent1ID
150 )
151 SET @Parent1MemberStatus = (
152 SELECT dbo.cust_CCIW_funct_luid_to_value(P.member_status)
153 FROM core_person P
154 WHERE P.person_id = @Parent1ID
155 )
156 SET @Parent2Name = (
157 SELECT P.first_name + ' ' + P.last_name
158 FROM core_person P
159 WHERE P.person_id = @Parent2ID
160 )
161 SET @Parent2LastName = (
162 SELECT P.last_name
163 FROM core_person P
164 WHERE P.person_id = @Parent2ID
165 )
166 SET @Parent2MemberStatus = (
167 SELECT dbo.cust_CCIW_funct_luid_to_value(P.member_status)
168 FROM core_person P
169 WHERE P.person_id = @Parent2ID
170 )
171 ---
172 SET @NewFamily = NULL
173 SET @NewAddress = NULL
174 SET @OldFamily = (
175 SELECT TOP 1 family_id
176 FROM core_family_member
177 WHERE person_id = @PersonID
178 )
179 SET @AdultCount = (
180 SELECT COUNT(*)
181 FROM core_family_member
182 WHERE role_luid = dbo.core_funct_luid_familyAdult(@OrganizationID)
183 AND family_id = @OldFamily
184 )
185 SET @ChildCount = (
186 SELECT COUNT(*)
187 FROM core_family_member
188 WHERE role_luid = dbo.core_funct_luid_familyChild(@OrganizationID)
189 AND family_id = @OldFamily
190 )
191
192 IF (
193 @AdultCount > 0
194 OR @ChildCount > 1
195 )
196 BEGIN
197 IF @Debug = 1
198 PRINT 'Creating New Family'
199
200 /**** Create new family ****/
201 INSERT INTO core_family (created_by, modified_by, family_name, foreign_key, organization_id)
202 SELECT @ProcessName, @ProcessName, P.last_name, NULL, P.organization_id
203 FROM core_person P
204 WHERE P.person_id = @PersonID
205
206 SET @NewFamily = SCOPE_IDENTITY()
207
208 IF @RecordChanges = 1
209 INSERT INTO @AllChanges
210 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Created New Family', CONVERT(VARCHAR(10), @NewFamily))
211
212 /**** If no main/home address tied to Adult Child, add HoH main/home address ****/
213 IF @Debug = 1
214 PRINT 'Checking if Address needs to be copied from HoH to Child'
215
216 IF NOT EXISTS (
217 SELECT person_id
218 FROM core_person_address
219 WHERE person_id = @PersonID
220 AND address_type_luid = dbo.core_funct_luid_addressMain(@OrganizationID)
221 )
222 BEGIN
223 /**** If Adult Child has no main/home address, add Head of Household’s (HoH) main/home address if one exists ****/
224 IF EXISTS (
225 SELECT person_id
226 FROM core_person_address
227 WHERE person_id = @Parent1ID
228 AND address_type_luid = dbo.core_funct_luid_addressMain(@OrganizationID)
229 )
230 BEGIN
231 IF @Debug = 1
232 PRINT 'Copying Main Address from Head of Household to Adult Child'
233
234 SET @TempValue = (
235 SELECT street_address_1
236 FROM core_address CA
237 LEFT JOIN core_person_address AS CPA ON CPA.address_id = CA.address_id
238 WHERE address_type_luid = dbo.core_funct_luid_addressMain(@OrganizationID)
239 AND person_id = @Parent1ID
240 )
241
242 IF @RecordChanges = 1
243 INSERT INTO @AllChanges
244 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Copying Main Address from Head of Household to Adult Child', @TempValue)
245
246 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)
247 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
248 FROM core_address CA
249 LEFT JOIN core_person_address AS CPA ON CPA.address_id = CA.address_id
250 WHERE address_type_luid = dbo.core_funct_luid_addressMain(@OrganizationID)
251 AND person_id = @Parent1ID
252
253 SET @NewAddress = SCOPE_IDENTITY()
254
255 /**** Create person:address relationship ****/
256 INSERT INTO core_person_address (person_id, address_id, address_type_luid, primary_address, notes, organization_id)
257 SELECT @PersonID, @NewAddress, dbo.core_funct_luid_addressMain(1), 1, '', 1
258 END
259 END --Copying Main Address from Head of Household to Adult Child
260
261 IF @Debug = 1
262 PRINT 'Checking if phone # needs to be copied from HoH to Adult Child'
263
264 /**** If Adult Child has no home phone or cell phone, add HoH home phone (if no HoH home phone, add HoH cell phone) ****/
265 IF NOT EXISTS (
266 SELECT person_id
267 FROM core_person_phone
268 WHERE person_id = @PersonID
269 AND (
270 phone_luid = dbo.core_funct_luid_homePhone(@OrganizationID)
271 OR phone_luid = dbo.core_funct_luid_cellPhone(@OrganizationID)
272 )
273 )
274 BEGIN
275 IF EXISTS (
276 SELECT person_id
277 FROM core_person_phone
278 WHERE person_id = @Parent1ID
279 AND phone_luid = dbo.core_funct_luid_homePhone(@OrganizationID)
280 )
281 BEGIN
282 IF @Debug = 1
283 PRINT 'Copying Home Phone from HoH to Adult Child'
284
285 SET @TempValue = (
286 SELECT phone_number_stripped
287 FROM core_person_phone
288 WHERE person_id = @Parent1ID
289 AND phone_luid = dbo.core_funct_luid_homePhone(@OrganizationID)
290 )
291
292 IF @RecordChanges = 1
293 INSERT INTO @AllChanges
294 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Copying Home Phone from HoH to Adult Child', @TempValue)
295
296 INSERT INTO core_person_phone (person_id, phone_luid, phone_number, phone_ext, unlisted, phone_number_stripped, organization_id)
297 SELECT @PersonID, phone_luid, phone_number, phone_ext, unlisted, phone_number_stripped, organization_id
298 FROM core_person_phone
299 WHERE person_id = @Parent1ID
300 AND phone_luid = dbo.core_funct_luid_homePhone(@OrganizationID)
301 END
302 ELSE IF EXISTS (
303 SELECT person_id
304 FROM core_person_phone
305 WHERE person_id = @Parent1ID
306 AND phone_luid = dbo.core_funct_luid_cellPhone(@OrganizationID)
307 )
308 BEGIN
309 IF @Debug = 1
310 PRINT 'Copying Cell Phone from HoH to Adult Child'
311
312 SET @TempValue = (
313 SELECT phone_number_stripped
314 FROM core_person_phone
315 WHERE person_id = @Parent1ID
316 AND phone_luid = dbo.core_funct_luid_cellPhone(@OrganizationID)
317 )
318
319 IF @RecordChanges = 1
320 INSERT INTO @AllChanges
321 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Copying Cell Phone from HoH to Adult Child', @TempValue)
322
323 INSERT INTO core_person_phone (person_id, phone_luid, phone_number, phone_ext, unlisted, phone_number_stripped, organization_id)
324 SELECT @PersonID, phone_luid, phone_number, phone_ext, unlisted, phone_number_stripped, organization_id
325 FROM core_person_phone
326 WHERE person_id = @Parent1ID
327 AND phone_luid = dbo.core_funct_luid_cellPhone(@OrganizationID)
328 END
329 END
330
331 /**** Add Parent Relationship ****/
332 IF @Debug = 1
333 PRINT 'Adding HoH Parent Relationship to Adult Child'
334
335 IF CHARINDEX(@AdultChildLastName, @Parent1LastName) > 0 OR CHARINDEX(@Parent1LastName,@AdultChildLastName) > 0 -- check if the child last's name is contained in the parents name
336 BEGIN
337 IF @RecordChanges = 1
338 INSERT INTO @AllChanges
339 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Parent Relationship', @Parent1Name + ' is Parent')
340
341 INSERT INTO core_relationship (created_by, modified_by, relationship_type_id, person_id, related_person_id)
342 SELECT @ProcessName, @ProcessName, @ParentRelationship, @PersonID, @Parent1ID
343 END
344 ELSE -- Parent has different last name
345 BEGIN
346 IF @RecordChanges = 1
347 INSERT INTO @AllChanges
348 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Skipping Parent 1 Relationship', @Parent1Name + ' has different last name from ' + @AdultChildName)
349 END
350
351 /**** Add Second Parent Relationship ****/
352 IF @Debug = 1
353 PRINT 'Checking if second Parent Relationship is need'
354
355 IF EXISTS (
356 SELECT TOP 1 person_id
357 FROM core_person
358 WHERE person_id = @Parent2ID
359 )
360 BEGIN
361 IF @Debug = 1
362 PRINT 'Adding second Parent Relationship to Adult Child'
363
364 IF CHARINDEX(@AdultChildLastName, @Parent2LastName) > 0 OR CHARINDEX(@Parent2LastName, @AdultChildLastName ) > 0
365 BEGIN
366 IF @RecordChanges = 1
367 INSERT INTO @AllChanges
368 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Parent Relationship', @Parent2Name + ' is Parent')
369
370 INSERT INTO core_relationship (created_by, modified_by, relationship_type_id, person_id, related_person_id)
371 SELECT @ProcessName, @ProcessName, @ParentRelationship, @PersonID, @Parent2ID
372 END
373 ELSE -- Parent has different last name
374 BEGIN
375 IF @RecordChanges = 1
376 INSERT INTO @AllChanges
377 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Skipping Parent 2 Relationship', @Parent2Name + ' has different last name from ' + @AdultChildName)
378 END
379 END
380
381 /**** Add Adult Child Relationship ****/
382 IF @Debug = 1
383 PRINT 'Adding Adult Child relationship to HoH'
384
385 IF CHARINDEX(@AdultChildLastName, @Parent1LastName) > 0
386 BEGIN
387 IF @RecordChanges = 1
388 INSERT INTO @AllChanges
389 VALUES (@PersonID, @AdultChildName, @Parent1Name, @Parent1MemberStatus, 'Adding Adult Child Relationship', @AdultChildName + ' is Adult Child (>18)')
390
391 INSERT INTO core_relationship (created_by, modified_by, relationship_type_id, person_id, related_person_id)
392 SELECT @ProcessName, @ProcessName, @ChildRelationship, @Parent1ID, @PersonID
393 END
394 ELSE
395 BEGIN
396 INSERT INTO @AllChanges
397 VALUES (@PersonID, @AdultChildName, @Parent1Name, @Parent1MemberStatus, 'Skipping Adult Child Relationship', @Parent1Name + ' has different last name from ' + @AdultChildName)
398 END
399
400 /**** Add Second Adult Child Relationship ****/
401 IF @Debug = 1
402 PRINT 'Checking if second Adult Child Relationship is needed'
403
404 IF EXISTS (
405 SELECT TOP 1 person_id
406 FROM core_person
407 WHERE person_id = dbo.core_funct_spouse(dbo.core_funct_familyHead(@PersonID), @OrganizationID)
408 )
409 BEGIN
410 IF CHARINDEX(@AdultChildLastName, @Parent2LastName) > 0
411 BEGIN
412 IF @RecordChanges = 1
413 INSERT INTO @AllChanges
414 VALUES (@PersonID, @AdultChildName, @Parent2Name, @Parent2MemberStatus, 'Adding Adult Child Relationship', @AdultChildName + ' is Adult Child (>18)')
415
416 INSERT INTO core_relationship (created_by, modified_by, relationship_type_id, person_id, related_person_id)
417 SELECT @ProcessName, @ProcessName, @ChildRelationship, @Parent2ID, @PersonID
418 END
419 ELSE
420 BEGIN
421 INSERT INTO @AllChanges
422 VALUES (@PersonID, @AdultChildName, @Parent2Name, @Parent2MemberStatus, 'Skipping Adult Child Relationship', @Parent2Name + ' has different last name from ' + @AdultChildName)
423 END
424 END
425
426 /**** Move person to new family (needs to be last, for HoH functions to work in previous statements)****/
427 IF @Debug = 1
428 PRINT 'Moving person to new family'
429
430 IF @RecordChanges = 1
431 INSERT INTO @AllChanges
432 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Adult Child to New Family', CONVERT(VARCHAR(10), @NewFamily))
433
434 IF @Debug = 1
435 PRINT 'Adding Person History'
436
437 DECLARE @Parent2Note VARCHAR(200)
438 DECLARE @NoAdultsInfo VARCHAR(200) = ''
439
440 IF (@Parent2Name IS NOT NULL)
441 SET @Parent2Note = ' & ' + @Parent2Name
442 ELSE
443 SET @Parent2Note = ''
444
445 IF (@AdultCount = 0)
446 SET @NoAdultsInfo = ', no adults were present in family'
447
448 IF @RecordChanges = 1
449 INSERT INTO @AllChanges
450 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Note', 'Removed from Family ' + ISNULL(@Parent1Name, '') + @Parent2Note + ' (' + CONVERT(VARCHAR(200), @OldFamily) + ') via ' + @ProcessName + @NoAdultsInfo)
451
452 INSERT INTO core_person_history
453 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
454 END -- Changes when @AdultCount > 0 or @ChildCount > 1
455 -- Change family role to adult
456
457 IF @Debug = 1
458 PRINT 'Changing Childs Family Role to Adult'
459
460 IF @RecordChanges = 1
461 INSERT INTO @AllChanges
462 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Changing Childs Family Role to Adult', '')
463
464 IF (@NewFamily IS NULL)
465 SET @NewFamily = @OldFamily -- we didn't need to move the person into a new family
466
467 UPDATE core_family_member
468 SET family_id = @NewFamily, role_luid = dbo.core_funct_luid_familyAdult(@OrganizationID), modified_by = @ProcessName, date_modified = GETDATE()
469 WHERE person_id = @PersonID
470
471 /**** Remove Relationships where there were no adults in family ****/
472 IF @Debug = 1
473 PRINT 'Remove invalid Relationships that were added because there were no adults in family'
474
475 DELETE
476 FROM core_relationship
477 WHERE person_id = related_person_id
478
479 IF (
480 @@ROWCOUNT > 0
481 AND @RecordChanges = 1
482 )
483 INSERT INTO @AllChanges
484 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Relationship values deleted because there were no adults in family', '')
485
486 /**** Adding Person History ****/
487 IF (
488 @AdultCount = 0
489 AND @ChildCount = 1
490 )
491 BEGIN
492 IF @Debug = 1
493 PRINT 'Adding Person History'
494
495 IF @RecordChanges = 1
496 INSERT INTO @AllChanges
497 VALUES (@PersonID, @AdultChildName, @AdultChildName, @AdultChildMemberStatus, 'Adding Note', 'Child family role changed to Adult via ' + @ProcessName + ', no other adults present in family record')
498
499 INSERT INTO core_person_history
500 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
501 END
502 END
503
504 FETCH NEXT
505 FROM PersonCursor
506 INTO @PersonID
507 END
508
509 CLOSE PersonCursor
510
511 DEALLOCATE PersonCursor
512
513 IF @Debug = 1
514 PRINT 'Fixing Giving Ids'
515
516 EXEC core_sp_save_person_giving_id - 1, @OrganizationID
517
518 SELECT *
519 FROM @AllChanges
520END
521GO
522
523
524