package com.jasamedika.medifirst2000.dao;

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

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.jasamedika.medifirst2000.entities.IndikatorBiosTransaksi;

@Repository("IndikatorBIOSTransaksiDao")
public interface IndikatorBIOSTransaksiDao extends PagingAndSortingRepository<IndikatorBiosTransaksi, String> {

	@Query("select new map (transaksi.bulan as bulan, " + "transaksi.tahun as tahun, " + "transaksi.jumlah as jumlah, "
			+ "indikator.id as idIndikator, " + "indikator.indikator as indikator, " + "indikator.uraian as uraian, "
			+ "indikator.nmrumpun as nrumpun," + "transaksi.jumlahHari as jumlahHari, "
			+ "indikator.namaExternal as namaExternal, " + "indikator.reportDisplay as layanan) "
			+ "from IndikatorBiosTransaksi transaksi " + "left join transaksi.indikatorBIOS indikator "
			+ "where indikator.reportDisplay like:layanan "
			+ " and transaksi.tahun = :tahun and transaksi.bulan = :bulan ")
	List<Map<String, Object>> findByLayanan(@Param("layanan") String layanan, @Param("tahun") String tahun,
			@Param("bulan") String bulan);

	@Query("select new Map(bios.kode_kelas as kodeRefKelasKamar," + "biosm.id as idMasterBios,"
			+ "(case when pd.tglRegistrasi < cast(:tglAwal as timestamp) then cast(:tglAwal as timestamp) else pd.tglRegistrasi end) as tglPeriodeStartRawat,"
			+ "(case when coalesce(pd.tglPulang, current_timestamp) > cast(:tglAkhir as timestamp) then cast(:tglAkhir as timestamp) else coalesce(pd.tglPulang, current_timestamp) end) as tglPeriodeEndRawat) "
			+ "from PasienDaftar pd, MappingKelasToKelasBios mapp, IndikatorBIOS biosm " + "inner join pd.kelas kls "
			+ "inner join mapp.kelasBios bios " + "where pd.kelasId = mapp.kelasId "
			+ "and biosm.indikator = bios.kode_kelas "
			+ "and case when pd.tglRegistrasi < cast(:tglAwal as timestamp) then cast(:tglAwal as timestamp) else pd.tglRegistrasi end >= cast(:tglAwal as timestamp) "
			+ "and case when coalesce(pd.tglPulang, current_timestamp) > cast(:tglAkhir as timestamp) then cast(:tglAkhir as timestamp) else coalesce(pd.tglPulang, current_timestamp) end <= cast(:tglAkhir as timestamp) "
			+ "and case when coalesce(pd.tglPulang, current_timestamp) > cast(:tglAkhir as timestamp) then cast(:tglAkhir as timestamp) else coalesce(pd.tglPulang, current_timestamp) end "
			+ ">= case when pd.tglRegistrasi < cast(:tglAwal as timestamp) then cast(:tglAwal as timestamp) else pd.tglRegistrasi end "
			+ "and pd.statusEnabled is true " + "order by bios.kode_kelas")
	List<Map<String, Object>> getDataLayananKesehatan(@Param("tglAwal") Date tglAwal, @Param("tglAkhir") Date tglAkhir);

	@Query("select new Map(count(distinct pd.noRegistrasi) as jumlah,"
			+ "dp.id as idDepartemen,dp.namaDepartemen as namaDepartemen,"
			+ "kp.id as idKelompokPasien,kp.kelompokPasien as kelompokPasien) " + "from AntrianPasienDiPeriksa apd "
			+ "inner join apd.pasienDaftar pd " + "inner join apd.ruangan ru " + "inner join ru.departemen dp "
			+ "left join pd.kelompokPasien kp " + "where pd.statusEnabled is true " + "and apd.statusEnabled is true "
			+ "and apd.tglRegistrasi between :tglAwal and :tglAkhir "
			+ "group by dp.id,dp.namaDepartemen,kp.id,kp.kelompokPasien")
	List<Map<String, Object>> getSaldoAwalJumlahPasien(@Param("tglAwal") Date tglAwal,
			@Param("tglAkhir") Date tglAkhir);

