<?php
/**
 * Exemple de lancement du script : /opt/lampp/bin/php cli.php ontologie -a analyser
 *
 */
require_once dirname(__FILE__).DS.'Traduction.php';

class Prometheus extends EfloreScript {

	private $projetDossier = '';
	private $lecteur = null;
	private $fichier = '';
	private $lotsTermes = array();
	private $lotsRelations = array();
	private $lotsImages = array();
	private $lotsPublications = array();
	private $lotsAuteurs = array();
	private $lotsHierarchie = array();
	private $baseIdGroupe = 10000;
	private $baseIdSousGroupe = 20000;
	private $types = array(
		'FREQUENCY_MODIFIERS' => 2,
		'QUALIFIERS' => 3,
		'RELATIVE_MODIFIERS' => 4,
		'RELATIVE_VALUES' => 5,
		'SPATIAL_MODIFIERS' => 6,
		'LOCATER_REGIONS' => 7,
		'TEMPORAL_MODIFIERS' => 8,
		'UNIT_TERMS' => 9,
		'QUANTITATIVE_PROPERTIES' => 10,
		'NEW_QUALITATIVE_PROPERTIES' => 11,
		'DISALLOWED_TERMS' => 20,
		'QUALITATIVE_STATES' => 13,
		'TYPE_OF_STRUCTURE_TERMS' => 14,
		'STRUCTURE_TERMS' => 15,
		'REGION_TERMS' => 16,
		'GENERIC_STRUCTURES' => 17);

	public function executer() {
		try {
			$this->initialiserProjet('prometheus');
			$this->projetDossier = Config::get('dossierTsv');

			$this->fichier = $this->projetDossier.'Ontology.xml';
			// Lancement de l'action demandée
			$cmd = $this->getParametre('a');
		    switch ($cmd) {
		    	case 'chargerTous' :
		    		$this->chargerStructureSql();
		    		$this->lireFichierXml();
		    		break;
		    	case 'chargerStructureSql' :
		    		$this->chargerStructureSql();
		    		break;
		    	case 'analyser' :
					$this->vider();
		    		$this->lireFichierXml();
					break;
				case 'chargerTraductions' :
					$this->viderTraductions();
					$this->chargerTraductions();
					break;
		    	case 'vider' :
		    		$this->vider();
		    	case 'viderTraductions' :
		    		$this->viderTraductions();
		    		break;
		    	case 'supprimerTous' :
		    		$this->supprimerTous();
		    		break;
				default :
					throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
			}
		} catch (Exception $e) {
			$this->traiterErreur($e->getMessage());
		}
    }


    /**
	 * Lit le fichier OSM et lance l'analyse des noeuds xml en fonction de leur type.
	 */
	private function lireFichierXml() {
		$termes = array_keys($this->types);
		$this->lecteur = new XMLReader();
		if ($this->ouvrirFichierXml()) {
			while ($this->lecteur->read()) {
				if ($this->lecteur->nodeType == XMLREADER::ELEMENT) {
					if (in_array($this->lecteur->localName, $termes)) {
						$noeud = $this->obtenirNoeudSimpleXml();
						$type = $this->lecteur->localName;
						$this->traiterTermes($this->types[$type], $noeud->children());
					} else if ($this->lecteur->localName == 'STATE_GROUPS') {
						$noeud = $this->obtenirNoeudSimpleXml();
						$this->traiterGroupes($noeud->children());
					} else if ($this->lecteur->localName == 'NewQualitativeProperties') {
						$noeud = $this->obtenirNoeudSimpleXml();
						$this->traiterNouvellesQualites($noeud->children());
					} else if ($this->lecteur->localName == 'RELATIONSHIPS') {
						$noeud = $this->obtenirNoeudSimpleXml();
						$this->traiterRelations($noeud->children());
					} else if ($this->lecteur->localName == 'PICTURES') {
						$noeud = $this->obtenirNoeudSimpleXml();
						$this->traiterImages($noeud->children());
					} else if ($this->lecteur->localName == 'CITATIONS') {
						$noeud = $this->obtenirNoeudSimpleXml();
						$this->traiterCitations($noeud->children());
					} else if ($this->lecteur->localName == 'AUTHORS') {
						$noeud = $this->obtenirNoeudSimpleXml();
						$this->traiterAuteurs($noeud->children());
					} else if ($this->lecteur->localName == 'TreeNode') {
						$noeud = $this->obtenirNoeudSimpleXml();
						$this->traiterHierarchie($noeud);
					}
				}
			}
			if (count($this->lotsTermes) > 0) {
				$this->insererLotDeTermes();
			}
			if (count($this->lotsRelations) > 0) {
				$this->insererLotDeRelations();
			}
			if (count($this->lotsImages) > 0) {
				$this->insererLotImages();
			}
			if (count($this->lotsPublications) > 0) {
				$this->insererLotDePublications();
			}
			if (count($this->lotsAuteurs) > 0) {
				$this->insererLotAuteurs();
			}
			if (count($this->lotsHierarchie) > 0) {
				$this->insererLotHierarchie();
			}
		}
	}

