Employee Search

  public void initPage() {
    SearchResultsVOImpl resultVO = this.getSearchResults();
    resultVO.executeEmptyRowSet();
   
    SearchCriteriaVOImpl criteriaVO = this.getSearchCriteria();
    RowSetIterator criteriaRSI = criteriaVO.createRowSetIterator("delete");
    while (criteriaRSI.hasNext()) {
      criteriaRSI.next().remove(); 
    }
    criteriaRSI.closeRowSetIterator();
    criteriaVO.insertRow(criteriaVO.createRow());
  }
 
  public void executeSearch() {
    SearchResultsVOImpl resultVO = this.getSearchResults();
    resultVO.setWhereClause(null);
    resultVO.setNestedSelectForFullSql(false);
   
    StringBuffer whereClause = new StringBuffer();
   
    SearchCriteriaVOImpl criteriaVO = this.getSearchCriteria();
    Row[] existingRows = criteriaVO.getAllRowsInRange();
    if (existingRows != null || existingRows.length > 0) {
      SearchCriteriaVORowImpl criteriaRow = (SearchCriteriaVORowImpl)existingRows[0];
      whereClause.append("\n EMP.JOB_ID = JOB.JOB_ID")
                 .append("\n AND  EMP.MANAGER_ID  = MGR.EMPLOYEE_ID (+)")
                 .append("\n AND  EMP.DEPARTMENT_ID  = DEPT.DEPARTMENT_ID (+)")
                 .append("\n AND  DEPT.LOCATION_ID = LOC.LOCATION_ID (+)")
                 .append("\n AND  LOC.COUNTRY_ID = COUNTRY.COUNTRY_ID (+)");
     
      if (criteriaRow.getEmpId() != null) {
        whereClause.append("\n AND  EMP.EMPLOYEE_ID = '" + criteriaRow.getEmpId() + "'");
      }
     
      if (criteriaRow.getFirstName() != null) {
        whereClause.append("\n AND  UPPER(EMP.FIRST_NAME) LIKE '%' || UPPER('" + criteriaRow.getFirstName() + "') || '%'");
      }
     
      if (criteriaRow.getLastName() != null) {
        whereClause.append("\n AND  UPPER(EMP.LAST_NAME) LIKE '%' || UPPER('" + criteriaRow.getLastName() + "') || '%'");
      }
     
      if (criteriaRow.getEmail() != null) {
        whereClause.append("\n AND  UPPER(EMP.EMAIL) LIKE '%' || UPPER('" + criteriaRow.getEmail() + "') || '%'"); 
      }
     
      if (criteriaRow.getHireDate() != null) {
        whereClause.append("\n AND  EMP.HIRE_DATE = TO_DATE('" + criteriaRow.getHireDate() + "', 'YYYY-MM-DD')");
      }
     
      if (criteriaRow.getJobTitle() != null) {
        whereClause.append("\n AND  JOB.JOB_TITLE = '" + criteriaRow.getJobTitle() + "'");
      }
     
      if (criteriaRow.getMgrName() != null) {
        whereClause.append("\n AND  UPPER(MGR.FIRST_NAME || ' ' || MGR.LAST_NAME) LIKE '%' || UPPER('" + criteriaRow.getMgrName() + "') || '%'"); 
      }
     
      if (criteriaRow.getSalaryMin() != null && criteriaRow.getSalaryMax() == null) {
        whereClause.append("\n AND  EMP.SALARY >= '" + criteriaRow.getSalaryMin() + "'");
      }
     
      if (criteriaRow.getSalaryMin() != null && criteriaRow.getSalaryMax() != null) {
        whereClause.append("\n AND  EMP.SALARY BETWEEN '" + criteriaRow.getSalaryMin() + "' AND '" + criteriaRow.getSalaryMax() + "'");
      }
     
      if (criteriaRow.getSalaryMin() == null && criteriaRow.getSalaryMax() != null) {
        whereClause.append("\n AND  EMP.SALARY <= '" + criteriaRow.getSalaryMax() + "'");
      }
     
      if (criteriaRow.getDeptName() != null) {
        whereClause.append("\n AND  DEPT.DEPARTMENT_NAME = '" + criteriaRow.getDeptName() + "'");
      }
     
      if (criteriaRow.getCity() != null) {
        whereClause.append("\n AND  LOC.CITY = '" + criteriaRow.getCity() + "'");
      }
     
      if (criteriaRow.getCountry() != null) {
        whereClause.append("\n AND  COUNTRY.COUNTRY_NAME = '" + criteriaRow.getCountry() + "'"); 
      }
     
      resultVO.addWhereClause(whereClause.toString());
      System.out.println(resultVO.getQuery());
      resultVO.executeQuery();
    } 
  }