Subversion Repositories eFlore/Applications.cel

Rev

Go to most recent revision | Details | 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;
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']." ...";
2895 delphine 93
			$dsl = array( 'transmission', 'altitude');
2894 delphine 94
			if (isset($dsl[$colonne['column_name']])) {
2895 delphine 95
				$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = NULL WHERE ".$colonne['column_name']."= ''";
2894 delphine 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
			}
2893 delphine 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);
2894 delphine 130
			$requete = "UPDATE cel_obs SET nom_referentiel = NULL WHERE nom_sel_nn = '' or nom_sel_nn = '0'";
131
			$this->bdd->requeter($requete);
2895 delphine 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);
2893 delphine 136
			echo "effectuée \n";
137
		}
2895 delphine 138
		$requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE nom_referentiel NOT IN ('".implode("', '", $referentiels)."', 'autre')";
2893 delphine 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);
2895 delphine 147
		$requete = "UPDATE cel_obs SET geodatum = NULL WHERE (latitude IS NULL or latitude == '0.00000')";
148
		$this->bdd->requeter($requete);
2893 delphine 149
		echo "effectuée \n";
150
	}
151
}