<?php

class DatabaseConnection {
    
    function executeStatement($stmt){
        global $db, $SysPrefs, $sql_queries, $Ajax,	$db_connections, $db_last_inserted_id;

	// set current db prefix
	$comp = isset($_SESSION["wa_current_user"]->cur_con) ? $_SESSION["wa_current_user"]->cur_con : 0;
	$cur_prefix = $db_connections[$comp]['tbpref'];
	$sql = str_replace(TB_PREF, $cur_prefix, $sql);

	if ($SysPrefs->show_sql)
	{
		$Ajax->activate('footer_debug');
		$sql_queries .= "<pre>$sql</pre>\n<hr>";
	}
        
        if ($stmt->execute()) {
            return $stmt;
        } else {
            echo "Error in : " . $query;
            trigger_error('Statement failed : ' . $stmt->error, E_USER_ERROR);
            return FALSE;
        }
        
        if($SysPrefs->sql_trail) {
		$db_last_inserted_id = $stmt->insert_id;	// preserve in case trail insert is done
		if ($SysPrefs->select_trail || (strstr($sql, 'SELECT') === false)) {
			mysqli_query($db, "INSERT INTO ".$cur_prefix."sql_trail
				(`sql`, `result`, `msg`)
				VALUES(".db_escape($sql).",".($result ? 1 : 0).",
				".db_escape($err_msg).")");
		}
	}

	if ($err_msg != null || $SysPrefs->go_debug) {
		$exit = $err_msg != null;
		if (function_exists('xdebug_call_file'))
			$err_msg = '<br>At file '.xdebug_call_file().':'.xdebug_call_line().':<br>'.$err_msg;
		check_db_error($err_msg, $sql, $exit);
	}
        
    }

    function querydb($query, $bind_params = array()) {
//                    echo $query;
//            print_r($bind_params);
        global $db;
        $stmt = $db->prepare($query);
        if (count($bind_params)) {
            call_user_func_array(array($stmt, 'bind_param'), self::refValues($bind_params));
        }
        
        $stmt = $this->executeStatement($stmt);
        $result = $manageDb->get_result($stmt);
        return $result;
    }

    function executedb($query, $bind_params = array()) {
//            echo $query;
//            print_r($bind_params);
        global $db;
        $test = false;
        $stmt = $db->prepare($query);
        if (count($bind_params)) {
            $bind_params = $manageDb->set_param_data_types($bind_params);
            call_user_func_array(array($stmt, 'bind_param'), self::refValues($bind_params));
        }
        $stmt = $this->executeStatement($stmt);
        if (!$stmt) {
            $test = true;
        }
        //mysqli_close($db);
        return $test;
    }

    function executedb_batch($query, $params_batch = array()) {
//            echo $query;
//            print_r($bind_params);
        global $db;
        $test = false;
        $stmt = $db->prepare($query);
        if (count($params_batch)) {
            foreach ($params_batch as $bind_params) {
                $bind_params = $manageDb->set_param_data_types($bind_params);
                call_user_func_array(array($stmt, 'bind_param'), self::refValues($bind_params));
                if ($stmt->execute()) {
                    $test = true;
                } else {
                    trigger_error('Statement failed : ' . $stmt->error, E_USER_ERROR);
                }
            }
        }
        //mysqli_close($db);
        return $test;
    }

    function getExecutedId($query, $bind_params = array()) {
//                    echo $query;
//            print_r($bind_params);
        global $db;
        $test = false;
        $stmt = $db->prepare($query);
        if (count($bind_params)) {
            $bind_params = $manageDb->set_param_data_types($bind_params);
            call_user_func_array(array($stmt, 'bind_param'), self::refValues($bind_params));
        }

        if ($stmt->execute()) {
            $id = $stmt->insert_id;
            //mysqli_close($db);
            return $id;
        } else {
            mysqli_close($db);
            return 0;
            trigger_error('Statement failed : ' . $stmt->error, E_USER_ERROR);
        }
    }

    function set_params($bind_params = array()) {
        $str = '';
        if (count($bind_params) > 0) {
            $iterator = 1;
            foreach ($bind_params as $param) {
                $str .= ' ? ';
                if ($iterator < count($bind_params))
                    $str .= ', ';
                $iterator ++;
            }
        }

        return $str;
    }

    function set_param_data_types($bind_params = array()) {
        $params = array();
        $str = '';
        if (count($bind_params) > 0) {
            foreach ($bind_params as $param) {
                $str .= 's';
            }

            $params[] = $str;
            foreach ($bind_params as $param) {
                $params[] = $param;
            }
        }

        return $params;
    }

    public function get_table_columns($table_name) {
        $table_columns = array();
        $sql = 'SHOW COLUMNS FROM ' . $table_name;
        $result = $this->querydb($sql);
        while ($data = array_shift($result)) {
            $table_columns[] = $data['Field'];
        }

        return $table_columns;
    }

    function get_result($Statement) {
        $RESULT = array();
        $Statement->store_result();
        for ($i = 0; $i < $Statement->num_rows; $i++) {
            $Metadata = $Statement->result_metadata();
            $PARAMS = array();
            while ($Field = $Metadata->fetch_field()) {
                $PARAMS[] = &$RESULT[$i][$Field->name];
            }
            call_user_func_array(array($Statement, 'bind_result'), $PARAMS);
            $Statement->fetch();
        }
        return $RESULT;
    }

    function refValues($arr) {
        if (strnatcmp(phpversion(), '5.3') >= 0) {
            $refs = array();
            foreach ($arr as $key => $value)
                $refs[$key] = &$arr[$key];
            return $refs;
        }
        return $arr;
    }

    /**
     * Import SQL from file
     *
     * @param string path to sql file
     */
    function sqlImport($file) {

        $delimiter = ';';
        $file = fopen($file, 'r');
        $isFirstRow = true;
        $isMultiLineComment = false;
        $sql = '';
        $test = TRUE;

        while (!feof($file)) {

            $row = fgets($file);

            // remove BOM for utf-8 encoded file
            if ($isFirstRow) {
                $row = preg_replace('/^\x{EF}\x{BB}\x{BF}/', '', $row);
                $isFirstRow = false;
            }

            // 1. ignore empty string and comment row
            if (trim($row) == '' || preg_match('/^\s*(#|--\s)/sUi', $row)) {
                continue;
            }

            // 2. clear comments
            $row = trim($this->clearSQL($row, $isMultiLineComment));

            // 3. parse delimiter row
            if (preg_match('/^DELIMITER\s+[^ ]+/sUi', $row)) {
                $delimiter = preg_replace('/^DELIMITER\s+([^ ]+)$/sUi', '$1', $row);
                continue;
            }

            // 4. separate sql queries by delimiter
            $offset = 0;
            while (strpos($row, $delimiter, $offset) !== false) {
                $delimiterOffset = strpos($row, $delimiter, $offset);
                if ($this->isQuoted($delimiterOffset, $row)) {
                    $offset = $delimiterOffset + strlen($delimiter);
                } else {
                    $sql = trim($sql . ' ' . trim(substr($row, 0, $delimiterOffset)));
                    if (!$this->query($sql)) {
                        $test = FALSE;
                    }

                    $row = substr($row, $delimiterOffset + strlen($delimiter));
                    $offset = 0;
                    $sql = '';
                }
            }
            $sql = trim($sql . ' ' . $row);
        }
        if (strlen($sql) > 0) {
            if (!$this->query($row)) {
                $test = FALSE;
            }
        }

        fclose($file);
        return $test;
    }

    /**
     * Remove comments from sql
     *
     * @param string sql
     * @param boolean is multicomment line
     * @return string
     */
    function clearSQL($sql, &$isMultiComment) {
        if ($isMultiComment) {
            if (preg_match('#\*/#sUi', $sql)) {
                $sql = preg_replace('#^.*\*/\s*#sUi', '', $sql);
                $isMultiComment = false;
            } else {
                $sql = '';
            }
            if (trim($sql) == '') {
                return $sql;
            }
        }

        $offset = 0;
        while (preg_match('{--\s|#|/\*[^!]}sUi', $sql, $matched, PREG_OFFSET_CAPTURE, $offset)) {
            list($comment, $foundOn) = $matched[0];
            if ($this->isQuoted($foundOn, $sql)) {
                $offset = $foundOn + strlen($comment);
            } else {
                if (substr($comment, 0, 2) == '/*') {
                    $closedOn = strpos($sql, '*/', $foundOn);
                    if ($closedOn !== false) {
                        $sql = substr($sql, 0, $foundOn) . substr($sql, $closedOn + 2);
                    } else {
                        $sql = substr($sql, 0, $foundOn);
                        $isMultiComment = true;
                    }
                } else {
                    $sql = substr($sql, 0, $foundOn);
                    break;
                }
            }
        }
        return $sql;
    }

    /**
     * Check if "offset" position is quoted
     *
     * @param int $offset
     * @param string $text
     * @return boolean
     */
    function isQuoted($offset, $text) {
        if ($offset > strlen($text))
            $offset = strlen($text);

        $isQuoted = false;
        for ($i = 0; $i < $offset; $i++) {
            if ($text[$i] == "'")
                $isQuoted = !$isQuoted;
            if ($text[$i] == "\\" && $isQuoted)
                $i++;
        }
        return $isQuoted;
    }

    function query($sql) {
        global $db;
        //$mysqli->set_charset("utf8");
        //echo '#<strong>SQL CODE TO RUN:</strong><br>' . htmlspecialchars($sql) . ';<br><br>';
        if (!$query = $db->query($sql)) {
            //echo mysqli_error($mysqli);
            return FALSE;
        } else {
            return TRUE;
        }
    }

}
?>