<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Struprogdet_Controller extends Controller {
	var $stat;
	
	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
    }
	
	function retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function retValOrZero($val){
		$val = ($val=='')? 0 : $val;
		return $val;
	}
			
	function get_struprogdet(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		/* $q = "SELECT a.*, ifnull(b.nmdosdgngelar,b.nmdostpgelar) as nmdosen,
			c.nmprodi as nmprodidosen, d.nmklsmhs, e.nmjenjangstudi, f.kdjnsdosen, f.nmjnsdosen, g.nmkelas
			FROM struprogdet a
			LEFT JOIN dosen b ON b.nidu=a.nidu
			LEFT JOIN prodi c ON c.kdprodi=a.kdprodidosen
			LEFT JOIN klsmhs d ON d.idklsmhs=a.idklsmhs
			LEFT JOIN (SELECT kdkodtbkod as kdjenjangstudi, nmkodtbkod as nmjenjangstudi 
				FROM tbkod WHERE kdapltbkod='04')e ON e.kdjenjangstudi=a.kdjenjangstudi
			LEFT JOIN jdosen f ON a.idjnsdosen = f.idjnsdosen
			LEFT JOIN kelas g ON g.idkelas=a.idkelas
			WHERE a.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'
			and a.kdmk='".$kdmk."'"; */
			
		$q = "SELECT a.*
			 , ifnull(b.nmdosdgngelar, b.nmdostpgelar) AS nmdosen
			 , c.nmprodi AS nmprodidosen
			 , d.nmkelas
			 , d.idklsmhs
			 , e.nmjenjangstudi
			 , f.kdjnsdosen
			 , f.nmjnsdosen
			 , g.kdklsmhs as nmklsmhs
			 , (SELECT count(nidu)
				FROM 
				  dosenpengganti
				WHERE idstruprogdet = a.idstruprogdet) as jmlniduganti
			 , (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 prodi c
		ON c.kdprodi = a.kdprodidosen
		LEFT JOIN kelas d
		ON d.idkelas = a.idkelas
		LEFT JOIN (SELECT kdkodtbkod AS kdjenjangstudi
						, nmkodtbkod AS nmjenjangstudi
				   FROM
					 tbkod
				   WHERE
					 kdapltbkod = '04') e
		ON e.kdjenjangstudi = a.kdjenjangstudi
		LEFT JOIN jdosen f
		ON a.idjnsdosen = f.idjnsdosen
		LEFT JOIN klsmhs g
		ON g.idklsmhs = d.idklsmhs
		WHERE
		  a.kdprodi = '".$kdprodi."'
		  AND a.kdstsemester = '".$kdstsemester."'
		  AND a.kdmk = '".$kdmk."'
		ORDER BY
		  a.idkelas, a.idjnsdosen";
		$this->rhlib->jsonFromQueryWLimit($q);
		
	}
	
	function get_count_koordinator(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$idkelas = $_POST['idkelas'];
		$column = $_POST['column'];
		//$queryExt = ($column=='idjnsdosen') ? " AND idjnsdosen = '1'" : "";
		$queryExt = " AND idjnsdosen = '1'";
		
		$q = "SELECT count(nidu) as jml FROM struprogdet 
			WHERE kdprodi='".$kdprodi."'
			AND kdstsemester='".$kdstsemester."'
			AND kdmk='".$kdmk."'
			".$queryExt."
			AND idkelas='".$idkelas."'";

		$query = $this->db->query($q);
		if ($query->num_rows() == 1){ 
			$jml = $query->row()->jml;		
			echo $jml;
		}
	}
		
	function insert_struprogdet(){
		$dataArray = $this->getFieldsAndValues();
		$ret = $this->rhlib->insertRecord('struprogdet',$dataArray);
        return $ret;
    }
		
	function update_struprogdet(){ 				
		$fieldsAndValues = $this->getFieldsAndValues();
		
		//UPDATE
		$this->db->where('idstruprogdet', $_POST['idstruprogdet']);
		$this->db->update('struprogdet', $fieldsAndValues); 
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
    }
	
	function update1field_struprogdet(){ 				
		$field = $_POST['field'];
		$value = $_POST['value'];
		
		//UPDATE
		$this->db->where('idstruprogdet', $_POST['idstruprogdet']);
		$this->db->set($field, $value);
		$this->db->update('struprogdet'); 
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
    }
	
	function delete_struprogdet(){     
        $where['idstruprogdet'] = $_POST['idstruprogdet'];
		$ret = $this->rhlib->deleteRecord('struprogdet',$where);
        return $ret;
    }
			
	function getFieldsAndValues(){
		$idkelas = (isset($_POST['idkelas']))? $_POST['idkelas'] : null;
		$silabus = (isset($_POST['silabus']))? $_POST['silabus'] : null;
		$satuanacarakuliah = (isset($_POST['satuanacarakuliah']))? $_POST['satuanacarakuliah'] : null;
		$bahanajar = (isset($_POST['bahanajar']))? $_POST['bahanajar'] : null;
		$diktat = (isset($_POST['diktat']))? $_POST['diktat'] : null;
		
		$dataArray = array(
		    'kdprodi'=> $_POST['kdprodi'], 
			'kdstsemester' => $_POST['kdstsemester'],
            'kdmk'=> $_POST['kdmk'],
			'nidu'=> $_POST['nidu'],
			'kdprodidosen'=>$_POST['kdprodidosen'],
			'kdjenjangstudi'=>$_POST['kdjenjangstudi'],
			'idkelas'=> $idkelas, 
			'silabus' => $silabus,
            'satuanacarakuliah'=> $satuanacarakuliah,
			'bahanajar'=> $bahanajar,
			'diktat'=> $diktat,
        );
		
		return $dataArray;
	}
	
	function get_dosenganti(){
		$idstruprogdet = $_POST['idstruprogdet'];
			
		$q = "SELECT dp.*, d.nmdosdgngelar, date_format(dp.tglaktif, '%d-%m-%Y') AS tglaktifrender, s.nmstatus
				FROM
				  dosenpengganti dp
				LEFT JOIN dosen d
				ON dp.nidu = d.nidu
				LEFT JOIN status s
				ON dp.idstatus = s.idstatus
				WHERE dp.idstruprogdet = '$idstruprogdet'
				ORDER BY dp.tglaktif DESC, dp.idstatus";
		$this->rhlib->jsonFromQuery($q);
		
	}
	
	function insert_dosenganti(){
		$dataArray = array(
		    'idstruprogdet'=> $_POST['idstruprogdet'], 
			'nidu' => $_POST['nidu'],
        );
		$ret = $this->rhlib->insertRecord('dosenpengganti',$dataArray);
        return $ret;
    }
	
	function update_dosenganti(){ 				
		$field = $_POST['field'];
		$value = $_POST['value'];
		
		//UPDATE
		$this->db->where('iddosenpengganti', $_POST['iddosenpengganti']);
		$this->db->set($field, $value);
		$this->db->update('dosenpengganti'); 
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
    }
	
	function delete_dosenganti(){     
        $where['iddosenpengganti'] = $_POST['iddosenpengganti'];
		$ret = $this->rhlib->deleteRecord('dosenpengganti',$where);
        return $ret;
    }

}
?>