Subversion Repositories eFlore/Applications.cel

Rev

Rev 2937 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
2893 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;
2937 delphine 25
	protected $colonnes;
2893 delphine 26
 
27
 
28
	public function __construct($script_nom, $parametres_cli) {
29
		parent::__construct($script_nom, $parametres_cli);
30
		$this->bdd = new Bdd();
31
	}
32
 
33
	public function executer() {
34
		$cmd = $this->getParametre('a');
2937 delphine 35
		$requete_colonnes = "SELECT column_name FROM information_schema.columns WHERE table_name = 'cel_obs' AND table_schema='tb_cel';";
36
		$this->colonnes = $this->bdd->recupererTous($requete_colonnes);
2893 delphine 37
		try {
38
			switch ($cmd) {
39
				case 'tout' :
2937 delphine 40
					$this->supprimerRetourLigne();
2893 delphine 41
					$this->supprimerAntislash();
2938 delphine 42
					$this->supprimerGuillemet();
2893 delphine 43
					$this->unifierNomReferentiel();
44
					$this->unifierCertitude();
45
					$this->verifierGeodatum();
46
					$this->unifierNull();
47
					break;
48
				case 'supprimerAntislash' :
49
					$this->supprimerAntislash();
50
					break;
51
				case 'unifierNomReferentiel' :
52
					$this->unifierNomReferentiel();
53
					break;
54
				case 'unifierCertitude' :
55
					$this->unifierCertitude();
56
					break;
57
				case 'verifierGeodatum' :
58
					$this->verifierGeodatum();
59
					break;
60
				case 'unifierNull' : // lancer en dernier pour bien réussir
61
					$this->unifierNull();
62
					break;
63
				default :
64
					$msg = "Erreur : la commande '$cmd' n'existe pas!\n".
65
						"Commandes existantes : regenererMotsClesTexteObs, regenererMotsClesTexteImages"
66
							. ", regenererMotsClesTexteObsTout, regenererMotsClesTexteImagesTout"
67
							. ", viderMotsClesTexteImagesSansMotsCles, viderMotsClesTexteObsSansMotsCles"
68
							. ", regenererChampsTailleImage, reparerLiaisonsOublieesObs, reparerLiaisonsOublieesImages"
69
							. ", reparerLiaisonsObsParMotsClesTexte, reductionMotsClesImages, reductionMotsClesImagesUtil"
70
							. ", reduireEtNormaliserMotsClesImagesUtil, reduireEtNormaliserMotsClesImages"
71
							. ", reductionMotsClesObs, reductionMotsClesObsUtil, reduireEtNormaliserMotsClesObsUtil"
72
							. ", reduireEtNormaliserMotsClesObs";
73
					throw new Exception($msg);
74
			}
75
		} catch (Exception $e) {
76
			$this->traiterErreur($e->getMessage());
77
		}
78
	}
79
 
80
 
2937 delphine 81
	private function supprimerRetourLigne() {
82
		foreach ($this->colonnes as $id=>$colonne) {
83
			echo "Suppression des retour ligne dans la colonne ".$colonne['column_name']." ...";
84
			$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\n", "")';
85
			$this->bdd->requeter($requete);
86
			echo "effectuée \n";
87
		}
88
	}
2893 delphine 89
 
90
	private function supprimerAntislash() {
2937 delphine 91
		foreach ($this->colonnes as $id=>$colonne) {
2893 delphine 92
			echo "Suppression des antislash dans la colonne ".$colonne['column_name']." ...";
2937 delphine 93
			$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\", "")';
2893 delphine 94
			$this->bdd->requeter($requete);
95
			echo "effectuée \n";
96
		}
97
	}
98
 
2937 delphine 99
	private function supprimerGuillemet() {
100
		foreach ($this->colonnes as $id=>$colonne) {
101
			echo "Suppression des guillements dans la colonne ".$colonne['column_name']." ...";
102
			$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].',"\"", "")';
103
			$this->bdd->requeter($requete);
104
			echo "effectuée \n";
105
		}
