[Devel] r415 - branches/dui

[svn AT agendadulibre.org]

Author: ldayot
Date: Sun Jan  4 16:44:00 2009
New Revision: 415

Log:
Petites modifications suite du precedent commit.
Principalement : maj du schema sql.
Separation de l'import des epn dans la base des GULs et de la recherche 
de coordonnees geospaciales des GULs.
Modif de recherche des noms de ville de name en majname (je ne comprends 
pas comment a ete remplie ma base cities avec des donnees partielles 
(sans les accents)).
Du coup, un peu plus de carte de localisation pour les lugs.



Added:
   branches/dui/findgeogul.php
Modified:
   branches/dui/importepn.php
   branches/dui/lugtextlist.php
   branches/dui/rss.php
   branches/dui/schema.sql
   branches/dui/showlug.php

Added: branches/dui/findgeogul.php
==============================================================================
--- (empty file)
+++ branches/dui/findgeogul.php Sun Jan  4 16:44:00 2009
@@ -0,0 +1,100 @@
+<?php
+/*
+ * Trouver les coordonnées geospatiale de tous les guls
+*/
+
+include("text.inc.php");
+include("bd.inc.php");
+include("funcs.inc.php");
+include("session.inc.php");
+include("user.inc.php");
+
+$db = new db();
+$user = new user($db);
+
+if (isset($_GET['disconnect']))
+{
+  $user->disconnect();
+  header("Location:" . calendar_absolute_url());
+}
+
+calendar_setlocale();
+
+put_header("Geolocalise les GULs");
+
+echo "<h2>G&eacute;olocalise les GULs</h2>";
+
+
+$lug_id = get_safe_integer('id', 0);
+if ($lug_id>0)
+  {
+    $lug_res = $db->query ("SELECT * FROM lugs WHERE id='{$lug_id}'");
+    if ($db->numRows($lug_res)==0)
+    {
+      echo "<p class=\"error\">Pas de GUL avec cet identifiant.</p>";
+      put_footer();
+      exit;
+    }
+  }
+else
+  {
+    $lug_res = $db->query ("SELECT * FROM lugs WHERE latitude=0 OR latitude 
IS NULL LIMIT 0,500");
+  }
+
+$count_ok = $count_nok = 0;
+
+while($lug = $db->fetchObject($lug_res))
+  {
+    // trouver les coordonnées GPS via Google à partir de l'adresse
+    $url_gps = "http://maps.google.com/maps/geo?q=";.
+      $lug->address. " ". $lug->postalcode. " ". $lug->city. " 
France&output=csv&key=ABQIAAAATndsWAV5Q2y7pRRi-22W_hTxw9fvAnrsiYejTsRxd4b0cj9HKxSNCXUxAANaoACDzXWznNLVPto_jA";
+    $gps_file = file(str_replace(" ", "+", $url_gps), FILE_IGNORE_NEW_LINES 
| FILE_SKIP_EMPTY_LINES);
+    if ($lug_id>0)
+      {
+        echo str_replace(" ", "+", $url_gps)."<br/>";
+        print_r($gps_file);
+      }
+    list(,,$lug->latitude, $lug->longitude) = explode(",", $gps_file[0]);
+    
+    if ($lug->latitude>0) 
+      {
+        echo "+ ";
+        $count_ok++;
+      }
+    else// on prend les coordonnees de la ville si on ne trouve pas
+      {
+        echo "-{$lug->id}\n";
+        $count_nok++;
+        if ($lug->city == "")
+          continue; // no city
+
+        $city = $db->query("select * from cities where majname LIKE '" . 
addslashes($lug->city) . "'");
+        if (! $city)
+          continue; // error in query
+        if ($db->numRows($city) == 0)
+          continue; // city not found
+        if ($db->numRows($city)>1)
+          {
+            $city = $db->query("select * from cities where majname LIKE '" . 
addslashes($lug->city) . "' AND postalcode='{$lug->postalcode}'");
+            if (! $city)
+              continue; // error in query
+            if ($db->numRows($city) == 0)
+              continue; // city with postalcode not found
+          }
+
+        $city = $db->fetchObject($city);
+        $lug->longitude = $city->longitude;
+        $lug->latitude  = $city->latitude;
+      }
+              
+    $sql = "UPDATE lugs SET latitude='{$lug->latitude}', 
longitude='{$lug->longitude}' WHERE id='{$lug->id}'";
+    $db->query($sql);
+
+  } // end while
+  
+  echo "\n\n<h3>Termin&eacute;</h3>\n";
+  echo "<p>Ok : $count_ok</p><p>Pas Ok : $count_nok</p>\n";
+
+  echo "<p><a href=\"findgeogul.php\">Relancer le script</a></p>\n";
+
+?>

Modified: branches/dui/importepn.php
==============================================================================
--- branches/dui/importepn.php  (original)
+++ branches/dui/importepn.php  Sun Jan  4 16:44:00 2009
@@ -1,14 +1,23 @@
 <?php
 /*
  * Importer le répertoire des EPN depuis la base nationale
- * et l'injecter dans la table des gul (ou autre, à voir)
+ * et l'injecter dans la table des GULs
 */
 
 include("text.inc.php");
 include("bd.inc.php");
 include("funcs.inc.php");
+include("session.inc.php");
+include("user.inc.php");
 
 $db = new db();
+$user = new user($db);
+
+if (isset($_GET['disconnect']))
+{
+  $user->disconnect();
+  header("Location:" . calendar_absolute_url());
+}
 
 put_header("Import des EPN");
 
@@ -45,7 +54,7 @@
               {
                 if (trim($element)=="</tr>")
                   {
-                    echo "<br />$nextIs = ". $epn[$nextIs]. "<br/>\n";
+                    //echo "<br />$nextIs = ". $epn[$nextIs]. "<br/>\n";
                     $nextIs = false;
                     continue;
                   }
@@ -79,11 +88,9 @@
                 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['address'] = str_ireplace($epn['postalcode'], "", 
$epn['address']);
+        $epn['address'] = str_ireplace($epn['city'], "", $epn['address']);
         
         $epn['comment'] =
           ($epn['services']>"" ? "Services : ". $epn['services']. "<br/>\n" 
: "").
@@ -123,5 +130,4 @@
     return isset($element) ? $element : false;
   }
 
-
 ?>

Modified: branches/dui/lugtextlist.php
==============================================================================
--- branches/dui/lugtextlist.php        (original)
+++ branches/dui/lugtextlist.php        Sun Jan  4 16:44:00 2009
@@ -27,14 +27,27 @@
 
 calendar_setlocale();
 
+$lug_id = get_safe_integer('id', 0);
+if ($lug_id>0)
+  {
+    $lug_res = $db->query ("SELECT * FROM lugs WHERE id='{$lug_id}'");
+    if ($db->numRows($lug_res)==0)
+    {
+      echo "<p class=\"error\">Pas de GUL avec cet identifiant.</p>";
+      put_footer();
+      exit;
+    }
+  }
+else
+  {
+    $lug_res = $db->query ("SELECT * FROM lugs");
+  }
+
 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))
