/// <summary>
/// 尋找訂單
/// </summary>
/// <param name="selectitem"></param>
/// <returns></returns>
public List <Orders> SelectOrder(Models.SelectSearch selectitem)
{
DataTable selectresult = new DataTable();
string sql = @"SELECT OrderID,b.CompanyName,CONVERT(varchar(10) ,OrderDate, 111 ) AS OrderDate,CONVERT(varchar(10) ,ShippedDate, 111 ) AS ShippedDate
FROM Sales.Orders AS a JOIN Sales.Customers AS b ON a.CustomerID=b.CustomerID JOIN Sales.Shippers AS c ON a.ShipperID=c.ShipperID
WHERE (b.CompanyName LIKE '%'+@CompanyName+'%' OR @CompanyName = '') AND
(a.OrderID LIKE '%'+@OrderID+'%' OR @OrderID = '') AND
(a.EmployeeID LIKE @EmployeeID OR @EmployeeID = '') AND
(a.ShipperID LIKE @ShipperID OR @ShipperID = '') AND
(OrderDate >= @OrderDate AND OrderDate < DATEADD(DAY,1,@OrderDate) OR @OrderDate = '') AND
(ShippedDate >= @ShippedDate AND ShippedDate < DATEADD(DAY,1,@ShippedDate) OR @ShippedDate = '') AND
(RequiredDate >= @RequiredDate AND RequiredDate < DATEADD(DAY,1,@RequiredDate) OR @RequiredDate = '')";
using (SqlConnection conn = new SqlConnection(this.GetconnectionStrings()))
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.Add(new SqlParameter("@OrderID", selectitem.OrderID == null ? string.Empty : selectitem.OrderID));
command.Parameters.Add(new SqlParameter("@CompanyName", selectitem.CompanyName == null ? string.Empty : selectitem.CompanyName));
command.Parameters.Add(new SqlParameter("@EmployeeID", selectitem.EmployeeID == null ? string.Empty : selectitem.EmployeeID));
command.Parameters.Add(new SqlParameter("@ShipperID", selectitem.ShipperID == null ? string.Empty : selectitem.ShipperID));
command.Parameters.Add(new SqlParameter("@OrderDate", selectitem.OrderDate == null ? string.Empty : selectitem.OrderDate));
command.Parameters.Add(new SqlParameter("@ShippedDate", selectitem.ShippedDate == null ? string.Empty : selectitem.ShippedDate));
command.Parameters.Add(new SqlParameter("@RequiredDate", selectitem.RequiredDate == null ? string.Empty : selectitem.RequiredDate));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
//sqlAdapter.SelectCommand = new SqlCommand(sql, conn);
//cmd.Parameters.Add(new SqlParameter("@CustomerID", arg.CustomerID == null ? string.Empty : arg.CustomerID));
sqlAdapter.Fill(selectresult);
conn.Close();
}
return(this.MapSelectOrder(selectresult));
}
// GET: Employees
public ActionResult Index(Models.SelectSearch selectitem)
{
EmployeesService EmployeesService = new EmployeesService();
List <Employees> result = EmployeesService.GetEmployeesName();
List <SelectListItem> EmployeesData = new List <SelectListItem>();
EmployeesData.Add(new SelectListItem()
{
Text = "",
Value = null
});
foreach (var item in result)
{
EmployeesData.Add(new SelectListItem()
{
Text = item.LastName,
Value = item.EmployeeID.ToString()
});
ViewData["EmpData"] = EmployeesData;
}
ShippersService ShippersService = new ShippersService();
List <Shippers> result1 = ShippersService.GetShippersName();
List <SelectListItem> ShippersData = new List <SelectListItem>();
ShippersData.Add(new SelectListItem()
{
Text = "",
Value = null
});
foreach (var item in result1)
{
ShippersData.Add(new SelectListItem()
{
Text = item.CompanyName,
Value = item.ShipperID.ToString()
});
ViewData["ShippersData"] = ShippersData;
}
OrdersService OrdersService = new OrdersService();
ViewBag.SelectData = OrdersService.SelectOrder(selectitem);
return(View());
}