	private function ouvrirFichierXml() {
		if ($this->lecteur->open($this->fichier) === false) {
			throw new Exception("Impossible d'ouvrir le fichier XML : $this->fichier");
		}
		return true;
	}

	private function obtenirNoeudSimpleXml() {
		$doc = new DOMDocument;
		$element = $this->lecteur->expand();
		$noeud = simplexml_import_dom($doc->importNode($element, true));
		return $noeud;
	}

	private function traiterTermes($type, $termes) {
		foreach ($termes as $terme) {
			$id = (int) $terme->attributes()->GLOBALID;
			if (isset($this->lotsTermes[$id]) === false) {
				$valeur = array();
				$valeur[] = (int) $id;
				$valeur[] = (int) $type;
				$valeur[] = (string) $terme->attributes()->term;
				$valeur[] = (string) $this->obtenirDefinition($terme);
				$valeur[] = (int) $this->obtenirPreference($terme);
				$valeur[] = (int) $this->obtenirAuteur($terme);
				$valeur[] = (int) $this->obtenirCitation($terme);
				$valeur[] = (int) $this->obtenirImage($terme);
				$this->lotsTermes[$id] = $valeur;
			}
			if (isset($terme->attributes()->parentID)) {
				$relation = array();
				$relation[] = (int) $terme->attributes()->GLOBALID;
				$relation[] = (int) $terme->attributes()->parentID;
				$relation[] = 'A POUR PARENT';
				$this->ajouterRelation($relation);
			}
			if (isset($terme->attributes()->PARENT_STRUCTURE_ID)) {
				$relation = array();
				$relation[] = (int) $terme->attributes()->GLOBALID;
				$relation[] = (int) $terme->attributes()->PARENT_STRUCTURE_ID;
				$relation[] = 'A POUR STRUCTURE PARENTE';
				$this->ajouterRelation($relation);
			}
			if (isset($terme->attributes()->stateOfNEWPROPERTYID)) {
				$relation = array();
				$relation[] = (int) $terme->attributes()->GLOBALID;
				$relation[] = (int) $terme->attributes()->stateOfNEWPROPERTYID;
				$relation[] = 'A POUR NOUVELLE QUALITE';
				$this->ajouterRelation($relation);
			}
		}
	}

	private function obtenirDefinition($terme) {
		$definition = null;
		if (isset($terme->DEFINITION)) {
			$definition = $terme->DEFINITION;
		}
		return $definition;
	}

	private function obtenirPreference($terme) {
		$preference = '1';
		if (isset($terme->attributes()->PREFERRED_TERM)) {
			$valeur = (string) $terme->attributes()->PREFERRED_TERM;
			$preference = (trim($valeur) == 'Disallowed Term') ? '0' : '1';
		}
		return $preference;
	}

	private function obtenirAuteur($terme) {
		$auteur = 0;
		if (isset($terme->attributes()->authorID)) {
			$auteur = $terme->attributes()->authorID;
		} elseif (isset($terme->attributes()->authorREF)) {
			$auteur = $terme->attributes()->authorREF;
		}
		return $auteur;
	}

	private function obtenirCitation($terme) {
		$citation = 0;
		if (isset($terme->attributes()->citationID)) {
			$citation = $terme->attributes()->citationID;
		} elseif (isset($terme->attributes()->citationREF)) {
			$citation = $terme->attributes()->citationREF;
		}
		return $citation;
	}

