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;
637 delphine 44
				case 'genererChpNomSciHtml' :
406 jpm 45
					$this->genererChpNomSciHtml();
46
					break;
637 delphine 47
				case 'genererChpNomComplet' :
48
					$this->initialiserGenerationChamps();
49
					$this->genererChpNomComplet();
50
					break;
406 jpm 51
				case 'genererChpFamille' :
52
					$this->genererChpFamille();
53
					break;
54
				case 'supprimerTous' :
55
					$this->supprimerTous();
56
					break;
57
				default :
58
					throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
59
			}
60
		} catch (Exception $e) {
61
			$this->traiterErreur($e->getMessage());
62
		}
63
	}
64
 
65
	private function chargerBdtxa() {
634 delphine 66
		$chemin = Config::get('chemins.bdtxa');
67
		$table = Config::get('tables.bdtxa');
406 jpm 68
		$requete = "LOAD DATA INFILE '$chemin' ".
69
				"REPLACE INTO TABLE $table ".
70
				'CHARACTER SET utf8 '.
71
				'FIELDS '.
72
				"	TERMINATED BY '\t' ".
73
				"	ENCLOSED BY '' ".
74
				"	ESCAPED BY '\\\' ".
75
				'IGNORE 1 LINES';
76
		$this->getBdd()->requeter($requete);
77
	}
78
 
79
	private function genererChpNomSciHtml() {
80
		$this->initialiserGenerationChamps();
81
		$this->preparerTablePrChpNomSciHtml();
82
		$generateur = new GenerateurNomSciHtml();
83
		$nbreTotal = $this->recupererNbTotalTuples();
84
		$this->departInsertion = 0;
85
		while ($this->departInsertion < $nbreTotal) {
86
			$resultat = $this->recupererTuplesPrChpNomSciHtml();
87
			$nomsSciEnHtml = $generateur->generer($resultat);
88
			$this->remplirChpNomSciHtm($nomsSciEnHtml);
89
			$this->departInsertion += $this->pasInsertion;
90
			$this->afficherAvancement("Insertion des noms scientifique au format HTML dans la base par paquet de {$this->pasInsertion} en cours");
91
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
92
		}
93
		echo "\n";
94
	}
95
 
96
	private function initialiserGenerationChamps() {
634 delphine 97
		$this->table = Config::get('tables.bdtxa');
406 jpm 98
	}
99
 
100
	private function preparerTablePrChpNomSciHtml() {
101
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_sci_html' ";
102
		$resultat = $this->getBdd()->recuperer($requete);
103
		if ($resultat === false) {
104
			$requete = 	"ALTER TABLE {$this->table} ".
105
					'ADD nom_sci_html VARCHAR( 500 ) '.
106
					'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
107
			$this->getBdd()->requeter($requete);
108
		}
109
	}
110
 
111
	private function recupererNbTotalTuples(){
112
		$requete = "SELECT count(*) AS nb FROM {$this->table} ";
113
		$resultat = $this->getBdd()->recuperer($requete);
114
		return $resultat['nb'];
115
	}
116
 
117
	private function recupererTuplesPrChpNomSciHtml() {
118
		$requete = 'SELECT 	num_nom, rang, nom_supra_generique, genre, epithete_infra_generique, '.
119
				'	epithete_sp, type_epithete, epithete_infra_sp,cultivar_groupe, '.
120
				'	nom_commercial, cultivar '.
121
				"FROM {$this->table} ".
122
				"LIMIT {$this->departInsertion},{$this->pasInsertion} ";
123
		$resultat = $this->getBdd()->recupererTous($requete);
124
		return $resultat;
125
	}
126
 
127
	private function remplirChpNomSciHtm($nomsSciHtm) {
128
		foreach ($nomsSciHtm as $id => $html) {
129
			$html = $this->getBdd()->proteger($html);
130
			$requete = "UPDATE {$this->table} SET nom_sci_html = $html WHERE num_nom = $id ";
131
			$resultat = $this->getBdd()->requeter($requete);
132
			if ($resultat === false) {
133
				throw new Exception("Erreur d'insertion pour le tuple $id");
134
			}
135
		}
636 aurelien 136
	}
137
 
138
	private function genererChpNomComplet() {
139
		$this->preparerTablePrChpNomComplet();
140
		$this->remplirChpNomComplet();
141
	}
142
 
143
	private function preparerTablePrChpNomComplet() {
144
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_complet' ";
145
		$resultat = $this->getBdd()->recuperer($requete);
146
		if ($resultat === false) {
147
			$requete = 	"ALTER TABLE {$this->table} ".
148
						'ADD nom_complet VARCHAR( 500 ) '.
149
						'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
150
			$this->getBdd()->requeter($requete);
151
		}
152
	}
153
 
154
	private function remplirChpNomComplet() {
155
		$this->afficherAvancement("Attribution du champ nom complet au taxons");
156
		$requete = "UPDATE {$this->table} SET nom_complet = CONCAT(nom_sci,' ',auteur)";
157
		$resultat = $this->getBdd()->requeter($requete);
158
		if ($resultat === false) {
159
			throw new Exception("Erreur de génération du champ nom complet");
160
		}
406 jpm 161
	}
