[Devel] r414 - branches/dui

[svn AT agendadulibre.org]

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&amp;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'&eacute;v&egrave;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&nbsp;:</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 ".