+ Start a Discussion
Zaheer KhanZaheer Khan 

How to do conditional selection of SOQL field in Apex?

Hi,

I have aggregate results and for my bar chart I would like to display Net Sales and Year. Net Sales will be populatd from one of two fields, either US net sale or Canadian net sale, based on customer region. Is there any way, I could get this condional selection in apex? 

Three Fields:
1. Currency - currency values could be either “US” or “CAD”.
2. NetUS – this field holds total sale in US dollars.
3. NetCAD – this field holds total sale in Canadian dollars.
 
IF Currency = “CAD” THEN
Net_Sale_in_CAD
ELSE
Net_Sale_in_US
 
I want to publish the results in Visualforce Chart, based on currency either “NetUS” or “NetCDN” but not both. 
 
This is urgent all the help will be appreciated. I would appreciate, if you include the code. Please see my Apex class and Visualforce page below. Thanks.
 
Apex Class
 
public with sharing class SaleSum {
 
    public Summary[] Summaries { get; set; }
 
 public SaleSum() {
        AggregateResult[] results = [
        Select Brand__c,
CALENDAR_MONTH(Invoice__r.Invoice_Date__c) InvMn,
CALENDAR_YEAR(Invoice__r.Invoice_Date__c) InvYr,
            SUM(Net_Amount_US__c) NetUS,
            SUM(Net_Amount_CDN__c) NetCDN,             
            Invoice__r.Account__r.Currency_Id__c,     
            Invoice__r.Account__r.Customer_Id__c
        From Invoice_Line__c
        Group By Brand__c,
            CALENDAR_MONTH(Invoice__r.Invoice_Date__c), 
            CALENDAR_YEAR(Invoice__r.Invoice_Date__c),
            Invoice__r.Account__r.Currency_Id__c,
            Invoice__r.Account__r.Customer_Id__c
        ];
       
        Summaries = new List<Summary>();
        for (AggregateResult gr : results) {
            Summaries.add(new Summary(gr));
        }
    }
 
    public class Summary {
        public Decimal NetUS { get; private set; }
        public Decimal NetCDN { get; private set; }
        public String Brand { get; private set; }
        public Integer InvMn { get; private set; }
        public integer InvYr { get; private set; }
        public String Curren { get; private set; }
        public String Customer { get; private set; }
       
   public Summary(AggregateResult gr) {
            NetUS = (Decimal) gr.get('NetUS');
            NetCDN = (Decimal) gr.get('NetCDN');
            Brand = (String) gr.get('Brand__c');
            InvMn = (Integer) gr.get('InvMn');
            InvYr = (Integer) gr.get('InvYr');
            Currency = (String) gr.get('Currency_Id__c');
            Customer = (String) gr.get('Customer_Id__c');
        }
    }
 
}
             
 
Visualforce Page:
<apex:page controller="GraphController" title="Annual Sale">
<apex:chart data="{!Summaries}" hidden="false" rendered="true" width="400" height="400">
<apex:axis type="Numeric" position="left" fields="NetUS" title="Net Sales"/>
<apex:axis type="Category" position="bottom" fields="InvYr" title="Year"/>
<apex:barSeries orientation="vertical" axis="left" xField="InvYr" yField="NetUS"/>
</apex:chart>

<apex:dataTable value="{!Summaries}" var="ar">
<apex:column headerValue="Year :" value="{!ar.InvYr}"/>
<apex:column headerValue="Net Sales US :" value="{!ar.NetUS}"/>
</apex:dataTable>
</apex:page>