<?php 
class Mapping_kelas_model extends CI_Model {
	
	function cekWali($id_kar) {
		
		$this->db->select("id");
		$this->db->from("ref_mapping_kelas");
		$this->db->where("id_karyawan",$id_kar);
		$num = $this->db->get()->num_rows();
		
		return $num;
	}
	
	function getKelas($id_smk){
		$this->db->select('*');
		$this->db->from("ref_kelas");
		$this->db->where("id_smk",$id_smk);
		$num = $this->db->get()->result_array();
		return $num;
	}
	
	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 getDataSMK(){
		$this->db->select('*');
		$this->db->from("ref_smk");
		$num = $this->db->get()->result_array();
		return $num;
	}
	
	function getDataJurusan(){
		$this->db->select('*');
		$this->db->from("ref_jur");
		$num = $this->db->get()->result_array();
		return $num;
	}
	function getJurEdit($id_smk){
		$this->db->select('*');
		$this->db->from('ref_jur');
		$this->db->where('id_smk',$id_smk);
		$data = $this->db->get()->result_array();
		return $data;
	}
	function cekWaliUpdate($id_kar,$id) {
		
		//get oldId
		$this->db->select("id_karyawan");
		$this->db->from("ref_mapping_kelas");
		$this->db->where("id",$id);
		$oldId = $this->db->get()->row()->id_karyawan;
		
		if ($oldId == $id_kar) {
			
			$return = 0;		
		
		} else {
			
			$this->db->select("id");
			$this->db->from("ref_mapping_kelas");
			$this->db->where("id_karyawan",$id_kar);
			$return = $this->db->get()->num_rows();
		
		}
		return $return;
	}
	
	function cekRuang($ruang, $jur, $kls) {
		$where = array('upper(ruang_kelas) ' => strtoupper($ruang) , 'upper(id_jur) ' => strtoupper($jur), 'upper(kelas) ' => strtoupper($kls));
		$this->db->select("id");
		$this->db->from("ref_mapping_kelas");
		$this->db->where($where);
		$num = $this->db->get()->num_rows();
		
		return $num;
		
	}
	
	function cekRuangUpdate($ruang, $jur, $kls, $id) {
		$where = array('upper(ruang_kelas) ' => strtoupper($ruang) , 'upper(id_jur) ' => strtoupper($jur), 'upper(kelas) ' => strtoupper($kls));
		//get ruang
		$this->db->select("ruang_kelas, id_jur, kelas");
		$this->db->from("ref_mapping_kelas");
		$this->db->where("id",$id);
		$oldrow = $this->db->get()->row();
		
		
		if ((strtoupper($oldrow->ruang_kelas) == strtoupper($ruang))&&(strtoupper($oldrow->id_jur) == strtoupper($jur))&&(strtoupper($oldrow->kelas) == strtoupper($kls))) {
			
			$return = 0;		
		
		} else {
			
			$this->db->select("id");
			$this->db->from("ref_mapping_kelas");
			$this->db->where($where);
			$return = $this->db->get()->num_rows();
		
		}
		return $return;
	}
		
	function get_wali_kelas() {
		
		$this->db->select('*');
		$this->db->from('ref_karyawan');
		$this->db->where('jabatan','GURU');
		$data = $this->db->get()->result_array();
		return $data;
	
	}
	
	function get_value_edit($id) {
		$this->db->select('*');
		$this->db->from('ref_mapping_kelas');
		$this->db->where('id',$id);
		$data = $this->db->get()->row();
		return $data;
	}
	
