<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class NilaiManual_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_makulkurikulum(){
		$kdprodi = $_POST['kdprodi'];
		$thnmasuk = $_POST['thnmasuk'];

		$q = "SELECT a.*, b.nmmkind
			, CASE
					   WHEN a.idsemester = 1 THEN
						 concat(a.thnmasuk, 1)
					   WHEN a.idsemester = 2 THEN
						 concat(a.thnmasuk, 2)
					   WHEN a.idsemester = 3 THEN
						 concat(a.thnmasuk + 1, 1)
					   WHEN a.idsemester = 4 THEN
						 concat(a.thnmasuk + 1, 2)
					   WHEN a.idsemester = 5 THEN
						 concat(a.thnmasuk + 2, 1)
					   WHEN a.idsemester = 6 THEN
						 concat(a.thnmasuk + 2, 2)
					   WHEN a.idsemester = 7 THEN
						 concat(a.thnmasuk + 3, 1)
					   WHEN a.idsemester = 8 THEN
						 concat(a.thnmasuk + 3, 2)
				  END AS kdstsemester
			FROM kurikulum a
			LEFT JOIN matakuliah b ON b.kdmk=a.kdmk  and b.kdprodi=a.kdprodi
			WHERE a.kdprodi='".$kdprodi."'
			and a.thnmasuk='".$thnmasuk."'
			ORDER BY a.idsemester, a.idkurikulum";
		$this->rhlib->jsonFromQueryWLimit($q);
		
	}
	
	function get_nilaimhs(){
		$q = "SELECT mahasiswa.nim
				 , mahasiswa.nmmhs
				 , mahasiswa.kdjnskelamin
				 , nilai.idnilai
				 , nilai.nilaihuruf
				 , nilai.bobotnilai
				 , v_tahunakademik.nmthnakademik
				 , nilai.idstambilmk
				 , stambilmk.nmstambilmk
				 , nilai.idstsetuju
				 , stsetuju.nmstsetuju
			FROM
			  nilai
			LEFT JOIN mahasiswa
			ON mahasiswa.nim = nilai.nim
			LEFT JOIN v_tahunakademik
			ON v_tahunakademik.kdstsemester = nilai.kdstsemester
			LEFT JOIN stambilmk
			ON stambilmk.idstambilmk = nilai.idstambilmk
			LEFT JOIN stsetuju
			ON stsetuju.idstsetuju = nilai.idstsetuju
			WHERE
			  nilai.kdprodi = '".$_POST['kdprodi']."'
			  AND nilai.kdmk = '".$_POST['kdmk']."'
			  AND mahasiswa.thnmasuk = '".$_POST['thnmasuk']."'";
			  
			  /* $q = "SELECT mahasiswa.nim
				 , mahasiswa.nmmhs
				 , mahasiswa.kdjnskelamin
				 , nilai.idnilai
				 , nilai.nilaihuruf
				 , nilai.bobotnilai
			FROM
			  nilai
			LEFT JOIN mahasiswa
			ON mahasiswa.nim = nilai.nim
			WHERE
			  nilai.kdprodi = '".$_POST['kdprodi']."'
			  AND nilai.kdstsemester = '".$_POST['kdstsemester']."'
			  AND nilai.kdmk = '".$_POST['kdmk']."'
			  AND mahasiswa.thnmasuk = '".$_POST['thnmasuk']."'"; */
		$this->rhlib->jsonFromQueryWLimit($q);
		
	}
	
	function get_addmhs(){
		$q = "select * from mahasiswa 
				where
			  kdprodi = '".$_POST['kdprodi']."'
			  AND thnmasuk ='".$_POST['thnmasuk']."'
			  AND nim not IN(SELECT mahasiswa.nim
			FROM
			  nilai
			LEFT JOIN mahasiswa
			ON mahasiswa.nim = nilai.nim
			WHERE
			  nilai.kdprodi = '".$_POST['kdprodi']."'
			  AND nilai.kdmk = '".$_POST['kdmk']."'
			  AND mahasiswa.thnmasuk = '".$_POST['thnmasuk']."')";
			  
			  /* $q = "select * from mahasiswa 
				where
			  kdprodi = '".$_POST['kdprodi']."'
			  AND thnmasuk ='".$_POST['thnmasuk']."'
			  AND nim not IN(SELECT mahasiswa.nim
			FROM
			  nilai
			LEFT JOIN mahasiswa
			ON mahasiswa.nim = nilai.nim
			WHERE
			  nilai.kdprodi = '".$_POST['kdprodi']."'
			  AND nilai.kdstsemester = '".$_POST['kdstsemester']."'
			  AND nilai.kdmk = '".$_POST['kdmk']."'
			  AND mahasiswa.thnmasuk = '".$_POST['thnmasuk']."')"; */
		$this->rhlib->jsonFromQueryWLimit($q);
		
	}
	
	function insert_nilai_bypass(){      // ISTRA
        $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]);
				
				$dataArray = array(
					'nim'=> ($rows2[0]=='null') ? null:$rows2[0],	
					'kdprodi'=> ($rows2[1]=='null') ? null:$rows2[1],					
					'kdstsemester'=> ($rows2[2]=='null') ? null:$rows2[2],					
					'kdmk'=> ($rows2[3]=='null') ? null:$rows2[3],
					'idstsetuju'=> 1,
					'idstambilmk'=> 1,					
				);
				$this->rhlib->insertRecord('nilai',$dataArray);
     
            }
        
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            $return["success"]=false;
            $return["message"]="Simpan Data gagal";
        }
        else
        {
            $this->db->trans_commit();
            $return["success"]=true;
            $return["message"]="Simpan Data Berhasil";
        }
        return $return["success"];
    }
	
	function update_nilai(){
		$idnilai = $_POST['idnilai'];
		$nilaihuruf = $_POST['nilaihuruf'];
		$bobotnilai = $_POST['bobotnilai'];
		
		//UPDATE
		$this->db->where('idnilai', $idnilai);
		
		$this->db->set('nilaihuruf', $nilaihuruf);
		$this->db->set('bobotnilai', $bobotnilai);
		$this->db->set('accnilaihuruf', $nilaihuruf);
		$this->db->set('accbobotnilai', $bobotnilai);
		
		$this->db->update('nilai'); 
        
		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_nilaimanual(){     
		$where['idnilai'] = $_POST['idnilai'];
		$ret = $this->rhlib->deleteRecord('nilai',$where);
        return $ret;
    }
	
	function get_stambilmk(){
		$q = "SELECT * FROM stambilmk";
		$this->jsonFromQuery($q);
	}
	
	function get_stsetuju(){
		$q = "SELECT * FROM stsetuju";
		$this->jsonFromQuery($q);
	}
	
	function update_smtambil(){
		$idnilai = $_POST['idnilai'];
		$kdstsemester = $_POST['kdstsemester'];
		
		//UPDATE
		$this->db->where('idnilai', $idnilai);
		$this->db->set('kdstsemester', $kdstsemester);
		$this->db->update('nilai'); 
        
		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_stambilmk(){
		$idnilai = $_POST['idnilai'];
		$idstambilmk = $_POST['idstambilmk'];
		
		//UPDATE
		$this->db->where('idnilai', $idnilai);
		$this->db->set('idstambilmk', $idstambilmk);
		$this->db->update('nilai'); 
        
		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_stsetuju(){
		$idnilai = $_POST['idnilai'];
		$idstsetuju = $_POST['idstsetuju'];
		
		//UPDATE
		$this->db->where('idnilai', $idnilai);
		$this->db->set('idstsetuju', $idstsetuju);
		$this->db->update('nilai'); 
        
		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 upload_data(){
		$ori_dir = 'resources/files/';
		if ($_FILES['f_excel']['name']) {
			move_uploaded_file($_FILES['f_excel']['tmp_name'], $ori_dir . $_FILES['f_excel']['name']);	
			
			$result["success"]=true;
			echo json_encode($result);
		} 
	}
	
	function get_import_nilai_trlnm(){
	
		include_once ( APPPATH."libraries/excel_reader2.php");	

		$file = $this->input->post("excel");
		
		$data = array();
	
	
			$data = new Spreadsheet_Excel_Reader('resources/files/'.$file);
			$baris = $data->rowcount($sheet_index=0);
	
				$build_array = array ("success"=>true,"results"=>$baris-1,"data"=>array());
		
				for ($i=2; $i<=$baris; $i++) {
					$nmmhs = $this->rhlib->get_par_val("mahasiswa","nmmhs","nim = '".$data->val($i, 5)."'");
					$nmprodi = $this->rhlib->get_par_val("prodi","nmprodi","kdprodi = '".$data->val($i, 4)."'");
					$nmthnakademik = $this->rhlib->get_par_val("v_tahunakademik","nmthnakademik","kdstsemester = '".$data->val($i, 1)."'");
					$nmmk = $this->rhlib->get_par_val("matakuliah","nmmkind","kdmk = '".$data->val($i, 6)."'");

					$cnilai = $this->rhlib->get_par_val("nilai","count(idnilai)",
										"nim = '".$data->val($i, 5)."' AND kdprodi = '".$data->val($i, 4)."' AND kdstsemester = '".$data->val($i, 1)."' AND kdmk = '".$data->val($i, 6)."'");
					
					$nilaihuruf = $data->val($i, 7);
					$bobotnilai = $data->val($i, 8);
					
					if ($nmmhs && $nmprodi && $nmthnakademik && $nmmk && $nilaihuruf && $bobotnilai) {
						if ($cnilai) {
							$keterangan = '<font color=red>Data Sudah Ada</font>';
						} else {
							$keterangan = '<font color=blue>Data Belum Ada</font>';
						}
					} else {
						$keterangan = '<font color=red>Data Tidak Lengkap</font>';
					}
					
					array_push($build_array["data"],array(
						'nim'=>$data->val($i, 5),
						'nmmhs'=>($nmmhs) ? $nmmhs:'<font color=red>Data Tidak Ditemukan</font>',
						'kdprodi'=>$data->val($i, 4),
						'nmprodi'=>($nmprodi) ? $nmprodi:'<font color=red>Data Tidak Ditemukan</font>',
						'kdstsemester'=>$data->val($i, 1),
						'nmthnakademik'=>($nmthnakademik) ? $nmthnakademik:'<font color=red>Data Tidak Ditemukan</font>',
						'kdmk'=>$data->val($i, 6),
						'nmmk'=>($nmmk) ? $nmmk:'<font color=red>Data Tidak Ditemukan</font>',
						'nilaihuruf'=>($nilaihuruf) ? $nilaihuruf:'<font color=red>Nilai Kosong</font>',
						'bobotnilai'=>($bobotnilai) ? substr($bobotnilai,0,4):'<font color=red>Nilai Kosong</font>',
						'keterangan'=>$keterangan,
                    ));
				}

				echo json_encode($build_array);

			unlink('resources/files/'.$file);

	}
	
	function import_nilai_trlnm(){      // ISTRA
        $par=$this->input->post("par");

        $this->db->trans_begin();
            $rows = explode(";",$par);
            $row_count = count($rows);
			$count = 0;
			if ($par) {
				for($ri=0;$ri<$row_count;$ri++){
					$rows2 = explode("x",$rows[$ri]);
					
					$dataArray = array(
						'nim'=> ($rows2[0]) ? $rows2[0]:null,	
						'kdprodi'=> ($rows2[1]) ? $rows2[1]:null,						
						'kdstsemester'=> ($rows2[2]) ? $rows2[2]:null,						
						'kdmk'=> ($rows2[3]) ? $rows2[3]:null,	
						'nilaihuruf'=> ($rows2[4]) ? $rows2[4]:null,	
						'bobotnilai'=> ($rows2[5]) ? $rows2[5]:null,		
						'idstsetuju'=> 1,
						'idstambilmk'=> 1,		
					);
					$insert = $this->rhlib->insertRecord('nilai',$dataArray);
					if ($insert) {
						$count++;
					}
		 
				}
			}
        
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            $return["success"]=false;
            $return["message"]="Simpan Data gagal";
			$return["count"]=$count;
        }
        else
        {
            $this->db->trans_commit();
            $return["success"]=true;
            $return["message"]="Simpan Data Berhasil";
			$return["count"]=$count;
        }
        echo json_encode($return);
    }
		
}
?>