CMS-CSE/core/class/user.class.php

<?php

class user {
    
    public function __construct() {
    }

    public static function getUserById(int $_id) {
        // Récupération des données de l'excel au format Json
        db::query("SELECT "
                . "" . DB_T_USER . ".id AS id, "
                . "" . DB_T_USER . ".email ,"
                . "" . DB_T_USER . ".prenom, "
                . "" . DB_T_USER . ".nom, "
                . "" . DB_T_USER . ".cree, "
                . "" . DB_T_USER . ".last_connect, "
                . "" . DB_T_USER . ".googleAuthenticator, "
                . "" . DB_T_USER . ".actif, "
                . "" . DB_T_USER . ".deleted, "
                . "" . DB_T_USER . ".id_type, "
                . "" . DB_T_TYPE_USER . ".type "
                . "FROM " . DB_T_USER . " "
                . "INNER JOIN " . DB_T_TYPE_USER . " ON " . DB_T_USER . ".id_type = " . DB_T_TYPE_USER . ".id "
                . "WHERE " . DB_T_USER . ".id = :id");
        db::bind(':id', $_id);
        $return = db::single();
        $return["tags"] = self::getTags($_id);
        return $return;
    }

    public static function getUsers() {
        // Récupération des données de l'excel au format Json
        db::query("SELECT "
                . "" . DB_T_USER . ".id, "
                . "" . DB_T_USER . ".email, "
                . "" . DB_T_USER . ".prenom, "
                . "" . DB_T_USER . ".nom, "
                . "" . DB_T_USER . ".cree, "
                . "" . DB_T_USER . ".last_connect, "
                . "" . DB_T_USER . ".googleAuthenticator, "
                . "" . DB_T_USER . ".actif, "
                . "" . DB_T_USER . ".id_type, "
                . "" . DB_T_TYPE_USER . ".type "
                . "FROM " . DB_T_USER . " "
                . "INNER JOIN " . DB_T_TYPE_USER . " ON " . DB_T_USER . ".id_type = " . DB_T_TYPE_USER . ".id "
                . "WHERE " . DB_T_USER . ".deleted = 0");
        $return =  db::resultset();

        foreach ($return as $key => $users) {
            $return[$key] = $users;
            $return[$key]["tags"] = self::getTags($users["id"]);
        }

        return $return;
    }

    public static function getNameById(int $_id) {
        db::query("SELECT "
                . "CONCAT (" . DB_T_USER . ".prenom, ' ', " . DB_T_USER . ".nom) AS 'name' "
                . "FROM " . DB_T_USER . " "
                . "WHERE " . DB_T_USER . ".id = :id");
        db::bind(':id', $_id);
        return db::single()["name"];
    }

    public static function getMyGoogleAuthenticator(int $_id){
        db::query("SELECT "
                . "" . DB_T_USER . ".googleAuthenticatorSecret "
                . "FROM " . DB_T_USER . " "
                . "WHERE " . DB_T_USER . ".id = :id");
        db::bind(':id', $_id);
        return db::single()["googleAuthenticatorSecret"];
    }

    public static function checkGoogleAuthenticator(string $_email){
        db::query("SELECT "
                . "" . DB_T_USER . ".googleAuthenticator "
                . "FROM " . DB_T_USER . " "
                . "WHERE " . DB_T_USER . ".email = :email");
        db::bind(':email', $_email);
        $return = db::single();
        return (isset($return["googleAuthenticator"])) ? $return["googleAuthenticator"] : 0;
    }

    private static function insertJWT($_id_user, $_jwt){
        self::deleteJWTbyUSer($_id_user);

        db::query("INSERT INTO " . DB_T_JWT . " (id_user, md5, jwt) VALUES (:id_user, :md5, :jwt)");
        db::bind(':id_user', $_id_user);
        db::bind(':md5', md5($_jwt));
        db::bind(':jwt', $_jwt);
        try {
            db::execute();
            return TRUE;
        } catch (Exception $ex) {
            return FALSE;
        }
    }

