<?php
/*
*    Copyright 2008,2009,2010 Maarch
*
*  This file is part of Maarch Framework.
*
*   Maarch Framework 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 3 of the License, or
*   (at your option) any later version.
*
*   Maarch Framework 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 Maarch Framework.  If not, see <http://www.gnu.org/licenses/>.
*/

/**
* @brief   Embedded sql functions (connection, database selection, query ).
*  Allow to changes the databases server
*
* @file
* @author  Claire Figueras <dev@maarch.org>
* @author  Laurent Giovannoni  <dev@maarch.org>
* @author  Loic Vinet <dev@maarch.org>
* @date $date$
* @version $Revision$
* @ingroup core
*/

/**
* @brief   Embedded sql functions (connection, database selection, query ). 
* Allow to changes the databases server
*
* <ul>
*  <li>Compatibility with the following databases : Mysql, Postgres, 
*      Mssql Server, Oracle
*  <li>Connection to the Maarch database</li>
* <li>Execution of SQL queries to the Maarch database</li>
* <li>Getting results of SQL queries</li>
* <li>Managing the database errors</li>
* </ul>
* @ingroup core
*/
class dbquery
{
    /**
    * Debug mode activation.
    * Integer 1,0
         */
    private $_debug;             // debug mode

    /**
    * Debug query (debug mode). String
    */
    private $_debugQuery;       // request for the debug mode

    /**
    * SQL link identifier
    * Integer
    */
    private $_sqlLink;          // sql link identifier


    /**
    * To know where the script was stopped
    *  Integer
    */
    private $_sqlError;    // to know where the script was stopped

    /**
    * SQL query
    * String
         */
    public $query;              // query

    /**
    * Number of queries made with this identifier
         * Integer
         */
    private $_nbQueries;          // number of queries made with this identifier

    /**
    * Sent query result
         * String
         */
    private $_result;            // sent query result

    /**
    * OCI query identifier
    * @access private
    * @var integer
    */
    private $_statement  ;       // OCI query identifier

    private $_server;
    private $_port;
    private $_user;
    private $_password;
    private $_database;
    private $_databasetype;
    private $_workspace;

