webform_report.inc in Webform Report 7
Same filename and directory in other branches
This file contains common functions and functions required to ouput a report for the webform report module
File
webform_report.incView source
<?php
/**
* @file
* This file contains common functions and functions required
* to ouput a report for the webform report module
*/
/**
* Search for nodes of type webform and returns their nids and titles
* in an associative array.
*/
function _webform_report_get_webforms() {
$webforms = array();
$webform_types = webform_variable_get('webform_node_types');
if ($webform_types) {
$result = db_select('node', 'n')
->fields('n')
->condition('n.type', $webform_types, 'IN')
->execute();
while ($record = $result
->fetchObject()) {
$webforms[$record->nid] = t($record->title);
}
}
return $webforms;
}
/**
* Get webform components.
*
* @param nid
* the node id of a webform
* @return
* an associative array of webform components
*/
function _webform_report_get_components($nid) {
$components = array();
// include meta components
$components[-1] = array(
'name' => t('Submitted By User'),
'type' => 'string',
);
$components[-2] = array(
'name' => t('Submission Date'),
'type' => 'date',
);
$components[-3] = array(
'name' => t('Submission Time'),
'type' => 'time',
);
$components[-4] = array(
'name' => t('Submission IP Address'),
'type' => 'string',
);
$components[-5] = array(
'name' => t('Edit Link'),
'type' => 'link',
);
$components[-6] = array(
'name' => t('View Link'),
'type' => 'link',
);
$components[-7] = array(
'name' => t('Delete Link'),
'type' => 'link',
);
// get components from webform
$result = db_query("SELECT c.cid, c.name, c.type \n FROM {webform_component} c \n WHERE c.type <> 'fieldset' AND c.nid = :nid \n ORDER BY c.weight", array(
':nid' => $nid,
));
foreach ($result as $row) {
$components[$row->cid] = array(
'name' => filter_xss($row->name),
'type' => $row->type,
);
}
return $components;
}
/**
* return an associative array containing the webform report columns
*
* @param node
* the webform report node
* @param components
* array of webform components
* @return
* an associative array of webform report columns
*/
function _webform_report_get_columns($node, $components) {
// build list of columns on the report
$columns = array();
if (is_array($node->columns)) {
for ($i = 0; $i < count($node->columns); $i++) {
$cid = $node->columns[$i]['cid'];
if (array_key_exists($cid, $components)) {
$columns[] = array(
'name' => $components[$cid]['name'],
'type' => $components[$cid]['type'],
'format' => $node->columns[$i]['format'],
'option' => isset($node->columns[$i]['option']) ? $node->columns[$i]['option'] : '',
'cid' => $node->columns[$i]['cid'],
'hidden' => isset($node->columns[$i]['hidden']) ? $node->columns[$i]['hidden'] : FALSE,
'num' => isset($node->columns[$i]['num']) ? $node->columns[$i]['num'] : FALSE,
'weight' => isset($node->columns[$i]['weight']) ? $node->columns[$i]['weight'] : 0,
);
}
}
}
return $columns;
}
/**
* return an associative array containing the webform report filters
*
* @param node
* the webform report node
* @param components
* array of webform components
* @return
* an associative array of webform report filters
*/
function _webform_report_get_filters($node, $components) {
// build list of filters
$filters = array();
if (is_array($node->filters)) {
for ($i = 0; $i < count($node->filters); $i++) {
$cid = $node->filters[$i]['field'];
if (array_key_exists($cid, $components)) {
$filters[] = array(
'name' => $components[$cid]['name'],
'type' => $components[$cid]['type'],
'ftype' => $node->filters[$i]['type'],
'value' => $node->filters[$i]['value'],
'cid' => $node->filters[$i]['field'],
'weight' => isset($node->filters[$i]['weight']) ? $node->filters[$i]['weight'] : 0,
);
}
}
}
// check for search values - add filters for search
$search =& $_SESSION['search_form'][$node->nid];
if (is_array($search)) {
$col = $search['column'];
$val = $search['value'];
if ($val != '') {
$columns = _webform_report_get_columns($node, $components);
// if all columns, add search filter for each column
if ($col == -1) {
foreach ($columns as $index => $column) {
// exclude special links (view, edit, etc)
// and hidden columns
if ($column['cid'] > -5 && !$column['hidden']) {
$filters[] = array(
'name' => '',
'type' => $column['type'],
'ftype' => -1,
'value' => $val,
'cid' => $column['cid'],
);
}
}
}
else {
if (array_key_exists($col, $columns)) {
$filters[] = array(
'name' => '',
'type' => $columns[$col]['type'],
'ftype' => -1,
'value' => $val,
'cid' => $columns[$col]['cid'],
);
}
}
}
}
return $filters;
}
/**
* return an array containing the webform report filter options
*
* @return
* an array of webform report filter options
*/
function _webform_report_get_filter_options($datetimeonly = FALSE) {
$opt = array();
$opt[0] = t('none');
if (!$datetimeonly) {
$opt[1] = t('begins with');
$opt[2] = t('does not begin with');
$opt[3] = t('contains');
$opt[4] = t('does not contain');
}
$opt[5] = t('equals');
$opt[6] = t('does not equal');
$opt[7] = t('is empty');
$opt[8] = t('is not empty');
$opt[9] = t('greater than');
$opt[10] = t('less than');
$opt[11] = t('greater than or equal');
$opt[12] = t('less than or equal');
$opt[13] = t('between');
return $opt;
}
/**
* return an associative array containing the webform report sort criteria
*
* @param node
* the webform report node
* @param components
* array of webform components
* @return
* an associative array of webform report sort criteria
*/
function _webform_report_get_sorton($node, $components) {
// build list of sort parameters
$sorton = array();
if (is_array($node->sorton)) {
for ($i = 0; $i < count($node->sorton); $i++) {
$cid = $node->sorton[$i]['cid'];
if (array_key_exists($cid, $components)) {
$sorton[] = array(
'name' => $components[$cid]['name'],
'type' => $components[$cid]['type'],
'order' => $node->sorton[$i]['order'],
'cid' => $node->sorton[$i]['cid'],
'weight' => isset($node->filters[$i]['weight']) ? $node->filters[$i]['weight'] : 0,
);
}
}
}
return $sorton;
}
/**
* Get submission data for the specified webform.
*
* @param node
* the current node object
* @return
* a database query result set
*/
function _webform_report_get_submissions($node) {
if (isset($node->wnid)) {
return db_query("\n SELECT w.nid, c.name, c.cid, d.nid, d.sid, d.data, s.uid, u.name as user, s.submitted, s.remote_addr\n FROM {webform} w\n LEFT JOIN {webform_submitted_data} d ON w.nid = d.nid\n LEFT JOIN {webform_component} c ON d.cid = c.cid\n LEFT JOIN {webform_submissions} s ON d.sid = s.sid\n LEFT JOIN {users} u ON s.uid = u.uid\n WHERE d.nid = c.nid \n AND c.nid = s.nid\n AND s.nid = :nid\n ORDER BY d.sid, c.cid, d.no", array(
':nid' => $node->wnid,
));
}
else {
return NULL;
}
}
/**
* Get node body content for the specified webform report.
*
* @param node
* the current node object
* @return
* a string of text or a themed table
*/
function _webform_report_get_body_content($node, $view_mode) {
global $user;
// get report data
$report = _webform_report_get_report_data($node);
// webform report does not have any criteria
if (!isset($report['headers'])) {
$output = t('It appears that no criteria have been specified for this report.
Please click on Report Criteria under Edit to add webform data to your report.');
}
elseif (!isset($report['rows'])) {
$output = t('There are no submissions for the selected webform. Either the form
has not yet been completed by anyone, or the results have been cleared. This will not
prevent you from creating this report, but this message will be displayed on the report
page until someone submits the selected webform.');
}
else {
$output = '';
// display search form if desired
if (isset($node->options['search_form']) && $node->options['search_form'] && $view_mode == 'full') {
// do not show search form to anonymous users if caching is enabled
if ($user->uid > 0 || !$GLOBALS['conf']['cache']) {
$output .= drupal_render(drupal_get_form('webform_report_search_form', $node));
}
}
// Display number of rows after description.
$output .= filter_xss_admin($node->description) . ' (' . count($report['rows']) . ' ' . t('results') . ') ';
if (!$node->options['hide_csv']) {
$output .= l(t('Download as CSV'), 'node/' . arg(1) . '/csv') . '</p>';
}
// output current page
$output .= _webform_report_pager($report['headers'], $report['rows'], $node);
// no submissions met criteria
if (count($report['rows']) == 0) {
$output .= t('There are no submissions that match the criteria for the selected webform.');
}
}
return $output;
}
/**
* Get webform report data
*
* @param node
* the current node object
* @return
* report array - keys 'headers' = report headers, 'rows' = report rows
*/
function _webform_report_get_report_data($node) {
$report = array();
// get report criteria
$components = _webform_report_get_components($node->wnid);
$columns = _webform_report_get_columns($node, $components);
// if any columns
if (count($columns) > 0) {
$headers = array();
$fields = array();
// set column headers and field info
foreach ($columns as $index => $col) {
if ($col['hidden'] != TRUE) {
// set report header - also save type for later
$headers[] = array(
'data' => $col['name'],
'field' => $col['cid'],
'type' => $col['type'],
);
}
// fields by cid for quick lookup
$fields[$col['cid']] = $col['name'];
// Get mapping for select lists (Start)
if ($col['type'] == 'select') {
// get component info
$result = db_query("SELECT c.extra FROM {webform_component} c WHERE c.nid = :nid AND c.cid = :cid", array(
':nid' => $node->wnid,
':cid' => $col['cid'],
));
$r = $result
->fetchObject();
$extra = unserialize($r->extra);
// add missing key to fix php notice
if (!array_key_exists('options_source', $extra)) {
$extra['options_source'] = '';
}
// load the webform select component handler
module_load_include('inc', 'webform', 'components/select');
// detect webforms 3.x and handle
if (function_exists('_webform_select_options_callback')) {
// make a webform "component"
$wfcomp['extra'] = $extra;
// get select options
$columns[$index]['pairs'] = _webform_select_options($wfcomp);
}
else {
if (strpos($extra['items'], '|')) {
$columns[$index]['pairs'] = array();
foreach (explode("\n", $extra['items']) as $pair) {
list($k, $v) = explode('|', $pair);
$columns[$index]['pairs'][$k] = $v;
}
}
}
}
// end - if ($col['type'] == 'select')...
// Get mapping for select lists (End)
}
// end - foreach ($columns as $index => $col)...
// query submissions
$rs = _webform_report_get_submissions($node);
if ($rs) {
// get other report criteria
$filters = _webform_report_get_filters($node, $components);
$sorton = _webform_report_get_sorton($node, $components);
// init values
$rows = array();
$csid = 0;
// add filter fields to lookup
foreach ($filters as $index => $filter) {
// fields by cid for quick lookup
$fields[$filter['cid']] = $filter['name'];
}
// submission counter
$sc = 0;
// get column data
while (TRUE) {
// get next submission data
$row = $rs
->fetchObject();
// check for end of data or end of submission
if (!$row || $row->sid != $csid) {
// process data for this submission, if any
if ($csid != 0) {
// test submission against filters
if (_webform_report_test_filters($data, $filters, $columns)) {
// output row if filters pass
$rows[] = _webform_report_output($data, $columns);
}
}
// if end of submission data, exit
if (!$row) {
break;
}
// count submission
$sc++;
// set current submission id
$csid = $row->sid;
// empty raw data row
$data = array();
// save submitter uid and node nid
$data['uid'] = $row->uid;
$data['nid'] = $row->nid;
// fill in meta fields
if (array_key_exists(-1, $fields)) {
$data[-1] = array(
l($row->user, 'user/' . $row->uid),
);
}
if (array_key_exists(-2, $fields)) {
$data[-2] = array(
$row->submitted,
);
}
if (array_key_exists(-3, $fields)) {
$data[-3] = array(
$row->submitted,
);
}
if (array_key_exists(-4, $fields)) {
$data[-4] = array(
$row->remote_addr,
);
}
if (array_key_exists(-5, $fields)) {
$data[-5] = array(
l(t('edit'), 'node/' . $row->nid . '/submission/' . $row->sid . '/edit'),
);
}
if (array_key_exists(-6, $fields)) {
$data[-6] = array(
l(t('view'), 'node/' . $row->nid . '/submission/' . $row->sid),
);
}
if (array_key_exists(-7, $fields)) {
$data[-7] = array(
l(t('delete'), 'node/' . $row->nid . '/submission/' . $row->sid . '/delete'),
);
}
}
// if component is on report
if (array_key_exists($row->cid, $fields)) {
// add raw data
$data[$row->cid][] = $row->data;
}
}
// end - while (TRUE)...
// see if any rows are available
if (count($rows) > 0) {
// sort
_webform_report_sort($headers, $rows, $sorton, $columns);
// execute php code, if given
if (isset($node->options['php_code'])) {
eval('?>' . $node->options['php_code']);
}
}
// save rows if any submissions were returned
if ($sc > 0) {
$report['rows'] = $rows;
}
}
// end - if (!empty($rs))...
$report['headers'] = $headers;
}
// end - if (count($columns) > 0)...
return $report;
}
/**
* test data against filters
*
* @param data
* array of raw submission data
* @param filters
* list of filters to test against data
* @return
* TRUE if filters passed, otherwise FALSE
*/
function _webform_report_test_filters($data, $filters, $columns) {
// filter result, return true if no filters
$ok = TRUE;
// if any filters
if (count($filters) > 0) {
// search flag
$hit = FALSE;
// loop through all filters
foreach ($filters as $index => $filter) {
// reset result for each filter
$ok = FALSE;
// check that cid is in data
if (array_key_exists($filter['cid'], $data)) {
// get raw data
$raw = $data[$filter['cid']];
// format value
foreach ($columns as $col) {
if ($col['cid'] == $filter['cid']) {
break;
}
}
$value = _webform_report_format_data($raw, $col);
// prepare filter values
$filter_data = strip_tags(strtolower(trim($value['data'])));
$filter_value = $filter['value'];
// Do token substitution - after checking whether the
// value might even contain a token
if (strpos($filter_value, '[') !== FALSE) {
// load submitter user and webform node if not done
if (!isset($submitter)) {
$submitter = user_load($data['uid']);
}
if (!isset($wfnode)) {
$wfnode = node_load($data['nid']);
}
// replace globals
$filter_value = token_replace($filter_value);
// replace submitter user values and webform node values
$filter_value = token_replace($filter_value, array(
'user' => $submitter,
'node' => $wfnode,
));
}
// normalize
$filter_value = strtolower(trim($filter_value));
// extract filter values for between
if ($filter['ftype'] == 13) {
$filter_value = explode('|', $filter_value);
// should be two values
if (count($filter_value) == 1) {
$filter_value[] = '';
}
}
// flag eq, ne, gt, lt, ge, le, between compares
$cmpflag = $filter['ftype'] == 5 || $filter['ftype'] == 6 || $filter['ftype'] == 9 || $filter['ftype'] == 10 || $filter['ftype'] == 11 || $filter['ftype'] == 12 || $filter['ftype'] == 13;
// see if special date and time processing is required
if ($filter['type'] == 'date' && $cmpflag) {
// user entered date
if ($filter['cid'] > 0) {
// dates are stored as yyyy-mm-dd
$filter_data = new DateTime($raw[0]);
}
else {
$filter_data = new DateTime();
$filter_data
->setTimeStamp($raw[0]);
}
}
if ($filter['type'] == 'time' && $cmpflag) {
// user entered time - stored as hh:mm:ss
if ($filter['cid'] > 0) {
$filter_data = new DateTime($raw[0]);
}
else {
$filter_data = new DateTime();
$filter_data
->setTimeStamp($raw[0]);
}
}
// convert filter value to timestamp if necessary
if (($filter['type'] == 'date' || $filter['type'] == 'time') && $cmpflag) {
if (is_array($filter_value)) {
$filter_value[0] = new DateTime($filter_value[0]);
$filter_value[1] = new DateTime($filter_value[1]);
}
else {
$filter_value = new DateTime($filter_value);
}
}
// extract filename for file types
if ($filter['type'] == 'file') {
$filter_data = '';
if (is_numeric($raw[0])) {
// must load the webform file component handler
module_load_include('inc', 'webform', 'components/file');
$file = webform_get_file($raw[0]);
if (!empty($file->fid)) {
$filter_data = webform_file_name($file->filename);
}
}
}
// convert to numeric if flagged and a non-string comparison
if ($col['num'] && $cmpflag) {
$filter_data = $filter_data + 0;
if ($filter['ftype'] == 13) {
$filter_value[0] = $filter_value[0] + 0;
$filter_value[1] = $filter_value[1] + 0;
}
else {
$filter_value = $filter_value + 0;
}
}
// apply filter
switch ($filter['ftype']) {
// search for x
case -1:
// convert submission date and time to strings
if ($filter['cid'] == -2 || $filter['cid'] == -3) {
$filter_data = new DateTime();
$filter_data
->setTimeStamp($raw[0]);
$filter_value = new DateTime($filter_value);
if ($filter_data && $filter_value) {
if ($filter['cid'] == -2) {
$filter_data = $filter_data
->setTime(0, 0, 0);
$filter_value = $filter_value
->setTime(0, 0, 0);
if ($filter_data == $filter_value) {
$hit = TRUE;
$ok = TRUE;
}
}
if ($filter['cid'] == -3) {
$dh = (int) $filter_data
->format('G');
$vh = (int) $filter_value
->format('G');
$dm = (int) $filter_data
->format('i');
$vm = (int) $filter_value
->format('i');
if ($dh == $vh && $dm == $vm) {
$hit = TRUE;
$ok = TRUE;
}
}
}
}
else {
if ($filter_data != '' && strpos($filter_data, $filter_value) !== FALSE) {
$hit = TRUE;
$ok = TRUE;
}
}
break;
// none
case 0:
$ok = TRUE;
// Begins with x.
case 1:
if (substr($filter_data, 0, drupal_strlen($filter_value)) == $filter_value) {
$ok = TRUE;
}
break;
// Does not begin with x.
case 2:
if (substr($filter_data, 0, drupal_strlen($filter_value)) != $filter_value) {
$ok = TRUE;
}
break;
// Contains x.
case 3:
if (!$filter_value || strpos($filter_data, $filter_value) !== FALSE) {
$ok = TRUE;
}
break;
// Does not contain x.
case 4:
if (!$filter_value || strpos($filter_data, $filter_value) === FALSE) {
$ok = TRUE;
}
break;
// Equals x.
case 5:
if ($filter_data == $filter_value) {
$ok = TRUE;
}
break;
// Does not equal x.
case 6:
if ($filter_data != $filter_value) {
$ok = TRUE;
}
break;
// is empty
case 7:
if (drupal_strlen($filter_data) == 0) {
$ok = TRUE;
}
break;
// is not empty
case 8:
if (drupal_strlen($filter_data) != 0) {
$ok = TRUE;
}
break;
// greater than
case 9:
if ($filter_data > $filter_value) {
$ok = TRUE;
}
break;
// less than
case 10:
if ($filter_data < $filter_value) {
$ok = TRUE;
}
break;
// greater than or equal
case 11:
if ($filter_data >= $filter_value) {
$ok = TRUE;
}
break;
// less than or equal
case 12:
if ($filter_data <= $filter_value) {
$ok = TRUE;
}
break;
// between
case 13:
// negate filter if both values are empty
if (!$filter_value[0] && !$filter_value[1] || $filter_data >= $filter_value[0] && $filter_data <= $filter_value[1]) {
$ok = TRUE;
}
break;
}
// end - switch($filter['type'])...
}
else {
if ($filter['ftype'] == 0) {
// pass it
$ok = TRUE;
}
}
// if hit on search, quit - search filters are last,
// so any report filters have already been applied
if ($hit) {
break;
}
// if last filter was search, keep going
if ($filter['ftype'] == -1) {
continue;
}
// if filter did not pass, don't check any further
if (!$ok) {
break;
}
}
// end - foreach($filters as $index => $filter)...
}
// end - if (count($filters) > 0)...
// return filter result
return $ok;
}
/**
* format output table row for report
*
* @param data
* raw submission data - array with key of cid, value an array of raw data
* @param columns
* list of report columns
* @return
* table row suitable for theme_table
*/
function _webform_report_output($data, $columns) {
$row = array();
// add each column to output row, applying formatting
foreach ($columns as $index => $col) {
// check if data exists
if (array_key_exists($col['cid'], $data)) {
// get raw data
$raw = $data[$col['cid']];
// add column to output row
$row[] = _webform_report_format_data($raw, $col);
}
else {
// no data, add dummy data
$row[] = array(
'data' => '',
'field' => $col['cid'],
'hidden' => $col['hidden'],
'sort' => '',
);
}
}
return $row;
}
/**
* format raw submission data
*
* @param raw
* array of raw submission data
* @param column
* the report column
* @return
* array of formatted table data
*/
function _webform_report_format_data($raw, $column) {
$out = '';
// handle file type
if ($column['type'] == 'file') {
// init variables
$link = '';
$text = '';
$path = '';
$text = '';
// new file handling in webforms 7.x-3.x - data is an id
if (is_numeric($raw[0])) {
// must load the webform file component handler
module_load_include('inc', 'webform', 'components/file');
$file = webform_get_file($raw[0]);
if (!empty($file->fid)) {
$text = webform_file_name(check_plain(webform_file_name($file->uri)));
$path = webform_file_url($file->uri);
$link = '<a href="' . $path . '">' . $text . '</a>' . ' (' . (int) ($file->filesize / 1024) . ' KB)';
}
}
else {
$tmp = unserialize($raw[0]);
if (!empty($tmp['filename'])) {
$text = $tmp['filename'];
$path = base_path() . $tmp['filepath'];
$link = '<a href="' . $path . '">' . $text . '</a>' . ' (' . (int) ($tmp['filesize'] / 1024) . ' KB)';
}
}
// output file column - save path for later
$out = array(
'data' => $link,
'field' => $column['cid'],
'sort' => $text,
'path' => $path,
);
}
elseif ($column['type'] == 'date') {
$ts = FALSE;
// if report date
if ($column['cid'] > 0) {
// date is stored as yyyy-mm-dd
$ts = new DateTime($raw[0]);
}
else {
$ts = new DateTime();
$ts
->setTimeStamp($raw[0]);
}
// if valid date
if ($ts) {
$format = $column['format'];
// set default format if none given
if (empty($format)) {
$format = 'Y-m-d';
}
// format the date
$out = array(
'data' => $ts
->format($format),
'field' => $column['cid'],
'sort' => $ts,
);
}
}
elseif ($column['type'] == 'time') {
$ts = FALSE;
if ($column['cid'] > 0) {
// date is stored as hh:mm:ss
$ts = new DateTime($raw[0]);
}
else {
// otherwise get submission timestamp
$ts = new DateTime();
$ts
->setTimeStamp($raw[0]);
}
// if valid timestamp
if ($ts) {
$format = $column['format'];
// default format if none given
if (empty($format)) {
$format = 'H:i';
}
// format the time
$out = array(
'data' => $ts
->format($format),
'field' => $column['cid'],
'sort' => $ts,
);
}
}
elseif ($column['type'] == 'select') {
$data = '';
$sort = '';
// process each key in case of multi-selects
for ($i = 0; $i < count($raw); $i++) {
if ($i > 0) {
$data .= ',';
$sort .= ',';
}
// save key value
$key = $raw[$i];
// display key if wanted or if no key/value pairs exist
if ($column['format'] == 'KEY' || !array_key_exists('pairs', $column)) {
$data .= $key;
}
else {
// set select value to friendly format (default)
$data .= isset($column['pairs'][$key]) ? $column['pairs'][$key] : '';
}
// set sort key - use key if selected
if ($column['option'] == 'KEY') {
$sort .= $key;
}
else {
if (array_key_exists($key, $column['pairs'])) {
$sort .= $column['pairs'][$key];
}
}
}
// save the value
$out = array(
'data' => $data,
'field' => $column['cid'],
'sort' => $sort,
);
}
else {
// get raw data
$tmp = filter_xss_admin(implode(',', $raw));
// build sort key
$sort = strip_tags($tmp);
// allow for accents if module is present
if (function_exists('_accents_remove_accents')) {
$sort = _accents_remove_accents($sort);
}
$out = array(
'data' => $tmp,
'field' => $column['cid'],
'sort' => $sort,
);
// add links
if (valid_email_address($out['data'])) {
$out['data'] = l($out['data'], 'mailto:' . $out['data']);
}
else {
if (valid_url($out['data'], TRUE)) {
$out['data'] = l($out['data'], $out['data']);
}
}
}
// pass hidden attribute to output
if ($column['hidden'] == TRUE) {
$out['hidden'] = TRUE;
}
// return formatted data
return $out;
}
/**
* sort row data
*
* anonymous sort comparison function creation adapted from:
* http://www.the-art-of-web.com/php/sortarray/
*
* @param headers
* array of report headers
* @param rows
* array of report rows
* @param sorton
* list of default sort parameters
*/
function _webform_report_sort(&$headers, &$rows, $sorton, $columns) {
// get current sort - 'name' key is column name, 'sql' is cid
$ts = tablesort_get_order($headers);
// get sort order
$ts_order = tablesort_get_sort($headers);
// if inital display (no column header has been clicked)
if (!isset($_GET['order'])) {
// if sort parameters given
if (count($sorton) > 0) {
// init variables
$a = array();
$cid = 0;
$ord = '';
// loop through sorts
foreach ($sorton as $sort) {
// find column to be sorted
foreach ($columns as $index => $col) {
if ($sort['cid'] == $col['cid']) {
// save first sort column
if ($cid == 0) {
$cid = $sort['cid'];
if ($sort['order'] == SORT_ASC) {
$ord = 'asc';
}
else {
$ord = 'desc';
}
}
// add the sort function
$a[] = _webform_report_sort_column($index, $sort['order'] == SORT_DESC, $sort['type'], $col['num']);
break;
}
}
}
// end - foreach ($sorton...
// flag the header with the first sort column
foreach ($headers as $index => $header) {
// mark the first sort header
if ($header['field'] == $cid) {
$headers[$index]['sort'] = $ord;
break;
}
// end - if ($header['field'])...
}
// end - foreach ($headers as...
if (count($a) == 0) {
// probably no columns
$code = 'return 0;';
}
elseif (count($a) == 1) {
// only one clause, return it
$code = 'return ' . $a[0] . ';';
}
else {
// multiple clauses, build descending compare function
$code = '';
for ($i = 0; $i < count($a); $i++) {
if ($i < count($a) - 1) {
$code .= 'if (($tmp = ' . $a[$i] . ') == 0) { ';
}
else {
$code .= '$tmp = ' . $a[$i] . '; ';
}
}
for ($i = 0; $i < count($a) - 1; $i++) {
$code .= ' } ';
}
$code .= 'return $tmp;';
}
}
// end - if (count($sorton) > 0)...
}
// end - if ($init)...
// user has clicked on a column header or no sort criteria specified
// must sort on current table sort column
if (!isset($code)) {
// find header to be sorted
foreach ($headers as $index => $header) {
if ($header['field'] == $ts['sql']) {
$headers[$index]['sort'] = $ts_order;
break;
}
}
// find column to be sorted
foreach ($columns as $index => $col) {
if ($col['cid'] == $ts['sql']) {
$code = 'return ' . _webform_report_sort_column($index, $ts_order == 'desc', $col['type'], $col['num']) . ';';
break;
}
}
}
// sort the rows
usort($rows, create_function('$a,$b', $code));
}
/**
* Generate a sort function for a column
*
* @param index
* the index of the field to sort
* @param desc
* if true sort is descending
* @param type
* the field type
* @return
* the sort function for the given column
*/
function _webform_report_sort_column($index, $desc = FALSE, $type = '', $num = FALSE) {
// create comparison based on type
// for dates and times compare timestamps
if ($type == 'date' || $type == 'time') {
$code = "(\$a[{$index}]['sort']>\$b[{$index}]['sort'])";
}
else {
if ($num) {
$code = "((\$a[{$index}]['sort']+0)-(\$b[{$index}]['sort']+0))";
}
else {
$code = "strcasecmp(\$a[{$index}]['sort'],\$b[{$index}]['sort'])";
}
}
// invert output for descending sort
if ($desc) {
$code = $code . '*-1';
}
$code = '(' . $code . ')';
return $code;
}
/**
* Display a list of webform reports in the admin area.
*
* @return
* a themed table
*/
function _webform_report_page() {
$output = NULL;
if (user_access('access webform reports')) {
$headers = array(
t('View'),
t('Edit'),
t('Delete'),
);
$rows = array();
$query = db_select('node', 'n');
$query
->fields('n');
$query
->condition('n.type', 'webform_report', '=');
$result = $query
->execute();
foreach ($result as $node) {
$rows[] = array(
l($node->title, 'node/' . $node->nid),
l(t('edit'), 'node/' . $node->nid . '/edit'),
l(t('delete'), 'node/' . $node->nid . '/delete'),
);
}
$output = theme('table', array(
'header' => $headers,
'rows' => $rows,
'attributes' => array(
'class' => array(
'webform',
),
),
));
}
return $output;
}
/**
* Paginate the webform report.
*
* @return
* a themed table with page links
*/
function _webform_report_pager($headers, $rows, $node) {
global $pager_page_array, $pager_total;
// get results per page setting - start with default
$results_per_page = 20;
// if value set, use that
if (array_key_exists('results_per_page', $node->options)) {
$results_per_page = $node->options['results_per_page'];
}
// Add the css file for form display.
drupal_add_css(drupal_get_path('module', 'webform_report') . '/webform_report.css');
// Break the array into chunks for pagination.
if ($results_per_page != 0) {
$pages = array_chunk($rows, $results_per_page, TRUE);
// get the current page
$page = pager_find_page();
}
else {
$pages[0] = $rows;
$page = 0;
}
// set report layout
$layout = 'REPORT';
if (array_key_exists('layout', $node->options)) {
$layout = $node->options['layout'];
}
// filter out hidden columns
$out = array();
if (count($rows) > 0 && is_array($pages[$page])) {
foreach ($pages[$page] as $row) {
$outrow = array();
foreach ($row as $cell) {
$hide = isset($cell['hidden']) ? $cell['hidden'] : FALSE;
if (!$hide) {
// remove cell attributes
if (is_array($cell)) {
unset($cell['hidden']);
unset($cell['sort']);
unset($cell['field']);
}
$outrow[] = $cell;
}
}
$out[] = $outrow;
}
}
if ($layout == 'REPORT') {
// format the table with the current page
$output = theme('table', array(
'header' => $headers,
'rows' => $out,
'attributes' => array(
'class' => array(
'webform_report',
),
),
));
}
else {
$output = webform_report_format_submissions($headers, $out);
}
// Add the pager to the output if needed
if ($results_per_page != 0) {
pager_default_initialize(count($rows), $results_per_page);
$output .= theme('pager');
}
return $output;
}
/**
* Output webform report submissions
*
* @param headers
* the field headers
* @param rows
* the fields
*/
function webform_report_format_submissions($headers, $rows) {
$out = array();
foreach ($rows as $row) {
$output = '';
for ($i = 0; $i < count($row); $i++) {
$output .= '<div class="webform-report-submission-cell">';
$output .= '<div class="webform-report-submission-header">';
if (is_array($headers[$i])) {
$output .= $headers[$i]['data'];
}
else {
$output .= $headers[$i];
}
$output .= '</div>';
$output .= '<div class="webform-report-submission-data">';
if (is_array($row[$i])) {
$output .= $row[$i]['data'];
}
else {
$output .= $row[$i];
}
$output .= '</div>';
$output .= '</div>';
}
$out[] = array(
$output,
);
}
return theme('table', array(
'rows' => $out,
'attributes' => array(
'class' => array(
'webform_report webform-report-submission',
),
),
));
}
/**
* Output a webform report in CSV format
*
* code adapted from Webform module
*
* @param node
* the current node
*/
function webform_report_csv($node) {
return drupal_get_form('webform_report_export_form', $node);
}
/**
* Generate a form for exporting report data
*
* @param form_state
* drupal form state
* @param node
* current node object
* @return
* an array of form elements
*/
function webform_report_export_form($form, $form_state, $node) {
$form = array();
// export fieldset
$form['export'] = array(
'#type' => 'fieldset',
'#title' => t('Export Options'),
);
$form['export']['nid'] = array(
'#type' => 'value',
'#value' => $node->nid,
);
$format = array(
'text' => t('Text'),
'excel' => t('Excel'),
);
$form['export']['format'] = array(
'#type' => 'radios',
'#title' => t('Export format'),
'#options' => $format,
'#default_value' => 'text',
);
$delim = array(
',' => t('Comma (,)'),
'\\t' => t('Tab (\\t)'),
';' => t('Semicolon (;)'),
':' => t('Colon (:)'),
'|' => t('Pipe (|)'),
'.' => t('Period (.)'),
' ' => t('Space ( )'),
);
$form['export']['delim'] = array(
'#type' => 'select',
'#title' => t('Delimiter'),
'#options' => $delim,
'#required' => TRUE,
);
$form['export']['submit'] = array(
'#type' => 'submit',
'#value' => t('Export'),
'#weight' => 1,
);
$form['return'] = array(
'#markup' => l(t('Return to Report'), 'node/' . $node->nid),
);
return $form;
}
/**
* Implementation of hook_submit for the report export form
*
* @param form_id
* drupal form id
* @param form_state
* drupal form state and values
*/
function webform_report_export_form_submit($form_id, $form_state) {
$nid = $form_state['values']['nid'];
$format = $form_state['values']['format'];
$delim = $form_state['values']['delim'];
$node = node_load($nid);
$report = _webform_report_get_report_data($node);
if (isset($report['headers']) && isset($report['rows'])) {
$output = _webform_report_output_csv($report['headers'], $report['rows'], $format, $delim);
if ($format == 'excel') {
$ftype = 'xls';
$ctype = 'application/x-msexcel';
}
else {
if ($delim == '\\t') {
$ftype = 'tsv';
$ctype = 'text/tab-separated-values';
}
else {
$ftype = 'csv';
$ctype = 'text/plain';
}
}
$fname = 'wfr_export.' . $ftype;
drupal_add_http_header('Content-Type', $ctype);
drupal_add_http_header('Content-Length', strlen($output));
drupal_add_http_header('Content-Disposition', 'attachment; filename="' . $fname . '"');
// Allow Internet Explorer to download Excel files over HTTPS
drupal_add_http_header('Cache-Control', 'private, must-revalidate');
echo $output;
die;
}
drupal_goto('/node/' . $nid);
}
/**
* Format webform report data as a CSV
*
* @param headers
* report headers
* @param rows
* report rows
* @param format
* report format
* @param delim
* report delimiter
* @return
* CSV output
*/
function _webform_report_output_csv($headers, $rows, $format = 'text', $delim = ',') {
$output = '';
// set delimiter to tab and output bof for excel
if ($format == 'excel') {
$delim = '\\t';
if (function_exists('mb_convert_encoding')) {
$output .= chr(255) . chr(254);
}
}
// set tab delimiter
if ($delim == '\\t') {
$delim = "\t";
}
// output headers
$tmp = array();
foreach ($headers as $header) {
$tmp[] .= _webform_report_format_csv_column($header['data'], $delim);
}
$output .= _webform_report_format_csv_row($tmp, $format, $delim);
// output rows
foreach ($rows as $row) {
$tmp = array();
foreach ($row as $cell) {
// output cell if not hidden
if (!array_key_exists('hidden', $cell) || $cell['hidden'] == FALSE) {
// special handling for file types - use entire path
if (array_key_exists('path', $cell)) {
$data = $cell['path'];
}
else {
$data = strip_tags($cell['data']);
}
$tmp[] = _webform_report_format_csv_column($data, $delim);
}
}
$output .= _webform_report_format_csv_row($tmp, $format, $delim);
}
return $output;
}
/**
* Format a CSV column value
*
* @param value
* the value to format for CSV
* @param delim
* the CSV delimiter
* @return
* CSV column
*/
function _webform_report_format_csv_column($value, $delim) {
// Replace single quotes with double quotes, replace CRLF with LF
$value = str_replace(array(
'"',
"\r\n",
), array(
'""',
"\n",
), $value);
return '"' . $value . '"';
}
/**
* Format a CSV row
*
* @param row
* an array of row values
* @param format
* the CSV file format
* @param delim
* the CSV delimiter
* @return
* the CSV row
*/
function _webform_report_format_csv_row($row, $format, $delim) {
$txt = implode($delim, $row) . "\n";
if ($format == 'excel' && function_exists('mb_convert_encoding')) {
$txt = mb_convert_encoding($txt, 'UTF-16LE', 'UTF-8');
}
return $txt;
}Functions
Name |
Description |
|---|---|
| webform_report_csv | Output a webform report in CSV format |
| webform_report_export_form | Generate a form for exporting report data |
| webform_report_export_form_submit | Implementation of hook_submit for the report export form |
| webform_report_format_submissions | Output webform report submissions |
| _webform_report_format_csv_column | Format a CSV column value |
| _webform_report_format_csv_row | Format a CSV row |
| _webform_report_format_data | format raw submission data |
| _webform_report_get_body_content | Get node body content for the specified webform report. |
| _webform_report_get_columns | return an associative array containing the webform report columns |
| _webform_report_get_components | Get webform components. |
| _webform_report_get_filters | return an associative array containing the webform report filters |
| _webform_report_get_filter_options | return an array containing the webform report filter options |
| _webform_report_get_report_data | Get webform report data |
| _webform_report_get_sorton | return an associative array containing the webform report sort criteria |
| _webform_report_get_submissions | Get submission data for the specified webform. |
| _webform_report_get_webforms | Search for nodes of type webform and returns their nids and titles in an associative array. |
| _webform_report_output | format output table row for report |
| _webform_report_output_csv | Format webform report data as a CSV |
| _webform_report_page | Display a list of webform reports in the admin area. |
| _webform_report_pager | Paginate the webform report. |
| _webform_report_sort | sort row data |
| _webform_report_sort_column | Generate a sort function for a column |
| _webform_report_test_filters | test data against filters |