<?php

//use Doctrine\ORM\Tools\Setup;
//use Doctrine\ORM\EntityManager;
//require_once "vendor/autoload.php";

use Illuminate\Support\Facades\DB as LaraDB;
use App\Models\User as LaraUser;

/**
 * FR_Object_DB
 *
 * @author Dirk Herbes <d.herbes@fair-computer.de>
 * @copyright Dirk Herbes <d.herbes@fair-computer.de>
 * @package Framework
 * @filesource
 */
require_once('DB.php');


//require_once('Doctrine.php');
// TODO 2013
//	if (!defined('_HM_NESTEDSETS_OFF') || _HM_NESTEDSETS_OFF !== true) {
//		require_once _HM_INCLUDES.'nestedsets.php';
//	}

/**
 * FR_Object_DB
 *
 * Extends the base FR_Object class to include a database connection.
 *
 * @author Dirk Herbes <d.herbes@fair-computer.de>
 * @package Framework
 */
abstract class FR_Object_DB extends FR_Object {

    protected $db;
    protected $dateformat = '';
    protected $timequotes = "'";

    static $countQueries = 1;
    public function __construct() {

        parent::__construct();




//$manager = Doctrine_Manager::getInstance();
////$pdo = new PDO("mssql:Server=".DBHOST.";Database=".DBNAME.";", DBUSER, DBPASS);
//
//$pdo = new PDO("odbc:Driver={SQL Server};Server=".DBHOST.";Database=".DBNAME.";", DBUSER, DBPASS);  // works with proper driver for ODBC and PHP ODBC.
//
//$conn = Doctrine_Manager::connection($pdo);
//
//$stmt = $conn->prepare('SELECT * FROM hm_Ticket');
//$stmt->execute();
//$results = $stmt->fetchAll();
//print_r($results);


        static $connection = null;

        if ($connection === null) {
            $phptype = @DBTYPE;

            $dsn = array(
                'phptype'  => DBTYPE,
                'hostspec' => DBHOST,
                'database' => (DBTYPE == 'mssql') ? '[' . DBNAME . ']' : DBNAME,
                'username' => DBUSER,
                'password' => DBPASS,
            );

            if (DBTYPE == 'oci8') {
                unset($dsn['database']);
                @putenv('NLS_LANG=GERMAN_GERMANY.WE8ISO8859P15');
            }

            if (defined('DBPORT') && (int) DBPORT > 0 && DBTYPE != 'oci8') {
                $dsn['port'] = DBPORT;
            }


            $options = array(
                'portability' => DB_PORTABILITY_LOWERCASE,
                 // 'encrypt' => 'yes',
                  //  'trust_server_certificate' => true,
            );
//var_dump($dsn);
//var_dump($options);

            $connection = DB::connect($dsn, $options);

            if (!PEAR::isError($connection)) {

                $connection->setFetchMode(DB_FETCHMODE_ASSOC);

                // $connection->query("SET NAMES 'utf8'");
                // $connection->query("SET CHARACTER SET 'utf8'");
            } else {
                if (isset($_SESSION['install']['InitDB']) && $_SESSION['install']['InitDB'] == 1) {
                    return false;
                }
                throw new Exception($connection->getMessage());
            }
        }
        $this->db = $connection;
    }

    public function legacy_query($query, $params = array()){
        if((defined('ZEITMESSUNG') && ZEITMESSUNG)){
            $start = microtime(true);

            $temp = debug_backtrace();
            $BTData = $temp[1];

            $Location = '';
            if(is_array($BTData) && isset($BTData['class'])){
                $Location = $BTData['class'] . '->' . $BTData['function'] . ' (' . $BTData['line'] . ')';
            }

            $res = $this->db->query($query, $params = array());
            //        echo "/n" . $Count++ . " Ich bin es! " . $query;
            if(isset($query)){
                $GLOBALS['usedQuerys'][]= array( $this->countQueries++ , date("Y-m-d H:i:s"), str_replace('.', ',', sprintf("%.2f", (microtime(true) - $start) * 1000)), $Location, str_replace(array("\n", "\r"), ' ', $query), md5($this->shortenQueryForHash($query)));
            }

            return $res;
        } else {
            return $this->db->query($query, $params = array());
        }
    }

