2

I am wondering if there is an easy way to dynamically query accounts based on the account having the primary FSE name attached to it. The Primary_FSE__c field is on the Installed Product Object, which is a related list to the account page. The thing I can't figure out is how to insert each FSE's name dynamically into the SOQL query at the bottom of this apex class. So where it states \'Tim Bowen%\', I need that to be where the query places the correct FSE on there (which is what the where id =:ApexPages.currentPage().getParameters().get('id) is referencing).

If anyone has any ideas, I would be very appreciative. Thanks!

public with sharing class NearbyTechnicianController {

   public Account account { get; set; }
   public SVMXC__Service_Group_Members__c sgm {get;set;}



    // constructor
    public NearbyTechnicianController() {
        sgm = [select id, name, SVMXC__Street__c, SVMXC__City__c, SVMXC__State__c, SVMXC__Zip__c, Geolocation__Latitude__s, Geolocation__Longitude__s from SVMXC__Service_Group_Members__c where id = :ApexPages.currentPage().getParameters().get('id')];

        account = [select Id, Name, CustomerMasterId__c, ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, Geolocation__Latitude__s, Geolocation__Longitude__s, (Select SVMXC__Status__c, Name, Primary_FSE__c, SVMXC__Product__c, Model_Code__c from R00N70000001hzZ0EAI__r) 
                  from Account limit 1];

    }

    @RemoteAction
    public static List<Account> getNearbyTech(Decimal latitude, Decimal longitude) {

        String q = 'select Id, Name, CustomerMasterId__c, ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, Geolocation__Latitude__s, Geolocation__Longitude__s, (Select SVMXC__Status__c, Name, Primary_FSE__c, SVMXC__Product__c, Model_Code__c from R00N70000001hzZ0EAI__r) from Account ';
        q += 'where DISTANCE(Geolocation__c, GEOLOCATION( ';
        q += String.valueOf(latitude) + ', ' + String.valueOf(longitude);
        q += ' ), \'km\') < 160';
        q += 'AND id in (select SVMXC__Company__c from SVMXC__Installed_Product__c where **(Primary_FSE__c like \'Tim Bowen%\')** AND (SVMXC__Status__c like \'Installed%\') AND ((Model_Code__c like \'DSD%\') OR (Model_Code__c like \'ADV%\') OR (Model_Code__c like \'EDG%\')))';

        return Database.query(q);      
    }  
}

<apex:page showHeader="false" sidebar="false" standardStylesheets="false" cache="false" doctype="html-5.0" controller="NearbyTechnicianController">
<html>

