WebSVN – eFlore/Projets.eflore-projets – Diff – /trunk/scripts/modules/bdtxa/Bdtxa.php


<?php
//declare(encoding='UTF-8');
/**
 * Exemple de lancement du script : :
 * /opt/lampp/bin/php cli.php bdtxa -a chargerTous
 *
 * @category	php 5.2
 * @package		eFlore/Scripts
 * @author		Jean-Pascal MILCENT <jpm@tela-botanica.org>
 * @copyright	Copyright (c) 2012, Tela Botanica (accueil@tela-botanica.org)
 * @license		http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL
 * @license		http://www.gnu.org/licenses/gpl.html Licence GNU-GPL
 * @version		$Id$
 */
class Bdtxa extends EfloreScript {
 
	private $table = null;
	private $pasInsertion = 1000;
	private $departInsertion = 0;
 
	protected $parametres_autorises = array(
		'-t' => array(false, false, 'Permet de tester le script sur un jeu réduit de données (indiquer le nombre de lignes).'));
 
	public function executer() {
		try {
			$this->initialiserProjet('bdtxa');
 
			// Lancement de l'action demandée
			$cmd = $this->getParametre('a');
			switch ($cmd) {
				case 'chargerTous' :
					$this->chargerStructureSql();
					$this->chargerBdtxa();
					$this->genererChpNomSciHtml();
					$this->genererChpFamille();
					$this->genererChpNomComplet();
					$this->genererChpHierarchie();
					break;
				case 'chargerStructureSql' :
					$this->chargerStructureSql();
					break;
				case 'chargerBdtxa' :
					$this->chargerBdtxa();
					break;
				case 'genererChpNomSciHtml' :
					$this->genererChpNomSciHtml();
					break;
				case 'genererChpNomComplet' :
					$this->initialiserGenerationChamps();
					$this->genererChpNomComplet();
					break;
				case 'genererChpFamille' :
					$this->genererChpFamille();
					break;
				case 'genererChpHierarchie' :
					$this->genererChpHierarchie();
					break;
				case 'supprimerTous' :
					$this->supprimerTous();
					break;
				default :
					throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
			}
		} catch (Exception $e) {
			$this->traiterErreur($e->getMessage());
		}
	}
 
	private function chargerBdtxa() {
		$chemin = Config::get('chemins.bdt');
		$table = Config::get('tables.bdtxa');
		$requete = "LOAD DATA INFILE '$chemin' ".
				"REPLACE INTO TABLE $table ".
				'CHARACTER SET utf8 '.
				'FIELDS '.
				"	TERMINATED BY '\t' ".
				"	ENCLOSED BY '' ".
				"	ESCAPED BY '\\\' ".
				'IGNORE 1 LINES';
		$this->getBdd()->requeter($requete);
	}
 
	private function genererChpNomSciHtml() {
		$this->initialiserGenerationChamps();
		$this->preparerTablePrChpNomSciHtml();
		$generateur = new GenerateurNomSciHtml();
		$nbreTotal = $this->recupererNbTotalTuples();
		$erreurs = array();
		$this->departInsertion = 0;
		while ($this->departInsertion < $nbreTotal) {
			$resultat = $this->recupererTuplesPrChpNomSciHtml();
 
			try {
				$nomsSciEnHtml = $generateur->generer($resultat);
			} catch (Exception $e) {
				$erreurs[] = $e->getMessage();
			}
 
			$this->remplirChpNomSciHtm($nomsSciEnHtml);
			$this->departInsertion += $this->pasInsertion;
			$this->afficherAvancement("Insertion des noms scientifique au format HTML dans la base par paquet de {$this->pasInsertion} en cours");
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
		}
		echo "\n";
 
		$this->creerFichierLog('Erreurs lors de la génération HTML des noms scientifiques', $erreurs, 'erreurs_noms_sci_html');
	}
 
	private function initialiserGenerationChamps() {
		$this->table = Config::get('tables.bdtxa');
		echo $this->table;
	}
 
	private function preparerTablePrChpNomSciHtml() {
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_sci_html' ";
		$resultat = $this->getBdd()->recuperer($requete);
		if ($resultat === false) {
			$requete = 	"ALTER TABLE {$this->table} ".
					'ADD nom_sci_html VARCHAR( 500 ) '.
					'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
			$this->getBdd()->requeter($requete);
		}
	}
 
	private function recupererNbTotalTuples(){
		$requete = "SELECT count(*) AS nb FROM {$this->table} ";
		$resultat = $this->getBdd()->recuperer($requete);
		return $resultat['nb'];
	}
 
	private function recupererTuplesPrChpNomSciHtml() {
		$requete = 'SELECT 	num_nom, rang, nom_supra_generique, genre, epithete_infra_generique, '.
				'	epithete_sp, type_epithete, epithete_infra_sp,cultivar_groupe, '.
				'	nom_commercial, cultivar '.
				"FROM {$this->table} ".
				"LIMIT {$this->departInsertion},{$this->pasInsertion} ";
		$resultat = $this->getBdd()->recupererTous($requete);
		return $resultat;
	}
 
	private function remplirChpNomSciHtm($nomsSciHtm) {
		foreach ($nomsSciHtm as $id => $html) {
			$html = $this->getBdd()->proteger($html);
			$requete = "UPDATE {$this->table} SET nom_sci_html = $html WHERE num_nom = $id ";
			$resultat = $this->getBdd()->requeter($requete);
			if ($resultat === false) {
				throw new Exception("Erreur d'insertion pour le tuple $id");
			}
		}
	}
 
