NHibernate sub query on Orders filtering by usergroup

by Joe Payne 17. March 2014 09:31

A client had a need to filter the Monthly Sales Summary report by group membership.  In other words, restrict the totals to orders placed by users only in a specific user group.

The initial NHibernate query is such:

ICriteria criteria = NHibernateHelper.CreateCriteria<CommerceBuilder.Orders.Order>("O")
    .CreateCriteria("Items", "OI", NHibernate.SqlCommand.JoinType.InnerJoin)
    .Add(Restrictions.Eq("O.Store", AbleContext.Current.Store));

 

So I added a dropdown that is populated by GroupDataSource.LoadAll().  Then it seemed easy enough to just add this code which I found in the UserSearchCriteria.cs file:

if (groupId >= 0)
{
    criteria.CreateCriteria("O.User.UserGroups", "UG", NHibernate.SqlCommand.JoinType.InnerJoin)
    .Add(Restrictions.Eq("UG.Id.Group.Id", this.GroupId));
}

The problem is, this code throws a big ol’ NHibernate error “nhibernate multi-part identifier <someobject> could not be bound”

For some reason, NHibernate couldn’t resolve the relationship of Order –> User –> UserGroups –> Group.   The code I swiped from UserSearchCriteria had no problem with it.  But here it just wouldn’t work.

I finally figured out the solution was to create a new reference to the User table and base the criteria from there.   So instead of starting at the Order object level, the join and restriction starts as the User object level which sort of makes sense now that I’m typing this…

if (groupId >= 0)
{
    criteria.CreateCriteria("O.User", "U", NHibernate.SqlCommand.JoinType.InnerJoin)
        .CreateCriteria("U.UserGroups", "UG")
        .Add(Restrictions.Eq("UG.Id.Group.Id", groupId));
}

Finally, the end result is the NHibernate query now properly searches the orders for only those records where the user that placed the order is a member of a specific GroupId.

Tags: , , , ,

AbleCommerce Gold | Personal

Slick little GeoIP feature for the AbleCommerce Order Manager

by Joe Payne 28. August 2012 21:20

Came up with an awesome mod for the Order Manager screen tonight.  It highlights orders that are placed from non-US IP addresses.  This makes it incredibly easy to identify potential fraud orders in your storefront.

This mod works with a local GeoIP database too, so there’s no lookup limits or bandwidth spikes to worry about.

Geographic IP lookups are a dime a dozen on the internet.  But the vast majority of these lookup sites will throttle the number of requests.  This creates a slow and often frustrating limit on the usefulness in a production environment.

By leveraging a local database, it becomes far more efficient (and less costly) to implement GeoIP lookups in your AbleCommerce website.

Tags: , ,

General News | AC7 Articles

Adding a total order count column to order summary

by Joe Payne 24. September 2008 09:09
Introduction
This modification will add a new column to your AC7 Order Summary page. The contents of the column will be a clickable number showing the total order history count for the customer that placed the order. This gives you the ability to quickly and easily see which customers are repeat customers without having to switch to the reports page.
The total count is all-inclusive and does not restrict by a particular date range. It is also a link. When clicked, it will take you to the Edit User page so the detailed history for that customer can be viewed.

Modifications
The page file we'll be changing is located in ~/Admin/Orders/. You'll need to change both the default.aspx and default.aspx.cs files, so back them both up before going any further.

Edit the ~/Admin/Orders/default.aspx file first. Look for this section of code:
Code:
                            <asp:TemplateField HeaderText="Customer" SortExpression="BillToLastName">
                                <ItemStyle HorizontalAlign="Center" />
                                <ItemTemplate>
                                    <asp:Label ID="CustomerName" runat="server" Text='<%# string.Format("{1}, {0}", Eval("BillToFirstName"), Eval("BillToLastName")) %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>


and replace it with this code:
Code:
                            <asp:TemplateField HeaderText="Customer" SortExpression="BillToLastName">
                                <ItemStyle HorizontalAlign="Center" />
                                <ItemTemplate>
                                    <asp:Label ID="CustomerName" runat="server" Text='<%# string.Format("{1}, {0}", Eval("BillToFirstName"), Eval("BillToLastName")) %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>


                            <asp:TemplateField HeaderText="History" SortExpression="">
                                <ItemStyle HorizontalAlign="Center" />
                                <ItemTemplate>
                                    <asp:HyperLink ID="OrderCount" runat="server" Text='<%# string.Format("{0}",OrderCount(Container.DataItem)) %>' SkinID="Link" NavigateUrl='<%# Eval("UserId", "~/Admin/People/Users/Edituser.aspx?UserId={0}") %>'></asp:HyperLink>
                                </ItemTemplate>
                            </asp:TemplateField>


Done? Good, save it. Now let's edit the ~/Admin/Orders/default.aspx.cs file. Look for this section of code:
Code:
    protected string GetPaymentStatus(object dataItem)
    {
        Order order = (Order)dataItem;
        if (order.PaymentStatus == OrderPaymentStatus.Paid) return "Paid";
        if (order.Payments.Count > 0)
        {
            order.Payments.Sort("PaymentDate");
            Payment lastPayment = order.Payments[order.Payments.Count - 1];
            return StringHelper.SpaceName(lastPayment.PaymentStatus.ToString());
        }
        return order.PaymentStatus.ToString();
    }