<head>
    <title>Nearby Installed Products</title>

    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1.0"/> 

    <!-- jQuery + jQuery Mobile files -->
    <apex:includeScript value="{!URLFOR($Resource.jquery, 'jquery-1.9.1.min.js')}"/>
    <apex:includeScript value="{!URLFOR($Resource.jquery, 'jquery.mobile-1.4.0.min.js')}"/>
    <apex:stylesheet value="{!URLFOR($Resource.jquery, 'jquery.mobile-1.4.0.min.css')}" />

 <!-- Google Maps -->
    <script type="text/javascript" src="https://maps.google.com/maps/api/js?sensor=true"></script>

    <style>
        body, html {
            height: 100%;   
            width: 100%;
        }
        #nearbyAccountsPage, .ui-content, #map_canvas { 
            width: 100%; 
            height: 100%; 
            padding: 0; 
        }
    </style>

    <script>

        var map;
        var lat = {!sgm.Geolocation__Latitude__s};
        var lng = {!sgm.Geolocation__Longitude__s};
        var TechName = "{!sgm.Name}";
        var acctMasterId = "{!account.CustomerMasterId__c}";
        var acctModelCode = "{!insp.Model_Code__c}";
        var Techstreet = "{!sgm.SVMXC__Street__c}";
        var Techcity = "{!sgm.SVMXC__City__c}";
        var Techstate = "{!sgm.SVMXC__State__c}";
        var TechZip = "{!sgm.SVMXC__Zip__c}";

        $(document).on('pageshow', '#nearbyAccountsPage', function() {
            initGmap();
        });

        function initGmap() {
            var latlng = new google.maps.LatLng(lat, lng);

            var mapOptions = {
                center: latlng,
                mapTypeId: google.maps.MapTypeId.ROADMAP,
                zoom: 13,

                // The following options are added to optimize for mobile screens. 
                mapTypeControl: false,
                streetViewControl: false, 
                scaleControl: false,
                rotateControl: false,
                panControl: false,
                zoomControlOptions: {
                    style: google.maps.ZoomControlStyle.SMALL,
                    position: google.maps.ControlPosition.LEFT_TOP
                }
            };

            map = new google.maps.Map(document.getElementById('map_canvas'), mapOptions);

            createMarkerAndInfoWindow(TechName + '<br>' + Techstreet + '<br>' + Techcity + ',' + ' ' + Techstate + ' ' + TechZip, latlng, true);
        }

        function createMarkerAndInfoWindow(TechName, latlng, isPrimary) {
            var marker = new google.maps.Marker({
                position: latlng,
                title: TechName,  
                map: map,  
                icon: isPrimary ? 'http://chart.apis.google.com/chart?chst=d_map_pin_letter&chld=%E2%80%A2|FE7569' : 'http://chart.apis.google.com/chart?chst=d_map_pin_letter&chld=%E2%80%A2|ADDE63'       
            });


            var infoWindow = new google.maps.InfoWindow({
                content:'<b>' + TechName + '<b>'                       
                });

            google.maps.event.addListener(marker, 'click', function() {
                infoWindow.open(map, marker);
            });
        }

        function getNearbyTech() {
            NearbyTechnicianController.getNearbyTech(lat, lng, TechName, function (result, event) {               
                if (event.status) {
                    for (i = 0; i < result.length; i++) {
                        var acctName = result[i].Name;
                        var acctShippingStreet = result[i].ShippingStreet;
                        var acctShippingCity = result[i].ShippingCity;
                        var acctShippingState = result[i].ShippingState;
                        var acctShippingPostalCode = result[i].ShippingPostalCode;
                        var acctMasterId = result[i].CustomerMasterID__c;
                        var acctModelCode = result[i].Model_Code__c;
                        var acctLatlng = new google.maps.LatLng(result[i].Geolocation__Latitude__s, result[i].Geolocation__Longitude__s);

                        createMarkerAndInfoWindow(acctName + '<br>' + acctShippingStreet + '<br>' + acctShippingCity + ', ' + acctShippingState + ' ' + acctShippingPostalCode + '<br>' + acctMasterId + '<br>' + acctModelCode, acctLatlng, false);
                    }
                }
            });
        }
    </script>
</head>
<body>
    <div data-role="page" id="nearbyAccountsPage">
        <div data-role="header" data-position="fixed">
            <h1>{!sgm.name}</h1>
            <a href="#" data-role="button" class="ui-btn-right" onclick="getNearbyTech();">Show Nearby</a>
        </div>
        <div data-role="content">
            <div id="map_canvas"></div> 
        </div>
    </div>
</body>
</html>
</apex:page>
3

Dynamic SOQL allows simple bind variables to be hooked into the query. So providing you create a simple variable - fseLike in the code below - you can just use the normal bind syntax:

@RemoteAction
public static List<Account> getNearbyTech(Decimal latitude, Decimal longitude) {

    String fseLike = sqm.name + '%';

    String q = '...';
    ....
    q += 'AND id in (select SVMXC__Company__c from SVMXC__Installed_Product__c '
    q += 'where (Primary_FSE__c like :fseLike) AND ...';

    return Database.query(q); 
}

(I'm not clear from your description what constitutes the "correct FSE" so guessed it was SVMXC__Service_Group_Members__c.name.)

PS

Didn't notice its a remote action. Any values used in a remote action have to be passed from the client-side so you'll need to add the parameter and change the client-side to match (i.e. add '{!sqm.name}' to the JavaScript function parameter list):

@RemoteAction
public static List<Account> getNearbyTech(String fse, Decimal latitude, Decimal longitude) {

    String fseLike = fse + '%';

    String q = '...';
    ....
    q += 'AND id in (select SVMXC__Company__c from SVMXC__Installed_Product__c '
    q += 'where (Primary_FSE__c like :fseLike) AND ...';

    return Database.query(q); 
}
|improve this answer|||||
  • I tried this and received this Error: Compile Error: Variable does not exist: sgm.name at line 19 column 26 – NewbieDeveloper27 Aug 22 '14 at 14:17
  • @NewbieDeveloper27 My typo - should be sgm not sqm. – Keith C Aug 22 '14 at 15:39
  • No i fixed it to sgm.name when i first put the code in and it still says that the variable doesn't exist. – NewbieDeveloper27 Aug 22 '14 at 16:24
  • @NewbieDeveloper27 See the "PS". – Keith C Aug 22 '14 at 16:42
  • One more question: How can I reference the Model_Code__c from the custom object SVCMX__Installed_Product__c in the google window on the map? Since the field is on the custom object and is not on the account object, the result is coming up as "undefined" – NewbieDeveloper27 Aug 26 '14 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.