/// <summary>
/// Loads a collection of orders associated to the given coupon code
/// </summary>
/// <param name="couponCode">Coupon Code for which to load the associated orders</param>
/// <param name="startDate">start date to consider when loading orders</param>
/// <param name="endDate">end date to consider when loading orders</param>
/// <param name="sortExpression">Sort expression to use for sorting the loaded objects</param>
/// <returns>A collection of orders associated to the given coupon code</returns>
public static OrderCollection LoadForCouponCode(string couponCode, DateTime startDate, DateTime endDate, string sortExpression)
{
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT ");
selectQuery.Append(Order.GetColumnNames("O"));
selectQuery.Append(" FROM ac_Orders O INNER JOIN ac_OrderCoupons C ON O.OrderId = C.OrderId");
selectQuery.Append(" WHERE C.CouponCode = @couponCode");
selectQuery.Append(" AND O.StoreId = @storeId");
List <OrderStatus> reportStatuses = OrderStatusDataSource.GetReportStatuses();
selectQuery.Append(" AND " + ReportDataSource.GetStatusFilter(reportStatuses, "O"));
if (startDate > DateTime.MinValue)
{
selectQuery.Append(" AND O.OrderDate >= @startDate");
}
if (endDate > DateTime.MinValue)
{
selectQuery.Append(" AND O.OrderDate <= @endDate");
}
if (!string.IsNullOrEmpty(sortExpression))
{
selectQuery.Append(" ORDER BY " + sortExpression);
}
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@couponCode", System.Data.DbType.String, couponCode);
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
if (startDate > DateTime.MinValue)
{
database.AddInParameter(selectCommand, "@startDate", System.Data.DbType.DateTime, LocaleHelper.FromLocalTime(startDate));
}
if (endDate > DateTime.MinValue)
{
database.AddInParameter(selectCommand, "@endDate", System.Data.DbType.DateTime, LocaleHelper.FromLocalTime(endDate));
}
ReportDataSource.SetStatusFilterParams(reportStatuses, database, selectCommand);
OrderCollection results = new OrderCollection();
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read())
{
Order order = new Order();
Order.LoadDataReader(order, dr);
results.Add(order);
}
dr.Close();
}
return(results);
}
/// <summary>
/// Loads a collection of orders associated to a given affiliate
/// </summary>
/// <param name="affiliateId">Affiliate for which to load the orders</param>
/// <param name="startDate">start date to consider when loading orders</param>
/// <param name="endDate">end date to consider when loading orders</param>
/// <param name="sortExpression">Sort expression to use for sorting the loaded objects</param>
/// <returns>A collection of orders associated to a given affiliate</returns>
public static OrderCollection LoadForAffiliate(int affiliateId, DateTime startDate, DateTime endDate, string sortExpression)
{
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT ");
selectQuery.Append(Order.GetColumnNames(string.Empty));
selectQuery.Append(" FROM ac_Orders");
selectQuery.Append(" WHERE AffiliateId = @affiliateId");
selectQuery.Append(" AND StoreId = @storeId");
if (startDate > DateTime.MinValue)
{
selectQuery.Append(" AND OrderDate >= @startDate");
}
if (endDate > DateTime.MinValue)
{
selectQuery.Append(" AND OrderDate <= @endDate");
}
if (!string.IsNullOrEmpty(sortExpression))
{
selectQuery.Append(" ORDER BY " + sortExpression);
}
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@affiliateId", System.Data.DbType.Int32, affiliateId);
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
if (startDate > DateTime.MinValue)
{
database.AddInParameter(selectCommand, "@startDate", System.Data.DbType.DateTime, LocaleHelper.FromLocalTime(startDate));
}
if (endDate > DateTime.MinValue)
{
database.AddInParameter(selectCommand, "@endDate", System.Data.DbType.DateTime, LocaleHelper.FromLocalTime(endDate));
}
OrderCollection results = new OrderCollection();
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read())
{
Order order = new Order();
Order.LoadDataReader(order, dr);
results.Add(order);
}
dr.Close();
}
return(results);
}
public static OrderCollection LoadForStore(int maximumRows, int startRowIndex, string sortExpression)
{
int storeId = Token.Instance.StoreId;
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT");
if (maximumRows > 0)
{
selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
}
selectQuery.Append(" " + Order.GetColumnNames(string.Empty));
selectQuery.Append(" FROM ac_Orders");
selectQuery.Append(" WHERE StoreId = @storeId");
if (!string.IsNullOrEmpty(sortExpression))
{
selectQuery.Append(" ORDER BY " + sortExpression);
}
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, storeId);
//EXECUTE THE COMMAND
OrderCollection results = new OrderCollection();
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
{
if (thisIndex >= startRowIndex)
{
Order order = new Order();
Order.LoadDataReader(order, dr);
results.Add(order);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
return(results);
}
public OrderCollection Search(OrderSearchCriteria criteria, int maximumRows, int startRowIndex, string sortExpression)
{
//CREATE RETURN SET
OrderCollection orders = new OrderCollection();
//GET LIST OF ORDER IDS THAT FIT THE CRITERIA
Database database = Token.Instance.Database;
DbCommand selectCommand = criteria.BuildSelectCommand(sortExpression);
List <int> orderIdList = new List <int>();
//EXECUTE THE COMMAND TO OBTAIN ORDER ID List
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read())
{
orderIdList.Add(dr.GetInt32(0));
}
dr.Close();
}
//NOW GET ORDER DETAIL FOR CORRECT INDEXES
int rowIndex = startRowIndex;
int rowCount = 0;
if (maximumRows < 1)
{
maximumRows = 1000;
}
while ((rowCount < maximumRows) && (rowIndex < orderIdList.Count))
{
Order order = OrderDataSource.Load(orderIdList[rowIndex]);
orders.Add(order);
rowIndex++;
rowCount++;
}
//RETURN ORDER DETAIL MATCHING CRITERIA
return(orders);
}
public static OrderCollection Search(int orderStatusId, OrderPaymentStatus paymentStatus, OrderShipmentStatus shipmentStatus, DateTime startDate, DateTime endDate, int maximumRows, int startRowIndex, string sortExpression)
{
//SET DEFAULT SORT
if (string.IsNullOrEmpty(sortExpression))
{
sortExpression = "OrderDate DESC";
}
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT");
if (maximumRows > 0)
{
selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
}
selectQuery.Append(" " + Order.GetColumnNames(string.Empty));
selectQuery.Append(" FROM ac_Orders");
selectQuery.Append(" WHERE StoreId = @storeId");
if (orderStatusId != 0)
{
selectQuery.Append(" AND OrderStatusId = @orderStatusId");
}
if (paymentStatus != OrderPaymentStatus.Unspecified)
{
selectQuery.Append(" AND PaymentStatusId = @paymentStatus");
}
if (shipmentStatus != OrderShipmentStatus.Unspecified)
{
selectQuery.Append(" AND ShipmentStatusId = @shipmentStatus");
}
if (startDate > DateTime.MinValue)
{
selectQuery.Append(" AND OrderDate >= @startDate");
}
if (endDate > DateTime.MinValue)
{
selectQuery.Append(" AND OrderDate <= @endDate");
}
selectQuery.Append(" ORDER BY " + sortExpression);
//BUILD THE COMMAND
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
if (orderStatusId != 0)
{
database.AddInParameter(selectCommand, "@orderStatusId", System.Data.DbType.Int32, orderStatusId);
}
if (startDate > DateTime.MinValue)
{
database.AddInParameter(selectCommand, "@startDate", System.Data.DbType.DateTime, startDate);
}
if (endDate > DateTime.MinValue)
{
database.AddInParameter(selectCommand, "@endDate", System.Data.DbType.DateTime, endDate);
}
if (paymentStatus != OrderPaymentStatus.Unspecified)
{
database.AddInParameter(selectCommand, "@paymentStatus", System.Data.DbType.Byte, paymentStatus);
}
if (shipmentStatus != OrderShipmentStatus.Unspecified)
{
database.AddInParameter(selectCommand, "@shipmentStatus", System.Data.DbType.Byte, shipmentStatus);
}
//EXECUTE THE COMMAND
OrderCollection results = new OrderCollection();
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
{
if (thisIndex >= startRowIndex)
{
Order order = new Order();
Order.LoadDataReader(order, dr);
results.Add(order);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
return(results);
}
){kind=link}