Automating and Exporting Activity Attendance for Group Participants

We’re back with this week’s tutorial that will build on and combine the following tutorials:

• How to retrieve a list of group participants
• Retrieving individual activity attendance
• How to automate using cron jobs
• Creating a reusable function for CCB API calls

I’ll discuss and show you how to combine tutorials to *automagically* export data from CCB, using the CCB API of course, into an Excel spreadsheet.

I won’t cover the automation portion in detail, but you can review the third bulleted tutorial above for that information. Let’s get started.

The origin of the activity attendance report

Not too long ago, I was approached by our Worship Pastor about creating a report to view all of the Worship Team participants and their monthly activity pertaining to their Community Group attendance. There was also the request to have this spreadsheet auto emailed on a quarterly basis.

Essentially, the need was to have a vertical column of months with names of individuals via a horizontal row. Each month would be listed at mm/yyyy (e.g., 1/12/2016). Where the month and individual intersect, that cell would have the totaled number of times a person attended a Community Group (shown in example image below).

We’ll create a Excel report that displays the attendance data for each person and their respective information of the group attended (i.e., type of group, group name and date attended).

To achieve the data as displayed above, I recommend that you use an Excel Pivot Table, or you’ll have to use a bit more advanced programming not covered in this tutorial to *automagically* create the report above.

Let’s start by defining variables

Open your text editor, creating and saving a file named group-participants-reporter.php.

I won’t spend much time on the variable setup other than to say be sure you that setup apiService and groupID variables (see the code example below). The apiService should be defined as group_participants, and the groupID should be the id of the group you would like to create the report for.

Start by using the code you created in the How to retrieve a list of group participants tutorial. Also, review the section about how to find your group’s id in CCB should you not know how.

<?PHP

/**
 * group participants reporter
 */

// contains resusable globals and functions
include("includes/config.php");

$apiService = 'group_participants'; // CCB api service
$groupID = '159'; // Adult Worship Team

$urlData = array_filter( array(
	'srv' => "$apiService",
    'id' => "$groupID",
	) );

$rss = ccbDetails('get',$urlData); // transform to XML

$nodes = $rss->xpath('//participants/participant');  // xpath for groups

?>

In addition, don’t forget to define the response_object variable to a blank string. This just ensures that we are not starting with previous data defined to the response_object variable.

Now below the response_object variable, we’ll redefine it’s value to be the names of the header columns for the Excel spreadsheet separated by commas. See the example code below:

<?PHP


$response_object = '';

$response_object = '"Name","Event","Group","Date"'. "\n";

?>

Creating the getAttendance function

Since it’s likely that your selected group will have more than one person in it, we’ll create a foreach statement that uses a function to gather each person’s attendance activity.

The person’s id and name will be passed as function arguments to the getAttendance function respectively (see below).

<?PHP

function getAttendance($id,$name){

}


?>

Now that you have successfully created the getAttendance function, simply copy and paste your code from the Retrieving individuals activity attendance tutorial.

Not so quick though, we’re not done. We’ll need to change two things in the copy and pasted code that is now inside of the getAttendance function.

Change the personID variable to be equal to the id variable. Now, you must change the response_object variable to be comma separated values much like the header values set up above in the previous step.

Set response_object value to be a concatenated string containing the name, node->event, node->group, and node->occurrence variables all separated by commas.

<?PHP

function getAttendance($id,$name){

$apiService = 'individual_attendance'; // CCB api service
$personID = $id; // Person's ID

$urlData = array_filter( array(
	'srv' => "$apiService",
    'individual_id' => "$personID",
	) );

$rss = ccbDetails('get',$urlData); // transform to XML

$nodes = $rss->xpath('//attendances/attendance');  // xpath for attendance

$response_object = '';

foreach ($nodes as $node)
{

	$pageString = $node->event;

	if ((strpos($pageString,'Group') !== false) || (strpos($pageString,'Gathering') !== false && strpos($pageString,'IF') === false) || (strpos($pageString,'Community') !== false)) {

    $response_object .= '"'.$name.'","'.$node->event.'","'.$node->group.'","'.$node->occurrence.'"'."\n";

	}
}

return $response_object;

}

?>

Creating and saving activity attendance data to an Excel file

After the foreach statement closes, it’s now time to save the activity attendance data to an Excel file.

You’ll see in the code I chose to echo the response_object so that you can view the data that is being saved when you execute the code in a few minutes from the web browser.

To create the excel file, we’ll use built-in PHP methods of fopen, fwrite, and fclose to open, write and close the Excel respectively.

Create a f variable, and set its value to use the fopen method with the apiService variable concatenated to .csv extension as the first argument and w+ as the second argument.

Next, we’ll write the data to the file using the fwrite method, passing it the f and response_object variables respectively as function/method arguments.

Finally, we close the file using using fclose, and that completes creating and saving activity attendance data to an Excel file.

<?PHP


echo $response_object;

$f = fopen($apiService.'.csv' , 'w+');
fwrite($f , $response_object);
fclose($f);


?>

Time to put it all together and test

Now you’re ready to test your activity attendance report. Simply save your group-participants-reporter.php file and open it in a web browser. You should now be able to view an activity attendance report for a group of individuals from the web browser and Excel spreadsheet.  Here’s the entire codebase…

<?PHP

/**
 * group participants reporter
 */

// contains resusable globals and functions
include("includes/config.php");

$apiService = 'group_participants'; // CCB api service
$groupID = '159'; // Adult Worship Team

$urlData = array_filter( array(
	'srv' => "$apiService",
    'id' => "$groupID",
	) );

$rss = ccbDetails('get',$urlData); // transform to XML

$nodes = $rss->xpath('//participants/participant');  // xpath for groups

$response_object = '';

$response_object = '"Name","Event","Group","Date"'. "\n";

foreach ($nodes as $node)
{
	$response_object .= getAttendance($node['id'],$node->name);
}

echo $response_object;

$f = fopen($apiService.'.csv' , 'w+');
fwrite($f , $response_object);
fclose($f);


function getAttendance($id,$name){

$apiService = 'individual_attendance'; // CCB api service
$personID = $id; // Person's ID

$urlData = array_filter( array(
	'srv' => "$apiService",
    'individual_id' => "$personID",
	) );

$rss = ccbDetails('get',$urlData); // transform to XML

$nodes = $rss->xpath('//attendances/attendance');  // xpath for attendance

$response_object = '';

foreach ($nodes as $node)
{

	$pageString = $node->event;

	if ((strpos($pageString,'Group') !== false) || (strpos($pageString,'Gathering') !== false && strpos($pageString,'IF') === false) || (strpos($pageString,'Community') !== false)) {

    $response_object .= '"'.$name.'","'.$node->event.'","'.$node->group.'","'.$node->occurrence.'"'."\n";

	}
}

return $response_object;

}

?>

One thing to note is that the Excel spreadsheet is really a .csv file named group-participants.csv, and saved in the same directory as the group-participants-reporter.php file.

Before I forget, use a cron job to to automate this report (see link at beginning of tutorial) and now you’re set. If you want to take it one step further, add code to send an email containing a link to the Excel or .csv file so that you can click the link to download the report from your email.

And for those wanting an .xlsx file, simply perform a save as when viewing the .csv and select Microsoft Excel as the new format.

Remember that this report only gives you the raw data.  You’ll have to use a Excel Pivot Table to sum the data by month by individual.

Well, that’s all I have for you this week.  We’ll start a new tutorial next, so see you then.