Customizations in Gold

[jguengerich]

Check your search settings, such as match case and match whole word.

[Brewhaus]

I had to search for hidden text in order to find it, but when I did, and replaced the code, the e-mail address did populate as it should. Thank you for the help!

[Humannature]

By messing with the ConLib/Utility/ProductPrice.ascx.cs file we can get both the regular and sale price to show, when applicable, however, they are both the same color, and then on the Product page it shows Reg. Price XXX, Sale Price: both the regular price (with strike-through) and the sale price. I hope that there is better control over the pricing display than this! Honestly, why would anyone not want to show the regular price and sale price? If you don't, then customers will simply think that this is your regular price, and when the sale goes off they will complain about the increased price.

I am starting to get concerned that we will not be able to upgrade within AC, meaning the 40+ hours that we have spent on it so far are wasted, and we will have to start looking at other software packages.

OK I don't see that anyone has explained how to fix the sale price showing twice on the product page after changing the .cs to display it on the category pages. Why are these controlled together when it conflicts with the product page? And why would this default to not showing the regular price. Who would ever say "no please don't tell the customer this item is on sale" ?!

And I TOTALLY agree with the "I am starting to get concerned that we will not be able to upgrade within AC, meaning the 40+ hours that we have spent on it so far are wasted, and we will have to start looking at other software packages." statement. Our upgrade has been a NIGHTMARE. Going to Gold was tough and r5 just starts this again. Plus we are experiencing the checkout cart processing lock up that was posted on July 26th. VERY FRUSTRATING, COSTLY AND TIME WASTING! There is no way to know how much money this has cost us.

[Brewhaus]

First- getting the sale price to show correctly (this is taken from my upgrade notes, so let me know if you have any problems understanding my steps):
• Show regular and sale price
o Open ConLib/Utlity/ProductItemDisplay.ascx
o Find <uc:ProductPrice ID="ProductPrice" runat="server" PriceFormat="<span class='label'>Price:</span> <span class='value'>{0}</span>" BasePriceFormat='Price: <span class="msrp">{0}</span> '></uc:ProductPrice>
o After </span> ‘ add ShowRetailPrice="true"
o Line should look like this <uc:ProductPrice ID="ProductPrice" runat="server" PriceFormat="<span class='label'>Price:</span> <span class='value'>{0}</span>" BasePriceFormat='Price: <span class="msrp">{0}</span> '></uc:ProductPrice>

As for upgrading, I am way beyond 40 hours now, and still have two remaining issues that are stopping us from making the move. I may be able to get UPS to help on one (re: tracking numbers being moved to the DB, as it is a result of a Worldship problem), but we still cannot get one of our customizations to work. AC is not willing to help (I understand that they are tight on time, but it would likely be a 15 minute fix for them, and would mean two upgrades being sold), and the person that did the upgrades and promised that it would work with Gold was wrong, and not willing to right it.

I REALLY do not want to start looking at other software packages, but may be left with no choice soon.

[ForumsAdmin]

Which customization of yours isn't working for you yet? I think whoever did the customization originally should have no problem upgrading it to AC Gold.

[jmestep]

I'm not sure of the reason for having one product price conlib for category and product pages, but that is the way it has always been. I personally have trouble following the logic so I have found that for me it is easier just to make a new one for use in one of those places, stripping the code down to just what I need. That saves me several hours of trying to figure it out, then forgetting and having to remember what the logic was six months later when I'm working on a different site. Different merchants was so many different things.

(You can use the green button feedback in the admin to submit a feature request to Able.)

[Brewhaus]

Which customization of yours isn't working for you yet? I think whoever did the customization originally should have no problem upgrading it to AC Gold.

In total we have two:

1) When we mark orders as shipped (from ..Admin/Orders) we have a routine that runs to move the tracking numbers from a temporary table into the ac_TrackingNumbers table. We had to do it this way because UPS Worldship sees the INT value of 4 for the OrderShipmentID and thinks that it can only send 4 characters, and truncates the OrderShipmentID to 4 characters. There are two pieces of code in the Admin/Orders/Default.aspx.cs file that we copied across to the same file in Gold, but we get a compilation error.

2) We had a routine written so that we could export sales for the day for importing into our accounting software. I had a version working on our old Windows 2003 32-bit server, but when we upgraded to Server 2008R2 64-bit it would not work. Instead of having it patched to work with the new server we had it written from scratch, as I was told that going this way it would work with Gold if we upgraded down the road. Unfortunately, it doesn't, and the person that wrote it refuses to assist, saying that they consider it 'new work', and they do not have time for it.

Both of these are relatively simple routines that would likely take someone who understands the code less than 15 minutes each to solve. I have spent hours on them with no luck.

[jguengerich]

For 2), you could check if that person minds if you post the code here and see if someone can give you pointers on how to make it work with Gold.

[Brewhaus]

It is definitely worth a shot.

OrderExport.aspx

Code: Select all

<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" CodeFile="OrderExport.aspx.cs" Inherits="OrderExport" Title="Order Export" %>

<%@ Register Src="~/Admin/UserControls/PickerAndCalendar.ascx" TagName="PickerAndCalendar" TagPrefix="uc1" %>

<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" Runat="Server">
        <table align="center" class="form" cellpadding="0" cellspacing="0" border="0" width="100%">
            <tr>
                <th class="sectionHeader" colspan="4">
                    <div style="text-align: left">
                        Export Period</div>
                </th>
            </tr>
            <tr>
                <td>
                    <div style="text-align: right; vertical-align: middle">
                        <asp:Label ID="Label1" runat="server" Text="Ship Date From:  " SkinID="FieldHeader"></asp:Label>
                    </div>
                </td>
                <td style="text-align: left">
                    <uc1:PickerAndCalendar ID="cal_StartDate" runat="server" />
                    
                </td>
                <td>
                    <div style="text-align: right; vertical-align: middle">
                        <asp:Label ID="Label4" runat="server" Text="To:  " SkinID="FieldHeader"></asp:Label>
                    </div>
                </td>
                <td style="text-align: left">
                    <uc1:PickerAndCalendar ID="cal_EndDate" runat="server" />
                </td>
            </tr>
            <tr>
                <td colspan="4">
                    <asp:Button ID="btn_Export" runat="server" Text = "Export Data" OnClick="btn_Export_Click" />
                </td>
            </tr>
            <tr>
                <td colspan="4">
                    <asp:Label ID="lbl_Status" runat="server" CssClass="errorCondition" Text="No data found for the specified criteria" Visible="false" />
                </td>
            </tr>
        </table>

    
</asp:Content> 

OrderExport.ascx.cs

Code: Select all

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using CommerceBuilder.Common;
using CommerceBuilder.Utility;
using CommerceBuilder.Orders;
using CommerceBuilder.Shipping;
using CommerceBuilder.Reporting;
using CommerceBuilder.Data;

public partial class OrderExport : CommerceBuilder.Web.UI.AbleCommerceAdminPage
{