    public static function deleteJWTbyUSer($_id_user){
        db::query("DELETE FROM " . DB_T_JWT . " WHERE id_user = :id_user");
        db::bind(':id_user', $_id_user);
        try {
            db::execute();
            return TRUE;
        } catch (Exception $ex) {
            return FALSE;
        }
    }

    public static function updateJWTbyMd5($_md5, $_jwt){
        db::query("UPDATE " . DB_T_JWT . " SET jwt = :jwt, md5 = :newmd5 WHERE md5 = :md5");
        db::bind(':md5', $_md5);
        db::bind(':jwt', $_jwt);
        db::bind(':newmd5', md5($_jwt));
        try {
            db::execute();
            return TRUE;
        } catch (Exception $ex) {
            return FALSE;
        }
    }

    public static function getInfosByJWT($_jwt){
        db::query("SELECT id_user, creer FROM " . DB_T_JWT . " WHERE md5 = :md5");
        db::bind(':md5', md5($_jwt));
        $row = db::single();

        if(isset($row["id_user"])){
            return $row;
        } else {
            return FALSE;
        }
    }

    public static function authenticator(array $_input)
    {
        db::query("SELECT id, email, password, prenom, nom, id_type, googleAuthenticator, googleAuthenticatorSecret, actif FROM " . DB_T_USER . " WHERE email = :email AND deleted = 0");
        db::bind(':email', $_input["email"]);
        $row = db::single();

        if (isset($row["id"])) {
            if ($row["actif"] == 0) {
                return [
                    "status" => "error",
                    "type" => "actif"
                ];
            } elseif (isset($_input["password"]) and md5($_input["password"]) == $row["password"]) {
                return [
                    "status" => "success",
                    "type" => "authent",
                    "id" => $row["id"],
                    "prenom" => $row["prenom"],
                    "nom" => $row["nom"],
                    "googleAuthenticator" => $row["googleAuthenticator"],
                    "googleAuthenticatorSecret" => $row["googleAuthenticatorSecret"],
                    "idType" => $row["id_type"],
                    "email" => $row["email"],
                    "actif" => $row["actif"],
                    "iat" => time(),
                    "exp" => time() + JWT_DURATION
                ];
            } else {
                return [
                    "status" => "error",
                    "type" => "authent"
                ];
            }
        } else {
            return [
                "status" => "error",
                "type" => "unknown"
            ];
        }
    }

    public static function connect(array $_input) {

        $connect = jwt::authenticate($_input);

        if ($connect["googleAuthenticator"] == 1 and DOMAIN_CONTROL != $_SERVER['SERVER_NAME']) {
            if (googleAuthenticator::verifyCode($connect["googleAuthenticatorSecret"], $_input["authenticator"], 1) == FALSE) {
                $connect["id"] = NULL;
            }
        }

        if (isset($connect["id"])) {
            if ($connect["status"] == "success") {
                $_SESSION["user"] = array(
                    "apiSession" => $connect["session_id"],
                    "id" => $connect["id"],
                    "prenom" => $connect["prenom"],
                    "nom" => $connect["nom"],
                    "googleAuthenticator" => $connect["googleAuthenticator"],
                    "idType" => $connect["idType"],
                    "email" => $connect["email"],
                    "actif" => $connect["actif"],
                    "token" => $connect["token"]
                );
                self::updateLastConnect($connect["id"]);
                self::insertJWT($connect["id"], $connect["token"]);
                return TRUE;
            } else {
                if ($connect["type"] == "actif") {
                    alert::recError("Votre compte est désactivé");
                    return FALSE;
                } elseif ($connect["type"] == "authent") {
                    alert::recError("Erreur d'authentification");
                    return FALSE;
                } elseif ($connect["type"] == "unknown") {
                    alert::recError("Erreur d'authentification");
                    return FALSE;
                }
            }
        } else {
            alert::recError("Erreur d'authentification");
            return FALSE;
        }
    }
    
