How-to: Relate users and records based on their geographical location

Show and Tell
1

Hi everyone,
today, I’d like to share an idea that came to mind after I had a closer look at the maps-modules in SuiteCRM.

use case/requirement:
often, clients would like to relate new contacts/accounts/opportunities automatically to specific users, based on their respective sales territory.

Example:

  • user “a” is in charge for customers in western Munich
  • user “b” is in charge for customers in eastern Munich
  • all new accounts in western Munich become related to user “a” automatically
  • all new accounts in eastern Munich become related to user “b” automatically

popular, but limited approach:

  • logic hooks/workflows based on postal codes, city or region names
  • downsides:
    • sales territories cannot be defined freely, as postal code areas are fixed
    • a mapping based on postal codes can be difficult, especially if clients work in multiple countries with different postal code systems

Idea instead:

  1. draw sales territories using the jjwg_areas-module like this:

NoA5AddbE4
NoA5AddbE4806×455 1.53 MB

hint: please note that I’ve changed the “assigned to” user of the map. This user is considered to be “in charge” of the selected area. It’s of course possible to create many sales territories/map areas per user.

  1. Done! With the code below you just need to wait for the schedulers to finish. All records that could be related become updated automatically. I’ve took another gif that displays the scheduler execution and the results:

D2ieEvhSAY
D2ieEvhSAY1943×747 2.5 MB

the code
The code for SuiteCRM is actually not very complicated, I’ve simply created a new scheduler and added some logging. The calculation is done using alexpechkarev/geometry-library, which I installed first using composer. The new scheduler looks for accounts that have no assigned user, but valid geo-coordinates.

the files themselves can be found in the linked repository, I just want to post the main class here:

GeoRelations.php:

<?php
if (!defined('sugarEntry') || !sugarEntry) {
    die('Not A Valid Entry Point');
}

class GeoRelations {
    /**
     * returns the list of accounts that are currently not related to any user but have valid lng/lat-values.
     * @return mixed
     */
    private static function getUnrelatedAccounts(){
        $accountBean = BeanFactory::getBean('Accounts');
        $list =  $accountBean->get_list(
            'name',
            "(accounts.assigned_user_id ='' or accounts.assigned_user_id is null) and 
            (accounts_cstm.jjwg_maps_lat_c is not null and accounts_cstm.jjwg_maps_lng_c is not null)");
        return $list['list'];
    }

    /**
     * Method to verify if a new record lies within any jjwg_area
     */
    public static function updateAssignedGeoUser(){
        LoggerManager::getLogger();
        $prefix = 'Scheduler: GeoRelate: ';

        $accountList = self::getUnrelatedAccounts();

        foreach($accountList as $bean){
            //only necessary if no user is assigned currently
            if($bean->assigned_user_id == "" || empty($bean->assigned_user_id)){
                $longitudeSet = ($bean->jjwg_maps_lng_c === "" || empty($bean->jjwg_maps_lng_c)) ? false : true;
                $latitudeSet = ($bean->jjwg_maps_lat_c === "" || empty($bean->jjwg_maps_lat_c)) ? false : true;
                if($latitudeSet && $longitudeSet){
                    LoggerManager::getLogger()->fatal($prefix . ' trying to relate account ' . $bean->name);

                    //get available map areas
                    $mapAreaBean = BeanFactory::getBean('jjwg_Areas');
                    $mapAreaList = $mapAreaBean->get_list('',"jjwg_areas.deleted=0",0,-1);
                    if(count($mapAreaList['list'])>0){
                        foreach($mapAreaList['list'] as $mapArea){
                            if(self::isBeanInArea($bean,$mapArea)){
                                /**
                                 * Match! The current $bean is supposed to be inside the current $mapArea!
                                 */
                                LoggerManager::getLogger()->fatal($prefix . ' area ' . $mapArea->name . ' is a match for bean ' . $bean->name .' ('. $bean->id.')');
                                $bean->assigned_user_id = $mapArea->assigned_user_id;
                                $bean->save();
                                break;
                            } else {
                                /**
                                 * No match! The current $bean does not belong to the current $mapArea
                                 */
                                LoggerManager::getLogger()->fatal($prefix . ' area ' . $mapArea->name . ' not suitable for bean ' . $bean->name .' ('. $bean->id.')');
                            }
                        }
                    } else {
                        //no map areas saved in suite -> continue without update
                        LoggerManager::getLogger()->fatal($prefix . 'no map areas found, skipping.');
                    }
                } else {
                    //no assigned user, but no geo-codes available in bean
                    LoggerManager::getLogger()->fatal($prefix . 'no geo information found in bean ' . $bean->name . ', skipping.');
                }
            } else {
                //assigned user is already set -> no to do.
                LoggerManager::getLogger()->fatal($prefix . 'assigned user already set, skipping.');
            }
        }
    }

    /**
     * actual method for calculating if a point (itemBean) is within a given area (areaBean)
     * @param $itemBean
     * @param $areaBean
     * @return bool
     */
    private static function isBeanInArea($itemBean,$areaBean){
        $lng = (float)$itemBean->jjwg_maps_lng_c;
        $lat = (float)$itemBean->jjwg_maps_lat_c;
        $beanArea = explode(" ",$areaBean->coordinates);
        $cleanArea = array();
        foreach($beanArea as $key=>$area){
            $tempArray = explode(",",$area);
            $point = array();
            $point['lat'] = $tempArray[1];
            $point['lng'] = $tempArray[0];
            array_push($cleanArea,$point);
        }

        return  \GeometryLibrary\PolyUtil::containsLocation([
            'lng' => $lng,
            'lat' => $lat
        ],
            $cleanArea,
            false
        );
    }
}

the testing:
obviously, this approach is more keen to fail. It is therefore highly recommended to test if

