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