<?php
// declare(encoding='UTF-8');
/**
 * Classe de nettoyage de la base de données du CEL.
 * Suppression des antislash
 * Remplacement des chaines vides par des NULL
 * Unifier les référentiels : pas de nom de version, majuscule...
 * Unifier les certitudes
 * Supprimer les INSEE-C: non rempli, les dates 0000-00 
 *
 * Utilisation :
 * - <code>/opt/lamp/bin/php cli.php nettoyage -a (voir méthode executer)</code>
 *
 * @category   CEL
 * @package    Scripts
 * @subpackage Nettoyage
 * @author     Delphine CAUQUIL <delphine@tela-botanica.org>
 * @license    GPL v3 <http://www.gnu.org/licenses/gpl.txt>
 * @license    CECILL v2 <http://www.cecill.info/licences/Licence_CeCILL_V2-en.txt>
 * @copyright  1999-2016 Tela Botanica <accueil@tela-botanica.org>
 */
class Nettoyage extends Script {

	protected $bdd;
	protected $colonnes;


	public function __construct($script_nom, $parametres_cli) {
		parent::__construct($script_nom, $parametres_cli);
		$this->bdd = new Bdd();
	}

	public function executer() {
		$cmd = $this->getParametre('a');
		$requete_colonnes = "SELECT column_name FROM information_schema.columns WHERE table_name = 'cel_obs' AND table_schema='tb_cel';";
		$this->colonnes = $this->bdd->recupererTous($requete_colonnes);
		try {
			switch ($cmd) {
				case 'tout' :
					$this->supprimerRetourLigne();
					$this->supprimerAntislash();
					$this->supprimerGuillemet();
					$this->unifierNomReferentiel();
					$this->unifierCertitude();
					$this->verifierGeodatum();
					$this->unifierNull();
					break;
				case 'supprimerAntislash' :
					$this->supprimerAntislash();
					break;
				case 'unifierNomReferentiel' :
					$this->unifierNomReferentiel();
					break;
				case 'unifierCertitude' :
					$this->unifierCertitude();
					break;
				case 'verifierGeodatum' :
					$this->verifierGeodatum();
					break;
				case 'unifierNull' : // lancer en dernier pour bien réussir
					$this->unifierNull();
					break;
				default :
					$msg = "Erreur : la commande '$cmd' n'existe pas!\n".
						"Commandes existantes : regenererMotsClesTexteObs, regenererMotsClesTexteImages"
							. ", regenererMotsClesTexteObsTout, regenererMotsClesTexteImagesTout"
							. ", viderMotsClesTexteImagesSansMotsCles, viderMotsClesTexteObsSansMotsCles"
							. ", regenererChampsTailleImage, reparerLiaisonsOublieesObs, reparerLiaisonsOublieesImages"
							. ", reparerLiaisonsObsParMotsClesTexte, reductionMotsClesImages, reductionMotsClesImagesUtil"
							. ", reduireEtNormaliserMotsClesImagesUtil, reduireEtNormaliserMotsClesImages"
							. ", reductionMotsClesObs, reductionMotsClesObsUtil, reduireEtNormaliserMotsClesObsUtil"
							. ", reduireEtNormaliserMotsClesObs";
					throw new Exception($msg);
			}
		} catch (Exception $e) {
			$this->traiterErreur($e->getMessage());
		}
	}
	

	private function supprimerRetourLigne() {		
		foreach ($this->colonnes as $id=>$colonne) {
			echo "Suppression des retour ligne dans la colonne ".$colonne['column_name']." ...";
			$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\n", "")';
			$this->bdd->requeter($requete);
			echo "effectuée \n";
		}
	}
	
	private function supprimerAntislash() {
		foreach ($this->colonnes as $id=>$colonne) {
			echo "Suppression des antislash dans la colonne ".$colonne['column_name']." ...";
			$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\", "")';
			$this->bdd->requeter($requete);
			echo "effectuée \n";
		}
	}
	
