Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 416 | Rev 439 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

<?php
/** Exemple lancement:
 * /opt/lampp/bin/php -d memory_limit=3500M ~/web/eflore-projets/scripts/cli.php baseflor -a chargerTous
*/
class Baseflor extends EfloreScript {

        private $table = null;
        private $fichierDonnees = '';
        private $log = '';
        private $nb_erreurs;
        private $erreurs_ligne;
        private $ligne_num;
        private $colonne_valeur;
        private $colonne_num;
        private $type_bio = array();
        private $ss_type_bio = array();
        private $signes_seuls = array();// basés sur valeurs trouvées (--> pas de légende !)
        private $signes_nn_seuls = array();// basés sur valeurs trouvées (--> pas de légende !)
        private $intervalles = array();
        private $motifs = array();

        public function executer() {
                try {
                        $this->initialiserProjet('baseflor');
                        $cmd = $this->getParametre('a');
                        switch ($cmd) {
                                case 'chargerStructureSql' :
                                        $this->chargerStructureSql();
                                        break;
                                case 'chargerMetadonnees':
                                        $this->chargerMetadonnees();
                                        break;
                                case 'chargerOntologies' :
                                        $this->chargerOntologies();
                                        break;
                                case 'verifierDonnees' :
                                        $this->verifFichier();
                                        break;
                                case 'chargerDonnees' :
                                        $this->chargerDonnees();
                                        break;
                                case 'genererChamps' :
                                         $this->genererChamps();
                                         break;
                                case 'chargerTous':
                                        $this->chargerStructureSql();
                                        $this->chargerMetadonnees();
                                        $this->chargerOntologies();
                                        $this->chargerDonnees();
                                        $this->genererChamps();
                                        break;
                                case 'supprimerTous' :
                                        $this->supprimerTous();
                                        break;
                                default :
                                        throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
                        }
                } catch (Exception $e) {
                        $this->traiterErreur($e->getMessage());
                }
        }

        private function genererChamps(){
                $this->initialiserGenerationChamps();
                $this->ajouterChamps();
                $this->analyserChampsExistant();
        }

        private function initialiserGenerationChamps() {
                $this->table = Config::get('tables.donnees');
        }

        private function ajouterChamps() {
                $this->preparerTablePrChpsBDNT();
                $this->preparerTablePrChpsNumTaxon();
                $this->preparerTablePrChpsNumNomen();
        }

        private function preparerTablePrChpsBDNT() {
                $requete = "SHOW COLUMNS FROM {$this->table} LIKE 'BDNT' ";
                $resultat = $this->getBdd()->recuperer($requete);
                if ($resultat === false) {
                        $requete =      "ALTER TABLE {$this->table} ".
                                        'ADD BDNT VARCHAR( 6 ) '.
                                        'CHARACTER SET utf8 COLLATE utf8_general_ci '.
                                        'NOT NULL AFTER catminat_code ';
                        $this->getBdd()->requeter($requete);
                }
        }

        private function preparerTablePrChpsNumTaxon() {
                $requete = "SHOW COLUMNS FROM {$this->table} LIKE 'num_taxon' ";
                $resultat = $this->getBdd()->recuperer($requete);
                if ($resultat === false) {
                        $requete = "ALTER TABLE {$this->table} ".
                                        'ADD num_taxon INT( 10 ) NOT NULL '.
                                        'AFTER catminat_code';
                        $this->getBdd()->requeter($requete);
                }
        }

        private function preparerTablePrChpsNumNomen() {
                $requete = "SHOW COLUMNS FROM {$this->table} LIKE 'num_nomen' ";
                $resultat = $this->getBdd()->recuperer($requete);
                if ($resultat === false) {
                        $requete = "ALTER TABLE {$this->table} ".
                                        'ADD num_nomen INT( 10 ) NOT NULL '.
                                        'AFTER catminat_code';
                        $this->getBdd()->requeter($requete);
                }
        }

