webform_import.module

<?php
/**
 * @file
 * Enables the upload of webform submissions from a delimited file.
 *
 * This is usefull for importing results from other systems in to Webform.
 *
 * @author John C Jemmett <jjemmett@northwind-inc.com>
 * @author Greg Bosen <gbosen@northwind-inc.com>
 * @author Joe Corall <joe.corall@gmail.com>
 *
 */

/**
 * Implements hook_menu().
 */
function webform_import_menu() {
  $items = array();
  $items['node/%webform_menu/webform-results/upload'] = array(
    'title' => 'Upload',
    'page callback' => 'backdrop_get_form',
    'page arguments' => array('webform_import_form', 1),
    'access arguments' => array('access webform upload'),
    'weight' => 10,
    'type' => MENU_LOCAL_TASK,
  );
  $items['node/%webform_menu/webform-results/upload/%'] = array(
    'title' => 'Get Template',
    'page callback' => 'webform_import_csvtemplate',
    'page arguments' => array(1, 4),
    'access arguments' => array('access webform upload'),
    'type' => MENU_CALLBACK,
  );
  return $items;
}

/**
 * Implements hook_permission().
 */
function webform_import_permission() {
  return array(
    'access webform upload' => array(
      'title' => t('Access Webform Upload'),
      'description' => t('Grants access to the Upload tab on all webform content.'),
      'restrict access' => TRUE,
    ),
  );
}

/**
 * Creates a downloadable CSV template file corresponding to a Webform structure.
 *
 * @param $node
 *   The current webform node.
 * @param $type
 *   The type of delimited file template to download.
 */
function webform_import_csvtemplate($node, $type) {
  $types = _webform_import_field_key_options();
  $filename = check_plain($node->title) . '_upload.csv';
  $headers = array();
  $node->webform['components']['-1'] = array(
    'name' => 'Submission ID',
    'form_key' => 'SID',
  );
  $node->webform['components']['-2'] = array(
    'name' => 'UID',
    'form_key' => 'UID',
  );
  $node->webform['components']['-3'] = array(
    'name' => 'IP Address',
    'form_key' => 'IP_ADDRESS',
  );
  if (array_key_exists($type, $types)) {

    foreach ($node->webform['components'] as $cid => $component) {
      $ctype = ( array_key_exists('type',$component) ? $component['type'] : '' );
      if ($ctype != 'file' && $ctype != 'fieldset' && $ctype != 'markup' && $ctype != 'pagebreak') {
        $headers[] = $component[$type];
      }
    }
    // @TODO Make this line more generic.
    $headers[] = ($type=='name') ? 'Submitted Time' : 'webform_time';
    $csv = join(',', $headers);

    backdrop_add_http_header('Content-Type', 'application/force-download');
    backdrop_add_http_header('Pragma', 'public');
    backdrop_add_http_header('Cache-Control', 'max-age=0');
    backdrop_add_http_header('Content-Type', 'text/csv');
    backdrop_add_http_header('Content-Disposition', "attachment; filename=$filename");
    print $csv;
  }
  else {
    backdrop_set_message(t('Invalid header type.'), 'warning');
    $path = explode('/', $_GET['q']);
    array_pop($path);
    $path = join('/', $path);
    backdrop_goto(backdrop_get_path_alias($path));
  }
}

/**
 * Client form generation function. Allows the user to upload a delimited file.
 *
 * @param $form_state
 *   The current form values of a submission, used in multipage webforms.
 * @param $node
 *   The current webform node.
 *
 * @see webform_import_form_submit()
 * @ingroup forms
 */
