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

@Repository
public class PlanningPegawaiMuSaReDaoImpl extends CoreDaoImpl<PlanningPegawaiMuSaRe> implements PlanningPegawaiMuSaReDaoCustom{
	
	public PlanningPegawaiMuSaReDaoImpl() {
		super(PlanningPegawaiMuSaRe.class);
	}

	@Override
	public List<Map<String, Object>> getListPlanningPegawaiMutasi(Integer id){
//		List<Map<String, Object>> result = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();
		buffer.append("select NEW Map (");
		buffer.append("a.noPlanning as noUsulan,");
		buffer.append("a.deskripsiMutasiSanksiResignPlan as deskripsiUsulan,");
		buffer.append("a.tglPlanning as tglUsulan,");
		buffer.append("b.namaLengkap as namaPegawai,");
		buffer.append("c.namaJabatan as jabatanFungsional,");
		buffer.append("d.namaJabatan as jabatanInternal,");
		buffer.append("e.namaRuangan as ruanganBekerja,");
		buffer.append("f.eselon as eselon,");
		buffer.append("g.name as pangkat,");
		buffer.append("h.namaJabatan as jabatanFungsionalPlan,");
		buffer.append("i.namaJabatan as jabatanInternalPlan,");
		buffer.append("j.namaRuangan as ruanganMutasi,");
		buffer.append("k.eselon as eselonPlan,");
		buffer.append("l.name as pangkatPlan,");
		buffer.append("m.namaLengkap as namaPengusul) ");
		buffer.append("from PlanningPegawaiMuSaRe a ");
		buffer.append("left join a.pegawai b ");
		buffer.append("left join a.jabatanFungsionalBefore c ");
		buffer.append("left join a.jabatanInternalBefore d ");
		buffer.append("left join a.ruanganBefore e ");
		buffer.append("left join a.eselonBefore f ");
		buffer.append("left join a.pangkatBefore g ");
		buffer.append("left join a.jabatanFungsionalPlan h ");
		buffer.append("left join a.jabatanInternalPlan i ");
		buffer.append("left join a.ruanganPlan j ");
		buffer.append("left join a.eselonPlan k ");
		buffer.append("left join a.pangkatPlan l ");
		buffer.append("left join a.pengusul m ");
		buffer.append("left join b.ruangan n ");
		buffer.append("where n.id=:id and a.approvalStatus=0 and a.statusMusare=1");
		Query query = em.createQuery(buffer.toString());
		query.setParameter("id", id);
		return query.getResultList();
	}

	@Override
	public List<Map<String, Object>> getListPlanningPegawaiSanksi(Integer id) {
//		List<Map<String, Object>> result = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();
		buffer.append("select NEW Map (");
		buffer.append("a.noPlanning as noUsulan,");
		buffer.append("a.deskripsiMutasiSanksiResignPlan as deskripsiUsulan,");
		buffer.append("a.tglPlanning as tglUsulan,");
		buffer.append("a.keteranganLainya as keterangan,");
		buffer.append("b.namaLengkap as namaPegawai,");
		buffer.append("c.namaJabatan as jabatanFungsional,");
		buffer.append("d.namaJabatan as jabatanInternal,");
		buffer.append("e.namaRuangan as ruanganBekerja,");
		buffer.append("f.eselon as eselon,");
		buffer.append("g.name as pangkat,");
		buffer.append("m.namaLengkap as namaPengusul,");
		buffer.append("o.jenisHukuman as jenisHukuman) ");
		buffer.append("from PlanningPegawaiMuSaRe a ");
		buffer.append("left join a.pegawai b ");
		buffer.append("left join a.jabatanFungsionalBefore c ");
		buffer.append("left join a.jabatanInternalBefore d ");
		buffer.append("left join a.ruanganBefore e ");
		buffer.append("left join a.eselonBefore f ");
		buffer.append("left join a.pangkatBefore g ");
		buffer.append("left join a.jabatanFungsionalPlan h ");
		buffer.append("left join a.jabatanInternalPlan i ");
		buffer.append("left join a.ruanganPlan j ");
		buffer.append("left join a.eselonPlan k ");
		buffer.append("left join a.pangkatPlan l ");
		buffer.append("left join a.pengusul m ");
		buffer.append("left join b.ruangan n ");
		buffer.append("left join a.jenisHukumanPlan o ");
		buffer.append("where n.id=:id and a.approvalStatus=0 and a.statusMusare=2");
		Query query = em.createQuery(buffer.toString());
		query.setParameter("id", id);
		return query.getResultList();
	}