        private function analyserChampsExistant() {
                $resultats = $this->recupererTuplesNumsOriginels();
                foreach ($resultats as $chps) {
                        $cle = $chps['cle'];
                        $nno = $chps['num_nomen_originel'];
                        $nto = $chps['num_taxon_originel'];

                        $valeurs = array();
                        $valeurs["BDNT"] = $this->genererChpsBDNT($nno, $nto);
                        $valeurs["num_taxon"] = $this->genererChpsNumTaxon($nto);
                        $valeurs["num_nomen"] = $this->genererChpsNumNomen($nno);

                        $this->remplirChamps($cle, $valeurs);

                        $this->afficherAvancement("Insertion des valeurs dans la base en cours");
                }
                echo "\n";
        }

        private function recupererTuplesNumsOriginels(){
                $requete = "SELECT cle, num_taxon_originel, num_nomen_originel FROM {$this->table} ";
                $resultat = $this->getBdd()->recupererTous($requete);
                return $resultat;
        }

        private function genererChpsBDNT($nno, $nto) {
                $bdnt = '';
                if (preg_match("/^([AB])[0-9]+$/", $nno, $retour) || preg_match("/^([AB])[0-9]+$/", $nto, $retour)){
                        if ($retour[1]=='A') {
                                $bdnt = "BDAFX";
                        } else {
                                $bdnt = "BDBFX";
                        }
                } elseif (($nno == 'nc') && ($nto == 'nc')) {
                        $bdnt = "nc";
                } else {
                        $bdnt = "BDTFX";
                }
                return $bdnt;
        }

        private function genererChpsNumTaxon($nto){
                $num_taxon = '';
                if (preg_match("/^[AB]([0-9]+)$/", $nto, $retour)) {
                        $num_taxon = intval($retour[1]);
                } elseif($nto == 'nc') {
                        $num_taxon = 0;
                } else {
                        $num_taxon = intval($nto);
                }
                return $num_taxon;
        }

        private function genererChpsNumNomen($nno) {
                $num_nomen = '';
                if (preg_match("/^[AB]([0-9]+)$/", $nno, $retour)) {
                        $num_nomen = intval($retour[1]);
                } elseif ($nno == 'nc') {
                        $num_nomen = 0;
                } else {
                        $num_nomen = intval($nno);
                }
                return $num_nomen;
        }

         private function remplirChamps($cle, $valeurs) {
                foreach ($valeurs as $nomChamp => $valeurChamp) {
                        $valeurChamp = $this->getBdd()->proteger($valeurChamp);
                        $requete = "UPDATE {$this->table} SET $nomChamp = $valeurChamp WHERE cle = $cle ";
                        $resultat = $this->getBdd()->requeter($requete);
                        if ($resultat === false) {
                                throw new Exception("Erreur d'insertion pour le tuple clé = $cle");
                        }
                }
        }

        //+------------------------------------------------------------------------------------------------------+
        // chargements, suppression, exécution

        protected function chargerMetadonnees() {
                $contenuSql = $this->recupererContenu(Config::get('chemins.metadonnees'));
                $this->executerScripSql($contenuSql);
        }

        private function chargerOntologies() {
                $chemin = Config::get('chemins.ontologies');
                $table = Config::get('tables.ontologies');
                $requete = "LOAD DATA INFILE '$chemin' ".
                        "REPLACE INTO TABLE $table ".
                        'CHARACTER SET utf8 '.
                        'FIELDS '.
                        "       TERMINATED BY '\t' ".
                        "       ENCLOSED BY '' ".
                        "       ESCAPED BY '\\\' "
                        ;
                $this->getBdd()->requeter($requete);
        }

        protected function chargerStructureSql() {
                $contenuSql = $this->recupererContenu(Config::get('chemins.structureSql'));
                $this->executerScripSql($contenuSql);
        }

        protected function executerScripSql($sql) {
                $requetes = Outils::extraireRequetes($sql);
                foreach ($requetes as $requete) {
                        $this->getBdd()->requeter($requete);
                }
        }

