<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class M_kepegawaian extends CI_Model {
	
	function __construct(){
		parent::__construct();
	}
	
	
	function list_pegawai($num, $offset){
		$query = $this->db->query("SELECT p.nik, p.id_pegawai, p.nmdgngelar, g.golongan, g.pangkat, j.jabatan, ja.jab_akad
									FROM pegawai as p
									JOIN golongan as g ON p.id_golongan = g.id_golongan
									JOIN jabatan as j ON p.id_jabatan = j.id_jabatan
									JOIN jabatan_akad as ja ON p.id_jabakad = ja.id_jabakad
									ORDER BY p.id_pegawai LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function detail_pegawai($id){
		$query = $this->db->query("SELECT p.id_pegawai, p.noktp, p.nmtnpagelar, p.nmdgngelar, p.jenkel, p.tmptlahir, p.tgllahir,
									p.alamat_rmh, p.kelurahan, p.kecamatan, p.kotakab, p.kdpos, p.notlp, p.email,
									p.tgl_msk, p.nik, p.id_unit_kerja, uk.nama_unit, p.id_jabatan, j.jabatan, p.det_jabatan,
									p.id_golongan, g.golongan, g.pangkat, pd.pendidikan, p.id_pend, p.det_pend, 
									p.nidu, p.nidn_nidk_nup, p.id_prodi, pr.nmprodi, p.tmt_dosen, p.id_jabakad, ja.jab_akad, p.id_gol_inpassing 
									FROM pegawai as p
									JOIN golongan as g ON p.id_golongan = g.id_golongan
									JOIN jabatan as j ON p.id_jabatan = j.id_jabatan
									JOIN inv_unit_kerja as uk ON p.id_unit_kerja = uk.id_unit_kerja
									JOIN pendidikan as pd ON p.id_pend = pd.id_pend
									JOIN prodi as pr ON pr.kdprodi = p.id_prodi
									JOIN jabatan_akad as ja ON p.id_jabakad = ja.id_jabakad
									WHERE p.id_pegawai = '$id'");
		return $query;
	}
	
	function detail_t_pendidik($id){
		$query = $this->db->query("SELECT p.id_pegawai, p.noktp, p.nmtnpagelar, p.nmdgngelar, p.id_pend, p.det_pend, p.jenkel,
									p.tmptlahir, p.tgllahir, p.alamat_rmh, p.kelurahan, p.kecamatan, p.kotakab, p.kdpos, p.notlp, p.email,
									p.nidu, p.nidn_nidk_nup, p.id_prodi, pr.nmprodi, p.tmt_dosen, p.id_jabakad, ja.jab_akad, p.id_gol_inpassing, 
									g.golongan, g.pangkat, p.id_st_ikatan_kerja, ik.ikatan
									FROM pegawai as p
									JOIN golongan as g ON p.id_gol_inpassing = g.id_golongan
									JOIN pendidikan as pd ON p.id_pend = pd.id_pend
									JOIN prodi as pr ON pr.kdprodi = p.id_prodi
									JOIN jabatan_akad as ja ON p.id_jabakad = ja.id_jabakad
									JOIN st_ikatan_kerja as ik ON p.id_st_ikatan_kerja = ik.id_st_ikatan_kerja
									WHERE p.id_pegawai = '$id'");
		return $query;
	}
	
	function save_pegawai($data){
		$this->db->insert('pegawai', $data);
	}
	
	function update_pegawai($id, $data){
		$this->db->where('id_pegawai', $id);
		$this->db->update('pegawai', $data);	
	}
	
	function delete_pegawai($id){
		$this->db->where('id_pegawai',$id);
		$this->db->delete('pegawai');
	}
	
	function get_drop_golongan(){
		$query = $this->db->query('SELECT * FROM golongan ORDER BY id_golongan asc')->result();
		return $query;
	}
	
	function get_drop_unit(){
		$query = $this->db->query('SELECT * FROM inv_unit_kerja ORDER BY id_unit_kerja asc')->result();
		return $query;
	}
	
	function get_drop_jabatan(){
		$query = $this->db->query('SELECT * FROM jabatan ORDER BY id_jabatan asc')->result();
		return $query;
	}
	
	function get_drop_jabakad(){
		$query = $this->db->query('SELECT * FROM jabatan_akad ORDER BY id_jabakad asc')->result();
		return $query;
	}
	
	function get_drop_pend(){
		$query = $this->db->query('SELECT * FROM pendidikan ORDER BY id_pend asc')->result();
		return $query;
	}
	
	function get_drop_prodi(){
		$query = $this->db->query('SELECT * FROM prodi ORDER BY kdfakultas, kdprodi asc')->result();
		return $query;
	}
	
	function get_drop_ikerja(){
		$query = $this->db->query('SELECT * FROM st_ikatan_kerja ORDER BY id_st_ikatan_kerja asc')->result();
		return $query;
	}
	
	function cari_t_pendidik($kunci, $kunci2){
		$query = $this->db->query("
									SELECT p.nidu, p.nidn_nidk_nup, p.id_pegawai, p.nmdgngelar, ja.jab_akad, pr.nmprodi
									FROM pegawai as p
									JOIN jabatan_akad as ja ON p.id_jabakad = ja.id_jabakad
									JOIN prodi as pr ON pr.kdprodi = p.id_prodi
									WHERE p.nmtnpagelar LIKE '%".$kunci."%' OR p.nmtnpagelar LIKE '%".$kunci2."%' 
									ORDER BY p.nmdgngelar");		
   		return $query;
	}
	
	function count_cari($kunci, $kunci2){
		$query = $this->db->query("SELECT COUNT(id_pegawai) as jml_cari FROM pegawai WHERE nmtnpagelar LIKE '%".$kunci."%' OR nmtnpagelar LIKE '%".$kunci2."%'");
		foreach ($query->result() as $row){
			$jc = $row->jml_cari;
			return $jc;
		}
	}
	
	function list_t_pendidik($num, $offset){
		$query = $this->db->query("SELECT p.nidu, p.nidn_nidk_nup, p.id_pegawai, p.nmdgngelar, ja.jab_akad, pr.nmprodi
									FROM pegawai as p
									JOIN jabatan_akad as ja ON p.id_jabakad = ja.id_jabakad
									JOIN prodi as pr ON pr.kdprodi = p.id_prodi
									WHERE p.nidu IS NOT NULL
									ORDER BY p.nmdgngelar
									LIMIT $num OFFSET $offset");
									//ORDER BY p.nmdgngelar
									//ORDER BY pr.kdfakultas, p.id_prodi, p.nidu
		return $query;
	}
	
	function jml_dosen(){
		$query = $this->db->query("SELECT count(nidu) as nidu
									FROM pegawai");
		foreach ($query->result() as $row){
			$jd = $row->nidu;
			return $jd;
		}	
	}
	
	function list_t_kependidikan($num, $offset){
		$query = $this->db->query("SELECT p.nik, p.id_pegawai, p.nmdgngelar, g.golongan, g.pangkat, j.jabatan, uk.nama_unit
									FROM pegawai as p
									JOIN golongan as g ON p.id_golongan = g.id_golongan
									JOIN jabatan as j ON p.id_jabatan = j.id_jabatan
									JOIN inv_unit_kerja as uk ON p.id_unit_kerja = uk.id_unit_kerja
									ORDER BY p.id_unit_kerja, p.id_jabatan LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function detail_t_kependidikan($id){
		$query = $this->db->query("SELECT p.id_pegawai, p.noktp, p.nmtnpagelar, p.nmdgngelar, p.jenkel, p.tmptlahir, p.tgllahir,
									p.alamat_rmh, p.kelurahan, p.kecamatan, p.kotakab, p.notlp, p.email, p.tgl_msk, p.nik,
									uk.nama_unit, p.id_jabatan, j.jabatan, p.det_jabatan, pd.pendidikan, p.det_pend, g.golongan, g.pangkat,
									p.id_unit_kerja, p.id_golongan, p.id_pend, p.kdpos
									FROM pegawai as p
									JOIN golongan as g ON p.id_golongan = g.id_golongan
									JOIN jabatan as j ON p.id_jabatan = j.id_jabatan
									JOIN inv_unit_kerja as uk ON p.id_unit_kerja = uk.id_unit_kerja
									JOIN pendidikan as pd ON p.id_pend = pd.id_pend
									WHERE p.id_pegawai = '$id'");
		return $query;
	}

}