<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Matkuldosmhs_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(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'];
		$jdosen = ($_POST['jdosen'] == 'Dosen Koordinator') ? "and a.idjnsdosen ='1'": "";
		
/* 		$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,m.idklsmhs, 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."'
				GROUP BY nidu,idklsmhs)d ON d.nidu=a.nidu AND d.idklsmhs=a.idklsmhs
			WHERE a.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'
			and a.kdmk='".$kdmk."'
			and ifnull(a.nidu,'')<>'' ".$jdosen; */
			
		$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, '') <> '' ".$jdosen;
			
		$this->jsonFromQueryWLimit($q);	
	}
	
	function get_mahasiswamk(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idklsmhs = $_POST['idklsmhs'];
		
		$q = "SELECT a.idnilai, a.kdprodi,a.kdstsemester, 
				a.kdmk, a.nidu, a.nim, b.nmmhs,
				b.kdjnskelamin,b.thnmasuk,
				b.kdstawalmhs,c.nmstawalmhs,
				b.idklsmhs,d.nmklsmhs,
				a.nilaihuruf, a.bobotnilai,
				a.klsparalel, a.nilaihurufsp, a.bobotnilaisp
				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 d.idklsmhs=b.idklsmhs
				WHERE a.kdprodi='".$kdprodi."'
				and a.kdstsemester='".$kdstsemester."'
				and a.kdmk='".$kdmk."'
				and a.nidu='".$nidu."'
				and b.idklsmhs='".$idklsmhs."'
				and a.idstsetuju=1";
		$this->jsonFromQueryWLimit($q);
	}
	
	function get_mahasiswaabsen(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idklsmhs = $_POST['idklsmhs'];
		
		$q = "SELECT a.idnilai, a.kdprodi,a.kdstsemester, 
				a.kdmk, a.nidu, a.nim, b.nmmhs,
				b.kdjnskelamin,b.thnmasuk,
				b.kdstawalmhs,c.nmstawalmhs,
				b.idklsmhs,d.nmklsmhs,
				a.nilaihuruf, a.bobotnilai,
				a.klsparalel, a.nilaihurufsp, a.bobotnilaisp
				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 d.idklsmhs=b.idklsmhs
				WHERE a.kdprodi='".$kdprodi."'
				and a.kdstsemester='".$kdstsemester."'
				and a.kdmk='".$kdmk."'
				and a.nidu='".$nidu."'
				and b.idklsmhs='".$idklsmhs."'
				and a.idstsetuju=1";
		$this->jsonFromQueryWLimit($q);
	}

}
?>