    private $_errorMsg;
    public function __construct()
    {
        $args = func_get_args();
        if (count($args) < 1) {
            if (isset($_SESSION['config']['databaseserver'])) {
                $this->_server = $_SESSION['config']['databaseserver'];
            }
            if (isset($_SESSION['config']['databaseserverport'])) {
                $this->_port = $_SESSION['config']['databaseserverport'];
            }
            if (isset($_SESSION['config']['databaseuser'])) {
                $this->_user = $_SESSION['config']['databaseuser'];
            }
            if (isset($_SESSION['config']['databasepassword'])) {
                $this->_password = $_SESSION['config']['databasepassword'];
            }
            if (isset($_SESSION['config']['databasename'])) {
                $this->_database = $_SESSION['config']['databasename'];
            }
            //$this->workspace = $_SESSION['config']['databaseworkspace'];
            if (isset($_SESSION['config']['databasetype'])) {
                $this->_databasetype = $_SESSION['config']['databasetype'];
            }
        } else {
            $errorArgs = true;
            if (is_array($args[0])) {
                if (! isset($args[0]['server'])) {
                    $this->_server = '127.0.0.1';
                } else {
                    $this->_server = $args[0]['server'];
                }
                if (! isset($args[0]['databasetype'])) {
                    $this->_databasetype = 'MYSQL';
                } else {
                    $this->_databasetype = $args[0]['databasetype'];
                }
                if (! isset($args[0]['port'])) {
                    $this->_port = '3304';
                } else {
                    $this->_port = $args[0]['port'];
                }
                if (! isset($args[0]['user'])) {
                    $this->_user = 'root';
                } else {
                    $this->_user = $args[0]['user'];
                }
                if (! isset($args[0]['workspace'])) {
                    $this->_workspace = 'public'; 
                    $this->_workspace = $args[0]['workspace'];
                }
                if (! isset($args[0]['pass'])) {
                    $this->_password = '';
                } else {
                    $this->_password = $args[0]['pass'];
                }
                if (! isset($args[0]['base'])) {
                    $this->_database = '';
                } else {
                    $this->_database = $args[0]['base'];
                }
                $errorArgs = false;
            } else if (is_string($args[0]) && file_exists($args[0])) {
                $xmlconfig = simplexml_load_file($args[0]);
                $config = $xmlconfig->CONFIG_BASE;
                $this->_server = (string) $config->databaseserver;
                $this->_port = (string) $config->databaseserverport;
                $this->_databasetype = (string) $config->databasetype;
                $this->_database = (string) $config->databasename;
                $this->_user = (string) $config->databaseuser;
                $this->_password = (string) $config->databasepassword;
                $this->workspace = (string) $config->databaseworkspace;
                $errorArgs = false;
            }
            if ($errorArgs) {
            	$this->_sqlError = 5; // error constructor
           		$this->error();
            	return false;
            }
        }
    }
    /**
    * Connects to the database
    *
    */
    public function connect()
    {
        $this->_debug = 0;
        $this->_nbQueries = 0;
        if ($this->_databasetype == 'MYSQL') {
            $this->_sqlLink = @mysqli_connect(
                $this->_server, $this->_user, $this->_password,
                $this->_database, $this->_port
            );
        } else if ($this->_databasetype == 'SQLSERVER') {
            $this->_sqlLink = @mssql_connect(
                $this->_server, $this->_user, $this->_password
            );
        } else if ($this->_databasetype == 'POSTGRESQL') {
            $this->_sqlLink = @pg_connect(
                'host=' . $this->_server . ' user=' . $this->_user .' password='
                . $this->_password . ' dbname=' . $this->_database . ' port='
                . $this->_port, PGSQL_CONNECT_FORCE_NEW
            );
        } else if ($this->_databasetype == 'ORACLE') {
            if ($this->_server <> '') {
                $this->_sqlLink = oci_connect(
                    $this->_user, $this->_password, '//' . $this->_server . '/'
                    . $this->_database, 'UTF8'
                );
            } else {
                $this->_sqlLink = oci_connect(
                    $this->_user, $this->_password, $this->_database, 'UTF8'
                );
            }
            // ALTER SESSIONS MUST BE MANAGED BY TRIGGERS DIRECTLY IN THE DB
            //$this->query("alter session set nls_date_format='dd-mm-yyyy'");
        } else {
            $this->_sqlLink = false;
        }

        if (! $this->_sqlLink) {
            $this->_sqlError = 1; // error connexion
            $this->error();
            return false;
        } else {
            if ($this->_databasetype <> 'POSTGRESQL'
                && $this->_databasetype <> 'MYSQL'
                && $this->_databasetype <> 'ORACLE'
            ) {
                if ($this->select_db() == false) {
                    return false;
                }
            }
        }
        return true;
    }

    /**
    * Database selection (only for SQLSERVER)
    *
    */
    public function select_db()
    {
        if ($this->_databasetype == 'SQLSERVER') {
            if (! @mssql_select_db($this->_database)) {
                $this->_sqlError = 2;
                $this->error();
                return false;
            }
        }
        return true;
    }

    /**
    * Test if the specified column exists in the database
    *
    * @param  $table : Name of searched table
    * @param  $field : Name of searched field in table
    *  ==Return : true is field is founed, false is not
    */
    public function test_column($table, $field)
    {
        if ($this->_databasetype == 'SQLSERVER') {
            return true;
        } else if ($this->_databasetype == 'MYSQL') {
            return true;
        } else if ($this->_databasetype == 'POSTGRESQL') {
            $this->connect();
            $this->query(
                "select column_name from information_schema.columns where "
                . "table_name = '" . $table . "' and column_name = '" . $field
                . "'"
            );
            $res = $this->nb_result();
            $this->disconnect();
            if ($res > 0) {
                return true;
            } else {
                return false;
            }
        } else if ($this->_databasetype == 'ORACLE') {
            $this->connect();
            $this->query(
                "SELECT * from USER_TAB_COLUMNS where TABLE_NAME = '" . $table
                . "' AND COLUMN_NAME = '" . $field . "'"
            );
            $res = $this->nb_result();
            $this->disconnect();
            if ($res > 0) {
                return true;
            } else {
                return false;
            }
        }
    }

