<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Model_jadwal extends CI_Model {

	protected $table_name = 'jadwal';

	public function __construct(){
		parent::__construct();
	}

	private function query(){
        $query = "SELECT * FROM
        (
        	SELECT `nama_jurusan`, nama_singkat_jurusan, `jenjang`, CONCAT(tahun_mulai, '/', `tahun_selesai`) AS ta, DK.*
			FROM `detail_kelas` AS DK
			LEFT JOIN
			`ref_jenjang` AS RJ 
			ON RJ.`id_ref_jenjang` = DK.`id_ref_jenjang`
			LEFT JOIN
			`ref_tahun_ajaran` AS RTA
			ON `RTA`.`id_ref_tahun_ajaran` = DK.`id_ref_tahun_ajaran`
			LEFT JOIN
			`ref_jurusan` AS RJU
			ON `RJU`.`id_ref_jurusan` = DK.`id_ref_jurusan`  
		) AS temp1 ";
        return $query;
    }

    public function getListData($options = []){
        $where_like = empty($options['where_like']) ? '1 = 1' : '('.implode(' OR ', $options['where_like']).')'; 
        $sql = $this->query()."         
            WHERE 
                1 = 1 AND ".$where_like."
            ORDER BY ".$options['order']." ".$options['mode']."
            LIMIT ".$options['offset'].", ".$options['limit'];

        $query = $this->db->query($sql);
        return $query->result();
    }

    public function getTotalData($options){
        $where_like = empty($options['where_like']) ? '1 = 1' : '('.implode(' OR ', $options['where_like']).')'; 
        $sql = $this->query()."         
            WHERE 
                1 = 1 AND ".$where_like;
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

    public function getTotal(){
        $sql = $this->query();
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

	public function getData()
	{
		$result = $this->db->get($this->table_name)->result();
		return $result;
	}

	public function getTahunAjaran()
	{
		$result = $this->db->select("CONCAT(tahun_mulai, '/', tahun_selesai) AS ta, ref_tahun_ajaran.*")->from('ref_tahun_ajaran')->get()->result_array();
		return $result;
	}

	public function getAlokasiJam($id_ref_tahun_ajaran)
	{
		$query = "SELECT 
					hari,
					COUNT(id_alokasi_jam) AS jumlah_data,
					CONCAT(
						'[', 
						GROUP_CONCAT(
						DISTINCT CONCAT('{#kode_jam#:#', kode_jam, '#, #id#:#', `id_alokasi_jam`,'#}') 
						ORDER BY kode_jam * 1
						SEPARATOR ','
						), 
						']'
					) AS jam FROM alokasi_jam
					WHERE id_ref_tahun_ajaran = '".$id_ref_tahun_ajaran."' AND is_istirahat = '0'
					GROUP BY hari
					ORDER BY FIELD(hari, 'senin', 'selasa', 'rabu', 'kamis', 'jumat', 'sabtu', 'minggu'), kode_jam * 1 ASC";
		$result = $this->db->query($query)->result_array();
		return $result;
	}

	public function getDetailKelas($id_ref_tahun_ajaran)
	{
		$query = "SELECT `nama_jurusan`, DK.id_ref_jurusan, DK.id_ref_tahun_ajaran,
						(
							SELECT COUNT(id_detail_kelas) FROM detail_kelas WHERE `id_ref_jurusan`= DK.id_ref_jurusan AND `id_ref_tahun_ajaran` = '$id_ref_tahun_ajaran'
						) AS jumlah_data,
						CONCAT('[',
							GROUP_CONCAT(
								DISTINCT CONCAT('{#id_jenjang#:#', DK.id_ref_jenjang, '#, #jenjang#:#', (SELECT jenjang FROM ref_jenjang WHERE `id_ref_jenjang`= DK.id_ref_jenjang LIMIT 1),'#, #jumlah_data#:#', (SELECT COUNT(id_detail_kelas) FROM detail_kelas WHERE id_ref_jenjang = DK.id_ref_jenjang AND id_ref_jurusan = DK.id_ref_jurusan AND id_ref_tahun_ajaran = '$id_ref_tahun_ajaran'), '#}')
								ORDER BY DK.id_ref_jenjang ASC 
								SEPARATOR ','
							),
							']'
						) AS jenjang,
						CONCAT('[',
							GROUP_CONCAT(
								DISTINCT CONCAT(
									'{#id#:#', `id_detail_kelas`, '#}')
								ORDER BY jenjang, nama_kelas ASC
								SEPARATOR ','
							),
							']'
						) AS id
					FROM detail_kelas AS DK
					LEFT JOIN ref_jenjang AS RJ ON DK.`id_ref_jenjang` = DK.`id_ref_jenjang`
					LEFT JOIN ref_tahun_ajaran AS RTA ON RTA.`id_ref_tahun_ajaran` = DK.`id_ref_tahun_ajaran`
					LEFT JOIN `ref_jurusan` AS RJU ON RJU.`id_ref_jurusan` = DK.`id_ref_jurusan`
					WHERE DK.id_ref_tahun_ajaran = '".$id_ref_tahun_ajaran."'
					GROUP BY DK.`id_ref_jurusan`";
		$result = $this->db->query($query)->result_array();
		return $result;
	}

	public function getDetailKelasById($id_detail_kelas)
	{
		$query = "SELECT `nama_jurusan`, jenjang, nama_singkat_jurusan, nama_kelas
				FROM detail_kelas AS DK
				LEFT JOIN ref_jenjang AS RJ ON DK.`id_ref_jenjang` = DK.`id_ref_jenjang`
				LEFT JOIN ref_tahun_ajaran AS RTA ON RTA.`id_ref_tahun_ajaran` = DK.`id_ref_tahun_ajaran`
				LEFT JOIN `ref_jurusan` AS RJU ON RJU.`id_ref_jurusan` = DK.`id_ref_jurusan`
				WHERE DK.id_detail_kelas = '".$id_detail_kelas."'";
		$result = $this->db->query($query)->row_array();
		return $result;
	}

	public function getJenjangById($id_ref_jurusan, $id_ref_jenjang, $id_ref_tahun_ajaran)
	{
		$query = "SELECT DISTINCT id_detail_kelas
				FROM detail_kelas AS DK
				LEFT JOIN ref_jenjang AS RJ ON DK.`id_ref_jenjang` = DK.`id_ref_jenjang`
				LEFT JOIN ref_tahun_ajaran AS RTA ON RTA.`id_ref_tahun_ajaran` = DK.`id_ref_tahun_ajaran`
				LEFT JOIN `ref_jurusan` AS RJU ON RJU.`id_ref_jurusan` = DK.`id_ref_jurusan`
				WHERE DK.id_ref_jenjang = '".$id_ref_jenjang."'
				AND DK.id_ref_tahun_ajaran = '".$id_ref_tahun_ajaran."'
				AND DK.id_ref_jurusan = '".$id_ref_jurusan."'";
		$result = $this->db->query($query)->result_array();
		return $result;
	}

	public function getGuruMapel($wheres = [])
	{
		$result = $this->db->select('*')->from('guru_mapel AS GM')
			->join('ref_tahun_ajaran AS RTA', 'GM.id_ref_tahun_ajaran = RTA.id_ref_tahun_ajaran', 'left')
			->join('ref_mapel AS RM', 'RM.id_ref_mapel = GM.id_ref_mapel', 'left')
			->join('guru AS G', 'G.id_guru = GM.id_guru', 'left')
			->where($wheres)
			->get()->result_array();
		return $result;
	}

	public function getGuruMapelDistinct($wheres = [])
	{
		$result = $this->db->select('*')->from('guru_mapel AS GM')
			->join('ref_tahun_ajaran AS RTA', 'GM.id_ref_tahun_ajaran = RTA.id_ref_tahun_ajaran', 'left')
			->join('ref_mapel AS RM', 'RM.id_ref_mapel = GM.id_ref_mapel', 'left')
			->join('guru AS G', 'G.id_guru = GM.id_guru', 'left')
			->where($wheres)
			->group_by('G.id_guru')
			->get()->result_array();
		return $result;
	}

	public function getRuang()
	{
		$result = $this->db->select('*')->from('ref_ruang')
			->order_by('nama_ruang', 'asc')
			->get()->result_array();
		return $result;
	}

	public function getDataById($id)
	{
		$result = $this->db->select('*')->from('jadwal AS J')
			->join('alokasi_jam AS AJ', 'AJ.id_alokasi_jam = J.id_alokasi_jam', 'left')
			->join('detail_kelas AS DK', 'DK.id_detail_kelas = J.id_detail_kelas', 'left')
			->join('guru_mapel AS GM', 'GM.id_guru_mapel = J.id_guru_mapel')
			->where(['id_jadwal' => $id])->get()->row_array();
		return $result;
	}

	public function getDataByParams($wheres = false)
	{
		$result = $this->db->select('*')->from('jadwal AS J')
			->join('alokasi_jam AS AJ', 'AJ.id_alokasi_jam = J.id_alokasi_jam', 'left')
			->join('detail_kelas AS DK', 'DK.id_detail_kelas = J.id_detail_kelas', 'left')
			->join('guru_mapel AS GM', 'GM.id_guru_mapel = J.id_guru_mapel', 'left')
			->join('ref_mapel AS RM', 'GM.id_ref_mapel = RM.id_ref_mapel', 'left')
			->join('guru AS G', 'GM.id_guru = G.id_guru', 'left')
			->join('ref_ruang AS R', 'R.id_ref_ruang = J.id_ref_ruang', 'left')
			->where($wheres)->get()->result_array();
		return $result;
	}

	public function getDataRuangByParams($wheres = false)
	{
		$result = $this->db->select('*')->from('jadwal AS J')
			->join('alokasi_jam AS AJ', 'AJ.id_alokasi_jam = J.id_alokasi_jam', 'left')
			->join('detail_kelas AS DK', 'DK.id_detail_kelas = J.id_detail_kelas', 'left')
			->join('guru_mapel AS GM', 'GM.id_guru_mapel = J.id_guru_mapel', 'left')
			->join('ref_mapel AS RM', 'GM.id_ref_mapel = RM.id_ref_mapel', 'left')
			->join('guru AS G', 'GM.id_guru = G.id_guru', 'left')
			->join('ref_ruang AS R', 'R.id_ref_ruang = J.id_ref_ruang', 'left')
			->where($wheres)->get()->result_array();
		return $result;
	}

	public function add($data)
	{
		$add = $this->db->insert($this->table_name, $data);
		return $add;
	}

	public function update($data, $wheres)
	{
		$update = $this->db->update($this->table_name, $data, $wheres);
		return $update;
	}

	public function delete($id_alokasi_jam, $id_detail_kelas){
		$delete = $this->db->where(array('id_detail_kelas' => $id_detail_kelas, 'id_alokasi_jam' => $id_alokasi_jam))->delete($this->table_name);
		return $delete;
	}

	public function delete_ruang($id_alokasi_jam, $id_detail_kelas){
		$delete = $this->db->update($this->table_name, array('id_ref_ruang' => NULL), array('id_detail_kelas' => $id_detail_kelas, 'id_alokasi_jam' => $id_alokasi_jam));
		return $delete;
	}

	public function get_last_updated(){
		$q = $this->db->select('last_updated')->from('jadwal')->where("last_updated = (SELECT MAX(last_updated) FROM jadwal LIMIT 1)")->get()->row();
		$data = empty($q) ? date('Y-m-d H:i:s') : $q->last_updated;
		return $data;
	}
}