	@Query("select new Map(cast(apd.tglRegistrasi as date) as tglRegistrasi,"
			+ "count(distinct pd.noRegistrasi) as jumlah,"
			+ "dp.id as idDepartemen,dp.namaDepartemen as namaDepartemen,"
			+ "kp.id as idKelompokPasien,kp.kelompokPasien as kelompokPasien) " + "from AntrianPasienDiPeriksa apd "
			+ "inner join apd.pasienDaftar pd " + "inner join apd.ruangan ru " + "inner join ru.departemen dp "
			+ "left join pd.kelompokPasien kp " + "where pd.statusEnabled is true " + "and apd.statusEnabled is true "
			+ "and apd.tglRegistrasi between :tglAwal and :tglAkhir "
			+ "group by cast(apd.tglRegistrasi as date),dp.id,dp.namaDepartemen,kp.id,kp.kelompokPasien")
	List<Map<String, Object>> getDataJumlahPasien(@Param("tglAwal") Date tglAwal, @Param("tglAkhir") Date tglAkhir);

	@Query("select new Map(" + "model.noRec as noRec," + "model.kdProfile as kdProfile,"
			+ "model.statusEnabled as statusEnabled," + "model.indikatorBIOSId as indikatorBIOSId,"
			+ "model.jumlah as jumlah," + "model.jumlahHari as jumlahHari," + "model.tglTransaksi as tglTransaksi,"
			+ "model.bulan as bulan," + "model.tahun as tahun," + "model.statusKirim as statusKirim) "
			+ "from IndikatorBiosTransaksi model " + "where model.statusEnabled is true "
			+ "and ((model.tahun >= :thnAwal and model.bulan >= :blnAwal and to_char(model.tglTransaksi,'yyyy-MM-dd') >= :tglAwal) "
			+ "or (model.tahun <= :thnAkhir and model.bulan <= :blnAkhir and to_char(model.tglTransaksi,'yyyy-MM-dd') <= :tglAkhir))")
	List<Map<String, Object>> getSavedTransaksi(@Param("tglAwal") Date tglAwal, @Param("blnAwal") String blnAwal,
			@Param("thnAwal") String thnAwal, @Param("tglAkhir") Date tglAkhir, @Param("blnAkhir") String blnAkhir,
			@Param("thnAkhir") String thnAkhir);

	String strQueryLayananKesehatan = "select new Map(ib.indikator as kdIndikator," + "ib.uraian as indikator,"
			+ "ibt.noRec as noRec," + "ibt.jumlahHari as jmlHari," + "ibt.jumlah as jmlPasien,"
			+ "ibt.tglTransaksi as tglT," + "to_char(ibt.tglTransaksi,'dd-MM-yyyy HH24:MI:SS') as tglTransaksi,"
			+ "ibt.tglUpdate as tglU," + "to_char(ibt.tglUpdate,'dd-MM-yyyy HH24:MI:SS') as tglUpdate,"
			+ "(case when ibt.statusKirim is true then 'Terkirim' else 'Belum Terkirim' end) as statusKirim) "
			+ "from IndikatorBiosTransaksi ibt " + "inner join ibt.indikatorBIOS ib "
			+ "where ibt.statusEnabled is true " + "and ib.statusEnabled is true "
			+ "and ib.indikator in (:listKdLayananKesehatan)";

	String strQueryIndikatorLain = "select new Map(ib.indikator as kdIndikator," + "ib.uraian as indikator,"
			+ "ibt.noRec as noRec," + "ibt.jumlah as jumlah," + "ibt.tglTransaksi as tglT,"
			+ "to_char(ibt.tglTransaksi,'dd-MM-yyyy HH24:MI:SS') as tglTransaksi," + "ibt.tglUpdate as tglU,"
			+ "to_char(ibt.tglUpdate,'dd-MM-yyyy HH24:MI:SS') as tglUpdate,"
			+ "(case when ibt.statusKirim is true then 'Terkirim' else 'Belum Terkirim' end) as statusKirim) "
			+ "from IndikatorBiosTransaksi ibt " + "inner join ibt.indikatorBIOS ib "
			+ "where ibt.statusEnabled is true " + "and ib.statusEnabled is true "
			+ "and ib.indikator in (:listKdIndikatorLain) and ib.nmrumpun = :nmRumpun";

