Example usage for org.hibernate.transform Transformers aliasToBean

List of usage examples for org.hibernate.transform Transformers aliasToBean

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.transform.*
  5. Transformers
  6. aliasToBean

Introduction

In this page you can find the example usage for org.hibernate.transform Transformers aliasToBean.

Prototype

public static ResultTransformer aliasToBean(Class target)

Source Link

Document

Creates a resulttransformer that will inject aliased values into instances of Class via property methods or fields.

Usage

From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java

@Override
public List<ReportEmpPensionPreparationModel> getReportPensionPreparementByParam(List<Long> listDepartmentId,
        List<Long> listEmpTypeId, List<Integer> listEmpAges, int firstResult, int maxResults, Order order) {

    final StringBuilder query = new StringBuilder(
            "SELECT empData.NIK AS nik, bioData.first_name AS firstName, bioData.last_name AS lastName, "
                    + " empData.join_date AS tglMulaiBekerja, golonganJabatan.code AS golJabatan, bioData.date_of_birth AS tglLahir, "
                    + " umur(bioData.date_of_birth , NOW()) AS usiaKaryawan, "
                    + " jabatan.name AS jabatan, department.department_name AS departmentName, department.id AS departmentId, "
                    + " employeeType.id AS empTypeId , employeeType.name AS statusKaryawan FROM emp_data empData "
                    + " INNER JOIN golongan_jabatan golonganJabatan ON empData.gol_jab_id = golonganJabatan.id  "
                    + " INNER JOIN bio_data bioData ON empData.bio_data_id = bioData.id  "
                    + " INNER JOIN jabatan jabatan ON empData.jabatan_id = jabatan.id "
                    + " INNER JOIN department department ON jabatan.departement_id = department.id "
                    + " INNER JOIN company company ON company.id = department.company_id "
                    + " INNER JOIN employee_type employeeType ON empData.emp_type_id = employeeType.id ");

    //Flag Untuk penanda apakah ada filter atau tidak
    //boolean isFiltered = !listDepartmentId.isEmpty() || !listEmpTypeId.isEmpty() || !listEmpAges.isEmpty();
    //Flag untuk penanda jika filter lebih dari satu
    //boolean multipleFilter = Boolean.FALSE;
    //if (isFiltered) {
    query.append(" WHERE company.id = " + HrmUserInfoUtil.getCompanyId() + " ");
    //}/* w w  w  .j a  va 2 s  .  co m*/

    if (!listDepartmentId.isEmpty()) {
        query.append(" jabatan.departement_id IN( ");

        int size = listDepartmentId.size();
        //karena pakai native query, isi List harus di parsing satu per satu
        for (int i = 0; i < size; i++) {
            if (i < (size - 1)) {
                query.append(String.valueOf(listDepartmentId.get(i)));
                query.append(" , ");
            } else {
                query.append(String.valueOf(listDepartmentId.get(i)));
            }
        }

        query.append(") ");
        //multipleFilter = Boolean.TRUE;
    }

    if (!listEmpTypeId.isEmpty()) {
        //if (multipleFilter) {
        query.append("AND empData.emp_type_id IN( ");
        /*} else {
         query.append(" e.emp_type_id IN( ");
         multipleFilter = Boolean.TRUE;
         }*/

        //karena pakai native query, isi List harus di parsing satu per satu
        int size = listEmpTypeId.size();
        for (int i = 0; i < size; i++) {
            if (i < (size - 1)) {
                query.append(String.valueOf(listEmpTypeId.get(i)));
                query.append(" , ");
            } else {
                query.append(String.valueOf(listEmpTypeId.get(i)));
            }
        }

        query.append(") ");
    }

    if (!listEmpAges.isEmpty()) {
        //if (multipleFilter) {
        query.append(" AND umur(bioData.date_of_birth , NOW()) IN( ");
        /*} else {
         query.append("umur(b.date_of_birth , NOW()) IN( ");
         }*/

        //karena pakai native query, isi List harus di parsing satu per satu
        int size = listEmpAges.size();
        for (int i = 0; i < size; i++) {
            if (i < (size - 1)) {
                query.append(String.valueOf(listEmpAges.get(i)));
                query.append(" , ");
            } else {
                query.append(String.valueOf(listEmpAges.get(i)));
            }
        }

        query.append(") ");
    }

    query.append(" ORDER BY ");

    if (StringUtils.equals("nik", order.getPropertyName())) {
        query.append("empData.nik ");
    } else if (StringUtils.equals("firstName", order.getPropertyName())) {
        query.append("bioData.first_name ");
    } else if (StringUtils.equals("tglMulaiBekerja", order.getPropertyName())) {
        query.append("empData.join_date ");
    } else if (StringUtils.equals("golJabatan", order.getPropertyName())) {
        query.append("golonganJabatan.code ");
    } else if (StringUtils.equals("jabatan", order.getPropertyName())) {
        query.append("jabatan.name ");
    } else if (StringUtils.equals("usiaKaryawan", order.getPropertyName())) {
        query.append("umur(bioData.date_of_birth , NOW()) ");
    }

    query.append(order.isAscending() ? " ASC " : " DESC ");

    //Limit query based on paging parameter
    query.append("LIMIT ").append(firstResult).append(",").append(maxResults).append(" ");

    return getCurrentSession().createSQLQuery(query.toString())
            .setResultTransformer(Transformers.aliasToBean(ReportEmpPensionPreparationModel.class)).list();

}

