/// <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>
/// Gets coupons codes used for orders
/// </summary>
/// <param name="startDate">The starting date for orders to be checked for coupons</param>
/// <param name="endDate">The ending date for orders to be checked for coupons</param>
/// <returns>An array of string with the codes of coupons used</returns>
public static string[] GetCouponCodes(DateTime startDate, DateTime endDate)
{
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT DISTINCT(C.CouponCode)");
selectQuery.Append(" FROM ac_Orders O INNER JOIN ac_OrderCoupons C ON O.OrderId = C.OrderId");
selectQuery.Append(" WHERE O.StoreId = @storeId");
if (startDate > DateTime.MinValue)
{
selectQuery.Append(" AND O.OrderDate >= @startDate");
}
if (endDate > DateTime.MinValue)
{
selectQuery.Append(" AND O.OrderDate <= @endDate");
}
List <OrderStatus> reportStatuses = OrderStatusDataSource.GetReportStatuses();
selectQuery.Append(" AND " + ReportDataSource.GetStatusFilter(reportStatuses, "O"));
selectQuery.Append(" ORDER BY C.CouponCode");
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
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);
List <string> results = new List <string>();
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read())
{
results.Add(dr.GetString(0));
}
dr.Close();
}
if (results.Count == 0)
{
return(null);
}
return(results.ToArray());
}