/// <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); }