Exemplo n.º 1
0
        /// <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);
        }
Exemplo n.º 2
0
        /// <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);
        }
Exemplo n.º 4
0
        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);
        }
Exemplo n.º 5
0
        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);
        }