Subversion Repositories eFlore/Applications.cel

<?php
// declare(encoding='UTF-8');
/**
 * Classe de nettoyage de la base de données du CEL.
 * Suppression des antislash
 * Remplacement des chaines vides par des NULL
 * Unifier les référentiels : pas de nom de version, majuscule...
 * Unifier les certitudes
 * Supprimer les INSEE-C: non rempli, les dates 0000-00 
 *
 * Utilisation :
 * - <code>/opt/lamp/bin/php cli.php nettoyage -a (voir méthode executer)</code>
 *
 * @category   CEL
 * @package    Scripts
 * @subpackage Nettoyage
 * @author     Delphine CAUQUIL <delphine@tela-botanica.org>
 * @license    GPL v3 <http://www.gnu.org/licenses/gpl.txt>
 * @license    CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
 * @copyright  1999-2016 Tela Botanica <accueil@tela-botanica.org>
 */
class Nettoyage extends Script {

        protected $bdd;
        protected $colonnes;


        public function __construct($script_nom, $parametres_cli) {
                parent::__construct($script_nom, $parametres_cli);
                $this->bdd = new Bdd();
        }

        public function executer() {
                $cmd = $this->getParametre('a');
                $requete_colonnes = "SELECT column_name FROM information_schema.columns WHERE table_name = 'cel_obs' AND table_schema='tb_cel';";
                $this->colonnes = $this->bdd->recupererTous($requete_colonnes);
                try {
                        switch ($cmd) {
                                case 'tout' :
                                        $this->supprimerRetourLigne();
                                        $this->supprimerAntislash();
                                        $this->supprimerGuillemet();
                                        $this->unifierNomReferentiel();
                                        $this->unifierCertitude();
                                        $this->verifierGeodatum();
                                        $this->unifierNull();
                                        break;
                                case 'supprimerAntislash' :
                                        $this->supprimerAntislash();
                                        break;
                                case 'unifierNomReferentiel' :
                                        $this->unifierNomReferentiel();
                                        break;
                                case 'unifierCertitude' :
                                        $this->unifierCertitude();
                                        break;
                                case 'verifierGeodatum' :
                                        $this->verifierGeodatum();
                                        break;
                                case 'unifierNull' : // lancer en dernier pour bien réussir
                                        $this->unifierNull();
                                        break;
                                default :
                                        $msg = "Erreur : la commande '$cmd' n'existe pas!\n".
                                                "Commandes existantes : regenererMotsClesTexteObs, regenererMotsClesTexteImages"
                                                        . ", regenererMotsClesTexteObsTout, regenererMotsClesTexteImagesTout"
                                                        . ", viderMotsClesTexteImagesSansMotsCles, viderMotsClesTexteObsSansMotsCles"
                                                        . ", regenererChampsTailleImage, reparerLiaisonsOublieesObs, reparerLiaisonsOublieesImages"
                                                        . ", reparerLiaisonsObsParMotsClesTexte, reductionMotsClesImages, reductionMotsClesImagesUtil"
                                                        . ", reduireEtNormaliserMotsClesImagesUtil, reduireEtNormaliserMotsClesImages"
                                                        . ", reductionMotsClesObs, reductionMotsClesObsUtil, reduireEtNormaliserMotsClesObsUtil"
                                                        . ", reduireEtNormaliserMotsClesObs";
                                        throw new Exception($msg);
                        }
                } catch (Exception $e) {
                        $this->traiterErreur($e->getMessage());
                }
        }
        

        private function supprimerRetourLigne() {               
                foreach ($this->colonnes as $id=>$colonne) {
                        echo "Suppression des retour ligne dans la colonne ".$colonne['column_name']." ...";
                        $requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\n", "")';
                        $this->bdd->requeter($requete);
                        echo "effectuée \n";
                }
        }
        
        private function supprimerAntislash() {
                foreach ($this->colonnes as $id=>$colonne) {
                        echo "Suppression des antislash dans la colonne ".$colonne['column_name']." ...";
                        $requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\", "")';
                        $this->bdd->requeter($requete);
                        echo "effectuée \n";
                }
        }
        