function webform_import_form($node, $form_state) {
  $node = $form_state['build_info']['args'][0];

  // Add a css class for all client forms.
  $form['#attributes'] = array('class' => 'webform-import-form');

  // Set the encoding type (necessary for file uploads).
  $form['#attributes']['enctype'] = 'multipart/form-data';

  $form['#redirect'] = 'node/' . $node->nid . '/webform-results/table';

  $form['#submit'][] = 'webform_import_form_submit';
  $form['details']['nid'] = array(
    '#type' => 'value',
    '#value' => $node->nid,
  );

  $button_text = t('Download template');
  $upload_path = 'node/' . $node->nid . '/webform-results/upload/';
  $button_optopns = array('attributes' => array('class'=>'button'));
  $name_button = l($button_text, $upload_path . 'name', $button_optopns);
  $key_button = l($button_text, $upload_path . 'form_key', $button_optopns);

  $component_table = array();
  $component_table['header'] = array(
    array('data' => t('Field Names') . '&nbsp;<div class="move-right"> ' . $name_button . ' </div>'),
    array('data' => t('Field Form Keys') . '&nbsp;<div class="move-right"> ' . $key_button . ' </div>'),
  );

  // This is the set of standard webform components.
  $component_table['rows'] = array();
  $component_table['rows'][] = array('Serial', 'webform_serial');
  $component_table['rows'][] = array('SID', 'webform_sid');
  $component_table['rows'][] = array('Submitted Time', 'webform_time');
  $component_table['rows'][] = array('Completed Time', 'webform_completed_time');
  $component_table['rows'][] = array('Modified Time', 'webform_modified_time');
  $component_table['rows'][] = array('Draft', 'webform_draft');
  $component_table['rows'][] = array('IP Address', 'webform_ip_address');
  $component_table['rows'][] = array('UID', 'webform_uid');
  $component_table['rows'][] = array('Username', 'webform_username');

  // The below are addtitional form components for the specific webform.
  foreach ($node->webform['components'] as $cid => $component) {
    // Skip non-data components.
    if ($component['type'] == 'file' || $component['type'] == 'fieldset'|| $component['type'] == 'markup' || $component['type'] == 'pagebreak') {
      continue;
    }
    $style = '';
    if (isset($component['required'])) {
      $style = 'font-weight: bold';
    }
    $component_table['rows'][] = array(
      array('data' => $component['name'], 'style' => $style ),
      array('data' => $component['form_key'], 'style' => $style ),
    );
  }

  $form['header'] = array(
    '#type' => 'item',
    '#markup' => '<h2>' . t('Webform Import for "@title"', array('@title' => $node->title)) . '</h2>',
  );

  $form['help'] = array(
    '#type' => 'help',
    '#markup' => t('Webform import enables the upload of delimited files to enter submission data.'),
  );

  $form['instructions'] = array(
    '#type' => 'fieldset',
    '#title' => t('Instructions'),
    '#collapsible' => TRUE,
    '#collapsed' => TRUE,
  );

  $instructions_items = array(
    'type' => 'ol',
    'items' => array(
      'All rows with a submission id (SID) will be updated. Those without a submission id will be inserted.',
      'All rows without submitted time will be inserted with current system time.',
      'All rows without a submitted IP Address will use the IP Address of the person performing the import.',
      'All rows without a submitted user id (UID) will use the user id of the person performing the import.',
      array(
        'data' => 'Component specific help:',
        'children' => array(
          '<strong>Date</strong>: must be in a format parsable by the php function <code>strtotime()</code> Any time data will be discarded.',
          '<strong>Grid</strong>: option keys must be separated by commas and in the order of the questions in the webform. (e.g., <em>"red,male,car"</em> for the questions <em>"Favorite color, Gender, Type of automobile you drive"</em> respectively.)',
          '<strong>File</strong>: * currently unable to handle this component.',
          '<strong>Select</strong>: for multiselect answer keys must be separated by commas. (e.g., <em>"1,2,3"</em>)',
          '<strong>Time</strong>: must be in a format parsable by the php function <code>strtotime()</code> Any date data will be discarded.',
        ),
      ),
      'Only data entry fields are importable. Excluded component types include: fieldset, markup &amp; pagebreak.',
      'Rows may contain a modified time (e.g. from a webform export) but it will be ignored and replaced with current system time if that row is updated in the DB.',
      'The file must use the following values for column headers, anything else will be ignored. &nbsp;<small>* bold names are mandatory and must contain a value. Other fields will be imported if present.</small>',
    ),
  );

  $markup  = '<div class="webform-import-instructions">';
  $markup .=    theme('item_list', $instructions_items);
  $markup .=    theme('table', $component_table);
  $markup .= '</div>';

  $path = backdrop_get_path('module', 'webform_import');
  $form['instructions']['instructions'] = array(
    '#type' => 'item',
    '#markup' => $markup,
    '#attached' => array(
      'css' => array($path . '/css/webform-import-admin.css'),
    ),
  );

  $form['upload'] = array(
    '#type' => 'file',
    '#title' => t('Delimited data file'),
    '#description' => t('Choose the data file containing the data you want uploaded. All rows with a submission id (SID) will be updated. Those without a submission id will be inserted.')
  );

  $form['delimiter'] = array(
    '#type' => 'select',
    '#title' => t('File Delimiter'),
    '#default_value' => ',',
    '#options' => _webform_import_delimiter_options(),
    '#description' => t('Delimiter for file being uploaded.'),
  );

  $form['field_keys'] = array(
    '#type' => 'select',
    '#title' => t('Column header contains'),
    '#default_value' => 'name',
    '#options' => _webform_import_field_key_options(),
    '#description' => t('What to use for the column key. (You probably want to choose form key if you have multiple fields with the same name.)'),
  );

  $form['actions'] = array(
    '#type' => 'actions',
  );

  // Add the submit button.
  $form['actions']['submit'] = array(
    '#type' => 'submit',
    '#value' => empty($node->webform['submit_text']) ? t('Import') : $node->webform['submit_text'],
    '#weight' => 10,
  );

  return $form;
}

