Searching for orders

This document describes how to work with order search in EPiServer Commerce. Searching for orders is a common part of e-commerce solutions, for instance when integrating with ERPs and custom administrative interfaces where orders are to be exported, filtered, or edited. Orders often need to be loaded based on date of submission, their status, or on particular properties or meta field properties in the order or cart, including properties of its child objects (for example shipments or line items).

EPiServer Commerce offers an API to search for and retrieve orders. This document covers:

• The capabilities and limitations of EPiServer Commerce order search
• The API - classes and properties used
• Code examples of different scenarios
• Description of the search stored procedure 

Classes referred to here are available in the following namespaces:

• Mediachase.Commerce.Orders

Search pattern

The simplest way to do order search in EPiServer Commerce using the API is to follow the pattern described below.

Begin the search code with:

OrderSearchOptions searchOptions = new OrderSearchOptions();
searchOptions.CacheResults = false; //or true if the results may be recalled shortly
searchOptions.StartingRecord = 0; //or whatever you want to specify for paging purposes
searchOptions.RecordsToRetrieve = 10000; //or whatever you want to specify for paging purposes searchOptions.Namespace = "Mediachase.Commerce.Orders";

Then continue with:

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder"); //this returns only purchase orders, for shopping carts, specify ShoppingCart parameters.SqlMetaWhereClause = "";
parameters.SqlWhereClause = sqlQuery.ToString();
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions); //If you are searching for shopping carts, you would call the FindCarts method and return an array of Cart objects

The sqlQuery above is of the form : OrderGroupId IN (SELECT OrderGroupId FROM .). See the code examples below.

How to use order search

The order search is designed to return arrays of OrderGroup objects - ShoppingCart, PaymentPlan, PurchaseOrder. To get a better understanding of the order object and table hierarchy, refer to the Order System/Order Object Model and Database Diagram section of this documentation.

The above is not intended for returning shipments, line items, payment objects or any other objects/data in an OrderGroup, except in the context of the whole order. If you need to do an order search for those types of objects alone, the Order Search API will not do this. The API allows you to search for carts/orders/payment plans based on properties, including meta fields associated with any of the objects in an OrderGroup and any outside database tables you may want to use for filtering/joins.

The search pattern implemented in the API has two parts:

• First get a list of OrderGroupIds as search results using the stored procedure ecf_OrderSearch and insert those values into a database table OrderSearchResults (with a GUID that separates it from other search results). This is the search phase, which is based on the criteria you pass in to the search.
• Next, the OrderGroupIds are loaded from OrderSearchResults and the corresponding array of ShoppingCart/PurchaseOrder/PaymentPlan is loaded and returned.

Note that sorting can only be done based on OrderGroup table fields.

Primary classes and properties

Mediachase.Commerce.Orders.Search.OrderSearchOptions
This class allows you to specify the number of records to be returned, whether the results are to be cached, the type of OrderGroup data to be returned (e.g. ShoppingCart/PurchaseOrder/PaymentPlan), starting record number (for paging).

Properties

CacheResults
Boolean. Set to true if the results may need to be re-retrieved soon.

RecordsToRetrieve
StartingRecord
Integer. You can use these two fields to do paging on the search results.

Classes

StringCollection
This allows you to specify which order metadata object you want to create a subquery on. For example, if you wanted to find all shipments where a shipment meta field value is a particular value, you could specify "ShipmentEx" for the classes property. This is a string collection, and in most cases only one class needs to be specified.

These are the classes you could specify, with the corresponding database table that will be queried:

• CashCardPayment (OrderFormPayment_CashCard)
• CreditCardPayment (OrderFormPayment_CreditCard)
• ExchangePayment (OrderFormPayment_Exchange)
• GiftCardPayment (OrderFormPayment_GiftCard)
• InvoicePayment (OrderFormPayment_Invoice)
• LineItemEx (LineItemEx)
• OrderFormEx (OrderFormEx)
• OrderGroupAddressEx (OrderGroupAddressEx)
• OtherPayment (OrderFormPayment_Other)
• PaymentPlan (OrderGroup_PaymentPlan)
• PurchaseOrder (OrderGroup_PurchaseOrder)
• ShipmentEx (ShipmentEx)
• ShoppingCart (OrderGroup_ShoppingCart)

