diogenes.table-editor.inc.php

<?php
/*
 * Copyright (C) 2003-2004 Polytechnique.org
 * http://opensource.polytechnique.org/
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */


require_once dirname(__FILE__).'/diogenes.misc.inc.php';
require_once dirname(__FILE__).'/diogenes.database.table.inc.php';

class DiogenesTableEditor extends DiogenesDatabaseTable {
  var $actions;
  var $autoinc;
  var $id;
  var $idedit;
  var $idsum = true;
  var $idfield;
  var $jtables = array();
  var $wheres = array();
  var $prefix = "frm_";
  var $readonly = false;
  var $maxrows = 0;

  var $table_class = "light";
  var $table_style = "";
  
  function DiogenesTableEditor($table, $idfield, $idedit = false) {
    global $globals;
    if (!is_object($globals->db))
      die("\$globals->db is not an object!");

    $this->DiogenesDatabaseTable($globals->db,$table);

    $this->actions = array();

    if (!isset($this->vars[$idfield]))
      die("the field '$idfield' was not found in '$table'");
    $this->idfield = $idfield;
    $extra = new flagset($this->vars[$idfield]["extra"]);
    $this->autoinc = $extra->hasflag("auto_increment");
    $this->idedit = $idedit;

    // unless the id field is editable, remove it from the variables
    if (!$this->idedit)
      unset($this->vars[$idfield]);
  }


  function addAction($text,$url)
  {
    array_push($this->actions, array("text" => $text, "url" =>$url));
  }


  function add_join_table($name,$joinid,$joindel,$joinextra="") {
    $this->jtables[$name] = array("joinid" => $joinid,"joindel" => $joindel,
      "joinextra" => $joinextra);
  }


  function add_join_field($table,$name,$field,$value,$desc,$sum,$type="text") {
    $this->vars[$name] = array("table" => $table, "field" => $field, "type" => $type,
     "value" => $value, "desc" => $desc, "sum" => $sum);
  }


  function add_where_condition($cond) {
    array_push($this->wheres, $cond);
  }


  function delete_db($id) {
    if ($id != '') {
      $this->dbh->query("delete from {$this->table} where {$this->idfield}='$id'");

      // delete dependencies in the tables on which we have a join
      foreach ($this->jtables as $key => $val) {
        if ($val['joindel']) {
          $sql = "delete from {$key} where {$val['joinid']}='$id'";
          if ($val['joinextra'])
            $sql .= " and {$val['joinextra']}";
          $this->dbh->query($sql);
        }
      }
      reset($this->jtables);
    }
  }


  function describe($name,$desc,$sum,$type='',$value='',$trans=null) {
    if (!isset($this->vars[$name]))
      die("unknown field $name");
    $this->vars[$name]["desc"] = $desc;
    $this->vars[$name]["sum"] = $sum;
    if (!empty($type))
      $this->vars[$name]["type"] = $type;
    if (!empty($value))
      $this->vars[$name]["value"] = $value;
    if (!empty($trans) and $type=="set")
      $this->vars[$name]["trans"] = $trans;
  }


  function describe_join_value($name,$desc,$sum,$vtable,$vjoinid,$vfield) {
    global $globals;
    if (!isset($this->vars[$name]))
      die("unknown field $name");
    $this->vars[$name]["desc"] = $desc;
    $this->vars[$name]["sum"] = $sum;
    $this->vars[$name]["type"] = "ext";

    $this->vars[$name]["vtable"] = $vtable;
    $this->vars[$name]["vjoinid"] = $vjoinid;
    $this->vars[$name]["vfield"] = $vfield;
  }


  function hide($name, $value = null) {
    if (!isset($this->vars[$name]))
      die("unknown field $name");

    if (!empty($value))
      $this->vars[$name]["value"] = $value;

    $this->vars[$name]["edit"] = false;
    $this->vars[$name]["show"] = false;
  }


  function hide_id() {
    $this->idsum = false;
  }


  function lock($name, $value = null) {
    if (!isset($this->vars[$name]))
      die("unknown field $name");
    
    if (!empty($value))
      $this->vars[$name]["value"] = $value;
    
    $this->vars[$name]["edit"] = false;
  }
  
