Thursday, 19 December 2013

Displaying Opportunities Amount Sums for All Accounts per year and months(Aggregate Query with multiple groupings)

Here we are displaying the all accounts with opportunities amounts sum for different months in a year.

Here in the following controller (class) i just used aggregate with grouping of Account Name,Calendar Month of closedate ,Calendar year of closed date.

I am using static year 2013 we can pass dynamically and we can get results with the same code.

Its very helpful if we need more than one grouping.

Controller for this:
==============
Account with Opportunity Sum for every month
========================

public  class Accountwithopportunitiesamountcls {
    public integer year{get;set;}
    public List<Amountcls> accountamountlst{get;set;}
    Set<String> accnames=new Set<String>();
    public Accountwithopportunitiesamountcls(){
        accountamountlst=new List<Amountcls>();
        year=2013;
        Amountcls amountobj=new Amountcls();       
        amountobj.accountName='Total';
        for(AggregateResult agtobj:[select Sum(Amount) sum,Calendar_Month(closedate) month,Calendar_year(closedate) year,Account.Name acname
            from Opportunity where Calendar_year(closedate)=:year group by Calendar_Month(closedate),Calendar_year(closedate),Account.Name]){
                Amountcls accobj=new Amountcls();
                if(!accnames.contains((String)agtobj.get('acname'))){
                    accnames.add((String)agtobj.get('acname'));
                    accobj.accountname=(String)agtobj.get('acname');
                    if(agtobj.get('month')==1){
                        accobj.jan= integer.valueof(agtobj.get('sum'));
                        if(amountobj.jan!=null)
                            amountobj.jan+=accobj.jan!=null?accobj.jan:0;
                        else
                            amountobj.jan=accobj.jan!=null?accobj.jan:0;
                    }
                   
                    if(agtobj.get('month')==2){
                        accobj.feb= integer.valueof(agtobj.get('sum'));
                        if(amountobj.feb!=null)
                            amountobj.feb+=accobj.feb!=null?accobj.feb:0;
                        else
                            amountobj.feb=accobj.feb!=null?accobj.feb:0;
                        //amountobj.feb+=accobj.feb;                       
                    }
                    if(agtobj.get('month')==3){
                        accobj.mar= integer.valueof(agtobj.get('sum'));
                        if(amountobj.mar!=null)
                            amountobj.mar+=accobj.mar!=null?accobj.mar:0;
                        else
                            amountobj.mar=accobj.mar!=null?accobj.mar:0;
                        //amountobj.mar+=accobj.mar;                   
                    }
                    if(agtobj.get('month')==4){
                        accobj.apr= integer.valueof(agtobj.get('sum'));
                        if(amountobj.apr!=null)
                            amountobj.apr+=accobj.apr!=null?accobj.apr:0;
                        else
                            amountobj.apr=accobj.apr!=null?accobj.apr:0;
                    }
                    if(agtobj.get('month')==5){
                        accobj.may= integer.valueof(agtobj.get('sum'));
                        if(amountobj.may!=null)
                            amountobj.may+=accobj.may!=null?accobj.may:0;
                        else
                            amountobj.may=accobj.may!=null?accobj.may:0;
                       
                    }
                    if(agtobj.get('month')==6){
                        accobj.jun= integer.valueof(agtobj.get('sum'));
                        if(amountobj.jun!=null)
                            amountobj.jun+=accobj.jun!=null?accobj.jun:0;
                        else
                            amountobj.jun=accobj.jun!=null?accobj.jun:0;
                    }
                    if(agtobj.get('month')==7){
                        accobj.jul= integer.valueof(agtobj.get('sum'));
                        if(amountobj.jul!=null)
                            amountobj.jul+=accobj.jul!=null?accobj.jul:0;
                        else
                            amountobj.jul=accobj.jul!=null?accobj.jul:0;
                    }
                    if(agtobj.get('month')==8){
                        accobj.aug= integer.valueof(agtobj.get('sum'));
                        if(amountobj.aug!=null)
                            amountobj.aug+=accobj.aug!=null?accobj.aug:0;
                        else
                            amountobj.aug=accobj.aug!=null?accobj.aug:0;
                    }
                    if(agtobj.get('month')==9){
                        accobj.sep= integer.valueof(agtobj.get('sum'));
                        if(amountobj.sep!=null)
                            amountobj.sep+=accobj.sep!=null?accobj.sep:0;
                        else
                            amountobj.sep=accobj.sep!=null?accobj.sep:0;
                    }
                    if(agtobj.get('month')==10){
                        accobj.oct= integer.valueof(agtobj.get('sum'));
                        if(amountobj.oct!=null)
                            amountobj.oct+=accobj.oct!=null?accobj.oct:0;
                        else
                            amountobj.oct=accobj.oct!=null?accobj.oct:0;   
                    }
                    if(agtobj.get('month')==11){
                        accobj.nov= integer.valueof(agtobj.get('sum'));
                        if(amountobj.nov!=null)
                            amountobj.nov+=accobj.nov!=null?accobj.nov:0;
                        else
                            amountobj.nov=accobj.nov!=null?accobj.nov:0;   
                    }
                    if(agtobj.get('month')==12){
                        accobj.dec= integer.valueof(agtobj.get('sum'));
                        if(amountobj.dec!=null)
                            amountobj.dec+=accobj.dec!=null?accobj.dec:0;
                        else
                            amountobj.dec=accobj.dec!=null?accobj.dec:0;   
                    }  
                   
                    accobj.total=(accobj.jan!=null?accobj.jan:0)+(accobj.feb!=null?accobj.feb:0)+(accobj.mar!=null?accobj.mar:0)+(accobj.apr!=null?accobj.apr:0)+(accobj.may!=null?accobj.may:0)+
                            +(accobj.jun!=null?accobj.jun:0)+(accobj.jul!=null?accobj.jul:0)+(accobj.aug!=null?accobj.aug:0)
                            +(accobj.sep!=null?accobj.sep:0)+(accobj.oct!=null?accobj.oct:0)+(accobj.nov!=null?accobj.nov:0)
                            +(accobj.dec!=null?accobj.dec:0);
                    if(amountobj.total!=null){
                        amountobj.total+=accobj.total!=null?accobj.total:0;
                    }
                    else{
                        amountobj.total=accobj.total!=null?accobj.total:0;
                    }
                    accountamountlst.add(accobj);
                }
                System.debug('accountamountlst:'+accountamountlst);
            }
            accountamountlst.add(amountobj);
    }
    public class Amountcls{
        public String accountname{get;set;}
        public integer jan{get;set;}
        public integer feb{get;set;}
        public integer mar{get;set;}
        public integer apr{get;set;}
        public integer may{get;set;}
        public integer jun{get;set;}
        public integer jul{get;set;}
        public integer aug{get;set;}
        public integer sep{get;set;}
        public integer oct{get;set;}
        public integer nov{get;set;}
        public integer dec{get;set;}
        public integer total{get;set;}
    }

}


