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 = 10000To 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