and replace it with this code:
Code:
    protected string GetPaymentStatus(object dataItem)
    {
        Order order = (Order)dataItem;
        if (order.PaymentStatus == OrderPaymentStatus.Paid) return "Paid";
        if (order.Payments.Count > 0)
        {
            order.Payments.Sort("PaymentDate");
            Payment lastPayment = order.Payments[order.Payments.Count - 1];
            return StringHelper.SpaceName(lastPayment.PaymentStatus.ToString());
        }
        return order.PaymentStatus.ToString();
    }

    protected string OrderCount(object dataItem)
    {
        Order order = (Order)dataItem;
        return order.User.Orders.Count.ToString();
    }


Save it when you're done. Upload the changes if you have to, then give it a try!

Conclusion
Knowing more about your customers is what makes a good business owner great. Give yourself another edge over your competition by quickly and easily knowing your repeat customers before their order has even been filled.

Tags: , ,

AC7 Articles

Add Order Number search to all Admin pages

by Joe Payne 22. July 2008 00:44
Introduction

This is a quick and dirty one, but oh will it make your day. I don't know about you, but I find myself jumping from order to order and it's always by order number. Well, there really isn't a way to get straight from one order to another by order number. You have to always go back to the main Dashboard and enter the order number there.

So I modified the Admin header navigation control to include an order number field. That way every single page on the Admin side has a box where you can type in any order number and jump straight to the order details page.

Code Changes

Normally I just post the changes to a file. In this case, the file is small so I'm going to post the entire file. That, and I have a dental appointment in 45 minutes :)

Edit the ~/Admin/UserControls/HeaderNavigation.ascx file and replace the entire contents with this code:

Code:
<%@ Control Language="C#" ClassName="HeaderNavigation" EnableViewState="false" %>
<script runat="server">
    protected void Page_Load(object sender, System.EventArgs e)
    {
        if (Token.Instance.User == null || Token.Instance.User.IsAdmin == false)
        {
            AdminNavigationHeaderPanel.Visible = false;
        }
        else
        {
            OrdersLink.Visible = (Token.Instance.User.IsInRole(Role.OrderAdminRoles));
            CatalogLink.Visible = (Token.Instance.User.IsInRole(Role.CatalogAdminRoles));
            OrderId.Visible = (Token.Instance.User.IsInRole(Role.OrderAdminRoles));
            ViewOrderButton.Visible = (Token.Instance.User.IsInRole(Role.OrderAdminRoles));
        }
    }

    protected void ViewOrderButton_Click(object sender, EventArgs e)
    {
        int tempOrderId = AlwaysConvert.ToInt(OrderId.Text);
        Order order = OrderDataSource.Load(tempOrderId);
        if (order != null)
        {
            Response.Redirect("~/Admin/Orders/ViewOrder.aspx?OrderId=" + tempOrderId.ToString());
        }
        else
        {
            CustomValidator invalidOrderId = new CustomValidator();
            invalidOrderId.ControlToValidate = "OrderId";
            invalidOrderId.ErrorMessage = "*";
            invalidOrderId.Text = "Order number is not valid";
            invalidOrderId.IsValid = false;
            AdminNavigationHeaderPanel.Controls.Add(invalidOrderId);
        }
    }

</script>
<asp:Panel ID="AdminNavigationHeaderPanel" runat="server" >
    <table>
    <tr>
        <td>
       <asp:HyperLink ID="DashboardLink" runat="server" NavigateUrl="~/Admin/Default.aspx" CssClass="dashboard" Text="Dashboard"></asp:HyperLink>
       <asp:HyperLink ID="OrdersLink" runat="server" NavigateUrl="~/Admin/Orders/Default.aspx" CssClass="orders" text="Orders"></asp:HyperLink>
       <asp:HyperLink ID="CatalogLink" runat="server" NavigateUrl="~/Admin/Catalog/Browse.aspx" CssClass="catalog" Text="Catalog"></asp:HyperLink>
       <asp:HyperLink ID="StoreLink" runat="server" NavigateUrl="~/Default.aspx" CssClass="stores" Text="Store"></asp:HyperLink>
       <asp:HyperLink ID="LogoutLink" runat="server" NavigateUrl="~/Logout.aspx" CssClass="logout" Text="Logout"></asp:HyperLink>
       </td>
       <td class="header" align="left" valign="bottom"><asp:Localize ID="ViewOrderNumberCaption" runat="server" Text="View Order:"></asp:Localize><br />
       <asp:TextBox ID="OrderId" runat="server" Width="40px" ValidationGroup="OrderSummary"></asp:TextBox>
        <asp:Button ID="ViewOrderButton" runat="server" ValidationGroup="OrderSummary" OnClick="ViewOrderButton_Click" Text="Go" />
        </td>
   </tr>
    </table>
</asp:Panel>


Save it.

Conclusion

You might notice the text color isn't right. Well, I hate CSS styles and CSS styles hate me. It's mutual and I'm ok with that. If you know how to make two stupid little words show the proper style color from the style sheet, please post it here. Others will be grateful and the score will become CSS 220, Joe 0 :wink:

Tags: , , ,

AC7 Articles

Month List