    public function legacy_getOne($query, $params = array())
    {
        if((defined('ZEITMESSUNG') && ZEITMESSUNG)){

        $start = microtime(true);

        $temp = debug_backtrace();
            $LineOfRequest = $temp[0]['line'];
        $BTData = $temp[1];

        $Location = '';
        if (is_array($BTData) && isset($BTData['class'])) {
            $Location = $BTData['class'] . '->' . $BTData['function'] .' (' . $LineOfRequest . ')';
        }

        $res = $this->db->getOne($query, $params = array());
        //        echo "/n" . $Count++ . " Ich bin es! " . $query;
        $GLOBALS['usedQuerys'][] = array($this->countQueries++, date("Y-m-d H:i:s") ,str_replace('.', ',',sprintf("%.2f", (microtime(true) - $start) * 1000)), $Location, str_replace(array("\n", "\r"), ' ', $query), md5($this->shortenQueryForHash($query)));
            return $res;
        } else {
            return $this->db->getOne($query, $params = array());
        }
    }

    private function shortenQueryForHash(string $query): string
    {
        $queryUpper = strtoupper($query); // Zum Vergleichen ohne Case-Sensitivität

        $wherePos = strpos($queryUpper, 'WHERE');
        $valuesPos = strpos($queryUpper, 'VALUES');

        if ($wherePos !== false) {
            return substr($query, 0, $wherePos);
        } elseif ($valuesPos !== false) {
            return substr($query, 0, $valuesPos);
        } else {
            return $query; // nichts zu kürzen
        }
    }


    /**
     * getTimeFormat()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function getTimeFormat() {
        if ($this->dateformat == '') {

            if (isset($_SESSION['dateformat']) && ($_SESSION['dateformat'] == 'ger' ||
                    $_SESSION['dateformat'] == 'eng')) {

                $this->dateformat = $_SESSION['dateformat'];
            } else {

                $this->dateformat = 'ger';

                //jk 18.03.2010 SQL-Server 2008 lieferte falsches Datumsformat bei alter Prüfung, MySQL bei der neuen
                if (DBTYPE == 'mysql') {
                    $result = $db->getOne("SELECT MONTH('2004-12-31 23:59:59')");
                    if (PEAR::isError($result) || $result != 12) {
                        $this->dateformat = 'eng';
                    }
                } else {
                    $result = $this->db->getOne((DBTYPE == 'oci8') ? 12 : "SELECT MONTH(CONVERT(datetime, ' 2004-12-31 23:59:59' , 104))");
                    if (PEAR::isError($result) || $result != 12) {
                        $this->dateformat = 'eng';
                    }
                }
                $_SESSION['dateformat'] = $this->dateformat;
            }
        }
    }

    /**
     * DB_DATE()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function DB_DATE($date) {
        if ($this->dateformat == '')
            $this->getTimeFormat();
        if ($date === 'NULL')
            return $date;
        $_SESSION['timequotes'] = $this->timequotes;
        $_SESSION['dateformat'] = $this->dateformat;

        $tempdate = strtolower(trim($date));

        if ($tempdate == 'getdate()' || $tempdate == 'now()') {

            switch (strtolower(DBTYPE)) {
                default:
                case 'odbc':
                case 'sqlsrv':
                case 'mssql': $date = 'GETDATE()';
                    break;
                case 'mysql': $date = 'NOW()';
                    break;
                case 'oci8': $date = 'SYSDATE';
                    break;
            }

            return ' ' . $date . ' ';
        } elseif (strtotime($date) !== false) {

            if (!is_numeric($date)) {
                $date = strtotime($date);
            }
            if (DBTYPE == 'oci8') {
                $date = " TO_DATE('" . date('Y-m-dH:i:s', $date) . "','YYYY-MM-DDHH24:MI:SS') ";
                return $date;
            } else {
                $date = ($this->dateformat != 'eng') ? date('Ymd H:i:s', $date) : date('Ymd H:i:s', $date);
                return $this->timequotes . $date . $this->timequotes;
            }
        } elseif (is_numeric($date)) {

            if (DBTYPE == 'oci8') {
                $date = " TO_DATE('" . date('Y-m-dH:i:s', $date) . "','YYYY-MM-DDHH24:MI:SS') ";
                return $date;
            } else {
                $date = ($this->dateformat != 'eng') ? date('Ymd H:i:s', $date) : date('Ymd H:i:s', $date);
                return $this->timequotes . $date . $this->timequotes;
            }
        } else {
            return $this->DB_CONVERT($date);
        }
    }

    /**
     * Wandelt ein Datum in das korrekte Datumsformat entsprechend der Datenbank um.
     * @param string $date
     * @return string Datum
     */
    public function NEW_DB_DATE($date) {
        if ($this->dateformat == '') {
            $this->getTimeFormat();
        }
        if ($date === 'NULL') {
            return $date;
        }

        $_SESSION['timequotes'] = $this->timequotes;
        $_SESSION['dateformat'] = $this->dateformat;

        $tempdate = strtolower(trim($date));

        if ($tempdate == 'getdate()' || $tempdate == 'now()') {

            switch (strtolower(DBTYPE)) {
                default:
                case 'odbc':
                case 'sqlsrv':
                case 'mssql': $date = 'GETDATE()';
                    break;
                case 'mysql': $date = 'NOW()';
                    break;
                case 'oci8': $date = 'SYSDATE';
                    break;
            }
            return ' ' . $date . ' ';
        }

        try {
            $datetime = new DateTime($date);
            if (DBTYPE == 'oci8') {
                return " TO_DATE('" . $datetime->format('Y-m-dH:i:s') . "','YYYY-MM-DDHH24:MI:SS') ";
            } else {
                $date = ($this->dateformat != 'eng') ? $datetime->format('Ymd H:i:s') : $datetime->format('Ymd H:i:s');
                return $this->timequotes . $date . $this->timequotes;
            }
        } catch (Exception $exc) {
            echo $exc->getTraceAsString();
        }
    }

