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.StrukBuktiPenerimaanCaraBayar;
import com.jasamedika.medifirst2000.util.CommonUtil;

@Repository
public class DaftarPenerimaanDaoCustomImpl extends CoreDaoImpl<StrukBuktiPenerimaanCaraBayar>
		implements DaftarPenerimaanDaoCustom {

	public DaftarPenerimaanDaoCustomImpl() {
		super(StrukBuktiPenerimaanCaraBayar.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDaftarPenerimaan(Integer idPegawai, Integer idCaraBayar, Integer idKelTransaksi,
			String dateStartTglSbm, String dateEndTglSbm, String dateStartVerif, String dateEndVerif,
			String dateStartClosing, String dateEndClosing, String status, String noClosing, String noVerifikasi) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select new map(noSbm.noRec as noRec,noSbm.nosbm as noSbm,noClosing.noClosing as noClosing,noVerif.noverifikasi as noVerifikasi,"
						+ "noSbm.keteranganlainnya as keterangan,noSbm.totaldibayar as totalPenerimaan,"
						+ "penerima.id as idPegawai,penerima.namaLengkap as namaPenerima,"
						+ "caraBayar.id as idCaraBayar,caraBayar.caraBayar as caraBayar,"
						+ "kelTransaksi.id as idKelTransaksi,kelTransaksi.kelompokTransaksi as kelTransaksi,noSbm.tglsbm as tglSbm,"
						+ "noVerif.tglverifikasi as tglVerif,noClosing.tglClosing as tglClosing,"
						+ "(CASE WHEN noVerif.tglverifikasi is null AND noClosing.tglClosing is null THEN '' "
						+ "WHEN noVerif.tglverifikasi is not null THEN 'VERIFIKASI' "
						+ "WHEN noClosing.tglClosing is not null THEN 'CLOSING' ELSE '' END) as status) "
						+ "from StrukBuktiPenerimaanCaraBayar sbpcb "
						+ "left join sbpcb.nosbm noSbm left join noSbm.kdpegawaipenerima.pegawai penerima left join sbpcb.kdcarabayar caraBayar "
						+ "left join noSbm.kdkelompoktransaksi kelTransaksi left join noSbm.noverifikasi noVerif left join noSbm.noclosing noClosing "
						+ "where noSbm.nosbm is not null ");

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

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

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

		if (CommonUtil.isNotNullOrEmpty(dateStartTglSbm) && CommonUtil.isNotNullOrEmpty(dateEndTglSbm)) {
			buffer.append("and date(noSbm.tglsbm) between date(:dateStartTglSbm) and date(:dateEndTglSbm) ");
		}

		if (CommonUtil.isNotNullOrEmpty(dateStartVerif) && CommonUtil.isNotNullOrEmpty(dateEndVerif)) {
			buffer.append("and date(noVerif.tglverifikasi) between date(:dateStartVerif) and date(:dateEndVerif) ");
		}

		if (CommonUtil.isNotNullOrEmpty(dateStartClosing) && CommonUtil.isNotNullOrEmpty(dateEndClosing)) {
			buffer.append("and date(noClosing.tglClosing) between date(:dateStartClosing) and date(:dateEndClosing) ");
		}

		if (CommonUtil.isNotNullOrEmpty(status)) {
			if (status.equalsIgnoreCase("VERIFIKASI")) {
				buffer.append("and noVerif.tglverifikasi is not null AND noClosing.tglClosing is null ");
			} else if (status.equalsIgnoreCase("CLOSING")) {
				buffer.append("and noVerif.tglverifikasi is not null AND noClosing.tglClosing is not null ");
			} else if (status.equalsIgnoreCase("-")) {
				buffer.append("and noVerif.tglverifikasi is null AND noClosing.tglClosing is null ");
			}
		}

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

		if (CommonUtil.isNotNullOrEmpty(noVerifikasi)) {
			buffer.append("and lower(noVerif.noverifikasi) like lower(:noVerifikasi) ");
		}

		buffer.append("order by noSbm.nosbm asc ");

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

		if (CommonUtil.isNotNullOrEmpty(dateStartTglSbm) && CommonUtil.isNotNullOrEmpty(dateEndTglSbm)) {
			query.setParameter("dateStartTglSbm", dateStartTglSbm);
			query.setParameter("dateEndTglSbm", dateEndTglSbm);
		}

		if (CommonUtil.isNotNullOrEmpty(dateStartVerif) && CommonUtil.isNotNullOrEmpty(dateEndVerif)) {
			query.setParameter("dateStartVerif", dateStartVerif);
			query.setParameter("dateEndVerif", dateEndVerif);
		}

		if (CommonUtil.isNotNullOrEmpty(dateStartClosing) && CommonUtil.isNotNullOrEmpty(dateEndClosing)) {
			query.setParameter("dateStartClosing", dateStartClosing);
			query.setParameter("dateEndClosing", dateEndClosing);
		}

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

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

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

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

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

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

		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getSetoran(String noRecSbm) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select new map(sbp.noRec as noRec,noStruk.noRec as noRecStrukPelayanan,noVerif.noRec as noRecVerifikasi,"
						+ "kelTransaksi.id as idKelTransaksi,penerima.id as idPenerima,sbp.kdpenjaminparekanan as penjaminPaRekanan,"
						+ "sbp.keteranganlainnya as keterangan,sbp.nosbm as noSbm,sbp.nosbm_intern as noSbmIntern,"
						+ "sbp.totaldibayar as totalDiBayar,sbp.tglsbm as tglSbm) from StrukBuktiPenerimaan sbp left join sbp.nostruk noStruk "
						+ "left join sbp.noverifikasi noVerif left join sbp.kdkelompoktransaksi kelTransaksi "
						+ "left join sbp.kdpegawaipenerima penerima where noVerif.noRec is null ");

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

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

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

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

		return list;

	}

}
