public ActionResult Index()
{
OrderService orderService = new OrderService();
simpleSystem.ViewModels.Order nullCondition = new simpleSystem.ViewModels.Order();//查詢條件設定成空的
nullCondition.OrderBy = new simpleSystem.ViewModels.OrderBy {
OrderByid = OrderByID[0], OrderByStrings = OrderByStrings[0]
};
nullCondition.OrderByid = OrderByID[0];
IPagedList <simpleSystem.ViewModels.Order> orderList = orderService.GetOrderByCondtion(nullCondition);
ViewBag.condition = nullCondition; //空白的查詢條件
ViewBag.Data = orderList; //傳回搜尋結果
//員工選單
ViewBag.Emps = GetEmpSelect(orderService);
//出貨公司選單
ViewBag.ShipperIds = GetShipperSelect(orderService);
#region 排序條件字串
List <simpleSystem.ViewModels.OrderBy> OrderByList = new List <simpleSystem.ViewModels.OrderBy>();
for (int i = 0; i < OrderByStrings.Length; i++)
{
OrderByList.Add(new simpleSystem.ViewModels.OrderBy
{
OrderByid = OrderByID[i],
OrderByStrings = OrderByStrings[i],
});
}
List <SelectListItem> OrderBySelect = new List <SelectListItem>();//value:empId text: empName
foreach (var orderBy in OrderByList)
{
OrderBySelect.Add(new SelectListItem
{
Text = orderBy.OrderByStrings.ToString(),
Value = orderBy.OrderByid.ToString()
});
ViewBag.OrderBySelect = OrderBySelect;
}
#endregion
return(View(orderList));
}
public ActionResult Index(simpleSystem.ViewModels.Order condition)
{
ViewBag.condition = condition;//將查詢結果傳回給前端
OrderService orderService = new OrderService();
IPagedList <simpleSystem.ViewModels.Order> orderList = orderService.GetOrderByCondtion(condition, condition.Page);
ViewBag.Data = orderList;
//員工選單
ViewBag.Emps = GetEmpSelect(orderService);
//出貨公司選單
ViewBag.ShipperIds = GetShipperSelect(orderService);
#region 搜尋條件相關日期
if (condition.OrderDate != null)
{
ViewBag.OrderDate = ((DateTime)condition.OrderDate).ToString("yyyy-MM-dd");
}
else
{
ViewBag.OrderDate = null;
}
if (condition.RequireDdate != null)
{
ViewBag.RequireDdate = ((DateTime)condition.RequireDdate).ToString("yyyy-MM-dd");
}
else
{
ViewBag.RequireDdate = null;
}
if (condition.ShippedDate != null)
{
ViewBag.ShippedDate = ((DateTime)condition.ShippedDate).ToString("yyyy-MM-dd");
}
else
{
ViewBag.ShippedDate = null;
}
#endregion
#region 排序條件字串
List <simpleSystem.ViewModels.OrderBy> OrderByList = new List <simpleSystem.ViewModels.OrderBy>();
for (int i = 0; i < OrderByStrings.Length; i++)
{
OrderByList.Add(new simpleSystem.ViewModels.OrderBy
{
OrderByid = OrderByID[i],
OrderByStrings = OrderByStrings[i],
});
}
List <SelectListItem> OrderBySelect = new List <SelectListItem>();//value:empId text: empName
foreach (var orderBy in OrderByList)
{
OrderBySelect.Add(new SelectListItem
{
Text = orderBy.OrderByStrings.ToString(),
Value = orderBy.OrderByid.ToString()
});
ViewBag.OrderBySelect = OrderBySelect;
}
#endregion
return(View(orderList));
}
/// <summary>
/// 依照條件取得訂單資料
/// </summary>
/// <returns></returns>
public IPagedList <simpleSystem.ViewModels.Order> GetOrderByCondtion(simpleSystem.ViewModels.Order order, int page = 1)
{
//IPagedList<simpleSystem.ViewModels.Order> result = new IPagedList<simpleSystem.ViewModels.Order>();
DateTime reDate = (Convert.ToDateTime(order.RequireDdate)).Date;
DateTime orDate = (Convert.ToDateTime(order.OrderDate)).Date;
DateTime shDate = (Convert.ToDateTime(order.ShippedDate)).Date;
using (Models.originalDB db = new Models.originalDB())
{
int currentPage = page < 1 ? 1 : page;
//依照給的order資訊下條件
var dbResult = db.Orders.Join(db.Customers,
orders => orders.CustomerID,
customers => customers.CustomerID,
(orders, customers) => new { Customers = customers, Orders = orders })
.Where(y => (
(order.OrderDd == y.Orders.OrderID || order.OrderDd == 0)
&&
(order.EmpId == y.Orders.EmployeeID || order.EmpId == 0)
&&
(y.Customers.CompanyName.Contains(order.CustName) || order.CustName == null)
&&
(order.ShipperId == y.Orders.ShipperID || order.ShipperId == 0)
&& //order.RequireDdate == y.Orders.RequiredDate
(DbFunctions.TruncateTime(y.Orders.RequiredDate) == reDate || order.RequireDdate == null)
&& // (order.Orderdate == y.Orders.OrderDate || order.Orderdate == null)
(DbFunctions.TruncateTime(y.Orders.OrderDate) == orDate || order.OrderDate == null)
&&
(DbFunctions.TruncateTime(y.Orders.ShippedDate) == shDate || order.ShippedDate == null)
))
.Select(x => new simpleSystem.ViewModels.Order
{
OrderDd = x.Orders.OrderID,
CustName = x.Customers.CompanyName,
OrderDate = x.Orders.OrderDate,
ShippedDate = x.Orders.ShippedDate,
})
.OrderBy(all => all.OrderDd);
//OrderByDescending
IPagedList <simpleSystem.ViewModels.Order> orderDbResult = dbResult.ToPagedList(currentPage, pageSize);
switch (order.OrderByid)
{
case "OrderDd":
orderDbResult = dbResult.OrderBy(all => all.OrderDd).ToPagedList(currentPage, pageSize);
break;
case "CustName":
orderDbResult = dbResult.OrderBy(all => all.CustName).ToPagedList(currentPage, pageSize);
break;
case "OrderdateStr":
orderDbResult = dbResult.OrderBy(all => all.OrderDate).ToPagedList(currentPage, pageSize);
break;
case "ShippedDateStr":
orderDbResult = dbResult.OrderBy(all => all.ShippedDate).ToPagedList(currentPage, pageSize);
break;
case "OrderDDesc":
orderDbResult = dbResult.OrderByDescending(all => all.OrderDd).ToPagedList(currentPage, pageSize);
break;
case "CustNameDesc":
orderDbResult = dbResult.OrderByDescending(all => all.CustName).ToPagedList(currentPage, pageSize);
break;
case "OrderdateStrDesc":
orderDbResult = dbResult.OrderByDescending(all => all.OrderDate).ToPagedList(currentPage, pageSize);
break;
case "ShippedDateStrDesc":
orderDbResult = dbResult.OrderByDescending(all => all.ShippedDate).ToPagedList(currentPage, pageSize);
break;
}
var index = 0;
foreach (var tmp in orderDbResult)
{
if (tmp.ShippedDate == null)
{
orderDbResult[index].ShippedDateStr = "";
}
else
{
orderDbResult[index].ShippedDateStr = ((DateTime)tmp.ShippedDate).ToString("yyyy/MM/dd");
}
orderDbResult[index].OrderdateStr = ((DateTime)tmp.OrderDate).ToString("yyyy/MM/dd");
index++;
}
//result.Add(new simpleSystem.ViewModels.Order() { CustId = "001", CustName = "叡揚資訊", EmpId = 1, ShipperName = "哈哈公司", ShipperId = 12, EmpName = "王小明", Orderdate = DateTime.Parse("2015/11/08"), ShippedDate = DateTime.Parse("2015/11/09") });
//result.Add(new simpleSystem.ViewModels.Order() { CustId = "002", CustName = "網軟資訊", EmpId = 2, ShipperName="嘻嘻公司",ShipperId=14, EmpName = "李小華", Orderdate = DateTime.Parse("2015/11/01"), ShippedDate = DateTime.Parse("2015/11/09") });
return(orderDbResult);
}
}
