class DB
{
    private $dbh;

    function __construct($host='localhost', $user='root', $password='', $dbname='ems')
    {
        $this->dbh = new mysqli( $host, $user, $password, $dbname);
    }


    function registerEmployee($name, $surname, $login, $password1, $password2, $email)
    {
        $name=htmlentities($name, ENT_QUOTES);
        $surname=htmlentities($surname, ENT_QUOTES);
        $login=htmlentities($login, ENT_QUOTES);
        $password1=htmlentities($password1, ENT_QUOTES);
        $password2=htmlentities($password2, ENT_QUOTES);
        $email=htmlentities($email, ENT_QUOTES);

        $stmt=$this->dbh->prepare('SELECT id FROM employee WHERE login=? OR email=?');
        $stmt->bind_param('ss', $login, $email);
        $stmt->execute();
        $stmt->bind_result($result);
        $stmt->fetch();

        $_SESSION['formerror']=0;
        $_SESSION['conflict']=0;
        $_SESSION['loginerror']=0;
        $_SESSION['passerror']=0;

        if(!$name || !$surname || !$login || !$password1 || !$password2 || !$email)  $_SESSION['formerror']=1;
        if($result)  $_SESSION['conflict']=1;

        if(strlen($login)<7)  $_SESSION['loginerror']=1;
        if((strlen($password1)>7||strlen($password2)>7)||($password1!=$password2))  $_SESSION['passerror']=1;

        if($_SESSION['formerror'] || $_SESSION['conflict'] || $_SESSION['loginerror'] || $_SESSION['passerror'])
        {
            return 0;
        }
        else
        {
            $stmt=0;
            $password=password_hash($password1, PASSWORD_DEFAULT);

            $stmt=$this->dbh->prepare("INSERT INTO employee VALUES (NULL, ?, ?, ?, ?, ?, NULL)");
            $stmt->bind_param('sssss', $name, $surname, $login, $password, $email);
            $stmt->execute();

            return 1;
        }
    }


    function addTask($from_place, $to_place, $from_time, $to_time, $km, $payment)
    {
        $stmt=$this->dbh->prepare('SELECT id FROM task WHERE from_place=? AND to_place=? AND from_time=? AND to_time=? AND km=? AND payment=?');
        $stmt->bind_param('ssssdd', $from_place, $to_place, $from_time, $to_time, $km, $payment);
        $stmt->execute();
        $stmt->bind_result($result);
        $stmt->fetch();
        if($result)
        {
            return 0;
        }

        $zero=0;
        $stmt=$this->dbh->prepare('INSERT INTO task VALUES(null, null, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->bind_param('ssssddd', $from_place, $to_place, $from_time, $to_time, $km, $payment, $zero);
        $stmt->execute();

        if($stmt)
        {
            return 1;
        }
        else
        {
            return 0;
        }
    }


    function setTaskPerformer($employee_id, $task_id)
    {
        $stmt=$this->dbh->prepare('UPDATE task SET performer=? WHERE id=?');
        $stmt->bind_param('ss', $employee_id, $task_id);
        $stmt->execute();
        $result=$stmt->affected_rows;
        if($result)
        {
            return 1;
        }
        else
        {
            return 0;
        }
    }


    function getTaskByID($task_id)
    {
        $stmt=$this->dbh->prepare('SELECT * FROM task WHERE id=?');
        $stmt->bind_param('s', $task_id);
        $stmt->execute();
        $result=$stmt->fetch_assoc();

        return $result;
    }


    function getTaskByPerformer($employee_id)
    {
        $stmt=$this->dbh->prepare('SELECT * FROM task WHERE performer=?');
        $stmt->bind_param('s', $employee_id);
        $stmt->execute();
        $result=$stmt->fetch_assoc();

        return $result;
    }


    function getEmployeeByID($employee_id)
    {
        $stmt=$this->dbh->prepare('SELECT id, name, surname, login, email, salary FROM task WHERE id=?');
        $stmt->bind_param('s', $employee_id);
        $stmt->execute();
        $result=$stmt->fetch_assoc();

        return $result;
    }


    function getTasks($count, $from=0)
    {
        $stmt=$this->dbh->prepare('SELECT id FROM task LIMIT ?, ?');
        $stmt->bind_param('ss', $count);
        $stmt->execute();
        $result=$stmt->get_result();
        $rows=$result->fetch_all(MYSQLI_ASSOC);

        return $rows;
    }


    function getOrderedTasks($count, $from=0)
    {
        $stmt=$this->dbh->prepare('SELECT id FROM task WHERE performer > 0  ORDER BY id ASC LIMIT ?, ?');
        $stmt->bind_param('ss', $from, $count);
        $stmt->execute();
        $result=$stmt->get_result();
        $rows=$result->fetch_all(MYSQLI_ASSOC);

        return $rows;
    }


    function getUnorderedTasks($count, $from=0)
    {
        $stmt=$this->dbh->prepare('SELECT id FROM task WHERE performer IS NULL ORDER BY id ASC LIMIT ?, ?');
        $stmt->bind_param('ss', $from, $count);
        $stmt->execute();
        $result=$stmt->get_result();
        $rows=$result->fetch_all(MYSQLI_ASSOC);

        return $rows;
    }


    function getCompletedTasks($count, $from=0)
    {
        $stmt=$this->dbh->prepare('SELECT id FROM task WHERE  completed = 1 ORDER BY id ASC LIMIT ?, ?');
        $stmt->bind_param('ss', $from, $count);
        $stmt->execute();
        $result=$stmt->get_result();
        $row=$result->fetch_all(MYSQLI_ASSOC);

        return $rows;
    }


    function getUncompletedTasks($count, $from=0)
    {
        $stmt=$this->dbh->prepare('SELECT id FROM task WHERE completed = 0  ORDER BY id ASC LIMIT ?, ?');
        $stmt->bind_param('ss', $from, $count);
        $stmt->execute();
        $result=$stmt->get_result();
        $rows=$result->fetch_all(MYSQLI_ASSOC);

        return $rows;
    }


    function getFreeEmployee($count, $from=0)
    {
        $stmt=$this->dbh->prepare('SELECT id FROM employee WHERE id NOT IN (SELECT performer FROM task WHERE performer IS NOT NULL AND completed = 0 ) ORDER BY id ASC LIMIT ?, ?');
        $stmt->bind_param('ss', $from, $count);
        $stmt->execute();
        $result=$stmt->get_result();
        $rows=$result->fetch_all(MYSQLI_ASSOC);

        return $rows;
    }
}