	String strQuerySaldoKeuangan = "select new Map(ibt.noRec as noRec," + "ibt.kdBank as kdBank,"
			+ "ibt.norek as norek," + "ibt.jumlah as jumlah," + "ibt.kdRek as kdRek," + "ibt.tglTransaksi as tglT,"
			+ "to_char(ibt.tglTransaksi,'dd-MM-yyyy HH24:MI:SS') as tglTransaksi," + "ibt.tglUpdate as tglU,"
			+ "to_char(ibt.tglUpdate,'dd-MM-yyyy HH24:MI:SS') as tglUpdate,"
			+ "(case when ibt.statusKirim is true then 'Terkirim' else 'Belum Terkirim' end) as statusKirim) "
			+ "from IndikatorBiosTransaksi ibt " + "where ibt.statusEnabled is true "
			+ " and ibt.indikatorBIOSId is null";

	String bulanTransaksi = " and to_char(ibt.tglTransaksi,'yyyy-MM') = :bulan";

	String periodeTransaksi = " and to_char(ibt.tglTransaksi,'yyyy-MM-dd HH:mm:ss') between :tglAwal and :tglAkhir";

	String idIndikator = " and ib.id = :idIndikator";

	String kdBank = " and ibt.kdBank = :kdBank";

	String kdRek = " and ibt.kdRek = :kdRek";

	String norecTransaksi = " and ibt.noRec = :noRec";

	String statusTransaksi = " and ibt.statusKirim = :statusKirim";

	String sortTglTransKdInd = " order by ibt.tglTransaksi, ib.indikator";

	String sortTglTrans = " order by ibt.tglTransaksi";

