WebSVN – eFlore/Projets.eflore-projets – Diff – /trunk/scripts/modules/isfan/Isfan.php


<?php
//declare(encoding='UTF-8');
/**
 * Exemple de lancement du script : :
 * /opt/lampp/bin/php cli.php isfan -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 Isfan 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('isfan');
 
			// Lancement de l'action demandée
			$cmd = $this->getParametre('a');
			switch ($cmd) {
				case 'chargerTous' :
					$this->chargerStructureSql();
					$this->chargerisfan();
					$this->genererChpNumTax();
					$this->genererChpNomSciHtml();
					$this->genererChpFamille();
					$this->genererChpNomComplet();
					$this->genererChpHierarchie();
					break;
				case 'chargerStructureSql' :
					$this->chargerStructureSql();
					break;
				case 'chargerIsfan' :
					$this->chargerIsfan();
					break;
				case 'genererChpNumTax' :
					$this->genererChpNumTax();
					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 chargerIsfan() {
		$chemin = Config::get('chemins.bdt');
		$table = Config::get('tables.isfan');
		$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 genererChpNumTax() {
		$this->initialiserGenerationChamps();
		$this->preparerTablePrChpNumTax();
		$erreurs = array();
		$this->departInsertion = 0;
		$dernier_num_tax = 0;
		
		$requete = 'SELECT num_nom '.
					'FROM '.$this->table.' '.
					'WHERE num_nom = num_nom_retenu AND num_nom_retenu != 0 '.
					'ORDER by num_nom_retenu ASC ';
 
		$resultat = $this->getBdd()->recupererTous($requete);
		foreach ($resultat as $taxon) {
			$dernier_num_tax++;
			$requete_maj = 'UPDATE '.$this->table.' '.
							'SET num_taxonomique = '.$dernier_num_tax.' '.
							'WHERE num_nom_retenu = '.$taxon['num_nom'];
			$this->getBdd()->requeter($requete_maj);
			$this->pasInsertion++;
			$this->afficherAvancement("Insertion des num tax, ".count($resultat)." num tax a traiter");
		}
		echo "\n";
		$this->creerFichierLog('Erreurs lors de la génération des numéros taxonomiques', $erreurs, 'erreurs_num_tax');
	}
	
	private function preparerTablePrChpNumTax() {
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'num_taxonomique' ";
		$resultat = $this->getBdd()->recuperer($requete);
		if ($resultat === false) {
			$requete = 	"ALTER TABLE {$this->table} ".
						'ADD num_taxonomique INT( 9 ) ';
			$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.isfan');
	}
 
	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, genre, '.
				'	epithete_sp, type_epithete, epithete_infra_sp '.
				"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.isfan');
	
		$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.isfan');
		$tableTest = Config::get('tables.isfanTest');
		$requete = "INSERT INTO $tableTest SELECT * FROM $table";
		$this->getBdd()->requeter($requete);
	}
 
	private function supprimerDonneesTestMultiVersion() {
		$tableMeta = Config::get('tables.isfanMeta');
		$requete = "DELETE FROM $tableMeta WHERE guid = 'urn:lsid:tela-botanica.org:isfan:1.00'";
		$this->getBdd()->requeter($requete);
 
		$tableTest = Config::get('tables.isfanTest');
		$requete = "DROP TABLE IF EXISTS $tableTest";
		$this->getBdd()->requeter($requete);
	}
	private function supprimerTous() {
		$requete = "DROP TABLE IF EXISTS isfan_meta, isfan_v0_01, isfan_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);
		}
	}
}
?>

Subversion Repositories eFlore/Projets.eflore-projets

(root)/trunk/scripts/modules/isfan/Isfan.php @ 1319 – Rev 706 → 716

Rev 706		Rev 716
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 isfan -a chargerTous`	5	`* /opt/lampp/bin/php cli.php isfan -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 Isfan extends EfloreScript {`	15	`class Isfan 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('isfan');`	26	`$this->initialiserProjet('isfan');`
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->chargerisfan();`	33	`$this->chargerisfan();`
34	`$this->genererChpNumTax();`	34	`$this->genererChpNumTax();`
35	`$this->genererChpNomSciHtml();`	35	`$this->genererChpNomSciHtml();`
36	`$this->genererChpFamille();`	36	`$this->genererChpFamille();`
37	`$this->genererChpNomComplet();`	37	`$this->genererChpNomComplet();`
38	`$this->genererChpHierarchie();`	38	`$this->genererChpHierarchie();`
39	`break;`	39	`break;`
40	`case 'chargerStructureSql' :`	40	`case 'chargerStructureSql' :`