<?php

class Databases {

    var $_where;
    var $_select;
    var $_join;
    var $_sql;
    var $_group;
    var $_conn;
    var $_show;
    var $_result = true;
    var $_res_transaction = array();

    function __construct() {
        $connectionInfo = array(
            "UID" => DB_USER,
            "PWD" => DB_PASS,
            "Database" => DB_NAME,
            "ReturnDatesAsStrings" => 1,
            "CharacterSet" => "UTF-8"
        );

        if (isset($this->_conn) && $this->_conn) {
            sqlsrv_close($this->_conn);
        }

        $this->_conn = sqlsrv_connect(DB_HOST, $connectionInfo);

        if ($this->_conn === false) {
            echo "Unable to connect.</br>";
            die();
        }

        $this->reset();
    }

    function sqldata() {
        // $this->_conn = $GLOBALS['conn'];
    }

    function init() {
        sqlsrv_begin_transaction($this->_conn);
        $this->_res_transaction = array();
    }

    function commit() {
        sqlsrv_commit($this->_conn);
    }

    function reset() {
        $this->_where = "";
        $this->_select = " * ";
        $this->_join = "";
        $this->_group = "";
        $this->_show = false;
        $this->_result = true;
    }

    function no_result() {
        $this->_result = false;
    }

    function group_by($group) {
        $this->_group = $group;
    }

    function where($where) {
        $this->_where = $where;
    }

    function select($select = ' * ') {
        $this->_select = $select;
    }

    function join($tabla, $on, $tipo = "") {
        $this->_join .= " " . $tipo . " JOIN " . $tabla . " ON " . $on . " ";
    }

    function get($tab, $where = false, $order = false) {

        if ($order)
            $order = "ORDER BY " . $order;
        if ($where)
            $where = "WHERE " . $where;
        if ($this->_group)
            $this->_group = " GROUP BY " . $this->_group;
        $sql = "SELECT " . $this->_select . " FROM $tab " . $this->_join . " " . $where . " " . $this->_group . " " . $order;

        $this->_sql = $sql;
        $_SESSION['ParSQL'] = $this->_sql;
        $registros = sqlsrv_query($sql);



        $result = array();

        $i = 0;
        while ($reg = sqlsrv_fetch_array($this->_conn, $registros)) {
            $result[] = $reg;
        }


        $this->reset();
        sqlsrv_free_stmt($registros);

        return $result;
    }

    function get_tabla($tab, $where = false, $order = false) {

        if ($order)
            $order = "ORDER BY " . $order;
        if ($where)
            $where = "WHERE " . $where;



        if ($this->_group)
            $this->_group = " GROUP BY " . $this->_group;
        $sql = "SELECT " . $this->_select . " FROM $tab " . $this->_join . " " . $where . " " . $this->_group . " " . $order;

        $this->_sql = $sql;
        if ($this->_show)
            echo $this->_sql;
        $_SESSION['ParSQL'] = $this->_sql;

        $this->timequery();
        $registros = sqlsrv_query($this->_conn, $sql);
        $time = $this->timequery();

        $this->_stack_transaction($registros, $time);
        $result = array();

        $i = 0;
        if ($this->_result) {
            while ($reg = sqlsrv_fetch_array($registros, SQLSRV_FETCH_ASSOC)) {
                $result[] = $reg;
            }
        }

        $this->reset();
        sqlsrv_free_stmt($registros);
        return $result;
    }

    function query($sql, $ret = true) {
        $this->_sql = $sql;
        $_SESSION['ParSQL'] = $this->_sql;
        if ($this->_show)
            echo $this->_sql;
        if ($ret) {
            $_SESSION['ParSQL'] = $sql;
            $this->timequery();
            $registros = sqlsrv_query($this->_conn, $sql);
            $time = $this->timequery();
            $this->_stack_transaction($registros, $time);

            $result = array();
            $i = 0;
            while ($reg = sqlsrv_fetch_array($registros, SQLSRV_FETCH_ASSOC)) {
                $result[$i++] = $reg;
            }

            if (!$registros)
                $result = $registros;

            return $result;
        }
        else {
            $_SESSION['ParSQL'] = $sql;
            $this->timequery();
            $res = sqlsrv_query($this->_conn, $sql);
            $time = $this->timequery();
            $this->_stack_transaction($res, $time);
            if ($res) {
                sqlsrv_free_stmt($res);
                $res = true;
            }

            return $res;
        }
    }