        private function supprimerGuillemet() {
                foreach ($this->colonnes as $id=>$colonne) {
                        echo "Suppression des guillements dans la colonne ".$colonne['column_name']." ...";
                        $requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].',"\"", "")';
                        $this->bdd->requeter($requete);
                        echo "effectuée \n";
                }
        }
        
        private function unifierNull() {
                $requete_colonnes = "SELECT column_name FROM information_schema.columns WHERE table_name = 'cel_obs' AND table_schema='tb_cel';";
                $colonnes = $this->bdd->recupererTous($requete_colonnes);
                foreach ($colonnes as $colonne) {
                        echo "Suppression des vides dans la colonne ".$colonne['column_name']." ...";
                        $dsl = array( 'transmission', 'altitude');
                        if (isset($dsl[$colonne['column_name']])) {
                                $requete = "UPDATE cel_obs SET ".$colonne['column_name']." = NULL WHERE ".$colonne['column_name']."= ''";
                        } else {
                                $requete = "UPDATE cel_obs SET ".$colonne['column_name']." = NULL WHERE ".$colonne['column_name']."= '' OR ".$colonne['column_name']." IN ('INSEE-C:', '0000-00-00 00:00:00', '0.00000')";
                        }
                        $this->bdd->requeter($requete);
                        echo "effectuée \n";
                }
        }
        
        private function unifierCertitude() {
                echo "Uniformisation du champ certitude/identification";
                $certitude = array("Douteuse" => "douteux",
                        "Certainea" => "certain",
                        "Certaine" => "certain",
                        "A déterminer" => "aDeterminer",
                        "à vérifier" => "aDeterminer",
                        "Certaine, par Jean Lebail" => "certain",
                        "Certaine (à 99%)" => "certain",
                        "sur" => "certain");
                foreach ($certitude as $valeur=>$saisie) {
                        $requete = "UPDATE cel_obs SET certitude = '".$saisie."' WHERE certitude ='".$valeur."'";
                        $this->bdd->requeter($requete);
                        echo "effectuée \n";
                }
                $requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'aDeterminer', '', NULL)";
                $verif = $this->bdd->recupererTous($requete_verif);
                if ($verif != array()) print_r($verif);
        }
        
        private function unifierNomReferentiel() {
                echo "Uniformisation du nom de référentiel";
                $referentiels = array("BDTFX", "BDTRE", "BDTXA", "APD", "LBF", "ISFAN");
                foreach ($referentiels as $referentiel) {
                        $requete = "UPDATE cel_obs SET nom_referentiel = '".$referentiel."' WHERE nom_referentiel like '".$referentiel."%'";
                        $this->bdd->requeter($requete);
                        $requete = "UPDATE cel_obs SET nom_referentiel = NULL WHERE nom_sel_nn = '' or nom_sel_nn = '0'";
                        $this->bdd->requeter($requete);
                        $requete = "UPDATE cel_obs SET nom_referentiel = 'APD' WHERE nom_referentiel like 'bdtao%'";
                        $this->bdd->requeter($requete);
                        $requete = "UPDATE cel_obs SET nom_referentiel = 'BDTFX' WHERE nom_referentiel like 'bdnff%'";
                        $this->bdd->requeter($requete);
                        echo "effectuée \n";
                }
                $requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE nom_referentiel NOT IN ('".implode("', '", $referentiels)."', 'autre')";
                $verif = $this->bdd->recupererTous($requete_verif);
                if ($verif != array()) print_r($verif);
        }
        
        private function verifierGeodatum() {
                echo "Uniformisation du geodatum";
                $requete = "UPDATE cel_obs SET geodatum = 'wgs84' WHERE (latitude IS NOT NULL or latitude != '0.00000') AND geodatum = ''";
                $this->bdd->requeter($requete);
                $requete = "UPDATE cel_obs SET geodatum = NULL WHERE (latitude IS NULL or latitude == '0.00000')";
                $this->bdd->requeter($requete);
                echo "effectuée \n";
        }
}