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

<?php

class banque
{

    public static function getAll()
    {
        db::query("SELECT "
            . "* "
            . "FROM " . DB_T_BANQUE_COMPTES );
            return db::resultset();
    }

    public static function update(){
        
        if(core::ifPost("solde")){
                $sql = ", solde = :solde, solde_date = :solde_date, import = :import ";
            } else {
                $sql = "";
            }
        
        db::query("UPDATE " . DB_T_BANQUE_COMPTES . " SET "
                . "label = :label, "
                . "compte = :compte, "
                . "commentaire = :commentaire, "
                . "icon = :icon "
                . $sql
                . "WHERE id = :id");
        
            db::bind(':label', core::getPost("label"));
            db::bind(':compte', core::getPost("compte"));
            db::bind(':commentaire', core::getPost("commentaire"));
            db::bind(':icon', core::getPost("icon"));
            db::bind(':id', core::getPost("id"));

        if(!empty(core::getPost("solde"))){
            db::bind(':solde', core::getPost("solde"));
            db::bind(':solde_date', core::getPost("solde_date"));
            db::bind(':import', core::getPost("import"));
        }
        
        try {
                db::execute();
                alert::recSuccess("Compte mis à jour avec succès");
                return TRUE;
            } catch (Exception $ex) {
                alert::recError("Erreur de mise à jour du Compte : " . $ex);
                return FALSE;
            }
    }

    public static function add(){
        
        db::query("INSERT INTO " . DB_T_BANQUE_COMPTES . " (label, compte, commentaire, solde, solde_date, icon, import) VALUES (:label, :compte, :commentaire, :solde, :solde_date, :icon, :import)");
        db::bind(':label', core::getPost("label"));
        db::bind(':compte', core::getPost("compte"));
        db::bind(':commentaire', core::getPost("commentaire"));
        db::bind(':solde', core::getPost("solde"));
        db::bind(':solde_date', core::getPost("solde_date"));
        db::bind(':icon', core::getPost("icon"));
        db::bind(':import', core::getPost("import"));

        try {
                db::execute();
                alert::recSuccess("Compte créé avec succès");
                return db::lastInsertId();
            } catch (Exception $ex) {
                alert::recError("Erreur de création du Compte : " . $ex);
                return FALSE;
            }
    }

    public static function menu(){
        banque::getAll();
        foreach (self::getAll() as $value) {
            core::elementMenu("compte-" . $value["id"], "/compte-" . $value["id"] . ".html", $value["label"], NULL, $value["icon"]);
        }
    }

