<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Matkuldos_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,a.idsemester
			FROM struprog a
			LEFT JOIN matakuliah b ON b.kdmk=a.kdmk and b.kdprodi=a.kdprodi
			LEFT JOIN (select kdmk, count(DISTINCT 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.idkelas
				 , c.nmkelas
				 , c.idklsmhs
				 , d.nmklsmhs
				 , ifnull(e.jmlmhs, 0) AS jmlmhs
			FROM
			  struprogdet a
			LEFT JOIN dosen b
			ON b.nidu = a.nidu
			LEFT JOIN kelas c
			ON c.idkelas = a.idkelas
			LEFT JOIN klsmhs d
			ON d.idklsmhs = c.idklsmhs
			LEFT JOIN (SELECT nidu,idkelas,count(DISTINCT n.nim) AS jmlmhs
					   FROM
						 absenmhs n
					   WHERE
						 n.kdprodi = '".$kdprodi."'
						 AND n.kdstsemester = '".$kdstsemester."'
						 AND n.kdmk = '".$kdmk."') e
			ON e.nidu = a.nidu AND e.idkelas = c.idkelas
			WHERE
			  a.kdprodi = '".$kdprodi."'
			  AND a.kdstsemester = '".$kdstsemester."'
			  AND a.kdmk = '".$kdmk."'
			  AND ifnull(a.nidu, '') <> ''"; */
			  
			  	$q = "SELECT a.kdprodi
				 , a.kdstsemester
				 , a.kdmk
				 , a.nidu AS nidu
				 , ifnull(dp.nidu, a.nidu) AS nidudisp
				 , ifnull(nmdp.nmdosdgngelar,b.nmdosdgngelar) AS nmdosen
				 , a.idjnsdosen
				 , (select s.nidu from struprogdet s
					LEFT JOIN dosenpengganti dp
					 ON dp.idstruprogdet = s.idstruprogdet AND dp.idstatus = 1
					WHERE s.kdprodi = '".$kdprodi."'
					 AND s.kdstsemester = '".$kdstsemester."'
					 AND s.kdmk = '".$kdmk."'
					 AND ifnull(s.nidu, '') <> ''
					 -- AND s.nidu=a.nidu
					 AND s.idkelas=a.idkelas
					 AND s.idjnsdosen=2) as niduasist
				 , (select ifnull(dp.nidu, s.nidu) from struprogdet s
					LEFT JOIN dosenpengganti dp
					 ON dp.idstruprogdet = s.idstruprogdet AND dp.idstatus = 1
					WHERE s.kdprodi = '".$kdprodi."'
					 AND s.kdstsemester = '".$kdstsemester."'
					 AND s.kdmk = '".$kdmk."'
					 AND ifnull(s.nidu, '') <> ''
					 -- AND s.nidu=a.nidu
					 AND s.idkelas=a.idkelas
					 AND s.idjnsdosen=2) as niduasistdisp
				 , (select ifnull(nmdosdgngelar, nmdostpgelar) from dosen where nidu = niduasistdisp) as nmdosasist
				 , a.idkelas
				 , c.nmkelas
				 , c.idklsmhs
				 , d.nmklsmhs
				 ,  (SELECT count(DISTINCT n.nim) AS jmlmhs
					FROM
					  absenmhs n
					JOIN mahasiswa m ON n.nim=m.nim
					LEFT JOIN nilai a
					ON a.kdmk = n.kdmk
						AND a.kdprodi = n.kdprodi
						AND a.kdstsemester = n.kdstsemester
						AND a.nidu = n.nidu
						AND a.nim = n.nim
					LEFT JOIN stmhssemester s
					ON n.nim = s.nim AND n.kdstsemester = s.kdstsemester
					WHERE
					  n.kdprodi = '".$kdprodi."'
					  AND n.kdstsemester = '".$kdstsemester."'
					  AND n.kdmk = '".$kdmk."'
					  AND n.idkelas = a.idkelas
					  -- AND n.nidu = a.nidu
					  AND s.kdstaktivitasmhs='A'
					  AND a.idstsetuju = 1) AS jmlmhs
			FROM
			  struprogdet a
			LEFT JOIN dosen b
			ON b.nidu = a.nidu
			LEFT JOIN kelas c
			ON c.idkelas = a.idkelas
			LEFT JOIN klsmhs d
			ON d.idklsmhs = c.idklsmhs
			LEFT JOIN dosenpengganti dp
			ON dp.idstruprogdet = a.idstruprogdet AND dp.idstatus=1
			LEFT JOIN dosen nmdp
			ON nmdp.nidu = dp.nidu
			WHERE
			  a.kdprodi = '".$kdprodi."'
			  AND a.kdstsemester = '".$kdstsemester."'
			  AND a.kdmk = '".$kdmk."'
			  AND ifnull(a.nidu, '') <> ''
			  AND a.idjnsdosen=1";
			
		$this->jsonFromQueryWLimit($q);	
	}

	function get_dosmk_koord(){
		$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.idkelas
				 , c.nmkelas
				 , c.idklsmhs
				 , d.nmklsmhs
				 , ifnull(e.jmlmhs, 0) AS jmlmhs
			FROM
			  struprogdet a
			LEFT JOIN dosen b
			ON b.nidu = a.nidu
			LEFT JOIN kelas c
			ON c.idkelas = a.idkelas
			LEFT JOIN klsmhs d
			ON d.idklsmhs = c.idklsmhs
			LEFT JOIN (SELECT nidu,idkelas,count(DISTINCT n.nim) AS jmlmhs
					   FROM
						 absenmhs n
					   WHERE
						 n.kdprodi = '".$kdprodi."'
						 AND n.kdstsemester = '".$kdstsemester."'
						 AND n.kdmk = '".$kdmk."') e
			ON e.nidu = a.nidu AND e.idkelas = c.idkelas
			WHERE
			  a.kdprodi = '".$kdprodi."'
			  AND a.kdstsemester = '".$kdstsemester."'
			  AND a.kdmk = '".$kdmk."'
			  AND ifnull(a.nidu, '') <> '' AND a.idjnsdosen ='1'";
			
		$this->jsonFromQueryWLimit($q);	
	}

}
?>