function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
etechcareersetechcareers 

Too many SOQL queries: 101???

Hi Everyone:

   I do not understand why is my getCustomers method timing out to too many SOQL Queries?? When I remove the method it works...

Here is the error on my log:

Class.compensationreport.getCustomers: line 148, column 1

Class.compensationreport.query: line 227, column 1

 

Please help....and I keep getting Apex Governor Limit Warning that states Number of SOQL queries: 77 out of 100???

Thanks

Z

 

public class compensationreport{
    public string AAid{get;set;}
    public string AAWid{get;set;}
    public string AATid{get;set;}
    public string AACSid{get;set;}
    public string CompName{get;set;}
    public Integer Gross{get;set;}
    public Integer Withdrawn{get;set;}
    public Integer Transferred{get;set;}
    public Integer Customers{get;set;}
    public String Level { get { return Level ;} set { Level=value ; } } 
    public String Monthrun { get { return Monthrun ;} set { Monthrun=value ; } }    
    
    public List<GrossHolder> queryResults{ get; set; }  
    public class GrossHolder {       
        public String AAName {get; set;}
        public Integer TotalGross {get; set;}
        public String compid{get;set;}    
        public GrossHolder(){}
    }
    
/*******************************************TOTAL Withdrawn******************************************************************************************/

    public List<WithdrawnHolder> queryResultsW{ get; set; }  
    public class WithdrawnHolder {
        public Integer TotalW {get; set;}
        public String AANameW {get; set;}
        public String aax {get;set;}
        public WithdrawnHolder(){}
    }
 
    public String getWithdrawn(String id){
    List<SObject> qrystringW = new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringW = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c ='Withdrawn' 
                            AND Withdrawn_Date__c = THIS_MONTH
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid]; 
        }
        else {
            qrystringW = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c ='Withdrawn' 
                            AND Withdrawn_Date__c = LAST_MONTH
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid]; 
        
        }
                            
    AggregateResult[] groupedResults = qrystringW; 
        
    queryResultsW = new List<WithdrawnHolder>();
    
    for (AggregateResult ar : groupedResults)  {
        WithdrawnHolder myObject = new WithdrawnHolder();
            myObject.aax = String.valueOf(ar.get('Ownerid'));
            AAWid =  myObject.aax;
            myObject.TotalW = (Integer) ar.get('ce');
        Withdrawn = myObject.TotalW ;    
        queryResultsW.add(myObject); 
    }
        return null;    
}  
/*******************************************TOTAL TRANSFERRED******************************************************************************************/
    public List<WithdrawnHolderT> queryResultsWT{ get; set; }  

    public class WithdrawnHolderT {

        public Integer TotalWT {get; set;}
         public String AANameWT {get; set;}
         public String aaxT {get;set;}
    public WithdrawnHolderT(){
    }
    }
 
    public String getWithdrawnT(String id){
    List<SObject> qrystringT = new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringT = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c = 'Transferred to DL' 
                            AND Withdrawn_Date__c = THIS_MONTH  
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid]; 
         }
         else {
              qrystringT = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c = 'Transferred to DL' 
                            AND Withdrawn_Date__c = LAST_MONTH  
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid];        
         }                   
    AggregateResult[] groupedResults = qrystringT; 
        
    queryResultsWT = new List<WithdrawnHolderT>();
    
    for (AggregateResult ar : groupedResults)  {
        WithdrawnHolderT myObject = new WithdrawnHolderT();
        myObject.aaxT = String.valueOf(ar.get('Ownerid'));
        AATid =  myObject.aaxT;
        myObject.TotalWT = (Integer) ar.get('ce');
        Transferred = myObject.TotalWT ;
    
    queryResultsWT.add(myObject); 
      
    }
    return null;    
    }  
/*******************************************TOTAL CUSTOMERS******************************************************************************************/    
    public List<customers> queryResultscs{ get; set; }  
    public class customers {

        public Integer TotalCS {get; set;}
         public String AANameCS {get; set;}
         public String aaxCS {get;set;}
    public customers(){
    }
    }
 
    public String getCustomers(String id){
    List<SObject> qrystringc = new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringc = [SELECT Ownerid,Count(Id) ce 
                            FROM Account 
                            Where Owner.Role_Name__c Like '%Advisor%' 
                            AND Ownerid=:id AND CreatedDate = THIS_MONTH
                            GROUP BY Ownerid];
        }
        else{
            qrystringc = [SELECT Ownerid,Count(Id) ce 
                            FROM Account 
                            Where Owner.Role_Name__c Like '%Advisor%' 
                            AND Ownerid=:id AND CreatedDate = LAST_MONTH
                            GROUP BY Ownerid];        
        }
    
    
    AggregateResult[] groupedResults = qrystringc;  
        
    queryResultscs = new List<customers>();
    
    for (AggregateResult ar : groupedResults)  {
        customers myObject = new customers();
        myObject.aaxCS = String.valueOf(ar.get('Ownerid'));
        AACSid =  myObject.aaxCS;
        myObject.TotalCS = (Integer) ar.get('ce');
        Customers = myObject.TotalCS ;
    
    queryResultscs.add(myObject); 
      
    }
    return null;    
    }                               
