Commit | Line | Data |
---|---|---|
6855525e JL |
1 | <?php |
2 | /* | |
3 | * Copyright (C) 2003-2004 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., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA | |
19 | */ | |
20 | ||
21 | ||
22 | require_once dirname(__FILE__).'/diogenes.misc.inc.php'; | |
23 | require_once dirname(__FILE__).'/diogenes.database.table.inc.php'; | |
24 | ||
25 | /** Generic class for editing MySQL tables. | |
26 | * | |
27 | * TODO: give an example of how to use the class | |
28 | * | |
29 | * @see DiogenesDatabaseTable | |
30 | */ | |
31 | class DiogenesTableEditor extends DiogenesDatabaseTable { | |
32 | /** The actions. */ | |
33 | var $actions; | |
34 | /** Is the idfield automatically incremented? */ | |
35 | var $autoinc; | |
36 | /** Id of the current entry. */ | |
37 | var $id; | |
38 | /** Is the id field editable? */ | |
39 | var $idedit; | |
40 | /** Is the id field in the summary? */ | |
41 | var $idsum = true; | |
42 | /** The field that is used as the primary key */ | |
43 | var $idfield; | |
44 | /** The tables we do a join on. */ | |
45 | var $jtables = array(); | |
46 | /** The where clause for the select */ | |
47 | var $wheres = array(); | |
48 | /** Prefix for all the form variables (to avoid conflicting with | |
49 | * any parameters used by the site's pages). */ | |
50 | var $prefix = "frm_"; | |
51 | /** Should editing functions be disabled? */ | |
52 | var $readonly = false; | |
53 | /** Maximum number of results per page, 0 for unlimited */ | |
54 | var $maxrows = 0; | |
55 | ||
56 | /** CSS class of the table that contains the editor */ | |
57 | var $table_class = "light"; | |
58 | /** Extra CSS style info for the table that contains the editor */ | |
59 | var $table_style = ""; | |
60 | ||
61 | /** The constructor. | |
62 | * | |
63 | * @param $table the MySQL table we are operating on | |
64 | * @param $idfield the field we use as unique id | |
65 | * @param $idedit is the id field editable ? | |
66 | */ | |
67 | function DiogenesTableEditor($table, $idfield, $idedit = false) { | |
68 | global $globals; | |
69 | if (!is_object($globals->db)) | |
70 | die("\$globals->db is not an object!"); | |
71 | ||
72 | $this->DiogenesDatabaseTable($globals->db,$table); | |
73 | ||
74 | $this->actions = array(); | |
75 | ||
76 | if (!isset($this->vars[$idfield])) | |
77 | die("the field '$idfield' was not found in '$table'"); | |
78 | $this->idfield = $idfield; | |
79 | $extra = new flagset($this->vars[$idfield]["extra"]); | |
80 | $this->autoinc = $extra->hasflag("auto_increment"); | |
81 | $this->idedit = $idedit; | |
82 | ||
83 | // unless the id field is editable, remove it from the variables | |
84 | if (!$this->idedit) | |
85 | unset($this->vars[$idfield]); | |
86 | } | |
87 | ||
88 | ||
89 | /** Add an "action" (hyperlink) to be displayed next to items in the summary. | |
90 | * | |
91 | * @param text the text for the hyperlink | |
92 | * @param url the url for the hyperlink. anything of the form %foobar% will | |
93 | * be replaced by the value of the field 'foobar' | |
94 | */ | |
95 | function addAction($text,$url) | |
96 | { | |
97 | array_push($this->actions, array("text" => $text, "url" =>$url)); | |
98 | } | |
99 | ||
100 | ||
101 | /** Adds a join with another table. | |
102 | * | |
103 | * @param $name the name of the table | |
104 | * @param $joinid the fields on which we do a join with our "id" | |
105 | * @param $joindel should we delete the joined entries in the table when we | |
106 | delete an entry from $this->table? | |
107 | * @param $joinextra extra clause for the join | |
108 | * | |
109 | * @see delete_db | |
110 | */ | |
111 | function add_join_table($name,$joinid,$joindel,$joinextra="") { | |
112 | $this->jtables[$name] = array("joinid" => $joinid,"joindel" => $joindel, | |
113 | "joinextra" => $joinextra); | |
114 | } | |
115 | ||
116 | ||
117 | /** EXPERIMENTAL : Add a field from another table. | |
118 | * | |
119 | * @param $table the table for which we are adding the field | |
120 | * @param $name name of the variable (AS clause) | |
121 | * @param $field the name of the table's field | |
122 | * @param $value the default value of the field | |
123 | * @param $desc a description | |
124 | * @param $sum should we display this field in the table summary? | |
125 | * @param $type type of the field : "enum", "set", "text", "textarea", "timestamp" | |
126 | */ | |
127 | function add_join_field($table,$name,$field,$value,$desc,$sum,$type="text") { | |
128 | $this->vars[$name] = array("table" => $table, "field" => $field, "type" => $type, | |
129 | "value" => $value, "desc" => $desc, "sum" => $sum); | |
130 | } | |
131 | ||
132 | ||
133 | /** Add a WHERE condition to the SELECT used for listing. | |
134 | * | |
135 | * @param cond string containing the WHERE condition | |
136 | */ | |
137 | function add_where_condition($cond) { | |
138 | array_push($this->wheres, $cond); | |
139 | } | |
140 | ||
141 | ||
142 | /** Deletes the specified entry from the database. | |
143 | * | |
144 | * @param $id id of the entry to be deleted | |
145 | */ | |
146 | function delete_db($id) { | |
147 | if ($id != '') { | |
148 | $this->dbh->query("delete from {$this->table} where {$this->idfield}='$id'"); | |
149 | ||
150 | // delete dependencies in the tables on which we have a join | |
151 | foreach ($this->jtables as $key => $val) { | |
152 | if ($val['joindel']) { | |
153 | $sql = "delete from {$key} where {$val['joinid']}='$id'"; | |
154 | if ($val['joinextra']) | |
155 | $sql .= " and {$val['joinextra']}"; | |
156 | $this->dbh->query($sql); | |
157 | } | |
158 | } | |
159 | reset($this->jtables); | |
160 | } | |
161 | } | |
162 | ||
163 | ||
164 | /** Describe a column and sets it visibility | |
165 | * | |
166 | * @param name the name of the field | |
167 | * @param desc the description for the field | |
168 | * @param sum a boolean indicating whether to display the field in the summary | |
169 | * @param type type of the field (optional) | |
170 | * @param value default value of the field (optional) | |
171 | * @param trans stores translations when it's a "set" value | |
172 | */ | |
173 | function describe($name,$desc,$sum,$type='',$value='',$trans=null) { | |
174 | if (!isset($this->vars[$name])) | |
175 | die("unknown field $name"); | |
176 | $this->vars[$name]["desc"] = $desc; | |
177 | $this->vars[$name]["sum"] = $sum; | |
178 | if (!empty($type)) | |
179 | $this->vars[$name]["type"] = $type; | |
180 | if (!empty($value)) | |
181 | $this->vars[$name]["value"] = $value; | |
182 | if (!empty($trans) and $type=="set") | |
183 | $this->vars[$name]["trans"] = $trans; | |
184 | } | |
185 | ||
186 | ||
187 | /** Describe a column and tell where to find possible values and sets it visibility | |
188 | * | |
189 | * @param name the name of the field | |
190 | * @param desc the description for the field | |
191 | * @param sum a boolean indicating whether to display the field in the summary | |
192 | * @param vtable the name of the table where are located the external values | |
193 | * @param vjoinid the $vtable field name corresponding to $this->table field $name | |
194 | * @param vfield the $vtable field wich hold the significative and comprehensive value | |
195 | */ | |
196 | function describe_join_value($name,$desc,$sum,$vtable,$vjoinid,$vfield) { | |
197 | global $globals; | |
198 | if (!isset($this->vars[$name])) | |
199 | die("unknown field $name"); | |
200 | $this->vars[$name]["desc"] = $desc; | |
201 | $this->vars[$name]["sum"] = $sum; | |
202 | $this->vars[$name]["type"] = "ext"; | |
203 | ||
204 | $this->vars[$name]["vtable"] = $vtable; | |
205 | $this->vars[$name]["vjoinid"] = $vjoinid; | |
206 | $this->vars[$name]["vfield"] = $vfield; | |
207 | } | |
208 | ||
209 | ||
210 | /** Make a field hidden and uneditable | |
211 | * | |
212 | * @param name the name of the field | |
213 | * @param value default value of the field (optional) | |
214 | */ | |
215 | function hide($name, $value = null) { | |
216 | if (!isset($this->vars[$name])) | |
217 | die("unknown field $name"); | |
218 | ||
219 | if (!empty($value)) | |
220 | $this->vars[$name]["value"] = $value; | |
221 | ||
222 | $this->vars[$name]["edit"] = false; | |
223 | $this->vars[$name]["show"] = false; | |
224 | } | |
225 | ||
226 | ||
227 | /** Do not display the id field in the summary | |
228 | * | |
229 | */ | |
230 | function hide_id() { | |
231 | $this->idsum = false; | |
232 | } | |
233 | ||
234 | ||
235 | /** Make a field uneditable | |
236 | * | |
237 | * @param name the name of the field | |
238 | * @param value default value of the field (optional) | |
239 | */ | |
240 | function lock($name, $value = null) { | |
241 | if (!isset($this->vars[$name])) | |
242 | die("unknown field $name"); | |
243 | ||
244 | if (!empty($value)) | |
245 | $this->vars[$name]["value"] = $value; | |
246 | ||
247 | $this->vars[$name]["edit"] = false; | |
248 | } | |
249 | ||
250 | /** Read the selected entry from database | |
251 | * | |
252 | * @param id id of the entry we want to read | |
253 | */ | |
254 | function from_db($id) { | |
255 | $sql = $this->make_select(false,$id); | |
256 | $res = $this->dbh->query($sql); | |
257 | ||
258 | if ($myrow = mysql_fetch_array($res)) { | |
259 | $this->id = $id; | |
260 | foreach ($this->vars as $key => $val) { | |
261 | $this->vars[$key]['value'] = $myrow[$key]; | |
262 | } | |
263 | reset($this->vars); | |
264 | return true; | |
265 | } else { | |
266 | return false; | |
267 | } | |
268 | } | |
269 | ||
270 | ||
271 | /** Read the current entry's values from the $_REQUEST variable | |
272 | */ | |
273 | function from_request() { | |
274 | if (isset($_REQUEST[$this->prefix.'id'])) | |
275 | $this->id = clean_request($this->prefix.'id'); | |
276 | ||
277 | foreach ($this->vars as $key => $val) { | |
278 | // if this field is editable, retrieve the value from $_REQUEST | |
279 | if ($val['edit']) | |
280 | $this->vars[$key]['value'] = clean_request($this->prefix.$key); | |
281 | ||
282 | // apply type-specific transformations | |
283 | switch ($val['type']) { | |
284 | case "set": | |
285 | if ( is_array($this->vars[$key]['value']) ) | |
286 | $this->vars[$key]['value'] = implode(",",$this->vars[$key]['value']); | |
287 | break; | |
288 | case "timestamp": | |
289 | $this->vars[$key]['value'] = mktime($this->vars[$key]['value']); | |
290 | break; | |
291 | } | |
292 | } | |
293 | reset($this->vars); | |
294 | } | |
295 | ||
296 | ||
297 | /** Write the current entry to database. | |
298 | */ | |
299 | function to_db() { | |
300 | $varlst = new flagset(); | |
301 | foreach ($this->vars as $key => $val) { | |
302 | # we only want fields from our own table that are either editable or new | |
303 | if (($val['table'] == $this->table) && ($val['edit'] || !isset($this->id))) { | |
304 | switch ($val['type']) { | |
305 | case "password": | |
306 | if ($val['value']) | |
307 | $varlst->addflag("$key='".md5($val['value'])."'"); | |
308 | break; | |
309 | default: | |
310 | $varlst->addflag("$key='{$val['value']}'"); | |
311 | } | |
312 | } | |
313 | } | |
314 | reset($this->vars); | |
315 | ||
316 | if (isset($this->id)) { | |
317 | $sql = "update {$this->table} set {$varlst->value} where {$this->idfield}='{$this->id}'"; | |
318 | if ($this->wheres) | |
319 | $sql .= " AND " . join(" AND ", $this->wheres); | |
320 | } else { | |
321 | if (!$this->autoinc && !$this->idedit) { | |
322 | list($this->id) = mysql_fetch_row($this->dbh->query("select MAX({$this->idfield})+1 from {$this->table}")); | |
323 | $varlst->addflag("{$this->idfield}='{$this->id}'"); | |
324 | } | |
325 | $sql = "insert into {$this->table} set {$varlst->value}"; | |
326 | } | |
327 | $this->dbh->query($sql); | |
328 | ||
329 | // retrieve the insertion id | |
330 | if ($this->idedit) { | |
331 | $this->id = $this->vars[$this->idfield]['value']; | |
332 | } else if ($this->autoinc && !isset($this->id)) { | |
333 | $this->id = $this->dbh->insert_id(); | |
334 | } | |
335 | } | |
336 | ||
337 | ||
338 | /** Returns the JOIN clause to read a field. | |
339 | * | |
340 | * @param $val the field we want to read | |
341 | */ | |
342 | function make_join_flag($val) { | |
343 | if ($val['table'] == $this->table) { | |
344 | // this field is local, no join clause needed | |
345 | return ""; | |
346 | } else { | |
347 | // not a local field, we need a join clause | |
348 | $tbl_key = $val['table']; | |
349 | $tbl_val = $this->jtables[$tbl_key]; | |
350 | $flg = "left join $tbl_key on {$this->table}.{$this->idfield}={$tbl_key}.{$tbl_val['joinid']}"; | |
351 | if ($tbl_val['joinextra']) | |
352 | $flg .= " and {$tbl_val['joinextra']}"; | |
353 | return $flg; | |
354 | } | |
355 | } | |
356 | ||
357 | ||
358 | /** Create the SELECT request to display the table summary | |
359 | * or to read an entry from the database | |
360 | * | |
361 | * @param $list boolean : are we displaying the summary? | |
362 | * @param $num depending on $list, either the column names or the entry to display | |
363 | */ | |
364 | function make_select($list,&$num) { | |
365 | if ($list) { | |
366 | $varlst = new flagset("{$this->table}.{$this->idfield}"); | |
367 | $orderby = ""; | |
368 | $num = $this->idsum ? 1 : 0; | |
369 | } else { | |
370 | $varlst = new flagset(); | |
371 | } | |
372 | ||
373 | $joinlst = new flagset(); | |
374 | // run over all the variables | |
375 | foreach ($this->vars as $key => $val) { | |
376 | // if we are listing the summary, we want only the variables | |
377 | // where ['sum'] is true. | |
378 | if (!$list || $val['sum']) { | |
379 | // type conversion | |
380 | if ($val['type'] == "timestamp") | |
381 | $varlst->addflag("UNIX_TIMESTAMP({$val['table']}.{$val['field']}) as $key"); | |
382 | else | |
383 | $varlst->addflag("{$val['table']}.{$val['field']} as $key"); | |
384 | ||
385 | // do we need a join clause? | |
386 | if ($flag = $this->make_join_flag($val)) | |
387 | $joinlst->addflag($flag); | |
388 | ||
389 | // if we are listing the summary, we want an order clause | |
390 | if ($list) { | |
391 | if (!$orderby) $orderby = "order by $key"; | |
392 | $num++; | |
393 | } | |
394 | } | |
395 | } | |
396 | ||
397 | reset($this->vars); | |
398 | ||
399 | $sql = "select {$varlst->value} from {$this->table} {$joinlst->value}"; | |
400 | ||
401 | # if we are in edit mode, add a WHERE condition | |
402 | if (!$list) | |
403 | $this->add_where_condition("{$this->table}.{$this->idfield}='$num'"); | |
404 | ||
405 | # sum up all the WHERE conditions | |
406 | if ($this->wheres) | |
407 | $sql .= " where " . join(" AND ", $this->wheres); | |
408 | ||
409 | # add order by clause | |
410 | if ($list) | |
411 | $sql .= " $orderby"; | |
412 | ||
413 | return $sql; | |
414 | } | |
415 | ||
416 | ||
417 | /** Set the maximum number of rows to return per page when viewing entries. | |
418 | * By default, this is set to 0, meaning all rows are returned. | |
419 | * | |
420 | * @param maxrows the maximum number of rows (0 means unlimited) | |
421 | */ | |
422 | function set_maxrows($maxrows) | |
423 | { | |
424 | // check we were given a zero or positive number | |
425 | if ($maxrows < 0) { | |
426 | trigger_error("You cannot pass a negative number ($maxrows) to set_maxrows!"); | |
427 | return; | |
428 | } | |
429 | ||
430 | $this->maxrows = $maxrows; | |
431 | } | |
432 | ||
433 | /** Process the requested action and fill out the Smarty variables for display. | |
434 | * By default, displays the entries in the database table. | |
435 | * | |
436 | * @param page the page that will display the editor's forms | |
437 | * @param outputvar the Smarty variable to which we should assign the output | |
438 | * @param template the template to use for display | |
439 | */ | |
440 | function run(&$page,$outputvar='',$template='') { | |
441 | global $globals; | |
442 | ||
443 | $action = clean_request('action'); | |
444 | $page->assign('table',$this->table); | |
445 | ||
446 | switch($action) { | |
447 | case "edit": | |
448 | // check we are not in read-only mode | |
449 | if ($this->readonly) die("Sorry, this table is read-only."); | |
450 | ||
451 | // if this is an existing entry, retrieve it | |
452 | if (clean_request("{$this->prefix}id") != '') { | |
453 | $this->from_db(clean_request("{$this->prefix}id")); | |
454 | $page->assign('id',$this->id); | |
455 | } | |
456 | ||
457 | // remove the uneditable fields | |
458 | #foreach ($this->vars as $key => $val) { | |
459 | #if (!$val['edit']) | |
460 | # unset($this->vars[$key]); | |
461 | #} | |
462 | reset($this->vars); | |
463 | ||
464 | $page->assign('doedit',true); | |
465 | break; | |
466 | case "update": | |
467 | // check we are not in read-only mode | |
468 | if ($this->readonly) die("Sorry, this table is read-only."); | |
469 | ||
470 | $this->from_request(); | |
471 | $this->to_db(); | |
472 | break; | |
473 | case "del": | |
474 | // check we are not in read-only mode | |
475 | if ($this->readonly) die("Sorry, this table is read-only."); | |
476 | ||
477 | $this->delete_db(clean_request("{$this->prefix}id")); | |
478 | break; | |
479 | } | |
480 | ||
481 | // if we are not in editor mode, display the list | |
482 | if ($action != "edit") { | |
483 | $ncols = 0; | |
484 | $sql = $this->make_select(true,$ncols); | |
485 | $res = $this->dbh->query($sql); | |
486 | $page->assign('ncols',$ncols); | |
487 | ||
488 | // determine start and stop of displayed results | |
489 | $p_total = mysql_num_rows($res); | |
490 | $p_start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0; | |
491 | $p_stop = $this->maxrows ? min($p_total, $p_start + $this->maxrows) : $p_total; | |
492 | $counter = 0; | |
493 | ||
494 | while (($counter < $p_stop) and ($myarr = mysql_fetch_array($res))) { | |
495 | if ($counter >= $p_start) { | |
496 | $actions = array(); | |
497 | foreach ($this->actions as $myaction) { | |
498 | $url = $myaction['url']; | |
499 | foreach ($myarr as $key=>$val) | |
500 | $url = str_replace("%$key%", $val, $url); | |
501 | ||
502 | array_push($actions, array($myaction['text'],$url)); | |
503 | } | |
504 | $page->append('rows', array($myarr[$this->idfield], $myarr, $actions) ); | |
505 | } | |
506 | $counter++; | |
507 | } | |
508 | mysql_free_result($res); | |
509 | ||
510 | // smarty assignements for prev / next page links | |
511 | $page->assign('p_prev', $p_start ? max($p_start - $this->maxrows, 0) : -1); | |
512 | $page->assign('p_next', ($p_stop < $p_total) ? $p_stop : - 1 ); | |
513 | $page->assign('p_total', $p_total); | |
514 | ||
515 | } | |
516 | ||
517 | $page->assign('vars',$this->vars); | |
518 | $page->assign('prefix',$this->prefix); | |
519 | $page->assign('idfield',$this->idfield); | |
520 | $page->assign('idsum',$this->idsum); | |
521 | $page->assign('readonly',$this->readonly); | |
522 | if ($this->readonly && ($this->actions == array())) | |
523 | $page->assign('hideactions', 1); | |
524 | $page->assign('table_class', $this->table_class); | |
525 | $page->assign('table_style', $this->table_style); | |
526 | ||
527 | // translations | |
528 | $page->assign('msg_previous_page', __("previous page")); | |
529 | $page->assign('msg_next_page', __("next page")); | |
530 | $page->assign('msg_id', __("id")); | |
531 | $page->assign('msg_action', __("action")); | |
532 | $page->assign('msg_create', __("create")); | |
533 | $page->assign('msg_delete', __("delete")); | |
534 | $page->assign('msg_edit', __("edit")); | |
535 | $page->assign('msg_new_entry', __("new entry")); | |
536 | $page->assign('msg_existing_entry', __("existing entry")); | |
537 | $page->assign('msg_no_change', __("(blank = no change)")); | |
538 | $page->assign('msg_back', __("back")); | |
539 | $page->assign('msg_confirm_delete', | |
540 | __("You are about to delete this entry. Do you want to proceed?")); | |
541 | $page->assign('msg_submit', __("Submit")); | |
542 | ||
543 | // if requested, assign the content to be displayed | |
544 | if (!empty($outputvar)) { | |
545 | if (empty($template)) | |
546 | $template = $globals->libroot."/templates/table-editor.tpl"; | |
547 | $page->assign($outputvar, $page->fetch($template)); | |
548 | } | |
549 | } | |
550 | ||
551 | } | |
552 | ||
553 | ?> |