/// <summary>
/// 取得員工資料.
/// </summary>
/// <param name="parameter">The SMEmployeeParameterDto.</param>
/// <returns>SMEmployeeModel</returns>
public SMEmployeeModel Get(SMEmployeeParameterDto parameter)
{
using (IDbConnection conn = new SqlConnection(this.DatabaseConstants.EEPSConnection))
{
string sqlCommandText = GenerateSqlCommandString(parameter);
var multi = this.DapperHelper.QueryMultiple(
dbConnection: conn,
sql: sqlCommandText,
param: new
{
EmpIds = parameter.EmpIds,
DeptIds = parameter.DeptIds,
Keyword = parameter.Keyword
});
SMEmployeeModel result = new SMEmployeeModel()
{
Base = multi.Read<SMEmployeeBaseModel>().ToList(),
Depts = multi.Read<SMEmployeeDeptModel>().ToList()
};
return result;
}
}
/// <summary>
/// 取得員工資料.
/// </summary>
/// <param name="parameter">The SMEmployeeParameterDto.</param>
/// <returns>List of SMEmployeeDto</returns>
public List<SMEmployeeDto> Get(SMEmployeeParameterDto parameter)
{
var result = this.EmployeeRepository.Get(parameter);
var response = new List<SMEmployeeDto>();
foreach (var baseItem in result.Base)
{
var affiliatedDepartment = (from item in result.Depts
where item.EmpID == baseItem.EmpId
select new SMEmployeeDeptDto()
{
JobId = item.JobId,
Leader = item.Leader,
DeptId = item.DeptId,
DeptName = item.DeptName,
ParentDeptId = item.ParentDeptId,
ParentDeptName = item.ParentDeptName,
Type = item.Type
}).ToList();
response.Add(new SMEmployeeDto()
{
EmpId = baseItem.EmpId,
EmpName = baseItem.EmpName,
JobId = baseItem.JobId,
Depts = affiliatedDepartment,
DeptId = baseItem.DeptId,
DeptName = baseItem.DeptName,
ParentDeptId = baseItem.ParentDeptId,
ParentDeptName = baseItem.ParentDeptName,
LeaveDate = baseItem.LeaveDate
});
}
return response;
}
/// <summary>
/// Generates the SQL command string.
/// </summary>
/// <param name="parameter">The parameter.</param>
/// <returns>SqlCommandString</returns>
private string GenerateSqlCommandString(SMEmployeeParameterDto parameter)
{
StringBuilder sqlEmployeeCondition = new StringBuilder();
StringBuilder sqlDepartmentCondition = new StringBuilder();
if (parameter.EmpIds.Length > 0)
{
sqlEmployeeCondition.AppendLine(@" AND E.EmpID IN @EmpIds");
sqlDepartmentCondition.AppendLine(@" AND ED.EmpID IN @EmpIds");
}
if (parameter.DeptIds.Length > 0)
{
parameter.DeptIds = this.GetAffiliatedDepartment(parameter.DeptIds, parameter.IsValidOnly, parameter.IsAllLevels);
sqlEmployeeCondition.AppendLine(@" AND E.DeptID IN @DeptIds");
sqlDepartmentCondition.AppendLine(@" AND ED.DeptID IN @DeptIds");
}
if (!parameter.Keyword.IsNullOrEmpty())
{
sqlEmployeeCondition.AppendLine(@" AND (E.EmpID = @Keyword OR E.EmpName LIKE '%' + @Keyword + '%' )");
}
if (parameter.IsValidOnly)
{
sqlEmployeeCondition.AppendLine(@" AND LeaveDate IS NULL");
}
string sqlCommandText =
@"
SELECT
E.JobID, E.DeptID, D.DeptName, E.EmpID, E.EmpName, E.LeaveDate,
D.ParentDeptID, S.DeptName AS ParentDeptName
FROM
dbo.SMEmployee AS E WITH (NOLOCK)
INNER JOIN dbo.SMDept AS D WITH (NOLOCK)
ON E.DeptID = D.DeptID
LEFT JOIN dbo.SMDept AS S WITH (NOLOCK)
ON D.ParentDeptID = S.DeptID
WHERE 1 = 1
{0}
;
SELECT
ED.EmpID, ED.DeptID, D.DeptName, ED.Leader, ED.Type, ED.JobID,
D.ParentDeptID, S.DeptName AS ParentDeptName
FROM
dbo.SMEmployeeDept AS ED WITH (NOLOCK)
INNER JOIN dbo.SMDept AS D WITH (NOLOCK)
ON ED.DeptID = D.DeptID
LEFT JOIN dbo.SMDept AS S WITH (NOLOCK)
ON D.ParentDeptID = S.DeptID
WHERE 1 = 1
{1}
;
";
return string.Format(sqlCommandText.ToString(), sqlEmployeeCondition, sqlDepartmentCondition);
}
public void Test_Get_傳入關鍵字_姓名_預期_回傳對應員工資料()
{
// arrange
// 模擬 repository 吐出的資料
var repositoryDummyData = RepositoryDummyData();
this.SMEmployeeRepository.Get(Arg.Any<DTOs.SMEmployeeParameterDto>())
.Returns(repositoryDummyData);
// 預期資料
var deptsDto = new List<Service.DTOs.SMEmployeeDeptDto>
{
new Service.DTOs.SMEmployeeDeptDto
{
DeptId = "MH2",
DeptName = "資訊處",
JobId = "1",
Leader = 4,
ParentDeptId = "MG1",
ParentDeptName = "永慶集團",
Type = "0010"
},
new Service.DTOs.SMEmployeeDeptDto
{
DeptId = "MLC",
DeptName = "研發中心",
JobId = "1",
Leader = 1,
ParentDeptId="MLE",
ParentDeptName = "資訊處(部)",
Type = "0"
},
new Service.DTOs.SMEmployeeDeptDto
{
DeptId = "MLE",
DeptName = "資訊處(部)",
JobId = "1",
Leader = 4,
ParentDeptId = "MH2",
ParentDeptName = "資訊處",
Type = "0"
}
};
var expected = new List<Service.DTOs.SMEmployeeDto>
{
new Service.DTOs.SMEmployeeDto
{
DeptId = "MH2",
DeptName = "資訊處",
EmpId = "F2949",
EmpName = "廖柏嘉",
JobId = "1",
LeaveDate = null,
ParentDeptId = "MG1",
ParentDeptName = "永慶集團",
Depts = deptsDto
}
};
var sut = GetSystemUnderTestInstance();
var parameter = new DTOs.SMEmployeeParameterDto
{
Keyword = "廖柏嘉"
};
// act
var actual = sut.Get(parameter);
//assert
expected.ToExpectedObject().ShouldEqual(actual);
}
