<?php

require_once 'dibi/dibi.php';

Class DataManager {

    public static function __getConnection() {
        dibi::connect(array(
            'driver' => 'mysql',
            'host' => 'localhost',
            'username' => 'skorvanek13',
            'password' => 'asx123456',
            'database' => 'skorvanek13',
            'charset' => 'utf8',
        ));
    }

    public static function getUserRow($userLogin, $userPass) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT user_id FROM user WHERE user_login = %s', $userLogin, ' AND user_pass = %s', $userPass);
        return count($res);
    }

    public static function getUserDataByID($user_id) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT * FROM user WHERE user_id = %i', $user_id);
        return $res->fetchAll();
    }

    public static function getUserDataByLogin($user_login) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT * FROM user WHERE user_login = %s', $user_login);
        return $res->fetchAll();
    }

    public static function getUserDataByEmail($userEmail) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT * FROM user WHERE user_email = %s', $userEmail);
        return $res->fetchAll();
    }

    public static function getUserID($userLogin) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT user_id FROM [user] WHERE [user_login] = %s', $userLogin);
        return $res->fetchSingle();
    }

    public static function insertEvent($paArrEv) {
        DataManager::__getConnection();
        $aEvTypeID = DataManager::getEventTypeID($paEventTypeID);

        $aArrEv = array(
            'ev_type_id' => $paArrEv[0],
            'user_id' => $paArrEv[1],
            'event_datetime' => $paArrEv[2] . " " . $paArrEv[3],
            'event_comment' => $paArrEv[4],
            'eventid_change' => $paArrEv[5],
            'event_location' => $paArrEv[6],
            'event_lat' => $paArrEv[7],
            'event_lng' => $paArrEv[8],
        );
        dibi::query('INSERT INTO [event]', $aArrEv);
        $eventID = dibi::getInsertId();
        return $eventID;
    }

    private static function getEventTypeID($paEventTypeID) {  //kontrola ci sa take ID v tabulke nachadza. 
        DataManager::__getConnection();
        $res = dibi::query('SELECT ev_type_id FROM [event_type] WHERE [ev_type_id] = %i', $paEventTypeID);
        return count($res);
    }

    public static function insertEventType() {
        DataManager::__getConnection();
        $arr = array(
            'ev_type_name' => 'Hmla',
            'ev_type_desc' => 'Hmla v okolí pozemnej komunikácií',
        );
        dibi::query('INSERT INTO [event_type]', $arr);
    }

    public static function selectEventType() {
        DataManager::__getConnection();
        $res = dibi::query('SELECT * FROM [event_type] WHERE ev_type_id <= %i', 8, '');
        return $res->fetchAll();
    }

    public static function selectEventTypeID($paeventName) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT ev_type_id FROM [event_type] WHERE [ev_type_name] = %s', $paeventName);
        return $res->fetchSingle();
    }

    public static function getEvents() {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('event')
                ->leftJoin('event_type')->on('event.ev_type_id = event_type.ev_type_id')
                ->orderBy('event_datetime')
                ->fetchAll();
        return $res;
    }

    public static function getOilRecordByUserID($userID) {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('oil_change')
                ->where('user_id = %i', $userID)
                ->orderBy('oil_date DESC')
                ->fetchAll();
        return $res;
    }

    public static function getOilRecordByID($oilID) {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('oil_change')
                ->where('oil_id = %i', $oilID)
                ->fetchAll();
        return $res;
    }

    public static function getLastOilMileageByUserID($user_id) {
        DataManager::__getConnection();
        $res = dibi::select('oil_mileage')
                ->from('oil_change')
                ->where('user_id = %i', $user_id)
                ->orderBy('oil_date DESC')
                ->fetchAll();
        foreach ($res as $r) {
            return $r['oil_mileage'];
        }
        return null;
    }

    public static function getLastOilChangeDateByUserID($user_id) {
        DataManager::__getConnection();
        $res = dibi::select('oil_date')
                ->from('oil_change')
                ->where('user_id = %i', $user_id)
                ->orderBy('oil_date DESC')
                ->fetchAll();
        foreach ($res as $r) {
            return $r['oil_date'];
        }
        return "0000-00-00 00:00:00";
    }

    public static function getCurrentMileageByUserID($userID) {
        DataManager::__getConnection();
        $pom = DataManager::getLastOilChangeDateByUserID($userID);

        $res = dibi::select('track_distance')
                ->from('track')
                ->where('user_id = %i', $userID, ' AND track_starttime > %t', $pom)
                ->fetchAll();
        $pom2 = (0 + DataManager::getLastOilMileageByUserID($userID));

        foreach ($res as $m) {
            $pom2 += $m['track_distance'];
        }
        return $pom2;
    }

    public static function getEvent($paID) {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('event')
                ->where('event_id = %s', $paID)
                ->fetchAll();
        return $res;
    }

    public static function getActualEvents() {
        $date = new DateTime(date('Y-m-d H:i:s'));
        $date->modify('-1 hour');
        $datetime = $date->format('Y-m-d H:i:s');
        //echo $datetime;
        DataManager::__getConnection();
        $ids = dibi::query('SELECT eventid_change FROM [event] WHERE [eventid_change] > 0 ');
        $ids = $ids->fetchAll();
        $arr = array();
        foreach ($ids as $aData) {
            $arr[] = array('event.event_id != %i', $aData['eventid_change']);
        }
        $arr[] = array(' event.eventid_change = %i', 0);
        $arr[] = array(' event.event_datetime > %t', $datetime);
        $res = dibi::select('*')
                ->from('event')
                ->leftJoin('event_type')->on('event.ev_type_id = event_type.ev_type_id')
                ->leftJoin('user')->on('event.user_id = user.user_id')
                ->where('%and', $arr)
                ->orderBy('event_datetime DESC')
                ->fetchAll();
        return $res;
    }

    public static function getEventsByLastID($id) {
        $date = new DateTime(date('Y-m-d H:i:s'));
        $date->modify('-1 hour');
        $datetime = $date->format('Y-m-d H:i:s');
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('event')
                ->leftJoin('event_type')->on('event.ev_type_id = event_type.ev_type_id')
                ->where('event.event_id >%i', $id, ' AND event_datetime > %t', $datetime )
                ->orderBy('event_datetime')
                ->fetchAll();
        return $res;
    }

    public static function login($login, $pass) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT user_id, user_login, user_name, user_surname, user_email FROM [user] WHERE user_login = %s', $login, ' AND user_pass = %s', $pass);
        return $res->fetchAll();
    }

    public static function setPass($login, $pass) {
        DataManager::__getConnection();
        $arr = array(
            'user_pass' => $pass,
        );
        $res = dibi::query('UPDATE user SET ', $arr, ' WHERE user_login = %s', $login);
    }

    public static function register($login, $pass, $email, $name, $surname) {
        DataManager::__getConnection();
        $arr = array(
            'user_login' => $login,
            'user_email' => $email,
            'user_pass' => $pass,
            'user_name' => $name,
            'user_surname' => $surname,
            'user_regdate' => date("Y-m-d")
        );
        dibi::query('INSERT INTO [user]', $arr);
        $eventID = dibi::getInsertId();
        return $eventID;
    }

    public static function setUserNameSurname($name, $surname, $userID) {
        DataManager::__getConnection();
        $arr = array(
            'user_id' => $userID,
            'user_name' => $name,
            'user_surname' => $surname
        );
        dibi::query('INSERT INTO [user]', $arr, 'WHERE user_id = %i', $userID);
    }

    public static function isRegistered($login) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT user_login, user_pass FROM [user] WHERE user_login = %s', $login);
        return $res->fetchSingle();
    }

    public static function getUserIdByLogin($paLogin) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT user_id FROM [user] WHERE user_login = %s', $paLogin);
        return $res->fetchSingle();
    }

    public static function getEventReport($paID) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT event_report FROM [event] WHERE event_id = %i', $paID);
        return $res->fetchSingle();
    }

    public static function getUserRole($paLogin) {
        DataManager::__getConnection();
        $res = dibi::query('SELECT usrol_id FROM [user] WHERE user_login = %s', $paLogin);
        return $res->fetchSingle();
    }

    public static function incrementEventReport($paID) {
        DataManager::__getConnection();
        $res = dibi::query('UPDATE event SET event_report = event_report +%i', 1, ' WHERE event_id = %i', $paID);
    }

    public static function getAllUsers() {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('user')
                ->orderBy('user_id')
                ->fetchAll();
        return $res;
    }

    public static function getTracksByUserID($userID) {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('track')
                ->where('user_id = %i', $userID)
                ->orderBy('track_starttime DESC')
                ->fetchAll();
        return $res;
    }

    public static function getTracksByID($trackID) {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('track')
                ->where('track_id = %i', $trackID)
                ->fetchAll();
        return $res;
    }

    public static function getPointsByTrackID($userID) {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('point')
                ->where('track_id = %i', $userID)
                ->orderBy('point_id ASC')
                ->fetchAll();
        return $res;
    }

    public static function getUserRoles() {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('user_roles')
                ->orderBy('usrol_id ASC')
                ->fetchAll();
        return $res;
    }

    public static function insertTrack($track_id, $user_id, $track_date, $track_distance, $track_name, $track_comment, $track_starttime, $track_endtime) {
        DataManager::__getConnection();
        $aArrEv = array(
            'track_id' => $track_id,
            'user_id' => $user_id,
            'track_date' => $track_date,
            'track_distance' => $track_distance,
            'track_name' => $track_name,
            'track_comment' => $track_comment,
            'track_starttime' => $track_starttime,
            'track_endtime' => $track_endtime
        );
        dibi::query('INSERT INTO [track]', $aArrEv);
        $eventID = dibi::getInsertId();
        return $eventID;
    }

    public static function deleteTrack($trackID) {
        DataManager::__getConnection();
        dibi::delete('track')->where('track_id = %i', $trackID)->execute();
        dibi::delete('point')->where('track_id = %i', $trackID)->execute();
    }

    public static function deleteOilRecordByID($oilID) {
        DataManager::__getConnection();
        dibi::delete('oil_change')->where('oil_id = %i', $oilID)->execute();
    }

    public static function deleteOilRecordByUserID($user_id) {
        dibi::delete('oil_change')->where('user_id = %i', $user_id)->execute();
    }

    public static function deleteUser($userID) {
        DataManager::__getConnection();
        $res = dibi::select('*')
                ->from('track')
                ->where('user_id = %i', $userID)
                ->fetchAll();
        foreach ($res as $track) {
            DataManager::deleteTrack($track['track_id']);
        }
        dibi::delete('user')->where('user_id = %i', $userID)->execute();
        DataManager::deleteOilRecordByUserID($userID);
    }

    public static function insertPoint($point) {
        dibi::query('INSERT INTO [point]', $point);
    }

    public static function addOilRecord($date, $milage, $userID) {
        DataManager::__getConnection();
        $arr = array(
            'oil_date' => $date,
            'oil_mileage' => $milage,
            'user_id' => $userID,
        );
        dibi::query('INSERT INTO [oil_change]', $arr);
    }

    public static function setTrackTitleAndCommentByID($trackID, $title, $comment) {
        DataManager::__getConnection();
        $arr = array(
            'track_name' => $title,
            'track_comment' => $comment,
        );
        $res = dibi::query('UPDATE track SET ', $arr, ' WHERE track_id = %s', $trackID);
    }
    
    public static function updateTrack($trackID, $track_distance, $track_endtime) {
        DataManager::__getConnection();
        $arr = array(
            'track_distance' => $track_distance,
            'track_endtime' => $track_endtime,
        );
        $res = dibi::query('UPDATE track SET ', $arr, ' WHERE track_id = %s', $trackID);
    }

    public static function setEventCommentByID($eventID, $comment) {
        DataManager::__getConnection();
        $arr = array(
            'event_comment' => $comment,
        );
        $res = dibi::query('UPDATE event SET ', $arr, ' WHERE event_id = %s', $eventID);
    }

    public static function setUserNameAndSurname($userID, $userName, $UserSurname) {
        DataManager::__getConnection();
        $arr = array(
            'user_name' => $userName,
            'user_surname' => $UserSurname,
        );
        $res = dibi::query('UPDATE user SET ', $arr, ' WHERE user_id = %s', $userID);
    }

    public static function setUserRoleByID($userID, $usrol_id) {
        DataManager::__getConnection();
        $arr = array(
            'usrol_id' => $usrol_id,
        );
        $res = dibi::query('UPDATE user SET ', $arr, ' WHERE user_id = %s', $userID);
    }

}

?>