Subversion Repositories eFlore/Projets.eflore-projets

<?php

// Encodage : UTF-8

// +-------------------------------------------------------------------------------------------------------------------+

/**

 * Export des données de SOPHY

 *

 * Description : classe permettant d'exporter les tableaux phytosociologiques de la banque de données SOPHY

 * Utilisation : php script.php export

 *

 * @category		PHP 5.3

 * @package		phytosocio

 //Auteur original :

 * @author		Delphine CAUQUIL <delphine@tela-botanica.org>

 * @copyright	Copyright (c) 2009, Tela Botanica (accueil@tela-botanica.org)

 * @license		http://www.gnu.org/licenses/gpl.html Licence GNU-GPL-v3

 * @license		http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL-v2

 * @version		$Id$

 *

 * /opt/lampp/bin/php -d memory_limit=2048M cli.php sophy/export -a rien  -c publication/strate/flore

 * -f annee:sup:1990/auteur:like:ma%/departement:in:14,50,61,76,27/nomsci:like:trifolium%/fournier:neg: >log.txt

 *

 *

 *

 * export pour le crpf

 *

 *

 * export pour les plantes de la liste rouge des suisses

 * /opt/lampp/bin/php -d memory_limit=2048M cli.php sophy/export -a rien  -f nomsci:in:"'Adenophora liliifolia (L.) A. DC.','Adenostyles leucophylla (Willd.) Rchb.','Allium angulosum L.','Allium rotundum L.','Anagallis minima (L.) E. H. L. Krause','Anagallis tenella (L.) L.','Anchusa officinalis L.','Andromeda polifolia L.','Androsace brevis (Hegetschw.) Ces.','Anemone baldensis L.','Anemone sylvestris L.','Anogramma leptophylla (L.) Link','Apium repens (Jacq.) Lag.','Aquilegia einseleana F. W. Schultz','Artemisia nivalis Braun-Blanq.','Asperugo procumbens L.','Asplenium adulterinum Milde','Asplenium billotii F. W. Schultz','Asplenium foreziense Magnier','Astragalus australis (L.) Lam.','Barbarea stricta Andrz.','Blackstonia acuminata (W. D. J. Koch & Ziz) Domin','Bromus grossus DC.','Bufonia paniculata Dubois','Campanula excisa Murith','Campanula latifolia L.','Cardamine kitaibelii Bech.','Cardamine matthioli Moretti','Carduus crispus L.','Carex baldensis L.','Carex fimbriata Schkuhr','Carex hartmanii Cajander','Carpesium cernuum L.','Chenopodium ficifolium Sm.','Chenopodium rubrum L.','Clematis alpina (L.) Mill.','Corydalis intermedia (L.) Mérat','Corydalis solida (L.) Clairv.','Crepis pygmaea L.','Crepis terglouensis (Hacq.) A. Kern.','Cuscuta europaea L.','Cytisus decumbens (Durande) Spach','Cytisus emeriflorus Rchb.','Cytisus nigricans L.','Cytisus scoparius (L.) Link','Deschampsia littoralis (Gaudin) Reut.','Dianthus gratianopolitanus Vill.','Dianthus seguieri Vill.','Diphasiastrum complanatum (L.) Holub','Diplotaxis muralis (L.) DC.','Draba fladnizensis Wulfen','Draba hoppeana Rchb.','Draba ladina Braun-Blanq.','Draba siliquosa M. Bieb.','Draba tomentosa Clairv.','Equisetum ramosissimum Desf.','Eriophorum gracile Roth','Erythronium dens-canis L.','Euonymus latifolius (L.) Mill.','Euphrasia christii Gremli','Falcaria vulgaris Bernh.','Fragaria viridis Duchesne','Gagea pratensis (Pers.) Dumort.','Galeopsis pubescens Besser','Gentiana engadinensis (Wettst.) Braun-Blanq. & Sam.','Geranium rivulare Vill.','Gladiolus imbricatus L.','Gladiolus italicus Mill.','Gladiolus palustris Gaudin','Gratiola officinalis L.','Hammarbya paludosa (L.) Kuntze','Helianthemum salicifolium (L.) Mill.','Iberis saxatilis L.','Inula britannica L.','Inula helvetica Weber','Inula spiraeifolia L.','Isopyrum thalictroides L.','Juniperus sabina L.','Knautia godetii Reut.','Lathyrus sphaericus Retz.','Leucanthemum halleri (Vitman) Ducommun','Leucojum aestivum L.','Linaria alpina subsp. petraea (Jord.) Rouy','Lindernia procumbens (Krock.) Borbás','Linnaea borealis L.','Littorella uniflora (L.) Asch.','Minuartia cherlerioides subsp. rionii (Gremli) Friedrich','Myosotis rehsteineri Wartm.','Myrrhis odorata (L.) Scop.','Nigella arvensis L.','Notholaena marantae (L.) Desv.','Ononis rotundifolia L.','Orchis laxiflora Lam.','Orchis papilionacea L.','Orchis provincialis DC.','Orchis spitzelii W. D. J. Koch','Ostrya carpinifolia Scop.','Pedicularis oederi Hornem.','Peucedanum verticillare (L.) Mert. & W. D. J. Koch','Phyteuma humile Gaudin','Pilularia globulifera L.','Pinguicula grandiflora Lam. s.str.','Polygonum minus Huds.','Potentilla alpicola Fauc.','Potentilla caulescens L.','Potentilla grammopetala Moretti','Potentilla inclinata Vill.','Primula daonensis (Leyb.) Leyb.','Primula latifolia Lapeyr.','Pulmonaria helvetica Bolliger','Ranunculus gramineus L.','Ranunculus parnassiifolius L.','Rhodiola rosea L.','Rhynchospora alba (L.) Vahl','Rumex nivalis Hegetschw.','Sagina nodosa (L.) Fenzl','Saponaria lutea L.','Saxifraga adscendens L.','Saxifraga aphylla Sternb.','Saxifraga diapensioides Bellardi','Saxifraga mutata L.','Scorzonera laciniata L. s.str.','Scutellaria alpina L.','Sedum anacampseros L.','Sedum cepaea L.','Sedum rubens L.','Senecio aquaticus Hill','Senecio halleri Dandy','Senecio incanus subsp. insubricus (Chenevard) Braun-Blanq.','Senecio paludosus L.','Silene pusilla Waldst. & Kit.','Silene suecica (Lodd.) Greuter & Burdet','Sisymbrium supinum L.','Teucrium scordium L.','Thlaspi rotundifolium subsp. corymbosum Gremli','Trifolium saxatile All.','Trochiscanthes nodiflora (All.) W. D. J. Koch','Typha minima Hoppe','Valeriana celtica L.','Valeriana supina Ard.','Viola cenisia L.','Viola elatior Fr.','Viola lutea Huds.','Viola persicifolia Schreb.','Viola pinnata L.','Viola pyrenaica DC.','Woodsia alpina (Bolton) Gray'"/departement:in:14,39,25,90,68,74

 *

 */

