=====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
%%
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 "Branchcode: " . $id . ", Address: " . $address . " failed to geocode.\n";
echo "Received status " . $status . "
\n
";
}
usleep($delay);
}
}
?>
%%
Then this script will generate a XML file, that google maps will access when displaying the map.
geocode_genxml.php
%%
','>',$xmlStr);
$xmlStr=str_replace('"','"',$xmlStr);
$xmlStr=str_replace("'",''',$xmlStr);
$xmlStr=str_replace("&",'&',$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 '';
// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
// ADD TO XML DOCUMENT NODE
echo '';
}
// End XML file
echo '';
?>
%%
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
%%