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