Getting Purchase Orders filtered via date from OrderContext

In this blog post I'll be sharing my experience with OrderContext using date b/w condition.

What was the need ?

Get the PurchaseOrders from Commerce based on dates (with between condition) and status.

What was done ?

The following pattern is the simplest way to execute order search in Episerver Commerce using the API.

Begin the search code with:

OrderSearchOptions searchOptions = new OrderSearchOptions();
searchOptions.StartingRecord = 0; //or whatever you want to specify for paging purposes
searchOptions.RecordsToRetrieve = 10000

To retrieve only Purchase Orders specify 'PurchaseOrder' as a search parameter -

 searchOptions.Classes.Add("PurchaseOrder");

The next thing is to build parameters against the table -

// initializes search parameter options to be used while querying against db.
OrderSearchParameters parameters = new OrderSearchParameters(); 

StringBuilder metaWhere = new StringBuilder();

// specify your conditions, my case it should be the orders with GersOrderNumber column!=null
metaWhere.Append("NOT META.GersOrderNumber IS NULL"); 

//the dates against the Created Column of OrderGroup_PurchaseOrder table
metaWhere.Append($" AND META.Created>='{startDate}' AND META.Created<='{endDate}'");

parameters.SqlMetaWhereClause = metaWhere.ToString();

// Get the inner query to have OrderGroupId from PurchaseOrder table.
parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)";

The last thing is to call the API -

PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.Search<PurchaseOrder>(parameters, searchOptions);

The API would return all Purchase orders within specified conditions.

Mar 17, 2021

Comments

Please login to comment.