// +-------------------------------------------------------------------------------------------------------------------+

class Export extends EfloreScript {

	protected $tableauTaxon;

	protected $dao;

	protected $observations;

	// Paramêtres autorisées lors de l'appel au script en ligne de commande

	protected $parametres_autorises = array(

		'-n' => array(false, 'export', 'Nom du fichier à créer'),

		'-c' => array(false, 'tous', 'Liste des champs à récupérer'),

		'-f' => array(false, true, 'Liste des filtres à executer'));

	// à utiliser dans les valeurs des parametres pour séparer les champs ou filtres

	protected $separateur = "/";

	// correspondances parametres/champs de la bd

	protected $champs_exportes = array(

		"code_identifiant" => "CONCAT(o.so_id_publi,'.',o.so_id_tableau,'.',o.so_id_releve,'.',

							o.so_num_ligne,'.',o.so_id_taxon,'.',o.so_id_strate) AS code_identifiant",

		"numero_publication" => "sp_id_publi AS numero_publication",

		"publication" => "CONCAT (sp_auteur, '. ', sp_revue, ' ', sp_volume, ' ', sp_tome, ' ', sp_fascicule, ', ', sp_date,

							'. ', sp_titre, ', p.', sp_page_debut, '-', sp_page_fin, '.') AS publication",

			"auteur" => "sp_auteur AS auteur",

			"annee" => "sp_date AS annee",

		"numero_tableau" => "sr_id_tableau AS numero_tableau",

		"numero_releve" => "sr_id_releve AS numero_releve",

		"nom_station" => "ss_localisation AS nom_station",

			"code_insee" => "IF (ss_code_departement != 0,

						CONCAT(ss_code_departement, ss_code_insee_commune), ss_code_insee_calculee) AS code_insee",

			"code_insee_calcule" => "ss_code_insee_calculee AS code_insee_calcule",

			"altitude" => "ss_altitude AS altitude",

			"coordonnees_wgs" => "ss_latitude_wgs, ss_longitude_wgs",

			"coordonnees_utm" => "ss_utmEasting, ss_utmNorthing, ss_utmZone",

			"precision_geographique" => "ss_ce_precision_geographique",

		"nom_scientifique" => "st_nom AS nom_scientifique",

			"flore" => "st_ce_num_fournier, st_ce_num_floeur, st_ce_num_algues, st_ce_num_characees, st_ce_num_bryo,

				st_ce_num_lichen, st_ce_num_syntri, st_ce_num_bdnff, st_ce_num_ciff, st_ce_num_codefr94",

		"strate" => "so_id_strate AS strate",

		"code_abondance" => "so_ce_abondance AS code_abondance");

	protected $autresChamps = array(

		"signification_precision" => "spg_num_precision, spg_valeur",

		"signification_strate" => "so_id_strate",

		"signification_abondance" => "sa_valeur");

	protected $filtres_existants = array(

		"annee" => "sp_date",

		"auteur" => "sp_auteur",

		"departement" => "",

		"commune" => "",

		"precisiongeo" => "ss_ce_precision_geographique",

		"nomsci" => "st_nom",

		"fournier" => "st_ce_num_fournier",

		"floeur" => "st_ce_num_floeur",

		"algues" => "st_ce_num_algues",

		"characees" => "st_ce_num_characees",

		"bryo" => "st_ce_num_bryo",

		"lichen" => "st_ce_num_lichen",

		"syntri" => "st_ce_num_syntri",

		"bdnff" => "st_ce_num_bdnff",

		"ciff" => "st_ce_num_ciff",

		"codefr" => "st_ce_num_codefr94");

	protected $operateurs = array(

		"inf" => "<",

		"sup" => ">",

		"eg" => "=",

		"neg" => "!=",

		"infeg" => "<=",

		"supeg" => ">=",

		"in" => " IN ",

		"like" => " LIKE ");

