<?php
//   This file is a part of CodingTeam. Take a look at <http://codingteam.org>.
//   Copyright © 2007-2014 Erwan Briand <erwan@codingteam.net>
//
//   This program is free software: you can redistribute it and/or modify it
//   under the terms of the GNU Affero General Public License as published by
//   the Free Software Foundation, version 3 only.
//
//   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 Affero General Public
//   License for more details.
//
//   You should have received a copy of the GNU Affero General Public License
//   along with this program. If not, see <http://www.gnu.org/licenses/>.

/**
 * @file
 * This file contains the Database class
 *
 * Connect to MySQL or PostgreSQL.
 * Functions for insert, update, delete, select.
 */


/**
 * Database class
 */
class Database
{
    private $type, $hostname, $database, $username, $password, $error;

    function __construct($type, $hostname, $database, $username,
                         $password, $error=FALSE)
    {
        $this->error = $error;
        $this->time = 0;

        // Trying to connect to the database
        try
        {
            switch ($type)
            {
                case 'mysql':
                    $this->dbclass = new PDO('mysql:host='.$hostname.
                                             ';dbname='.$database, $username,
                                             $password);
                break;

                case 'pgsql':
                    $this->dbclass = new PDO('pgsql:host='.$hostname.
                                             ';dbname='.$database, $username,
                                             $password);
                break;

                default:
                    exit('Error: database type not supported by CodingTeam.');
            }
        }
        catch (PDOException $e)
        {
            if ($error)
                $this->error->displayError('The link to the database could '.
                                           'not be etablished. <br />'.
                                           $e->getMessage(), 2);
        }
    }


    /**
     * Select
     *
     * Treatment for a SELECT request.
     * @param $table
     *   The table in the database.
     * @param $where
     *   An array like field=>value (can be 'field'=>array('value', 'LIKE')).
     * @param $what (optional)
     *   The list of wished field's value.
     * @param $optional (optional)
     *   SQL syntax.
     * @param $operator (optional)
     *   A string for the SQL operator (AND or OR).
     * @return
     *   The fetched results.
     */
    public function select($_table, $where, $what='*', $optional='',
                           $operator='AND')
    {
        // Trying to select data
        try
        {
            // Start execution time
            $time = microtime(true);

            // Handle requests in more than one table
            if (strstr($_table, ', '))
            {
                $table_ = explode(', ', $_table);
                $table = '';

                for($i=0; $i<count($table_); $i++)
                    $table .= '`'.$table_[$i].'`'.
                              (((count($table_) - 1) > $i) ? ', ' : '');

                $o = '';
            }
            else
            {
                $o = '`';
                $table = '`'.$_table.'`';
            }

            $sql = 'SELECT '.$what.' '.((empty($_table)) ? '' : 'FROM '.$table.' ');
            $paramArray = array();
            if(is_array($where))
            {
                $sql .= 'WHERE (';

                foreach ($where as $key => $value)
                {
                    $pre = (count($paramArray) ? ' '.$operator.' ' : '');

                    switch ($value)
                    {
                        case (!is_array($value)):
                        case (isset($value[1]) && $value[1] == 'LIKE'):
                        case (isset($value[1]) && $value[1] == 'NOT LIKE'):
                        case (isset($value[1]) && $value[1] == 'FIELD'):
                        case (isset($value[1]) && $value[1] == '!'):
                        case (isset($value[2]) && $value[2] == 'BETWEEN'):

                            $ret = $this->select_where_request($key, $value,
                                                               $pre.$o.$key.$o,
                                                               $o);
                            $sql .= $ret['sql'];
                            $paramArray = array_merge($paramArray,
                                                      $ret['paramArray']);
                        break;

                        case (is_array($value)):

                            $i = 0;
                            if ($pre == '')
                                $sql .= '(';

                            foreach ($value as $field)
                            {
                                $ret = $this->select_where_request($key, $field,
                                                                   $o.$key.$o,
                                                                   $o);

                                if ($i)
                                    $sql .= ' OR ';
                                elseif ($pre != '')
                                    $sql .= $pre.'(';

                                $sql .= $ret['sql'];
                                $paramArray = array_merge($paramArray,
                                                          $ret['paramArray']);
                                $i++;
                            }
                            $sql .= ')';
                        break;
                    }
                }

                $sql .= ')';
            }
            else
            {
                /* Warning: use this with care!
                 *
                 * This should be used only if the select/where API can not
                 * handle the work you want to do. Nothing is escaped and your
                 * string is just concatened into the SQL request.
                 */
                $sql .= $where;
            }

            $rs = $this->dbclass->prepare($sql.' '.$optional);

            if($rs->execute($paramArray))
            {
                $this->time += microtime(true) - $time;
                return $rs->fetchAll(PDO::FETCH_ASSOC);
            }

            $err = $rs->errorInfo();
            if ($err)
                $this->error->displayError('An error occured.<br />'.
                                           $err[2], 1);
        }
        catch (PDOException $e)
        {
            $this->error->displayError('The request could not be '.
                                       'finalized.<br />'.$e->getMessage(),
                                       1);
        }
    }