    public DataTable _RptData;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            // default start date
            cal_StartDate.SelectedDate = DateTime.Now.AddDays(-1);
            cal_EndDate.SelectedDate = DateTime.Now.AddDays(-1);
        }

    }

    protected void btn_Export_Click(object sender, EventArgs e)
    {
        // first we bind the data to a data table control
        bool _FoundData = BindData();

        if (!_FoundData)
        {
            lbl_Status.Visible = true;
            return;
        }
        else
            lbl_Status.Visible = false;

        // now export datatable to the browser
        ExportToSpreadsheet(_RptData, "OrderExport");


    }

    private bool BindData()
    {
        // set up the data table
        InitDataTable();

        // pull in orders based on criteria
        DateTime _StartDate = new DateTime();
        DateTime _EndDate = new DateTime();

        if (cal_StartDate.SelectedDate != DateTime.MinValue)
            _StartDate = new DateTime(cal_StartDate.SelectedDate.Year, cal_StartDate.SelectedDate.Month, cal_StartDate.SelectedDate.Day, 0, 0, 0);

        if (cal_EndDate.SelectedDate != DateTime.MinValue)
            _EndDate = new DateTime(cal_EndDate.SelectedDate.Year, cal_EndDate.SelectedDate.Month, cal_EndDate.SelectedDate.Day, 23, 59, 59);

        OrderShipmentCollection _Shipments = LoadForShipDates(_StartDate, _EndDate);

        if (_Shipments.Count == 0)
            return false;
        
        // load up each shipment as separate row in data table
        int _LastOrderNum = 0;
        foreach (OrderShipment _Shipment in _Shipments)
        {
            // if order number has changed since last row, insert blank row
            if (_LastOrderNum != _Shipment.Order.OrderNumber && _LastOrderNum != 0)
            {
                DataRow _BlankRow = _RptData.NewRow();
                _RptData.Rows.Add(_BlankRow);
                _RptData.AcceptChanges();
            }

            // populate order object
            Order _Order = _Shipment.Order;

            // set our last order number value so we can tell when the order changes
            _LastOrderNum = _Order.OrderNumber;

            // loop through each line item on each shipment
            foreach (OrderItem _Item in _Shipment.OrderItems)
            {
                // skip tax line items
                if (_Item.OrderItemType == OrderItemType.Tax)
                    continue;

                DataRow _Row = _RptData.NewRow();

                // set address info
                _Row["Co./Last Name"] = CheckCompanyName(_Order.BillToCompany);
                _Row["First Name"] = string.Empty;
                _Row["Addr 1 - Line 1"] = _Shipment.ShipToFirstName + " " + _Shipment.ShipToLastName;
                _Row["           - Line 2"] = _Shipment.ShipToAddress1 + " " + _Shipment.ShipToAddress2;
                _Row["           - Line 3"] = _Shipment.ShipToCity + ", " + _Shipment.ShipToProvince + " " + _Shipment.ShipToPostalCode + " " + _Shipment.ShipToCountryCode;
                _Row["           - Line 4"] = string.Empty;
                _Row["Invoice #"] = _Shipment.Order.OrderNumber.ToString();
                _Row["Date"] = string.Format("{0:d}", _Shipment.ShipDate);
                _Row["Customer PO"] = _Shipment.Order.OrderNumber.ToString();
                _Row["Ship Via"] = _Shipment.ShipMethodName;
                _Row["Delivery Status"] = "P";
                _Row["Item Number"] = _Item.OrderItemType == OrderItemType.Shipping ? "Shipping" : _Item.Sku;

                // invert quantity if line item is a credit or discount

                if (_Item.Price < 0)
                {
                    _Row["Quantity"] = (_Item.Quantity * -1).ToString();
                    _Row["Price"] = (_Item.Price * -1).ToString("c");
                }
                else
                {
                    _Row["Quantity"] = _Item.Quantity.ToString();
                    _Row["Price"] = _Item.Price.ToString("c");
                }

                _Row["Description"] = _Item.Name;
                _Row["Discount"] = string.Format("{0:c}", 0);
                _Row["Total"] = _Item.ExtendedPrice.ToString("c");
                _Row["Job"] = string.Empty;
                _Row["Comment"] = string.Empty;
                _Row["Journal Memo"] = "Sale; " + _Shipment.ShipToCompany;
                _Row["Salesperson Last Name"] = string.Empty;
                _Row["Salesperson First Name"] = string.Empty;
                _Row["Shipping Date"] = string.Format("{0:d}", _Shipment.ShipDate);
                _Row["Tax Code"] = _Item.TaxCodeId > 0 && _Shipment.Order.BillToProvince.ToUpper() == "TX" ? "STE" : string.Empty;
                _Row["Tax Amount"] = _Item.OrderBy == 0 ? _Order.Items.TotalPriceById((int)OrderItemType.Tax).ToString("ulc") : "$0.00"; // GetSalesTax(_Item);
                _Row["Freight Amount"] = string.Empty;
                _Row["Tax on Freight"] = string.Empty;
                _Row["Freight Tax Amount"] = string.Format("{0:c}", 0);
                _Row["Sale Status"] = "I";
                _Row["Currency Code"] = "USD";
                _Row["Exchange Rate"] = "1";
                _Row["Terms - Payment is Due"] = "1"; 
                _Row["           - Discount Days"] = "0";
                _Row["           - Balance Due Days"] = "0";
                _Row["           - % Discount"] = "0";
                _Row["           - % Monthly Charge"] = "0";
                _Row["Referral Source"] = string.Empty;
                _Row["Amount Paid"] = _Order.TotalPayments.ToString("ulc");

                // identify payment method name based on first payment found for the order
                string _PName = string.Empty;
                if (_Order.Payments.Count > 0)
                    _PName = _Order.Payments[0].PaymentMethodName;

                _Row["Payment Method"] = _PName;
                _Row["Payment Notes"] = string.Empty;
                _Row["Name on Card"] = string.Empty;
                _Row["Card Number"] = string.Empty;
                _Row["Expiration Date"] = string.Empty;
                _Row["Address (AVS)"] = string.Empty;
                _Row["Zip (AVS)"] = string.Empty;
                _Row["Card Verification (CVV2)"] = string.Empty;
                _Row["Authorization Code"] = string.Empty;
                _Row["Check Number"] = string.Empty;
                _Row["Category"] = string.Empty;

                // add row to datatable and commit changes
                _RptData.Rows.Add(_Row);
                _RptData.AcceptChanges();
            } 
 
        }

        // return true since we found data
        return true;
    }

    protected void ExportToSpreadsheet(DataTable table, string name)
    {
        HttpContext context = HttpContext.Current;
        context.Response.Clear();

        // write remaining columns
        foreach (DataColumn column in table.Columns)
            context.Response.Write(column.ColumnName + "\t");

        context.Response.Write(Environment.NewLine);

        // write data rows
        foreach (DataRow row in table.Rows)
        {
            // write remaining data column values
            for (int i = 0; i <= table.Columns.Count - 1; i++)
                context.Response.Write(row[i].ToString().Replace(",", string.Empty) + "\t");

            context.Response.Write(Environment.NewLine);
        }

        // set browser output-type and finish response
        context.Response.ContentType = "text/plain; charset=us-ascii";
        context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".txt");
        context.Response.End();
    }


    private void InitDataTable()
    {
        // initialize table
        _RptData = new DataTable();
        _RptData.Columns.Add("Co./Last Name", typeof(string));
        _RptData.Columns.Add("First Name", typeof(string));
        _RptData.Columns.Add("Addr 1 - Line 1", typeof(string));
        _RptData.Columns.Add("           - Line 2", typeof(string));
        _RptData.Columns.Add("           - Line 3", typeof(string));
        _RptData.Columns.Add("           - Line 4", typeof(string));
        _RptData.Columns.Add("Invoice #", typeof(string));
        _RptData.Columns.Add("Date", typeof(string));
        _RptData.Columns.Add("Customer PO", typeof(string));
        _RptData.Columns.Add("Ship Via", typeof(string));
        _RptData.Columns.Add("Delivery Status", typeof(string));
        _RptData.Columns.Add("Item Number", typeof(string));
        _RptData.Columns.Add("Quantity", typeof(string));
        _RptData.Columns.Add("Description", typeof(string));
        _RptData.Columns.Add("Price", typeof(string));
        _RptData.Columns.Add("Discount", typeof(string));
        _RptData.Columns.Add("Total", typeof(string));
        _RptData.Columns.Add("Job", typeof(string));
        _RptData.Columns.Add("Comment", typeof(string));
        _RptData.Columns.Add("Journal Memo", typeof(string));
        _RptData.Columns.Add("Salesperson Last Name", typeof(string));
        _RptData.Columns.Add("Salesperson First Name", typeof(string));
        _RptData.Columns.Add("Shipping Date", typeof(string));
        _RptData.Columns.Add("Tax Code", typeof(string));
        _RptData.Columns.Add("Tax Amount", typeof(string));
        _RptData.Columns.Add("Freight Amount", typeof(string));
        _RptData.Columns.Add("Tax on Freight", typeof(string));
        _RptData.Columns.Add("Freight Tax Amount", typeof(string));
        _RptData.Columns.Add("Sale Status", typeof(string));
        _RptData.Columns.Add("Currency Code", typeof(string));
        _RptData.Columns.Add("Exchange Rate", typeof(string));
        _RptData.Columns.Add("Terms - Payment is Due", typeof(string));
        _RptData.Columns.Add("           - Discount Days", typeof(string));
        _RptData.Columns.Add("           - Balance Due Days", typeof(string));
        _RptData.Columns.Add("           - % Discount", typeof(string));
        _RptData.Columns.Add("           - % Monthly Charge", typeof(string));
        _RptData.Columns.Add("Referral Source", typeof(string));
        _RptData.Columns.Add("Amount Paid", typeof(string));
        _RptData.Columns.Add("Payment Method", typeof(string));
        _RptData.Columns.Add("Payment Notes", typeof(string));
        _RptData.Columns.Add("Name on Card", typeof(string));
        _RptData.Columns.Add("Card Number", typeof(string));
        _RptData.Columns.Add("Expiration Date", typeof(string));
        _RptData.Columns.Add("Address (AVS)", typeof(string));
        _RptData.Columns.Add("Zip (AVS)", typeof(string));
        _RptData.Columns.Add("Card Verification (CVV2)", typeof(string));
        _RptData.Columns.Add("Authorization Code", typeof(string));
        _RptData.Columns.Add("Check Number", typeof(string));
        _RptData.Columns.Add("Category", typeof(string));
        //_RptData.PrimaryKey = new[] { _RptData.Columns["Key"] };    
    }

    private OrderShipmentCollection LoadForShipDates(DateTime _ShipDateStart, DateTime _ShipDateEnd)
    {
        //CREATE THE DYNAMIC SQL TO LOAD OBJECT
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("SELECT");
        selectQuery.Append(" " + OrderShipment.GetColumnNames(string.Empty));
        selectQuery.Append(" FROM ac_OrderShipments");
        selectQuery.Append(" WHERE 1 = 1");  // dummy where criteria

        if (_ShipDateStart > DateTime.MinValue)
            selectQuery.Append(" AND ShipDate >= @StartDate");

        if (_ShipDateEnd > DateTime.MinValue)
            selectQuery.Append(" AND ShipDate <= @EndDate");

        // connect to Able database
        Database database = Token.Instance.Database;
        DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());

        if (_ShipDateStart > DateTime.MinValue)
            database.AddInParameter(selectCommand, "@StartDate", System.Data.DbType.DateTime, LocaleHelper.FromLocalTime(_ShipDateStart));

        if (_ShipDateEnd > DateTime.MinValue)
            database.AddInParameter(selectCommand, "@EndDate", System.Data.DbType.DateTime, LocaleHelper.FromLocalTime(_ShipDateEnd));

        //EXECUTE THE COMMAND
        OrderShipmentCollection results = new OrderShipmentCollection();
        using (IDataReader dr = database.ExecuteReader(selectCommand))
        {
            while (dr.Read())
            {
                OrderShipment orderShipment = new OrderShipment();
                OrderShipment.LoadDataReader(orderShipment, dr);
                results.Add(orderShipment);
            }
            dr.Close();
        }
        return results;
    }

    private string CheckCompanyName(string _BillCompany)
    {
        // fix company name before search
        string _Crit = _BillCompany.Replace("'", "''");

        //CREATE THE DYNAMIC SQL TO LOAD OBJECT
        StringBuilder selectQuery = new StringBuilder();

        selectQuery.Append("SELECT cmpName");
        selectQuery.Append(" FROM Companies");
        selectQuery.Append(" WHERE cmpName = @Crit");

        Database database = Token.Instance.Database;
        DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());

        database.AddInParameter(selectCommand, "@Crit", System.Data.DbType.String, _Crit);

        //EXECUTE THE COMMAND
        string _RetVal = "Miscellaneous";
        using (IDataReader dr = database.ExecuteReader(selectCommand))
        {
            while (dr.Read())
            {
                _RetVal = (string)dr[0];
                break;
            }
            dr.Close();
        }
        return _RetVal;
    }

    private string GetSalesTax(OrderItem _LineItem)
    {
        // set up return value
        string _RetVal = "$ 0.00";

        // if this lineitem is the first line item, return a tax amount
        // if not, return 0.   We only want total tax on the first line
        // item for each order
        if (_LineItem.OrderBy > 0)
            return _RetVal;

        // total up taxes for specified line item
        LSDecimal _TotalTax = 0;
        foreach (OrderItem _OrderItem in _LineItem.Order.Items)
            _TotalTax += _OrderItem.TaxAmount;

        // exit and return value
        _RetVal = _TotalTax.ToString("ulc");
        return _RetVal;
    }

    


}

