<?php // Plugin Functions

// Display Upload Form
function csvImport_uploadForm() {
  global $TABLE_PREFIX, $CURRENT_USER;
  list($thisPluginPath, $thisPluginUrl) = getPluginPathAndUrl();

  // set preSaveTempId for new uploads
  $preSaveTempId   = @$_REQUEST['preSaveTempId'] ? $_REQUEST['preSaveTempId'] : uniqid('x');
  $recordNum       = intval( @$_REQUEST['num'] );
  $uploads         = getUploadRecords('_csv_import_jobs', 'csv_upload', $recordNum, $preSaveTempId);

  ### Cleanup old data (that is 24+ hours old)

  // delete old/abandoned import jobs and job data
  $jobsDeleted         = mysql_delete('_csv_import_jobs', null, "`updatedDate` < (NOW() - INTERVAL 1 DAY) AND isCustomMenu != 1");  // erase old import jobs
  $currentJobNumsAsCSV = mysql_escapeCSV( array_pluck( mysql_select('_csv_import_jobs'), 'num') ); 
  $rowsDeleted         = mysql_delete('_csv_import_data', null, "`jobNum` NOT IN ($currentJobNumsAsCSV)");  // erase old import data
  foreach (getSchemaTables() as $tempTable) { // drop temp tables that no longer have associated jobs
    if (!preg_match("/^_csv_import_temp_job(\d+)$/", $tempTable, $matches)) { continue; }
    mysqli()->query("DROP TABLE IF EXISTS `{$GLOBALS['TABLE_PREFIX']}$tempTable`") or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
    $schemaPath  = realpath(DATA_DIR . '/schema') . '/' . $tempTable . ".ini.php";
    @unlink($schemaPath);
  }        
  
  // delete old/abandoned uploaded import files 
  $oldUploadRecords    = mysql_select('uploads', "`tableName` = '_csv_import_jobs' AND `createdTime` < (NOW() - INTERVAL 1 DAY)");  // erase old import uploads
  $oldUploadNumsAsCSV  = mysql_escapeCSV( array_pluck( $oldUploadRecords, 'num') ); 
  $uploadsDeleted      = removeUploads("`num` IN ($oldUploadNumsAsCSV)"); // remove old uploads
  
  if ($jobsDeleted || $rowsDeleted || $uploadsDeleted) {
    alert("Cleanup: Removed old import data ($jobsDeleted jobs, $rowsDeleted rows, $uploadsDeleted uploads)<br>\n");
  }
  ### /Cleanup
  
  // load import job (if we've already created one and then pressed back)
  if ($recordNum) { 
    list($importJob, $csvFilepath) = _csvImport_loadImportJob($recordNum);
    if (!array_key_exists('fileFormat',       $_REQUEST)) { $_REQUEST['fileFormat']       = $importJob['fileFormat']; }
    if (!array_key_exists('hasColumnHeaders', $_REQUEST)) { $_REQUEST['hasColumnHeaders'] = $importJob['hasColumnHeaders']; }
    if (!array_key_exists('targetTable',      $_REQUEST)) { $_REQUEST['targetTable']      = $importJob['targetTable']; }
  }
  else { // else load defaults
    if (!array_key_exists('hasColumnHeaders', $_REQUEST)) { $_REQUEST['hasColumnHeaders'] = $GLOBALS['CSV_IMPORT_DEFAULTS']['USE_COLUMN_HEADERS']; }
  }
  
  
  // save record
  if (@$_REQUEST['submitForm']) {

    // error checking
    $errors = '';
    if ($_SERVER['REQUEST_METHOD'] != 'POST') { $errors .= "Form method must be POST!<br>\n"; }
    if (!@$_REQUEST['preSaveTempId'])         { $errors .= "No preSaveTempId set!<br>\n"; }
    if (!$uploads)                            { $errors .= "You must upload a CSV file to import!<br>\n"; }
    if (!@$_REQUEST['targetTable'])           { $errors .= "You must specify a section to import into!<br>\n"; }
    if ($errors) { alert($errors); }

    // save record
    if (!$errors) {

      // get columnNamesMap
      $columnNamesMap = '';
      $csvFilepath    = @$uploads[0]['filePath'];
      $columnNames    = _csvParser_getColumnNames($csvFilepath, !$_REQUEST['hasColumnHeaders']);
      $columnNamesMap = _csvImport_arrayToFieldMap($columnNames);


      // create/update import job
      $colsToValues = array(
        'updatedDate='     => 'NOW()',
        'updatedByUserNum' => $CURRENT_USER['num'],
        
        'fileFormat'       => $_REQUEST['fileFormat'],
        'hasColumnHeaders' => $_REQUEST['hasColumnHeaders'],
        'targetTable'      => $_REQUEST['targetTable'],
        'columnNamesMap'   => $columnNamesMap,
      );
      
      // copy fields from parent record
      if (csvImport_isCustomMenu()) { 
        $parentJob    = mysql_get('_csv_import_jobs', csvImport_isCustomMenu());
        $copyKeys     = array('fileFormat','hasColumnHeaders','targetTable','columnNamesMap',
                      //'columnCount','recordCount', // don't copy these
                        'useIdField','idField','updateDuplicates','removeOrphans','wipeDatabase','setAutoIncrement','newAutoIncrement','mapFields');
        foreach ($copyKeys as $key) { $colsToValues[$key] = $parentJob[$key]; } // force user to uses these values
      }
      
      //
      if ($recordNum) {
        mysql_update('_csv_import_jobs', $recordNum, null, $colsToValues);
      }
      else {
        $colsToValues['createdDate=']     = 'NOW()';
        $colsToValues['createdByUserNum'] = $CURRENT_USER['num'];
        $recordNum  = mysql_insert('_csv_import_jobs', $colsToValues, true);
      }

      // adopt temp uploads
      adoptUploads('_csv_import_jobs', $preSaveTempId, $recordNum);
      removeExpiredUploads(); // erase old expired uploads

      // 
      
      // redirect to next page
      $nextAction  = 'csvImport_analyseForm';
      $nextPageUrl = "?_plugin=$thisPluginPath&_pluginAction=$nextAction&num=$recordNum&menu=".urlencode(@$_REQUEST['menu']);
      redirectBrowserToURL($nextPageUrl);
      exit;
    }

  }

  // adminUI
  // prepare adminUI() placeholders
  $adminUI = array();
  
  // page title
  $adminUI['PAGE_TITLE'] = array(
    t("Plugins") => '?menu=admin&action=plugins',
    t("CSV Import") => "?_pluginAction=csvImport_uploadForm",
  );
  
  // button
  $adminUI['BUTTONS'] = array(
    array('name' => 'simple_menu_import', 'label' => 'Simple Menu Import &gt;&gt;', 'onclick' => 'window.location="?_pluginAction=csvImport_simpleMenuImport";', 'type' => 'button'),
    array('name' => 'import_jobs', 'label' => 'Import Jobs &gt;&gt;', 'onclick' => 'window.location="?menu=_csv_import_jobs";', 'type' => 'button'),
    array('name' => 'back_to_plugins', 'label' => 'Back to Plugins &gt;&gt;', 'onclick' => 'window.location="?menu=admin&action=plugins";', 'type' => 'button'),
  );
  
  // content to display
  $adminUI['CONTENT']  = ob_capture('_csvImport_showHeader');
  $adminUI['CONTENT'] .= ob_capture(function() use($preSaveTempId, $recordNum) {
    ?>

    <div class="alert alert-info">
      <h4>CSV Import Options</h4>
      <p>Choose your import method:</p>
      <div class="row">
        <div class="col-md-6">
          <div class="panel panel-default">
            <div class="panel-heading">
              <h5 class="panel-title">Standard CSV Import</h5>
            </div>
            <div class="panel-body">
              <p>Import data into any section with field mapping and validation.</p>
              <a href="?_pluginAction=csvImport_uploadForm" class="btn btn-primary">Standard Import</a>
            </div>
          </div>
        </div>
        <div class="col-md-6">
          <div class="panel panel-default">
            <div class="panel-heading">
              <h5 class="panel-title">Simple Menu Import</h5>
            </div>
            <div class="panel-body">
              <p>Import menu items with options and choices from a single CSV file.</p>
              <a href="?_pluginAction=csvImport_simpleMenuImport" class="btn btn-success">Menu Import</a>
            </div>
          </div>
        </div>
      </div>
    </div>

    <hr>

    <input type="hidden" name="_pluginAction" value="csvImport_uploadForm">
    <input type="hidden" name="preSaveTempId" value="<?php echo htmlencode($preSaveTempId) ?>">
    <input type="hidden" name="num"           value="<?php echo htmlencode($recordNum) ?>">
    
    <div class="form-horizontal">
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Import CSV File
        </div>
        <div class="col-sm-10" style="padding-top: 4px">
          <?php
            $iframeUrl  = pluginAction_getLink('csvImport_uploadForm_uploadIframe');
            $iframeUrl .= "&amp;num=$recordNum";
            $iframeUrl .= "&amp;preSaveTempId=$preSaveTempId";
            $iframeUrl .= "&amp;menu=" . urlencode(@$GLOBALS['menu']);
          ?>
          <iframe src='<?php echo $iframeUrl ?>' height='26' width='600' frameborder='0' scrolling='no' style="min-height: 34px !important">
          </iframe>
        </div>
      </div>
      
      <?php if (!csvImport_isCustomMenu()): ?>
      <div class="form-group">
        <div class="col-sm-2 control-label">
          File Format
        </div>
        <div class="col-sm-10">
          <?php
            $valuesToLabels = getListOptions('_csv_import_jobs', 'fileFormat');
            $optionsHTML    = getSelectOptions(@$_REQUEST['fileFormat'], array_keys($valuesToLabels), array_values($valuesToLabels), false);
          ?>
          <select name="fileFormat" class="form-control" onchange="csvImport_updatePreview()"><?php echo $optionsHTML ?></select>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Column Headers
        </div>
        <div class="col-sm-10">
          <input type="hidden" name="hasColumnHeaders" value="0">
          <div class="checkbox">
            <label>
              <input type="checkbox" name="hasColumnHeaders" value="1" <?php checkedIf(@$_REQUEST['hasColumnHeaders'], 1) ?> onclick="csvImport_updatePreview()">
              Use the first line of import file as column names
            </label>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Target Table
        </div>
        <div class="col-sm-10">
          <?php
            $valuesToLabels = array();
            $selectedValue  = array_key_exists('targetTable', $_REQUEST) ? $_REQUEST['targetTable'] : $GLOBALS['CSV_IMPORT_DEFAULTS']['TARGET_TABLE'];
            foreach (getSortedSchemas() as $tablename => $schema) {
              if ($tablename[0] == '_') { continue; }
              if (@$schema['menuType'] == 'menugroup') { continue; }
              if (@$schema['menuType'] == 'link')      { continue; }
              $valuesToLabels[$tablename] = "$tablename ({$schema['menuName']})";
            }
            asort($valuesToLabels);
            $optionsHTML  = getSelectOptions($selectedValue, array_keys($valuesToLabels), array_values($valuesToLabels), true);
            print "<select name='targetTable' class='form-control'>$optionsHTML</select>\n";
          ?>
        </div>
      </div>
      
      <?php endif; // if (!csvImport_isCustomMenu()) ?>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
        </div>
        <div class="col-sm-10">
          <?php echo adminUI_button(['type' => 'submit', 'btn-type' => 'default', 'label' => t('Analyse Data &gt;&gt;')]); ?>
        </div>
      </div>
    </div>

    <br>
    <?php echo adminUI_separator(t('Live Preview')) ?>

    <div style="height: 50px; overflow: auto" id="previewContent">
      <div style="padding: 10px; text-align: center">Upload file to see preview...</div><br>
    </div>

  <?php });
  // display content
  adminUI($adminUI);
  exit;
}



