31874f6f |
1 | <?php |
2 | |
3 | define('CSV_INSERT', 'insert'); // INSERT IGNORE |
4 | define('CSV_REPLACE', 'replace'); // REPLACE |
5 | define('CSV_UPDATE', 'update'); // INSERT and UPDATE on error |
6 | |
7 | class CSVImporter |
8 | { |
9 | private $table; |
10 | private $key; |
11 | private $do_sql; |
12 | |
13 | private $index; |
14 | private $separator; |
15 | private $data = array(); |
16 | |
17 | private $user_functions = array(); |
18 | |
19 | public function CSVImporter($table, $key = 'id', $do_sql = true) |
20 | { |
21 | $this->table = $table; |
22 | $this->key = $key; |
23 | $this->do_sql = $do_sql; |
24 | } |
25 | |
26 | private function processLine($line) |
27 | { |
28 | $array = split($this->separator, $line); |
29 | if (is_null($this->index)) { |
30 | $this->index = $array; |
31 | return true; |
32 | } |
33 | |
34 | if (count($array) != count($this->index)) { |
35 | return false; |
36 | } |
37 | $assoc = array(); |
38 | $i = 0; |
39 | foreach ($this->index as $key) { |
40 | $assoc[$key] = $array[$i]; |
41 | $i++; |
42 | } |
43 | $this->data[] = $assoc; |
44 | return true; |
45 | } |
46 | |
47 | private function makeAssoc($line, $relation) |
48 | { |
49 | $ops = array(); |
50 | foreach ($relation as $key=>$ref) { |
51 | if (@array_key_exists($ref, $line)) { |
52 | $value = $line[$ref]; |
53 | } elseif (is_callable($ref, false)) { |
54 | $value = call_user_func($ref, $line, $key); |
55 | } else { |
56 | $value = $ref; |
57 | } |
58 | if (is_null($value) || $value == 'NULL') { |
59 | $value = 'NULL'; |
60 | } |
61 | $ops[$key] = $value; |
62 | } |
63 | return $ops; |
64 | } |
65 | |
66 | private function makeRequestArgs($line, $relation) |
67 | { |
68 | $ops = array(); |
69 | foreach ($relation as $key=>$ref) { |
70 | if (@array_key_exists($ref, $line)) { |
71 | $value = $line[$ref]; |
72 | } elseif (is_callable($ref, false)) { |
73 | $value = call_user_func($ref, $line, $key); |
74 | } else { |
75 | $value = $ref; |
76 | } |
77 | if (is_null($value) || $value == 'NULL') { |
78 | $value = 'NULL'; |
79 | } else { |
80 | $value = "'" . addslashes($value) . "'"; |
81 | } |
82 | $ops[$key] = "$key = $value"; |
83 | } |
84 | return $ops; |
85 | } |
86 | |
87 | private function makeRelation() |
88 | { |
89 | $relation = array(); |
90 | foreach ($this->index as $title) { |
91 | $relation[$title] = $title; |
92 | } |
93 | return $relation; |
94 | } |
95 | |
96 | private function execute($query) |
97 | { |
98 | if (!$this->do_sql) { |
99 | echo "$query;\n"; |
100 | return false; |
101 | } |
102 | return XDB::execute($query); |
103 | } |
104 | |
105 | private function getFieldList() |
106 | { |
107 | $res = XDB::query("SHOW COLUMNS FROM {$this->table}"); |
108 | if ($res->numRows()) { |
109 | return $res->fetchColumn(); |
110 | } |
111 | return null; |
112 | } |
113 | |
114 | public function setCSV($csv, $index = null, $separator = ';') |
115 | { |
116 | $this->index = null; |
117 | $this->separator = $separator; |
118 | $csv = preg_split("/(\r\n|\r|\n)/", $csv); |
119 | |
120 | foreach ($csv as $line) { |
121 | $this->processLine($line); |
122 | } |
123 | } |
124 | |
125 | public function run($action = CSV_UPDATE, $insert_relation = null, $update_relation = null) |
126 | { |
127 | if (is_null($insert_relation)) { |
128 | $insert_relation = $this->makeRelation(); |
129 | } |
130 | if (is_null($update_relation)) { |
131 | $update_relation = $insert_relation; |
132 | } |
133 | foreach ($this->data as $line) { |
134 | $set = join(', ', $this->makeRequestArgs($line, $insert_relation)); |
135 | switch ($action) { |
136 | case CSV_INSERT: |
137 | $this->execute("INSERT IGNORE INTO {$this->table} SET $set"); |
138 | break; |
139 | case CSV_REPLACE: |
140 | $this->execute("REPLACE INTO {$this->table} SET $set"); |
141 | break; |
142 | case CSV_UPDATE: |
143 | if (!$this->execute("INSERT INTO {$this->table} SET $set")) { |
144 | $ops = $this->makeRequestArgs($line, $update_relation); |
145 | $set = join(', ', $ops); |
146 | $this->execute("UPDATE {$this->table} SET $set WHERE {$ops[$this->key]}"); |
147 | } |
148 | break; |
149 | } |
150 | } |
151 | } |
152 | |
153 | static public function dynamicCond($line, $key) |
154 | { |
155 | static $fields, $conds, $values, $thens, $elses; |
156 | |
157 | if (!isset($fields)) { |
158 | $fields = Env::v('csv_cond_field'); |
159 | $conds = Env::v('csv_cond'); |
160 | $values = Env::v('csv_cond_value'); |
161 | $thens = Env::v('csv_cond_then'); |
162 | $elses = Env::v('csv_cond_else'); |
163 | } |
164 | $field = $line[$fields[$key]]; |
165 | $cond = $conds[$key]; |
166 | $value = $values[$key]; |
167 | if (is_numeric($field) && is_numeric($value)) { |
168 | $field = floatval($field); |
169 | $value = floatval($value); |
170 | } |
171 | switch ($cond) { |
172 | case 'defined': $ok = (!empty($field)); break; |
173 | case 'equals': $ok = ($field == $value); break; |
174 | case 'contains': $ok = (strpos($field, $value) !== false); break; |
175 | case 'contained': $ok = (strpos($value, $field) !== false); break; |
176 | case 'greater': $ok = ($field > $value); break; |
177 | case 'greater_or_equal': $ok ($field >= $value); break; |
178 | case 'lower': $ok = ($field < $value); break; |
179 | case 'lower_or_equal': $ok = ($field <= $value); break; |
180 | default: $ok = false; |
181 | } |
182 | if ($ok) { |
183 | return $thens[$key]; |
184 | } else { |
185 | return $elses[$key]; |
186 | } |
187 | } |
188 | |
189 | public function registerFunction($name, $desc, $callback) |
190 | { |
191 | if (is_callable($callback)) { |
192 | $this->user_functions['func_' . $name] = array('desc' => $desc, 'callback' => $callback); |
193 | return true; |
194 | } |
195 | return false; |
196 | } |
197 | |
198 | /** Handle insertion form |
199 | * @param $page PlatalPage to process |
200 | * @param $url URI of the page |
201 | * @param $field Editable fields |
202 | */ |
203 | public function apply(&$page, $url, $fields = null) |
204 | { |
205 | if (is_null($fields)) { |
206 | $fields = $this->getFieldList(); |
207 | } |
208 | if (is_null($fields)) { |
209 | return false; |
210 | } |
211 | |
212 | $current = Env::v('csv_page'); |
213 | if (empty($current)) { |
214 | $current = 'source'; |
215 | } |
216 | $next = Env::v('csv_next_page'); |
217 | if (empty($next)) { |
218 | $next = $current; |
219 | } |
220 | $csv = Env::v('csv'); |
221 | if ($current == 'source' && Env::has('csv_valid')) { |
222 | $csv = Env::v('csv_source'); |
223 | $next = 'values'; |
224 | } |
225 | if ($csv) { |
226 | $this->setCSV($csv); |
227 | } |
228 | if ($current == 'values' && Env::has('csv_valid')) { |
229 | $next = 'valid'; |
230 | } |
231 | if (empty($csv)) { |
232 | $next = 'source'; |
233 | } |
234 | if ($next == 'valid') { |
235 | $insert = Env::v('csv_value'); |
236 | $values = Env::v('csv_user_value'); |
237 | $update = Env::v('csv_update'); |
238 | foreach ($insert as $key=>$value) { |
239 | if (empty($value)) { |
240 | $insert[$key] = null; |
241 | } elseif ($value == 'user_value') { |
242 | $insert[$key] = $values[$key]; |
243 | } elseif ($value == 'cond_value') { |
244 | $insert[$key] = array($this, 'dynamicCond'); |
245 | } elseif (array_key_exists($value, $this->user_functions)) { |
246 | $insert[$key] = $this->user_functions[$value]['callback']; |
247 | } |
248 | if (isset($update[$key])) { |
249 | $update[$key] = $insert[$key]; |
250 | } |
251 | } |
252 | if ($current == 'valid' && Env::has('csv_valid')) { |
253 | $this->run(Env::v('csv_action'), $insert, $update); |
254 | $page->assign('csv_done', true); |
255 | } else { |
256 | $preview = array(); |
257 | foreach ($this->data as $line) { |
258 | $preview[] = $this->makeAssoc($line, $insert); |
259 | } |
260 | $page->assign('csv_preview', $preview); |
261 | } |
262 | } |
263 | $page->assign('csv_index', $this->index); |
264 | $page->assign('csv_funtions', $this->user_functions); |
265 | $page->assign('csv_page', $next); |
266 | $page->assign('csv_path', $url); |
267 | $page->assign('csv_fields', $fields); |
268 | $page->assign('csv', $csv); |
269 | } |
270 | } |
271 | |
272 | ?> |