<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Psidang_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_psidang_old(){
		$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."'
			and a.st_sidang = 1
			ORDER BY a.nim";
		//$this->jsonFromQueryWLimit($q);		
		$this->jsonFromQueryStmhsSemester($q);		
	}
	
	function get_psidang(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$idklppengguna = $_POST['idklppengguna'];
		$q = "SELECT a.nim
     , a.kdstsemester
     , a.kdprodi
     , b.nmmhs
     , b.thnmasuk
     , ((SELECT count(syaratsidang.kdsyaratsidang)
                                                      FROM
                                                         syaratsidang
                                                       WHERE
                                                         syaratsidang.kdprodi = a.kdprodi
                                                         AND syaratsidang.kdstsemester = a.kdstsemester) - (SELECT count(klkpsyaratsidang.nim)
        FROM
          klkpsyaratsidang
        WHERE
          nim = a.nim
          AND klkpsyaratsidang.kdstsemester = a.kdstsemester
          AND klkpsyaratsidang.kdprodi = a.kdprodi)) as total_syaratsidang
	, ((SELECT count(syaratsidang.kdsyaratsidang)
                                                      FROM
                                                         syaratsidang
                                                       WHERE
                                                         syaratsidang.kdprodi = a.kdprodi
                                                         AND syaratsidang.kdstsemester = a.kdstsemester
														 AND syaratsidang.idklppengguna = '".$idklppengguna."') - (SELECT count(klkpsyaratsidang.nim)
        FROM
          klkpsyaratsidang
        WHERE
          nim = a.nim
          AND klkpsyaratsidang.kdstsemester = a.kdstsemester
          AND klkpsyaratsidang.kdprodi = a.kdprodi
		  and klkpsyaratsidang.idklppengguna = '".$idklppengguna."')) as tot_statusdet
	   , concat((SELECT ifnull(get_persenbayardpp(a.nim, b.kdprodi, k.idjnskls, b.thnmasuk, a.kdstsemester), 0)), ' %') AS persendpp
     , b.kdjnskelamin
FROM
  mhs_pesertasidang a
LEFT JOIN mahasiswa b
ON b.nim = a.nim
LEFT JOIN klsmhs k
ON k.idklsmhs = b.idklsmhs
WHERE
  a.kdprodi = $kdprodi
  AND a.kdstsemester = $kdstsemester";
		//$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 g_JKP(){ //ISTRA
        
        //======================================================================
        $start                  = $this->input->post("start");
        $limit                  = $this->input->post("limit");
        
        $fields                  = $this->input->post("fields");
        $query                  = $this->input->post("query");
      
        $this->db->select("*");
        $this->db->from("klppengguna");
		$where = array();   
		$where['klppengguna.nmklppengguna']= $_POST['nm_klp'];          
	
        $this->db->where($where);
        
        
        if($fields!="" || $query !=""){
            $k=array('[',']','"');
            $r=str_replace($k, '', $fields);
    //        $a[explode(',', $r)];
            $b=explode(',', $r);
            $c=count($b);
            for($i=0;$i<$c;$i++){
                $d[$b[$i]]=$query;
            }

           // $this->db->bracket('open','like');
             $this->db->or_like($d, $query);
           // $this->db->bracket('close','like');
        }
        
        //$this->db->order_by("jdashboard");
                
        if ($start!=null){
            $this->db->limit($limit,$start);
        }else{
            $this->db->limit(50,0);
        }
        
            $q = $this->db->get(); 
        
        
      //  $q = $this->db->get(); 
       
        $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $datax = $this->db->count_all('klppengguna');
        $ttl = $datax;
        
        //======================================================================
        
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

      foreach($data as $row) {
            array_push($build_array["data"],array(
                'idklppengguna'=>$row->idklppengguna,
                'kdklppengguna'=>$row->kdklppengguna,
                'nmklppengguna'=>$row->nmklppengguna,
                'deskripsi'=>$row->deskripsi,
                'idjnsdashboard'=>$this->nm_field('nmjnsdashboard', 'jdashboard', 'idjnsdashboard', $row->idjnsdashboard),
                'idstatus'=>$this->nm_field('nmstatus', 'status', 'idstatus', $row->idstatus),
            ));
        }
        echo json_encode($build_array);
        
    }
  
	
	function insert_psidang(){
		$dataArray = array(
			'nim'=>$_POST['nim'],
			'kdprodi'=>$_POST['kdprodi'],
			'kdstsemester'=>$_POST['kdstsemester'],
			'st_sidang'=>0,
		);
		$ret = $this->rhlib->insertRecord('mhs_pesertasidang',$dataArray);
		//$this->update_stat_mhs();
        return $ret;
    }
	
	function update_psidang_old(){ 				
		$dataArray = array(
			'st_sidang'=>1
		);
		//UPDATE
		$this->db->where('nim', $_POST['nim']);
		$this->db->where('kdstsemester', $_POST['kdstsemester']);
		$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_psidang(){     
		$where['nim'] = $_POST['nim'];
		$ret = $this->rhlib->deleteRecord('mhs_pesertasidang',$where);
		$ret = $this->rhlib->deleteRecord('klkpsyaratsidang',$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'];
		$nm_klp = $_POST['nm_klp'];
		
		$q = "SELECT syaratsidang.kdstsemester,
		syaratsidang.kdprodi,
		syaratsidang.kdsyaratsidang,
		syaratsidang.nmsyaratsidang,
		syaratsidang.deskripsi,
		syaratsidang.idklppengguna,
		  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
		LEFT JOIN klppengguna
		on syaratsidang.idklppengguna = klppengguna.idklppengguna
		WHERE kdstsemester = '$kdstsemester' AND kdprodi = '$kdprodi'
		AND nmklppengguna = '$nm_klp'";
				
		$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],$rows2[5]));
     
            }
        
        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"];
    }
	
	
	function export_excel($kdprodi,$kdstsemester) {
		$querymhs = "SELECT a.nim, b.nmmhs, b.tptlahir, b.tgllahir, b.nmfakultas
					, b.nmprodi , b.thnmasuk, b.alamatsurat FROM mhs_pesertasidang a
					LEFT JOIN vv_mahasiswa_profile2 b
					ON a.nim = b.nim
					WHERE 
					a.kdprodi = '".$kdprodi."' AND a.kdstsemester = '".$kdstsemester."'";
		  
		$data['eksport'] = $this->db->query($querymhs)->result();
		$data['table'] = "excel_daftar_calon_peserta_sidang";
		$data['fieldname'] = array();
		$getloop='loop';
		foreach ($data['eksport'] as $row) {
			if ($getloop=='loop') {
				foreach ($row as $key => $value) {
					array_push($data['fieldname'], $key);
				}
			}
			$getloop='end';
		}
		$this->load->view('exportexcel', $data); 	
	}
	
	
}
?>