New file |
0,0 → 1,151 |
<?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; |
|
|
public function __construct($script_nom, $parametres_cli) { |
parent::__construct($script_nom, $parametres_cli); |
$this->bdd = new Bdd(); |
} |
|
public function executer() { |
$cmd = $this->getParametre('a'); |
try { |
switch ($cmd) { |
case 'tout' : |
$this->supprimerAntislash(); |
$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 supprimerAntislash() { |
$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 $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 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"; |
} |
} |