<?php
/** 
 * By Mikkel Hansen <mikkel@hansen.net>
 * Includes code written by Tyler Hall <tyler@sitening.com>
 * Released under the Creative Commons license
 */

include "Database.class.php";

class Sql2Object 
{
    const   STRDELIM = "{STRDELIM}";
    const   TRIGGER_COLVAL = "{TRIGGER_COLVAL}";

    private $keyName;
    private $tableName;
    private $columns = array();
    private $addedColumns = array();
    private $sqlColumnDefs = array();
    private $addedColumnsChanged = array();
    private $saveTriggers = array();
    private $updatedColumns = array();
    private $limit = 0;
    private $orderBy = '';
    private $isInColumn = '';
    private $isInArray = array();
    private $where = array();
    private $database = null;
    private $delayed = false;

    /**
    * Constructor
    * It is possible to create an instance with an empty $keyName,
    * however updates and deletes will not be possible
    * @param    string  Table name
    * @param    string  Primary key name
    * @param    array   Table columns
    */
    function __construct($tableName, $keyName, $columns)
    {
        $this->tableName = $tableName;
        $this->keyName = $keyName;
        $this->database = $GLOBALS['db'];

        foreach($columns as $key) {
            $this->columns[$key] = null;
        }
    }

    private function __get($key)
    {
        if (isset($this->columns[$key])) {
            return $this->columns[$key];
        }
        trigger_error("Column $key not found", E_USER_NOTICE);
        return null;
    }

    private function __set($key, $value)
    {
        if(array_key_exists($key, $this->columns))
        {
            $this->updatedColumns[] = $key;
            $this->columns[$key] = $value;
            return true;
        }
        return false;
    }

    public function addSaveTrigger($addedColumn, $trigger)
    {
        $this->saveTriggers[$addedColumn] = array('target' => $trigger['target'], 'value' => str_replace(self::STRDELIM, '"', mysql_real_escape_string($trigger['value'])));
    }

    /**
    * Set limit for returned objects
    * @param    int
    */
    public function setLimit($limit)
    {
        $this->limit = (int) $limit;
    }

    /**
    * Get limit for returned objects
    * @return   int
    */
    public function getLimit()
    {
        return $this->limit;
    }

    /**
    * Set whether or not inserts and updates should be low priority 
    * @param    int
    */
    public function setDelayed($delayed)
    {
        $this->delayed = $delayed;
    }

    /**
    * Add a conditional statement to the query
    * @param    string
    */
    public function addWhere($where)
    {
        $this->where[] = str_replace(self::STRDELIM, '"', mysql_real_escape_string($where));
    }

    /**
    * Add a conditional statement to the query
    * @param    string
    */
    public function addColumn($column, $name = '')
    {
        if (!$name) {
            $name = $column;
        }
        $this->sqlColumnDefs[mysql_real_escape_string($name)] = str_replace(self::STRDELIM, '"', mysql_real_escape_string($column));
    }

    /**
    * Set order for the query
    * @param    string
    */
    public function setOrderBy($order)
    {
        $this->orderBy = mysql_real_escape_string($order);
    }

    /**
    * Set x IN (y,z) for the query
    * @param    string
    * @param    array
    */
    public function setIsIn($column, $array)
    {
        $this->isInColumn = mysql_real_escape_string($column);
        $this->isInArray = array_map(array(get_class($this), 'escapeValue'), $array);
    }

    /**
    * Escape an sql value if it is not null or an empty string 
    * @param    string  the value to be escaped
    * @return   string  the escaped value
    */
    private function escapeValue($value)
    {
        if ($value !== null && $value !== '') {
            return mysql_real_escape_string($value);
        }
        return $value;
    }

    /**
    * Clean all row values before they are used in an sql statement
    */
    private function cleanValues()
    {
        $this->columns = array_map(array(get_class($this), 'escapeValue'), $this->columns);
    }