    public static function printFormCompte(?array $_array = NULL) {
        if($_array == NULL){
            $_array = ["id" => "add", "label" => NULL, "compte" => NULL, "solde" => NULL, "solde_date" => NULL, "icon" => NULL, "commentaire" => NULL];
            $txtSubmit = "Enregistrer un nouveau compte";
        } else {
            $txtSubmit = "Modifier ce compte";
        }

        $nbLines = self::countLines($_array["id"]);
        $protected = ($_array["solde"] != NULL AND $nbLines > 0) ? 1 : 0;
        if($protected == 1) { 
            $txtProtected = ($nbLines > 1) ? "Vous ne pouvez pas modifier ces données car cela entraînerait une désynchronisation des " . $nbLines . " lignes associées à ce compte. Pour se faire vous devez supprimer toutes les lignes associées à ce compte." : "Vous ne pouvez pas modifier ces données car cela entraînerait une désynchronisation de la ligne associée à ce compte. Pour se faire vous devez supprimer la ligne associée à ce compte.";
        }

        echo "<form method=\"post\" action=\"/submit.php\">";
        echo "<input type=\"hidden\" name=\"from\" value=\"parametres-compte-edit\">";
        echo "<input type=\"hidden\" name=\"id\" value=\"" . $_array["id"] . "\">";
        echo "<div class=\"form-group\"><label>Nom du compte</label><input type=\"text\" class=\"form-control form-control-sm\" name=\"label\" value=\"" . $_array["label"] . "\" required></div><br />";
        echo "<div class=\"form-group\"><label>Numéro de compte</label><input type=\"text\" class=\"form-control form-control-sm\" name=\"compte\" value=\"" . $_array["compte"] . "\"></div><br />";
        
        if($protected == 1) { 
            echo "<fieldset class=\"border\" style=\"border-color: orange!important; padding: 15px;\">";
            echo "<legend class=\"float-none w-auto\" style=\"font-size: 0.9rem; color: orange!important; padding: 0 5px;\">Protégé</legend>"; 
            echo "<div class=\"alert alert-warning\" role=\"alert\">" . $txtProtected . "</div>";
        }

        echo "<div class=\"form-group\"><label>Solde initial</label><input type=\"number\" class=\"form-control form-control-sm\" name=\"solde\" value=\"" . $_array["solde"] . "\"";
        echo ($protected == 1) ? " disabled" : " required";
        echo "></div><br />";

        echo "<div class=\"form-group\"><label>Date du solde initial</label><input type=\"date\" class=\"form-control form-control-sm\" name=\"solde_date\" value=\"" . $_array["solde_date"] . "\"";
        echo ($protected == 1) ? " disabled" : " required";
        echo "></div><br />"; 

        echo "<div class=\"form-group\"><label>Mode d'importation des données</label><select name=\"import\" class=\"form-select\"";
        echo ($protected == 1) ? " disabled" : " required";
        echo ">";
        echo "<option value=\"csv\"" . (($_array["import"] == "csv") ? " selected" : NULL) . ">Importer des données depuis le CSV de Crédit Mutuel</option>";
        echo "<option value=\"manuel\"" . (($_array["import"] == "manuel") ? " selected" : NULL) . ">Ajouter des lignes manuellement</option>";
        echo "</select></div><br />";

        if($protected == 1) { 
            echo "</fieldset><br />";
        }

        echo "<div class=\"form-group\"><label>Icône</label><select name=\"icon\" class=\"form-select\">";
        echo "<option value=\"courant\"" . (($_array["icon"] == "courant") ? " selected" : NULL) . ">Icône liée à un compte courant</option>";
        echo "<option value=\"epargne\"" . (($_array["icon"] == "epargne") ? " selected" : NULL) . ">Icône liée à un compte d'épargne</option>";
        echo "</select></div><br />";
        echo "<div class=\"form-group\"><label>Commentaire</label><input type=\"text\" class=\"form-control form-control-sm\" name=\"commentaire\" value=\"" . $_array["commentaire"] . "\"></div><br />";
        echo "<button class=\"btn btn-primary btn-lg\" style=\"width: 100%\" type=\"submit\">" . $txtSubmit . "</button>";
        echo "</form>";
    }

    public static function countLines(int|string $_id)
    {
        if($_id == "add"){
            return 0;
        }

        db::query("SELECT "
            . "COUNT(id_compte) AS nb "
            . "FROM " . DB_T_BANQUE_LIGNES . " "
            . "WHERE " . DB_T_BANQUE_LIGNES . ".id_compte = :id");
            db::bind(':id', $_id);
            $nb = db::single()["nb"];
            return (!empty($nb)) ? $nb : 0;
    }
    
    public static function lastArrayRecord(int $_id)
    {
        db::query("SELECT "
            . "* "
            . "FROM " . DB_T_BANQUE_LIGNES . " "
            . "WHERE " . DB_T_BANQUE_LIGNES . ".id_compte = :id "
            . "ORDER BY creer DESC "
            . "LIMIT 1");
            db::bind(':id', $_id);
            return db::single();
    }
    
    public static function lastRecord(int $_id)
    {
        db::query("SELECT "
            . "creer "
            . "FROM " . DB_T_BANQUE_LIGNES . " "
            . "WHERE " . DB_T_BANQUE_LIGNES . ".id_compte = :id "
            . "ORDER BY creer DESC "
            . "LIMIT 1");
            db::bind(':id', $_id);
            $last = db::single();
            return (!empty($last["creer"])) ? $last["creer"] : NULL;
    }
    
    public static function getInitial(int $_id)
    {
        $tmp = self::getInitialCompte($_id );
        $return = array(
            "num" => 0,
            "id" => 0,
            "import" => $tmp["import"],
            "date" => $tmp["solde_date"],
            "label" => "SOLDE CREDITEUR AU " . core::convertDate($tmp["solde_date"], FALSE),
            "debit" => 0,
            "credit" => 0,
            "solde" => $tmp["solde"],
        );
        return $return;
    }