	function get_list_data() {
	
		$param = $this->input->post(array('page', 'rows', 'sort', 'order', 'filterRules'));
        $offset = intval(($param['page'] - 1) * $param['rows']);
		
		// clause filter, array not null
        $cond = '';
        if (count($param['filterRules']) > 0) {
            $filter = json_decode($param['filterRules']);
            $loop = 0;
            foreach ($filter as $json) {
                // convert to array
                $rule = get_object_vars($json);
                // declare variable
                if($rule['field']=='nama_smk'){
					$field = 'b.name';
				}else if($rule['field']=='nama_jur'){
					$field = 'c.nama_jurusan';
				}else{
					$field = $rule['field'];
				}
				
                $opt = $rule['op'];
                $value = $rule['value'];
                if ($loop == 0) {
                    // user where
                    if (!empty($value)) {
                        if ($opt == 'contains') {
                            $cond .= "where ($field like '%$value%')";
                        } else if ($opt == 'greater') {
                            $cond .= "where $field > '$value'";
                        } else if ($opt == 'less') {
                            $cond .= "where $field < '$value'";
                        } else if ($opt == 'notequal') {
                            $cond .= "where $field != '$value'";
                        } else if ($opt == 'equal') {
                            $cond .= "where $field = '$value'";
                        }
                        $loop++; // flag where
                    }
                } else {
                    // user and
                    if (!empty($value)) {
                        if ($opt == 'contains') {
                            $cond .= " and ($field like '%$value%')";
                        } else if ($opt == 'greater') {
                            $cond .= " and $field > '$value'";
                        } else if ($opt == 'less') {
                            $cond .= " and $field < '$value'";
                        } else if ($opt == 'notequal') {
                            $cond .= " and $field != '$value'";
                        } else if ($opt == 'equal') {
                            $cond .= " and $field = '$value'";
                        }
                    }
                }
            }
        }
		
		$response = array();
		$table = 'ref_mapping_kelas';
		$table2 = 'ref_smk';
		$table3 = 'ref_jur';
		$table4 = 'ref_karyawan';
		
        if (empty($param['sort'])) {
            if (empty($cond)) {
                $sql = "select a.id, b.name as nama_smk, c.nama_jurusan as nama_jur, a.kelas, a.ruang_kelas, d.nama_karyawan, a.created_by, a.created_date, a.modified_by, a.modified_date from $table a, $table2 b, $table3 c, $table4 d where a.id_smk = b.id and a.id_jur = c.id and c.id_smk = b.id and a.id_karyawan=d.id order by b.name, c.nama_jurusan, a.kelas, a.ruang_kelas limit ?, ?";
                $sqlcount = "select a.id, b.name as nama_smk, c.nama_jurusan as nama_jur, a.kelas, a.ruang_kelas, d.nama_karyawan, a.created_by, a.created_date, a.modified_by, a.modified_date from $table a, $table2 b, $table3 c, $table4 d where a.id_smk = b.id and a.id_jur = c.id and c.id_smk = b.id and a.id_karyawan=d.id";
            } else {
                $sql = "select a.id, b.name as nama_smk, c.nama_jurusan as nama_jur, a.kelas, a.ruang_kelas, d.nama_karyawan, a.created_by, a.created_date, a.modified_by, a.modified_date from $table a, $table2 b, $table3 c, $table4 d  $cond and a.id_smk = b.id and a.id_jur = c.id and c.id_smk = b.id and a.id_karyawan=d.id order by b.name, c.nama_jurusan, a.kelas, a.ruang_kelas limit ?, ?";
                $sqlcount = "select a.id, b.name as nama_smk, c.nama_jurusan as nama_jur, a.kelas, a.ruang_kelas, d.nama_karyawan, a.created_by, a.created_date, a.modified_by, a.modified_date from $table a, $table2 b, $table3 c, $table4 d  $cond and a.id_smk = b.id and a.id_jur = c.id and c.id_smk = b.id and a.id_karyawan=d.id";
            }
            $result_array = $this->db->query($sql, array($offset, intval($param['rows'])));
            $response['total'] = $this->db->query($sqlcount)->num_rows();
            $response['rows'] = $result_array->result();
        } else {
            $sort = $param['sort'];
            $order = $param['order'];
            if (empty($cond)) {
                $sql = "select a.id, b.name as nama_smk, c.nama_jurusan as nama_jur, a.kelas, a.ruang_kelas, d.nama_karyawan, a.created_by, a.created_date, a.modified_by, a.modified_date from $table a, $table2 b, $table3 c, $table4 d where a.id_smk = b.id and a.id_jur = c.id and c.id_smk = b.id and a.id_karyawan=d.id order by $sort $order, b.name, c.nama_jurusan, a.kelas, a.ruang_kelas limit ?, ?";
                $sqlcount = "select a.id, b.name as nama_smk, c.nama_jurusan as nama_jur, a.kelas, a.ruang_kelas, d.nama_karyawan, a.created_by, a.created_date, a.modified_by, a.modified_date from $table a, $table2 b, $table3 c, $table4 d where a.id_smk = b.id and a.id_jur = c.id and c.id_smk = b.id and a.id_karyawan=d.id";
            } else {
                $sql = "select a.id, b.name as nama_smk, c.nama_jurusan as nama_jur, a.kelas, a.ruang_kelas, d.nama_karyawan, a.created_by, a.created_date, a.modified_by, a.modified_date from $table a, $table2 b, $table3 c, $table4 d $cond and a.id_smk = b.id and a.id_jur = c.id and c.id_smk = b.id and a.id_karyawan=d.id order by $sort $order, b.name, c.nama_jurusan, a.kelas, a.ruang_kelas limit ?, ?";
                $sqlcount = "select a.id, b.name as nama_smk, c.nama_jurusan as nama_jur, a.kelas, a.ruang_kelas, d.nama_karyawan, a.created_by, a.created_date, a.modified_by, a.modified_date from $table a, $table2 b, $table3 c, $table4 d  $cond and a.id_smk = b.id and a.id_jur = c.id and c.id_smk = b.id and a.id_karyawan=d.id";
            }
            $result_array = $this->db->query($sql, array($offset, intval($param['rows'])));
            $response['total'] = $this->db->query($sqlcount)->num_rows();
            $response['rows'] = $result_array->result();
        }
        return $response;
	}
	
