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;

<?php
header ("Content-Type:text/xml");
echo('<?xml version="1.0" encoding="UTF-8"?>');
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('<!--');
print_r($assoc);
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 = '<gmd:MD_Metadata xmlns:gmd="http://www.isotc211.org/2005/gmd" xmlns:cat="http://www.opengis.net/cat/csw" xmlns:csw="http://www.opengis.net/cat/csw/2.0.2" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dc2="http://www.purl.org/dc/elements/1.1/" xmlns:dct="http://purl.org/dc/terms/" xmlns:dct2="http://www.purl.org/dc/terms/" xmlns:fra="http://www.cnig.gouv.fr/2005/fra" xmlns:gco="http://www.isotc211.org/2005/gco" xmlns:gfc="http://www.isotc211.org/2005/gfc" xmlns:gmi="http://www.isotc211.org/2005/gmi" xmlns:gml="http://www.opengis.net/gml" xmlns:gmx="http://www.isotc211.org/2005/gmx" xmlns:ins="http://www.inspire.org" xmlns:ns17="http://www.opengis.net/ows/1.1" xmlns:ns9="http://www.mdweb-project.org/files/xsd" xmlns:ogc="http://www.opengis.net/ogc" xmlns:ows="http://www.opengis.net/ows" xmlns:srv="http://www.isotc211.org/2005/srv" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.isotc211.org/2005/gmd http://schemas.opengis.net/iso/19139/20060504/gmd/gmd.xsd">
    <gmd:fileIdentifier>
        <gco:CharacterString>MDweb-40884177-aeec-4b72-9a7f-2f5f80f7afcd</gco:CharacterString>
    </gmd:fileIdentifier>
    <gmd:language>
        <gco:CharacterString>fre</gco:CharacterString>
    </gmd:language>
    <gmd:hierarchyLevel>
        <gmd:MD_ScopeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#dataset" codeListValue="dataset"/>
    </gmd:hierarchyLevel>
    <gmd:contact>
        <gmd:CI_ResponsibleParty>
            <gmd:organisationName>
                <gco:CharacterString>Conservatoire des Espaces Naturels du Languedoc-Roussillon</gco:CharacterString>
            </gmd:organisationName>
            <gmd:contactInfo>
                <gmd:CI_Contact>
                    <gmd:address>
                        <gmd:CI_Address>
                            <gmd:electronicMailAddress>
                                <gco:CharacterString>cenlr@cenlr.org</gco:CharacterString>
                            </gmd:electronicMailAddress>
                        </gmd:CI_Address>
                    </gmd:address>
                    </gmd:CI_Contact>
            </gmd:contactInfo>
            <gmd:role>
                <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#pointOfContact" codeListValue="pointOfContact"/>
            </gmd:role>
        </gmd:CI_ResponsibleParty>
    </gmd:contact>
    <gmd:dateStamp>
        <gco:DateTime>2010-10-28T00:00:00+02:00</gco:DateTime>
    </gmd:dateStamp>
    <gmd:metadataStandardName>
        <gco:CharacterString>x-urn:mdweb:schema:exp:ISO19115:INSPIRE:nature-sdi:spp-distrib:mdweb-vector</gco:CharacterString>
    </gmd:metadataStandardName>
    <gmd:metadataStandardVersion>
        <gco:CharacterString>2010.04</gco:CharacterString>
    </gmd:metadataStandardVersion>
    <gmd:spatialRepresentationInfo>
        <gmd:MD_VectorSpatialRepresentation>
            <gmd:geometricObjects>
                <gmd:MD_GeometricObjects>
                    <gmd:geometricObjectType>
                        <gmd:MD_GeometricObjectTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#point" codeListValue="point"/>
                    </gmd:geometricObjectType>
                </gmd:MD_GeometricObjects>
            </gmd:geometricObjects>
        </gmd:MD_VectorSpatialRepresentation>
    </gmd:spatialRepresentationInfo>
    <gmd:referenceSystemInfo>
        <gmd:MD_ReferenceSystem>
            <gmd:referenceSystemIdentifier>
                <gmd:RS_Identifier>
                    <gmd:code>
		    <gco:CharacterString>'.$assoc['srid'].'</gco:CharacterString>
                    </gmd:code>
                </gmd:RS_Identifier>
            </gmd:referenceSystemIdentifier>
        </gmd:MD_ReferenceSystem>
    </gmd:referenceSystemInfo>
    <gmd:referenceSystemInfo>
        <gmd:MD_ReferenceSystem>
            <gmd:referenceSystemIdentifier>
                <gmd:RS_Identifier>
                    <gmd:code>
                        <gco:CharacterString>Gregorian Calendar</gco:CharacterString>
                    </gmd:code>
                </gmd:RS_Identifier>
            </gmd:referenceSystemIdentifier>
        </gmd:MD_ReferenceSystem>
    </gmd:referenceSystemInfo>
    <gmd:identificationInfo>
        <ns9:NATSDI_DataIdentification>
            <gmd:citation>
                <gmd:CI_Citation>
                    <gmd:title>
                        <gco:CharacterString>'.$assoc['lot_donnee_libelle'].'</gco:CharacterString>
                    </gmd:title>
                    <gmd:date>
                        <gmd:CI_Date>
                            <gmd:date>
                                <gco:DateTime>2010-10-28T00:00:00+02:00</gco:DateTime>
                            </gmd:date>
                            <gmd:dateType>
                                <gmd:CI_DateTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#publication" codeListValue="publication"/>
                            </gmd:dateType>
                        </gmd:CI_Date>
                    </gmd:date>
                    <gmd:identifier>
                        <gmd:RS_Identifier>
                            <gmd:code>
                                <gco:CharacterString>http://www.cenlr.org/'.$assoc['id_lot'].'</gco:CharacterString>
                            </gmd:code>
                        </gmd:RS_Identifier>
                    </gmd:identifier>
                </gmd:CI_Citation>
            </gmd:citation>
            <gmd:abstract>
                <gco:CharacterString>'.$assoc['lot_donnee_resume'].'</gco:CharacterString>
            </gmd:abstract>
            <gmd:pointOfContact>
                <gmd:CI_ResponsibleParty>
                    <gmd:organisationName>
                        <gco:CharacterString>Conservatoire des Espaces Naturels du Languedoc-Roussillon</gco:CharacterString>
                    </gmd:organisationName>
                    <gmd:contactInfo>
                        <gmd:CI_Contact>
                            <gmd:address>
                                <gmd:CI_Address>
                                    <gmd:electronicMailAddress>
                                        <gco:CharacterString>cenlr@cenlr.org</gco:CharacterString>
                                    </gmd:electronicMailAddress>
                                </gmd:CI_Address>
                            </gmd:address>
                            </gmd:CI_Contact>
                    </gmd:contactInfo>
                    <gmd:role>
                        <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#pointOfContact" codeListValue="pointOfContact"/>
                    </gmd:role>
                </gmd:CI_ResponsibleParty>
            </gmd:pointOfContact>
            <gmd:pointOfContact>
                <gmd:CI_ResponsibleParty>
                    <gmd:organisationName>
                        <gco:CharacterString>responsable de l\'étude</gco:CharacterString>
                    </gmd:organisationName>
                    <gmd:contactInfo>
                        <gmd:CI_Contact>
                            <gmd:address>
                                <gmd:CI_Address>
                                    <gmd:electronicMailAddress>
                                        <gco:CharacterString>cenlr@cenlr.rog</gco:CharacterString>
                                    </gmd:electronicMailAddress>
                                </gmd:CI_Address>
                            </gmd:address>
                            </gmd:CI_Contact>
                    </gmd:contactInfo>
                    <gmd:role>
                        <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#pointOfContact" codeListValue="pointOfContact"/>
                    </gmd:role>
                </gmd:CI_ResponsibleParty>
            </gmd:pointOfContact>
            <gmd:resourceMaintenance>
                <gmd:MD_MaintenanceInformation>
                    <gmd:updateScope>
                        <gmd:MD_ScopeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#dataset" codeListValue="dataset"/>
                    </gmd:updateScope>
                </gmd:MD_MaintenanceInformation>
            </gmd:resourceMaintenance>
            <gmd:descriptiveKeywords>
                <gmd:MD_Keywords>
                    <gmd:keyword>
                        <gco:CharacterString>'.$ssoc['annexe'].'Species distribution</gco:CharacterString>
                    </gmd:keyword>
                    <gmd:thesaurusName>
                        <gmd:CI_Citation>
                            <gmd:title>
                                <gco:CharacterString>GEMET - INSPIRE themes, version 1.0</gco:CharacterString>
                            </gmd:title>
                            <gmd:date>
                                <gmd:CI_Date>
                                    <gmd:date>
                                        <gco:DateTime>2008-06-01T00:00:00+02:00</gco:DateTime>
                                    </gmd:date>
                                    <gmd:dateType>
                                        <gmd:CI_DateTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#publication" codeListValue="publication"/>
                                    </gmd:dateType>
                                </gmd:CI_Date>
                            </gmd:date>
                            <gmd:edition>
                                <gco:CharacterString>version 1.0</gco:CharacterString>
                            </gmd:edition>
                            <gmd:citedResponsibleParty>
                                <gmd:CI_ResponsibleParty>
                                    <gmd:organisationName>
                                        <gco:CharacterString>EIONET</gco:CharacterString>
                                    </gmd:organisationName>
                                    <gmd:contactInfo>
                                        <gmd:CI_Contact>
                                            <gmd:onlineResource>
                                                <gmd:CI_OnlineResource>
                                                    <gmd:linkage>
                                                        <gmd:URL>http://www.eionet.europa.eu/gemet/</gmd:URL>
                                                    </gmd:linkage>
                                                </gmd:CI_OnlineResource>
                                            </gmd:onlineResource>
                                        </gmd:CI_Contact>
                                    </gmd:contactInfo>
                                    <gmd:role>
                                        <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#publisher" codeListValue="publisher"/>
                                    </gmd:role>
                                </gmd:CI_ResponsibleParty>
                            </gmd:citedResponsibleParty>
                        </gmd:CI_Citation>
                    </gmd:thesaurusName>
                </gmd:MD_Keywords>
            </gmd:descriptiveKeywords>
            <gmd:resourceConstraints>
                <gmd:MD_LegalConstraints>
                    <gmd:useLimitation>
                        <gco:CharacterString>respect de la paternité et de l\'intégriité des données</gco:CharacterString>
                    </gmd:useLimitation>
                </gmd:MD_LegalConstraints>
            </gmd:resourceConstraints>
            <gmd:resourceConstraints>
                <gmd:MD_SecurityConstraints>
                    <gmd:classification>
                        <gmd:MD_ClassificationCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#unclassified" codeListValue="unclassified"/>
                    </gmd:classification>
                </gmd:MD_SecurityConstraints>
            </gmd:resourceConstraints>
            <gmd:spatialResolution>
                <gmd:MD_Resolution>
                    <gmd:equivalentScale>
                        <gmd:MD_RepresentativeFraction>
                            <gmd:denominator>
                                <gco:Integer>0</gco:Integer>
                            </gmd:denominator>
                        </gmd:MD_RepresentativeFraction>
                    </gmd:equivalentScale>
                </gmd:MD_Resolution>
            </gmd:spatialResolution>
            <gmd:language>
                <gco:CharacterString>fre</gco:CharacterString>
            </gmd:language>
            <gmd:topicCategory>
                <gmd:MD_TopicCategoryCode>biota</gmd:MD_TopicCategoryCode>
            </gmd:topicCategory>
            <gmd:extent>
                <gmd:EX_Extent>
                    <gmd:geographicElement>
                        <gmd:EX_GeographicBoundingBox>
                            <gmd:westBoundLongitude>
                                <gco:Decimal>1.57</gco:Decimal>
                            </gmd:westBoundLongitude>
                            <gmd:eastBoundLongitude>
                                <gco:Decimal>9.21</gco:Decimal>
                            </gmd:eastBoundLongitude>
                            <gmd:southBoundLatitude>
                                <gco:Decimal>41.68</gco:Decimal>
                            </gmd:southBoundLatitude>
                            <gmd:northBoundLatitude>
                                <gco:Decimal>50.48</gco:Decimal>
                            </gmd:northBoundLatitude>
                        </gmd:EX_GeographicBoundingBox>
                    </gmd:geographicElement>
                    </gmd:EX_Extent>
            </gmd:extent>
            <ns9:speciesInformation>
                <ns9:NATSDI_SpeciesInformation>
                    <ns9:taxonomicClassification>
                        <ns9:NATSDI_TaxonomicClassification>
                            <ns9:taxonRankName>
                                <ns9:NATSDI_RankNameCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#species" codeListValue="species"/>
                            </ns9:taxonRankName>
                            <ns9:taxonRankValue>
                                <gmd:MD_Keywords>
                                    <gmd:keyword>
                                        <gco:CharacterString>erithacus rubecula</gco:CharacterString>
                                    </gmd:keyword>
                                    <gmd:type>
                                        <gmd:MD_KeywordTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#taxon" codeListValue="taxon"/>
                                    </gmd:type>
                                </gmd:MD_Keywords>
                            </ns9:taxonRankValue>
                        </ns9:NATSDI_TaxonomicClassification>
                    </ns9:taxonomicClassification>
                    </ns9:NATSDI_SpeciesInformation>
            </ns9:speciesInformation>
        </ns9:NATSDI_DataIdentification>
    </gmd:identificationInfo>
    <gmd:distributionInfo>
        <gmd:MD_Distribution>
            <gmd:distributionFormat>
                <gmd:MD_Format>
                    <gmd:name>
                        <gco:CharacterString>esri shape</gco:CharacterString>
                    </gmd:name>
                    <gmd:version>
                        <gco:CharacterString>inconnu</gco:CharacterString>
                    </gmd:version>
                </gmd:MD_Format>
            </gmd:distributionFormat>
            </gmd:MD_Distribution>
    </gmd:distributionInfo>
    <gmd:dataQualityInfo>
        <gmd:DQ_DataQuality>
            <gmd:scope>
                <gmd:DQ_Scope>
                    <gmd:level>
                        <gmd:MD_ScopeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#dataset" codeListValue="dataset"/>
                    </gmd:level>
                </gmd:DQ_Scope>
            </gmd:scope>
            <gmd:report>
                <gmd:DQ_DomainConsistency>
                    <gmd:result>
                        <gmd:DQ_ConformanceResult>
                            <gmd:specification>
                                <gmd:CI_Citation>
                                    <gmd:title>
                                        <gco:CharacterString>Not yet published</gco:CharacterString>
                                    </gmd:title>
                                    <gmd:date>
                                        <gmd:CI_Date>
                                            <gmd:date>
                                                <gco:DateTime>2010-10-28T00:00:00+02:00</gco:DateTime>
                                            </gmd:date>
                                            <gmd:dateType>
                                                <gmd:CI_DateTypeCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#publication" codeListValue="publication"/>
                                            </gmd:dateType>
                                        </gmd:CI_Date>
                                    </gmd:date>
                                </gmd:CI_Citation>
                            </gmd:specification>
                            <gmd:explanation>
                                <gco:CharacterString>See the referenced specification</gco:CharacterString>
                            </gmd:explanation>
                            <gmd:pass>
                                <gco:Boolean>true</gco:Boolean>
                            </gmd:pass>
                        </gmd:DQ_ConformanceResult>
                    </gmd:result>
                </gmd:DQ_DomainConsistency>
            </gmd:report>
            <gmd:lineage>
                <gmd:LI_Lineage>
                    <gmd:statement>
                        <gco:CharacterString>commentaire sur l\'origine d ela donnée</gco:CharacterString>
                    </gmd:statement>
                    <gmd:processStep>
                        <gmd:LI_ProcessStep>
                            <gmd:description>
                                <gco:CharacterString>brève description du protocole</gco:CharacterString>
                            </gmd:description>
                            <gmd:processor>
                                <gmd:CI_ResponsibleParty>
                                    <gmd:role>
                                        <gmd:CI_RoleCode codeList="http://www.tc211.org/ISO19139/resources/codeList.xml#pointOfContact" codeListValue="pointOfContact"/>
                                    </gmd:role>
                                </gmd:CI_ResponsibleParty>
                            </gmd:processor>
                        </gmd:LI_ProcessStep>
                    </gmd:processStep>
                    <gmd:source>
                        <gmd:LI_Source>
                            <gmd:description>
                                <gco:CharacterString>??????????????????</gco:CharacterString>
                            </gmd:description>
                            </gmd:LI_Source>
                    </gmd:source>
                </gmd:LI_Lineage>
            </gmd:lineage>
        </gmd:DQ_DataQuality>
    </gmd:dataQualityInfo>
</gmd:MD_Metadata>';
echo $xml_string;
?>