rename to pod2diogenes
[diogenes.git] / include / diogenes / diogenes.table-editor.inc.php
... / ...
CommitLineData
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
22require_once dirname(__FILE__).'/diogenes.misc.inc.php';
23require_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 */
31class 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?>