106
	}
107
 
2893 delphine 108
	private function unifierNull() {
109
		$requete_colonnes = "SELECT column_name FROM information_schema.columns WHERE table_name = 'cel_obs' AND table_schema='tb_cel';";
110
		$colonnes = $this->bdd->recupererTous($requete_colonnes);
111
		foreach ($colonnes as $colonne) {
112
			echo "Suppression des vides dans la colonne ".$colonne['column_name']." ...";
2895 delphine 113
			$dsl = array( 'transmission', 'altitude');
2894 delphine 114
			if (isset($dsl[$colonne['column_name']])) {
2895 delphine 115
				$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = NULL WHERE ".$colonne['column_name']."= ''";
2894 delphine 116
			} else {
117
				$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')";
118
			}
2893 delphine 119
			$this->bdd->requeter($requete);
120
			echo "effectuée \n";
121
		}
122
	}
123
 
124
	private function unifierCertitude() {
125
		echo "Uniformisation du champ certitude/identification";
126
		$certitude = array("Douteuse" => "douteux",
127
			"Certainea" => "certain",
128
			"Certaine" => "certain",
129
			"A déterminer" => "aDeterminer",
130
			"à vérifier" => "aDeterminer",
131
			"Certaine, par Jean Lebail" => "certain",
132
			"Certaine (à 99%)" => "certain",
133
			"sur" => "certain");
134
		foreach ($certitude as $valeur=>$saisie) {
135
			$requete = "UPDATE cel_obs SET certitude = '".$saisie."' WHERE certitude ='".$valeur."'";
136
			$this->bdd->requeter($requete);
137
			echo "effectuée \n";
138
		}
139
		$requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'aDeterminer', '', NULL)";
140
		$verif = $this->bdd->recupererTous($requete_verif);
141
		if ($verif != array()) print_r($verif);
142
	}
143
 
144
	private function unifierNomReferentiel() {
145
		echo "Uniformisation du nom de référentiel";
146
		$referentiels = array("BDTFX", "BDTRE", "BDTXA", "APD", "LBF", "ISFAN");
147
		foreach ($referentiels as $referentiel) {
148
			$requete = "UPDATE cel_obs SET nom_referentiel = '".$referentiel."' WHERE nom_referentiel like '".$referentiel."%'";
149
			$this->bdd->requeter($requete);
2894 delphine 150
			$requete = "UPDATE cel_obs SET nom_referentiel = NULL WHERE nom_sel_nn = '' or nom_sel_nn = '0'";
151
			$this->bdd->requeter($requete);
2895 delphine 152
			$requete = "UPDATE cel_obs SET nom_referentiel = 'APD' WHERE nom_referentiel like 'bdtao%'";
153
			$this->bdd->requeter($requete);
154
			$requete = "UPDATE cel_obs SET nom_referentiel = 'BDTFX' WHERE nom_referentiel like 'bdnff%'";
155
			$this->bdd->requeter($requete);
2893 delphine 156
			echo "effectuée \n";
157
		}
2895 delphine 158
		$requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE nom_referentiel NOT IN ('".implode("', '", $referentiels)."', 'autre')";
2893 delphine 159
		$verif = $this->bdd->recupererTous($requete_verif);
160
		if ($verif != array()) print_r($verif);
161
	}
162
 
163
	private function verifierGeodatum() {
164
		echo "Uniformisation du geodatum";
165
		$requete = "UPDATE cel_obs SET geodatum = 'wgs84' WHERE (latitude IS NOT NULL or latitude != '0.00000') AND geodatum = ''";
166
		$this->bdd->requeter($requete);
2895 delphine 167
		$requete = "UPDATE cel_obs SET geodatum = NULL WHERE (latitude IS NULL or latitude == '0.00000')";
168
		$this->bdd->requeter($requete);
2893 delphine 169
		echo "effectuée \n";
170
	}
171
}