Event Costs and Fees

Just cooked this up for one of our trainers, but it’s something I think a lot of admins may end up wanting once they start to see it, so here’s the stuff needed for it.

CREATE PROCEDURE dbo.cust_sp_evnt_costs_and_fees
@profile INT
AS
SELECT
'Cost: '+ec.title AS [Cost or Fee Name]
,COUNT(*) AS [Number of Paid Registrants]
FROM
evnt_registrant_cost AS erc
JOIN evnt_registrant_cost_payment AS ercp ON ercp.registrant_cost_id=erc.registrant_cost_id
JOIN evnt_cost AS ec ON ec.cost_id=erc.cost_id
WHERE erc.profile_id = @profile
AND erc.paid=1
GROUP BY ec.title
UNION
SELECT
'Fee: '+ef.title+ISNULL(': '+NULLIF(erf.selected_value,''),'') AS [Cost or Fee Name]
,SUM(erf.quantity) AS [Number of Paid Registrants]
FROM
evnt_registrant_fee AS erf
JOIN evnt_registrant_fee_payment AS erfp ON erfp.registrant_fee_id=erf.registrant_fee_id
JOIN evnt_fee AS ef ON ef.fee_id=erf.fee_id
WHERE erf.profile_id = @profile
AND erf.paid=1
GROUP BY ef.title,erf.selected_value

This is the setup in Pages (explanation below):

Explanation:
On the Event Tags page's Main Content tab, add a new module.
Per the instructions in the image, drag the new module up one spot and back down (to force the program to recognize that it should be at the bottom of the page)
Open the new module and change the type to Report Grid From Query (it's in the Reporting section).
Set the Parameter and Query options as shown in the image (don't forget the semi-colon at the end of the parameter).
Refresh the cache and you're good to go.

What this does is use a Report Grid From Query at the bottom of the Event details page showing a count of how many of each cost and fee (it separates out fees by selected values, so if you’re doing T-shirts for $10 each, you’ll see something like “Fee: T-Shirt: X-Large……3”) have been selected, restricted to the current event (it does not roll up sub-events). It’s just an aggregate count, and it only includes Paid costs & fees, but gives you a quick count of how many vegetarian meals to order, or whatnot.

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