| 2896 |
delphine |
1 |
<?php
|
|
|
2 |
// declare(encoding='UTF-8');
|
|
|
3 |
/**
|
|
|
4 |
* Classe de nettoyage de la base de données du CEL.
|
|
|
5 |
* Suppression des antislash
|
|
|
6 |
* Remplacement des chaines vides par des NULL
|
|
|
7 |
* Unifier les référentiels : pas de nom de version, majuscule...
|
|
|
8 |
* Unifier les certitudes
|
|
|
9 |
* Supprimer les INSEE-C: non rempli, les dates 0000-00
|
|
|
10 |
*
|
|
|
11 |
* Utilisation :
|
|
|
12 |
* - <code>/opt/lamp/bin/php cli.php nettoyage -a (voir méthode executer)</code>
|
|
|
13 |
*
|
|
|
14 |
* @category CEL
|
|
|
15 |
* @package Scripts
|
|
|
16 |
* @subpackage Nettoyage
|
|
|
17 |
* @author Delphine CAUQUIL <delphine@tela-botanica.org>
|
|
|
18 |
* @license GPL v3 <http://www.gnu.org/licenses/gpl.txt>
|
|
|
19 |
* @license CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
|
|
|
20 |
* @copyright 1999-2016 Tela Botanica <accueil@tela-botanica.org>
|
|
|
21 |
*/
|
|
|
22 |
class Nettoyage extends Script {
|
|
|
23 |
|
|
|
24 |
protected $bdd;
|
|
|
25 |
|
|
|
26 |
|
|
|
27 |
public function __construct($script_nom, $parametres_cli) {
|
|
|
28 |
parent::__construct($script_nom, $parametres_cli);
|
|
|
29 |
$this->bdd = new Bdd();
|
|
|
30 |
}
|
|
|
31 |
|
|
|
32 |
public function executer() {
|
|
|
33 |
$cmd = $this->getParametre('a');
|
|
|
34 |
try {
|
|
|
35 |
switch ($cmd) {
|
|
|
36 |
case 'tout' :
|
|
|
37 |
$this->supprimerAntislash();
|
|
|
38 |
$this->unifierNomReferentiel();
|
|
|
39 |
$this->unifierCertitude();
|
|
|
40 |
$this->verifierGeodatum();
|
|
|
41 |
$this->unifierNull();
|
|
|
42 |
break;
|
|
|
43 |
case 'supprimerAntislash' :
|
|
|
44 |
$this->supprimerAntislash();
|
|
|
45 |
break;
|
|
|
46 |
case 'unifierNomReferentiel' :
|
|
|
47 |
$this->unifierNomReferentiel();
|
|
|
48 |
break;
|
|
|
49 |
case 'unifierCertitude' :
|
|
|
50 |
$this->unifierCertitude();
|
|
|
51 |
break;
|
|
|
52 |
case 'verifierGeodatum' :
|
|
|
53 |
$this->verifierGeodatum();
|
|
|
54 |
break;
|
|
|
55 |
case 'unifierNull' : // lancer en dernier pour bien réussir
|
|
|
56 |
$this->unifierNull();
|
|
|
57 |
break;
|
|
|
58 |
default :
|
|
|
59 |
$msg = "Erreur : la commande '$cmd' n'existe pas!\n".
|
|
|
60 |
"Commandes existantes : regenererMotsClesTexteObs, regenererMotsClesTexteImages"
|
|
|
61 |
. ", regenererMotsClesTexteObsTout, regenererMotsClesTexteImagesTout"
|
|
|
62 |
. ", viderMotsClesTexteImagesSansMotsCles, viderMotsClesTexteObsSansMotsCles"
|
|
|
63 |
. ", regenererChampsTailleImage, reparerLiaisonsOublieesObs, reparerLiaisonsOublieesImages"
|
|
|
64 |
. ", reparerLiaisonsObsParMotsClesTexte, reductionMotsClesImages, reductionMotsClesImagesUtil"
|
|
|
65 |
. ", reduireEtNormaliserMotsClesImagesUtil, reduireEtNormaliserMotsClesImages"
|
|
|
66 |
. ", reductionMotsClesObs, reductionMotsClesObsUtil, reduireEtNormaliserMotsClesObsUtil"
|
|
|
67 |
. ", reduireEtNormaliserMotsClesObs";
|
|
|
68 |
throw new Exception($msg);
|
|
|
69 |
}
|
|
|
70 |
} catch (Exception $e) {
|
|
|
71 |
$this->traiterErreur($e->getMessage());
|
|
|
72 |
}
|
|
|
73 |
}
|
|
|
74 |
|
|
|
75 |
|
|
|
76 |
|
|
|
77 |
private function supprimerAntislash() {
|
|
|
78 |
$requete_colonnes = "SELECT column_name FROM information_schema.columns WHERE table_name = 'cel_obs' AND table_schema='tb_cel';";
|
|
|
79 |
$colonnes = $this->bdd->recupererTous($requete_colonnes);
|
|
|
80 |
foreach ($colonnes as $id=>$colonne) {
|
|
|
81 |
echo "Suppression des antislash dans la colonne ".$colonne['column_name']." ...";
|
|
|
82 |
$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\\\", "")';
|
|
|
83 |
$this->bdd->requeter($requete);
|
|
|
84 |
echo "effectuée \n";
|
|
|
85 |
}
|
|
|
86 |
}
|
|
|
87 |
|
|
|
88 |
private function unifierNull() {
|
|
|
89 |
$requete_colonnes = "SELECT column_name FROM information_schema.columns WHERE table_name = 'cel_obs' AND table_schema='tb_cel';";
|
|
|
90 |
$colonnes = $this->bdd->recupererTous($requete_colonnes);
|
|
|
91 |
foreach ($colonnes as $colonne) {
|
|
|
92 |
echo "Suppression des vides dans la colonne ".$colonne['column_name']." ...";
|
|
|
93 |
$dsl = array( 'transmission', 'altitude');
|
|
|
94 |
if (isset($dsl[$colonne['column_name']])) {
|
|
|
95 |
$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = NULL WHERE ".$colonne['column_name']."= ''";
|
|
|
96 |
} else {
|
|
|
97 |
$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')";
|
|
|
98 |
}
|
|
|
99 |
$this->bdd->requeter($requete);
|
|
|
100 |
echo "effectuée \n";
|
|
|
101 |
}
|
|
|
102 |
}
|
|
|
103 |
|
|
|
104 |
private function unifierCertitude() {
|
|
|
105 |
echo "Uniformisation du champ certitude/identification";
|
|
|
106 |
$certitude = array("Douteuse" => "douteux",
|
|
|
107 |
"Certainea" => "certain",
|
|
|
108 |
"Certaine" => "certain",
|
|
|
109 |
"A déterminer" => "aDeterminer",
|
|
|
110 |
"à vérifier" => "aDeterminer",
|
|
|
111 |
"Certaine, par Jean Lebail" => "certain",
|
|
|
112 |
"Certaine (à 99%)" => "certain",
|
|
|
113 |
"sur" => "certain");
|
|
|
114 |
foreach ($certitude as $valeur=>$saisie) {
|
|
|
115 |
$requete = "UPDATE cel_obs SET certitude = '".$saisie."' WHERE certitude ='".$valeur."'";
|
|
|
116 |
$this->bdd->requeter($requete);
|
|
|
117 |
echo "effectuée \n";
|
|
|
118 |
}
|
|
|
119 |
$requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'aDeterminer', '', NULL)";
|
|
|
120 |
$verif = $this->bdd->recupererTous($requete_verif);
|
|
|
121 |
if ($verif != array()) print_r($verif);
|
|
|
122 |
}
|
|
|
123 |
|
|
|
124 |
private function unifierNomReferentiel() {
|
|
|
125 |
echo "Uniformisation du nom de référentiel";
|
|
|
126 |
$referentiels = array("BDTFX", "BDTRE", "BDTXA", "APD", "LBF", "ISFAN");
|
|
|
127 |
foreach ($referentiels as $referentiel) {
|
|
|
128 |
$requete = "UPDATE cel_obs SET nom_referentiel = '".$referentiel."' WHERE nom_referentiel like '".$referentiel."%'";
|
|
|
129 |
$this->bdd->requeter($requete);
|
|
|
130 |
$requete = "UPDATE cel_obs SET nom_referentiel = NULL WHERE nom_sel_nn = '' or nom_sel_nn = '0'";
|
|
|
131 |
$this->bdd->requeter($requete);
|
|
|
132 |
$requete = "UPDATE cel_obs SET nom_referentiel = 'APD' WHERE nom_referentiel like 'bdtao%'";
|
|
|
133 |
$this->bdd->requeter($requete);
|
|
|
134 |
$requete = "UPDATE cel_obs SET nom_referentiel = 'BDTFX' WHERE nom_referentiel like 'bdnff%'";
|
|
|
135 |
$this->bdd->requeter($requete);
|
|
|
136 |
echo "effectuée \n";
|
|
|
137 |
}
|
|
|
138 |
$requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE nom_referentiel NOT IN ('".implode("', '", $referentiels)."', 'autre')";
|
|
|
139 |
$verif = $this->bdd->recupererTous($requete_verif);
|
|
|
140 |
if ($verif != array()) print_r($verif);
|
|
|
141 |
}
|
|
|
142 |
|
|
|
143 |
private function verifierGeodatum() {
|
|
|
144 |
echo "Uniformisation du geodatum";
|
|
|
145 |
$requete = "UPDATE cel_obs SET geodatum = 'wgs84' WHERE (latitude IS NOT NULL or latitude != '0.00000') AND geodatum = ''";
|
|
|
146 |
$this->bdd->requeter($requete);
|
|
|
147 |
$requete = "UPDATE cel_obs SET geodatum = NULL WHERE (latitude IS NULL or latitude == '0.00000')";
|
|
|
148 |
$this->bdd->requeter($requete);
|
|
|
149 |
echo "effectuée \n";
|
|
|
150 |
}
|
|
|
151 |
}
|