From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java

@Override
public List<SearchEmployeeCandidateViewModel> getAllDataEmpCandidateByParamWithDetail(
        SearchEmployeeCandidateParameter searchEmployeeCandidateParameter) {

    String listEducationId = Lambda.join(searchEmployeeCandidateParameter.getListEducationlevelId(), "','");
    StringBuffer selectQuery = new StringBuffer(" SELECT empData.id AS empDataId, empData.nik AS nik, "
            + " bioData.first_name AS firstName, bioData.last_name AS lastName, "
            + " jabatan.id AS idJabatan, jabatan.name AS jabatanName, "
            + "  (   SELECT educationLevelInner.id FROM bio_education_history bioEduInner "
            + "     INNER JOIN education_level educationLevelInner ON bioEduInner.pendidikan_level_id =  educationLevelInner.id "
            + "    INNER JOIN bio_data bioDataInner ON bioEduInner.biodata_id = bioDataInner.id  "
            + "    WHERE bioDataInner.id = bioData.id " + "      AND educationLevelInner.id IN ('"
            + listEducationId + "') " + "    AND bioEduInner.score > "
            + searchEmployeeCandidateParameter.getGpa() + "  LIMIT 1 " + "  )    AS lastEducationLevelId, "
            + " religion.id AS idReligion, religion.name AS religionName " + " FROM emp_data empData "
            + " INNER JOIN  jabatan jabatan ON empData.jabatan_id = jabatan.id "
            + " INNER JOIN department department ON jabatan.departement_id = department.id "
            + " INNER JOIN company company  ON department.company_id = company.id "
            + " LEFT OUTER JOIN bio_data bioData ON empData.bio_data_id = bioData.id "
            + " INNER JOIN religion religion ON bioData.agama_id = religion.id " + " WHERE company.id = '"
            + HrmUserInfoUtil.getCompanyId() + "' ");

    selectQuery.append(setQueryParamForEmpCandidateSearchQuery(searchEmployeeCandidateParameter));
    Query hbm = getCurrentSession().createSQLQuery(selectQuery.toString());
    return hbm.setResultTransformer(Transformers.aliasToBean(SearchEmployeeCandidateViewModel.class)).list();

}

From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java

@SuppressWarnings("unchecked")
@Override/*from   w w w .j  av a2  s  .  c  o  m*/
public List<DepAttendanceRealizationViewModel> getListDepAttendanceByDepartmentIdAndRangeDate(Long departmentId,
        Date dateFrom, Date dateUntill) {

    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

    StringBuilder query = new StringBuilder(" SELECT  jabatan.departement_id AS departmentId, "
            + " WEEK(tempJadwalKaryawan.tanggal_waktu_kerja) AS weekNumber,"
            + " COUNT(tempJadwalKaryawan.working_our_id) as attendanceSchedule ,"
            + " COUNT(tempProcessReadFinger.working_hour_id) as attendanceReal,"
            + " (COUNT(tempProcessReadFinger.working_hour_id) / COUNT(tempJadwalKaryawan.working_our_id)) as attendancePercentage"
            + " FROM temp_jadwal_karyawan tempJadwalKaryawan"
            + " LEFT JOIN temp_process_read_finger tempProcessReadFinger ON tempProcessReadFinger.emp_data_id = tempJadwalKaryawan.emp_id"
            + " AND tempProcessReadFinger.schedule_date = tempJadwalKaryawan.tanggal_waktu_kerja"
            + " INNER JOIN emp_data empData ON tempJadwalKaryawan.emp_id = empData.id"
            + " INNER JOIN jabatan jabatan ON empData.jabatan_id = jabatan.id"
            + " INNER JOIN wt_working_hour wtWorkingHour ON tempJadwalKaryawan.working_our_id = wtWorkingHour.id"
            + " WHERE tempJadwalKaryawan.tanggal_waktu_kerja BETWEEN '" + dateFormat.format(dateFrom)
            + "' AND '" + dateFormat.format(dateUntill) + "' " + " AND wtWorkingHour.code <> 'OFF'"
            + " AND jabatan.departement_id =  " + departmentId + " AND empData.status <> '"
            + HRMConstant.EMP_TERMINATION + "' "
            + " GROUP BY WEEK(tempJadwalKaryawan.tanggal_waktu_kerja) , jabatan.departement_id ; ");

    return getCurrentSession().createSQLQuery(query.toString())
            .setResultTransformer(Transformers.aliasToBean(DepAttendanceRealizationViewModel.class)).list();
}

