Make a date dynamic SOQL query for Database.getQueryLocator

apexbatchdebuggingdynamic-soql

My goal for this class is to run for every missing day up until today, with just one call from an anon block.

I would like some assistance with creating a dynamic soql query that will work within the batch apex Database.getQueryLocator. As many of you probably know, there are three required functions within batch apex (Start, execute and finish). I am specifically talking about the start method that query all of the records that will be batched through. I've narrowed down the issue to be the bind variable missingDate. The original variable is being pulled from the argument I give in the constructor from the anon block. It is then used again after being modified to the next day in the finish function to call the next day.

The query works when I run it in an anon block after assigning the variable but returns nothing when testing.

Updated after Rubens Initial Comment(Thank you again):

public class MasterInventoryMissingBatchCleanUp implements Database.Batchable<sObject>{


    public Date missingDate;

    public MasterInventoryMissingBatchCleanUp(Date missingDate){
        this.missingDate = missingDate;
    }

    public Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator(
            'SELECT Aisle__c,Item__c,Last_Record__c,Section__c,Shelf__c,Units_in_Stock__c,units_entry__c, Warehouse_name__c,Account_item_owner__c,Effective_Start_Date__c,Effective_End_Date__c, Status__c,Stock_Location__c,Transaction_Entry__c,Transaction_Number__c,Virtual_Bin_Designation__c, Master_Allocation__c,P_O__c,Shipping_ID__c FROM Stock_Entry__c WHERE Effective_Start_Date__c <= :missingDate AND (Effective_End_Date__c = NULL OR Effective_End_Date__c >= :missingDate) AND units_in_stock__c > 0'  
            );
    }
    public void execute(Database.BatchableContext bc, List<Stock_Entry__c> scope){
    // process each batch of records
        List<Master_Inventory__c > snewt = new List <Master_Inventory__c>(); 
        
        for(Stock_Entry__c se :Scope){
            system.debug('Effective start date: ' + se.Effective_Start_Date__c);
            system.debug('Effective end date: ' + se.Effective_End_Date__c);
            
            Master_Inventory__c snew = new Master_Inventory__c (
                units_entry__c = se.units_entry__c ,
                Units_in_Stock__c = se.Units_in_Stock__c,
                warehouse_name__c = se.warehouse_name__c ,
                Aisle__c = se.Aisle__c,
                Section__c = se.section__c,
                Shelf__c = se.Shelf__c,
                Status__c = se.Status__c,
                Item__c = se.Item__c,
                Account_item_owner__c =se.Account_item_owner__c,
                Effective_Start_Date__c = se.Effective_Start_Date__c,
                Effective_End_Date__c = se.Effective_End_Date__c,
                Master_Allocation__c = se.Master_Allocation__c,
                P_O__c = se.P_O__c,
                Shipping_ID__c = se.Shipping_ID__c,
                Stock_Location__c = se.Stock_Location__c,
                Transaction_Entry__c = se.Transaction_Entry__c,
                Transaction_Number__c = se.Transaction_Number__c,
                Virtual_Bin_Designation__c = se.Virtual_Bin_Designation__c,
                Power_of_One__c = 1,
                Inventory_Date__c = missingDate
                
            );
            
            snewt.add(snew);
            
        }
        
        insert snewt;
    }


public void finish(Database.BatchableContext bc){
    Date newMissingDate = missingDate.addDays(1);
    if( newMissingDate < system.Date.today()){
        MasterInventoryMissingBatchCleanUp myBatchObject = new MasterInventoryMissingBatchCleanUp(newMissingDate);
        Id batchId = Database.executeBatch(myBatchObject,500);

    }

    String[] toAddresses = new String[] {'[email protected]'};
    Messaging.SingleEmailMessage mail=new Messaging.SingleEmailMessage();
        mail.setToAddresses(toAddresses);
            mail.setReplyTo('[email protected]');
            mail.setSenderDisplayName('Batch Completion Notification');
            mail.setSubject('Missing Master Inventory Batch Clean Up Completed for Date: '+ string.valueOf(missingdate));
            mail.setPlainTextBody('The Missing Master Inventory Batch Clean Up has Completed');
        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });


}

}

Testclass also after Ruben's initial comment:

@istest
public class MasterInventoryMissingBatchCleanUp_Test{

