Collecting Civicrm Data Through An Ubercart Checkout Pane
on
September 4, 2009
Collecting Civicrm Data Through An Ubercart Checkout Pane
Recently, I was involved in a project here at CivicActions in which the client – a major university – wanted to to sell online courses geared towards high school teachers. These courses are intended to help school teachers incorporate more effective teaching techniques into their daily lesson plans. This client not only wanted the ability to sell these courses, but also wished to collect data from their customers that would allow the client to better target potential customers. For example, they wished to collect data about how long each customer has been teaching, what grades and subjects they teach, which school district they teach in, etc. In addition, they required a workflow like so:
- The customer chooses the course they would like to purchase
- Adds it to their cart
- Upon checkout, in addition to the standard address and billing information, the aforementioned custom data (years teaching, subjects taught, school district, etc) is also collected.
- When the order is submitted this custom data is stored in a way that it can be searched and reported on.
This particular client was already running a drupal 6.x site, so in order to accomplish the online sales part of the requirements, we installed and configured ubercart with a paypal payment gateway. The data collection part of the requirements was a bit trickier. For the data storage, we chose to install and configure civicrm keeping the civicrm and the drupal databases separate. Within civicrm, we created a set of custom fields: school_district, teaching_since, subjects_taught, grades_taught.
We then gathered these custom data fields into a civicrm profile. In order to adhere to the workflow required by the client, we needed to be able to add these custom fields to the checkout page. Ubercart provides a handy way to do this through the use of checkout panes. So we wrote a module to take admin-selected civicrm profiles and display them as form fields in an ubercart checkout pane. The module basically allows the admin (via the settings page) to view and select a list of civicrm profiles to be included in the checkout pane. Those settings are stored in the drupal database, and inside the callback function for the hook_checkout_pane() they are retrieved. To display the custom fields stored within these profiles we switch (from within the ‘view’ $op) on the profile field’s ‘html_type’:
case 'view':
if (!empty($uf_profiles)) {
foreach ($uf_profiles as $profile_id => $fields) {
foreach ($fields as $field_name => $field_info) {
// Get the default value…
$params = array(
'contact_id' => $contact_id,
'return.'. $field_name => 1,
);
require_once('api/v2/Contact.php');
$contact = civicrm_contact_get($params);
$default_value = $contact[$contact_id][$field_name];
switch ($field_info['html_type']) {
case 'Text':
$contents[$field_name] = array(
'#type' => 'textfield',
'#title' => $field_info['title'],
'#default_value' => $default_value,
'#description' => $field_info['help_post'],
);
break;
case 'Radio':
// Split the custom field name on '_' in order to get the civicrm_custom_field.id.
$field_pieces = explode('_', $field_name);
$custom_field_id = $field_pieces[1];
// Now use the civicrm_custom_field.id to get the option values and labels for this custom field.
$result = civicrm_db_query("SELECT * FROM {civicrm_option_value} WHERE option_group_id = (SELECT option_group_id FROM {civicrm_custom_field} WHERE id = %d)", $custom_field_id);
while ($row = db_fetch_object($result)) {
$options[$row->value] = t($row->label);
}
$contents[$field_name] = array(
'#type' => 'radios',
'#title' => $field_info['title'],
'#options' => $options,
'#description' => $field_info['help_post'],
);
break;
…
…case 'process':
foreach ($arg2 as $field_name => $field_value) {
$arg1->uc_civicrm_profile_paneData[$field_name] = $arg2[$field_name];
}case 'save':
if (!empty($order->uc_civicrm_profile_paneData)) {
foreach ($order->uc_civicrm_profile_paneData as $field_name => $field_value) {
$result = db_query("SELECT count(*) as count FROM {uc_civicrm_profile_pane} WHERE uc_order_id = %d", $order->order_id);
if ($row = db_fetch_object($result)) {
if ($row->count > 0) {
// There is already an entry for this order (this is mostly to prevent the db table from filling up while I'm testing by hitting refresh on the order submit page.)
db_query("UPDATE {uc_civicrm_profile_pane} SET custom_field_value = '%s' WHERE uc_order_id = %d", $field_value, $order->order_id);
}
else {
// Write the custom field data off to the uc_civicrm_profile_pane table
db_query("INSERT INTO {uc_civicrm_profile_pane} (uc_order_id, custom_field_name, custom_field_value) VALUES (%d, '%s', '%s')", $order->order_id, $field_name, $field_value);
}
}
// Get the custom field id
$custom_field_id = _get_custom_field_id($field_name);
// Now get the table name and column name used to store the value for this custom field
$result = civicrm_db_query("SELECT ccf.column_name, ccg.table_name FROM {civicrm_custom_field} ccf LEFT JOIN {civicrm_custom_group} ccg ON ccf.custom_group_id = ccg.id WHERE ccf.id = %d", $custom_field_id);
while ($row = db_fetch_object($result)) {
civicrm_db_query("UPDATE {%s} SET %s = '%s' WHERE entity_id = %d", $row->table_name, $row->column_name, $field_value, $contact_id);
}
}
}
Bevan Rudge September 21, 2009
Very nice! The PHP snippets haven't been html-encoded properly. not the ">" instead of ">". Try using the <?php tags instead with the code filter.
xurizaemon September 9, 2009
Thanks AJ, this looks like a helpful addition. We currently do a fair bit of both Ubercart and CiviCRM work and look forward to more integration between the two packages in future releases. I've filed a couple of small issues with patches on the project page, hope they are to your liking. Cheers!
What versions civicrm are you running on the site? We have a client that needs to track individual downloads and purchases, so I am using the uc_civicrm integration module added to ubercart. The problem is it only works in civicrm 2.2.2
From my cursory trolling it seems there is a need to maintain some relationships between civicrm contacts and resources/purchases/downloads through ubercart. For things that don't really need to be purchased, there might be other ways to do this, but I'm just starting with civicrm. Anyway, good work on this module, I'll take a look at it too.