Rev 1714 | Rev 1750 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
<?php/*** Service fournissant des informations concernant COEL au format RSS1, RSS2 ou ATOM.* Encodage en entrée : utf8* Encodage en sortie : utf8** Notes : pour les requêtes sur la table d'historique cela peut être assez compliqué!* Voir : http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/** @author Jean-Pascal MILCENT <jpm@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>* @version $Id: CoelSyndication.php 381 2010-05-17 17:10:37Z jpm $* @copyright 2009*/class CoelSyndication extends Coel {private $format = null;private $service = null;private $squelette = null;private $squelette_dossier = null;private $squelette_diff = null;private $flux = array();/*** Méthode appelée avec une requête de type GET.*/public function getElement($param = array()) {// Initialisation des variables$info = array();$contenu = '';// Pré traitement des paramêtres$pour_bdd = false;$p = $this->traiterParametresUrl(array('service', 'format'), $param, $pour_bdd);// Récupération de la liste des flux$this->chargerListeDesFlux();// Chargement du bon type de service demandéif (isset($p['service'])) {$this->service = strtolower($p['service']);$methode = $this->getNomMethodeService();if (method_exists($this, $methode)) {if ($this->service != 'liste_des_flux') {if (isset($p['format']) && preg_match('/^(?:rss1|rss2|atom)$/i', $p['format'])) {// Multiplication par deux de la limite car nous récupérons deux lignes par item$this->limit = $this->limit*2;// Mise en minuscule de l'indication du format$this->format = strtolower($p['format']);// Définition du fichier squelette demandé$this->squelette_dossier = dirname(__FILE__).DIRECTORY_SEPARATOR.'squelettes'.DIRECTORY_SEPARATOR;$this->squelette = $this->squelette_dossier.$this->format.'.tpl.xml';$this->squelette_diff = $this->squelette_dossier.'diff.tpl.html';} else {$this->format = '';$this->messages[] = "Le service COEL Syndication nécessite d'indiquer en second paramètre le format : rss1, rss2 ou atom.";}}// Récupération du contenu à renvoyer$contenu = $this->$methode();} else {$this->messages[] = "Le type d'information demandé '$this->service' n'est pas disponible.";}} else {$this->messages[] = "Le service COEL Syndication nécessite d'indiquer en premier paramètre le type d'information demandé.";}// Envoie sur la sortie standard$encodage = 'utf-8';$mime = $this->getTypeMime();$formatage_json = $this->getFormatageJson();$this->envoyer($contenu, $mime, $encodage, $formatage_json);}private function getUrlServiceBase() {$url_service = $this->config['coel']['urlBaseJrest'].'CoelSyndication/'.$this->service.'/'.$this->format;return $url_service;}private function getNomMethodeService() {$methode = '';$service_formate = str_replace(' ', '', ucwords(implode(' ', explode('_', $this->service))));$methode = 'getService'.$service_formate;return $methode;}private function getTypeMime() {$mime = '';switch ($this->format) {case 'atom' :$mime = 'application/atom+xml';break;case 'rss1' :case 'rss2' :$mime = 'application/rss+xml';break;default:$mime = 'text/html';}return $mime;}private function getFormatageJson() {$json = false;switch ($this->service) {case 'liste_des_flux' :$json = true;break;default:$json = false;}return $json;}private function getFlux($nom) {$nom = strtolower($nom);return isset($this->flux[$nom]) ? $this->flux[$nom] : array();}private function setFlux($nom, $titre, $description) {$url_base = $this->config['coel']['urlBaseJrest'].'CoelSyndication/';$formats = array('atom', 'rss2', 'rss1');$flux = array();foreach ($formats as $format) {$url = $url_base.$nom.'/'.$format;$flux[$format] = $url;}$this->flux[$nom] = array('titre' => $titre, 'description' => $description, 'urls' => $flux);}private function chargerListeDesFlux() {$this->setFlux('projet', 'Flux de syndication des projets','Ce flux fournit des informations sur les mises à jour des projets saisis dans COEL.');$this->setFlux('structure','Flux de syndication des institutions','Ce flux fournit des informations sur les mises à jour des institutions saisies dans COEL.');$this->setFlux('collection', 'Flux de syndication des Collections','Ce flux fournit des informations sur les mises à jour des collections saisies dans COEL.');$this->setFlux('personne', 'Flux de syndication des personnes','Ce flux fournit des informations sur les mises à jour des personnes saisies dans COEL.');$this->setFlux('publication', 'Flux de syndication des publications','Ce flux fournit des informations sur les mises à jour des publications saisies dans COEL.');$this->setFlux('commentaire', 'Flux de syndication des notes','Ce flux fournit des informations sur les mises à jour des notes saisies dans COEL.');}private function getServiceListeDesFlux() {return $this->flux;}private function getServiceProjet() {// Construction de la requête$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, '.' h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' h1.cmhl_cle_ligne AS guid, '." 'prj' AS guid_type, ".' cp_fmt_nom_complet AS modifier_par '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne ON (h1.cmhl_ce_modifier_par = cp_id_personne) '.'WHERE h1.cmhl_ce_table = 115 %s '.'GROUP BY h1.cmhl_cle_ligne, h1.cmhl_date_modification, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);// Création du contenu$contenu = $this->executerService('cpr_nom', $elements);return $contenu;}private function getServiceStructure() {$elements = array();$elements = array_merge($elements, $this->getHistoriqueTableStructure(120));$elements = array_merge($elements, $this->getHistoriqueTableStructure(122));$elements = array_merge($elements, $this->getHistoriqueTableStructure(123));$elements = array_merge($elements, $this->getHistoriqueStructureAPersonne());krsort($elements);$elements = array_slice($elements, 0, ($this->limit/2));// Création du contenu$contenu = $this->executerService('cs_nom', $elements);return $contenu;}private function getHistoriqueTableStructure($table_id) {$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' h1.cmhl_cle_ligne AS guid, '." 'str' AS guid_type, ".(($table_id != 120) ? ' CONCAT(IF (h1.cmhl_ce_table = 122, "Conservation", "Valorisation"), " - ", cs_nom) AS titre, ' : '').' cp_fmt_nom_complet AS modifier_par '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne ON (h1.cmhl_ce_modifier_par = cp_id_personne) '.(($table_id != 120) ? ' LEFT JOIN coel_structure ON (cs_id_structure = h1.cmhl_cle_ligne) ' : '')."WHERE h1.cmhl_ce_table = $table_id %s ".(($table_id != 120) ? ' AND h1.cmhl_ce_etat != 3 ' : '').'GROUP BY h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);return $elements;}private function getHistoriqueStructureAPersonne() {$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' h1.cmhl_cle_ligne AS guid, '." 'csap' AS guid_type, ".' p1.cp_fmt_nom_complet AS modifier_par, '.' CONCAT(IF(h1.cmhl_ce_etat = 1, "Ajout", IF (h1.cmhl_ce_etat = 3, "Suppression", "Modification")), " d\'une personne liée à «", s1.cs_nom, "»") AS titre, '.' CONCAT("Personne «", p2.cp_fmt_nom_complet, "» liée à «", s1.cs_nom, "» avec rôle «", lv1.cmlv_nom, "»") AS description '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne AS p1 ON (h1.cmhl_ce_modifier_par = p1.cp_id_personne) '.' LEFT JOIN coel_personne AS p2 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', 1) = p2.cp_id_personne) ".' LEFT JOIN coel_structure AS s1 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', -1) = s1.cs_id_structure) ".' LEFT JOIN coel_meta_liste_valeur AS lv1 '." ON (SUBSTRING_INDEX(SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', 2), '-', -1) = lv1.cmlv_id_valeur) ".'WHERE h1.cmhl_ce_table = 121 %s '.'GROUP BY h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);return $elements;}private function getServiceCollection() {$elements = array();$elements = array_merge($elements, $this->getHistoriqueTableCollection(101));$elements = array_merge($elements, $this->getHistoriqueTableCollection(106));$elements = array_merge($elements, $this->getHistoriqueCollectionAPersonne());$elements = array_merge($elements, $this->getHistoriqueCollectionAPublication());$elements = array_merge($elements, $this->getHistoriqueCollectionACommentaire());krsort($elements);$elements = array_slice($elements, 0, ($this->limit/2));//echo '<pre>'.print_r($elements, true).'</pre>';// Création du contenu$contenu = $this->executerService('cc_nom', $elements);return $contenu;}private function getHistoriqueTableCollection($table_id) {// Reque générale avec paramêtres$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' h1.cmhl_cle_ligne AS guid, '." 'col' AS guid_type, ".(($table_id == 106) ? ' CONCAT("Botanique", " - ", cc_nom) AS titre, ' : '').' cp_fmt_nom_complet AS modifier_par '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne ON (h1.cmhl_ce_modifier_par = cp_id_personne) '.(($table_id == 106) ? ' LEFT JOIN coel_collection ON (cc_id_collection = h1.cmhl_cle_ligne) ' : '')."WHERE h1.cmhl_ce_table = $table_id %s ".(($table_id == 106) ? ' AND h1.cmhl_ce_etat != 3 ' : '').'GROUP BY h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);return $elements;}private function getHistoriqueCollectionAPersonne() {$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' p1.cp_fmt_nom_complet AS modifier_par, '.' h1.cmhl_cle_ligne AS guid, '." 'ccap' AS guid_type, ".' CONCAT(IF(h1.cmhl_ce_etat = 1, "Ajout", IF (h1.cmhl_ce_etat = 3, "Suppression", "Modification")), " d\'une personne liée à «", c1.cc_nom, "»") AS titre, '.' CONCAT("Personne «", p2.cp_fmt_nom_complet, "» liée à «", c1.cc_nom, "» avec rôle «", lv1.cmlv_nom, "»") AS description '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne AS p1 ON (h1.cmhl_ce_modifier_par = p1.cp_id_personne) '.' LEFT JOIN coel_personne AS p2 '." ON (SUBSTRING_INDEX(SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', 2), '-', -1) = p2.cp_id_personne) ".' LEFT JOIN coel_collection AS c1 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', 1) = c1.cc_id_collection) ".' LEFT JOIN coel_meta_liste_valeur AS lv1 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', -1) = lv1.cmlv_id_valeur) ".'WHERE h1.cmhl_ce_table = 103 %s '.'GROUP BY h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);return $elements;}private function getHistoriqueCollectionAPublication() {$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' p1.cp_fmt_nom_complet AS modifier_par, '.' h1.cmhl_cle_ligne AS guid, '." 'ccapu' AS guid_type, ".' CONCAT(IF(h1.cmhl_ce_etat = 1, "Ajout", IF (h1.cmhl_ce_etat = 3, "Suppression", "Modification")), " d\'une publication liée à «", c1.cc_nom, "»") AS titre, '.' CONCAT("Publication «", p2.cpu_fmt_nom_complet, "» liée à «", c1.cc_nom, "»") AS description '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne AS p1 ON (h1.cmhl_ce_modifier_par = p1.cp_id_personne) '.' LEFT JOIN coel_publication AS p2 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', -1) = p2.cpu_id_publication) ".' LEFT JOIN coel_collection AS c1 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', 1) = c1.cc_id_collection) ".'WHERE h1.cmhl_ce_table = 104 %s '.'GROUP BY h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);return $elements;}private function getHistoriqueCollectionACommentaire() {$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' p1.cp_fmt_nom_complet AS modifier_par, '.' h1.cmhl_cle_ligne AS guid, '." 'ccacm' AS guid_type, ".' CONCAT(IF(h1.cmhl_ce_etat = 1, "Ajout", IF (h1.cmhl_ce_etat = 3, "Suppression", "Modification")), " d\'un commentaire lié à «", c1.cc_nom, "»") AS titre, '.' CONCAT("Commentaire «", c.ccm_titre, "» liée à «", c1.cc_nom, "»") AS description '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne AS p1 ON (h1.cmhl_ce_modifier_par = p1.cp_id_personne) '.' LEFT JOIN coel_commentaire AS c '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', -1) = c.ccm_id_commentaire) ".' LEFT JOIN coel_collection AS c1 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', 1) = c1.cc_id_collection) ".'WHERE h1.cmhl_ce_table = 102 %s '.'GROUP BY h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);return $elements;}private function getServicePersonne() {$elements = array();$elements = array_merge($elements, $this->getHistoriqueTable(113, 'per'));$elements = array_merge($elements, $this->getHistoriquePublicationAPersonne());krsort($elements);$elements = array_slice($elements, 0, ($this->limit/2));//echo '<pre>'.print_r($elements, true).'</pre>';// Création du contenu$contenu = $this->executerService('cp_fmt_nom_complet', $elements);return $contenu;}private function getHistoriquePublicationAPersonne() {$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' p1.cp_fmt_nom_complet AS modifier_par, '.' h1.cmhl_cle_ligne AS guid, '." 'cpap' AS guid_type, ".' CONCAT(IF(h1.cmhl_ce_etat = 1, "Ajout", IF (h1.cmhl_ce_etat = 3, "Suppression", "Modification")), " d\'une publication liée à «", p2.cp_fmt_nom_complet, "»") AS titre, '.' CONCAT("Publication «", pu.cpu_fmt_nom_complet, "» liée à «", p2.cp_fmt_nom_complet, "» avec rôle «", lv1.cmlv_nom, "»") AS description '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne AS p1 ON (h1.cmhl_ce_modifier_par = p1.cp_id_personne) '.' LEFT JOIN coel_publication AS pu '." ON (SUBSTRING_INDEX(SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', 2), '-', -1) = pu.cpu_id_publication) ".' LEFT JOIN coel_personne AS p2 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', 1) = p2.cp_id_personne) ".' LEFT JOIN coel_meta_liste_valeur AS lv1 '." ON (SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', -1) = lv1.cmlv_id_valeur) ".'WHERE h1.cmhl_ce_table = 119 '.' %s '." AND SUBSTRING_INDEX(h1.cmhl_cle_ligne, '-', -1) IN (2361,2362,2363) ".'GROUP BY h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);return $elements;}private function getServicePublication() {$elements = array();$elements = $this->getHistoriqueTable(118, 'pub');krsort($elements);$elements = array_slice($elements, 0, ($this->limit/2));//echo '<pre>'.print_r($elements, true).'</pre>';// Création du contenu$contenu = $this->executerService('cpu_fmt_nom_complet', $elements);return $contenu;}private function getServiceCommentaire() {$elements = array();$elements = $this->getHistoriqueTable(107, 'com');krsort($elements);$elements = array_slice($elements, 0, ($this->limit/2));//echo '<pre>'.print_r($elements, true).'</pre>';// Création du contenu$contenu = $this->executerService('ccm_titre', $elements);return $contenu;}private function getHistoriqueTable($table_id, $guid_type) {// Reque générale avec paramêtres$requete = (($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' '.' h1.cmhl_id_historique_ligne, h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_enregistrement, '.' h1.cmhl_date_modification, h1.cmhl_ce_etat, h1.cmhl_ip, '.' h1.cmhl_cle_ligne AS guid, '." '$guid_type' AS guid_type, ".' cp_fmt_nom_complet AS modifier_par '.'FROM coel_meta_historique_ligne AS h1 '.' LEFT JOIN coel_meta_historique_ligne AS h2 '.' ON (h1.cmhl_ce_table = h2.cmhl_ce_table '.' AND h1.cmhl_cle_ligne = h2.cmhl_cle_ligne '.' AND h1.cmhl_date_modification <= h2.cmhl_date_modification ) '.' LEFT JOIN coel_personne ON (h1.cmhl_ce_modifier_par = cp_id_personne) '."WHERE h1.cmhl_ce_table = $table_id %s ".'GROUP BY h1.cmhl_ce_table, h1.cmhl_cle_ligne, h1.cmhl_date_modification '.'HAVING COUNT(*) = %s '.'ORDER BY '.((!is_null($this->orderby)) ? $this->orderby : 'h1.cmhl_date_modification DESC').' '."LIMIT $this->start,$this->limit ";$elements = $this->executerRequeteHistorique($requete);return $elements;}private function executerRequete($requete) {try {$infos = $this->bdd->query($requete)->fetchAll(PDO::FETCH_ASSOC);if ($infos === false) {$this->messages[] = "La requête a retourné aucun résultat.";}} catch (PDOException $e) {$this->messages[] = sprintf($this->getTxt('sql_erreur'), $e->getFile(), $e->getLine(), $e->getMessage());}return $infos;}private function fusionnerEnregistrements($infos) {// Fusion des lignes$elements = array();foreach ($infos as $info) {$id = $info['cmhl_ce_table'].'|'.$info['cmhl_cle_ligne'];if (!isset($elements[$id])) {$elements[$id] = $info;} else {if ($elements[$id]['cmhl_date_modification'] < $info['cmhl_date_modification']) {$elements[$id] = $this->traiterInfosPrecedentes($info, $elements[$id]);} else {$elements[$id] = $this->traiterInfosPrecedentes($elements[$id], $info);}}}// Nettoyage et utilisation de la date pour pouvoir trier le tableau$sortie = array();foreach ($elements as $id => $element) {$element = $this->nettoyerNomChamps($element);$id_avec_date = $element['cmhl_date_modification'].'|'.$id;$sortie[$id_avec_date] = $element;}return $sortie;}private function executerRequeteHistorique($requete) {$elements = array();// Récupération des 1er éléments$requete_elements_1er = sprintf($requete, '', '1');$infos_elements_1er = $this->executerRequete($requete_elements_1er);// Construction de la requête pour récupérer les second éléments$elements_1er_cle_ligne = array();foreach ($infos_elements_1er as $info) {$elements_1er_cle_ligne[] = $info['cmhl_cle_ligne'];}$chaine_1er_elements = 'AND h1.cmhl_cle_ligne IN ('.str_replace(',,',',',implode(',', $elements_1er_cle_ligne)).') ';$requete_elements_2nd = sprintf($requete, $chaine_1er_elements, '2');// Récupération des 2nd éléments$infos_elements_2nd = $this->executerRequete($requete_elements_2nd);// Fusion des 1er et 2nd éléments$infos = array_merge($infos_elements_1er, $infos_elements_2nd);$elements = $this->fusionnerEnregistrements($infos);return $elements;}private function executerService($champ_titre, $elements) {// Prétraitement des données$donnees = $this->construireDonneesCommunesAuFlux($elements);foreach ($elements as $element) {$xml = $this->getXmlHisto($element);$enrg = $this->getTableauDepuisXmlHisto($xml);$diff = $this->getDiffInfos($element);$diff['differences'] = $this->getDiff($element);$diff_html = (!is_null($diff['differences'])) ? Coel::traiterSquelettePhp($this->squelette_diff, $diff) : '';$item = $this->construireDonneesCommunesAuxItems($element);$item['titre'] = $this->creerTitre($champ_titre, $element, $enrg);$item['guid'] = sprintf($this->config['coel']['guid'], 'coel', $element['guid_type'].$element['guid']);$item['lien'] = $this->config['coel']['urlBaseCoel'].'#'.urlencode($item['guid']);$item['description'] = '<p>'.$this->getMessageModif($item).'</p>';$item['description'] .= $this->creerDescription($element, $enrg);$item['description'] .= $diff_html;$item['description'] = $this->nettoyerTexte($item['description']);$item['description_encodee'] = htmlspecialchars($item['description']);$donnees['items'][] = $item;}// Création du contenu à partir d'un template PHP$contenu = Coel::traiterSquelettePhp($this->squelette, $donnees);return $contenu;}private function creerTitre($champ, $element, $enrg) {$titre = '';if (isset($element['titre'])) {$titre = $element['titre'];} else if (isset($element[$champ])) {$titre = $element[$champ];} else if (isset($enrg[$champ])) {$titre = $enrg[$champ];}$titre = $this->nettoyerTexte($titre);return $titre;}private function creerDescription($element, $enrg) {$description = '';if (isset($element['description'])) {$description = $element['description'];}return $description;}private function nettoyerNomChamps($infos) {$sortie = array();foreach ($infos as $champ => $valeur) {if (preg_match('/^__(.+)$/', $champ, $match)) {$sortie[$match[1]] = $valeur;} else {$sortie[$champ] = $valeur;}}return $sortie;}private function traiterInfosPrecedentes($infos_courantes, $infos_precedentes) {$infos_precedentes_traitees = array();foreach ($infos_precedentes as $champ => $valeur) {if ($champ == 'cmhl_date_modification') {$infos_precedentes_traitees['date_prec'] = $valeur;} else if ($champ == 'cmhl_enregistrement') {$infos_precedentes_traitees['enrg_prec'] = $valeur;} else if (preg_match('/^__(.+)$/', $champ, $match)) {$infos_precedentes_traitees[$match[1].'_prec'] = $valeur;}}$sortie = array_merge($infos_courantes, $infos_precedentes_traitees);return $sortie;}private function nettoyerTexte($txt) {$txt = preg_replace('/&(?!amp;)/i', '&', $txt, -1);return $txt;}private function getMessageModif($item) {$message = $item['etat'].' le '.$item['date_maj_simple'].' par '.$item['modifier_par'].' depuis l\'IP '.$item['ip'];return $message;}private function construireDonneesCommunesAuxItems($info) {$item = array();$date_modification_timestamp = strtotime($info['cmhl_date_modification']);$item['date_maj_simple'] = strftime('%A %d %B %Y à %H:%M', $date_modification_timestamp);$item['date_maj_RSS'] = date(DATE_RSS, $date_modification_timestamp);$item['date_maj_ATOM'] = date(DATE_ATOM, $date_modification_timestamp);$item['date_maj_W3C'] = date(DATE_W3C, $date_modification_timestamp);$item['guid'] = $info['cmhl_id_historique_ligne'];$item['cle'] = $info['cmhl_cle_ligne'];$item['ip'] = $info['cmhl_ip'];$item['modifier_par'] = $info['modifier_par'];$item['etat'] = isset($info['cmhl_ce_etat']) ? $this->getTexteEtat($info['cmhl_ce_etat']) : '';return $item;}private function getXmlHisto($info) {$xml = '';if ($info['cmhl_ce_etat'] == '3') {$xml = $info['enrg_prec'];} else {$xml = $info['cmhl_enregistrement'];}return $xml;}private function getDiff($info) {$diff = null;if ($info['cmhl_ce_etat'] == '1') {$nouveau = $this->getTableauDepuisXmlHisto($info['cmhl_enregistrement']);foreach ($nouveau as $cle => $valeur) {$diff[$cle] = array('type' => 'A', 'type_txt' => 'Ajout', 'nouveau' => $valeur, 'ancien' => ' ');}} else if ($info['cmhl_ce_etat'] == '2') {$nouveau = $this->getTableauDepuisXmlHisto($info['cmhl_enregistrement']);$ancien = $this->getTableauDepuisXmlHisto($info['enrg_prec']);foreach ($nouveau as $cle => $valeur) {if (!isset($ancien[$cle])) {$diff[$cle] = array('type' => 'A', 'type_txt' => 'Ajout', 'nouveau' => $valeur, 'ancien' => ' ');} else if (isset($ancien[$cle]) && $ancien[$cle] != $valeur) {$diff[$cle] = array('type' => 'M', 'type_txt' => 'Modification', 'nouveau' => $valeur, 'ancien' => $ancien[$cle]);}}foreach ($ancien as $cle => $valeur) {if (!isset($nouveau[$cle])) {$diff[$cle] = array('type' => 'S', 'type_txt' => 'Suppression', 'nouveau' => ' ', 'ancien' => $valeur);}}}return $diff;}private function getDiffInfos($info) {$diff = null;$format = '%d/%m/%Y à %H:%M:%S';if ($info['cmhl_ce_etat'] == '1') {$diff['date_nouvelle'] = strftime($format, strtotime($info['cmhl_date_modification']));$diff['etat'] = 'A';} else if ($info['cmhl_ce_etat'] == '2') {$diff['date_nouvelle'] = strftime($format, strtotime($info['cmhl_date_modification']));$diff['date_ancienne'] = strftime($format, strtotime($info['date_prec']));$diff['etat'] = 'M';} else if ($info['cmhl_ce_etat'] == '3') {$diff['etat'] = 'S';}return $diff;}private function getTexteEtat($code) {$etat = '';switch ($code) {case '1' :$etat = 'Ajouté';break;case '2' :$etat = 'Modifié';break;case '3' :$etat = 'Supprimé';break;default :$etat = '!Problème!';$e = "Le champ cmhl_ce_etat possède une valeur innatendue : $code";$this->messages[] = $e;}return $etat;}private function construireDonneesCommunesAuFlux($infos) {$donnees = $this->getFlux($this->service);$donnees['guid'] = $this->getUrlServiceBase();$donnees['lien_service'] = htmlentities($this->creerUrlService());$donnees['lien_coel'] = $this->config['coel']['urlBaseCoel'];$donnees['editeur'] = $this->config['coel']['editeur'];$derniere_info_en_date = reset($infos);$date_modification_timestamp = strtotime($derniere_info_en_date['cmhl_date_modification']);$donnees['date_maj_RSS'] = date(DATE_RSS, $date_modification_timestamp);$donnees['date_maj_ATOM'] = date(DATE_ATOM, $date_modification_timestamp);$donnees['date_maj_W3C'] = date(DATE_W3C, $date_modification_timestamp);$donnees['annee_courante'] = date('Y');$donnees['generateur'] = 'COEL - Jrest';preg_match('/([0-9]+)/', '$Revision: 381 $', $match);$donnees['generateur_version'] = $match[1];return $donnees;}private function creerUrlService() {$url_service = $this->getUrlServiceBase();if (isset($this->start) || isset($this->limit)) {$arguments = array();if (isset($this->start) && isset($_GET['start'])) {$arguments[] = 'start='.$this->start;}if (isset($this->limit) && isset($_GET['limit'])) {$arguments[] = 'limit='.($this->limit/2);}if (count($arguments) > 0) {$url_service .= '?'.implode('&', $arguments);}}return $url_service;}}