/*******************************************Actual******************************************************************************************/     
public PageReference query() { 
Date Lastmonthfirst;
Date Lastmonthend;

Date selectedDate =  Date.Today(); //Give your date
Date firstDate = selectedDate.toStartOfMonth();
Date lastDate = firstDate.addDays(date.daysInMonth(selectedDate.year() , selectedDate.month())  - 1);
system.debug(firstDate  + ' **** ' +lastDate );
//******************************************************************************************************
Lastmonthfirst = selectedDate.toStartOfMonth().addMonths(-1);
Lastmonthend = Lastmonthfirst.addDays(date.daysInMonth(Lastmonthfirst.year() , Lastmonthfirst.month())  - 1);
system.debug(Lastmonthfirst  + ' **** ' +Lastmonthend );

     
    List<Compensation__c>  delmarker = new List<Compensation__c>(); 
    If(Monthrun=='THIS_MONTH'){
        delmarker=[select id from Compensation__c where Date_ran__c =THIS_MONTH];
    }
    else{delmarker=[select id from Compensation__c where Date_ran__c =LAST_MONTH];}
    
    for(Compensation__c sm:delmarker){
        delete sm;
    }  
       
    List<SObject> qrystringf =new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringf=[SELECT Ownerid, Count(Id) ce
                        FROM Opportunity Where Comp__c=False  
                        AND CloseDate=THIS_MONTH 
                        AND StageName = 'Closed Won'
                        GROUP BY Ownerid];
    }
    else
    {
            qrystringf=[SELECT Ownerid, Count(Id) ce
                        FROM Opportunity Where Comp__c=False  
                    AND CloseDate=LAST_MONTH 
    AND StageName = 'Closed Won'
    GROUP BY Ownerid];           
    }         
  If (Level=='AA'){
       
              AggregateResult[] groupedResults = qrystringf; 

                                      
    queryResults = new List<GrossHolder>();
    for (AggregateResult ar : groupedResults)  {
        GrossHolder myObject = new GrossHolder();
            myObject.TotalGross = (Integer) ar.get('ce');
            myObject.compid = String.valueOf(ar.get('Ownerid'));
            AAid = myObject.compid;
            
            getWithdrawn(AAid);
            getWithdrawnT(AAid);
            getCustomers(AAid);
/*****************************INSERT COMP**********************************************************************/

            Compensation__c cmp = new Compensation__c();
            cmp.Name__c = myobject.compid;
            cmp.Comp_Plan__c ='AA';
            cmp.Total_Gross__c = myObject.TotalGross;
            IF(Monthrun=='THIS_MONTH'){            
                cmp.Date_ran__c = date.TODAY();
            }
            else{
                cmp.Date_ran__c = Lastmonthfirst;
            }
                
               if(AAWid!=AAid){
                    cmp.Total_Withdrawn__c = 0;
                }
                else{
                    cmp.Total_Withdrawn__c = Withdrawn;
                    if(cmp.Total_Withdrawn__c == null){
                        cmp.Total_Withdrawn__c = 0;
                    }                    
                }
                 
                if(AATid!=AAid){
                    cmp.Total_Transfers__c = 0;
                }
                else{
                    cmp.Total_Transfers__c = Transferred;
                    if(cmp.Total_Transfers__c == null){
                        cmp.Total_Transfers__c = 0;
                    }
               } 
               
                if(AACSid!=AAid){
                    cmp.Total_Customers__c = 0;
                }
                else{
                    cmp.Total_Customers__c = Customers;
                    if(cmp.Total_Customers__c == null){
                        cmp.Total_Customers__c = 0;
                    }
               }                                
                insert cmp;
        queryResults.add(myObject); 
        }
        }
        return null;
    } 
}

 

Naidu PothiniNaidu Pothini

In your queries, when you use variables like this 

 

Withdrawn_Date__c = LAST_MONTH

 Use : before the variables.

 

Try changing the queires.

 

public class compensationreport{
    public string AAid{get;set;}
    public string AAWid{get;set;}
    public string AATid{get;set;}
    public string AACSid{get;set;}
    public string CompName{get;set;}
    public Integer Gross{get;set;}
    public Integer Withdrawn{get;set;}
    public Integer Transferred{get;set;}
    public Integer Customers{get;set;}
    public String Level { get { return Level ;} set { Level=value ; } } 
    public String Monthrun { get { return Monthrun ;} set { Monthrun=value ; } }    
    
    public List<GrossHolder> queryResults{ get; set; }  
    public class GrossHolder {       
        public String AAName {get; set;}
        public Integer TotalGross {get; set;}
        public String compid{get;set;}    
        public GrossHolder(){}
    }
    
/*******************************************TOTAL Withdrawn******************************************************************************************/

    public List<WithdrawnHolder> queryResultsW{ get; set; }  
    public class WithdrawnHolder {
        public Integer TotalW {get; set;}
        public String AANameW {get; set;}
        public String aax {get;set;}
        public WithdrawnHolder(){}
    }
 
