<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Mahasiswa_Controller extends Controller {
	var $stat;
		
	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
	}
	
	var $_jsVars = array();
	public function setJsVar($name, $value)
    {
        $this->_jsVars[$name] = $value;
    }
	
	function getDataLimited($q){
		$start = $_POST['start'];
        $limit = $_POST['limit'];
		
		if ($start==null){
            $start = 0;
			$limit = 20;
		}	
		$data = array();
		$build_array = array ("success"=>false,"results"=>0,"data"=>array());			
        
		$query1  = $this->db->query($q);	//query all rows        
        if ($query1->num_rows() > 0) {
			$total = $query1->num_rows(); // total data			
			$q .=" LIMIT ".$start.", ".$limit; // query dengan limit
			$query2  = $this->db->query($q);
            $data = $query2->result();
			$build_array["success"]=true;
			$build_array["results"]=$total;
            //$build_array["count"]=count($data);
			$build_array["data"]=$data;
        }
        echo json_encode($build_array);
	}
	
	function get_mhs_byId(){
		$q = "SELECT a.* 
				FROM mahasiswa a 
				WHERE nim='".$_POST['nim']."'";
		$query  = $this->db->query($q);
        $data = array();
		$build_array = array ("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data");
        
		if ($query->num_rows() > 0) {
            $data = $query->result(); 			
			$ttl = count($data);
			if($ttl == 1){
				$build_array["success"]=true;
				$build_array["msg"]= "";
			}
			$build_array["results"]=$ttl;
			$build_array["data"]=$data;
			$build_array["msg"]= "data lebih dari satu!";
        }
        echo json_encode($build_array);
	}
	
	function get_autoNum($table, $idTobeNum, $cond, $default){
		$num = $default;
		$q = "select IFNULL(
						(select MAX(
							CAST(".$idTobeNum
							." AS UNSIGNED)) + 1
						from ".$table 
						." WHERE ".$cond.")
					,".$default.") 
				as num";
		$query = $this->db->query($q);
		if ($query->num_rows() == 1) 
			$num = $query->row()->num;
		
		return $num;
	}
	
	function get_nim(){
		/* //FOR DEBUGING
		select nim, SUBSTR(nim, 11,  4) as nuourut, nmmhs, SUBSTR(nim, 1, 10) as awal 
		from mahasiswa WHERE SUBSTR(nim, 1, 10) = '4115202010';
		*/
		$prefix = $_POST['prefix'];
		$table = "mahasiswa";
		$idTobeNum = "nim";								//"SUBSTR(nim, 11, 4)"; //jika 4 digit terakhir saja yg berupa angka
		$cond = "SUBSTR(nim, 1, 10) = '".$prefix."'"; 	//"nim like '".$prefix."%'";
		$default = $prefix."0001";						// 1; jika hanya diambil bilangan terakhir saja
		$nim = $this->get_autoNum($table, $idTobeNum, $cond, $default);
		echo json_encode(array ("success"=>true,"nim"=>$nim));		
	}
			
	function get_mhs(){
		$q = "SELECT DISTINCT a.*, 
			b.nmfakultas, b.nourutprodi, b.nmprodi, ifnull(b.lnmprodi,b.nmprodi) as lnmprodi, 
			b.nmjenjangstudi,
			c.nmstawal, d.nmstaktiv,
			e.nmkotkabasal, f.nmkotkabsurat, g.nmkotkabortu, 
			h.nmkotkabslta, i.nmproslta
			FROM mahasiswa a
			LEFT JOIN (SELECT kdprodi, nourutprodi, nmprodi, 
					CONCAT(RTRIM(nourutprodi),'. ',nmprodi,' (',nmjenjangstudi,')') as lnmprodi,
					nmfakultas, kdjenjangstudi, nmjenjangstudi,
					tglawalberdiri, email, noskakreditasi, tglskakreditasi, notelpprodi
				FROM v_prodi where kdprodi <> '')b
			ON b.kdprodi=a.kdprodi
			LEFT JOIN (SELECT kdkodtbkod as kdstawal, nmkodtbkod as nmstawal 
				FROM tbkod WHERE kdapltbkod='06')c
				ON a.kdstawalmhs=c.kdstawal
			LEFT JOIN (SELECT kdkodtbkod as kdstaktiv, nmkodtbkod as nmstaktiv 
				FROM tbkod WHERE kdapltbkod='05')d
				ON a.kdstaktivitasmhs=d.kdstaktiv 
			LEFT JOIN (SELECT CONCAT(rtrim(kdprotbpro),ltrim(kdkabtbpro)) as kdkota, 
				nmkabtbpro as nmkotkabasal FROM tbpro 
				where kdkabtbpro <> '')e
				ON a.kdkotkabasal = e.kdkota
			LEFT JOIN (SELECT CONCAT(rtrim(kdprotbpro),ltrim(kdkabtbpro)) as kdkota, 
				nmkabtbpro as nmkotkabsurat FROM tbpro 
				where kdkabtbpro <> '')f
				ON a.kdkotkabsurat = f.kdkota
			LEFT JOIN (SELECT CONCAT(rtrim(kdprotbpro),ltrim(kdkabtbpro)) as kdkota, 
				nmkabtbpro as nmkotkabortu FROM tbpro 
				where kdkabtbpro <> '')g
				ON a.kdkotkabortu = g.kdkota
			LEFT JOIN (SELECT CONCAT(rtrim(kdprotbpro),ltrim(kdkabtbpro)) as kdkota, 
				nmkabtbpro as nmkotkabslta FROM tbpro 
				where kdkabtbpro <> '')h
				ON a.kdkotkabslta = h.kdkota 
			LEFT JOIN (SELECT kdprotbpro, nmprotbpro as nmproslta FROM tbpro where kdprotbpro <> '')i
				ON a.kdpropinsislta = i.kdprotbpro";
				
		$this->getDataLimited($q);
	}
	
	function lookup_mhs(){
		$q = "SELECT DISTINCT a.nim, a.nmmhs,a.kdjnskelamin,a.notelpmhs,nohpmhs 
			b.nmfakultas, b.nourutprodi, b.nmprodi, ifnull(b.lnmprodi,b.nmprodi) as lnmprodi, 
			b.nmjenjangstudi,
			c.nmstawal, d.nmstaktiv,
			FROM mahasiswa a
			LEFT JOIN (SELECT kdprodi, nourutprodi, nmprodi, 
					CONCAT(RTRIM(nourutprodi),'. ',nmprodi,' (',nmjenjangstudi,')') as lnmprodi,
					nmfakultas, kdjenjangstudi, nmjenjangstudi,
					tglawalberdiri, email, noskakreditasi, tglskakreditasi, notelpprodi
				FROM v_prodi where kdprodi <> '')b
			ON b.kdprodi=a.kdprodi
			LEFT JOIN (SELECT kdkodtbkod as kdstawal, nmkodtbkod as nmstawal 
				FROM tbkod WHERE kdapltbkod='06')c
				ON a.kdstawalmhs=c.kdstawal
			LEFT JOIN (SELECT kdkodtbkod as kdstaktiv, nmkodtbkod as nmstaktiv 
				FROM tbkod WHERE kdapltbkod='05')d
				ON a.kdstaktivitasmhs=d.kdstaktiv ";
				
		$this->getDataLimited($q);
	}
	
	
	function insert_mhs(){
		$filedAndVal = $this->getfiledAndVal();

        $this->db->insert('mahasiswa', $filedAndVal);
        if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Simpan Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Simpan Data Gagal';
        }
        return $ret;
    }

	function update_mhs(){ 				
		$filedAndVal = $this->getfiledAndVal();
		
		//UPDATE
		$this->db->where('nim', $_POST['nim']);
		$this->db->update('mahasiswa', $filedAndVal); 
		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 retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function getfiledAndVal()
	{
		$dataArray = array(
		//PERSONAL			
             'nim'=> $_POST['nim'], //$_POST['tf.npm-mhs'],
             'nmmhs'=> $_POST['nmmhs'], //$_POST['tf.nama-mhs'],
			 'kdjnskelamin'=> $this->retValOrNull($_POST['kdjnskelamin']), 
			 'tptlahir'=> $_POST['tptlahir'], 
			 'tgllahir'=> $_POST['tgllahir'], 
			 'kdagama'=> $this->retValOrNull($_POST['kdagama']), 
			 'idgoldarah'=> $this->retValOrNull($_POST['idgoldarah']), 
			 'kdwn'=> $this->retValOrNull($_POST['kdwn']),
			 'notelpmhs'=> $_POST['notelpmhs'],
			 'nohpmhs'=> $_POST['nohpmhs'],
			 'alamatasal'=> $_POST['alamatasal'],
			 'kelurahanasal'=> $_POST['kelurahanasal'],
			 'kecamatanasal'=> $_POST['kecamatanasal'],
			 'kdkotkabasal'=> $_POST['kdkotkabasal'],
			 'kdposasal'=> $_POST['kdposasal'],
			 'alamatsurat'=> $_POST['alamatsurat'],
			 'kelurahansurat'=> $_POST['kelurahansurat'],
			 'kecamatansurat'=> $_POST['kecamatansurat'],
			 'kdkotkabsurat'=> $_POST['kdkotkabsurat'],
			 'kdpossurat'=> $_POST['kdpossurat'],
		//ORTU
			 'nmortu'=> $_POST['nmortu'],
			 'idpekerjaanortu'=> $this->retValOrNull($_POST['idpekerjaanortu']),
			 'idpendidikanortu'=> $this->retValOrNull($_POST['idpendidikanortu']),
			 'alamatortu'=> $_POST['alamatortu'],
			 'kelurahanortu'=> $_POST['kelurahanortu'],
			 'kecamatanortu'=> $_POST['kecamatanortu'],
			 'kdkotkabortu'=> $_POST['kdkotkabortu'],
			 'kdposortu'=> $_POST['kdposortu'],
			 'notelportu'=> $_POST['notelportu'],
		//DAFTAR
			 'nopmb'=> $_POST['nopmb'],
			 'kdstsemester'=> $this->retValOrNull($_POST['kdstsemester']),
			 'kdprodi'=> $_POST['kdprodi'], //$this->retValOrNull($_POST['kdprodi']),
			 'shiftmhs'=> $this->retValOrNull($_POST['shiftmhs']),
			 'idklsmhs'=> $this->retValOrNull($_POST['idklsmhs']),
			 'thnmasuk'=> $this->retValOrNull($_POST['thnmasuk']),
			 'tglmasuk'=> $_POST['tglmasuk'],
			 'kdstawalmhs'=> $this->retValOrNull($_POST['kdstawalmhs']),
			 'kdstaktivitasmhs'=> $this->retValOrNull($_POST['kdstaktivitasmhs']),
			 'kdpropinsislta'=> $this->retValOrNull($_POST['kdpropinsislta']),
			 'btsstudi'=> $this->retValOrNull($_POST['btsstudi']),
		//SLTA ASAL
			'nmslta'=> $_POST['nmslta'],
			'kdkotkabslta'=> $this->retValOrNull($_POST['kdkotkabslta']),
			'idjnsslta'=> $this->retValOrNull($_POST['idjnsslta']),
			'idstakreditasislta'=> $this->retValOrNull($_POST['idstakreditasislta']),
			'thnlulusslta'=> $this->retValOrNull($_POST['thnlulusslta']),
			'nemslta'=> $this->retValOrNull($_POST['nemslta']),
			'rangkingslta'=> $this->retValOrNull($_POST['rangkingslta']),
			'noijazahslta'=> $_POST['noijazahslta'],
			'tglijazahslta'=> $this->retValOrNull($_POST['tglijazahslta']),
		//ASAL PT/PINDAHAN
			'kdptasal'=> $this->retValOrNull($_POST['kdptasal']),
			'nmptasal'=> $this->retValOrNull($_POST['nmptasal']),
			'idjnspt'=> $this->retValOrNull($_POST['idjnspt']),
			'idstakreditasi'=> $this->retValOrNull($_POST['idstakreditasi']),
			'idkuakreditasi'=> $this->retValOrNull($_POST['idkuakreditasi']),
			'kdjenjangstudiasal'=> $this->retValOrNull($_POST['kdjenjangstudiasal']),
			'fakultasasal'=> $this->retValOrNull($_POST['fakultasasal']),
			'kdprodiasal'=> $this->retValOrNull($_POST['kdprodiasal']),
			'nmprodiasal'=> $this->retValOrNull($_POST['nmprodiasal']),
			'nimasal'=> $this->retValOrNull($_POST['nimasal']),
			'noijazahasal'=> $this->retValOrNull($_POST['noijazahasal']),
			'tglijazahasal'=> $this->retValOrNull($_POST['tglijazahasal']),
			'idsemesterpindah'=> $this->retValOrNull($_POST['idsemesterpindah']),
			'jmlsksdiakui'=> $this->retValOrNull($_POST['jmlsksdiakui']),
			'alasanpindah'=> $this->retValOrNull($_POST['alasanpindah']),
		//PEKERJAAN MHS
			'idpekerjaanmhs'=> $this->retValOrNull($_POST['idpekerjaanmhs']),
			'nmtptbekerjamhs'=> $this->retValOrNull($_POST['nmtptbekerjamhs']),
			'alamatkerjamhs'=> $this->retValOrNull($_POST['alamatkerkamhs']),
			'jabatanmhs'=> $this->retValOrNull($_POST['jabatanmhs']),
		//ALUMNI
			'idpekerjaanalumni'=> $this->retValOrNull($_POST['idpekerjaanalumni']),
			'nmtptbekerjaalumni'=> $this->retValOrNull($_POST['nmtptbekerjaalumni']),
			'alamatkerjaalumni'=> $this->retValOrNull($_POST['alamatkerjaalumni']),
			'jabatanalumni'=> $this->retValOrNull($_POST['jabatanalumni']),
			'thnkeluar'=> $this->retValOrNull($_POST['thnkeluar']),
			'tgllulus'=> $this->retValOrNull($_POST['tgllulus']),
		//S3
			'kdbiayastudi'=> $this->retValOrNull($_POST['kdbiayastudi']),
			'kdpttptbekerja'=> $this->retValOrNull($_POST['kdpttptbekerja']),
			'nmpttptbekerja'=> $this->retValOrNull($_POST['nmpttptbekerja']),
			'kdproditptbekerja'=> $this->retValOrNull($_POST['kdproditptbekerja']),
			'nmproditptbekerja'=> $this->retValOrNull($_POST['nmproditptbekerja']),
			'nidupromotor'=> $this->retValOrNull($_POST['nidupromotor']),
			'nidukopromotor1'=> $this->retValOrNull($_POST['nidukopromotor1']),
			'nidukopromotor2'=> $this->retValOrNull($_POST['nidukopromotor2']),
			'nidukopromotor3'=> $this->retValOrNull($_POST['nidukopromotor3']),
			'nidukopromotor3'=> $this->retValOrNull($_POST['nidukopromotor4']),
			
		//LAINNYA
			'email'=> $this->retValOrNull($_POST['email']),
			'facebook'=> $this->retValOrNull($_POST['facebook']),
			'twitter'=> $this->retValOrNull($_POST['twitter']),
			'pinbb'=> $this->retValOrNull($_POST['pinbb']),
			'website'=> $this->retValOrNull($_POST['website']),			
        );
		
		return $dataArray;
	}
}