    public static function getInitialCompte(int $_id)
    {
        db::query("SELECT "
            . "* "
            . "FROM " . DB_T_BANQUE_COMPTES . " "
            . "WHERE " . DB_T_BANQUE_COMPTES . ".id = :id");
            db::bind(':id', $_id);
            return db::single();
    }
    
    public static function getLignes(int $_id)
    {
        db::query("SELECT "
            . "ROW_NUMBER() OVER (ORDER BY id) num, "
            . "" . DB_T_BANQUE_LIGNES . ".id, "
            . "" . DB_T_BANQUE_LIGNES . ".date, "
            . "" . DB_T_BANQUE_LIGNES . ".label, "
            . "" . DB_T_BANQUE_LIGNES . ".debit, "
            . "" . DB_T_BANQUE_LIGNES . ".credit, "
            . "" . DB_T_BANQUE_LIGNES . ".solde "
            . "FROM " . DB_T_BANQUE_LIGNES . " "
            . "WHERE " . DB_T_BANQUE_LIGNES . ".id_compte = :id_compte");
            db::bind(':id_compte', $_id);
            return db::resultset();
    }

    public static function getEtatCompte(int $_id)
    {
        db::query("SELECT "
            . "" . DB_T_BANQUE_LIGNES . ".solde, "
            . "" . DB_T_BANQUE_LIGNES . ".date "
            . "FROM " . DB_T_BANQUE_LIGNES . " "
            . "WHERE " . DB_T_BANQUE_LIGNES . ".id_compte = :id_compte "
            . "ORDER BY " . DB_T_BANQUE_LIGNES . ".id DESC");
            db::bind(':id_compte', $_id);
            return db::single();
    }

    public static function getHistoriqueCSV()
    {
        db::query("SELECT "
            . "" . DB_T_FILES . ".id, "
            . "" . DB_T_FILES . ".name, "
            . "" . DB_T_FILES . ".size, "
            . "" . DB_T_FILES . ".creer, "
            . "CONCAT (" . DB_T_USER . ".prenom, ' ', " . DB_T_USER . ".nom) AS 'user' "
            . "FROM " . DB_T_FILES . " "
            . "INNER JOIN " . DB_T_USER . " ON " . DB_T_FILES . ".id_user = " . DB_T_USER . ".id "
            . "INNER JOIN " . DB_T_BANQUE_CSV . " ON " . DB_T_BANQUE_CSV . ".md5 = " . DB_T_FILES . ".id");
        return db::resultset();
    }

    public static function addMd5CSV(string $_md5, int $_id_compte)
    {
        db::query("INSERT INTO " . DB_T_BANQUE_CSV . " (md5, id_compte) VALUES (:md5, :id_compte)");
        db::bind(':md5', $_md5);
        db::bind(':id_compte', $_id_compte);

        try {
            db::execute();
            return TRUE;
        } catch (Exception $ex) {
            return FALSE;
        }
    }

    public static function findMd5(string $_md5)
    {
        db::query("SELECT "
            . "id "
            . "FROM " . DB_T_BANQUE_LIGNES . " "
            . "WHERE " . DB_T_BANQUE_LIGNES . ".md5 = :md5");
            db::bind(':md5', $_md5);
            if(empty(db::single()["id"])){
                return FALSE;
            } else {
                return TRUE;
            }

            return db::single()["id"];
    }

    public static function recordLigne(array $_array, int $_id){
        if(self::findMd5($_array["md5"]) == FALSE){
            
            db::query("INSERT INTO " . DB_T_BANQUE_LIGNES . " (id_compte, date, label, debit, credit, solde, md5) VALUES (:id_compte, :date, :label, :debit, :credit, :solde, :md5)");
            db::bind(':id_compte', $_id);
            db::bind(':date', $_array["date"]);
            db::bind(':label', $_array["label"]);
            db::bind(':debit', $_array["debit"]);
            db::bind(':credit', $_array["credit"]);
            db::bind(':solde', $_array["solde"]);
            db::bind(':md5', $_array["md5"]);

            try {
                db::execute();
                return TRUE;
            } catch (Exception $ex) {
                return FALSE;
            }
        }

        return FALSE;
    }

