Filter for Aggregated List Results in Custom Controller

Question

I have a custom controller with a Visualforce page that displays a list of accounts with aggregated results for each account. I want to create a filter for state and inspection date that would filter the records that display on this list. How would I go about doing this? Any steps on how to get started would be appreciated.

Controller:

public with sharing class inspectionsDashboard {

public List<AggregateResult> Result {get;set;}    
public List<Summary> SummaryList {get;set;}
public List<Account> AcctList {get;set;}
public Map<Id, Account> IdAccount {get;set;}
List<Id> Ids {get;set;}
private String soql {get;set;}

public String sortDir {
    get  { if (sortDir == null) {  sortDir = 'asc'; } return sortDir;  }
    set;
}

// the current field to sort by. defaults to last name
public String sortField {
    get  { if (sortField == null) {sortField = 'Name'; } return sortField;  }
    set;
}

// format the soql for display on the visualforce page
public String debugSoql {
    get { return soql + ' order by ' + sortField + ' ' + sortDir + ' limit 20'; }
    set;
}

public void toggleSort() {
    // simply toggle the direction
    sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
    // run the query again
    runQuery();
}

public inspectionsDashboard() {
    SummaryList = new List<Summary>();
    Result = new List<AggregateResult>();
    Ids = new List<Id>();
    AcctList = new List<Account>();
    IdAccount = new Map<Id, Account>();
    soql = 'SELECT Name,BillingState FROM Account WHERE Id IN : Ids';

    Result = [SELECT Count(Id) Total, Sum(Home_Inspection_dollars__c) Dollars, Franchise__c 
              FROM Inspection__c 
              WHERE Month_Since_Launch__c > 0 AND Status__c = 'Completed' 
              GROUP BY Franchise__c];               

    for(AggregateResult a : Result) {   
        Ids.add((Id)a.get('Franchise__c'));
    }

    runQuery();

    System.debug('Account List' + AcctList);

    for(Account a : AcctList) {
        IdAccount.put(a.Id, a);            
    }

    System.debug('Ids and Accounts are ' + IdAccount);

    for(AggregateResult a : Result) {
        Account TempAcct = new Account();            
        TempAcct = IdAccount.get((Id)(a.get('Franchise__c')));
        system.debug('Account Name is ' + TempAcct.Name);
        SummaryList.add(new Summary(a, TempAcct.Name, TempAcct.BillingState));                
    }       
}

public void runQuery() {

    try {
      AcctList = Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 20');
    } catch (Exception e) {
      ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Ooops!'));
    }
}

public class Summary {
    public Integer Total {get;set;}
    public Decimal Dollars {get;set;}
    public String AcctName {get;set;}
    public String StateProvince {get;set;}

    public Summary(AggregateResult a, String AccountName, String State) {
        Total =  (Integer)a.get('Total');
        Dollars = (Decimal)a.get('Dollars');
        AcctName = AccountName;
        StateProvince = State;
    }
}

public PageReference runSearch() {

    String AccountName = Apexpages.currentPage().getParameters().get('AccountName');
    String State = Apexpages.currentPage().getParameters().get('State');
    soql = 'SELECT Name,BillingState FROM Account WHERE Id IN : Ids';

    List<String> li = new List<String>();
    if (!AccountName.equals(''))
      li.add(' Name LIKE \''+String.escapeSingleQuotes(AccountName)+'%\'');
    if (!State.equals(''))
      li.add(' BillingState LIKE \''+String.escapeSingleQuotes(State)+'%\'');  
    String c = String.join( li, ' or ');
    if (c.length() > 0)
        c = ' and (' + c + ')';
    system.debug('condition: ' + c);
    soql += c;
    // run the query again
    runQuery();
    return null;
}

}

Visualforce page:

<apex:page controller="inspectionsDashboard" sidebar="false">
<apex:form>
<apex:pageMessages id="errors" />

    <apex:pageBlock title="Find Me A Franchisee!" mode="edit">

      <table width="100%" border="0">
          <tr>  
            <td width="200" valign="top">

        <apex:pageBlock title="Parameters" mode="edit" id="criteria">

          <script type="text/javascript">
          function doSearch() {
            searchServer(
              document.getElementById("AccountName").value,
              document.getElementById("State").value
              );
          }
          </script> 

          <apex:actionFunction name="searchServer" action="{!runSearch}" rerender="results,debug,errors">
              <apex:param name="AccountName" value="" />
              <apex:param name="State" value="" />
          </apex:actionFunction>

          <table cellpadding="2" cellspacing="2">
          <tr>
            <td style="font-weight:bold;">Account Name<br/>
            <input type="text" id="AccountName" onkeyup="doSearch();"/>
            </td>
          </tr>
          <tr>
            <td style="font-weight:bold;">State/Province<br/>
            <input type="text" id="State" onkeyup="doSearch();"/>
            </td>
          </tr>
          </table>

          </apex:pageBlock>
          </td>

    <apex:pageBlock >
        <apex:pageblockTable value="{!SummaryList}" var="r"> 
            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="Account Name" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="AccountName" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputText value="{!r.AcctName}"/>
            </apex:column>
            <apex:column >
                <apex:facet name="header">
                    <apex:commandLink value="State/Province" action="{!toggleSort}" rerender="results,debug">
                        <apex:param name="sortField" value="State" assignTo="{!sortField}"/>
                    </apex:commandLink>
                </apex:facet>
                <apex:outputText value="{!r.StateProvince}"/>
            </apex:column>
            <apex:column headerValue="Number of Home Inspections" value="{!r.Total}" id="Total"/>
            <apex:column headerValue="Home Inspection $" value="{!r.Dollars}" id="Dollars"/>
        </apex:pageblockTable>

        <apex:pageBlock title="Debug - SOQL" id="debug">
            <apex:outputText value="{!debugSoql}" />           
        </apex:pageBlock>

    </apex:pageBlock>
          </tr>   
       </table>
    </apex:pageBlock>
</apex:form>

Answer 1

@Sebastian Please go through this link. It will help you in achieving your requirement.

https://blog.jeffdouglas.com/2010/07/13/building-a-dynamic-search-page-in-visualforce/

Some of the interesting features of the Visualforce page and Apex controller include:

Instead of the typical overhead of using getters/setters in the controller to maintain the variables for the search form, He used Dave Carroll's blog as an example to pass the variables via Javascript.

Since he is using Javascript to pass my parameters, he can get fancy and make the search form more dynamic by running the search as the user types or selects an option in the picklist. No submit button is required.

Users can click on the column headers to toggle the direction of the search results by using a CommandLink component and passing the column name. Build the picklist using Dynamic Apex's Describe functionality so that it's maintenance free.

Perform the search against multiple fields including multi-select picklists using dynamic SOQL and preventing SOQL injection attacks.