Note that you do need to specify at least one class here, even if you dont specify a SQLMetaWhereClause in the parameters object (below). If you do not specify SQLMetaWhereClause but do specify an OrderGroup (PurchaseOrder, ShoppingCart, PaymentPlan), it will filter the results for that ordergroup type only.

Namespace

For order searches, this should always be Mediachase.Commerce.Orders

Mediachase.Commerce.Orders.Search.OrderSearchParameters
This class allows you to specify the where clauses necessary to narrow your order search.

Properties

OrderByClause
This clause can only use ordergroup table fields. If this field is not set, OrderGroupId is used.

SqlMetaWhereClause
This field matches up with the OrderSearchOptions.Classes field to form a subquery based on an OrderGroup metaclass. This clause looks like this: "META.CardType = 'Visa'" (where the option classes collection contains "CreditCardPayment"). That returns all orders/carts/payment plans with visa payments. META is the SQL alias for the table associated with the class you specify (see options Classes property above).

SqlWhereClause
This is an SQL WHERE clause that you can use to specify additional criteria in your order search. This where clause is used to form a subquery in the search stored procedure (see the stored procedure breakdown below). Note that using the META keyword here will be referring to a different alias than using it in the SqlMetaWhereClause. In the SqlMetaWhereClause, it is an alias for the table associated with the class specified in the options object. In the SQLWhereClause, it refers to a table in memory that has two columns:

• Key - which is the OrderGroupId
• Rank - which is part of the meta where clause subquery - you probably will not want to use META here.

The only table referenced directly in the SQL where clause is the OrderGroup table, meaning that you could have a clause like: OrderGroupId IN (SELECT OrdergroupId FROM Shipment WHERE NOT ShipmentTrackingNumber IS NULL). Note that you cannot add an ORDER BY clause here, as its part of a subquery (see stored procedure description below).

AdvancedFreeTextSearchPhrase and FreeTextSearchPhrase
These fields will not work and should be left blank.

Note that the following fields are NOT used for order search:

• JoinSourceTable
• JoinSourceTableKey
• JoinTargetQuery
• JoinTargetTableKey
• JoinType

Mediachase.Commerce.Orders.OrderContext
This class is a singleton with several methods to find orders:

• FindActiveOrders() - to retrieve all PurchaseOrders where the OrderGroup.Status is either InProgress or PartiallyShipped.
• FindCarts() - allows you to find shopping carts based on parameters and options specified with the two other classes.
• FindPaymentPlans() - same as FindCarts except with PaymentPlans.
• FindPurchaseOrders() - same as FindCarts except with PurchaseOrders.
• FindPurchaseOrdersByStatus() - allows you to retrieve orders with particular order statuses.

Code examples

For all of the code examples below, the following code is common and first defined:

OrderSearchOptions searchOptions = new OrderSearchOptions();
searchOptions.CacheResults = false;
searchOptions.StartingRecord = 0;
searchOptions.RecordsToRetrieve = 10000;
searchOptions.Namespace = "Mediachase.Commerce.Orders";

The following examples are the code that follows the above code.

Example 1

In this example all purchase orders are retrieved with a tracking number that is like a particular pattern, and where a tracking number (a meta field) has been assigned to at least one of the shipments in the purchase order:

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder");
parameters.SqlMetaWhereClause = "META.TrackingNumber LIKE '%PO%'";
parameters.SqlWhereClause = "OrderGroupId IN (SELECT OrdergroupId FROM Shipment WHERE NOT ShipmentTrackingNumber IS NULL)";
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

Example 2

Here all purchase orders are retrieved that contain line items that have an RMANumber (a meta field) associated with them:

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("LineItemEx");
parameters.SqlMetaWhereClause = "NOT META.RMANumber IS NULL";
parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)";
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

Note that the SqlWhereClause is used to ensure that only purchase orders are returned. If you do not do this, you may get carts (or payment plans, if they exist in your system) also in the dataset and you will get errors like: Index 0 is either negative or above rows count.

This is because the metadata object load is getting confused by the different meta fields in a purchase order vs a cart. In any cases where you are querying orders using the API, and you are not specifying which OrderGroup object as the meta class you are seeking, you need to add this where filter in the SQLWhereClause. Of course, you can make a compound where clause including this filter.

Example 3

In this example all shopping carts are retrieved where lineitems have an expiration date (a meta field) greater than a particular value:

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("LineItemEx");
parameters.SqlMetaWhereClause = "META.ExpirationDate > '1/1/2012'";
parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_ShoppingCart)";
Cart[] cartCollection = OrderContext.Current.FindCarts(parameters, searchOptions);