	@Query(strQueryLayananKesehatan + bulanTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getLayananKesehatan(@Param("listKdLayananKesehatan") List<String> listKdLayananKesehatan,
			@Param("bulan") String bulan);

	@Query(strQueryLayananKesehatan + norecTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getLayananKesehatanByNorec(
			@Param("listKdLayananKesehatan") List<String> listKdLayananKesehatan, @Param("noRec") String noRec);

	@Query(strQueryLayananKesehatan + bulanTransaksi + statusTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getLayananKesehatanByPeriod(
			@Param("listKdLayananKesehatan") List<String> listKdLayananKesehatan, @Param("bulan") String bulan,
			@Param("statusKirim") Boolean statusKirim, Pageable pageable);

	@Query(strQueryIndikatorLain + bulanTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLain(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("bulan") String bulan, @Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + bulanTransaksi + periodeTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLain(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("bulan") String bulan, @Param("tglAwal") String tglAwal, @Param("tglAkhir") String tglAkhir,
			@Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + idIndikator + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLain(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("idIndikator") Integer idIndikator, @Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + statusTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLain(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("statusKirim") Boolean statusKirim, @Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + bulanTransaksi + periodeTransaksi + idIndikator + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLain(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("bulan") String bulan, @Param("tglAwal") String tglAwal, @Param("tglAkhir") String tglAkhir,
			@Param("idIndikator") Integer idIndikator, @Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + bulanTransaksi + periodeTransaksi + statusTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLain(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("bulan") String bulan, @Param("tglAwal") String tglAwal, @Param("tglAkhir") String tglAkhir,
			@Param("statusKirim") Boolean statusKirim, @Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + idIndikator + statusTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLain(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("idIndikator") Integer idIndikator, @Param("statusKirim") Boolean statusKirim,
			@Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + bulanTransaksi + periodeTransaksi + idIndikator + statusTransaksi
			+ sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLain(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("bulan") String bulan, @Param("tglAwal") String tglAwal, @Param("tglAkhir") String tglAkhir,
			@Param("idIndikator") Integer idIndikator, @Param("statusKirim") Boolean statusKirim,
			@Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + norecTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLainByNorec(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("noRec") String noRec, @Param("nmRumpun") String nmRumpun);

	@Query(strQueryIndikatorLain + bulanTransaksi + statusTransaksi + sortTglTransKdInd)
	List<Map<String, Object>> getIndikatorLainByPeriod(@Param("listKdIndikatorLain") List<String> listKdIndikatorLain,
			@Param("bulan") String bulan, @Param("statusKirim") Boolean statusKirim, @Param("nmRumpun") String nmRumpun,
			Pageable pageable);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuangan(@Param("bulan") String bulan);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + periodeTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuangan(@Param("bulan") String bulan, @Param("tglAwal") String tglAwal,
			@Param("tglAkhir") String tglAkhir);

	@Query(strQuerySaldoKeuangan + kdBank + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByKdBank(@Param("kdBank") String kdBank);

	@Query(strQuerySaldoKeuangan + kdRek + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByKdRek(@Param("kdRek") String kdRek);

	@Query(strQuerySaldoKeuangan + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuangan(@Param("statusKirim") Boolean statusKirim);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + periodeTransaksi + kdBank + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByKdBank(@Param("bulan") String bulan, @Param("tglAwal") String tglAwal,
			@Param("tglAkhir") String tglAkhir, @Param("kdBank") String kdBank);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + periodeTransaksi + kdRek + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByKdRek(@Param("bulan") String bulan, @Param("tglAwal") String tglAwal,
			@Param("tglAkhir") String tglAkhir, @Param("kdRek") String kdRek);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + periodeTransaksi + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuangan(@Param("bulan") String bulan, @Param("tglAwal") String tglAwal,
			@Param("tglAkhir") String tglAkhir, @Param("statusKirim") Boolean statusKirim);

	@Query(strQuerySaldoKeuangan + kdBank + kdRek + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuangan(@Param("kdBank") String kdBank, @Param("kdRek") String kdRek);

	@Query(strQuerySaldoKeuangan + kdBank + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByKdBank(@Param("kdBank") String kdBank,
			@Param("statusKirim") Boolean statusKirim);

	@Query(strQuerySaldoKeuangan + kdRek + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByKdRek(@Param("kdRek") String kdRek,
			@Param("statusKirim") Boolean statusKirim);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + periodeTransaksi + kdBank + kdRek + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuangan(@Param("bulan") String bulan, @Param("tglAwal") String tglAwal,
			@Param("tglAkhir") String tglAkhir, @Param("kdBank") String kdBank, @Param("kdRek") String kdRek);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + periodeTransaksi + kdBank + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByKdBank(@Param("bulan") String bulan, @Param("tglAwal") String tglAwal,
			@Param("tglAkhir") String tglAkhir, @Param("kdBank") String kdBank,
			@Param("statusKirim") Boolean statusKirim);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + periodeTransaksi + kdRek + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByKdRek(@Param("bulan") String bulan, @Param("tglAwal") String tglAwal,
			@Param("tglAkhir") String tglAkhir, @Param("kdRek") String kdRek,
			@Param("statusKirim") Boolean statusKirim);

	@Query(strQuerySaldoKeuangan + kdBank + kdRek + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuangan(@Param("kdBank") String kdBank, @Param("kdRek") String kdRek,
			@Param("statusKirim") Boolean statusKirim);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + periodeTransaksi + kdBank + kdRek + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuangan(@Param("bulan") String bulan, @Param("tglAwal") String tglAwal,
			@Param("tglAkhir") String tglAkhir, @Param("kdBank") String kdBank, @Param("kdRek") String kdRek,
			@Param("statusKirim") Boolean statusKirim);

	@Query(strQuerySaldoKeuangan + norecTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByNorec(@Param("noRec") String noRec);

	@Query(strQuerySaldoKeuangan + bulanTransaksi + statusTransaksi + sortTglTrans)
	List<Map<String, Object>> getSaldoKeuanganByPeriod(@Param("bulan") String bulan,
			@Param("statusKirim") Boolean statusKirim, Pageable pageable);

	@Query("select min(ibt.tglTransaksi) " + "from IndikatorBiosTransaksi ibt " + "inner join ibt.indikatorBIOS ib "
			+ "where ib.indikator in (:listKdIndikator) " + "and ibt.statusEnabled is true "
			+ "and ib.statusEnabled is true")
	Date getTglSaldoAwalIndikator(@Param("listKdIndikator") List<String> listKdIndikator);

	@Query("select min(ibt.tglTransaksi) " + "from IndikatorBiosTransaksi ibt " + "inner join ibt.indikatorBIOS ib "
			+ "where ib.id = :indikatorId " + "and ibt.statusEnabled is true " + "and ib.statusEnabled is true")
	Date getTglSaldoAwalIndikator(@Param("indikatorId") Integer indikatorId);

}
