1

I converted existing batch class that was processing single record into schedule/batch class that can process multiple records. But I got stuck in bulkifying the dynamic SOQL inside the for loop.

Single Record Batch Apex that is triggered from custom button:

global class Batch_EmailBlast implements Database.Batchable<sObject>, Database.Stateful {

  String query;
  public string emailBlastId;
  public Email_Blast__c emailBlast;
  public List<Case> listOfEmailsToBeSent;
  public List<Case> listOfDuplicateCases;
  public List<Account> listOfAccountsToBeUpdated;
  public Map<Id,String> mapOfOrgCaseIdToError;
  public Map<Id,String> mapOfDupCaseIdToError;
  public boolean isError;
  global Batch_EmailBlast() {

  }

  global Database.QueryLocator start(Database.BatchableContext BC) {
    listOfEmailsToBeSent = new List<Case>();
    listOfDuplicateCases = new List<Case>();    
    mapOfOrgCaseIdToError = new Map<Id,String>();
    mapOfDupCaseIdToError = new Map<Id,String>();
    listOfAccountsToBeUpdated = new List<Account>();
    emailBlast = [Select Id, Inquiry_Type__c, Product_type__c, Solution__c, Reason_For_Calling__c,
        Problem_Category__c, Duplicate_Case_Problem_Category__c,Duplicate_Case_Product_Type__c,
        Duplicate_Case_Reason_For_Calling__c, Duplicate_Case_Status__c,Web_Product_Business_Group__c,
        WebProductPLTypeName__c,Age_Hours__c,Language__c, Case_Status__c,Completed_Date__c,
        status__c, No_Of_Emails_Sent__c, No_Of_Duplicate_Cases__c from Email_Blast__c where 
        Id =: emailBlastId];
    String businessGroup = emailBlast.Web_Product_Business_Group__c;
    String webProductPLTypeName = emailBlast.WebProductPLTypeName__c;
    Integer age = Integer.valueOf(emailBlast.Age_Hours__c);
    List<String> languageList = new List<String>();
    if(emailBlast.Language__c != null && emailBlast.Language__c.contains(';')){
      List<String> allLangs = emailBlast.Language__c.split(';');
      for(String iterString: allLangs){
        languageList.add(iterString);
      } 
    }
    else{
      languageList.add(emailBlast.Language__c);
    }

    String status = emailBlast.Case_Status__c;
    Id caseId = '5005A00000v2BB4';
    query = 'Select Id, Inquiry_Type__c, Product_type__c, Solution__c, Reason_For_Calling__c, Email_Blast__c, '
        +'Problem_Category__c,Status,Send_Mass_Email__c,Web_Product_Support_Product__c, Validation_Flag__c ,'
        +'Account.PersonEmail,AccountId, Account.Mass_Email_Received__c, Account.Last_mass_email_Date__c from Case where '
        +'Web_Product_Support_Product__r.Business_Group__c =: businessGroup AND '
        +'WebProductPLTypeName__c =: webProductPLTypeName AND Case_Age_in_Hours__c >: age AND '
        +'Language__c in: languageList AND Status=: status AND Account.Mass_Email_Received__c = false '
        +'order by Id, Account.PersonEmail, Case_Age_in_Hours__c DESC ';

    return Database.getQueryLocator(query);
  }

     global void execute(Database.BatchableContext BC, List<Case> scope) {
    set<String> setOfDuplicateCreiteria = new set<String>();
    set<ID> setOfAccIds = new set<Id>();
    system.debug('scopeeee '+scope.size());
    //Get Duplicate cases and cases for which email to be sent
    for(Case iterCase: scope){
      String criteria = iterCase.Account.PersonEmail +'-'+iterCase.Web_Product_Support_Product__c;
      if(!setOfDuplicateCreiteria.contains(criteria)){
        setOfDuplicateCreiteria.add(iterCase.Account.PersonEmail +'-'+iterCase.Web_Product_Support_Product__c);
        iterCase.Send_Mass_Email__c = true;
        iterCase.Inquiry_Type__c = emailBlast.Inquiry_Type__c;
        iterCase.Product_type__c = emailBlast.Product_type__c;
        iterCase.Problem_Category__c = emailBlast.Problem_Category__c;
        iterCase.Reason_For_Calling__c = emailBlast.Reason_For_Calling__c;
        iterCase.Solution__c = emailBlast.Solution__c;
        iterCase.Email_Blast__c = emailBlast.Id;
        If(Test.isRunningTest() && ProcessEmailBlast.isError)
          iterCase.Id = null;
        listOfEmailsToBeSent.add(iterCase);

      }
      else{
        iterCase.Status = emailBlast.Duplicate_Case_Status__c;
        iterCase.Product_type__c = emailBlast.Duplicate_Case_Product_Type__c;
        iterCase.Problem_Category__c = emailBlast.Duplicate_Case_Problem_Category__c;
        iterCase.Reason_For_Calling__c = emailBlast.Duplicate_Case_Reason_For_Calling__c;
        iterCase.Email_Blast__c = emailBlast.Id; 
        If(Test.isRunningTest() && ProcessEmailBlast.isError)
          iterCase.Id = null;
        listOfDuplicateCases.add(iterCase);
      }
    }

    system.debug('listOfEmailsToBeSentttttsizee '+listOfEmailsToBeSent.size());
    system.debug('listOfDuplicateCasessssssizee '+listOfDuplicateCases.size());
    system.debug('listOfEmailsToBeSenttttt '+listOfEmailsToBeSent);
    system.debug('listOfDuplicateCasesssss '+listOfDuplicateCases);
    if(!listOfEmailsToBeSent.isEmpty()){
      //update listOfEmailsToBeSent;
      List<Database.SaveResult> updateResults = Database.update(listOfEmailsToBeSent, false);
      for(Integer i=0;i<updateResults.size();i++){
        if (updateResults.get(i).isSuccess()){
          setOfAccIds.add(listOfEmailsToBeSent.get(i).AccountId);

        }else if (!updateResults.get(i).isSuccess()){
          // DML operation failed
          Database.Error error = updateResults.get(i).getErrors().get(0);
          String failedDML = error.getMessage();
          mapOfOrgCaseIdToError.put(listOfEmailsToBeSent.get(i).Id,failedDML);//failed record from the list
          system.debug('Failed ID'+listOfEmailsToBeSent.get(i).Id);
        }

      }
    }

    if(!listOfDuplicateCases.isEmpty()){
      //update listOfDuplicateCases;
      List<Database.SaveResult> updateResults = Database.update(listOfDuplicateCases, false);
      for(Integer i=0;i<updateResults.size();i++){
        if (updateResults.get(i).isSuccess()){
          updateResults.get(i).getId();

        }else if (!updateResults.get(i).isSuccess()){
          // DML operation failed
          Database.Error error = updateResults.get(i).getErrors().get(0);
          String failedDML = error.getMessage();
          mapOfDupCaseIdToError.put(listOfDuplicateCases.get(i).Id,failedDML);//failed record from the list
          system.debug('Failed ID'+listOfDuplicateCases.get(i).Id);
        }

      }
    }

    for(Account iterAcc : [select Id, Mass_Email_Received__c, Last_mass_email_Date__c from Account where
                Id in: setOfAccIds ]){
      iterAcc.Mass_Email_Received__c = true;
      iterAcc.Last_mass_email_Date__c = Date.Today();
      listOfAccountsToBeUpdated.add(iterAcc);

    }
    system.debug('listOfAccountsToBeUpdateddd '+listOfAccountsToBeUpdated);
    try{
      if(!listOfAccountsToBeUpdated.isEmpty()){
        update listOfAccountsToBeUpdated;
      }
    }
    catch(Exception e){
      SystemLoggerUtil.logError(e);
    }

  }

  global void finish(Database.BatchableContext BC) {
    if(mapOfDupCaseIdToError.keySet().size() > 0){
      String OrgCaseError = 'Case Id,Error Message\n';
      for(Id iterCaseId : mapOfDupCaseIdToError.keySet()){
        OrgCaseError += iterCaseId+','+mapOfDupCaseIdToError.get(iterCaseId)+'\n';
      }
      Attachment dupCaseAttchment = new Attachment();
      dupCaseAttchment.Name = 'Duplicate Case Errors'+ dateTime.Now()+'.csv';
      dupCaseAttchment.contentType = 'text/csv';
      dupCaseAttchment.body = Blob.valueOf(OrgCaseError);
      dupCaseAttchment.ParentId = emailBlast.Id;
      insert dupCaseAttchment;
    }

    if(mapOfOrgCaseIdToError.keySet().size() > 0){
      String OrgCaseError = 'Case Id,Error Message\n';
      for(Id iterCaseId : mapOfOrgCaseIdToError.keySet()){
        OrgCaseError += iterCaseId+','+mapOfOrgCaseIdToError.get(iterCaseId)+'\n';
      }
      Attachment orgCaseAttchment = new Attachment();
      orgCaseAttchment.Name = 'Case Errors'+ dateTime.Now()+'.csv';
      orgCaseAttchment.contentType = 'text/csv';
      orgCaseAttchment.body = Blob.valueOf(OrgCaseError);
      orgCaseAttchment.ParentId = emailBlast.Id;
      insert orgCaseAttchment;
    }

    emailBlast.Completed_Date__c = DateTime.Now();
    if(!mapOfDupCaseIdToError.values().isEmpty() || !mapOfOrgCaseIdToError.values().isEmpty()){
      emailBlast.status__c = 'Partially Completed';
    }
    else 
      emailBlast.status__c = 'Completed';
    emailBlast.No_Of_Emails_Sent__c = listOfEmailsToBeSent.size();
    emailBlast.No_Of_Duplicate_Cases__c = listOfDuplicateCases.size();
    update emailBlast;
  }

}