        private function chargerDonnees() {
                $this->verifFichier();
                if ($this->nb_erreurs > 0) {
                        $e = "Je ne peux pas charger les données car le fichier comporte des erreurs.".
                                        "Voir le fichier baseflor_verif.txt\n";
                        throw new Exception($e);
                }

                $table = Config::get('tables.donnees');
                $requete = "LOAD DATA INFILE '".Config::get('chemins.donnees')."' ".
                        "REPLACE INTO TABLE $table ".
                        'CHARACTER SET utf8 '.
                        'FIELDS '.
                        "       TERMINATED BY '\t' ".
                        "       ENCLOSED BY '' ".
                        "       ESCAPED BY '\\\'";
                $this->getBdd()->requeter($requete);
        }

        private function supprimerTous() {
                $requete = "DROP TABLE IF EXISTS baseflor_meta, baseflor_ontologies, baseflor_v2012_03_19";
                $this->getBdd()->requeter($requete);
        }

        //+------------------------------------------------------------------------------------------------------+
        // vérifications de données

        //verifie la cohérence des valeurs des colonnes
        private function verifFichier(){
                $this->initialiserParametresVerif();

                $lignes = file($this->fichierDonnees, FILE_IGNORE_NEW_LINES);
                if ($lignes != false) {
                        $this->ajouterAuLog("!!! REGARDEZ LES COLONNES DANS NUMERO_COLONNES_IMPORTANT.TXT.");
                        foreach ($lignes as $this->ligne_num => $ligne) {
                                $this->verifierErreursLigne($ligne);
                                $this->afficherAvancement("Vérification des lignes");
                        }
                        echo "\n";
                } else {
                        $this->traiterErreur("Le fichier {$this->fichierDonnees} ne peut pas être ouvert.");
                }

                if ($this->nb_erreurs == 0) {
                        $this->ajouterAuLog("Il n'y a pas d'erreurs.");
                }
                $this->traiterInfo($this->nb_erreurs." erreurs");

                $this->ecrireFichierLog();
        }

        //vérifie par colonnes les erreurs d'une ligne
        private function verifierErreursLigne($ligne){
                $this->erreurs_ligne = array();
                $colonnes = explode("\t", $ligne);
                if (isset($colonnes)) {
                        foreach ($colonnes as $this->colonne_num => $this->colonne_valeur) {
                                if (( $this->colonne_num > 0 && $this->colonne_num < 15 )
                                                || $this->colonne_num == 16
                                                || ($this->colonne_num > 18 && $this->colonne_num < 23)
                                                || $this->colonne_num > 39) {
                                        $this->verifierColonne();
                                } elseif ($this->colonne_num == 15) {
                                        $this->verifierTypeBio();
                                } elseif ($this->colonne_num >= 23 && $this->colonne_num <= 32) {
                                        $this->verifierIntervalles($this->colonne_valeur);
                                } elseif ($this->colonne_num >= 33 && $this->colonne_num < 41) {
                                        $this->verifierValeursIndic();
                                }
                        }
                } else {
                        $message = "Ligne {$this->ligne_num} : pas de tabulation";
                        $this->ajouterAuLog($message);
                }

                $this->controlerErreursLigne();
        }

        private function verifierColonne(){
                $motif = $this->motifs[$this->colonne_num];
                if (preg_match($motif, $this->colonne_valeur) == 0 && $this->verifierSiVide() == false){
                        $this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
                }
        }

        private function verifierSiVide(){
                $vide = ($this->colonne_valeur  == '') ? true : false;
                return $vide;
        }

        private function verifierTypeBio(){
                if (preg_match("/(.+)\((.+)\)$/", $this->colonne_valeur, $retour) == 1) {
                        $this->verifierTypeEtSsType($retour[1]);
                        $this->verifierTypeEtSsType($retour[2]);
                } else {
                        $this->verifierTypeEtSsType($this->colonne_valeur);
                }
        }

        private function verifierTypeEtSsType($chaine_a_verif){
                if (preg_match("/^([a-zA-Zé]+)\-(.+)$|^([a-zA-Zé]+[^\-])$/", $chaine_a_verif, $retour) == 1) {
                        $type = (isset($retour[3])) ? $retour[3] : $retour[1];
                        $this->verifierType($type);

                        $sousType = $retour[2];
                        $this->verifierSousType($sousType);
                }
        }