//
function csvImport_uploadForm_uploadIframe() {
  require_once('csvImport_uploadIframe.php');
  exit;
}


//
function csvImport_previewData_ajax() {
  $recordNum        = @$_REQUEST['num'];
  $preSaveTempId    = @$_REQUEST['preSaveTempId'];
  $hasColumnHeaders = @$_REQUEST['hasColumnHeaders'];

  // load import record (if applicable)
  if ($recordNum) {
    $importJob = _csvImport_loadImportJob($recordNum); // this also sets CSV_DELIMITER
  }

  // load CSV upload
  $uploads = getUploadRecords('_csv_import_jobs', 'csv_upload', $recordNum, $preSaveTempId);
  $csvFilepath = @$uploads[0]['filePath'];

  // error checking
  if     (!$csvFilepath)              { exit; } // return nothing for ajax functions
  elseif (!file_exists($csvFilepath)) { die("Import file doesn't exist!"); }

  // get preview data
  $previewRows    = array();
  $maxPreviewRows = 12;
  $maxCols        = 0;
  foreach (range(1, $maxPreviewRows) as $rowNum) {
    list($colValues, $offset) = _csvParser_getNextLine($csvFilepath);
    if (!$offset) { break; }
    $previewRows[] = $colValues;
    $maxCols       = max($maxCols, count($colValues));
  }

  // show preview table header
  $columnNames = ($hasColumnHeaders) ? array_shift($previewRows) : array();
  print "<table cellspacing='0' cellpadding='0' class='data table table-striped table-hover'>\n";
  print "  <thead>\n";
  print "    <tr class='nodrag nodrop'>\n";
  foreach (range(1, $maxCols) as $colNum) {
    $columnName = array_shift($columnNames);
    print "    <th><b>";
    if ($hasColumnHeaders) { print htmlencode($columnName) . "<br>\n"; }
    else                   { print "Column #$colNum"; }
    print "</th>\n";
  }
  print "    </tr>";
  print "  </thead>";

  // show preview rows
  print "<tbody>";
  foreach ($previewRows as $row) {
    print "    <tr>\n";
    foreach (range(1, $maxCols) as $colNum) {
      $colValue = array_shift($row);
      print "    <td>" .htmlencode($colValue). "</td>\n";
    }
    print "    </tr>\n";
  }
  print "</tbody>";

  // show preview table footer
  print "</table>";
  exit;

}

//
function _csvImport_pluginSetup_createSchemas() {
  
  // install/upgrade if needed
  $versionFile = DATA_DIR . '/schema/_csv_import_v106.ini.php';
  if (!file_exists($versionFile)) {
    $schemas = array('_csv_import_jobs','_csv_import_data','_csv_import_v106');
    
    // delete old schema file and replace with new one
    foreach ($schemas as $schemaName) {
      $targetPath = DATA_DIR . "/schema/$schemaName.ini.php";
      @unlink($targetPath);
      
      $sourcePath = __DIR__ . "/pluginSchemas/$schemaName.ini.php";
      createSchemaFromFile($sourcePath);
    }

    // NOT USED: drop num field from data table
    //$dataTable  = $GLOBALS['TABLE_PREFIX'] . '_csv_import_data';
    //$alterQuery = "ALTER TABLE `$dataTable` DROP COLUMN `num`";
    //mysqli()->query($alterQuery) or die("MySQL Error:<br>\n". htmlencode(mysqli()->error) . "\n");
    
    // create new mysql tables or columns
    createMissingSchemaTablesAndFields();
  }
}



//
function csvImport_analyseForm() {
  global $TABLE_PREFIX;
  list($thisPluginPath, $thisPluginUrl) = getPluginPathAndUrl();

  // load import job
  $recordNum = @$_REQUEST['num'] ? intval($_REQUEST['num']) : 0;
  list($importJob, $csvFilepath) = _csvImport_loadImportJob($recordNum);

  // on submit...
  if (@$_REQUEST['submitForm']) {

    // error checking
    $errors = '';
    if ($_SERVER['REQUEST_METHOD'] != 'POST') { $errors .= "Form method must be POST!<br>\n"; }
    if ($errors) { alert($errors); }

    // save record
    if (!$errors) {
      $colsToValues = array(
        'updatedDate='     => 'NOW()',
        'updatedByUserNum' => $GLOBALS['CURRENT_USER']['num'],
        'columnCount'      => @$_REQUEST['columnCount'],
        'recordCount'      => @$_REQUEST['recordCount'],
      );
      mysql_update('_csv_import_jobs', $recordNum, null, $colsToValues); 

      // redirect to next page
      if (csvImport_isCustomMenu()) { $nextAction  = 'csvImport_validateForm'; } // skip ahead for pre-configured custom import menus
      else                          { $nextAction  = 'csvImport_optionsForm'; }
      
      $nextPageUrl = "?_plugin=$thisPluginPath&_pluginAction=$nextAction&num=$recordNum&menu=".urlencode(@$_REQUEST['menu']);
      redirectBrowserToURL($nextPageUrl);
      exit;
    }
  }

  //
  $goBackURL = "?_plugin=$thisPluginPath&_pluginAction=csvImport_uploadForm&num=" . $importJob['num'] . "&menu=".urlencode(@$_REQUEST['menu']);

  
  // adminUI
  // prepare adminUI() placeholders
  $adminUI = array();
  
  // page title
  $adminUI['PAGE_TITLE'] = array(
    t("Plugins") => '?menu=admin&action=plugins',
    t("CSV Import") => "?_pluginAction=csvImport_uploadForm",
  );
  
  // button
  $adminUI['BUTTONS'] = array(
    array('name' => 'simple_menu_import', 'label' => 'Simple Menu Import &gt;&gt;', 'onclick' => 'window.location="?_pluginAction=csvImport_simpleMenuImport";', 'type' => 'button'),
    array('name' => 'import_jobs', 'label' => 'Import Jobs &gt;&gt;', 'onclick' => 'window.location="?menu=_csv_import_jobs";', 'type' => 'button'),
    array('name' => 'back_to_plugins', 'label' => 'Back to Plugins &gt;&gt;', 'onclick' => 'window.location="?menu=admin&action=plugins";', 'type' => 'button'),
  );
  
  // content to display
  $adminUI['CONTENT']  = ob_capture('_csvImport_showHeader');
  $adminUI['CONTENT'] .= ob_capture(function() use($importJob, $csvFilepath, $goBackURL) {
?>

    <input type="hidden" name="_pluginAction" value="csvImport_analyseForm">
    <input type="hidden" name="num" id="num"  value="<?php echo (int) $importJob['num'] ?>">
    <input type="hidden" name="recordCount" id="recordCount" value="0"><?php // this is updated by ajax/js ?>
    <input type="hidden" name="columnCount" id="columnCount" value="0"><?php // this is updated by ajax/js ?>
    <input type="hidden" name="currentOffset" id="currentOffset" value="0"><?php // this is updated and used by ajax/js only ?>


    <script type="text/javascript">
      $(document).ready(function(){
        csvImport_analyseFile();
      });
    </script>
    
    <div class="form-horizontal">
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Import File
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo basename($csvFilepath); ?>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Column Headers
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo $importJob['hasColumnHeaders:text']; ?>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Target Table
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo htmlencode($importJob['targetTable']) ?>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Analysing Data
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <ul>
              <li>Status: <span id="status">Loading...</span></li>
              <li>Total Records: <span id="recordCountText">0</span></li>
              <li>Total Columns: <span id="columnCountText">0</span></li>
            </ul>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          <div id="pleaseWait" style="font-weight: bold; font-size: 14px">
            Please wait...
          </div>
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <div id="bottomButtons" style="display: none">
              <?php
              echo adminUI_button(['type' => 'button', 'btn-type' => 'default', 'label' => t('&lt;&lt; Go Back'), 'onclick' => "window.location='$goBackURL'"]);
              
              if (csvImport_isCustomMenu()){ // skip ahead for pre-configured custom import menus
                echo adminUI_button(['type' => 'submit', 'btn-type' => 'default', 'label' => t('Validate Data &gt;&gt;')]);
              }
              else{
                echo adminUI_button(['type' => 'submit', 'btn-type' => 'default', 'label' => t('Choose Options &gt;&gt;')]);
              }
              ?>
            </div>
          </div>
        </div>
      </div>
    </div>

  <?php });
  // display content
  adminUI($adminUI);
  exit;
}


