<?php
/** Exemple lancement:
 * /opt/lampp/bin/php -d memory_limit=3500M ~/web/eflore-projets/scripts/cli.php baseflor -a chargerTous
*/
class Baseflor extends EfloreScript {

	private $table = null;


	public function executer() {
		try {
			$this->initialiserProjet('baseflor');
			$cmd = $this->getParametre('a');
			switch ($cmd) {
				case 'chargerStructureSql' :
					$this->chargerStructureSql();
					break;
				case 'chargerMetadonnees':
					$this->chargerMetadonnees();
					break;
				case 'chargerOntologies' :
					$this->chargerOntologies();
					break;
				case 'verifierFichier' :
					$this->verifierFichier();
					break;
				case 'chargerDonnees' :
					$this->chargerDonnees();
					break;
				case 'genererChamps' :
					 $this->genererChamps();
					 break;
				case 'chargerTous':
					$this->chargerStructureSql();
					$this->chargerMetadonnees();
					$this->chargerOntologies();
					$this->chargerDonnees();
					$this->genererChamps();
					$this->insererDonneesBaseflorRangSupEcolo();
					$this->insererDonneesIndex();
					break;
				case 'insererDonneesRangSup' :
					$this->insererDonneesBaseflorRangSupEcolo();
					break;
				case 'supprimerTous' :
					$this->supprimerTous();
					break;
				case 'voirRangSup' :
					$this->voirRangSup();
					break;
				case 'voirRangSupEcologie' :
					$this->voirRangSupEcologie();
					break;
				case 'insererDonneesIndex' :
					$this->insererDonneesIndex();
					break;
				default :
					throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
			}
		} catch (Exception $e) {
			$this->traiterErreur($e->getMessage());
		}
	}


	//-- traitement de la table baseflorRangSupInsertion --//

	private function  getClasseBaseflorRangSupInsertion() {
		$conteneur = new Conteneur();
		require_once dirname(__FILE__)."/BaseflorRangSupInsertion.php";
		$rangSupInsert = new BaseflorRangSupInsertion($conteneur, $this->getBdd());
		return $rangSupInsert;
	}

	private function insererDonneesBaseflorRangSupEcolo(){
		$rangSupInsert = $this->getClasseBaseflorRangSupInsertion();
		$rangSupInsert->insererDonnees();
	}

	private function voirRangSup(){
		$rangSupInsert = $this->getClasseBaseflorRangSupInsertion();
		$rangSupInsert->testAscendantsDeBaseflor();
	}

	private function voirRangSupEcologie(){
		$rangSupInsert = $this->getClasseBaseflorRangSupInsertion();
		$rangSupInsert->testEcologieAscendantsDeBaseflor();
	}


	//-- traitement de la table baseflorIndex --//

	private function  getClasseBaseflorIndex() {
		$conteneur = new Conteneur();
		require_once dirname(__FILE__)."/BaseflorIndex.php";
		$Index = new BaseflorIndex($conteneur, $this->getBdd());
		return $Index;
	}

	private function insererDonneesIndex(){
		$Index= $this->getClasseBaseflorIndex();
		$Index->insererDonnees();
	}


	//-- traitement de la table generer champs --//

	private function genererChamps(){
		$this->initialiserGenerationChamps();
		$this->ajouterChamps();
		$this->analyserChampsExistant();
	}

	private function initialiserGenerationChamps() {
		$this->table = Config::get('tables.donnees');
	}

	private function ajouterChamps() {
		$this->preparerTablePrChpsBDNT();
		$this->preparerTablePrChpsNumTaxon();
		$this->preparerTablePrChpsNumNomen();
	}

	private function preparerTablePrChpsBDNT() {
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'BDNT' ";
		$resultat = $this->getBdd()->recuperer($requete);
		if ($resultat === false) {
			$requete = 	"ALTER TABLE {$this->table} ".
					'ADD BDNT VARCHAR( 6 ) '.
					'CHARACTER SET utf8 COLLATE utf8_general_ci '.
					'NOT NULL AFTER catminat_code ';
			$this->getBdd()->requeter($requete);
		}
	}

	private function preparerTablePrChpsNumTaxon() {
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'num_taxon' ";
		$resultat = $this->getBdd()->recuperer($requete);
		if ($resultat === false) {
			$requete = "ALTER TABLE {$this->table} ".
					'ADD num_taxon INT( 10 ) NOT NULL '.
					'AFTER catminat_code';
			$this->getBdd()->requeter($requete);
		}
	}

	private function preparerTablePrChpsNumNomen() {
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'num_nomen' ";
		$resultat = $this->getBdd()->recuperer($requete);
		if ($resultat === false) {
			$requete = "ALTER TABLE {$this->table} ".
					'ADD num_nomen INT( 10 ) NOT NULL '.
					'AFTER catminat_code';
			$this->getBdd()->requeter($requete);
		}
	}

	private function analyserChampsExistant() {
		$resultats = $this->recupererTuplesNumsOriginels();
		foreach ($resultats as $chps) {
			$cle = $chps['cle'];
			$nno = $chps['num_nomen_originel'];
			$nto = $chps['num_taxon_originel'];

			$valeurs = array();
			$valeurs["BDNT"] = $this->genererChpsBDNT($nno, $nto);
			$valeurs["num_taxon"] = $this->genererChpsNumTaxon($nto);
			$valeurs["num_nomen"] = $this->genererChpsNumNomen($nno);

			$this->remplirChamps($cle, $valeurs);

			$this->afficherAvancement("Insertion des valeurs dans la base en cours");
		}
		echo "\n";
	}

