<?php 
class Model_jadwal extends CI_model{

//xxxxxxxxxxxxxxxxxxxxxxxxx
    function list_(){
        return $this->db->query("SELECT A.jadwal_id,
								A.dosen_id,
								A.reguler,
								A.jam_mulai,
								A.jam_selesai,
								A.tahun_akademik_id,
								B.dosen_id,
								B.nama_lengkap,
								C.mata_kuliah_id,
								C.kode_mata_kuliah,
								C.nama_mata_kuliah,
								C.sks,
								C.semester,
								D.ruangan_id,
								D.nama_ruangan,
								D.nama_gedung,
								D.kapasitas,
								E.hari_id,
								E.nama_hari,
								F.tahun_akademik
								FROM akademik_jadwal_kuliah A
								LEFT JOIN tb_dosen B ON B.dosen_id=A.dosen_id
								LEFT JOIN tb_mata_kuliah C ON C.mata_kuliah_id=A.mata_kuliah_id
								LEFT JOIN tb_ruangan D ON D.ruangan_id=A.ruangan_id
								LEFT JOIN tb_hari E ON E.hari_id=A.hari_id 
								LEFT JOIN tb_tahun_akademik F ON F.tahun_akademik_id=A.tahun_akademik_id");
    }
	function cek_data($mata_kuliah_id,$tahun_akademik_id,$reguler){
        return $this->db->query("SELECT * from akademik_jadwal_kuliah WHERE mata_kuliah_id = '$mata_kuliah_id' AND tahun_akademik_id = '$tahun_akademik_id' AND reguler = '$reguler'");
    }
	function cek_dataxx($mata_kuliah_id,$tahun_akademik_id,$reguler){
		try {
			$this->db->where('mata_kuliah_id',$mata_kuliah_id);
			$this->db->where('tahun_akademik_id',$tahun_akademik_id);
			$this->db->where('reguler',$reguler);
			return $this->db->get('akademik_jadwal_kuliah');
		} catch (Exception $e) {
			return false;
		}
	}
	function cek_data_mk($mata_kuliah_id){
		try {
			$this->db->where('mata_kuliah_id',$mata_kuliah_id);
				return $this->db->get('tb_mata_kuliah');
		} catch (Exception $e) {
			return false;
		}
	}
	function cek_tahun_akademik_id($tahun_akademik_id){
		try {
			$this->db->where('tahun_akademik_id',$tahun_akademik_id);
				return $this->db->get('tb_tahun_akademik');
		} catch (Exception $e) {
			return false;
		}
	}
	function list_ta(){
        return $this->db->query("SELECT * from tb_tahun_akademik ORDER BY tahun_akademik DESC");
    }
	function list_semester(){
        return $this->db->query("SELECT * from tb_semester ORDER BY id_semester ASC");
    }
	function list_detail_jadwal($tahun_akademik,$semester,$reguler){
		return $this->db->query("SELECT A.dosen_id,
									A.jam_mulai,
									A.jam_selesai,
									A.reguler,
									B.dosen_id,
									B.nama_lengkap,
									C.mata_kuliah_id,
									C.kode_mata_kuliah,
									C.nama_mata_kuliah,
									C.sks,
									A.semester,
									C.semester,
									D.ruangan_id,
									D.nama_ruangan,
									D.nama_gedung,
									D.kapasitas,
									E.hari_id,
									E.nama_hari,
									F.tahun_akademik
									FROM akademik_jadwal_kuliah A
									LEFT JOIN tb_dosen B ON B.dosen_id=A.dosen_id
									LEFT JOIN tb_mata_kuliah C ON C.mata_kuliah_id=A.mata_kuliah_id
									LEFT JOIN tb_ruangan D ON D.ruangan_id=A.ruangan_id
									LEFT JOIN tb_hari E ON E.hari_id=A.hari_id 
									LEFT JOIN tb_tahun_akademik F ON F.tahun_akademik_id=A.tahun_akademik_id 
								WHERE A.tahun_akademik_id = '$tahun_akademik' AND C.semester = '$semester' AND A.reguler = '$reguler'");
    }
	function list_detail_jadwal_row($tahun_akademik,$semester,$reguler){
		return $this->db->query("SELECT A.dosen_id,
									A.jam_mulai,
									A.jam_selesai,
									A.reguler,
									B.dosen_id,
									B.nama_lengkap,
									C.mata_kuliah_id,
									C.kode_mata_kuliah,
									C.nama_mata_kuliah,
									C.sks,
									A.semester,
									C.semester,
									D.ruangan_id,
									D.nama_ruangan,
									D.nama_gedung,
									D.kapasitas,
									E.hari_id,
									E.nama_hari,
									F.tahun_akademik
									FROM akademik_jadwal_kuliah A
									LEFT JOIN tb_dosen B ON B.dosen_id=A.dosen_id
									LEFT JOIN tb_mata_kuliah C ON C.mata_kuliah_id=A.mata_kuliah_id
									LEFT JOIN tb_ruangan D ON D.ruangan_id=A.ruangan_id
									LEFT JOIN tb_hari E ON E.hari_id=A.hari_id 
									LEFT JOIN tb_tahun_akademik F ON F.tahun_akademik_id=A.tahun_akademik_id 
								WHERE A.tahun_akademik_id = '$tahun_akademik' AND C.semester = '$semester' AND A.reguler = '$reguler'
								LIMIT 1");
    }
	function list_hari(){
        return $this->db->query("SELECT * from tb_hari ORDER BY hari_id ASC");
    }
	function list_dosen(){
        return $this->db->query("SELECT * from tb_dosen ORDER BY nama_lengkap ASC");
    }
	function list_hari_id($id){
        return $this->db->query("SELECT * from tb_hari WHERE hari_id = '$id' ORDER BY hari_id ASC");
    }
	function list_dosen_id($id){
        return $this->db->query("SELECT * from tb_dosen WHERE dosen_id = '$id' ORDER BY nama_lengkap ASC");
    }
	///pencarian list_filter2a
	function list_filter1($ta){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE tahun_akademik = '$ta'");
    }
	function list_filter2($ta,$semester){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE tahun_akademik = '$ta' AND semester = '$semester'");
    }
	function list_filter3($ta,$semester,$hari){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE tahun_akademik = '$ta' AND semester = '$semester' AND hari_id = '$hari'");
    }
	
	function list_filter4($ta,$semester,$hari,$dosen){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE tahun_akademik = '$ta' AND semester = '$semester' AND hari_id = '$hari' AND dosen_id = '$dosen'");
    }
	//nnn list_filter2ab
	function list_filter1a($semester){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE semester = '$semester'");
    }
	function list_filter2ab($semester,$hari){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE semester = '$semester' AND hari_id = '$hari'");
    }
	function list_filter3a($semester,$hari,$dosen){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE semester = '$semester' AND hari_id = '$hari' AND dosen_id = '$dosen'");
    }
	function list_filter2a($hari){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE hari_id = 1");
    }
	function list_filter2b($hari,$dosen){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE hari_id = '$hari' AND dosen_id = '$dosen'");
    }
	function list_filter3b($dosen){
		return $this->db->query("SELECT * from v_jadwal_kuliah WHERE dosen_id = '$dosen'");
    }
	///batasss pencarian
	function list_semester_ganjil(){
		$year = date('Y');$yearnext = date('Y')+1;
		$TA = $year.'-'.$yearnext ;
        return $this->db->query("SELECT * from v_jadwal_kuliah WHERE tahun_akademik = '$TA' AND semester_akademik=1");
    }
	function list_semester_genap(){
		$year = date('Y');$yearnext = date('Y')+1;
		$TA = $year.'-'.$yearnext ;
        return $this->db->query("SELECT * from v_jadwal_kuliah WHERE tahun_akademik = '$TA' AND semester_akademik=2");
    }
	function list_tahunsekarang(){
		$year = date('Y');$yearnext = date('Y')+1;
		$TA = $year.'-'.$yearnext ;
        return $this->db->query("SELECT * from v_jadwal_kuliah WHERE tahun_akademik = '$TA'");
    }
    function tambah_baru(){
      
		$datadb = array('dosen_id'=>$this->db->escape_str($this->input->post('dosen_id')),
						'mata_kuliah_id'=>$this->db->escape_str($this->input->post('mata_kuliah_id')),
						'hari_id'=>$this->input->post('hari'),
						'ruangan_id'=>$this->db->escape_str($this->input->post('kelas')),
						'jam_mulai'=>$this->input->post('jam_mulai'),
						'jam_selesai'=>$this->input->post('jam_selesai'),
						'reguler'=>$this->input->post('reguler'),
						'tahun_akademik_id'=>$this->input->post('tahun_akademik_id'),
						'tanggal_input'=>date('Y-m-d H:i:s'),
						'username_input'=>$this->session->username);
		$this->db->insert('akademik_jadwal_kuliah',$datadb);
    }
	function list_edit($id){
        return $this->db->query("SELECT * from tb_ruangan WHERE ruangan_id='$id'");
    }
	 function hapus_data($id){
        return $this->db->query("DELETE FROM akademik_jadwal_kuliah where jadwal_id='$id'");
		
    }
	function list_detail($id){
        return $this->db->query("SELECT A.*,B.keterangan AS agama FROM tb_mahasiswa A LEFT JOIN tb_agama B ON A.agama_id=B.agama_id
								where A.mahasiswa_id='$id'")->result_array();
    }

}