	private function obtenirImage($terme) {
		$image = 0;
		if (isset($terme->attributes()->pictureREF)) {
			$image = $terme->attributes()->pictureREF;
		}
		return $image;
	}

	private function traiterGroupes($groupes) {
		foreach ($groupes as $groupe) {
			$id = $this->baseIdGroupe + (int) $groupe->attributes()->GROUP_ID;
			if (isset($this->lotsTermes[$id]) === false) {
				$valeur = array();
				$valeur[] = (int) $id;
				$valeur[] = 18;
				$valeur[] = (string) $groupe->attributes()->groupName;
				$valeur[] = '';
				$valeur[] = 1;
				$valeur[] = 0;
				$valeur[] = 0;
				$valeur[] = 0;
				$this->lotsTermes[$id] = $valeur;
			}
			if (isset($groupe->STRUCTURES_LINKED_TO_GROUP)) {
				foreach ($groupe->STRUCTURES_LINKED_TO_GROUP->children() as $structure) {
					$relation = array();
					$relation[] = (int) $structure->attributes()->GLOBALID;
					$relation[] = (int) $id;
					$relation[] = 'A POUR GROUPE';
					$this->ajouterRelation($relation);
				}
			}
			if (isset($groupe->STATES_IN_GROUP)) {
				foreach ($groupe->STATES_IN_GROUP->children() as $etat) {
					$relation = array();
					$relation[] = (int) $id;
					$relation[] = (int) $etat->attributes()->GLOBALID;
					$relation[] = 'A POUR ETAT';
					$this->ajouterRelation($relation);
				}
			}
			if (isset($groupe->STATESUBGROUPS)) {
				$this->traiterSousGroupes($id, $groupe->STATESUBGROUPS->children());
			}
		}
	}

	private function traiterSousGroupes($idGroupe, $sousGroupes) {
		foreach ($sousGroupes as $sg) {
			$id = $this->baseIdSousGroupe + (int) $sg->attributes()->STATESUBGROUP_GLOBALID;
			if (isset($this->lotsTermes[$id]) === false) {
				$valeur = array();
				$valeur[] = (int) $id;
				$valeur[] = 19;
				$valeur[] = (string) $sg->attributes()->subgGroupName;
				$valeur[] = '';
				$valeur[] = 1;
				$valeur[] = 0;
				$valeur[] = 0;
				$valeur[] = 0;
				$this->lotsTermes[$id] = $valeur;

				$relation = array();
				$relation[] = (int) $idGroupe;
				$relation[] = (int) $id;
				$relation[] = 'A POUR SOUS-GROUPE';
				$this->ajouterRelation($relation);
			}
			if (isset($sg->STATES_IN_SUBGROUP)) {
				foreach ($sg->STATES_IN_SUBGROUP->children() as $etat) {
					$relation = array();
					$relation[] = (int) $id;
					$relation[] = (int) $etat->attributes()->GLOBALID;
					$relation[] = 'A POUR ETAT';
					$this->ajouterRelation($relation);
				}
			}
		}
	}

	private function traiterNouvellesQualites($qualites) {
		foreach ($qualites as $qualite) {
			$id = (int) $qualite->attributes()->IDSEQ;
			if (isset($this->lotsTermes[$id]) === false) {
				$valeur = array();
				$valeur[] = (int) $id;
				$valeur[] = 11;
				$valeur[] = (string) $qualite->attributes()->term;
				$valeur[] = (string) $this->obtenirDefinition($terme);
				$valeur[] = (int) $this->obtenirPreference($terme);
				$valeur[] = (int) $this->obtenirAuteur($terme);
				$valeur[] = (int) $this->obtenirCitation($terme);
				$valeur[] = (int) $this->obtenirImage($terme);
				$this->lotsTermes[$id] = $valeur;
			}
			if (isset($qualite->attributes()->ParentPropertyID)) {
				$relation = array();
				$relation[] = (int) $qualite->attributes()->IDSEQ;
				$relation[] = (int) $qualite->attributes()->ParentPropertyID;
				$relation[] = 'A POUR PARENT';
				$this->ajouterRelation($relation);
			}
			if (isset($qualite->MemberStates)) {
				$etats = $qualite->MemberStates->children();
				$idParent = $qualite->attributes()->IDSEQ;
				$this->traiterEtatsMembre($etats, $idParent);
			}
			if (isset($qualite->Structures_linked_to_Property)) {
				$structures = $qualite->Structures_linked_to_Property->children();
				$idParent = $qualite->attributes()->IDSEQ;
				$this->traiterStructuresLiees($structures, $idParent);
			}
			if (isset($qualite->ContextGroups)) {
				$contextes = $qualite->ContextGroups->children();
				if (count($contextes) > 0) {
					foreach ($contextes as $contexte) {
						$idParent = $contexte->attributes()->ID;
						$structures = $contexte->Structures_linked_to_Context->children();
						$this->traiterStructuresLiees($structures, $idParent);
						$etats = $contexte->MemberStates->children();
						$this->traiterEtatsMembre($etats, $idParent);
					}
				}
			}
		}
	}

