/// <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));
}
/// <summary>
/// 取得所有主管ID(Employee.ManagerID)
/// </summary>
/// <returns></returns>
public List <SelectListItem> GetManagerID()
{
DataTable dt = new DataTable();
string sql = @"Select Distinct FirstName + LastName AS CodeVal, EmployeeID AS CodeId
From HR.Employees";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(TableFactory.Table2SelectListItem(dt));
}
/// <summary>
/// 取得所有職稱(Employee.Title)
/// </summary>
/// <returns></returns>
public List <SelectListItem> GetTitle()
{
DataTable dt = new DataTable();
string sql = @"Select Distinct CodeVal, CodeId
From CodeTable
WHERE CodeType = 'TITLE'";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(TableFactory.Table2SelectListItem(dt));
}
/// <summary>
/// 取得emp資訊 by 輸入的ID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Employee GetEmployeeByID(string id)
{
DataTable dt = new DataTable();
string sql = @"SELECT [EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],
[BirthDate],[HireDate],[Address],[City],[Country],[Phone],
[ManagerID],[Gender],[MonthlyPayment],[YearlyPayment]
FROM [HR].[Employees]
WHERE EmployeeID = @employeeID";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@employeeID", id));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(TableFactory.Table2EmployeeInsertArg(dt));
}