    private function select_where_request($key, $value, $field, $o)
    {
        $paramArray = array();
        $sql = '';

        if (!is_array($value))
        {
            // key = value
            $sql .= $field.' = ?';
            array_push($paramArray, $value);
        }
        elseif ($value[1] == 'LIKE' || $value[1] == 'NOT LIKE')
        {
            // key LIKE %value%
            $sql .= $field.' '.$value[1].' ?';
            array_push($paramArray, '%'.$value[0].'%');
        }
        elseif ($value[1] == 'FIELD')
        {
            // key = value (Warning: value is not escaped)
            $sql .= $field.' = '.$o.$value[0].$o;
        }
        elseif ($value[1] == '!')
        {
            // key != value
            $sql .= $field.' != ?';
            array_push($paramArray, $value[0]);
        }
        elseif ($value[2] == 'BETWEEN')
        {
            // key BETWEEN first_value and second_value
            $sql .= $field.' BETWEEN ? and ?';
            array_push($paramArray, $value[0]);
            array_push($paramArray, $value[1]);
        }

        return array('sql'        => $sql,
                     'paramArray' => $paramArray);
    }


    /**
     * Insert
     *
     * Treatment for a INSERT database.
     * @param $table
     *   The table in the database.
     * @param $data
     *   The datas in an array like 'field' => 'value'.
     * @return
     *   The database id of this record.
     */
    public function insert($table, $data)
    {
        if (!is_array($data))
            exit('Error: datas must be in an array.');

        // Trying to insert data
        try
        {
            // Start execution time
            $time = microtime(true);

            $query = 'INSERT INTO `'.$table.'` (`'.join('`, `',
                      array_keys($data)).'`) VALUES (';

            for ($i=0; $i < count($data); $i++)
            {
                $query .= '?';
                if ($i != count($data)-1)
                    $query .= ', ';
            }
            $query .=')';
            $rs = $this->dbclass->prepare($query);

            if($rs->execute(array_values($data)))
            {
                $this->time += microtime(true) - $time;
                return $this->dbclass->lastInsertId();
            }
            else
            {
                $err = $rs->errorInfo();

                if ($err)
                    $this->error->displayError('An error occured. <br />'.
                                               $err[2], 1);
            }

        }
        catch (PDOException $e)
        {
            $this->error->displayError('The request could not be '.
                                       'finalized.<br />'.$e->getMessage(),
                                       1);
        }
    }


    /**
     * Update
     *
     * Treatment for an UPDATE request.
     * @param $table
     *   The table in the database.
     * @param $data
     *   The new datas in an array like 'field' => 'value'.
     * @param $where
     *   An array like 'field' => 'value'.
     */
    public function update($table, $data, $where)
    {
        // Trying to update data
        try
        {
            // Start execution time
            $time = microtime(true);

            $paramArray = array();
            $sql_query = "UPDATE `".$table."` SET ";

            $new_values = array();
            $i = 0;
            foreach ($data as $key => $value)
            {
                $sql_query .= '`'.$key.'` = ?';
                if ($i != count($data)-1)
                    $sql_query .= ', ';

                array_push($paramArray, $value);
                $i ++;
            }
            $sql_query .= implode(',', $new_values);

            if(is_array($where))
            {
                $sql_query .= ' WHERE ';
                $i = 0;
                foreach($where as $key => $value)
                {
                    $pre = (($i) ? ' AND ' : '');

                    $sql_query .= $pre.'`'.$key.'` = ?';
                    array_push($paramArray, $value);

                    $i ++;
                }
            }

            $rs = $this->dbclass->prepare($sql_query);

            if($rs->execute($paramArray))
            {
                $this->time += microtime(true) - $time;
                return TRUE;
            }
        }
        catch (PDOException $e)
        {
            $this->error->displayError('The request could not be '.
                                       'finalized.<br />'.$e->getMessage(),
                                       1);
        }
    }


    /**
     * Delete
     *
     * Treatment for a DELETE request.
     * @param $table
     *   The table in the database.
     * @param $where
     *   An array like 'field' => 'value'.
     */
    public function delete($table, $where)
    {
        // Trying to delete data
        try
        {
            // Start execution time
            $time = microtime(true);

            $sql = 'DELETE FROM `'.$table.'` WHERE ';
            $paramArray = array();
            if(is_array($where))
            {
                foreach($where as $key => $value)
                {
                    $pre = (count($paramArray) ? ' AND ' : '');

                    $sql .= $pre.'`'.$key.'` = ?';
                    array_push($paramArray, $value);
                }
            }

            $rs = $this->dbclass->prepare($sql);

            if($rs->execute($paramArray))
            {
                $this->time += microtime(true) - $time;
                return TRUE;
            }

            $err = $rs->errorInfo();
            if ($error)
                $this->error->displayError('An error occured.<br />'.$err[2], 1);
        }
        catch (PDOException $e)
        {
            $this->error->displayError('The request could not be '.
                                       'finalized.<br />'.$e->getMessage(),
                                       1);
        }
    }


    /**
     * Clean entry
     *
     * Remove dangerous characters from the string.
     * @param $postval
     *   The user's data.
     * @param $line
     *   TRUE if $postval contains only 1 line.
     * @return
     *   The safe entry.
     */
    public function cleanentry($postval, $line)
    {
        if ($line)
            $postval = preg_replace('[\x00-\x1f]', '', $postval);
        else
        {
            $postval = preg_replace('[\x00-\x09\x0b\x0c\x0e\x1f]', '', $postval);
            $postval = preg_replace('(\x0d\x0a)', "\x0a", $postval);
            $postval = preg_replace('(\x0d)', "\x0a", $postval);
        }
        return $postval;
    }


    /**
     * Get execution time
     *
     * Get the aproximatical time of SQL execution.
     * @return
     *   The time.
     */
    public function getExecutionTime()
    {
        return round($this->time, 2);
    }
}
?>