	private function ajouterRelation($relation) {
		$id = implode('-', $relation);
		if (isset($this->lotsRelations[$id]) === false) {
			$this->lotsRelations[$id] = $relation;
		}
	}

	private function traiterEtatsMembre($etats, $idParent) {
		if (count($etats) > 0) {
			foreach ($etats as $etat) {
				$relation = array();
				$relation[] = (int) $idParent;
				$relation[] = (int) $etat->attributes()->RefID;
				$relation[] = 'A POUR ETAT';
				$this->ajouterRelation($relation);
			}
		}
	}

	private function traiterStructuresLiees($structures, $idParent) {
		if (count($structures) > 0) {
			foreach ($structures as $structure) {
				$relation = array();
				$relation[] = (int) $structure->attributes()->RefID;
				$relation[] = (int) $idParent;
				$relation[] = 'A POUR PROPRIETE';
				$this->ajouterRelation($relation);
			}
		}
	}

	private function traiterRelations($relations) {
		foreach ($relations as $rel) {
			$relation = array();
			$relation[] = (int) $rel->attributes()->term1REF;
			$relation[] = (int) $rel->attributes()->term2REF;
			$relation[] = (string) $this->obtenirTypeRelation($rel->attributes()->relationship);
			$this->ajouterRelation($relation);
		}
	}

	private function obtenirTypeRelation($type) {
		switch ($type) {
			case 'ASSOCIATED WITH' :
				$relation = 'ASSOCIE AVEC';
				break;
			case 'IS A PART OF' :
				$relation = 'EST UNE PARTIE DE';
				break;
			case 'IS A TYPE OF' :
				$relation = 'EST UN TYPE DE';
				break;
			default :
				$relation = '';
		}
		return $relation;
	}

	private function traiterImages($images) {
		foreach ($images as $img) {
			$valeur = array();
			$valeur[] = (int) $img->attributes()->ID;
			$valeur[] = (string) $img->attributes()->NAME;
			$valeur[] = (int) $img->attributes()->CITATION_REFID;
			$this->lotsImages[] = $valeur;
		}
	}

	private function traiterCitations($citations) {
		foreach ($citations as $publi) {
			$valeur = array();
			$valeur[] = (int) $publi->attributes()->ID;
			$valeur[] = (int) $publi->attributes()->primaryAuthorREF;
			$valeur[] = (string) $publi->PUBLICATION;
			$valeur[] = (string) $publi->DATE;
			$valeur[] = (string) $publi->PAGE;
			$this->lotsPublications[] = $valeur;
		}
	}

	private function traiterAuteurs($auteurs) {
		foreach ($auteurs as $auteur) {
			$valeur = array();
			$valeur[] = (int) $auteur->attributes()->ID;
			$valeur[] = (string) $auteur->attributes()->givenNames;
			$valeur[] = (string) $auteur->attributes()->surname;
			$valeur[] = $this->obtenirDateNaissance((string) $auteur->attributes()->born);
			$valeur[] = (string) $auteur->attributes()->died;
			$this->lotsAuteurs[] = $valeur;
		}
	}

	private function obtenirDateNaissance($annee) {
		$date = $annee.'-00-00';
		return $date;
	}

