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 
}