	private function recupererTuplesNumsOriginels(){
		$requete = "SELECT cle, num_taxon_originel, num_nomen_originel FROM {$this->table} ";
		$resultat = $this->getBdd()->recupererTous($requete);
		return $resultat;
	}

	private function genererChpsBDNT($nno, $nto) {
		$bdnt = '';
		if (preg_match("/^([AB])[0-9]+$/", $nno, $retour) || preg_match("/^([AB])[0-9]+$/", $nto, $retour)){
			if ($retour[1]=='A') {
				$bdnt = "BDAFX";
			} else {
				$bdnt = "BDBFX";
			}
		} elseif (($nno == 'nc') && ($nto == 'nc')) {
			$bdnt = "nc";
		} else {
			$bdnt = "BDTFX";
		}
		return $bdnt;
	}

	private function genererChpsNumTaxon($nto){
		$num_taxon = '';
		if (preg_match("/^[AB]([0-9]+)$/", $nto, $retour)) {
			$num_taxon = intval($retour[1]);
		} elseif($nto == 'nc') {
			$num_taxon = 0;
		} else {
			$num_taxon = intval($nto);
		}
		return $num_taxon;
	}

	private function genererChpsNumNomen($nno) {
		$num_nomen = '';
		if (preg_match("/^[AB]([0-9]+)$/", $nno, $retour)) {
			$num_nomen = intval($retour[1]);
		} elseif ($nno == 'nc') {
			$num_nomen = 0;
		} else {
			$num_nomen = intval($nno);
		}
		return $num_nomen;
	}

	 private function remplirChamps($cle, $valeurs) {
		foreach ($valeurs as $nomChamp => $valeurChamp) {
			$valeurChamp = $this->getBdd()->proteger($valeurChamp);
			$requete = "UPDATE {$this->table} SET $nomChamp = $valeurChamp WHERE cle = $cle ";
			$resultat = $this->getBdd()->requeter($requete);
			if ($resultat === false) {
				throw new Exception("Erreur d'insertion pour le tuple clé = $cle");
			}
		}
	}

	//+------------------------------------------------------------------------------------------------------+
	// chargements, suppression, exécution

	protected function chargerMetadonnees() {
		$contenuSql = $this->recupererContenu(Config::get('chemins.metadonnees'));
		$this->executerScriptSql($contenuSql);
	}

	private function chargerOntologies() {
		$chemin = Config::get('chemins.ontologies');
		$table = Config::get('tables.ontologies');
		$requete = "LOAD DATA INFILE '$chemin' ".
			"REPLACE INTO TABLE $table ".
			'CHARACTER SET utf8 '.
			'FIELDS '.
			"	TERMINATED BY '\t' ".
			"	ENCLOSED BY '' ".
			"	ESCAPED BY '\\\' "
			;
		$this->getBdd()->requeter($requete);
	}

	protected function chargerStructureSql() {
		$contenuSql = $this->recupererContenu(Config::get('chemins.structureSql'));
		$this->executerScriptSql($contenuSql);
	}

	protected function executerScriptSql($sql) {
		$requetes = Outils::extraireRequetes($sql);
		foreach ($requetes as $requete) {
			$this->getBdd()->requeter($requete);
		}
	}

	private function chargerDonnees() {
		$nb_err = $this->verifierFichier();
		if ($nb_err > 0) {
			$e = "Je ne peux pas charger les données car le fichier comporte des erreurs.".
					"Voir le fichier baseflor_verif.txt\n";
			throw new Exception($e);
		}

		$table = Config::get('tables.donnees');
		$requete = "LOAD DATA INFILE '".Config::get('chemins.donnees')."' ".
			"REPLACE INTO TABLE $table ".
			'CHARACTER SET utf8 '.
			'FIELDS '.
			"	TERMINATED BY '\t' ".
			"	ENCLOSED BY '' ".
			"	ESCAPED BY '\\\'";
		$this->getBdd()->requeter($requete);
	}

	private function supprimerTous() {
		// TODO : rajouter une boucle utilisant un parametre de config stockant toutes les versions pour supprimer les tables
		$requete = "DROP TABLE IF EXISTS baseflor_meta, baseflor_ontologies, ".
			" baseflor_v2012_03_19, ".
			" baseflor_v2012_05_08, baseflor_rang_sup_ecologie_v2012_05_08, baseflor_index_v2012_05_08, ".
			" baseflor_v2012_12_31, baseflor_rang_sup_ecologie_v2012_12_31, baseflor_index_v2012_12_31,  ".
			" baseflor_v2013_07_04, baseflor_rang_sup_ecologie_v2013_07_04, baseflor_index_v2013_07_04";
		$this->getBdd()->requeter($requete);
	}

	//++------------------------------------verifierFichier------------------------------------------++//

	private function  getClasseBaseflorVerif() {
		$conteneur = new Conteneur();
		require_once dirname(__FILE__)."/BaseflorVerif.php";
		$verif = new BaseflorVerif($conteneur,'baseflor');
		return $verif;
	}

	private function verifierFichier() {
		$verif = $this->getClasseBaseflorVerif();
		$nb_erreurs = $verif->verifierFichier(Config::get('chemins.donnees'));
		return $nb_erreurs;
	}

}
?>