Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 335 | Rev 339 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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