public OperationResult <IList <IEmployeeDTO> > SearchEmployeeByRawQuery(ISearchEmployeeDTO employeeDTO, bool checkTerminationDate)
{
OperationResult <IList <IEmployeeDTO> > searchEmployee = null;
try
{
IUserDAC userDAC = (IUserDAC)DACFactory.Instance.Create(DACType.UserManagerDAC);
IList <IEmployeeDTO> userDTOList = userDAC.SearchEmployeeByRawQuery(employeeDTO, checkTerminationDate);
if (userDTOList != null)
{
searchEmployee = OperationResult <IList <IEmployeeDTO> > .CreateSuccessResult(userDTOList);
}
else
{
searchEmployee = OperationResult <IList <IEmployeeDTO> > .CreateFailureResult("SearchEmployeeByRawQuery Method Failed");
}
}
catch (DACException dacEx)
{
searchEmployee = OperationResult <IList <IEmployeeDTO> > .CreateErrorResult(dacEx.Message, dacEx.StackTrace);
}
catch (Exception ex)
{
ExceptionManager.HandleException(ex);
searchEmployee = OperationResult <IList <IEmployeeDTO> > .CreateErrorResult(ex.Message, ex.StackTrace);
}
return(searchEmployee);
}
public ActionResult SearchEmployee(SearchEmployee searchEmployee)
{
ActionResult retVal = null;
if (ModelState.IsValid)
{
ISearchEmployeeDTO searchEmployeeDTO = (ISearchEmployeeDTO)DTOFactory.Instance.Create(DTOType.SearchEmployeeDTO);
DTOConverter.FillDTOFromViewModel(searchEmployeeDTO, searchEmployee);
IUserFacade userFacade = (IUserFacade)FacadeFactory.Instance.Create(FacadeType.UserManagerFacade);
OperationResult <IList <IEmployeeDTO> > result = userFacade.SearchEmployeeByRawQuery(searchEmployeeDTO, (User.IsInRole("NonAdmin"))?true:false);
if (result.IsValid())
{
IList <Employee> employeeList = new List <Employee>();
Employee employee = null;
IDepartmentFacade departmentFacade = (IDepartmentFacade)FacadeFactory.Instance.Create(FacadeType.DepartmentManagerFacade);
//OperationResult<IList<IEmployeeDTO>> result = userFacade.SearchEmployeeByRawQuery(searchEmployeeDTO, true);
foreach (var employeeDTO in result.Data)
{
employee = new Employee();
DTOConverter.FillViewModelFromDTO(employee, employeeDTO);
OperationResult <IDepartmentDTO> department = departmentFacade.GetADepartment(employeeDTO.DepartmentId);
if (department.IsValid())
{
employee.Department = new Department();
DTOConverter.FillViewModelFromDTO(employee.Department, department.Data);
}
employeeList.Add(employee);
}
retVal = PartialView("~/Views/User/_SearchEmployeeList.cshtml", employeeList);
}
else if (result.HasFailed())
{
}
else
{
retVal = View("~/Views/Shared/Error.cshtml");
}
}
return(retVal);
}
static void SearchEmployeeBySProc()
{
ISearchEmployeeDTO employeeDTO = (ISearchEmployeeDTO)DTOFactory.Instance.Create(DTOType.SearchEmployeeDTO);
employeeDTO.Firstname = "kan";
IUserFacade userFacade = (IUserFacade)FacadeFactory.Instance.Create(FacadeType.UserManagerFacade);
OperationResult <IList <IEmployeeDTO> > employeeDTOList = userFacade.SearchEmployeeBySProc(employeeDTO, true);
if (employeeDTOList.IsValid())
{
foreach (IEmployeeDTO employee in employeeDTOList.Data)
{
System.Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", employee.EmployeeId, employee.FirstName,
employee.LastName, employee.DateOfJoining, employee.DepartmentId);
}
}
}
public OperationResult <IList <IEmployeeDTO> > SearchEmployeeByRawQuery(ISearchEmployeeDTO employeeDTO, bool checkTerminationDate)
{
IUserBDC userBDC = (IUserBDC)BDCFactory.Instance.Create(BDCType.UserManagerBDC);
return(userBDC.SearchEmployeeByRawQuery(employeeDTO, checkTerminationDate));
}
public IList <IEmployeeDTO> SearchEmployeeByRawQuery(ISearchEmployeeDTO searchEmployeeDTO, bool checkTerminationDate)
{
IList <IEmployeeDTO> retVal = null;
string query = @"SELECT *
FROM Employees
WHERE
(
(@FirstName IS NOT NULL AND FirstName LIKE '%' + @FirstName + '%') OR
(@LastName IS NOT NULL AND LastName LIKE '%' + @LastName + '%') OR
(@Email IS NOT NULL AND Email LIKE '%' + @Email + '%') OR
(@BeginDate IS NOT NULL AND DateOfJoining >= @BeginDate ) OR
(@EndDate IS NOT NULL AND DateOfJoining <= @EndDate) OR
(@Department IS NOT NULL AND DepartmentId = @Department)
)" + (checkTerminationDate ? "AND TerminationDate IS NULL" : string.Empty);
try
{
using (EmployeePortal2017Entities portal = new EmployeePortal2017Entities())
{
var firstNameParameter = searchEmployeeDTO.Firstname != null ?
new SqlParameter("FirstName", searchEmployeeDTO.Firstname) :
new SqlParameter("FirstName", DBNull.Value);
var lastNameParameter = searchEmployeeDTO.LastName != null ?
new SqlParameter("LastName", searchEmployeeDTO.LastName) :
new SqlParameter("LastName", DBNull.Value);
var emailParameter = searchEmployeeDTO.Email != null ?
new SqlParameter("Email", searchEmployeeDTO.Email) :
new SqlParameter("Email", DBNull.Value);
var startDateParameter = searchEmployeeDTO.BeginDate.HasValue ?
new SqlParameter("BeginDate", searchEmployeeDTO.BeginDate) :
new SqlParameter("BeginDate", DBNull.Value);
var endDateParameter = searchEmployeeDTO.Enddate.HasValue ?
new SqlParameter("EndDate", searchEmployeeDTO.Enddate) :
new SqlParameter("EndDate", DBNull.Value);
var departmentIdParameter = searchEmployeeDTO.DepartmentId.HasValue ?
new SqlParameter("Department", searchEmployeeDTO.DepartmentId) :
new SqlParameter("Department", DBNull.Value);
IList <Employee> employeeList = new List <Employee>();
employeeList = portal.ObjectContext.ExecuteStoreQuery <Employee>(query, new object[] { firstNameParameter,
lastNameParameter, emailParameter, startDateParameter,
endDateParameter, departmentIdParameter })
.ToList();
if (employeeList.Count > 0)
{
retVal = new List <IEmployeeDTO>();
foreach (var employee in employeeList)
{
IEmployeeDTO employeeDTO = (IEmployeeDTO)DTOFactory.Instance.Create(DTOType.EmployeeDTO);
EntityConverter.FillDTOFromEntity(employee, employeeDTO);
retVal.Add(employeeDTO);
}
}
}
}
catch (Exception ex)
{
ExceptionManager.HandleException(ex);
throw new DACException(ex.Message);
}
return(retVal);
}
