This is my first wrapper class and I think it is a hard one. I tried to put this all into one query and it failed because of a junction object. I then put it into 2 queries using components and it failed again because of SOQL query limits. My only hope is that a wrapper class will allow me to pull all the data into one list so I can present it in a VF page. Below are my two SOQL queries with a for loop in a single class. I know there is some redundancy but I am still learning. Any help to either use this to write a 2nd class as a wrapper or to rewrite this to be a wrapper class would be most helpful. Thanks in advance.
Current Apex Class:
public class MonthlySeizureController1{
public task taskRec { get; set; }
public task taskRec1 { get; set; }
public List<Case__c> theCases { get; set; } // this was the ca reference
public Map<Id, Business__c> businessMap { get; set; } // this was the bus1 reference
public MonthlySeizureController1(ApexPages.StandardController sc){
taskRec = new task (ActivityDate__c = Date.today().addMonths(-1));
taskRec1 = new task (ActivityDate__c = Date.today());
// get the supply chain chases in the last month be careful of 10k cases in this result
this.theCases = querySupplyChainCasesBetweenDates(taskRec.ActivityDate__c, taskRec1.ActivityDate__c);
// collect the ids of the businesses in these cases
Set<Id> businessIds = new Set<Id>();
for (Case__c c : theCases) {
for (BusinessCasesAssociation__c bca : c.BusinessCasesAssociations__r) {
businessIds.add(bca.Business__c);
}
}
// get the businesses related to these cases and assign them to the variable in the controller
this.businessMap = queryBusinesses(businessIDs);
}
public MonthlySeizureController1() {
// why do we have this constructor too? consider moving the logic above in here and then calling 'this();' in the first line of the standard controller constructor
}
private Map<Id, Business__c> queryBusinesses(Set<Id> businessIDs) {
Map<Id, Business__c> result = new Map<Id, Business__c>([SELECT Id
, Name
, Business_Name__c
, Business_Type__c
, (SELECT Street__c
, Business_City__c
, State_Province__c
, Country__c
, Zip_Postal_Code__c
FROM BPR_Business_Addresses__r)
, (SELECT Number_Type__c
, Number__c
FROM Business_Phone_Numbers__r)
, (SELECT Email_Bus__c
FROM Email_Businesses__r)
FROM Business__c
WHERE (Id = :businessIDs)]);
return result;
}
private List<Case__c> querySupplyChainCasesBetweenDates(Date lowerDate, Date upperDate) {
List<Case__c> cases = [SELECT Id
, Name
, CreatedDate
, LastModifiedDate
, Owner.FirstName
, Owner.LastName
, Notification_Date__c
, Incident_City__c
, Incident_Country__r.Name
, Total_Product__c
, Comments_Postal_Express__c
, (SELECT RecordType.Name
, Container__c
, Ship_Date__c
, Date_of_Retention__c
, Carrier__c
, Port_of_Loading__c
, Port_of_Discharge__c
, Container_Type__c
, Place_of_Retention__c
, Vessel__c
, Master_BOL__c
, House_BOL__c
FROM Incident_Locations__r)
, (SELECT Brand__c
, Product_Detail__c
, Product_Types__c
, Total_Product_Quantity__c
FROM Products__r)
, (SELECT Business__c
, Case__c
, Business__r.Business_Name__c
, Business__r.Business_Type__c
FROM BusinessCasesAssociations__r)
, (SELECT Subject_Name__r.Subject_Name__c
, Subject_Name__r.Subject_Type__c
FROM Subject_for_Cases__r)
FROM Case__c
WHERE Supply_Chain_Type__c = 'SEA'
AND Notification_Date__c >= :lowerDate
AND Notification_Date__c <= :upperDate
ORDER BY Notification_Date__c ASC];
return cases;
}
public PageReference next(){
PageReference nextpage = new PageReference('/apex/securityreport1ppv');
return nextpage;
}
public PageReference next1(){
PageReference nextpage = new PageReference('/apex/securityreport1sap');
return nextpage;
}
}
VF PAGE:
<apex:page standardController="Case__c" extensions="MonthlySeizureController1">
<style type="text/css">
table.myTable{
border-collapse: collapse;
}
table.myTable td{
border: 1px solid black;
padding: 5px;
}
table.myTable th{
border: 2px solid black;
padding: 5px;
}
a.linkAsBtn,
a.linkAsBtn:hover {
text-decoration: none;
padding: 3px 6px;
}
</style>
<apex:form >
<apex:panelGrid columns="8" id="dates1">
<b>Start Date: </b><apex:inputfield value="{!taskRec.ActivityDate__c}" required="true"/>
<b>End Date: </b><apex:inputfield value="{!taskRec1.ActivityDate__c}" required="true"/>
<apex:commandLink value="Submit" rerender="test" styleClass="btn linkAsBtn"/>
<apex:commandLink value="Printable View" action="{!next}" id="cmdNext" target="_blank" styleClass="btn linkAsBtn"/>
<apex:commandLink value="Save as PDF" action="{!next1}" id="cmdNext1" target="_blank" styleClass="btn linkAsBtn"/>
</apex:panelGrid>
<apex:outputPanel id="test">
<table class="myTable">
<tr>
<th>BPR Case</th>
<th>BP MGR</th>
<th>SHIPPING DATE/SEIZURE DATE</th>
<th>NOTIFY DATE</th>
<th>SEIZURE LOCATION</th>
<th>CONTAINER NO.</th>
<th>CARRIER</th>
<th>TRADING CO.</th>
<th>SHIPPER CO.</th>
<th>SHIPPER NAME</th>
<th>SHIPPER ADDRESS</th>
<th>SHIPPER PHONE</th>
<th>SHIPPER EMAIL</th>
<th>PORT OF LOADING</th>
<th>MANUFACTURER/PROVIDER</th>
<th>MANUFACTURER/PROVIDER ADDRESS</th>
<th>CONSIGNEE BUSINESS</th>
<th>CONSIGNEE NAME</th>
<th>CONSIGNEE ADDRESS</th>
<th>CONSIGNEE PHONE</th>
<th>CONSIGNEE EMAIL</th>
<th>PORT OF DISCHARGE</th>
<th>NOTIFY PARTY</th>
<th>NOTIFY PARTY ADDRESS</th>
<th>NOTIFY PARTY PHONE</th>
<th>PRODUCT</th>
<th>AMOUNT (pairs, items)</th>
<th>CONTAINER STATS</th>
<th>ADDITIONAL INFORMATION</th>
</tr>
<apex:repeat value="{!theCases}" var="c" id="case">
<tr>
<td>
<a href="https://mydomain.my.salesforce.com/{!c.Id}">{!c.name}</a>
</td>
<td>
<apex:outputText value="{!c.Owner.FirstName}"></apex:outputText> <apex:outputText value="{!c.Owner.LastName}"></apex:outputText>
</td>
<td>
<apex:repeat value="{!c.Incident_Locations__r}" var="loc">
<apex:outputText value="{0,date,MM'-'dd'-'yyyy}"><apex:param value="{!loc.Ship_Date__c}"/></apex:outputText>/
<apex:outputText value="{0,date,MM'-'dd'-'yyyy}"><apex:param value="{!loc.Date_of_Retention__c}"/></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:outputText value="{0,date,MM'-'dd'-'yyyy}"><apex:param value="{!c.Notification_Date__c}"/></apex:outputText>
</td>
<td>
<apex:outputText value="{!c.Incident_City__c}"></apex:outputText>/
<apex:outputText value="{!c.Incident_Country__r.Name}"></apex:outputText>
</td>
<td>
<apex:repeat value="{!c.Incident_Locations__r}" var="loc">
<apex:outputText value="{!loc.Container__c}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.Incident_Locations__r}" var="loc">
<apex:outputText value="{!loc.Carrier__c}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
<apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Trading Company'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
<apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Exporter'}"></apex:outputText>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.Subject_for_Cases__r}" var="sub">
<apex:outputText value="{!sub.Subject_Name__r.Subject_Name__c}" rendered="{!sub.Subject_Name__r.Subject_Type__c = 'Exporter'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="businessCaseAssociation">
<apex:outputField value="{!businessMap[businessCaseAssociation.Business__c].BPR_Business_Addresses__r.Street__c}"/><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="businessCaseAssociation">
<c:business_shipper_contact_info2 BusinessID="{!businessMap[businessCaseAssociation.Business__c]}" /><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="businessCaseAssociation">
<c:business_shipper_contact_info3 BusinessID="{!businessMap[businessCaseAssociation.Business__c]}" /><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.Incident_Locations__r}" var="loc">
<apex:outputText value="{!loc.Port_of_Loading__c}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
<apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Factory/Manufacturer'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
MANUFACTURER/PROVIDER ADDRESS
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
<apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Importer'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.Subject_for_Cases__r}" var="sub">
<apex:outputText value="{!sub.Subject_Name__r.Subject_Name__c}" rendered="{!sub.Subject_Name__r.Subject_Type__c = 'Importer'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
CONSIGNEE ADDRESS
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
CONSIGNEE PHONE
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
CONSIGNEE EMAIL
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.Incident_Locations__r}" var="loc">
<apex:outputText value="{!loc.Port_of_Discharge__c}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
<apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Notify Party'}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
NOTIFY PARTY ADDRESS
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
NOTIFY PARTY PHONE
</apex:repeat>
</td>
<td>
<apex:repeat value="{!c.Products__r}" var="pro">
<apex:outputText value="{!pro.Brand__c}"></apex:outputText> <apex:outputText value="{!pro.Product_Types__c}"></apex:outputText> (<apex:outputText value="{0,number,###,###}"><apex:param value="{!pro.Total_Product_Quantity__c}"/></apex:outputText>)<br></br>
</apex:repeat>
</td>
<td>
<apex:outputText value="{!c.Total_Product__c}"></apex:outputText>
</td>
<td>
<apex:repeat value="{!c.Incident_Locations__r}" var="loc">
<apex:outputText value="{!loc.Container_Type__c}"></apex:outputText><br></br>
</apex:repeat>
</td>
<td>
<apex:outputText value="{!c.Comments_Postal_Express__c}"></apex:outputText>
</td>
</tr>
</apex:repeat>
</table>
</apex:outputPanel>
</apex:form>
</apex:page>
VF Component:
<apex:component controller="MonthlySeizureController1" access="global">
<apex:attribute id="recordID" assignTo="{!recordID}" name="BusinessID" type="Id" description="The Business's SF Id"/>
<apex:repeat value="{!bus1}" var="addr1">
<apex:outputText value="{!addr1.Street__c}"></apex:outputText> <apex:outputText value="{!addr1.Business_City__c}"></apex:outputText> <apex:outputText value="{!addr1.State_Province__c}"></apex:outputText> <apex:outputText value="{!addr1.Country__c}"></apex:outputText> <apex:outputText value="{!addr1.Zip_Postal_Code__c}"></apex:outputText>
</apex:repeat>
</apex:component>
System.LimitException: Too many SOQL queries: 101It points to the bus1 as causing it to go over. If I remove that it works fine. The main objects I am working with here are the Case__c object and the Business__c object. There is a junction object connecting them which is called BusinessCasesAssociations__c. I have a for loop in the bus1 call above that links them using the Business__c.BusinessCasesAssociations__c field to the Business__c.Id. I hope this helps. If you need more info, please let me know. Thanks.caandbus1once.