/// <summary> /// 取得 Orders by 條件 /// </summary> /// <returns></returns> public IList <Order> GetOrders(OrderQueryArg arg) { CustomerService customerService = new CustomerService(); OrdersDao orderDao = new OrdersDao(); // 取得所有訂單後進行篩選 (注意: 此處應將查詢條件串入SQL中為較好之寫法) IEnumerable <Order> currentOrders = orderDao.GetOrdersByArg(arg); return(currentOrders.OrderBy(m => m.OrderID).ToList()); }
/// <summary> /// 取得 Orders by 條件 /// </summary> /// <returns></returns> public IList <Order> GetOrders(OrderQueryArg arg) { CustomerService customerService = new CustomerService(); OrdersDao orderDao = new OrdersDao(); // 取得所有訂單後進行篩選 (注意: 此處應將查詢條件串入SQL中為較好之寫法) IEnumerable <Order> currentOrders = orderDao.GetAllOrders(); // 訂單編號 if (arg.OrderID.HasValue) { currentOrders = currentOrders.Where(m => m.OrderID == arg.OrderID.Value); } // 客戶名稱 (like 查詢) if (!string.IsNullOrWhiteSpace(arg.CompanyName)) { currentOrders = currentOrders.Where( m => customerService.GetCompanyName(m.CustomerID).Contains(arg.CompanyName) ); } // 員工編號 if (arg.EmployeeID.HasValue) { currentOrders = currentOrders.Where(m => m.EmployeeID == arg.EmployeeID.Value); } // 出貨公司 if (arg.ShipperID.HasValue) { currentOrders = currentOrders.Where(m => m.ShipperID == arg.ShipperID.Value); } // 訂購日期 if (arg.OrderDate.HasValue) { currentOrders = currentOrders.Where(m => m.OrderDate == arg.OrderDate.Value); } // 需要日期 if (arg.RequiredDate.HasValue) { currentOrders = currentOrders.Where(m => m.RequiredDate == arg.RequiredDate.Value); } // 出貨日期 if (arg.ShipedDate.HasValue) { currentOrders = currentOrders.Where(m => m.ShippedDate == arg.ShipedDate.Value); } return(currentOrders.OrderBy(m => m.OrderID).ToList()); }
public ActionResult OrderList(OrderQueryArg arg) { SalesOrdersService orderService = new SalesOrdersService(); CustomerService customerService = new CustomerService(); // 過濾後訂單資料 IList <SalesOrders> orders = orderService.GetOrders(arg); // 所有客戶資料 ViewBag.Customers = customerService.GetCustomers(); return(View(orders)); }
public JsonResult GetOrderList(OrderQueryArg arg) { OrderService orderService = new OrderService(); CustomerService customerService = new CustomerService(); // 過濾後訂單資料 IList <Order> orders = orderService.GetOrders(arg); // 所有客戶資料 ViewBag.Customers = customerService.GetCustomers(); return(Json(orders)); }
public ActionResult Query(OrderQueryArg arg) { OrderService orderService = new OrderService(); CustomerService customerService = new CustomerService(); // 過濾後訂單資料 IList <Order> orders = orderService.GetOrders(arg); var list = new List <Tuple <int, string, string, string, decimal?> >(); for (int i = 0; i < orders.Count; i++) { list.Add(new Tuple <int, string, string, string, decimal?>( orders[i].OrderID, customerService.GetCompanyName(orders[i].CustomerID), orders[i].OrderDate.ToString("yyyy/MM/dd"), orders[i].ShippedDate.HasValue ? orders[i].ShippedDate.Value.ToString("yyyy/MM/dd") : string.Empty, orders[i].Freight )); } //return View(orders); return(Json(list)); }
public List <Order> GetOrdersByArg(OrderQueryArg arg) { using (SqlConnection conn = GetSqlConnection()) { List <Order> result = new List <Order>(); Dictionary <object, object> ruleDict = new Dictionary <object, object>(); if (arg.OrderID.HasValue) { ruleDict.Add("OrderID", arg.OrderID); } if (!string.IsNullOrWhiteSpace(arg.CompanyName)) { ruleDict.Add("CompanyName", "%" + arg.CompanyName + "%"); } if (arg.EmployeeID.HasValue) { ruleDict.Add("EmployeeID", arg.EmployeeID); } if (arg.ShipperID.HasValue) { ruleDict.Add("ShipperID", arg.ShipperID); } if (arg.OrderDate.HasValue) { ruleDict.Add("OrderDate", arg.OrderDate); } if (arg.RequiredDate.HasValue) { ruleDict.Add("RequiredDate", arg.RequiredDate); } if (arg.ShippedDate.HasValue) { ruleDict.Add("ShippedDate", arg.ShippedDate); } StringBuilder stringBuilder = new StringBuilder(); string sql; if (ruleDict.Count == 0) { sql = "select * from Sales.Orders join Sales.Customers on Sales.Orders.CustomerID = Sales.Customers.CustomerID"; } else { sql = "select * from Sales.Orders join Sales.Customers on Sales.Orders.CustomerID = Sales.Customers.CustomerID Where"; stringBuilder.Append(sql); int count = 0; foreach (KeyValuePair <object, object> kvp in ruleDict) { if (kvp.Key.ToString() != "CompanyName") { stringBuilder.Append(" " + kvp.Key + " = @" + kvp.Key); } else { stringBuilder.Append(" " + kvp.Key + " LIKE @" + kvp.Key); } if (count != ruleDict.Count - 1) { stringBuilder.Append(" AND"); } count++; } sql = stringBuilder.ToString(); } SqlCommand cmd = new SqlCommand(sql, conn); if (ruleDict.Count != 0) { foreach (KeyValuePair <object, object> kvp in ruleDict) { cmd.Parameters.AddWithValue("@" + kvp.Key, kvp.Value); } } SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) { result.Add(new Order { OrderID = int.Parse(row["OrderID"].ToString()), CustomerID = int.Parse(row["CustomerID"].ToString()), CompanyName = row["CompanyName"].ToString(), EmployeeID = int.Parse(row["EmployeeID"].ToString()), OrderDate = DateTime.Parse(row["OrderDate"].ToString()), RequiredDate = DateTime.Parse(row["RequiredDate"].ToString()), ShippedDate = (!string.IsNullOrWhiteSpace(row["ShippedDate"].ToString())) ? new DateTime?(DateTime.Parse(row["ShippedDate"].ToString())) : null, ShipperID = (!string.IsNullOrWhiteSpace(row["ShipperID"].ToString())) ? new int?(int.Parse(row["ShipperID"].ToString())) : null, Freight = (!string.IsNullOrWhiteSpace(row["Freight"].ToString())) ? new decimal?(decimal.Parse(row["Freight"].ToString())) : null, ShipCountry = row["ShipCountry"].ToString(), ShipCity = row["ShipCity"].ToString(), ShipRegion = row["ShipRegion"].ToString(), ShipPostalCode = row["ShipPostalCode"].ToString(), ShipAddress = row["ShipAddress"].ToString() }); } return(result); } }