	private function traiterHierarchie($noeud) {
		$valeur = array();
		$valeur[] = (int) $noeud->attributes()->ID;
		$valeur[] = (int) $noeud->attributes()->ParentNodeID;
		$valeur[] = (string) $noeud->attributes()->pathAsNames;
		$valeur[] = (string) $noeud->attributes()->pathAsID;
		$valeur[] = (int) $noeud->attributes()->TermID;
		$this->lotsHierarchie[] = $valeur;
	}

	private function insererLotDeTermes() {
		$champs = implode(',', array('id_terme', 'ce_type', 'nom_en', 'description_en', 'preference', 'ce_auteur', 'ce_publication', 'ce_image'));
		$values = $this->creerValues($this->lotsTermes);
		$requete = "INSERT INTO prometheus_ontologies_terme_v1_00 ($champs) VALUES $values";
		$this->executerSql($requete);
	}

	private function insererLotDeRelations() {
		$champs = implode(',', array('id_terme_01', 'id_terme_02', 'relation'));
		$values = $this->creerValues($this->lotsRelations);
		$requete = "INSERT INTO prometheus_ontologies_relation_v1_00 ($champs) VALUES $values";
		$this->executerSql($requete);
	}

	private function insererLotImages() {
		$champs = implode(',', array('id_image', 'uri', 'ce_publication'));
		$values = $this->creerValues($this->lotsImages);
		$requete = "INSERT INTO prometheus_ontologies_image_v1_00 ($champs) VALUES $values";
		$this->executerSql($requete);
	}

	private function insererLotDePublications() {
		$champs = implode(',', array('id_publication', 'ce_auteur_principal', 'titre', 'date', 'uri'));
		$values = $this->creerValues($this->lotsPublications);
		$requete = "INSERT INTO prometheus_ontologies_publication_v1_00 ($champs) VALUES $values";
		$this->executerSql($requete);
	}

	private function insererLotAuteurs() {
		$champs = implode(',', array('id_auteur', 'prenom', 'nom', 'naissance_date', 'deces_date'));
		$values = $this->creerValues($this->lotsAuteurs);
		$requete = "INSERT INTO prometheus_ontologies_auteur_v1_00 ($champs) VALUES $values";
		$this->executerSql($requete);
	}

	private function insererLotHierarchie() {
		$champs = implode(',', array('id_noeud', 'id_noeud_parent', 'chemin_noms', 'chemin_ids', 'ce_terme'));
		$values = $this->creerValues($this->lotsHierarchie);
		$requete = "INSERT INTO prometheus_ontologies_hierarchie_v1_00 ($champs) VALUES $values";
		$this->executerSql($requete);
	}

	private function creerValues($valeurs) {
		$values = array();
		foreach ($valeurs as $valeur) {
			foreach ($valeur as $id => $val) {
				$valeur[$id] = $this->etreVide($val) ? 'NULL' : $this->proteger(trim($val));
			}
			$values[]  = '('.implode(',', $valeur).')';
		}
		$values = implode(',', $values);
		return $values;
	}

	private function etreVide($val) {
		$vide = ($val === null || trim($val) === '') ? true : false;
		return $vide;
	}

	private function executerSql($requete) {
		$this->getBdd()->requeter($requete);
	}

	private function proteger($chaine) {
		return $this->getBdd()->proteger($chaine);
	}

	private function viderTraductions() {
		$requete = 'UPDATE prometheus_ontologies_terme_v1_00 '.
			'SET nom = NULL, description = NULL, notes = NULL';
		$this->executerSql($requete);
	}

	private function chargerTraductions() {
		$dossier = $this->projetDossier.'traductions'.DS;
		$pointeur = opendir($dossier);
		while ($fichierNom = readdir($pointeur)) {
			if (preg_match('/^[.]{1,2}/', $fichierNom) == false) {
				$fichierChemin = $dossier.$fichierNom;
				$lecteur = new LecteurExcel($fichierChemin);
				//$this->verifierStructureFichierExcel($lecteur);
				for ($ligne = 2; $ligne < $lecteur->getNbreLignes(); $ligne++) {
					$traduction = new Traduction();
					$traduction->type = $lecteur->getValeur($ligne, 1);
					$traduction->en = $lecteur->getValeur($ligne, 2);
					$traduction->fr = $lecteur->getValeur($ligne, 3);
					$traduction->sourcesTraduction = $lecteur->getValeur($ligne, 4);
					$traduction->remarques = $lecteur->getValeur($ligne, 5);
					$traduction->sources = $lecteur->getValeur($ligne, 6);
					$traduction->relectureRemarques = $lecteur->getValeur($ligne, 7);
					$this->genererNotes($traduction);

					$this->insererTraduction($traduction);
				}
			}
		}
		closedir($pointeur);
	}