//
function csvImport_analyseForm_ajax() {
  $dataTable = $GLOBALS['TABLE_PREFIX'] . '_csv_import_data';
  $setOffset = (int) @$_REQUEST['offset'];
  $rowNum    = (int) @$_REQUEST['rowNum'];

  // load import job
  $recordNum = @$_REQUEST['num'] ? intval($_REQUEST['num']) : 0;
  list($importJob, $csvFilepath) = _csvImport_loadImportJob($recordNum);

  // remove any old import record for this job before we start
  if (!$setOffset) {
    $deleteQuery = mysql_escapef("DELETE FROM `$dataTable` WHERE jobNum = ?", $recordNum);
    mysqli()->query($deleteQuery) or die("MySQL Error:<br>\n". htmlencode(mysqli()->error) . "\n");
  }

  // skip column headers
  if ($importJob['hasColumnHeaders'] && !$setOffset) { _csvParser_getNextLine($csvFilepath); } // skip column headers

  // get max packet size from mysql
  $maxQueryLen = coalesce(array_value(mysql_query_fetch_row_array('show variables like "max_allowed_packet"'), 1), 1024 * 1024);

  // import next line(s)
  $maxRowsPerBatch = 100;
  $batchCounter    = 0;
  $done            = false;
  $insertValues    = '';
  $maxColumnCount  = 0;

  $insertQuery     = "INSERT INTO `$dataTable` (`jobNum`, `rowNum`, `colNum`, `colValue`) VALUES ";
  $insertQueryLen  = strlen($insertQuery);

  while (!$done && $batchCounter < $maxRowsPerBatch) {
    if ($batchCounter > 0) { $rowNum++; }
    $batchCounter++;

    list($colValues, $offset) = _csvParser_getNextLine($csvFilepath, $setOffset);
    if (!$colValues) { $done = true; break; } //If we're dealing with a blank line, skip it and stop the form analysis
    $maxColumnCount = max($maxColumnCount, count($colValues));
    $done = (!$colValues && !$offset); // no results means end of file
    if ($done) { break; }

    // add insert values
    $newInsertValues = '';
    foreach ($colValues as $index => $value) {
      $colNum          =  $index + 1;
      $newInsertValues .= mysql_escapef("(?,?,?,?),\n", $recordNum, $rowNum, $colNum, $value);
    }

    // if this row would push us past $maxQueryLen, stop!
    $insertQueryLen  += strlen($newInsertValues);
    if ($insertQueryLen > $maxQueryLen) {
      if ($batchCounter == 1) { die("Import error: Row $rowNum is larger than your database allows. Ask your server admin to increase MySQL's 'max_allowed_packet' setting (currently '$maxQueryLen' bytes)!"); }
      $rowNum--; // we'll process this row in the next batch
      break;
    }

    // add row
    $insertValues .= $newInsertValues;
  }

  // import row(s)
  if ($insertValues) {
    $insertValues = chop($insertValues, ",\n"); // remove trailing comma
    $insertQuery .= $insertValues;
    mysqli()->query($insertQuery) or die("MySQL Error:<br>\n". htmlencode(mysqli()->error) . "\n");
  }

  //
  print json_encode(array(
    'offset'      => $offset,
    'columnCount' => $maxColumnCount,
    'lastRowNum'  => $rowNum,
    'done'        => $done,
  ));
  exit;

}