    public String getWithdrawn(String id){
    List<SObject> qrystringW = new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringW = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c ='Withdrawn' 
                            AND Withdrawn_Date__c = :THIS_MONTH
                            AND Ownerid = :id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid]; 
        }
        else {
            qrystringW = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c ='Withdrawn' 
                            AND Withdrawn_Date__c = :LAST_MONTH
                            AND Ownerid = :id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid]; 
        
        }
                            
    AggregateResult[] groupedResults = qrystringW; 
        
    queryResultsW = new List<WithdrawnHolder>();
    
    for (AggregateResult ar : groupedResults)  {
        WithdrawnHolder myObject = new WithdrawnHolder();
            myObject.aax = String.valueOf(ar.get('Ownerid'));
            AAWid =  myObject.aax;
            myObject.TotalW = (Integer) ar.get('ce');
        Withdrawn = myObject.TotalW ;    
        queryResultsW.add(myObject); 
    }
        return null;    
}  
/*******************************************TOTAL TRANSFERRED******************************************************************************************/
    public List<WithdrawnHolderT> queryResultsWT{ get; set; }  

    public class WithdrawnHolderT {

        public Integer TotalWT {get; set;}
         public String AANameWT {get; set;}
         public String aaxT {get;set;}
    public WithdrawnHolderT(){
    }
    }
 
    public String getWithdrawnT(String id){
    List<SObject> qrystringT = new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringT = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c = 'Transferred to DL' 
                            AND Withdrawn_Date__c = :THIS_MONTH  
                            AND Ownerid = :id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid]; 
         }
         else {
              qrystringT = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c = 'Transferred to DL' 
                            AND Withdrawn_Date__c = :LAST_MONTH  
                            AND Ownerid = :id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid];        
         }                   
    AggregateResult[] groupedResults = qrystringT; 
        
    queryResultsWT = new List<WithdrawnHolderT>();
    
    for (AggregateResult ar : groupedResults)  {
        WithdrawnHolderT myObject = new WithdrawnHolderT();
        myObject.aaxT = String.valueOf(ar.get('Ownerid'));
        AATid =  myObject.aaxT;
        myObject.TotalWT = (Integer) ar.get('ce');
        Transferred = myObject.TotalWT ;
    
    queryResultsWT.add(myObject); 
      
    }
    return null;    
    }  
/*******************************************TOTAL CUSTOMERS******************************************************************************************/    
    public List<customers> queryResultscs{ get; set; }  
    public class customers {

        public Integer TotalCS {get; set;}
         public String AANameCS {get; set;}
         public String aaxCS {get;set;}
    public customers(){
    }
    }
 
    public String getCustomers(String id){
    List<SObject> qrystringc = new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringc = [SELECT Ownerid,Count(Id) ce 
                            FROM Account 
                            Where Owner.Role_Name__c Like '%Advisor%' 
                            AND Ownerid = :id AND CreatedDate = :THIS_MONTH
                            GROUP BY Ownerid];
        }
        else{
            qrystringc = [SELECT Ownerid,Count(Id) ce 
                            FROM Account 
                            Where Owner.Role_Name__c Like '%Advisor%' 
                            AND Ownerid = :id AND CreatedDate =  :LAST_MONTH
                            GROUP BY Ownerid];        
        }
    
    
    AggregateResult[] groupedResults = qrystringc;  
        
    queryResultscs = new List<customers>();
    
    for (AggregateResult ar : groupedResults)  {
        customers myObject = new customers();
        myObject.aaxCS = String.valueOf(ar.get('Ownerid'));
        AACSid =  myObject.aaxCS;
        myObject.TotalCS = (Integer) ar.get('ce');
        Customers = myObject.TotalCS ;
    
    queryResultscs.add(myObject); 
      
    }
    return null;    
    }                               