    /**
     * Liefert einen Formatierten Datumsstring zum einf�gen in SQL-DBs.
     * Das Format ist abh�ngig vom DB-Typ:
     * oci8: TO_DATE('Y-m-dH:i:s','YYYY-MM-DDHH24:MI:SS')
     * sonst: 'Ymd H:i:s'
     * @param DateTime $DateTime
     * @return string
     */
    public function DB_DATETIME(DateTime $DateTime) {

        try {
            if (DBTYPE == 'oci8') {
                return " TO_DATE('" . $DateTime->format('Y-m-dH:i:s') . "','YYYY-MM-DDHH24:MI:SS') ";
            } else {
                $date = ($this->dateformat != 'eng') ? $DateTime->format('Ymd H:i:s') : $DateTime->format('Ymd H:i:s');
                return $this->timequotes . $date . $this->timequotes;
            }
        } catch (Exception $exc) {
            echo $exc->getTraceAsString();
        }
    }

    /**
     * DB_DATEPART()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function DB_DATEPART($part, $time) {
        $sql = '';
        switch (strtolower(DBTYPE)) {
            case 'oci8':
                switch (strtolower($part)) {
                    case 'year': $sql = " TO_CHAR($time, 'YYYY') ";
                        break;
                    case 'month': $sql = " TO_CHAR($time, 'MM') ";
                        break;
                    case 'day': $sql = " TO_CHAR($time, 'DD') ";
                        break;
                    case 'minute': $sql = " TO_CHAR($time, 'MI') ";
                        break;
                    case 'hour': $sql = " TO_CHAR($time, 'HH24') ";
                        break;
                    case 'second': $sql = " TO_CHAR($time, 'SS') ";
                        break;
                }
                break;

            case 'mysql':
                switch (strtolower($part)) {
                    case 'year': $sql = " YEAR($time) ";
                        break;
                    case 'month': $sql = " MONTH($time) ";
                        break;
                    case 'day': $sql = " DAY($time) ";
                        break;
                    case 'minute': $sql = " MINUTE($time) ";
                        break;
                    case 'hour': $sql = " HOUR($time) ";
                        break;
                    case 'second': $sql = " SECOND($time) ";
                        break;
                }
                break;

            case 'odbc' :
            case 'mssql':
            case 'sqlsrv':
                switch (strtolower($part)) {
                    case 'year': $sql = " YEAR($time) ";
                        break;
                    case 'month': $sql = " MONTH($time) ";
                        break;
                    case 'day': $sql = " DAY($time) ";
                        break;
                    case 'minute': $sql = " DATEPART(mi, $time) ";
                        break;
                    case 'hour': $sql = " DATEPART(hh, $time) ";
                        break;
                    case 'second': $sql = " DATEPART(ss, $time) ";
                        break;
                }
                break;
        }
        return $sql;
    }

    /**
     * DB_DATEADD()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function DB_DATEADD($datepart, $number, $date) {
        $dateparts = array('year', 'month', 'day', 'hour', 'minute', 'second');
        $sql = '';

        $number = (int) $number;

        if (in_array(strtolower(trim($datepart)), $dateparts)) {
            switch (strtolower(DBTYPE)) {
                case 'oci8':
                    switch ($datepart) {

                        case 'year': $sql .= " ADD_MONTHS($date, $number * 12) ";
                            break;
                        case 'month': $sql .= " ADD_MONTHS($date, $number) ";
                            break;
                        case 'day': $sql .= " $date + $number ";
                            break;
                        case 'hour': $sql .= " $date + ($number/24) ";
                            break;
                        case 'minute': $sql .= " $date + ($number/1440) ";
                            break;
                        case 'second': $sql .= " $date + ($number/86400) ";
                            break;
                    }
                    break;
                case 'odbc':
                case 'sqlsrv':
                case 'mssql': $sql = " DATEADD($datepart, $number, $date) ";
                    break;
                case 'mysql': $sql = " DATE_ADD($date, INTERVAL $number $datepart) ";
                    break;
            }
        } else {
            $sql = $date;
        }
        return $sql;
    }

    /**
     * DB_DATESUB()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function DB_DATESUB($datepart, $number, $date) {
        $dateparts = array('year', 'month', 'day', 'hour', 'minute', 'second');
        $sql = '';

        $number = (int) $number;

        if (in_array(strtolower(trim($datepart)), $dateparts)) {
            switch (strtolower(DBTYPE)) {
                case 'oci8':
                    switch ($datepart) {
                        case 'year':
                            $number *= -12;
                            $sql .= " ADD_MONTHS($date, $number) ";
                            break;
                        case 'month':
                            $number *= -1;
                            $sql .= " ADD_MONTHS($date, $number) ";
                            break;
                        case 'day':
                            $sql .= " $date - $number ";
                            break;
                        case 'hour':
                            $sql .= " $date - ($number/24) ";
                            break;
                        case 'minute':
                            $sql .= " $date - ($number/1440) ";
                            break;
                        case 'second':
                            $sql .= " $date - ($number/86400) ";
                            break;
                    }
                    break;
                case 'odbc':
                case 'sqlsrv':
                case 'mssql': $sql = " DATEADD($datepart, -$number, $date) ";
                    break;
                case 'mysql': $sql = " DATE_SUB($date, INTERVAL $number $datepart) ";
                    break;
            }
        } else {
            $sql = $date;
        }
        return $sql;
    }

    /**
     * DB_CONVERT()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function DB_CONVERT($date) {
        // Auswahl nach Datenbanktyp
        switch (strtolower(DBTYPE)) {
            case 'odbc':
            case 'sqlsrv':
            case 'mssql': $sql = " CONVERT(VARCHAR(19), $date, 120) ";
                break;
            case 'oci8': $sql = " TO_CHAR($date, 'YYYY-MM-DD HH24:MI:SS') ";
                break;
            default: $sql = $date;
                break;
        }
        return $sql;
    }

    /**
     * DB_DATEDIFF()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function DB_DATEDIFF($datepart, $date1, $date2) {
        // Moegliche Differenzeinheiten
        $dateparts = array('year', 'month', 'week', 'day', 'hour', 'minute', 'second');

        // Wenn die Einheit gltig ist
        if (in_array(strtolower(trim($datepart)), $dateparts)) {
            // Fr jeden Connection Type entsprechend umsetzen
            switch (strtolower(DBTYPE)) {
                case 'oci8':
                    switch (strtolower($datepart)) {
                        case 'year': $sql = " FLOOR(MONTHS_BETWEEN($date2, $date1)/12)";
                            break;
                        case 'month': $sql = " FLOOR(MONTHS_BETWEEN($date2, $date1)) ";
                            break;
                        case 'week': $sql = " FLOOR(($date2 - $date1) / 7) ";
                            break;
                        case 'day': $sql = " FLOOR($date2 - $date1) ";
                            break;
                        case 'hour': $sql = " FLOOR(($date2 - $date1) * 24) ";
                            break;
                        case 'minute': $sql = " FLOOR(($date2 - $date1) * 1440) ";
                            break;
                        case 'second': $sql = " FLOOR(($date2 - $date1) * 86400) ";
                            break;
                    }
                    break;
                // Microsoft SQL Server
                case 'odbc':
                case 'mssql':
                case 'sqlsrv':
                    switch (strtolower($datepart)) {
                        case 'year': $datepart = "yyyy";
                            break;
                        case 'month': $datepart = "mm";
                            break;
                        case 'week': $datepart = "ww";
                            break;
                        case 'day': $datepart = "dd";
                            break;
                        case 'hour': $datepart = "hh";
                            break;
                        case 'minute':$datepart = "mi";
                            break;
                        case 'second':$datepart = "ss";
                            break;
                    }
                    $sql = " DATEDIFF($datepart, $date1, $date2) ";
                    break;
                    break;

                // MySQL Server
                case 'mysql':
                    switch (strtolower($datepart)) {
                        /* Ab MySQL Version 5.0 */
                        case 'year': $sql = " TIMESTAMPDIFF(YEAR,$date2, $date1) ";
                            break;
                        case 'month': $sql = " TIMESTAMPDIFF(MONTH,$date2, $date1) ";
                            break;
                        /* Ab MySQL 4.0 */
                        case 'week': $sql = " floor((UNIX_TIMESTAMP($date2) -  UNIX_TIMESTAMP($date1)) / (60 * 60 * 24 * 7)) ";
                            break;
                        case 'day': $sql = " floor((UNIX_TIMESTAMP($date2) -  UNIX_TIMESTAMP($date1)) / (60 * 60 * 24)) ";
                            break;
                        case 'hour': $sql = " floor((UNIX_TIMESTAMP($date2) -  UNIX_TIMESTAMP($date1)) / (60 * 60)) ";
                            break;
                        case 'minute':$sql = " floor((UNIX_TIMESTAMP($date2) - UNIX_TIMESTAMP($date1)) / 60) ";
                            break;
                        case 'second':$sql = " (UNIX_TIMESTAMP($date2) - UNIX_TIMESTAMP($date1)) ";
                            break;
                    }
                    break;

                default: $sql = 0;
                    break; // 0 Wenn nicht Untersttzt
            }
        } else {
            $sql = 0; // 0 Wenn nicht guelitg
        }
        return $sql;
    }

    /**
     * strtotimestamp()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function strtotimestamp($date) {
        if (strtotime($date) !== -1) {
            return strtotime($date);
        } elseif (is_int($date) && (int) $date > 0) {
            return $date;
        } else {
            $date = trim($date);
            if (preg_match('/([0-3]{0,1}[0-9]{1})\.([0-1]{0,1}[0-9]{1})\.([0-9]{2,4})/', $date, $match)) {
                $newDate = $match[3] . '-' . $match[2] . '-' . $match[1];
                if (preg_match('/([0-2]{0,1}[0-9]{1}):([0-5]{0,1}[0-9]{1}):([0-5]{0,1}[0-9]{1})/', $date, $match)) {
                    $newTime = $match[0];
                } elseif (preg_match('/([0-2]{0,1}[0-9]{1}):([0-5]{0,1}[0-9]{1})/', $date, $match)) {
                    $newTime = $match[0];
                } else {
                    $newTime = '00:00:00';
                }
                $date = strtotime($newDate . ' ' . $newTime);
                if ($date !== -1) {
                    return $date;
                }
            }
        }
        return false;
    }

    /**
     * date_to_sql()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function date_to_sql($startdate, $enddate = null) {
        $startdate = $this->strtotimestamp($startdate . ' 23:59:59');
        $enddate = $this->strtotimestamp($enddate . ' 23:59:59');

        if ($startdate > $enddate) {
            $temp = $startdate;
            $startdate = $enddate;
            $enddate = $temp;
        }

        $endtime = $this->DB_DATE($enddate);
        $starttime = $this->DB_DATE(strtotime(date('Y-m-d 00:00:01', $startdate)));
        $startform = date('d.m.Y', $startdate);
        $endform = date('d.m.Y', $enddate);

        return array($starttime, $endtime, $startform, $endform);
    }

    /**
     * mysqlparse()
     *
     * @author Dirk Herbes <d.herbes@fair-computer.de>
     *
     */
    public function mysqlparse($dql) {
        if (strtolower(DBTYPE) == 'mysql' || strtolower(DBTYPE) == 'mssql' ||
                strtolower(DBTYPE) == 'odbc') {
            if (strtolower(DBTYPE) == 'mysql') {
                $sql = preg_replace('=\[(.*)\]=Uis', '`\1`', $sql);
            }
            $sql = preg_replace('?#(.*)#?Uis', '\'\1\'', $sql);
        }
        return $sql;
    }

    function __destruct() {
        parent::__destruct();
        if (DB::isConnection($this->db)) {
//            $this->db->disconnect();
        }
    }

}

?>