    @TestSetup
    static void makeData(){
        // Create your test data
        Account acc = new Account();
        acc.name ='Test';
        acc.RecordTypeID = '012j0000000pirBAAQ';
        insert acc;

        Stock_Location__c sl = new Stock_Location__c();
        sl.Aisle__c = 'a';
        sl.Section__c = '2';
        sl.Shelf__c = 'a';
        insert sl;

        Sourcing_Item__c sitem = new Sourcing_Item__c();
        sitem.name = 'test mitem';
        sitem.Unit_of_measure__c = 'unit';
        sitem.Account_Owner__c = acc.id; 
        insert sitem;



        Date oneMonthAgo = System.today().addMonths(-1);
        List<Stock_Entry__c> stockEntryList = new List<Stock_Entry__c>();
        Stock_Entry__c sec1 = new Stock_Entry__c();
        sec1.Virtual_Bin_Designation__c = 'test';
        sec1.units_Entry__c = 1;
        sec1.item__c = sitem.Id;
        sec1.status__c = 'In hand';
        sec1.aisle__c = 'a';
        sec1.section__c = 2;
        sec1.shelf__c = 'a';
        sec1.Last_Record__c = True;
        sec1.Effective_Start_Date__c = oneMonthAgo; 
        sec1.Effective_End_Date__c = NULL;
        sec1.units_in_stock__c = 1;
        stockEntryList.add(sec1);
        
        Stock_Entry__c sec2 = new Stock_Entry__c();
        sec2.Virtual_Bin_Designation__c = 'test';
        sec2.units_Entry__c = 1;
        sec2.item__c = sitem.Id;
        sec2.status__c = 'In hand';
        sec2.aisle__c = 'a';
        sec2.section__c = 2;
        sec2.shelf__c = 'a';
        sec2.Last_Record__c = True;
        sec2.Effective_Start_Date__c = oneMonthAgo;
        sec2.Effective_End_Date__c = NULL;
        sec2.units_in_stock__c = 2;
        stockEntryList.add(sec2);
        
        Stock_Entry__c sec3 = new Stock_Entry__c();
        sec3.Virtual_Bin_Designation__c = 'test';
        sec3.units_Entry__c = 1;
        sec3.item__c = sitem.Id;
        sec3.status__c = 'In hand';
        sec3.aisle__c = 'a';
        sec3.section__c = 2;
        sec3.shelf__c = 'a';
        sec3.Last_Record__c = True;
        sec3.Effective_Start_Date__c = oneMonthAgo;
        sec3.Effective_End_Date__c = NULL;
        sec3.units_in_stock__c =3;
        stockEntryList.add(sec3);
        
        insert stockEntryList;

            
    }


    @istest static void test() {
        Test.startTest();
        date twoDaysAgo = system.today().addDays(-2);
        MasterInventoryMissingBatchCleanUp miTest = new MasterInventoryMissingBatchCleanUp(twoDaysAgo);
        Id batchId = Database.executeBatch(miTest);
        Test.stopTest();
        
        Integer expectedRecords = [SELECT COUNT() FROM Stock_Entry__c];
        System.assertEquals(expectedRecords, [SELECT COUNT() FROM Master_Inventory__c], 'There are not ' + expectedRecords + ' records');

        }
}

What this test returns:
System.AssertException: Assertion Failed: There are not 3 records: Expected: 3, Actual: 0

Best Answer

The query in start method has three conditions that must be fulfilled:

Effective_Start_Date__c <= :missingDate
Effective_End_Date__c = NULL OR Effective_End_Date__c >= :missingDate
units_in_stock__c > 0

In the makeData method you create three Stock Entry with these values:

Effective_Start_Date__c = system.today();
Effective_End_Date__c = NULL;
units_in_stock__c = null (because you never set it)

Then you set twoDaysAgo as missingDate, but today (Effective_Start_Date__c) is not less or equal than twoDaysAgo.
So the first and the third conditions of the start query are not verified.

In the makeData method you could set Effective_Start_Date__c = System.today().addMonths(-1); and units_in_stock__c = 1.

Date oneMonthAgo = System.today().addMonths(-1);
List<Stock_Entry__c> stockEntryList = new List<Stock_Entry__c>();
Stock_Entry__c sec1 = new Stock_Entry__c();
sec1.Virtual_Bin_Designation__c = 'test';
sec1.units_Entry__c = 1;
sec1.item__c = sitem.Id;
sec1.status__c = 'In hand';
sec1.aisle__c = 'a';
sec1.section__c = 2;
sec1.shelf__c = 'a';
sec1.Last_Record__c = True;
sec1.Effective_Start_Date__c = oneMonthAgo; 
sec1.Effective_End_Date__c = NULL;
sec1.units_in_stock__c = 1;
stockEntryList.add(sec1);