/*******************************************Actual******************************************************************************************/     
public PageReference query() { 
Date Lastmonthfirst;
Date Lastmonthend;

Date selectedDate =  Date.Today(); //Give your date
Date firstDate = selectedDate.toStartOfMonth();
Date lastDate = firstDate.addDays(date.daysInMonth(selectedDate.year() , selectedDate.month())  - 1);
system.debug(firstDate  + ' **** ' +lastDate );
//******************************************************************************************************
Lastmonthfirst = selectedDate.toStartOfMonth().addMonths(-1);
Lastmonthend = Lastmonthfirst.addDays(date.daysInMonth(Lastmonthfirst.year() , Lastmonthfirst.month())  - 1);
system.debug(Lastmonthfirst  + ' **** ' +Lastmonthend );

     
    List<Compensation__c>  delmarker = new List<Compensation__c>(); 
    If(Monthrun=='THIS_MONTH'){
        delmarker=[select id from Compensation__c where Date_ran__c = :THIS_MONTH];
    }
    else{delmarker=[select id from Compensation__c where Date_ran__c = :LAST_MONTH];}
    
    for(Compensation__c sm:delmarker){
        delete sm;
    }  
       
    List<SObject> qrystringf =new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringf=[SELECT Ownerid, Count(Id) ce
                        FROM Opportunity Where Comp__c=False  
                        AND CloseDate = :THIS_MONTH 
                        AND StageName = 'Closed Won'
                        GROUP BY Ownerid];
    }
    else
    {
            qrystringf=[SELECT Ownerid, Count(Id) ce
                        FROM Opportunity Where Comp__c = False  
                    AND CloseDate = :LAST_MONTH 
    AND StageName = 'Closed Won'
    GROUP BY Ownerid];           
    }         
  If (Level=='AA'){
       
              AggregateResult[] groupedResults = qrystringf; 

                                      
    queryResults = new List<GrossHolder>();
    for (AggregateResult ar : groupedResults)  {
        GrossHolder myObject = new GrossHolder();
            myObject.TotalGross = (Integer) ar.get('ce');
            myObject.compid = String.valueOf(ar.get('Ownerid'));
            AAid = myObject.compid;
            
            getWithdrawn(AAid);
            getWithdrawnT(AAid);
            getCustomers(AAid);
/*****************************INSERT COMP**********************************************************************/

            Compensation__c cmp = new Compensation__c();
            cmp.Name__c = myobject.compid;
            cmp.Comp_Plan__c ='AA';
            cmp.Total_Gross__c = myObject.TotalGross;
            IF(Monthrun=='THIS_MONTH'){            
                cmp.Date_ran__c = date.TODAY();
            }
            else{
                cmp.Date_ran__c = Lastmonthfirst;
            }
                
               if(AAWid!=AAid){
                    cmp.Total_Withdrawn__c = 0;
                }
                else{
                    cmp.Total_Withdrawn__c = Withdrawn;
                    if(cmp.Total_Withdrawn__c == null){
                        cmp.Total_Withdrawn__c = 0;
                    }                    
                }
                 
                if(AATid!=AAid){
                    cmp.Total_Transfers__c = 0;
                }
                else{
                    cmp.Total_Transfers__c = Transferred;
                    if(cmp.Total_Transfers__c == null){
                        cmp.Total_Transfers__c = 0;
                    }
               } 
               
                if(AACSid!=AAid){
                    cmp.Total_Customers__c = 0;
                }
                else{
                    cmp.Total_Customers__c = Customers;
                    if(cmp.Total_Customers__c == null){
                        cmp.Total_Customers__c = 0;
                    }
               }                                
                insert cmp;
        queryResults.add(myObject); 
        }
        }
        return null;
    } 
}

 
etechcareersetechcareers

Hi:

   Thanks again for helping out, but where are you declaring that variable? I get the error:

Error: Compile Error: Variable does not exist: THIS_MONTH at line 40 column 54

 

Thanks

Z
Naidu PothiniNaidu Pothini
public class compensationreport{
    public string AAid{get;set;}
    public string AAWid{get;set;}
    public string AATid{get;set;}
    public string AACSid{get;set;}
    public string CompName{get;set;}
    public Integer Gross{get;set;}
    public Integer Withdrawn{get;set;}
    public Integer Transferred{get;set;}
    public Integer Customers{get;set;}
    public String Level { get { return Level ;} set { Level=value ; } } 
    public String Monthrun { get { return Monthrun ;} set { Monthrun=value ; } }    
    
    public List<GrossHolder> queryResults{ get; set; }  
    public class GrossHolder {       
        public String AAName {get; set;}
        public Integer TotalGross {get; set;}
        public String compid{get;set;}    
        public GrossHolder(){}
    }
    
/*******************************************TOTAL Withdrawn******************************************************************************************/

    public List<WithdrawnHolder> queryResultsW{ get; set; }  
    public class WithdrawnHolder {
        public Integer TotalW {get; set;}
        public String AANameW {get; set;}
        public String aax {get;set;}
        public WithdrawnHolder(){}
    }
 
    public String getWithdrawn(String id){
    List<SObject> qrystringW = new List<SObject>();
        if(Monthrun=='THIS_MONTH'){
            qrystringW = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c ='Withdrawn' 
                            AND Withdrawn_Date__c = THIS_MONTH
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid]; 
        }
        else {
            qrystringW = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c ='Withdrawn' 
                            AND Withdrawn_Date__c = LAST_MONTH
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid];

then why are you using it in the above statements?

 

Is withdrawn_Date__c, a date field or a text Field?
etechcareersetechcareers

It is a Date Field...

So here is the thing, I have a picklist value on my VF page Like this:

<apex:page controller="compensationreport">
<apex:pageBlock title="Testing of Compensation">
  <apex:form >
    <apex:outputText value="Select Level"></apex:outputText>
    <apex:selectList value="{!Level}" size="1" >
        <apex:selectOption itemValue="AA" itemLabel="AA" />
        <apex:selectOption itemValue="LR" itemLabel="LR" />
        <apex:selectOption itemValue="SA" itemLabel="SA" />
    </apex:selectList>
    <br/>
    <apex:outputText value="Select Month"></apex:outputText>
    <apex:selectList value="{!Monthrun}" size="1" >
        <apex:selectOption itemValue="THIS_MONTH" itemLabel="THIS_MONTH" />
        <apex:selectOption itemValue="LAST_MONTH" itemLabel="LAST_MONTH" />
    </apex:selectList>    
    <apex:commandButton value="Run Compensation Plan This Month" action="{!query}" ></apex:commandButton>    
  </apex:form>
</apex:pageBlock>

</apex:page>