converetd Multiple Record Batch Apex that is scheduled:

global class Batch_EmailBlast implements Schedulable,Database.Batchable<sObject>, Database.Stateful {

  String query;
  public string emailBlastId;
  public Email_Blast__c emailBlast;
  public List<Case> listOfEmailsToBeSent;
  public List<Case> listOfDuplicateCases;
  public List<Account> listOfAccountsToBeUpdated;
  public Map<Id,String> mapOfOrgCaseIdToError;
  public Map<Id,String> mapOfDupCaseIdToError;
  public boolean isError;
  global Batch_EmailBlast() {

  }

  global Database.QueryLocator start(Database.BatchableContext BC) {
String caseStatus = System.Label.Email_Blast_Process_Status;
String caseTested = System.Label.Email_Blast_TestingStatus;
String caseCount = System.Label.Email_Blast_Case_Count_Limit;
        String emailBlastQuery = 'SELECT Id, Inquiry_Type__c, Product_type__c, Solution__c, Reason_For_Calling__c, '+
                                'Problem_Category__c, Duplicate_Case_Problem_Category__c,Duplicate_Case_Product_Type__c, '+
                                'Duplicate_Case_Reason_For_Calling__c, Duplicate_Case_Status__c,Web_Product_Business_Group__c, '+
                                'WebProductPLTypeName__c,Age_Hours__c,Language__c, Case_Status__c,Completed_Date__c, '+
                                'status__c, No_Of_Emails_Sent__c, No_Of_Duplicate_Cases__c '+
                                'FROM  Email_Blast__c '+
                                'WHERE status__c =: caseStatus 
                                AND Criteria_Tested__c =:caseTested
                                AND No_Of_Records_Satisfying_Criteria__c > caseCount';

                              System.debug('hello'+emailBlastQuery);

    Id caseId = '5005A00000v2BB4';


    return Database.getQueryLocator(emailBlastQuery);
  }

    global void execute(Database.BatchableContext BC, List<Email_Blast__c> scope) 
    {
        set<String> setOfDuplicateCreiteria = new set<String>();
        set<ID> setOfAccIds = new set<Id>();
        //Get Duplicate cases and cases for which email to be sent

        for(Email_Blast__c emailBlast : scope)
        {
            String businessGroup = emailBlast.Web_Product_Business_Group__c;
            String webProductPLTypeName = emailBlast.WebProductPLTypeName__c;
            Integer age = Integer.valueOf(emailBlast.Age_Hours__c);
            List<String> languageList = new List<String>();
            if(emailBlast.Language__c != null && emailBlast.Language__c.contains(';'))
            {
                List<String> allLangs = emailBlast.Language__c.split(';');
                for(String iterString: allLangs)
                {
                    languageList.add(iterString);
                } 
            }
            else
            {
            languageList.add(emailBlast.Language__c);
            }
            String status = emailBlast.Case_Status__c;

            String caseQuery = 'Select Id, Inquiry_Type__c, Product_type__c, Solution__c, Reason_For_Calling__c, Email_Blast__c, '
                                +'Problem_Category__c,Status,Send_Mass_Email__c,Web_Product_Support_Product__c, Validation_Flag__c ,'
                                +'Account.PersonEmail,AccountId, Account.Mass_Email_Received__c, Account.Last_mass_email_Date__c from Case where '
                                +'Web_Product_Support_Product__r.Business_Group__c =: businessGroup AND '
                                +'WebProductPLTypeName__c =: webProductPLTypeName AND Case_Age_in_Hours__c >: age AND '
                                +'Language__c in: languageList AND Status=: status AND Account.Mass_Email_Received__c = false '
                                +'order by Id, Account.PersonEmail, Case_Age_in_Hours__c DESC ';

            for(Case iterCase : Database.query(caseQuery))
            {
                listOfEmailsToBeSent = new List<Case>();
                listOfDuplicateCases = new List<Case>();    
                mapOfOrgCaseIdToError = new Map<Id,String>();
                mapOfDupCaseIdToError = new Map<Id,String>();
                listOfAccountsToBeUpdated = new List<Account>();

                String criteria = iterCase.Account.PersonEmail +'-'+iterCase.Web_Product_Support_Product__c;
                if(!setOfDuplicateCreiteria.contains(criteria)){
                    setOfDuplicateCreiteria.add(iterCase.Account.PersonEmail +'-'+iterCase.Web_Product_Support_Product__c);
                    iterCase.Send_Mass_Email__c = true;
                    iterCase.Inquiry_Type__c = emailBlast.Inquiry_Type__c;
                    iterCase.Product_type__c = emailBlast.Product_type__c;
                    iterCase.Problem_Category__c = emailBlast.Problem_Category__c;
                    iterCase.Reason_For_Calling__c = emailBlast.Reason_For_Calling__c;
                    iterCase.Solution__c = emailBlast.Solution__c;
                    iterCase.Email_Blast__c = emailBlast.Id;
                    If(Test.isRunningTest() && ProcessEmailBlast.isError)
                    iterCase.Id = null;
                    listOfEmailsToBeSent.add(iterCase);

                }
                else{
                    iterCase.Status = emailBlast.Duplicate_Case_Status__c;
                    iterCase.Product_type__c = emailBlast.Duplicate_Case_Product_Type__c;
                    iterCase.Problem_Category__c = emailBlast.Duplicate_Case_Problem_Category__c;
                    iterCase.Reason_For_Calling__c = emailBlast.Duplicate_Case_Reason_For_Calling__c;
                    iterCase.Email_Blast__c = emailBlast.Id; 
                    If(Test.isRunningTest() && ProcessEmailBlast.isError)
                    iterCase.Id = null;
                    listOfDuplicateCases.add(iterCase);
                }

        if(!listOfEmailsToBeSent.isEmpty()){
        //update listOfEmailsToBeSent;
        List<Database.SaveResult> updateResults = Database.update(listOfEmailsToBeSent, false);
            for(Integer i=0;i<updateResults.size();i++){
                    if (updateResults.get(i).isSuccess()){
                    setOfAccIds.add(listOfEmailsToBeSent.get(i).AccountId);

                    }else if (!updateResults.get(i).isSuccess()){
                    // DML operation failed
                    Database.Error error = updateResults.get(i).getErrors().get(0);
                    String failedDML = error.getMessage();
                    mapOfOrgCaseIdToError.put(listOfEmailsToBeSent.get(i).Id,failedDML);//failed record from the list
                    system.debug('Failed ID'+listOfEmailsToBeSent.get(i).Id);
                    }

            }
        }

        if(!listOfDuplicateCases.isEmpty()){
        //update listOfDuplicateCases;
        List<Database.SaveResult> updateResults = Database.update(listOfDuplicateCases, false);
        for(Integer i=0;i<updateResults.size();i++)
            {
                if (updateResults.get(i).isSuccess()){
                updateResults.get(i).getId();

                }else if (!updateResults.get(i).isSuccess()){
                // DML operation failed
                Database.Error error = updateResults.get(i).getErrors().get(0);
                String failedDML = error.getMessage();
                mapOfDupCaseIdToError.put(listOfDuplicateCases.get(i).Id,failedDML);//failed record from the list
                system.debug('Failed ID'+listOfDuplicateCases.get(i).Id);
                }

            }
        }

        for(Account iterAcc : [select Id, Mass_Email_Received__c, Last_mass_email_Date__c from Account where
                    Id in: setOfAccIds ])
            {
                iterAcc.Mass_Email_Received__c = true;
                iterAcc.Last_mass_email_Date__c = Date.Today();
                listOfAccountsToBeUpdated.add(iterAcc);
            }
        system.debug('listOfAccountsToBeUpdateddd '+listOfAccountsToBeUpdated);
        try{
                if(!listOfAccountsToBeUpdated.isEmpty())
                {
                    update listOfAccountsToBeUpdated;
                }
            }
            catch(Exception e){
                SystemLoggerUtil.logError(e);
            }

            }
            updateEmailBlast(emailBlastItem);
        }




  }

  global void finish(Database.BatchableContext BC) {

  }

   global void execute(SchedulableContext scon) {
      Database.executeBatch(new Batch_EmailBlast(),1000);
   }

   private void updateEmailBlast(Email_Blast__c emailBlast)
   {
       if(mapOfDupCaseIdToError.keySet().size() > 0){
      String OrgCaseError = 'Case Id,Error Message\n';
      for(Id iterCaseId : mapOfDupCaseIdToError.keySet()){
        OrgCaseError += iterCaseId+','+mapOfDupCaseIdToError.get(iterCaseId)+'\n';
      }
      Attachment dupCaseAttchment = new Attachment();
      dupCaseAttchment.Name = 'Duplicate Case Errors'+ dateTime.Now()+'.csv';
      dupCaseAttchment.contentType = 'text/csv';
      dupCaseAttchment.body = Blob.valueOf(OrgCaseError);
      dupCaseAttchment.ParentId = emailBlast.Id;
      insert dupCaseAttchment;
    }

    if(mapOfOrgCaseIdToError.keySet().size() > 0){
      String OrgCaseError = 'Case Id,Error Message\n';
      for(Id iterCaseId : mapOfOrgCaseIdToError.keySet()){
        OrgCaseError += iterCaseId+','+mapOfOrgCaseIdToError.get(iterCaseId)+'\n';
      }
      Attachment orgCaseAttchment = new Attachment();
      orgCaseAttchment.Name = 'Case Errors'+ dateTime.Now()+'.csv';
      orgCaseAttchment.contentType = 'text/csv';
      orgCaseAttchment.body = Blob.valueOf(OrgCaseError);
      orgCaseAttchment.ParentId = emailBlast.Id;
      insert orgCaseAttchment;
    }

    emailBlast.Completed_Date__c = DateTime.Now();
    if(!mapOfDupCaseIdToError.values().isEmpty() || !mapOfOrgCaseIdToError.values().isEmpty()){
      emailBlast.status__c = 'Partially Completed';
    }
    else 
      emailBlast.status__c = 'Completed';
    emailBlast.No_Of_Emails_Sent__c = listOfEmailsToBeSent.size();
    emailBlast.No_Of_Duplicate_Cases__c = listOfDuplicateCases.size();
    update emailBlast;
   }

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.