//
function csvImport_optionsForm() {
  global $TABLE_PREFIX;
  list($thisPluginPath, $thisPluginUrl) = getPluginPathAndUrl();

  // load import job
  $recordNum = @$_REQUEST['num'] ? intval($_REQUEST['num']) : 0;
  list($importJob, $csvFilepath) = _csvImport_loadImportJob($recordNum);
  $alreadyEntered = $importJob['mapFields']; // if map fields is defined then we've already saved on this page and then clicked "Back"
  
  // set defaults
  if (!$alreadyEntered) { 
    if (!array_key_exists('useIdField', $_REQUEST))       { $_REQUEST['useIdField']       = $GLOBALS['CSV_IMPORT_DEFAULTS']['USE_ID_FIELD']; }
    if (!array_key_exists('idField', $_REQUEST))          { $_REQUEST['idField']          = $GLOBALS['CSV_IMPORT_DEFAULTS']['ID_FIELD']; }
    if (!array_key_exists('updateDuplicates', $_REQUEST)) { $_REQUEST['updateDuplicates'] = $GLOBALS['CSV_IMPORT_DEFAULTS']['UPDATE_DUPLICATES']; }
    if (!array_key_exists('removeOrphans', $_REQUEST))    { $_REQUEST['removeOrphans']    = $GLOBALS['CSV_IMPORT_DEFAULTS']['REMOVE_ORPHANS']; }
    if (!array_key_exists('wipeDatabase', $_REQUEST))     { $_REQUEST['wipeDatabase']     = $GLOBALS['CSV_IMPORT_DEFAULTS']['WIPE_DATABASE']; }
    if (!array_key_exists('setAutoIncrement', $_REQUEST)) { $_REQUEST['setAutoIncrement'] = $GLOBALS['CSV_IMPORT_DEFAULTS']['SET_AUTO_INCREMENT']; }
    if (!array_key_exists('newAutoIncrement', $_REQUEST)) { $_REQUEST['newAutoIncrement'] = $GLOBALS['CSV_IMPORT_DEFAULTS']['NEW_AUTO_INCREMENT']; }
  }

  // on submit...
  if (@$_REQUEST['submitForm']) {

    function __isDefinedTargetField($key) { return (preg_match("/^targetFieldForCol/", $key) && @$_REQUEST[$key]); }
    $definedTargetFieldsCount = count(array_filter(array_keys($_REQUEST), '__isDefinedTargetField'));

    function __isThisTheIdField($key) { return (@$_REQUEST['idField'] && preg_match("/^targetFieldForCol/", $key) && @$_REQUEST[$key] == $_REQUEST['idField']); }
    $idFieldMapCount = count(array_filter(array_keys($_REQUEST), '__isThisTheIdField'));

    // error checking
    $errors = '';
    if     ($_SERVER['REQUEST_METHOD'] != 'POST') { $errors .= "Form method must be POST!<br>\n"; }
    if (@$_REQUEST['useIdField']) {
      if     (!@$_REQUEST['updateDuplicates'] &&
              !@$_REQUEST['removeOrphans'])      { $errors .= "Use ID Field: This setting isn't useful unless you select 'Update Duplicate' or 'Remove Orphans'.<br>\n"; }
      elseif (!@$_REQUEST['idField'])            { $errors .= "Use ID Field: You must select a fieldname.<br>\n"; }
      elseif (!$idFieldMapCount)                 { $errors .= "Use ID Field: You must select the ID Field as one of the target fields under 'Map Fields'.<br>\n"; }
      elseif ($idFieldMapCount > 1)              { $errors .= "Use ID Field: ID Field is mapped more than once under 'Map Fields'.<br>\n"; }
    }
    else {
      if (@$_REQUEST['idField'])                 { $errors .= "ID Fieldname: You must select 'Use ID Field' to use this feature.<br>\n"; }
      if (@$_REQUEST['updateDuplicates'])        { $errors .= "Update Duplicates: You must select 'Use ID Field' to use this feature.<br>\n"; }
      if (@$_REQUEST['removeOrphans'])           { $errors .= "Remove Orphans: You must select 'Use ID Field' to use this feature.<br>\n"; }
    }
    
    if (@$_REQUEST['isCustomMenu']) {
      if (!@$_REQUEST['customMenuName'])         { $errors .= "Create Menu: You must specify a menu name.<br>\n"; }
    }
    else { 
      if (@$_REQUEST['customMenuName'])          { $errors .= "Menu Name: You must select 'Create Menu' to use this feature.<br>\n"; }
    }
    
    if (@$_REQUEST['setAutoIncrement'] && !@$_REQUEST['wipeDatabase']) { $errors .= "Start Numbering: You must select 'Wipe Database' to use this feature.<br>\n"; }
    if (!$definedTargetFieldsCount) { $errors .= "No target fields selected!  You must select at least one target field!<br>\n"; }

    // create fields as necessary
    $targetFields    = _csvImport_fieldMapToArray(_csvImport_arrayToFieldMap($_REQUEST, 'targetFieldForCol'));
    $schema          = loadSchema($importJob['targetTable']);
    $needToAddFields = false;
    $fieldCount      = 0;
    foreach ($targetFields as $fieldName) {
      if (!$fieldName) { continue; } //skip empty array elements caused when "Skip Field" is selected

      $fieldCount++;
      if (!@$schema[$fieldName]) {
        $reservedFieldnames = explode(' ', "menu menuName menuType menuOrder menuHidden tableHidden listPageFields listPageOrder listPageSearchFields length order action page");
        if (in_array($fieldName, $reservedFieldnames))  { $errors .= "Field Creation: '$fieldName' is a reserved field name"; continue; }
        if (preg_match('/^_/', $fieldName))             { $errors .= "Field Creation: '$fieldName' cannot start with an underscore"; continue; }
        if (strlen($fieldName) > 64 )                   { $errors .= "Field Creation: '$fieldName' cannot be longer than 64 characters."; continue; }
        if (preg_match('/[^a-z0-9\_\-]/i', $fieldName)) { $errors .= "Field Creation: '$fieldName' can only contain the following characters (a-z, A-Z, 0-9, - and _)"; continue; }
        if (preg_match('/^\d+$/', $fieldName))          { $errors .= "Field Creation: '$fieldName' cannot contain numbers only"; continue; }

        // create field
        $schema[$fieldName] = array(
          'order'           => time() + $fieldCount,
          'label'           => $fieldName,
          'type'            => "textfield",
          'defaultValue'    => "",
          'isPasswordField' => 0,
          'isRequired'      => 0,
          'isUnique'        => 0,
          'minLength'       => "",
          'maxLength'       => "",
          'charsetRule'     => "",
          'charset'         => "",
        );
        $needToAddFields = true;
      }
    }
    if ($needToAddFields) {
      saveSchema($importJob['targetTable'], $schema);
      createMissingSchemaTablesAndFields();
    }

    if ($errors) { alert($errors); }

    // save record
    if (!$errors) {
      $mapFields    = _csvImport_arrayToFieldMap($_REQUEST, 'targetFieldForCol');
      $colsToValues = array(
        'updatedDate='       => 'NOW()',
        'updatedByUserNum'   => @$GLOBALS['CURRENT_USER']['num'],
        'useIdField'         => @$_REQUEST['useIdField'],
          'idField'          => @$_REQUEST['idField'],
          'updateDuplicates' => @$_REQUEST['updateDuplicates'],
          'removeOrphans'    => @$_REQUEST['removeOrphans'],
        'wipeDatabase'       => @$_REQUEST['wipeDatabase'],
          'setAutoIncrement' => @$_REQUEST['setAutoIncrement'],
          'newAutoIncrement' => @$_REQUEST['newAutoIncrement'],
        'isCustomMenu'       => @$_REQUEST['isCustomMenu'],
          'customMenuName'   => @$_REQUEST['customMenuName'],
        'mapFields'          => $mapFields,
      );
      mysql_update('_csv_import_jobs', $recordNum, null, $colsToValues);
      
      // redirect to next page
      $nextAction = 'csvImport_validateForm';
      if (@$GLOBALS['CSV_IMPORT_ALLOW_SKIPVALIDATION'] && @$_REQUEST['skipValidation']) {
        $nextAction = 'csvImport_importForm';
      }
      $nextPageUrl = "?_plugin=$thisPluginPath&_pluginAction=$nextAction&num=$recordNum&menu=".urlencode(@$_REQUEST['menu']);
      redirectBrowserToURL($nextPageUrl);
      exit;
    }

  }

  //
  $goBackURL = "?_plugin=$thisPluginPath&_pluginAction=csvImport_analyseForm&num=$recordNum&menu=".urlencode(@$_REQUEST['menu']);

  // adminUI
  // prepare adminUI() placeholders
  $adminUI = array();
  
  // page title
  $adminUI['PAGE_TITLE'] = array(
    t("Plugins") => '?menu=admin&action=plugins',
    t("CSV Import") => "?_pluginAction=csvImport_uploadForm",
  );
  
  // button
  $adminUI['BUTTONS'] = array(
    array('name' => 'simple_menu_import', 'label' => 'Simple Menu Import &gt;&gt;', 'onclick' => 'window.location="?_pluginAction=csvImport_simpleMenuImport";', 'type' => 'button'),
    array('name' => 'import_jobs', 'label' => 'Import Jobs &gt;&gt;', 'onclick' => 'window.location="?menu=_csv_import_jobs";', 'type' => 'button'),
    array('name' => 'back_to_plugins', 'label' => 'Back to Plugins &gt;&gt;', 'onclick' => 'window.location="?menu=admin&action=plugins";', 'type' => 'button'),
  );
  
  // content to display
  $adminUI['CONTENT']  = ob_capture('_csvImport_showHeader');
  $adminUI['CONTENT'] .= ob_capture(function() use($goBackURL, $importJob, $csvFilepath) {
    
    // get target table fields and counts
    list($targetTableFieldsToFieldSchemas, $targetTableColumnCount, $targetTableRecordCount) = _csvImport_getTargetTableDetails( $importJob['targetTable'] );
    
    //
    $formValues = _csvImport_getFormValues($importJob, $_REQUEST);
    ?>

    <input type="hidden" name="_pluginAction" value="csvImport_optionsForm">
    <input type="hidden" name="num"           value="<?php echo (int) $formValues['num'] ?>">
    <input type="hidden" name="preSaveTempId" value="">
    <input type="checkbox" name="hasColumnHeaders" value="1" style="display: none" <?php checkedIf(@$formValues['hasColumnHeaders'], 1) ?>>

    <script type="text/javascript">
      $(document).ready(function(){
        csvImport_updatePreview();
      });
    </script>
    
    <div class="form-horizontal">
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Import File
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo basename($csvFilepath); ?> (<?php echo $importJob['columnCount'] ?> columns, <?php echo $importJob['recordCount'] ?> records)
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Column Headers
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo $importJob['hasColumnHeaders:text']; ?>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Target Table
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo htmlencode($importJob['targetTable']) ?> (<?php echo $targetTableColumnCount ?> columns, <?php echo $targetTableRecordCount ?> records)
          </div>
        </div>
      </div>
      
      <?php /*
      <div class="form-group">
        <div class="col-sm-2 control-label">
          CSV Charset
        </div>
        <div class="col-sm-10">
          <?php
            $optionsArray = array('CP1252', 'ASCII', 'ISO-8859-1'); // More: http://php.net/manual/en/mbstring.supported-encodings.php
            $optionsHTML  = getSelectOptions(@$formValues['charset'], $optionsArray);
          ?>
          <select name="charset" class="form-control">
            <option value="">Auto-detect</option>
            <?php echo $optionsHTML ?>
          </select>
          <p class="help-block">If data doesn't look right try different charsets</p>
        </div>
      </div>
      */ ?>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Advanced Options
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            
            <input type="hidden" name="useIdField" value="0">
            <label><input type="checkbox" name="useIdField" value="1" <?php checkedIf(@$formValues['useIdField'], 1) ?> style="vertical-align: baseline">
            <b>Use ID Field</b> - Duplicate records have the same value in this field</label>

            <?php $optionsHTML = getSelectOptions(@$formValues['idField'], array_keys($targetTableFieldsToFieldSchemas)); ?>
            <select name="idField" style="padding: 0px">
              <option value="">&lt;select field&gt;</option>
              <?php echo $optionsHTML; ?>
            </select>
            </dt>

            <div style="margin-left: 30px">
              <input type="hidden" name="updateDuplicates" value="0">
              <label><input type="checkbox" name="updateDuplicates" value="1" <?php checkedIf(@$formValues['updateDuplicates'], 1) ?> style="vertical-align: baseline">
              Update Duplicates: Update existing duplicate records instead of adding them again</label><br>

              <input type="hidden" name="removeOrphans" value="0">
              <label><input type="checkbox" name="removeOrphans" value="1" <?php checkedIf(@$formValues['removeOrphans'], 1) ?> style="vertical-align: baseline">
              Remove Orphans: Remove existing records if their ID isn't found in the import file</label><br>
            </div>

            <input type="hidden" name="wipeDatabase" value="0">
            <label><input type="checkbox" name="wipeDatabase" value="1" <?php checkedIf(@$formValues['wipeDatabase'], 1) ?> style="vertical-align: baseline">
            <b>Wipe Database:</b> Remove all existing records before importing new records</label><br>

            <div style="margin-left: 30px">
              <input type="hidden" name="setAutoIncrement" value="0">
              <label><input type="checkbox" name="setAutoIncrement" value="1" <?php checkedIf(@$formValues['setAutoIncrement'], 1) ?> style="vertical-align: baseline">
              Start numbering new records at</label>
              <input type="text" name="newAutoIncrement" value="<?php echo htmlencode( @$formValues['newAutoIncrement'] ? intval($formValues['newAutoIncrement']) : 1) ?>" size="5" maxlength='11'><br>
            </div>

            <?php if (@$GLOBALS['CSV_IMPORT_ALLOW_SKIPVALIDATION']): ?>
              <input type="hidden" name="skipValidation" value="0">
              <label><input type="checkbox" name="skipValidation" value="1" <?php checkedIf(@$formValues['skipValidation'], 1) ?> style="vertical-align: baseline">
              <b>Skip Validation:</b> This option is unsafe and can leave your table half-imported!</label><br>
            <?php endif; ?>

            <!-- Create Menu --> 
            <input type="hidden" name="isCustomMenu" value="0">
            <label><input type="checkbox" name="isCustomMenu" value="1" <?php checkedIf(@$formValues['isCustomMenu'], 1) ?> style="vertical-align: baseline">
            <b>Create Menu: </b> Create a new menu that automatically imports with these settings.</label><br>
            <div style="margin-left: 55px">
              Menu name: <input type="text" name="customMenuName" value="<?php echo htmlencode(@$formValues['customMenuName']); ?>" size="25"><br>
            </div>
            
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Map Fields
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <!-- map fields -->
            <table cellspacing="0" cellpadding="0" class="data table table-striped table-hover" style="width: auto">
              <thead>
                <tr>
                 <th style="font-size: 12px; padding: 0 10px">Col #</th>
                 <th style="font-size: 12px">Col Name</th>
                 <th style="font-size: 12px; padding: 0 10px">to</th>
                 <th style="font-size: 12px">Target Field</th>
                </tr>
              </thead>
  
              <?php $importFileColumnNames = _csvImport_fieldMapToArray( $importJob['columnNamesMap'] ); ?>
              <?php foreach ($importFileColumnNames as $columnNum => $importColumnName): ?>
                <?php $bgClass = (@$bgClass == 'listRowOdd') ? 'listRowEven' : 'listRowOdd'; ?>
                <?php
                  $selectedValue = @$formValues["targetFieldForCol$columnNum"];
                  if (!$selectedValue && @$targetTableFieldsToFieldSchemas[$importColumnName]) { $selectedValue = $importColumnName; }
                ?>
                <tr class='<?php echo $bgClass?>'>
                  <td align="center" style="text-align: center"><?php echo $columnNum ?></td>
                  <td><?php echo htmlencode($importColumnName) ?></td>
                  <td>&nbsp; to &nbsp;</td>
                  <td>
                    <?php $targetFieldOptions = getSelectOptions($selectedValue, array_keys($targetTableFieldsToFieldSchemas)); ?>
                    <select name="targetFieldForCol<?php echo $columnNum ?>" style="padding: 2px; " class="form-control">
                      <option value="">&lt;skip this field&gt;</option>
                      <?php if (!@$targetTableFieldsToFieldSchemas[$importColumnName]): ?>
                        <option value="<?php echo htmlencode($importColumnName); ?>">&lt;create new field&gt;</option>
                      <?php endif; ?>
                      <?php echo $targetFieldOptions; ?>
                    </select>
  
                  </td>
                </tr>
              <?php endforeach ?>
            </table>
            <!-- /map fields -->
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          
        </div>
        <div class="col-sm-10">
          <?php
          echo adminUI_button(['type' => 'button', 'btn-type' => 'default', 'label' => t('&lt;&lt; Go Back'), 'onclick' => "window.location='$goBackURL'"]);
          echo adminUI_button(['type' => 'submit', 'btn-type' => 'default', 'label' => t('Validate Data &gt;&gt')]);
          ?>
        </div>
      </div>
    
    </div>
    
    
    <br>
    <?php echo adminUI_separator(t('Live Preview')) ?>

    <div style="height: 50px; overflow: auto" id="previewContent">
      <div style="padding: 10px; text-align: center">Loading...</div><br>
    </div>

  <?php });
  // display content
  adminUI($adminUI);
  exit;
}



