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;
2936 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');
2936 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' :
2936 delphine 40 
					$this->supprimerRetourLigne();
2893 delphine 41 
					$this->supprimerAntislash();
2936 delphine 42 
					$this->supprimerGuillement();
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 
 
2936 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() {
2936 delphine 91 
		foreach ($this->colonnes as $id=>$colonne) {
2893 delphine 92 
			echo "Suppression des antislash dans la colonne ".$colonne['column_name']." ...";
2936 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 
 
2936 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 
}