Facebook: I Like Arena

Facebook: 500 million users, decent API, social media at its best, and one of the fastest communications tools out there for allowing your Membership to quickly reach each other with information about your Ministry. So the first step in getting into that world is the ubiquitous "Like" button.

To implement this, you'll need to do two things. First, you'll need to register your "app" (Arena-powered website) with Facebook by logging in to your church's Facebook account (using your personal account makes it your app, not the organization's) and going to: http://developers.facebook.com/setup/.

Once you've registered your app and have your AppID, add an Advanced HTML Text module to any page of your site, and copy this code into it, replacing the 'YOUR APP ID HERE' with... you guessed it: your app id (but keep the single-quotes around the ID, they're important).

<div id="fb-root"></div>
<script src="http://connect.facebook.net/en_US/all.js" ></script>
<script>
window.fbAsyncInit = function () {
FB.init({ appId: 'YOUR APP ID HERE', status: true, cookie: true,xfbml: true});
};
(function () {
var e = document.createElement('script');
e.type = 'text/javascript';
e.src = document.location.protocol + '//connect.facebook.net/en_US/all.js';
e.async = true;
document.getElementById('fb-root').appendChild(e);
} ());
</script>
<fb:like layout="button_count" width="90"></fb:like>

And there's your Like button!
You can add this code directly to your template (or you might want to just add a specific content area to the template and add an Adv. HTML Text module to that content area in the Template inside Arena so you can manage security on it), since it intelligently "likes" the current dynamic URL (right down to the query string, so it can even "like" events or promotions).

Admin's Friend: Javascript

Not so much SQL this time, but still super useful for an admin: Add this code to the Welcome Message section of your Login Logout module when using the Arena Basic template to add two useful links at the top: Edit This Page (takes you to the Pages view for the current page) and Refresh Cache (...exactly that).

<script type="text/javascript">
function RefreshCache() {
var rr = document.location.href

if (rr.indexOf("?")>0)
window.location = rr + "&refreshcache=true";
else
window.location = rr + "?refreshcache=true";
}
function getQueryVariable(variable) {
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var i=0;i<vars.length;i++) {
var pair = vars[i].split("=");
if (pair[0] == variable) {
return pair[1];
}
}
return 3;
}
function EditPage() {
window.location = "default.aspx?page=34&pageid=" + getQueryVariable("page");
}
</script>
<b><a href="javascript:EditPage()">|Edit This Page|</a>&nbsp;&nbsp;&nbsp;<a href="javascript:RefreshCache()">|Refresh Cache|</a></b>&nbsp;&nbsp;&nbsp;Welcome ##nickname## ##lastname##.

Staff Birthdays

The goal: show all staff members whose birth days are either within the last week (previous 7 days) or in the coming month (next 30 days), with past birthdates slightly greyed out. The mechanism: HTML From Stored Procedure. The result:

CREATE PROC dbo.cust_sp_staff_birthdays_html
AS

DECLARE @datatable TABLE (nick_name VARCHAR(50),last_name VARCHAR(50),normalized_birth_date DATETIME,birth_day VARCHAR(50),person_guid UNIQUEIDENTIFIER)

INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE()),DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE()),DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

IF (MONTH(GETDATE())=12)
INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE())+1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE())+1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

IF (MONTH(GETDATE())=1)
INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE())-1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE())-1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

SELECT ISNULL(REPLACE(REPLACE(
'<style type="text/css">
a.pastdate{color:#888888;font-family: Verdana, Arial, Helvetica, sans-serif;font-size: 10px;text-decoration: none;}
a.foredate{color:#000000;font-family: Verdana, Arial, Helvetica, sans-serif;font-size: 10px;text-decoration: none;}
</style>
<table cellspacing="2" cellpadding="0" class="smallText">'+
'<tr><td colspan="2"><h2>Staff Birthdays</h2></td></tr>'+
(SELECT
'<tr >
<td width="160"><a href="default.aspx?page=7&guid='+ISNULL(CONVERT(VARCHAR(50),person_guid),'')+'" style="text-decoration:none;" class="'+(CASE WHEN normalized_birth_date < DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0) THEN 'pastdate' ELSE 'foredate' END)+'" >'+ISNULL(nick_name,'')+' '+ISNULL(last_name,'')+'</a></td>
<td width="30"><a href="default.aspx?page=7&guid='+ISNULL(CONVERT(VARCHAR(50),person_guid),'')+'" style="text-decoration:none;" class="'+(CASE WHEN normalized_birth_date < DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0) THEN 'pastdate' ELSE 'foredate' END)+'" >'+ISNULL(birth_day,'')+'</a></td>
</tr>'
FROM @datatable
ORDER BY normalized_birth_date, last_name,nick_name
FOR XML PATH(''))
+'</table>'
,'&lt;','<'),'&gt;','>'),'') AS [html]

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.