include/diogenes/diogenes.table-editor.inc.php

   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 ?>