	function update_data() {
		
		$id = $this->input->post('id');	
		
		$data['id_jur']			= $this->input->post('id_mapping_jur');
		$data['id_smk']			= $this->input->post('smk');
		
		$data['kelas']			= $this->input->post('kelas');
		$data['ruang_kelas']	= $this->input->post('ruang_kelas');
		$data['id_karyawan']	= $this->input->post('id_karyawan');	
		$data['modified_date']	= date("Y-m-d H:i:s");
		$data['modified_by']	= $this->session->userdata('username');
		
		$this->db->where("id",$id);
		$q = $this->db->update("ref_mapping_kelas",$data);
		
		
		$data2['id_wali_kelas']	= $this->input->post('id_karyawan');
		$this->db->where("id_mapping_kelas",$id);
		$this->db->update("ref_siswa",$data2);
		
		return $q;	
		
	}
	
	function insert_data() {
		$data['id_jur']			= $this->input->post('id_mapping_jur');
		$data['id_smk']			= $this->input->post('smk');
		$data['kelas']			= $this->input->post('kelas');
		$data['ruang_kelas']	= $this->input->post('ruang_kelas');
		$data['id_karyawan']	= $this->input->post('id_karyawan');
		$data['created_date']	= date("Y-m-d H:i:s");
		$data['created_by']	= $this->session->userdata('username');
		$q = $this->db->insert("ref_mapping_kelas",$data);
		return $q;	
		
	}
	
	function delete_data($id){
		$this->db->where('id', $id);
		$return = $this->db->delete('ref_mapping_kelas');
		return $return;
	}

	function get_tahun_ajaran() {
	
		$this->db->select("tahun_ajaran");
		$this->db->from("ref_tahun_ajaran");
		$this->db->where("status","U");
		$return = $this->db->get()->row()->tahun_ajaran;
		
		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 getJurusanEdit($id,$idMappingClass) {
		$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 b.id='".$id."'");
		
		$data = $q->result_array();
		foreach ($data as $val) {
			if ($idMappingClass == $val['id_jur']) {
				$return .='<option value="'.$val['id_jur'].'" selected>'.$val['nama_jurusan'].'</option>';
			} else {
				$return .='<option value="'.$val['id_jur'].'">'.$val['nama_jurusan'].'</option>';
			}
			
		} 
		return $return;
	}
}