I tried to make report summary that will be sent to email, this is my code now. But i got System.LimitException: Too many Email Invocations: 11, what sould i do to avoid this error? should i made an batch? but how to make it? need
Global class UT_MDM_Email_MonthlyReport implements Schedulable {
Global void execute(SchedulableContext SC) {
prepare_data();
}
Global static void prepare_data() {
List<String> lstOwnerPlant = new List<String>();
List<String> lstBranchsite = new List<String>();
list<aggregateresult> lstPlantSite = [SELECT Owner.UserRole.Name plant
FROM Contact
WHERE Email_Support__c != NULL AND Is_Drop__c = FALSE AND SOURCE__C IN ('SPS')
GROUP BY Owner.UserRole.Name];
list<aggregateresult> lsBranchSite = [SELECT Branch_Site_Support__r.ParentTerritory.Name site
FROM Contact
WHERE Email_Support__c != NULL AND Is_Drop__c = FALSE AND SOURCE__C IN ('FSL') AND Branch_Site_Support__r.ParentTerritory.Name != NULL
GROUP BY Branch_Site_Support__r.ParentTerritory.Name];
for(AggregateResult ctc : lstPlantSite)
{
lstOwnerPlant.add(ctc.get('plant').toString());
}
for(AggregateResult ctcSite : lsBranchSite)
{
lstBranchsite.add(ctcSite.get('site').toString());
}
if(lstOwnerPlant.size() > 0){
for(String req : lstOwnerPlant)
{
sendMailForYa(req, 'SPS');
}
}
if(lstBranchsite.size() > 0){
for(String req : lstBranchsite)
{
sendMailForYa(req,'FSL');
}
}
}
public static void sendMailForYa(String plantSite, string sourceVal)
{
System.debug('sendMailForYa');
map<string,List<aggregateresult>> mapPlant = new map<string,List<aggregateresult>>();
map<string,List<aggregateresult>> mapSite = new map<string,List<aggregateresult>>();
List<Messaging.SingleEmailMessage> masterListMails = new List<Messaging.SingleEmailMessage>();
List<Contact> LContact = new List<Contact>();
Set<String> setPlantSite = new Set<String>();
Set<String> setSource = new Set<String>();
List<String> setArea = new List<String>();
if(sourceVal == 'FSL'){
for(AggregateResult fslEmailCont : [SELECT Branch_Site_Support__r.ParentTerritory.Name parentTerritory, Branch_Site_Support__r.Area_Name__c area
FROM Contact
WHERE Email_Support__c != NULL AND Is_Drop__c = FALSE AND Branch_Site_Support__r.ParentTerritory.Name = :plantSite AND Branch_Site_Support__r.Area_Name__c != NULL
GROUP BY Branch_Site_Support__r.ParentTerritory.Name,Branch_Site_Support__r.Area_Name__c ])
{
setPlantSite.add((string)fslEmailCont.get('parentTerritory'));
setSource.add('FSL');
setArea.add((string)fslEmailCont.get('area'));
}
}
if(sourceVal == 'SPS'){
for(AggregateResult spsEmailCont : [SELECT Owner.UserRole.Name OwnerRole,Owner.Area_Name__c area
FROM Contact
WHERE Email_Support__c != NULL AND Is_Drop__c = FALSE AND Owner.UserRole.Name = :plantSite AND Owner.Area_Name__c != NULL
GROUP BY Owner.UserRole.Name,Owner.Area_Name__c])
{
setPlantSite.add((string)spsEmailCont.get('OwnerRole'));
setSource.add('SPS');
setArea.add((string)spsEmailCont.get('area'));
}
}
System.debug('CEK SET YASH : Plant' + setPlantSite + ' setSource : '+ setSource + ' setArea : '+ setArea);
if(setPlantSite != null && setSource != null && setArea != null ){
String strPlantSite = String.join(new List<String>(setPlantSite), ',');
String strSource = String.join(new List<String>(setSource), ',');
String strArea = String.join(new List<String>(setArea), ',');
System.debug('strPlantSite + strPlantSite :' + strPlantSite + ' '+ strPlantSite);
String header_letter = '<p>Dengan Hormat, </p>';
String body_letter = '<p>Berikut ini kami sampaikan summary status contact yang ada di cabang/site: </p><br/>';
String table_letter = getTableEmailBody(strPlantSite); //tambah parameter branchsite/owner plant
String footer_letter = '<br/><br/>Terimakasih<br/> - Salesforce System';
String letter = header_letter + body_letter + table_letter + footer_letter;
// Define the email
Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
// Set list of peoples who should recieve an email
List<String> Str_ToAddress = new List<String>();
List<String> CC_ToAddress = new List<String>();
List<String> ltolist;
//Set CC Email Reminder
String ccLabel = System.Label.UT_Contact_MDM_CC; //CC Label Rizki, Pak Wijang dan Pak Eko
List<String> lcclist = ccLabel.split(',');
//label for CSSD
String UT_MDM_CC_Site_1A_1B_SPS = System.Label.UT_MDM_CC_Site_1A_1B_SPS;
String UT_MDM_CC_Site_2A_2B_SPS = System.Label.UT_MDM_CC_Site_2A_2B_SPS;
String UT_MDM_CC_Site_3A_3B_SPS = System.Label.UT_MDM_CC_Site_3A_3B_SPS;
String UT_MDM_CC_Branch_Area1_SPS = System.Label.UT_MDM_CC_Branch_Area1_SPS;
String UT_MDM_CC_Branch_Area2_SPS = System.Label.UT_MDM_CC_Branch_Area2_SPS;
String UT_MDM_CC_Branch_Area3_SPS = System.Label.UT_MDM_CC_Branch_Area3_SPS;
String UT_MDM_CC_Site_1A_1B_FSL = System.Label.UT_MDM_CC_Site_1A_1B_FSL;
String UT_MDM_CC_Site_2A_2B_FSL = System.Label.UT_MDM_CC_Site_2A_2B_FSL;
String UT_MDM_CC_Site_3A_3B_FSL = System.Label.UT_MDM_CC_Site_3A_3B_FSL;
String UT_MDM_CC_Branch_Area1_FSL = System.Label.UT_MDM_CC_Branch_Area1_FSL;
String UT_MDM_CC_Branch_Area2_FSL = System.Label.UT_MDM_CC_Branch_Area2_FSL;
String UT_MDM_CC_Branch_Area3_FSL = System.Label.UT_MDM_CC_Branch_Area3_FSL;
//coba cari cara gmn query biar bisa dapatin BM, PDH dan SDH Based on setPlantSite
String nameOfPlantSite;
if(strPlantSite.contains('MDN')){
nameOfPlantSite = 'Medan';
}else if(strPlantSite.contains('PKB')){
nameOfPlantSite = 'Pekanbaru';
}
System.debug('nameOfPlantSite :' +nameOfPlantSite + ' strPlantSite:'+ strPlantSite);
string nameOfBMPlantSite = nameOfPlantSite + ' BM';
List<User> get_CC_BM = [SELECT Id, Name,Email, ProfileId, UserType, Plant__c, Area_Name__c, UserRole.Name FROM User WHERE UserRole.Name =:nameOfBMPlantSite AND isActive = TRUE];
System.debug('get_CC_BM : '+ get_CC_BM + ' nameOfBMPlantSite :' + nameOfBMPlantSite);
for(User getBM : get_CC_BM){
lcclist.add(getBM.Email);
}
//get PDH
string nameOfPDH = 'Parts Department Head - ' + strPlantSite + ' SPS';
List<User> get_to_PDH = [SELECT Id, Name,Email, ProfileId, UserType, Plant__c, Area_Name__c, UserRole.Name FROM User WHERE UserRole.Name =:nameOfPDH AND isActive = TRUE];
for(User getPDH : get_to_PDH){
Str_ToAddress.add(getPDH.Email);
}
System.debug('get_to_PDH : '+ Str_ToAddress + ' nameOfPDH :' + nameOfPDH);
String nameOfSDH = nameOfPlantSite + ' SDH';
List<User> get_to_SDH = [SELECT Id, Name,Email, ProfileId, UserType, Plant__c, Area_Name__c, UserRole.Name FROM User WHERE UserRole.Name =:nameOfSDH AND isActive = TRUE];
for(User getPDH : get_to_SDH){
Str_ToAddress.add(getPDH.Email);
} System.debug('get_to_SDH : '+ Str_ToAddress + ' nameOfSDH :'+ nameOfSDH);
CC_ToAddress = lcclist;
email.setToAddresses(Str_ToAddress);
email.setSenderDisplayName(' - Salesforce System');
email.setCcAddresses((CC_ToAddress != NULL ? CC_ToAddress : NULL));
// Sets the paramaters of the email
email.setSubject( System.Label.UT_MDM_Subject + ' '+ (strPlantSite != null? strPlantSite : ''));
email.setHtmlBody( letter );
masterListMails.add(email);
}
System.debug('Master List Mails : ' + masterListMails.size());
//Send List Mail
if(masterListMails.size() > 0)
{
List<Contact> lscon = new List<Contact>();
Messaging.SendEmailResult[] results = Messaging.sendEmail(masterListMails);
if(results[0].success){
System.debug('The email was sent successfully');
}else {
System.debug('The email failed to send: ' + results[0].errors[0].message);
}
}
}
public static string getTableEmailBody(String Fromadd)
{
System.debug('Fromadd :' + Fromadd);
String htmlbody = '';
//open table..
htmlBody = '<table border="1" style="border-collapse: collapse"><caption>SUMMARY STATUS CONTACT</caption><tr><th>PLANT</th><th>TOTAL</th><th>VALIDATE</th><th>NEED FOLLOW UP</th><th>STATUS</th>';
List<ContactWrapper> wraps = new List<ContactWrapper>();
//FSL - Based on Branchsite
list<aggregateresult> fslCont = [SELECT Count(Id) totalNFU, Branch_Site_Support__r.ParentTerritory.Name branchsite, NEED_FOLLOW_UP__c FROM Contact
WHERE Email_Support__c != NULL AND Branch_Site_Support__r.ParentTerritory.Name =: Fromadd AND is_drop__c = FALSE AND Source__c = 'FSL'
GROUP BY Branch_Site_Support__r.ParentTerritory.Name, NEED_FOLLOW_UP__c ORDER BY Branch_Site_Support__r.ParentTerritory.Name, NEED_FOLLOW_UP__c];
System.debug('fslCont :' + fslCont + ' size: ' +fslCont.size());
//SPS - Based on Owner Plant
list<aggregateresult> spsCont = [SELECT Count(Id) totalNFU, Owner.UserRole.Name plant, NEED_FOLLOW_UP__c FROM Contact
WHERE Email_Support__c != NULL AND Owner.UserRole.Name =: Fromadd AND is_drop__c = FALSE AND Source__c = 'SPS'
GROUP BY Owner.UserRole.Name, NEED_FOLLOW_UP__c ORDER BY Owner.UserRole.Name, NEED_FOLLOW_UP__c];
map<string,List<aggregateresult>> mapFSLCont = new map<string,List<aggregateresult>>();
map<string,List<aggregateresult>> mapSPSCont = new map<string,List<aggregateresult>>();
System.debug('spsCont :' + spsCont + ' size: ' +spsCont.size());
for(AggregateResult agrFSL: fslCont){
if(mapFSLCont.containsKey((string)agrFSL.get('branchsite'))){
mapFSLCont.get((string)agrFSL.get('branchsite')).add(agrFSL);
}
else{
mapFSLCont.put((string)agrFSL.get('branchsite'),new List<aggregateresult>{agrFSL});
}
}
for(AggregateResult agrSPS: spsCont){
if(mapSPSCont.containsKey((string)agrSPS.get('plant'))){
mapSPSCont.get((string)agrSPS.get('plant')).add(agrSPS);
}
else{
mapSPSCont.put((string)agrSPS.get('plant'),new List<aggregateresult>{agrSPS});
}
}
System.debug('Check mapSPSCont List : '+ mapSPSCont);
System.debug('Check mapFSLCont List : '+ mapFSLCont);
for (String key : mapFSLCont.keySet()) {
Integer ac; Integer nonac;
List<AggregateResult> lstAgr = mapFSLCont.get(key);
for(AggregateResult mapLstFSL: lstAgr ){
if(mapLstFSL.get('Need_Follow_Up__c') == false){
System.debug('FALSE' +mapLstFSL.get('totalNFU') );
ac = (Integer) mapLstFSL.get('totalNFU');
}
else{
nonac = (Integer) mapLstFSL.get('totalNFU');
}
}
wraps.add(new ContactWrapper(key,ac,nonac));
System.debug('### lines.get(key) : ' + lstAgr + 'Wrap' + wraps);
}
for (String key : mapSPSCont.keySet()) {
Integer ac; Integer nonac;
List<AggregateResult> lstAgr = mapSPSCont.get(key);
for(AggregateResult mapLstSPS: lstAgr ){
if(mapLstSPS.get('Need_Follow_Up__c') == false){
ac = (Integer) mapLstSPS.get('totalNFU');
}
else{
nonac = (Integer) mapLstSPS.get('totalNFU');
}
}
wraps.add(new ContactWrapper(key,ac,nonac));
System.debug('### lines.get(key) 2 : ' + lstAgr + 'Wrap' + wraps);
}
for(ContactWrapper wrapCont : wraps){
String myBranch = wrapCont.tempBranchSite; if(wrapCont.tempBranchSite == null){myBranch = '-';}
Integer myNFU = wrapCont.tempTotalNFU; if(wrapCont.tempTotalNFU == null){myNFU = 0;}
Integer myValidate = wrapCont.tempTotalVal; if(wrapCont.tempTotalVal == null){myValidate = 0;}
Integer myTotal = myValidate + myNFU;
Decimal myValPers = (Decimal) myValidate/myTotal*100; Double myValPersentage = myValPers.setScale(2);
Decimal myNFUPers = (Decimal) myNFU/myTotal*100; Double myNFUPersentage = myNFUPers.setScale(2);
String myStatus;
if(myValPersentage < 25){
myStatus = 'Urgent';
}else if(myValPersentage >= 25 && myValPersentage <= 75){
myStatus = 'Attention';
}else{
myStatus = 'Normal';
}
htmlBody += '<tr><td rowspan="2">'+myBranch+'</td><td rowspan="2">' + myTotal + '</td><td>' + myValidate +'</td><td>' +myNFU
+'</td><td rowspan="2" >'+ myStatus + '</td></tr>' +
'<tr><td>' + myValPersentage +'%' + '</td><td>' +myNFUPersentage +'%'+ '</td></tr>'; }
//close table...
htmlBody += '</table>';
System.debug('Table ->' + htmlBody);
return htmlBody;
}
public class ContactWrapper{
public String tempBranchSite { get; set; }
public Integer tempTotalNFU { get; set; }
public Integer tempTotalVal { get; set; }
public ContactWrapper(String branchsite, Integer totalNFU, Integer totalVal) {
this.tempBranchSite = branchsite;
this.tempTotalNFU = totalNFU;
this.tempTotalVal = totalVal;
System.debug('This is Contact Wrapp: ' + tempBranchSite + ' ' + tempTotalNFU + ' ' + tempTotalVal);
}
} }