    /**
    * Execution the sql query
    *
    * @param  $sqlQuery string SQL query
    * @param  $catchError bool In case of error, catch the error or not, 
    * 		  if not catched, the error is displayed (false by default)
    */
    public function query($sqlQuery, $catchError=false)
    {
        // query
        $this->_debugQuery = $sqlQuery;
        if ($this->_databasetype == 'MYSQL') {
            $this->query = @mysqli_query($this->_sqlLink, $sqlQuery);
        } else if ($this->_databasetype == 'SQLSERVER') {
            $this->query = @mssql_query($sqlQuery);
        } else if ($this->_databasetype == 'POSTGRESQL') {
            $this->query = @pg_query($sqlQuery);
        } else if ($this->_databasetype == 'ORACLE') {
            $this->statement = @oci_parse($this->_sqlLink, $sqlQuery);
            if ($this->statement == false) {
                if ($catchError) {
                    return false;
                }
                $this->_sqlError = 6;
                $this->error();
                return false;
            } else {
                if (! @oci_execute($this->statement)) {
                    if ($catchError) {
                        return false;
                    }
                    //$error = oci_error($this->statement);
                    $this->_sqlError = 3;
                    $this->error();
                    return false;
                }
            }
        } else {
            $this->query = false;
        }
        if ((($this->_databasetype == 'ORACLE' && $this->statement == false)
            || ($this->_databasetype <> 'ORACLE' && $this->query == false))
                && ! $catchError
        ) {
            $this->_sqlError = 3;
            $this->error();
            return false;
        }
        $this->_nbQueries ++;
        if ($this->_databasetype == 'ORACLE') {
            return $this->_statement;
        } else {
            return $this->query;
        }
    }

    /**
    * Returns the query results in an object
    *
    * @return Object
    */
    public function fetch_object()
    {
        if ($this->_databasetype == 'MYSQL') {
            return @mysqli_fetch_object($this->query);
        } else if ($this->_databasetype == 'SQLSERVER') {
            return @mssql_fetch_object($this->query);
        } else if ($this->_databasetype == 'POSTGRESQL') {
            return @pg_fetch_object($this->query);
        } else if ($this->_databasetype == 'ORACLE') {
            $myObject = @oci_fetch_object($this->statement);
            $myLowerObject = false;
            if (isset($myObject) && ! empty($myObject)) {
                foreach ($myObject as $key => $value) {
                    if (oci_field_type($this->statement, $key) == 'CLOB') {
                        $newKey = strtolower($key);
                        $myBlob = $myObject->$key;
                        if (isset($myBlob)) {
                            $myLowerObject->$newKey = $myBlob->read(
                                $myBlob->size()
                            );
                        }
                    } else {
                        $newKey = strtolower($key);
                        $myLowerObject->$newKey = $myObject->$key;
                    }
                }
                return $myLowerObject;
            } else {
                return false;
            }
        } else {

        }
        return false;
    }

    /**
    * Returns the query results in an array
    *
    * @return array
    */
    public function fetch_array()
    {
        if ($this->_databasetype == 'MYSQL') {
            return @mysqli_fetch_array($this->query);
        } else if ($this->_databasetype == 'SQLSERVER') {
            return @mssql_fetch_array($this->query);
        } else if ($this->_databasetype == 'POSTGRESQL') {
            return @pg_fetch_array($this->query);
        } elseif ($this->_databasetype == 'ORACLE') {
            $tmpStatement = array();
            $tmpStatement = @oci_fetch_array($this->statement);

            if (is_array($tmpStatement)) {
                //$this->show_array($tmpStatement);
                foreach (array_keys($tmpStatement) as $key) {
                    if (! is_numeric($key)
                        && oci_field_type($this->statement, $key) == 'CLOB'
                    ) {
                        if (isset($tmpStatement[$key])) {
                            $tmp = $tmpStatement[$key];
                            $tmpStatement[$key] = $tmp->read($tmp->size());
                        }
                    }
                }
                return array_change_key_case($tmpStatement, CASE_LOWER);
            }
        } else {

        }
        return false;
    }

    /**
    * Returns the query results in a row
    *
    * @return array
    */
    public function fetch_row()
    {
        if ($this->_databasetype == 'MYSQL') {
            return @mysqli_fetch_row($this->query);
        } else if ($this->_databasetype == 'SQLSERVER') {
            return @mssql_fetch_row($this->query);
        } else if ($this->_databasetype == 'POSTGRESQL') {
            return @pg_fetch_row($this->query);
        } else if ($_SESSION['config']['databasetype'] == 'ORACLE') {
            return @oci_fetch_row($this->statement);
        } else {

        }
        return false;
    }