	private function supprimerGuillemet() {
		foreach ($this->colonnes as $id=>$colonne) {
			echo "Suppression des guillements dans la colonne ".$colonne['column_name']." ...";
			$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].',"\"", "")';
			$this->bdd->requeter($requete);
			echo "effectuée \n";
		}
	}
	
	private function unifierNull() {
		$requete_colonnes = "SELECT column_name FROM information_schema.columns WHERE table_name = 'cel_obs' AND table_schema='tb_cel';";
		$colonnes = $this->bdd->recupererTous($requete_colonnes);
		foreach ($colonnes as $colonne) {
			echo "Suppression des vides dans la colonne ".$colonne['column_name']." ...";
			$dsl = array( 'transmission', 'altitude');
			if (isset($dsl[$colonne['column_name']])) {
				$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = NULL WHERE ".$colonne['column_name']."= ''";
			} else {
				$requete = "UPDATE cel_obs SET ".$colonne['column_name']." = NULL WHERE ".$colonne['column_name']."= '' OR ".$colonne['column_name']." IN ('INSEE-C:', '0000-00-00 00:00:00', '0.00000')";
			}
			$this->bdd->requeter($requete);
			echo "effectuée \n";
		}
	}
	
	private function unifierCertitude() {
		echo "Uniformisation du champ certitude/identification";
		$certitude = array("Douteuse" => "douteux",
			"Certainea" => "certain",
			"Certaine" => "certain",
			"A déterminer" => "aDeterminer",
			"à vérifier" => "aDeterminer",
			"Certaine, par Jean Lebail" => "certain",
			"Certaine (à 99%)" => "certain",
			"sur" => "certain");
		foreach ($certitude as $valeur=>$saisie) {
			$requete = "UPDATE cel_obs SET certitude = '".$saisie."' WHERE certitude ='".$valeur."'";
			$this->bdd->requeter($requete);
			echo "effectuée \n";
		}
		$requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'aDeterminer', '', NULL)";
		$verif = $this->bdd->recupererTous($requete_verif);
		if ($verif != array()) print_r($verif);
	}
	
	private function unifierNomReferentiel() {
		echo "Uniformisation du nom de référentiel";
		$referentiels = array("BDTFX", "BDTRE", "BDTXA", "APD", "LBF", "ISFAN");
		foreach ($referentiels as $referentiel) {
			$requete = "UPDATE cel_obs SET nom_referentiel = '".$referentiel."' WHERE nom_referentiel like '".$referentiel."%'";
			$this->bdd->requeter($requete);
			$requete = "UPDATE cel_obs SET nom_referentiel = NULL WHERE nom_sel_nn = '' or nom_sel_nn = '0'";
			$this->bdd->requeter($requete);
			$requete = "UPDATE cel_obs SET nom_referentiel = 'APD' WHERE nom_referentiel like 'bdtao%'";
			$this->bdd->requeter($requete);
			$requete = "UPDATE cel_obs SET nom_referentiel = 'BDTFX' WHERE nom_referentiel like 'bdnff%'";
			$this->bdd->requeter($requete);
			echo "effectuée \n";
		}
		$requete_verif = "SELECT nom_referentiel FROM cel_obs WHERE nom_referentiel NOT IN ('".implode("', '", $referentiels)."', 'autre')";
		$verif = $this->bdd->recupererTous($requete_verif);
		if ($verif != array()) print_r($verif);
	}
	
	private function verifierGeodatum() {
		echo "Uniformisation du geodatum";
		$requete = "UPDATE cel_obs SET geodatum = 'wgs84' WHERE (latitude IS NOT NULL or latitude != '0.00000') AND geodatum = ''";
		$this->bdd->requeter($requete);
		$requete = "UPDATE cel_obs SET geodatum = NULL WHERE (latitude IS NULL or latitude == '0.00000')";
		$this->bdd->requeter($requete);
		echo "effectuée \n";
	}
}