	@Override
	public List<Map<String, Object>> getListPlanningPegawaiResign(Integer id) {
//		List<Map<String, Object>> result = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();
		buffer.append("select NEW Map (");
		buffer.append("a.noPlanning as noUsulan,");
		buffer.append("a.deskripsiMutasiSanksiResignPlan as deskripsiUsulan,");
		buffer.append("a.tglPlanning as tglRencanaKeluar,");
		buffer.append("a.keteranganLainya as keterangan,");
		buffer.append("a.tglPengajuan as tglRekam,");
		buffer.append("b.namaLengkap as namaPegawai,");
		buffer.append("c.namaJabatan as jabatanFungsional,");
		buffer.append("d.namaJabatan as jabatanInternal,");
		buffer.append("e.namaRuangan as ruanganBekerja,");
		buffer.append("f.eselon as eselon,");
		buffer.append("g.name as pangkat,");
		buffer.append("m.namaLengkap as namaPengusul) ");
		buffer.append("from PlanningPegawaiMuSaRe a ");
		buffer.append("left join a.pegawai b ");
		buffer.append("left join a.jabatanFungsionalBefore c ");
		buffer.append("left join a.jabatanInternalBefore d ");
		buffer.append("left join a.ruanganBefore e ");
		buffer.append("left join a.eselonBefore f ");
		buffer.append("left join a.pangkatBefore g ");
		buffer.append("left join a.jabatanFungsionalPlan h ");
		buffer.append("left join a.jabatanInternalPlan i ");
		buffer.append("left join a.ruanganPlan j ");
		buffer.append("left join a.eselonPlan k ");
		buffer.append("left join a.pangkatPlan l ");
		buffer.append("left join a.pengusul m ");
		buffer.append("left join b.ruangan n ");
		buffer.append("where n.id=:id and a.approvalStatus=0 and a.statusMusare=3");
		Query query = em.createQuery(buffer.toString());
		query.setParameter("id", id);
		return query.getResultList();
	}

	@Override
	public List<Map<String, Object>> getListPlanningPegawaiMuSaRe(String namaPegawai) {
		List<Map<String, Object>> result = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();	
		buffer.append("select NEW Map (");
		buffer.append("a.noRec as noRec,");
		buffer.append("a.statusMusare as statusMusare,");
		buffer.append("a.approvalStatus as approvalStatus,");
		buffer.append("a.noPlanning as noUsulan,");
		buffer.append("a.deskripsiMutasiSanksiResignPlan as deskripsiUsulan,");
		buffer.append("a.keteranganLainya as keterangan,");
		buffer.append("a.tglPlanning as tglUsulan,");
		buffer.append("a.tglPengajuan as tglRekam,");
		buffer.append("b.id as pegawaiId,");
		buffer.append("b.namaLengkap as namaPegawai,");
		buffer.append("c.id as pengusulId,");
		buffer.append("c.namaLengkap as namaPengusul,");
		buffer.append("d.id as ruanganBeforeId,");
		buffer.append("d.namaRuangan as namaRuanganBefore,");
		buffer.append("e.id as ruanganPlanId,");
		buffer.append("e.namaRuangan as namaRuanganPlan,");
		buffer.append("f.id as jabatanStrukturalBeforeId,");
		buffer.append("f.namaJabatan as namaJabatanStrukturalBefore,");
		buffer.append("g.id as jabatanStrukturalPlanId,");
		buffer.append("g.namaJabatan as namaJabatanStrukturalPlan,");
		buffer.append("h.id as jabatanFungsionalBeforeId,");
		buffer.append("h.namaJabatan as namaJabatanFungsionalBefore,");
		buffer.append("i.id as jabatanFungsionalPlanId,");
		buffer.append("i.namaJabatan as namaJabatanFungsionalPlan,");
		buffer.append("j.id as jabatanInternalBeforeId,");
		buffer.append("j.namaJabatan as namaJabatanInternalBefore,");
		buffer.append("k.id as jabatanInternalPlanId,");
		buffer.append("k.namaJabatan as namaJabatanInternalPlan,");
		buffer.append("l.id as eselonBeforeId,");
		buffer.append("l.eselon as eselonBefore,");
		buffer.append("m.id as eselonPlanId,");
		buffer.append("m.eselon as eselonPlan,");
		buffer.append("n.id as pangkatBeforeId,");
		buffer.append("n.name as pangkatBefore,");
		buffer.append("o.id as pangkatPlanId,");
		buffer.append("o.name as pangkatPlan,");
		buffer.append("p.id as jenisHukumanId,");
		buffer.append("p.jenisHukuman as jenisHukuman) ");
		buffer.append("from PlanningPegawaiMuSaRe a ");
		buffer.append("left join a.pegawai b ");
		buffer.append("left join a.pengusul c ");
		buffer.append("left join a.ruanganBefore d ");
		buffer.append("left join a.ruanganPlan e ");
		buffer.append("left join a.jabatanStrukturalBefore f ");
		buffer.append("left join a.jabatanStrukturalPlan g ");
		buffer.append("left join a.jabatanFungsionalBefore h ");
		buffer.append("left join a.jabatanFungsionalPlan i ");
		buffer.append("left join a.jabatanInternalBefore j ");
		buffer.append("left join a.jabatanInternalPlan k ");
		buffer.append("left join a.eselonBefore l ");
		buffer.append("left join a.eselonPlan m ");
		buffer.append("left join a.pangkatBefore n ");
		buffer.append("left join a.pangkatPlan o ");
		buffer.append("left join a.jenisHukumanPlan p ");
		
		if(CommonUtil.isNotNullOrEmpty(namaPegawai)){
			buffer.append("where b.namaLengkap like :namaPegawai");
			Query query = em.createQuery(buffer.toString());
			query.setParameter("namaPegawai", "%"+namaPegawai+"%");
			
			result = query.getResultList();
		} else if(CommonUtil.isNullOrEmpty(namaPegawai)){
			Query query = em.createQuery(buffer.toString());
			result = query.getResultList();
		}
		
		return result;
	}
	
}