 Monthrun in Apex code I had to place as string, I tried it as date but it did not work...

public String Monthrun { get { return Monthrun ;} set { Monthrun=value ; } }

 So if they select this month it would run all data this month ... that is why you see the if then statement there...

  if(Monthrun=='THIS_MONTH'){

The variable is a string 'THIS_MONTH' which Monthrun holds the value...

Once they run this compensation plan, it would along this run the trigger you helped me out with in the previous help...

But the trigger is running but it does not run with the getCustomers method....If I deactivate the trigger then this class works... If I make the trigger active then getcustomers do not work...

I tried to change the trigger previously to do it after insert,after update but I got an error stating

Apex trigger bonusAmount caused an unexpected exception, contact your administrator: bonusAmount: execution of AfterUpdate caused by: System.FinalException: Record is read-only: Trigger.bonusAmount: line 45, column 1

 

you see my dilema....
Naidu PothiniNaidu Pothini
qrystringW = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c ='Withdrawn' 
                            AND Withdrawn_Date__c = LAST_MONTH
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid];

 You might need to change the above line. you are comparing date field with string.
etechcareersetechcareers

Hi thank you for everything but do you know how i can capture aggregate results in my test class... this is what i got so far which is up to 47%...for the above Apex class within this discussion.

thanks

z

Happy holidays

@istest
private class compensationreport_TestMethod // Name of the test class, Can be any name
{         
    static testmethod void compensationreport_TestMethod()   // Name of the test method. Must be the same as the class name 
    {
    //Set up user 
Date Lastmonthfirst;
Date Lastmonthend;

Date selectedDate =  Date.Today(); //Give your date
Date firstDate = selectedDate.toStartOfMonth();
Date lastDate = firstDate.addDays(date.daysInMonth(selectedDate.year() , selectedDate.month())  - 1);

//******************************************************************************************************
Lastmonthfirst = selectedDate.toStartOfMonth().addMonths(-1);
Lastmonthend = Lastmonthfirst.addDays(date.daysInMonth(Lastmonthfirst.year() , Lastmonthfirst.month())  - 1);
  
    User u1 = [SELECT Id FROM User WHERE Alias='ZishanR'];
        //Use the PageReference Apex class to instantiate a page
         PageReference query = Page.comp_run;
          
         //In this case, the Visualforce page named 'success' is the starting point of this test method.
         Test.setCurrentPage(query);

Account a = new Account();
a.firstName = 'lisa';
a.Lastname = 'stevenlee';
a.PersonLeadSource = 'Other';
a.PersonEmail = 'choosepros123@iintest.com';
a.PersonBirthdate = date.newinstance(1974,10,11);
a.Birthday__c = date.newinstance(1974,10,11);
insert a;

Opportunity oW = new Opportunity();
oW.Accountid = a.id;
ow.Name = 'IINTestComp - HTCP Jan 2012';
ow.CloseDate = Lastmonthfirst;
ow.StageName = 'Closed Won';
ow.Comp__c = False;
ow.LeadSource = 'Other';
insert ow;

Opportunity oWT = new Opportunity();
oWT.Accountid = a.id;
owT.Name = 'IINTestWT - HTCP Jan 2012';
owT.CloseDate = Lastmonthfirst;
owT.Withdrawn_Date__c = Lastmonthfirst; 
owT.StageName = 'Closed Won';
owT.Withdrawn__c = True;
owT.LeadSource = 'Other';
owT.Withdrawal_Status__c = 'Transferred to DL';
owT.Refunded_Amount__c = 3100;
owT.Withdrawal_Fee__c = 100;
insert owT;
        

        compensationreport testcls = new compensationreport();   // Initialize variable for original class 
        
       // String returnPage = testcls.query().getURL();
        testcls.query();
        testcls.getWithdrawn(u1.id);                        // Call Function of Original class
        testcls.getWithdrawnT(u1.id); 
        testcls.getCustomers(u1.id);
          
    }    
}

 
Naidu PothiniNaidu Pothini

if possible can you post the class, it would be much easier to write a test class.
etechcareersetechcareers
public class compensationreport{
    public string AAid{get;set;}
    public string AAWid{get;set;}
    public string AATid{get;set;}
    public string AACSid{get;set;}
    public string CompName{get;set;}
    public Integer Gross{get;set;}
    public Integer Withdrawn{get;set;}
    public Integer Transferred{get;set;}
    public Integer Customers{get;set;}
    public String Level { get { return Level ;} set { Level=value ; } } 
    
/*******************************************TOTAL Withdrawn******************************************************************************************/

