/// <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); }