[jguengerich]

Assuming you have a button or link somewhere that takes you to it, I don't think anything needs to be changed in the aspx file. The aspx.cs file will obviously need some changes though. I can see several issues:

1) CommerceBuilder.Web.UI.AbleCommerceAdminPage (near the top of the file) will need to be changed to CommerceBuilder.UI.AbleCommerceAdminPage (I figured that out by looking at another admin page's aspx.cs file)

2) I think the OrderShipmentsCollection will have to be changed to an IList<OrderShipment> (which necessitates number 3 below), so:
OrderShipmentCollection _Shipments = LoadForShipDates(_StartDate, _EndDate);
becomes
IList<OrderShipment> _Shipments = LoadForShipDates(_StartDate, _EndDate);

3) The LoadForShipDates method will have to be changed to return a different type, and to use "Gold-compatible" way of accessing the ac_OrderShipments table. Using NHibernate criteria may reduce the amount of code quite a bit.

4) The CheckCompanyName method will need to be changed to to use "Gold-compatible" way of accessing your Companies table.

5) It looks like the GetSalesTax method is not being used (a ternary statement is used instead), so it could be removed.

There's probably some other minor tweaks too. 3) and 4) will be the most significant changes.

I'll try to take a look at it in more detail later and give some more specific help. One question, is the Companies database table used by the CheckCompanyName method a table you added, or is it part of AC 7? I'm guessing you added it, because it doesn't start with "ac_" like the other tables.

[Brewhaus]

Thank you for taking a look at the files. I am afraid that some of the tweaks are beyond me.

Yes, the Companies table was added by us, as it allows us to use the company name on an order to compare and see if the customer has an account in our accounting software (where we import the days' orders into). Essentially, if the name in the Companies table matches the company name on the order, the order is imported to that company's account. If not, then it is imported into 'Miscellaneous'.

[jguengerich]

I did some brief testing, and I think this works. I don't have your Companies table, so I couldn't test the CheckCompanyName part.

Code: Select all

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using CommerceBuilder.Common;
using CommerceBuilder.Utility;
using CommerceBuilder.Orders;
using CommerceBuilder.Shipping;
using CommerceBuilder.Reporting;
using System.Data.SqlClient;
using NHibernate;
using NHibernate.Criterion;
using CommerceBuilder.DomainModel;
using CommerceBuilder.Extensions;

public partial class OrderExport : CommerceBuilder.UI.AbleCommerceAdminPage
{

    public DataTable _RptData;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            // default start date
            cal_StartDate.SelectedDate = DateTime.Now.AddDays(-1);
            cal_EndDate.SelectedDate = DateTime.Now.AddDays(-1);
        }

    }

    protected void btn_Export_Click(object sender, EventArgs e)
    {
        // first we bind the data to a data table control
        bool _FoundData = BindData();

        if (!_FoundData)
        {
            lbl_Status.Visible = true;
            return;
        }
        else
            lbl_Status.Visible = false;

        // now export datatable to the browser
        ExportToSpreadsheet(_RptData, "OrderExport");


    }

    private bool BindData()
    {
        // set up the data table
        InitDataTable();

        // pull in orders based on criteria
        DateTime _StartDate = new DateTime();
        DateTime _EndDate = new DateTime();

        if (cal_StartDate.SelectedDate != DateTime.MinValue)
            _StartDate = new DateTime(cal_StartDate.SelectedDate.Year, cal_StartDate.SelectedDate.Month, cal_StartDate.SelectedDate.Day, 0, 0, 0);

        if (cal_EndDate.SelectedDate != DateTime.MinValue)
            _EndDate = new DateTime(cal_EndDate.SelectedDate.Year, cal_EndDate.SelectedDate.Month, cal_EndDate.SelectedDate.Day, 23, 59, 59);

        IList<OrderShipment> _Shipments = LoadForShipDates(_StartDate, _EndDate);

        if (_Shipments.Count == 0)
            return false;

        // load up each shipment as separate row in data table
        int _LastOrderNum = 0;
        foreach (OrderShipment _Shipment in _Shipments)
        {
            // if order number has changed since last row, insert blank row
            if ((_LastOrderNum != _Shipment.Order.OrderNumber) && _LastOrderNum != 0)
            {
                DataRow _BlankRow = _RptData.NewRow();
                _RptData.Rows.Add(_BlankRow);
                _RptData.AcceptChanges();
            }

            // populate order object
            CommerceBuilder.Orders.Order _Order = _Shipment.Order;

            // set our last order number value so we can tell when the order changes
            _LastOrderNum = _Order.OrderNumber;

            // loop through each line item on each shipment
            foreach (OrderItem _Item in _Shipment.OrderItems)
            {
                // skip tax line items
                if (_Item.OrderItemType == OrderItemType.Tax)
                    continue;

                DataRow _Row = _RptData.NewRow();

                // set address info
                _Row["Co./Last Name"] = CheckCompanyName(_Order.BillToCompany);
                _Row["First Name"] = string.Empty;
                _Row["Addr 1 - Line 1"] = _Shipment.ShipToFirstName + " " + _Shipment.ShipToLastName;
                _Row["           - Line 2"] = _Shipment.ShipToAddress1 + " " + _Shipment.ShipToAddress2;
                _Row["           - Line 3"] = _Shipment.ShipToCity + ", " + _Shipment.ShipToProvince + " " + _Shipment.ShipToPostalCode + " " + _Shipment.ShipToCountryCode;
                _Row["           - Line 4"] = string.Empty;
                _Row["Invoice #"] = _Shipment.Order.OrderNumber.ToString();
                _Row["Date"] = string.Format("{0:d}", _Shipment.ShipDate);
                _Row["Customer PO"] = _Shipment.Order.OrderNumber.ToString();
                _Row["Ship Via"] = _Shipment.ShipMethodName;
                _Row["Delivery Status"] = "P";
                _Row["Item Number"] = _Item.OrderItemType == OrderItemType.Shipping ? "Shipping" : _Item.Sku;

                // invert quantity if line item is a credit or discount

                if (_Item.Price < 0)
                {
                    _Row["Quantity"] = (_Item.Quantity * -1).ToString();
                    _Row["Price"] = (_Item.Price * -1).ToString("c");
                }
                else
                {
                    _Row["Quantity"] = _Item.Quantity.ToString();
                    _Row["Price"] = _Item.Price.ToString("c");
                }

                _Row["Description"] = _Item.Name;
                _Row["Discount"] = string.Format("{0:c}", 0);
                _Row["Total"] = _Item.ExtendedPrice.ToString("c");
                _Row["Job"] = string.Empty;
                _Row["Comment"] = string.Empty;
                _Row["Journal Memo"] = "Sale; " + _Shipment.ShipToCompany;
                _Row["Salesperson Last Name"] = string.Empty;
                _Row["Salesperson First Name"] = string.Empty;
                _Row["Shipping Date"] = string.Format("{0:d}", _Shipment.ShipDate);
                _Row["Tax Code"] = _Item.TaxCodeId > 0 && _Shipment.Order.BillToProvince.ToUpper() == "TX" ? "STE" : string.Empty;
                _Row["Tax Amount"] = _Item.OrderBy == 0 ? _Order.Items.TotalPriceById((int)OrderItemType.Tax).LSCurrencyFormat("ulc") : "$0.00";
                _Row["Freight Amount"] = string.Empty;
                _Row["Tax on Freight"] = string.Empty;
                _Row["Freight Tax Amount"] = string.Format("{0:c}", 0);
                _Row["Sale Status"] = "I";
                _Row["Currency Code"] = "USD";
                _Row["Exchange Rate"] = "1";
                _Row["Terms - Payment is Due"] = "1";
                _Row["           - Discount Days"] = "0";
                _Row["           - Balance Due Days"] = "0";
                _Row["           - % Discount"] = "0";
                _Row["           - % Monthly Charge"] = "0";
                _Row["Referral Source"] = string.Empty;
                _Row["Amount Paid"] = _Order.TotalPayments.LSCurrencyFormat("ulc");

                // identify payment method name based on first payment found for the order
                string _PName = string.Empty;
                if (_Order.Payments.Count > 0)
                    _PName = _Order.Payments[0].PaymentMethodName;

                _Row["Payment Method"] = _PName;
                _Row["Payment Notes"] = string.Empty;
                _Row["Name on Card"] = string.Empty;
                _Row["Card Number"] = string.Empty;
                _Row["Expiration Date"] = string.Empty;
                _Row["Address (AVS)"] = string.Empty;
                _Row["Zip (AVS)"] = string.Empty;
                _Row["Card Verification (CVV2)"] = string.Empty;
                _Row["Authorization Code"] = string.Empty;
                _Row["Check Number"] = string.Empty;
                _Row["Category"] = string.Empty;

                // add row to datatable and commit changes
                _RptData.Rows.Add(_Row);
                _RptData.AcceptChanges();
            }

        }

        // return true since we found data
        return true;
    }

    protected void ExportToSpreadsheet(DataTable table, string name)
    {
        HttpContext context = HttpContext.Current;
        context.Response.Clear();

        // write remaining columns
        foreach (DataColumn column in table.Columns)
            context.Response.Write(column.ColumnName + "\t");

        context.Response.Write(Environment.NewLine);

        // write data rows
        foreach (DataRow row in table.Rows)
        {
            // write remaining data column values
            for (int i = 0; i <= table.Columns.Count - 1; i++)
                context.Response.Write(row[i].ToString().Replace(",", string.Empty) + "\t");

            context.Response.Write(Environment.NewLine);
        }

        // set browser output-type and finish response
        context.Response.ContentType = "text/plain; charset=us-ascii";
        context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".txt");
        context.Response.End();
    }


    private void InitDataTable()
    {
        // initialize table
        _RptData = new DataTable();
        _RptData.Columns.Add("Co./Last Name", typeof(string));
        _RptData.Columns.Add("First Name", typeof(string));
        _RptData.Columns.Add("Addr 1 - Line 1", typeof(string));
        _RptData.Columns.Add("           - Line 2", typeof(string));
        _RptData.Columns.Add("           - Line 3", typeof(string));
        _RptData.Columns.Add("           - Line 4", typeof(string));
        _RptData.Columns.Add("Invoice #", typeof(string));
        _RptData.Columns.Add("Date", typeof(string));
        _RptData.Columns.Add("Customer PO", typeof(string));
        _RptData.Columns.Add("Ship Via", typeof(string));
        _RptData.Columns.Add("Delivery Status", typeof(string));
        _RptData.Columns.Add("Item Number", typeof(string));
        _RptData.Columns.Add("Quantity", typeof(string));
        _RptData.Columns.Add("Description", typeof(string));
        _RptData.Columns.Add("Price", typeof(string));
        _RptData.Columns.Add("Discount", typeof(string));
        _RptData.Columns.Add("Total", typeof(string));
        _RptData.Columns.Add("Job", typeof(string));
        _RptData.Columns.Add("Comment", typeof(string));
        _RptData.Columns.Add("Journal Memo", typeof(string));
        _RptData.Columns.Add("Salesperson Last Name", typeof(string));
        _RptData.Columns.Add("Salesperson First Name", typeof(string));
        _RptData.Columns.Add("Shipping Date", typeof(string));
        _RptData.Columns.Add("Tax Code", typeof(string));
        _RptData.Columns.Add("Tax Amount", typeof(string));
        _RptData.Columns.Add("Freight Amount", typeof(string));
        _RptData.Columns.Add("Tax on Freight", typeof(string));
        _RptData.Columns.Add("Freight Tax Amount", typeof(string));
        _RptData.Columns.Add("Sale Status", typeof(string));
        _RptData.Columns.Add("Currency Code", typeof(string));
        _RptData.Columns.Add("Exchange Rate", typeof(string));
        _RptData.Columns.Add("Terms - Payment is Due", typeof(string));
        _RptData.Columns.Add("           - Discount Days", typeof(string));
        _RptData.Columns.Add("           - Balance Due Days", typeof(string));
        _RptData.Columns.Add("           - % Discount", typeof(string));
        _RptData.Columns.Add("           - % Monthly Charge", typeof(string));
        _RptData.Columns.Add("Referral Source", typeof(string));
        _RptData.Columns.Add("Amount Paid", typeof(string));
        _RptData.Columns.Add("Payment Method", typeof(string));
        _RptData.Columns.Add("Payment Notes", typeof(string));
        _RptData.Columns.Add("Name on Card", typeof(string));
        _RptData.Columns.Add("Card Number", typeof(string));
        _RptData.Columns.Add("Expiration Date", typeof(string));
        _RptData.Columns.Add("Address (AVS)", typeof(string));
        _RptData.Columns.Add("Zip (AVS)", typeof(string));
        _RptData.Columns.Add("Card Verification (CVV2)", typeof(string));
        _RptData.Columns.Add("Authorization Code", typeof(string));
        _RptData.Columns.Add("Check Number", typeof(string));
        _RptData.Columns.Add("Category", typeof(string));
        //_RptData.PrimaryKey = new[] { _RptData.Columns["Key"] };   
    }

    private IList<OrderShipment> LoadForShipDates(DateTime _ShipDateStart, DateTime _ShipDateEnd)
    {
        // variable to return
        IList<OrderShipment> results = new List<OrderShipment>();

        ICriteria criteria = NHibernateHelper.CreateCriteria<CommerceBuilder.Orders.OrderShipment>();
        criteria.Add(Restrictions.Ge("ShipDate", _ShipDateStart));
        criteria.Add(Restrictions.Lt("ShipDate", _ShipDateEnd.Date.AddDays(1)));
        results = OrderShipmentDataSource.LoadForCriteria(criteria);
        return results;
    }


    private string CheckCompanyName(string _BillCompany)
    {
        string _RetVal = "Miscellaneous";

        // fix company name before search
        string _Crit = _BillCompany.Replace("'", "''");

        //CREATE THE DYNAMIC SQL TO LOAD OBJECT
        StringBuilder selectQuery = new StringBuilder();

        selectQuery.Append("SELECT cmpName");
        selectQuery.Append(" FROM Companies");
        selectQuery.Append(" WHERE cmpName = @Crit");

        string ableCommerceCnString = System.Configuration.ConfigurationManager.ConnectionStrings["AbleCommerce"].ConnectionString;
        SqlConnection ableCommerceConnection = new SqlConnection(ableCommerceCnString);
        try
        {
            ableCommerceConnection.Open();
            SqlCommand selectCommand = new SqlCommand(selectQuery.ToString(), ableCommerceConnection);
            selectCommand.CommandType = CommandType.Text;

            SqlParameter oneParam = selectCommand.Parameters.Add("@Crit", System.Data.SqlDbType.NVarChar);
            oneParam.Value = _Crit;

            //EXECUTE THE COMMAND
            using (IDataReader dr = selectCommand.ExecuteReader())
            {
                while (dr.Read())
                {
                    _RetVal = dr.GetString(0);
                    break;
                }
                dr.Close();
            }
            ableCommerceConnection.Close();
        }
        catch (SqlException e)
        {
            Logger.Error("SqlException error attempting to look up company name.", e);
        }
        return _RetVal;
    }
}