//
function csvImport_validateForm() {
  global $TABLE_PREFIX;
  list($thisPluginPath, $thisPluginUrl) = getPluginPathAndUrl();

  // load import job
  $recordNum = @$_REQUEST['num'] ? intval($_REQUEST['num']) : 0;
  list($importJob, $csvFilepath) = _csvImport_loadImportJob($recordNum);
  
  // on submit...
  if (@$_REQUEST['submitForm']) {

    // error checking
    $errors = '';
    if ($_SERVER['REQUEST_METHOD'] != 'POST') { $errors .= "Form method must be POST!<br>\n"; }
    if (@$_REQUEST['validationComplete'])     { $errors .= "You must wait for the data to be validated before you continue!<br>\n"; }
    if ($errors) { alert($errors); }

    // save record
    if (!$errors) {

      // redirect to next page
      $nextAction  = 'csvImport_importForm';
      $nextPageUrl = "?_plugin=$thisPluginPath&_pluginAction=$nextAction&num=$recordNum&menu=".urlencode(@$_REQUEST['menu']);
      redirectBrowserToURL($nextPageUrl);
      exit;

    }
  }

  //
  $goBackURL = "?_plugin=$thisPluginPath&_pluginAction=csvImport_optionsForm&num=" . $importJob['num'] . "&menu=".urlencode(@$_REQUEST['menu']);
  
  
  // adminUI
  // prepare adminUI() placeholders
  $adminUI = array();
  
  // page title
  $adminUI['PAGE_TITLE'] = array(
    t("Plugins") => '?menu=admin&action=plugins',
    t("CSV Import") => "?_pluginAction=csvImport_uploadForm",
  );
  
  // button
  $adminUI['BUTTONS'] = array(
    array('name' => 'simple_menu_import', 'label' => 'Simple Menu Import &gt;&gt;', 'onclick' => 'window.location="?_pluginAction=csvImport_simpleMenuImport";', 'type' => 'button'),
    array('name' => 'import_jobs', 'label' => 'Import Jobs &gt;&gt;', 'onclick' => 'window.location="?menu=_csv_import_jobs";', 'type' => 'button'),
    array('name' => 'back_to_plugins', 'label' => 'Back to Plugins &gt;&gt;', 'onclick' => 'window.location="?menu=admin&action=plugins";', 'type' => 'button'),
  );
  
  // content to display
  $adminUI['CONTENT']  = ob_capture('_csvImport_showHeader');
  $adminUI['CONTENT'] .= ob_capture(function() use($importJob, $csvFilepath, $goBackURL) {
    
    $formValues = _csvImport_getFormValues($importJob); // don't use request values for this, just get field maps
  
    // get target table fields and counts
    list($targetTableFieldsToFieldSchemas, $targetTableColumnCount, $targetTableRecordCount) = _csvImport_getTargetTableDetails( $importJob['targetTable'] );
    ?>

    <script type="text/javascript">
      $(document).ready(function(){
        csvImport_importFile('useTempTable');
      });
    </script>

    <input type="hidden" name="_pluginAction" value="csvImport_validateForm">
    <input type="hidden" name="num" id="num"  value="<?php echo (int) $importJob['num'] ?>">
    <input type="hidden" name="currentRowNum" id="currentRowNum" value="0"><?php // this is updated by ajax/js ?>
    <input type="hidden" name="currentOffset" id="currentOffset" value="0"><?php // this is updated and used by ajax/js only ?>
    <input type="hidden" name="totalRows" id="totalRows" value="<?php echo $importJob['recordCount'] ?>"><?php // this is updated and used by ajax/js only ?>
    
    <div class="form-horizontal">
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Import File
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo basename($csvFilepath); ?> (<?php echo $importJob['columnCount'] ?> columns, <?php echo $importJob['recordCount'] ?> records)
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Column Headers
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo $importJob['hasColumnHeaders:text']; ?>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Target Table
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo htmlencode($importJob['targetTable']) ?> (<?php echo $targetTableColumnCount ?> columns, <?php echo $targetTableRecordCount ?> records)
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Advanced Options
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <ul style="padding-bottom: 0">
              
              <?php if ($formValues['useIdField']): ?>
              <li>
                <b>Use ID Field</b> - Duplicate records have the same value in this field: <b><?php echo htmlencode(@$importJob['idField']) ?></b>
                <?php if ($formValues['updateDuplicates'] || $formValues['removeOrphans']): ?>
                  <ul>
                    <?php if ($formValues['updateDuplicates']): ?><li>Update Duplicates: Update existing duplicate records instead of adding them again</li><?php endif ?>
                    <?php if ($formValues['removeOrphans']):    ?><li>Remove Orphans: Remove existing records if their ID isn't found in the import file</li><?php endif ?>
                  </ul>
                <?php endif; ?>
              </li>
              <?php endif; ?>
    
              <?php if ($formValues['wipeDatabase']): ?>
              <li>
                <b>Wipe Database:</b> Remove all existing records before importing new records<br>
                <?php if ($formValues['setAutoIncrement']): ?>
                  <ul>
                    <li>Start numbering new records at: <b><?php echo htmlencode(@$formValues['newAutoIncrement']) ?></b></li>
                  </ul>
                <?php endif ?>
              </li>
              <?php endif; ?>
                
              <?php if ($formValues['isCustomMenu']): ?>
              <li>
                <b>Create Menu:</b> Create a new menu that automatically imports with these settings.<br>
                <ul><li>Menu name: <b><?php echo htmlencode(@$formValues['customMenuName']) ?></b></li></ul>
              </li>
              <?php endif; ?>
            </ul>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Map Fields
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <!-- map fields -->
            <table cellspacing="0" cellpadding="0" class="data table table-striped table-hover" style="width: inherit">
              <thead>
                <tr>
                 <th style="font-size: 13px; padding: 0 10px">Col #</th>
                 <th style="font-size: 13px">Col Name</th>
                 <th style="font-size: 13px; padding: 0 10px">to</th>
                 <th style="font-size: 13px">Target Field</th>
                </tr>
              </thead>
  
              <?php $importFileColumnNames = _csvImport_fieldMapToArray( $importJob['columnNamesMap'] ); ?>
              <?php foreach ($importFileColumnNames as $columnNum => $importColumnName): ?>
                <?php $bgClass = (@$bgClass == 'listRowOdd') ? 'listRowEven' : 'listRowOdd'; ?>
                <tr class='<?php echo $bgClass?>'>
                  <td align="center" style="text-align: center"><?php echo $columnNum ?></td>
                  <td><?php echo htmlencode($importColumnName) ?></td>
                  <td>&nbsp; to &nbsp;</td>
                  <td style="padding: 0 10px 0 0"><?php echo htmlencode( @$formValues["targetFieldForCol$columnNum"] ) ?></td>
                </tr>
              <?php endforeach ?>
            </table>
            <!-- /map fields -->
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Validating Data
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <ul>
              <li>Status: <span id="status"></span></li>
              <?php if ($importJob['updateDuplicates']): ?>
                <li>Duplicates Found: <span id="duplicatesFound">0</span> (these will be updated instead of added)</li>
              <?php endif ?>
              <?php if ($importJob['removeOrphans']): ?>
                <li>Orphans Found: <span id="orphansFound">0</span> (these will be removed from database)</li>
              <?php endif ?>
            </ul>
  
            <div id="errors_div">
              <b>Errors:</b>
              <div style="color: #000; padding: 6px 12px;" id="noerrors">No errors so far.</div>
              <div style="color: #C00; padding: 6px 12px;" id="errors"></div>
            </div>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
        </div>
        <div class="col-sm-10">
          <div id="complete_buttons_div" style="display: none;">
            <?php echo adminUI_button(['type' => 'button', 'btn-type' => 'default', 'label' => t('&lt;&lt; Go Back'), 'onclick' => "window.location='$goBackURL'"]); ?>
            <?php echo adminUI_button(['type' => 'submit', 'btn-type' => 'default', 'label' => t('Import Data &gt;&gt;')]); ?>
          </div>
        </div>
      </div>
      
    </div>

  <?php });
  // display content
  adminUI($adminUI);
  exit;
}


