<?php 
class Rep_spp_model extends CI_Model {
	
	function get_data($smk, $jur, $kelas, $ruang, $thn) {
		$tahun_ajaran = $this->get_tahun_ajaran();
		if(empty($smk)&&empty($jur)&&empty($kelas)&&empty($ruang)){
			$q = $this->db->query("
			select 
				a.nis,
				b.nama,
				c.ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ref_siswa b, ref_mapping_kelas c
			where a.nis = b.nis
			and b.id_mapping_kelas = c.id
			and a.tahun_ajaran = '".$thn."' and b.status='1'");
		}else if(empty($jur)&&(!empty($smk))){
			$q = $this->db->query("
			select 
				a.nis,
				b.nama,
				c.ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ref_siswa b, ref_mapping_kelas c
			where a.nis = b.nis
			and b.id_mapping_kelas = c.id
			and b.id_smk = '".$smk."'
			and a.tahun_ajaran = '".$thn."' and b.status='1'");
		}else if(!empty($jur)&&(!empty($smk))&&(empty($kelas))){
			$q = $this->db->query("
			select 
				a.nis,
				b.nama,
				c.ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ref_siswa b, ref_mapping_kelas c
			where a.nis = b.nis
			and b.id_mapping_kelas = c.id
			and b.id_smk = '".$smk."'
			and b.id_jur = '".$jur."'
			and a.tahun_ajaran = '".$thn."' and b.status='1'");
		}else if((!empty($jur))&&(!empty($smk))&&(!empty($kelas))&&(empty($ruang))){
			
			$q = $this->db->query("
			select 
				a.nis,
				b.nama,
				c.ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ref_siswa b, ref_mapping_kelas c
			where a.nis = b.nis
			and b.id_mapping_kelas = c.id
			and b.id_smk = '".$smk."'
			and b.id_jur = '".$jur."'
			and b.kelas = '".$kelas."'
			and a.tahun_ajaran = '".$thn."' and b.status='1'");
		}else if((!empty($jur))&&(!empty($smk))&&(!empty($kelas))&&(!empty($ruang))){
			
			$q = $this->db->query("
			select 
				a.nis,
				b.nama,
				c.ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ref_siswa b, ref_mapping_kelas c
			where a.nis = b.nis
			and b.id_mapping_kelas = c.id
			and b.id_smk = '".$smk."'
			and b.id_jur = '".$jur."'
			and b.kelas = '".$kelas."'
			and c.ruang_kelas = '".$ruang."'
			and a.tahun_ajaran = '".$thn."' and b.status='1'");
		}
		
		return $q->result_array();
	}
	
	function get_data_ppdb($smk, $jur, $kelas, $ruang, $thn) {
		$tahun_ajaran = $this->get_tahun_ajaran();
		if(empty($smk)&&empty($jur)&&empty($kelas)&&empty($ruang)){
			$q = $this->db->query("
			select 
				'PPDB' as nis,
				b.nama,
				b.kelas as ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ppdb_siswa b
			where a.id_ppdb = b.id_daftar
			and a.tahun_ajaran = '".$thn."' and b.approval_status in ('0','1')");
		}else if(empty($jur)&&(!empty($smk))){
			$q = $this->db->query("
			select 
				'PPDB' as nis,
				b.nama,
				b.kelas as ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ppdb_siswa b
			where a.id_ppdb = b.id_daftar
			and b.id_smk = '".$smk."'
			and a.tahun_ajaran = '".$thn."' and b.approval_status in ('0','1')");
		}else if(!empty($jur)&&(!empty($smk))&&(empty($kelas))){
			$q = $this->db->query("
			select 
				'PPDB' as nis,
				b.nama,
				b.kelas as ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ppdb_siswa b
			where a.id_ppdb = b.id_daftar
			and b.id_smk = '".$smk."'
			and b.id_jur = '".$jur."'
			and a.tahun_ajaran = '".$thn."' and b.approval_status in ('0','1')");
		}else if((!empty($jur))&&(!empty($smk))&&(!empty($kelas))&&(empty($ruang))){
			
			$q = $this->db->query("
			select 
				'PPDB' as nis,
				b.nama,
				b.kelas as ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ppdb_siswa b
			where a.id_ppdb = b.id_daftar
			and b.id_smk = '".$smk."'
			and b.id_jur = '".$jur."'
			and b.kelas = '".$kelas."'
			and a.tahun_ajaran = '".$thn."' and b.approval_status in ('0','1')");
		}else if((!empty($jur))&&(!empty($smk))&&(!empty($kelas))&&(!empty($ruang))){
			$q = $this->db->query("
			select 
				'PPDB' as nis,
				b.nama,
				b.kelas as ruang_kelas,
				a.July,
				a.August, 
				a.September,
				a.October,
				a.November,
				a.December,
				a.January,
				a.February,
				a.March,
				a.April,
				a.May,
				a.June
			from rep_spp a, ppdb_siswa b
			where a.id_ppdb = b.id_daftar
			and b.id_smk = '0'
			and b.id_jur = '0'
			and b.kelas = '0'
			and a.tahun_ajaran = '".$thn."' and b.approval_status in ('0','1')");
		}
		
		return $q->result_array();
	}
	function cekKelas($id_smk){
		$return='';
		$this->db->select('*');
		$this->db->from("ref_kelas");
		$this->db->where("id_smk",$id_smk);
		$num = $this->db->get()->result_array();
		
		foreach ($num as $v_kls) {
			$return.='<option value="'.$v_kls['kelas'].'">Kelas '.$v_kls['kelas'].'</option>';
		}
								
		return $return;
	}
	
	function getRuang($kelas, $id_mapping_jur) {
		$return = '';
		$q = $this->db->query("select a.nama_karyawan, b.id, b.ruang_kelas from ref_karyawan a, ref_mapping_kelas b 
		where a.id = b.id_karyawan
		and b.kelas = '".$kelas."' and b.id_jur='".$id_mapping_jur."'");
		
		
		
		$data = $q->result_array();
		foreach ($data as $val) {
			$return .='<option value="'.$val['ruang_kelas'].'">'.$val['ruang_kelas'].'</option>';
		} 
		return $return;
	}
	
	function getJurusan($id_smk) {
		$return = '';
		$q = $this->db->query("select a.name, a.id as id_smk, b.id as id_jur, b.nama_jurusan from ref_smk a, ref_jur b where a.id = b.id_smk and a.id='".$id_smk."'");
		
		$data = $q->result_array();
		foreach ($data as $val) {
			$return .='<option value="'.$val['id_jur'].'">'.$val['nama_jurusan'].'</option>';
		} 
		return $return;
	}
	function get_ruang_kelas() {
		
		$this->db->select("id,ruang_kelas");
		$this->db->from("ref_mapping_kelas");
		$data = $this->db->get()->result_array();
		
		return $data;
	}
	function getDataSMK(){
		$this->db->select('*');
		$this->db->from("ref_smk");
		$num = $this->db->get()->result_array();
		return $num;
	}
	
	function getDataThn(){
		$this->db->distinct("tahun_ajaran");
		$this->db->select("tahun_ajaran");
		$this->db->from("ref_tahun_ajaran");
		$this->db->order_by("row_id","desc");
		$num = $this->db->get()->result_array();
		return $num;
	}

	function get_tahun_ajaran() {
		
		$this->db->select("tahun_ajaran");
		$this->db->from("ref_tahun_ajaran");
		$this->db->where("status","U");
		$tahun = $this->db->get()->row()->tahun_ajaran;
		
		return $tahun;
	}
	
}