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

@Repository
public class PenyusunanTRPNBPDaoCustomImpl extends CoreDaoImpl<StrukPelayananDetail>
		implements PenyusunanTRPNBPDaoCustom {

	public PenyusunanTRPNBPDaoCustomImpl() {
		super(StrukPelayananDetail.class);
	}

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

		StringBuffer buffer = new StringBuffer();
		buffer.append("select count(spd.noRec) from StrukPelayananDetail spd join spd.kdproduk pdk ");

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("where date(spd.tglpelayanan) between date(:dateStart) and date(:dateEnd) ");
		}

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

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

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

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getPenyusunanTRPNBP(int rowStart, int rowEnd, String sort, String dir,
			String dateStart, String dateEnd) {

		StringBuffer buffer = new StringBuffer();
		// buffer.append(
		// "select new map(pdk.id as produkId,pdk.namaProduk as
		// namaProduk,SUM(spd.qtyproduk) as qtyTotal,spd.hargasatuan as
		// hargaSatuan,"
		// + "(SUM(spd.qtyproduk) * spd.hargasatuan) as total) from
		// StrukPelayananDetail spd join spd.kdproduk pdk ");

		buffer.append("select new map(pdk.id as produkId,pdk.kdProduk as kdProduk,pdk.namaProduk as namaProduk,"
				+ "detail.volumeRealisasi as volumeRealisasi,detail.volumeTarget as volumeTarget,detail.harga as harga,"
				+ "detail.totalTarget as totalTarget,detail.totalRealisasi as totalRealisasi,"
				+ "SUM(detail.totalRealisasi+detail.totalTarget) as total,p.prosentase as prosentase,p.tglPenyusunan as tglPenyusunan,"
				+ "p.periodeAwal as periodeAwal,p.periodeAkhir as periodeAkhir,p.tahun as tahun) "
				+ "from PenyusunanTRPNPB p left join p.penyusunanDetailTRPNPBSet detail left join detail.produk pdk ");

		if (CommonUtil.isNotNullOrEmpty(dateStart) && CommonUtil.isNotNullOrEmpty(dateEnd)) {
			buffer.append("where date(p.periodeAwal) between date(:dateStart) and date(:dateEnd) ");
		}

		buffer.append("group by pdk.id,pdk.kdProduk,pdk.namaProduk,detail.volumeRealisasi,"
				+ "detail.harga,detail.totalTarget,detail.totalRealisasi,"
				+ "p.prosentase,p.tglPenyusunan,p.tahun,detail.volumeTarget,p.periodeAwal,p.periodeAkhir ");

		if (sort != "") {
			buffer.append("order by pdk." + sort + " " + dir);
		} else {
			buffer.append("order by pdk.namaProduk asc ");
		}

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

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

		query.setFirstResult(rowStart);
		query.setMaxResults(rowEnd);

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

		return list;
	}

	@Override
	public int findCountDaftarPenyusunanTRPNPB(Integer tahun) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select count(trpnpb.noRec) from PenyusunanTRPNPB trpnpb left join trpnpb.penyusunanDetailTRPNPBSet trpnpbDetail ");

		buffer.append("where trpnpb.tahun=:tahun ");

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

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

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

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDaftarPenyusunanTRPNBP(int rowStart, int rowEnd, Integer tahun) {

		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select distinct new map(trpnpb.tahun as tahun,trpnpb.prosentase as prosentase,trpnpb.periodeAwal as periodeAwal,"
						+ "trpnpb.periodeAkhir as periodeAkhir,trpnpb.status as status,"
						+ "SUM(trpnpbDetail.totalRealisasi+trpnpbDetail.totalTarget) as grandTotal) "
						+ "from PenyusunanTRPNPB trpnpb left join trpnpb.penyusunanDetailTRPNPBSet trpnpbDetail ");

		if (CommonUtil.isNotNullOrEmpty(tahun)) {
			buffer.append("where trpnpb.tahun=:tahun ");
		}

		buffer.append("group by trpnpb.tahun,trpnpb.status,trpnpb.prosentase,trpnpb.periodeAwal,trpnpb.periodeAkhir ");

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

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

		query.setFirstResult(rowStart);
		query.setMaxResults(rowEnd);

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

		return list;
	}

}
