<?php
/*
Plugin Name: CSV Export
Description: Add "CSV Export" option to Advanced Commands pulldown in all sections
Version: 1.04
Requires at least: 2.06
*/

// UPDATE THESE VALUES
$GLOBALS['CSVEXPORT_ADMIN_ONLY']   = false;    // Only allow admin users to use CSV export (true or false)
$GLOBALS['CSVEXPORT_INCLUDE']      = array();  // List of tables to include, leave blank for all.  Example: ('news','jobs','etc')
$GLOBALS['CSVEXPORT_SKIP_FIELDS']  = array('dragSortOrder','createdBy._link','_filename','_link');   // These fields won't be exported from any table.  Example: ('_filename','_link','lastUpdated','etc')
$GLOBALS['CSVEXPORT_CREATED_BY']   = false; // include createdBy.* fields
$GLOBALS['CSVEXPORT_FOR_EXCEL']    = true; // format CSV output for excel only (convert utf8 to windows charset and escape zero padded numbers as ="0123" *TODO*)

// You can set custom row order and field order by section below.  Replace 'example' with your section name an duplicate for each section
$GLOBALS['CSVEXPORT_ORDER_BY']['example']    = 'title, num DESC';     // set custom row sorting with "Order by" for specific table, eg: $GLOBALS['CSVEXPORT_ORDER_BY']['news']    = 'title, num DESC';
$GLOBALS['CSVEXPORT_FIELD_ORDER']['example'] = array('num','title');  // set custom field list or order for specific table, eg: $GLOBALS['CSVEXPORT_FIELD_ORDER']['news'] = array('num','title');

// DON'T UPDATE ANYTHING BELOW THIS LINE

$GLOBALS['CSVEXPORT_SORT_FIELDS'] = true; // sort fieldnames by schema order

addFilter('list_advancedCommands',      'csvExport_addAdvancedOption', null, 1);
addAction('section_unknownAction',      'csvExport_exportTable', null, 2);
addFilter('viewerOnly_allowed_actions', 'csvExport_addPluginActions', null, 1);


// allow users with viewerOnly access to call plugin actions
function csvExport_addPluginActions($validActions) {
  $validActions[] = 'exportAllCSV';
  $validActions[] = 'exportSearchCSV';
  $validActions[] = 'exportCheckedCSV';

  return $validActions;
}

//
function csvExport_addAdvancedOption($labelsToValues) {
  if (!_csvExport_allowAccess()) { return $labelsToValues; }

  $labelsToValues['Export as CSV (All Records)']     = 'exportAllCSV';
  $labelsToValues['Export as CSV (Search Results)']  = 'exportSearchCSV';
  $labelsToValues['Export as CSV (Checked Records)'] = 'exportCheckedCSV';
  return $labelsToValues;
}

