/// <summary> /// Loads an order object for given GoogleOrderNumber /// </summary> /// <param name="googleOrderNumber">The GoogleOrderNumber to load and Order object for</param> /// <returns>The loaded Order object or null if no order is found for the given GoogleOrderNumber</returns> public static Order LoadForGoogleOrderNumber(string googleOrderNumber) { //CREATE THE DYNAMIC SQL TO LOAD OBJECT StringBuilder selectQuery = new StringBuilder(); selectQuery.Append("SELECT "); selectQuery.Append(Order.GetColumnNames(string.Empty)); selectQuery.Append(" FROM ac_Orders"); selectQuery.Append(" WHERE GoogleOrderNumber = @googleOrderNumber"); selectQuery.Append(" AND StoreId = @storeId"); Database database = Token.Instance.Database; DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString()); database.AddInParameter(selectCommand, "@googleOrderNumber", System.Data.DbType.String, googleOrderNumber); database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId); using (IDataReader dr = database.ExecuteReader(selectCommand)) { if (dr.Read()) { Order order = new Order(); Order.LoadDataReader(order, dr); return(order); } dr.Close(); } return(null); }
/// <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 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); }