<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class M_alumni extends CI_Model {
	
	function __construct(){
		parent::__construct();
	}
	
	/* Bidang Kerja */
	function list_bidang_kerja($num, $offset){
		$query = $this->db->query("SELECT * FROM almn_bidang ORDER BY id_bidang_kerja LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function get_bidang_kerja($id){
		$query = $this->db->query("SELECT nama_bidang FROM almn_bidang WHERE id_bidang_kerja = '$id' ");
		foreach ($query->result() as $row){
			$nama_bidang_kerja = $row->nama_bidang;
			return $nama_bidang_kerja;
		}
	}
	
	function save_bidang_kerja($data){
		$this->db->insert('almn_bidang', $data);
	}
	
	function update_bidang_kerja($id, $data){
		$this->db->where('id_bidang_kerja', $id);
		$this->db->update('almn_bidang', $data);	
	}
		
	function delete_bidang_kerja($id){
		$this->db->where('id_bidang_kerja',$id);
		$this->db->delete('almn_bidang');
	}
	
	/* Instansi Kerja */
	function list_instansi_kerja($num, $offset){
		$query = $this->db->query("SELECT * FROM almn_instansi ORDER BY id_instansi_kerja LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function get_instansi_kerja($id){
		$query = $this->db->query("SELECT nama_instansi FROM almn_instansi WHERE id_instansi_kerja = '$id' ");
		foreach ($query->result() as $row){
			$nama_instansi_kerja = $row->nama_instansi;
			return $nama_instansi_kerja;
		}
	}
	
	function save_instansi_kerja($data){
		$this->db->insert('almn_instansi', $data);
	}
	
	function update_instansi_kerja($id, $data){
		$this->db->where('id_instansi_kerja', $id);
		$this->db->update('almn_instansi', $data);	
	}
		
	function delete_instansi_kerja($id){
		$this->db->where('id_instansi_kerja',$id);
		$this->db->delete('almn_instansi');
	}
	
	/* Daftar Pekerjaan */
	function list_pekerjaan($num, $offset){
		$query = $this->db->query("SELECT * FROM almn_pekerjaan ORDER BY id_pekerjaan LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function get_pekerjaan($id){
		$query = $this->db->query("SELECT nama_pekerjaan FROM almn_pekerjaan WHERE id_pekerjaan = '$id' ");
		foreach ($query->result() as $row){
			$nama_pekerjaan = $row->nama_pekerjaan;
			return $nama_pekerjaan;
		}
	}
	
	function save_pekerjaan($data){
		$this->db->insert('almn_pekerjaan', $data);
	}
	
	function update_pekerjaan($id, $data){
		$this->db->where('id_pekerjaan', $id);
		$this->db->update('almn_pekerjaan', $data);	
	}
		
	function delete_pekerjaan($id){
		$this->db->where('id_pekerjaan',$id);
		$this->db->delete('almn_pekerjaan');
	}
	
	/* Daftar Alumni */
	function list_alumni($num, $offset){
		$query = $this->db->query("SELECT a.id_almn, p.nmprodi, a.nm_alumni, a.jen_kel, a.thn_msk, a.thn_lulus
								   FROM almn AS a
								   JOIN prodi AS p ON p.kdprodi = a.id_prodi
								   ORDER BY a.id_almn LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function detail_alumni($id_almn){
		$query = $this->db->query("SELECT a.id_almn, p.nmprodi, a.nm_alumni, a.jen_kel, a.tmpt_lhir,
									a.tgl_lhir, a.almt_alumni, a.no_tlp, a.almt_email, a.ukkaos, a.thn_msk, a.thn_lulus,
									b.nama_bidang, i.nama_instansi, pk.nama_pekerjaan, a.nm_instansi, a.almt_instansi, a.id_prodi,
									a.id_bidang_kerja, a.id_pekerjaan, a.id_instansi_kerja
								   FROM almn AS a
								   JOIN prodi AS p ON p.kdprodi = a.id_prodi
								   JOIN almn_bidang AS b ON b.id_bidang_kerja = a.id_bidang_kerja
								   JOIN almn_instansi AS i ON i.id_instansi_kerja = a.id_instansi_kerja
								   JOIN almn_pekerjaan AS pk ON pk.id_pekerjaan = a.id_pekerjaan
								   WHERE a.id_almn = '$id_almn'
								  ");
		return $query;
	}
	
	function filter_daftar($id_prodi, $thnlulus, $num, $offset){
		$query = $this->db->query("
								   SELECT a.id_almn, p.nmprodi, a.nm_alumni, a.jen_kel, a.thn_msk, a.thn_lulus
								   FROM almn AS a
								   JOIN prodi AS p ON p.kdprodi = a.id_prodi
								   WHERE a.id_prodi = '$id_prodi' AND a.thn_lulus = '$thnlulus'
								   ORDER BY a.id_almn LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function filter_daftar_prodi($id_prodi, $num, $offset){
		$query = $this->db->query("SELECT a.id_almn, p.nmprodi, a.nm_alumni, a.jen_kel, a.thn_msk, a.thn_lulus
								   FROM almn AS a
								   JOIN prodi AS p ON p.kdprodi = a.id_prodi
								   WHERE a.id_prodi = '$id_prodi'
								   ORDER BY a.id_almn LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function filter_daftar_tahun($thnlulus, $num, $offset){
		$query = $this->db->query("SELECT a.id_almn, p.nmprodi, a.nm_alumni, a.jen_kel, a.thn_msk, a.thn_lulus
								   FROM almn AS a
								   JOIN prodi AS p ON p.kdprodi = a.id_prodi
								   WHERE a.thn_lulus = '$thnlulus'
								   ORDER BY a.id_almn LIMIT $num OFFSET $offset");
		return $query;
	}
	
	function cari_daftar($kunci, $kunci2){
		$query = $this->db->query("
									SELECT a.id_almn, p.nmprodi, a.nm_alumni, a.jen_kel, a.thn_msk, a.thn_lulus
								    FROM almn AS a
								    JOIN prodi AS p ON p.kdprodi = a.id_prodi
									WHERE a.nm_alumni LIKE '%".$kunci."%' OR a.nm_alumni LIKE '%".$kunci2."%' 
									ORDER BY a.id_almn");		
   		return $query;
	}
	
	function count_cari($kunci, $kunci2){
		$query = $this->db->query("SELECT COUNT(id_almn) as jml_cari FROM almn WHERE nm_alumni LIKE '%".$kunci."%' OR nm_alumni LIKE '%".$kunci2."%'");
		foreach ($query->result() as $row){
			$jc = $row->jml_cari;
			return $jc;
		}
	}
	
	function count_filter($id_prodi, $thnlulus){
		$query = $this->db->query("SELECT COUNT(id_almn) as jml_almn FROM almn WHERE id_prodi = '$id_prodi' AND thn_lulus = '$thnlulus'");
		foreach ($query->result() as $row){
			$cf = $row->jml_almn;
			return $cf;
		}
	}
	
	function count_filter_prodi($id_prodi){
		$query = $this->db->query("SELECT COUNT(id_almn) as jml_almn FROM almn WHERE id_prodi = '$id_prodi'");
		foreach ($query->result() as $row){
			$cf = $row->jml_almn;
			return $cf;
		}
	}
	
	function count_filter_tahun($thnlulus){
		$query = $this->db->query("SELECT COUNT(id_almn) as jml_almn FROM almn WHERE thn_lulus = '$thnlulus'");
		foreach ($query->result() as $row){
			$cf = $row->jml_almn;
			return $cf;
		}
	}
	
	function get_drop_prodi(){
		$query = $this->db->query('SELECT kdprodi, nmprodi FROM prodi ORDER BY kdprodi asc')->result();
		return $query;
	}
	
	function get_drop_pekerjaan(){
		$query = $this->db->query('SELECT id_pekerjaan, nama_pekerjaan FROM almn_pekerjaan ORDER BY id_pekerjaan asc')->result();
		return $query;
	}
	
	function get_drop_bidang(){
		$query = $this->db->query('SELECT id_bidang_kerja, nama_bidang FROM almn_bidang ORDER BY id_bidang_kerja asc')->result();
		return $query;
	}
	
	function get_drop_instansi(){
		$query = $this->db->query('SELECT id_instansi_kerja, nama_instansi FROM almn_instansi ORDER BY id_instansi_kerja asc')->result();
		return $query;
	}
	
	function get_prodi($id){
		$query = $this->db->query("SELECT nmprodi FROM prodi WHERE kdprodi = '$id' ");
		foreach ($query->result() as $row){
			$nama_prodi = $row->nmprodi;
			return $nama_prodi;
		}
	}
	
	function save_daftar($data){
		$this->db->insert('almn', $data);
	}
	
	function update_daftar($id, $data){
		$this->db->where('id_almn', $id);
		$this->db->update('almn', $data);	
	}
	
	function delete_daftar($id){
		$this->db->where('id_almn',$id);
		$this->db->delete('almn');
	}
	
}