+while($lug = $db->fetchObject($lug_res))
 {
   // has lug coordonates ?
   if ($lug->longitude==0)
@@ -42,14 +55,14 @@
       if ($lug->city == "")
         continue; // no city
 
-      $city = $db->query("select * from cities where name='" . 
addslashes($lug->city) . "'");
+      $city = $db->query("select * from cities where majname LIKE '" . 
addslashes($lug->city) . "'");
       if (! $city)
         continue; // error in query
 
-      if (mysql_num_rows($city) != 1)
+      if ($db->numRows($city) != 1)
         continue; // city not found
 
-      $city = mysql_fetch_object($city);
+      $city = $db->fetchObject($city);
       $lug->longitude = $city->longitude;
       $lug->latitude  = $city->latitude;
     }

Modified: branches/dui/rss.php
==============================================================================
--- branches/dui/rss.php        (original)
+++ branches/dui/rss.php        Sun Jan  4 16:44:00 2009
@@ -42,7 +42,7 @@
 
 function get_city_coordinates ($db, $city)
 {
-  $sql = "SELECT longitude, latitude FROM cities WHERE name='" . $city . "'";
+  $sql = "SELECT longitude, latitude FROM cities WHERE namemaj='" . $city . 
"'";
   $ret = $db->query($sql);
 
   if (mysql_num_rows($ret) == 1)

Modified: branches/dui/schema.sql
==============================================================================
--- branches/dui/schema.sql     (original)
+++ branches/dui/schema.sql     Sun Jan  4 16:44:00 2009
@@ -4,7 +4,9 @@
   `description` text NOT NULL,
   `start_time` datetime NOT NULL default '0000-00-00 00:00:00',
   `end_time` datetime NOT NULL default '0000-00-00 00:00:00',
+  `address` VARCHAR( 255 ) NULL,
   `city` varchar(255) NOT NULL default '',
+  `department` INT NULL,
   `region` int(11) NOT NULL default '0',
   `locality` int(11) NOT NULL default '0',
   `url` varchar(255) NOT NULL default '',
@@ -16,6 +18,7 @@
   `moderated` int(11) NOT NULL default '0',
   PRIMARY KEY  (`id`),
   INDEX (`region`),
+  INDEX (`department`),
   INDEX (`moderated`),
   INDEX (`start_time`),
   INDEX (`end_time`)
@@ -67,16 +70,6 @@
 ) TYPE=MyISAM;
 
 CREATE TABLE 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 '',
