<?php

/**
 * DBForms - database wrapper
 */
if (!defined('THE_DB_FORMS_MODULE')) {
    define('THE_DB_FORMS_MODULE', 'THE_DB_FORMS_MODULE_DEFINED');

    define('__LogSQL', false);

    /**
     * QuoteAndSlashes - quotes SQL query arguments
     * @param mixed $s - string or array of elements to quote
     * @param char $quote - default quote is '
     * @return string - quoted string
     */
    function QuoteAndSlashes($s, $quote = "'") {
        if (is_array($s)) {
            foreach ($s as $k => $v) {
                $s[$k] = $quote . addslashes($v) . $quote;
            }
            return $s;
        } else {
            return $quote . addslashes($s) . $quote;
        }
    }

    /**
     * Build query statement 
     * @param array $Hash field name -> value
     * @param string $Glue 'and' or 'or' default 'and'
     * @param bool $Quote quote values? default - false
     * @return string
     */
    function DBBuildQuery($Hash, $Glue = ' and ', $Quote = false) {
        if ($Hash && is_array($Hash)) {
            $Q = array();
            foreach ($Hash as $f => $v) {
                $Q[] = $f . '=' . ($Quote ? QuoteAndSlashes($v) : $v);
            }
            return implode($Glue, $Q);
        } else {
            return 'true';
        }
    }

    $GLOBALS['__DBFormsLastID'] = 0;
    $GLOBALS['__DBAffectedRows'] = 0;
    $GLOBALS['__ALLSQLs'] = '';
    $GLOBALS['__nALLSQLs'] = 0;
    $GLOBALS['__nCacheSQLs'] = 0;

    $GLOBALS['DynaCont']['DBID'] = false;

    if (version_compare(PHP_VERSION, '5.0.0', '<')) {
        ErrorMessage('PHP 5.0.0 required!');
    }

    /**
     * Initialise database connection. Uses constants: DC_II_DBHOST, DC_II_DBUSER, DC_II_DBPASS, DC_II_DATABASE
     * @return true or die if no connaction
     */
    function DBInit() {
        global $DynaCont;
        global $DB, $DBUSER, $DBPASSWORD, $DBHOST;

        $DBNAME = $DB;
        $DB = @mysqli_connect( $DBHOST, $DBUSER, $DBPASSWORD, $DBNAME);

        if (!$DB) {
            ErrorMessage('DB connection error occured (' . $DBHOST . ':' . $DBUSER . ':' . $DBNAME . ")");
            ErrorMessage('Not connected : ' . mysqli_connect_errno());
            return 0;
        }

        /*
          SET collation_connection = utf8_general_ci;
          SET collation_database = utf8_general_ci;
          SET collation_server = utf8_general_ci;
          SET character_set_client = utf8;
          SET character_set_connection = utf8;
          SET character_set_database = utf8;
          SET character_set_results = utf8;
          SET character_set_server = utf8;
         */
        mysqli_set_charset($DB, 'cp1251');

        /*
          mysqli_query($DB, 'SET collation_connection = utf8_general_ci');
          mysqli_query($DB, 'SET collation_database = utf8_general_ci');
          mysqli_query($DB, 'SET collation_server = utf8_general_ci');
          mysqli_query($DB, 'SET character_set_client = utf8');
          mysqli_query($DB, 'SET character_set_connection = utf8');
          mysqli_query($DB, 'SET character_set_database = utf8');
          mysqli_query($DB, 'SET character_set_results = utf8');
          mysqli_query($DB, 'SET character_set_server = utf8');
         */
        mysqli_query($DB, 'SET collation_connection = cp1251_general_ci');
        mysqli_query($DB, 'SET collation_database = cp1251_general_ci');
        mysqli_query($DB, 'SET collation_server = cp1251_general_ci');
        mysqli_query($DB, 'SET character_set_client = cp1251');
        mysqli_query($DB, 'SET character_set_connection = cp1251');
        mysqli_query($DB, 'SET character_set_database = cp1251');
        mysqli_query($DB, 'SET character_set_results = cp1251');
        mysqli_query($DB, 'SET character_set_server = cp1251');

        if (!mysqli_select_db($DB, $DBNAME)) {
            ErrorMEssage("Can't use " . $DBNAME . " : " . mysqli_error($DB));
            return 0;
        }
        $DynaCont['DB_Error'] = '';
        return $DB;
    }

    /**
     * DBExec - Generic Exec SQL
     *
     * @global DBID $DBID database ID
     * @global int $__DBFormsLastID
     * @global int $__DBAffectedRows
     * @global int $__nCacheSQLs
     * @global string $__ALLSQLs
     * @global int $__nALLSQLs
     * @param DBID $DB
     * @param string $SQL
     * @return Type: resource
     * For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysqli_query returns a resource on success, or false on error.
     * For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysqli_query returns true on success or false on error.
     * The returned result resource should be passed to mysqli_fetch_array, and other functions for dealing with result tables, to access the returned data.
     *
     */
    function DBExec($DB, $SQL, $LogError = true) {
        global $__DBFormsLastID;
        global $__DBAffectedRows;
        global $__nCacheSQLs;
        global $__ALLSQLs, $__nALLSQLs;

        if (__LogSQL === true) {
            $__ALLSQLs .= $SQL . "\n";
            $fp = fopen('/tmp/dc2.db.log', 'a');
            fputs($fp, date('Y-m-d H:i:s:') . $_SERVER['REMOTE_ADDR'] . ':' . $_SERVER['SERVER_NAME'] . ':' . $SQL . "\n");
            fclose($fp);
        }
        $__nALLSQLs++;

        $result = mysqli_query($DB, $SQL);
        $GLOBALS['DynaCont']['DB_LastSQL'] = $SQL;
        if (mysqli_errno($DB) && $LogError) {
            $GLOBALS['DynaCont']['DB_Error'] .= 'DBExec: ' . $SQL . ' : ' . mysqli_error($DB) . "\n";
        }
        $__DBAffectedRows = mysqli_affected_rows($DB);
        $GLOBALS['DynaCont']['DB_AffectedRows'] = $__DBAffectedRows;

        if ($result) {
            @mysqli_free_result($result);
        }
        return $result;
    }

    /**
     * DBInsert - insert new record
     * @global <DBID $DBID
     * @global <type> $__DBFormsLastID
     * @global <type> $__nCacheSQLs
     * @param DBID $DB
     * @param string $TableName - name of the table to insert data into
     * @param hash array $UpdateArray - hash array. indexes - property names, values - property values
     * @return int - false on error or new record ID
     */
    function DBInsert($DB, $TableName, $UpdateArray) {
        global $__DBFormsLastID;
        global $__nCacheSQLs;

        $__DBAffectedRows = -1;

        if (is_array($UpdateArray)) {
            $SQL = 'insert into ' . $TableName;
            $FieldList = ' (';
            $ValueList = ' values (';
            $first = true;

            foreach ($UpdateArray as $FieldName => $FieldValue) {
                if (!$first) {
                    $FieldList .= ',';
                    $ValueList .= ',';
                } else {
                    $first = false;
                }
                $FieldList .= $FieldName;
                $ValueList .= $FieldValue;
            }

            $FieldList .= ')';
            $ValueList .= ')';
            $SQL .= $FieldList . $ValueList;
            DBExec($DB, $SQL, false);
            if (mysqli_errno($DB)) {
                $GLOBALS['DynaCont']['DB_Error'] .= 'DBInsert: ' . $SQL . ' : ' . mysqli_error($DB) . "\n";
            }
            $__DBFormsLastID = mysqli_insert_id($DB);
            //		if( $__DBFormsLastID && __UseSHMDBCache===true ){
            //			cache_cleanup($TableName);
            //		}
            return $__DBFormsLastID;
        }
        return false;
    }

    /**
     * DBUpdate - update table
     * @param DBID $DB - database to process
     * @param string $TableName - table to process
     * @param hash array $UpdateArray - hash array of updates, key - property name, value - propery value
     * @param string $WhereClause - string with where clause
     * @return bool - true on sucess, false - on error, also appends the value of $GLOBALS['DynaCont']['DB_Error'] whith the last mnemonic error message string
     *
     */
    function DBUpdate($DB, $TableName, $UpdateArray, $WhereClause = "") {
        if (is_array($UpdateArray)) {
            $SQL = "update $TableName set ";

            $first = true;

            foreach ($UpdateArray as $FieldName => $FieldValue) {
                if (!$first) {
                    $SQL .= ',';
                } else {
                    $first = false;
                }
                $SQL .= "$FieldName=$FieldValue";
            }

            if ($WhereClause != '') {
                $SQL .= ' where ' . $WhereClause;
            }

            DBExec($DB, $SQL, false);
            if (mysqli_errno($DB)) {
                $GLOBALS['DynaCont']['DB_Error'] .= 'DBUpdate: ' . $SQL . ' : ' . mysqli_error($DB) . "\n";
                return false;
            }
            // if( __UseSHMDBCache===true ){
            //		cache_cleanup($TableName);
            // }
            return true;
        }
        return true;
    }

    /**
     * DBDelete - delete records from table
     * @param DBID $DB
     * @param string $TableName - table name to operate
     * @param string $WhereClause - where clause, default "1==2" ;) false coz!
     */
    function DBDelete($DB, $TableName, $WhereClause = "1=2") {
        $SQL = "delete from $TableName where " . $WhereClause;
        DBExec($DB, $SQL, false);
        if (mysqli_errno($DB)) {
            $GLOBALS['DynaCont']['DB_Error'] .= 'DBDelete: ' . $SQL . ' : ' . mysqli_error($DB) . "\n";
            return false;
        }
        return true;
        // if( __UseSHMDBCache===true ){
        //		cache_cleanup($TableName);
        // }
    }

    function DBGetErrors($CleanupErrors = false) {
        $errors = $GLOBALS['DynaCont']['DB_Error'];
        if ($CleanupErrors) {
            $GLOBALS['DynaCont']['DB_Error'] = '';
        }
        return $errors;
    }

    /**
     * starts SQL transaction
     * @param DBID $DB
     */
    function DBBegin($DB) {
        $SQL = "begin";
        DBExec($DB, $SQL);
    }

    /**
     * commit SQL transaction
     * @param DBID $DB
     */
    function DBCommit($DB) {
        $SQL = "commit";
        DBExec($DB, $SQL);
    }

    /**
     * rollback SQL transaction
     * @param DBID $DB
     */
    function DBRollback($DB) {
        $SQL = "rollback";
        DBExec($DB, $SQL);
    }

    /**
     * check table existance
     * @global DBID $DBID
     * @param type $DB
     * @param string $TableName
     * @return bool 
     */
    function DBTableExists($DB, $TableName) {
        return mysqli_num_rows(mysqli_query($DB, "SHOW TABLES LIKE " . QuoteAndSlashes($TableName)));
    }

    /**
     * update table definition
     * @param type $DB
     * @param string $TableName
     * @param array $TableColumns columns declaration/ keys - column names, values - declaration
     * @param array $TableKeys other definitions: keys e.t.c.
     */
    function DBTableAlter($DB, $TableName, $AlterStatements) {
        if (!isCE()) {
            ErrorLog('Attempt to alter table ' . _hs($TableName), 'Error', __FILE__, __LINE__);
            return false;
        }

        if (!DBTableExists($DB, $TableName)) {
            ErrorLog('Attempt to alter unexisting table ' . _hs($TableName), 'Error', __FILE__, __LINE__);
        } else {
            $SQL = 'ALTER TABLE ' . QuoteAndSlashes($TableName, '`');
            if (!is_array($AlterStatements)) {
                ErrorMessage('Alter statements are missing!');
                return false;
            }
            foreach ($AlterStatements as $key => $value) {
                $SQL .= $value . ',';
            }
            $SQL = rtrim($SQL, ', ');
            $Result = DBExec($DB, $SQL);
            ErrorLog(($Result ? 'Table altered: ' : 'Alter table failure: ' ) . $SQL, $Result ? 'Info' : 'Error', __FILE__, __LINE__);
            return $Result;
        }
        return false;
    }

    /**
     * create or modify table 
     * @param type $DB
     * @param string $TableName 
     * @param array $TableColumns
     * @param array $TableKeys
     * @return bool 
     */
    function DBTableCreate($DB, $TableName, $TableColumns, $TableKeys) {

        if (!isCE()) {
            ErrorLog('Attempt to create table ' . _hs($TableName), 'Error', __FILE__, __LINE__);
            return false;
        }

        if (!DBTableExists($DB, $TableName)) {
            $SQL = 'CREATE TABLE ' . QuoteAndSlashes($TableName, '`') . ' (';
            if (!is_array($TableColumns)) {
                ErrorMessage('Columns declaration is missing!');
                return false;
            }
            foreach ($TableColumns as $key => $value) {
                $SQL .= QuoteAndSlashes($key, '`') . ' ' . $value . ', ';
            }
            if (is_array($TableKeys)) {
                foreach ($TableKeys as $key => $value) {
                    $SQL .= $value . ', ';
                }
            }
            $SQL = rtrim($SQL, ', ');
            $SQL .= ') ENGINE=InnoDB DEFAULT CHARSET=utf8';

            $Result = DBExec($DB, $SQL);
            ErrorLog(($Result ? 'Table created: ' : 'Create table failure: ' ) . $SQL, $Result ? 'Info' : 'Error', __FILE__, __LINE__);
            return $Result;
        } else {
            ErrorLog('Table already exists ' . _hs($TableName), 'Error', __FILE__, __LINE__);
            return false;
        }
    }

    /**
     * Generic GetObject please don't use SQLs like "select table1.*, table2.* ..."
     * coz PHP doesn't support var.prop names :(
     *
     * @global <type> $DBID
     * @global <type> $__ALLSQLs
     * @global <type> $__nALLSQLs
     * @global <type> $__nCacheSQLs
     * @param DBID $DB
     * @param string $SQL
     * @return hash array with property name-> property value pairs, of false in case of error or multiple results
     */
    function GetObject($DB, $SQL) {
        global $__ALLSQLs, $__nALLSQLs;
        global $__nCacheSQLs, $DB;

        //	if( __UseSHMDBCache===true ){
        //		$_smObj = get_value($SQL);
        //		if( $_smObj!=HASHID_MISSING ){
        //			$__nCacheSQLs++;
        //			return $_smObj;
        //		};
        //	}
        if (__LogSQL === true) {
            $__ALLSQLs .= $SQL . "\n";
            $fp = fopen('/tmp/dc2.db.log', 'a');
            fputs($fp, date('Y-m-d H:i:s:') . $_SERVER['REMOTE_ADDR'] . ':' . $_SERVER['SERVER_NAME'] . ':' . $SQL . "\n");
            fclose($fp);
        }
        $__nALLSQLs++;

        $result = mysqli_query($DB, $SQL);
        if (!$result) {
            //		if( __UseSHMDBCache===true ){
            //			store_value($SQL,FALSE,$update_hash_keys=true);
            //		}
            return FALSE;
        }

        if (mysqli_num_rows($result) == 1) {
            $Obj = mysqli_fetch_assoc($result);
            mysqli_free_result($result);
            //		if( __UseSHMDBCache===true ){
            //			store_value($SQL,$Obj,$update_hash_keys=true);
            //		}
            return $Obj;
        } else {
            //		if( __UseSHMDBCache===true ){
            //			store_value($SQL,FALSE,$update_hash_keys=true);
            //		}
            mysqli_free_result($result);
            return FALSE;
        }
    }

    /**
     * GetArray selects multiple records from database
     * @global <type> $DBID
     * @global <type> $__ALLSQLs
     * @global <type> $__nALLSQLs
     * @global <type> $__nCacheSQLs
     * @param <type> $DB
     * @param <type> $SQL
     * @param const $ResultMode MYSQLI_ASSOC
     * @return hasharray of records
     */
    function GetArray($DB, $SQL, $ResultMode = MYSQLI_ASSOC) {
        global $__ALLSQLs, $__nALLSQLs;
        global $__nCacheSQLs;

        //	if( __UseSHMDBCache===true ){
        //		$_smObj = get_value($SQL);
        //		if( $_smObj!=HASHID_MISSING ){
        //			$__nCacheSQLs++;
        //			return $_smObj;
        //		};
        //	}

        if (__LogSQL === true) {
            $__ALLSQLs .= $SQL . "\n";
            $fp = fopen('/tmp/dc2.db.log', 'a');
            fputs($fp, date('Y-m-d H:i:s:') . $_SERVER['REMOTE_ADDR'] . ':' . $_SERVER['SERVER_NAME'] . ':' . $SQL . "\n");
            fclose($fp);
        }
        $__nALLSQLs++;
        $result = mysqli_query($DB, $SQL);

        if (!$result) {
            //		if( __UseSHMDBCache===true ){
            //			store_value($SQL,FALSE,$update_hash_keys=true);
            //		}
            return FALSE;
        }

        if (mysqli_num_rows($result) >= 1) {
            $row = 0;
			if( $ResultMode===MYSQLI_ASSOC){
		        while ($Row = mysqli_fetch_assoc($result)) {
                $Datum[$row++] = $Row;
		        }
			}else{
		        while ($Row = mysqli_fetch_row($result)) {
		            $Datum[$row++] = $Row;
		        }
            }
            //		if( __UseSHMDBCache===true ){
            //			store_value($SQL,$Datum,$update_hash_keys=true);
            //		}
            mysqli_free_result($result);
            return $Datum;
        } else {
            //		if( __UseSHMDBCache===true ){
            //			store_value($SQL,FALSE,$update_hash_keys=true);
            //		}
            mysqli_free_result($result);
            return FALSE;
        }
    }

    function GetSQLHash($DB, $SQL, $Objtype = 'array') {

        $CacheData = GetObject($DB, $TSQL = "SELECT RQ_Answer FROM ICDA_RequiestHash WHERE RQ_SQL_HASH = " . QuoteAndSlashes(md5($SQL)));

        if ($CacheData['RQ_Answer'] != '') {
            DBUpdate(
                    $DB, $Tablename = "ICDA_RequiestHash", array(
                'RQ_Counter' => 'RQ_Counter+1'
                    ), "RQ_SQL_HASH=" . QuoteAndSlashes(md5($SQL))
            );
            return unserialize($CacheData['RQ_Answer']);
        } else {
            if ($Objtype == 'array') {
                $Data = GetArray($DB, $SQL);
            } else {
                $Data = GetObject($DB, $SQL);
            }
            DBInsert(
                    $DB, $Tablename = "ICDA_RequiestHash", array(
                'RQ_SQL_HASH' => QuoteAndSlashes(md5($SQL)),
                'RQ_SQL' => QuoteAndSlashes($SQL),
                'RQ_Answer' => QuoteAndSlashes(serialize($Data)),
                'RQ_Counter' => 1
                    )
            );
            return $Data;
        }
    }

// GetObjectHash($DB, $SQL)

    /**
     * ExportObjectFieldsAsVars create global variables $Object properties or hash indexes
     * @param object or hasharray $Object
     * @return number of new global variables
     */
    function ExportObjectFieldsAsVars($Object) {

        if (!is_array($Object) && !is_object($Object))
            return 0;

        $expvars = 0;

        while (list ($key, $val) = each($Object)) {
            // scan hash array
            if (!is_int($key)) {
                // for each text "VariableName"
                eval("global \$$key;");
                // define this "VariableName" as GLOBAL
                if (is_object($Object)) {
                    eval("\$$key = \$Object->$key;");
                } else {
                    eval("\$$key = \$Object[\"$key\"];");
                }

                $expvars++;
            }
        }

        return $expvars;
    }

}
?>