WebSVN – Sites.obs-saisons.fr – Diff – /trunk/applications/jrest/services/OdsExport.php


<?php
 
class OdsExport extends OdsTriple {
	
	const PREFIXE = 'get';
	const ABBR_LISTE_EVENEMENTS = 'evenement';
	const ABBR_LISTE_ESPECES = 'espece';
	
	private $type_mime = 'text/html';
	
	/**
 	* Méthodes d'extractions d'informations
 	*/
	
	/**
     * Méthode appelée avec une requête de type GET.
     *
     */
    function getElement($param = array()) {
    	
    	$type = $param[0];
    	    	
        if ($type == '*' || is_numeric($type)) {
            $info = $this->getElementParDefaut($param);
        } else {
            $methode = self::PREFIXE.$type;
            if (method_exists($this, $methode)) {
                array_shift($param);
                $info = $this->$methode($param);
            } else {
                $this->messages[] = "Le type d'information demandé '$type' n'est pas disponible.";
            }
        }
       
        $this->envoyer($info,$this->type_mime);
    }
    	
	public function getExportObservation($start = null,$limit = null, $order_by = 'oo_date') {
						
		$requete_selection_observations = 'SELECT * FROM ods_observations '.
									' LEFT JOIN ods_individus '. 
									' ON oi_id_individu = oo_ce_individu'.
									' LEFT JOIN ods_stations '.
									' ON oi_ce_station = os_id_station ';
		
		$requete_selection_observations .= $this->construireConditionRequete();
		$requete_selection_observations .= ' ORDER BY oo_date DESC';
		
		$res_selection_observations = $this->executerRequete($requete_selection_observations);
				
		return $res_selection_observations;
	}
	
	public function construireConditionRequete() {
		
		$condition = ' WHERE oo_date != "0000-00-00" AND DAY(oo_date) != "00" AND ';
		
		foreach($_GET as $cle => $valeur) {
			
			switch($cle) {
			
				case 'type_espece':
					$condition .= 'oi_ce_espece IN (SELECT oe_id_espece FROM ods_especes WHERE oe_ce_type = '.$this->proteger($valeur).')';
				break;
				
				case 'annee':
					if($this->anneesMultiplesDemandees($valeur)) {
						$valeur = rtrim($valeur,',');
						$condition .= 'YEAR(oo_date) IN ('.$valeur.')';						
					} else {
						$condition .= 'YEAR(oo_date) ';
						$condition .= ' = '.$this->proteger($valeur);
					}
				break;
				
				case 'mois':
					$condition .= 'MONTH(oo_date) <= '.$this->proteger($valeur);
				break;
				
				case 'espece':
					$condition .= 'oi_ce_espece IN ('.$valeur.') ';
				break;
				
				case 'evenement':
					$condition .= 'oo_ce_evenement = '.$this->proteger($valeur);
				break;
				
				case 'departement':
					$condition .= 'os_ce_commune LIKE "'.$valeur.'%" ';
				break;
				
				case 'utilisateur':
					$condition .= 'oo_ce_participant = '.$this->proteger($valeur);	
				break;
				
				default:
				break;
			}
			
			$condition .= ' AND ';
		}
		
		$condition = rtrim($condition,'AND ');
		
		$id_demo = $this->config['appli']['id_participant_demo'];
		$id_admin = $this->config['appli']['id_participant_admin'];
		
		$condition .= ' AND oo_ce_participant != '.$id_demo;
		$condition .= ' AND oo_ce_participant != '.$id_admin;
		
		return $condition;
	}
	
	private function anneesMultiplesDemandees($annee) {
		// un ensemble d'identifiants est une suite d'identifiants séparés par des virgules
		// sans virgule terminale
		$reg_exp = "/^(([0-9])+,)*([0-9])+$/";
		return preg_match($reg_exp, $annee);
	}
	
	public function getExportObservationPlat() {
				
		$donnees = $this->getExportObservation();
		$donnees_formatees = $this->formaterPourExportObservationCSV($donnees);	
			
		return $donnees_formatees ;
	}
	
	public function getExportObservationJson() {
		
		$donnees = $this->getExportObservation();
		$donnees_formatees = $this->formaterPourExportObservationJson($donnees);
		
		$this->type_mime = 'application/json';
		
		return $donnees_formatees;
	}
	
