Additions:
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.
geocode.php
geocode_genxml.php
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.
displaymap.php
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.
geocode.php
geocode_genxml.php
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.
displaymap.php
Deletions:
Another script is run once per day, 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.
Now you can add the display map page to webERP.
Additions:
====Summary====
It gives webERP a menu option to display all customers via a google map.
====How to====
It gives webERP a menu option to display all customers via a google map.
====How to====
Additions:
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>';
>
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>';
>
Additions:
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.
You'll need to change your customer branch types to either commercial or domestic manually.
Deletions:
Additions:
Then this script will generate a XML file, that google maps will access when displaying the map.
function parseToXML($htmlStr)
{
$xmlStr=str_replace('<','<',$htmlStr);
$xmlStr=str_replace('>','>',$xmlStr);
$xmlStr=str_replace('"','"',$xmlStr);
$xmlStr=str_replace("'",''',$xmlStr);
$xmlStr=str_replace("&",'&',$xmlStr);
return $xmlStr;
$connection=mysql_connect (localhost, $dbuser, $dbpassword);
die('Not connected : ' . mysql_error());
die ('Can\'t use db : ' . mysql_error());
// Select all the rows in the markers table
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>';
Now you can add the 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.
<?
$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>
function parseToXML($htmlStr)
{
$xmlStr=str_replace('<','<',$htmlStr);
$xmlStr=str_replace('>','>',$xmlStr);
$xmlStr=str_replace('"','"',$xmlStr);
$xmlStr=str_replace("'",''',$xmlStr);
$xmlStr=str_replace("&",'&',$xmlStr);
return $xmlStr;
$connection=mysql_connect (localhost, $dbuser, $dbpassword);
die('Not connected : ' . mysql_error());
die ('Can\'t use db : ' . mysql_error());
// Select all the rows in the markers table
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>';
Now you can add the 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.
<?
$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>
Additions:
You need to get your own google maps API key.
First update your custbranch table to add the fields required:
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.
<?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);
}
}
?>
First update your custbranch table to add the fields required:
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.
<?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);
}
}
?>
Additions:
=====Adding Google Maps to webERP ie. Geocoding data =====
This mod does the following:
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).
A script is run once per day (via cron), and this updates all customers with their geocoded address data.
Another script is run once per day, 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.
ADD `lng` FLOAT( 10, 6 ) NOT NULL AFTER `lat`
ADD `type` varchar(30) NO NULL AFTER `lng`;
This mod does the following:
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).
A script is run once per day (via cron), and this updates all customers with their geocoded address data.
Another script is run once per day, 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.
ADD `lng` FLOAT( 10, 6 ) NOT NULL AFTER `lat`
ADD `type` varchar(30) NO NULL AFTER `lng`;