[Devel] r414 - branches/dui
svn at agendadulibre.org
svn at agendadulibre.org
Dim 4 Jan 10:54:04 CET 2009
Author: ldayot
Date: Sun Jan 4 10:54:02 2009
New Revision: 414
Log:
Principale modification : ajout de renseignements plus complets sur les
lug (les esapces publics numeriques pour la branche dui).
- Ajout de champs dans la table lugs (commentaire, adresse et
coordonnees geographiques) et dans la table events (premiere
partie : pour faire la liaison avec les departement).
- Ajout d'une couche avec les lugs dans la carto.
- Modification de showevent.php pour ne montrer que les lugs du
departement s'il y en a beaucoup, sinon, ceux de la region.
Le lien vers les lugs depend des renseignements de la base. Si on n'a
pas de renseignements, on envoie sur le site du lug. Sinon, on envoi
avec showlugs.php qui montre tous les details.
- Reprise du script lugtextlist.php pour lien avec la carto. Meme url
que pour showevent.php pour les lugs.
- Nouveau script showlug.php pour montrer tous les details du lug.
- Nouveau script importepn.php qui va chercher sur le site de la DUI le
repertoire des EPN et trouve les coordonnees geographiques.
Added:
branches/dui/importepn.php
branches/dui/lugtextlist.php
branches/dui/showlug.php
Modified:
branches/dui/UPGRADE
branches/dui/infos.php
branches/dui/map.php
branches/dui/schema.sql
branches/dui/showevent.php
branches/dui/text.inc.php
Modified: branches/dui/UPGRADE
==============================================================================
--- branches/dui/UPGRADE (original)
+++ branches/dui/UPGRADE Sun Jan 4 10:54:02 2009
@@ -151,3 +151,28 @@
done
Add -p ou --password=password as option to mysqldump and mysql command if necessary
+
+
+Revisions lower than 412 - 2008-12-07
+=====================================
+
+# Add coordonates tu lugs and events
+# and more informations on lugs
+
+ALTER TABLE `lugs` ADD `address` VARCHAR( 255 ) NULL ,
+ADD `contact` VARCHAR( 255 ) NULL ,
+ADD `phone` VARCHAR( 255 ) NULL ,
+ADD `mail` VARCHAR( 255 ) NULL ,
+ADD `comment` TEXT NULL ,
+ADD `latitude` FLOAT NULL ,
+ADD `longitude` FLOAT NULL ,
+ADD `postalcode` INT NULL;
+
+ALTER TABLE `lugs` ADD INDEX ( `region` );
+ALTER TABLE `lugs` ADD INDEX ( `department` );
+
+# Add address in events table
+
+ALTER TABLE `events` ADD `address` VARCHAR( 255 ) NULL AFTER `end_time` ;
+ALTER TABLE `events` ADD `department` INT NULL AFTER `city` ;
+ALTER TABLE `events` ADD INDEX ( `department` ) ;
Added: branches/dui/importepn.php
==============================================================================
--- (empty file)
+++ branches/dui/importepn.php Sun Jan 4 10:54:02 2009
@@ -0,0 +1,127 @@
+<?php
+/*
+ * Importer le répertoire des EPN depuis la base nationale
+ * et l'injecter dans la table des gul (ou autre, à voir)
+*/
+
+include("text.inc.php");
+include("bd.inc.php");
+include("funcs.inc.php");
+
+$db = new db();
+
+put_header("Import des EPN");
+
+echo "<h2>Import de la base des EPN depuis le site de la DUI</h2>";
+
+$url = "http://delegation.internet.gouv.fr/bddui/api/accespublic/index.php";
+$repertoire = "?commande=choix&preserve=1";
+//$repertoire = "?commande=choix&IdRegion=2";
+
+$lignes = file($url. $repertoire, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES );
+$n=0;
+$texteLien = "index.php?commande=aperfiche&id=";
+$longTexteLien = strlen($texteLien);
+
+$fieldsDui2Adl = array("Nom de l'espace"=>"name", "Adresse"=>"address",
+ "Personne(s) à contacter"=>"contact", " Téléphone "=>"phone", "Mél"=>"mail",
+ "Site Web propre à l'espace"=>"url", "Services et activités"=>"services",
+ "Matériel/Nombre de postes"=>"equipment", "Nombre d'animateurs"=>"countFacilitator");
+$epn = array();
+
+foreach ($lignes as $ligne)
+ {
+ if ($pos = strpos($ligne, $texteLien))
+ {
+ $id = substr($ligne, $pos+$longTexteLien, 4);
+ echo ++$n. " (". $id. ") ";
+ $description = file($url. "?". $texteLien. $id, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
+ $nextIs=false;
+ foreach ($fieldsDui2Adl as $field) $epn[$field]="";
+ foreach ($description as $element)
+ {
+ $element = utf8_encode($element);
+ if ($nextIs)
+ {
+ if (trim($element)=="</tr>")
+ {
+ echo "<br />$nextIs = ". $epn[$nextIs]. "<br/>\n";
+ $nextIs = false;
+ continue;
+ }
+ $epn[$nextIs] = trim($epn[$nextIs]). " ". trim(strip_tags($element));
+ }
+ if ($villeDepartementRegion = get_html_element("H4", $element))
+ {
+ $ville = substr($villeDepartementRegion, 0, strpos($villeDepartementRegion, " - "));
+ $departement = substr($villeDepartementRegion, strlen($ville)+3,
+ strpos($villeDepartementRegion, " - ", strlen($ville)+3)-strlen($ville)-3);
+
+ // Find region number
+ $epn['regionName'] = substr($villeDepartementRegion, strlen($ville)+3 + strlen($departement)+3);
+ $resReg = $db->query("SELECT id FROM regions WHERE `name` LIKE \"". str_replace(" ", "-", $epn['regionName']). "\"");
+
+ if ($recReg = $db->fetchObject($resReg)) $epn['region'] = $recReg->id;
+
+ $epn['postalcode'] = substr($ville, strpos($ville,"(")+1, 5);
+ $epn['city'] = substr($ville, 0, strpos($ville,"(")-1);
+ $epn['department'] = substr($departement, strpos($departement, "(")+1, 2);
+ $epn['departement'] = substr($departement, 0, strpos($departement, "(")-1);
+ echo " <strong>{$epn['city']}</strong> {$epn['postalcode']} | {$epn['departement']} {$epn['department']} | {$epn['region']} {$epn['regionName']}";
+ $epn['id'] = $id;
+ continue;
+ }
+ //echo "<br/>".trim(strip_tags($element))."<br/>";
+ if (isset($fieldsDui2Adl[trim(strip_tags($element))]))
+ {
+ //echo ".";
+ $nextIs = $fieldsDui2Adl[trim(strip_tags($element))];
+ continue;
+ }
+ }
+ // trouver les coordonnées GPS via Google à partir de l'adresse
+ $url_gps = "http://maps.google.com/maps/geo?q=".
+ $epn['address']. " ". $epn['postalcode']. " ". $epn['city']. " France&output=csv&key=ABQIAAAATndsWAV5Q2y7pRRi-22W_hTxw9fvAnrsiYejTsRxd4b0cj9HKxSNCXUxAANaoACDzXWznNLVPto_jA";
+ $gps_file = file(str_replace(" ", "+", $url_gps), FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
+ list(,,$epn['latitude'], $epn['longitude']) = explode(",", $gps_file[0]);
+
+ $epn['comment'] =
+ ($epn['services']>"" ? "Services : ". $epn['services']. "<br/>\n" : "").
+ ($epn['equipment']>"" ? "Matériel/Nombre de postes : ". $epn['equipment']. "<br/>\n" : "").
+ ($epn['countFacilitator']>"" ? "Nombre d'animateurs : ". $epn['countFacilitator'] : "");
+ $epn['url'] = str_replace("http:", "http://", $epn['url']);
+ $fields = array('id', 'region', 'department', 'name', 'url', 'city',
+ 'postalcode', 'address', 'contact', 'phone', 'mail', 'comment',
+ 'latitude', 'longitude');
+ $sql = "REPLACE lugs SET ";
+ foreach ($fields as $field)
+ {
+ if (isset($epn[$field]))
+ {
+ $sql .= $field. "=\"". $epn[$field]. "\", ";
+ }
+ }
+ $sql = substr($sql, 0, strlen($sql)-2);
+ $db->query($sql);
+ echo "<br/>\n";
+ }
+ }
+
+function get_html_element($tag, $htmlString)
+ {
+ $res = xml_parser_create();
+ xml_parse_into_struct($res, $htmlString, $values);
+ //print_r($values);
+ foreach($values as $value)
+ {
+ if ($value['tag']==$tag)
+ {
+ $element = $value['value'];
+ }
+ }
+ xml_parser_free($res);
+ return isset($element) ? $element : false;
+ }
+
+
+?>
Modified: branches/dui/infos.php
==============================================================================
--- branches/dui/infos.php (original)
+++ branches/dui/infos.php Sun Jan 4 10:54:02 2009
@@ -36,7 +36,7 @@
<li>Mélanie Bats</li>
<li><a href="http://thomas.enix.org">Thomas Petazzoni</a></li>
<li><a href="http://www.linux-france.org/~dmentre/">David Mentré</a>, divers patches</li>
- <li>Loïc Dayot <a href="http://jeudisepn.org">association des Jeudis des Esapces Publics Numériques</a>, adaptation pour la Délégation aux Usages de l'Internet</li>
+ <li>Loïc Dayot <a href="http://jeudisepn.org">association des Jeudis des Espaces Publics Numériques</a>, adaptation pour la Délégation aux Usages de l'Internet</li>
<li>Liste complète à voir sur <a href="http://www.agendadulibre.org/infos.php">Agenda du
Libre</a></li>
</ul>
Added: branches/dui/lugtextlist.php
==============================================================================
--- (empty file)
+++ branches/dui/lugtextlist.php Sun Jan 4 10:54:02 2009
@@ -0,0 +1,80 @@
+<?php
+/* Copyright 2008-2009
+ * - Thomas Petazzoni <thomas POINT petazzoni CHEZ enix POINT org>
+ * - Loic Dayot
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License as
+ * published by the Free Software Foundation; either version 2 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
+ * 02111-1307, USA.
+ */
+
+include("bd.inc.php");
+include("bd-private.inc.php");
+include("funcs.inc.php");
+
+$db = new db();
+
+calendar_setlocale();
+
+Header("Content-type: text/plain; charset: utf-8");
+
+echo "lon\tlat\ttitle\tdescription\ticon\n";
+
+$sql = "select * from lugs";
+
+$lugs = $db->query($sql);
+while($lug = mysql_fetch_object($lugs))
+{
+ // has lug coordonates ?
+ if ($lug->longitude==0)
+ { // no, take city
+ if ($lug->city == "")
+ continue; // no city
+
+ $city = $db->query("select * from cities where name='" . addslashes($lug->city) . "'");
+ if (! $city)
+ continue; // error in query
+
+ if (mysql_num_rows($city) != 1)
+ continue; // city not found
+
+ $city = mysql_fetch_object($city);
+ $lug->longitude = $city->longitude;
+ $lug->latitude = $city->latitude;
+ }
+
+ echo
+ $lug->longitude . "\t" .
+ $lug->latitude . "\t" .
+ $lug->name . "\t";
+ // comment : address + url
+ echo
+ "<p style=\"font-size: 60%; text-align: center;\"><i>";
+ if ($lug->address>"")
+ echo "{$lug->address} - {$lug->postalcode}";
+ echo " à " . $lug->city . "</i><br/>";
+ if ($lug->comment>"")
+ {
+ echo "<a href=\"". calendar_absolute_url("showlug.php?id=". $lug->id). "\">".
+ calendar_absolute_url("showlug.php?id=". $lug->id). "</a>";
+ } else {
+ if ($lug->url>"")
+ {
+ echo "<a href=\"". $lug->url. "\">". $lug->url. "</a>";
+ }
+ }
+ echo "</p>" . "\t" .
+ calendar_absolute_url("lug.png") . "\n";
+}
+?>
Modified: branches/dui/map.php
==============================================================================
--- branches/dui/map.php (original)
+++ branches/dui/map.php Sun Jan 4 10:54:02 2009
@@ -58,8 +58,12 @@
map.setCenter (lonLat, zoom);
+ var newl = new OpenLayers.Layer.Text('EPN', {location: '" . calendar_absolute_url("lugtextlist.php") . "'});
+ map.addLayer(newl);
+
var newl = new OpenLayers.Layer.GeoRSS('AdL', '" . calendar_absolute_url("rss.php?region=all&map=1&daylimit=0") . "');
map.addLayer(newl);
+
}
</script>\n";
Modified: branches/dui/schema.sql
==============================================================================
--- branches/dui/schema.sql (original)
+++ branches/dui/schema.sql Sun Jan 4 10:54:02 2009
@@ -76,6 +76,25 @@
PRIMARY KEY (id)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
+CREATE TABLE IF NOT EXISTS `lugs` (
+ `id` int(11) NOT NULL auto_increment,
+ `region` int(11) NOT NULL default '0',
+ `department` int(11) NOT NULL default '0',
+ `name` varchar(255) NOT NULL default '',
+ `url` varchar(255) NOT NULL default '',
+ `city` varchar(255) NOT NULL default '',
+ `address` varchar(255) default NULL,
+ `contact` varchar(255) default NULL,
+ `phone` varchar(255) default NULL,
+ `mail` varchar(255) default NULL,
+ `comment` text,
+ `latitude` float default NULL,
+ `longitude` float default NULL,
+ `postalcode` int(11) default NULL,
+ PRIMARY KEY (`id`),
+ KEY `region` (`region`)
+) TYPE=MyISAM CHARSET=utf8;
+
-- --------------------------------------------------------
CREATE TABLE `tags` (
Modified: branches/dui/showevent.php
==============================================================================
--- branches/dui/showevent.php (original)
+++ branches/dui/showevent.php Sun Jan 4 10:54:02 2009
@@ -39,37 +39,52 @@
exit;
}
-$lugs = $db->query ("select distinct region, name, url from lugs where region = '".
- $event->region . "'");
+
+// Display LUGS
+
+$lugs = $db->query ("select * from lugs where department = '".
+ $event->department . "'");
+
+if ($db->numRows ($lugs) < 10)
+ {
+ $lugs = $db->query ("select * from lugs where region = '".
+ $event->region . "'");
+ }
if ($db->numRows ($lugs) > 0)
{
- echo "<div id=\"lug-list\"><h1>Groupes d'utilisateurs de la région</h1>";
+ echo "<div id=\"lug-list\"><h1>". $adl_lugs_around. "</h1>";
echo " <ul>";
-
while ($lug = $db->fetchObject ($lugs))
{
- echo "<li><a href=\"" . $lug->url . "\">" . $lug->name . "</a> (";
- $dpts = $db->query ("select distinct department from lugs where name = " . $db->quote_smart($lug->name));
- $i = 1;
- while ($dpt = mysql_fetch_object ($dpts))
+ echo "<li>";
+ if ($lug->comment>"")
+ {
+ echo "<a href=\"showlug.php?id=". $lug->id;
+ echo "\" title=\"". sprintf(_("Voir la fiche de %s"), $lug->name). "\">";
+ } else {
+ if ($lug->url>"")
+ {
+ echo "<a href=\"". $lug->url;
+ echo "\" title=\"". sprintf(_("Vers le site de %s"), $lug->name). "\">";
+ }
+ }
+ echo $lug->name;
+ if ($lug->comment>"" | $lug->url>"")
{
- echo $dpt->department;
- if ($i != $db->numRows($dpts))
- echo ", ";
- $i++;
+ echo "</a>";
}
- echo ")</li>";
+ echo " (". $lug->department. ")</li>";
}
echo " </ul>";
}
$event->tags = eventTagsList($event->id);
-if (in_array("libre-en-fete-2007", split(" ", $event->tags)))
+if (in_array("libre-en-fete-2009", split(" ", $event->tags)))
{
echo "<hr/>";
- echo "<h1>Libre en Fête 2007</h1>";
+ echo "<h1>Libre en Fête 2009</h1>";
echo "<p>";
echo "<a href=\"http://www.libre-en-fete.net\">";
echo "<img src=\"lef-2007.png\" width=\"100\" style=\"float: right; margin-left: 5px; border: 0;\"/>";
@@ -102,6 +117,8 @@
$event->submitter,
eventTagsList($event->id));
+//echo return_map_event($event);
+
put_footer();
?>
Added: branches/dui/showlug.php
==============================================================================
--- (empty file)
+++ branches/dui/showlug.php Sun Jan 4 10:54:02 2009
@@ -0,0 +1,78 @@
+<?php
+
+/* Copyright 2004-2009
+ * - Loic Dayot <ldayot A ouvaton POINT org>
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License as
+ * published by the Free Software Foundation; either version 2 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
+ * 02111-1307, USA.
+ */
+
+include("bd.inc.php");
+include("funcs.inc.php");
+include("session.inc.php");
+
+$db = new db();
+$session = new session();
+
+put_header($adl_lug_info);
+
+$lug_id = get_safe_integer('id', 0);
+$lug_res = $db->query ("SELECT * FROM lugs WHERE id='{$lug_id}'");
+$lug = $db->fetchObject($lug_res);
+
+if (! $lug)
+{
+ echo "<p class=\"error\">Pas d'évènement avec cet identifiant.</p>";
+ put_footer();
+ exit;
+}
+
+function format_lug ($db, $lug)
+{
+ $name = stripslashes($lug->name);
+ $region = stripslashes(region_find($db, $lug->region));
+ $city = stripslashes($lug->city);
+ $address = stripslashes($lug->address);
+ $postalcode = stripslashes($lug->postalcode);
+ $comment = stripslashes($lug->comment);
+ $url = stripslashes($lug->url);
+ $contact = stripslashes($lug->contact);
+ $mail = scramble_email(stripslashes($lug->mail));
+ $phone = stripslashes($lug->phone);
+
+ $result = "<h2><i>" . $city . "</i> : " . $name . "</h2>\n\n";
+ $result .= "<h3>Localisation</h3>\n";
+ $result .= "<p>$address - $postalcode <i><a href=\"http://fr.wikipedia.org/wiki/" . $city . "\">"
+ . $city . "</a></i> - <a href=\"http://fr.wikipedia.org/wiki/"
+ . $region . "\">" . $region . "</a></p>\n";
+
+ $result .= "<h3>Description</h3>\n";
+ $result .= "$comment\n";
+
+ $result .= "<h3>Informations</h3>\n";
+ $result .= "<p>Site Web: <a href=\"" . $url . "\">" . $url . "</a></p>\n";
+ $result .= "<p>Contact: $contact <a href=\"mailto:" . $mail . "\">" . $mail . "</a> $phone</p>\n";
+
+ return $result;
+}
+
+
+echo format_lug ($db, $lug);
+
+//echo return_map_lug($event);
+
+put_footer();
+
+?>
Modified: branches/dui/text.inc.php
==============================================================================
--- branches/dui/text.inc.php (original)
+++ branches/dui/text.inc.php Sun Jan 4 10:54:02 2009
@@ -134,6 +134,12 @@
"</li>\n</ul>\n".
"<h5>Ci-dessous, vous pouvez voir le résultat de votre sélection :</h5>";
+// showevents.php
+$adl_lugs_around = "Groupes d'utilisateurs alentours";
+
+// showlug.php
+$adl_lug_info = "Information sur un groupe d'utilisateur";
+
// submit.php
$adl_submit_summary = "<p>L'événement proposé n'apparaîtra dans l'Agenda qu'après ".
"sa validation par un modérateur. Vous en serez informé par un courrier ".
Plus d'informations sur la liste de diffusion Devel