<?php
    /*
    ######################## CLASS FUNCTION HEADERS ############################
    GetAllEndoscopes()
    InsertNewExam($userID, $currentCountry, $currentCity, $addressSince)
    ############################################################################
    */
    abstract class DaoUsers
    {
        // =====================================================================
        //  INSERTION FUNCTIONS
        // =====================================================================
        static public function InsertNewRequest($name, $email, $just = null, $institution)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   INSERT INTO ".$dal->get_dbschema().".request
                (
                    name, email, justification, institution
                )
                VALUES
                (
                    :name, :email, :justification, :institution
                )";

            //echo $query;

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':name' => $name,
                                    ':email'=> $email,
                                    ':justification'=> $just,
                                    ':institution'=> $institution)))
            {
                return true;
                //return $dal->get_pdo()->lastInsertId();
            }

            return false;
        }

        static public function InsertInvites($email, $name, $institution, $accepted)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   INSERT INTO ".$dal->get_dbschema().".request
                (
                    name, email, institution, accepted
                )
                VALUES
                (
                    :name, :email, :institution, :accepted
                )";

            //echo $query;

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':name'         => $name,
                                    ':email'        => $email,
                                    ':institution'  => $institution,
                                    ':accepted'     => $accepted)))
            {
                return $dal->get_pdo()->lastInsertId();
            }

            return false;
        }

        static public function InsertUserData($name, $age, $yearsOfExp, $email, $username, $password, $entityID, $roleID)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   INSERT INTO ".$dal->get_dbschema().".user
                (
                    name, age, yearsOfExperience, username,
                    password, email, entityID, roleID
                )
                VALUES
                (
                    :name, :age, :yearsOfExperience, :username,
                    :password, :email, :entityID, :roleID
                )";

            //echo $query;

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':name'             => $name,
                                    ':age'              => $age,
                                    ':yearsOfExperience'=> $yearsOfExp,
                                    ':email'            => $email,
                                    ':username'         => $username,
                                    ':password'         => $password,
                                    ':entityID'         => $entityID,
                                    ':roleID'           => $roleID )))
            {
                return true;
                //return $dal->get_pdo()->lastInsertId();
            }

            return false;
        }
        // =====================================================================
        //  UPDATE/EDIT FUNCTIONS
        // =====================================================================
        static public function UpdateUserData($ID, $name, $email, $age, $exp, $entity, $role)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   UPDATE " .
                    $dal->get_dbschema().".user
                SET
                    name                = :name,
                    email               = :email,
                    age                 = :age,
                    yearsOfExperience   = :exp,
                    entityID            = :entity,
                    roleID              = :role
                WHERE
                    ID = :ID";

            $stmt = $dal->get_pdo()->prepare($query);

            if ($stmt->execute(array(':ID'      => $ID,
                                     ':name'    => $name,
                                     ':email'   => $email,
                                     ':age'     => $age,
                                     ':exp'     => $exp,
                                     ':entity'  => $entity,
                                     ':role'    => $role )))
            {
                return true;
            }
        }

        static public function UpdateRequests($requestID, $accept)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   UPDATE " .
                    $dal->get_dbschema().".request
                SET
                    accepted        = :accept
                WHERE
                    ID = :ID";

            $stmt = $dal->get_pdo()->prepare($query);

            if ($stmt->execute(array(':ID'      => $requestID,
                                     ':accept'  => $accept)))
            {
                if ($accept == true)
                {
                    $query2 =
                        "   SELECT
                                email
                            FROM " .
                                $dal->get_dbschema().".request
                            WHERE
                                ID = :ID";

                    $stmt2 = $dal->get_pdo()->prepare($query2);

                    if ($stmt2->execute(array(':ID'     => $requestID)))
                    {
                        $resultArray = $stmt2->fetchAll(PDO::FETCH_ASSOC);
                        return $resultArray;
                    }
                }
            }
        }

        // =====================================================================
        //  GETTER FUNCTIONS
        // =====================================================================
        static public function GetUserByID($ID)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   SELECT
                    u.ID, u.name, u.age, u.yearsOfExperience,
                    u.email,

                    u.roleID,

                    e.designation as entityName, e.ID as entityID
                FROM " .
                    $dal->get_dbschema().".user AS u
                INNER JOIN ".
                    $dal->get_dbschema().".entity AS e
                ON
                    e.ID = u.entityID
                WHERE
                    u.ID = :ID";

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':ID' => $ID)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);
                return $resultArray;
            }

            return false;
        }

        static public function GetUsers($entityID)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   SELECT
                    u.ID, u.name, u.age, u.yearsOfExperience,
                    u.email, u.password, u.username, u.roleID,

                    e.designation as entityName, e.ID as entityID
                FROM " .
                    $dal->get_dbschema().".user AS u
                INNER JOIN ".
                    $dal->get_dbschema().".entity AS e
                ON
                    e.ID = u.entityID
                WHERE
                    u.entityID = :entityID";

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':entityID' => $entityID)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);
                return $resultArray;
            }

            return false;
        }

        static public function GetAllUsers()
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   SELECT
                    u.ID, u.name, u.age, u.yearsOfExperience,
                    u.email, u.password, u.username, u.roleID,

                    e.designation as entityName, e.ID as entityID
                FROM " .
                    $dal->get_dbschema().".user AS u
                INNER JOIN ".
                    $dal->get_dbschema().".entity AS e
                ON
                    e.ID = u.entityID";

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute())
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);
                return $resultArray;
            }

            return false;
        }

        static public function CheckUsernameExists($username)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   SELECT
                    *
                FROM " .
                    $dal->get_dbschema().".user
                WHERE
                    username = :username";

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':username' => $username)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);

                if (!empty($resultArray))
                {
                    return true;
                }

            }

            return false;
        }

        static public function CheckLogin($password, $username)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   SELECT
                    u.ID, u.email, u.name, u.username, u.roleID,

                    u.entityID,

                    e.designation AS entityName
                FROM " .
                    $dal->get_dbschema().".user  AS u
                INNER JOIN " .
                    $dal->get_dbschema().".entity AS e
                ON
                    e.ID = u.entityID
                WHERE
                    u.password = :pass
                AND
                    u.username = :username";

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':pass'     => $password,
                                    ':username' => $username)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);

                if (!empty($resultArray))
                {
                    return $resultArray[0];
                }

            }

            return false;
        }

        static public function GetPatientByLocalID($ID)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   SELECT
                    p.ID AS patientID
                FROM " .
                    $dal->get_dbschema().".patient_ehr AS p
                WHERE
                    localID = :ID";

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':ID' => $ID)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);
                if (!empty($resultArray))
                    return $resultArray[0];
                else
                    return false;
            }

            return false;
        }

        static public function GetRequestInfo($ID)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   SELECT
                    email, name, institution, requestDate
                FROM " .
                    $dal->get_dbschema().".request
                WHERE
                    ID = :ID";

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':ID' => $ID)))
            {
                $resultArray = $stmt->fetch(PDO::FETCH_ASSOC);
                return $resultArray;
            }

            return false;
        }

        static public function CountPendingRequests()
        {
            $dal = DataAccessLayer::Instance();

            $query =
                "   SELECT count(*)
                    FROM " .
                        $dal->get_dbschema().".request
                    WHERE
                        accepted is NULL";


            $stmt = $dal->get_pdo()->prepare($query);


            if($stmt->execute())
            {
                $resultArray = $stmt->fetch(PDO::FETCH_ASSOC);

                return $resultArray['count(*)'];
            }

            return false;
        }

        static public function CountPendingRequestsByEntity($entity)
        {
            $dal = DataAccessLayer::Instance();

            $query =
                "   SELECT count(*)
                    FROM " .
                        $dal->get_dbschema().".request
                    WHERE
                        accepted is NULL
                    AND
                        institution = :entity";


            $stmt = $dal->get_pdo()->prepare($query);


            if($stmt->execute(array(':entity' => $entity)))
            {
                $resultArray = $stmt->fetch(PDO::FETCH_ASSOC);

                return $resultArray['count(*)'];
            }

            return false;
        }

        static public function GetAllPendingRequests()
        {
            $dal = DataAccessLayer::Instance();

            $query =
                "   SELECT
                        *
                    FROM " .
                        $dal->get_dbschema().".request
                    WHERE
                        accepted is NULL";


            $stmt = $dal->get_pdo()->prepare($query);


            if($stmt->execute())
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);

                return $resultArray;
            }

            return false;
        }

        static public function GetAllPendingRequestsByEntity($entity)
        {
            $dal = DataAccessLayer::Instance();

            $query =
                "   SELECT
                        *
                    FROM " .
                        $dal->get_dbschema().".request
                    WHERE
                        accepted is NULL
                    AND
                        institution = :entity";


            $stmt = $dal->get_pdo()->prepare($query);


            if($stmt->execute(array(':entity' => $entity)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);

                return $resultArray;
            }

            return false;
        }

        static public function GetEntity($ID)
        {
            $dal = DataAccessLayer::Instance();

            $query =
                "   SELECT
                        institution
                    FROM " .
                        $dal->get_dbschema().".request
                    WHERE
                        ID = :ID";


            $stmt = $dal->get_pdo()->prepare($query);


            if($stmt->execute(array(':ID' => $ID)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);

                return $resultArray[0];
            }

            return false;
        }

        static public function GetPatientData($examID)
        {
            $dal = DataAccessLayer::Instance();

            $query =
                "   SELECT
                        p.initials, mg.name AS gender,

                        p.smoke, p.drinks, p.birthDate,
                        mc.cityName AS city, mct.countryName AS country, p.localID,
                        e.addressHowLongYears, p.ID
                    FROM " .
                        $dal->get_dbschema().".patient_ehr AS p
                    INNER JOIN " .
                        $dal->get_dbschema().".exam AS e
                    ON
                        e.patientID = p.ID
                    INNER JOIN " .
                        $dal->get_dbschema().".mf_gender AS mg
                    ON
                        mg.ID = p.gender
                    LEFT OUTER JOIN " .
                        $dal->get_dbschema().".mf_city AS mc
                    ON
                        mc.ID = p.birthCity
                    LEFT OUTER JOIN " .
                        $dal->get_dbschema().".mf_country AS mct
                    ON
                        mct.ID = p.birthCountry
                    WHERE
                        e.ID = :ID";

            $stmt = $dal->get_pdo()->prepare($query);


            if($stmt->execute(array(':ID' => $examID)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);

                return $resultArray[0];
            }

            return false;
        }

        static public function GetAddress($examID)
        {
            //var_dump($examID);
            $dal = DataAccessLayer::Instance();

            $query =
                "   SELECT
                        mc.cityName AS city,
                        mct.countryName AS country
                    FROM " .
                        $dal->get_dbschema().".exam AS e
                    LEFT OUTER JOIN " .
                        $dal->get_dbschema().".mf_city AS mc
                    ON
                        mc.ID = e.addressCity
                    LEFT OUTER JOIN " .
                        $dal->get_dbschema().".mf_country AS mct
                    ON
                        mct.ID = e.addressCountry
                    WHERE
                        e.ID = :ID";

            $stmt = $dal->get_pdo()->prepare($query);


            if($stmt->execute(array(':ID' => $examID)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);
                //var_dump($resultArray);
                return $resultArray[0];
            }

            return false;
        }

        static public function GetEmailAdminEntity($entity)
        {
            $dal = DataAccessLayer::Instance();

            $query =
                "   SELECT
                        u.email
                    FROM " .
                        $dal->get_dbschema().".user AS u
                    INNER JOIN " .
                        $dal->get_dbschema().".entity AS e
                    ON
                        e.ID = u.entityID
                    WHERE
                        e.designation = :desc
                    AND
                        u.roleID = 2";


            $stmt = $dal->get_pdo()->prepare($query);


            if($stmt->execute(array(':desc' => $entity)))
            {
                $resultArray = $stmt->fetchAll(PDO::FETCH_ASSOC);

                return $resultArray[0]['email'];
            }

            return false;
        }

        // =====================================================================
        //  DELETE FUNCTIONS
        // =====================================================================
        static public function DeleteUser($userID)
        {
            $dal = DataAccessLayer::Instance();

            $query =
            "   DELETE FROM " .
                    $dal->get_dbschema().".user
                WHERE
                    ID = :ID";

            $stmt = $dal->get_pdo()->prepare($query);

            if($stmt->execute(array(':ID'   => $userID)))
            {
                return true;
            }

            return false;
        }
    }
?>