/// <summary>
/// 依照條件取得訂單資料
/// </summary>
/// <returns></returns>
public List <Models.Customer> GetCustomerByCondtioin(Models.CustomerSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"select * FROM Sales.Customers WHERE (CustomerID Like '%' + @CustomerID + '%' Or CustomerID='' ) And (Companyname Like '%' + @CustName + '%' Or @CustName='') And
(ContactName Like '%' + @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("@CustName", 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.MapCustomerDataToList(dt));
}
public DataTable GetCustomerByCondition(Models.CustomerSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT
A.CustomerID,
A.CompanyName,
A.ContactName,
B.CodeId+'-'+B.CodeVal AS ContactTitle
FROM
Sales.Customers AS A INNER JOIN
CodeTable AS B ON A.ContactTitle=B.CodeId
WHERE
(A.Companyname Like @CompanyName OR @CompanyName = '') AND
(A.CustomerID Like @CustomerID OR @CustomerID = '') AND
(A.ContactName = @ContactName OR @ContactName = '') AND
( (B.CodeId+'-'+B.CodeVal) = @ContactTitle OR @ContactTitle = '')";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@CompanyName", arg.CompanyName == null ? string.Empty : '%' + arg.CompanyName + '%'));
cmd.Parameters.Add(new SqlParameter("@CustomerID", arg.CustomerId == null ? string.Empty : '%' + arg.CustomerId + '%'));
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(dt);
}
/// <summary>
/// Read
/// </summary>
/// <param name="arg"></param>
/// <returns></returns>
public JsonResult Read(Models.CustomerSearchArg arg)
{
try
{
Service.CustomerService customerService = new Service.CustomerService();
JsonResult result = this.Json(customerService.GetCustomerByCondition(arg), JsonRequestBehavior.AllowGet);
return(result);
}
catch (Exception)
{
return(this.Json(false));
}
}
public List <Models.Customer> GetCustomerByCondition(Models.CustomerSearchArg arg)
{
DataTable dt = customerDao.GetCustomerByCondition(arg);
return(this.MapCustomerDataToList(dt));
}
public JsonResult Customer(Models.CustomerSearchArg arg)
{
ViewBag.EmpCodeData = this.CustomerService.GetContactTitle();
Models.CustomerService CustomerService = new Models.CustomerService();
return(Json(CustomerService.GetCustomerByCondtioin(arg)));
}