From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java

@Override
public List<DepAttendanceRealizationViewModel> getListDepAttendanceByListRangeDepIdAndRangeDate(
        String rangeDepId, Date dateFrom, Date dateUntill) {
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

    StringBuilder query = new StringBuilder(" SELECT  jabatan.departement_id AS departmentId, "
            + " WEEK(tempJadwalKaryawan.tanggal_waktu_kerja) AS weekNumber,"
            + " COUNT(tempJadwalKaryawan.working_our_id) as attendanceSchedule ,"
            + " COUNT(tempProcessReadFinger.finger_in) as attendanceReal,"
            + " (COUNT(tempProcessReadFinger.finger_in) / COUNT(tempJadwalKaryawan.working_our_id)) as attendancePercentage"
            + " FROM temp_jadwal_karyawan tempJadwalKaryawan"
            + " LEFT JOIN temp_process_read_finger tempProcessReadFinger ON tempProcessReadFinger.emp_data_id = tempJadwalKaryawan.emp_id"
            + " AND tempProcessReadFinger.schedule_date = tempJadwalKaryawan.tanggal_waktu_kerja"
            + " INNER JOIN emp_data empData ON tempJadwalKaryawan.emp_id = empData.id"
            + " INNER JOIN jabatan jabatan ON empData.jabatan_id = jabatan.id"
            + " INNER JOIN department department ON jabatan.departement_id = department.id"
            + " INNER JOIN wt_working_hour wtWorkingHour ON tempJadwalKaryawan.working_our_id = wtWorkingHour.id"
            + " WHERE tempJadwalKaryawan.tanggal_waktu_kerja BETWEEN '" + dateFormat.format(dateFrom)
            + "' AND '" + dateFormat.format(dateUntill) + "' " + " AND wtWorkingHour.code <> 'OFF'"
            + " AND department.id in(" + rangeDepId + " )" + " AND empData.status <> '"
            + HRMConstant.EMP_TERMINATION + "' "
            + " GROUP BY WEEK(tempJadwalKaryawan.tanggal_waktu_kerja) , jabatan.departement_id ; ");

    return getCurrentSession().createSQLQuery(query.toString())
            .setResultTransformer(Transformers.aliasToBean(DepAttendanceRealizationViewModel.class)).list();
}

From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java

