0

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!!

0

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!!!

0

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.

0

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); }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.