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