    public static function recordLignes(array $_array, int $_id){
        $z = 0;
        foreach ($_array as $values) {
            if(self::recordLigne($values, $_id) == TRUE){
                $z++;
            }
        }
        return $z;
    }

    public static function readCompte(array $_temp) {
        $data = [];

        if (!file_exists($_temp['tmp_name']) || !is_readable($_temp['tmp_name'])) {
            return false;
        }

        if (($handle = fopen($_temp['tmp_name'], 'r')) !== false) {
            $header = NULL;
            $a = -1;
            while (($row = fgetcsv($handle, 1000, ";")) !== false) {
                $a++;

                $convertedRow = array_map(function ($field) {
                    return mb_convert_encoding($field, 'UTF-8', 'ISO-8859-1');
                }, $row);

                if ($header == NULL) {
                    $header = TRUE;
                    if(
                        $convertedRow[0] != "Date" OR 
                        $convertedRow[1] != "Date de valeur" OR
                        $convertedRow[2] != "Débit" OR
                        $convertedRow[3] != "Crédit" OR
                        $convertedRow[4] != "Libellé" OR
                        $convertedRow[5] != "Solde"
                        ){
                            ($convertedRow[0] != "Date") ? alert::recError("La 1er colonne doit se nommer Date") : "";
                            ($convertedRow[1] != "Date de valeur") ? alert::recError("La 2em colonne doit se nommer Date de valeur") : "";
                            ($convertedRow[2] != "Débit") ? alert::recError("La 3em colonne doit se nommer Débit") : "";
                            ($convertedRow[3] != "Crédit") ? alert::recError("La 4em colonne doit se nommer Crédit") : "";
                            ($convertedRow[4] != "Libellé") ? alert::recError("La 5em colonne doit se nommer Libellé") : "";
                            ($convertedRow[5] != "Solde") ? alert::recError("La 6em colonne doit se nommer Solde") : "";
                            alert::recError("Le fichier " . $_temp['name'] . " n'est pas un CSV de la banque Crédit Mutuel.");
                            return FALSE;
                        }

                } else {
                    $data[$a]["date"] = self::convertToMySqlDate($convertedRow[0]);
                    $data[$a]["label"] = $convertedRow[4];
                    $data[$a]["debit"] = self::transformerChaineEnNombre($convertedRow[2]);
                    $data[$a]["credit"] = self::transformerChaineEnNombre($convertedRow[3]);
                    $data[$a]["solde"] = self::transformerChaineEnNombre($convertedRow[5], TRUE);
                    $data[$a]["md5"] = md5($data[$a]["date"].$data[$a]["debit"].$data[$a]["credit"].$data[$a]["solde"]);
                }
            }
            fclose($handle);
        }

        return $data;
    }

    public static function transformerChaineEnNombre(string $_chaine, bool $_negatif = FALSE) {
        // Supprimer les tirets "-"
        if($_negatif == 0) {
            $chaine = str_replace('-', '', $_chaine);
        } else {
            $chaine = $_chaine;
        }
    
        // Remplacer les virgules par des points
        $chaine = str_replace(',', '.', $chaine);
    
        // Convertir en nombre à virgule flottante (float)
        $nombre = (float) $chaine;
    
        return $nombre;
    }

    public static function convertToMySqlDate($date) {
        // Crée un objet DateTime à partir de la date au format dd/mm/yyyy
        $dateTime = DateTime::createFromFormat('d/m/Y', $date);
        
        // Vérifie si la date est valide
        if ($dateTime === false) {
            return false; // Retourne false si la date n'est pas valide
        }
        
        // Retourne la date au format MySQL (yyyy-mm-dd)
        return $dateTime->format('Y-m-d');
    }

    public static function getEuro(float $_decimal){
        return number_format($_decimal, 2, ',', '.') . " €";
    }

    public static function fileName(string $_compte){
        return str_replace(' ', '', $_compte);
    }

    public static function numberAccountCreditMutuel(string $_compte, string $_fileName){
        $tmpCompte = str_replace(' ', '', $_compte);
        $compte = substr($tmpCompte, 5);
        return (str_contains($_fileName, $compte)) ? TRUE : FALSE;
    }
}