        private function verifierType($type) {
                if (in_array($type, $this->type_bio) == false) {
                        $this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
                }
        }

        private function verifierSousType($sousType) {
                if ($sousType != ''){
                        $ss_type = explode('-', $sousType);
                        foreach ($ss_type as $sst) {
                                if (in_array($sst, $this->ss_type_bio) == false) {
                                        $this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
                                }
                        }
                }
        }

        private function verifierIntervalles($valeur){
                if ($valeur != '') {
                        list($min, $max) = explode('-', $this->intervalles[$this->colonne_num]);
                        if ($valeur < $min || $valeur > $max){
                                $this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
                        }
                }
        }

        private function verifierValeursIndic(){
                if (preg_match("/^([^0-9])*([0-9]+)([^0-9])*$/", $this->colonne_valeur, $retour) == 1){
                        $this->verifierIntervalles($retour[2]);
                        if (isset($retour[3]) && in_array($retour[3], $this->signes_nn_seuls) == false){
                                $this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
                        }
                        if ($retour[1] != '-' && $retour[1] != ''){
                                $this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
                        }
                } elseif (in_array( $this->colonne_valeur, $this->signes_seuls) == false && $this->verifierSiVide() == false) {
                        $this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
                }
        }

        private function controlerErreursLigne() {
                $nbreErreursLigne = count($this->erreurs_ligne);
                $this->nb_erreurs += $nbreErreursLigne;
                if ($nbreErreursLigne != 0) {
                        $this->ajouterAuLog("Erreurs sur la ligne {$this->ligne_num}");
                        $ligneLog = '';
                        foreach ($this->erreurs_ligne as $cle => $v){
                                $ligneLog .= "colonne $cle : $v - ";
                        }
                        $this->ajouterAuLog($ligneLog);
                }
        }

        //+------------------------------------------------------------------------------------------------------+
        // Chargement Paramètres

        private function initialiserParametresVerif() {
                $this->nb_erreurs = 0;
                $this->fichierDonnees = Config::get('chemins.donnees');
                $this->type_bio = $this->getParametreTableau('Parametres.typesBio');
                $this->ss_type_bio = $this->getParametreTableau('Parametres.sousTypesBio');
                $this->signes_seuls = $this->getParametreTableau('Parametres.signesSeuls');
                $this->signes_nn_seuls = $this->getParametreTableau('Parametres.signesNonSeuls');
                $this->intervalles = $this->inverserTableau($this->getParametreTableau('Parametres.intervalles'));
                $this->motifs = $this->inverserTableau($this->getParametreTableau('Parametres.motifs'));
        }

        private function getParametreTableau($cle) {
                $tableau = array();
                $parametre = Config::get($cle);
                if (empty($parametre) === false) {
                        $tableauPartiel = explode(',', $parametre);
                        $tableauPartiel = array_map('trim', $tableauPartiel);
                        foreach ($tableauPartiel as $champ) {
                                if (strpos($champ, '=') !== false && strlen($champ) >= 3) {
                                        list($cle, $val) = explode('=', $champ);
                                        $tableau[trim($cle)] = trim($val);
                                } else {
                                        $tableau[] = trim($champ);
                                }
                        }
                }
                return $tableau;
        }

        private function inverserTableau($tableau) {
                $inverse = array();
                foreach ($tableau as $cle => $valeurs) {
                        $valeurs = explode(';', $valeurs);
                        foreach ($valeurs as $valeur) {
                                $inverse[$valeur] = $cle;
                        }
                }
                return $inverse;
        }

        //+------------------------------------------------------------------------------------------------------+
        // Gestion du Log

        private function ajouterAuLog($txt) {
                $this->log .= "$txt\n";
        }

        private function ecrireFichierLog() {
                $fichierLog = dirname(__FILE__).'/log/verification.log';
                file_put_contents($fichierLog, $this->log);
        }
}
?>