package com.jasamedika.medifirst2000.dao.custom;

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

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.jasamedika.medifirst2000.util.CommonUtil;
import com.jasamedika.medifirst2000.util.DateUtil;

/**
 * Repository Custom class
 * 
 * @author Roberto
 */

@Repository("CssdSterilisasiDao")
public class CssdSterilisasiDaoCustomImpl implements CssdSterilisasiDaoCustom {

	@PersistenceContext
	protected EntityManager em;

	@Override
	public List<Map<String, Object>> getPaket() {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select new Map(paket.namaPaket as namaPaket,paket.id as id) from MapPaketToProduk map "
				+ "left join map.paket paket "
				+ "left join map.produk produk group by paket.id,paket.namaPaket order by paket.namaPaket asc");

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

		return query.getResultList();
	}

	@Override
	public Map<String, Object> getByIdProduk(Integer paketId, Integer produkId) {
		StringBuffer buffer = new StringBuffer();

		buffer.append(
				" select new map(produk.namaProduk as namaProduk,produk.id as id,harga.hargaSatuan as hargaSatuan,satuanStandar.satuanStandar as satuanStandar,map.qtyProduk as qtyProduk) ");
		buffer.append(" from HargaNettoProdukByKelas harga ");
		buffer.append(
				" left join harga.produk produk left join produk.satuanStandar satuanStandar,MapPaketToProduk map left join map.paket paket");
		buffer.append(" where map.produkId=produk.id and paket.id=:paketId and produk.id=:produkId");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("paketId", paketId);
		query.setParameter("produkId", produkId);

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

	@Override
	public List<Map<String, Object>> getProdukByPaket(Integer paketId) {
		StringBuffer buffer = new StringBuffer();

		buffer.append(
				" select new map(produk.namaProduk as namaProduk,produk.id as id,harga.hargaSatuan as hargaSatuan,satuanStandar.satuanStandar as satuanStandar,map.qtyProduk as qtyProduk) ");
		buffer.append(" from HargaNettoProdukByKelas harga ");
		buffer.append(
				" left join harga.produk produk left join produk.satuanStandar satuanStandar,MapPaketToProduk map left join map.paket paket");
		buffer.append(" where map.produkId=produk.id and paket.id=:paketId order by produk.namaProduk asc");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("paketId", paketId);

		return query.getResultList();
	}

	@Override
	public List<Map<String, Object>> getProdukNonPaket() {
		StringBuffer buffer = new StringBuffer();

		buffer.append(
				" select new map(produk.namaProduk as namaProduk,produk.id as id,harga.hargaSatuan as hargaSatuan,satuanStandar.satuanStandar as satuanStandar,map.qtyProduk as qtyProduk) ");
		buffer.append(" from HargaNettoProdukByKelas harga ");
		buffer.append(
				" left join harga.produk produk left join produk.satuanStandar satuanStandar,MapPaketToProduk map left join map.paket paket");
		buffer.append(" where map.produkId=produk.id  order by produk.namaProduk asc");

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

		return query.getResultList();
	}

	@Override
	public int getNoUrut(Date date) {
		StringBuffer buffer = new StringBuffer();

		buffer.append(
				"select count(p.noRec)+1 from StrukPelayanan p where p.tglstruk is not null and date(p.tglstruk)=:tanggal  ");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("tanggal", date);

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

	@Override
	public List<Map<String, Object>> listSterilisasi(int rowStart, int rowEnd, String sort, String dir,
			String tanggalAwal, String tanggalAhir, Integer kelompokTransaksiId, String noStruk, String jenisCustomer) {
		StringBuffer buffer = new StringBuffer();

		buffer.append(
				"select new Map (pelanggan.namaPasien as customer ,ruanganAsal.namaRuangan as namaRuangan,strukPelayanan.noRec as strukPelayananId,strukPelayanan.noUrut as noUrut,strukPelayanan.nostruk as noOrder,strukPelayanan.tglstruk as tanggalOrder,prosesSterilisasi.name as prosesSterilisasi,pegawaiPenerima.namaLengkap as petugasPenerima) from StrukPelayananDetail strukPelayananDetail "
						+ "left join strukPelayananDetail.nostruk strukPelayanan "
						+ "left join strukPelayanan.cssdProsesSterilisasi prosesSterilisasi "
						+ "left join strukPelayanan.nocm pelanggan "
						+ "left join strukPelayanan.menerima pegawaiPenerima  "
						+ "left join strukPelayanan.kdkelompoktransaksi kelompokTransaksi left join strukPelayanan.kdruanganasal ruanganAsal "
						+ "where  kelompokTransaksi.id=:kelompokTransaksiId");

		if (CommonUtil.isNotNullOrEmpty(tanggalAwal) && CommonUtil.isNotNullOrEmpty(tanggalAhir)) {
			buffer.append(" and strukPelayanan.tglstruk between :tanggalAwal and :tanggalAhir");
		}

		if (CommonUtil.isNotNullOrEmpty(jenisCustomer)) {
			if (jenisCustomer.equalsIgnoreCase("internal")) {
				buffer.append(" and pelanggan is null");
			} else {
				buffer.append(" and pelanggan is not null");
			}
		}

		if (CommonUtil.isNotNullOrEmpty(noStruk)) {
			buffer.append(" and lower(strukPelayanan.nostruk) like:noStruk");
		}
		buffer.append(
				" group by pelanggan.namaPasien,ruanganAsal.namaRuangan,strukPelayanan.noRec,strukPelayanan.noUrut,strukPelayanan.nostruk,strukPelayanan.tglstruk,prosesSterilisasi.name,pegawaiPenerima.namaLengkap ");
		Query query = em.createQuery(buffer.toString());
		query.setParameter("kelompokTransaksiId", kelompokTransaksiId);

		if (CommonUtil.isNotNullOrEmpty(tanggalAwal) && CommonUtil.isNotNullOrEmpty(tanggalAhir)) {
			query.setParameter("tanggalAwal", DateUtil.toDate(tanggalAwal));
			query.setParameter("tanggalAhir", DateUtil.toDate(tanggalAhir));
		}
		if (CommonUtil.isNotNullOrEmpty(noStruk)) {
			query.setParameter("noStruk", "%" + noStruk.toLowerCase() + "%");
		}

		return query.getResultList();
	}

	@Override
	public int countSterilisasi(String tanggalAwal, String tanggalAhir, Integer kelompokTransaksiId, String noStruk,
			String jenisCustomer) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select count(strukPelayanan.noRec) from StrukPelayananDetail strukPelayananDetail "
				+ "left join strukPelayananDetail.nostruk strukPelayanan "
				+ "left join strukPelayanan.cssdProsesSterilisasi prosesSterilisasi "
				+ "left join strukPelayanan.nocm pelanggan " + "left join strukPelayanan.menerima pegawaiPenerima  "
				+ "left join strukPelayanan.kdkelompoktransaksi kelompokTransaksi "
				+ "where  kelompokTransaksi.id=:kelompokTransaksiId");

		if (CommonUtil.isNotNullOrEmpty(tanggalAwal) && CommonUtil.isNotNullOrEmpty(tanggalAhir)) {
			buffer.append(" and strukPelayanan.tglstruk between :tanggalAwal and :tanggalAhir");
		}
		if (CommonUtil.isNotNullOrEmpty(jenisCustomer)) {
			if (jenisCustomer.equalsIgnoreCase("internal")) {
				buffer.append(" and pelanggan is null");
			} else {
				buffer.append(" and pelanggan is not null");
			}
		}

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

		Query query = em.createQuery(buffer.toString());
		query.setParameter("kelompokTransaksiId", kelompokTransaksiId);

		if (CommonUtil.isNotNullOrEmpty(tanggalAwal) && CommonUtil.isNotNullOrEmpty(tanggalAhir)) {
			query.setParameter("tanggalAwal", DateUtil.toDate(tanggalAwal));
			query.setParameter("tanggalAhir", DateUtil.toDate(tanggalAhir));
		}
		if (CommonUtil.isNotNullOrEmpty(noStruk)) {
			query.setParameter("noStruk", "%" + noStruk.toLowerCase() + "%");
		}

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

	@Override
	public List<Map<String, Object>> getCaraSterilisasi(String strukPelayananId) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select new map(caraSterilisasi.name as caraSterilisasi)  from CssdMappingCaraSterilisasi map "
				+ "left join map.cssdCaraSterilisasi caraSterilisasi "
				+ "where map.strukPelayananId=:strukPelayananId");

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

		query.setParameter("strukPelayananId", strukPelayananId);

		return query.getResultList();
	}

	@Override
	public String jamMulaiUjiSwap(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(min(to_char(p.tglPlanningAwalAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Uji Swap'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamAhirUjiSwap(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(max(to_char(p.tglPlanningAkhirAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Uji Swap'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamMulaiDecontaminasi(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(min(to_char(p.tglPlanningAwalAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Decontaminasi'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamAhirDecontaminasi(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(max(to_char(p.tglPlanningAkhirAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Decontaminasi'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamMulaiPengemasan(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(min(to_char(p.tglPlanningAwalAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Pengemasan'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamAhirPengemasan(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(max(to_char(p.tglPlanningAkhirAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Pengemasan'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamMulaiSterilisasi(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(min(to_char(p.tglPlanningAwalAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Steril'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamAhirSterilisasi(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(max(to_char(p.tglPlanningAkhirAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Steril'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamMulaiPenyimpanan(String noRec) {

		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(min(to_char(p.tglPlanningAwalAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Penyimpanan'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jamAhirPenyimpanan(String noRec) {

		StringBuffer buffer = new StringBuffer();

		buffer.append("select coalesce(max(to_char(p.tglPlanningAkhirAn,'HH24:MI')),'-') from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Penyimpanan'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object jenisSterilisasi(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select planning.deskripsiplanning as jenisSterilisasi from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Steril'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object noKirim(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select planning.noplanning_intern as jenisSterilisasi from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Distribusi'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Object status(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select planning.deskripsiplanning as status from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Distribusi'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public Map<String, Object> findStrukPelayananByNoRec(String noRec) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(
				" select new Map(strukPelayanan.NoCMId as customerId,strukPelayanan.noRec as noRec,strukPelayanan.noUrut as noBundel, strukPelayanan.nostruk as noOrder,strukPelayanan.tglstruk as tanggalPlanning, "
						+ "ruanganAsal.namaRuangan as ruanganAsal,ruanganTujuan.namaRuangan as ruanganTujuan , "
						+ "pegawaiPenerima.namaLengkap as petugasPenerima,menyerahkan.namaLengkap as petugasMenyerahkan )"
						+ "from StrukPelayanan strukPelayanan left join strukPelayanan.kdruangan ruanganTujuan left join strukPelayanan.menyerahkan menyerahkan "
						+ "left join strukPelayanan.kdruanganasal ruanganAsal "
						+ "left join strukPelayanan.menerima as pegawaiPenerima where  strukPelayanan.noRec=:noRec ");

		Query query = em.createQuery(buffer.toString());
		query.setMaxResults(1);
		query.setParameter("noRec", noRec);

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

		return r;
	}

	@Override
	public List<Map<String, Object>> findStrukPelayananDetailByNoRec(String noRec) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(
				" select new Map ( detail.keteranganlainnya as spesifikasi,paket.namaPaket as namaPaket,satuan.satuanStandar as satuanStandar,produk.id as produkId,produk.namaProduk as namaProduk,detail.qtyproduk as qtyProduk ) "
				+ "from StrukPelayananDetail detail left join detail.nostruk strukPelayanan left join strukPelayanan.kdruangan ruanganTujuan "
				+ "left join strukPelayanan.kdruanganasal ruanganAsal "
				+ "left join strukPelayanan.menerima as pegawaiPenerima "
				+ "left join detail.kdproduk produk   "
				+ "left join produk.satuanStandar satuan, "
				+ "MapPaketToProduk mpt left join mpt.paket paket "
				+ "where mpt.produkId=produk.id  "
				+ "and strukPelayanan.noRec=:noRec ");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

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

		return r;
	}

	@Override
	public Map<String, Object> findPasienByStrukPelayanan(String noRec) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(
				" select new Map ( customer.namaPasien as customer,customer.noTelepon as noTelepon,strukPlanning.siklus as siklus ) "
				+ "from PlanningPelayanan p left join p.strukPlaning strukPlanning left join p.strukPelayanan strukPelayanan left join strukPelayanan.nocm customer where strukPelayanan.noRec=:noRec and strukPlanning.namaplanning='Steril' "
				+ "group by customer.namaPasien,customer.noTelepon,strukPlanning.siklus ");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

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

		return r;
	}

	@Override
	public List<Map<String, Object>> findStrukPelayananDetailByNoRecWithHarga(String noRec) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(
				" select new map( detail.keteranganlainnya as spesifikasi,paket.namaPaket as namaPaket,satuan.satuanStandar as satuanStandar,produk.id as produkId,produk.namaProduk as namaProduk,detail.qtyproduk as qtyProduk ,harganettoByKelas.hargaSatuan as hargaSatuan) "
				+ " from StrukPelayananDetail detail left join detail.nostruk strukPelayanan left join strukPelayanan.kdruangan ruanganTujuan "
				+ " left join strukPelayanan.kdruanganasal ruanganAsal "
				+ " left join strukPelayanan.menerima as pegawaiPenerima "
				+ " left join detail.kdproduk produk  "
				+ " left join produk.satuanStandar satuan,"
				+ " MapPaketToProduk mpt left join mpt.paket paket,"
				+ " HargaNettoProdukByKelas harganettoByKelas"
				+ " where mpt.produkId=produk.id and "
				+ " harganettoByKelas.produkId=produk.id "
				+ " and strukPelayanan.noRec=:noRec");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

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

		return r;
	}

	
	@Override
	public String jenisHarga(String noRec) {
		StringBuffer buffer = new StringBuffer();

		buffer.append("select planning.jenisHarga as jenisHarga from PlanningPelayanan p "
				+ "left join p.strukPelayanan strukPelayanan left join p.strukPlaning planning where strukPelayanan.noRec=:noRec and planning.namaplanning='Steril'");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("noRec", noRec);

		return ((String) query.getSingleResult());
	}

	@Override
	public List<Map<String, Object>> getJenisPaket() {
		StringBuffer buffer = new StringBuffer();
		buffer.append(
				" select new map(jenisPaket.id as id, " +
			" jenisPaket.jenisPaket as jenisPaket) from JenisPaket jenisPaket");

		Query query = em.createQuery(buffer.toString());
		//query.setMaxResults(1);
		//query.setParameter("noRec", noRec);

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

	@Override
	public List<Map<String, Object>> getPaketByJenisPaket(Integer idJenisPaket) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(
				" select new map(paket.id as idPaket, paket.namaPaket as namaPaket) " +
			" from Paket paket left join paket.jenisPaket jenisPaket " +
			" where jenisPaket.id=:idJenisPaket");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("idJenisPaket", idJenisPaket);

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

	@Override
	public List<Map<String, Object>> getMapPaketByProduk(Integer idPaket) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(
			" select new map(a.id as id, produk.id as idProduk, a.qtyProduk as qty, " +
			" produk.namaProduk as namaProduk, satuan.id as idSatuan, " +
			" satuan.satuanStandar as satuanStandar, paket.id as idPaket) " +
			" from MapPaketToProduk a " +
			" left join a.produk produk " +
			" left join a.satuan satuan " +
			" left join a.paket paket where paket.id=:idPaket");

		Query query = em.createQuery(buffer.toString());
		query.setParameter("idPaket", idPaket);

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