	private function verifierStructureFichierExcel($lecteur) {
		$messages = array();
		$colonnes = array("Type d'élément", "Élément en anglais", "Traduction en français", "Sources de la traduction", "Remarques", "Sources", "Relecture/ Remarques");
		foreach ($colonnes as $numero => $intitule) {
			$valeurBrute = $lecteur->getValeur(1, ($numero + 1));
			if ($valeurBrute != $intitule) {
				$messages[] = "Le fichier {$lecteur->getFichier()} ne contient pas la bonne colonne #$numero : $intitule != $valeurBrute";
			}
		}
		if (count($messages) > 0) {
			throw new Exception(implode("\n", $messages));
		}
	}

	private function genererNotes(Traduction $traduction) {
		$notes = array();
		if ($this->etreVide($traduction->sourcesTraduction) === false) {
			$notes[] = "Sources de la traduction : ".$traduction->sourcesTraduction;
		}
		if ($this->etreVide($traduction->remarques) === false) {
			$notes[] = "Remarques : ".$traduction->remarques;
		}
		if ($this->etreVide($traduction->sources) === false) {
			$notes[] = "Sources : ".$traduction->sources;
		}
		if ($this->etreVide($traduction->relectureRemarques) === false) {
			$notes[] = "Remarques sur la relecture : ".$traduction->relectureRemarques;
		}
		if (count($notes) > 0) {
			$traduction->notes = implode("\n", $notes);
		}
	}

	private function insererTraduction($traduction) {
		$requete = null;
		$notes = $traduction->notes;
		if ($traduction->type == 'term') {
			$notes = $this->proteger("Nom :\n".$notes);
			$nom = $this->proteger($traduction->fr);
			$nomEn = $this->proteger($traduction->en);
			$requete = "UPDATE prometheus_ontologies_terme_v1_00 ".
				"SET nom = $nom, notes = $notes ".
				"WHERE nom_en = $nomEn ";
		} else if ($traduction->type == 'DEFINITION') {
			$notes = $this->proteger("Description :\n".$notes);
			$description = $this->proteger($traduction->fr);
			$descriptionEn = $this->proteger($traduction->en);
			$requete = "UPDATE prometheus_ontologies_terme_v1_00 ".
				"SET description = $description, notes = CONCAT(notes, '\n', $notes) ".
				"WHERE description_en = $descriptionEn ";
		}
		if ($requete != null) {
			$this->executerSql($requete);
		}
	}

	private function vider() {
		$requete = 'TRUNCATE TABLE IF EXISTS prometheus_ontologies_auteur_v1_00';
		$this->executerSql($requete);
		$requete = 'TRUNCATE TABLE IF EXISTS prometheus_ontologies_hierarchie_v1_00';
		$this->executerSql($requete);
		$requete = 'TRUNCATE TABLE IF EXISTS prometheus_ontologies_image_v1_00';
		$this->executerSql($requete);
		$requete = 'TRUNCATE TABLE IF EXISTS prometheus_ontologies_publication_v1_00';
		$this->executerSql($requete);
		$requete = 'TRUNCATE TABLE IF EXISTS prometheus_ontologies_relation_v1_00';
		$this->executerSql($requete);
		$requete = 'TRUNCATE TABLE IF EXISTS prometheus_ontologies_terme_v1_00';
		$this->executerSql($requete);
	}

	private function supprimerTous() {
		$requete = "DROP TABLE IF EXISTS prometheus_meta, prometheus_ontologies_auteur_v1_00, prometheus_ontologies_hierarchie_v1_00,
			prometheus_ontologies_image_v1_00, prometheus_ontologies_publication_v1_00, prometheus_ontologies_relation_v1_00,
			prometheus_ontologies_terme_v1_00, prometheus_ontologies_type_v1_00";
		$this->getBdd()->requeter($requete);
	}
}
?>