//
function csvImport_importForm() {
  
  // adminUI
  // prepare adminUI() placeholders
  $adminUI = array();
  
  // page title
  $adminUI['PAGE_TITLE'] = array(
    t("Plugins") => '?menu=admin&action=plugins',
    t("CSV Import") => "?_pluginAction=csvImport_uploadForm",
  );
  
  // button
  $adminUI['BUTTONS'] = array(
    array('name' => 'simple_menu_import', 'label' => 'Simple Menu Import &gt;&gt;', 'onclick' => 'window.location="?_pluginAction=csvImport_simpleMenuImport";', 'type' => 'button'),
    array('name' => 'import_jobs', 'label' => 'Import Jobs &gt;&gt;', 'onclick' => 'window.location="?menu=_csv_import_jobs";', 'type' => 'button'),
    array('name' => 'back_to_plugins', 'label' => 'Back to Plugins &gt;&gt;', 'onclick' => 'window.location="?menu=admin&action=plugins";', 'type' => 'button'),
  );
  
  // content to display
  $adminUI['CONTENT']  = ob_capture('_csvImport_showHeader');
  $adminUI['CONTENT'] .= ob_capture(function() {
    global $TABLE_PREFIX;
    list($thisPluginPath, $thisPluginUrl) = getPluginPathAndUrl();
  
    // load import job
    $recordNum = @$_REQUEST['num'] ? intval($_REQUEST['num']) : 0;
    list($importJob, $csvFilepath) = _csvImport_loadImportJob($recordNum);
    $formValues = _csvImport_getFormValues($importJob); // don't use request values for this, just get field maps
    // get target table fields and counts
    list($targetTableFieldsToFieldSchemas, $targetTableColumnCount, $targetTableRecordCount) = _csvImport_getTargetTableDetails( $importJob['targetTable'] );
    ?>

    <script type="text/javascript">
      $(document).ready(function(){
        csvImport_importFile(); // no argument means _don't_ use temp table
      });
    </script>

    <input type="hidden" name="_pluginAction" value="csvImport_validateForm">
    <input type="hidden" name="num" id="num"  value="<?php echo (int) $importJob['num'] ?>">
    <input type="hidden" name="currentRowNum" id="currentRowNum" value="0"><?php // this is updated by ajax/js ?>
    <input type="hidden" name="currentOffset" id="currentOffset" value="0"><?php // this is updated and used by ajax/js only ?>
    <input type="hidden" name="totalRows" id="totalRows" value="<?php echo $importJob['recordCount'] ?>"><?php // this is updated and used by ajax/js only ?>

    <div class="form-horizontal">
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Import File
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo basename($csvFilepath); ?> (<?php echo $importJob['columnCount'] ?> columns, <?php echo $importJob['recordCount'] ?> records)
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Column Headers
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo $importJob['hasColumnHeaders:text']; ?>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Target Table
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php echo htmlencode($importJob['targetTable']) ?> (<?php echo $targetTableColumnCount ?> columns, <?php echo $targetTableRecordCount ?> records)
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Advanced Options
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <ul style="padding-bottom: 0">

              <?php if ($formValues['useIdField']): ?>
              <li><b>Use ID Field</b> - Duplicate records have the same value in this field: <b><?php echo htmlencode(@$importJob['idField']) ?></b>
                <?php if ($formValues['updateDuplicates'] || $formValues['removeOrphans']): ?>
                <ul>
                  <?php if ($formValues['updateDuplicates']): ?><li>Update Duplicates: Update existing duplicate records instead of adding them again</li><?php endif ?>
                  <?php if ($formValues['removeOrphans']):    ?><li>Remove Orphans: Remove existing records if their ID isn't found in the import file</li><?php endif ?>
                </ul>
                <?php endif; ?>
              </li>
              <?php endif; ?>
  
              <?php if ($formValues['wipeDatabase']): ?>
              <li><b>Wipe Database:</b> Remove all existing records before importing new records<br>
                <?php if ($formValues['setAutoIncrement']): ?>
                <ul>
                  <li>Start numbering new records at: <b><?php echo htmlencode(@$formValues['newAutoIncrement']) ?></b></li>
                </ul>
                <?php endif ?>
              </li>
              <?php endif; ?>
  
            </ul>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Map Fields
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <!-- map fields -->
            <table cellspacing="0" cellpadding="0" class="data table table-striped table-hover" style="width: inherit">
              <thead>
                <tr>
                 <th style="font-size: 13px; padding: 0 10px">Col #</th>
                 <th style="font-size: 13px">Col Name</th>
                 <th style="font-size: 13px; padding: 0 10px">to</th>
                 <th style="font-size: 13px">Target Field</th>
                </tr>
              </thead>
  
              <?php $importFileColumnNames = _csvImport_fieldMapToArray( $importJob['columnNamesMap'] ); ?>
              <?php foreach ($importFileColumnNames as $columnNum => $importColumnName): ?>
                <?php $bgClass = (@$bgClass == 'listRowOdd') ? 'listRowEven' : 'listRowOdd'; ?>
                <tr class='<?php echo $bgClass?>'>
                  <td align="center" style="text-align: center"><?php echo $columnNum ?></td>
                  <td><?php echo htmlencode($importColumnName) ?></td>
                  <td>&nbsp; to &nbsp;</td>
                  <td style="padding: 0 10px 0 0"><?php echo htmlencode( @$formValues["targetFieldForCol$columnNum"] ) ?></td>
                </tr>
              <?php endforeach ?>
            </table>
            <!-- /map fields -->
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Creating Backup
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <?php backupDatabase(null, $importJob['targetTable']); ?>
            Backup created of table: <?php echo htmlencode($importJob['targetTable']); ?>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          Importing Data
        </div>
        <div class="col-sm-10 control-label">
          <div class="text-left">
            <ul>
              <li>Status: <span id="status"></span></li>
              <?php if ($importJob['updateDuplicates']): ?>
                <li>Duplicates Found: <span id="duplicatesFound">0</span> (these will be updated instead of added)</li>
              <?php endif ?>
              <?php if ($importJob['removeOrphans']): ?>
                <li>Orphans Found: <span id="orphansFound">0</span> (these will be removed from database)</li>
              <?php endif ?>
            </ul>
  
            <div id="errors_div">
              <b>Errors:</b>
              <div style="color: #000; padding: 6px 12px;" id="noerrors">No errors so far.</div>
              <div style="color: #C00; padding: 6px 12px;" id="errors"></div>
            </div>
          </div>
        </div>
      </div>
      
      <div class="form-group">
        <div class="col-sm-2 control-label">
          
        </div>
        <div class="col-sm-10">
          <?php
            $targetSchema   = loadSchema($importJob['targetTable']);
            $targetMenuName = coalesce(@$targetSchema['menuName'], "Target Table");
          ?>
          <div id="complete_buttons_div" style="display: none;">
            <?php
              if (csvImport_isCustomMenu()) { $importAgainURL = "?menu=".urlencode(@$_REQUEST['menu']); }
              else                          { $importAgainURL = '?_plugin=' .htmlencode($thisPluginPath). '&_pluginAction=csvImport_uploadForm'; }
            ?>
            
            <?php echo adminUI_button(['type' => 'button', 'btn-type' => 'default', 'label' => t('&lt;&lt; Import Another File'),                   'onclick' => "window.location='{$importAgainURL}'"]); ?>
            <?php echo adminUI_button(['type' => 'button', 'btn-type' => 'default', 'label' => t('Go to '.htmlencode($targetMenuName).' &gt;&gt;'), 'onclick' => "window.location='?menu=".htmlencode($importJob['targetTable'])."'"]); ?>
            
            <?php if ($importJob['isCustomMenu']): ?>
              <?php echo adminUI_button(['type' => 'button', 'btn-type' => 'default', 'label' => t('Go to Custom Import Menu &gt;&gt;'), 'onclick' => "window.location='?menu=_csv_import_menu_".htmlencode($importJob['num'])."'"]); ?>
            <?php endif ?>
          </div>
        </div>
      </div>
      
    </div>

  <?php });
  // display content
  adminUI($adminUI);
  exit;
}


//
function csvImport_importForm_ajax($setOffset = 0) {
  global $TABLE_PREFIX;
  $dataTable    = getTableNameWithPrefix('_csv_import_data');
  $rowNum       = (int) @$_REQUEST['rowNum'];
  $useTempTable = $_REQUEST['useTempTable'];
  if (!array_key_exists('useTempTable', $_REQUEST)) { die("No value specified for 'useTempTable'!"); }

  // load import job
  $recordNum = @$_REQUEST['num'] ? intval($_REQUEST['num']) : 0;
  list($importJob, $csvFilepath) = _csvImport_loadImportJob($recordNum);
  $formValues = _csvImport_getFormValues($importJob); // don't use request values for this, just get field maps

  $tempTable           = '_csv_import_temp_job' . $recordNum;
  $targetTable         = $useTempTable ? $tempTable : $importJob['targetTable'];
  $prefixedTargetTable = getTableNameWithPrefix( $targetTable );

  // create temp table if needed
  if ($useTempTable && ($rowNum == 1)) { _csvImport_importForm_ajax_createTempTable($importJob, $tempTable); }

  // wipe database - and reset counter
  if ($importJob['wipeDatabase'] && ($rowNum == 1)) { _csvImport_importForm_ajax_resetTableAndCounter($importJob, $targetTable); }

  ### import row(s)
  $maxRowsPerBatch  = 25;
  $batchCounter     = 0;
  $sqlInsertCounter = 0;
  $sqlUpdateCounter = 0;
  $done             = false;
  $errors           = '';

  // Update Duplicates: get row nums to update (import row number to target record number)
  $sqlUpdate_rowNumsToRecordNums = array();
  if ($importJob['updateDuplicates'] && $importJob['useIdField'] && $importJob['idField']) {
    $idFieldColumnNum = _getIdFieldColumnNum($importJob, $formValues);

    list($minRowNum, $maxRowNum) = array( intval($rowNum), intval($rowNum+$maxRowsPerBatch) );
    $rowNumsQuery  = "SELECT d.rowNum, t.num FROM `$prefixedTargetTable` t\n";
    $rowNumsQuery .= "  JOIN `$dataTable` d\n";
    $rowNumsQuery .= "    ON t.`".$importJob['idField']."` = colValue\n";
    $rowNumsQuery .= mysql_escapef("    WHERE d.jobNum = ? AND d.colNum = ? AND d.rowNum BETWEEN ? AND ?", $importJob['num'], $idFieldColumnNum, $minRowNum, $maxRowNum);
    $records       = mysql_query_fetch_all_array($rowNumsQuery);
    foreach ($records as $values) {
      $sqlUpdate_rowNumsToRecordNums[ $values[0] ] = $values[1];
    }
  }

  // Insert/Update record
  $targetFieldnames    = array_filter(array_unique(array_values( $formValues['colsToTargetFields'] ))); // remove blanks and duplicates
  $columnNumsToNames   = _csvImport_fieldMapToArray( $importJob['columnNamesMap'] );
  $mysqlColumnsToTypes = getMySqlColsAndType( $prefixedTargetTable );

  // get SET values for non-target fields
  $setForDefaultFields = '';
  foreach (getMySqlColsAndType( $prefixedTargetTable ) as $fieldname => $mysqlColType) {
    if (in_array($fieldname, $targetFieldnames)) { continue; }
    if     ($fieldname == 'num') { continue; }
    if     (preg_match("/^datetime/i", $mysqlColType))   { $setForDefaultFields .= ", `$fieldname` = '0000-00-00 00:00:00'\n"; }
    elseif (preg_match("/^(tiny)?int/i", $mysqlColType)) { $setForDefaultFields .= ", `$fieldname` = 0\n"; }
    else                                                 { $setForDefaultFields .= ", `$fieldname` = DEFAULT\n"; }
  }
   //showme(getMySqlColsAndType( $prefixedTargetTable )); exit;

  //
  foreach (range(1, $maxRowsPerBatch) as $mycount) {
    if ($batchCounter++ > 0) { $rowNum++; }

    // load row
    $query           = mysql_escapef("SELECT colNum, colValue FROM `$dataTable` WHERE jobNum = ? AND rowNum = ? ORDER BY colNum", $importJob['num'], $rowNum);
    $recordSet       = mysql_query_fetch_all_array($query);
    $colValues       = array_pluck($recordSet, 1);
    //$colNums         = array_pluck($recordSet, 0);
    //$colNumsToValues = array_combine($colNums, $colValues);
    $done            = (!$recordSet); // no results means end of import job
    if ($done) { break; }

    // insert or update row
    $mysqlSet            = _csvImport_importForm_ajax_getQuerySetString($prefixedTargetTable, $columnNumsToNames, $formValues['colsToTargetFields'], $colValues);
    $updateTargetNum     = @$sqlUpdate_rowNumsToRecordNums[ $rowNum ];
    if ($updateTargetNum) { $updateOrInsertQuery = "UPDATE `$prefixedTargetTable` $mysqlSet WHERE num = $updateTargetNum"; }
    else                  { $updateOrInsertQuery = "INSERT `$prefixedTargetTable` $mysqlSet $setForDefaultFields"; }
    if (!mysqli()->query($updateOrInsertQuery)) {
      $errors .= "Import Row #$rowNum - MySQL Error: ". htmlencode(mysqli()->error) . "<br>\n";
      $errors .= nl2br(htmlencode($mysqlSet)) . "<br><br>\n";
    }
    if ($updateTargetNum)                   { $sqlUpdateCounter++; }
    else                                    { 
      $sqlInsertCounter++; 
      
      // Get the inserted record number for menu options processing
      $insertedRecordNum = mysqli()->insert_id;
      
      // Hook for menu options processing (if target table is menu)
      if ($insertedRecordNum && (
          $targetTable === 'menu' || 
          $importJob['targetTable'] === 'menu' ||
          strpos($prefixedTargetTable, 'menu') !== false
      )) {
        _csvImport_processMenuOptions($insertedRecordNum, $colValues, $columnNumsToNames, $formValues);
      }
    }
  }

  // remove orphans if needed
  $orphansRemoved = 0;
  if ($done && $importJob['removeOrphans']) {
    $orphansRemoved = _csvImport_importForm_ajax_removeOrphans($prefixedTargetTable, $importJob, $formValues);
  }

  // drop temp table if needed
  if ($done && $useTempTable) {
    mysqli()->query("DROP TABLE IF EXISTS `{$GLOBALS['TABLE_PREFIX']}$tempTable`") or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
    $targetPath  = realpath(DATA_DIR . '/schema') . '/' . $tempTable . ".ini.php";
    @unlink($targetPath);
  }

  // drop temp import data records (Only do this on final import)
  if ($done && !$useTempTable) {
  
    // remove import data
    mysql_delete('_csv_import_data', null, array('jobNum' => $importJob['num']));
    
    // remove uploaded import file
    $removeUploadsWhere = mysql_escapef("`tableName` = ? AND recordNum = ?", '_csv_import_jobs', $recordNum); 
    $uploadsRemoved     = removeUploads($removeUploadsWhere);
    
    // remove import job
    if (!$importJob['isCustomMenu']) { 
      mysql_delete('_csv_import_jobs', null, array('num' => $importJob['num']));
    }
  }
  
  // create custom menu
  if ($importJob['isCustomMenu']) {
    $customMenu   = "_csv_import_menu_" . $importJob['num'];
    $customSchema = array(
      'menuName'          => $importJob['customMenuName'],
      '_requiredPlugins' => 'csvImport.php',
      'menuOrder'        => time(),
      'menuType'         => 'single',
      'num'              => array('order' => '1', 'type' => 'none', 'label' => 'Record Number', 'isSystemField' => '1'),
    );
    saveSchema($customMenu, $customSchema);
    createMissingSchemaTablesAndFields();
  }
  

  //
  print json_encode(array(
    'orphansRemoved'    => $orphansRemoved,
    'duplicatesUpdated' => $sqlUpdateCounter,
    'lastRowNum'        => $rowNum,
    'errors'            => $errors,
    'done'              => $done,
  ));
  exit;

}