162
 
163
	private function genererChpFamille() {
164
		$this->initialiserGenerationChamps();
165
		$this->preparerTablePrChpFamille();
166
		$resultats = $this->recupererTuplesPrChpFamille();
167
		$noms = array();
634 delphine 168
		$introuvables = array();
169
		$introuvablesSyno = array();
406 jpm 170
		foreach ($resultats as $id => $nom) {
171
			$nn = $nom['num_nom'];
172
			$nnr = $nom['num_nom_retenu'];
173
			$nts = $nom['num_tax_sup'];
174
			$rg = $nom['rang'];
175
			if ($nnr != '') {
176
				if ($rg == '180') {
177
					$noms[$nn] = $nom['nom_sci'];
178
				} else {
179
					if ($nn == $nnr) {// nom retenu
180
						if (isset($noms[$nts])) {
181
							$noms[$nn] = $noms[$nts];
182
						} else {
183
							$introuvables[] = $nn;
184
						}
185
					} else {// nom synonyme
186
						if (isset($noms[$nnr])) {
187
							$noms[$nn] = $noms[$nnr];
188
						} else {
189
							$introuvablesSyno[] = $nom;
190
						}
191
					}
192
				}
193
			}
194
			unset($resultats[$id]);
195
			$this->afficherAvancement("Attribution de leur famille aux noms en cours");
196
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
197
		}
198
		echo "\n";
199
 
200
		foreach ($introuvablesSyno as $id => $nom) {
201
			$nn = $nom['num_nom'];
202
			$nnr = $nom['num_nom_retenu'];
203
			if (isset($noms[$nnr])) {
204
				$noms[$nn] = $noms[$nnr];
205
			} else {
206
				$introuvables[] = $nn;
207
			}
208
			unset($introuvablesSyno[$id]);
209
			$this->afficherAvancement("Attribution de leur famille aux synonymes en cours");
210
		}
211
		echo "\n";
212
 
213
		if (count($introuvables) != 0) {
214
			$introuvablesNbre = count($introuvables);
215
			echo "Famille introuvable pour $introuvablesNbre noms ! Voir le log.\n";
216
 
217
			$logContenu = implode(", \n", $introuvables);
218
			$logFichier = realpath(dirname(__FILE__)).'/log/famille_introuvable.log';
219
			echo $logFichier."\n";
220
			file_put_contents($logFichier, $logContenu);
221
		}
222
		$this->remplirChpFamille($noms);
223
	}
224
 
225
	private function preparerTablePrChpFamille() {
226
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'famille' ";
227
		$resultat = $this->getBdd()->recuperer($requete);
228
		if ($resultat === false) {
229
			$requete = 	"ALTER TABLE {$this->table} ".
230
				'ADD famille VARCHAR(255) '.
231
				'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
232
			$this->getBdd()->requeter($requete);
233
		}
234
	}
235
 
236
	private function recupererTuplesPrChpFamille() {
237
		$requete = 'SELECT num_nom, num_nom_retenu, num_tax_sup, rang, nom_sci '.
238
			"FROM {$this->table} ".
239
			"WHERE rang >= 180 ".
240
			"ORDER BY rang ASC, num_tax_sup ASC, num_nom_retenu DESC ";
241
		$resultat = $this->getBdd()->recupererTous($requete);
242
		return $resultat;
243
	}
244
 
245
	private function remplirChpFamille($noms) {
246
		foreach ($noms as $id => $famille) {
247
			$famille = $this->getBdd()->proteger($famille);
248
			$requete = "UPDATE {$this->table} SET famille = $famille WHERE num_nom = $id ";
249
			$resultat = $this->getBdd()->requeter($requete);
250
			if ($resultat === false) {
251
				throw new Exception("Erreur d'insertion pour le tuple $id");
252
			}
253
			$this->afficherAvancement("Insertion des noms de famille dans la base en cours");
254
		}
255
		echo "\n";
256
	}
634 delphine 257
	private function genererDonneesTestMultiVersion() {
258
		$contenuSql = $this->recupererContenu(Config::get('chemins.structureSqlTest'));
259
		$this->executerScripSql($contenuSql);
260
 
261
		$table = Config::get('tables.bdtxa');
262
		$tableTest = Config::get('tables.bdtxaTest');
263
		$requete = "INSERT INTO $tableTest SELECT * FROM $table";
264
		$this->getBdd()->requeter($requete);
265
	}
266
 
267
	private function supprimerDonneesTestMultiVersion() {
268
		$tableMeta = Config::get('tables.bdtxaMeta');
269
		$requete = "DELETE FROM $tableMeta WHERE guid = 'urn:lsid:tela-botanica.org:bdtfx:1.02'";
270
		$this->getBdd()->requeter($requete);
271
 
272
		$tableTest = Config::get('tables.bdtxaTest');
273
		$requete = "DROP TABLE IF EXISTS $tableTest";
274
		$this->getBdd()->requeter($requete);
275
	}
406 jpm 276
	private function supprimerTous() {
277
		$requete = "DROP TABLE IF EXISTS bdtxa_meta, bdtxa_v0_01";
278
		$this->getBdd()->requeter($requete);
279
	}
280
}
281
?>