If you want more info on the changes, let me know, and I'll try to write some notes in the next couple of days.

[Brewhaus]

That appears to work. Thank you so much for your help. Now we can just start some final testing, and we will be good to move to Gold!

[Brewhaus]

Well, I thought that I could make the changes to create a version of the "OrderExport" that would give me unshipped orders from a given OrderID and greater, by copying the changes from our AC7 version to the Gold version. I was wrong. We use this to pull a list of items that have been ordered but not shipped as this helps us immensely in building our orders to from suppliers.

I got most of it completed, but then hit a bump when the file format changed near the end of the .cs file. My .aspx file and .cs file are below (with the .cs file having the changes up to private IList<OrderShipment> LoadForStartID(int startID). I believe that we just need to get the file to pull where the OrderID is => the ID that we enter and ShipDate=Null. Can anyone help me on this?

.ASPX file

Code: Select all

<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" CodeFile="OrderExport-Unshipped.aspx.cs" Inherits="OrderExport" Title="Order Export" %>

<%@ Register Src="~/Admin/UserControls/PickerAndCalendar.ascx" TagName="PickerAndCalendar" TagPrefix="uc1" %>

<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" Runat="Server">
        <table align="center" class="form" cellpadding="0" cellspacing="0" border="0" width="100%">
            <tr>
                <th class="sectionHeader" colspan="4">
                    <div style="text-align: left">
                        Export Period</div>
                </th>
            </tr>
            <tr>
                <td>
                    <div style="text-align: right; vertical-align: middle">
                        <asp:Label ID="Label1" runat="server" Text="Order ID From:  " SkinID="FieldHeader"></asp:Label>
                    </div>
                </td>
                <td style="text-align: left">
<!--                    <uc1:PickerAndCalendar ID="cal_StartDate" runat="server" />-->
                    <input type="text" ID="startID" runat="server"><br>
                </td>
                <td style="text-align: left">
<!--                    <uc1:PickerAndCalendar ID="cal_EndDate" runat="server" /> -->
                </td>
            </tr>
            <tr>
                <td colspan="4">
                    <asp:Button ID="btn_Export" runat="server" Text = "Export Data" OnClick="btn_Export_Click" />
                </td>
            </tr>
            <tr>
                <td colspan="4">
                    <asp:Label ID="lbl_Status" runat="server" CssClass="errorCondition" Text="No data found for the specified criteria" Visible="false" />
                </td>
            </tr>
        </table>

    
</asp:Content> 

.ASPX.CS file

Code: Select all

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using CommerceBuilder.Common;
using CommerceBuilder.Utility;
using CommerceBuilder.Orders;
using CommerceBuilder.Shipping;
using CommerceBuilder.Reporting;
using System.Data.SqlClient;
using NHibernate;
using NHibernate.Criterion;
using CommerceBuilder.DomainModel;
using CommerceBuilder.Extensions;

public partial class OrderExport : CommerceBuilder.UI.AbleCommerceAdminPage
{

    public DataTable _RptData;
    //public int startID;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            // default start date
//            cal_StartDate.SelectedDate = DateTime.Now.AddDays(-1);
//            cal_EndDate.SelectedDate = DateTime.Now.AddDays(-1);
        }

    }

    protected void btn_Export_Click(object sender, EventArgs e)
    {
        // first we bind the data to a data table control
        bool _FoundData = BindData();

        if (!_FoundData)
        {
            lbl_Status.Visible = true;
            return;
        }
        else
            lbl_Status.Visible = false;

        // now export datatable to the browser
        ExportToSpreadsheet(_RptData, "OrderExport");


    }

    private bool BindData()
    {
        // set up the data table
        InitDataTable();

        // pull in orders based on criteria
//        DateTime _StartDate = new DateTime();
//        DateTime _EndDate = new DateTime();

//        if (cal_StartDate.SelectedDate != DateTime.MinValue)
//            _StartDate = new DateTime(cal_StartDate.SelectedDate.Year, cal_StartDate.SelectedDate.Month, cal_StartDate.SelectedDate.Day, 0, 0, 0);

//        if (cal_EndDate.SelectedDate != DateTime.MinValue)
//            _EndDate = new DateTime(cal_EndDate.SelectedDate.Year, cal_EndDate.SelectedDate.Month, cal_EndDate.SelectedDate.Day, 23, 59, 59);

        IList<OrderShipment> _Shipments = LoadForStartID(Convert.ToInt32(startID.Value));

        if (_Shipments.Count == 0)
            return false;

        // load up each shipment as separate row in data table
        int _LastOrderNum = 0;
        foreach (OrderShipment _Shipment in _Shipments)
        {
            // if order number has changed since last row, insert blank row
            if ((_LastOrderNum != _Shipment.Order.OrderNumber) && _LastOrderNum != 0)
            {
                DataRow _BlankRow = _RptData.NewRow();
                _RptData.Rows.Add(_BlankRow);
                _RptData.AcceptChanges();
            }

            // populate order object
            CommerceBuilder.Orders.Order _Order = _Shipment.Order;

            // set our last order number value so we can tell when the order changes
            _LastOrderNum = _Order.OrderNumber;

            // loop through each line item on each shipment
            foreach (OrderItem _Item in _Shipment.OrderItems)
            {
                // skip tax line items
                if (_Item.OrderItemType == OrderItemType.Tax)
                    continue;

                DataRow _Row = _RptData.NewRow();

                // set address info
                _Row["Co./Last Name"] = CheckCompanyName(_Order.BillToCompany);
                _Row["First Name"] = string.Empty;
                _Row["Addr 1 - Line 1"] = _Shipment.ShipToFirstName + " " + _Shipment.ShipToLastName;
                _Row["           - Line 2"] = _Shipment.ShipToAddress1 + " " + _Shipment.ShipToAddress2;
                _Row["           - Line 3"] = _Shipment.ShipToCity + ", " + _Shipment.ShipToProvince + " " + _Shipment.ShipToPostalCode + " " + _Shipment.ShipToCountryCode;
                _Row["           - Line 4"] = string.Empty;
                _Row["Invoice #"] = _Shipment.Order.OrderNumber.ToString();
                _Row["Date"] = string.Format("{0:d}", _Shipment.ShipDate);
                _Row["Customer PO"] = _Shipment.Order.OrderNumber.ToString();
                _Row["Ship Via"] = _Shipment.ShipMethodName;
                _Row["Delivery Status"] = "P";
                _Row["Item Number"] = _Item.OrderItemType == OrderItemType.Shipping ? "Shipping" : _Item.Sku;

                // invert quantity if line item is a credit or discount

                if (_Item.Price < 0)
                {
                    _Row["Quantity"] = (_Item.Quantity * -1).ToString();
                    _Row["Price"] = (_Item.Price * -1).ToString("c");
                }
                else
                {
                    _Row["Quantity"] = _Item.Quantity.ToString();
                    _Row["Price"] = _Item.Price.ToString("c");
                }

                _Row["Description"] = _Item.Name;
                _Row["Discount"] = string.Format("{0:c}", 0);
                _Row["Total"] = _Item.ExtendedPrice.ToString("c");
                _Row["Job"] = string.Empty;
                _Row["Comment"] = string.Empty;
                _Row["Journal Memo"] = "Sale; " + _Shipment.ShipToCompany;
                _Row["Salesperson Last Name"] = string.Empty;
                _Row["Salesperson First Name"] = string.Empty;
                _Row["Shipping Date"] = string.Format("{0:d}", _Shipment.ShipDate);
                _Row["Tax Code"] = _Item.TaxCodeId > 0 && _Shipment.Order.BillToProvince.ToUpper() == "TX" ? "STE" : string.Empty;
                _Row["Tax Amount"] = _Item.OrderBy == 0 ? _Order.Items.TotalPriceById((int)OrderItemType.Tax).LSCurrencyFormat("ulc") : "$0.00";
                _Row["Freight Amount"] = string.Empty;
                _Row["Tax on Freight"] = string.Empty;
                _Row["Freight Tax Amount"] = string.Format("{0:c}", 0);
                _Row["Sale Status"] = "I";
                _Row["Currency Code"] = "USD";
                _Row["Exchange Rate"] = "1";
                _Row["Terms - Payment is Due"] = "1";
                _Row["           - Discount Days"] = "0";
                _Row["           - Balance Due Days"] = "0";
                _Row["           - % Discount"] = "0";
                _Row["           - % Monthly Charge"] = "0";
                _Row["Referral Source"] = string.Empty;
                _Row["Amount Paid"] = _Order.TotalPayments.LSCurrencyFormat("ulc");

                // identify payment method name based on first payment found for the order
                string _PName = string.Empty;
                if (_Order.Payments.Count > 0)
                    _PName = _Order.Payments[0].PaymentMethodName;

                _Row["Payment Method"] = _PName;
                _Row["Payment Notes"] = string.Empty;
                _Row["Name on Card"] = string.Empty;
                _Row["Card Number"] = string.Empty;
                _Row["Expiration Date"] = string.Empty;
                _Row["Address (AVS)"] = string.Empty;
                _Row["Zip (AVS)"] = string.Empty;
                _Row["Card Verification (CVV2)"] = string.Empty;
                _Row["Authorization Code"] = string.Empty;
                _Row["Check Number"] = string.Empty;
                _Row["Category"] = string.Empty;

                // add row to datatable and commit changes
                _RptData.Rows.Add(_Row);
                _RptData.AcceptChanges();
            }

        }

        // return true since we found data
        return true;
    }

    protected void ExportToSpreadsheet(DataTable table, string name)
    {
        HttpContext context = HttpContext.Current;
        context.Response.Clear();

        // write remaining columns
        foreach (DataColumn column in table.Columns)
            context.Response.Write(column.ColumnName + "\t");

        context.Response.Write(Environment.NewLine);

        // write data rows
        foreach (DataRow row in table.Rows)
        {
            // write remaining data column values
            for (int i = 0; i <= table.Columns.Count - 1; i++)
                context.Response.Write(row[i].ToString().Replace(",", string.Empty) + "\t");

            context.Response.Write(Environment.NewLine);
        }

        // set browser output-type and finish response
        context.Response.ContentType = "text/plain; charset=us-ascii";
        context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".txt");
        context.Response.End();
    }


    private void InitDataTable()
    {
        // initialize table
        _RptData = new DataTable();
        _RptData.Columns.Add("Co./Last Name", typeof(string));
        _RptData.Columns.Add("First Name", typeof(string));
        _RptData.Columns.Add("Addr 1 - Line 1", typeof(string));
        _RptData.Columns.Add("           - Line 2", typeof(string));
        _RptData.Columns.Add("           - Line 3", typeof(string));
        _RptData.Columns.Add("           - Line 4", typeof(string));
        _RptData.Columns.Add("Invoice #", typeof(string));
        _RptData.Columns.Add("Date", typeof(string));
        _RptData.Columns.Add("Customer PO", typeof(string));
        _RptData.Columns.Add("Ship Via", typeof(string));
        _RptData.Columns.Add("Delivery Status", typeof(string));
        _RptData.Columns.Add("Item Number", typeof(string));
        _RptData.Columns.Add("Quantity", typeof(string));
        _RptData.Columns.Add("Description", typeof(string));
        _RptData.Columns.Add("Price", typeof(string));
        _RptData.Columns.Add("Discount", typeof(string));
        _RptData.Columns.Add("Total", typeof(string));
        _RptData.Columns.Add("Job", typeof(string));
        _RptData.Columns.Add("Comment", typeof(string));
        _RptData.Columns.Add("Journal Memo", typeof(string));
        _RptData.Columns.Add("Salesperson Last Name", typeof(string));
        _RptData.Columns.Add("Salesperson First Name", typeof(string));
        _RptData.Columns.Add("Shipping Date", typeof(string));
        _RptData.Columns.Add("Tax Code", typeof(string));
        _RptData.Columns.Add("Tax Amount", typeof(string));
        _RptData.Columns.Add("Freight Amount", typeof(string));
        _RptData.Columns.Add("Tax on Freight", typeof(string));
        _RptData.Columns.Add("Freight Tax Amount", typeof(string));
        _RptData.Columns.Add("Sale Status", typeof(string));
        _RptData.Columns.Add("Currency Code", typeof(string));
        _RptData.Columns.Add("Exchange Rate", typeof(string));
        _RptData.Columns.Add("Terms - Payment is Due", typeof(string));
        _RptData.Columns.Add("           - Discount Days", typeof(string));
        _RptData.Columns.Add("           - Balance Due Days", typeof(string));
        _RptData.Columns.Add("           - % Discount", typeof(string));
        _RptData.Columns.Add("           - % Monthly Charge", typeof(string));
        _RptData.Columns.Add("Referral Source", typeof(string));
        _RptData.Columns.Add("Amount Paid", typeof(string));
        _RptData.Columns.Add("Payment Method", typeof(string));
        _RptData.Columns.Add("Payment Notes", typeof(string));
        _RptData.Columns.Add("Name on Card", typeof(string));
        _RptData.Columns.Add("Card Number", typeof(string));
        _RptData.Columns.Add("Expiration Date", typeof(string));
        _RptData.Columns.Add("Address (AVS)", typeof(string));
        _RptData.Columns.Add("Zip (AVS)", typeof(string));
        _RptData.Columns.Add("Card Verification (CVV2)", typeof(string));
        _RptData.Columns.Add("Authorization Code", typeof(string));
        _RptData.Columns.Add("Check Number", typeof(string));
        _RptData.Columns.Add("Category", typeof(string));
        //_RptData.PrimaryKey = new[] { _RptData.Columns["Key"] };   
    }

    private IList<OrderShipment> LoadForStartID(int startID)
    {
        // variable to return
        IList<OrderShipment> results = new List<OrderShipment>();

        ICriteria criteria = NHibernateHelper.CreateCriteria<CommerceBuilder.Orders.OrderShipment>();
        criteria.Add(Restrictions.Ge("ShipDate", _ShipDateStart));
        criteria.Add(Restrictions.Lt("ShipDate", _ShipDateEnd.Date.AddDays(1)));
        results = OrderShipmentDataSource.LoadForCriteria(criteria);
        return results;
    }


    private string CheckCompanyName(string _BillCompany)
    {
        string _RetVal = "Miscellaneous";

        // fix company name before search
        string _Crit = _BillCompany.Replace("'", "''");

        //CREATE THE DYNAMIC SQL TO LOAD OBJECT
        StringBuilder selectQuery = new StringBuilder();

        selectQuery.Append("SELECT cmpName");
        selectQuery.Append(" FROM Companies");
        selectQuery.Append(" WHERE cmpName = @Crit");

        string ableCommerceCnString = System.Configuration.ConfigurationManager.ConnectionStrings["AbleCommerce"].ConnectionString;
        SqlConnection ableCommerceConnection = new SqlConnection(ableCommerceCnString);
        try
        {
            ableCommerceConnection.Open();
            SqlCommand selectCommand = new SqlCommand(selectQuery.ToString(), ableCommerceConnection);
            selectCommand.CommandType = CommandType.Text;

            SqlParameter oneParam = selectCommand.Parameters.Add("@Crit", System.Data.SqlDbType.NVarChar);
            oneParam.Value = _Crit;

            //EXECUTE THE COMMAND
            using (IDataReader dr = selectCommand.ExecuteReader())
            {
                while (dr.Read())
                {
                    _RetVal = dr.GetString(0);
                    break;
                }
                dr.Close();
            }
            ableCommerceConnection.Close();
        }
        catch (SqlException e)
        {
            Logger.Error("SqlException error attempting to look up company name.", e);
        }
        return _RetVal;
    }
}