	public function formaterPourExportObservationJson($tableau_observations_infos) {
		
		$gestionnaire_especes = new OdsEspece($this->config);
		$gestionnaire_communes = new OdsCommune($this->config);
				
		$especes = $gestionnaire_especes->getToutesEspeces();
		$evenements = $this->obtenirValeursListeParAbreviation(self::ABBR_LISTE_EVENEMENTS);
		
		$resultats_formates = array();
		
		foreach($tableau_observations_infos as $observations_infos) {
		
			$id_espece = $observations_infos['oi_ce_espece'];
			$nom_espece =  $especes[$id_espece]['nom_scientifique'];
				
			$id_evenement = $observations_infos['oo_ce_evenement'];
			$chaine_evenement = $evenements[$id_evenement]['ot_cle'];
				
			$infos_evenement = $this->renvoyerInformationStadeAPartirChaineTriple($chaine_evenement);
				
			$date_observation_formatee = date($this->config['appli']['format_date'], strtotime($observations_infos['oo_date']));
			
			$id_observation = $observations_infos['oo_id_observation'];
			$infos_formatees = array(
				'date' => $date_observation_formatee,
				'evenenement' => $infos_evenement['nom'],
				'code_bbch' => $infos_evenement['numero'],
				'nom_scientifique' => $nom_espece,
				'id_espece' => $id_espece,
				'participant' => $observations_infos['os_ce_participant']
			);
			
			$id_station = $observations_infos['oi_ce_station'];
			
			if(!isset($resultats_formates[$id_station])) {
				$resultats_formates[$id_station]['station'] = $observations_infos['os_nom'];
				$resultats_formates[$id_station]['code_commune'] = $observations_infos['os_ce_commune'];
				//$resultats_formates[$id_station]['nom_commune'] = $gestionnaire_communes->obtenirNomCommuneParCodeInsee($observations_infos['os_ce_commune']);
				$resultats_formates[$id_station]['latitude'] = $observations_infos['os_latitude'];
				$resultats_formates[$id_station]['longitude'] = $observations_infos['os_longitude'];
				$resultats_formates[$id_station]['altitude'] = $observations_infos['os_altitude'];
				$resultats_formates[$id_station]['milieu'] = $observations_infos['os_milieu'];
				$resultats_formates[$id_station]['participant'] = $observations_infos['os_ce_participant'];
			}
			
			$resultats_formates[$id_station]['obs'][$id_observation] = $infos_formatees;
		}
		
		return $resultats_formates;
	}
	
	public function getFichierExportObservationCsv() {
				
		$donnees = $this->getExportObservation();
		$donnees_formatees = $this->formaterPourExportObservationCSV($donnees);		
		$chaine_csv = $this->convertirTableauAssocVersCSV($donnees_formatees);
			
		$this->envoyerFichier($chaine_csv);
	}
	
	public function formaterPourExportObservationCSV($tableau_observations_infos) {
		
		$gestionnaire_especes = new OdsEspece($this->config);
		$gestionnaire_communes = new OdsCommune($this->config);
		$gestionnaire_utilisateurs = new OdsUtilisateur($this->config);
				
		$especes = $gestionnaire_especes->getToutesEspeces();
		$evenements = $this->obtenirValeursListeParAbreviation(self::ABBR_LISTE_EVENEMENTS);
		
		$codes_insee_communes = array();
		
		$resultats_formates = array();
		
		foreach($tableau_observations_infos as $observations_infos) {
		
			$id_espece = $observations_infos['oi_ce_espece'];
			$nom_espece =  $especes[$id_espece]['nom_scientifique'];
				
			$id_evenement = $observations_infos['oo_ce_evenement'];
			$chaine_evenement = $evenements[$id_evenement]['ot_cle'];
				
			$infos_evenement = $this->renvoyerInformationStadeAPartirChaineTriple($chaine_evenement);
				
			$date_observation_formatee = date($this->config['appli']['format_date'], strtotime($observations_infos['oo_date']));
			
			$id_observation = $observations_infos['oo_id_observation'];
			
			$infos_formatees = array(
				'id_observation' => $id_observation,
				'date' => $date_observation_formatee,
				'evenenement' => $infos_evenement['nom'],
				'code_bbch' => $infos_evenement['numero'],
				'nom_scientifique' => $nom_espece,
				'station' => $observations_infos['os_nom'],
				'code_commune' => $observations_infos['os_ce_commune'],
				'nom_commune' => '',
				'latitude' => $observations_infos['os_latitude'],
				'longitude' => $observations_infos['os_longitude'],
				'altitude' => $observations_infos['os_altitude'],
				'milieu' => $observations_infos['os_milieu'],
				'id_participant' => $observations_infos['os_ce_participant'],
				'pseudo_participant' => '',
				'mail_participant' => ''
			);
			
			$resultats_formates[] = $infos_formatees;
			
			if(is_numeric($observations_infos['os_ce_commune'])) {
				$codes_insee_communes[] = $observations_infos['os_ce_commune'];
			}
		}
		
		$correspondance_codes_insee_noms = $gestionnaire_communes->obtenirTableauNomsCommunesParTableauCodesInsee($codes_insee_communes);
		$correspondance_id_utilisateur = $gestionnaire_utilisateurs->getListeUtilisateurFormateeId();
		
		foreach($resultats_formates as &$resultat) {
			
			if(isset($correspondance_codes_insee_noms[$resultat['code_commune']]) && trim($correspondance_codes_insee_noms[$resultat['code_commune']]) != '') {								
				$resultat['nom_commune'] = $correspondance_codes_insee_noms[$resultat['code_commune']];
			}
			
			$resultat['pseudo_participant'] = $correspondance_id_utilisateur[$resultat['id_participant']]['name'];
			$resultat['mail_participant'] = $correspondance_id_utilisateur[$resultat['id_participant']]['mail'];
		}
		
		return $resultats_formates;
	}
	
