package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;

import util.AuthenticationService;
import util.HibernateUtil;

public class MasterDAOImpl {

	public Map<String, Object> getInboxNotification() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if( !session.getTransaction().isActive() ) session.beginTransaction();

		String sql = "SELECT U.ID, U.NOMOR_USUL, U.JENIS_USUL, U.TANGGAL_USUL "
				+"FROM "
				+"INBOX I "
				+"INNER JOIN PROSEDUR_STEP STEP ON I.PROSEDUR_STEP_ID = STEP.ID "
				+"INNER JOIN BERKAS BKS ON BKS.ID = I.ID "
				+"INNER JOIN USUL U ON BKS.USUL_ID = U.ID "
				+"WHERE STEP.NCSIS_PROFILE_ID IN ( "
				+"SELECT ID  FROM NCSIS_PROFILE WHERE PROSEDUR_PROFILE = 1  "
				+ "AND ID IN ( SELECT NCSIS_PROFILE_ID FROM USER_PROFILE WHERE NCSIS_USER_PNS_ORANG_ID=:pnsId )  "
				+"AND LOWER( PROFILE_NAMA ) NOT LIKE '%paper%' "
				+") "
				+"AND I.SATUAN_KERJA_ID = (SELECT SATUAN_KERJA_KERJA_ID FROM PNS WHERE ID=:pnsId)";

		Query q = session.createSQLQuery(sql);
		new AuthenticationService();
		q.setParameter("pnsId", AuthenticationService.getUserAuthentication());
		long recordsTotal = q.list().size();
		q.setMaxResults(5);

		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		@SuppressWarnings("unchecked")
		List<Object> r = q.list();

		List<Object> lists = new ArrayList<Object>();
		for (Object o : r) {
			Map<String, Object> m = siasn.util.Util.convertColumnToKey(o);
			lists.add(m);
		}

