[Summary view] [Print] [Text view]

   1  <?php
   2  /*#################################################################
   3  #
   4  # Name:    export.php
   5  #
   6  # Description:
   7  #   Extract columns from $sql_table
   8  #
   9  # Parameters:
  10  #   cid - optional cookie id number. Allows multiple instances of SQLView
  11  #   fcn - what to do 'export', 'prompt' (the default)
  12  #   hdr - if specified, include column names as a header line
  13  #   delim - delimiter to use for data
  14  #   col - array of column names to export
  15  #   where - where clause for export
  16  #
  17  # Copyright (C) 2001-2005 Dmitriy Katsman, Terry Gliedt, University of Michigan
  18  # This is free software; you can redistribute it and/or modify it under the
  19  # terms of the GNU General Public License as published by the Free Software
  20  # Foundation; See http://www.gnu.org/copyleft/gpl.html
  21  #################################################################*/
  22  include_once "common.php";
  23  include_once "session.php";             // Set up session variables
  24  include_once "local_config.php";        // Allow local options
  25  global $desc;
  26  
  27  //print "<!-- _GET=\n"; print_r($_GET); print " -->\n";
  28  //print "<!-- _POST=\n"; print_r($_POST); print " -->\n";
  29  //print "<!-- _SESSION=\n"; print_r($_SESSION); print "-->\n";
  30  
  31  //  Get parameters passed in via normal invocation
  32  extract (isolate_parms( 'cid', 'fcn', 'col', 'hdr', 'delim',
  33      'where', 'exportfilter'));
  34  if (! $delim) { $delim = ','; }
  35  if ($delim == 'tab') { $delim = "\t"; }
  36  if ($exportfilter) { $hdr = 1; }    // If filter, force header line
  37  
  38  //  Override any project related variables
  39  get_project($_SESSION['project']);
  40  verify_access($project);
  41  
  42  //  No need to play if we have no table
  43  $sql_table = $_SESSION['sql_table'];
  44  if (! $sql_table) { nice_exit("Somehow we have lost the table name"); }
  45  
  46  //  All set up, get connection with session database
  47  db_connect($sql_type, $sql_host, $sql_user, $sql_pass, $sql_db);
  48  
  49  //-------------------------------------------------------------------
  50  //  Export the data selected
  51  //-------------------------------------------------------------------
  52  if ($fcn == 'export') {
  53      //  Get list of columns to select on
  54      if (is_array($col) || $col == 'Array') {
  55          $docols = array_keys($_POST['col']);
  56      }
  57      else {
  58          get_desc($sql_table);
  59          $docols = $desc;
  60          unset($docols['_inc_']);
  61          unset($docols['_prikey_']);
  62          $docols = array_keys($docols);
  63          sort($docols);
  64      }
  65  
  66      //  Fetch the data
  67      $sql_query = "SELECT " . implode(',',$docols) . " FROM $sql_table";
  68      //  Add possible WHERE clause (remove possible semicolons)
  69      $where = str_replace(';', ' ',$where);
  70      if (preg_match('/^\s*where (.+)/i', $where, $m)) { $where = $m[1]; }
  71      if ($where) { $sql_query .= ' WHERE ' . stripslashes($where); }
  72      $result = $dbh->query($sql_query);
  73      if (DB::isError($result)) { nice_PEAR_exit($result); }
  74  
  75      $data = '';
  76  
  77      //  Put out as delimited text
  78      if ($hdr) { $data .= implode($delim,$docols) . "\n"; }
  79      $r = $result->fetchRow(DB_FETCHMODE_ASSOC);
  80      while($r) {
  81          $arrdata = array();
  82          while (list($col,$val) = each($r)) {
  83              array_push($arrdata,$val);
  84          }
  85          $data .= implode($delim,$arrdata) . "\n";
  86          $r = $result->fetchRow(DB_FETCHMODE_ASSOC);
  87      }
  88  
  89      //  If a filter was specified, save $data to file
  90      //  invoke filter and then read it back
  91      if ($exportfilter) {
  92          foreach ($CFG['export_filter'] as $unused => $pgm) {
  93              $p = getmypid();
  94  //$p = 999; unlink("/tmp/$p.IN"); unlink("/tmp/$p.OUT");
  95              $h = fopen("/tmp/$p.IN", 'w');
  96              if (fwrite($h, $data) === FALSE) {
  97                  redmsg("Unable to create temp file for exportfilter");
  98                  exit;
  99              }
 100              fclose($h);
 101              $cmd = str_replace('%IN%', "/tmp/$p.IN", $pgm);
 102              $cmd = str_replace('%OUT%', "/tmp/$p.OUT", $cmd);
 103              $s = system($cmd);
 104  //redmsg("cmd=$cmd");
 105              if ($s) { redmsg("Exportfilter output:\n$s"); }
 106              $lines = file("/tmp/$p.OUT");
 107              $data = implode('',$lines) . "\n";
 108  //            unlink("/tmp/$p.IN");
 109  //            unlink("/tmp/$p.OUT");
 110              redmsg("To get the exported data, save the web page and remove the HTML.");
 111              break;
 112          }
 113      }
 114  
 115      //  Show data as PRE. Cannot use download cause headers already sent :-(
 116      print "<pre>$data</pre>\n";
 117      exit;
 118      /* header("Content-Disposition: attachment; filename=$sql_table.csv");
 119      header("Content-length: " . strlen($data));
 120      header("Content-type: application/download");
 121      header("Connection: close"); 
 122      header("Expires: 0"); 
 123      set_time_limit(0); 
 124      print $data;
 125      exit;
 126      */
 127  }
 128  
 129  //-------------------------------------------------------------------
 130  //  Prompt for what to export
 131  //-------------------------------------------------------------------
 132  doheader(1);
 133  
 134  //  Get the columns and some details from the database
 135  get_desc($sql_table);
 136  $d = $desc;
 137  ksort($d);
 138  $prikey = $desc['_prikey_'];
 139  
 140  //  Generate table with header + checkbox and key/value from array
 141  print <<<END
 142  <h2 align="center">Export Data from '$sql_table'</h2>
 143  <p>Select the columns you want and click on 'Export'. When the data
 144  is presented, just save the page that is generated.
 145  You may need to remove the &lt;pre&gt; tags at the start end end
 146  of the data you save.</p>
 147  <form action="export.php" method="post">
 148  <input type="hidden" name="cid" value="$cid"/>
 149  <input type="hidden" name="fcn" value="export"/>
 150  <b><input type="submit" value=" Export Columns "/></b>
 151   (If no columns selected, all are exported)<br/><br/>
 152  
 153  <table class="exp" width="80\%">
 154  <tr>
 155    <th class="exphdr">&nbsp;</th>
 156    <th class="exphdr" colspan="2" align="center"><b>Select Column(s) to Export</b></th>
 157  </tr>
 158  
 159  END;
 160  while ( list($key,$val) = each($d)) {
 161      if ($key == '_prikey_') { continue; }
 162      if ($key == '_inc_') { continue; }
 163      if ($key == $prikey) { $c = 'exppri'; }
 164      else { $c = 'exp'; }
 165      print "<tr>\n" .
 166          "  <td class=\"exp\"><input type=\"checkbox\" name=\"col[$key]\" value=\"$val\"/></td>\n" .
 167          "  <td class=\"$c\"><b>$key</b></td>\n" .
 168          "  <td class=\"exp\">$val</td>\n" .
 169          "</tr>\n";
 170  }
 171  $where = '';
 172  if ($CFG['export_where']) { $where = $CFG['export_where']; }
 173  $delim = ',';
 174  if ($CFG['export_delimiter']) { $delim = $CFG['export_delimiter']; }
 175  print <<<END
 176  </table>
 177  <table class="expopt" width="80\%">
 178  <tr>
 179    <td class="expopt" align="right"><b>WHERE clause</b></td>
 180    <td class="expopt"><input type="text" name="where" size="30" value="$where"/></td>
 181    <td class="expopt">(Leave blank for 'all records')</td>
 182  </tr>
 183  <tr>
 184    <td class="expopt" align="right"><b>Include header line</b></td>
 185    <td class="expopt"><input type="checkbox" name="hdr" value="1" checked="checked"/></td>
 186    <td class="expopt">&nbsp;</td>
 187  </tr>
 188  <tr>
 189    <td class="expopt" align="right"><b>Delimiter</b></td>
 190    <td class="expopt"><input type="text" name="delim" size="3" value="$delim"/></td>
 191    <td class="expopt">(Use 'tab' for tab char.)</td>
 192  </tr>
 193  END;
 194  
 195  if ($CFG['export_filter']) {
 196      foreach ($CFG['export_filter'] as $s => $pgm) {
 197          print <<<END
 198  <tr>
 199    <td class="expopt" align="right"><b>$s</b></td>
 200    <td class="expopt"><input type="checkbox" name="exportfilter" value="1"/></td>
 201    <td class="expopt">&nbsp;</td>
 202  </tr>
 203  END;
 204      break;
 205      }
 206  }
 207  print "</table>\n<br/><b><input type=\"submit\" value=\" Export Columns \"/></b>\n</form>\n";
 208  
 209  dofooter(1);
 210  
 211  exit;
 212  
 213  ?>