    public List<WithdrawnHolder> queryResultsW{ get; set; }  
    public class WithdrawnHolder {
        public Integer TotalW {get; set;}
        
        public String aax {get;set;}
        public WithdrawnHolder(){}
    }
 
    public String getWithdrawn(String id){


                            
    AggregateResult[] groupedResults = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c ='Withdrawn' 
                            AND Withdrawn_Date__c = LAST_MONTH
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid]; 
        
    queryResultsW = new List<WithdrawnHolder>();
    
    for (AggregateResult ar : groupedResults)  {
        WithdrawnHolder myObject = new WithdrawnHolder();
            myObject.aax = String.valueOf(ar.get('Ownerid'));
            AAWid =  myObject.aax;
            myObject.TotalW = (Integer) ar.get('ce');
        Withdrawn = myObject.TotalW ;    
        queryResultsW.add(myObject); 
    }
        return null;    
}  
/*******************************************TOTAL TRANSFERRED******************************************************************************************/
    public List<WithdrawnHolderT> queryResultsWT{ get; set; }  

    public class WithdrawnHolderT {

        public Integer TotalWT {get; set;}
         
         public String aaxT {get;set;}
    public WithdrawnHolderT(){
    }
    }
 
    public String getWithdrawnT(String id){

             
    AggregateResult[] groupedResults = [SELECT Ownerid,Count(Id) ce 
                            FROM Opportunity Where 
                            Withdrawn__c = True 
                            AND StageName = 'Closed Won' 
                            AND Withdrawal_Status__c = 'Transferred to DL' 
                            AND Withdrawn_Date__c = LAST_MONTH  
                            AND Ownerid=:id 
                            AND (NOT Name Like '%COMP%')
                            GROUP BY Ownerid];         
        
    queryResultsWT = new List<WithdrawnHolderT>();
    
    for (AggregateResult ar : groupedResults)  {
        WithdrawnHolderT myObject = new WithdrawnHolderT();
        myObject.aaxT = String.valueOf(ar.get('Ownerid'));
        AATid =  myObject.aaxT;
        myObject.TotalWT = (Integer) ar.get('ce');
        Transferred = myObject.TotalWT ;
    
    queryResultsWT.add(myObject); 
      
    }
    return null;    
    }  
/*******************************************TOTAL CUSTOMERS******************************************************************************************/    
    public List<customers> queryResultscs{ get; set; }  
    public class customers {

        public Integer TotalCS {get; set;}
         
         public String aaxCS {get;set;}
    public customers(){
    }
    }
 
    public String getCustomers(String id){


    
    AggregateResult[] groupedResults = [SELECT Ownerid,Count(Id) ce 
                                        FROM Account 
                                        Where Owner.Level__c = 'AA' 
                                        AND Ownerid=:id AND CreatedDate = LAST_MONTH
                                        GROUP BY Ownerid]; 
                    
    queryResultscs = new List<customers>();
    
    for (AggregateResult ar : groupedResults)  {
        customers myObject = new customers();
        myObject.aaxCS = String.valueOf(ar.get('Ownerid'));
        AACSid =  myObject.aaxCS;
        myObject.TotalCS = (Integer) ar.get('ce');
        Customers = myObject.TotalCS ;
    
    queryResultscs.add(myObject); 
      
    }
    return null;    
    }                               
/*******************************************Actual******************************************************************************************/     
    public List<GrossHolder> queryResults{ get; set; }  
    public class GrossHolder {       
        
        public Integer TotalGross {get; set;}
        public String compid{get;set;}    
        public GrossHolder(){}
    }

public PageReference query() { 
Date Lastmonthfirst;
Date Lastmonthend;

Date selectedDate =  Date.Today(); //Give your date
Date firstDate = selectedDate.toStartOfMonth();
Date lastDate = firstDate.addDays(date.daysInMonth(selectedDate.year() , selectedDate.month())  - 1);
system.debug(firstDate  + ' **** ' +lastDate );
//******************************************************************************************************
Lastmonthfirst = selectedDate.toStartOfMonth().addMonths(-1);
Lastmonthend = Lastmonthfirst.addDays(date.daysInMonth(Lastmonthfirst.year() , Lastmonthfirst.month())  - 1);
system.debug(Lastmonthfirst  + ' **** ' +Lastmonthend );

     
    List<Compensation__c>  delmarker = new List<Compensation__c>(); 

    delmarker=[select id from Compensation__c where Date_ran__c =LAST_MONTH];

    
    for(Compensation__c sm:delmarker){
        delete sm;
    }  
       
       
        
  If (Level=='AA'){
  
       
             AggregateResult[] groupedResults = [SELECT Ownerid, Count(Id) ce
                                                 FROM Opportunity Where Comp__c=False  
                                                 AND CloseDate=LAST_MONTH 
                                                 AND StageName = 'Closed Won'
                                                 GROUP BY Ownerid];  

                                      
    queryResults = new List<GrossHolder>();
    for (AggregateResult ar : groupedResults)  {
        GrossHolder myObject = new GrossHolder();
            myObject.TotalGross = (Integer) ar.get('ce');
            myObject.compid = String.valueOf(ar.get('Ownerid'));
            AAid = myObject.compid;
            

            getWithdrawn(AAid);
            getWithdrawnT(AAid);
            getCustomers(AAid);
/*****************************INSERT COMP**********************************************************************/

            Compensation__c cmp = new Compensation__c();
            cmp.Name__c = myobject.compid;
            cmp.Comp_Plan__c ='AA';
            cmp.Total_Gross__c = myObject.TotalGross;
            cmp.Date_ran__c = Lastmonthfirst;
                
               if(AAWid!=AAid){
                    cmp.Total_Withdrawn__c = 0;
                }
                else{
                    cmp.Total_Withdrawn__c = Withdrawn;
                    if(cmp.Total_Withdrawn__c == null){
                        cmp.Total_Withdrawn__c = 0;
                    }                    
                }
                 
                if(AATid!=AAid){
                    cmp.Total_Transfers__c = 0;
                }
                else{
                    cmp.Total_Transfers__c = Transferred;
                    if(cmp.Total_Transfers__c == null){
                        cmp.Total_Transfers__c = 0;
                    }
               } 
               
                if(AACSid!=AAid){
                    cmp.Total_Customers__c = 0;
                }
                else{
                    cmp.Total_Customers__c = Customers;
                    if(cmp.Total_Customers__c == null){
                        cmp.Total_Customers__c = 0;
                    }
               }                                
                insert cmp;
        queryResults.add(myObject); 
        }
        }
        return null;
    } 
}

 

