package com.jasamedika.medifirst2000.dao.custom;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.jasamedika.medifirst2000.dao.IkiDanRemunerasiDao;
import com.jasamedika.medifirst2000.dao.SettingDataFixedDao;
import com.jasamedika.medifirst2000.dao.custom.base.impl.CoreDaoImpl;
import com.jasamedika.medifirst2000.entities.IndekKinerja;
import com.jasamedika.medifirst2000.service.MonitoringAbsenService;
import com.jasamedika.medifirst2000.util.CommonUtil;

@Repository
public class IndekKinerjaCustomDaoImpl extends CoreDaoImpl<IndekKinerja> implements IndeksKinerjaDaoCustom {

	public IndekKinerjaCustomDaoImpl() {
		super(IndekKinerja.class);
	}

	@Autowired
	private SettingDataFixedDao settingDataFixedDao;

	@Autowired
	private IkiDanRemunerasiDao ikiDanRemunerasiDao;

	@Autowired
	private MonitoringAbsenService monitoringAbsenService;

	@Override
	public List<Map<String, Object>> findAllProdukPerTanggal(Integer id, String tanggal,
			List<Integer> listIdJenisProduk, Boolean isRawatInap, Integer idProduk, Double harga,
			List<Integer> listDetailJenisProduk) {
		StringBuffer buffer = new StringBuffer();
		List<String> listDetailKaryawan = ikiDanRemunerasiDao.findIdKategoriPegawai();
		buffer.append(" select new map (produk.namaProduk as namaProduk, produk.id as produkId,"
				+ " count(produk.namaProduk) as count,pelayananPasien.hargaSatuan as harga,detail.id as detailId,jenisProduk.id as idJenisProduk)"
				+ " from PelayananPasienPetugas pelayananPasienPetugas"
				+ " left join pelayananPasienPetugas.kdpegawai dokter"
				+ " left join dokter.detailKategoryPegawai detailKategoriPegawai"
				+ " left join dokter.kategoryPegawai kategoryPegawai"
				+ " left join pelayananPasienPetugas.pelayananPasien pelayananPasien"
				+ " left join pelayananPasien.pasienDaftar antrian" + " left join antrian.ruangan ruangan"
				+ " left join ruangan.departemen departemen" + " left join pelayananPasien.produk produk "
				+ " left join produk.detailJenisProduk detail" + " left join detail.jenisProduk jenisProduk"
				+ " where (pelayananPasienPetugas.statusEnabled is null or pelayananPasienPetugas.statusEnabled is true)");

		if (CommonUtil.isNotNullOrEmpty(id)) {
			buffer.append("and dokter.id=:id ");
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			buffer.append("and produk.id=:idProduk ");
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append("and pelayananPasien.hargaSatuan=:harga ");
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			buffer.append("and jenisProduk.id not in (:listIdJenisProduk) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			buffer.append("and detail.id not in (:listDetailJenisProduk) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			buffer.append("and cast(kategoryPegawai.id as string) in (:listDetailKaryawan) ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");

		if (isRawatInap) {
			buffer.append("and " + " (departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
					+ "departemen.id=:departemenPeristi or " + "departemen.id=:departementBedahSentral ) ");
			buffer.append("and pelayananPasien.tglPelayanan  between :startDate and :endDate ");
		} else if (!isRawatInap) {
			buffer.append("and " + "(departemen.id=:departementRawatJalan or "
					+ "departemen.id=:instalasiGawatDarurat or " + "departemen.id=:instalasiRehabilMedik or "
					+ "departemen.id=:instalasiRadiologi or " + "departemen.id=:instalasiLaboratorium) ");
			buffer.append("and pelayananPasien.tglPelayanan  between :startDate and :endDate ");
		}

		buffer.append(" group by produk.namaProduk,produk.id,pelayananPasien.hargaSatuan,detail.id,jenisProduk.id ");
		buffer.append(" order by produk.namaProduk asc ");

		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(id)) {
			query.setParameter("id", id);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			query.setParameter("listDetailKaryawan", listDetailKaryawan);
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			query.setParameter("listIdJenisProduk", listIdJenisProduk);
		}

		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			query.setParameter("listDetailJenisProduk", listDetailJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			query.setParameter("idProduk", idProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			query.setParameter("harga", harga);
		}
		if (isRawatInap) {
			if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
					|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
				query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
				query.setParameter("departementICU", Integer.parseInt(departementICU));
				query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
				query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
			}
			query.setParameter("startDate", convertDateCustom(tanggal, true, true));
			query.setParameter("endDate", convertDateCustom(tanggal, true, false));
		}
		if (!isRawatInap) {
			if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
					|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
					|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
					|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
				query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
				query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
				query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
				query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
				query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
			}
			query.setParameter("startDate", convertDateCustom(tanggal, false, true));
			query.setParameter("endDate", convertDateCustom(tanggal, false, false));
		}

		return (List<Map<String, Object>>) query.getResultList();
	}