// Process menu options during CSV import
function _csvImport_processMenuOptions($menuId, $colValues, $columnNumsToNames, $formValues) {
    global $TABLE_PREFIX;
    
    // Check if menu options plugin is available
    if (!function_exists('createMenuOptions')) {
        require_once(SCRIPT_DIR . '/plugins/menuOptions/menuOptions_functions.php');
    }
    
    try {
        // Extract column values into associative array
        $rowData = array();
        foreach ($colValues as $colNum => $value) {
            if (isset($columnNumsToNames[$colNum])) {
                $fieldName = $columnNumsToNames[$colNum];
                // Map to target field if mapping exists
                if (isset($formValues['colsToTargetFields'][$colNum])) {
                    $targetField = $formValues['colsToTargetFields'][$colNum];
                    if ($targetField && $targetField !== 'IGNORE') {
                        $rowData[$targetField] = $value;
                    }
                } else {
                    $rowData[$fieldName] = $value;
                }
            }
        }
        
        // Check if this row has option data
        $hasOptionData = false;
        $optionFields = array('option_name', 'option_type', 'choice_name');
        foreach ($optionFields as $field) {
            if (isset($rowData[$field]) && !empty($rowData[$field])) {
                $hasOptionData = true;
                break;
            }
        }
        
        if (!$hasOptionData) return;
        
        // Extract and create option data
        if (!empty($rowData['option_name'])) {
            $optionData = array(
                'menu_id' => $menuId,
                'option_name' => $rowData['option_name'],
                'option_type' => isset($rowData['option_type']) ? $rowData['option_type'] : 'required',
                'option_category' => isset($rowData['option_category']) ? $rowData['option_category'] : '',
                'price' => isset($rowData['option_price']) ? floatval($rowData['option_price']) : 0,
                'is_required' => isset($rowData['is_required']) ? intval($rowData['is_required']) : 0,
                'min_selection' => isset($rowData['min_selection']) ? intval($rowData['min_selection']) : 1,
                'max_selection' => isset($rowData['max_selection']) ? intval($rowData['max_selection']) : 1,
                'sort_order' => isset($rowData['option_sort_order']) ? intval($rowData['option_sort_order']) : 0,
                'is_active' => 1,
                'createdDate' => date('Y-m-d H:i:s'),
                'updatedDate' => date('Y-m-d H:i:s')
            );
            
            $optionNum = mysql_insert($TABLE_PREFIX . 'menu_options', $optionData, true);
            
            // Add choice if provided
            if ($optionNum && !empty($rowData['choice_name'])) {
                $choiceData = array(
                    'option_id' => $optionNum,
                    'choice_name' => $rowData['choice_name'],
                    'choice_description' => isset($rowData['choice_description']) ? $rowData['choice_description'] : '',
                    'price' => isset($rowData['choice_price']) ? floatval($rowData['choice_price']) : 0,
                    'sort_order' => isset($rowData['choice_sort_order']) ? intval($rowData['choice_sort_order']) : 0,
                    'is_available' => 1,
                    'createdDate' => date('Y-m-d H:i:s'),
                    'updatedDate' => date('Y-m-d H:i:s')
                );
                
                mysql_insert($TABLE_PREFIX . 'menu_option_choices', $choiceData);
            }
        }
        
    } catch (Exception $e) {
        // Silently continue if menu options processing fails
        // The main import should not be affected
        error_log("Menu options processing failed for menu $menuId: " . $e->getMessage());
    }
}


//
function _csvImport_importForm_ajax_createTempTable($importJob, $tempTable) {
  global $TABLE_PREFIX;

  // copy schema
  $sourcePath  = realpath(DATA_DIR . '/schema') . '/' . $importJob['targetTable'] . ".ini.php";
  $targetPath  = realpath(DATA_DIR . '/schema') . '/' . $tempTable . ".ini.php";
  @copy($sourcePath, $targetPath) or die("Error copying schema file! " .@$php_errormsg);

  // update schema
  $schema = loadSchema($tempTable);
  $schema['menuOrder']  = time();  // sort temp schema to the bottom
  $schema['menuHidden'] = 1;       // hide temp schema on menu
  saveSchema($tempTable, $schema);

  // create mysql table
  $dropQuery   = "DROP TABLE IF EXISTS `$TABLE_PREFIX$tempTable`";
  $createQuery = "CREATE TABLE `$TABLE_PREFIX$tempTable` LIKE `$TABLE_PREFIX{$importJob['targetTable']}`"; // use like so we copy indexes too!
  $insertQuery = "INSERT INTO `$TABLE_PREFIX$tempTable` SELECT * FROM `$TABLE_PREFIX{$importJob['targetTable']}`";
  mysqli()->query($dropQuery)   or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
  mysqli()->query($createQuery) or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
  mysqli()->query($insertQuery) or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
}

//
function _csvImport_importForm_ajax_resetTableAndCounter($importJob, $targetTable) {
  global $TABLE_PREFIX;
  $prefixedTargetTable = $TABLE_PREFIX . $targetTable;

  if (!$importJob['wipeDatabase']) { return; }

  $wipeQuery = "DELETE FROM `$prefixedTargetTable`";
  mysqli()->query($wipeQuery) or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");

  if ($importJob['setAutoIncrement'] && $importJob['newAutoIncrement']) {
    $resetNumQuery = "ALTER TABLE `$prefixedTargetTable` AUTO_INCREMENT = {$importJob['newAutoIncrement']}";
    mysqli()->query($resetNumQuery) or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
  }
}


// import or update record
function _csvImport_importForm_ajax_getQuerySetString($prefixedTargetTable, $colNumsToNames, $colNumsToTargetFieldnames, $colValues) {
  static $targetFieldnames;
  if (!$targetFieldnames)  { $targetFieldnames  = array_filter(array_unique(array_values( $colNumsToTargetFieldnames ))); } // remove blanks and duplicates

  // get mysql SET
  $mysqlSet = "SET ";
  foreach ($targetFieldnames as $targetField) {
    $mysqlValue    = '';
    $prefixColName = in_array($targetField, $GLOBALS['CSV_IMPORT_OPTIONS']['PREFIX_VALUES_FOR']);
    foreach ($colNumsToTargetFieldnames as $colNum => $colTargetField) {
      if ($colTargetField != $targetField) { continue; }
      $colValue = @$colValues[$colNum - 1];            // convert colNum to index
      if ($colValue == '')   { continue; }            // skip blank strings in multiple values
      if ($mysqlValue != '') { $mysqlValue .= $GLOBALS['CSV_IMPORT_OPTIONS']['MULTI_VALUE_SEPARATOR']; } // join multi values with this char
      if ($prefixColName) { $mysqlValue .= $colNumsToNames[$colNum] . $GLOBALS['CSV_IMPORT_OPTIONS']['PREFIX_SEPARATOR']; }
      $mysqlValue .= $colValues[$colNum - 1];
    }

    // filter value
    $filterFunction = @$GLOBALS['CSV_IMPORT_OPTIONS']['FIELD_FILTERS'][$targetField];
    if ($filterFunction) { $mysqlValue = call_user_func($filterFunction, $mysqlValue); }

    //
    $mysqlSet .= mysql_escapef("`$targetField` = ?,\n", $mysqlValue);
  }

  //
  $mysqlSet = chop($mysqlSet, ", \n");
  return $mysqlSet;
}


//
function _csvImport_importForm_ajax_removeOrphans($prefixedTargetTable, $importJob, $formValues) {
  global $TABLE_PREFIX;
  $dataTable = $GLOBALS['TABLE_PREFIX'] . '_csv_import_data';

  if (!$importJob['removeOrphans']) { return 0; }

  $orphansRemoved     = 0;
  $idFieldColumnNum   = _getIdFieldColumnNum($importJob, $formValues);

  $idFieldValuesQuery = mysql_escapef("SELECT colValue FROM `$dataTable` WHERE jobNum = ? AND colNum = ?", $importJob['num'], $idFieldColumnNum);
  $deleteOrphansQuery = "DELETE FROM `$prefixedTargetTable` WHERE `{$importJob['idField']}` NOT IN ($idFieldValuesQuery)";
  mysqli()->query($deleteOrphansQuery) or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
  $orphansRemoved = mysqli()->affected_rows;

  return $orphansRemoved;
}


