<?php

class db {

    const hostname = 'localhost';
    const user = 'root';
    const pass = '';
    const dbname = 'ngo';

    private $dbh;

    function __construct() {
        try {
            $conn_string = "mysql:host=" . self::hostname . ";dbname=" . self::dbname;
            $this->dbh = new PDO($conn_string, self::user, self::pass);
        } catch (PDOException $e) {
            echo "ERROR: ";
            echo $e->getMessage();
        }
    }

    function __destruct() {
        $this->dbh = null;
    }

    public function randomString() {
        $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $randstring = '';
        for ($i = 0; $i < 10; $i++) {
            $randstring .= $characters[rand(0, strlen($characters) - 1)];
        }
        return $randstring;
    }

    public function addPatient($fullname, $state, $address, $city, $age, $gender, $norp, $aorp, $phone, $relation, $passport) {
        try {
            $sql = "INSERT INTO patient(fullname, state,address,city,age,gender,norp,aorp,phone,relation,passport) ";
            $sql .= "VALUES ('$fullname','$address','$city','$state','$age','$gender','$norp','$aorp','$phone','$relation','$passport')";
            $this->dbh->exec($sql);
        } catch (PDOException $e) {
            echo "ERROR: ";
            echo $e->getMessage();
            return false;
        }
    }

    public function getFiveSimilar($name) {
        $sql = "SELECT id, fullname "
                . "FROM patient "
                . "WHERE fullname like '$name%' "
                . "LIMIT 5";
        $pdo = $this->dbh->query($sql);
        $n = $pdo->fetchALL(PDO::FETCH_ASSOC);
        return $n;
    }

    public function lastPatient() {
        $sql = "SELECT id as id "
                . "FROM patient "
                . "ORDER BY id desc "
                . "LIMIT 1";
        $pdo = $this->dbh->query($sql);
        $n = $pdo->fetch(PDO::FETCH_ASSOC);
        return $n['id'];
    }

    public function addVisit($doa, $diagnosis, $prescription, $lastPatient) {
        try {
            $sql = "INSERT INTO visit(date_entered,date_left,patient_id,diagnosis,prescription) ";
            $sql .= "VALUES ('$doa','','$lastPatient','$diagnosis','$prescription')";
            $this->dbh->exec($sql);
        } catch (PDOException $e) {
            echo "ERROR: ";
            echo $e->getMessage();
            return false;
        }
    }

    public function formatDate($date) {
        $dt = explode('/', $date);
        return $dt[2] . '-' . $dt[1] . '-' . $dt[0];
    }

    public function getSinglePatientData($id) {
        $sql = "SELECT * "
                . "FROM patient "
                . "WHERE id=$id";
        $pdo = $this->dbh->query($sql);
        $n = $pdo->fetch(PDO::FETCH_ASSOC);
        return $n;
    }

    public function getVisits($name, $dateFrom, $dateTo) {
        $sql = "SELECT p.id as patientId, p.fullname as fullname, "
                . "p.address as address, p.city as city, "
                . "p.state as state, p.age as age, "
                . "p.gender as gender, p.norp as norp, "
                . "p.aorp as aorp, p.phone as phone, p.relation as relation, "
                . "p.passport as passport, v.id as visitId, "
                . "v.date_entered as dateEntered, v.date_left as dateLeft, "
                . "v.diagnosis as diagnosis, v.prescription as prescription "
                . "FROM patient p "
                . "JOIN visit v "
                . "ON p.id=v.patient_id "
                . "WHERE fullname LIKE '$name%' "
                . "AND v.date_entered>='$dateFrom' "
                . "AND v.date_left<='$dateTo'";
        $pdo = $this->dbh->query($sql);
        $n = $pdo->fetchALL(PDO::FETCH_ASSOC);
        return $n;
    }

        public function getVisitsForDownload($name, $dateFrom, $dateTo) {
        $sql = "SELECT p.id as patientId, p.fullname as fullname, "
                . "p.address as address, p.city as city, "
                . "p.state as state, p.age as age, "
                . "p.gender as gender, p.norp as norp, "
                . "p.aorp as aorp, p.phone as phone, p.relation as relation, "
                . "p.passport as passport, v.id as visitId, "
                . "v.date_entered as dateEntered, IF(v.date_left='0000-00-00','not finished',v.date_left) as dateLeft, "
                . "v.diagnosis as diagnosis, v.prescription as prescription "
                . "FROM patient p "
                . "JOIN visit v "
                . "ON p.id=v.patient_id "
                . "WHERE fullname LIKE '$name%' "
                . "AND v.date_entered>='$dateFrom' "
                . "AND v.date_left<='$dateTo'";
        $pdo = $this->dbh->query($sql);
        $n = $pdo->fetchALL(PDO::FETCH_ASSOC);
        return $n;
    }

    public function finishVisit($date,$id) {
        $sql = "UPDATE visit SET date_left='" . $date . "' WHERE id='" . $id . "'";
        $pdo = $this->dbh->prepare($sql);
        $pdo->execute();
    }
}
?>