<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Stmhssemester_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 getStrQueryWSearchStmhsSemester($q){
		$searchBy = "";		
		if(isset($_POST['key'])){
			$key = $_POST['key'];
			if($key!=null && $key!=''){
				$val = "";
				if(isset($_POST['value']))
					$val = $_POST['value'];
				if ($_POST['key']=='nmkelas' || $_POST['key']=='tingsem' || $_POST['key']=='nmstaktivitasmhs' || $_POST['key']=='nmcutidet') {
					if ($val) {
						$searchBy = " WHERE ".$key." = '".$val."'";
					} else {
						$searchBy = " WHERE ".$key." = '' OR ".$key." IS NULL";
					}
				} else {
					$searchBy = " WHERE ".$key." like '%".$val."%'";
				}
			}
		}		
		return "SELECT * FROM(".$q.")A ".$searchBy;
	}
	
	function jsonFromQueryStmhsSemester($q){
		$strQuery = $this->getStrQueryWSearchStmhsSemester($q);
		$query  = $this->db->query($strQuery);
        $data = array();
		$build_array = array ("success"=>false,"results"=>-1,"data"=>array(),"msg"=>"error");

		if ($query->num_rows() == 0)
		{
			$build_array["success"]=true;
			$build_array["results"]=0;
			$build_array["msg"]="no data";
		}
		if ($query->num_rows() > 0) {
            $data = $query->result();
			$ttl = count($data);
			if($ttl > 0){
				$build_array["success"]=true;
				$build_array["results"]=$ttl;
				$build_array["data"]=$data;
				$build_array["msg"]= "";
			}
        }
        echo json_encode($build_array);
	}
		
	function get_stmhssemester(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT a.*, b.nmstaktivitasmhs, c.nmmhs, c.kdprodi, c.kdjnskelamin, c.thnmasuk, d.nmkelas, e.tingkat, f.nmkodtbkod,
			concat(e.tingkat, '/ ', e.kdsemester) AS tingsem, g.nmcutidet, 
			concat((SELECT ifnull(get_persenbayardpp(a.nim, c.kdprodi, k.idjnskls, c.thnmasuk, a.kdstsemester),0)), ' %') AS persendpp,
			CASE
			   WHEN (i.nmstkrs IS NOT NULL) THEN
				 i.nmstkrs
			   ELSE
				 'Belum KRS'
			   END AS stkrs
			FROM stmhssemester a 
			LEFT JOIN (SELECT kdkodtbkod as kode, nmkodtbkod as nmstaktivitasmhs 
			FROM tbkod WHERE kdapltbkod='05') b ON b.kode=a.kdstaktivitasmhs
			LEFT JOIN mahasiswa c ON c.nim=a.nim
            LEFT JOIN kelas d ON d.idkelas=a.idkelas
            LEFT JOIN semester e ON e.idsemester=a.idsemester
			LEFT JOIN tbkod f ON c.kdstawalmhs = f.kdkodtbkod AND f.kdapltbkod = '06'
			LEFT JOIN cutidet g ON g.idcutidet=a.catnonaktif
			LEFT JOIN klsmhs k ON k.idklsmhs=c.idklsmhs
			LEFT JOIN v_krs i ON i.nim = a.nim AND i.kdstsemester = a.kdstsemester
			WHERE c.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'
			ORDER BY a.nim";
		//$this->jsonFromQueryWLimit($q);		
		$this->jsonFromQueryStmhsSemester($q);		
	}
	
	function get_jmlstmhssemester(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT sum(if(a.kdstaktivitasmhs = 'A', 1, 0)) AS aktif
				 , sum(if(a.kdstaktivitasmhs = 'C', 1, 0)) AS cuti
				 , sum(if(a.kdstaktivitasmhs = 'D', 1, 0)) AS dropout
				 , sum(if(a.kdstaktivitasmhs = 'G', 1, 0)) AS doubledegree
				 , sum(if(a.kdstaktivitasmhs = 'K', 1, 0)) AS keluar
				 , sum(if(a.kdstaktivitasmhs = 'L', 1, 0)) AS lulus
				 , sum(if(a.kdstaktivitasmhs = 'N', 1, 0)) AS nonaktif
			FROM
			  stmhssemester a
			LEFT JOIN (SELECT kdkodtbkod AS kode
							, nmkodtbkod AS nmstaktivitasmhs
					   FROM
						 tbkod
					   WHERE
						 kdapltbkod = '05') b
			ON b.kode = a.kdstaktivitasmhs
			LEFT JOIN mahasiswa c
			ON c.nim = a.nim
			LEFT JOIN kelas d
			ON d.idkelas = a.idkelas
			LEFT JOIN semester e
			ON e.idsemester = a.idsemester
			WHERE c.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'";
		
		$query  =  $this->db->query($q);
        $data= ''; 
                    
        if ($query->num_rows() > 0)
        {
            $row = $query->row();
			$dataArray = array(
			'aktif'=> $row->aktif,
			'cuti'=> $row->cuti,
			'dropout'=> $row->dropout,
			'doubledegree'=> $row->doubledegree,
			'keluar'=> $row->keluar,
			'lulus'=> $row->lulus,
			'nonaktif'=> $row->nonaktif,
		);
        }
        echo json_encode ($dataArray);
	}
	
	function insert_stmhssemester(){
		$dataArray = $this->getFieldsAndValues();
		$ret = $this->rhlib->insertRecord('stmhssemester',$dataArray);
		$this->update_stat_mhs();
        return $ret;
    }
	
	function update_stmhssemester(){ 				
		$dataArray = $this->getFieldsAndValues();
		
		//UPDATE
		$this->db->where('idstmhssemester', $_POST['idstmhssemester']);
		$this->db->update('stmhssemester', $dataArray); 
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
			$this->update_stat_mhs();
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
			$this->update_stat_mhs();
        }
        return $ret;
    }
	
	function update_stat_mhs(){ 				
		$dataArray = array(
			'kdstaktivitasmhs'=> $_POST['stmhs'], 
			'catnonaktif'=> $_POST['catnonaktif'] ? ($_POST['catnonaktif']):null,
		);
		
		//UPDATE
		$this->db->where('nim', $_POST['nim']);
		$this->db->update('mahasiswa', $dataArray); 
		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 update_kelas(){
		$idstmhssemester = $_POST['idstmhssemester'];
		$idkelas = $_POST['idkelas'];
		
		//UPDATE
		$this->db->where('idstmhssemester', $idstmhssemester);
		$this->db->set('idkelas', $idkelas);
		$this->db->update('stmhssemester'); 
        
		$this->update_kelas_absen();
		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 update_kelas_absen(){
		
		//UPDATE
		$this->db->where('nim', $_POST['nim']);
		$this->db->where('kdprodi', $_POST['kdprodi']);
		$this->db->where('kdstsemester', $_POST['kdstsemester']);
		$this->db->set('idkelas', $_POST['idkelas']);
		$this->db->update('absenmhs'); 
        
		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 update_tingsem(){
		$idstmhssemester = $_POST['idstmhssemester'];
		$idsemester = $_POST['idsemester'];
		
		//UPDATE
		$this->db->where('idstmhssemester', $idstmhssemester);
		$this->db->set('idsemester', $idsemester);
		$this->db->update('stmhssemester'); 
        
		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 getFieldsAndValues(){
		$dataArray = array(
             //'idstmhssemester'=> $_POST['idstmhssemester'],
			 'nim'=> $_POST['nim'], 
			 //'kdprodi' => $_POST['kdprodi'],  
			 'kdstsemester' => $_POST['kdstsemester'],
			 'kdstaktivitasmhs'=> $_POST['stmhs'], 
			 'tgllulus'=> $_POST['tgllulus'] ? ($_POST['tgllulus']):null,
			 'skslulus'=> $_POST['sks'] ? ($_POST['sks']):null,
			 'ipk'=> $_POST['ipk'] ? ($_POST['ipk']):null,
			 'nosk'=> $_POST['nosk'] ? ($_POST['nosk']):null,
			 'tglsk'=> $_POST['tglsk'] ? ($_POST['tglsk']):null,
			 'noijazah'=> $_POST['noseriizh'] ? ($_POST['noseriizh']):null,
			 'kdtskripsi'=> $_POST['tskripsi'] ? ($_POST['tskripsi']):null,
			 'kdjskripsi'=> $_POST['jskripsi'] ? ($_POST['jskripsi']):null,
			 'awalbimbingan'=> $_POST['tglawalbimbing'] ? ($_POST['tglawalbimbing']):null,
			 'akhirbimbingan'=> $_POST['tglakhirbimbing'] ? ($_POST['tglakhirbimbing']):null,
			 'nidu1'=> $_POST['idnidu1'] ? ($_POST['idnidu1']):null,
			 'nidu2'=> $_POST['idnidu2'] ? ($_POST['idnidu2']):null,
			 'nidu3'=> $_POST['idnidu3'] ? ($_POST['idnidu3']):null,
			 'nidu4'=> $_POST['idnidu4'] ? ($_POST['idnidu4']):null,
			 'nidu5'=> $_POST['idnidu5'] ? ($_POST['idnidu5']):null,
             'idsemester'=> $_POST['idsemester'],
             'idkelas'=> $_POST['idkelas'],
			 'catnonaktif'=> $_POST['catnonaktif'] ? ($_POST['catnonaktif']):null,
        );
    	return $dataArray;
	}
	
	function delete_stmhssemester(){     
		$where['idstmhssemester'] = $_POST['idstmhssemester'];
		$ret = $this->rhlib->deleteRecord('stmhssemester',$where);
        return $ret;
    }
	
	function copy_stmhssemester(){
		$kdprodi = $_POST['kdprodi']; 
		$smtbaru = $_POST['smtbaru'];
		$smtlama = $_POST['smtlama'];

		$exec = $this->db->query("CALL sp_copy_stmhssemester (?,?,?)",
				array($kdprodi,$smtlama,$smtbaru)
				);
				
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Copy Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Copy Data Gagal';
        }
        return $ret;
	}
	
	function get_klkpsyaratsidang(){
		$nim = $_POST['nim'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdprodi = $_POST['kdprodi'];
		
		$q = "SELECT * ,
		  if(((
			SELECT klp.kdsyaratsidang
			FROM
			  klkpsyaratsidang klp
			WHERE
			  klp.nim = '$nim'
			  AND klp.kdstsemester = '$kdstsemester'
			  AND klp.kdprodi = '$kdprodi'
			  AND klp.kdsyaratsidang = syaratsidang.kdsyaratsidang) = syaratsidang.kdsyaratsidang), 1, 0) AS cek
		FROM syaratsidang
		WHERE kdstsemester = '$kdstsemester' AND kdprodi = '$kdprodi'";
				
		$this->rhlib->jsonFromQuery($q);
	}
	
	function save_syaratsidang(){
        $par=$this->input->post("par");

        $this->db->trans_begin();
            $rows = explode(";",$par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
					
				$this->db->query("CALL SP_simpan_syaratsidang (?,?,?,?,?)",
				array($rows2[0],$rows2[1],$rows2[2],$rows2[3],$rows2[4]));
     
            }
        
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            $return["success"]=false;
            $return["message"]="Ubah Data gagal";
        }
        else
        {
            $this->db->trans_commit();
            $return["success"]=true;
            $return["message"]="Ubah Data Berhasil";
        }
        return $return["success"];
    }
	
	function get_klkpsyaratwisuda(){
		$nim = $_POST['nim'];
		$kdstsemester = $_POST['kdstsemester'];
		
		$q = "SELECT * ,
		  if(((
			SELECT klp.kdsyaratwisuda
			FROM
			  klkpsyaratwisuda klp
			WHERE
			  klp.nim = '$nim'
			  AND klp.kdstsemester = '$kdstsemester'
			  AND klp.kdsyaratwisuda = syaratwisuda.kdsyaratwisuda) = syaratwisuda.kdsyaratwisuda), 1, 0) AS cek
		FROM syaratwisuda
		WHERE kdstsemester = '$kdstsemester'";
				
		$this->rhlib->jsonFromQuery($q);
	}
	
	function save_syaratwisuda(){
        $par=$this->input->post("par");

        $this->db->trans_begin();
            $rows = explode(";",$par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
					
				$this->db->query("CALL SP_simpan_syaratwisuda (?,?,?,?)",
				array($rows2[0],$rows2[1],$rows2[2],$rows2[3]));
     
            }
        
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            $return["success"]=false;
            $return["message"]="Ubah Data gagal";
        }
        else
        {
            $this->db->trans_commit();
            $return["success"]=true;
            $return["message"]="Ubah Data Berhasil";
        }
        return $return["success"];
    }
	
}
?>