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