Subversion Repositories eFlore/Projets.eflore-projets

<?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();

					break;

				case 'chargerStructureSql' :

					$this->chargerStructureSql();

					break;

				case 'chargerBdtxa' :

					$this->chargerBdtxa();

					break;

				case 'genererNomSciHtml' :

					$this->genererChpNomSciHtml();

					break;

				case 'genererChpFamille' :

					$this->genererChpFamille();

					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.bdtxa');

		$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();

		$this->departInsertion = 0;

		while ($this->departInsertion < $nbreTotal) {

			$resultat = $this->recupererTuplesPrChpNomSciHtml();

			$nomsSciEnHtml = $generateur->generer($resultat);

			$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";

	}

	private function initialiserGenerationChamps() {

		$this->table = Config::get('tables.bdtxa');

	}

	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 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";

		if (count($introuvables) != 0) {

			$introuvablesNbre = count($introuvables);

			echo "Famille introuvable pour $introuvablesNbre noms ! Voir le log.\n";

			$logContenu = implode(", \n", $introuvables);

			$logFichier = realpath(dirname(__FILE__)).'/log/famille_introuvable.log';

			echo $logFichier."\n";

			file_put_contents($logFichier, $logContenu);

		}

		$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 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:bdtfx:1.02'";

		$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";

		$this->getBdd()->requeter($requete);

	}

}

?>