/// <summary>
/// 取得emp by 表單上的data
/// </summary>
/// <param name="arg"></param>
/// <returns></returns>
public List <EmployeeSearchResult> GetSearchResultByArg(Models.EmployeeSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT hs.EmployeeID, hs.FirstName + hs.LastName AS EmployeeName,
ct1.CodeId + '-' + ct1.CodeVal AS Type, convert(varchar, hs.HireDate, 111) HireDate,
ct2.CodeVal AS Gender, Year(GETDATE())-Year(BirthDate) Age
FROM HR.Employees hs JOIN CodeTable ct1
ON (ct1.CodeId = hs.Title AND ct1.CodeType = 'TITLE')
LEFT JOIN CodeTable ct2
ON (ct2.CodeId = hs.Gender AND ct2.CodeType = 'GENDER')
WHERE (@employeeID IS NULL OR @employeeID = EmployeeID) AND
(@employeeName IS NULL OR FirstName LIKE '%' + @employeeName + '%' OR hs.LastName LIKE '%' + @employeeName + '%') AND
(@codeVal IS NULL OR @codeVal = hs.Title) AND
(@startHireDate IS NULL OR @endHireDate IS NULL OR HireDate BETWEEN @startHireDate AND @endHireDate) ;" ;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@employeeID", NullToDBNullValue(arg.EmployeeId, true)));
cmd.Parameters.Add(new SqlParameter("@employeeName", NullToDBNullValue(arg.EmployeeName, true)));
cmd.Parameters.Add(new SqlParameter("@codeVal", NullToDBNullValue(arg.Title, true)));
cmd.Parameters.Add(new SqlParameter("@startHireDate", NullToDBNullValue(arg.StartHireDate, true)));
cmd.Parameters.Add(new SqlParameter("@endHireDate", NullToDBNullValue(arg.EndHireDate, true)));
/*如果用string的方式，可以改成下面這兩行*/
//cmd.Parameters.Add(new SqlParameter("@startHireDate", arg.StartHireDate.ToString() == null ? string.Empty : arg.StartHireDate.ToString()));
//cmd.Parameters.Add(new SqlParameter("@endHireDate", arg.EndHireDate.ToString() == null ? string.Empty : arg.EndHireDate.ToString()));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(TableFactory.Table2EmployeeSearchResult(dt));
}
public ActionResult SearchEmployee(Models.EmployeeSearchArg arg)
{
Models.EmployeeService employeeService = new Models.EmployeeService();
ViewBag.Type = employeeService.GetTitle();
List <Models.EmployeeSearchResult> employeeSearchResult = employeeService.GetSearchResultByArg(arg);
ViewBag.EmployeeSearchResult = employeeSearchResult;
return(View(arg));
}