-  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',
@@ -92,7 +85,8 @@
   `longitude` float default NULL,
   `postalcode` int(11) default NULL,
   PRIMARY KEY  (`id`),
-  KEY `region` (`region`)
+  KEY `region` (`region`),
+  KEY `department` (`department`)
 ) TYPE=MyISAM CHARSET=utf8;
 
 -- --------------------------------------------------------

Modified: branches/dui/showlug.php
==============================================================================
--- branches/dui/showlug.php    (original)
+++ branches/dui/showlug.php    Sun Jan  4 16:44:00 2009
@@ -23,22 +23,68 @@
 include("funcs.inc.php");
 include("session.inc.php");
 
+calendar_setlocale();
+
 $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>";
+  echo "<p class=\"error\">Pas de GUL avec cet identifiant.</p>";
   put_footer();
   exit;
 }
 
+
+// start map
+$jcode = "
+    <script src=\"http://openlayers.org/api/OpenLayers.js\";></script>
+    <script 
src=\"http://openstreetmap.org/openlayers/OpenStreetMap.js\";></script>
+    <script type=\"text/javascript\">
+        var lat={$lug->latitude}
+        var lon={$lug->longitude}
+        var zoom=11
+        var map;
+        function init() {
+            map = new OpenLayers.Map (\"map\", {
+                controls:[
+                    new OpenLayers.Control.Navigation(),
+                    new OpenLayers.Control.PanZoomBar(),
+                    new OpenLayers.Control.Attribution()],
+                    maxResolution: 156543.0399,
+                    units: 'm',
+            } );
+
+            map.addControl(new OpenLayers.Control.LayerSwitcher());
+
+            layerTilesAtHome = new OpenLayers.Layer.OSM.Mapnik(\"Mapnik\");
+            map.addLayer(layerTilesAtHome);
+
+            var lonLat = new OpenLayers.LonLat(lon, lat).transform(new 
OpenLayers.Projection(\"EPSG:4326\"), new 
OpenLayers.Projection(\"EPSG:900913\"));
+
+            map.setCenter (lonLat, zoom);
+
+            var newl = new OpenLayers.Layer.Text('EPN', {location: '" . 
calendar_absolute_url("lugtextlist.php?id={$lug_id}") . "'});
+            map.addLayer(newl);
+            
+        }
+ 
+    </script>\n";
+
+// end map
+if ($lug->latitude>0)
+  {
+    put_header($adl_lug_info, $jcode, "init();");
+  }
+else
+  {
+    put_header($adl_lug_info);
+  }
+
 function format_lug ($db, $lug)
 {
   $name        = stripslashes($lug->name);
@@ -63,7 +109,7 @@
 
   $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";
+  $result .= "<p>Contact: $contact - <a href=\"mailto:"; . $mail . "\">" . 
$mail . "</a> - $phone</p>\n";
 
   return $result;
 }
@@ -71,7 +117,10 @@
 
 echo format_lug ($db, $lug);
 
-//echo return_map_lug($event);
+// start map
+if ($lug->latitude>0)
+  echo "<div style=\"margin: auto; width:500px; height:400px; border: 1px 
solid black;\" id=\"map\"></div>\n";
+// end map
 
 put_footer();