Monday, 28 December 2015

Month wise data distribution using Google Chart


This topic is related to the charting on Account, I need to show the chart in which the scenario is to show the Order total against account on monthly basis. For example, 10 Orders are made in December 2015, 15 Order in November and 30 Orders in October. So if user go to any Account than there would be Chart which would show these statistics. So I have a made a custom VF page in which I showed this chart using Google Chart. So Let’s start.

I have a made a custom button in Account page layout and if user click this custom button than it would redirect to the custom VF page taking Account Id and current Year in query string. If you see below image, you would that custom button on Account

Picture A

If you click this button on Account detail page than it would open this below custom VF Page

Picture B

This above picture show the Order Total sum month wise. I have made a 3 custom field in Order

1)    Order Month (Formula fieldà Month(EffectiveDate))   [Integer Type]
2)    Order Year    (Formula fieldà Year(EffectiveDate))     [Integer Type]
3)    Order Month String  (Text Field).

1st two fields are the formula field which returns Number of Month and Year. The 3rd field is tricky one, in this field we need the Month Name like “December”, so this field contains the data using Workflow, in workflow, I have set this string field using formula, if the Order Month (formula field) contains 12 than set the “December” text in this field, see below formula image taken from workflow

Picture 3

So this way, I have reached to that position in which SOQL will return that data which will pass into the Google chart to show Chart distribution. Please see the below apex page code.
<apex:page controller="GoogleChartsController" sidebar="false">
    <!-- Google API inclusion -->
