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

The Corner of Nothing and NULL

Sometimes, converting a manual attendance sheet over to a Reporting Services report can be a bit harder than it would at first seem, and can result in some odd code to produce the desired results. Case in point: a client recently asked for a report to emulate their attendance sheet, but to update the sheet with the attendance information each time it is run. The report had to display members of a Tag as rows, a number of static data fields about each member, and then have a dynamic listing of columns for the Occurrences they may have attended (or future occurrences they might not yet have attended).

The kicker is that the Occurrences are tied to a specific set of Small Groups, and the selected Tag members may or may not be members of the set of Small Groups. So we needed a way to make sure that we had everyone in the selected Tag (even if they had never attended any of the occurrences, so a NULL for that person in the attendance tables) and all of the selected Occurrences (even if nobody had attended them, so Nothing in the attendance tables at all) without any existing, direct way to link the persons to the occurrences they hadn't attended.

I tried a few different approaches before I realized that the problem was easily solved by applying an unusual and often shunned equation: "1=1". This comparison is frequently used as a placeholder for an actual variable, but all too often it is forgotten and suddenly there is a hard-coded loop or action somewhere in the program that nobody can explain, so most programmers look down on its use. In this case, however, it was exactly what was needed.

What I ended up with was a join statement like this:

...
FROM
core_profile_member AS cpm
JOIN (
   SELECT co2.occurrence_id, co2.occurrence_start_time
   FROM
   core_occurrence AS co2
   JOIN smgp_group_occurrence AS sgo2 ON sgo2.occurrence_id=co2.occurrence_id
   WHERE sgo2.group_id IN (<Group Listing>)
  ) AS co ON 1=1
LEFT OUTER JOIN core_occurrence_attendance AS coa ON coa.person_id=cpm.person_id
   AND coa.occurrence_id=co.occurrence_id
...
WHERE cpm.profile_id IN (<Tag Listing>)
...

This subquery ends up joining every single occurrence linked to those Small Groups with every single person from the required Tags, so I end up with a row for every person:occurrence combination, whether they have attendance info or not.

I ended up having to do some date manipulation techniques to end up with just 1 report of attendance per day (in the odd chance there might be multiple occurrences on the same day), as well as a few other tweaks to match the client's specific criteria, but this ends up with exactly what I need: people regardless of attendance and attendance regardless of people, without a direct link between the two.

Re-assign a My List