@istest
private class compensationreport_TestMethod // Name of the test class, Can be any name
{         
    static testmethod void compensationreport_TestMethod()   // Name of the test method. Must be the same as the class name 
    {
    //Set up user 
Date Lastmonthfirst;
Date Lastmonthend;

Date selectedDate =  Date.Today(); //Give your date
Date firstDate = selectedDate.toStartOfMonth();
Date lastDate = firstDate.addDays(date.daysInMonth(selectedDate.year() , selectedDate.month())  - 1);

//******************************************************************************************************
Lastmonthfirst = selectedDate.toStartOfMonth().addMonths(-1);
Lastmonthend = Lastmonthfirst.addDays(date.daysInMonth(Lastmonthfirst.year() , Lastmonthfirst.month())  - 1);
  
User u1 = [SELECT Id FROM User WHERE Alias='ZishanR'];

Compensation__c cc = new Compensation__c
    (
        Total_Gross__c = 120, 
        Total_Withdrawn__c = 10,
        Total_Transfers__c = 0,
        Comp_Plan__c = 'AA',
        Total_Customers__c = 127,
        Name__c = u1.Id,
        Date_ran__c = Lastmonthfirst
    );
    insert cc;
Account a = new Account();
a.firstName = 'lisa';
a.Lastname = 'stevenlee';
a.PersonLeadSource = 'Other';
a.PersonEmail = 'choosepros123@iintest.com';
a.PersonBirthdate = date.newinstance(1974,10,11);
a.Birthday__c = date.newinstance(1974,10,11);
insert a;

Opportunity o = new Opportunity();
o.Accountid = a.id;
o.Name = 'IINTestO - HTCP Jan 2012';
o.CloseDate = Lastmonthfirst;
o.StageName = 'Closed Won';
o.Comp__c = False;
o.LeadSource = 'Other';
insert o;

Opportunity oWT = new Opportunity();
oWT.Accountid = a.id;
owT.Name = 'IINTestWT - HTCP Jan 2012';
owT.CloseDate = Lastmonthfirst;
owT.Withdrawn_Date__c = Lastmonthfirst; 
owT.StageName = 'Closed Won';
owT.Withdrawn__c = True;
owT.LeadSource = 'Other';
owT.Withdrawal_Status__c = 'Transferred to DL';
owT.Refunded_Amount__c = 3100;
owT.Withdrawal_Fee__c = 100;
insert owT;

Opportunity oW2 = new Opportunity();
oW2.Accountid = a.id;
ow2.Name = 'IINTestW2 - HTCP Jan 2012';
ow2.CloseDate = Lastmonthfirst;
ow2.Withdrawn_Date__c = Lastmonthfirst; 
ow2.StageName = 'Closed Won';
ow2.Withdrawn__c = True;
ow2.LeadSource = 'Other';
ow2.Withdrawal_Status__c = 'Withdrawn';
ow2.Refunded_Amount__c = 3100;
ow2.Withdrawal_Fee__c = 100;
ow2.Withdrawal_Reason_Further_Detail__c = 'this is my test';
insert ow2;
        //Use the PageReference Apex class to instantiate a page
        PageReference query = Page.comp_run;
        
        //In this case, the Visualforce page named 'success' is the starting point of this test method.
        Test.setCurrentPage(query);
        compensationreport testcls = new compensationreport();   // Initialize variable for original class 
        
        testcls.query();
        testcls.getWithdrawn(u1.id);                        // Call Function of Original class
        testcls.getWithdrawnT(u1.id); 
        testcls.getCustomers(u1.id);
         Compensation__c cmp = new Compensation__c();
            cmp.Name__c = u1.id;
            cmp.Comp_Plan__c ='AA';
            cmp.Total_Gross__c = 1;
            cmp.Date_ran__c = Lastmonthfirst;
            cmp.Total_Withdrawn__c = 1;
            cmp.Total_Transfers__c = 1;
            cmp.Total_Customers__c = 1;
        insert cmp;
  
    }    
}

 I got 58% but query is not being pick up correctly?