Page Code:
==========

<apex:page controller="Accountwithopportunitiesamountcls" sidebar="false">
            <apex:form >
                        <apex:pageBlock >
                                    <apex:pageBlockTable value="{!accountamountlst}" var="acc">
                                                <apex:column headerValue="Account Name" value="{!acc.Accountname}"/>
                                                <apex:column headerValue="Jan" value="{!acc.jan}"/>               
                                                <apex:column headerValue="Feb" value="{!acc.feb}"/>              
                                                <apex:column headerValue="Mar" value="{!acc.mar}"/>              
                                                <apex:column headerValue="Apr" value="{!acc.apr}"/>   
                                                <apex:column headerValue="May" value="{!acc.may}"/>            
                                                <apex:column headerValue="Jun" value="{!acc.jun}"/>               
                                                <apex:column headerValue="Jul" value="{!acc.jul}"/>                                                                 
                                                <apex:column headerValue="Aug" value="{!acc.aug}"/> 
                                                <apex:column headerValue="Oct" value="{!acc.sep}"/>              
                                                <apex:column headerValue="Sep" value="{!acc.oct}"/>              
                                                <apex:column headerValue="Nov" value="{!acc.nov}"/>             
                                                <apex:column headerValue="Dec" value="{!acc.dec}"/>             
                                                <apex:column headerValue="Total" value="{!acc.total}"/>                       
                                    </apex:pageBlockTable>                      
                        </apex:pageBlock>                  
            </apex:form>
</apex:page>


Output:
=======



No comments:

Post a Comment