global class LoanOfficerBatch implements Database.Batchable<sObject> {
public String query = 'SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, ConvertedOpportunityId, Name, Phone,'
+ 'Status, Est_Re_Pull_Date__c, Realtor_Name__c '
+ ' FROM Lead';
public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];
global Database.QueryLocator start(Database.BatchableContext bc) {
query += ' WHERE CreatedDate = LAST_MONTH AND Loan_Officer_1a__c != null';
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext BC, list<Lead> allLeads) {
List<String> convertedOppId = new List<String>();
for(Lead l: allLeads){
convertedOppId.add(l.ConvertedOpportunityId);
}
Map<Id, Opportunity> opptyMap = new Map<Id, Opportunity>();
for(Opportunity o : [SELECT Id,Name,Contact__r.Name,Contact__r.Phone,Starting_Credit_Score__c,Enrolled_On__c,Est_Re_Pull_Date__c,StageName FROM Opportunity WHERE Id IN: convertedOppId]){
opptyMap.put(o.Id, o);
}
Map<Id,List<Lead>> leadMap = new Map<Id,List<Lead>>();
List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMEssage>();
if(allLeads != null && allLeads.size() > 0){
for(Lead l: allLeads){
if(!leadMap.containsKey(l.Loan_Officer_1a__c)){
leadMap.put(l.Loan_Officer_1a__c, new List<lead>());
}
leadMap.get(l.Loan_Officer_1a__c).add(l);
}
}
if(leadMap.keySet().size() > 0){
Map<Id,Contact> officers = new Map<Id,Contact>([SELECT Id,Email,Name FROM Contact WHERE Id IN: leadMap.keySet()]);
for(Id i: leadMap.keySet()){
Contact con = officers.get(i);
System.debug(con);
if(String.isnOtBlank(con.Email)){
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
mail.setToAddresses(new String[]{con.EMail});
mail.setSubject(templateId.Subject);
String html = templateId.HtmlValue;
html = html.replace('||OfficerName||',con.Name);
String leadsTable = '<table cellpadding="3" cellspacing="3" width="100%" align="center" border="1" style="border-collapse:collapse;">'+
'<tr style="font-weight:bold;"><td>Name</td><td>Phone</td><td>Status</td><td>Est. Re Pull Date</td><td>Realtor Name</td></tr>';
for(Lead l: leadMap.get(i))
if(l.ConvertedOpportunityId==null)
{ {
leadsTable += '<tr><td>'+l.Name+'</td>'+
'<td>'+l.Phone+'</td><td>'+l.Status+'</td>'+
'<td>'+l.Est_Re_Pull_Date__c+'</td><td>'+l.Realtor_Name__c+'</td></tr>';
}
}
leadsTable += '</table>';
String opptyTable = '<table cellpadding="3" cellspacing="3" width="100%" align="center" border="1" style="border-collapse:collapse;">'+
'<tr style="font-weight:bold;"><td>Name</td><td>Phone</td><td>Starting Credit Score</td><td>Enrolled On</td><td>Estimated Pull Date</td><td>StageName</td></tr>';
for(Id idKey: opptyMap.keySet()){
Opportunity o = opptyMap.get(idKey);
opptyTable += '<tr><td>'+o.Contact__r.Name+'</td><td>'+o.Contact__r.Phone+'</td><td>'+o.Starting_Credit_Score__c+'</td><td>'+o.Enrolled_On__c+'</td>'
+'<td>'+o.Est_Re_Pull_Date__c+'</td><td>'+o.StageName+'</td></tr>';
}
opptyTable += '</table>';
html = html.replace('||Leads||',leadsTable);
html = html.replace('||Opportunity||',opptyTable);
html = html.replace('null',' ');
mail.setHTMLBody(html);
mails.add(mail);
}
}
}
if(mails.size() > 0){
Messaging.sendEmail(mails);
}
}
global void finish(Database.BatchableContext BC) {
}
}
please help
Hi Mohammad,First create scheduler class to your batch class. Then only you can run your batch class monthly.Scheduler Class:global class LoanOfficerBatchSheduler implements Schedulable { global void execute(SchedulableContext sc) { LoanOfficerBatch b = new LoanOfficerBatch(); database.executebatch(b); }}Then there is two way to run this batch class via the Scheduler:1. Salesforce Standard Out of Box Scheduler : (Cons : you can only scehdule up to next five years) Setup > Apex Classes > Shedule Apex Button > Then you can lookup the above Sheduler Class. Please refer the below screenshot for further:
2. Using CRON and Developer Console:
Developer Console > Debug > Open Execute Anonymous Window > paste the below code > ExecuteLoanOfficerBatchSheduler m = new LoanOfficerBatchSheduler();String sch = '0 0 0 1 * ? *';String jobID = system.schedule('Merge Job', sch, m);https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_scheduler.htmhttp://amitsalesforce.blogspot.com/2017/07/how-to-write-test-class-for-scheduler.htmlhttps://webkul.com/blog/cron-expression-for-scheduling-jobs-in-salesforce/Thanks,Maharajan.C
