<?php  // $Id: webform.inc,v 1.16.2.3 2005/06/18 08:14:10 ullgren Exp $  /**    * @author Pontus Ullgren <ullgren@user.sourceforge.net>   * @package module_webform   * @copyright Pontus Ullgren 2004   **/function _webform_page() {  $header = array(                  t('Title'),                   array('data' => t('View'),                        'colspan' => '3'),                  array('data' => t('Operations'),                        'colspan' => '3')                  );  $result = db_query("SELECT nid, title FROM {node} WHERE type='webform'");     while ($node = db_fetch_object($result)) {    $rows[] = array($node->title,                    l(t('submissions'),'node/' . $node->nid . '/results'),                    l(t('analysis'),'node/' . $node->nid . '/results/analysis'),                    l(t('table'),'node/' . $node->nid . '/results/table'),                    l(t('download'),'node/' . $node->nid . '/results/download'),                    //l(t('edit'), 'node/'.$node->nid.'/edit'),                    l(t('clear'), 'node/' . $node->nid . '/results/clear'));  }  $content = theme('table', $header, $rows);  drupal_set_title($title);  print theme('page', $content);} // end function _webform_page/* * Delete all submission for a form * @param      integer ID of node for which to clear submissions */function _webform_results_clear($nid) {  if ($_POST['edit']['confirm']) {    $query = 'DELETE FROM {webform_submitted_data} WHERE nid = %d';    $res = db_query($query, $nid);    $node = node_load(array('nid' => $nid));    $title = $node->title;    watchdog('webform','webform "' . $title . '" entries cleared.', WATCHDOG_NOTICE);    drupal_goto('webform');  }  else {    $content = theme('confirm', t('Do you really want to delete all submissions for this form?'), 'webform',                     t('Do you really want to delete <strong>all</strong> submissions for this form?'). '<br>'.t('This action cannot be undone.'),                      'yes', 'no', NULL);    return $content;  }}/** Delete one form submission* @param	integer	ID of node for which this webform was submitted* @param	integer	ID of submission to be deleted (from webform_submitted_data)*/function _webform_submission_delete($nid, $sid) {  if ($_POST['edit']['confirm']) {    $query = 'DELETE FROM {webform_submitted_data} WHERE nid = %d AND sid = %d';    $res = db_query($query, $nid, $sid);    drupal_goto('node/'.$nid.'/results');  }  else {    $content = theme('confirm', t('Do you really want to delete this form submission?'), 'node/'.$nid.'/results',                      t('Do you really want to delete this form submission?').'<br>'.t('This action cannot be undone.'),                      'yes', 'no', NULL);    return $content;  }}function _webform_results_download($nid) {      /**       * The CSV requires that the data be presented in a flat file.  In order        * to maximize useability to the Excel community and minimize subsequent        * stats or spreadsheet programming this program extracts data from the        * various records for a given session and presents them as a single file        * where each row represents a single record.       * The structure of the file is:       *   Heading Line 1: Gives group overviews padded by empty cells to the        *                   next group.  A group may be a question and corresponds        *                   to a component in the webform philosophy. Each group        *                   overview will have a fixed number of columns beneath it.       *   Heading line 2: gives column headings       *   Data line 1 .....       *   Data line 2 .....       *          * An example of this format is given below.  Note the columns have had spaces        * added so the columns line up.  This is not the case with actual file where        * a column may be null.  Note also, that multiple choice questions as produced       * by checkboxes or radio buttons have been presented as "yes" or "no" and the       * actual choice text is retained only in the header line 2.       * Data from text boxes and input fields are written out in the body of the table.       *       *   Submission Details,    ,   ,      ,Question 1,        ,        ,..,        ,Question 2,        ,        ,..,        ,Question n       *   timestamp         ,time,SID,userid,Choice 1  ,Choice 2,Choice 3,..,Choice n,Choice 1  ,Choice 2,Choice 3,..,Choice n,Comment       *   21 Feb 2005       ,1835,23 ,34    ,Yes       ,No      ,No      ,..,No      ,Yes       ,Yes     ,Yes     ,..,Yes     ,My comment       *   23 Feb 2005       ,1125,24 ,89    ,Yes       ,Yes     ,No      ,..,No      ,Yes       ,Yes     ,Yes     ,..,Yes     ,Hello       *   ...............................................................................................................       *   27 Feb 2005       ,1035,56 ,212   ,Yes       ,No      ,No      ,..,No      ,Yes       ,No      ,Yes     ,..,Yes     ,How is this?       **/  $node_info = _webform_get_node_info($nid);  $title_string = $node_info->title;  $file_name = _webform_records_string($title_string,$nid,'file'); // This will default to printing the output which saves stack space  drupal_set_header("Content-type: text/plain; charset=utf-8");  drupal_set_header("Content-Disposition: attachment; filename=" . preg_replace('/\.$/', '', str_replace(' ', '_', $title_string)) . ".csv");  @readfile($file_name);  // The @ makes it silent  @unlink($file_name);  // Clean up, the @ makes it silent  exit(0);}/** function _webform_results_submissions() is a database accessor function designed to return lists *  of submissions and data. * @param $nid the node id of the webform */function _webform_results_submissions($nid) {  $node_info = _webform_get_node_info($nid);  $title_string = $node_info->title;  $content = _webform_records_string($title_string,$nid,'submission_list');  return $content;}function _webform_results_table($nid) {      $first = true;      $previous = -1;      $cell = array();  // Get all the submitted values for the node  $query = 'SELECT sd.sid as sid, c.cid as cid, sd.name as name, sd.data as data'.    ' FROM '.    ' {webform_submitted_data} sd, {webform_component} c '.    ' WHERE sd.nid = c.nid '.    ' AND sd.nid = %d '.    ' GROUP BY sd.sid, sd.name '.    ' ORDER BY sd.sid, c.cid ';  $res = db_query($query, $nid);      $header[] = t('#');      while ($field = db_fetch_object($res)) {        if ( ($previous != -1)              && ($previous != $field->sid)) {                    $rows[] = array_merge(array($previous), $cell);          unset($cell);          $first = false;        }        if($first) {          $header[] = $field->name;        }                if ( $field->name == '__timestamp' ) {          $cell[] = format_date($field->data, 'small');        }        else if ( unserialize($field->data) ) {          $cell[] = theme('item_list', unserialize($field->data));        }        else {          $cell[] = $field->data;        }        $previous = $field->sid;      }      // and the last one ...      $rows[] = array_merge(array($previous), $cell);  return theme('table', $header, $rows);}/** _webform_records_string - Decomposes the database to a flat file for a given SID. * @param $title - Title for this report. Will be printed as a heading if appropriate. * @param $nid - The drupal node id representing the webform data that is to be extracted. * @param $print_now Whether to print the result as it is formed or store it to a string and return it. * @param $new_line - A string to be used to render a new line.  Defaults for print on windows based systems * @return a reference to a string representing the file in csv format. **/function _webform_records_string($title_string,$nid,$mode = 'file',$new_line = "\r\n"){  // Because the components may have changed during the lifestyle of the form  // and because we may be using a low version of MySQL et al. The query is done in two  // passes.  This would be more efficiently done using a VIEW or SUB-SELECT but we cannot guarantee  // that it is available.  // The first query identifies the characteristics of components that are in the data set  $component_query = 'SELECT wc.cid as cid, wc.name as name, wc.type as type, wc.value as value, '.    '                        wc.extra as extra, wc.mandatory as mandatory, wc.weight as weight '.    'FROM {webform_component} wc '.    'WHERE wc.nid = %d '.    'ORDER BY wc.weight, wc.name';  $component_result = db_query($component_query, $nid);  // First we populate it with the common record variables  $record_array = array();  $record_array[0]['serial'] = "Serial";  $record_array[0]['__timestamp'] = "Unix";  $record_array[0]['sid'] = "sid";  $record_array[0]['__userid'] = "userid";  $record_array[0]['__remotehost'] = "remotehost";  $record_array[0]['__useragent'] = "useragent";  $record_array[0]['components'] = array();  $record_array[0]['index'] = array(); // Converts between name and index;  $header[0] =  "$title_string,,,,"; // The inner quotes allow the title to include commas  $header[1] .= "Submission Details,,,,";  $header[2] .= "Serial,Timestamp,SID,User,Host";  $index = 0; // Ensures they retain the correct order  //and second the field structure that is unique to this node  while ($record = db_fetch_object($component_result)) {    // It would seem more logical to index on 'cid' rather than 'name' however    // webform_submited_data does not store cid.    $record_array[0]['index'][$record->name] = ++$index;    $record_array[0]['components'][$index]['cid'] = $record->cid;    $record_array[0]['components'][$index]['type'] = $record->type;    $record_array[0]['components'][$index]['value'] = $record->value;    $header[0] .= ',';    $header[1] .= ',' . $record->name;    if($record->type == 'select'){      $record_array[0]['components'][$index]['options'] = unserialize($record->extra);  // This will create the sub dimensions ['multiple'] and ['items']      $items = explode("\n", _webform_filtervalues($record_array[0]['components'][$index]['options']['items']));      $record_array[0]['components'][$index]['options']['items'] = $items;            foreach($record_array[0]['components'][$index]['options']['items'] as $key =>$item){        $record_array[0]['components'][$index]['options']['items'][$key] = trim($item);  // Trim off any linefeeds etc. and put them back in the same place      }            $header[0] .= str_pad('', count($items) - 1,',');      $header[1] .= str_pad('', count($items) - 1,',');            foreach($items as $item){        //print trim($item);        $header[2] .= ',' . '"'.trim($item) . '"';	// The quotes are added so the items can contain commas.      }          } else {      $record_array[0]['components'][$index]['data'][0] = '';      $header[2] .= ',';    }        $record_array[0]['components'][$index]['extra'] = $record->extra;    $record_array[0]['components'][$index]['mandatory'] = $record->mandatory;  }    // Supplement the $index_array with the field types that are not actually components but  // that act like them.    $record_array[0]['index']['__userid'] = ++$index;  $record_array[0]['index']['__remotehost'] = ++$index;  $record_array[0]['index']['__useragent'] = ++$index;  $record_array[0]['index']['__timestamp'] = ++$index;    $header[0] .= $new_line;  $header[1] .= $new_line;  $header[2] .= $new_line;    $main_query = 'SELECT sd.sid as sid, sd.name as name, sd.data as data '.    'FROM {webform_submitted_data} sd '.    'WHERE sd.nid = %d ORDER BY sd.sid';    $main_result = db_query($main_query, $nid);  $current_sid = 0;  $record_count = 0;  $serial = 0;    switch($mode){  case 'submission_list':    // The list view needs to know how many records are being displayed    $submission_list_header = array('#','SID',t('Date'),t('User'),'IPAddr',t('Action'),'');    $submission_list_array = array();    break;  case 'print':    print $header[0] . $header[1] . $header[2];    break;  case 'string':    $records = $header[0] . $header[1] . $header[2];    break;  case 'file':  default:    $file_record = $header[0] . $header[1] . $header[2];    $file_name = tempnam(variable_get('file_directory_temp', FILE_DIRECTORY_TEMP), 'webform');    $handle = @fopen($file_name, 'w'); // The @ suppresses errors    @fwrite($handle,$file_record);  }      // Step through the database pulling out all records.  A given SID will  // be represented by multiple rows so these are collated into a single meta record  // given in $record_array[1][] Once this has been populated it is sent off to  // _webform_make_record_csv_string() to turn it into a string    while ($record = db_fetch_object($main_result)) {        if($record->sid != $current_sid){      $current_sid = $record->sid;      if($serial != 0){        $record_count++;        switch($mode){        case 'submission_list':          $print_sid = "sid=".$record_array[1]['sid'];          $print_view_ref = l(t('View'),"node/$nid",NULL,$print_sid,NULL,FALSE);          $print_delete_ref = l(t('Delete'),"node/$nid/results/delete/" . $record_array[1]['sid'],NULL,NULL,NULL,FALSE);          $submission_list_array[] = array($record_count,$record_array[1]['sid'],$record_array[1]['__timestamp'],$record_array[1]['__userid'],$record_array[1]['__remotehost'],$print_view_ref,$print_delete_ref);          break;        case 'print':          print _webform_make_record_csv_string($record_array,$new_line);          break;        case 'string':          $records .= _webform_make_record_csv_string($record_array,$new_line);          break;        case 'file':        default:          $file_record = _webform_make_record_csv_string($record_array,$new_line);          @fwrite($handle,$file_record);        }      }      $record_array[1]['serial'] = ++$serial;      $record_array[1]['sid'] = $current_sid = $record->sid;      $record_array[1]['components'] = $record_array[0]['components'];	// Reset the rest of the data    }    //print "<p>Setting sid $current_sid :" . $record_array[$index]['sid'];    $index = $record_array[0]['index'][$record->name];    switch($record->name){    case '__timestamp':      // By using the drupal date functions we can use the users own format. However,      // Even the 'small' date is pretty ugly for this field.      // If we assume the 'small' format retains the '-' then we could chop it up into two fields.      // Time and date.  That way we would retain the users date and time formating preferences but they      // would be readable by excel      $record_array[1][$record->name] = format_date($record->data,'small');		// Becareful, longer formats have commas in them.      break;