//
function csvExport_exportTable($tableName, $action) {
  global $schema, $CURRENT_USER;
  $validActions = array('exportAllCSV','exportSearchCSV', 'exportCheckedCSV');
  if (!in_array($action,$validActions)) { return; }
  if (!_csvExport_allowAccess())        { return; }

  // error checking
  if (!@$schema)                                                       { die("schema not defined!"); }
  if (!@$CURRENT_USER)                                                 { die("CURRENT_USER not defined!"); }
  if (!array_key_exists('hasEditorAccess', $GLOBALS))                  { die("hasEditorAccess not defined!"); }
  if ($action == 'exportCheckedCSV' && !@$_REQUEST['selectedRecords']) { die(t("No records were selected!")); }

  // get user where (to limit to records user has access to) - MATCHES CODE IN list_functions_init()
  $where     = '1';
  if (@$schema['createdByUserNum'] && !$GLOBALS['hasEditorAccess'] && !userHasSectionViewerAccessOnly($tableName)) {
    $where .= " AND `createdByUserNum` = '{$CURRENT_USER['num']}'";
  }
  if ($tableName == 'accounts' && !@$CURRENT_USER['isAdmin'])       { $where .= " AND `isAdmin` = '0'"; }

  // get check record numbers
  if ($action == 'exportCheckedCSV' && @$_REQUEST['selectedRecords']) {
    $recordNums      = array_map('intval', $_REQUEST['selectedRecords']);
    $recordNumsAsCSV = implode(',', $recordNums);

    $where .= " AND num IN ($recordNumsAsCSV) ";
  }

  // load records
  $allowSearch = ($action == 'exportSearchCSV');
  require_once("lib/viewer_functions.php");
  list($records, $metaData) = getRecords(array(
    'tableName'               => $tableName,
    'loadCreatedBy'           => @$GLOBALS['CSVEXPORT_CREATED_BY'],
    'loadUploads'             => true,
    'allowSearch'             => $allowSearch,
    'where'                   => $where,
    'orderBy'                 => @$GLOBALS['CSVEXPORT_ORDER_BY'][$tableName],

    'ignoreHidden'            => false,  // don't hide records with hidden flag set
    'ignorePublishDate'       => false,  // don't hide records with publishDate > now
    'ignoreRemoveDate'        => false,  // don't hide records with removeDate < now
    'includeDisabledAccounts' => true,   // include records that were created by disabled accounts.  See: Admin > Section Editors > Advanced > Disabled Accounts

    //'debugSql'                => true,
  ));

  // modify records
  foreach (array_keys($records) as $index) {
    $record = &$records[$index];

    // export uploads urls as CSV
    foreach ($record as $name => $uploads) {
      if (@$schema[$name]['type'] != 'upload') { continue; } // skip all but upload fields
      $record[$name] = array_pluck($uploads, 'urlPath');

      // make urls absolute
      $isHTTPS = @$_SERVER['HTTPS'] == 'on' || @$_SERVER['SERVER_PORT'] == 443;
      $proto   = $isHTTPS ? 'https://' : 'http://';
      foreach (array_keys($record[$name]) as $index) {
        $urlPath = &$record[$name][$index];
        if (!preg_match("/^https?:/", $urlPath) && @$_SERVER['HTTP_HOST']) {
          $urlPath = $proto . $_SERVER['HTTP_HOST'] . $urlPath;
        }
        unset($urlPath);
      }
    }

    // sort record fields
    if ($GLOBALS['CSVEXPORT_SORT_FIELDS']) { uksort($record, '_csvExport_sortFieldsBySchemaOrder'); }

    unset($record);
  }

  // get first record
  $firstRecord = @$records[0];
  if (!$firstRecord) { die("No records to export!"); }

  // get fieldnames
  $fieldnames = @$GLOBALS['CSVEXPORT_FIELD_ORDER'][$tableName];
  if (@$GLOBALS['CSVEXPORT_FIELD_ORDER'][$tableName]) { $fieldnames = $GLOBALS['CSVEXPORT_FIELD_ORDER'][$tableName]; }
  else                                                { $fieldnames = array_keys($firstRecord); }

  // send header
  $filename = "$tableName-" . date('Ymd-His') . '.csv';
#  header('Content-type: text/plain; charset=utf-8'); # Uncomment for debugging
  header("Content-type: application/octet-stream");
  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Transfer-Encoding: binary");

  // send column headers
  $line = '';
  foreach ($fieldnames as $field) {
    if (in_array($field, $GLOBALS['CSVEXPORT_SKIP_FIELDS'])) { continue; }
    $line .= _csvExport_escapeAsCSV($field) . ',';
  }
  $line = chop($line, ',') . "\n";
  print $line;

  // send rows
  foreach ($records as $record) {
    if ($GLOBALS['CSVEXPORT_FOR_EXCEL']) { mb_convert_variables('CP1252', 'UTF-8', $record); }

    $line = '';
    foreach ($fieldnames as $field) {
      $value = array_key_exists($field, $record) ? $record[$field] : "Unknown field '$field'";
      if (in_array($field, $GLOBALS['CSVEXPORT_SKIP_FIELDS'])) { continue; }
      if (is_array($value)) { $value = implode(', ', $value); }
      $line .= _csvExport_escapeAsCSV($value) . ',';
    }
    $line = chop($line, ',') . "\n";
    print $line;
  }

  exit;
}


//
function _csvExport_escapeAsCSV($value) {
  $value = str_replace('<br/>', '', $value);
  $value = str_replace('"', '""', $value);
  $value = '"' . $value . '"';

  if ($GLOBALS['CSVEXPORT_FOR_EXCEL'] && preg_match('/^"0\d+"$/s', $value)) {
    $value = '='.$value;
  }

  return $value;
}


// check if user is allowed to use CSV Export
function _csvExport_allowAccess() {

  // admin only?
  if ($GLOBALS['CSVEXPORT_ADMIN_ONLY'] && !$GLOBALS['CURRENT_USER']['isAdmin']) { return false; }

  // limit by table
  $tableName = @$_REQUEST['menu'];
  if ($GLOBALS['CSVEXPORT_INCLUDE'] && !in_array($tableName, $GLOBALS['CSVEXPORT_INCLUDE'])) { return false; }

  //
  return true;
}

// sort field list by order key, eg: uksort($record, '_csvExport_sortFieldsBySchemaOrder');
function _csvExport_sortFieldsBySchemaOrder($fieldA, $fieldB) {
  global $schema;

  // sort pseudo fields that start with _ down (eg: _link, _filename, etc)
  if (substr($fieldA, 0, 1) == '_') { return 1; }
  if (substr($fieldB, 0, 1) == '_') { return -1; }

  // get short names (without :suffix)
  @list($shortA, $suffixA) = explode(':', $fieldA);
  @list($shortB, $suffixB) = explode(':', $fieldB);

  // sort :suffix fields down (if comparing same fieldname)
  if ($shortA == $shortB) {
    return strcmp($suffixA, $suffixB);
  }

  // sort fields by schema order
  $orderA = intval(@$schema[$shortA]['order']);
  $orderB = intval(@$schema[$shortB]['order']);
  if      ($orderA < $orderB) { $r = -1; }
  else if ($orderA > $orderB) { $r = 1; }
  else                        { $r = 0; }
  return $r;

}


?>