public List<RecruitAgreementNoticeViewModel> getAllEmployeeForRecruitAggrementNoticeWithNativeQuery(
        RecruitAgreementNoticeSearchParameter searchParameter, int firstResult, int maxResults,
        Order orderable) {/*from w w w.  j a  v  a 2  s.  co  m*/
    final StringBuilder query = new StringBuilder(
            "SELECT emp.id as employeeId, bio.id as bioDataId, bio.first_name as firstName, bio.last_name as lastName, jabatan.name as jabatanName, pangkat.pangkat_name as pangkatName, bio.date_of_birth as birthOfDate,");
    query.append(" (SELECT pangkat2.pangkat_name FROM pangkat pangkat2");
    query.append(" WHERE pangkat2.level < pangkat.level ORDER BY LEVEL DESC LIMIT 1) as jabatanDituju,");
    query.append(" pangkat.level,");
    query.append(" (SELECT el.name FROM education_level el ");
    query.append(" INNER JOIN bio_education_history beh ON el.id = beh.pendidikan_level_id");
    query.append(" INNER JOIN bio_data bio2 ON bio2.id = beh.biodata_id");
    query.append(" WHERE beh.biodata_id = bio.id ORDER by el.name DESC LIMIT 1 ) as lastEducationLevel");
    query.append(" FROM bio_data bio ");
    query.append(" LEFT JOIN emp_data emp ON bio.id = emp.bio_data_id");
    /*         query.append(" INNER JOIN bio_education_history beh ON beh.biodata_id = bio.id");
     query.append(" INNER JOIN education_level el ON el.id = beh.pendidikan_level_id");*/
    query.append(" LEFT JOIN jabatan jabatan ON emp.jabatan_id = jabatan.id");
    query.append(" LEFT JOIN golongan_jabatan goljab ON goljab.id = emp.gol_jab_id");
    query.append(" LEFT JOIN pangkat pangkat ON goljab.pangkat_id = pangkat.id");
    //query for action searching
    doSearchEmployeeForRecruitAgreementNoticeWithNativeQuery(searchParameter, query);
    //order query base on orderable
    query.append(" ORDER BY ");

    if (StringUtils.equals("firstName", orderable.getPropertyName())) {
        query.append("firstName ");
    } else if (StringUtils.equals("jabatanName", orderable.getPropertyName())) {
        query.append("jabatanName ");
    } else if (StringUtils.equals("jabatanDituju", orderable.getPropertyName())) {
        query.append("jabatanDituju ");
    } else if (StringUtils.equals("lastEducationLevel", orderable.getPropertyName())) {
        query.append("lastEducationLevel ");
    } else if (StringUtils.equals("birthOfDate", orderable.getPropertyName())) {
        query.append("birthOfDate ");
    }

    query.append(orderable.isAscending() ? " ASC " : " DESC ");

    //Limit query based on paging parameter
    query.append(" LIMIT ").append(firstResult).append(",").append(maxResults).append(" ");
    return getCurrentSession().createSQLQuery(query.toString())
            .setResultTransformer(Transformers.aliasToBean(RecruitAgreementNoticeViewModel.class)).list();
}

From source file:com.inkubator.hrm.dao.impl.JabatanDaoImpl.java

@Override
public List<KompetensiJabatanViewModel> getByParamForKompetensiJabatan(
        KompetensiJabatanSearchParameter searchParameter, int firstResult, int maxResults, Order order) {
    final StringBuilder query = new StringBuilder("SELECT jabatan.code AS jabatanCode,");
    query.append(" jabatan.id AS id,");
    query.append(" jabatan.name AS jabatanName,");
    query.append(" golonganJabatan.code AS golonganJabatanCode ");
    query.append(" FROM Jabatan jabatan ");
    query.append(" INNER JOIN jabatan.golonganJabatan golonganJabatan ");

    //filter by search param
    query.append(doSearchByParamForKompetensiJabatan(searchParameter));
    query.append(" ORDER BY " + order);

    Query hbm = getCurrentSession().createQuery(query.toString());
    hbm = this.setValueQueryKompetensiJabatanViewModelByParam(hbm, searchParameter);

    return hbm.setMaxResults(maxResults).setFirstResult(firstResult)
            .setResultTransformer(Transformers.aliasToBean(KompetensiJabatanViewModel.class)).list();
}

From source file:com.inkubator.hrm.dao.impl.JabatanDaoImpl.java

@Override
public List<PerformanceIndicatorJabatanViewModel> getByParamForPerformanceIndicatorJabatan(
        PerformanceIndicatorJabatanSearchParameter searchParameter, int firstResult, int maxResults,
        Order order) {//from  w ww .jav  a  2s.com
    StringBuffer selectQuery = new StringBuffer("SELECT jabatan.id AS jabatanId, "
            + "jabatan.code AS jabatanCode, " + "jabatan.name AS jabatanName, "
            + "CONCAT(golonganJabatan.code,' - ',pangkat.pangkatName) AS golonganJabatan, "
            + "SUM(CASE WHEN emp IS NULL THEN 0 ELSE 1 END) AS totalEmployee " + "FROM Jabatan AS jabatan "
            + "JOIN jabatan.golonganJabatan AS golonganJabatan " + "JOIN golonganJabatan.pangkat AS pangkat "
            + "LEFT JOIN jabatan.empByJabatans AS emp ");
    selectQuery.append(this.getWhereQueryByParamForPerformanceIndicatorJabatan(searchParameter));
    selectQuery.append("GROUP BY jabatan.id ");
    selectQuery.append("ORDER BY " + order);

    Query hbm = getCurrentSession().createQuery(selectQuery.toString()).setMaxResults(maxResults)
            .setFirstResult(firstResult)
            .setResultTransformer(Transformers.aliasToBean(PerformanceIndicatorJabatanViewModel.class));
    hbm = this.setValueQueryByParamForPerformanceIndicatorJabatan(hbm, searchParameter);

    return hbm.list();
}