    private static function updateLastConnect(int $_id){
        db::query("UPDATE " . DB_T_USER . " SET `last_connect` = CURRENT_TIMESTAMP() WHERE id = :id");
        db::bind(':id', $_id);
        db::execute();
    }
    
    public static function add_user(array $_input){
        db::query("INSERT INTO " . DB_T_USER . " "
                . "(email, password, googleAuthenticator, googleAuthenticatorSecret, prenom, nom, id_type, actif) "
                . "VALUES (:email, :password, :googleAuthenticator, :googleAuthenticatorSecret, :prenom, :nom, :id_type, :actif)");
        db::bind(':email', $_input["email"]);
        db::bind(':password', md5($_input["password"]));
        db::bind(':prenom', $_input["prenom"]);
        db::bind(':nom', $_input["nom"]);
        db::bind(':googleAuthenticator', $_input["googleAuthenticator"]);
        db::bind(':googleAuthenticatorSecret', googleAuthenticator::createSecret());
        db::bind(':id_type', $_input["id_type"]);
        db::bind(':actif', $_input["actif"]);

        try {
            db::execute();

            $tags = tags::textToId($_input["tags"], 1); 
            self::addTags(db::lastInsertId(), $tags);
            alert::recSuccess("La création a bien été prise en compte");
        } catch (Exception $ex) {
            alert::recError("Erreur lors de la création de l'utilisateur");
            header("Location: /add-user.html");
            exit();
        }
    }
    
    public static function lastUser(){
        db::query("SELECT MAX(id) AS id FROM ". DB_T_USER);
        return db::single()["id"];
    }
    
