/// <summary>
/// 依照條件取得訂單資料
/// </summary>
/// <returns></returns>
public List <Models.Order> GetOrderByCondtioin(Models.OrderSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT
A.OrderID,A.CustomerID,B.CompanyName As CustName,
A.EmployeeID,C.LastName+ C.FirstName As EmpName,
A.OrderDate,A.RequireDdate,A.ShippedDate,
A.ShipperId,D.CompanyName As ShipperName,A.Freight,
A.ShipName,A.ShipAddress,A.ShipCity,A.ShipRegion,A.ShipPostalCode,A.ShipCountry
From Sales.Orders As A
INNER JOIN Sales.Customers As B ON A.CustomerID=B.CustomerID
INNER JOIN HR.Employees As C On A.EmployeeID=C.EmployeeID
inner JOIN Sales.Shippers As D ON A.shipperid=D.ShipperID
Where (B.CompanyName Like @CustName Or @CustName='') And
(A.EmployeeID = @EmpId Or @EmpId=-1) And
(A.OrderDate=@Orderdate Or @Orderdate='') " ;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@CustName", arg.CustName == null ? string.Empty : arg.CustName));
cmd.Parameters.Add(new SqlParameter("@Orderdate", arg.OrderDate == null ? string.Empty : arg.OrderDate));
cmd.Parameters.Add(new SqlParameter("@EmpId", arg.EmpId == -1 ? -1 : arg.EmpId));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapOrderDataToList(dt));
}
public List <Models.Order> GetOrderByCondtioin(Models.OrderSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT
CustomerID,CompanyName,ContactName,ContactTitle
from Sales.Customers
Where (CustomerID = @CustomerID Or @CustomerID='') And
(CompanyName = @CompanyName Or @CompanyName='') And
(ContactName = @ContactName Or @ContactName='') And
(ContactTitle = @ContactTitle Or @ContactTitle='')" ;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@CustomerID", arg.CustomerID == null ? string.Empty : arg.CustomerID));
cmd.Parameters.Add(new SqlParameter("@CompanyName", arg.CompanyName == null ? string.Empty : arg.CompanyName));
cmd.Parameters.Add(new SqlParameter("@ContactName", arg.ContactName == null ? string.Empty : arg.ContactName));
cmd.Parameters.Add(new SqlParameter("@ContactTitle", arg.ContactTitle == null ? string.Empty : arg.ContactTitle));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapOrderDataToList(dt));
}
public ActionResult Index(Models.OrderSearchArg arg)
{
ViewBag.EmpCodeData = this.codeService.GetEmp();
Models.OrderService orderService = new Models.OrderService();
ViewBag.SearchResult = orderService.GetOrderByCondtioin(arg);
return(View("Index"));
}
public ActionResult Index(Models.OrderSearchArg arg)
{
if (arg.Delete != null)
{
orderService.DeleteOrderById(arg.Delete);
}
else if (arg.Insert != null)
{
ModelState.Clear();
return(View("InsertOrder", new Models.Order()));
}
ViewBag.SearchResult = orderService.GetOrderByCondtioin(arg);
return(View("Index"));
}
