.png?width=667&height=667&ext=.png)
How To Search by PO Number in Kentico E-Commerce
Introduction
One of the primary strengths of the Kentico e-commerce platform is that it can be customized with relative ease. Honestly, this one of the main reasons that my team and I decided to go with Kentico in the first place. The Kentico API and provider object model is top notch, and it can be extended to do just about anything. This is especially true in the e-commerce pillar of the all-in-one solution. Do you need to integrate your Kentico e-commerce site with an ERP system, roll your own custom payment gateway implementation, or sync up with a shipping carrier's API? Have no fear, all of these tasks are totally accomplishable in Kentico.
With that being said, one of the most common requests that we get at BizStream when building a custom e-commerce site in Kentico is the ability for B2B users to make purchases via the use of a Purchase Order Number (PO Number) instead of a standard credit card payment method. That's one feature that isn't totally 100% out of the box when you have a custom checkout process. Again this task is not a tough issue, and there are many helpful links to get you started in this area if you need to.
Funny enough, we are actually currently working on a project that has this requirement in Kentico 9 (yes this is actually a Kentico MVC site too). And after demoing the working functionality to the client, we received a question of how to then look up orders in the administrative interface by a PO Number. And that is something that I didn't have an immediate answer for. After doing some quick research, it turns out that this customization is not that hard to do.
Step 1- Know Where your PO Number is Stored
Before we can show a custom field in the admin interface, we need to know where it is in the architecture. Kentico provides a few different ways to store custom fields on an Order. The first method is add a custom field to the Order class. This method is ok if you have maybe one or two new fields to add to the Order object but somewhat non upgrade proof. Also, if the need is there to store many fields, this may not be the best choice. The other method that is recommended is to use the OrderCustomData field that already exists on the object and is considered "in-the-box" and upgrade safe.
Normally we choose the OrderCustomData field for our projects at BizStream. It is a simple string field on the Order object that allows you to store name/value pair information via an XML representation in the SQL database. This method allows you to place as many values as you want in it, and has a pre-built API to handle updating and retrieving the information from the field. This field has also been inside Kentico for as long as I can remember, and not going anywhere in the future. Which to me, says it is upgrade safe.
Basically you just need to use the following code snippet in your payment provider or custom checkout process to collect and store at PO Number for an order. As you might guess the XML is stored in the OrderCustomData column of the COM_Order table in the Kentico database.
order.OrderCustomData["PONumber"] = "PO Number here";
order.Update();
After running your code you can take a look at your COM_Order table and it should look like:
Step 2- Update the Kentico Admin Orders List
Now that you know where the custom field is and how to update it, let's look at the main piece of the solution. The Kentico Orders list is inside of the Kentico Administrative interface. Typically this is the part of the Orders application in Kentico that gets used the most. It's where most company's customer service reps or content admins will look to see new orders as they come in on the Kentico e-commerce site.
As you can see the standard list of Orders gives you a lot of search ability, but nowhere can you search by anything in OrderCustomData by default. Even if you expand the Advanced search options link, there still isn't anything for custom fields. The real choice you have here is to customize this part of the Kentico system if you need to do what we are about to do. Normally, I do not recommend customizing any core Kentico system files. It can cause too many upgrade issues. However, in this scenario we will need to.
The first step comes down to opening and editing the ~\CMS\CMSModules\Ecommerce\Controls\UI\OrderList.ascx file in your Kentico instance. We need to update the UniGrid control to know about our customization. First, update the Columns attribute of the UniGrid control to contain the OrderCustomData field. This includes the field in the query back to the database on the Orders list screen.
DisplayFilter="true" RememberStateByParam="customerId" RememberDefaultState="true"
Columns="OrderID,OrderInvoiceNumber,OrderCustomerID,OrderDate,OrderTotalPrice,OrderTotalPriceInMainCurrency,OrderPaymentOptionID,OrderIsPaid,OrderShippingOptionID,
OrderCurrencyID,OrderTrackingNumber,OrderNote,OrderSiteID,OrderStatusID,OrderCustomData"
ObjectType="ecommerce.order">
After that, add a new ug:Column before the Notes column (ug:Notes) that is already in that file. You can copy the line I have below if you wish.
Save and close that file. Up next we need to edit the code behind file at ~\CMS\CMSModules\Ecommerce\Controls\UI\OrderList.ascx.cs. In this step we need to add some code into the existing gridElem_OnExternalDataBound method. See the code snippet below. What we are doing is adding another case on the sourceName switch statement. Since in the previous edit we used the ExternalSourceName attribute of the ug:Column, that value gets passed to this method and we can use it to help populate the column with our custom information on this event.
Again in the below code snippet you can see I am working with the passed in DataRowView to look for the field we need, OrderCustomData. I'm actually loading the fields content into the CMS.Helper.CustomData object. This is the magical part of the API that allows us to easily work with the XML formatted data in SQL as standard C# fields via its GetValue and SetValue methods. CustomData is the object that allows us to easily return the PO Number's value and not any extra XML formatting.
{
DataRowView dr = null;
switch (sourceName.ToLowerCSafe())
{
case "ponumber":
CustomData data = new CustomData();
dr = (DataRowView)parameter;
data.LoadData(ValidationHelper.GetString(dr["OrderCustomData"], string.Empty));
return ValidationHelper.GetString(data.GetValue("PONumber"), string.Empty);
//... modified for brevity ...
}
return parameter;
}
Once that code is correctly added your screen should look like the follow. You should see one new column that contains your PO Number values.
Step 3- Make it Filterable
Now that we have the PO Numbers showing up, let's finish the solution by editing the filter. This change is what will give our content admins the ability to search by PO Number in Kentico. Start by opening the ~\CMS\CMSModules\Ecommerce\Controls\Filters\OrderFilter.ascx file. This file contains the filter that the main Orders app uses. Add in the following code right below before the Advanced search code.
<div class="form-group">
<div class="filter-form-label-cell">
<cms:LocalizedLabel CssClass="control-label" ID="lblPONumber" runat="server" Text="PO Number"
DisplayColon="true" EnableViewState="false" />
div>
<div class="filter-form-value-cell">
<cms:CMSTextBox ID="txtPONumber" WatermarkText="E00042, A34rS234E-01" MaxLength="200" runat="server" EnableViewState="false" />
div>
div>
Save and close that file. Up next we need to edit the code behind file at ~\CMS\CMSModules\Ecommerce\Controls\Filters\OrderFilter.ascx.cs. In this file we need to add the supporting code behind logic in the GetFilterWhereCondition method. Add the following right after the Order is Paid switch at about line 376.
Update 8/16/16 - Juraj Komlosi, Security Specialist at Kentico, reminded me to avoid SQL Injection attacks by escaping the dynamic where clause. Note the new use of SQLHelper.EscapeQuotes and SQLHelper.EscapeLikeText. For more great tips by Juraj, check out his post on Avoiding SQL Injection attacks. Thanks Juraj!
string poNumberQuery = txtPONumber.Text.Trim();
if(!string.IsNullOrEmpty(poNumberQuery))
{
condition.Where("cast([OrderCustomData] as xml).value('(CustomData/PONumber)[1]', 'Varchar(200)') LIKE '%" + sqlSafePoNumberQuery + "%'");
}
The job of this code behind is to actually build the WhereCondition of the main UniGrid. We are using some nifty SQL to cast the OrderCustomData column to an XML type that SQL Server recognizes. It's fairly the same as XPath if you are used to that. Basically this cast allows us to treat the stored string as more structured XML data. I'm simply starting at the root CustomData node and then looking for the PONumber node underneath that to get the value of the PONumber. From there I'm just doing a string match on what the user types into the txtPONumber textbox on the filter.
Once that code is correctly added your screen should look like the follow. You should a new filter above the grid that allows you to filter on PO Number values.
Now don't forget to also update the ResetFilter method of the control. This you simply need to set the txtPONumber's value to an empty string. This will clear out the filter on reset.
/// Resets filter to the default state.
///
public override void ResetFilter()
{
txtOrderId.Text = String.Empty;
txtCustomerNameOrCompanyOrEmail.Text = String.Empty;
statusSelector.Value = null;
siteSelector.SiteID = SiteContext.CurrentSiteID;
siteSelector.Reload(true);
dtpFrom.DateTimeTextBox.Text = string.Empty;
dtpCreatedTo.DateTimeTextBox.Text = string.Empty;
txtTrackingNumber.Text = string.Empty;
//PO Number reset
txtPONumber.Text = string.Empty;
ResetSelectors();
ReloadUniselectors();
}
And that is it! You should now have the full solution up and running.
Conclusion
So there you have it. I have shown you how to customize the Kentico Orders app to search for PO Numbers (or any other custom order data) in the main grid of Orders. One thing to note, now that you have customized your Kentico core files, you need to remember this. This will be a prime location to test that everything still works on an upgrade cycle. Luckily Kentico's cool code upgrade tool can help with this. Have you ever customized Kentico like this? Did it in a different way? If so let me know via the comments below. Thanks.