	private function genererChpNomComplet() {
		$this->preparerTablePrChpNomComplet();
		$this->remplirChpNomComplet();
	}
 
	private function preparerTablePrChpNomComplet() {
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_complet' ";
		$resultat = $this->getBdd()->recuperer($requete);
		if ($resultat === false) {
			$requete = 	"ALTER TABLE {$this->table} ".
						'ADD nom_complet VARCHAR( 500 ) '.
						'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
			$this->getBdd()->requeter($requete);
		}
	}
 
	private function remplirChpNomComplet() {
		echo "Attribution du champ nom complet au taxons : ";
		$requete = "UPDATE {$this->table} SET nom_complet = CONCAT(nom_sci,' ',auteur)";
		$resultat = $this->getBdd()->requeter($requete);
		if ($resultat === false) {
			echo "KO\n";
			throw new Exception("Erreur de génération du champ nom complet");
		} else {
			echo "OK\n";
		}
	}
 
	private function genererChpFamille() {
		$this->initialiserGenerationChamps();
		$this->preparerTablePrChpFamille();
		$resultats = $this->recupererTuplesPrChpFamille();
		$noms = array();
		$introuvables = array();
		$introuvablesSyno = array();
		foreach ($resultats as $id => $nom) {
			$nn = $nom['num_nom'];
			$nnr = $nom['num_nom_retenu'];
			$nts = $nom['num_tax_sup'];
			$rg = $nom['rang'];
			if ($nnr != '') {
				if ($rg == '180') {
					$noms[$nn] = $nom['nom_sci'];
				} else {
					if ($nn == $nnr) {// nom retenu
						if (isset($noms[$nts])) {
							$noms[$nn] = $noms[$nts];
						} else {
							$introuvables[] = $nn;
						}
					} else {// nom synonyme
						if (isset($noms[$nnr])) {
							$noms[$nn] = $noms[$nnr];
						} else {
							$introuvablesSyno[] = $nom;
						}
					}
				}
			}
			unset($resultats[$id]);
			$this->afficherAvancement("Attribution de leur famille aux noms en cours");
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
		}
		echo "\n";
 
		foreach ($introuvablesSyno as $id => $nom) {
			$nn = $nom['num_nom'];
			$nnr = $nom['num_nom_retenu'];
			if (isset($noms[$nnr])) {
				$noms[$nn] = $noms[$nnr];
			} else {
				$introuvables[] = $nn;
			}
			unset($introuvablesSyno[$id]);
			$this->afficherAvancement("Attribution de leur famille aux synonymes en cours");
		}
		echo "\n";
 
		$msg = 'Plusieurs familles sont introuvables';
		$this->creerFichierLog($msg, $introuvables, 'famille_introuvable');
 
		$this->remplirChpFamille($noms);
	}
 
	private function preparerTablePrChpFamille() {
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'famille' ";
		$resultat = $this->getBdd()->recuperer($requete);
		if ($resultat === false) {
			$requete = 	"ALTER TABLE {$this->table} ".
				'ADD famille VARCHAR(255) '.
				'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
			$this->getBdd()->requeter($requete);
		}
	}
 
	private function recupererTuplesPrChpFamille() {
		$requete = 'SELECT num_nom, num_nom_retenu, num_tax_sup, rang, nom_sci '.
			"FROM {$this->table} ".
			"WHERE rang >= 180 ".
			"ORDER BY rang ASC, num_tax_sup ASC, num_nom_retenu DESC ";
		$resultat = $this->getBdd()->recupererTous($requete);
		return $resultat;
	}
 
	private function remplirChpFamille($noms) {
		foreach ($noms as $id => $famille) {
			$famille = $this->getBdd()->proteger($famille);
			$requete = "UPDATE {$this->table} SET famille = $famille WHERE num_nom = $id ";
			$resultat = $this->getBdd()->requeter($requete);
			if ($resultat === false) {
				throw new Exception("Erreur d'insertion pour le tuple $id");
			}
			$this->afficherAvancement("Insertion des noms de famille dans la base en cours");
		}
		echo "\n";
	}
	
	private function genererChpHierarchie() {
		$this->initialiserGenerationChamps();
		$this->preparerTablePrChpHierarchie();
		$table = Config::get('tables.bdtxa');
	
		$requete = "UPDATE $table SET hierarchie = NULL ";
		$mise_a_jour = $this->getBdd()->requeter($requete);
	
		$requete_hierarchie = "SELECT num_nom, num_nom_retenu, num_tax_sup FROM ".$table." ORDER BY rang DESC";
	
		$resultat = $this->getBdd()->recupererTous($requete_hierarchie);
		$num_nom_a_num_sup = array();
		foreach($resultat as &$taxon) {
			$num_nom_a_num_sup[$taxon['num_nom']] = $taxon['num_tax_sup'];
		}
		$chemin_taxo = "";
		foreach($resultat as &$taxon) {
			$chemin_taxo = $this->traiterHierarchieNumTaxSup($taxon['num_nom_retenu'], $num_nom_a_num_sup).'-';
			$requete = "UPDATE $table SET hierarchie = ".$this->getBdd()->proteger($chemin_taxo)." WHERE num_nom = ".$taxon['num_nom']." ";
			$mise_a_jour = $this->getBdd()->requeter($requete);
			$this->afficherAvancement("Insertion de la hierarchie taxonomique en cours");
		}
		echo "\n";
	}
	
