Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 634 | Rev 637 | Go to most recent revision | Details | Compare with Previous | 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 
?>