<?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 get_matkulProdiSmt(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		/*
		$q = "SELECT a.kdprodi,a.kdstsemester,a.kdmk,b.nmmkind,
			IFNULL(c.jmldos,0) as jmldos
			FROM struprog a
			LEFT JOIN matakuliah b ON b.kdmk=a.kdmk and b.kdprodi=a.kdprodi
			LEFT JOIN (select kdprodi,kdstsemester,kdmk, count(nidu) as jmldos
				from struprogdet 
				GROUP BY kdprodi,kdstsemester,kdmk)c
				ON c.kdprodi=a.kdprodi and c.kdstsemester=a.kdstsemester  
				and c.kdmk=a.kdmk
			WHERE a.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'";
		*/
		$q = "SELECT a.kdprodi,a.kdstsemester,a.kdmk,b.nmmkind,
			IFNULL(c.jmldos,0) as jmldos
			FROM struprog a
			LEFT JOIN matakuliah b ON b.kdmk=a.kdmk and b.kdprodi=a.kdprodi
			LEFT JOIN (select kdmk, count(nidu) as jmldos
				from struprogdet s
				WHERE s.kdprodi='".$kdprodi."' and s.kdstsemester='".$kdstsemester."'
				GROUP BY kdmk)c
				ON c.kdmk=a.kdmk
			WHERE a.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'";
		$this->jsonFromQueryWLimit($q);		
	}
	
	function get_dosmk(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		
		$q = "SELECT a.kdprodi, a.kdstsemester, a.kdmk, 
			a.nidu, ifnull(b.nmdosdgngelar,b.nmdostpgelar) as nmdosen, 
			a.idklsmhs, c.nmklsmhs, IFNULL(d.jmlmhs,0) as jmlmhs
			FROM struprogdet a
			LEFT JOIN dosen b on b.nidu=a.nidu
			LEFT JOIN klsmhs c on c.idklsmhs=a.idklsmhs 
			LEFT JOIN (SELECT nidu, COUNT(DISTINCT nim) as jmlmhs FROM nilai
				WHERE kdprodi='".$kdprodi."' 
				and kdstsemester='".$kdstsemester."'
				and kdmk='".$kdmk."'
				GROUP BY nidu)d ON d.nidu=a.nidu
			WHERE a.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'
			and a.kdmk='".$kdmk."'
			and ifnull(a.nidu,'')<>''";
			
		$this->jsonFromQueryWLimit($q);	
	}
	
	function get_mahasiswamk(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		
		$q = "SELECT a.kdprodi,a.kdstsemester, 
				a.kdmk, a.nidu, a.nim, b.nmmhs,
				b.kdjnskelamin,b.thnmasuk,
				b.kdstawalmhs,c.nmstawalmhs,
				b.idklsmhs,d.nmklsmhs
				FROM nilai a
				LEFT JOIN mahasiswa b ON b.nim=a.nim
				LEFT JOIN (SELECT kdkodtbkod as kode, nmkodtbkod as nmstawalmhs 
					FROM tbkod WHERE kdapltbkod='06')c
					ON c.kode=b.kdstawalmhs
				LEFT JOIN klsmhs d ON b.idklsmhs=b.idklsmhs
				WHERE a.kdprodi='".$kdprodi."'
				and a.kdstsemester='".$kdstsemester."'
				and a.kdmk='".$kdmk."'
				and a.nidu='".$nidu."'";
		$this->jsonFromQuery($q);
	}

	function get_jadwalkuliah(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$jmlmhs = $this->retValOrZero($_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,
				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";
		$this->jsonFromQuery($q);	
	}
	
	function get_jadwalujian(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$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,
				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";
		$this->jsonFromQuery($q);	
	}
	
	function insert_jadwalkuliah(){
		$dataArray = array(
            'kdstsemester'=> $_POST['kdstsemester'],
            'kdprodi'=> $_POST['kdprodi'],
			'kdmk'=> $_POST['kdmk'],
			'nidu'=> $_POST['nidu'], 
			'idhari'=> $_POST['idhari'],
			'darijam'=> $_POST['darijam'],
			'sampaijam'=> $_POST['sampaijam'], 
			'idruangan'=> $_POST['idruangan'],
        );
		$ret = $this->insertRecord('jadwalkuliah',$dataArray);	
        return $ret;
	}
	
	function insert_jadwalujian(){
		$dataArray = array(
            'kdstsemester'=> $_POST['kdstsemester'],
            'kdprodi'=> $_POST['kdprodi'],
			'kdmk'=> $_POST['kdmk'],
			'nidu'=> $_POST['nidu'], 
			'idhari'=> $this->retValOrNull($_POST['idhari']),
			'tglujian'=> $_POST['tglujian'],
			'darijam'=> $_POST['darijam'],
			'sampaijam'=> $_POST['sampaijam'], 
			'idjnsujian'=> $_POST['idjnsujian'],
			'idruangan'=> $_POST['idruangan'],
        );
		$ret = $this->insertRecord('jadwalujian',$dataArray);	
        return $ret;
	}
	
	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;
    }
}
?>