Display the related aggregated value on a VF page

Question

We have a custom VF page that we use in our Org for time entry, and on that page employees select a project, iteration, and enter their time for that week. Note: some employees might be working on different projects and different iterations in the same week.
I'm supposed to display the total hours ,for each iteration, that has been worked by that employee , so I'm using AggregateResult SUM().

My code for getting the sum is :

list<AggregateResult> iterationResults = [select  SUM(duration__c) sumDuration 
                                          from time_entry__c
                                          where User__c = :UserID 
                                          group by Iteration__c];

I threw a system debug to make sure it works:

        for (AggregateResult ar: iterationResults) {
            decimal dec1 = (Decimal)ar.get('sumDuration');
            system.debug('\n Target is : \n' + dec1);
        }

I'm getting the correct SUM, which is good, but that decimal variable holds the right value but without differentiating between iterations. So here are the two things that i'm stuck on:

1. How can I assign the SUM of each iteration to a variable where I can pass that value to my VF page?
2. I understand that I cannot bind VF components to AggregateResult, so I'll need a wrapper class. How is my wrapper class going to look like?

Additional detail that might be helpful is the query I use to get the projects/iterations data from database:

[select  id, Memo__c, project__r.name, iteration__r.name, Milestones_PM_Task__r.name, date__c,  day_of_week__c, duration__c 
 from time_entry__c
 where User__c = :UserID 
 and date__c >= :firstDate and date__c < :lastDate
 order by project__r.name, iteration__r.name, date__c, createddate ]

I really appreciate your effort and help

Answer 1

First fine-tune your query so you get the results you're happy with. If you want the split per iteration - probably something like this:

SELECT Iteration__r.Name iName, SUM(duration__c) sumDuration
FROM Time_Entry__c
WHERE User__c = :UserInfo.getUserId()
GROUP BY ROLLUP(Iteration__r.Name)

1. Eclipse IDE might behave funny when it comes to aggregate queries. Consider using the Workbench, RealForceExplorer or another tool if you'll see columns that appear to be blank.
2. ROLLUP word is useful if you want to get a grand total row at the bottom. If you don't need it - go with simple GROUP BY Iteration__r.Name
3. Add more fields as you see fit. Roughly speaking every field you'll add to main SELECT you'll also have to add to GROUP BY clause.

Once you're happy create a wrapper class, I'd go with

public class Wrapper{
    public Decimal duration {get; private set;}
    String iterationName {get; private set;}

    // Constructor
    public Wrapper(AggregateResult ar){
        duration = Decimal.valueOf(ar.get('sumDuration'));
        iterationName = String.valueOf(ar.get('iName'));
    }
}

And then simply loop through the results like you had system.debugs but keep adding Wrappers to a list:

List<Wrapper> wrappers = new List<Wrapper>(); // should be a publicly available variable so either declare it at class level or provide a getter ;)
for (AggregateResult ar: iterationResults) {
    wrappers.add(new Wrapper(ar));
}

---

Edit (re: comment about recalculating the list of wrappers on save)

Roughly speaking you have 2 options:

1. Stick the code that queries & recalculates the wrappers (preferably a call to helper function) at the end of the method that's being called when you hit save button. This gets bit more interesting if you were using the {!save} from standard controller, let me know.

2. Useful especially if it's a throwaway thing - you use this List<Wrapper> only to display it on VF and nowhere else - delete the class variable with the list and create a getter function instead. That way it'll be taken fresh every time VF "gets" the values:

   public List<Wrapper> getWrappers(){
       List<Wrapper> temp = new List<Wrapper>();
       // query here...
       return temp;
   }