Hello all,

I have some "working" code that I feel can be much more efficient but am not sure how to go about it any help would be greatly appreciated.

This is the complete working code im using to display fields on a custom visualforce page. I feel like I can reduce the number of SOQL queries by using the Group by Rollup and grouping feature but I’m not sure how parse the results.


Example of query that will give me more than one piece of information (this returns the expected resutls, but im not sure how to capture the various datapoints in apex):

List <AggregateResult> EventAgg =  [SELECT Sum(Total_Memberships__c) MembershipSum, count(ID) CNT, Calendar_month(Event_Date__c) Month, Facilitated_by_MD__c FacilitatedBy, Booked_By__c BookedBy,GROUPING (Facilitated_by_MD__C) FacilGroup, GROUPING (Booked_By__c) BookedGroup, GROUPING (Event_Date__c) monthGroup FROM Campaign WHERE End_Date_Time__c = This_Year GROUP BY ROLLUP (Facilitated_by_MD__c , Booked_By__c, Event_Date__c)];

Complete "working" code:

@testvisible  
Public with sharing class InfoPanelController {

     Public integer PacketsOutstanding = 0;
     public integer PayrollFormsOutstanding = 0;
     public integer BranchReferrals = 0;
     Public integer EventsMTD = 0;
     Public integer ErrorsMTD = 0;
     Public integer ErrorsYTD = 0;
     Public integer MembershipsMTD = 0;
     Public integer MembershipsYTD = 0;
     Public string  UserFullName = UserInfo.getFirstname() + ' ' + UserInfo.getLastname();
        
         
    
List <AggregateResult> PacketResults         =  [SELECT Count(ID)PacketCount FROM Campaign WHERE Facilitated_by_MD__r.id = :UserInfo.getUserId() AND Expecting_packet__c = 'True']; 
    
List <AggregateResult> PayrollResults        =  [SELECT Count(ID)PayrollCount FROM Campaign WHERE Facilitated_by_MD__r.id = :UserInfo.getUserId() AND Payroll_DateStamp__c = null AND   End_Date_Time__c >= Today AND End_Date_Time__c = This_Year AND Status = 'Completed' AND Payroll_Forms__c >0];
                                                  
List <AggregateResult> BranchReferralResults =  [SELECT Count(ID)BranchReferralCount FROM Branch_Referrals__c WHERE Referral_Assigned_to__r.id = :UserInfo.getUserId() AND Referral_Status__c = 'Pending'];
                                                  
List <AggregateResult> EventsMTDResults      =  [SELECT Count(ID)EventsMTDCount FROM Campaign WHERE Booked_by__r.id = :UserInfo.getUserId() AND End_Date_Time__c = This_Month AND status = 'Completed'];                                              
                                                      
List <AggregateResult> ErrorsMTDResults      =  [SELECT Count(ID)ErrorsMTDCount FROM Missing_Info_Errors__c WHERE Officer__c = :userfullname AND App_Sign_Date__c = This_Month AND Known__c = 'Known' ];
    
List <AggregateResult> ErrorsYTDResults      =  [SELECT Count(ID)ErrorsYTDCount FROM Missing_Info_Errors__c WHERE Officer__c = :userfullname AND App_Sign_Date__c = This_Year AND Known__c = 'Known' ];
                                                      
List <AggregateResult> MembershipsMTDResults =  [SELECT Sum(Total_Memberships__c)MembershipsMTDCount FROM Campaign WHERE Booked_by__r.id = :UserInfo.getUserid() AND End_Date_Time__c = This_Month AND Status = 'Completed'];
    
List <AggregateResult> MembershipsYTDResults =  [SELECT Sum(Total_Memberships__c)MembershipsYTDCount FROM Campaign WHERE Booked_by__r.id = :UserInfo.getUserid() AND End_Date_Time__c = This_Year AND Status = 'Completed'];
    
     
       Public integer getpacketsOutstanding(){
            packetsOutstanding = integer.valueof(PacketResults[0].get('PacketCount'));
            Return PacketsOutstanding;
        }
        
        Public integer getPayrollFormsOutstanding(){
            PayrollFormsOutstanding = integer.valueof(PayrollResults[0].get('PayrollCount'));
            Return PayrollFormsOutstanding;
        }
        
        Public integer getBranchReferrals(){
            BranchReferrals = integer.valueof(BranchReferralResults[0].get('BranchReferralCount'));
            Return BranchReferrals;
        }
        
        Public integer getEventsMTD(){
            EventsMTD = integer.valueof(EventsMTDResults[0].get('EventsMTDCount'));
            Return EventsMTD;   
        }
        
        Public integer getErrorsMTD(){
            ErrorsMTD = integer.valueof(ErrorsMTDResults[0].get('ErrorsMTDCount'));
            Return ErrorsMTD;   
        }    
    
        Public integer getErrorsYTD(){
            ErrorsYTD = integer.valueof(ErrorsYTDResults[0].get('ErrorsYTDCount'));
            Return ErrorsYTD;   
        } 
     
        Public integer getMembershipsMTD(){
            MembershipsMTD = integer.valueof(MembershipsMTDResults[0].get('MembershipsMTDCount'));
            If (MembershipsMTD == null)
                MembershipsMTD = 0;
            Return MembershipsMTD;
        }
            
        Public integer getMembershipsYTD(){
            MembershipsYTD = integer.valueof(MembershipsYTDResults[0].get('MembershipsYTDCount'));
            If (MembershipsYTD == null)
                MembershipsYTD = 0;
            Return MembershipsYTD;    
        } 
}