Adding Google Maps to webERP ie. Geocoding data


This mod does the following:

Summary


It gives webERP a menu option to display all customers via a google map.

How to


It adds some fields to the customer branch table, these fields allow a latitude and longitude to be assigned to customers. It also adds a 'type' field that allows you to categorize customers in different ways (for example domestic clients, and commercial clients), these types can then be displayed with different markers in google maps.

You need to ensure you have valid address data assigned to the customer branches, or the geocoding will fail on that customer (ie the address will not be found).

You need to get your own google maps API key.

A script is run (you probably only need to run it once per day via cron), and this updates all customers with their geocoded address data.

Another script is run which generates an XML file, which contains all the data google maps needs... since google maps references the XML file and not the database, this saves database processing time.

First update your custbranch table to add the fields required:

ALTER TABLE `custbranch` ADD `lat` FLOAT( 10, 6 ) NOT NULL AFTER `braddress6` ,
ADD `lng` FLOAT( 10, 6 ) NOT NULL AFTER `lat` 
ADD `type`  varchar(30) DEFAULT 'domestic' NOT NULL AFTER `lng`;


You'll need to change your customer branch types to either commercial or domestic manually.

Then add a new script, named geocode.php, modify the config variables to suit your setup:
This script when run will update all your customer branches with the latitude and longitude, and output any errors.

geocode.php
<?php
require("config.php");

// Set the weberp database name
$database = 'your weberp database name';

define("MAPS_HOST", "maps.google.com");
// Remember to set your own API key
define("KEY", "your google maps api key");

// Opens a connection to a MySQL server
$connection = mysql_connect("localhost", $dbuser, $dbpassword);
if (!$connection) {
  die("Not connected : " . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die("Can\'t use db : " . mysql_error());
}

// Select all the rows in the custbranch table
$query = "SELECT * FROM custbranch WHERE 1";
$result = mysql_query($query);
if (!$result) {
  die("Invalid query: " . mysql_error());
}

// Initialize delay in geocode speed
$delay = 0;
$base_url = "http://" . MAPS_HOST . "/maps/geo?output=xml" . "&key=" . KEY;

// Iterate through the rows, geocoding each address
while ($row = @mysql_fetch_assoc($result)) {
  $geocode_pending = true;

  while ($geocode_pending) {
	$address = $row["braddress1"] . ", " . $row["braddress2"] . ", " . $row["braddress3"] . ", " . $row["braddress4"];
	$id = $row["branchcode"];
	$debtorno =$row["debtorno"];
	$request_url = $base_url . "&q=" . urlencode($address);
	$xml = simplexml_load_file($request_url) or die("url not loading");

	$status = $xml->Response->Status->code;
	if (strcmp($status, "200") == 0) {
	  // Successful geocode
	  $geocode_pending = false;
	  $coordinates = $xml->Response->Placemark->Point->coordinates;
	  $coordinatesSplit = split(",", $coordinates);
	  // Format: Longitude, Latitude, Altitude
	  $lat = $coordinatesSplit[1];
	  $lng = $coordinatesSplit[0];

	  $query = sprintf("UPDATE custbranch " .
	         " SET lat = '%s', lng = '%s' " .
	         " WHERE branchcode = '%s' " .
	         " AND debtorno = '%s' LIMIT 1;",
	         mysql_real_escape_string($lat),
	         mysql_real_escape_string($lng),
	         mysql_real_escape_string($id),
	         mysql_real_escape_string($debtorno));
	  $update_result = mysql_query($query);
	  if (!$update_result) {
	    die("Invalid query: " . mysql_error());
	  }
	} else if (strcmp($status, "620") == 0) {
	  // sent geocodes too fast
	  $delay += 100000;
	} else {
	  // failure to geocode
	  $geocode_pending = false;
	  echo "<p>Branchcode: " . $id . ", Address: " . $address . " failed to geocode.\n";
	  echo "Received status " . $status . "
\n</p>";
	}
	usleep($delay);
  }
}
?>



Then this script will generate a XML file, that google maps will access when displaying the map.

geocode_genxml.php
<?php
require("config.php");

// Set the weberp database name
$database = 'your weberp database name';

function parseToXML($htmlStr)
{
$xmlStr=str_replace('<','&lt;',$htmlStr);
$xmlStr=str_replace('>','&gt;',$xmlStr);
$xmlStr=str_replace('"','&quot;',$xmlStr);
$xmlStr=str_replace("'",'&#39;',$xmlStr);
$xmlStr=str_replace("&",'&amp;',$xmlStr);
return $xmlStr;
}

// Opens a connection to a MySQL server
$connection=mysql_connect (localhost, $dbuser, $dbpassword);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM custbranch WHERE 1";
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

header("Content-type: text/xml");

// Start XML file, echo parent node
echo '<markers>';

// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE
  echo '<marker ';
  echo 'name="' . parseToXML($row['brname']) . '" ';
  echo 'address="' . parseToXML($row["braddress1"] . ", " . $row["braddress2"] . ", " . $row["braddress3"] . ", " . $row["braddress4"]) . '" ';
  echo 'lat="' . $row['lat'] . '" ';
  echo 'lng="' . $row['lng'] . '" ';
  echo 'type="' . $row['type'] . '" ';
  echo '/>';
}

// End XML file
echo '</markers>';
?>


You can run the script (geocode_genxml.php) to see if your XML file is being created properly.

Now you can add a display map page to webERP.

Create a file called displaymap.php.
You need to set the API key again.
You need to set the centre of the map to where your business is, and the zoom level.
You also need to set the google map height and width to suit your screen resolution.

