SQL dump - InnoDB, foreign keys
[mirrors/SokoMan.git] / index.php
1 <?php
2 /*
3 * SkladovySystem - Storage management system compatible with LMS
4 * Copyright (C) 2011 Tomas Mudrunka
5 *
6 * This program is free software: you can redistribute it and/or modify
7 * it under the terms of the GNU Affero General Public License as
8 * published by the Free Software Foundation, either version 3 of the
9 * License, or (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 Affero General Public License for more details.
15 *
16 * You should have received a copy of the GNU Affero General Public License
17 * along with this program. If not, see <http://www.gnu.org/licenses/>.
18 */
19
20 require_once('sklad.conf.php');
21 require_once('Sklad_LMS-fake.class.php');
22 require_once('HTTP_Auth.class.php');
23
24 /**
25 * Trida poskytuje podpurne funkce pro generovani HTML kodu specificke pro sklad
26 *
27 * Tato trida by nemela sama nic vypisovat (vyjma chybovych a debugovacich hlasek)!
28 *
29 * @package Sklad_HTML
30 * @author Tomas Mudrunka
31 */
32 class Sklad_HTML {
33 function header($title='') {
34 $home = URL_HOME;
35 $script = $_SERVER['SCRIPT_NAME'];
36 $search = htmlspecialchars(@trim($_GET['q']));
37 $message = htmlspecialchars(@trim($_GET['message']));
38 return <<<EOF
39 <head>
40 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
41 </head>
42 <h1><a href="$script/">SystémSklad</a><small>$title</small></h1>
43 <div>
44 <menu>
45 <li><a href="?logout">Logout</a></li>
46 <li><a href="$script/">Home</a></li>
47 </menu>
48 <form action="?" method="GET">
49 <input type="text" name="q" placeholder="regexp..." value="$search" />
50 <input type="submit" value="filter" />
51 </form>
52 <!-- form action="$script/" method="GET">
53 <input type="text" name="q" placeholder="regexp..." value="$search" />
54 <input type="submit" value="search items" />
55 </form -->
56 </div>
57 <div style="background-color:#FFDDDD;">
58 <font color="red">$message</font>
59 </div>
60 EOF;
61 }
62
63 function row($row) {
64 $html='<tr>';
65 foreach($row as $var) {
66 if(trim($var) == '') $var = '&nbsp;';
67 $html.="<td>$var</td>";
68 }
69 $html.='</tr>';
70 return $html;
71 }
72
73 function table(&$table, $params='border=1') {
74 $html="<table $params>";
75 $header=true;
76 foreach($table as $row) {
77 if($header) {
78 $html.=$this->row(array_keys($row));
79 $header=false;
80 }
81 $html.=$this->row($row);
82 }
83 $html.='</table>';
84 return $html;
85 }
86
87 function internal_url($link) {
88 return $_SERVER['SCRIPT_NAME'].'/'.$link;
89 }
90
91 function link($title='n/a', $link='#void', $internal=true) {
92 if($internal) $link = $this->internal_url($link);
93 return "<a href='$link'>$title</a>";
94 }
95
96 function img($src='#void', $title='img') {
97 return "<img src='$src' alt='$title' title='$title' width=64 />";
98 }
99
100 function table_add_images(&$table) {
101 $image = array('model_id');
102 foreach($table as $id => $row) {
103 foreach($image as $column) if(isset($table[$id][$column])) {
104 $type = @array_shift(preg_split('/_/', $column));
105 $src=URL_IMAGES."/$type/".$table[$id][$column].'.jpg';
106 $table[$id][$type.'_image']=$this->img($src, $table[$id][$column]);
107 }
108 }
109 }
110
111 function table_collapse(&$table) {
112 $collapse = array(
113 'item_id' => 'item_id',
114 'model_id' => 'model_name',
115 'category_id' => 'category_name',
116 'producer_id' => 'producer_name',
117 'vendor_id' => 'vendor_name',
118 'room_id' => 'room_name',
119 'status_id' => 'status_name',
120 );
121 foreach($table as $id => $row) {
122 foreach($collapse as $link => $title)
123 if(isset($table[$id][$link])) {
124 $type = @array_shift(preg_split('/_/', $link));
125 if($link != $title) unset($table[$id][$link]);
126 $table[$id][$title]=$this->link($row[$title], $type.'/'.$row[$link].'/');
127 }
128 }
129 }
130
131 function table_sort(&$table) {
132 $precedence = array('item_id', 'model_image', 'model_name','model_descript','category_name','status_name','room_name');
133 $table_sorted = array();
134 foreach($table as $id => $row) {
135 $table_sorted[$id] = array();
136 foreach($precedence as $column) if(isset($table[$id][$column])) {
137 $table_sorted[$id][$column]=$table[$id][$column];
138 unset($table[$id][$column]);
139 }
140 $table_sorted[$id]=array_merge($table_sorted[$id],$table[$id]);
141 }
142 $table = $table_sorted;
143 }
144
145 function render_item_table($table) {
146 $this->table_add_images($table);
147 $this->table_collapse($table);
148 $this->table_sort($table);
149 return $this->table($table);
150 }
151
152 function input($name=false, $value=false, $type='text', $placeholder=false, $options=false) {
153 $html = "<input type='$type' ";
154 if($name) $html.= "name='$name' ";
155 if(!is_bool($value)) $html.= "value='$value' ";
156 if($options) $html.= "$options ";
157 if($placeholder) $html.= "placeholder='$placeholder' ";
158 $html .= '/>';
159 return $html;
160 }
161
162 function select($name, $selectbox, $default=false) {
163 //echo('<pre>'); print_r($selectbox);
164 $html = "<select name='$name'>";
165
166 if($default) {
167 $value=$default; $title=$selectbox[$value];
168 $html .= "<option value='$value'>$value :: $title</option>";
169 unset($selectbox[$value]);
170 }
171 foreach($selectbox as $value => $title) {
172 $html .= "<option value='$value'>$value :: $title</option>";
173 }
174 $html .= "</select>";
175 return $html;
176 }
177
178 function render_insert_form($class, $columns, $selectbox=array(), $current=false, $multi_insert=true) {
179 //echo('<pre>'); print_r($selectbox);
180 //echo('<pre>'); print_r($current);
181 $update = false;
182 if(is_array($current)) {
183 $update = true;
184 $current = array_shift($current);
185 }
186
187 $html='<form method="POST">';
188 if($multi_insert) $html.='<div name="input_set" style="float:left; border:1px solid grey;">';
189 //$html.=$this->input('table', $class, 'hidden');
190 foreach($columns as $column) {
191 $html.=$class.':<b>'.$column['Field'].'</b>: ';
192 $name="values[$class][".$column['Field'].'][]';
193 $val = $update ? $current[$column['Field']] : false;
194 switch(true) {
195 case preg_match('/auto_increment/', $column['Extra']):
196 if(!$val) $val = '';
197 $html.=$this->input($name, $val, 'hidden');
198 $html.=$val.'(AUTO)';
199 break;
200 case isset($selectbox[$column['Field']]):
201 $html.=$this->select($name,$selectbox[$column['Field']],$val);
202 break;
203 default:
204 $html.=$this->input($name, $val);
205 break;
206 }
207 $html.='<br />';
208 }
209
210 if($multi_insert) {
211 //TODO, move to separate JS file
212 $html.=<<<EOF
213 </div>
214 <span name="input_set_next"></span><br style="clear:both" />
215 <script>
216 function duplicate_element(what, where) {
217 document.getElementsByName(where)[0].outerHTML =
218 document.getElementsByName(what)[0].outerHTML
219 + document.getElementsByName(where)[0].outerHTML;
220 }
221 </script>
222 <a href='#' onClick="duplicate_element('input_set', 'input_set_next')">+</a>
223 EOF;
224 }
225
226 $btn = is_array($current) ? 'UPDATE' : 'INSERT';
227 $html.=$this->input(false, $btn, 'submit');
228 $html.='</form>';
229 return $html;
230 }
231 }
232
233 /**
234 * Trida poskytuje rozhrani k databazi skladu
235 *
236 * @package Sklad_DB
237 * @author Tomas Mudrunka
238 */
239 class Sklad_DB extends PDO {
240 function __construct() {
241 $this->lms = new Sklad_LMS();
242
243 parent::__construct(
244 DB_DSN, DB_USER, DB_PASS,
245 array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") //Force UTF8 for MySQL
246 );
247 }
248
249 function escape($str) {
250 return preg_replace('(^.|.$)', '', $this->quote($str)); //TODO HACK
251 }
252
253 function build_query_select($class, $id=false, $limit=false, $offset=0, $search=false, $order=false, $suffix_id='_id') {
254 //Configuration
255 $join = array(
256 'item' => array('model', 'category', 'producer', 'vendor', 'room', 'status'),
257 'model' => array('category', 'producer')
258 );
259 $search_fields = array(
260 'item' => array('item_id','model_name','model_barcode','model_descript','producer_name','vendor_name')
261 );
262
263 //Escaping
264 $class = $this->escape($class);
265
266 //SELECT
267 $sql="SELECT * FROM $class\n";
268 //JOIN
269 if(isset($join[$class])) foreach($join[$class] as $j) $sql .= "LEFT JOIN $j USING($j$suffix_id)\n";
270 //WHERE/REGEXP
271 if($search) {
272 $search = $this->quote($search);
273 if(!isset($search_fields[$class])) {
274 trigger_error("Ve tride $class zatim vyhledavat nemozno :-(");
275 die();
276 }
277 $sql .= 'WHERE FALSE ';
278 foreach($search_fields[$class] as $column) $sql .= "OR $column REGEXP $search ";
279 } elseif($id) $sql .= "WHERE $class$suffix_id = $id\n";
280 //LIMIT/OFFSET
281 if($limit) {
282 $limit = $this->escape((int)$limit);
283 $offset = $this->escape((int)$offset);
284 $sql .= "LIMIT $offset,$limit\n";
285 }
286 //ORDER
287 if(!$order) $order = $class.$suffix_id;
288 $sql .= "ORDER BY $order";
289
290 return $sql;
291 }
292
293 function safe_query($sql) {
294 $result = $this->query($sql);
295 if(!$result) {
296 $error = $this->errorInfo();
297 die(trigger_error("<font color=red><b>QUERY FAILED ($error[0],$error[1]): </b>$error[2]<br /><br /><b>QUERY:</b>\n<pre>$sql</pre></font>"));
298 }
299 return $result;
300 }
301
302 function get_listing($class, $id=false, $limit=false, $offset=0, $search=false, $indexed=array(), $suffix_id='_id') {
303 $sql = $this->build_query_select($class, $id, $limit, $offset, $search);
304 $result = $this->safe_query($sql)->fetchAll(PDO::FETCH_ASSOC);
305 if(!$result || !is_array($indexed)) return $result;
306
307 foreach($result as $key => $row) $indexed[$row[$class.$suffix_id]]=$row;
308 return $indexed;
309 }
310
311 function get_columns($class) {
312 $class = $this->escape($class);
313 $sql = "SHOW COLUMNS FROM $class;";
314 return $this->safe_query($sql)->fetchAll(PDO::FETCH_ASSOC);
315 }
316
317 function columns_get_selectbox($columns, $class=false, $suffix_id='_id', $suffix_name='_name') {
318 $selectbox=array();
319 foreach($columns as $column) {
320 if($class && $column['Field'] == $class.$suffix_id) continue;
321 if(!preg_match('/'.$suffix_id.'$/', $column['Field'])) continue;
322 $table=preg_replace('/'.$suffix_id.'$/','',$column['Field']);
323 $sql = "SELECT $table$suffix_id, $table$suffix_name FROM $table;"; //TODO: tabulka nemusi obsahovat *_name!!! momentalne se to tyka jen tabulky user (a item - u ty to nevadi)!
324 $result=$this->safe_query($sql)->fetchAll(PDO::FETCH_ASSOC);
325 foreach($result as $row) $selectbox[$table.$suffix_id][$row[$table.$suffix_id]]=$row[$table.$suffix_name];
326 }
327 //echo('<pre>'); print_r($selectbox);
328 return $selectbox;
329 }
330
331 function build_query_insert($table, $values, $replace=true, $suffix_id='_id') {
332 $table = $this->escape($table);
333
334 //Get list of POSTed columns
335 $columns = implode(',',array_map(array($this,'escape'), array_keys($values[0])));
336
337 //Insert into table (columns)
338 $sql = 'INSERT';
339 if($replace) $sql = 'REPLACE';
340 $sql .= " INTO $table ($columns) VALUES ";
341
342 //Values (a,b,c),(d,e,f)
343 $comma='';
344 foreach($values as $row) {
345 $sql .= $comma.'('.implode(',',array_map(array($this,'quote'), $row)).')';
346 $comma = ',';
347 }
348
349 //Terminate
350 $sql .= ';';
351 return $sql;
352 }
353
354 function insert_or_update($table, $values) {
355 $sql = $this->build_query_insert($table, $values);
356 $this->safe_query($sql);
357 return $this->lastInsertId();
358 }
359
360 function insert_or_update_multitab($values) {
361 $last=false;
362 foreach($values as $table => $rows) $last = $this->insert_or_update($table, $rows);
363 return $last;
364 }
365
366 function delete($table, $id, $suffix_id='_id') {
367 $key = $this->escape($table.$suffix_id);
368 $table = $this->escape($table);
369 $id = $this->quote($id);
370 return $this->safe_query("DELETE FROM $table WHERE $key = $id LIMIT 1;");
371 }
372 }
373
374 /**
375 * Trida implementuje uzivatelske rozhrani skladu
376 *
377 * Example usage:
378 * $sklad = new Sklad_UI();
379 * $sklad->process_http_request();
380 *
381 * @package Sklad_UI
382 * @author Tomas Mudrunka
383 */
384 class Sklad_UI {
385 function __construct() {
386 $this->db = new Sklad_DB();
387 $this->html = new Sklad_HTML();
388 }
389
390 function render_items($class, $id=false, $limit=false, $offset=0, $search=false) {
391 return $this->html->render_item_table($this->db->get_listing($class, $id, $limit, $offset, $search));
392 }
393
394 function render_form_add($class) {
395 $columns = $this->db->get_columns($class);
396 $selectbox = $this->db->columns_get_selectbox($columns, $class);
397 return $this->html->render_insert_form($class, $columns, $selectbox);
398 }
399
400 function render_form_edit($class, $id) {
401 $columns = $this->db->get_columns($class);
402 $selectbox = $this->db->columns_get_selectbox($columns, $class);
403 $current = $this->db->get_listing($class, $id);
404 return $this->html->render_insert_form($class, $columns, $selectbox, $current);
405 }
406
407 function render_single_record_details($class, $id) {
408 $id_next = $id + 1;
409 $id_prev = $id - 1 > 0 ? $id - 1 : 0;
410 $get = $_SERVER['QUERY_STRING'] != '' ? '?'.$_SERVER['QUERY_STRING'] : '';
411 $html='';
412 $html.= $this->html->link('<<', "$class/$id_prev/");
413 $html.= '-';
414 $html.= $this->html->link('>>', "$class/$id_next/");
415 $html.= '<br />';
416 $html.= $this->html->link('edit', "$class/$id/edit/");
417 return $html;
418 }
419
420 function render_listing_navigation($class, $id, $limit, $offset) {
421 $offset_next = $offset + $limit;
422 $offset_prev = $offset - $limit > 0 ? $offset - $limit : 0;
423 $get = $_SERVER['QUERY_STRING'] != '' ? '?'.$_SERVER['QUERY_STRING'] : '';
424 $html='';
425 $html.= $this->html->link('<<', "$class/$id/$limit/$offset_prev/$get");
426 $html.= '-';
427 $html.= $this->html->link('>>', "$class/$id/$limit/$offset_next/$get");
428 $html.= '<br />';
429 $html.= $this->html->link('new', "$class/new/$get");
430 return $html;
431 }
432
433 function render_listing_extensions($class, $id, $limit, $offset, $edit=false) {
434 $html='';
435 if(is_numeric($id)) {
436 $html.=$this->render_single_record_details($class, $id);
437 } else {
438 $html.=$this->render_listing_navigation($class, '*', $limit, $offset);
439 }
440 if($edit) {
441 $html.='<br />TODO UPDATE FORM!<br />';
442 $html.= $this->render_form_edit($class, $id);
443 $action = $_SERVER['SCRIPT_NAME']."/$class/$id/delete";
444 $html.= "<form action='$action' method='POST'>";
445 $html.= $this->html->input(false, 'DELETE', 'submit');
446 $html.= 'sure?'.$this->html->input('sure', false, 'checkbox');
447 $html.= '</form>';
448 $action = $_SERVER['SCRIPT_NAME']."/$class/$id/image";
449 $html.= "<form action='$action' method='POST' enctype='multipart/form-data'>";
450 $html.= $this->html->input('image', false, 'file', false, 'size="30"');
451 $html.= $this->html->input(false, 'IMAGE', 'submit');
452 $html.='</form>';
453 }
454 return $html;
455 }
456
457 function check_auth() {
458 new HTTP_Auth('SkladovejSystem', true, array($this->db->lms,'check_auth'));
459 }
460
461 function post_redirect_get($location, $message='') {
462 $location = $this->html->internal_url($location).'?message='.urlencode($message);
463 header('Location: '.$location);
464 die("Location: $location");
465 }
466
467 function safe_include($dir,$name,$vars=array(),$ext='.inc.php') {
468 if(preg_match('/[^a-zA-Z0-9-]/',$name)) die(trigger_error('SAFE INCLUDE: Securityfuck.'));
469 $filename="$dir/$name$ext";
470 if(!is_file($filename)) die(trigger_error('SAFE INCLUDE: Fuckfound.'));
471 foreach($vars as $var => $val) $$var=$val;
472 ob_start();
473 include($filename);
474 $out=ob_get_contents();
475 ob_end_clean();
476 return $out;
477 }
478
479 function process_http_request_post($action=false, $class=false, $id=false) {
480 if($_SERVER['REQUEST_METHOD'] != 'POST') return;
481 //echo('<pre>'); //DEBUG (maybe todo remove), HEADERS ALREADY SENT!!!!
482
483 //SephirPOST:
484
485 /* Tenhle foreach() prekopiruje promenne
486 * z: $_POST['values'][$table][$column][$id];
487 * do: $values[$table][$id][$column]
488 */
489 if(isset($_POST['values'])) {
490 $values=array();
491 foreach($_POST['values'] as $table => $columns) {
492 foreach($columns as $column => $ids) {
493 foreach($ids as $id => $val) $values[$table][$id][$column] = $val;
494 }
495 }
496 //die(print_r($values));
497 }
498
499 if($action) switch($action) {
500 case 'new':
501 case 'edit':
502 //if(!isset($_POST['table'])) die(trigger_error("Jest nutno specifikovat tabulku voe!"));
503 //$table=$_POST['table'];
504 $table='item';
505 //print_r($values); //debug
506 $last = $this->db->insert_or_update_multitab($values);
507 $last = "$table/$last/";
508 $next = "$table/new/";
509 echo 'Hotovo. Poslední vložený záznam naleznete '.$this->html->link('zde', $last).'.<br />'.
510 'Další záznam přidáte '.$this->html->link('zde', $next).'.';
511 die();
512 break;
513 case 'delete':
514 if(!isset($_POST['sure']) || !$_POST['sure']) die(trigger_error('Sure user expected :-)'));
515 $this->db->delete($class, $id);
516 $this->post_redirect_get("$class", "Neco (pravdepodobne /$class/$id) bylo asi smazano. Fnuk :'-(");
517 break;
518 case 'image':
519 $image_classes = array('model'); //TODO, use this more widely across the code
520 if(!in_array($class, $image_classes)) die(trigger_error("Nekdo nechce k DB Tride '$class' prirazovat obrazky!"));
521 $image_destination = DIR_IMAGES."/$class/$id.jpg";
522 if($_FILES['image']['name'] == '') die(trigger_error('Kazde neco se musi nejak jmenovat!'));
523 if(move_uploaded_file($_FILES['image']['tmp_name'], $image_destination)) {
524 chmod ($image_destination, 0664);
525 $this->post_redirect_get("$class/$id", 'Obrazek se naladoval :)');
526 } else die(trigger_error('Soubor se nenahral :('));
527 break;
528 default:
529 trigger_error('Nothin\' to do here my cutie :-*');
530 break;
531 }
532
533 die('POSTed pyčo!');
534 }
535
536 function process_http_request() {
537 $this->check_auth();
538
539 @ini_set('magic_quotes_gpc' , 'off');
540 if(get_magic_quotes_gpc()) {
541 die(trigger_error("Error: magic_quotes_gpc needs to be disabled! F00K!"));
542 }
543
544 $PATH_INFO=@trim($_SERVER[PATH_INFO]);
545 if($_SERVER['REQUEST_METHOD'] != 'POST') echo $this->html->header($PATH_INFO); //TODO tahle podminka naznacuje ze je v navrhu nejaka drobna nedomyslenost...
546
547
548 //Sephirot:
549 $PATH_CHUNKS = preg_split('/\//', $PATH_INFO);
550 if(!isset($PATH_CHUNKS[1])) $PATH_CHUNKS[1]='';
551 switch($PATH_CHUNKS[1]) {
552 case 'test': //test
553 die('Tell me why you cry');
554 break;
555 case 'assistant': //assistant
556 $assistant_vars['step'] = isset($PATH_CHUNKS[3]) && is_numeric($PATH_CHUNKS[3]) ? trim($PATH_CHUNKS[3]) : false;
557 echo $this->safe_include(DIR_ASSISTANTS,$PATH_CHUNKS[2],$assistant_vars);
558 break;
559 default: //?
560 $search = (isset($_GET['q']) && trim($_GET['q']) != '') ? trim($_GET['q']) : false;
561 $class = (isset($PATH_CHUNKS[1]) && $PATH_CHUNKS[1] != '') ? $PATH_CHUNKS[1] : 'item';
562 if(!isset($PATH_CHUNKS[2])) $PATH_CHUNKS[2]='';
563 switch($PATH_CHUNKS[2]) {
564 case 'new': //?/new
565 $this->process_http_request_post($PATH_CHUNKS[2], $class);
566 echo $this->render_form_add($class);
567 break;
568 default: //?/?
569 $id = (isset($PATH_CHUNKS[2]) && is_numeric($PATH_CHUNKS[2]) ? (int) $PATH_CHUNKS[2] : false);
570 if(!isset($PATH_CHUNKS[3])) $PATH_CHUNKS[3]='';
571 $edit=false;
572 switch($PATH_CHUNKS[3]) {
573 case 'edit': //?/?/edit
574 case 'image': //?/image
575 case 'delete': //?/delete
576 $this->process_http_request_post($PATH_CHUNKS[3], $class, $id);
577 $edit=true;
578 default: //?/?/?
579 $limit = (int) (isset($PATH_CHUNKS[3]) ? $PATH_CHUNKS[3] : '0');
580 $offset = (int) (isset($PATH_CHUNKS[4]) ? $PATH_CHUNKS[4] : '0');
581 echo $this->render_items($class, $id, $limit, $offset, $search);
582 echo $this->render_listing_extensions($class, $id, $limit, $offset, $edit);
583 //print_r(array("<pre>",$_SERVER));
584 break;
585 }
586 break;
587 }
588 break;
589 }
590 }
591 }
592
593 $sklad = new Sklad_UI();
594 $sklad->process_http_request();
595
596 echo("<hr/>");
This page took 1.224531 seconds and 5 git commands to generate.