AC7 .ASPX.CS file (to see how this was accomplished in AC7)

Code: Select all

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using CommerceBuilder.Common;
using CommerceBuilder.Utility;
using CommerceBuilder.Orders;
using CommerceBuilder.Shipping;
using CommerceBuilder.Reporting;
using CommerceBuilder.Data;

public partial class OrderExport : CommerceBuilder.Web.UI.AbleCommerceAdminPage
{

    public DataTable _RptData;
    //public int startID;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            // default start date
//            cal_StartDate.SelectedDate = DateTime.Now.AddDays(-1);
//            cal_EndDate.SelectedDate = DateTime.Now.AddDays(-1);
        }

    }

    protected void btn_Export_Click(object sender, EventArgs e)
    {
        // first we bind the data to a data table control
        bool _FoundData = BindData();

        if (!_FoundData)
        {
            lbl_Status.Visible = true;
            return;
        }
        else
            lbl_Status.Visible = false;

        // now export datatable to the browser
        ExportToSpreadsheet(_RptData, "OrderExport");


    }

    private bool BindData()
    {
        // set up the data table
        InitDataTable();

        // pull in orders based on criteria
//        DateTime _StartDate = new DateTime();
//        DateTime _EndDate = new DateTime();

//        if (cal_StartDate.SelectedDate != DateTime.MinValue)
//            _StartDate = new DateTime(cal_StartDate.SelectedDate.Year, cal_StartDate.SelectedDate.Month, cal_StartDate.SelectedDate.Day, 0, 0, 0);

//        if (cal_EndDate.SelectedDate != DateTime.MinValue)
//            _EndDate = new DateTime(cal_EndDate.SelectedDate.Year, cal_EndDate.SelectedDate.Month, cal_EndDate.SelectedDate.Day, 23, 59, 59);

        OrderShipmentCollection _Shipments = LoadForStartID(Convert.ToInt32(startID.Value));

        if (_Shipments.Count == 0)
            return false;
        
        // load up each shipment as separate row in data table
        int _LastOrderNum = 0;
        foreach (OrderShipment _Shipment in _Shipments)
        {
            // if order number has changed since last row, insert blank row
            if (_LastOrderNum != _Shipment.Order.OrderNumber && _LastOrderNum != 0)
            {
                DataRow _BlankRow = _RptData.NewRow();
                _RptData.Rows.Add(_BlankRow);
                _RptData.AcceptChanges();
            }

            // populate order object
            Order _Order = _Shipment.Order;

            // set our last order number value so we can tell when the order changes
            _LastOrderNum = _Order.OrderNumber;

            // loop through each line item on each shipment
            foreach (OrderItem _Item in _Shipment.OrderItems)
            {
                // skip tax line items
                if (_Item.OrderItemType == OrderItemType.Tax)
                    continue;

                DataRow _Row = _RptData.NewRow();

                // set address info
                _Row["Co./Last Name"] = CheckCompanyName(_Order.BillToCompany);
                _Row["First Name"] = string.Empty;
                _Row["Addr 1 - Line 1"] = _Shipment.ShipToFirstName + " " + _Shipment.ShipToLastName;
                _Row["           - Line 2"] = _Shipment.ShipToAddress1 + " " + _Shipment.ShipToAddress2;
                _Row["           - Line 3"] = _Shipment.ShipToCity + ", " + _Shipment.ShipToProvince + " " + _Shipment.ShipToPostalCode + " " + _Shipment.ShipToCountryCode;
                _Row["           - Line 4"] = string.Empty;
                _Row["Invoice #"] = _Shipment.Order.OrderNumber.ToString();
                _Row["Date"] = string.Format("{0:d}", _Shipment.ShipDate);
                _Row["Customer PO"] = _Shipment.Order.OrderNumber.ToString();
                _Row["Ship Via"] = _Shipment.ShipMethodName;
                _Row["Delivery Status"] = "P";
                _Row["Item Number"] = _Item.OrderItemType == OrderItemType.Shipping ? "Shipping" : _Item.Sku;

                // invert quantity if line item is a credit or discount

                if (_Item.Price < 0)
                {
                    _Row["Quantity"] = (_Item.Quantity * -1).ToString();
                    _Row["Price"] = (_Item.Price * -1).ToString("c");
                }
                else
                {
                    _Row["Quantity"] = _Item.Quantity.ToString();
                    _Row["Price"] = _Item.Price.ToString("c");
                }

                _Row["Description"] = _Item.Name;
                _Row["Discount"] = string.Format("{0:c}", 0);
                _Row["Total"] = _Item.ExtendedPrice.ToString("c");
                _Row["Job"] = string.Empty;
                _Row["Comment"] = string.Empty;
                _Row["Journal Memo"] = "Sale; " + _Shipment.ShipToCompany;
                _Row["Salesperson Last Name"] = string.Empty;
                _Row["Salesperson First Name"] = string.Empty;
                _Row["Shipping Date"] = string.Format("{0:d}", _Shipment.ShipDate);
                _Row["Tax Code"] = _Item.TaxCodeId > 0 && _Shipment.Order.BillToProvince.ToUpper() == "TX" ? "STE" : string.Empty;
                _Row["Tax Amount"] = _Item.OrderBy == 0 ? _Order.Items.TotalPriceById((int)OrderItemType.Tax).ToString("ulc") : "$0.00"; // GetSalesTax(_Item);
                _Row["Freight Amount"] = string.Empty;
                _Row["Tax on Freight"] = string.Empty;
                _Row["Freight Tax Amount"] = string.Format("{0:c}", 0);
                _Row["Sale Status"] = "I";
                _Row["Currency Code"] = "USD";
                _Row["Exchange Rate"] = "1";
                _Row["Terms - Payment is Due"] = "1"; 
                _Row["           - Discount Days"] = "0";
                _Row["           - Balance Due Days"] = "0";
                _Row["           - % Discount"] = "0";
                _Row["           - % Monthly Charge"] = "0";
                _Row["Referral Source"] = string.Empty;
                _Row["Amount Paid"] = _Order.TotalPayments.ToString("ulc");

                // identify payment method name based on first payment found for the order
                string _PName = string.Empty;
                if (_Order.Payments.Count > 0)
                    _PName = _Order.Payments[0].PaymentMethodName;

                _Row["Payment Method"] = _PName;
                _Row["Payment Notes"] = string.Empty;
                _Row["Name on Card"] = string.Empty;
                _Row["Card Number"] = string.Empty;
                _Row["Expiration Date"] = string.Empty;
                _Row["Address (AVS)"] = string.Empty;
                _Row["Zip (AVS)"] = string.Empty;
                _Row["Card Verification (CVV2)"] = string.Empty;
                _Row["Authorization Code"] = string.Empty;
                _Row["Check Number"] = string.Empty;
                _Row["Category"] = string.Empty;

                // add row to datatable and commit changes
                _RptData.Rows.Add(_Row);
                _RptData.AcceptChanges();
            } 
 
        }

        // return true since we found data
        return true;
    }

    protected void ExportToSpreadsheet(DataTable table, string name)
    {
        HttpContext context = HttpContext.Current;
        context.Response.Clear();

        // write remaining columns
        foreach (DataColumn column in table.Columns)
            context.Response.Write(column.ColumnName + "\t");

        context.Response.Write(Environment.NewLine);

        // write data rows
        foreach (DataRow row in table.Rows)
        {
            // write remaining data column values
            for (int i = 0; i <= table.Columns.Count - 1; i++)
                context.Response.Write(row[i].ToString().Replace(",", string.Empty) + "\t");

            context.Response.Write(Environment.NewLine);
        }

        // set browser output-type and finish response
        context.Response.ContentType = "text/plain; charset=us-ascii";
        context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".txt");
        context.Response.End();
    }


    private void InitDataTable()
    {
        // initialize table
        _RptData = new DataTable();
        _RptData.Columns.Add("Co./Last Name", typeof(string));
        _RptData.Columns.Add("First Name", typeof(string));
        _RptData.Columns.Add("Addr 1 - Line 1", typeof(string));
        _RptData.Columns.Add("           - Line 2", typeof(string));
        _RptData.Columns.Add("           - Line 3", typeof(string));
        _RptData.Columns.Add("           - Line 4", typeof(string));
        _RptData.Columns.Add("Invoice #", typeof(string));
        _RptData.Columns.Add("Date", typeof(string));
        _RptData.Columns.Add("Customer PO", typeof(string));
        _RptData.Columns.Add("Ship Via", typeof(string));
        _RptData.Columns.Add("Delivery Status", typeof(string));
        _RptData.Columns.Add("Item Number", typeof(string));
        _RptData.Columns.Add("Quantity", typeof(string));
        _RptData.Columns.Add("Description", typeof(string));
        _RptData.Columns.Add("Price", typeof(string));
        _RptData.Columns.Add("Discount", typeof(string));
        _RptData.Columns.Add("Total", typeof(string));
        _RptData.Columns.Add("Job", typeof(string));
        _RptData.Columns.Add("Comment", typeof(string));
        _RptData.Columns.Add("Journal Memo", typeof(string));
        _RptData.Columns.Add("Salesperson Last Name", typeof(string));
        _RptData.Columns.Add("Salesperson First Name", typeof(string));
        _RptData.Columns.Add("Shipping Date", typeof(string));
        _RptData.Columns.Add("Tax Code", typeof(string));
        _RptData.Columns.Add("Tax Amount", typeof(string));
        _RptData.Columns.Add("Freight Amount", typeof(string));
        _RptData.Columns.Add("Tax on Freight", typeof(string));
        _RptData.Columns.Add("Freight Tax Amount", typeof(string));
        _RptData.Columns.Add("Sale Status", typeof(string));
        _RptData.Columns.Add("Currency Code", typeof(string));
        _RptData.Columns.Add("Exchange Rate", typeof(string));
        _RptData.Columns.Add("Terms - Payment is Due", typeof(string));
        _RptData.Columns.Add("           - Discount Days", typeof(string));
        _RptData.Columns.Add("           - Balance Due Days", typeof(string));
        _RptData.Columns.Add("           - % Discount", typeof(string));
        _RptData.Columns.Add("           - % Monthly Charge", typeof(string));
        _RptData.Columns.Add("Referral Source", typeof(string));
        _RptData.Columns.Add("Amount Paid", typeof(string));
        _RptData.Columns.Add("Payment Method", typeof(string));
        _RptData.Columns.Add("Payment Notes", typeof(string));
        _RptData.Columns.Add("Name on Card", typeof(string));
        _RptData.Columns.Add("Card Number", typeof(string));
        _RptData.Columns.Add("Expiration Date", typeof(string));
        _RptData.Columns.Add("Address (AVS)", typeof(string));
        _RptData.Columns.Add("Zip (AVS)", typeof(string));
        _RptData.Columns.Add("Card Verification (CVV2)", typeof(string));
        _RptData.Columns.Add("Authorization Code", typeof(string));
        _RptData.Columns.Add("Check Number", typeof(string));
        _RptData.Columns.Add("Category", typeof(string));
        //_RptData.PrimaryKey = new[] { _RptData.Columns["Key"] };    
    }

    private OrderShipmentCollection LoadForStartID(int startID)
    {
        //CREATE THE DYNAMIC SQL TO LOAD OBJECT
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("SELECT");
        selectQuery.Append(" " + OrderShipment.GetColumnNames(string.Empty));
        selectQuery.Append(" FROM ac_OrderShipments");
//        selectQuery.Append(" WHERE 1 = 1");  // dummy where criteria
	   selectQuery.Append(" WHERE ShipDate IS NULL");

//        if (_ShipDateStart > DateTime.MinValue)
//            selectQuery.Append(" AND ShipDate >= @StartDate");

//        if (_ShipDateEnd > DateTime.MinValue)
//            selectQuery.Append(" AND ShipDate <= @EndDate");

	selectQuery.Append(" AND OrderID >= " + startID.ToString());

        // connect to Able database
        Database database = Token.Instance.Database;
        DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());

