ronaldkwandy

Untitled

Oct 28th, 2020
716
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package id.co.sofcograha.gajiidapi.domain.hris.attendance.rpt.displaydownloaddatasurat.repositories;
  2.  
  3. import java.util.Date;
  4.  
  5. import javax.persistence.EntityManager;
  6.  
  7. import org.springframework.stereotype.Repository;
  8. import org.springframework.transaction.annotation.Transactional;
  9.  
  10. import id.co.sofcograha.gajiidapi.base.extendables.BaseRepository;
  11. import id.co.sofcograha.gajiidapi.base.search.NativeSearchBuilder;
  12. import id.co.sofcograha.gajiidapi.base.search.SearchParameter;
  13. import id.co.sofcograha.gajiidapi.base.search.SearchResult;
  14. import id.co.sofcograha.gajiidapi.base.utils.QueryUtil;
  15. import id.co.sofcograha.gajiidapi.domain.hris.attendance.rpt.displaydownloaddatasurat.entities.RptDisplayDownloadDataSurat;
  16.  
  17. @Repository
  18. public class RptDisplayDownloadDataSuratRepository extends BaseRepository<RptDisplayDownloadDataSurat> {
  19.  
  20.     public RptDisplayDownloadDataSuratRepository(EntityManager em) {
  21.         super(RptDisplayDownloadDataSurat.class, em);
  22.     }
  23.    
  24.     @Transactional
  25.     public SearchResult<RptDisplayDownloadDataSurat> search(SearchParameter searchParameter) {
  26.         String nik = (String) searchParameter.getValueFromMappedParam("nik");
  27.         String nama = (String) searchParameter.getValueFromMappedParam("nama");
  28.         Date tanggalAwalAbsensi = (Date) searchParameter.getValueFromMappedParam("tanggalAwalAbsensi");
  29.         Date tanggalAkhirAbsensi = (Date) searchParameter.getValueFromMappedParam("tanggalAkhirAbsensi");
  30.         String jenisAbsensiId = (String) searchParameter.getValueFromMappedParam("jenisAbsensi.id");
  31.         Boolean konfirmasi = (Boolean) searchParameter.getValueFromMappedParam("konfirmasi");
  32.         String lokasiKerjaId = (String) searchParameter.getValueFromMappedParam("lokasiKerja.id");
  33.         String departemenId = (String) searchParameter.getValueFromMappedParam("departemen.id");
  34.         String jabatanId = (String) searchParameter.getValueFromMappedParam("jabatan.id");
  35.        
  36.         StringBuilder queryStringBuilder = new StringBuilder();
  37.         queryStringBuilder.append("SELECT          public.sys_guid() as id, d.id as hm_oem001_karyawan_id, d.employee_id as hm_oem001_karyawan_nik, d.name as hm_oem001_karyawan_name, \n");
  38.         queryStringBuilder.append("                g.id as hm_mem006_workplace_id, g.code as hm_mem006_workplace_code, g.name as hm_mem006_workplace_name, \n");
  39.         queryStringBuilder.append("                f.id as hm_mem004_department_id, f.code as hm_mem004_department_code, f.name as hm_mem004_department_name, \n");
  40.         queryStringBuilder.append("                e.id as hm_mem002_position_id, e.code as hm_mem002_position_code, e.name as hm_mem002_position_name, \n");
  41.         queryStringBuilder.append("                COALESCE(b.tanggal, a.tanggal) as tanggal_absensi, h.id as hm_mat005_jenis_absensi_id, h.nama as hm_mat005_jenis_absensi_nama, \n");
  42.         queryStringBuilder.append("                COALESCE(b.full_day, a.full_day) as full_day, COALESCE(b.awal_akhir_hari, a.awal_akhir_hari) as awal_akhir_hari, \n");
  43.         queryStringBuilder.append("                TO_CHAR(COALESCE(b.timestamp_awal, a.timestamp_awal), 'HH24MI') as jam_awal, TO_CHAR(COALESCE(b.timestamp_akhir, a.timestamp_akhir), 'HH24MI') as jam_akhir, \n");
  44.         queryStringBuilder.append("                COALESCE(b.jam_lembur, a.jam_lembur) as jam_lembur, COALESCE(b.jam_istirahat, a.jam_istirahat) as jam_istirahat, \n");
  45.         queryStringBuilder.append("                COALESCE(b.dapat_cuti_pengganti, a.dapat_cuti_pengganti) as dapat_cuti_pengganti, a.konfirmasi, a.keterangan, \n");
  46.         queryStringBuilder.append("                null as atasan_selanjutnya, \n"); // pending
  47.         queryStringBuilder.append("                0 as version, null as user_create, null as creation_time, null as user_modify, null as modification_time \n");
  48.         queryStringBuilder.append("FROM            hm_oat051 a \n");
  49.         queryStringBuilder.append("LEFT OUTER JOIN hm_oat027 b ON b.hm_oem001_karyawan_id = a.hm_oem001_karyawan_id AND b.tanggal = a.tanggal AND b.trx_number = a.trx_number \n");
  50.         queryStringBuilder.append("LEFT OUTER JOIN hm_oat030 c ON c.hm_oat027_data_surat_id = b.id \n");
  51.         queryStringBuilder.append("INNER JOIN      hm_oem001 d ON d.id = a.hm_oem001_karyawan_id \n");
  52.         queryStringBuilder.append("LEFT OUTER JOIN hm_mem002 e ON e.id = d.hm_mem002_position_id \n");
  53.         queryStringBuilder.append("LEFT OUTER JOIN hm_mem004 f ON f.id = d.hm_mem004_department_id \n");
  54.         queryStringBuilder.append("LEFT OUTER JOIN hm_mem006 g ON g.id = d.hm_mem006_workplace_id \n");
  55.         queryStringBuilder.append("LEFT OUTER JOIN hm_mat005 h ON h.id = COALESCE(b.hm_mat005_jenis_absensi_id, a.hm_mat005_jenis_absensi_id) \n");
  56.         queryStringBuilder.append("WHERE           1 = 1 \n");
  57.         if (!QueryUtil.isAll(nik)) {
  58.             queryStringBuilder.append("AND LOWER(d.employee_id) LIKE LOWER(:nik) \n");
  59.         }
  60.         if (!QueryUtil.isAll(nama)) {
  61.             queryStringBuilder.append("AND LOWER(d.name) LIKE LOWER(:nama) \n");
  62.         }
  63.         if (tanggalAwalAbsensi != null) {
  64.             queryStringBuilder.append("AND COALESCE(b.tanggal, a.tanggal) >= :tanggalAwalAbsensi \n");
  65.         }
  66.         if (tanggalAkhirAbsensi != null) {
  67.             queryStringBuilder.append("AND COALESCE(b.tanggal, a.tanggal) <= :tanggalAkhirAbsensi \n");
  68.         }
  69.         if (jenisAbsensiId != null) {
  70.             queryStringBuilder.append("AND h.id = :jenisAbsensiId \n");
  71.         }
  72.         if (konfirmasi != null) {
  73.             queryStringBuilder.append("AND a.konfirmasi = :konfirmasi \n");
  74.         }
  75.         if (lokasiKerjaId != null) {
  76.             queryStringBuilder.append("AND g.id = :lokasiKerjaId \n");
  77.         }
  78.         if (departemenId != null) {
  79.             queryStringBuilder.append("AND f.id = :departemenId \n");
  80.         }
  81.         if (jabatanId != null) {
  82.             queryStringBuilder.append("AND e.id = :jabatanId \n");
  83.         }
  84.         queryStringBuilder.append("UNION \n");
  85.         queryStringBuilder.append("SELECT          public.sys_guid() as id, b.id as hm_oem001_karyawan_id, b.employee_id as hm_oem001_karyawan_nik, b.name as hm_oem001_karyawan_name, \n");
  86.         queryStringBuilder.append("                e.id as hm_mem006_workplace_id, e.code as hm_mem006_workplace_code, e.name as hm_mem006_workplace_name, \n");
  87.         queryStringBuilder.append("                d.id as hm_mem004_department_id, d.code as hm_mem004_department_code, d.name as hm_mem004_department_name, \n");
  88.         queryStringBuilder.append("                e.id as hm_mem002_position_id, e.code as hm_mem002_position_code, e.name as hm_mem002_position_name, \n");
  89.         queryStringBuilder.append("                a.tanggal as tanggal_absensi, f.id as hm_mat005_jenis_absensi_id, f.nama as hm_mat005_jenis_absensi_nama, \n");
  90.         queryStringBuilder.append("                a.full_day as full_day, a.awal_akhir_hari as awal_akhir_hari, \n");
  91.         queryStringBuilder.append("                TO_CHAR(a.timestamp_awal, 'HH24MI') as jam_awal, TO_CHAR(a.timestamp_akhir, 'HH24MI') as jam_akhir, \n");
  92.         queryStringBuilder.append("                a.jam_lembur as jam_lembur, a.jam_istirahat as jam_istirahat, \n");
  93.         queryStringBuilder.append("                a.dapat_cuti_pengganti as dapat_cuti_pengganti, true as konfirmasi, '' as keterangan, \n");
  94.         queryStringBuilder.append("                null as atasan_selanjutnya, \n"); // pending
  95.         queryStringBuilder.append("                0 as version, null as user_create, null as creation_time, null as user_modify, null as modification_time \n");
  96.         queryStringBuilder.append("FROM            hm_oat030 a \n");
  97.         queryStringBuilder.append("INNER JOIN      hm_oem001 b ON b.id = a.hm_oem001_karyawan_id \n");
  98.         queryStringBuilder.append("LEFT OUTER JOIN hm_mem002 c ON c.id = b.hm_mem002_position_id \n");
  99.         queryStringBuilder.append("LEFT OUTER JOIN hm_mem004 d ON d.id = b.hm_mem004_department_id \n");
  100.         queryStringBuilder.append("LEFT OUTER JOIN hm_mem006 e ON e.id = b.hm_mem006_workplace_id \n");
  101.         queryStringBuilder.append("LEFT OUTER JOIN hm_mat005 f ON f.id = a.hm_mat005_jenis_absensi_id \n");
  102.         queryStringBuilder.append("WHERE           1 = 1 \n");
  103.         if (!QueryUtil.isAll(nik)) {
  104.             queryStringBuilder.append("AND LOWER(b.employee_id) LIKE LOWER(:nik) \n");
  105.         }
  106.         if (!QueryUtil.isAll(nama)) {
  107.             queryStringBuilder.append("AND LOWER(b.name) LIKE LOWER(:nama) \n");
  108.         }
  109.         if (tanggalAwalAbsensi != null) {
  110.             queryStringBuilder.append("AND a.tanggal >= :tanggalAwalAbsensi \n");
  111.         }
  112.         if (tanggalAkhirAbsensi != null) {
  113.             queryStringBuilder.append("AND a.tanggal <= :tanggalAkhirAbsensi \n");
  114.         }
  115.         if (jenisAbsensiId != null) {
  116.             queryStringBuilder.append("AND f.id = :jenisAbsensiId \n");
  117.         }
  118.         if (konfirmasi != null) {
  119.             queryStringBuilder.append("AND TRUE = :konfirmasi \n");
  120.         }
  121.         if (lokasiKerjaId != null) {
  122.             queryStringBuilder.append("AND e.id = :lokasiKerjaId \n");
  123.         }
  124.         if (departemenId != null) {
  125.             queryStringBuilder.append("AND d.id = :departemenId \n");
  126.         }
  127.         if (jabatanId != null) {
  128.             queryStringBuilder.append("AND c.id = :jabatanId \n");
  129.         }
  130.        
  131.         NativeSearchBuilder<RptDisplayDownloadDataSurat> search = new NativeSearchBuilder<>(RptDisplayDownloadDataSurat.class, em);
  132.         if (!QueryUtil.isAll(nik)) {
  133.             search.addParameter("nik", QueryUtil.formatStringForLikeFilter(nik));
  134.         }
  135.         if (!QueryUtil.isAll(nama)) {
  136.             search.addParameter("nama", QueryUtil.formatStringForLikeFilter(nama));
  137.         }
  138.         if (tanggalAwalAbsensi != null) {
  139.             search.addParameter("tanggalAwalAbsensi", tanggalAwalAbsensi);
  140.         }
  141.         if (tanggalAkhirAbsensi != null) {
  142.             search.addParameter("tanggalAkhirAbsensi", tanggalAkhirAbsensi);
  143.         }
  144.         if (jenisAbsensiId != null) {
  145.             search.addParameter("jenisAbsensiId", jenisAbsensiId);
  146.         }
  147.         if (konfirmasi != null) {
  148.             search.addParameter("konfirmasi", konfirmasi);
  149.         }
  150.         if (lokasiKerjaId != null) {
  151.             search.addParameter("lokasiKerjaId", lokasiKerjaId);
  152.         }
  153.         if (departemenId != null) {
  154.             search.addParameter("departemenId", departemenId);
  155.         }
  156.         if (jabatanId != null) {
  157.             search.addParameter("jabatanId", jabatanId);
  158.         }
  159.        
  160.         search.setQueryString(queryStringBuilder);
  161.         search.setPagination(searchParameter.getPagination());
  162.         search.setSort(searchParameter.getSort());
  163.  
  164.         return search.getSearchResult();
  165.     }
  166. }
  167.  
RAW Paste Data