DROP VIEW md.description_lot;
CREATE OR REPLACE VIEW md.description_lot AS
SELECT lot_donnee.id_lot,
CASE
WHEN lot_donnee.libelle IS NULL THEN lot_donnee.code
ELSE lot_donnee.libelle
END AS lot_donnee_libelle, lot_donnee.id_etude AS lot_donnee_id_etude, lot_donnee.type_donnee AS lot_donnee_type_donnee, lot_donnee.resume AS lot_donnee_resume, lot_donnee.description_genealogie AS lot_donnee_description_genealogie, lot_donnee.echelle_utilisation AS lot_donnee_echelle_utilisation, lot_donnee.licence AS lot_donnee_licence, lot_donnee.limitation_acces_public AS lot_donnee_limitation_acces_public, lot_donnee.qualite_thematique AS lot_donnee_qualite_thematique, lot_donnee.id_protocole AS lot_donnee_id_protocole, etude.id_etude AS etude_id_etude, etude.nom_etude AS etude_nom_etude, etude.cahier_des_charges AS etude_cahier_des_charges, etude.date_debut AS etude_date_debut, etude.date_fin AS etude_date_fin, etude.description AS etude_description, etude.lien_rapport_final AS etude_lien_rapport_final, protocole.id_protocole AS protocole_id_protocole, protocole.libelle AS protocole_libelle, protocole.auteurs AS protocole_auteurs, protocole.id_biblio AS protocole_id_biblio, lower(spatial_ref_sys.auth_name)||' '||spatial_ref_sys.auth_srid AS srid,
CASE WHEN lot_donnee.type_donnee::text LIKE '%faune%' OR lot_donnee.type_donnee::text LIKE '%flore%' THEN 'Species distribution' ELSE 'Habitats and biotopes' END as annexe
FROM md.lot_donnee
JOIN md.etude USING (id_etude)
JOIN md.protocole USING (id_protocole),
geometry_columns, spatial_ref_sys
WHERE f_table_name = type_donnee::text AND geometry_columns.srid = spatial_ref_sys.srid
ORDER BY lot_donnee.id_lot;
====== Script php ======
');
if(!$conn=pg_connect('dbname=sicen user=titi password=toto host=192.168.1.230')){echo("pas de connexion à la bdd");}
$query1='select * from md.description_lot WHERE id_lot ='.$_GET['id_lot'].' LIMIT 1;';
//echo $query1;
$result = pg_query($conn, $query1);
if (!$result) {
echo "An error occured.\n";
exit;
}
$assoc = pg_fetch_assoc($result);
echo('');
/*
select * from md.description_lot WHERE id_lot =14 LIMIT 1;Array
(
[id_lot] => 14
--[lot_donnee_libelle] => Titre de mon lot de données
[lot_donnee_code] => etude_001_protocole_002_lignes_faune
[lot_donnee_id_etude] => 1
[lot_donnee_type_donnee] => ligne_faune
[lot_donnee_resume] =>
[lot_donnee_description_genealogie] =>
[lot_donnee_echelle_utilisation] =>
[lot_donnee_licence] =>
[lot_donnee_limitation_acces_public] =>
[lot_donnee_qualite_thematique] =>
[lot_donnee_id_protocole] => 2
[etude_id_etude] => 1
[etude_nom_etude] => Test de l'application
[etude_cahier_des_charges] => aucun
[etude_date_debut] =>
[etude_date_fin] =>
[etude_description] =>
[etude_lien_rapport_final] =>
[protocole_id_protocole] => 2
[protocole_libelle] => IPA
[protocole_auteurs] =>
[protocole_id_biblio] =>
)
*/
$xml_string = '
MDweb-40884177-aeec-4b72-9a7f-2f5f80f7afcd
fre
Conservatoire des Espaces Naturels du Languedoc-Roussillon
cenlr@cenlr.org
2010-10-28T00:00:00+02:00
x-urn:mdweb:schema:exp:ISO19115:INSPIRE:nature-sdi:spp-distrib:mdweb-vector
2010.04
'.$assoc['srid'].'
Gregorian Calendar
'.$assoc['lot_donnee_libelle'].'
2010-10-28T00:00:00+02:00
http://www.cenlr.org/'.$assoc['id_lot'].'
'.$assoc['lot_donnee_resume'].'
Conservatoire des Espaces Naturels du Languedoc-Roussillon
cenlr@cenlr.org
responsable de l\'étude
cenlr@cenlr.rog
'.$ssoc['annexe'].'Species distribution
GEMET - INSPIRE themes, version 1.0
2008-06-01T00:00:00+02:00
version 1.0
EIONET
http://www.eionet.europa.eu/gemet/
respect de la paternité et de l\'intégriité des données
0
fre
biota
1.57
9.21
41.68
50.48
erithacus rubecula
esri shape
inconnu
Not yet published
2010-10-28T00:00:00+02:00
See the referenced specification
true
commentaire sur l\'origine d ela donnée
brève description du protocole
??????????????????
';
echo $xml_string;
?>