//        if (_ShipDateStart > DateTime.MinValue)
//            database.AddInParameter(selectCommand, "@StartDate", System.Data.DbType.DateTime, LocaleHelper.FromLocalTime(_ShipDateStart));

//        if (_ShipDateEnd > DateTime.MinValue)
//            database.AddInParameter(selectCommand, "@EndDate", System.Data.DbType.DateTime, LocaleHelper.FromLocalTime(_ShipDateEnd));

        //EXECUTE THE COMMAND
        OrderShipmentCollection results = new OrderShipmentCollection();
        using (IDataReader dr = database.ExecuteReader(selectCommand))
        {
            while (dr.Read())
            {
                OrderShipment orderShipment = new OrderShipment();
                OrderShipment.LoadDataReader(orderShipment, dr);
                results.Add(orderShipment);
            }
            dr.Close();
        }
        return results;
    }

    private string CheckCompanyName(string _BillCompany)
    {
        // fix company name before search
        string _Crit = _BillCompany.Replace("'", "''");

        //CREATE THE DYNAMIC SQL TO LOAD OBJECT
        StringBuilder selectQuery = new StringBuilder();

        selectQuery.Append("SELECT cmpName");
        selectQuery.Append(" FROM Companies");
        selectQuery.Append(" WHERE cmpName = @Crit");

        Database database = Token.Instance.Database;
        DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());

        database.AddInParameter(selectCommand, "@Crit", System.Data.DbType.String, _Crit);

        //EXECUTE THE COMMAND
        string _RetVal = "Miscellaneous";
        using (IDataReader dr = database.ExecuteReader(selectCommand))
        {
            while (dr.Read())
            {
                _RetVal = (string)dr[0];
                break;
            }
            dr.Close();
        }
        return _RetVal;
    }

    private string GetSalesTax(OrderItem _LineItem)
    {
        // set up return value
        string _RetVal = "$ 0.00";

        // if this lineitem is the first line item, return a tax amount
        // if not, return 0.   We only want total tax on the first line
        // item for each order
        if (_LineItem.OrderBy > 0)
            return _RetVal;

        // total up taxes for specified line item
        LSDecimal _TotalTax = 0;
        foreach (OrderItem _OrderItem in _LineItem.Order.Items)
            _TotalTax += _OrderItem.TaxAmount;

        // exit and return value
        _RetVal = _TotalTax.ToString("ulc");
        return _RetVal;
    }

    


}