A question recently came up of how to re-assign a My List to another user (or how to add a list directly to another user's My Lists page). The current functionality always reverts a list to the creator's control, so the easiest way to do this is to change the creator of the list in the database. Using a bit of reverse engineering of Nick Airdo's Edit Page functionality (Link Here), and combining it with an unorthodox use of the Report Grid From Query module, I think I have a solution to the problem:

1. Set the page ID (3186) in the SQL code below to your My Lists page.

CREATE PROCEDURE dbo.cust_sp_reassign_list
@reportid INT
,@newuser VARCHAR(50)
AS
  
  IF EXISTS (
    SELECT TOP 1 1 
    FROM secu_login 
    WHERE login_id=@newuser)
  BEGIN
    UPDATE list_save_reports
    SET created_by=@newuser
    WHERE report_id=@reportid
    
    SELECT 
    0 AS ordering
    , 'Report ID '
      +CONVERT(VARCHAR,@reportid)
      +' transfered to user '
      +UPPER(@NEWUSER) AS Result
    UNION
    SELECT
    1 AS ordering
    ,'<a href="default.aspx?page=3186">Click here to return to My Lists</a>' AS Result
    ORDER BY ordering
  END
  ELSE
    SELECT 
    0 AS ordering
    , 'User '
      +UPPER(@NEWUSER)
      +' not found!' AS Result
    UNION
    SELECT 
    1 AS ordering
    , '<a href="default.aspx?page=3186">Click here to return to My Lists</a>' AS Result
    ORDER BY ordering

2. Run the SQL code to generate the cust_sp_reassign_list stored proc.

3. Create a new page with a Report Grid From Query module in it, with the two parameters listed for the SP below, suppressing the "ordering" column.

4. Set the page ID (3292) in the javascript below to the page ID of the newly created page from step 3.

5. Put the following javascript in an Advanced HTML Text module (with Evaluate Query Parameters = True) on the Report View page. Don't forget to give security access to this new module to anyone you want to be able to reassign Lists.

<script language="javascript" type="text/javascript">
function reassignList()
{
  var newuser = prompt("Enter the new user ID:");
  window.location = "default.aspx?page=3292&reportid=##REPORTID##&newuser=" + newuser;
}
</script> <a onclick="reassignList()" href="#">Assign This List To A New User</a>

This will give you a link at the top of each report to allow you to re-assign that specific report to a new user. Note that you need to know the exact username of the new user, as the stored proc will reject an invalid username.

What date is next Tuesday?

So I was working on a project, and for part of the project, I needed to know what date was the next specific weekday after a given date. Not only that, but the "specific weekday" could be a range of possible weekdays (e.g. "When is the next Monday or Thursday after today?"). After a bit of Googling, I couldn't find a ready-made solution to this, so I decide to take a crack at it.
The following User-Defined Function (since I wanted to make this code reusable) is the fruit of my labor:

CREATE FUNCTION dbo.cust_funct_nextdate 
(@startDate DATETIME, @weekdays VARCHAR(10))
RETURNS DATETIME
AS
BEGIN

 DECLARE 
 @dw INT
 ,@returndate DATETIME
 
 DECLARE @days TABLE (day_of_week INT)

 IF PATINDEX('%U%',@weekdays)
  INSERT INTO @days SELECT 1

 IF PATINDEX('%M%',@weekdays)
  INSERT INTO @days SELECT 2

 IF PATINDEX('%T%',@weekdays)
  INSERT INTO @days SELECT 3

 IF PATINDEX('%W%',@weekdays)
  INSERT INTO @days SELECT 4

 IF PATINDEX('%R%',@weekdays)
  INSERT INTO @days SELECT 5

 IF PATINDEX('%F%',@weekdays)
  INSERT INTO @days SELECT 6

 IF PATINDEX('%S%',@weekdays)
  INSERT INTO @days SELECT 7

 SET @dw=(
  SELECT MIN(day_of_week) 
  FROM @days 
  WHERE day_of_week>DATEPART(DW,@startDate))
  
 IF (@dw IS NULL)
  SET @returndate=(
   SELECT DATEADD(dd,7-(datepart(DW,@startDate)
    -(SELECT MIN(day_of_week) FROM @days))
    ,@startdate))
 ELSE
  SET @returndate=(
   SELECT DATEADD(dd,(
    SELECT @dw-DATEPART(dw,@startDate))
    ,@startDate))
  
 RETURN @returndate
END

As you can see, you pass it a date and a string field containing the letters representing the days of the week you want it to evaluate, following a simple "MTWRFSU" format for Monday through Sunday. It then evaluates the string value to determine what days of the week you want it to look for, and then manipulates the date input to be the next upcoming value in your supplied list of weekdays.

Just a handy tool I thought I'd share for anyone who needs it

Everything fits IN a NULL

I just learned something I hadn't really thought about before: while a NULL is nothing, everything is IN a NULL. What I mean is when doing a comparison of ...

WHERE a.something IN (SELECT things FROM aTable)

...if any of the values in your target collection are NULL, the IN comparison will return true, even if the source is not NULL.

To get around this, my co-worker came up with a quick and clever solution:

WHERE a.something IN (SELECT ISNULL(things,-1) FROM aTable)

This simply removes any NULLs from the target collection without having to do a NULL comparison on the source value. Obviously, the alternate value in your ISNULL() function would have to be something that cannot be your source value, so -1 may not be the correct value for your query, but you get the idea....it's important to know if there are any NULL values in the target collection, because aparently the IN function will say that pretty much anything is in a NULL value.