	public function getExportStationJson() {
    	
    	$donnees = $this->getExportStation();
    	$donnees_formatees = $this->formaterPourExportStationJson($donnees);
    	
    	$this->type_mime = 'application/json';
    	
    	return $donnees_formatees;
    }
    
    public function getExportStation() {
    	
    	$requete_selection_stations = 'SELECT * FROM ods_stations '.
    								  ' LEFT JOIN ods_individus '.
    								  ' ON ods_individus.oi_ce_station = ods_stations.os_id_station '.
    								  ' ORDER BY ods_stations.os_id_station, ods_individus.oi_ce_espece';
    								  
 		$res_selection_stations = $this->executerRequete($requete_selection_stations);
 		
 		return $res_selection_stations;
    }
    
    public function formaterPourExportStationJson($tableau_stations_infos) {
    	
    	$gestionnaire_especes = new OdsEspece($this->config);
		$gestionnaire_communes = new OdsCommune($this->config);
		$gestionnaire_utilisateurs = new OdsUtilisateur($this->config);		
				
		$especes = $gestionnaire_especes->getToutesEspeces();
		
		$resultats_formates = array();
		
		foreach($tableau_stations_infos as &$stations_infos) {
			
			if(!isset($resultats_formates[$stations_infos['os_id_station']])) {
				if($stations_infos['os_ce_commune'] == "NULL") {
					$stations_infos['os_ce_commune'] = '';
				}
				
				$resultats_formates[$stations_infos['os_id_station']] = array(
					'station' => $stations_infos['os_nom'],
					'code_commune' => $stations_infos['os_ce_commune'],
					'nom_commune' => '',
					'latitude' => $stations_infos['os_latitude'],
					'longitude' => $stations_infos['os_longitude'],
					'altitude' => $stations_infos['os_altitude'],
					'id_participant' => $stations_infos['os_ce_participant'],
					'pseudo_participant' => '',
					'mail_participant' => '',
					'especes' => array()
				);
				
				if(is_numeric($stations_infos['os_ce_commune'])) {
					$codes_insee_communes[] = $stations_infos['os_ce_commune'];
				}
 
 
 
 
 
 
 
 
 
 
 
 
			}
 
 
 
 
 
			
			$infos_espece = $especes[$stations_infos['oi_ce_espece']];
			
			if($infos_espece != null) {
				
				if($infos_espece['type'] != "") {
					$infos_espece['type'] = $this->obtenirValeurTripleParId($infos_espece['type']);
				}
				
				$resultats_formates[$stations_infos['os_id_station']]['especes'][$infos_espece['id_espece']] = $infos_espece;
			}
		}
		
		$correspondance_codes_insee_noms = $gestionnaire_communes->obtenirTableauNomsCommunesParTableauCodesInsee($codes_insee_communes);
		$correspondance_id_utilisateur = $gestionnaire_utilisateurs->getListeUtilisateurAvecInfosComplementairesFormateeId();
		
		foreach($resultats_formates as &$resultat) {
			
			if(isset($correspondance_codes_insee_noms[$resultat['code_commune']]) && trim($correspondance_codes_insee_noms[$resultat['code_commune']]) != '') {								
				$resultat['nom_commune'] = $correspondance_codes_insee_noms[$resultat['code_commune']];
			}
			
			$resultat['pseudo_participant'] = $correspondance_id_utilisateur[$resultat['id_participant']]['name'];
			$resultat['mail_participant'] = $correspondance_id_utilisateur[$resultat['id_participant']]['mail'];
			
			$types = array("Particulier" ,"Établissement scolaire", "Association" ,"Professionnel", "Autre","Inconnu");
			
			if(isset($correspondance_id_utilisateur[$resultat['id_participant']]['profile_type'])) {
				$resultat['type_participant'] = $correspondance_id_utilisateur[$resultat['id_participant']]['profile_type'];
			} else {
				$resultat['type_participant'] = "Inconnu";
			}
		}
		
		return $resultats_formates;
    }
	
