"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 ""; } 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); } }