    function delete($tabla, $where = false) {
        if ($where)
            $where = " where " . $where;

        $sql = "delete " . $tabla . $where;
        $this->_sql = $sql;

        $res = sqlsrv_query($this->_conn, $sql);
        $this->_stack_transaction($res);
        if ($res)
            $res = true;

        return $res;
    }

    function insert($tabla, $data) {
        $id = $this->save($tabla, $data, 0);
        return $id;
    }

    function show() {
        $this->_show = true;
    }

    function update($tabla, $data, $where = false, $quotes = true) {
        if ($where)
            $this->_where = $where;

        $edits = "";
        foreach ($data as $key => $val) {
            $st = false;
            if (substr($val, 0, 1) == '[' && substr($val, -1, 1) == ']') {
                $st = true;
                $val = trim(trim($val, "["), "]");
            }
            $val = str_replace("'", "''", $val);

            if ($quotes && !$st) {
                $edits .= $key . "='" . $val . "', ";
            } else {
                $edits .= $key . "=" . $val . ", ";
            }
        }
        $edits = trim($edits, ", ");

        $this->_sql = "update " . $tabla . " set " . $edits . " where " . $this->_where;
        if ($this->_show)
            echo $this->_sql;

        $_SESSION['ParSQL'] = $this->_sql;
        $res = sqlsrv_query($this->_conn, $this->_sql);

        $this->_stack_transaction($res);

        $this->reset();
        if ($res) {
            return sqlsrv_rows_affected($res);
        }
        return FALSE;
    }

    function save($tabla, $data, $tipo = 0) {
        if ($tipo == 0) {
            $cols = "";
            $vals = "";
            foreach ($data as $key => $val) {
                $cols .= $key . ", ";
                $vals .= "'" . str_replace("'", "''", $val) . "', ";
            }

            $cols = trim($cols, ", ");
            $vals = trim($vals, ", ");
            $sql = "insert into " . $tabla . " (" . $cols . ") values (" . $vals . ")";
        } else {
            $edits = "";
            $i = 0;
            $id_key = "";
            $id_val = "";
            foreach ($data as $key => $val) {
                if ($i == 0) {
                    $id_key = $key;
                    $id_val = $val;
                } else {
                    $edits .= $key . "='" . str_replace("'", "''", $val) . " ', ";
                }
                $i++;
            }
            $edits = trim($edits, ", ");

            $sql = "update " . $tabla . " set " . $edits . " where " . $id_key . " = " . $id_val;
        }
        $this->_sql = $sql;
        $_SESSION['ParSQL'] = $this->_sql;

        if ($this->_show)
            echo $this->_sql;
        $res = sqlsrv_query($this->_conn, $sql);

        $this->_stack_transaction($res);

        return $res;
    }

    function last_query() {
        return $this->_sql;
    }

    function _stack_transaction($res, $time = 0) {
        if ($res === false) {

            $error = "<pre>" . print_r(sqlsrv_errors(), true) . "</pre>";

            sqlsrv_rollback($this->_conn);


            $file = fopen(LOG_FILE, 'a+');
            fwrite($file, $this->_sql . "--> " . $error . " --> " . date('Y-m-d H:i:s') . " --> \r\n");
            fclose($file);

            $this->_res_transaction[] = $res;
        } else {
            if (SAVE_SQL_FILE !== false) {
                $init = "";
                if (count($this->_res_transaction) == 0) {
                    $init = "\r\n\r\n\r\nTRANSACCION: " . date('Y-m-d H:i:s') . " - " . SQL_VERSION_DRIVER . " - TERMINAL: " . $_SESSION['ParPOS'] . "\r\n";
                } else {
                    $init = "";
                }
                $terminal = trim("- TER " . $_SESSION['ParPOS'] . " - " . date('d-m-Y'));
                $file = fopen(SAVE_SQL_FILE . " " . $terminal . ".log", 'a+');
                fwrite($file, $init . $this->_sql . " - " . $time . " \r\n\r\n");
                fclose($file);
            }
            $this->_res_transaction[] = $res;
        }
    }

    private function timequery() {
        static $querytime_begin;
        list($usec, $sec) = explode(' ', microtime());

        if (!isset($querytime_begin)) {
            $querytime_begin = ((float) $usec + (float) $sec);
        } else {
            $querytime = (((float) $usec + (float) $sec)) - $querytime_begin;
            return sprintf('%01.5f', $querytime);
        }
    }

}

?>