    /**
    * Returns the number of results for the current query
    *
    * @return integer Results number
    */
    public function nb_result()
    {
        if ($this->_databasetype == 'MYSQL') {
            return @mysqli_num_rows($this->query);
        } else if ($this->_databasetype == 'SQLSERVER') {
            return @mssql_num_rows($this->query);
        } else if ($this->_databasetype == 'POSTGRESQL') {
            return @pg_num_rows($this->query);
        } else if ($this->_databasetype == 'ORACLE') {
            // 2eme version Maarch
            $db = new dbquery();
            $db->connect();
            $db->query("SELECT COUNT(*) FROM  (" . $this->_debugQuery . ")");
            $row = $db->fetch_array();
            return $row[0];
        } else {

        }
        return false;
    }

    /**
    * Closes database connexion
    *
    */
    public function disconnect()
    {
        if ($this->_databasetype == 'MYSQL') {
            if (! mysqli_close($this->_sqlLink)) {
                $this->_sqlError = 4;
                $this->error();
                return false;
            }
        } else if ($this->_databasetype == 'SQLSERVER') {
            if (! mssql_close($this->_sqlLink)) {
                $this->_sqlError = 4;
                $this->error();
                return false;
            }
        } else if ($this->_databasetype == 'POSTGRESQL') {
            if (get_resource_type($this->_sqlLink) == 'pgsql link') {
                if (! pg_close($this->_sqlLink)) {
                    $this->_sqlError = 4;
                    $this->error();
                    return false;
                }
            }
        } else if ($this->_databasetype == 'ORACLE') {
            if (! oci_close($this->_sqlLink)) {
                $this->_sqlError = 4;
                $this->error();
                return false;
            }
        } else {

        }
    }

    /**
    * SQL Error management
    *
    */
    private function error()
    {
        // Connexion error
        if ($this->_sqlError == 1) {
            // Shows the connexion data (server, port, user, pass)
            $this->errorMsg = 'Database connection error : ('
                            . $this->_databasetype . ', ' . $this->_server . ':'
                            . $this->_port . ', Base : '. $this->_database 
                            . ', ' . $this->_user . '/' . $this->_password 
                            . ')';
        } else if ($this->_sqlError == 2) {  // Selection error
            $this->errorMsg = 'Database selection error :' . $this->_database;
        } else if ($this->_sqlError == 3) { // Query error
            $this->errorMsg = 'Query Error : ';
            if ($this->_databasetype == 'MYSQL') {
                $this->errorMsg .= 'Error n°' . @mysqli_errno($this->_sqlLink)
                . ' ' . @mysqli_error($this->_sqlLink);
            } else if ($this->_databasetype == 'POSTGRESQL') {
                $this->errorMsg .= @pg_send_query(
                    $this->_sqlLink, $this->_debugQuery
                );
                $res = @pg_get_result($this->_sqlLink);
                $this->errorMsg .= @pg_result_error($res);
            } else if ($this->_databasetype == 'SQLSERVER') {
                $this->errorMsg .= @mssql_get_last_message();
            } else if ($this->_databasetype == 'ORACLE') {
                $res = @oci_error($this->statement);
                 $this->errorMsg .= $res['message'];
            }
            $this->errorMsg .= ' Query : ' . $this->_debugQuery;

        } else if ($this->_sqlError == 4) {  // Closing connexion error
            $this->errorMsg = 'Error during the connexion closing';
        } else if ($this->_sqlError == 5) {  // Constructor error
            $this->errorMsg = 'Dbquery construct error';
        } else if ($this->_sqlError == 6) {
            // QUery Preparation error (ORACLE)
            $this->errorMsg = 'Query preparation error';
        }
    }

    /**
    * Shows the query for debug
    *
    */
    public function show()
    {
        echo _LAST_QUERY . " : <textarea cols=\"70\" rows=\"10\">"
            . $this->_debugQuery . "</textarea>";
    }

    /**
    * Returns the last insert id for the current query in case  of 
    * autoincrement id
    *
    * @return integer  last increment id
    */
    public function last_insert_id($sequenceName='')
    {
        if ($this->_databasetype == 'MYSQL') {
            return @mysqli_insert_id($this->_sqlLink);
        } else if ($this->_databasetype == 'POSTGRESQL') {
            $this->query = @pg_query(
                "select currval('" . $sequenceName . "') as lastinsertid "
            );
            $line = @pg_fetch_object($this->query);
            return $line->lastinsertid;
        } else if ($this->_databasetype == 'SQLSERVER') {

        } else if ($this->_databasetype == 'ORACLE') {

        } else {

        }
    }

    public function getErrorMsg()
    {
        return $this->errorMsg;
    }
}