Subversion Repositories eFlore/Projets.eflore-projets

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
11 jpm 1
<?php
121 jpm 2
//declare(encoding='UTF-8');
11 jpm 3
/**
4
 * Exemple de lancement du script : :
121 jpm 5
 * /opt/lampp/bin/php cli.php bdtfx -a chargerTous
6
 *
11 jpm 7
 * @category	php 5.2
121 jpm 8
 * @package		eFlore/Scripts
11 jpm 9
 * @author		Jennifer DHÉ <jennifer@tela-botanica.org>
10
 * @author		Jean-Pascal MILCENT <jpm@tela-botanica.org>
11
 * @copyright	Copyright (c) 2011, Tela Botanica (accueil@tela-botanica.org)
12
 * @license		http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL
13
 * @license		http://www.gnu.org/licenses/gpl.html Licence GNU-GPL
14
 * @version		$Id$
15
 */
68 jpm 16
class Bdtfx extends EfloreScript {
46 jpm 17
 
68 jpm 18
	private $table = null;
19
	private $pasInsertion = 1000;
20
	private $departInsertion = 0;
11 jpm 21
 
22
	protected $parametres_autorises = array(
369 mathilde 23
		'-t' => array(false, false, 'Permet de tester le script sur un jeu réduit de données (indiquer le nombre de lignes).'));
11 jpm 24
 
25
	public function executer() {
46 jpm 26
		try {
68 jpm 27
			$this->initialiserProjet('bdtfx');
46 jpm 28
 
29
			// Lancement de l'action demandée
30
			$cmd = $this->getParametre('a');
31
			switch ($cmd) {
32
				case 'chargerTous' :
33
					$this->chargerStructureSql();
34
					$this->chargerBdtfx();
340 jpm 35
					$this->genererChpNomSciHtml();
36
					$this->genererChpFamille();
103 jpm 37
					$this->genererDonneesTestMultiVersion();
699 aurelien 38
					$this->genererChpHierarchie();
46 jpm 39
					break;
40
				case 'chargerStructureSql' :
41
					$this->chargerStructureSql();
42
					break;
43
				case 'chargerBdtfx' :
44
					$this->chargerBdtfx();
45
					break;
46
				case 'genererNomSciHtml' :
340 jpm 47
					$this->genererChpNomSciHtml();
46 jpm 48
					break;
335 jpm 49
				case 'genererChpFamille' :
50
					$this->genererChpFamille();
51
					break;
699 aurelien 52
				case 'genererChpHierarchie' :
53
					$this->genererChpHierarchie();
54
					break;
103 jpm 55
				case 'genererDonneesTestMultiVersion' :
56
					$this->genererDonneesTestMultiVersion();
57
					break;
58
				case 'supprimerDonneesTestMultiVersion' :
59
					$this->supprimerDonneesTestMultiVersion();
60
					break;
130 jpm 61
				case 'supprimerTous' :
62
					$this->supprimerTous();
63
					break;
46 jpm 64
				default :
65
					throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
66
			}
67
		} catch (Exception $e) {
68
			$this->traiterErreur($e->getMessage());
11 jpm 69
		}
46 jpm 70
	}
11 jpm 71
 
46 jpm 72
	private function chargerBdtfx() {
73
		$chemin = Config::get('chemins.bdtfx');
74
		$table = Config::get('tables.bdtfx');
75
		$requete = "LOAD DATA INFILE '$chemin' ".
76
				"REPLACE INTO TABLE $table ".
77
				'CHARACTER SET utf8 '.
78
				'FIELDS '.
79
				"	TERMINATED BY '\t' ".
80
				"	ENCLOSED BY '' ".
81
				"	ESCAPED BY '\\\' ".
82
				'IGNORE 1 LINES';
68 jpm 83
		$this->getBdd()->requeter($requete);
46 jpm 84
	}
85
 
340 jpm 86
	private function genererChpNomSciHtml() {
335 jpm 87
		$this->initialiserGenerationChamps();
88
		$this->preparerTablePrChpNomSciHtml();
46 jpm 89
		$generateur = new GenerateurNomSciHtml();
90
		$nbreTotal = $this->recupererNbTotalTuples();
661 jpm 91
		$erreurs = array();
335 jpm 92
		$this->departInsertion = 0;
46 jpm 93
		while ($this->departInsertion < $nbreTotal) {
340 jpm 94
			$resultat = $this->recupererTuplesPrChpNomSciHtml();
661 jpm 95
 
96
			try {
97
				$nomsSciEnHtml = $generateur->generer($resultat);
98
			} catch (Exception $e) {
99
				$erreurs[] = $e->getMessage();
100
			}
101
 
340 jpm 102
			$this->remplirChpNomSciHtm($nomsSciEnHtml);
46 jpm 103
			$this->departInsertion += $this->pasInsertion;
104
			$this->afficherAvancement("Insertion des noms scientifique au format HTML dans la base par paquet de {$this->pasInsertion} en cours");
68 jpm 105
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
11 jpm 106
		}
107
		echo "\n";
661 jpm 108
 
109
		$this->creerFichierLog('Erreurs lors de la génération HTML des noms scientifiques', $erreurs, 'erreurs_noms_sci_html');
11 jpm 110
	}
111
 
335 jpm 112
	private function initialiserGenerationChamps() {
46 jpm 113
		$this->table = Config::get('tables.bdtfx');
11 jpm 114
	}
115
 
335 jpm 116
	private function preparerTablePrChpNomSciHtml() {
11 jpm 117
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_sci_html' ";
68 jpm 118
		$resultat = $this->getBdd()->recuperer($requete);
11 jpm 119
		if ($resultat === false) {
120
			$requete = 	"ALTER TABLE {$this->table} ".
335 jpm 121
				'ADD nom_sci_html VARCHAR( 500 ) '.
122
				'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
68 jpm 123
			$this->getBdd()->requeter($requete);
11 jpm 124
		}
125
	}
126
 
127
	private function recupererNbTotalTuples(){
46 jpm 128
		$requete = "SELECT count(*) AS nb FROM {$this->table} ";
68 jpm 129
		$resultat = $this->getBdd()->recuperer($requete);
46 jpm 130
		return $resultat['nb'];
11 jpm 131
	}
132
 
340 jpm 133
	private function recupererTuplesPrChpNomSciHtml() {
11 jpm 134
		$requete = 'SELECT 	num_nom, rang, nom_supra_generique, genre, epithete_infra_generique, '.
335 jpm 135
			'	epithete_sp, type_epithete, epithete_infra_sp,cultivar_groupe, '.
136
			'	nom_commercial, cultivar '.
137
			"FROM {$this->table} ".
138
			"LIMIT {$this->departInsertion},{$this->pasInsertion} ";
68 jpm 139
		$resultat = $this->getBdd()->recupererTous($requete);
11 jpm 140
		return $resultat;
141
	}
142
 
340 jpm 143
	private function remplirChpNomSciHtm($nomsSciHtm) {
46 jpm 144
		foreach ($nomsSciHtm as $id => $html) {
68 jpm 145
			$html = $this->getBdd()->proteger($html);
340 jpm 146
			$requete = "UPDATE {$this->table} SET nom_sci_html = $html WHERE num_nom = $id ";
68 jpm 147
			$resultat = $this->getBdd()->requeter($requete);
46 jpm 148
			if ($resultat === false) {
149
				throw new Exception("Erreur d'insertion pour le tuple $id");
11 jpm 150
			}
151
		}
152
	}
339 jpm 153
 
335 jpm 154
	private function genererChpFamille() {
155
		$this->initialiserGenerationChamps();
156
		$this->preparerTablePrChpFamille();
336 jpm 157
		$resultats = $this->recupererTuplesPrChpFamille();
158
		$noms = array();
159
		$introuvables = array();
339 jpm 160
		$introuvablesSyno = array();
161
		foreach ($resultats as $id => $nom) {
336 jpm 162
			$nn = $nom['num_nom'];
339 jpm 163
			$nnr = $nom['num_nom_retenu'];
336 jpm 164
			$nts = $nom['num_tax_sup'];
165
			$rg = $nom['rang'];
339 jpm 166
			if ($nnr != '') {
167
				if ($rg == '180') {
340 jpm 168
					$noms[$nn] = $nom['nom_sci'];
336 jpm 169
				} else {
339 jpm 170
					if ($nn == $nnr) {// nom retenu
171
						if (isset($noms[$nts])) {
172
							$noms[$nn] = $noms[$nts];
173
						} else {
174
							$introuvables[] = $nn;
175
						}
176
					} else {// nom synonyme
177
						if (isset($noms[$nnr])) {
178
							$noms[$nn] = $noms[$nnr];
179
						} else {
180
							$introuvablesSyno[] = $nom;
181
						}
182
					}
336 jpm 183
				}
184
			}
339 jpm 185
			unset($resultats[$id]);
336 jpm 186
			$this->afficherAvancement("Attribution de leur famille aux noms en cours");
335 jpm 187
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
188
		}
336 jpm 189
		echo "\n";
339 jpm 190
 
191
		foreach ($introuvablesSyno as $id => $nom) {
192
			$nn = $nom['num_nom'];
193
			$nnr = $nom['num_nom_retenu'];
194
			if (isset($noms[$nnr])) {
195
				$noms[$nn] = $noms[$nnr];
196
			} else {
197
				$introuvables[] = $nn;
198
			}
199
			unset($introuvablesSyno[$id]);
200
			$this->afficherAvancement("Attribution de leur famille aux synonymes en cours");
336 jpm 201
		}
339 jpm 202
		echo "\n";
203
 
661 jpm 204
		$msg = 'Plusieurs familles sont introuvables';
205
		$this->creerFichierLog($msg, $introuvables, 'famille_introuvable');
339 jpm 206
 
340 jpm 207
		$this->remplirChpFamille($noms);
335 jpm 208
	}
699 aurelien 209
 
335 jpm 210
	private function preparerTablePrChpFamille() {
211
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'famille' ";
212
		$resultat = $this->getBdd()->recuperer($requete);
213
		if ($resultat === false) {
214
			$requete = 	"ALTER TABLE {$this->table} ".
215
				'ADD famille VARCHAR(255) '.
216
				'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
217
			$this->getBdd()->requeter($requete);
218
		}
219
	}
339 jpm 220
 
335 jpm 221
	private function recupererTuplesPrChpFamille() {
339 jpm 222
		$requete = 'SELECT num_nom, num_nom_retenu, num_tax_sup, rang, nom_sci '.
335 jpm 223
				"FROM {$this->table} ".
224
				"WHERE rang >= 180 ".
340 jpm 225
				"ORDER BY rang ASC, num_tax_sup ASC, num_nom_retenu DESC ";
335 jpm 226
		$resultat = $this->getBdd()->recupererTous($requete);
227
		return $resultat;
228
	}
339 jpm 229
 
340 jpm 230
	private function remplirChpFamille($noms) {
336 jpm 231
		foreach ($noms as $id => $famille) {
232
			$famille = $this->getBdd()->proteger($famille);
340 jpm 233
			$requete = "UPDATE {$this->table} SET famille = $famille WHERE num_nom = $id ";
336 jpm 234
			$resultat = $this->getBdd()->requeter($requete);
235
			if ($resultat === false) {
236
				throw new Exception("Erreur d'insertion pour le tuple $id");
237
			}
238
			$this->afficherAvancement("Insertion des noms de famille dans la base en cours");
239
		}
240
		echo "\n";
241
	}
699 aurelien 242
 
243
	private function genererChpHierarchie() {
244
		$this->initialiserGenerationChamps();
245
		$this->preparerTablePrChpHierarchie();
246
		$table = Config::get('tables.bdtfx');
247
 
248
		$requete = "UPDATE $table SET hierarchie = NULL ";
249
		$mise_a_jour = $this->getBdd()->requeter($requete);
250
 
251
		$requete_hierarchie = "SELECT num_nom, num_nom_retenu, num_tax_sup FROM ".$table." ORDER BY rang DESC";
252
 
253
		$resultat = $this->getBdd()->recupererTous($requete_hierarchie);
254
		$num_nom_a_num_sup = array();
255
		foreach($resultat as &$taxon) {
256
			$num_nom_a_num_sup[$taxon['num_nom']] = $taxon['num_tax_sup'];
257
		}
258
		$chemin_taxo = "";
259
		foreach($resultat as &$taxon) {
260
			$chemin_taxo = $this->traiterHierarchieNumTaxSup($taxon['num_nom_retenu'], $num_nom_a_num_sup).'-';
261
			$requete = "UPDATE $table SET hierarchie = ".$this->getBdd()->proteger($chemin_taxo)." WHERE num_nom = ".$taxon['num_nom']." ";
262
			$mise_a_jour = $this->getBdd()->requeter($requete);
263
			$this->afficherAvancement("Insertion de la hierarchie taxonomique en cours");
264
		}
265
		echo "\n";
266
	}
267
 
268
	private function traiterHierarchieNumTaxSup($num_nom_retenu, &$num_nom_a_num_sup) {
269
		$chaine_hierarchie = "";
270
		if(isset($num_nom_a_num_sup[$num_nom_retenu])) {
271
			$num_tax_sup = $num_nom_a_num_sup[$num_nom_retenu];
272
			$chaine_hierarchie = '-'.$num_tax_sup;
273
			if($num_tax_sup != 0 && $num_tax_sup != '') {
274
				$chaine_hierarchie = $this->traiterHierarchieNumTaxSup($num_tax_sup, $num_nom_a_num_sup).$chaine_hierarchie;
275
			}
276
		}
277
		return $chaine_hierarchie;
278
	}
279
 
280
	private function preparerTablePrChpHierarchie() {
281
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'hierarchie' ";
282
		$resultat = $this->getBdd()->recuperer($requete);
283
		if ($resultat === false) {
284
			$requete = 	"ALTER TABLE {$this->table} ".
285
					'ADD hierarchie VARCHAR(1000) '.
286
					'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
287
			$this->getBdd()->requeter($requete);
288
		}
289
	}
11 jpm 290
 
103 jpm 291
	private function genererDonneesTestMultiVersion() {
292
		$contenuSql = $this->recupererContenu(Config::get('chemins.structureSqlTest'));
293
		$this->executerScripSql($contenuSql);
68 jpm 294
 
103 jpm 295
		$table = Config::get('tables.bdtfx');
296
		$tableTest = Config::get('tables.bdtfxTest');
297
		$requete = "INSERT INTO $tableTest SELECT * FROM $table";
298
		$this->getBdd()->requeter($requete);
299
	}
300
 
301
	private function supprimerDonneesTestMultiVersion() {
302
		$tableMeta = Config::get('tables.bdtfxMeta');
303
		$requete = "DELETE FROM $tableMeta WHERE guid = 'urn:lsid:tela-botanica.org:bdtfx:1.02'";
304
		$this->getBdd()->requeter($requete);
305
 
306
		$tableTest = Config::get('tables.bdtfxTest');
342 jpm 307
		$requete = "DROP TABLE IF EXISTS $tableTest";
103 jpm 308
		$this->getBdd()->requeter($requete);
309
	}
130 jpm 310
 
311
	private function supprimerTous() {
342 jpm 312
		$requete = "DROP TABLE IF EXISTS bdtfx_meta, bdtfx_v1_01, bdtfx_v1_02";
130 jpm 313
		$this->getBdd()->requeter($requete);
314
	}
661 jpm 315
 
316
	private function creerFichierLog($message, $lignes, $nomFichier) {
317
		$lignesNbre = count($lignes);
318
		if ($lignesNbre != 0) {
319
			echo "$message. Voir le log de $lignesNbre lignes :\n";
320
 
321
			$logContenu = implode(", \n", $lignes);
322
			$logFichier = realpath(dirname(__FILE__))."/log/$nomFichier.log";
323
			echo $logFichier."\n";
324
					file_put_contents($logFichier, $logContenu);
325
		}
326
	}
11 jpm 327
}
328
?>