/**
 * Form submission function to parse the delimited file and add to the database.
 *
 * @param $form
 *   The current form.
 * @param $form_state
 *   The current form values of a submission.
 *
 * @see webform_import_form()
 */
function webform_import_form_submit($form, $form_state) {

  // Define your limits for the submission here.
  $limits = array(
    'extensions' => 'csv tsv txt'
  );
  $validators = array(
    'file_validate_extensions' => array($limits['extensions'])
  );

  if ($file = file_save_upload('upload', $validators)) {
    _webform_import_import($form, $form_state, $file);
  }
  else {
    form_set_error('upload', t('Uploaded file could not be saved.'));
    watchdog('webform-import', 'File save error. Could not save file %file to path %path.!details', array('%file' => $file->filename, '%path' => $file->filepath, '!results' => '<br />\n<pre>' . htmlentities(print_r($form_state['values'], TRUE)) . '</pre>'), WATCHDOG_ERROR);
  }
}

/**
 * Function to parse the delimited file and add submissions to the database.
 *
 * @param $form
 *   The current form.
 * @param $form_state
 *   The current form values of a submission.
 *
 * @see webform_import_form()
 */
function _webform_import_import($form, $form_state, $file) {

  // This makes php auto-detect legacy mac line endings. Deprecated in php 8.1.
  // ini_set('auto_detect_line_endings', TRUE);

  if (($handle = fopen($file->destination, 'r')) === FALSE) {
    form_set_error('upload', t('File could not be opened for reading.'));
    watchdog('webform-import', 'File read error. Could not read file %file at path %path.!details', array('%file' => $file->filename, '%path' => $file->filepath, '!results' => '<br />\n<pre>' . htmlentities(print_r($form_state['values'], TRUE)) . '</pre>'), WATCHDOG_ERROR);
    return;
  }

  module_load_include('inc', 'webform', 'includes/webform.submissions');

  $sids = array();
  $cids = array();
  $fields = array();

  $webform = node_load(intval($form_state['values']['nid']));
  $delimiter = $form_state['values']['delimiter'];
  $delimiter = $delimiter == "\t" ? "\t" : $delimiter;
  $field_key = $form_state['values']['field_keys'];

  // When importing using Field Names, set up the form keys for each field name.
  if ($field_key === 'name') {
    $components_list['Serial'] = array('form_key' => 'webform_serial');
    $components_list['SID'] = array('form_key' => 'webform_sid');
    $components_list['Submitted Time'] = array('form_key' => 'webform_time');
    $components_list['Completed Time'] = array('form_key' => 'webform_completed_time');
    $components_list['Modified Time'] = array('form_key' => 'webform_modified_time');
    $components_list['Draft'] = array('form_key' => 'webform_draft');
    $components_list['IP Address'] = array('form_key' => 'webform_ip_address');
    $components_list['UID'] = array('form_key' => 'webform_uid');
    $components_list['Username'] = array('form_key' => 'webform_username');
  }
  else {
  // When using Field Form Keys, the form_key has the same value as the keys in the componenents_list array.
    foreach (array('webform_serial','webform_sid','webform_time','webform_completed_time','webform_modified_time','webform_draft','webform_ip_address','webform_uid','webform_username') as $key) {
      $components_list[$key] = array('form_key' => $key);
    }
  }

  foreach ($webform->webform['components'] as $cid => $component) {
    $component['cid'] = $cid;
    $components_list[trim($component[$field_key])] = $component;
  }

  $data = array();
  $num = 0;
  $arraylen = 0;
  $count = -1;

  while (!feof($handle)) {
    $count++;
    $data = fgetcsv($handle, 0, $delimiter);

    // Ignore empty rows.
    if ($data[0] === NULL) {
      continue;
    }

    if ($count == 0 ) { // This is the header row.
      $arraylen = count($data);
      foreach ($data as $k => &$cell_value) {
        $cell_value = _webform_import_csvfieldtrim($cell_value);
        if ($cell_value && !$components_list[$cell_value]) {
          // Go to the next row and reset the counter because this isn't the real webform table header.
          backdrop_set_message(t('Can not find column @k in components list, skipping row.', array('@k' => $cell_value)), 'warning');
          $count--;
          continue;
        }
        elseif (isset($components_list[$cell_value]['cid'])) {
          $cids[$k] = $components_list[$cell_value]['cid'];
        }
      }
      $fields = array_flip($data);

      foreach ($components_list as $k => $component) {
        if (!strcmp($k, 'name')) {
          if ($component['required'] && !isset($fields[$k])) {
            form_set_error('upload', t('Column @k is required but could not be found in this file. Alter the file or the webform and try again.', array('@k' => $k)));
          }
        }
      }

      $fields = $data;
      continue 1;
    }

    // Set defaults for form data in case it's missing in the current row.
    global $user;
    $uid = $user->uid;
    $ipaddress = webform_ip_address($webform);
    $submitted = REQUEST_TIME;
    $completed = REQUEST_TIME;
    $modified = REQUEST_TIME;
    $sid = NULL;
    $serial = NULL;  // Default NULL to identify rows with missing serial numbers.
    $num = count($data);

    if ($arraylen == $num) {
      // Clear the sub_array for new row (submission data record).
      $sub_array = array();
      foreach ($data as $k => &$cell_value) {
        $cell_value = _webform_import_csvfieldtrim($cell_value);
        if ($cell_value == '') {
          // Check that each mandatory field has a value.
          $isset = isset($components_list[$fields[$k]]['required']);
          if ($isset && $components_list[$fields[$k]]['required']) {
            $message = 'Required field has no value at row,col: @row,@column. Skipping this row!';
            backdrop_set_message(t($message, array('@row' => $count, '@column' => $k)), 'warning');
            continue 2;
          }
          else {
            continue 1;  // Skip the field if empty.
          }
        }

        // Check SID value for input security.
        if ($components_list[$fields[$k]]['form_key'] === 'webform_sid') {
          if (!is_numeric($cell_value)) {
            backdrop_set_message(t('Invalid Submission ID at row,col: @row,@column.  Skipping this row!', array('@row' => $count, '@column' => $k)), 'warning');
            continue 2;
          }
          else {
            $sid = intval($cell_value); // Sanitize to integer value only.
          }
        }
        // Get the serial number for the webform submission.
        elseif ($components_list[$fields[$k]]['form_key'] === 'webform_serial') {
          $serial = intval($cell_value); // Sanitize to integer value only.
        }
        // It's a real component, so parse and add to $sub_array.
        elseif (($cid = isset($cids[$k]) ? $cids[$k]:FALSE) !== FALSE) {
          $type = $components_list[$fields[$k]]['type'];
          // Handle date and time components.
          if ($type === 'date' || $type === 'time') {
            $timezone = date_default_timezone();
            $date = new DateTime($cell_value, new DateTimeZone($timezone));
            $timestamp = $date->format('U');
            if ($time = $timestamp) {
              $cell_value = $type === 'date' ? date('Y-m-d', $time) : date('H:i:s', $time);
            }
            else {
              backdrop_set_message(t('Invalid datetime value at row,col: @row,@column. Skipping this row!', array('@row' => $count, '@column' => $k)), 'warning');
              continue 2;
            }
          }
          // Handle grid and multi-select components.
          elseif ($type === 'grid' || ($type === 'select' && $components_list[$fields[$k]]['extra']['multiple'] == 1)) {
            // Explode the value into an array and save it back to the value.
            $cell_value = explode(',', $cell_value);
          }
          $sub_array[$cid] = $cell_value;
        }

        if ($components_list[$fields[$k]]['form_key'] === 'webform_uid') {
          $uid = $cell_value;
        }
        if ($components_list[$fields[$k]]['form_key'] === 'webform_ip_address') {
          $ipaddress = $cell_value;
        }
        // Read the timestamps for the webform submission.
        if ($components_list[$fields[$k]]['form_key'] === 'webform_time') {
          // Modify saved date string format so that strtotime can parse it.
          $submitted = strtotime(preg_replace('/ - /', ' ', $cell_value));
        }
        if ($components_list[$fields[$k]]['form_key'] === 'webform_completed_time') {
          $completed = strtotime(preg_replace('/ - /', ' ', $cell_value));
        }
        // @todo The next 3 lines can probably be removed because webform_submission_insert will update the modified time to current.
        if ($components_list[$fields[$k]]['form_key'] === 'webform_modified_time') {
          $modified = strtotime(preg_replace('/ - /', ' ', $cell_value));
        }
      }

      $submission = (object) array(
        'nid' => $webform->nid,
        'uid' => $uid,
        'submitted' => $submitted,
        'completed' => $completed,
        'modified' => $modified,
        'remote_addr' => $ipaddress,
        'is_draft' => FALSE,
        // Use the row counter as the serial number if it's missing (NULL).
        // It needs to be unique or it will cause a DB error.
        'serial' => $serial ?? $count,
        'data' => webform_submission_data($webform, $sub_array),
      );

      // If there is no SID, assume it is a new record and insert it.
      if ($sid != NULL) {
        $submission->sid = $sid;
        // Check whether this record is in the DB already and update if yes or insert if no.
        $result = db_query("SELECT sid FROM {webform_submissions} WHERE sid = $sid AND nid = $submission->nid");
        if ($result->rowCount()) {
          backdrop_set_message(t('Updating submission with SID @sid', array('@sid'=>$sid)), 'information');
          $sids[] = webform_submission_update($webform, $submission);
        }
        else {
          backdrop_set_message(t('Creating new submission with SID @sid', array('@sid'=>$sid)), 'information');
          // webform_submission_insert does not write to the webform_submissions table if SID is set, so we do it ourselves.
          backdrop_write_record('webform_submissions', $submission);
          $sids[] = webform_submission_insert($webform, $submission);
        }
      }
      else {
        $sids[] = webform_submission_insert($webform, $submission);
        backdrop_set_message(t('Creating new submission'), 'information');
      }
    }
    else {
      backdrop_set_message(t('Row @row is malformed and will need to be fixed and resubmitted.', array('@row' => ($count+1))), 'warning');
    }
  }
  fclose($handle);

  if (!file_delete($file->fid)) {
    watchdog('webform-import', 'File could not be deleted (cleanup process). File: %file at path %path . !details', array('%file' => $file->filename, '%path' => $file->destination, '!results' => '<br />\n<pre>' . htmlentities(print_r($form_state['values'], TRUE)) . '</pre>'), WATCHDOG_ERROR);
  }

  backdrop_set_message(t('We uploaded @count submissions', array( '@count' => count($sids))));
  watchdog('webform-import', 'Submission file uploaded to %title.', array('%title' => check_plain($webform->title), '!results' => '<br />\n<pre>' . htmlentities(print_r($form_state['values'], TRUE)) . '</pre>'));

}

