<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class DosenWali_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 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_doswal(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT a.*, 
			ifnull(b.nmdosdgngelar,b.nmdostpgelar) as nmdosen, 
			b.kdjnskelamin, c.nmjabak
			FROM dosenwali a
			LEFT JOIN dosen b ON b.nidu=a.nidu
			LEFT JOIN (SELECT kdkodtbkod as kdjabak, nmkodtbkod as nmjabak 
				FROM tbkod WHERE kdapltbkod='02')c
				ON c.kdjabak=b.kdjabakademik
			WHERE a.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'";
		$this->jsonFromQueryWLimit($q);		
	}
	
	function get_doswaldet(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$nidu = $_POST['nidu'];
		$q = "SELECT a.*, 
			b.nmmhs,b.kdjnskelamin, b.thnmasuk, b.idklsmhs, 
			c.kdklsmhs as nmklsmhs, d.stdaftar
			FROM dosenwalidet a
			LEFT JOIN mahasiswa b ON b.nim=a.nim and b.kdprodi=a.kdprodi
			LEFT JOIN klsmhs c ON c.idklsmhs=b.idklsmhs
			LEFT JOIN (SELECT kdkodtbkod as kode, nmkodtbkod as stdaftar 
				FROM tbkod WHERE kdapltbkod='06')d
				ON d.kode=b.kdstawalmhs
			WHERE a.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'
			and a.nidu='".$nidu."'";
		$this->jsonFromQueryWLimit($q);	
	}
	
	function lookup_add_doswal(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT a.kdprodi
				 , a.kdstdosen
				 , a.nidu
				 , a.nidn
				 , a.nmdostpgelar
				 , a.nmdosdgngelar
				 , a.kdjabakademik
				 , a.kdjnskelamin
				 , b.nmjabak
				 , ifnull(a.nmdosdgngelar, a.nmdostpgelar) AS nmdosen
				 , c.nmstikj
				 , d.nmstaktiv
				 , e.nmprodi
			FROM
			  dosen a
			LEFT JOIN (SELECT kdkodtbkod AS kdjabak
							, nmkodtbkod AS nmjabak
					   FROM
						 tbkod
					   WHERE
						 kdapltbkod = '02') b
			ON b.kdjabak = a.kdjabakademik
			LEFT JOIN (SELECT kdkodtbkod AS kdstikj
							, nmkodtbkod AS nmstikj
					   FROM
						 tbkod
					   WHERE
						 kdapltbkod = '03') c
			ON a.kdstikatankerjadosen = c.kdstikj
			LEFT JOIN (SELECT kdkodtbkod AS kdstaktiv
							, nmkodtbkod AS nmstaktiv
					   FROM
						 tbkod
					   WHERE
						 kdapltbkod = '15') d
			ON a.kdstaktivitasdosen = d.kdstaktiv
			JOIN prodi e
			ON a.kdprodi = e.kdprodi
			WHERE
			  a.kdstaktivitasdosen = 'A'
			ORDER BY 
				a.kdprodi
				, a.nidu";		
				
		/* $kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT a.kdprodi,a.kdstdosen,a.nidu,a.nidn,a.nmdostpgelar,a.nmdosdgngelar,
			a.kdjabakademik, a.kdjnskelamin, b.nmjabak,
			ifnull(a.nmdosdgngelar,a.nmdostpgelar) as nmdosen,					
			c.nmstikj, d.nmstaktiv
			FROM dosen a
			LEFT JOIN (SELECT kdkodtbkod as kdjabak, nmkodtbkod as nmjabak 
				FROM tbkod WHERE kdapltbkod='02')b 
				ON b.kdjabak=a.kdjabakademik
			LEFT JOIN (SELECT kdkodtbkod as kdstikj, nmkodtbkod as nmstikj
				FROM tbkod WHERE kdapltbkod='03')c
				ON a.kdstikatankerjadosen = c.kdstikj
			LEFT JOIN (SELECT kdkodtbkod as kdstaktiv, nmkodtbkod as nmstaktiv
				FROM tbkod WHERE kdapltbkod='15')d
				ON a.kdstaktivitasdosen = d.kdstaktiv
			WHERE a.kdprodi='".$kdprodi."'
			AND a.kdstaktivitasdosen='A'
			AND a.nidu NOT IN (SELECT nidu from dosenwali
						WHERE kdprodi='".$kdprodi."' 
						AND kdstsemester='".$kdstsemester."')";		 */	
		$this->jsonFromQueryWLimit($q);	
	}
	
