3 * Copyright (C) 2003-2004 Polytechnique.org
4 * http://opensource.polytechnique.org/
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.
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.
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
22 require_once dirname(__FILE__
).'/diogenes.misc.inc.php';
23 require_once dirname(__FILE__
).'/diogenes.database.table.inc.php';
25 /** Generic class for editing MySQL tables.
27 * TODO: give an example of how to use the class
29 * @see DiogenesDatabaseTable
31 class DiogenesTableEditor
extends DiogenesDatabaseTable
{
34 /** Is the idfield automatically incremented? */
36 /** Id of the current entry. */
38 /** Is the id field editable? */
40 /** Is the id field in the summary? */
42 /** The field that is used as the primary key */
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). */
51 /** Should editing functions be disabled? */
52 var $readonly = false
;
53 /** Maximum number of results per page, 0 for unlimited */
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 = "";
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 ?
67 function DiogenesTableEditor($table, $idfield, $idedit = false
) {
69 if (!is_object($globals->db
))
70 die("\$globals->db is not an object!");
72 $this->DiogenesDatabaseTable($globals->db
,$table);
74 $this->actions
= array();
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;
83 // unless the id field is editable, remove it from the variables
85 unset($this->vars
[$idfield]);
89 /** Add an "action" (hyperlink) to be displayed next to items in the summary.
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'
95 function addAction($text,$url)
97 array_push($this->actions
, array("text" => $text, "url" =>$url));
101 /** Adds a join with another table.
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
111 function add_join_table($name,$joinid,$joindel,$joinextra="") {
112 $this->jtables
[$name] = array("joinid" => $joinid,"joindel" => $joindel,
113 "joinextra" => $joinextra);
117 /** EXPERIMENTAL : Add a field from another table.
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"
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);
133 /** Add a WHERE condition to the SELECT used for listing.
135 * @param cond string containing the WHERE condition
137 function add_where_condition($cond) {
138 array_push($this->wheres
, $cond);
142 /** Deletes the specified entry from the database.
144 * @param $id id of the entry to be deleted
146 function delete_db($id) {
148 $this->dbh
->query("delete from {$this->table} where {$this->idfield}='$id'");
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);
159 reset($this->jtables
);
164 /** Describe a column and sets it visibility
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
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;
179 $this->vars
[$name]["type"] = $type;
181 $this->vars
[$name]["value"] = $value;
182 if (!empty($trans) and $type=="set")
183 $this->vars
[$name]["trans"] = $trans;
187 /** Describe a column and tell where to find possible values and sets it visibility
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
196 function describe_join_value($name,$desc,$sum,$vtable,$vjoinid,$vfield) {
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";
204 $this->vars
[$name]["vtable"] = $vtable;
205 $this->vars
[$name]["vjoinid"] = $vjoinid;
206 $this->vars
[$name]["vfield"] = $vfield;
210 /** Make a field hidden and uneditable
212 * @param name the name of the field
213 * @param value default value of the field (optional)
215 function hide($name, $value = null
) {
216 if (!isset($this->vars
[$name]))
217 die("unknown field $name");
220 $this->vars
[$name]["value"] = $value;
222 $this->vars
[$name]["edit"] = false
;
223 $this->vars
[$name]["show"] = false
;
227 /** Do not display the id field in the summary
231 $this->idsum
= false
;
235 /** Make a field uneditable
237 * @param name the name of the field
238 * @param value default value of the field (optional)
240 function lock($name, $value = null
) {
241 if (!isset($this->vars
[$name]))
242 die("unknown field $name");
245 $this->vars
[$name]["value"] = $value;
247 $this->vars
[$name]["edit"] = false
;
250 /** Read the selected entry from database
252 * @param id id of the entry we want to read
254 function from_db($id) {
255 $sql = $this->make_select(false
,$id);
256 $res = $this->dbh
->query($sql);
258 if ($myrow = mysql_fetch_array($res)) {
260 foreach ($this->vars
as $key => $val) {
261 $this->vars
[$key]['value'] = $myrow[$key];
271 /** Read the current entry's values from the $_REQUEST variable
273 function from_request() {
274 if (isset($_REQUEST[$this->prefix
.'id']))
275 $this->id
= clean_request($this->prefix
.'id');
277 foreach ($this->vars
as $key => $val) {
278 // if this field is editable, retrieve the value from $_REQUEST
280 $this->vars
[$key]['value'] = clean_request($this->prefix
.$key);
282 // apply type-specific transformations
283 switch ($val['type']) {
285 if ( is_array($this->vars
[$key]['value']) )
286 $this->vars
[$key]['value'] = implode(",",$this->vars
[$key]['value']);
289 $this->vars
[$key]['value'] = mktime($this->vars
[$key]['value']);
297 /** Write the current entry to database.
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']) {
307 $varlst->addflag("$key='".md5($val['value'])."'");
310 $varlst->addflag("$key='{$val['value']}'");
316 if (isset($this->id
)) {
317 $sql = "update {$this->table} set {$varlst->value} where {$this->idfield}='{$this->id}'";
319 $sql .= " AND " . join(" AND ", $this->wheres
);
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}'");
325 $sql = "insert into {$this->table} set {$varlst->value}";
327 $this->dbh
->query($sql);
329 // retrieve the insertion id
331 $this->id
= $this->vars
[$this->idfield
]['value'];
332 } else if ($this->autoinc
&& !isset($this->id
)) {
333 $this->id
= $this->dbh
->insert_id();
338 /** Returns the JOIN clause to read a field.
340 * @param $val the field we want to read
342 function make_join_flag($val) {
343 if ($val['table'] == $this->table
) {
344 // this field is local, no join clause needed
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']}";
358 /** Create the SELECT request to display the table summary
359 * or to read an entry from the database
361 * @param $list boolean : are we displaying the summary?
362 * @param $num depending on $list, either the column names or the entry to display
364 function make_select($list,&$num) {
366 $varlst = new flagset("{$this->table}.{$this->idfield}");
368 $num = $this->idsum ?
1 : 0;
370 $varlst = new flagset();
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']) {
380 if ($val['type'] == "timestamp")
381 $varlst->addflag("UNIX_TIMESTAMP({$val['table']}.{$val['field']}) as $key");
383 $varlst->addflag("{$val['table']}.{$val['field']} as $key");
385 // do we need a join clause?
386 if ($flag = $this->make_join_flag($val))
387 $joinlst->addflag($flag);
389 // if we are listing the summary, we want an order clause
391 if (!$orderby) $orderby = "order by $key";
399 $sql = "select {$varlst->value} from {$this->table} {$joinlst->value}";
401 # if we are in edit mode, add a WHERE condition
403 $this->add_where_condition("{$this->table}.{$this->idfield}='$num'");
405 # sum up all the WHERE conditions
407 $sql .= " where " . join(" AND ", $this->wheres
);
409 # add order by clause
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.
420 * @param maxrows the maximum number of rows (0 means unlimited)
422 function set_maxrows($maxrows)
424 // check we were given a zero or positive number
426 trigger_error("You cannot pass a negative number ($maxrows) to set_maxrows!");
430 $this->maxrows
= $maxrows;
433 /** Process the requested action and fill out the Smarty variables for display.
434 * By default, displays the entries in the database table.
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
440 function run(&$page,$outputvar='',$template='') {
443 $action = clean_request('action');
444 $page->assign('table',$this->table
);
448 // check we are not in read-only mode
449 if ($this->readonly
) die("Sorry, this table is read-only.");
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
);
457 // remove the uneditable fields
458 #foreach ($this->vars as $key => $val) {
460 # unset($this->vars[$key]);
464 $page->assign('doedit',true
);
467 // check we are not in read-only mode
468 if ($this->readonly
) die("Sorry, this table is read-only.");
470 $this->from_request();
474 // check we are not in read-only mode
475 if ($this->readonly
) die("Sorry, this table is read-only.");
477 $this->delete_db(clean_request("{$this->prefix}id"));
481 // if we are not in editor mode, display the list
482 if ($action != "edit") {
484 $sql = $this->make_select(true
,$ncols);
485 $res = $this->dbh
->query($sql);
486 $page->assign('ncols',$ncols);
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;
494 while (($counter < $p_stop) and ($myarr = mysql_fetch_array($res))) {
495 if ($counter >= $p_start) {
497 foreach ($this->actions
as $myaction) {
498 $url = $myaction['url'];
499 foreach ($myarr as $key=>$val)
500 $url = str_replace("%$key%", $val, $url);
502 array_push($actions, array($myaction['text'],$url));
504 $page->append('rows', array($myarr[$this->idfield
], $myarr, $actions) );
508 mysql_free_result($res);
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);
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
);
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"));
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));