		Map<String, Object> result = new HashMap<String, Object>();
		result.put("data", lists);
		result.put("recordsTotal", recordsTotal);
		return result;
	}

	public List<Object> getNip(String nipBaru, String isPns) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if(!session.getTransaction().isActive()) session.beginTransaction();

		String sql = null;
		if (isPns.equals("pns")) {
			sql = "SELECT A.ID, A.NIP_BARU,B.NAMA, C.NAMA NAMA_JABATAN,D.NAMA_UNOR UNOR_NAMA,E.NAMA NAMA_GOLONGAN,A.TMT_GOLONGAN,"
					+ "A.TMT_JABATAN, I.NAMA NAMA_INSTANSI "
					+ "FROM PNS A LEFT JOIN ORANG B ON A.ID = B.ID "
					+ "LEFT JOIN JABATAN_FUNGSIONAL C ON A.JABATAN_FUNGSIONAL_ID = C.ID "
					+ "LEFT JOIN UNOR D ON A.UNOR_ID = D.ID "
					+ "LEFT JOIN GOLONGAN E ON A.GOLONGAN_ID = E.ID "
					+ "LEFT JOIN INSTANSI I ON A.INSTANSI_KERJA_ID = I.ID "
					+ "WHERE A.NIP_BARU = :pnsId ";
		} else if (isPns.equals("non")) {
			sql = "SELECT A.ID,A.NRP NIP_BARU,A.NAMA,A.JABATAN NAMA_JABATAN,A.UNOR UNOR_NAMA,"
					+ "A.GOLONGAN NAMA_GOLONGAN,A.TMT_GOLONGAN " + "FROM ATASAN_NON_PNS A "
					+ "WHERE A.NRP = :pnsId ";
		}

		Query q = session.createSQLQuery(sql);

		q.setParameter("pnsId", nipBaru);
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}
	public List<Object> getEselon() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM ESELON";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs)
			results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getInstansiLike(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT DISTINCT initcap(NAMA) INSTANSI,ID,LOKASI_ID FROM INSTANSI "
				+ "WHERE lower(NAMA) LIKE lower(:query)";

		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getLhkpnJenisForm(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		List<Object> results = new ArrayList<Object>();

		String sql = "SELECT ID, NAMA FROM LHKPN_JENIS_FORM WHERE LOWER(NAMA) LIKE :query";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(10);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

		List<?> rs = q.list();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;

	}

	public List<Object> getLhkpnNamaForm(String id) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		List<Object> results = new ArrayList<Object>();

		String sql = "SELECT NAMA FROM LHKPN_JENIS_FORM WHERE ID = :id";
		Query q = session.createSQLQuery(sql);
		q.setParameter("id", id);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

		List<?> rs = q.list();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;

	}

	public List<Object> getSatkerLike(String instansiId, String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA,LOKASI_ID "
				+ "FROM SATUAN_KERJA "
				+ "WHERE INSTANSI_ID=:instansiId and lower(nama) LIKE LOWER(:query) ORDER BY KANREG_ID ASC";

		Query q = session.createSQLQuery(sql);
		q.setParameter("instansiId", instansiId);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getKlasifikasiBerita() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM MYSAPK_KLASIFIKASI_BERITA";

		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getUnorLike(String instansiId, String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT INITCAP(a.NAMA_UNOR) UNOR,a.ID,"
				+ "(CASE WHEN A.INDUK_UNOR_ID IS NULL THEN initcap(A.NAMA_UNOR) "
				+ "	ELSE ( SELECT initcap(NAMA_UNOR) FROM UNOR WHERE ID = A.INDUK_UNOR_ID ) END) UNOR_INDUK,"
				+ "(CASE WHEN A.INDUK_UNOR_ID IS NULL THEN A.ID ELSE ( SELECT ID FROM UNOR WHERE ID = A.INDUK_UNOR_ID ) END) UNOR_INDUK_ID "
				+ "FROM UNOR A "
				+ "WHERE A.INSTANSI_ID=:instansiId AND LOWER(A.NAMA_UNOR) LIKE LOWER(:query) AND A.STATUS='A' "
				+ "ORDER BY A.ESELON_ID ASC";

		Query q = session.createSQLQuery(sql);
		q.setParameter("instansiId", instansiId);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getPenghargaanLike(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT A.ID, A.NAMA "
				+ "FROM JENIS_HARGA A "
				+ "WHERE LOWER(A.NAMA) LIKE LOWER(:query) "
				+ "ORDER BY A.NAMA ASC";

		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getJabatanLike(String param, String instansiId, String query) {
		List<Object> results = new ArrayList<Object>();

		if (param.toLowerCase().equals("umum")) {
			results = getJabatanFungsionalUmum(query);

		} else if (param.toLowerCase().equals("fungsional")) {
			results = getJabatanFungsional(query);

		} else if (param.toLowerCase().equals("struktural")) {
			results = getJabatanStruktural(instansiId, query);
		}

		return results;
	}

	private List<Object> getJabatanFungsional(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA, IS_SYARAT_JABATAN FROM JABATAN_FUNGSIONAL WHERE lower(nama) like(:query)";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getJabatanFungs() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM KEL_JABATAN";

		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	private List<Object> getJabatanFungsionalUmum(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM JABATAN_FUNGSIONAL_UMUM WHERE lower(nama) like(:query)";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	private List<Object> getJabatanStruktural(String instansiId, String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA_JABATAN AS NAMA FROM UNOR WHERE INSTANSI_ID=:instansiId AND LOWER(NAMA_JABATAN) LIKE(:query)";
		Query q = session.createSQLQuery(sql);
		q.setParameter("instansiId", instansiId);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getPendidikanFormalLike(String tkPendidikan, String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();
		String sql = "SELECT ID,NAMA FROM PENDIDIKAN WHERE TK_PENDIDIKAN_ID=:tkPendidikan and lower(NAMA) LIKE lower(:query)";

		Query q = session.createSQLQuery(sql);
		q.setParameter("tkPendidikan", tkPendidikan);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getTingkatPendidikan() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM TK_PENDIDIKAN";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getDiklatLike(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM LATIHAN_STRUKTURAL WHERE lower(NAMA) LIKE lower(:query)";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getJenisKursusLike(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID, NAMA FROM JENIS_KURSUS WHERE lower(NAMA) LIKE lower(:query)";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public String getInstansiByPNSId(String pnsId) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT INSTANSI_KERJA_ID FROM PNS WHERE ID=:pnsId";
		Query q = session.createSQLQuery(sql);
		q.setParameter("pnsId", pnsId);
		@SuppressWarnings("unchecked")
		List<String> rs = q.list();

		return (rs.size() > 0) ? rs.get(0).toString() : null;
	}

	public List<Object> getJenisJabatan() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM JENIS_JABATAN";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}


	public List<Object> getUserProsedurProfil(String pnsId) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID, PROFILE_NAMA, LOKASI_PROFILE FROM NCSIS_PROFILE WHERE PROSEDUR_PROFILE = '1' AND ID IN "
				+ "(SELECT NCSIS_PROFILE_ID FROM USER_PROFILE WHERE NCSIS_USER_PNS_ORANG_ID=:pnsId) "
				+ "AND LOWER(PROFILE_NAMA) NOT LIKE '%paper%'";

		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		q.setParameter("pnsId", pnsId);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;

	}

	public List<Object> getJenisKompetensi() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM REF_JENIS_KOMPETENSI";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getJenisTingkatPrestasi() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM JENIS_TK_PRESTASI";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getJenisHukuman() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM JENIS_HUKUMAN";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}


	public List<Object> getCltn() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM CLTN";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getGolongan() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM GOLONGAN";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getJenisKp() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM JENIS_KP";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getJenisKepanitiaan() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT JENIS_ORGANISASI, NAMA FROM JENIS_KEPANITIAAN";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getJenisOrganisasi() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM JENIS_ORGANISASI";
		Map<String, Object> results = new HashMap<String, Object>();
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> result = new ArrayList<Object>();
		for(Object o : rs) result.add( siasn.util.Util.convertColumnToKey(o) );

		return result;
	}

	public List<Object> getKedudukanHukum() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM KEDUDUKAN_HUKUM";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();
		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getLokasi(String query) {
				Session session = HibernateUtil.getSessionFactory().getCurrentSession();
				if (!session.getTransaction().isActive()) session.beginTransaction();

				String sql = "SELECT * FROM LOKASI "
						+ "WHERE LOWER(NAMA) LIKE LOWER(:query) "
						+ "ORDER BY NAMA ASC";
				Query q = session.createSQLQuery(sql);
				q.setParameter("query", "%"+query+"%");
				q.setMaxResults(5);
				q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List<?> rs = q.list();

				List<Object> results = new ArrayList<Object>();
				for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

				return results;
			}
	public List<Object> getPasangan(String query, String pnsId) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT A.ID, A.ORANG_ID, B.NAMA FROM PNS_ORANG_ISTRI A "
				+ "INNER JOIN ORANG B ON A.ORANG_ID = B.ID "
				+ "WHERE PNS_ORANG_ID = :pnsId AND LOWER(B.NAMA) LIKE LOWER(:query) ";
		Query q = session.createSQLQuery(sql);
		q.setParameter("pnsId", pnsId);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getKedudukanOrganisasi() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID,NAMA FROM KEDUDUKAN_ORGANISASI";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getProfesiLike(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID, NAMA FROM PROFESI WHERE lower(NAMA) LIKE lower(:query)";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}
	public List<Object> getPNS(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT B.NAMA, A.* FROM PNS A "
				+ "INNER JOIN ORANG B ON A.ID = B.ID "
				+ "WHERE lower(A.NIP_BARU) LIKE LOWER(:query) "
				+ " ORDER BY NAMA ASC";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;


	}

	public List<Object> getJenisPegawai() {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID, NAMA FROM JENIS_PEGAWAI";
		Query q = session.createSQLQuery(sql);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}

	public List<Object> getNonPNS(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT * FROM ATASAN_NON_PNS "
				+ "WHERE LOWER(NAMA) LIKE LOWER(:query) "
				+ "ORDER BY NAMA ASC";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}
	public List<Object> getKpknLike(String query) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		if (!session.getTransaction().isActive()) session.beginTransaction();

		String sql = "SELECT ID, NAMA_ASLI AS NAMA FROM KPKN WHERE lower(NAMA) LIKE lower(:query)";
		Query q = session.createSQLQuery(sql);
		q.setParameter("query", "%"+query+"%");
		q.setMaxResults(5);
		q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> rs = q.list();

		List<Object> results = new ArrayList<Object>();
		for(Object o : rs) results.add( siasn.util.Util.convertColumnToKey(o) );

		return results;
	}
}