/* 	function lookup_add_doswal(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT a.kdprodi
				 , a.kdstdosen
				 , a.nidu
				 , a.nidn
				 , a.nmdostpgelar
				 , a.nmdosdgngelar
				 , a.kdjabakademik
				 , a.kdjnskelamin
				 , b.nmjabak
				 , ifnull(a.nmdosdgngelar, a.nmdostpgelar) AS nmdosen
				 , c.nmstikj
				 , d.nmstaktiv
				 , e.nmprodi
			FROM
			  dosen a
			LEFT JOIN (SELECT kdkodtbkod AS kdjabak
							, nmkodtbkod AS nmjabak
					   FROM
						 tbkod
					   WHERE
						 kdapltbkod = '02') b
			ON b.kdjabak = a.kdjabakademik
			LEFT JOIN (SELECT kdkodtbkod AS kdstikj
							, nmkodtbkod AS nmstikj
					   FROM
						 tbkod
					   WHERE
						 kdapltbkod = '03') c
			ON a.kdstikatankerjadosen = c.kdstikj
			LEFT JOIN (SELECT kdkodtbkod AS kdstaktiv
							, nmkodtbkod AS nmstaktiv
					   FROM
						 tbkod
					   WHERE
						 kdapltbkod = '15') d
			ON a.kdstaktivitasdosen = d.kdstaktiv
			JOIN prodi e
			ON a.kdprodi = e.kdprodi
			WHERE
			  a.kdstaktivitasdosen = 'A'
			  AND a.nidu NOT IN (SELECT nidu
								 FROM
								   dosenwali
								 WHERE
								   kdstsemester = '".$kdstsemester."')
			ORDER BY 
				a.kdprodi
				, a.nidu";		
					
		$this->jsonFromQueryWLimit($q);	
	} */
	
	function lookup_add_doswaldet(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		//$nidu = $_POST['nidu'];
		$q = "SELECT a.nim, a.nmmhs, a.kdjnskelamin, a.thnmasuk, a.idklsmhs, 
			b.nmklsmhs,	c.stdaftar
			FROM mahasiswa a
			LEFT JOIN klsmhs b ON b.idklsmhs=a.idklsmhs
			LEFT JOIN (SELECT kdkodtbkod as kode, nmkodtbkod as stdaftar 
				FROM tbkod WHERE kdapltbkod='06')c
				ON c.kode=a.kdstawalmhs
			WHERE a.kdprodi='".$kdprodi."'
			AND a.nim NOT IN (
					SELECT nim FROM dosenwalidet
					WHERE kdprodi='".$kdprodi."'
					and kdstsemester='".$kdstsemester."')";
					//and nidu='".$nidu."')";
		$this->jsonFromQueryWLimit($q);	
	}
	
	function insert_doswal(){
		$dataArray = array(
		     'kdprodi'=> $_POST['kdprodi'], 
			 'kdstsemester' => $_POST['kdstsemester'],
             'nidu'=> $_POST['nidu'],
        );
		$ret = $this->rhlib->insertRecord('dosenwali',$dataArray);
        return $ret;
    }
	
	function insert_doswaldet(){
		$dataArray = array(
		     'kdprodi'=> $_POST['kdprodi'], 
			 'kdstsemester' => $_POST['kdstsemester'],
             'nidu'=> $_POST['nidu'],
			 'nim'=> $_POST['nim']
        );
		$ret = $this->rhlib->insertRecord('dosenwalidet',$dataArray);
        return $ret;
    }
			
	function delete_detail($where){     
        $this->db->trans_begin();   
        $this->db->delete("dosenwalidet",$where);       
        if ($this->db->trans_status() === FALSE){
            $this->db->trans_rollback(); return false;
        }else{
            $this->db->trans_commit(); return true;
        }
    }
	
	function delete_doswal(){
		$where['kdprodi'] = $_POST['kdprodi'];
		$where['kdstsemester'] = $_POST['kdstsemester'];
		$where['nidu'] = $_POST['nidu'];		
		$delDet = $this->delete_detail($where); //delete details first
		if($delDet){
			$this->db->trans_begin();   
			$this->db->delete("dosenwali",$where);
       
			if ($this->db->trans_status() === FALSE){
				$this->db->trans_rollback();
				$return["success"]=false;
				$return["message"]="Hapus Data gagal";
			}else{
				$this->db->trans_commit();
				$return["success"]=true;
				$return["message"]="Hapus Data Berhasil";
			}
		}
		else{
			$return["success"]=false;
			$return["message"]="Hapus Detail gagal";
		}
		return $return;
    }
	
	function delete_doswaldet(){
		$where['kdprodi'] = $_POST['kdprodi'];
		$where['kdstsemester'] = $_POST['kdstsemester'];
		$where['nidu'] = $_POST['nidu'];
		$where['nim'] = $_POST['nim'];
		$ret = $this->rhlib->deleteRecord('dosenwalidet',$where);
        return $ret;
    }
	
	function copy_dosenwali(){
		$kdprodi = $_POST['kdprodi']; 
		$thbaru = $_POST['thbaru'];
		$thlama = $_POST['thlama'];
		$sql = "INSERT dosenwali(kdprodi,kdstsemester,nidu)
				SELECT kdprodi,".$thbaru.",nidu
				FROM dosenwali 
				WHERE kdprodi='".$kdprodi."'
				and kdstsemester='".$thlama."'";
		$exec = $this->db->query($sql);
		if ($exec) {
			$this->copy_dosenwalidet($kdprodi,$thbaru,$thlama);
		}
		/* if($this->db->affected_rows()){
			$this->copy_dosenwalidet($kdprodi,$thbaru,$thlama);
            $ret["success"]=true;
            $ret["msg"]='Copy Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Copy Data Gagal';
        }
        return $ret; */
	}
	
	function copy_dosenwalidet($kdprodi,$thbaru,$thlama){
		$sql = "INSERT dosenwalidet(kdprodi,kdstsemester,nidu,nim)
				SELECT kdprodi,".$thbaru.",nidu,nim
				FROM dosenwalidet
				WHERE kdprodi='".$kdprodi."'
				and kdstsemester='".$thlama."'";
		$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;
	}
		
}
?>