[jguengerich]

The syntax for the NHibernate criteria is a bit cryptic, but it does allow you to write compact code. A few of the common restrictions for criteria are:
Restrictions.Lt means less than
Restrictions.Le means less than or equal to
Restrictions.Eq means equal
Restrictions.Ge means greater than or equal to
Restrictions.Gt means greater than
Restrictions.IsNull means is null (OK, so that one's a little more obvious )

So you'll need to replace the criteria restrictions:

Code: Select all

        criteria.Add(Restrictions.Ge("ShipDate", _ShipDateStart));
        criteria.Add(Restrictions.Lt("ShipDate", _ShipDateEnd.Date.AddDays(1)));

with

Code: Select all

        criteria.Add(Restrictions.Ge("OrderId", startID));  // include shipments where OrderId >= startID...
        criteria.Add(Restrictions.IsNull("ShipDate"));      // ...and ShipDate is null

You may want to use Order Number instead of Order Id, since technically they don't have to be the same. I think the changes would be minor, but I'd want to be at a computer with AC on it so I could test the code before suggesting it.

[Brewhaus]

I agree that using Order Number would be much easier, especially now that the OrderID is not shown in the URL when we go to the order page, but that would require additional coding I expect, as the OrderNumber is not in the same table (so it would require cross-referencing).

I implemented the change above, and get the following error:

Source Error:


The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:

1. Add a "Debug=true" directive at the top of the file that generated the error. Example:

<%@ Page Language="C#" Debug="true" %>

or:

2) Add the following section to the configuration file of your application:

<configuration>
<system.web>
<compilation debug="true"/>
</system.web>
</configuration>

Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.

Stack Trace:



[SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.]
System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) +2177821
System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) +232
System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) +46
System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) +5056593
System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) +6509
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() +12
NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) +292
NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) +244
NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +186
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +129
NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) +116