Example 4

Here all purchase orders are retrieved that have shipments with a particular a shipping status (a meta field):

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("ShipmentEx");
parameters.SqlMetaWhereClause = "NOT META.PrevStatus IS NULL";
parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)";
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

Example 5

This is a more complex SQL where clause to return all orders that contain SKUs whose name contains a substring by using a join with the CatalogEntry table. Note that we do not have an SqlMetaWhereClause but do specify a PurchaseOrder class.

You can add any subquery here, allowing the most flexibility.

StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("OrderGroupId IN (SELECT OrderGroupId From LineItem li ");
sqlQuery.Append("INNER JOIN CatalogEntry en ");
sqlQuery.Append("ON li.CatalogEntryId = en.Code ");
sqlQuery.Append("WHERE en.Name LIKE '%Wine%')");

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder"); parameters.SqlMetaWhereClause = "";
parameters.SqlWhereClause = sqlQuery.ToString();
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

Example 6

In this example, several order meta data tables are joined in the SQL where clause. This is a better, easier way to do more complex order searches with multiple order meta classes (rather than using the options Classes collection for filtering order meta classes). Note that the query below appears to have the potential to return duplicate ordergroupids as an order can have multiple shipments or lineitems. However, the order search stored procedure ensures that only distinct order IDs are returned.

StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("OrderGroupId IN (SELECT li.OrderGroupId From LineItem li ");
sqlQuery.Append("INNER JOIN LineItemEx ex ");
sqlQuery.Append("ON li.LineItemId = ex.ObjectId ");
sqlQuery.Append("INNER JOIN Shipment sh ");
sqlQuery.Append("ON li.OrderGroupId = sh.OrderGroupId ");
sqlQuery.Append("INNER JOIN ShipmentEx shex ");
sqlQuery.Append("ON sh.ShipmentId = shex.ObjectId ");
sqlQuery.Append("WHERE ex.ExpirationDate > '1/1/2011'");
sqlQuery.Append("AND NOT shex.PrevStatus IS NULL)");

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder");
parameters.SqlMetaWhereClause = "";
parameters.SqlWhereClause = sqlQuery.ToString();
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

Note Always test your queries in SQL Management Studio first, and remember the closing ) for your SqlWhereClause property value.

Stored procedure breakdown

The first part of the order search is to perform a dynamic search for orders based on the OrderSearchParameters and OrderSearchOption properties set in code.

This is the final query that is built based on the settings you specify. This is assuming that the AdvancedFreeTextSearchPhrase and FreeTextSearchPhrase properties are left blank (see above). The stored procedure which uses these properties to create a list of order ids is ecf_OrderSearch.

declare @Page_temp table (TotalRecords int, OrderGroupId int);
with OrderedResults as
(SELECT count([OrderGroup].OrderGroupId)
OVER() TotalRecords, [OrderGroup].OrderGroupId, Rank, ROW_NUMBER()
OVER(ORDER BY ) RowNumber
FROM [OrderGroup] OrderGroup
INNER JOIN (select distinct U.[KEY], U.Rank from (select 100 as 'Rank', META.ObjectId as 'Key', * from <database table associated with the order metaclass in the options Classes property> META
WHERE <SQLMetaWhereClause>) U) META
ON OrderGroup.[OrderGroupId] = META.[KEY]
WHERE ApplicationId = '[Application ID]' AND
(<SQLWhereClause>))

INSERT INTO @Page_temp (TotalRecords, OrderGroupId)
SELECT top(<RecordsToRetrieve>) TotalRecords, OrderGroupId FROM OrderedResults
WHERE RowNumber > <StartingRecord>;;

select @RecordCount = TotalRecords from @Page_temp;

INSERT INTO OrderSearchResults (SearchSetId, OrderGroupId)
SELECT [SearchSetId], OrderGroupId from @Page_temp;

Note the following:

• The query for meta data is built using the class you specify and the SQLMetaWhereClause you specify (already mentioned above). The META where clause "inside" the SQLMetaWhereClause is different from the META alias available to the SQLWhereClause.
• If no SQLWhereClause is specified, the query executes without the AND [SQLWhereClause] portion. The same goes for SQLMetaWhereClause.
• The Application ID will be managed internally by EPiServer Commerce .