	@Override
	public Date convertDateCustom(String date, Boolean isRawatInap, Boolean isStartDate) {
		Date newDate = null;
		List<String> libur = this.findHariLibur(date);// getHariLibur
		if (CommonUtil.isNotNullOrEmpty(libur))// jika ada datanya maka return
												// null
		{
			return newDate;
		}
		if (CommonUtil.isNotNullOrEmpty(date)) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Calendar c = Calendar.getInstance();
			try {
				c.setTime(sdf.parse(date));
				String dayOfWeek = new SimpleDateFormat("EEEE", Locale.ENGLISH).format(c.getTime());
				if (!dayOfWeek.equals("Sunday") && !dayOfWeek.equals("Saturday")) {
					String h = "0";
					String m = "0";
					if (isRawatInap)// rawat inap kecuali sabtu minggu dan hari
									// libur
					{
						if (isStartDate) {
							h = "00";
							m = "01";
						}
						if (!isStartDate) {
							h = "24";
							m = "00";
						}
					} else {// rawat jalan sesuai jadwal kerja nonshift
						if (isStartDate)// senin kamis -> 07.01-15.30
						{
							h = "07";
							m = "01";
						}
						if (!isStartDate) {
							if (dayOfWeek.equals("Friday")) // jumat ->
															// 07.01-16.00
							{
								h = "16";
								m = "00";
							} else {
								h = "15";
								m = "30";
							}
						}
					}
					c.set(Calendar.HOUR, Integer.valueOf(h));
					c.set(Calendar.MINUTE, Integer.valueOf(m));
					newDate = c.getTime();
				}

			} catch (ParseException e) {
				e.printStackTrace();
			}

		}
		return newDate;
	}

	@Override
	public List<String> findHariLibur(String tanggal) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(" select mapToHariLibur.namaExternal " + " from MapKalenderToHariLibur mapToHariLibur "
				+ " left join mapToHariLibur.tanggal kalender "
				+ " where to_char(kalender.tanggal, 'yyyy-MM-dd') =:tanggal");

		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(tanggal)) {
			query.setParameter("tanggal", tanggal);
		}

		return (List<String>) query.getResultList();
	}

	@Override
	public List<Map<String, Object>> findTindakanByIdUser(Integer id, String tanggal, List<Integer> listIdJenisProduk,
			Boolean isRawatInap, List<Integer> listDetailJenisProduk) {
		StringBuffer buffer = new StringBuffer();
		List<String> listDetailKaryawan = ikiDanRemunerasiDao.findIdKategoriPegawai();
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select distinct new map ( produk.namaProduk as namaProduk, produk.id as produkId,"
				+ " count(produk.namaProduk) as count,pelayananPasien.hargaSatuan as harga,detail.id as detailId,jenisProduk.id as idJenisProduk ) "
				+ " from PelayananPasienPetugas pelayananPasienPetugas"
				+ " left join pelayananPasienPetugas.kdjenispetugaspe jenisPetugas "
				+ " left join pelayananPasienPetugas.kdpegawai dokter"
				+ " left join dokter.detailKategoryPegawai detailKategoriPegawai"
				+ " left join dokter.kategoryPegawai kategoryPegawai"
				+ " left join pelayananPasienPetugas.pelayananPasien pelayananPasien"
				+ " left join pelayananPasien.pasienDaftar antrian" + " left join antrian.ruangan ruangan"
				+ " left join ruangan.departemen departemen" + " left join pelayananPasien.produk produk "
				+ " left join produk.detailJenisProduk detail" + " left join detail.jenisProduk jenisProduk "
				+ " where (pelayananPasienPetugas.statusEnabled is null or pelayananPasienPetugas.statusEnabled is true)");

		if (CommonUtil.isNotNullOrEmpty(id)) {
			buffer.append("and dokter.id=:id ");
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			buffer.append("and jenisProduk.id not in (:listIdJenisProduk) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			buffer.append("and detail.id not in (:listDetailJenisProduk) ");
		}

		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			buffer.append("and cast(kategoryPegawai.id as string) in (:listDetailKaryawan) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append("and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");

		if (isRawatInap) {
			buffer.append("and " + " (departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
					+ "departemen.id=:departemenPeristi or " + "departemen.id=:departementBedahSentral ) ");
			buffer.append("and pelayananPasien.tglPelayanan  between :startDate and :endDate ");
		} else if (!isRawatInap) {
			buffer.append("and " + "(departemen.id=:departementRawatJalan or "
					+ "departemen.id=:instalasiGawatDarurat or " + "departemen.id=:instalasiRehabilMedik or "
					+ "departemen.id=:instalasiRadiologi or " + "departemen.id=:instalasiLaboratorium) ");
			buffer.append("and pelayananPasien.tglPelayanan  between :startDate and :endDate ");
		}

		buffer.append(" group by produk.namaProduk,produk.id,pelayananPasien.hargaSatuan,detail.id,jenisProduk.id ");
		buffer.append(" order by produk.namaProduk asc ");
		
		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(id)) {
			query.setParameter("id", id);
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			query.setParameter("listIdJenisProduk", listIdJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			query.setParameter("listDetailKaryawan", listDetailKaryawan);
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}

		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			query.setParameter("listDetailJenisProduk", listDetailJenisProduk);
		}
		if (isRawatInap) {
			if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
					|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
				query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
				query.setParameter("departementICU", Integer.parseInt(departementICU));
				query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
				query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
			}
			query.setParameter("startDate", convertDateCustom(tanggal, true, true));
			query.setParameter("endDate", convertDateCustom(tanggal, true, false));
		}
		if (!isRawatInap) {
			if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
					|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
					|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
					|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
				query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
				query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
				query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
				query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
				query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
			}
			query.setParameter("startDate", convertDateCustom(tanggal, false, true));
			query.setParameter("endDate", convertDateCustom(tanggal, false, false));
		}

		return (List<Map<String, Object>>) query.getResultList();
	}

	@Override
	public List<Map<String, Object>> findAllProdukPerPeriode(Integer id, String periode,
			List<Integer> listIdJenisProduk, Boolean isRawatInap, Integer idProduk, Double harga,
			List<String> listFriday, List<String> listSK, List<String> listRanap, List<Integer> listDetailJenisProduk) {
		StringBuffer buffer = new StringBuffer();
		List<String> listDetailKaryawan = ikiDanRemunerasiDao.findIdKategoriPegawai();
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select new map ( count(produk.namaProduk) as count,pelayananPasien.hargaSatuan as harga,"
				+ " to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd') as tanggal)"
				+ " from PelayananPasienPetugas pelayananPasienPetugas"
				+ " left join pelayananPasienPetugas.kdjenispetugaspe jenisPetugas "
				+ " left join pelayananPasienPetugas.kdpegawai dokter"
				+ " left join dokter.detailKategoryPegawai detailKategoriPegawai"
				+ " left join dokter.kategoryPegawai kategoryPegawai"
				+ " left join pelayananPasienPetugas.pelayananPasien pelayananPasien"
				+ " left join pelayananPasien.pasienDaftar antrian" + " left join antrian.ruangan ruangan"
				+ " left join ruangan.departemen departemen" + " left join pelayananPasien.produk produk "
				+ " left join produk.detailJenisProduk detail" + " left join detail.jenisProduk jenisProduk"
				+ " where (pelayananPasienPetugas.statusEnabled is null or pelayananPasienPetugas.statusEnabled is true)"
				+ " and (pelayananPasien.statusEnabled is null or pelayananPasien.statusEnabled is true) ");

		if (CommonUtil.isNotNullOrEmpty(id)) {
			buffer.append("and dokter.id=:id ");
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			buffer.append("and produk.id=:idProduk ");
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append("and pelayananPasien.hargaSatuan=:harga ");
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			buffer.append("and jenisProduk.id not in (:listIdJenisProduk) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			buffer.append("and detail.id not in (:listDetailJenisProduk) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			buffer.append("and cast(kategoryPegawai.id as string) in (:listDetailKaryawan) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append("and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");
		// Ranap
		buffer.append("and (" + " ((departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
				+ "departemen.id=:departemenPeristi or "
				+ "departemen.id=:departementBedahSentral ) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:00' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateRanap))");
		// Senin-Kamis RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('15:30:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate))");
		// Jumar RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('16:00:00' as time)  "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateFriday)))");

		buffer.append(" group by pelayananPasien.hargaSatuan,to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd')");
		buffer.append(" order by to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd') ");

		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(id)) {
			query.setParameter("id", id);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			query.setParameter("listDetailKaryawan", listDetailKaryawan);
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			query.setParameter("listIdJenisProduk", listIdJenisProduk);
		}

		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			query.setParameter("idProduk", idProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			query.setParameter("harga", harga);
		}
		if (CommonUtil.isNotNullOrEmpty(listSK)) {
			query.setParameter("listDate", listSK);
		}
		if (CommonUtil.isNotNullOrEmpty(listRanap)) {
			query.setParameter("listDateRanap", listRanap);
		}
		if (CommonUtil.isNotNullOrEmpty(listFriday)) {
			query.setParameter("listDateFriday", listFriday);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
				|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
			query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
			query.setParameter("departementICU", Integer.parseInt(departementICU));
			query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
			query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}

		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			query.setParameter("listDetailJenisProduk", listDetailJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
				|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
				|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
				|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
			query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
			query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
			query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
			query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
			query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
		}
		return (List<Map<String, Object>>) query.getResultList();
	}

	@Override
	public List<String> convertMonthtoDateCustom(String periode, Boolean isFriday) {
		List<String> result = new ArrayList<String>();
		int maxday = monitoringAbsenService.iterateDate(periode + "-01");
		for (int i = 1; i <= maxday; i++) {
//			Map<String, Object> subResult = new HashMap<String, Object>();
			String date = "";
//			Double total = 0.0;
			if (i < 10) {
				date = periode + "-0" + i;
			} else {
				date = periode + "-" + i;
			}
			List<String> libur = this.findHariLibur(date);// getHariLibur
			if (CommonUtil.isNullOrEmpty(libur))// jika ada datanya maka return
												// null
			{
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				Calendar c = Calendar.getInstance();
				try {
					c.setTime(sdf.parse(date));
					String dayOfWeek = new SimpleDateFormat("EEEE", Locale.ENGLISH).format(c.getTime());
					if (CommonUtil.isNotNullOrEmpty(isFriday)) {
						if (isFriday) {// utk RJ Jumat
							if (dayOfWeek.equals("Friday")) {
								result.add(date);
							}
						} else {
							if (!dayOfWeek.equals("Sunday") && !dayOfWeek.equals("Saturday")
									&& !dayOfWeek.equals("Friday")) {// untuk RJ senin-kamis
								result.add(date);
							}
						}
					} else {
						if (!dayOfWeek.equals("Sunday") && !dayOfWeek.equals("Saturday")) {// untuk rawat inap
							result.add(date);
						}
					}

				} catch (ParseException e) {
					e.printStackTrace();
				}

			}
		}
		return result;
	}

	@Override
	public List<Map<String, Object>> findDetailAllProdukPerPeriode(Integer id, String periode,
			List<Integer> listIdJenisProduk) {
		StringBuffer buffer = new StringBuffer();
		List<String> listDetailKaryawan = ikiDanRemunerasiDao.findIdKategoriPegawai();
		buffer.append(" select new map (produk.namaProduk as namaProduk, produk.id as produkId, "
				+ " count(produk.namaProduk) as count,pelayananPasien.hargaSatuan as harga,detail.id as detailId,jenisProduk.id as idJenisProduk)"
				+ " from PelayananPasienPetugas pelayananPasienPetugas"
				+ " left join pelayananPasienPetugas.kdpegawai dokter"
				+ " left join dokter.detailKategoryPegawai detailKategoriPegawai"
				+ " left join dokter.kategoryPegawai kategoryPegawai"
				+ " left join pelayananPasienPetugas.pelayananPasien pelayananPasien"
				+ " left join pelayananPasien.pasienDaftar antrian" + " left join antrian.ruangan ruangan"
				+ " left join ruangan.departemen departemen" + " left join pelayananPasien.produk produk "
				+ " left join produk.detailJenisProduk detail" + " left join detail.jenisProduk jenisProduk"
				+ " where (pelayananPasienPetugas.statusEnabled is null or pelayananPasienPetugas.statusEnabled is true)");

		if (CommonUtil.isNotNullOrEmpty(id)) {
			buffer.append("and dokter.id=:id ");
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			buffer.append("and jenisProduk.id not in (:listIdJenisProduk) ");
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustom(periode, false))) {
			buffer.append("and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			buffer.append("and cast(kategoryPegawai.id as string) in (:listDetailKaryawan) ");
		}
		buffer.append(
				" group by produk.namaProduk,produk.id,pelayananPasien.hargaSatuan,detail.id,jenisProduk.id,pelayananPasien.tglPelayanan,to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd') ");
		buffer.append(" order by pelayananPasien.tglPelayanan ");

		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(id)) {
			query.setParameter("id", id);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			query.setParameter("listDetailKaryawan", listDetailKaryawan);
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			query.setParameter("listIdJenisProduk", listIdJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(periode)) {
			List<String> listDate = convertMonthtoDateCustom(periode, false);
			listDate.addAll(convertMonthtoDateCustom(periode, true));
			query.setParameter("listDate", listDate);
		}

		return (List<Map<String, Object>>) query.getResultList();
	}

	@Override
	public List<Map<String, Object>> findPatientDetailForEveryTindakan(Integer idProduk, Double harga,
			Integer idPegawai, String tgl, List<String> listFriday, List<String> listSK, List<String> listRanap) {
		StringBuffer buffer = new StringBuffer();
//		String periode = tgl.substring(0, 7);
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select new map (petugas.noRec as norecPetugas, " + " ruangan.namaRuangan as ruangan, "
				+ " pelayananPasien.noRec as noRecPelayananPasien, " + " produk.namaProduk as namaProduk, "
				+ " pasien.namaPasien as namapasien, " + " pasien.noCm as noCm, " + " pasien.tglLahir as tglLahir, "
				+ " pasienDaftar.noRegistrasi as noRegistrasi, " + " pasienDaftar.tglRegistrasi as tglRegistrasi, "
				+ " pelayananPasien.hargaSatuan as harga, " + " pelayananPasien.tglPelayanan as tglpelayanan, "
				+ " kelas.namaKelas as namaKelas, " + " pasienDaftar.tglPulang, "
				+ " jenisPetugas.jenisPetugasPe as jenisPetugas) " + " from PelayananPasienPetugas  petugas "
				+ " left join petugas.pelayananPasien pelayananPasien "
				+ " left join petugas.kdjenispetugaspe jenisPetugas " + " left join petugas.kdpegawai dokter "
				+ " left join pelayananPasien.kelas kelas " + " left join pelayananPasien.produk produk "
				+ " left join pelayananPasien.pasienDaftar antrian " + " left join antrian.ruangan ruangan "
				+ " left join ruangan.departemen departemen" + " left join antrian.pasienDaftar pasienDaftar "
				+ " left join pasienDaftar.pasien pasien "
				+ " where (pelayananPasien.statusEnabled is true or pelayananPasien.statusEnabled is null) "
				+ " and (petugas.statusEnabled is true or petugas.statusEnabled is null) ");
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append(" and pelayananPasien.hargaSatuan = :harga");
		}
		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			buffer.append(" and dokter.id = :idPegawai");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append(" and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append(" and produk.id = :idProduk");
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append(" and to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd') = :tgl ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");
		// Ranap
		buffer.append("and (" + " ((departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
				+ "departemen.id=:departemenPeristi or "
				+ "departemen.id=:departementBedahSentral ) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:00' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateRanap))");
		// Senin-Kamis RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('15:30:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate))");
		// Jumar RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('16:00:00' as time)  "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateFriday)))");

		buffer.append(" order by pelayananPasien.tglPelayanan ");

		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			query.setParameter("idPegawai", idPegawai);
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			query.setParameter("idProduk", idProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			query.setParameter("harga", harga);
		}
		if (CommonUtil.isNotNullOrEmpty(tgl)) {
			query.setParameter("tgl", tgl);
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(listRanap)) {
			query.setParameter("listDateRanap", listRanap);
		}
		if (CommonUtil.isNotNullOrEmpty(listSK)) {
			query.setParameter("listDate", listSK);
		}
		if (CommonUtil.isNotNullOrEmpty(listFriday)) {
			query.setParameter("listDateFriday", listFriday);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
				|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
			query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
			query.setParameter("departementICU", Integer.parseInt(departementICU));
			query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
			query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
				|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
				|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
				|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
			query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
			query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
			query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
			query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
			query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
		}
		return (List<Map<String, Object>>) query.getResultList();
	}

	@Override
	public List<Map<String, Object>> findPatientDetailForEveryTindakanAll(Integer idProduk, Double harga,
			Integer idPegawai, String periode, List<String> listFriday, List<String> listSK, List<String> listRanap) {
		StringBuffer buffer = new StringBuffer();
		// String periode = tgl.substring(0, 7);
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select new map (pasien.namaPasien as namapasien, " + " pasien.noCm as noCm, "
				+ " pasien.tglLahir as tglLahir, " + " pasienDaftar.noRegistrasi as noRegistrasi, "
				+ " pasienDaftar.tglRegistrasi as tglRegistrasi, " + " pelayananPasien.hargaSatuan as harga, "
				+ " to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd') as tglpelayanan, "
				+ " kelas.namaKelas as namaKelas, " + " pasienDaftar.tglPulang) "
				+ " from PelayananPasienPetugas  petugas " + " left join petugas.pelayananPasien pelayananPasien "
				+ " left join petugas.kdjenispetugaspe jenisPetugas " + " left join petugas.kdpegawai dokter "
				+ " left join pelayananPasien.kelas kelas " + " left join pelayananPasien.produk produk "
				+ " left join pelayananPasien.pasienDaftar antrian " + " left join antrian.ruangan ruangan "
				+ " left join ruangan.departemen departemen" + " left join antrian.pasienDaftar pasienDaftar "
				+ " left join pasienDaftar.pasien pasien "
				+ " where (pelayananPasien.statusEnabled is true or pelayananPasien.statusEnabled is null) "
				+ " and (petugas.statusEnabled is true or petugas.statusEnabled is null) ");
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append(" and pelayananPasien.hargaSatuan = :harga");
		}
		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			buffer.append(" and dokter.id = :idPegawai");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append(" and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			buffer.append(" and produk.id = :idProduk");
		}
		if (CommonUtil.isNotNullOrEmpty(periode)) {
			buffer.append(" and to_char(pelayananPasien.tglPelayanan,'yyyy-MM') = :tgl ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");
		// Ranap
		buffer.append("and (" + " ((departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
				+ "departemen.id=:departemenPeristi or "
				+ "departemen.id=:departementBedahSentral ) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:00' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateRanap))");
		// Senin-Kamis RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('15:30:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate))");
		// Jumar RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('16:00:00' as time)  "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateFriday)))");

		buffer.append(" order by pelayananPasien.tglPelayanan ");
		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			query.setParameter("idPegawai", idPegawai);
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			query.setParameter("idProduk", idProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			query.setParameter("harga", harga);
		}
		if (CommonUtil.isNotNullOrEmpty(periode)) {
			query.setParameter("tgl", periode);
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(listRanap)) {
			query.setParameter("listDateRanap", listRanap);
		}
		if (CommonUtil.isNotNullOrEmpty(listSK)) {
			query.setParameter("listDate", listSK);
		}
		if (CommonUtil.isNotNullOrEmpty(listFriday)) {
			query.setParameter("listDateFriday", listFriday);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
				|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
			query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
			query.setParameter("departementICU", Integer.parseInt(departementICU));
			query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
			query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
				|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
				|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
				|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
			query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
			query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
			query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
			query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
			query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
		}
		return (List<Map<String, Object>>) query.getResultList();
	}
	
	@Override
	public List<Map<String, Object>> findPatientDetailForEveryTindakanAllV2(Integer idPegawai, String tgl,
			List<String> listFriday, List<String> listSK, List<String> listRanap) {
		StringBuffer buffer = new StringBuffer();
		// String periode = tgl.substring(0, 7);
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select new map (pasien.namaPasien as namapasien, " + " pasien.noCm as noCm, "
				+ " pasien.tglLahir as tglLahir, " + " pasienDaftar.noRegistrasi as noRegistrasi, "
				+ " pasienDaftar.tglRegistrasi as tglRegistrasi, " + " pelayananPasien.hargaSatuan as harga, "
				+ " to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd') as tglpelayanan, "
				+ " produk.id as idProduk, dokter.id as idPegawai, "
				+ " kelas.namaKelas as namaKelas, " + " pasienDaftar.tglPulang) "
				+ " from PelayananPasienPetugas  petugas " + " left join petugas.pelayananPasien pelayananPasien "
				+ " left join petugas.kdjenispetugaspe jenisPetugas " + " left join petugas.kdpegawai dokter "
				+ " left join pelayananPasien.kelas kelas " + " left join pelayananPasien.produk produk "
				+ " left join pelayananPasien.pasienDaftar antrian " + " left join antrian.ruangan ruangan "
				+ " left join ruangan.departemen departemen" + " left join antrian.pasienDaftar pasienDaftar "
				+ " left join pasienDaftar.pasien pasien "
				+ " where (pelayananPasien.statusEnabled is true or pelayananPasien.statusEnabled is null) "
				+ " and (petugas.statusEnabled is true or petugas.statusEnabled is null) ");
		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			buffer.append(" and dokter.id = :idPegawai");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append(" and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		if (CommonUtil.isNotNullOrEmpty(tgl)) {
			buffer.append(" and to_char(pelayananPasien.tglPelayanan,'yyyy-MM') = :tgl ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");
		// Ranap
		buffer.append("and (" + " ((departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
				+ "departemen.id=:departemenPeristi or "
				+ "departemen.id=:departementBedahSentral ) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:00' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateRanap))");
		// Senin-Kamis RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('15:30:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate))");
		// Jumar RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('16:00:00' as time)  "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateFriday)))");

		buffer.append(" order by pelayananPasien.tglPelayanan ");
		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(idPegawai)) {
			query.setParameter("idPegawai", idPegawai);
		}
		if (CommonUtil.isNotNullOrEmpty(tgl)) {
			query.setParameter("tgl", tgl);
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(listRanap)) {
			query.setParameter("listDateRanap", listRanap);
		}
		if (CommonUtil.isNotNullOrEmpty(listSK)) {
			query.setParameter("listDate", listSK);
		}
		if (CommonUtil.isNotNullOrEmpty(listFriday)) {
			query.setParameter("listDateFriday", listFriday);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
				|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
			query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
			query.setParameter("departementICU", Integer.parseInt(departementICU));
			query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
			query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
				|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
				|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
				|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
			query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
			query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
			query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
			query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
			query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
		}
		return (List<Map<String, Object>>) query.getResultList();
	}

	@Override
	public List<String> convertMonthtoDateCustomFeeForService(String periode, Boolean isFriday) {
		List<String> result = new ArrayList<String>();
		int maxday = monitoringAbsenService.iterateDate(periode + "-01");
		for (int i = 1; i <= maxday; i++) {
//			Map<String, Object> subResult = new HashMap<String, Object>();
			String date = "";
//			Double total = 0.0;
			if (i < 10) {
				date = periode + "-0" + i;
			} else {
				date = periode + "-" + i;
			}
			List<String> libur = this.findHariLibur(date);// getHariLibur
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Calendar c = Calendar.getInstance();

			try {
				c.setTime(sdf.parse(date));
				String dayOfWeek = new SimpleDateFormat("EEEE", Locale.ENGLISH).format(c.getTime());
				if (CommonUtil.isNotNullOrEmpty(libur)) {
					result.add(date);
				} else if (dayOfWeek.equals("Sunday") || dayOfWeek.equals("Saturday")) {
					result.add(date);
				}

			} catch (ParseException e) {
				e.printStackTrace();
			}

		}

		return result;
	}

	@Override
	public List<Map<String, Object>> findAllProdukPerPeriodeFeeForServicesNewReport(Integer id, String periode,
			List<Integer> listIdJenisProduk, Boolean isRawatInap, Integer idProduk, Double harga, Boolean isFriday) {
		StringBuffer buffer = new StringBuffer();
		List<String> listDetailKaryawan = ikiDanRemunerasiDao.findIdKategoriPegawai();
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select new map ( count(produk.namaProduk) as count,pelayananPasien.hargaSatuan as harga,"
				+ " to_char(pelayananPasien.tglPelayanan,'dd-MM-YYYY') as tanggal, kelas.namaKelas as namaKelas, produk.namaProduk as namaProduk)"
				+ " from PelayananPasienPetugas pelayananPasienPetugas"
				+ " left join pelayananPasienPetugas.kdjenispetugaspe jenisPetugas "
				+ " left join pelayananPasienPetugas.kdpegawai dokter"
				+ " left join dokter.detailKategoryPegawai detailKategoriPegawai"
				+ " left join dokter.kategoryPegawai kategoryPegawai"
				+ " left join pelayananPasienPetugas.pelayananPasien pelayananPasien"
				+ " left join pelayananPasien.kelas kelas " + " left join pelayananPasien.pasienDaftar antrian"
				+ " left join antrian.ruangan ruangan" + " left join ruangan.departemen departemen"
				+ " left join pelayananPasien.produk produk " + " left join produk.detailJenisProduk detail"
				+ " left join detail.jenisProduk jenisProduk"
				+ " where (pelayananPasienPetugas.statusEnabled is null or pelayananPasienPetugas.statusEnabled is true)");

		if (CommonUtil.isNotNullOrEmpty(id)) {
			buffer.append("and dokter.id=:id ");
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			buffer.append("and produk.id=:idProduk ");
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append("and pelayananPasien.hargaSatuan=:harga ");
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			buffer.append("and jenisProduk.id not in (:listIdJenisProduk) ");
		}
		convertMonthtoDateCustomFeeForService(periode, false);
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			buffer.append("and cast(kategoryPegawai.id as string) in (:listDetailKaryawan) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append("and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");
		// Ranap
		buffer.append("and (" + " ((departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
				+ "departemen.id=:departemenPeristi or "
				+ "departemen.id=:departementBedahSentral ) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:00' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateLibur))");
		// RJ libur
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:01' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateLibur))");
		// Senin-Kamis RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('15:30:01' as time) and CAST('07:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate))");
		// Jumar RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('16:00:01' as time) and CAST('07:00:00' as time)  "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateFriday)))");

		buffer.append(" group by pelayananPasien.hargaSatuan,to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd')");
	 	buffer.append(" order by to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd')");
		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(id)) {
			query.setParameter("id", id);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			query.setParameter("listDetailKaryawan", listDetailKaryawan);
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			query.setParameter("listIdJenisProduk", listIdJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustomFeeForService(periode, false))) {
			query.setParameter("listDateLibur", convertMonthtoDateCustomFeeForService(periode, false));
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			query.setParameter("idProduk", idProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			query.setParameter("harga", harga);
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustom(periode, false))) {
			query.setParameter("listDate", convertMonthtoDateCustom(periode, false));
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustom(periode, true))) {
			query.setParameter("listDateFriday", convertMonthtoDateCustom(periode, true));
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
				|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
			query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
			query.setParameter("departementICU", Integer.parseInt(departementICU));
			query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
			query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
				|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
				|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
				|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
			query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
			query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
			query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
			query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
			query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
		}
		return (List<Map<String, Object>>) query.getResultList();
	}

	@Override
	public List<Map<String, Object>> findAllProdukPerPeriodeFeeForServices(Integer id, String periode,
			List<Integer> listIdJenisProduk, Boolean isRawatInap, Integer idProduk, Double harga, Boolean isFriday,
			List<Integer> listDetailJenisProduk) {
		StringBuffer buffer = new StringBuffer();
		List<String> listDetailKaryawan = ikiDanRemunerasiDao.findIdKategoriPegawai();
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select new map ( count(produk.namaProduk) as count,pelayananPasien.hargaSatuan as harga,"
				+ " to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd') as tanggal)"
				+ " from PelayananPasienPetugas pelayananPasienPetugas"
				+ " left join pelayananPasienPetugas.kdjenispetugaspe jenisPetugas "
				+ " left join pelayananPasienPetugas.kdpegawai dokter"
				+ " left join dokter.detailKategoryPegawai detailKategoriPegawai"
				+ " left join dokter.kategoryPegawai kategoryPegawai"
				+ " left join pelayananPasienPetugas.pelayananPasien pelayananPasien"
				+ " left join pelayananPasien.pasienDaftar antrian" + " left join antrian.ruangan ruangan"
				+ " left join ruangan.departemen departemen" + " left join pelayananPasien.produk produk "
				+ " left join produk.detailJenisProduk detail" + " left join detail.jenisProduk jenisProduk"
				+ " where (pelayananPasienPetugas.statusEnabled is null or pelayananPasienPetugas.statusEnabled is true)");

		if (CommonUtil.isNotNullOrEmpty(id)) {
			buffer.append("and dokter.id=:id ");
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			buffer.append("and produk.id=:idProduk ");
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append("and pelayananPasien.hargaSatuan=:harga ");
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			buffer.append("and jenisProduk.id not in (:listIdJenisProduk) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			buffer.append("and detail.id not in (:listDetailJenisProduk) ");
		}

		convertMonthtoDateCustomFeeForService(periode, false);
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			buffer.append("and cast(kategoryPegawai.id as string) in (:listDetailKaryawan) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append("and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");
		// Ranap
		buffer.append("and (" + " ((departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
				+ "departemen.id=:departemenPeristi or "
				+ "departemen.id=:departementBedahSentral ) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:00' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateLibur))");
		// RJ libur
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:01' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateLibur))");
		// Senin-Kamis RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('15:30:01' as time) and CAST('07:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate))");
		// Jumar RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('16:00:01' as time) and CAST('07:00:00' as time)  "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateFriday)))");

		buffer.append(" group by pelayananPasien.hargaSatuan,to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd')");
		buffer.append(" order by to_char(pelayananPasien.tglPelayanan,'yyyy-MM-dd')");
		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(id)) {
			query.setParameter("id", id);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			query.setParameter("listDetailKaryawan", listDetailKaryawan);
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			query.setParameter("listIdJenisProduk", listIdJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustomFeeForService(periode, false))) {
			query.setParameter("listDateLibur", convertMonthtoDateCustomFeeForService(periode, false));
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			query.setParameter("idProduk", idProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			query.setParameter("harga", harga);
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustom(periode, false))) {
			query.setParameter("listDate", convertMonthtoDateCustom(periode, false));
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustom(periode, true))) {
			query.setParameter("listDateFriday", convertMonthtoDateCustom(periode, true));
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
				|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
			query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
			query.setParameter("departementICU", Integer.parseInt(departementICU));
			query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
			query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}

		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			query.setParameter("listDetailJenisProduk", listDetailJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
				|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
				|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
				|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
			query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
			query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
			query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
			query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
			query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
		}
		return (List<Map<String, Object>>) query.getResultList();
	}

	@Override
	public int findAllProdukPerPeriodeRekap(Integer id, String tanggal, List<Integer> listIdJenisProduk,
			Boolean isRawatInap, Integer idProduk, Double harga, List<String> listFriday, List<String> listSK,
			List<String> listRanap, List<Integer> listDetailJenisProduk) {
		StringBuffer buffer = new StringBuffer();
		List<String> listDetailKaryawan = ikiDanRemunerasiDao.findIdKategoriPegawai();
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select  count(produk.namaProduk) " + " from PelayananPasienPetugas pelayananPasienPetugas"
				+ " left join pelayananPasienPetugas.kdjenispetugaspe jenisPetugas "
				+ " left join pelayananPasienPetugas.kdpegawai dokter"
				+ " left join dokter.detailKategoryPegawai detailKategoriPegawai"
				+ " left join dokter.kategoryPegawai kategoryPegawai"
				+ " left join pelayananPasienPetugas.pelayananPasien pelayananPasien"
				+ " left join pelayananPasien.pasienDaftar antrian" + " left join antrian.ruangan ruangan"
				+ " left join ruangan.departemen departemen" + " left join pelayananPasien.produk produk "
				+ " left join produk.detailJenisProduk detail" + " left join detail.jenisProduk jenisProduk"
				+ " where (pelayananPasienPetugas.statusEnabled is null or pelayananPasienPetugas.statusEnabled is true)"
				+ " and (pelayananPasien.statusEnabled is null or pelayananPasien.statusEnabled is true) ");

		if (CommonUtil.isNotNullOrEmpty(id)) {
			buffer.append("and dokter.id=:id ");
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			buffer.append("and produk.id=:idProduk ");
		}

		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			buffer.append("and detail.id not in (:listDetailJenisProduk) ");
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append("and pelayananPasien.hargaSatuan=:harga ");
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			buffer.append("and jenisProduk.id not in (:listIdJenisProduk) ");
		}

		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			buffer.append("and cast(kategoryPegawai.id as string) in (:listDetailKaryawan) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append("and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");
		// Ranap
		buffer.append("and (" + " ((departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
				+ "departemen.id=:departemenPeristi or "
				+ "departemen.id=:departementBedahSentral ) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:00' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateRanap))");
		// Senin-Kamis RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('15:30:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate))");
		// Jumar RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('07:00:00' as time) and CAST('16:00:00' as time)  "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateFriday)))");

		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(id)) {
			query.setParameter("id", id);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			query.setParameter("listDetailKaryawan", listDetailKaryawan);
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			query.setParameter("listIdJenisProduk", listIdJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			query.setParameter("listDetailJenisProduk", listDetailJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			query.setParameter("idProduk", idProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			query.setParameter("harga", harga);
		}
		if (CommonUtil.isNotNullOrEmpty(listSK)) {
			query.setParameter("listDate", listSK);
		}
		if (CommonUtil.isNotNullOrEmpty(listRanap)) {
			query.setParameter("listDateRanap", listRanap);
		}
		if (CommonUtil.isNotNullOrEmpty(listFriday)) {
			query.setParameter("listDateFriday", listFriday);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
				|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
			query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
			query.setParameter("departementICU", Integer.parseInt(departementICU));
			query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
			query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
				|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
				|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
				|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
			query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
			query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
			query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
			query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
			query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
		}
		return ((Long) query.getSingleResult()).intValue();
	}

	@Override
	public int findAllProdukPerPeriodeFeeForServicesReport(Integer id, String periode, List<Integer> listIdJenisProduk,
			Boolean isRawatInap, Integer idProduk, Double harga, Boolean isFriday,
			List<Integer> listDetailJenisProduk) {
		StringBuffer buffer = new StringBuffer();
		List<String> listDetailKaryawan = ikiDanRemunerasiDao.findIdKategoriPegawai();
		List<String> listjenisPetugasPelaksana = ikiDanRemunerasiDao.findIdJeniPetugasPelaksana();
		buffer.append(" select  count(produk.namaProduk)  " + " from PelayananPasienPetugas pelayananPasienPetugas"
				+ " left join pelayananPasienPetugas.kdjenispetugaspe jenisPetugas "
				+ " left join pelayananPasienPetugas.kdpegawai dokter"
				+ " left join dokter.detailKategoryPegawai detailKategoriPegawai"
				+ " left join dokter.kategoryPegawai kategoryPegawai"
				+ " left join pelayananPasienPetugas.pelayananPasien pelayananPasien"
				+ " left join pelayananPasien.pasienDaftar antrian" + " left join antrian.ruangan ruangan"
				+ " left join ruangan.departemen departemen" + " left join pelayananPasien.produk produk "
				+ " left join produk.detailJenisProduk detail" + " left join detail.jenisProduk jenisProduk"
				+ " where (pelayananPasienPetugas.statusEnabled is null or pelayananPasienPetugas.statusEnabled is true)");

		if (CommonUtil.isNotNullOrEmpty(id)) {
			buffer.append("and dokter.id=:id ");
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			buffer.append("and produk.id=:idProduk ");
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			buffer.append("and pelayananPasien.hargaSatuan=:harga ");
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			buffer.append("and jenisProduk.id not in (:listIdJenisProduk) ");
		}

		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			buffer.append("and detail.id not in (:listDetailJenisProduk) ");
		}
		convertMonthtoDateCustomFeeForService(periode, false);
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			buffer.append("and cast(kategoryPegawai.id as string) in (:listDetailKaryawan) ");
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			buffer.append("and cast(jenisPetugas.id as string) in (:listjenisPetugasPelaksana) ");
		}
		String departementRawatInap = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatInap");

		String departementICU = settingDataFixedDao.getSettingDataFixed("KdInstalasiICU");

		String departementBedahSentral = settingDataFixedDao.getSettingDataFixed("KdInstalasiBedahSentral");

		String departementRawatJalan = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRawatJalan");

		String instalasiGawatDarurat = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiGawatDarurat");

		String instalasiRehabilMedik = settingDataFixedDao
				.getSettingDataFixed("KdDepartemenInstalasiRehabilitasiMedik");

		String instalasiLaboratorium = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiLaboratorium");

		String instalasiRadiologi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiRadiologi");

		String departemenPeristi = settingDataFixedDao.getSettingDataFixed("KdDepartemenInstalasiPeristi");
		// Ranap
		buffer.append("and (" + " ((departemen.id=:departementRawatInap or " + "departemen.id=:departementICU or "
				+ "departemen.id=:departemenPeristi or "
				+ "departemen.id=:departementBedahSentral ) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:00' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateLibur))");
		// RJ libur
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('00:00:01' as time) and CAST('24:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateLibur))");
		// Senin-Kamis RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('15:30:01' as time) and CAST('07:00:00' as time) "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDate))");
		// Jumar RJ
		buffer.append("or " + "((departemen.id=:departementRawatJalan or " + "departemen.id=:instalasiGawatDarurat or "
				+ "departemen.id=:instalasiRehabilMedik or " + "departemen.id=:instalasiRadiologi or "
				+ "departemen.id=:instalasiLaboratorium) and CAST (pelayananPasien.tglPelayanan as time ) between CAST ('16:00:01' as time) and CAST('07:00:00' as time)  "
				+ "and to_char (pelayananPasien.tglPelayanan,'yyyy-MM-dd') in (:listDateFriday)))");

		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(id)) {
			query.setParameter("id", id);
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailKaryawan)) {
			query.setParameter("listDetailKaryawan", listDetailKaryawan);
		}
		if (CommonUtil.isNotNullOrEmpty(listIdJenisProduk)) {
			query.setParameter("listIdJenisProduk", listIdJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustomFeeForService(periode, false))) {
			query.setParameter("listDateLibur", convertMonthtoDateCustomFeeForService(periode, false));
		}
		if (CommonUtil.isNotNullOrEmpty(listDetailJenisProduk)) {
			query.setParameter("listDetailJenisProduk", listDetailJenisProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(idProduk)) {
			query.setParameter("idProduk", idProduk);
		}
		if (CommonUtil.isNotNullOrEmpty(harga)) {
			query.setParameter("harga", harga);
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustom(periode, false))) {
			query.setParameter("listDate", convertMonthtoDateCustom(periode, false));
		}
		if (CommonUtil.isNotNullOrEmpty(convertMonthtoDateCustom(periode, true))) {
			query.setParameter("listDateFriday", convertMonthtoDateCustom(periode, true));
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatInap) || CommonUtil.isNotNullOrEmpty(departementICU)
				|| CommonUtil.isNotNullOrEmpty(departementBedahSentral)) {
			query.setParameter("departementRawatInap", Integer.parseInt(departementRawatInap));
			query.setParameter("departementICU", Integer.parseInt(departementICU));
			query.setParameter("departemenPeristi", Integer.parseInt(departemenPeristi));
			query.setParameter("departementBedahSentral", Integer.parseInt(departementBedahSentral));
		}
		if (CommonUtil.isNotNullOrEmpty(listjenisPetugasPelaksana)) {
			query.setParameter("listjenisPetugasPelaksana", listjenisPetugasPelaksana);
		}
		if (CommonUtil.isNotNullOrEmpty(departementRawatJalan) || CommonUtil.isNotNullOrEmpty(instalasiGawatDarurat)
				|| CommonUtil.isNotNullOrEmpty(instalasiRehabilMedik)
				|| CommonUtil.isNotNullOrEmpty(instalasiLaboratorium)
				|| CommonUtil.isNotNullOrEmpty(instalasiRadiologi)) {
			query.setParameter("departementRawatJalan", Integer.parseInt(departementRawatJalan));
			query.setParameter("instalasiGawatDarurat", Integer.parseInt(instalasiGawatDarurat));
			query.setParameter("instalasiRehabilMedik", Integer.parseInt(instalasiRehabilMedik));
			query.setParameter("instalasiLaboratorium", Integer.parseInt(instalasiLaboratorium));
			query.setParameter("instalasiRadiologi", Integer.parseInt(instalasiRadiologi));
		}
		return ((Long) query.getSingleResult()).intValue();
	}

}