thanks

Z
etechcareersetechcareers

I got it to 71%...

but nothing in query() method is being called... Would you know why?

@isTest(SeeAllData=true)
private class compensationreport_TestMethod // Name of the test class, Can be any name
{         
    static testmethod void compensationreport_TestMethod()   // Name of the test method. Must be the same as the class name 
    {
    //Set up user 
Date Lastmonthfirst;
Date Lastmonthend;

Date selectedDate =  Date.Today(); //Give your date
Date firstDate = selectedDate.toStartOfMonth();
Date lastDate = firstDate.addDays(date.daysInMonth(selectedDate.year() , selectedDate.month())  - 1);

//******************************************************************************************************
Lastmonthfirst = selectedDate.toStartOfMonth().addMonths(-1);
Lastmonthend = Lastmonthfirst.addDays(date.daysInMonth(Lastmonthfirst.year() , Lastmonthfirst.month())  - 1);
  
User u1 = [SELECT Id FROM User WHERE Alias='ZishanR'];
System.runAs(u1) {
Compensation__c cc = new Compensation__c
    (
        Total_Gross__c = 120, 
        Total_Withdrawn__c = 10,
        Total_Transfers__c = 0,
        Comp_Plan__c = 'AA',
        Total_Customers__c = 127,
        Name__c = u1.Id,
        Date_ran__c = Lastmonthfirst
    );
    insert cc;
Account a = new Account();
a.firstName = 'lisa';
a.Lastname = 'stevenlee';
a.PersonLeadSource = 'Other';
a.PersonEmail = 'choosepros123@iintest.com';
a.PersonBirthdate = date.newinstance(1974,10,11);
a.Birthday__c = date.newinstance(1974,10,11);
a.CreatedDate = Lastmonthfirst;
a.ownerid = u1.id;
a.BillingStreet = '128 new st';
a.BillingCity = 'New York';
a.BillingState = 'NY';
a.BillingCountry = 'USA';
a.BillingPostalCode = '10061';
insert a;
System.Debug('yes' + a.CreatedDate + a.id + a.Ownerid + a.Owner.Level__c);
Opportunity o = new Opportunity();
o.Accountid = a.id;
o.Name = 'IINTestO - HTCP Jan 2012';
o.CloseDate = Lastmonthfirst;
o.StageName = 'Closed Won';
o.Comp__c = False;
o.LeadSource = 'Other';
insert o;

Opportunity oWT = new Opportunity();
oWT.Accountid = a.id;
owT.Name = 'IINTestWT - HTCP Jan 2012';
owT.CloseDate = Lastmonthfirst;
owT.Withdrawn_Date__c = Lastmonthfirst; 
owT.StageName = 'Closed Won';
owT.Withdrawn__c = True;
owT.LeadSource = 'Other';
owT.Withdrawal_Status__c = 'Transferred to DL';
owT.Refunded_Amount__c = 3100;
owT.Withdrawal_Fee__c = 100;
insert owT;

Opportunity oW2 = new Opportunity();
oW2.Accountid = a.id;
ow2.Name = 'IINTestW2 - HTCP Jan 2012';
ow2.CloseDate = Lastmonthfirst;
ow2.Withdrawn_Date__c = Lastmonthfirst; 
ow2.StageName = 'Closed Won';
ow2.Withdrawn__c = True;
ow2.LeadSource = 'Other';
ow2.Withdrawal_Status__c = 'Withdrawn';
ow2.Refunded_Amount__c = 3100;
ow2.Withdrawal_Fee__c = 100;
ow2.Withdrawal_Reason_Further_Detail__c = 'this is my test';
insert ow2;
        //Use the PageReference Apex class to instantiate a page
        PageReference query = Page.comp_run;
        Test.setCurrentPage(query);       
        compensationreport testcls = new compensationreport();   // Initialize variable for original class 
        Test.startTest();
        testcls.query();
        testcls.getWithdrawn(u1.id);                        // Call Function of Original class
        testcls.getWithdrawnT(u1.id); 
        testcls.getCustomers(u1.id);
        
         Compensation__c cmp = new Compensation__c();
            cmp.Name__c = u1.id;
            cmp.Comp_Plan__c ='AA';
            cmp.Total_Gross__c = 1;
            cmp.Date_ran__c = Lastmonthfirst;
            cmp.Total_Withdrawn__c = 1;
            cmp.Total_Transfers__c = 1;
            cmp.Total_Customers__c = 1;
        insert cmp;
      Test.stopTest();
    }    
    }
}

 Thanks

Happy Holidays