<?php
    /**
    * o------------------------------------------------------------------------------o
    * | This package is dual licensed as GPL and a commercial license.               |
    * | If you use the code commercially (or if you don't want to be restricted by   |
    * | the GPL license), you will need the commercial license. It's only £49 (GBP - |
    * | roughly $98 depending on the exchange rate) and helps me out a lot. Thanks.  |
    * o------------------------------------------------------------------------------o
    *
    * © Copyright Richard Heyes
    */

    /**
    * TableEditor Class 1.0.6
    *
    * Allows editing of table information. See http://www.phpguru.org/static/TableEditor.html
    * for documentation.
    */

    /**
    * TODO:
    *
    * o Multiple column primary keys
    * o NULL support (?)
    * o auto_increment recognition
    * o Check that, when adding, fields with preset value lists contain one of the preset
    *   values.
    */

    require_once('Pager/Sliding.php');
    require_once('Net/URL.php');

    
    class TableEditor
    {
        
        /**
        * [DE] Button label
        * @var object
        */
        var $label_ok = "OK";

        /**
        * [DE] Button label
        * @var object
        */
        var $label_cancel = "Abbrechen";

        /**
        * [DE] Button label
        * @var object
        */
        var $label_apply = "Anwenden";
                
        /**
        * Database handle
        * @var object
        */
        var $db;


        /**
        * Name of primary key field
        * @var string
        */
        var $pk;


        /**
        * Name of table to edit
        * @var string
        */
        var $table;


        /**
        * Field data
        * @var array
        */
        var $fields;


        /**
        * Array of errors
        * @var array
        */
        var $errors;


        /**
        * Array of contextual errors, used for add/edit/copy page
        * @var array
        */
        var $contextErrors;


        /**
        * Various configuration settings
        * @var array
        */
        var $config;


        /**
        * Order by field and direction
        * @var array
        */
        var $orderby;


        /**
        * Addition callbacks
        * @var array
        */
        var $addCallbacks;


        /**
        * Edit callbacks
        * @var array
        */
        var $editCallbacks;


        /**
        * Copy callbacks
        * @var array
        */
        var $copyCallbacks;


        /**
        * Delete callbacks
        * @var array
        */
        var $deleteCallbacks;


        /**
        * Data validation callbacks
        * @var array
        */
        var $validationCallbacks;


        /**
        * Filter conditions
        * @var array
        */
        var $dataFilters;


        /**
        * Extra tables to join to
        * @var array
        */
        var $joinTables;


        /**
        * Constructor
        *
        * @param resource $db    Your MySQL connection resource
        * @param string   $table The table to be edited
        */
        function TableEditor($db, $table)
        {
            /**
            * If X-Moz set to prefetch, exit
            */
            if (!empty($_SERVER['HTTP_X_MOZ']) AND strcasecmp($_SERVER['HTTP_X_MOZ'], 'prefetch') == 0) {
                exit;
            }

            // Check the db resource
            if (!is_resource($db)) {
                die("First argument is not a valid database connection!");
            }

            $this->db         = $db;
            $this->table      = $table;
            $this->search     = null;
            $this->joinTables = array();

            $this->config['perPage']        = 25;
            $this->config['allowPKEditing'] = false;
            $this->config['allowView']      = true;
            $this->config['allowCSV']       = true;
            $this->config['allowAdd']       = true;
            $this->config['allowEdit']      = true;
            $this->config['allowCopy']      = true;
            $this->config['allowDelete']    = true;
            $this->config['allowASearch']   = true;
            $this->config['csvEscapeFunc']  = array($this, 'csvEscapeData');
            $this->config['useFunctions']   = false;
            $this->config['functions']      = array('Current Date'          => create_function('', 'return date("Y-m-d");'),
                                                    'Current Time'          => create_function('', 'return date("H:i:s");'),                                                     'Current Date and Time' => create_function('', 'return date("Y-m-d H:i:s");'),
                                                    'MD5 Hash'              => 'md5',                                                     'Unix Timestamp'        => 'time');
            $this->config['searchableFields'] = array();
            $this->config['title']            = 'MySQL TableEditor';
            $this->config['headerfile']       = null;
            $this->config['footerfile']       = null;

            $this->getStructure($table);
        }


        /**
        * PHP5 Constructor
        */
        function __construct()
        {
            $args = func_get_args();
            call_user_func_array(array(&$this, 'TableEditor'), $args);
        }


        /**
        * Adds an error to the object
        *
        * @param string $error The error message
        */
        function addError($error)
        {
            $this->errors[] = $error;
        }


        /**
        * Sets a contextual error for the add/edit/copy pages. These
        * errors appear next to the appropriate input. There can only be
        * one.
        *
        * @param string $field Field to set error for
        * @param string $error Error message to display
        */
        function setContextualError($field, $error)
        {
            $this->contextErrors[$field] = $error;
        }


        /**
        * Sets a config value
        *
        * @param string $name  Name of the config parameter
        * @param mixed  $value Value to set the parameter to
        */
        function setConfig($name, $value)
        {
            if (in_array($name, array('headerfile', 'footerfile')) AND !file_exists($value)) {
                $this->errors[] = "Datei für '$name' nicht gefunden: " . htmlspecialchars($value);
                return;
            }

            $this->config[$name] = $value;
        }


        /**
        * Retrieves config value
        *
        * @param string $name Name of the config parameter
        */
        function getConfig($name)
        {
            return $this->config[$name];
        }


        /**
        * Sets default order by
        *
        * @param string $field     Name of field to order by
        * @param int    $direction 1 = ascending, 0 = descending
        */
        function setDefaultOrderby($field, $direction = 1)
        {
            $this->orderby = array('field' => $field, 'direction' => (int)$direction);
        }


        /**
        * Sets default values for additions. Particularly useful for when fields
        * are hidden from users on the add page
        *
        * @param array $arr Associative array of default values, keyed by field name,
        *                   values are the default values.
        */
        function setDefaultValues($arr)
        {
            if (is_array($arr)) {
                foreach ($arr as $field => $v) {
                    if (!empty($this->fields[$field])) {
                        $this->fields[$field]['default'] = $v;
                    }
                }
            }
        }


        /**
        * Sets which fields are required to be filled in on the add/edit/copy pages.
        * If one is left blank, an error is shown.
        *
        * @param string ... One or more field names which are required
        */
        function setRequiredFields()
        {
            $args = func_get_args();

            foreach ($args as $a) {
                if (!empty($this->fields[$a])) {
                    $this->fields[$a]['required'] = true;
                }
            }
        }


        /**
        * Sets which fields can be searched on.
        *
        * @param string ... One or more field names which are searchable
        */
        function setSearchableFields()
        {
            $args = func_get_args();

            $this->setConfig('searchableFields', $args);
        }


        /**
        * Sets the display name of given fields. Arg should be an
        * array of fieldname/displayname combos.
        *
        * @param array $arr Associative array of display names. Key should be
        *                   the field name, value should be the display/friendly name
        */
        function setDisplayNames($arr)
        {
            if (!empty($arr) AND is_array($arr)) {
                foreach ($arr as $k => $v) {
                    if (!empty($this->fields[$k])) {
                        $this->fields[$k]['display'] = $v;
                    }
                }
            }
        }


        /**
        * Sets the values from the given SQL query. Query should return
        * two columns - the first being the value that is set in this
        * tables column, the second being the value that is shown to the
        * user. Commonly used for foreign keys.
        *
        * @param string $field Name of field
        * @param string $sql   SQL query to perform
        */
        function setValuesFromQuery($field, $sql)
        {
            if (!empty($this->fields[$field])) {
                $this->fields[$field]['values'] = $this->dbGetAssoc($sql);
            }
        }


        /**
        * Sets the values from the given array. Array should be keyed by *actual* value,
        * and the value should be the *display* value. These can be the same.
        *
        * @param string $field Name of field
        * @param array  $arr   Associative array of values
        */
        function setValuesFromArray($field, $arr)
        {
            if (!empty($this->fields[$field])) {
                $this->fields[$field]['values'] = $arr;
            }
        }


        /**
        * Adds a table on which to join to to fetch more columns. These columns
        * can be used just as the others can. If you're adding things like filters
        * and/or validation callbacks, they should be added *after* this method has
        * been called. Adding join tables automatically disables add/copy/delete.
        *
        * @param string $table       The table to join to
        * @param string $mainCol     The column in the main table to use in the join clause
        * @param string $foreignCol  The column in the joined to table to use in the join clause.
        */
        function addJoinTable($table, $mainCol, $foreignCol)
        {
            $this->joinTables[] = array('table'       => $table,
                                        'maincol'     => $mainCol,
                                        'foreigncol'  => $foreignCol);

            $this->getStructure($table, false);

            $this->setConfig('allowAdd', false);
            $this->setConfig('allowCopy', false);
            $this->setConfig('allowDelete', false);
        }


        /**
        * Sets the input type for editing/adding. Possible types can be:
        *  o text
        *  o textarea
        *  o select
        *  o password - This is not the normal HTML password input type, this
        *               will cause the display of *two* password inputs, to
        *               facilitate confirmation. If the two entered are not
        *               identical, an error is displayed.
        *  o bitmask  - This is a pseudo field type which accomodates bitmask
        *               values. You'll need to use one of the setValuesFrom*()
        *               methods to provide a user friendly set of values which
        *               correspond to the bit values. This is presented as a
        *               multiple select on the add/edit pages.
        *
        * @param string $field Name of field
        * @param string $input Type of form input to use on add/edit pages
        */
        function setInputType($field, $input)
        {
            if (!empty($this->fields[$field])) {
                $this->fields[$field]['input'] = $input;
            }
        }


        /**
        * Allows not showing of certain fields
        *
        * @param string ... One or more field names to omit from display
        */
        function noDisplay()
        {
            $args = func_get_args();

            foreach ($args as $a) {
                if (isset($this->fields[$a])) {
                    $this->fields[$a]['noDisplay'] = true;
                }
            }
        }


        /**
        * Allows showing, but not editing of certain fields
        *
        * @param string ... One or more field names to omit from add/edit
        */
        function noEdit()
        {
            $args = func_get_args();

            foreach ($args as $a) {
                if (isset($this->fields[$a])) {
                    $this->fields[$a]['noEdit'] = true;
                }
            }
        }


        /**
        * Kind of the "opposite" to noDisplay(), this will set all columns to
        * not be displayed except those specified.
        *
        * @param string ... One or more fields which are to be displayed, all others
        *                   will be hidden
        */
        function onlyDisplay()
        {
            $args = func_get_args();

            foreach ($this->fields as $field => $v) {
                $this->fields[$field]['noDisplay'] = !in_array($field, $args);
            }
        }


        /**
        * Kind of the "opposite" to noEdit(), this will set all columns to
        * not be editable except those specified.
        *
        * @param string ... One or more fields which are to be editable, all others
        *                   will not be
        */
        function onlyEdit()
        {
            $args = func_get_args();

            foreach ($this->fields as $field => $v) {
                $this->fields[$field]['noEdit'] = !in_array($field, $args);
            }
        }


        /**
        * Adds a data validation callback. This is called when an add/edit/copy
        * is submitted. The callback must:
        *  o Accept two arguments: the first is the TableEditor object, and the
        *    second is the data to validate.
        *  o Return the value which is to be inserted into the database. This is
        *    to allow modification of data before insertion. You can of course
        *    return the supplied data untouched if you only wish to validate it.
        *  o Call the addError() method on the TableEditor object if an error
        *    occurs to register the error. The error should naturally be descriptive.
        *
        * @param string   $field    The field name to use with this callback
        * @param callback $callback A valid PHP callback
        */
        function addValidationCallback($field, $callback)
        {
            if (!empty($this->fields[$field]) AND empty($this->fields[$field]['noEdit'])) {
                $this->validationCallbacks[$field][] = $callback;
            }
        }


        /**
        * Adds data filter conditions. Allows only showing of certain rows.
        * Arguments should be valid MySQL WHERE clause for this table.
        *
        * @param string ... One or more SQL WHERE clause conditions.
        */
        function addDataFilter()
        {
            $args = func_get_args();

            foreach ($args as $a) {
                $this->dataFilters[] = $a;
            }
        }


        /**
        * Applys a filter to a field. Purely for display, not during add/edit.
        *
        * @param string   $field    Name of field
        * @param callback $callback PHP callback
        */
        function addDisplayFilter($field, $callback)
        {
            if (is_callable($callback) AND isset($this->fields[$field])) {
                $this->fields[$field]['filters'][] = $callback;

            } else if (is_callable($callback)) {
                $this->errors[] = "Unbekanntes Feld: $field";

            } else {
                $this->errors[] = "Fehler beim Hinzufügen der Callback-Funktion - Die Funktion ist keine gültige Callback-Funktion";
            }
        }


        /**
        * Adds an add callback. Gets called when a row is added. All
        * added data is passed as an array to the callback function.
        *
        * @param callback $callback The callback to be used
        */
        function addAdditionCallback($callback)
        {
            if (is_callable($callback)) {
                $this->addCallbacks[] = $callback;
            } else {
                $this->errors[] = "Fehler beim Hinzufügen des 'add'-Callbacks - Die Funktion ist keine gültige Callback-Funktion";
            }
        }


        /**
        * Adds an edit callback. Gets called when a row is successfully edited. All
        * edited data is passed as an array to the callback function.
        *
        * @param callback $callback The callback to be used
        */
        function addEditCallback($callback)
        {
            if (is_callable($callback)) {
                $this->editCallbacks[] = $callback;
            } else {
                $this->errors[] = "Fehler beim Hinzufügen des 'edit'-Callbacks - Die Funktion ist keine gültige Callback-Funktion";
            }
        }


        /**
        * Adds a copy callback. Gets called when a row is successfully copied. All
        * newly inserted data is passed as an array to the callback function.
        *
        * @param callback $callback The callback to be used
        */
        function addCopyCallback($callback)
        {
            if (is_callable($callback)) {
                $this->copyCallbacks[] = $callback;
            } else {
                $this->errors[] = "Fehler beim Hinzufügen des 'copy'-Callbacks - Die Funktion ist keine gültige Callback-Funktion";
            }
        }


        /**
        * Adds an delete callback. Gets called when a row is deleted. All
        * row data is passed as an array to the callback function.
        *
        * @param callback $callback The callback to be used
        */
        function addDeleteCallback($callback)
        {
            if (is_callable($callback)) {
                $this->deleteCallbacks[] = $callback;
            } else {
                $this->errors[] = "Fehler beim Hinzufügen des 'delete'-Callbacks - Die Funktion ist keine gültige Callback-Funktion";
            }
        }


        /**
        * Works out the structure of the table
        *
        * @param string $table The name of the table to get the structure for
        * @param bool   $usepk Whether to use this tables primary key as the primary
        *                      key for the "row". Set to false when getting structure for
        *                      join tables.
        */
        function getStructure($table, $usepk = true)
        {
            $res = $this->dbGetAll("DESC {$table}");

            // For multi-column pks:
            // list(, $sql) = $this->dbGetRow("SHOW CREATE TABLE {$this->table}", DB_FETCHMODE_NUM);

            $basetypes = 'real|double|float|decimal|numeric|tinyint|smallint|mediumint|int|bigint|date|time|timestamp|datetime|char|varchar|tinytext|text|mediumtext|longtext|enum|set|tinyblob|blob|mediumblob|longblob';
            $extra     = 'unsigned|zerofill|binary|ascii|unicode| ';

            foreach ($res as $row) {

                preg_match("#^($basetypes)(\([^)]+\))?($extra)*$#i", $row['Type'], $matches);

                // What type is the field?
                switch ($matches[1]) {
                    case 'smalltext':
                    case 'mediumtext':
                    case 'text':
                    case 'longtext':
                        $this->addField($row['Field'], 'textarea');
                        break;

                    case 'enum':
                        $type   = substr($row['Type'], 6, -2);
                        $values = array_flip(preg_split("#','#", $type));

                        foreach ($values as $k => $v) {
                            $values[$k] = $k;
                        }

                        $this->addField($row['Field'], 'select', $values);
                        break;

                    case 'date':
                        $this->addField($row['Field'], 'date', null, date('Y-m-d'));
                        break;

                    case 'time':
                        $this->addField($row['Field'], 'time', null, date('H:i:s'));
                        break;

                    case 'datetime':
                        $this->addField($row['Field'], 'datetime', null, date('Y-m-d H:i:s'));
                        break;

                    default:
                        $this->addField($row['Field'], 'text');
                }

                // Look for primary key, if found, order by it by default
                if ($usepk AND $row['Key'] == 'PRI') {
                    $this->pk = $row['Field'];
                    $this->setDefaultOrderby($row['Field']);
                }
            }
        }


        /**
        * Adds a field to the list
        *
        * @param string $name      Name of the field
        * @param string $inputType Type of input to be used on add/edit page
        * @param array  $values    Preset values to display
        */
        function addField($name, $inputType, $values = null, $default = null)
        {
            $this->fields[$name] = array('display' => $name,
                                         'input'   => $inputType,
                                         'values'  => $values,
                                         'default' => $default);
        }


        /**
        * Substitutes actual values with the values in the fields
        * array (if any). Works on a single row of data.
        *
        * @param array &$row Row of data from the table
        */
        function parseResults(&$row)
        {
            foreach ($row as $field => $v) {
                if (!empty($this->fields[$field]['values'])) {

                    $values = $this->fields[$field]['values'];

                    // Must handle bitmasks initially
                    if ($this->fields[$field]['input'] == 'bitmask') {
                        $descs = array();
                        foreach ($values as $bit => $desc) {
                            if ($v & $bit) { // One ampersand only
                                $descs[]  = $desc;
                            }
                        }
                        $row[$field] = implode(', ', $descs);
                    }

                    if (isset($values[$v])) {
                        $row[$field] = $values[$v];
                    }
                }
            }
        }


        /**
        * Applys display filters to a single row of data. Does htmlspecialchars() first.
        *
        * @param array &$results Data from table
        */
        function applyDisplayFilters(&$row)
        {
            foreach ($row as $field => $value) {
                if (!empty($this->fields[$field]['filters'])) {
                    foreach ($this->fields[$field]['filters'] as $f) {
                        $value = call_user_func($f, $value);
                    }

                    $row[$field] = $value;
                }
            }
        }


        /**
        * Deletes the row with the given ID
        *
        * @param mixed $id ID of row to delete
        */
        function deleteRow($ids)
        {
            $ids       = array_map(array(&$this, 'dbQuote'), $ids);
            $ids       = implode(', ', $ids);
            $callbacks = !empty($this->deleteCallbacks);

            /**
            * Data filters
            */
            if (!empty($this->dataFilters)) {
                $filters = implode(' AND ', $this->dataFilters);
            } else {
                $filters = 1;
            }

            // Fetch the data for these row(s) so we can pass it over to any callback functions
            if ($callbacks) {
                $data = $this->dbGetAll("SELECT * FROM {$this->table} WHERE $filters AND {$this->pk} IN($ids)");
            }

            // Do the delete
            $result = (bool)$this->dbQuery("DELETE FROM {$this->table} WHERE $filters AND {$this->pk} IN($ids)");

            // Callbacks
            if ($result AND $callbacks) {
                foreach ($data as $row) {
                    foreach ($this->deleteCallbacks as $c) {
                        call_user_func($c, $row);
                    }
                }
            }

            return $result;
        }


        /**
        * Returns an array of the tables to be specified in the query,
        * and the join clause to use.
        */
        function getQueryTables()
        {
            $tables[]   = $this->table;
            $joinClause = array('1');

            if (!empty($this->joinTables)) {
                foreach ($this->joinTables as $jt) {
                    $tables[] = $jt['table'];
                    $joinClause[] = "{$jt['maincol']} = {$jt['foreigncol']}";
                }
            }

            return array(implode(', ', $tables), implode(' AND ', $joinClause));
        }
        
        
        /**
        * Returns an array of fields which are allowed to be displayed. Always
        * includes the primary key however, whether it's to be displayed or not.
        *
        * @return array Fields used in display
        */
        function getDisplayFields()
        {
            $fields = array();

            foreach ($this->fields as $field => $v) {
                if (empty($v['noDisplay']) OR $field == $this->pk) {
                    $fields[] = $field;
                }
            }
            
            return $fields;
        }


        /**
        * Displays the page
        */
        function display()
        {
            // [DE]: set charset for german "umlaute"
            header("Content-Type: text/html; charset=iso-8859-1");
            
            /**
            * Call a different function if we're editing/copying/adding a row.
            */
            if (isset($_GET['edit'])) {
                if ($this->getConfig('allowEdit')) {
                    $this->handleAddEditCopy($_GET['edit']);
                } else {
                    $this->errors[] = 'Editieren von Zeilen ist nicht erlaubt';
                }

            } else if (isset($_GET['copy'])) {
                if ($this->getConfig('allowCopy')) {
                    $this->handleAddEditCopy($_GET['copy']);