package com.jasamedika.medifirst2000.dao;

import com.jasamedika.medifirst2000.entities.SlipGaji;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

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

public interface SlipGajiDao extends JpaRepository<SlipGaji, String> {
	@Query("select sg from SlipGaji sg where sg.pegawai.id = :pegawaiId and sg.bulan between :start and :end")
	Optional<SlipGaji> findByPegawaiAndBulan(@Param("pegawaiId") Integer pegawaiId, @Param("start") Date start,
			@Param("end") Date end);

	@Query("select sg from SlipGaji sg where sg.bulan between :start and :end")
	List<SlipGaji> findByBulan(@Param("start") Date start, @Param("end") Date end);

	@Query("select pg.id from SlipGaji sg inner join sg.pegawai pg where sg.bulan between :start and :end")
	List<Integer> findPegawaiByBulan(@Param("start") Date start, @Param("end") Date end);

	@Query("select distinct pg.id from SlipGajiKomponen sgk " + "inner join sgk.slipGaji sg "
			+ "inner join sg.pegawai pg where sg.bulan between :start and :end")
	List<Integer> findPegawaiKomponenByBulan(@Param("start") Date start, @Param("end") Date end);

	@Query("select new Map(sg.id as id,pg.id as idPegawai,pg.namaLengkap as namaPegawai,"
			+ "coalesce(pg.nipPns,'-') as nip," + "coalesce(kp.kategoryPegawai,'-') as statusKepegawaian) "
			+ "from SlipGaji sg " + "inner join sg.pegawai pg " + "left join pg.kategoryPegawai kp "
			+ "where sg.bulan between :start and :end " + "order by pg.namaLengkap")
	List<Map<String, Object>> findAllByBulan(@Param("start") Date start, @Param("end") Date end);
}