	private function traiterHierarchieNumTaxSup($num_nom_retenu, &$num_nom_a_num_sup) {
		$chaine_hierarchie = "";
		if(isset($num_nom_a_num_sup[$num_nom_retenu])) {
			$num_tax_sup = $num_nom_a_num_sup[$num_nom_retenu];
			$chaine_hierarchie = '-'.$num_tax_sup;
			if($num_tax_sup != 0 && $num_tax_sup != '') {
				$chaine_hierarchie = $this->traiterHierarchieNumTaxSup($num_tax_sup, $num_nom_a_num_sup).$chaine_hierarchie;
			}
		}
		return $chaine_hierarchie;
	}
	
	private function preparerTablePrChpHierarchie() {
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'hierarchie' ";
		$resultat = $this->getBdd()->recuperer($requete);
		if ($resultat === false) {
			$requete = 	"ALTER TABLE {$this->table} ".
						'ADD hierarchie VARCHAR(1000) '.
						'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
			$this->getBdd()->requeter($requete);
		}
	}
	
	private function genererDonneesTestMultiVersion() {
		$contenuSql = $this->recupererContenu(Config::get('chemins.structureSqlTest'));
		$this->executerScripSql($contenuSql);
 
		$table = Config::get('tables.bdtxa');
		$tableTest = Config::get('tables.bdtxaTest');
		$requete = "INSERT INTO $tableTest SELECT * FROM $table";
		$this->getBdd()->requeter($requete);
	}
 
	private function supprimerDonneesTestMultiVersion() {
		$tableMeta = Config::get('tables.bdtxaMeta');
		$requete = "DELETE FROM $tableMeta WHERE guid = 'urn:lsid:tela-botanica.org:bdtxa:1.00'";
		$this->getBdd()->requeter($requete);
 
		$tableTest = Config::get('tables.bdtxaTest');
		$requete = "DROP TABLE IF EXISTS $tableTest";
		$this->getBdd()->requeter($requete);
	}
	private function supprimerTous() {
		$requete = "DROP TABLE IF EXISTS bdtxa_meta, bdtxa_v0_01, bdtxa_v1_00";
		$this->getBdd()->requeter($requete);
	}
 
	private function creerFichierLog($message, $lignes, $nomFichier) {
		$lignesNbre = count($lignes);
		if ($lignesNbre != 0) {
			echo "$message. Voir le log de $lignesNbre lignes :\n";
 
			$logContenu = implode(", \n", $lignes);
			$logFichier = realpath(dirname(__FILE__))."/log/$nomFichier.log";
			echo $logFichier."\n";
			file_put_contents($logFichier, $logContenu);
		}
	}
}
?>

