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

<?php

class banque
{

    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);
            return db::single()["creer"];
    }
    
    public static function getInitial(int $_id)
    {
        $tmp = self::getInitialCompte($_id );
        $return = array(
            "num" => 0,
            "id" => 0,
            "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){
        $n = explode(" ", $_compte);
        return $n[1].$n[2];
    }
}