<apex:form >
    <apex:includeScript id="a" value="" />

 <span>Select Year:</span><apex:selectList value="{!selectedYear}" multiselect="false" size="1">
                <apex:selectOption itemValue="2015" itemLabel="2015"/>
                <apex:selectOption itemValue="2014" itemLabel="2014"/>
                <apex:selectOption itemValue="2013" itemLabel="2013"/>
                <apex:selectOption itemValue="2012" itemLabel="2012"/>
                <apex:selectOption itemValue="2011" itemLabel="2011"/>
           <apex:commandButton action="{!Generate}" value="Generate Report" id="theButton"/>
 <div id="chartBlock" />
    <script type="text/javascript">
    function getUrlVars()
    var vars = [], hash;
    var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');
    for(var i = 0; i < hashes.length; i++)
        hash = hashes[i].split('=');
        vars[hash[0]] = hash[1];
    return vars;

        // Load the Visualization API and the piechart package.
        google.load('visualization', '1.0', {'packages':['corechart']});
        // Set a callback to run when the Google Visualization API is loaded.
        function initCharts() {        
          // Following the usual Remoting syntax
          // [<namespace>.]<controller>.<method>([params...,] <callbackFunction>(result, event) {...}
          // controller : GoogleChartsController
          // method : loadOpps
          var Id = getUrlVars()["Id"];
          var Year = getUrlVars()["Year"];
                 function(result, event){ 
                     // load Column chart
                     var visualization = new google.visualization.ColumnChart(document.getElementById('chartBlock'));
                     // Prepare table model for chart with columns
                     var data = new google.visualization.DataTable();
                     data.addColumn('string', 'Order');
                     data.addColumn('number', 'Order Amount');
//                     data.addColumn('number', 'Amount');   
                     // add rows from the remoting results
                     for(var i =0; i<result.length;i++){
                        var r = result[i];
                        data.addRow([r.OMonth__c, r.OrderTotal]);
                    // all done, lets draw the chart with some options to make it look nice.
                    visualization.draw(data, {legend : {position: 'top', textStyle: {color: 'blue', fontSize: 10}}, width:window.innerWidth,vAxis:{textStyle:{fontSize: 10}},hAxis:{textStyle:{fontSize: 10},showTextEvery:1,slantedText:false}});
              }, {escape:true});


In above yellow highlighted area, I am fetching the Query strings in javascript and pass these query string values in GoogleChartsController.loadOpps in above yellow highlighted area. This LoadOpps is actually is a remote action defined in controller of this apex page. Now moving towards the controller, see the controller code below.

global with sharing class GoogleChartsController {
public String selectedYear{get;set;}

public GoogleChartsController()
    selectedYear = ApexPages.currentPage().getParameters().get('Year');
public PageReference Generate()
    PageReference nextPage = new PageReference('/apex/GoogleChartsController?Id=' + ApexPages.currentPage().getParameters().get('Id') + '&Year=' + selectedYear);
    return nextPage;

      Loads Orders
    global static List<AggregateResult> loadOpps(String Id,decimal Year) {
        //return [select Id, Name, ExpectedRevenue, Amount from Opportunity order by CreatedDate DESC limit 10];
        List<AggregateResult> li= [SELECT OMonth__c, sum(TotalAmount) OrderTotal FROM Order where AccountId = :Id and Order_Year__c = :Year GROUP BY OMonth__c];
        return li;


The query I am using in above controller code is actually returning that data which I need and this SOQL based on some filter criteria. This way this page show the Order Statistics using google chart.

Sunday, 27 December 2015

On demad Integration of Salesforce Org to another Salesforce Org


This topic is related to the On demad Integration of Salesforce with another Salesforce Org. The scenario is to make a custom VF page and this page we need to capture only those account which are made in last 24 hours in 1st Org and fetch all these account on the 2nd Org. So lets Start, we have 2 Salesforce Org for this activity. So I have made a 2 Orgs. I am naming this Org as Org 1 and Org 2. So we only need to work on Org 2. No need to work on Org 1.

Create a new custom VF page."AccountFromOrg" in Org 2. Please see the below picture

Picture A

In above picture A, if user click the top button “Get from SF Org” than it will go to the first Org i.e. Org 1 and fetches those accounts which are created in between last 24 hours and after fetching those account, it will make 2 panel, one panel contains those accounts which are the new account for Org 2 and right columns contains those accounts which contains those accounts which have already imported to Org 2 and now if user want to make any update on these right column account than he needs to only select those entries and then press the below “Import Record” than these accounts will update on Org 2. And similarly insert the left column account in Org 2.

So in VF Page, please see the below VF page code.
<apex:page controller="AccountFromOrg">
    <apex:form >
        <apex:pagemessages />
        <apex:pageblock title="Upload Account(s)">
            <apex:pageblocksection columns="4">
                <apex:commandbutton value="Get from SF Org" action="{!fetch}" />
        <table style="width:100%">
                <td style="width:50%">
                    <apex:pageblock title="New Record(s)" rendered="{!acclistVisible}">
                        <apex:pageblocktable value="{!lstUAcc}" var="lstA">
                            <apex:column >
                                <apex:facet name="header">
                                    <apex:inputcheckbox onclick="selectAllCheckboxes(this,'uAcc')" />
                                <!-- This is our selected Boolean property in our wrapper class -->
                                <apex:inputcheckbox value="{!lstA.selected}" id="uAcc" />
                            <apex:column value="{!}" />
                            <apex:column value="{!lstA.account.BillingStreet}" />
                            <apex:column value="{!lstA.account.BillingPostalCode}" />
                            <apex:column value="{!lstA.account.BillingCity}" />
                            <apex:column value="{!lstA.account.BillingCountry}" />
                            <apex:column value="{!lstA.account.Site}" />
                            <apex:column value="{!}" />
                            <apex:column value="{!lstA.account.fax}" />
                <td style="width:50%">
                    <apex:pageblock title="Duplicate Record(s)" rendered="{!dacclistVisible}">
                        <apex:pageblocktable value="{!lstDAcc}" var="lstD">
                            <apex:column >
                                <apex:facet name="header">
                                    <apex:inputcheckbox onclick="selectAllCheckboxes(this,'dAcc')" />
                                <!-- This is our selected Boolean property in our wrapper class -->
                                <apex:inputcheckbox value="{!lstD.selected}" id="dAcc" />
                            <apex:column value="{!}" />
                            <apex:column value="{!lstD.account.BillingStreet}" />
                            <apex:column value="{!lstD.account.BillingPostalCode}" />
                            <apex:column value="{!lstD.account.BillingCity}" />
                            <apex:column value="{!lstD.account.BillingCountry}" />
                            <apex:column value="{!lstD.account.Site}" />
                            <apex:column value="{!}" />
                            <apex:column value="{!lstD.account.fax}" />
                <td colspan="2" style="text-align: center">
                    <apex:commandbutton value="Import Record(s)" action="{!importCSVFile}" rendered="{!dacclistVisible || acclistVisible}" />
        <script type="text/javascript">
            function selectAllCheckboxes(obj, receivedInputID) {
                var inputCheckBox = document.getElementsByTagName("input");
                for (var i = 0; i < inputCheckBox.length; i++) {
                    if (inputCheckBox[i].id.indexOf(receivedInputID) != -1) {
                        inputCheckBox[i].checked = obj.checked;

After this, if we see the controller of above apex page.

public class AccountFromOrg{
    public Blob csvFileBody {get;set;}
    public string csvAsString {get;set;}
    public String[] csvFileLines {get;set;}
    public List<AccountWrapper> lstUAcc {get;set;}
    public List<AccountWrapper> lstDAcc {get;set;}
    public Map<String, Contact> mapUContact {get;set;}
    public Map<String, Account> mapUAccount {get;set;}
    public List<Account> lstAccount {get; set;}
    public Boolean acclistVisible {get; set;}
    public Boolean dacclistVisible {get; set;}
    public Map<String, Account> mapAccount {get; set;}
    //Login Domain May be test, prerellogin.pre
    String LOGIN_DOMAIN = 'login';
    public String pwd{get;set;}
    public String userName{get;set;}
    public String errMsg{get;set;}
    public String displayError{get;set;}
    public List<Account> acc{get;set;}
    public Integer depositAmount{get;set;}
    public string AccountId{get;set;}
    public AccountFromOrg(){
        csvFileLines = new String[]{};
        lstUAcc = New List<AccountWrapper>();
        lstDAcc = New List<AccountWrapper>();
        lstAccount = new List<Account>([select Id, Name, Original_Company_Name__c, Account_Duplicate_Count__c from Account]);
        acclistVisible = lstUAcc.size() > 0;
        dacclistVisible = lstDAcc.size() > 0;
        mapAccount = new Map<String, Account>();
        string n;
        for(Account a: lstAccount){
            n = string.valueof(a.Original_Company_Name__c).replaceAll( '\\s+', '').toLowerCase();
            mapAccount.put(n, a);
        userName = '';
        pwd = 'MaizOutllookTTvdM2qm3p9RTkX3gjHfQ8t6';

     public void fetch()
        errMsg  = 'Some error occurred, please try again';
        // Login via SOAP/XML web service api
        HttpRequest request = new HttpRequest();
        request.setEndpoint('https://' + LOGIN_DOMAIN + '');
        request.setHeader('Content-Type', 'text/xml;charset=UTF-8');
        request.setHeader('SOAPAction', '""');
        //not escaping username and password because we're setting those variables above
        //in other words, this line "trusts" the lines above
        //if username and password were sourced elsewhere, they'd need to be escaped below
        request.setBody('<Envelope xmlns=""><Header/><Body><login xmlns=""><username>' + userName+ '</username><password>' + pwd+ '</password></login></Body></Envelope>');
        Dom.XmlNode resultElmt = (new Http()).send(request).getBodyDocument().getRootElement()
          .getChildElement('Body', '')
          .getChildElement('loginResponse', '')
          .getChildElement('result', '');

        // Grab session id and server url
        final String SERVER_URL = resultElmt.getChildElement('serverUrl', '') .getText().split('/services')[0];
        final String SESSION_ID = resultElmt.getChildElement('sessionId', '') .getText();

        // Load first 10 accounts via REST API
        final PageReference theUrl = new PageReference(SERVER_URL + '/services/data/v22.0/query/');
        datetime dt = - 1;
        Date myDate = date.newinstance(dT.year(), dT.month(),;
        string dtS = string.valueof(myDate).replace('00:00:00','T01:02:03Z');
        string query =    'Select a.Phone, a.Name, a.CreatedBy.FirstName, a.CreatedById,a.Original_Company_Name__c,a.Account_Duplicate_Count__c From Account a where a.createddate >= ' + dtS + 'T01:02:03Z' ;
        //order by a.createddate desc';
        system.debug('query' + query);
        theUrl.getParameters().put('q',query );
        request = new HttpRequest();
        request.setHeader('Authorization', 'OAuth ' + SESSION_ID);
        String body = (new Http()).send(request).getBody();
        JSONParser parser = JSON.createParser(body);

        }while(parser.hasCurrentToken() && !'records'.equals(parser.getCurrentName()));


        acc = (List<Account>) parser.readValueAs(List<Account>.class);
        catch(Exception e)
            displayError = 'block';

    public pageReference importCSVFile(){
            List<Account> tmpA = new List<Account>();
            List<Account> tmpB = new List<Account>();
            mapUAccount = new Map<String, Account>();
            for(AccountWrapper aw: lstUAcc){
            insert tmpA;
            for(AccountWrapper aw: lstDAcc){
            system.debug('tmpAdeel: ' + tmpA);
            system.debug('tmpBdeel: ' + tmpB);
            update tmpB;

        catch (Exception e){
            ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured.');
            return null;
        PageReference nextPage = new PageReference('/apex/AccountFromOrg');
        return nextPage;
    public void uploadCSVFile(){
        try {
            if(csvFileBody != null && csvFileBody.size() > 0){
                csvAsString = csvFileBody.toString();
                csvFileLines = csvAsString.split('\n');
                system.debug('CVS File: '+ csvFileLines.size());
                List<Account> lstTmp = new List<Account>();
                //List<Contact> lstTmp1 = new List<Contact>();
                mapUContact = new Map<String, Contact>();
                for(Integer i=1;i<csvFileLines.size();i++){
                    Account a = new Account();
                    Contact c = new Contact();
                    string[] csvRecordData = csvFileLines[i].split(',');
                    string str = csvRecordData[1].replaceAll( '\\s+', '').toLowerCase();
                    a.Name = csvRecordData[1];
                    a.Original_Company_Name__c = csvRecordData[1];
                    a.Phone = csvRecordData[9];
                    a.Fax = csvRecordData[10];
                    a.BillingStreet = csvRecordData[4];
                    a.BillingPostalCode = csvRecordData[7];
                    a.BillingCity = csvRecordData[5];
                    a.BillingCountry = csvRecordData[8];
                    a.Site = csvRecordData[6];
                    if(string.isNotBlank(csvRecordData[2]) && string.isNotBlank(csvRecordData[3])){
                        c.LastName = csvRecordData[2];
                        c.Title = csvRecordData[3];
                        mapUContact.put(str, c);
                ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.WARNING,'Please select valid file to upload.');
        catch (Exception e){
            ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured while reading CSV file.');
    public class AccountWrapper{
        public Account account {get; set;}
        public Boolean selected {get; set;}
        public AccountWrapper(Account a){
            this.account = a;
            this.selected = false;
    public void filterAccounts(List<Account> accounts){
        system.debug('Asif: ' + mapAccount);
        for(Account a: accounts){
            string str = string.valueof(a.Name).replaceAll( '\\s+', '').toLowerCase();
            system.debug('key: ' + str);
                a.Id = mapAccount.get(str).Id ;
                //double cnt = mapAccount.get(str).Account_Duplicate_Count__c != null ? double.valueof(mapAccount.get(str).Account_Duplicate_Count__c) : 0;
                //a.Account_Duplicate_Count__c = double.valueof(cnt+1);
                //system.debug('a.Account_Duplicate_Count__c: ' + a.Account_Duplicate_Count__c);
                lstDAcc.add(new AccountWrapper(a));
                //a.Id = mapAccount.get(str).Id ;
                lstUAcc.add(new AccountWrapper(a));
        system.debug('lstDAccAdeel: ' + lstDAcc);
        system.debug('lstUAccAdeel: ' + lstUAcc);
        acclistVisible = lstUAcc.size() > 0;
        dacclistVisible = lstDAcc.size() > 0;

In above code, if you see the yellow highlighted code, you would see that function which is making a call to Org 1 and pass the query which is in red highlighted area. So this way it only fetches only those account which are created in last 24 years. In above code, if you see the green highlighted code, this code is the User Name and password of Org 1, the security token should be append with password. In above light blue highlighted code, it is actually deciding which list is go for Insert or Update in Org 2. After selecting from right and left panel and presses the Import button than Accounts get passed into the Org 2 in smooth manner.