From source file:com.inkubator.hrm.dao.impl.LoanNewApplicationDaoImpl.java

@Override
public List<LoanNewApplicationBoxViewModel> getUndisbursedActivityByParam(
        LoanNewApplicationBoxSearchParameter parameter, int firstResult, int maxResults, Order orderable) {

    StringBuffer selectQuery = new StringBuffer("SELECT approvalActivity.id AS approvalActivityId, "
            + "approvalActivity.activity_number AS activityNumber, "
            + "loanApplication.id AS loanNewApplicationId, " + "empData.nik AS empNik, "
            + "CONCAT(bioData.first_name,' ',bioData.last_name) AS empName, "
            + "empDataApprover.nik AS approverNik, "
            + "CONCAT(bioDataApprover.first_name,' ',bioDataApprover.last_name) AS approverName, "
            + "loanType.id AS loanNewTypeId, " + "loanType.loan_type_name AS loanNewTypeName, "
            + "approvalActivity.approval_status AS approvalStatus, "
            + "approvalActivity.pending_data AS jsonData " + "FROM approval_activity approvalActivity "
            + "INNER JOIN approval_definition AS approvalDefinition ON approvalDefinition.id = approvalActivity.approval_def_id "
            + "INNER JOIN hrm_user AS approver ON approver.user_id = approvalActivity.approved_by  "
            + "INNER JOIN hrm_user AS requester ON requester.user_id = approvalActivity.request_by  "
            + "INNER JOIN emp_data AS empData ON requester.emp_data_id = empData.id  "
            + "INNER JOIN jabatan AS jabatan ON empData.jabatan_id = jabatan.id  "
            + "INNER JOIN department AS department ON jabatan.departement_id = department.id  "
            + "INNER JOIN company AS company ON department.company_id = company.id  "
            + "INNER JOIN bio_data AS bioData ON empData.bio_data_id = bioData.id  "
            + "INNER JOIN emp_data AS empDataApprover ON approver.emp_data_id = empDataApprover.id  "
            + "INNER JOIN bio_data AS bioDataApprover ON empDataApprover.bio_data_id = bioDataApprover.id  "
            + "INNER JOIN loan_new_type AS loanType ON approvalActivity.type_specific = loanType.id  "
            + "LEFT JOIN loan_new_application AS loanApplication ON approvalActivity.activity_number = loanApplication.approval_activity_number  "
            + "WHERE (approvalActivity.activity_number,approvalActivity.sequence) IN (SELECT app.activity_number,max(app.sequence) FROM approval_activity app GROUP BY app.activity_number)  "
            + "AND approvalDefinition.name = :appDefinitionName" + " AND  company.id = :companyId ");

    selectQuery.append(this.setWhereQueryUndisbursedActivityByParam(parameter));
    selectQuery.append("GROUP BY approvalActivity.activity_number ");
    selectQuery.append("ORDER BY ").append(orderable);

    Query hbm = getCurrentSession().createSQLQuery(selectQuery.toString()).setMaxResults(maxResults)
            .setFirstResult(firstResult)
            .setResultTransformer(Transformers.aliasToBean(LoanNewApplicationBoxViewModel.class));
    hbm = this.setValueQueryUndisbursedActivityByParam(hbm, parameter);

    return hbm.list();
}

From source file:com.inkubator.hrm.dao.impl.LoanNewApplicationDaoImpl.java

