package com.jasamedika.medifirst2000.dao.custom;

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

@Repository
public class RekrutmenDaoCustomImpl extends CoreDaoImpl<Pegawai> implements RekrutmenDaoCustom{

	public RekrutmenDaoCustomImpl() {
		super(Pegawai.class);
	}

	@Override
	public List<Map<String, Object>> getJadwalSeleksi(String periodeLowongan) {
		List<Map<String, Object>> result = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();
		buffer.append("select new Map (noPlanning.noRec as noRecStrukPlanning,"
						+ "noPlanning.noplanning as noPlanning,"
						+ "to_char(noPlanning.tglplanning,'yyyy-mm-dd') as tglPlanning,"
						+ "to_char(noPlanning.tglpegajuan,'yyyy-mm-dd') as tglPengajuan,"
						+ "to_char(noPlanning.tglplanning,'mm-yyyy') as periodeLowongan,"
						+"jenisKelamin.id as jenisKelaminId,"
						+ "jenisKelamin.jenisKelamin as jenisKelamin,"
						+"pegawai.id as pelamarId,"
						+ "pegawai.email as email,"
						+ "pegawai.namaLengkap as namaPelamar,"
						+"produkRs.id as produkRsId,"
						+ "produkRs.namaProduk as namaProdukRs) "
						+"from PlanningRekrutmenSeleksiD model "
						+"left join model.noPlanning noPlanning "
						+"left join model.produkRs produkRs "
						+"left join model.pegawai pegawai "
						+ "left join pegawai.jenisKelamin jenisKelamin ");
		
		if (CommonUtil.isNotNullOrEmpty(periodeLowongan)) {
			buffer.append("where to_char(model.tglPlanning,'mm-yyyy') = :periodeLowongan ");
		}

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

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

		result = query.getResultList();
		return result;
	}

	@Override
	public List<Map<String, Object>> getJadwalSeleksi2(String noRecStrukPlanning) {
		List<Map<String, Object>> result = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();
		buffer.append("select new Map (ruangan.id as ruanganId,ruangan.namaRuangan as namaRuangan) " 
						+"from PlanningRekrutmenSeleksi model "
						+"left join model.ruangan ruangan "
						+"left join model.noPlanning noPlanning "
						+"where noPlanning.noRec=:noRecStrukPlanning");		
		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(noRecStrukPlanning)) {
			query.setParameter("noRecStrukPlanning", noRecStrukPlanning);
		}
		result = query.getResultList();
		return result;
	}

	@Override
	public List<Map<String, Object>> getJadwalSeleksi3(String noRecStrukPlanning) {
		List<Map<String, Object>> result = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();
		buffer.append("select new Map (pegawai.id as petugasId,pegawai.namaLengkap as namaPetugas) "
						+"from PlanningPelayananPetugas model "
						+"left join model.strukPlaning strukPlanning "
						+"left join model.pegawai pegawai "
						+"where strukPlanning.noRec=:noRecStrukPlanning");		
		Query query = em.createQuery(buffer.toString());
		if (CommonUtil.isNotNullOrEmpty(noRecStrukPlanning)) {
			query.setParameter("noRecStrukPlanning", noRecStrukPlanning);
		}
		result = query.getResultList();
		return result;
	}

	@Override
	public List<Map<String, Object>> getJadwalSeleksiPeserta(String noRecPlanning,Integer jenisTestId) {
		List<Map<String, Object>> result = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();
		buffer.append("select new Map (pelamar.id as pelamarId,"
						+ "noPlanning.noplanning,"
						+ "noPlanning.tglplanning,"
						+"produkRs.id,"
						+ "produkRs.namaProduk) " 
						+"from PlanningRekrutmenSeleksiD model "
						+ "left join model.pegawai pelamar ");
		if (CommonUtil.isNotNullOrEmpty(noRecPlanning)) {
			buffer.append("where noPlanning.noRec = :noRecPlanning ");
		}

		if (CommonUtil.isNotNullOrEmpty(jenisTestId)) {
			buffer.append("and produkRs.id = :jenisTestId ");
		}
		System.out.println("QUERYYYYYYYYY : "+buffer.toString());
		Query query = em.createQuery(buffer.toString());

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

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

		return result;
	}

}