// +-------------------------------------------------------------------------------------------------------------------+

	public function executer() {

		include_once dirname(__FILE__).'/bibliotheque/Dao.php';

		Config::charger(dirname(__FILE__).'/sophy.ini');

		$this->dao = new Dao();

		// Récupération de paramétres

		$requete['nomFichier'] = $this->getParametre('n');

		$requete['champs'] = $this->formaterChamps($this->getParametre('c'));

		$requete['filtres'] = $this->formaterFiltres($this->getParametre('f'));

		$donnees = $this->recupererDonnees($requete);

		if ($donnees === false) {

			$info = "Pas de données";

		} else {

			$titre = $this->formaterTitre($this->getParametre('c')); //à revoir

			$this->exportCSV($requete['nomFichier'], $titre, $donnees);

		}

	}

	protected function formaterFiltres($filtres) {

		$where = array();

		if ($filtres != '') {

			$liste_filtres = explode($this->separateur, $filtres);

			foreach ($liste_filtres as $filtre) {

				$morceaux_filtre = explode(':', $filtre);

				if (isset($this->filtres_existants[$morceaux_filtre[0]])) {

					if (isset($this->operateurs[$morceaux_filtre[1]])) {

						$where[] = $this->traiterFiltres($morceaux_filtre);

					} else {

						echo "L'operateur demandé {$morceaux_filtre[1]} n'existe pas. Les opérateurs existants sont :\n".

						implode(', ', array_keys($this->operateurs));

					}

				} else {

					echo "Le filtre demandé {$morceaux_filtre[0]} n'existe pas. Les filtres existants sont :\n".

						implode(', ', array_keys($this->filtres_existants));

				}

			}

			$where = ' WHERE '.implode(' AND ', $where);

		}

		return $where;

	}

	protected function traiterFiltres($morceaux) {

		$where = '';

		if ($this->operateurs[$morceaux[1]] == ' IN ') {

			$where = $this->operateurs[$morceaux[1]].'('.$morceaux[2].')';

		} else {

			$where= $this->operateurs[$morceaux[1]].$this->getBdd()->proteger($morceaux[2]);

		}

		switch ($morceaux[0]) {

			case 'departement' :

				$where = "( ss_code_departement".$where.

					" OR substring( `ss_code_insee_calculee`, -5, 2 ) ".$where.") ";

				break;

			case 'commune' :

				$where = "( CONCAT(ss_code_departement, ss_code_insee_commune) ".$where.

									" OR `ss_code_insee_calculee` ".$where.") ";

				break;

			default : $where= $this->filtres_existants[$morceaux[0]].$where;

				break;

		}

		return $where;

	}

	protected function formaterChamps($champs_demandes) {

		$champs_demandes = explode($this->separateur, $champs_demandes);

		if ($champs_demandes[0] == 'tous') {

			$champs = implode(', ', array_values($this->champs_exportes));

		} else {

			foreach ($champs_demandes as $champ) {

				if (isset($this->champs_exportes[$champ])) {

					$champs[] = $this->champs_exportes[$champ];

				} else {

					echo "Le champ demandé {$champ} n'existe pas. Les champs existants sont :\n".

					implode(', ', array_keys($this->champs_exportes));

				}

				}

				$champs = implode(', ', $champs);

		}

		return $champs;

	}

	protected function formaterTitre($champs) {

		$liste_champs = ($champs == 'tous') ? array_keys($this->champs_exportes) :explode(',', $champs);

		foreach ($liste_champs as $champs) {

			switch ($champs) {

				case "flore" : $titre[] = "fournier"; $titre[] = "floeur"; $titre[] = "algues"; $titre[] = "characees";

					$titre[] = "bryo"; $titre[] = "lichen"; $titre[] = "syntri"; $titre[] = "bdnff"; $titre[] = "ciff";

					$titre[] = "code france 94"; break;

				case "coordonnees_wgs": $titre[] = "latitude (wgs)"; $titre[] = "longitude (wgs)"; break;

				case "coordonnees_utm" :

					$titre[] = "Easting (utm)"; $titre[] = "Northing (utm)"; $titre[] = "zone utm"; break;

				default: $titre[] = $champs; break;

			}

		}

		return $titre;

	}

	// Requête de création et d'insertion sur table sophy_tapir

	public function recupererDonnees($parametre) {

		$bdd = new Bdd();

		$requete = "SELECT {$parametre['champs']}

					FROM sophy_observation o LEFT JOIN sophy_taxon t ON (o.so_id_taxon = t.st_id_taxon)

						LEFT JOIN sophy_releve r ON (r.sr_id_publi = o.so_id_publi AND r.sr_id_tableau = o.so_id_tableau AND r.sr_id_releve = o.so_id_releve )

						LEFT JOIN sophy_station s ON (r.sr_id_station = s.ss_id_station)

						LEFT JOIN sophy_publication p ON (r.sr_id_publi = p.sp_id_publi)

					{$parametre['filtres']}";

					//INTO OUTFILE '/home/delphine/web/eflore-projets/scripts/{$parametre['nomFichier']}.csv';";

		echo $requete;

		$reponse = $bdd->recupererTous($requete);

		return $reponse;

	}

	function exportCSV($nomFichier, $titre, $data) {

		$outstream = fopen("./{$nomFichier}.csv", 'w');

		fputcsv($outstream, $titre, ';', '"');

		function __outputCSV(&$vals, $key, $filehandler) {

			fputcsv($filehandler, $vals, ';', '"'); //\t = chr(9)

		}

		array_walk($data, '__outputCSV', $outstream);

		fclose($outstream);

	}

}

?>