package com.jasamedika.medifirst2000.dao.custom;

import java.util.List;
import java.util.Map;

import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.jasamedika.medifirst2000.dao.custom.base.impl.CoreDaoImpl;
import com.jasamedika.medifirst2000.entities.PegawaiHistoriRekapGaji;
import com.jasamedika.medifirst2000.util.CommonUtil;

@Repository
public class GajiPegawaiDaoCustomImpl extends CoreDaoImpl<PegawaiHistoriRekapGaji> implements GajiPegawaiDaoCustom {

	public GajiPegawaiDaoCustomImpl() {
		super(PegawaiHistoriRekapGaji.class);
	}

	@Override
	public int findCount(String dateStart, String dateEnd, String detailKategoryPegawai) {

		StringBuffer buffer = new StringBuffer();
		buffer.append("select count(strukHistori.noRec) from PegawaiHistoriRekapGaji gaji "
				+ "left join gaji.strukHistory strukHistori left join gaji.pegawai pegawai "
				+ "left join pegawai.detailKategoryPegawai dkp left join pegawai.golonganPegawai golongan "
				+ "where strukHistori.tglHistori is not null ");

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("and date(strukHistori.tglAwal) between date(:dateStart) and date(:dateEnd) ");
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			buffer.append("and lower(dkp.detailKategoryPegawai) like lower(:detailKategoryPegawai) ");
		}

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			query.setParameter("dateStart", dateStart);
			query.setParameter("dateEnd", dateEnd);
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			query.setParameter("detailKategoryPegawai", "%" + detailKategoryPegawai + "%");
		}

		return ((Long) query.getSingleResult()).intValue();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDaftarGajiPegawai(String sort, String dir, String dateStart, String dateEnd,
			String detailKategoryPegawai, String noRec, String gridFor, List<Integer> ruanganId, String status) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select distinct new map(strukHistori.noRec as noRec,sv.noRec as noRecVerif,strukHistori.tglAwal as tglAwal,"
						+ "(CASE WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 1 THEN 'JANUARI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 2 THEN 'FEBRUARI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 3 THEN 'MARET' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 4 THEN 'APRIL' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 5 THEN 'MEI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 6 THEN 'JUNI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 7 THEN 'JULI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 8 THEN 'AGUSTUS' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 9 THEN 'SEPTEMBER' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 10 THEN 'OKTOBER' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 11 THEN 'NOVEMBER' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 12 THEN 'DESEMBER' ELSE NULL END) as bulan,"
						+ "EXTRACT(year FROM (strukHistori.tglAwal)) as tahun,strukHistori.tglAhir as tglAkhir,"
						+ "strukHistori.tglHistori as tglHistori,dkp.detailKategoryPegawai as jenisPegawai,SUM(gaji.hargaSatuan) as total,"
						+ "svSet.noverifikasi as noVerifikasi,svSet.tglEksekusi as tglEksekusi,svSet.tglverifikasi as tglVerifikasi,"
						+ "svSet.noUrutRuangan as noUrutRuangan,ruanganVerifikasi.id as idRuangan,"
						+ "ruanganVerifikasi.namaRuangan as namaRuangan,'BELUM VERIFIKASI' as statusVerif,"
						+ "(CASE WHEN noSbk.totalSisaHutang is null THEN 'BELUM LUNAS' "
						+ "WHEN noSbk.totalSisaHutang is not null and noSbk.totalSisaHutang > 0 THEN 'KREDIT' "
						+ "WHEN noSbk.totalSisaHutang = 0 THEN 'LUNAS' ELSE '' END) as status) "
						+ "from StrukHistori strukHistori left join strukHistori.pegawaiHistoriRekap phr left join phr.noStrukPelayanan noStruk "
						+ "left join noStruk.nosbklast noSbk "
						+ "left join phr.strukVerifikasi sv left join sv.strukVerifikasiSet svSet left join svSet.ruangan ruanganVerifikasi "
						+ "left join strukHistori.rekapGaji gaji left join gaji.pegawai pegawai "
						+ "left join pegawai.detailKategoryPegawai dkp left join pegawai.golonganPegawai golongan "
						+ "where strukHistori.tglHistori is not null "
						+ "and svSet.noUrutRuangan in (select min(svSetf.noUrutRuangan) from StrukVerifikasi svf left join svf.strukVerifikasiSet svSetf "
						+ "where svSetf.noverifikasi = svSet.noverifikasi and svSetf.tglEksekusi is null) and gaji.hargaSatuan is not null ");

		// cek jika request dari ruangan
		if (CommonUtil.isNotNullOrEmpty(ruanganId)) {
			buffer.append("and ruanganVerifikasi.id in (:ruanganId) ");
		}

		if (CommonUtil.isNotNullOrEmpty(status)) {
			if (status.equalsIgnoreCase("LUNAS")) {
				buffer.append("and noSbk.totalSisaHutang = 0 ");
			} else if (status.equalsIgnoreCase("KREDIT")) {
				buffer.append("and noSbk.totalSisaHutang is not null and noSbk.totalSisaHutang > 0 ");
			} else if (status.equalsIgnoreCase("BELUMLUNAS")) {
				buffer.append("and noSbk.totalSisaHutang is null ");
			}
		}

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("and date(strukHistori.tglHistori) between date(:dateStart) and date(:dateEnd) ");
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			buffer.append("and lower(dkp.detailKategoryPegawai) like lower(:detailKategoryPegawai) ");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			buffer.append("and strukHistori.noRec=:noRec ");
		}

		buffer.append("group by strukHistori.noRec,sv.noRec,strukHistori.tglHistori,dkp.detailKategoryPegawai,"
				+ "svSet.noverifikasi,svSet.tglEksekusi,svSet.tglverifikasi,"
				+ "svSet.noUrutRuangan,ruanganVerifikasi.id,ruanganVerifikasi.namaRuangan,noSbk.totalSisaHutang ");

		buffer.append("order by strukHistori.tglAwal asc ");

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(ruanganId)) {
			query.setParameter("ruanganId", ruanganId);
		}

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			query.setParameter("dateStart", dateStart);
			query.setParameter("dateEnd", dateEnd);
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			query.setParameter("detailKategoryPegawai", "%" + detailKategoryPegawai + "%");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			query.setParameter("noRec", noRec);
		}

		List<Map<String, Object>> list = query.getResultList();

		return list;
	}

	@Override
	public int findCountDetailGajiPegawai(String dateStart, String dateEnd, String detailKategoryPegawai) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select count(sh.noRec) from PegawaiHistoriRekapGaji rg left join rg.strukHistory sh left join sh.ruangan ruangan "
						+ "left join sh.jenisGaji jg left join sh.kelompokTransaksi kt "
						+ "left join rg.komponenHarga kh left join rg.pegawai pgw left join pgw.golonganPegawai gp "
						+ "left join pgw.detailKategoryPegawai dkp "
						+ "where sh.tglAwal is not null and sh.tglHistori is not null and rg.hargaSatuan is not null and rg.hargaSatuan != 0 ");

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("and date(sh.tglAwal) between date(:dateStart) and date(:dateEnd) ");
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			buffer.append("and lower(dkp.detailKategoryPegawai) like lower(:detailKategoryPegawai) ");
		}

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			query.setParameter("dateStart", dateStart);
			query.setParameter("dateEnd", dateEnd);
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			query.setParameter("detailKategoryPegawai", "%" + detailKategoryPegawai + "%");
		}

		return ((Long) query.getSingleResult()).intValue();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDetailGajiPegawai(String sort, String dir, String dateStart, String dateEnd,
			String detailKategoryPegawai, String noRec, Integer idPegawai) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select distinct new map(sh.noRec as noRec,ruangan.namaRuangan as namaRuangan,jg.name as jenisGaji,"
						+ "kt.kelompokTransaksi as kelTransaksi,dkp.detailKategoryPegawai as detailKategori,"
						+ "SUM(rg.hargaSatuan) as total,"
						+ "gp.golonganPegawai as golPegawai,pgw.bankRekeningAtasNama as rekeningAtasNama,pgw.bankRekeningNama as namaBank,"
						+ "pgw.bankRekeningNomor as noRekening,pgw.id as idPegawai,pgw.namaLengkap as namaPegawai,"
						+ "pgw.nipPns as nipPns) from PegawaiHistoriRekapGaji rg left join rg.strukHistory sh left join sh.ruangan ruangan "
						+ "left join sh.jenisGaji jg left join sh.kelompokTransaksi kt "
						+ "left join rg.komponenHarga kh left join rg.pegawai pgw left join pgw.golonganPegawai gp "
						+ "left join pgw.detailKategoryPegawai dkp where sh.tglHistori is not null ");

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("and date(sh.tglHistori) between date(:dateStart) and date(:dateEnd) ");
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			buffer.append("and lower(dkp.detailKategoryPegawai) like lower(:detailKategoryPegawai) ");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			buffer.append("and sh.noRec=:noRec ");
		}

		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			buffer.append("and pgw.id=:idPegawai ");
		}

		buffer.append(
				"group by sh.noRec,pgw.id,dkp.detailKategoryPegawai,ruangan.namaRuangan,jg.name,kt.kelompokTransaksi,"
						+ "gp.golonganPegawai,pgw.nomorRekening,pgw.namaLengkap,pgw.nipPns ");

		buffer.append("order by pgw.namaLengkap asc ");

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			query.setParameter("dateStart", dateStart);
			query.setParameter("dateEnd", dateEnd);
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			query.setParameter("detailKategoryPegawai", "%" + detailKategoryPegawai + "%");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			query.setParameter("noRec", noRec);
		}

		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			query.setParameter("idPegawai", idPegawai);
		}

		List<Map<String, Object>> list = query.getResultList();

		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> getTotalGajiPegawai(String dateStart, String dateEnd, String detailKategoryPegawai) {

		StringBuffer buffer = new StringBuffer();
		buffer.append("select new map(SUM(gaji.hargaSatuan) as totalGaji) "
				+ "from PegawaiHistoriRekapGaji gaji left join gaji.strukHistory strukHistori "
				+ "left join gaji.pegawai pegawai left join pegawai.detailKategoryPegawai dkp "
				+ "left join pegawai.golonganPegawai golongan "
				+ "where strukHistori.tglHistori is not null and strukHistori.tglAwal is not null ");

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("and date(strukHistori.tglAwal) between date(:dateStart) and date(:dateEnd) ");
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			buffer.append("and lower(dkp.detailKategoryPegawai) like lower(:detailKategoryPegawai) ");
		}

		buffer.append("order by pegawai.namaLengkap asc ");

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			query.setParameter("dateStart", dateStart);
			query.setParameter("dateEnd", dateEnd);
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			query.setParameter("detailKategoryPegawai", "%" + detailKategoryPegawai + "%");
		}

		Map<String, Object> map = (Map<String, Object>) query.getSingleResult();

		return map;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDetailKompHarga(String dateStart, String dateEnd, String detailKategoryPegawai,
			String noRec, Integer idPegawai) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select new map(kompHarga.komponenHarga as kompHarga,SUM(rg.hargaSatuan) as harga,pgw.id as idPegawai) "
						+ "from PegawaiHistoriRekapGaji rg left join rg.strukHistory sh left join sh.ruangan ruangan "
						+ "left join sh.jenisGaji jg left join sh.kelompokTransaksi kt left join rg.pegawai pgw left join pgw.golonganPegawai gp "
						+ "left join pgw.detailKategoryPegawai dkp left join rg.komponenHarga kompHarga where sh.tglHistori is not null ");

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("and date(sh.tglHistori) between date(:dateStart) and date(:dateEnd) ");
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			buffer.append("and lower(dkp.detailKategoryPegawai) like lower(:detailKategoryPegawai) ");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			buffer.append("and sh.noRec=:noRec ");
		}

		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			buffer.append("and pgw.id=:idPegawai ");
		}

		buffer.append("group by kompHarga.komponenHarga,pgw.id,pgw.namaLengkap ");
		buffer.append("order by pgw.namaLengkap asc ");

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			query.setParameter("dateStart", dateStart);
			query.setParameter("dateEnd", dateEnd);
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			query.setParameter("detailKategoryPegawai", "%" + detailKategoryPegawai + "%");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			query.setParameter("noRec", noRec);
		}

		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			query.setParameter("idPegawai", idPegawai);
		}

		List<Map<String, Object>> list = query.getResultList();

		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDaftarGajiPegawaiVerifikasi(String sort, String dir, String dateStart,
			String dateEnd, String detailKategoryPegawai, String noRec, String gridFor, List<Integer> ruanganId,
			String status) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select distinct new map(strukHistori.noRec as noRec,sv.noRec as noRecVerif,strukHistori.tglAwal as tglAwal,"
						+ "(CASE WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 1 THEN 'JANUARI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 2 THEN 'FEBRUARI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 3 THEN 'MARET' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 4 THEN 'APRIL' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 5 THEN 'MEI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 6 THEN 'JUNI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 7 THEN 'JULI' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 8 THEN 'AGUSTUS' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 9 THEN 'SEPTEMBER' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 10 THEN 'OKTOBER' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 11 THEN 'NOVEMBER' "
						+ "WHEN EXTRACT(month FROM (strukHistori.tglAwal)) = 12 THEN 'DESEMBER' ELSE NULL END) as bulan,"
						+ "EXTRACT(year FROM (strukHistori.tglAwal)) as tahun,strukHistori.tglAhir as tglAkhir,"
						+ "strukHistori.tglHistori as tglHistori,dkp.detailKategoryPegawai as jenisPegawai,SUM(gaji.hargaSatuan) as total,"
						+ "svSet.noverifikasi as noVerifikasi,svSet.tglEksekusi as tglEksekusi,svSet.tglverifikasi as tglVerifikasi,"
						+ "svSet.noUrutRuangan as noUrutRuangan,ruanganVerifikasi.id as idRuangan,"
						+ "ruanganVerifikasi.namaRuangan as namaRuangan,'VERIFIKASI' as statusVerif,"
						+ "(CASE WHEN noSbk.totalSisaHutang is null THEN 'BELUM LUNAS' "
						+ "WHEN noSbk.totalSisaHutang is not null and noSbk.totalSisaHutang > 0 THEN 'KREDIT' "
						+ "WHEN noSbk.totalSisaHutang = 0 THEN 'LUNAS' ELSE '' END) as status) "
						+ "from StrukHistori strukHistori left join strukHistori.pegawaiHistoriRekap phr left join phr.noStrukPelayanan noStruk "
						+ "left join noStruk.nosbklast noSbk "
						+ "left join phr.strukVerifikasi sv left join sv.strukVerifikasiSet svSet left join svSet.ruangan ruanganVerifikasi "
						+ "left join strukHistori.rekapGaji gaji left join gaji.pegawai pegawai "
						+ "left join pegawai.detailKategoryPegawai dkp left join pegawai.golonganPegawai golongan "
						+ "where strukHistori.tglHistori is not null "
						+ "and svSet.tglEksekusi is not null and gaji.hargaSatuan is not null ");

		// cek jika request dari ruangan
		if (CommonUtil.isNotNullOrEmpty(ruanganId)) {
			buffer.append("and ruanganVerifikasi.id in (:ruanganId) ");
		}

		if (CommonUtil.isNotNullOrEmpty(status)) {
			if (status.equalsIgnoreCase("LUNAS")) {
				buffer.append("and noSbk.totalSisaHutang = 0 ");
			} else if (status.equalsIgnoreCase("KREDIT")) {
				buffer.append("and noSbk.totalSisaHutang is not null and noSbk.totalSisaHutang > 0 ");
			} else if (status.equalsIgnoreCase("BELUMLUNAS")) {
				buffer.append("and noSbk.totalSisaHutang is null ");
			}
		}

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("and date(strukHistori.tglHistori) between date(:dateStart) and date(:dateEnd) ");
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			buffer.append("and lower(dkp.detailKategoryPegawai) like lower(:detailKategoryPegawai) ");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			buffer.append("and strukHistori.noRec=:noRec ");
		}

		buffer.append("group by strukHistori.noRec,sv.noRec,strukHistori.tglHistori,dkp.detailKategoryPegawai,"
				+ "svSet.noverifikasi,svSet.tglEksekusi,svSet.tglverifikasi,"
				+ "svSet.noUrutRuangan,ruanganVerifikasi.id,ruanganVerifikasi.namaRuangan,noSbk.totalSisaHutang ");

		buffer.append("order by strukHistori.tglAwal asc ");

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(ruanganId)) {
			query.setParameter("ruanganId", ruanganId);
		}

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			query.setParameter("dateStart", dateStart);
			query.setParameter("dateEnd", dateEnd);
		}

		if (CommonUtil.isNotNullOrEmpty(detailKategoryPegawai)) {
			query.setParameter("detailKategoryPegawai", "%" + detailKategoryPegawai + "%");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			query.setParameter("noRec", noRec);
		}

		List<Map<String, Object>> list = query.getResultList();

		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDaftarGajiPegawai(Integer idRekanan, String noRec, String status) {

		StringBuffer buffer = new StringBuffer();
		buffer.append("select new map(noStruk.noRec as noRecStrukPelayanan,noSbk.noRec as noRecStrukBuktiPengeluaran,"
				+ "noStruk.tglterimakiriman as tglTerimaKiriman,noStruk.tglfaktur as tglFaktur,noStruk.tglstruk as tglStruk,"
				+ "kt.id as idKelompokTransaksi,kt.kelompokTransaksi as kelompokTransaksi,"
				+ "ruangan.id as idRuangan,ruangan.namaRuangan as namaRuangan,"
				+ "rekanan.id as idRekanan,rekanan.namaRekanan as namaRekanan,"
				+ "noStruk.nofaktur as noFaktur,noStruk.nostruk as noStruk,"
				+ "noSbk.noSBK as noSbk,noSbk.tglSBK as tglSbk,noSbk.pembayaranKe as pembayaranKe,"
				+ "noStruk.tgljatuhtempo as tglJatuhTempo,coalesce(noStruk.totaldiscount,0) as totalDiskon,"
				+ "coalesce(noStruk.totalhargasatuan,0) as totalHargaSatuan,SUM(spd.qtyproduk * spd.hargasatuan) as totalHarusDibayar,"
				+ "coalesce(noStruk.totalppn,0) as totalPpn,coalesce(noSbk.totalSisaHutang,0) as totalSisaHutang,"
				+ "coalesce(noSbk.totalDiBayar,0) as totalDiBayar,"
				+ "coalesce(noSbk.totalSudahDiBayar,0) as totalSudahDiBayar,"
				+ "coalesce(noSbk.totalDiBayarBefore,0) as totalDibayarBefore) "
				+ "from StrukPelayanan noStruk left join noStruk.nosbklast noSbk left join noStruk.strukPelayananDetail spd "
				+ "left join noStruk.kdrekanan rekanan left join noStruk.kdrekanansales rekananSales "
				+ "left join noStruk.kdkelompoktransaksi kt left join noStruk.kdruangan as ruangan "
				+ "where noStruk.nostruk is not null and noStruk.nofaktur is not null ");

		if (CommonUtil.isNotNullOrEmpty(idRekanan)) {
			buffer.append("and rekanan.id=:idRekanan ");
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			buffer.append("and noStruk.noRec=:noRec ");
		}

		if (CommonUtil.isNotNullOrEmpty(status)) {
			if (status.equalsIgnoreCase("LUNAS")) {
				buffer.append("and noSbk.totalSisaHutang = 0 ");
			} else if (status.equalsIgnoreCase("KREDIT")) {
				buffer.append("and noSbk.totalSisaHutang is not null and noSbk.totalSisaHutang > 0 ");
			} else if (status.equalsIgnoreCase("BELUMLUNAS")) {
				buffer.append("and noSbk.totalSisaHutang is null ");
			}
		}

		buffer.append("group by noStruk.noRec,noSbk.noRec,noStruk.tglterimakiriman,rekanan.id,rekanan.namaRekanan,"
				+ "noStruk.nofaktur,noStruk.nostruk,noSbk.noSBK,noSbk.tglSBK,noSbk.pembayaranKe,"
				+ "noStruk.tgljatuhtempo,noStruk.totaldiscount,noStruk.totalhargasatuan,"
				+ "noStruk.totalppn,noSbk.totalSisaHutang,noSbk.totalSudahDiBayar,"
				+ "kt.id,kt.kelompokTransaksi,ruangan.id,ruangan.namaRuangan,"
				+ "noStruk.tglfaktur,noStruk.tglstruk,noSbk.totalDiBayarBefore ");

		buffer.append("order by noSbk.noSBK asc ");

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(idRekanan)) {
			query.setParameter("idRekanan", idRekanan);
		}

		if (CommonUtil.isNotNullOrEmpty(noRec)) {
			query.setParameter("noRec", noRec);
		}

		List<Map<String, Object>> list = query.getResultList();

		return list;
	}

	@Override
	public List<Map<String, Object>> getKlaimPembayaran(String noRegistrasi) {

		StringBuffer buffer = new StringBuffer();
		buffer.append("select new map(noStruk.noRec as noRecStrukPelayanan,spp.noRec as noRecSbm,"
				+ "noStruk.noregistrasi.noRec as noRecPasienDaftar,noStruk.nocm.id as idPasien,noStruk.nosbmlast.noRec as noRecNoSbm,"
				+ "noStruk.kdkelaslast.id as idKelasLast,noStruk.kdkelompoktransaksi.id as idKelTransaksi,"
				+ "noStruk.kdpegawaipenerima.id as idPegawaiPenerima,noStruk.kdruangan.id as idRuangan,"
				+ "noStruk.nostruk as noStruk,noStruk.tglstruk as tglStruk,"
				+ "noStruk.totalharusdibayar as totalHarusDibayar,noStruk.totalprekanan as totalPRekanan,"
				+ "spp.kdKelompokPasien.id as idKelPasien,spp.totalBiaya as totalBiaya,"
				+ "spp.totalSudahPPenjamin as totalSudahPPenjamin,spp.totalSisaHarusDibayar as totalSisaHarusDibayar,"
				+ "spp.totalPPenjamin as totalPPenjamin,spp.totalHarusDibayar as totalHarusDibayar,"
				+ "spp.totalSisaPiutang as totalSisaPiutang,spp.totalSudahDibayar as totalSudahDibayar,spp.noVerifikasi as noVerifikasi,"
				+ "spp.kdRekananPenjamin.id as idRekanan,spp.kdPegawaiPenjamin as kdPegawaiPenjamin) from StrukPelayananPenjamin spp "
				+ "left join spp.noStruk noStruk where noStruk.noRec is not null ");

		if (CommonUtil.isNotNullOrEmpty(noRegistrasi)) {
			buffer.append("and spp.noRec=:noRegistrasi ");
		}

		Query query = em.createQuery(buffer.toString());

		if (CommonUtil.isNotNullOrEmpty(noRegistrasi)) {
			query.setParameter("noRegistrasi", noRegistrasi);
		}

		List<Map<String, Object>> list = query.getResultList();

		return list;
	}

}