    /**
    * Get an instance of the current class by its' primary key
    * @param    int     primary key
    * @return   object  an instance of the current class
    */
    public function getObjectById($id)
    {
        $addedColsString = '';
        $addedCols = array();
        foreach($this->sqlColumnDefs as $name => $column) {
            if ($column !== null) {
                $addedCols[] = "$column as $name";
            }
        }
        $addedColsString = implode(", ", $addedCols);
        
        $this->database->query("SELECT *" . ($addedColsString ? ", $addedColsString " : '') . " FROM " . $this->tableName . " WHERE " . $this->keyName . " = '" . 
            mysql_real_escape_string($id) . "'");
        if(mysql_num_rows($this->database->result) == 0) {
            return false;
        } else {
            $row = mysql_fetch_array($this->database->result, MYSQL_ASSOC);
            foreach($row as $key => $val) {
                $this->columns[$key] = $val;
            }
        }

        return $this;
    }

    /**
    * Get an instance of the current class that matches this object's properties
    * @return   object  an instance of the current class
    */
    public function findOne()
    {
        $this->cleanValues();

        $pairs = array();
        foreach($this->columns as $key => $val) {
            if ($val !== null) {
                $pairs[] = "$key = '$val'";
            }
        } 
        $cond = implode(" AND ", $pairs);
        $where = '';
        if ($this->where) {
            $where = implode(" AND ", $this->where);
        }

        $addedColsString = '';
        $addedCols = array();
        foreach($this->sqlColumnDefs as $name => $column) {
            if ($column !== null) {
                $addedCols[] = "$column as $name";
            }
        }
        $addedColsString = implode(", ", $addedCols);
        
        $result =   $this->database->query("SELECT " .
                    "*" . ($addedColsString ? ", $addedColsString " : '') .
                    " FROM " . $this->tableName . 
                    ($cond || $where ? " WHERE 1=1 " : '') . 
                    ($cond ? " AND $cond " : '') .
                    ($where ? " AND $where " : '') .
                    " LIMIT 1");

        if ($result && mysql_num_rows($result)) {
            $row = mysql_fetch_array($this->database->result, MYSQL_ASSOC);
            foreach($row as $key => $val) {
                $this->columns[$key] = $val;
            }
            return $this;
        } else {
            return false;
        }
    }

    /**
    * Get an array of instances of the current class that matches this objects properties
    * @return   array   array of instances of the current class
    */
    public function findAll()
    {
        $this->cleanValues();

        $pairs = array();
        foreach($this->columns as $key => $val) {
            if ($val !== null) {
                $pairs[] = "$key = '$val'";
            }
        }
        if ($this->isInColumn && $this->isInArray) {
            $pairs[] = $this->isInColumn . ' IN (' . implode(', ', $this->isInArray) . ')';
        }    
        $cond = implode(' AND ', $pairs);
        $where = '';
        if ($this->where) {
            $where = implode(" AND ", $this->where);
        }
        $addedColsString = '';
        $addedCols = array();
        foreach($this->sqlColumnDefs as $name => $column) {
            if ($column !== null) {
                $addedCols[] = "$column as $name";
            }
        }
        $addedColsString = implode(", ", $addedCols);
        
        $result =   $this->database->query("SELECT " .
                    "*" . ($addedColsString ? ", $addedColsString " : '') .
                    " FROM " . $this->tableName . 
                    ($cond || $where ? " WHERE 1=1 " : '') .
                    ($cond ? " AND $cond " : '') .
                    ($where ? " AND $where " : '') .
                    ($this->orderBy ? ' ORDER BY ' . $this->orderBy : '') .
                    ($this->limit ? ' LIMIT ' . $this->limit : ''));

        if ($result) {
            $objects = array();
            $className = get_class($this);
            while($row = mysql_fetch_array($this->database->result, MYSQL_ASSOC)) {
                $object = new $className;
                foreach($row as $key => $val) {
                    $object->columns[$key] = $val;
                }
                $objects[] = $object;
            }
            return $objects;
        } else {
            return false;
        }
    }

