Revision [1095]

Last edited on 2008-02-07 15:59:21 by EmDee
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
Deletions:
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.
Now you can add the display map page to webERP.

Revision [1094]

Edited on 2008-02-07 15:47:39 by EmDee
Additions:
====Summary====
It gives webERP a menu option to display all customers via a google map.
====How to====

Revision [1093]

Edited on 2008-02-07 15:46:04 by EmDee
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 ''._('Latitude').':';
> echo '';
> echo ''._('Longitude').':';
> echo '';
> echo ''._('Type (commercial, domestic)').':';
> echo '';
>

Revision [1092]

Edited on 2008-02-07 15:40:38 by EmDee
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.
Deletions:
ADD `type` varchar(30) NO NULL AFTER `lng`;

Revision [1091]

Edited on 2008-02-07 15:38:27 by EmDee
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 '';
// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
// ADD TO XML DOCUMENT NODE
echo ' 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 '';
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');








include ('includes/footer.inc');

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



  • " . _('Geo Map of Customers') . '
    • '; ?>


    Revision [1090]

    Edited on 2008-02-07 15:29:34 by EmDee
    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.
    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 "

    Branchcode: " . $id . ", Address: " . $address . " failed to geocode.\n";
    echo "Received status " . $status . "
    \n

    ";
    }
    usleep($delay);
    }
    }
    ?>

    Revision [1089]

    Edited on 2008-02-07 15:24:57 by EmDee
    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`;
    Deletions:
    ADD `lng` FLOAT( 10, 6 ) NOT NULL AFTER `lat` ;

    Revision [1072]

    The oldest known version of this page was created on 2008-01-31 16:08:30 by EmDee
    Valid XHTML 1.0 Transitional :: Valid CSS :: Powered by WikkaWiki