Rev 661	Rev 699
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 bdtxa -a chargerTous`	5	`* /opt/lampp/bin/php cli.php bdtxa -a chargerTous`
6	`*`	6	`*`
7	`* @category php 5.2`	7	`* @category php 5.2`
8	`* @package eFlore/Scripts`	8	`* @package eFlore/Scripts`
9	`* @author Jean-Pascal MILCENT <jpm@tela-botanica.org>`	9	`* @author Jean-Pascal MILCENT <jpm@tela-botanica.org>`
10	`* @copyright Copyright (c) 2012, Tela Botanica (accueil@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`	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`	12	`* @license http://www.gnu.org/licenses/gpl.html Licence GNU-GPL`
13	`* @version $Id$`	13	`* @version $Id$`
14	`*/`	14	`*/`
15	`class Bdtxa extends EfloreScript {`	15	`class Bdtxa extends EfloreScript {`
16		16
17	`private $table = null;`	17	`private $table = null;`
18	`private $pasInsertion = 1000;`	18	`private $pasInsertion = 1000;`
19	`private $departInsertion = 0;`	19	`private $departInsertion = 0;`
20		20
21	`protected $parametres_autorises = array(`	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).'));`	22	`'-t' => array(false, false, 'Permet de tester le script sur un jeu réduit de données (indiquer le nombre de lignes).'));`
23		23
24	`public function executer() {`	24	`public function executer() {`
25	`try {`	25	`try {`
26	`$this->initialiserProjet('bdtxa');`	26	`$this->initialiserProjet('bdtxa');`
27		27
28	`// Lancement de l'action demandée`	28	`// Lancement de l'action demandée`
29	`$cmd = $this->getParametre('a');`	29	`$cmd = $this->getParametre('a');`
30	`switch ($cmd) {`	30	`switch ($cmd) {`
31	`case 'chargerTous' :`	31	`case 'chargerTous' :`
32	`$this->chargerStructureSql();`	32	`$this->chargerStructureSql();`
33	`$this->chargerBdtxa();`	33	`$this->chargerBdtxa();`
34	`$this->genererChpNomSciHtml();`	34	`$this->genererChpNomSciHtml();`
35	`$this->genererChpFamille();`	35	`$this->genererChpFamille();`
36	`$this->genererChpNomComplet();`	36	`$this->genererChpNomComplet();`
-		37	`$this->genererChpHierarchie();`
37	`break;`	38	`break;`
38	`case 'chargerStructureSql' :`	39	`case 'chargerStructureSql' :`
39	`$this->chargerStructureSql();`	40	`$this->chargerStructureSql();`
40	`break;`	41	`break;`
41	`case 'chargerBdtxa' :`	42	`case 'chargerBdtxa' :`
42	`$this->chargerBdtxa();`	43	`$this->chargerBdtxa();`
43	`break;`	44	`break;`
44	`case 'genererChpNomSciHtml' :`	45	`case 'genererChpNomSciHtml' :`
45	`$this->genererChpNomSciHtml();`	46	`$this->genererChpNomSciHtml();`
46	`break;`	47	`break;`
47	`case 'genererChpNomComplet' :`	48	`case 'genererChpNomComplet' :`
48	`$this->initialiserGenerationChamps();`	49	`$this->initialiserGenerationChamps();`
49	`$this->genererChpNomComplet();`	50	`$this->genererChpNomComplet();`
50	`break;`	51	`break;`
51	`case 'genererChpFamille' :`	52	`case 'genererChpFamille' :`
52	`$this->genererChpFamille();`	53	`$this->genererChpFamille();`
53	`break;`	54	`break;`
-		55	`case 'genererChpHierarchie' :`
-		56	`$this->genererChpHierarchie();`
-		57	`break;`
54	`case 'supprimerTous' :`	58	`case 'supprimerTous' :`
55	`$this->supprimerTous();`	59	`$this->supprimerTous();`
56	`break;`	60	`break;`
57	`default :`	61	`default :`
58	`throw new Exception("Erreur : la commande '$cmd' n'existe pas!");`	62	`throw new Exception("Erreur : la commande '$cmd' n'existe pas!");`
59	`}`	63	`}`
60	`} catch (Exception $e) {`	64	`} catch (Exception $e) {`
61	`$this->traiterErreur($e->getMessage());`	65	`$this->traiterErreur($e->getMessage());`
62	`}`	66	`}`
63	`}`	67	`}`
64		68
65	`private function chargerBdtxa() {`	69	`private function chargerBdtxa() {`
66	`$chemin = Config::get('chemins.bdt');`	70	`$chemin = Config::get('chemins.bdt');`
67	`$table = Config::get('tables.bdtxa');`	71	`$table = Config::get('tables.bdtxa');`
68	`$requete = "LOAD DATA INFILE '$chemin' ".`	72	`$requete = "LOAD DATA INFILE '$chemin' ".`
69	`"REPLACE INTO TABLE $table ".`	73	`"REPLACE INTO TABLE $table ".`
70	`'CHARACTER SET utf8 '.`	74	`'CHARACTER SET utf8 '.`
71	`'FIELDS '.`	75	`'FIELDS '.`
72	`" TERMINATED BY '\t' ".`	76	`" TERMINATED BY '\t' ".`
73	`" ENCLOSED BY '' ".`	77	`" ENCLOSED BY '' ".`
74	`" ESCAPED BY '\\\' ".`	78	`" ESCAPED BY '\\\' ".`
75	`'IGNORE 1 LINES';`	79	`'IGNORE 1 LINES';`
76	`$this->getBdd()->requeter($requete);`	80	`$this->getBdd()->requeter($requete);`
77	`}`	81	`}`
78		82
79	`private function genererChpNomSciHtml() {`	83	`private function genererChpNomSciHtml() {`
80	`$this->initialiserGenerationChamps();`	84	`$this->initialiserGenerationChamps();`
81	`$this->preparerTablePrChpNomSciHtml();`	85	`$this->preparerTablePrChpNomSciHtml();`
82	`$generateur = new GenerateurNomSciHtml();`	86	`$generateur = new GenerateurNomSciHtml();`
83	`$nbreTotal = $this->recupererNbTotalTuples();`	87	`$nbreTotal = $this->recupererNbTotalTuples();`
84	`$erreurs = array();`	88	`$erreurs = array();`
85	`$this->departInsertion = 0;`	89	`$this->departInsertion = 0;`
86	`while ($this->departInsertion < $nbreTotal) {`	90	`while ($this->departInsertion < $nbreTotal) {`
87	`$resultat = $this->recupererTuplesPrChpNomSciHtml();`	91	`$resultat = $this->recupererTuplesPrChpNomSciHtml();`
88		92
89	`try {`	93	`try {`
90	`$nomsSciEnHtml = $generateur->generer($resultat);`	94	`$nomsSciEnHtml = $generateur->generer($resultat);`
91	`} catch (Exception $e) {`	95	`} catch (Exception $e) {`
92	`$erreurs[] = $e->getMessage();`	96	`$erreurs[] = $e->getMessage();`
93	`}`	97	`}`
94		98
95	`$this->remplirChpNomSciHtm($nomsSciEnHtml);`	99	`$this->remplirChpNomSciHtm($nomsSciEnHtml);`
96	`$this->departInsertion += $this->pasInsertion;`	100	`$this->departInsertion += $this->pasInsertion;`
97	`$this->afficherAvancement("Insertion des noms scientifique au format HTML dans la base par paquet de {$this->pasInsertion} en cours");`	101	`$this->afficherAvancement("Insertion des noms scientifique au format HTML dans la base par paquet de {$this->pasInsertion} en cours");`
98	`if ($this->stopperLaBoucle($this->getParametre('t'))) break;`	102	`if ($this->stopperLaBoucle($this->getParametre('t'))) break;`
99	`}`	103	`}`
100	`echo "\n";`	104	`echo "\n";`
101		105
102	`$this->creerFichierLog('Erreurs lors de la génération HTML des noms scientifiques', $erreurs, 'erreurs_noms_sci_html');`	106	`$this->creerFichierLog('Erreurs lors de la génération HTML des noms scientifiques', $erreurs, 'erreurs_noms_sci_html');`
103	`}`	107	`}`
104		108
105	`private function initialiserGenerationChamps() {`	109	`private function initialiserGenerationChamps() {`
106	`$this->table = Config::get('tables.bdtxa');`	110	`$this->table = Config::get('tables.bdtxa');`
-		111	`echo $this->table;`
107	`}`	112	`}`
108		113
109	`private function preparerTablePrChpNomSciHtml() {`	114	`private function preparerTablePrChpNomSciHtml() {`
110	`$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_sci_html' ";`	115	`$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_sci_html' ";`
111	`$resultat = $this->getBdd()->recuperer($requete);`	116	`$resultat = $this->getBdd()->recuperer($requete);`
112	`if ($resultat === false) {`	117	`if ($resultat === false) {`
113	`$requete = "ALTER TABLE {$this->table} ".`	118	`$requete = "ALTER TABLE {$this->table} ".`
114	`'ADD nom_sci_html VARCHAR( 500 ) '.`	119	`'ADD nom_sci_html VARCHAR( 500 ) '.`
115	`'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';`	120	`'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';`
116	`$this->getBdd()->requeter($requete);`	121	`$this->getBdd()->requeter($requete);`
117	`}`	122	`}`
118	`}`	123	`}`
119		124
120	`private function recupererNbTotalTuples(){`	125	`private function recupererNbTotalTuples(){`
121	`$requete = "SELECT count(*) AS nb FROM {$this->table} ";`	126	`$requete = "SELECT count(*) AS nb FROM {$this->table} ";`
122	`$resultat = $this->getBdd()->recuperer($requete);`	127	`$resultat = $this->getBdd()->recuperer($requete);`
123	`return $resultat['nb'];`	128	`return $resultat['nb'];`
124	`}`	129	`}`
125		130
126	`private function recupererTuplesPrChpNomSciHtml() {`	131	`private function recupererTuplesPrChpNomSciHtml() {`
127	`$requete = 'SELECT num_nom, rang, nom_supra_generique, genre, epithete_infra_generique, '.`	132	`$requete = 'SELECT num_nom, rang, nom_supra_generique, genre, epithete_infra_generique, '.`
128	`' epithete_sp, type_epithete, epithete_infra_sp,cultivar_groupe, '.`	133	`' epithete_sp, type_epithete, epithete_infra_sp,cultivar_groupe, '.`
129	`' nom_commercial, cultivar '.`	134	`' nom_commercial, cultivar '.`
130	`"FROM {$this->table} ".`	135	`"FROM {$this->table} ".`
131	`"LIMIT {$this->departInsertion},{$this->pasInsertion} ";`	136	`"LIMIT {$this->departInsertion},{$this->pasInsertion} ";`
132	`$resultat = $this->getBdd()->recupererTous($requete);`	137	`$resultat = $this->getBdd()->recupererTous($requete);`
133	`return $resultat;`	138	`return $resultat;`
134	`}`	139	`}`
135		140
136	`private function remplirChpNomSciHtm($nomsSciHtm) {`	141	`private function remplirChpNomSciHtm($nomsSciHtm) {`
137	`foreach ($nomsSciHtm as $id => $html) {`	142	`foreach ($nomsSciHtm as $id => $html) {`
138	`$html = $this->getBdd()->proteger($html);`	143	`$html = $this->getBdd()->proteger($html);`
139	`$requete = "UPDATE {$this->table} SET nom_sci_html = $html WHERE num_nom = $id ";`	144	`$requete = "UPDATE {$this->table} SET nom_sci_html = $html WHERE num_nom = $id ";`
140	`$resultat = $this->getBdd()->requeter($requete);`	145	`$resultat = $this->getBdd()->requeter($requete);`
141	`if ($resultat === false) {`	146	`if ($resultat === false) {`
142	`throw new Exception("Erreur d'insertion pour le tuple $id");`	147	`throw new Exception("Erreur d'insertion pour le tuple $id");`
143	`}`	148	`}`
144	`}`	149	`}`
145	`}`	150	`}`
146		151
147	`private function genererChpNomComplet() {`	152	`private function genererChpNomComplet() {`
148	`$this->preparerTablePrChpNomComplet();`	153	`$this->preparerTablePrChpNomComplet();`
149	`$this->remplirChpNomComplet();`	154	`$this->remplirChpNomComplet();`
150	`}`	155	`}`
151		156
152	`private function preparerTablePrChpNomComplet() {`	157	`private function preparerTablePrChpNomComplet() {`
153	`$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_complet' ";`	158	`$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_complet' ";`
154	`$resultat = $this->getBdd()->recuperer($requete);`	159	`$resultat = $this->getBdd()->recuperer($requete);`
155	`if ($resultat === false) {`	160	`if ($resultat === false) {`
156	`$requete = "ALTER TABLE {$this->table} ".`	161	`$requete = "ALTER TABLE {$this->table} ".`
157	`'ADD nom_complet VARCHAR( 500 ) '.`	162	`'ADD nom_complet VARCHAR( 500 ) '.`
158	`'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';`	163	`'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';`
159	`$this->getBdd()->requeter($requete);`	164	`$this->getBdd()->requeter($requete);`
160	`}`	165	`}`
161	`}`	166	`}`
162		167
163	`private function remplirChpNomComplet() {`	168	`private function remplirChpNomComplet() {`
164	`echo "Attribution du champ nom complet au taxons : ";`	169	`echo "Attribution du champ nom complet au taxons : ";`
165	`$requete = "UPDATE {$this->table} SET nom_complet = CONCAT(nom_sci,' ',auteur)";`	170	`$requete = "UPDATE {$this->table} SET nom_complet = CONCAT(nom_sci,' ',auteur)";`
166	`$resultat = $this->getBdd()->requeter($requete);`	171	`$resultat = $this->getBdd()->requeter($requete);`
167	`if ($resultat === false) {`	172	`if ($resultat === false) {`
168	`echo "KO\n";`	173	`echo "KO\n";`
169	`throw new Exception("Erreur de génération du champ nom complet");`	174	`throw new Exception("Erreur de génération du champ nom complet");`
170	`} else {`	175	`} else {`
171	`echo "OK\n";`	176	`echo "OK\n";`
172	`}`	177	`}`
173	`}`	178	`}`
174		179
175	`private function genererChpFamille() {`	180	`private function genererChpFamille() {`
176	`$this->initialiserGenerationChamps();`	181	`$this->initialiserGenerationChamps();`
177	`$this->preparerTablePrChpFamille();`	182	`$this->preparerTablePrChpFamille();`
178	`$resultats = $this->recupererTuplesPrChpFamille();`	183	`$resultats = $this->recupererTuplesPrChpFamille();`
179	`$noms = array();`	184	`$noms = array();`
180	`$introuvables = array();`	185	`$introuvables = array();`
181	`$introuvablesSyno = array();`	186	`$introuvablesSyno = array();`
182	`foreach ($resultats as $id => $nom) {`	187	`foreach ($resultats as $id => $nom) {`
183	`$nn = $nom['num_nom'];`	188	`$nn = $nom['num_nom'];`
184	`$nnr = $nom['num_nom_retenu'];`	189	`$nnr = $nom['num_nom_retenu'];`
185	`$nts = $nom['num_tax_sup'];`	190	`$nts = $nom['num_tax_sup'];`
186	`$rg = $nom['rang'];`	191	`$rg = $nom['rang'];`
187	`if ($nnr != '') {`	192	`if ($nnr != '') {`
188	`if ($rg == '180') {`	193	`if ($rg == '180') {`
189	`$noms[$nn] = $nom['nom_sci'];`	194	`$noms[$nn] = $nom['nom_sci'];`
190	`} else {`	195	`} else {`
191	`if ($nn == $nnr) {// nom retenu`	196	`if ($nn == $nnr) {// nom retenu`
192	`if (isset($noms[$nts])) {`	197	`if (isset($noms[$nts])) {`
193	`$noms[$nn] = $noms[$nts];`	198	`$noms[$nn] = $noms[$nts];`
194	`} else {`	199	`} else {`
195	`$introuvables[] = $nn;`	200	`$introuvables[] = $nn;`
196	`}`	201	`}`
197	`} else {// nom synonyme`	202	`} else {// nom synonyme`
198	`if (isset($noms[$nnr])) {`	203	`if (isset($noms[$nnr])) {`
199	`$noms[$nn] = $noms[$nnr];`	204	`$noms[$nn] = $noms[$nnr];`
200	`} else {`	205	`} else {`
201	`$introuvablesSyno[] = $nom;`	206	`$introuvablesSyno[] = $nom;`
202	`}`	207	`}`
203	`}`	208	`}`
204	`}`	209	`}`
205	`}`	210	`}`
206	`unset($resultats[$id]);`	211	`unset($resultats[$id]);`
207	`$this->afficherAvancement("Attribution de leur famille aux noms en cours");`	212	`$this->afficherAvancement("Attribution de leur famille aux noms en cours");`
208	`if ($this->stopperLaBoucle($this->getParametre('t'))) break;`	213	`if ($this->stopperLaBoucle($this->getParametre('t'))) break;`
209	`}`	214	`}`
210	`echo "\n";`	215	`echo "\n";`
211		216
212	`foreach ($introuvablesSyno as $id => $nom) {`	217	`foreach ($introuvablesSyno as $id => $nom) {`
213	`$nn = $nom['num_nom'];`	218	`$nn = $nom['num_nom'];`
214	`$nnr = $nom['num_nom_retenu'];`	219	`$nnr = $nom['num_nom_retenu'];`
215	`if (isset($noms[$nnr])) {`	220	`if (isset($noms[$nnr])) {`
216	`$noms[$nn] = $noms[$nnr];`	221	`$noms[$nn] = $noms[$nnr];`
217	`} else {`	222	`} else {`
218	`$introuvables[] = $nn;`	223	`$introuvables[] = $nn;`
219	`}`	224	`}`
220	`unset($introuvablesSyno[$id]);`	225	`unset($introuvablesSyno[$id]);`
221	`$this->afficherAvancement("Attribution de leur famille aux synonymes en cours");`	226	`$this->afficherAvancement("Attribution de leur famille aux synonymes en cours");`
222	`}`	227	`}`
223	`echo "\n";`	228	`echo "\n";`
224		229
225	`$msg = 'Plusieurs familles sont introuvables';`	230	`$msg = 'Plusieurs familles sont introuvables';`
226	`$this->creerFichierLog($msg, $introuvables, 'famille_introuvable');`	231	`$this->creerFichierLog($msg, $introuvables, 'famille_introuvable');`
227		232
228	`$this->remplirChpFamille($noms);`	233	`$this->remplirChpFamille($noms);`
229	`}`	234	`}`
230		235
231	`private function preparerTablePrChpFamille() {`	236	`private function preparerTablePrChpFamille() {`
232	`$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'famille' ";`	237	`$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'famille' ";`
233	`$resultat = $this->getBdd()->recuperer($requete);`	238	`$resultat = $this->getBdd()->recuperer($requete);`
234	`if ($resultat === false) {`	239	`if ($resultat === false) {`
235	`$requete = "ALTER TABLE {$this->table} ".`	240	`$requete = "ALTER TABLE {$this->table} ".`
236	`'ADD famille VARCHAR(255) '.`	241	`'ADD famille VARCHAR(255) '.`
237	`'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';`	242	`'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';`
238	`$this->getBdd()->requeter($requete);`	243	`$this->getBdd()->requeter($requete);`
239	`}`	244	`}`
240	`}`	245	`}`
241		246
242	`private function recupererTuplesPrChpFamille() {`	247	`private function recupererTuplesPrChpFamille() {`
243	`$requete = 'SELECT num_nom, num_nom_retenu, num_tax_sup, rang, nom_sci '.`	248	`$requete = 'SELECT num_nom, num_nom_retenu, num_tax_sup, rang, nom_sci '.`
244	`"FROM {$this->table} ".`	249	`"FROM {$this->table} ".`
245	`"WHERE rang >= 180 ".`	250	`"WHERE rang >= 180 ".`
246	`"ORDER BY rang ASC, num_tax_sup ASC, num_nom_retenu DESC ";`	251	`"ORDER BY rang ASC, num_tax_sup ASC, num_nom_retenu DESC ";`
247	`$resultat = $this->getBdd()->recupererTous($requete);`	252	`$resultat = $this->getBdd()->recupererTous($requete);`
248	`return $resultat;`	253	`return $resultat;`
249	`}`	254	`}`
250		255
251	`private function remplirChpFamille($noms) {`	256	`private function remplirChpFamille($noms) {`
252	`foreach ($noms as $id => $famille) {`	257	`foreach ($noms as $id => $famille) {`
253	`$famille = $this->getBdd()->proteger($famille);`	258	`$famille = $this->getBdd()->proteger($famille);`
254	`$requete = "UPDATE {$this->table} SET famille = $famille WHERE num_nom = $id ";`	259	`$requete = "UPDATE {$this->table} SET famille = $famille WHERE num_nom = $id ";`
255	`$resultat = $this->getBdd()->requeter($requete);`	260	`$resultat = $this->getBdd()->requeter($requete);`
256	`if ($resultat === false) {`	261	`if ($resultat === false) {`
257	`throw new Exception("Erreur d'insertion pour le tuple $id");`	262	`throw new Exception("Erreur d'insertion pour le tuple $id");`
258	`}`	263	`}`
259	`$this->afficherAvancement("Insertion des noms de famille dans la base en cours");`	264	`$this->afficherAvancement("Insertion des noms de famille dans la base en cours");`
260	`}`	265	`}`
261	`echo "\n";`	266	`echo "\n";`
262	`}`	267	`}`
-		268
-		269	`private function genererChpHierarchie() {`
-		270	`$this->initialiserGenerationChamps();`
-		271	`$this->preparerTablePrChpHierarchie();`
-		272	`$table = Config::get('tables.bdtxa');`
-		273
-		274	`$requete = "UPDATE $table SET hierarchie = NULL ";`
-		275	`$mise_a_jour = $this->getBdd()->requeter($requete);`
-		276
-		277	`$requete_hierarchie = "SELECT num_nom, num_nom_retenu, num_tax_sup FROM ".$table." ORDER BY rang DESC";`
-		278
-		279	`$resultat = $this->getBdd()->recupererTous($requete_hierarchie);`
-		280	`$num_nom_a_num_sup = array();`
-		281	`foreach($resultat as &$taxon) {`
-		282	`$num_nom_a_num_sup[$taxon['num_nom']] = $taxon['num_tax_sup'];`
-		283	`}`
-		284	`$chemin_taxo = "";`
-		285	`foreach($resultat as &$taxon) {`
-		286	`$chemin_taxo = $this->traiterHierarchieNumTaxSup($taxon['num_nom_retenu'], $num_nom_a_num_sup).'-';`
-		287	`$requete = "UPDATE $table SET hierarchie = ".$this->getBdd()->proteger($chemin_taxo)." WHERE num_nom = ".$taxon['num_nom']." ";`
-		288	`$mise_a_jour = $this->getBdd()->requeter($requete);`
-		289	`$this->afficherAvancement("Insertion de la hierarchie taxonomique en cours");`
-		290	`}`
-		291	`echo "\n";`
-		292	`}`
-		293
-		294	`private function traiterHierarchieNumTaxSup($num_nom_retenu, &$num_nom_a_num_sup) {`
-		295	`$chaine_hierarchie = "";`
-		296	`if(isset($num_nom_a_num_sup[$num_nom_retenu])) {`
-		297	`$num_tax_sup = $num_nom_a_num_sup[$num_nom_retenu];`
-		298	`$chaine_hierarchie = '-'.$num_tax_sup;`
-		299	`if($num_tax_sup != 0 && $num_tax_sup != '') {`
-		300	`$chaine_hierarchie = $this->traiterHierarchieNumTaxSup($num_tax_sup, $num_nom_a_num_sup).$chaine_hierarchie;`
-		301	`}`
-		302	`}`
-		303	`return $chaine_hierarchie;`
-		304	`}`
-		305
-		306	`private function preparerTablePrChpHierarchie() {`
-		307	`$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'hierarchie' ";`
-		308	`$resultat = $this->getBdd()->recuperer($requete);`
-		309	`if ($resultat === false) {`
-		310	`$requete = "ALTER TABLE {$this->table} ".`
-		311	`'ADD hierarchie VARCHAR(1000) '.`
-		312	`'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';`
-		313	`$this->getBdd()->requeter($requete);`
-		314	`}`
-		315	`}`
-		316
263	`private function genererDonneesTestMultiVersion() {`	317	`private function genererDonneesTestMultiVersion() {`
264	`$contenuSql = $this->recupererContenu(Config::get('chemins.structureSqlTest'));`	318	`$contenuSql = $this->recupererContenu(Config::get('chemins.structureSqlTest'));`
265	`$this->executerScripSql($contenuSql);`	319	`$this->executerScripSql($contenuSql);`
266		320
267	`$table = Config::get('tables.bdtxa');`	321	`$table = Config::get('tables.bdtxa');`
268	`$tableTest = Config::get('tables.bdtxaTest');`	322	`$tableTest = Config::get('tables.bdtxaTest');`
269	`$requete = "INSERT INTO $tableTest SELECT * FROM $table";`	323	`$requete = "INSERT INTO $tableTest SELECT * FROM $table";`
270	`$this->getBdd()->requeter($requete);`	324	`$this->getBdd()->requeter($requete);`
271	`}`	325	`}`
272		326
273	`private function supprimerDonneesTestMultiVersion() {`	327	`private function supprimerDonneesTestMultiVersion() {`
274	`$tableMeta = Config::get('tables.bdtxaMeta');`	328	`$tableMeta = Config::get('tables.bdtxaMeta');`
275	`$requete = "DELETE FROM $tableMeta WHERE guid = 'urn:lsid:tela-botanica.org:bdtxa:1.00'";`	329	`$requete = "DELETE FROM $tableMeta WHERE guid = 'urn:lsid:tela-botanica.org:bdtxa:1.00'";`
276	`$this->getBdd()->requeter($requete);`	330	`$this->getBdd()->requeter($requete);`
277		331
278	`$tableTest = Config::get('tables.bdtxaTest');`	332	`$tableTest = Config::get('tables.bdtxaTest');`
279	`$requete = "DROP TABLE IF EXISTS $tableTest";`	333	`$requete = "DROP TABLE IF EXISTS $tableTest";`
280	`$this->getBdd()->requeter($requete);`	334	`$this->getBdd()->requeter($requete);`
281	`}`	335	`}`
282	`private function supprimerTous() {`	336	`private function supprimerTous() {`
283	`$requete = "DROP TABLE IF EXISTS bdtxa_meta, bdtxa_v0_01, bdtxa_v1_00";`	337	`$requete = "DROP TABLE IF EXISTS bdtxa_meta, bdtxa_v0_01, bdtxa_v1_00";`
284	`$this->getBdd()->requeter($requete);`	338	`$this->getBdd()->requeter($requete);`
285	`}`	339	`}`
286		340
287	`private function creerFichierLog($message, $lignes, $nomFichier) {`	341	`private function creerFichierLog($message, $lignes, $nomFichier) {`
288	`$lignesNbre = count($lignes);`	342	`$lignesNbre = count($lignes);`
289	`if ($lignesNbre != 0) {`	343	`if ($lignesNbre != 0) {`
290	`echo "$message. Voir le log de $lignesNbre lignes :\n";`	344	`echo "$message. Voir le log de $lignesNbre lignes :\n";`
291		345
292	`$logContenu = implode(", \n", $lignes);`	346	`$logContenu = implode(", \n", $lignes);`
293	`$logFichier = realpath(dirname(__FILE__))."/log/$nomFichier.log";`	347	`$logFichier = realpath(dirname(__FILE__))."/log/$nomFichier.log";`
294	`echo $logFichier."\n";`	348	`echo $logFichier."\n";`
295	`file_put_contents($logFichier, $logContenu);`	349	`file_put_contents($logFichier, $logContenu);`
296	`}`	350	`}`
297	`}`	351	`}`
298	`}`	352	`}`
299	`?>`	353	`?>`

Subversion Repositories eFlore/Projets.eflore-projets

(root)/trunk/scripts/modules/bdtxa/Bdtxa.php @ 634 – Rev 661 → 699