    /**
    * Count how many instances of the current class that matches this objects properties
    * @return   int
    */
    public function countAll()
    {
        $this->cleanValues();

        $pairs = array();
        foreach($this->columns as $key => $val) {
            if ($val !== null) {
                $pairs[] = "$key = '$val'";
            }
        }    
        $cond = implode(' AND ', $pairs);
        $where = '';
        if ($this->where) {
            $where = implode(" AND ", $this->where);
        }
        $result = $this->database->query('SELECT COUNT(*) AS cnt FROM ' . $this->tableName . 
            ($cond || $where ? " WHERE 1=1 " : '') .
            ($cond ? " AND $cond " : '') .
            ($where ? " AND $where " : '') .
            ' LIMIT 1');

        if ($result) {
            while($row = mysql_fetch_object($this->database->result)) {
                return $row->cnt;
            }
            return 0;
        } else {
            return 0;
        }
    }

    /**
    * Save the current object to its' database row, if it has a primary key, otherwise save it to a new row
    * @return   bool  success of save
    */
    public function save()
    {
        $this->cleanValues();
        
        if (isset($this->columns[$this->keyName]) && $this->columns[$this->keyName] != '') {
            // Perform an update by primary key
            $idValue = $this->columns[$this->keyName];
            unset($this->columns[$this->keyName]);
            
            $updateColumns = join(", ", array_keys($this->columns));
            $updateValues = "'" . join("', '", $this->columns) . "'"; 
            $pairs = array();
            foreach($this->columns as $key => $val) {
                if (in_array($key, $this->updatedColumns)) {
                    if (!in_array($key, array_keys($this->sqlColumnDefs))) { // Do not attempt to save custom columns
                        $pairs[] = "$key = '$val'"; 
                    }
                }
            }
            // Check for triggers
            foreach($this->saveTriggers as $column => $action) {
                if (!empty($this->columns[$column]) && in_array($column, $this->updatedColumns)) {
                    $pairs[] = $action['target'] . ' = ' . str_replace(self::TRIGGER_COLVAL, $this->columns[$column], $action['value']);
                }
            }
            $updates = implode(", ", $pairs);
            $result = $this->database->query("UPDATE " . ($this->delayed ? 'LOW_PRIORITY ' : '') . $this->tableName . " SET $updates WHERE " . 
                $this->keyName . " = '" . $idValue . "'");
            $this->columns[$this->keyName] = $idValue;
        } else {
            // Perform an insert
            $tempCols = array_diff_key($this->columns, $this->sqlColumnDefs); // Don't insert user created columns
            $insertColumns = join(", ", array_keys($tempCols));
            $insertValues = "'" . join("', '", $tempCols) . "'";
            

            $result = $this->database->query("INSERT " . ($this->delayed ? 'DELAYED' : '') . " INTO " . $this->tableName . " ($insertColumns) VALUES ($insertValues)");
            $this->columns[$this->keyName] = mysql_insert_id($this->database->db);
            if ($result) {
                // Check for triggers
                $pairs = array();
                foreach($this->saveTriggers as $column => $action) {
                    if (!empty($this->sqlColumnDefs[$column])) {
                        $pairs[] = $action['target'] . ' = ' . str_replace(self::TRIGGER_COLVAL, $this->columns[$column], $action['value']);
                    }
                }
                $updates = implode(", ", $pairs);
                if ($pairs) {
                    $result = $this->database->query("UPDATE " . $this->tableName . " SET $updates WHERE " . $this->keyName . " = '" . $this->columns[$this->keyName] . "'");
                }            
            }
        }
        $this->updatedColumns = array();

        return $result;
    }

    /**
    * Delete the current instance by its' primary key
    * @return   object  an instance of the current class
    */
    public function delete()
    {
        $result = false;
        if (!empty($this->columns[$this->keyName])) {    
            $result = $this->database->query("DELETE FROM " . $this->tableName . " WHERE " . $this->keyName . " = '" . (int) $this->columns[$this->keyName] . "'");
        }
        return $result;
    }

    /**
    * Get the last error generated by the sql server
    * @return   string  the error string
    */
    public function getLastError()
    {
        return $this->database->getLastError();
    }
}
?>