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