I have a custom controller and visualforce page. The page loads fine with the SummaryList. When the user enters parameters, it should filter the list according to the runSearch method. I'm getting an error with runQuery2: illegal assignment from list to list. The database.query does not get an error if I create a separate list of type aggregateResult, and I use that list instead of SummaryList; but that is not going to work, because I can't query an aggregateResult type. How do I make this work?
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;}
private String soql2 {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'));
}
System.debug('Account List' + AcctList);
runQuery();
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 void runQuery2() {
try {
SummaryList = Database.query(soql2);
} 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';
soql2 = 'SELECT AcctName,StateProvince,Dollars,Total FROM Summary__c WHERE AcctName != null';
List<String> li = new List<String>();
if (!AccountName.equals(''))
li.add(' AcctName LIKE \''+String.escapeSingleQuotes(AccountName)+'%\'');
if (!State.equals(''))
li.add(' StateProvince LIKE \''+String.escapeSingleQuotes(State)+'%\'');
String c = String.join( li, ' or ');
if (c.length() > 0)
c = ' and (' + c + ')';
system.debug('condition: ' + c);
soql2 += c;
// run the query again
runQuery2();
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>
<td valign="top">
<apex:pageBlock mode="edit" id="results">
<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="{!r.AcctName}" 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="{!r.StateProvince}" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputText value="{!r.StateProvince}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Number of Home Inspections" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="{!r.Total}" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputText value="{!r.Total}"/>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink value="Home Inspection $" action="{!toggleSort}" rerender="results,debug">
<apex:param name="sortField" value="{!r.Dollars}" assignTo="{!sortField}"/>
</apex:commandLink>
</apex:facet>
<apex:outputText value="{!r.Dollars}"/>
</apex:column>
</apex:pageblockTable>
</apex:pageBlock>
</td>
</tr>
</table>
<apex:pageBlock title="Debug - SOQL" id="debug">
<apex:outputText value="{!debugSoql}" />
</apex:pageBlock>
</apex:pageBlock>
</apex:form>
</apex:page>