  function from_db($id) {
    $sql = $this->make_select(false,$id);
    $res = $this->dbh->query($sql);

    if ($myrow = mysql_fetch_assoc($res)) {
      $this->id = $id;
      foreach ($this->vars as $key => $val) {
        $this->vars[$key]['value'] = $myrow[$key];
      }
      reset($this->vars);
      return true;
    } else {
      return false;
    }
  }


  function from_request() {
    if (isset($_REQUEST[$this->prefix.'id']))
      $this->id = clean_request($this->prefix.'id');

    foreach ($this->vars as $key => $val) {
      // if this field is editable, retrieve the value from $_REQUEST
      if ($val['edit'])
        $this->vars[$key]['value'] = clean_request($this->prefix.$key);

      // apply type-specific transformations
      switch ($val['type']) {
      case "set":
        if ( is_array($this->vars[$key]['value']) )
          $this->vars[$key]['value'] = implode(",",$this->vars[$key]['value']);
        break;
      case "timestamp":
        $this->vars[$key]['value'] = mktime($this->vars[$key]['value']);
        break;
      }
    }
    reset($this->vars);
  }


  function to_db() {
    $varlst = new flagset();
    foreach ($this->vars as $key => $val) {
      # we only want fields from our own table that are either editable or new
      if (($val['table'] == $this->table) && ($val['edit'] || !isset($this->id))) {
        switch ($val['type']) {
        case "password":
          if ($val['value'])
            $varlst->addflag("$key='".md5($val['value'])."'");
          break;
        default:
          $varlst->addflag("$key='{$val['value']}'");
        }
      }
    }
    reset($this->vars);

    if (isset($this->id)) {
      $sql = "update {$this->table} set {$varlst->value} where {$this->idfield}='{$this->id}'";
      if ($this->wheres)
        $sql .= " AND " . join(" AND ", $this->wheres);
    } else {
      if (!$this->autoinc && !$this->idedit) {
        list($this->id) = mysql_fetch_row($this->dbh->query("select MAX({$this->idfield})+1 from {$this->table}"));
        $varlst->addflag("{$this->idfield}='{$this->id}'");
      }
      $sql = "insert into {$this->table} set {$varlst->value}";
    }
    $this->dbh->query($sql);

    // retrieve the insertion id
    if ($this->idedit) { 
      $this->id = $this->vars[$this->idfield]['value'];
    } else if ($this->autoinc && !isset($this->id)) {
      $this->id = $this->dbh->insert_id();
    }
  }


  function make_join_flag($val) {
    if ($val['table'] == $this->table) {
      // this field is local, no join clause needed
      return "";
    } else {
      // not a local field, we need a join clause
      $tbl_key = $val['table'];
      $tbl_val = $this->jtables[$tbl_key];
      $flg = "left join $tbl_key on {$this->table}.{$this->idfield}={$tbl_key}.{$tbl_val['joinid']}";
      if ($tbl_val['joinextra'])
        $flg .= " and {$tbl_val['joinextra']}";
      return $flg;
    }
  }


  function make_select($list,&$num) {
    if ($list) {
      $varlst = new flagset("{$this->table}.{$this->idfield}");
      $orderby = "";
      $num = $this->idsum ? 1 : 0;
    } else {
      $varlst = new flagset();
    }

    $joinlst = new flagset();
    // run over all the variables
    foreach ($this->vars as $key => $val) {
      // if we are listing the summary, we want only the variables
      // where ['sum'] is true.
      if (!$list || $val['sum']) {
        // type conversion
        if ($val['type'] == "timestamp")
          $varlst->addflag("UNIX_TIMESTAMP({$val['table']}.{$val['field']}) as $key");
        else
          $varlst->addflag("{$val['table']}.{$val['field']} as $key");

        // do we need a join clause?
        if ($flag = $this->make_join_flag($val))
          $joinlst->addflag($flag);

        // if we are listing the summary, we want an order clause
        if ($list) {
          if (!$orderby) $orderby = "order by $key";
          $num++;
        }
      }
    }

    reset($this->vars);

    $sql = "select {$varlst->value} from {$this->table} {$joinlst->value}";

    # if we are in edit mode, add a WHERE condition
    if (!$list)
      $this->add_where_condition("{$this->table}.{$this->idfield}='$num'");

    # sum up all the WHERE conditions
    if ($this->wheres)
      $sql .= " where " . join(" AND ", $this->wheres);

    # add order by clause
    if ($list)
      $sql .= " $orderby";
   
    return $sql;
  }