// 
function _csvImport_getAdminUiDefaults(){
  list($thisPluginPath, $thisPluginUrl) = getPluginPathAndUrl();
  
  $adminUI = array();
  
  // check if this is a custom import menu
  if (csvImport_isCustomMenu()) {
    // page title
    $title = array_value(loadSchema(@$_REQUEST['menu']), 'menuName');
    $adminUI['PAGE_TITLE'] = array(
      t($title)       => "?menu=" . htmlencode(@$_REQUEST['menu']),
    );
  }
  else{
    // page title
    $adminUI['PAGE_TITLE'] = array(
      t("Plugins")                   => '?menu=admin&action=plugins',
      t("CSV Import - Import File")  => "?_plugin={$thisPluginPath}&_pluginAction=csvImport_uploadForm",
    );
    
    // button
    $adminUI['BUTTONS'] = array(
      array('name' => 'simple_menu_import', 'label' => 'Simple Menu Import &gt;&gt;', 'onclick' => 'window.location="?_pluginAction=csvImport_simpleMenuImport";', 'type' => 'button'),
      array('name' => 'import_jobs', 'label' => 'Import Jobs &gt;&gt;', 'onclick' => 'window.location="?menu=_csv_import_jobs";', 'type' => 'button'),
      array('name' => 'back_to_plugins', 'label' => 'Back to Plugins &gt;&gt;', 'onclick' => 'window.location="?menu=admin&action=plugins";', 'type' => 'button'),
    );
  }
  
  return $adminUI;
}

// Plugin Menu: Add action links for this plugin
function _csvImport_showHeader() {
  list($thisPluginPath, $thisPluginUrl) = getPluginPathAndUrl();
  list($jsPath, $jsUrl)                 = getPluginPathAndUrl('csvImport.js');
  
  ?>

  <script type="text/javascript" src="<?php echo $jsUrl ?>"></script>

  <input type="hidden" name="menu"          value="<?php echo htmlencode(@$_REQUEST['menu']) ?>">
  <input type="hidden" name="_plugin"       value="<?php echo htmlencode($thisPluginPath); ?>">
  <input type="hidden" name="submitForm"    value="1">

  <?php
}



// list($importJob, $csvFilepath) = _csvImport_loadImportJob($recordNum);
function _csvImport_loadImportJob($recordNum) {

  require_once SCRIPT_DIR . '/lib/viewer_functions.php';

  list($importJobs, $importJobsMetaData) = getRecords(array(
    'tableName'   => '_csv_import_jobs',
    'where'       => mysql_escapef("num = ?", $recordNum),
    'limit'       => '1',
    'allowSearch' => false,
  ));
  $importJob = @$importJobs[0]; // get first record
  if (!$importJob) { die("Import job #$recordNum not found!"); }

  //
  $csvFilepath = @$importJob['csv_upload'][0]['filePath'];
  if     (!$csvFilepath)              { die("No import filename!"); }
  elseif (!file_exists($csvFilepath)) { die("Import file doesn't exist!"); }
  //unset($importJob['csv_upload']); //

  // set delimiter for CSV parsing (done in csvImport_init and _csvImport_loadImportJob)
  if ($importJob['fileFormat'] == 'csvg') { $GLOBALS['CSV_IMPORT_OPTIONS']['CSV_DELIMITER'] = ';'; }

  //
  return array($importJob, $csvFilepath);
}

// get values to pre-populate form
// $formValues = _csvImport_getFormValues($importJob, $_REQUEST);
function _csvImport_getFormValues($record, $request = array()) {
  $formValues = array();

  // use submitted form value if available or else use record value
  foreach ($record as $name => $value) {
    if (array_key_exists($name, $request)) { $formValues[$name] = $request[$name]; }
    else                                   { $formValues[$name] = $value; }
  }


  // get values for: col# -> column name
  $columnNameArray = _csvImport_fieldMapToArray($record['columnNamesMap']);
  foreach ($columnNameArray as $colNumber => $colName) {
    if ($colNumber) { $formValues['columnNames'][$colNumber] = $colName; }
  }
  unset($formValues['columnNamesMap']);

  // get values for: col# -> target field
  $colNumToTargetField = _csvImport_fieldMapToArray($record['mapFields']);
  foreach (range(1, $record['columnCount']) as $colNumber) {
    if (!$colNumber) { continue; }
    $name    = "targetFieldForCol$colNumber";
    $value   = "";
    $colName = @$formValues['columnNames'][$colNumber];
    if      (array_key_exists($name, $request))                  { $value = $request[$name]; }
    else if (array_key_exists($colNumber, $colNumToTargetField)) { $value = $colNumToTargetField[$colNumber]; }
    else if ($colName) { // check for default mappings for column name
      if     (@$GLOBALS['CSV_IMPORT_DEFAULTS']['FIELD_MAP'][$colName]) { $value = $GLOBALS['CSV_IMPORT_DEFAULTS']['FIELD_MAP'][$colName]; }
      elseif (@$GLOBALS['CSV_IMPORT_DEFAULTS']['FIELD_MAP']['*'])      { $value = $GLOBALS['CSV_IMPORT_DEFAULTS']['FIELD_MAP']['*']; }
    }

    $formValues[$name] = $value;
    $formValues['colsToTargetFields'][$colNumber] = $value;
  }
  unset($formValues['mapFields']);

  //
  return $formValues;
}


// list($fieldNamesToSchemas, $columnCount, $recordCount) = _csvImport_getTargetTableDetails( $importJob['targetTable'] );
function _csvImport_getTargetTableDetails( $targetTable ) {
  $prefixedTargetTable  = getTableNameWithPrefix( $targetTable );
  $fieldnamesToSchemas  = array();
  $mysqlColumnsToTypes  = getMySqlColsAndType( $prefixedTargetTable );

  foreach (getSchemaFields($targetTable) as $fieldname => $fieldSchema) {
    if (!@$mysqlColumnsToTypes[$fieldname]) { continue; }
    $fieldnamesToSchemas[$fieldname] = $fieldSchema;
  }

  $columnCount = count( $fieldnamesToSchemas );
  $recordCount = mysql_count( $targetTable );

  return array($fieldnamesToSchemas, $columnCount, $recordCount);
}


// $idFieldColumnNum = _getIdFieldColumnNum($importJob, $formValues);
function _getIdFieldColumnNum($importJob, $formValues) {
  $idFieldColumnNum = '';

  $targetFieldsToCols   = array_flip( $formValues['colsToTargetFields'] );
  $idFieldColumnNum     = @$targetFieldsToCols[ $importJob['idField'] ];
  if (!$idFieldColumnNum) { die("No \$idFieldColumnNum!"); }

  return $idFieldColumnNum;
}


// Convert "1|Name\n3|Title\n" to array('1' => 'Name', '3' => 'Title')
function _csvImport_fieldMapToArray($fieldMap) {
  $mapArray = array();

  foreach (explode("\n", $fieldMap??'') as $line) {
    @list($colNumber, $value) = explode('|', $line, 2);
    if (!$colNumber) { continue; }
    $mapArray["$colNumber"] = $value;
  }

  return $mapArray;
}





// Convert array('1' => 'Name', '3' => 'Title') to "1|Name\n3|Title\n"
function _csvImport_arrayToFieldMap($mapArray, $removeSuffix = '') {
  $fieldMap = '';

  foreach ($mapArray as $index => $value) {
    if ($removeSuffix) {
      if (!preg_match("/^$removeSuffix/", $index)) { continue; }
      $index = preg_replace("/^$removeSuffix/", '', $index);
      $index--; // use actual index number for assoc arrays - decreased below
    }
    $colNumber = $index+1;
    $fieldMap .= "$colNumber|$value\n";
    @list($colNumber, $value) = explode('|', $line, 2);
    if (!$colNumber) { continue; }
    $mapArray["$colNumber"] = $value;
  }

  return $fieldMap;
}

// $colNames = _csvParser_getColumnNames($filepath);
function _csvParser_getColumnNames($filepath, $useGenericColumnNames) {
  $colNames = array();

  // error checking
  if (!$filepath) { die(__FUNCTION__ . ": No filepath specified!"); }

  //
  $handle    = fopen($filepath, 'r');
  $colValues = fgetcsv($handle, 10000, $GLOBALS['CSV_IMPORT_OPTIONS']['CSV_DELIMITER']);
  if ($GLOBALS['CSV_IMPORT_OPTIONS']['ENCODING']) { mb_convert_variables('UTF-8', $GLOBALS['CSV_IMPORT_OPTIONS']['ENCODING'], $colValues); }

  for ($index=0; $index<count($colValues); $index++) {
    $colName = $colValues[$index];
  
    // assign names to blank columns
    if      ($useGenericColumnNames) { $colName = 'column ' . ($index+1); }
    else if ($colName == '')         { $colName = 'unnamed_col_' . ($index+1); }
    
    // replace/remove whitespace
    $colName = trim($colName);                        // remove leading/trailing whitespace
    $colName = preg_replace("/\s+/s", ' ', $colName); // replace nextlines and multiple whitespace chars

    //
    $colNames[] = $colName;
  }
  fclose($handle);


  //
  return $colNames;
}

// return one line from CSV file for each call
// returns false at end of file and resets itself.
// Example Usage:
/*
  // import file
  while($r = _csvParser_getNextLine($filepath)) {
    list($colValues, $offset) = $r;
    showme($rowValues);
  }
*/
function _csvParser_getNextLine($filepath, $setOffset = null) {
  static $handle;
  static $headerCount;

  // open new file
  if (!$handle) {
    $handle = fopen($filepath, "r");
    if (!$handle) { die("Can't open '" .htmlencode($filepath). "' - " .@$php_errormsg. "!"); }
    if ($setOffset) { fseek($handle, $setOffset); }
  }

  // get next row
  $colValues = fgetcsv($handle, 10000, $GLOBALS['CSV_IMPORT_OPTIONS']['CSV_DELIMITER']);
  if ($GLOBALS['CSV_IMPORT_OPTIONS']['ENCODING']) { mb_convert_variables('UTF-8', $GLOBALS['CSV_IMPORT_OPTIONS']['ENCODING'], $colValues); }
  $newOffset = ftell($handle);

  // if no more rows: close file & reset vars
  if (!$colValues) {
    fclose($handle);
    unset($handle, $headerCount);
    return false;
  }

  // return record
  if ($colValues) { return array($colValues, $newOffset); }
  else            { return false; }

}



// eof