  • the sales territories are big enough to work
  • the form of the sales territories is not too complex
  • and similiar concerns

I therefore created a new repository that is using phpunit9 to test the correct allocation of new records. The idea is to have a test tool that supports to easily create maps and test points.

approach:

  • create a new map on google my maps
  • add one layer called “test_area”. draw a polygon (the sales territory to test)
  • and one layer called “test_valid”. add as many points that are within your polygon. these points are tested for a positive result.
  • add the final layer “test_invalid”. add as many points as you like, but the points should be outside of the polygon as the expected test result for this layer is false.

the map should look like this:

chrome_iWo9iFfogB
chrome_iWo9iFfogB1040×745 372 KB

this map can be exported as .kml file. if the layers match the previous descriptions, the test framework will generate unit tests for every single point (inside and outside of the area), and for as many maps as needed. Simply put each .kml file into the maps/ folder and run the index.php with any browser to get an analysis like this (one table per map):

chrome_ILY6NDwiAh
chrome_ILY6NDwiAh295×746 6.74 KB

or, if you’re more into phpunit, all maps and points can be tested with "vendor/bin/phpunit.bat" --testdox tests
result:

phpunit9 result

λ “vendor/bin/phpunit.bat” --testdox tests
PHPUnit 9.4.0 by Sebastian Bergmann and contributors.

Geo Code
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 1”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 2”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 3”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 4”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 5”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 6”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 7”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 8”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 9”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 10”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 11”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 12”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 13”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 14”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 15”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 16”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 17”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 18”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 19”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 20”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 21”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 22”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 23”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 24”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 25”
:heavy_check_mark: Point was positioned correctly with data set “fi_northern_germany.kml / Punkt 26”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 1”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 2”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 3”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 4”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 5”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 6”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 7”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 8”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 9”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 10”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 11”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 12”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 13”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 14”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 15”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 16”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 17”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 18”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 19”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 20”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 21”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 22”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 23”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 24”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 25”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 26”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 27”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 28”
:heavy_check_mark: Point was positioned correctly with data set “fi_southern_germany.kml / Punkt 29”

Time: 00:00.397, Memory: 6.00 MB
OK (55 tests, 55 assertions)

You can also find another map example directly on google:

Testresults for this specific map:

fi_test_ffm_close results

unit tests:

PHPUnit 9.4.0 by Sebastian Bergmann and contributors.

Geo Code
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 1.0"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 2.1"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 3.2"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 4.3"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 5.4"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 6.5"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 7.6"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 8.7"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 9.8"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 10.9"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 11.10"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 12.11"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 13.12"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 14.13"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 15.14"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 16.15"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 17.16"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 18.17"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 19.18"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 20.19"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 21.20"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 22.21"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 23.22"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 24.23"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 25.24"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 26.25"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 27.26"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 28.27"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 1.28"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 2.29"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 3.30"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 4.31"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 6.32"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 7.33"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 8.34"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 9.35"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 10.36"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 11.37"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 12.38"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 13.39"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 14.40"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 15.41"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 16.42"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 17.43"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 18.44"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 19.45"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 20.46"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 21.47"
 ✔ Point was positioned correctly with data set "fi_test_ffm_close.kml / Punkt 22.48"

Time: 00:00.356, Memory: 6.00 MB

OK (49 tests, 49 assertions)

index.php:

chrome_HQSQGDgyks
chrome_HQSQGDgyks296×859 7.1 KB

I collected all touched or created files in a new repository, and you don’t need to rerun composer as the vendor/ folder is included completely (and some demo maps). Within subfolder SuiteCRM-src/, you’ll also find my scheduler (and all other involved files) that is shown above.

Please feel free to comment/improve this approach! I’m already having some ideas to improve the “sales territory” module:

  • add multiselect-box to enable/disable specific modules (so that the scheduler not only looks for accounts)
  • add a security group picker (in case a team is responsible for the territory)
7 Likes
2

very detailed and useful contribution. Thank you @crmspace for sharing this.

3

It’s nice to see you’re getting into this module, it’s a bit complicated for me since I never used it in practice. But I do see it’s quite valuable; indeed for some people it’s pure gold.

I’d love to see a PR moving all this code definitely into Core and getting rid of those jjwg prefixes all over. It’s unnecessary to have all these vardefs and logic hooks spread around SuiteCRM installation as if this was still an add-on.

4

btw, an additional idea:

  • each map should have a specific point, lets call it center point
  • if there are no matching sales territories for a new record, choose the closest one (measurement: distance from the new records location to the center point of each area)
5

Useful and explicit information, thanks for this!