/**
 * Returns a list of value delimiters we can use.
 *
 * @return
 *   An array of key/value pairs for form options list.
 */
function _webform_import_delimiter_options() {
  return array(
    ',' => t('Comma (,)'),
    "\t" => t('Tab (\t)'),
    ';' => t('Semicolon (;)'),
    ':' => t('Colon (:)'),
    '|' => t('Pipe (|)'),
    '.' => t('Period (.)'),
    ' ' => t('Space ( )'),
  );
}

/**
 * Returns a list of field header types we can use.
 *
 * @return
 *   An array of key/value pairs for form options list.
 */
function _webform_import_field_key_options() {
  return array(
    'name' => t('Field Names'),
    'form_key' => t('Field Form Keys'),
  );
}

/**
 * Returns a trimmed field value
 *
 * @param $value
 *   Field value to be trimmed.
 * @return
 *   Trimmed field value.
 */
function _webform_import_csvfieldtrim($value) {
  // Remove whitespace.
  $value = trim($value);

  // Strip off the beginning and ending quotes if necessary.
  $value = trim($value, '"');

  // Remove control characters. Some editors add invalid EOL chars.
  // fgetcsv does not handle unicode characters therefore we replace them
  // manually. See http://bugs.php.net/bug.php?id=31632.
  $value = str_replace('\x00..\x1F\xfe\xff', '', $value);

  return $value;
}