	public function convertirTableauAssocVersCSV($tableau) {
		
		$csv = '';
		$colonnes = array_keys($tableau[0]);
		$csv .= implode(';',$colonnes).";\n";
		
		foreach($tableau as $elements) {
			$csv .= implode(';',$elements).";\n";
		}
		
		return $csv;
	}
	
	public function envoyerFichier($contenu) {
		
		$nom_fichier = "observations_export.csv";
		$chemin_fichier = $this->config['appli']['chemin_fichiers_temp'].'/'.$nom_fichier;
		
		file_put_contents($chemin_fichier, $contenu);	
			
		$contenu = file_get_contents($chemin_fichier);
		$taille_fichier = filesize($chemin_fichier);
		
		unlink($chemin_fichier);
		
		ini_set('zlib.output_compression','Off');
		
		header('Pragma: public');
		header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
		header('Cache-Control: must-revalidate, pre-check=0, post-check=0, max-age=0');
		
		header('Content-Tranfer-Encoding: none');
		
		header('Content-Type: application/octetstream; name="'.$nom_fichier.'"');
		header('Content-Disposition: attachement; filename="'.$nom_fichier.'"');
		
		header('Content-Length: '.$taille_fichier);
		
		echo $contenu;
		exit();
	}
	
	private function formaterInformationPourEnvoi($tableauinfos) {
 
	}
}
?>

Subversion Repositories Sites.obs-saisons.fr

(root)/trunk/applications/jrest/services/OdsExport.php – Rev 246 → 248

Rev 246		Rev 248
1	`<?php`	1	`<?php`
2		2
3	`class OdsExport extends OdsTriple {`	3	`class OdsExport extends OdsTriple {`
4		4
5	`const PREFIXE = 'get';`	5	`const PREFIXE = 'get';`
6	`const ABBR_LISTE_EVENEMENTS = 'evenement';`	6	`const ABBR_LISTE_EVENEMENTS = 'evenement';`
7	`const ABBR_LISTE_ESPECES = 'espece';`	7	`const ABBR_LISTE_ESPECES = 'espece';`
8		8
9	`private $type_mime = 'text/html';`	9	`private $type_mime = 'text/html';`
10		10
11	`/**`	11	`/**`
12	`* Méthodes d'extractions d'informations`	12	`* Méthodes d'extractions d'informations`
13	`*/`	13	`*/`
14		14
15	`/**`	15	`/**`
16	`* Méthode appelée avec une requête de type GET.`	16	`* Méthode appelée avec une requête de type GET.`
17	`*`	17	`*`
18	`*/`	18	`*/`
19	`function getElement($param = array()) {`	19	`function getElement($param = array()) {`
20		20
21	`$type = $param[0];`	21	`$type = $param[0];`
22		22
23	`if ($type == '*' \|\| is_numeric($type)) {`	23	`if ($type == '*' \|\| is_numeric($type)) {`
24	`$info = $this->getElementParDefaut($param);`	24	`$info = $this->getElementParDefaut($param);`
25	`} else {`	25	`} else {`
26	`$methode = self::PREFIXE.$type;`	26	`$methode = self::PREFIXE.$type;`
27	`if (method_exists($this, $methode)) {`	27	`if (method_exists($this, $methode)) {`
28	`array_shift($param);`	28	`array_shift($param);`
29	`$info = $this->$methode($param);`	29	`$info = $this->$methode($param);`
30	`} else {`	30	`} else {`
31	`$this->messages[] = "Le type d'information demandé '$type' n'est pas disponible.";`	31	`$this->messages[] = "Le type d'information demandé '$type' n'est pas disponible.";`
32	`}`	32	`}`
33	`}`	33	`}`
34		34
35	`$this->envoyer($info,$this->type_mime);`	35	`$this->envoyer($info,$this->type_mime);`
36	`}`	36	`}`
37		37
38	`public function getExportObservation($start = null,$limit = null, $order_by = 'oo_date') {`	38	`public function getExportObservation($start = null,$limit = null, $order_by = 'oo_date') {`
39		39
40	`$requete_selection_observations = 'SELECT * FROM ods_observations '.`	40	`$requete_selection_observations = 'SELECT * FROM ods_observations '.`