<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Jadwal_Controller extends Controller {
	var $stat;
	
	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
    }
	
	function jsonFromQuery($q) {
		$this->rhlib->jsonFromQuery($q);
	}
	function jsonFromQueryWLimit($q){
		$this->rhlib->jsonFromQueryWLimit($q);
	}	
	function insertRecord($table,$dataArray){
		return $this->rhlib->insertRecord($table,$dataArray);
	}	
	function deleteRecord($table,$where){
		return $this->rhlib->deleteRecord($table,$where);
	}
	
	function retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function retValOrZero($val){
		$val = ($val=='' || $val==null)? 0 : $val;
		return $val;
	}
	
	function autoNumber($column,$tbl){
        $q = "SELECT max(".$column.")+1 as max FROM ".$tbl."" ;
        $query  = $this->db->query($q);
        $max = ''; 
                    
        if ($query->num_rows() != 0)
        {
            $row = $query->row();
            $max=$row->max;
        }
        if ($max == null){
            $max=1;
        }
        return $max;
    }  
	
	function get_jamkul(){
		$idjk              = $this->input->post("idjk");
		
		$q = $this->db->query("SELECT `jam`.`idjam` AS `idjam`
		 , `jam`.`kdjam` AS `kdjam`
		 , `jam`.`darijam` AS `darijam`
		 , `jam`.`sampaijam` AS `sampaijam`
		 , if(((SELECT `jadwalkuliahdet`.`idjam` AS `idjam`
				FROM
				  `jadwalkuliahdet`
				WHERE
				  ((`jadwalkuliahdet`.`idjk` = '".$idjk."')
				  AND (`jadwalkuliahdet`.`idjam` = `jam`.`idjam`))) = `jam`.`idjam`), 1, 0) AS `pilih`
			FROM
			  `jam`");
				
		$data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $datax = $this->db->count_all('jam');
        $ttl = $datax;
        
        //======================================================================
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

		foreach($data as $row) {
            array_push($build_array["data"],array(
               
                'idjam'=>$row->idjam,
				'kdjam'=>$row->kdjam,
				'darijam'=>$row->darijam,
				'sampaijam'=>$row->sampaijam,
				'pilih'=>$row->pilih,
				
               
                            ));
        }
        echo json_encode($build_array);
	}
	
	function get_jamuj(){
		$idju              = $this->input->post("idju");
		
		$q = $this->db->query("SELECT `jam`.`idjam` AS `idjam`
		 , `jam`.`kdjam` AS `kdjam`
		 , `jam`.`darijam` AS `darijam`
		 , `jam`.`sampaijam` AS `sampaijam`
		 , if(((SELECT `jadwalujiandet`.`idjam` AS `idjam`
				FROM
				  `jadwalujiandet`
				WHERE
				  ((`jadwalujiandet`.`idju` = '".$idju."')
				  AND (`jadwalujiandet`.`idjam` = `jam`.`idjam`))) = `jam`.`idjam`), 1, 0) AS `pilih`
			FROM
			  `jam`");
				
		$data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $datax = $this->db->count_all('jam');
        $ttl = $datax;
        
        //======================================================================
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

		foreach($data as $row) {
            array_push($build_array["data"],array(
               
                'idjam'=>$row->idjam,
				'kdjam'=>$row->kdjam,
				'darijam'=>$row->darijam,
				'sampaijam'=>$row->sampaijam,
				'pilih'=>$row->pilih,
				
               
                            ));
        }
        echo json_encode($build_array);
	}
	
	function get_jmlmhs(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idklsmhs = $_POST['idklsmhs'];
		$klsparalel = $_POST['klsparalel'];
		
		$q = "SELECT COUNT(DISTINCT n.nim) as jmlmhs FROM nilai n
			LEFT JOIN mahasiswa m ON m.nim=n.nim
			WHERE n.kdprodi='".$kdprodi."' 
			and n.kdstsemester='".$kdstsemester."'
			and n.kdmk='".$kdmk."'
			and n.nidu='".$nidu."'
			and n.klsparalel ='".$klsparalel."'
			and m.idklsmhs='".$idklsmhs."'";
			
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			$jmlmhs = $query->row()->jmlmhs;
			echo json_encode(array ("success"=>true,"jmlmhs"=>$jmlmhs));
		}
		else
			echo json_encode(array ("success"=>false,"jmlmhs"=>0));		
	}
	
	function cek_harijadwalkul(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idkelas = $_POST['idkelas'];
		$idhari = $_POST['idhari'];

		$q = "SELECT count(idhari) as jumhari
			FROM jadwalkuliah 
			WHERE kdprodi='".$kdprodi."' 
			AND kdstsemester='".$kdstsemester."'
			AND kdmk='".$kdmk."'
			AND idkelas='".$idkelas."'
			AND nidu='".$nidu."'
			AND idhari='".$idhari."'";
			
		$query  = $this->db->query($q);
        $jum = ''; 
                    
        if ($query->num_rows() != 0)
        {
            $row = $query->row();
            $jum=$row->jumhari;
        }
        if ($jum == null){
            $jum=0;
        }
        echo $jum;
	}
	
	function cek_tgljadwaluj(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idjnsujian = $_POST['idjnsujian'];
		$susulan = $_POST['susulan'];
		$tglujian = $_POST['tglujian'];
		$idkelas = $_POST['idkelas'];
		
		$arr_tgl    = explode('-',substr($tglujian,0,10));
		$displaytgl  = $arr_tgl[2]."/".$arr_tgl[1]."/".$arr_tgl[0];

		$q = "SELECT count(tglujian) as jumtgl
			FROM jadwalujian 
			WHERE kdprodi='".$kdprodi."' 
			AND kdstsemester='".$kdstsemester."'
			AND kdmk='".$kdmk."'
			AND idjnsujian='".$idjnsujian."'
			AND susulan='".$susulan."'
			AND nidu='".$nidu."'
			AND tglujian='".$tglujian."'
			AND idkelas='".$idkelas."'";
			
		$query  = $this->db->query($q);
        $jum = ''; 
                    
        if ($query->num_rows() != 0)
        {
            $row = $query->row();
            $jum=$row->jumtgl;
        }
        if ($jum == null){
            $jum=0;
        }
		
		$return['jum']= $jum;
		$return['disptgl']= $displaytgl;
		
        echo json_encode($return);
	}
	
	function get_jadwalkuliah(){
		$kdprodi 		= $_POST['kdprodi'];
		$kdstsemester 	= $_POST['kdstsemester'];
		$kdmk 			= $_POST['kdmk'];
		$nidu 			= $_POST['nidu'];
		$idkelas 		= $_POST['idkelas'];
		$jmlmhs 		= $_POST['jmlmhs'];

		/*
		$q = "SELECT A.*, (kapasitas - jmlmhs) as selisih
			FROM(
				SELECT a.idjk,a.kdprodi,a.kdstsemester, 
				a.kdmk, a.nidu,  a.idhari, b.nmhari,
				a.darijam,a.sampaijam,
				CAST(".$jmlmhs." AS UNSIGNED) as jmlmhs, 
				a.idruangan, c.kdruangan, c.nmruangan,
				a.klsparalel,
				IFNULL(c.kapasitas,0) as kapasitas
				FROM jadwalkuliah a
				LEFT JOIN hari b ON b.idhari=a.idhari
				LEFT JOIN ruangan c ON c.idruangan=a.idruangan
				WHERE a.kdprodi='".$kdprodi."'
				and a.kdstsemester='".$kdstsemester."'
				and a.kdmk='".$kdmk."'
				and a.nidu='".$nidu."'
			)A";
		*/
		$q = "SELECT v_jadwalkuliah.*, CAST('$jmlmhs' AS SIGNED) as jmlmhs, kapasitas - CAST('$jmlmhs' AS SIGNED) AS selisih
			  FROM v_jadwalkuliah 
			  WHERE kdprodi='$kdprodi'
			  AND kdstsemester='$kdstsemester'
			  AND kdmk='$kdmk'
			  AND idkelas='$idkelas'
			  AND nidu='$nidu'";
		
$a = "SELECT h.nmhari AS nmhari
     , r.nmruangan AS nmruangan
     , jkd.darijam AS mulai
     , jkd.sampaijam AS selesai
     , cast('$jmlmhs' AS SIGNED) AS jmlmhs
     , r.kapasitas - cast('$jmlmhs' AS SIGNED) AS selisih
	 , jk.idjk
	 , jk.idkelas
	 , jk.idruangan
	 , jk.idhari
	 , jk.idtipekelas
FROM
  jadwalkuliah as jk
  join ruangan as r
  on r.idruangan = jk.idruangan
  join hari as h
  on h.idhari = jk.idhari
  join jadwalkuliahdet as jkd
  on jkd.idjk = jk.idjk
  join tipekelas as tp
  on tp.idtipekelas = jk.idtipekelas
WHERE
  jk.kdprodi = '$kdprodi'
  AND jk.kdstsemester = '$kdstsemester'
  AND jk.idkelas='$idkelas'
  AND jk.nidu = '$nidu'
  AND jk.kdmk = '$kdmk'";
  
		$this->jsonFromQuery($a);	
	}
	
	function get_jadwalujian(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idkelas = $_POST['idkelas'];
		$jmlmhs = $this->retValOrZero($_POST['jmlmhs']);
		
		/* $q = "SELECT A.*, (kapasitas - jmlmhs) as selisih
			FROM(
				SELECT a.idju,a.kdprodi,a.kdstsemester, 
				a.kdmk, a.nidu,  a.idhari, b.nmhari,
				a.tglujian,a.darijam,a.sampaijam,
				a.idjnsujian, c.kdjnsujian, c.nmjnsujian,
				CAST(".$jmlmhs." AS UNSIGNED) as jmlmhs, 
				a.idruangan, d.kdruangan, d.nmruangan,
				a.klsparalel,
				IFNULL(d.kapasitas,0) as kapasitas
				FROM jadwalujian a
				LEFT JOIN hari b ON b.idhari=a.idhari
				LEFT JOIN jujian c ON c.idjnsujian=a.idjnsujian
				LEFT JOIN ruangan d ON d.idruangan=a.idruangan
				WHERE a.kdprodi='".$kdprodi."'
				and a.kdstsemester='".$kdstsemester."'
				and a.kdmk='".$kdmk."'
				and a.nidu='".$nidu."'
			)A"; */
			
		$q = "SELECT v_jadwalujian.*,
				CAST(".$jmlmhs." AS SIGNED) as jmlmhs, 
				kapasitas - CAST(".$jmlmhs." AS SIGNED) AS selisih FROM v_jadwalujian
				WHERE kdprodi='".$kdprodi."'
				and kdstsemester='".$kdstsemester."'
				and kdmk='".$kdmk."'
				and idkelas='".$idkelas."'";
				//and nidu='".$nidu."'";
				
		$a = "SELECT ju.idju, jj.nmjnsujian
     , ju.tglujian
     , jud.darijam AS mulai
     , jud.sampaijam AS selesai
     , cast('$jmlmhs' AS SIGNED) AS jmlmhs
     , r.nmruangan
     , r.kapasitas
     , r.kapasitas - cast('$jmlmhs' AS SIGNED) AS selisih
     , ju.susulan AS susulan
     , if((ju.susulan = '1'), 'Ya', 'Tidak') AS ketsusulan
	 , ju.idjnsujian
	 , ju.idruangan
FROM
  jadwalujian AS ju
JOIN jujian AS jj
ON jj.idjnsujian = ju.idjnsujian
JOIN jadwalujiandet AS jud
ON jud.idju = ju.idju
JOIN ruangan AS r
ON r.idruangan = ju.idruangan
WHERE
  ju.kdprodi = '$kdprodi'
  AND ju.kdstsemester = '$kdstsemester'
  AND ju.nidu = '$nidu'
  AND ju.kdmk = '$kdmk'
  AND ju.idkelas = '$idkelas'";
  
		$this->jsonFromQuery($a);	
	}
	
	function insert_jadwalkuliah(){
		$dataArray = array(
			'idjk'=> $this->autoNumber('idjk', 'jadwalkuliah'),
            'idkelas'=> $_POST['idkelas'],
            'idtipekelas'=> $this->retValOrNull($_POST['idtipekelas']),
			'kdstsemester'=> $_POST['kdstsemester'],
			'kdprodi'=> $_POST['kdprodi'], 
			'mat_kdprodi'=> $_POST['mat_kdprodi'],
			'kdmk'=> $_POST['kdmk'],
			'nidu'=> $_POST['nidu'], 
			'idhari'=> $this->retValOrNull($_POST['idhari']),
			'idruangan'=> $this->retValOrNull($_POST['idruangan']),
        );
		$arrwaktu=$this->input->post("waktu");
		
		$this->db->insert('jadwalkuliah', $dataArray);
        if($this->db->affected_rows()){
			$val = $this->insert_jadwalkuliahdet($dataArray['idjk']);
			if ($val) {
				$return["message"]="Simpan Data Berhasil";
			} else {
				$return["message"]="Simpan Data Gagal";
			}
        }else{
			$return["message"]="Simpan Data Gagal";
        }

	}
	
	function update_jadwalkuliah(){ 	
		$dataArray = array(
            'idtipekelas'=> $this->retValOrNull($_POST['idtipekelas']),
			'idhari'=> $this->retValOrNull($_POST['idhari']),
			'idruangan'=> $this->retValOrNull($_POST['idruangan']),
        );

		$where['idjk'] = $_POST['idjk'];	
		$this->db->where($where);
		$this->db->update('jadwalkuliah', $dataArray); 
		
		$val = $this->insert_jadwalkuliahdet($_POST['idjk']);
		if ($val) {
			$ret["success"]=true;
			$return["message"]="Update Data Berhasil";
		} else {
			$ret["success"]=false;
			$return["message"]="Update Data Gagal";
		}
        return $ret;
    }
	
	function insert_jadwalkuliahdet($idjk){      // ISTRA
        $arr=$this->input->post("waktu");
       
        $this->db->trans_begin();
            $rows = explode(";",$arr);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
                $this->db->query("CALL SP_simpan_waktu_kul (?,?,?,?,?)",array($rows2[0],$idjk,$rows2[1],$rows2[2],$rows2[3])); 
            }

        
         if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            $return=false;
        }
        else
        {
            $this->db->trans_commit();
            $return=true;
        }
        return $return;
     }
	
	function insert_jadwalujian(){
		$dataArray = array(
			'idju'=> $this->autoNumber('idju', 'jadwalujian'),
            'kdstsemester'=> $_POST['kdstsemester'],
            'kdprodi'=> $_POST['kdprodi'],
			'mat_kdprodi'=> $_POST['mat_kdprodi'],
			'kdmk'=> $_POST['kdmk'],
			'nidu'=> $_POST['nidu'], 
			'tglujian'=> $_POST['tglujian'], 
			'idjnsujian'=> $_POST['idjnsujian'],
			'susulan'=> $_POST['susulan'],
			'idruangan'=> $_POST['idruangan'],
			'idkelas'=> $_POST['idkelas'],
        );
		$arrwaktu=$this->input->post("waktu");
		
		$this->db->insert('jadwalujian', $dataArray);
        if($this->db->affected_rows()){
			$val = $this->insert_jadwalujiandet($dataArray['idju']);
			if ($val) {
				$return["message"]="Simpan Data Berhasil";
			} else {
				$return["message"]="Simpan Data Gagal";
			}
        }else{
			$return["message"]="Simpan Data Gagal";
        }
	}
	
	function update_jadwalujian(){ 	
		$dataArray = array(
            'tglujian'=> $this->retValOrNull($_POST['tglujian']),
			'idjnsujian'=> $this->retValOrNull($_POST['idjnsujian']),
			'susulan'=> $this->retValOrNull($_POST['susulan']),
			'idruangan'=> $this->retValOrNull($_POST['idruangan']),
        );

		$where['idju'] = $_POST['idju'];	
		$this->db->where($where);
		$this->db->update('jadwalujian', $dataArray); 
		
		$val = $this->insert_jadwalujiandet($_POST['idju']);
		if ($val) {
			$ret["success"]=true;
			$return["message"]="Update Data Berhasil";
		} else {
			$ret["success"]=false;
			$return["message"]="Update Data Gagal";
		}
        return $ret;
    }
	
	function insert_jadwalujiandet($idju){      // ISTRA
        $arr=$this->input->post("waktu");
       
        $this->db->trans_begin();
            $rows = explode(";",$arr);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
                $this->db->query("CALL SP_simpan_waktu_uj (?,?,?,?,?)",array($rows2[0],$idju,$rows2[1],$rows2[2],$rows2[3])); 
            }

        
         if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            $return=false;
        }
        else
        {
            $this->db->trans_commit();
            $return=true;
        }
        return $return;
     }
	
	function delete_jadwalkuliah(){     
        $where['idjk'] = $_POST['idjk'];
		$ret = $this->deleteRecord('jadwalkuliah',$where);
        return $ret;
    }
	
	function delete_jadwalujian(){     
        $where['idju'] = $_POST['idju'];
		$ret = $this->deleteRecord('jadwalujian',$where);
        return $ret;
    }
	
	function get_jadwalkulcopy(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$idkelas = $_POST['idkelas'];
		
		$q = "SELECT j.idjk
			 , j.kdstsemester
			 , vt.nmthnakademik
			 , j.idhari
			 , h.nmhari
			 , jd.darijam
			 , jd.sampaijam
			 , r.idruangan
			 , r.nmruangan
		FROM
		  jadwalkuliah j
		LEFT JOIN jadwalkuliahdet jd
		ON j.idjk = jd.idjk
		LEFT JOIN v_tahunakademik vt
		ON j.kdstsemester = vt.kdstsemester
		LEFT JOIN hari h
		ON j.idhari = h.idhari
		LEFT JOIN ruangan r
		ON j.idruangan = r.idruangan
		WHERE
		  j.kdprodi = '$kdprodi'
		  AND j.kdstsemester <> '$kdstsemester'
		  AND j.kdmk = '$kdmk'
		  AND j.idkelas = '$idkelas'
		ORDER BY j.kdstsemester DESC";
		  
		$this->rhlib->jsonFromQuery($q);		
	}
	
	function copy_jadwalkul(){
		$idjklama = $_POST['idjklama']; 
		$kdprodi = $_POST['kdprodi']; 
		$kdmk = $_POST['kdmk'];
		$idkelas = $_POST['idkelas'];
		$kdstsemesterlama = $_POST['kdstsemesterlama']; 
		$kdstsemesterbaru = $_POST['kdstsemesterbaru'];
		$nidubaru = $_POST['nidubaru'];
		
		$idjkbaru = $this->autoNumber('idjk','jadwalkuliah');
		
		$sql = "INSERT INTO jadwalkuliah
				SELECT '$idjkbaru',idkelas,idtipekelas,'$kdstsemesterbaru',kdprodi,mat_kdprodi,kdmk,'$nidubaru',idhari,idruangan
				FROM
				  jadwalkuliah
				WHERE
				  kdprodi = '$kdprodi'
				  AND kdmk = '$kdmk'
				  AND idkelas = '$idkelas'
				  AND kdstsemester = '$kdstsemesterlama'";
		$exec = $this->db->query($sql);
		if($this->db->affected_rows()){
			$retdet = $this->copy_jadwalkuldet($idjklama, $idjkbaru);
			if ($retdet) {
				$ret["success"]=true;
				$ret["msg"]='Copy Data Berhasil';
			} else {
				$ret["success"]=false;
				$ret["msg"]='Copy Data Gagal';
			}
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Copy Data Gagal';
        }
        return $ret;
	}
	
	function copy_jadwalkuldet($idjklama, $idjkbaru){
		$sql = "INSERT INTO jadwalkuliahdet
				SELECT idjam, '$idjkbaru', darijam, sampaijam
				FROM
				  jadwalkuliahdet
				WHERE idjk='$idjklama'";
		$exec = $this->db->query($sql);
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Copy Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Copy Data Gagal';
        }
        return $ret;
	}
}
?>