@Override
public List<LoanHistoryViewModel> getListLoanHistoryByEmpDataId(Long empDataId) {
    StringBuffer selectQuery = new StringBuffer(" SELECT loanNewApplication.id AS loanNewApplicationId, "
            + " loanNewApplication.applicationDate AS tglPengajuan, "
            + " loanNewApplication.loanStatus AS loanStatus, " + " loanNewType.id AS loanNewTypeId, "
            + " loanNewType.loanTypeName AS loanNewTypeName, "
            + " loanNewApplication.nominalPrincipal AS loanNominal, "
            + " loanNewApplication.termin AS totalNumberOfInstallment, "
            + " loanNewType.interestMethod AS typeOfInterest, " + " loanNewType.interest AS loanInterestRate, "
            + " loanNewApplication.dibursementDate AS loanPaymentDate, "
            + " loanNewApplication.bufferTime AS buffer " + " FROM LoanNewApplication loanNewApplication "
            + " INNER JOIN loanNewApplication.empData empData  "
            + " INNER JOIN loanNewApplication.loanNewType loanNewType  "
            + " INNER JOIN loanNewApplication.loanNewSchema loanNewSchema  "
            + " WHERE empData.id = :empDataId ");

    Query hbm = getCurrentSession().createQuery(selectQuery.toString()).setParameter("empDataId", empDataId)
            .setResultTransformer(Transformers.aliasToBean(LoanHistoryViewModel.class));

    return hbm.list();
}

From source file:com.inkubator.hrm.dao.impl.LoanNewApplicationDaoImpl.java

@Override
public List<LoanNewApplicationStatusViewModel> getAllDataLoanNewApplicationStatus(
        LoanStatusSearchParameter parameter, int firstResult, int maxResults, Order orderable) {

    StringBuffer selectQuery = new StringBuffer(" SELECT loanNewApplication.id AS id,"
            + " bioData.first_name AS firstName, " + " bioData.last_name AS lastName,"
            + " loanNewSchema.loan_schema_name AS loanSchemaName, "
            + " loanNewApplication.nominal_principal AS nominalPrincipal,"
            + " approvalDefinition.name AS approvalName, "
            + " loanNewApplication.dibursement_date AS dibursmentDate, "
            + " loanNewApplication.purpose_note AS purposeNote, "
            + " loanNewApplication.loan_status AS loanStatus, " + " loanNewApplication.termin AS totalTermin, "
            + "    (SELECT MAX(loanNewApplicationInstallment.num_of_installment) "
            + "    FROM loan_new_application_installment loanNewApplicationInstallment "
            + "    WHERE loanNewApplicationInstallment.installment_date < now() "
            + "      AND loanNewApplicationInstallment.loan_new_application_id = loanNewApplication.id)"
            + "      AS termin, " + " approvalActivity.pending_data AS jsonData,"
            + " loanNewApplication.approval_activity_number AS approvalActivityNumber, "
            + " approvalActivity.approval_status AS approvalStatus,"
            + " approvalActivity.activity_number AS activityNumber "
            + " FROM hrm.approval_activity approvalActivity "
            + " LEFT JOIN approval_definition AS approvalDefinition ON approvalDefinition.id = approvalActivity.approval_def_id "
            + "   LEFT JOIN hrm_user AS requester ON requester.user_id = approvalActivity.request_by "
            + " LEFT JOIN emp_data AS empData ON requester.emp_data_id = empData.id "
            + " INNER JOIN golongan_jabatan AS golonganJabatan ON empData.gol_jab_id = golonganJabatan.id "
            + " INNER JOIN jabatan AS jabatan ON empData.jabatan_id = jabatan.id "
            + " INNER JOIN department AS department ON jabatan.departement_id = department.id "
            + " INNER JOIN company AS company ON department.company_id = company.id "
            + " LEFT JOIN bio_data AS bioData ON empData.bio_data_id = bioData.id "
            + " LEFT JOIN loan_new_application AS loanNewApplication ON approvalActivity.activity_number = loanNewApplication.approval_activity_number "
            + " LEFT JOIN loan_new_schema AS loanNewSchema ON loanNewApplication.loan_new_schema = loanNewSchema.id "
            + " WHERE approvalDefinition.name = '" + HRMConstant.LOAN + "' "
            + "AND (approvalActivity.activity_number,approvalActivity.sequence) IN (SELECT app.activity_number, MAX(app.sequence)"
            + " FROM approval_activity app GROUP BY app.activity_number) ");

    selectQuery.append(this.doSearchLoanNewApplicationStatus(parameter));
    selectQuery.append(" ORDER BY " + orderable);

    Query hbm = getCurrentSession().createSQLQuery(selectQuery.toString()).setMaxResults(maxResults)
            .setFirstResult(firstResult)
            .setResultTransformer(Transformers.aliasToBean(LoanNewApplicationStatusViewModel.class));

    return hbm.list();
}