classes/csvimporter.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2011 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22 define('CSV_INSERT',     'insert'); // INSERT IGNORE
  23 define('CSV_REPLACE',    'replace'); // REPLACE
  24 define('CSV_UPDATE',     'update'); // INSERT and UPDATE on error
  25 define('CSV_UPDATEONLY', 'updateonly'); // UPDATE
  26
  27 class CSVImporter
  28 {
  29     private $table;
  30     private $key;
  31     private $do_sql;
  32
  33     private $index;
  34     protected $data = array();
  35
  36     private $user_functions = array();
  37     private $field_desc = array();
  38     private $field_value = array();
  39
  40     public function __construct($table, $key = 'id', $do_sql = true)
  41     {
  42         $this->table     = $table;
  43         $this->key       = explode(',', $key);
  44         $this->do_sql    = $do_sql;
  45     }
  46
  47     private function processLine(array $array)
  48     {
  49         if (is_null($this->index)) {
  50             $this->index = array_map('strtolower', $array);
  51             return true;
  52         }
  53
  54         if (count($array) != count($this->index)) {
  55             return false;
  56         }
  57         $assoc = array();
  58         $i     = 0;
  59         foreach ($this->index as $key) {
  60             $assoc[$key] = $array[$i];
  61             $i++;
  62         }
  63         $this->data[] = $assoc;
  64         return true;
  65     }
  66
  67     static public function getValue($line, $key, $action, $relation = null)
  68     {
  69         if (@array_key_exists($action, $line)) {
  70             $value = $line[$action];
  71         } elseif (is_callable($action, false)) {
  72             $value = call_user_func($action, $line, $key, $relation);
  73         } else {
  74             $value = $action;
  75         }
  76         if (is_null($value) || $value == 'NULL') {
  77             $value = 'NULL';
  78         }
  79         return $value;
  80     }
  81
  82     protected function makeAssoc($line, $relation)
  83     {
  84         $ops = array();
  85         foreach ($relation as $key=>$ref) {
  86             $ops[$key] = $this->getValue($line, $key, $ref, $relation);
  87         }
  88         return $ops;
  89     }
  90
  91     private function makeRequestArgs($line, $relation)
  92     {
  93         $ops = array();
  94         foreach ($relation as $key=>$ref) {
  95             $value = $this->getValue($line, $key, $ref, $relation);
  96             if (!is_null($value) && $value != 'NULL') {
  97                 $value = "'" . addslashes($value) . "'";
  98             }
  99             $ops[$key] = "$key = $value";
 100         }
 101         return $ops;
 102     }
 103
 104     private function makeRelation()
 105     {
 106         $relation = array();
 107         foreach ($this->index as $title) {
 108             $relation[$title] = $title;
 109         }
 110         return $relation;
 111     }
 112
 113     private function execute($query)
 114     {
 115         if (!$this->do_sql) {
 116             echo "$query;\n";
 117             return false;
 118         }
 119         return XDB::execute($query);
 120     }
 121
 122     private function getFieldList()
 123     {
 124         $res = XDB::query("SHOW COLUMNS FROM {$this->table}");
 125         if ($res->numRows()) {
 126             return $res->fetchColumn();
 127         }
 128         return null;
 129     }
 130
 131     public function setCSV($csv, $index = null, $separator = ';')
 132     {
 133         require_once dirname(__FILE__) . '/varstream.php';
 134         VarStream::init();
 135         global $csv_source;
 136         $this->index     = null;
 137
 138         $csv_source = $csv;
 139         $res        = fopen('var://csv_source', 'r');
 140
 141         while (!feof($res)) {
 142             $this->processLine(fgetcsv($res, 0, $separator));
 143         }
 144     }
 145
 146     public function run($action = CSV_UPDATE, $insert_relation = null, $update_relation = null)
 147     {
 148         if (is_null($insert_relation)) {
 149             $insert_relation = $this->makeRelation();
 150         }
 151         if (is_null($update_relation)) {
 152             $update_relation = $insert_relation;
 153         }
 154         foreach ($this->data as $line) {
 155             $set = join(', ', $this->makeRequestArgs($line, $insert_relation));
 156             switch ($action) {
 157               case CSV_INSERT:
 158                 $this->execute("INSERT IGNORE INTO {$this->table} SET $set");
 159                 break;
 160               case CSV_REPLACE:
 161                 $this->execute("REPLACE INTO {$this->table} SET $set");
 162                 break;
 163               case CSV_UPDATE: case CSV_UPDATEONLY:
 164                 if ($action == CSV_UPDATEONLY || !$this->execute("INSERT INTO {$this->table} SET $set")) {
 165                     $ops = $this->makeRequestArgs($line, $update_relation);
 166                     $set = join(', ', $ops);
 167                     $where = array();
 168                     foreach ($this->key as $key) {
 169                         $where[] = $ops[$key];
 170                     }
 171                     $where = join(' AND ', $where);
 172                     $this->execute("UPDATE {$this->table} SET $set WHERE $where");
 173                 }
 174                 break;
 175             }
 176         }
 177     }
 178
 179     static public function dynamicCond($line, $key)
 180     {
 181         static $fields, $conds, $values, $thens, $elses;
 182
 183         if (!isset($fields)) {
 184             $fields = $_SESSION['csv_cond_field'];
 185             $conds  = $_SESSION['csv_cond'];
 186             $values = $_SESSION['csv_cond_value'];
 187             $thens  = $_SESSION['csv_cond_then'];
 188             $elses  = $_SESSION['csv_cond_else'];
 189         }
 190         $field = $line[$fields[$key]];
 191         $cond  = $conds[$key];
 192         $value = $values[$key];
 193         if (is_numeric($field) && is_numeric($value)) {
 194             $field = floatval($field);
 195             $value = floatval($value);
 196         }
 197         switch ($cond) {
 198             case 'defined':          $ok = (!empty($field)); break;
 199             case 'equals':           $ok = ($field == $value); break;
 200             case 'contains':         $ok = (strpos($field, $value) !== false); break;
 201             case 'contained':        $ok = (strpos($value, $field) !== false); break;
 202             case 'greater':          $ok = ($field > $value); break;
 203             case 'greater_or_equal': $ok ($field >= $value); break;
 204             case 'lower':            $ok = ($field < $value); break;
 205             case 'lower_or_equal':   $ok = ($field <= $value); break;
 206             default:                 $ok = false;
 207         }
 208         if ($ok) {
 209             return $thens[$key];
 210         } else {
 211             return $elses[$key];
 212         }
 213     }
 214
 215     public function registerFunction($name, $desc, $callback)
 216     {
 217         if (is_callable($callback, false, $ref)) {
 218             $this->user_functions['func_' . $name] = array('desc' => $desc, 'callback' => $callback);
 219             return true;
 220         }
 221         return false;
 222     }
 223
 224     public function describe($name, $desc)
 225     {
 226         $this->field_desc[$name] = $desc;
 227     }
 228
 229     public function forceValue($name, $value)
 230     {
 231         $this->field_value[$name] = $value;
 232     }
 233
 234     private function cleanSession($fields)
 235     {
 236         foreach ($fields as $field) {
 237             unset($_SESSION[$field]);
 238         }
 239     }
 240
 241     /** Handle insertion form
 242      * @param $page  PlPage to process
 243      * @param $url   URI of the page
 244      * @param $field Editable fields
 245      */
 246     public function apply($page, $url, $fields = null)
 247     {
 248         $sesfields = array('csv_value', 'csv_user_value', 'csv_cond_field',
 249                            'csv_update', 'csv_action', 'csv_cond_field',
 250                            'csv_cond', 'csv_cond_value', 'csv_cond_then',
 251                            'csv_cond_else', 'csv', 'csv_separator', 'csv_url');
 252         if ($url != @$_SESSION['csv_url']) {
 253             $this->cleanSession($sesfields);
 254             $_SESSION['csv_url'] = $url;
 255         }
 256
 257         if (is_null($fields) || empty($fields)) {
 258             $fields = $this->getFieldList();
 259         }
 260         if (is_null($fields)) {
 261             return false;
 262         }
 263         foreach ($this->field_value as $key=>$value) {
 264             $search = array_search($key, $fields);
 265             unset($fields[$search]);
 266         }
 267
 268         $current = Env::v('csv_page');
 269         if (empty($current)) {
 270             $current = 'source';
 271         }
 272         $next = Env::v('csv_next_page');
 273         if (empty($next)) {
 274             $next = $current;
 275         }
 276         $csv  = @$_SESSION['csv'];
 277         if ($current == 'source' && Env::has('csv_valid')) {
 278             $csv = Env::v('csv_source');
 279             $_SESSION['csv'] = $csv;
 280             $next = 'values';
 281         }
 282         if ($csv) {
 283             if (Env::has('csv_separator')) {
 284                 $sep = Env::v('csv_separator');
 285                 if (empty($sep)) {
 286                     $sep = ';';
 287                 }
 288                 $_SESSION['csv_separator'] = $sep;
 289             }
 290             $this->setCSV($csv, null, $_SESSION['csv_separator']);
 291         }
 292         if ($current == 'values' && Env::has('csv_valid')) {
 293             $next = 'valid';
 294         }
 295         if (empty($csv)) {
 296             $next = 'source';
 297         }
 298         if (Env::has('csv_action')) {
 299             $_SESSION['csv_action'] = Env::v('csv_action');
 300         }
 301         if ($next == 'valid') {
 302             if ($current != 'valid') {
 303                 $cpyfields = array('csv_value', 'csv_user_value', 'csv_cond_field',
 304                                    'csv_update', 'csv_action', 'csv_cond_field',
 305                                    'csv_cond', 'csv_cond_value', 'csv_cond_then',
 306                                    'csv_cond_else');
 307                 foreach ($cpyfields as $field) {
 308                     $_SESSION[$field] = Env::v($field);
 309                 }
 310             }
 311             $insert   = $_SESSION['csv_value'];
 312             $values   = $_SESSION['csv_user_value'];
 313             $update   = $_SESSION['csv_update'];
 314             foreach ($insert as $key=>$value) {
 315                 if (empty($value)) {
 316                     $insert[$key] = null;
 317                 } elseif ($value == 'user_value') {
 318                     $insert[$key] = $values[$key];
 319                 } elseif ($value == 'cond_value') {
 320                     $insert[$key] = array($this, 'dynamicCond');
 321                 } elseif (array_key_exists($value, $this->user_functions)) {
 322                     $insert[$key] = $this->user_functions[$value]['callback'];
 323                 }
 324                 if (isset($update[$key])) {
 325                     $update[$key] = $insert[$key];
 326                 }
 327             }
 328             foreach ($this->field_value as $key=>$value) {
 329                 $insert[$key] = $value;
 330                 $fields[]     = $key;
 331             }
 332             if ($current == 'valid' && Env::has('csv_valid')) {
 333                 S::assert_xsrf_token();
 334                 $this->run($_SESSION['csv_action'], $insert, $update);
 335                 $page->assign('csv_done', true);
 336                 $this->cleanSession($sesfields);
 337             } else {
 338                 $preview = array();
 339                 foreach ($this->data as $line) {
 340                     $preview[] = $this->makeAssoc($line, $insert);
 341                 }
 342                 $page->assign('csv_preview', $preview);
 343             }
 344         }
 345         $page->assign('csv_index', $this->index);
 346         $page->assign('csv_functions', $this->user_functions);
 347         $page->assign('csv_field_desc', $this->field_desc);
 348         $page->assign('csv_page', $next);
 349         $page->assign('csv_path', $url);
 350         $page->assign('csv_fields', $fields);
 351     }
 352 }
 353
 354 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8:
 355 ?>