  function set_maxrows($maxrows)
  {
    // check we were given a zero or positive number
    if ($maxrows < 0) {
      trigger_error("You cannot pass a negative number ($maxrows) to set_maxrows!");
      return;
    }
    
    $this->maxrows = $maxrows;
  }
  
  function run(&$page,$outputvar='',$template='') {
    global $globals;

    $action = clean_request('action');
    $page->assign('table',$this->table);

    switch($action) {
    case "edit":
      // check we are not in read-only mode
      if ($this->readonly) die("Sorry, this table is read-only.");
        
      // if this is an existing entry, retrieve it
      if (clean_request("{$this->prefix}id") != '') {
        $this->from_db(clean_request("{$this->prefix}id"));
        $page->assign('id',$this->id);
      }
      
      // remove the uneditable fields
      #foreach ($this->vars as $key => $val) {
      #if (!$val['edit'])
      #  unset($this->vars[$key]);
      #}
      reset($this->vars);
     
      $page->assign('doedit',true);
      break;
    case "update":
      // check we are not in read-only mode
      if ($this->readonly) die("Sorry, this table is read-only.");

      $this->from_request();
      $this->to_db();
      break;
    case "del":
      // check we are not in read-only mode
      if ($this->readonly) die("Sorry, this table is read-only.");
      
      $this->delete_db(clean_request("{$this->prefix}id"));
      break;
    }

    // if we are not in editor mode, display the list
    if ($action != "edit") {
      $ncols = 0;
      $sql = $this->make_select(true,$ncols);
      $res = $this->dbh->query($sql);
      $page->assign('ncols',$ncols);

      // determine start and stop of displayed results
      $p_total = mysql_num_rows($res);
      $p_start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
      $p_stop = $this->maxrows ? min($p_total, $p_start + $this->maxrows) : $p_total;
      $counter = 0;

      while (($counter < $p_stop) and ($myarr = mysql_fetch_assoc($res))) {
        if ($counter >= $p_start) {
          $actions = array();
          foreach ($this->actions as $myaction) {
            $url = $myaction['url'];
            foreach ($myarr as $key=>$val)
              $url = str_replace("%$key%", $val, $url);

            array_push($actions, array($myaction['text'],$url));
          }
          $page->append('rows', array($myarr[$this->idfield], $myarr, $actions) );
        }
        $counter++;
      }
      mysql_free_result($res);

      // smarty assignements for prev / next page links
      $page->assign('p_prev', $p_start ? max($p_start - $this->maxrows, 0) : -1);
      $page->assign('p_next', ($p_stop < $p_total) ? $p_stop  : - 1 );
      $page->assign('p_total', $p_total);
     
    }

    $page->assign('vars',$this->vars);
    $page->assign('prefix',$this->prefix);
    $page->assign('idfield',$this->idfield);
    $page->assign('idsum',$this->idsum);
    $page->assign('readonly',$this->readonly);
    if ($this->readonly && ($this->actions == array()))
      $page->assign('hideactions', 1);
    $page->assign('table_class', $this->table_class);
    $page->assign('table_style', $this->table_style);
      
    // translations
    $page->assign('msg_previous_page', __("previous page"));
    $page->assign('msg_next_page', __("next page"));
    $page->assign('msg_id', __("id"));
    $page->assign('msg_action', __("action"));
    $page->assign('msg_create', __("create"));
    $page->assign('msg_delete', __("delete"));
    $page->assign('msg_edit', __("edit"));
    $page->assign('msg_new_entry', __("new entry"));
    $page->assign('msg_existing_entry', __("existing entry"));
    $page->assign('msg_no_change', __("(blank = no change)"));
    $page->assign('msg_back', __("back"));
    $page->assign('msg_confirm_delete', 
                  __("You are about to delete this entry. Do you want to proceed?"));
    $page->assign('msg_submit', __("Submit"));
    
    // if requested, assign the content to be displayed
    if (!empty($outputvar)) {
      if (empty($template))
        $template = $globals->libroot."/templates/table-editor.tpl";
      $page->assign($outputvar, $page->fetch($template));
    }
  }

}

?>

---