Commit | Line | Data |
---|---|---|
31874f6f | 1 | <?php |
212aae73 | 2 | /*************************************************************************** |
179afa7f | 3 | * Copyright (C) 2003-2008 Polytechnique.org * |
212aae73 | 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 | ***************************************************************************/ | |
31874f6f | 21 | |
440b63df | 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 | |
31874f6f | 26 | |
27 | class CSVImporter | |
28 | { | |
29 | private $table; | |
30 | private $key; | |
31 | private $do_sql; | |
32 | ||
33 | private $index; | |
31874f6f | 34 | private $data = array(); |
35 | ||
36 | private $user_functions = array(); | |
a6613b2e | 37 | private $field_desc = array(); |
1f53925a | 38 | private $field_value = array(); |
31874f6f | 39 | |
080bfddb | 40 | public function __construct($table, $key = 'id', $do_sql = true) |
31874f6f | 41 | { |
42 | $this->table = $table; | |
440b63df | 43 | $this->key = explode(',', $key); |
31874f6f | 44 | $this->do_sql = $do_sql; |
45 | } | |
46 | ||
e508a902 | 47 | private function processLine(array $array) |
31874f6f | 48 | { |
31874f6f | 49 | if (is_null($this->index)) { |
1f53925a | 50 | $this->index = array_map('strtolower', $array); |
31874f6f | 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 | ||
a6613b2e | 67 | private function getValue($line, $key, $action) |
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); | |
73 | } else { | |
74 | $value = $action; | |
75 | } | |
76 | if (is_null($value) || $value == 'NULL') { | |
77 | $value = 'NULL'; | |
78 | } | |
79 | return $value; | |
80 | } | |
81 | ||
31874f6f | 82 | private function makeAssoc($line, $relation) |
83 | { | |
84 | $ops = array(); | |
85 | foreach ($relation as $key=>$ref) { | |
a6613b2e | 86 | $ops[$key] = $this->getValue($line, $key, $ref); |
31874f6f | 87 | } |
88 | return $ops; | |
89 | } | |
90 | ||
91 | private function makeRequestArgs($line, $relation) | |
92 | { | |
93 | $ops = array(); | |
94 | foreach ($relation as $key=>$ref) { | |
a6613b2e | 95 | $value = $this->getValue($line, $key, $ref); |
96 | if (!is_null($value) && $value != 'NULL') { | |
31874f6f | 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 | { | |
e508a902 | 133 | require_once dirname(__FILE__) . '/varstream.php'; |
134 | VarStream::init(); | |
135 | global $csv_source; | |
31874f6f | 136 | $this->index = null; |
eaf30d86 | 137 | |
e508a902 | 138 | $csv_source = $csv; |
139 | $res = fopen('var://csv_source', 'r'); | |
31874f6f | 140 | |
e508a902 | 141 | while (!feof($res)) { |
142 | $this->processLine(fgetcsv($res, 0, $separator)); | |
31874f6f | 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; | |
440b63df | 163 | case CSV_UPDATE: case CSV_UPDATEONLY: |
164 | if ($action == CSV_UPDATEONLY || !$this->execute("INSERT INTO {$this->table} SET $set")) { | |
31874f6f | 165 | $ops = $this->makeRequestArgs($line, $update_relation); |
166 | $set = join(', ', $ops); | |
440b63df | 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"); | |
31874f6f | 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)) { | |
2f95cd8f | 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']; | |
31874f6f | 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 | { | |
1f53925a | 217 | if (is_callable($callback, false, $ref)) { |
31874f6f | 218 | $this->user_functions['func_' . $name] = array('desc' => $desc, 'callback' => $callback); |
219 | return true; | |
220 | } | |
221 | return false; | |
222 | } | |
223 | ||
a6613b2e | 224 | public function describe($name, $desc) |
225 | { | |
226 | $this->field_desc[$name] = $desc; | |
227 | } | |
228 | ||
1f53925a | 229 | public function forceValue($name, $value) |
230 | { | |
231 | $this->field_value[$name] = $value; | |
232 | } | |
233 | ||
2f95cd8f | 234 | private function cleanSession($fields) |
235 | { | |
236 | foreach ($fields as $field) { | |
237 | unset($_SESSION[$field]); | |
238 | } | |
239 | } | |
240 | ||
31874f6f | 241 | /** Handle insertion form |
242 | * @param $page PlatalPage to process | |
243 | * @param $url URI of the page | |
244 | * @param $field Editable fields | |
245 | */ | |
246 | public function apply(&$page, $url, $fields = null) | |
247 | { | |
2f95cd8f | 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 | ||
a6613b2e | 257 | if (is_null($fields) || empty($fields)) { |
31874f6f | 258 | $fields = $this->getFieldList(); |
259 | } | |
260 | if (is_null($fields)) { | |
261 | return false; | |
262 | } | |
1f53925a | 263 | foreach ($this->field_value as $key=>$value) { |
cbce77b7 | 264 | $search = array_search($key, $fields); |
265 | unset($fields[$search]); | |
1f53925a | 266 | } |
31874f6f | 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 | } | |
2f95cd8f | 276 | $csv = @$_SESSION['csv']; |
31874f6f | 277 | if ($current == 'source' && Env::has('csv_valid')) { |
278 | $csv = Env::v('csv_source'); | |
2f95cd8f | 279 | $_SESSION['csv'] = $csv; |
31874f6f | 280 | $next = 'values'; |
281 | } | |
282 | if ($csv) { | |
2f95cd8f | 283 | if (Env::has('csv_separator')) { |
284 | $sep = Env::v('csv_separator'); | |
285 | if (empty($sep)) { | |
286 | $sep = ';'; | |
287 | } | |
288 | $_SESSION['csv_separator'] = $sep; | |
080bfddb | 289 | } |
2f95cd8f | 290 | $this->setCSV($csv, null, $_SESSION['csv_separator']); |
31874f6f | 291 | } |
292 | if ($current == 'values' && Env::has('csv_valid')) { | |
293 | $next = 'valid'; | |
eaf30d86 | 294 | } |
31874f6f | 295 | if (empty($csv)) { |
296 | $next = 'source'; | |
297 | } | |
2f95cd8f | 298 | if (Env::has('csv_action')) { |
299 | $_SESSION['csv_action'] = Env::v('csv_action'); | |
eaf30d86 | 300 | } |
31874f6f | 301 | if ($next == 'valid') { |
2f95cd8f | 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']; | |
31874f6f | 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 | } | |
1f53925a | 328 | foreach ($this->field_value as $key=>$value) { |
329 | $insert[$key] = $value; | |
cbce77b7 | 330 | $fields[] = $key; |
1f53925a | 331 | } |
31874f6f | 332 | if ($current == 'valid' && Env::has('csv_valid')) { |
03849c3e | 333 | if (!S::has_xsrf_token()) { |
e61326ed VZ |
334 | $page->kill("L'opération n'a pas pu être effectuée, merci de réessayer."); |
335 | } | |
2f95cd8f | 336 | $this->run($_SESSION['csv_action'], $insert, $update); |
31874f6f | 337 | $page->assign('csv_done', true); |
2f95cd8f | 338 | $this->cleanSession($sesfields); |
31874f6f | 339 | } else { |
340 | $preview = array(); | |
341 | foreach ($this->data as $line) { | |
342 | $preview[] = $this->makeAssoc($line, $insert); | |
343 | } | |
344 | $page->assign('csv_preview', $preview); | |
345 | } | |
346 | } | |
347 | $page->assign('csv_index', $this->index); | |
1f53925a | 348 | $page->assign('csv_functions', $this->user_functions); |
a6613b2e | 349 | $page->assign('csv_field_desc', $this->field_desc); |
31874f6f | 350 | $page->assign('csv_page', $next); |
351 | $page->assign('csv_path', $url); | |
eaf30d86 | 352 | $page->assign('csv_fields', $fields); |
31874f6f | 353 | } |
354 | } | |
355 | ||
a7de4ef7 | 356 | // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: |
31874f6f | 357 | ?> |