Subversion Repositories eFlore/Projets.eflore-projets

Rev

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

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