How to customise Webform results table

Question

So its Christmas eve and this is really dampening the Christmas spirit.

I need to be able to customise the submissions results page table to show the county taxonomy for the submission user. I have never really done anything with SQL before but I can get all the information I need by running the below select query in phpMyAdmin:

SELECT `sid`, `nid`, `serial`, `uid`, `is_draft`, `submitted`, `remote_addr`, 
field_data_field_counrty.field_counrty_tid as CountryID ,

(select name from taxonomy_term_data where taxonomy_term_data.tid=field_data_field_counrty.field_counrty_tid) as Country


FROM `webform_submissions` 

inner join field_data_field_counrty  on webform_submissions.uid =field_data_field_counrty.entity_id

And i know that the below code is where it generates the submissions table in webform.submissions.inc:

// Build the list of submissions and load their basic information.
$pager_query = db_select('webform_submissions', 'ws')
// Ensure only one row per submission is returned. Could be more than one if
// sorting on a column that uses multiple rows for its data.
->distinct()
->addTag('webform_get_submissions_sids')
->fields('ws', array('sid', 'nid', 'serial', 'submitted', 'remote_addr', 'uid', 'Country', 'is_draft'));
foreach ($filters as $column => $value) {
$pager_query->condition($column, $value);
}

// Join to the users table to include user name in results.
$pager_query->leftJoin('users', 'u', 'u.uid = ws.uid');
$pager_query->fields('u', array('name'));
if (isset($filters['u.uid']) && $filters['u.uid'] === 0) {
if (!empty($_SESSION['webform_submission'])) {
  $anonymous_sids = array_keys($_SESSION['webform_submission']);
  $pager_query->condition('sid', $anonymous_sids, 'IN');
}
else {
  $pager_query->condition('sid', 0);
}
}

But i have no idea what to do from there. If anyone could offer any advice, tips or anything, I would really appreciate the help.

Happy holidays everyone!!

Answer 1

OK, so I solved it myself.

Open and edit the webform.submissions.inc file in the includes folder of the module

In the Return all the submissions for a particular node. section find the code with the comment "// Join to the users table to include user name in results."

 // Join to the users table to include user name in results.
  $pager_query->leftJoin('users', 'u', 'u.uid = ws.uid');
  $pager_query->fields('u', array('name'));
  if (isset($filters['u.uid']) && $filters['u.uid'] === 0) {
    if (!empty($_SESSION['webform_submission'])) {
      $anonymous_sids = array_keys($_SESSION['webform_submission']);
      $pager_query->condition('sid', $anonymous_sids, 'IN');
    }
    else {
      $pager_query->condition('sid', 0);
    }
  }

To add my country section I had to find the table in phpMyAdmin that contained my taxonomy data. In this case it was "field_data_field_counrty" (I've spelled country wrong i know but I couldn't change it after).

From that I figured out that the entity ID was the the user ID in the Users table. This is because I had used the country taxonomy on the registration form.

The syntax for the inner join query is 'Table, Table Alias, Condition, Arguments'. From that my innerjoin became,

 $pager_query->innerJoin('field_data_field_counrty ', 'fld_Country', 'fld_Country.entity_id = ws.uid' );

The field syntax is 'Table Alias, Field Name', so my field selection became,

$pager_query->fields('fld_Country', array('field_counrty_tid'));

This gave me the country ID number that had been assigned to the taxonomy term. I then had to cross reference that field with the taxonomy data table to retrieve the name of the country the ID referred to. That was done with another innerjoin.

$pager_query->innerJoin('taxonomy_term_data ', 'taxonomy_Country', 'taxonomy_Country.tid = fld_Country.field_counrty_tid' );

This time the field I needed to retrieve was called name. However, 'name' had already been used further up in the original code for the user name. So to add an alias to the field I had to use addField instead of fields. The syntax for addField is 'Table Alias, Field, Alias'. My code became,

 $pager_query->addField('taxonomy_Country', 'name', 'tax_name');

The code as a whole is:

 // Join to the field country table to include Country ID in results.
  $pager_query->innerJoin('field_data_field_counrty ', 'fld_Country', 'fld_Country.entity_id = ws.uid' );
  $pager_query->fields('fld_Country', array('field_counrty_tid'));
  $pager_query->innerJoin('taxonomy_term_data ', 'taxonomy_Country', 'taxonomy_Country.tid = fld_Country.field_counrty_tid' );
  $pager_query->addField('taxonomy_Country', 'name', 'tax_name');

Further down in the code is the section:

 $result = $pager_query->execute();

  $sids = array();
  foreach ($result as $row) {
    $sids[] = $row->sid;
    $submissions[$row->sid] = new stdClass();
    $submissions[$row->sid]->sid = $row->sid;
    $submissions[$row->sid]->nid = $row->nid;
    $submissions[$row->sid]->serial = $row->serial;
    $submissions[$row->sid]->submitted = $row->submitted;
    $submissions[$row->sid]->remote_addr = $row->remote_addr;
    $submissions[$row->sid]->uid = $row->uid;
    $submissions[$row->sid]->name = $row->name;
    $submissions[$row->sid]->is_draft = $row->is_draft;
    $submissions[$row->sid]->preview = FALSE;
    $submissions[$row->sid]->data = array();

In this section I added the line:

        $submissions[$row->sid]->CountryID = $row->tax_name;

To display the data, we now need to edit the webform.report.inc file. Find the section near the top that says,

 foreach ($submissions as $sid => $submission) {
    $row = array(
      $submission->is_draft ? t('@serial (draft)', array('@serial' => $submission->serial)) : $submission->serial,
      format_date($submission->submitted, 'short'),

    );
    if (webform_results_access($node, $user)) {
      $row[] = theme('username', array('account' => $submission));
      $row[] = $submission->remote_addr;

I then added the following line after the format_date section to add my new column after the submitted column.

      $submission->CountryID,

To add the header for the new column find the section with the comment,

 * Theme the header of the submissions table.

I then added the following line after the submitted section,

    array('data' => t('Country'), 'field' => 'tax_name'),

Save both files and view a webform results page (providing it has a submission). Your new column should now display.

I hope this helps anyone else that is confused on this subject!

Happy new year everyone!!!

Answer 2

Also, it turns out, that because I was using a customised version of Webforms, I didnt realise you could make this as a view-block.

Now I just feel stupid.

Answer 3

function webformmanager_menu_alter(&$items) { $items['node/%webform_menu/webform-results/table']['page callback'] = 'webform_results_table_new'; }

function webform_results_table_new($node, $pager_count = 0) { if($node->nid==16650 || $node->nid==16654 || $node->nid==16655){ $shiftitems = array(); $components = array(); if($node->nid==16650){ $shiftitems = array(37, 35, 7, 27, 28, 29, 36); } elseif($node->nid==16654){ $shiftitems = array(31, 35, 34, 26, 28, 29, 27); } elseif($node->nid==16655){ $shiftitems = array(50, 35, 7, 48, 27, 47, 28, 29); } if(!empty($shiftitems)){ foreach($shiftitems as $val){ $movecomponent = $node->webform['components'][$val]; $components[$val] = $node->webform['components'][$val]; unset($node->webform['components'][$val]); } foreach($node->webform['components'] as $key => $val){ $components[$key] = $val; } $node->webform['components'] = $components; } } return webform_results_table($node, $pager_count); }