[GenericADOException: could not execute query
[ SELECT this_.OrderShipmentId as OrderShi1_40_0_, this_.OrderId as OrderId40_0_, this_.WarehouseId as Warehous3_40_0_, this_.ShipToFirstName as ShipToFi4_40_0_, this_.ShipToLastName as ShipToLa5_40_0_, this_.ShipToCompany as ShipToCo6_40_0_, this_.ShipToAddress1 as ShipToAd7_40_0_, this_.ShipToAddress2 as ShipToAd8_40_0_, this_.ShipToCity as ShipToCity40_0_, this_.ShipToProvince as ShipToP10_40_0_, this_.ShipToPostalCode as ShipToP11_40_0_, this_.ShipToCountryCode as ShipToC12_40_0_, this_.ShipToPhone as ShipToP13_40_0_, this_.ShipToFax as ShipToFax40_0_, this_.ShipToEmail as ShipToE15_40_0_, this_.ShipToResidence as ShipToR16_40_0_, this_.ShipMethodId as ShipMet17_40_0_, this_.ShipMethodName as ShipMet18_40_0_, this_.ShipMessage as ShipMes19_40_0_, this_.ShipDate as ShipDate40_0_ FROM ac_OrderShipments this_ WHERE this_.ShipDate >= @p0 and this_.ShipDate < @p1 ]
Name:cp0 - Value:1/1/0001 12:00:00 AM Name:cp1 - Value:1/2/0001 12:00:00 AM
[SQL: SELECT this_.OrderShipmentId as OrderShi1_40_0_, this_.OrderId as OrderId40_0_, this_.WarehouseId as Warehous3_40_0_, this_.ShipToFirstName as ShipToFi4_40_0_, this_.ShipToLastName as ShipToLa5_40_0_, this_.ShipToCompany as ShipToCo6_40_0_, this_.ShipToAddress1 as ShipToAd7_40_0_, this_.ShipToAddress2 as ShipToAd8_40_0_, this_.ShipToCity as ShipToCity40_0_, this_.ShipToProvince as ShipToP10_40_0_, this_.ShipToPostalCode as ShipToP11_40_0_, this_.ShipToCountryCode as ShipToC12_40_0_, this_.ShipToPhone as ShipToP13_40_0_, this_.ShipToFax as ShipToFax40_0_, this_.ShipToEmail as ShipToE15_40_0_, this_.ShipToResidence as ShipToR16_40_0_, this_.ShipMethodId as ShipMet17_40_0_, this_.ShipMethodName as ShipMet18_40_0_, this_.ShipMessage as ShipMes19_40_0_, this_.ShipDate as ShipDate40_0_ FROM ac_OrderShipments this_ WHERE this_.ShipDate >= @p0 and this_.ShipDate < @p1]]
NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) +213
NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) +18
NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) +79
NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) +560
NHibernate.Impl.CriteriaImpl.List(IList results) +56
NHibernate.Impl.CriteriaImpl.List() +58
CommerceBuilder.DomainModel.RepositoryWithTypedId`2.LoadForCriteria(ICriteria criteria) +49
CommerceBuilder.DomainModel.DataSourceWithTypedId`3.LoadForCriteria(ICriteria criteria) +63
OrderExport.LoadForShipDates(DateTime _ShipDateStart, DateTime _ShipDateEnd) +191
OrderExport.BindData() +729
OrderExport.btn_Export_Click(Object sender, EventArgs e) +13
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

[jguengerich]

Oops, it is still "linked" to the OrderExport page.
Near the top of the OrderExport-Unshipped.aspx.cs file, change the class definition from:

Code: Select all

public partial class OrderExport : CommerceBuilder.UI.AbleCommerceAdminPage

to

Code: Select all

public partial class OrderExport-Unshipped : CommerceBuilder.UI.AbleCommerceAdminPage

At the top of the OrderExport-Unshipped.aspx file, change:

Code: Select all

<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" CodeFile="OrderExport-Unshipped.aspx.cs" Inherits="OrderExport" Title="Order Export" %>

to

Code: Select all

<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" CodeFile="OrderExport-Unshipped.aspx.cs" Inherits="OrderExport-Unshipped" Title="Order Export" %>

The Inherits="" argument of the Page item in the .axpx file should match the class name in the .cs file, which by convention is usually the "base" part of the file name.

[Brewhaus]

When I make these changes I get the following error:

Parser Error Message: 'OrderExport-Unshipped' is not a valid value for attribute 'inherits'.

Source Error:



Line 1: <%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" CodeFile="OrderExport-Unshipped.aspx.cs" Inherits="OrderExport-Unshipped" Title="Order Export" %>
Line 2:
Line 3: <%@ Register Src="~/Admin/UserControls/PickerAndCalendar.ascx" TagName="PickerAndCalendar" TagPrefix="uc1" %>


Source File: /Admin/orderexport-unshipped.aspx Line: 1


I can resolve the error by changing the Inherits (removing -Unshipped) and changing the partial class (removing -Unshipped) then I can get to the page, but get the error noted previously when we try to run the script.

[jguengerich]

I guess it doesn't like "-" (dash) in a class name. You could use "_" (underscore). Make sure to change it both places. To strictly follow convention, you could change the file names to use the underscore also.

[Brewhaus]

Below are the snipets from the AC7 files (currently being used). This is what we are using in the same place in the Gold files, so I assume that they should be fine. Thoughts?

AC7 .aspx file:

Code: Select all

<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" CodeFile="OrderExport-Unshipped.aspx.cs" Inherits="OrderExport" Title="Order Export" %>

AC7 .aspx.cs file:

Code: Select all

public partial class OrderExport : CommerceBuilder.Web.UI.AbleCommerceAdminPage

[jguengerich]

Did you try it with OrderExport_Unshipped instead of OrderExport-Unshipped?

[Brewhaus]

I just tried and get the error below.


could not resolve property: OrderId of: CommerceBuilder.Orders.OrderShipment
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: NHibernate.QueryException: could not resolve property: OrderId of: CommerceBuilder.Orders.OrderShipment

Source Error:


The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:

1. Add a "Debug=true" directive at the top of the file that generated the error. Example:

<%@ Page Language="C#" Debug="true" %>

or:

2) Add the following section to the configuration file of your application:

<configuration>
<system.web>
<compilation debug="true"/>
</system.web>
</configuration>

Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.

Stack Trace:



[QueryException: could not resolve property: OrderId of: CommerceBuilder.Orders.OrderShipment]
NHibernate.Persister.Entity.AbstractPropertyMapping.ToType(String propertyName) +59
NHibernate.Persister.Entity.AbstractEntityPersister.GetSubclassPropertyTableNumber(String propertyPath) +34
NHibernate.Persister.Entity.BasicEntityPropertyMapping.ToColumns(String alias, String propertyName) +25
NHibernate.Persister.Entity.AbstractEntityPersister.ToColumns(String alias, String propertyName) +21
NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetColumns(ICriteria subcriteria, String propertyName) +76
NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetColumnsUsingProjection(ICriteria subcriteria, String propertyName) +95
NHibernate.Criterion.CriterionUtil.GetColumnNamesUsingPropertyName(ICriteriaQuery criteriaQuery, ICriteria criteria, String propertyName, Object value, ICriterion critertion) +25
NHibernate.Criterion.SimpleExpression.ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters) +52
NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetWhereCondition(IDictionary`2 enabledFilters) +249
NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable persister, CriteriaQueryTranslator translator, ISessionFactoryImplementor factory, ICriteria criteria, String rootEntityName, IDictionary`2 enabledFilters) +333
NHibernate.Loader.Criteria.CriteriaLoader..ctor(IOuterJoinLoadable persister, ISessionFactoryImplementor factory, CriteriaImpl rootCriteria, String rootEntityName, IDictionary`2 enabledFilters) +195
NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) +408
NHibernate.Impl.CriteriaImpl.List(IList results) +56
NHibernate.Impl.CriteriaImpl.List() +58
CommerceBuilder.DomainModel.RepositoryWithTypedId`2.LoadForCriteria(ICriteria criteria) +49
CommerceBuilder.DomainModel.DataSourceWithTypedId`3.LoadForCriteria(ICriteria criteria) +63
OrderExport_Unshipped.LoadForStartID(Int32 startID) +132
OrderExport_Unshipped.BindData() +93
OrderExport_Unshipped.btn_Export_Click(Object sender, EventArgs e) +13
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

[jguengerich]

Looks like it wants Order.Id instead of OrderId:

Code: Select all

criteria.Add(Restrictions.Ge("Order.Id", startID));  // include shipments where OrderId >= startID...

[Brewhaus]

It is working now.
Now comes a big question- how can we use the OrderNumber instead of OrderID? Given that the OrderID does not show in the URL with Gold it will be a lot of work to find the OrderID based on the OrderNumber (I would literally have to go into the SQL table to cross reference).

[jguengerich]

In regards to using the order number, I had a similar situation where I wanted information from both the Payments table and the Orders table. After some searching and testing, I was able to figure it out. If you add a line above your criteria.Add statements like this:

Code: Select all

        criteria.CreateAlias("Order", "o", NHibernate.SqlCommand.JoinType.InnerJoin);   // join orders table to shipments table
        criteria.Add(Restrictions.Ge("o.OrderNumber", startID));  // include shipments where OrderNumber >= startID...
        criteria.Add(Restrictions.IsNull("ShipDate"));      // ...and ShipDate is null

you can use the order number instead of the order Id. Note the slight change to the first criteria.Add line (line 2 above) to make use of the order number.

For consistency and self-documenting code, you may want to change your variable and function names to use "Number" instead of "ID" (i.e. startNumber, LoadForStartNumber, etc.).

As I mentioned before, the code is a bit strange (CreateAlias seems like an odd method name for joining tables), but with one extra line you can now create filters (criteria) based on the Order fields in addition to the Shipment fields.