Capitalize Names

The request this time is for some way to quickly capitalize the names in the database, so I created a SQL function that can be used in nearly any context to capitalize a string passed to it. This function will upper-case the first letter of each word of the string passed to it, so for example: "this is bad capitalization" will become "This Is Bad Capitalization" (which, ironically, it isn't anymore). Note that it will only affect the first letter, so for names like "mcDonald" it will properly ignore the rest of the name (i.e. "McDonald"); also it will capitalize every word, so it might mess up names like "von Richtofen" which should have a proper non-capitalized word.

CREATE FUNCTION dbo.cust_funct_capitalize (@name VARCHAR(MAX))
RETURNS VARCHAR(MAX)

AS

BEGIN
DECLARE @firstletter VARCHAR(10), @restofname VARCHAR(MAX), @partofname VARCHAR(MAX),@totalname VARCHAR(MAX)

IF (CHARINDEX(' ',@name)>0)
BEGIN
SET @firstletter=LEFT(@name,1)
SET @restofname=SUBSTRING(@name,2,CHARINDEX(' ',@name)-2)
SET @partofname=SUBSTRING(@name,CHARINDEX(' ',@name)+1,LEN(@name))
SET @totalname=UPPER(@firstletter)+@restofname+' '
WHILE LEN(@partofname)>0
BEGIN
SET @firstletter=LEFT(@partofname,1)
IF (CHARINDEX(' ',@partofname)>0)
BEGIN
SET @restofname=SUBSTRING(@partofname,2,CHARINDEX(' ',@partofname)-2)
SET @partofname=SUBSTRING(@partofname,CHARINDEX(' ',@partofname)+1,LEN(@partofname))
END
ELSE
BEGIN
SET @restofname=SUBSTRING(@partofname,2,LEN(@partofname))
SET @partofname=''
END
SET @totalname=@totalname+UPPER(@firstletter)+@restofname+' '
END
END
ELSE
BEGIN
SET @firstletter=LEFT(@name,1)
SET @restofname=SUBSTRING(@name,2,LEN(@name))
SET @totalname=UPPER(@firstletter)+@restofname
END

SET @totalname=RTRIM(@totalname)

RETURN @totalname
END

As an example of how this is designed to be used:

UPDATE core_person
SET first_name=dbo.cust_funct_capitalize(first_name)

Copy Calling Campaign Notes to Head of Household

We're on a roll, so I'll just keep going with it: A client requested the ability to save Calling Campaign notes to the family, and since this is really just a mutation of the earlier script, I tossed it together and thought I'd upload it here as well. Couple of notes on this variation though, specifically that it only copies the note to the Head of Household (can be changed, just add the core_family_member table before core_person and adjust the core_person join to link through core_family_member), and because individual calling campaigns do not have their own security, it sets security based on who has access to the Active Campaigns module under Membership.

CREATE PROC [dbo].[cust_sp_cpgn_notes_to_HoH]
AS

INSERT INTO [ArenaDB].[dbo].[core_person_history]
([date_created]
,[date_modified]
,[created_by]
,[modified_by]
,[person_id]
,[history_type_luid]
,[history_qualifier_id]
,[history]
,[organization_id])
SELECT DISTINCT
cfn.date_modified
,cfn.date_modified
,'Campaign Notes'
,cfn.modified_by
,cp.person_id
,355
,cfn.campaign_family_notes_id
,ccpgn.name+': ('+cl.lookup_value+') '+CONVERT(VARCHAR(MAX),cfn.notes)
,(SELECT TOP 1 organization_id FROM orgn_organization)
FROM
cpgn_family_notes AS cfn
JOIN core_lookup AS cl ON cl.lookup_id=cfn.status_luid
JOIN cpgn_family AS cfam ON cfam.campaign_family_id=cfn.campaign_family_id
JOIN cpgn_campaign AS ccpgn ON ccpgn.campaign_id=cfam.campaign_id
JOIN core_person AS cp ON cp.person_id=dbo.core_funct_familyHead2(cfam.family_id)
WHERE cfn.campaign_family_notes_id NOT IN (SELECT history_qualifier_id FROM core_person_history WHERE person_id=cp.person_id AND history_type_luid=355 AND created_by='Campaign Notes')

DECLARE @historyID INT
DECLARE historyCursor CURSOR FAST_FORWARD FOR
SELECT person_history_id FROM core_person_history WHERE created_by='Campaign Notes' AND person_history_id NOT IN (SELECT object_key FROM secu_permission WHERE object_type=11)

OPEN historyCursor

FETCH NEXT FROM historyCursor
INTO @historyID

WHILE @@FETCH_STATUS<>-1
BEGIN
IF @@FETCH_STATUS=0
BEGIN
INSERT INTO secu_permission (object_type,object_key,subject_type,subject_key,operation_type)
SELECT
11 AS object_type
,@historyID
,subject_type
,subject_key
,operation_type
FROM
secu_permission
WHERE object_type=2
AND object_key=300
END
FETCH NEXT FROM historyCursor
INTO @historyID
END
CLOSE historyCursor
DEALLOCATE historyCursor

Copy Assignment Notes to Target Person

I was asked today about the fact that there is a "Add to Requester's Person Notes" option for assignment notes but nothing that copies the note to the target's Person Notes. This might not seem like a big deal at first, but imagine you have an assignment type of "Hospital Visitation" and you as a staff member are requesting a visitation for a congregation member: You're the requester, the member is the Target person (custom field), but you're the one getting all the notes added to your Person Details page! This is something we're going to look at adding, but in the mean time, here's a couple of scripts I developed that copies all non-Private notes from Person Details-workflow-type assignments to the Target person's Person Notes (and also sets security on the notes equal to the security for the assignment type they come from).

Custom view for the data (run this once):

CREATE VIEW dbo.cust_v_assignment_target_notes
AS
SELECT
aah.assignment_history_id
,aat.name
,aa.title
,aafv.selected_value AS target_person_id
,aah.date_created
,aah.person_id AS noter_person_id
,cp.nick_name
,cp.last_name
,aah.note
FROM
asgn_assignment_history AS aah
JOIN asgn_assignment AS aa ON aa.assignment_id=aah.assignment_id
JOIN asgn_assignment_type AS aat ON aat.assignment_type_id=aa.assignment_type_id
JOIN asgn_assignment_field_value AS aafv ON aafv.custom_field_id=aat.workflow_custom_field_id AND aafv.assignment_id=aa.assignment_id
JOIN core_v_person_basic AS cp ON cp.person_id=aah.person_id
WHERE aat.workflow_type_id=1
AND aah.action='Note'
AND aah.private=0

Custom Stored Procedure (schedule this to run regularly, couple times a day or maybe more):

CREATE PROCEDURE dbo.cust_sp_asgn_notes_to_target

AS

INSERT INTO [dbo].[core_person_history]
([date_created]
,[date_modified]
,[created_by]
,[modified_by]
,[person_id]
,[history_type_luid]
,[history_qualifier_id]
,[history]
,[organization_id])
SELECT
date_created AS date_created
,date_created AS date_modified
,'Assignment Notes' AS created_by
,nick_name+' '+last_name AS modified_by
,target_person_id AS person_id
,355 AS history_type_luid
,assignment_history_id AS history_qualifier_id
,name+', '+title+': ('+nick_name+' '+last_name+') '+note AS history
,(SELECT TOP 1 organization_id FROM orgn_organization) AS organization_id
FROM
cust_v_assignment_target_notes
WHERE assignment_history_id NOT IN (SELECT history_qualifier_id FROM core_person_history WHERE person_id=target_person_id AND history_type_luid=355 AND created_by='Assignment Notes')

DECLARE @historyID INT
DECLARE historyCursor CURSOR FAST_FORWARD FOR
SELECT person_history_id FROM core_person_history WHERE created_by='Assignment Notes' AND person_history_id NOT IN (SELECT object_key FROM secu_permission WHERE object_type=11)

OPEN historyCursor

FETCH NEXT FROM historyCursor
INTO @historyID

WHILE @@FETCH_STATUS<>-1
BEGIN
IF @@FETCH_STATUS=0
BEGIN
INSERT INTO secu_permission (object_type,object_key,subject_type,subject_key,operation_type)
SELECT
11 AS object_type
,@historyID
,subject_type
,subject_key
,operation_type
FROM
secu_permission
WHERE object_type=13
AND object_key=(SELECT TOP 1 assignment_type_id FROM asgn_assignment WHERE assignment_id=(SELECT assignment_id FROM asgn_assignment_history WHERE assignment_history_id=(SELECT history_qualifier_id FROM core_person_history WHERE person_history_id=@historyID)))
END
FETCH NEXT FROM historyCursor
INTO @historyID
END
CLOSE historyCursor
DEALLOCATE historyCursor