Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 340 | Rev 369 | Go to most recent revision | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

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