    public static function maj_user(array $_input){

        if($_input["password"] != ""){
            db::query("UPDATE " . DB_T_USER . " SET password = :password WHERE id = :id");
            db::bind(':password', md5($_input["password"]));
            db::bind(':id', $_input["id"]);
            try {
                db::execute();
            } catch (Exception $ex) {
                alert::recError("Erreur lors de la modification du mot de passe");
                header("Location: /user-" . $_input["id"] .".html");
                exit();
            }
        }

        if(self::getMyGoogleAuthenticator($_input["id"]) == NULL){
            db::query("UPDATE " . DB_T_USER . " SET googleAuthenticatorSecret = :googleAuthenticatorSecret WHERE id = :id");
            db::bind(':googleAuthenticatorSecret', googleAuthenticator::createSecret());
            db::bind(':id', $_input["id"]);
            try {
                db::execute();
            } catch (Exception $ex) {
                alert::recError("Erreur lors de la création du token de Google Authenticator");
                header("Location: /user-" . $_input["id"] .".html");
                exit();
            }
        }

        $tags = tags::textToId($_input["tags"], 1); 
        self::addTags($_input["id"], $tags);

        db::query("UPDATE " . DB_T_USER . " SET 
                    email = :email, 
                    prenom = :prenom, 
                    nom = :nom, 
                    id_type = :id_type, 
                    googleAuthenticator = :googleAuthenticator, 
                    actif = :actif
                    WHERE id = :id");
        db::bind(':email', $_input["email"]);
        db::bind(':prenom', $_input["prenom"]);
        db::bind(':nom', $_input["nom"]);
        db::bind(':googleAuthenticator', $_input["googleAuthenticator"]);
        db::bind(':id_type', $_input["id_type"]);
        db::bind(':actif', $_input["actif"]);
        db::bind(':id', $_input["id"]);
        
        try {
            db::execute();
            alert::recSuccess("La modification a bien été prise en compte");
        } catch (Exception $ex) {
            alert::recError("Erreur lors de la modification de l'utilisateur");
            header("Location: /user-" . $_input["id"] . ".html");
            exit();
        }
    }

    static public function getTags(float $_idUser){
        db::query("SELECT "
            . "" . DB_T_TAGS . ".label "
            . "FROM " . DB_T_USER_TAGS . " "
            . "INNER JOIN " . DB_T_TAGS . " ON " . DB_T_TAGS . ".id = " . DB_T_USER_TAGS . ".id_tags "
            . "WHERE " . DB_T_USER_TAGS . ".id_user = :id "
            . "ORDER BY " . DB_T_USER_TAGS . ".creer");
        db::bind(':id', $_idUser);
        $tmp = db::resultset();

        if(isset($tmp[0])){
            $return = NULL;
            foreach ($tmp as $value) {
                $return .= $value["label"].",";
            }
    
            $return = substr($return, 0, -1);
            return $return;
        } else {
            return NULL;
        }
    }  

    static public function getIdTags(float $_idUser){
        db::query("SELECT "
            . "" . DB_T_USER_TAGS . ".id_tags "
            . "FROM " . DB_T_USER_TAGS . " "
            . "WHERE " . DB_T_USER_TAGS . ".id_user = :id "
            . "ORDER BY " . DB_T_USER_TAGS . ".creer");
        db::bind(':id', $_idUser);
        $tmp = db::resultset();
        if(isset($tmp[0])){
            $return = [];
            foreach ($tmp as $value) {
                $return[] = $value["id_tags"];
            }
            return $return;
        } else {
            return NULL;
        }
    }

    private static function addTags(float $_idUser, string $_tags = NULL)
    {
        db::query("DELETE FROM " . DB_T_USER_TAGS . " WHERE id_user = :id_user");
        db::bind(':id_user', $_idUser);
        db::execute();

        if($_tags != NULL){ 
            $tags = explode(",", $_tags);

            $sqlMaj = "";
            foreach ($tags as $tag) {
                $sqlMaj .= " (:id_user, ".$tag."),";
            }

            $sqlMaj = substr($sqlMaj, 0, -1);

            db::query("INSERT INTO " . DB_T_USER_TAGS . " (id_user, id_tags) VALUES" . $sqlMaj);
            db::bind(':id_user', $_idUser);
            try {
                db::execute();
                return TRUE;
            } catch (Exception $ex) {
                return FALSE;
            }
        }
    }
    
    public static function deleteUser(int $_id){
        db::query("UPDATE " . DB_T_USER . " SET deleted = 1 WHERE id = :id");
        db::bind(':id', $_id);
        try {
            db::execute();
        } catch (Exception $ex) {
            alert::recError("Erreur lors de la suppression");
            header("Location: /user-" . $_id .".html");
            exit();
        }
    }

    public static function restoreUser(int $_id){
        db::query("UPDATE " . DB_T_USER . " SET deleted = 0 WHERE id = :id");
        db::bind(':id', $_id);
        try {
            db::execute();
        } catch (Exception $ex) {
            alert::recError("Erreur lors de la restauration");
            header("Location: /user-" . $_id .".html");
            exit();
        }
    }

    static public function checkSecur(){
        db::query("SELECT googleAuthenticator FROM " . DB_T_USER . " WHERE id = :id");
        db::bind(':id', session::getId());
        return db::single()["googleAuthenticator"] == 1 ? TRUE : FALSE;
    }

    static public function printIsSecur(){
        if(ALERT_AUTHENTICATOR == TRUE){
            $_SESSION["CALLOUT"] ??= 0;
            if(self::checkSecur() == FALSE AND $_SESSION["CALLOUT"] < NB_ALERT_AUTHENTICATOR){
                $callout = [
                    "type" => "danger",
                    "size" => "tiny",
                    "p" => "Pour sécuriser l'accès au CMS, il est fortement recommandé d'activer la double authentification (Google Authenticator) sur votre profil. Pour l'activer sur votre profil en <a href=\"/user.html\">cliquant ici</a>.",
                ];
                callout::print($callout);
                $_SESSION["CALLOUT"]++;
            }
        }
    }

}