Stock_Entry__c sec2 = new Stock_Entry__c();
sec2.Virtual_Bin_Designation__c = 'test';
sec2.units_Entry__c = 1;
sec2.item__c = sitem.Id;
sec2.status__c = 'In hand';
sec2.aisle__c = 'a';
sec2.section__c = 2;
sec2.shelf__c = 'a';
sec2.Last_Record__c = True;
sec2.Effective_Start_Date__c = oneMonthAgo;
sec2.Effective_End_Date__c = NULL;
sec2.units_in_stock__c = 2;
stockEntryList.add(sec2);

Stock_Entry__c sec3 = new Stock_Entry__c();
sec3.Virtual_Bin_Designation__c = 'test';
sec3.units_Entry__c = 1;
sec3.item__c = sitem.Id;
sec3.status__c = 'In hand';
sec3.aisle__c = 'a';
sec3.section__c = 2;
sec3.shelf__c = 'a';
sec3.Last_Record__c = True;
sec3.Effective_Start_Date__c = oneMonthAgo;
sec3.Effective_End_Date__c = NULL;
sec3.units_in_stock__c =3;
stockEntryList.add(sec3);

insert stockEntryList;

Moreover the batch will run only once as explained by Phil, so the amount of created Master_Inventory__c records will be exactly the same as the Stock_Entry__c records collected by the start method, therefore the actual assert is wrong (you create 3 Stock Entry records, but you expect 6 Master Inventory records).
It should be:

Integer expectedRecords = [SELECT COUNT() FROM Stock_Entry__c];
System.assertEquals(expectedRecords, [SELECT COUNT() FROM Master_Inventory__c], 'There are not ' expectedRecords + ' records');

The way you added a variable to the query used in the Database.getQueryLocator is already fine.
start, execute and finish are instance methods, so they can see instance variables.

Anyway since you aren't using a dynamic query, you should prefer inline queries in the start method. This way if you mistype a field, you won't be able to deploy the class, while using a query string will lead to a runtime error. Moreover with inline queries you bind variable field.

Docs

unlike inline SOQL, dynamic SOQL can’t use bind variable fields in the query string.

I.E. Not Allowed (results in a "Variable does not exist" error)

MyCustomObject__c myVariable = new MyCustomObject__c(field1__c ='TestField');
Database.getQueryLocator('SELECT Id FROM MyCustomObject__c WHERE field1__c = :myVariable.field1__c');

Allowed

MyCustomObject__c myVariable = new MyCustomObject__c(field1__c ='TestField');
Database.getQueryLocator([SELECT Id FROM MyCustomObject__c WHERE field1__c = :myVariable.field1__c]);

Binding Statement

soql += ' and Date__c = :wkenddate'; // Include the ':'

Note: DOES NOT WORK with expressions, such as Map.keySet(), array[index], etc. Only simple primitives. You can use sets and lists, however, provided you mean to use the entire list.

Formatting

soql += ' and Date__c = '+DateTime.newInstance(wkenddate, Time.newInstance(0, 0, 0, 0)).format('yyyy-dd-MM'); // Make sure it's normal SOQL formatted.

[SalesForce] How to make soql query dynamic

Here's a Salesforce doc on Dymanic SOQL:

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_soql.htm

Here's the idea: First, write you're SOQL query as a string. Start with something simple.

string queryString = 'SELECT id,Name,Account.Name FROM Contact LIMIT 3';

To get the result, use the database.query method. Note that the method returns a list<sObject>.

list<sObject> sContactList = database.query(queryString);

// or cast to what you want to return 

list<Contact> contactList = (list<Contact>)database.query(queryString);

Now, let's do a more complicated SOQL query:

 map<id,Contact> contactMap = new map<id,Contact>(contactList);

 // using standard soql
 list<Contact> theSameContactList = [SELECT id,Name,Account.Name
                                     FROM Contact
                                     WHERE id IN :contactMap.keySet()];

 // but this doesn't work so straitforwardlike in dynamic SOQL
 string queryThatDoesntWork = 'SELECT id,Name,Account.Name FROM Contact WHERE id IN:contactMap.keySet()';

 // queryThatDoesntWork doesn't work because you are reference a method of an object.
 // In dynamic SOQL, you can only reference an object -- but not any of its methods

 set<id> contactKeySet = contactMap.keySet();
 string queryStringThatDOESWork = 'SELECT id,Name,Account.Name FROM Contact WHERE id IN:contactKeySet';

 list<Contact> theSameContactList = (list<Contact>)database.query(queryStringThatDOESWork);

Best Answer

Related Solutions

[SalesForce] Dynamic SOQL How to bind date

Binding Statement

Formatting

[SalesForce] How to make soql query dynamic

Related Topic