displaymap.php
<?
$PageSecurity = 3;
	 include ('includes/session.inc');
$title = _('Geocoded Customer Branches Report');
	 include ('includes/header.inc');
?>
	<script src="http://maps.google.com/maps?file=api&v=your api key"
	        type="text/javascript"></script>
	<script type="text/javascript">
	//<![CDATA[

 var iconBlue = new GIcon();
	iconBlue.image = 'http://labs.google.com/ridefinder/images/mm_20_blue.png';
	iconBlue.shadow = 'http://labs.google.com/ridefinder/images/mm_20_shadow.png';
	iconBlue.iconSize = new GSize(12, 20);
	iconBlue.shadowSize = new GSize(22, 20);
	iconBlue.iconAnchor = new GPoint(6, 20);
	iconBlue.infoWindowAnchor = new GPoint(5, 1);

var iconRed = new GIcon();
	iconRed.image = 'http://labs.google.com/ridefinder/images/mm_20_red.png';
	iconRed.shadow = 'http://labs.google.com/ridefinder/images/mm_20_shadow.png';
	iconRed.iconSize = new GSize(12, 20);
	iconRed.shadowSize = new GSize(22, 20);
	iconRed.iconAnchor = new GPoint(6, 20);
	iconRed.infoWindowAnchor = new GPoint(5, 1);

	var customIcons = [];
	customIcons["commercial"] = iconBlue;
	customIcons["domestic"] = iconRed;

	function load() {
	  if (GBrowserIsCompatible()) {
	    var map = new GMap2(document.getElementById("map"));
	    map.addControl(new GSmallMapControl());
	    map.addControl(new GMapTypeControl());
	    map.setCenter(new GLatLng(-37.813465, 144.878143), 10);

	    GDownloadUrl("geocode_genxml.php", function(data) {
	      var xml = GXml.parse(data);
	      var markers = xml.documentElement.getElementsByTagName("marker");
	      for (var i = 0; i < markers.length; i++) {
	        var name = markers[i].getAttribute("name");
	        var address = markers[i].getAttribute("address");
	        var type = markers[i].getAttribute("type");
	        var point = new GLatLng(parseFloat(markers[i].getAttribute("lat")),
	                                parseFloat(markers[i].getAttribute("lng")));
	        var marker = createMarker(point, name, address, type);
	        map.addOverlay(marker);
	      }
	    });
	  }
	}
 function createMarker(point, name, address, type) {
	  var marker = new GMarker(point, customIcons[type]);
	  var html = "<b>" + name + "</b> <br/>" + address;
	  GEvent.addListener(marker, 'click', function() {
	    marker.openInfoWindowHtml(html);
	  });
	  return marker;
}

	//]]>
  </script>
  </head>

  <body onload="load()" onunload="GUnload()">
<p>
	<center><div align="center" id="map" style="width: 1300px; height: 600px"></div></center>
</p>
  </body>
<?
include ('includes/footer.inc');
?>
</html>


You can add a menu link to the google map by modifying webERP's index.php

from line 171, add
<tr>
	                                                   <tr>
	                                                    <td class="menu_group_item">
	                                                            <?php echo "<A HREF='" . $rootpath . '/displaymap.php?' . SID . "'><LI>" . _('Geo Map of Customers') . '</LI></A>'; ?>
	                                                    </td>
	                                                    </tr>


Finally, to add a facility to view and update the lat/lng and the customer type, you can make these changes to CustomerBranches.php, this will give you those options from within webERP.

This is a diff from 3.08 CustomerBranches.php.old, to the new CustomerBranches.php:
82a83,85
>                                               lat = '" . DB_escape_string($_POST['lat']) . "',
>                                               lng = '" . DB_escape_string($_POST['lng']) . "',
>                                               type = '" . DB_escape_string($_POST['type']) . "',
126a130,132
>                                               lat,
>                                               lng,
>                                               type,
155a162,164
>                                       '" . DB_escape_string($_POST['lat']) . "',
>                                       '" . DB_escape_string($_POST['lng']) . "',
>                                       '" . DB_escape_string($_POST['type']) . "',
187a197,199
>                                               lat,
>                                               lng,
>                                               type,
215a228,230
>                                       '" . DB_escape_string($_POST['lat']) . "',
>                                       '" . DB_escape_string($_POST['lng']) . "',
>                                       '" . DB_escape_string($_POST['type']) . "',
312a328,330
>               unset($_POST['lat']);
>               unset($_POST['lng']);
>               unset($_POST['type']);
527a546,548
>                               lat,
>                               lng,
>                               type,
561a583,585
>               $_POST['lat']  = $myrow['lat'];
>               $_POST['lng']  = $myrow['lng'];
>               $_POST['type']  = $myrow['type'];
653a678,685
> // added long/lat coordinates - emdeex
>       echo '<TR><TD>'._('Latitude').':</TD>';
>       echo '<TD><input type="Text" name="lat" SIZE=16 MAXLENGTH=16 value="'. $_POST['lat'].'"></TD></TR>';
>       echo '<TR><TD>'._('Longitude').':</TD>';
>       echo '<TD><input type="Text" name="lng" SIZE=16 MAXLENGTH=16 value="'. $_POST['lng'].'"></TD></TR>';
>       echo '<TR><TD>'._('Type (commercial, domestic)').':</TD>';
>       echo '<TD><input type="Text" name="type" SIZE=16 MAXLENGTH=16 value="'. $_POST['type'].'"></TD></TR>';
>
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki