<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Nilai_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 insertRecord($table,$dataArray){
		return $this->rhlib->insertRecord($table,$dataArray);
	}	
	function deleteRecord($table,$where){
		return $this->rhlib->deleteRecord($table,$where);
	}
	
	function retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function retValOrZero($val){
		$val = ($val=='' || $val==null)? 0 : $val;
		return $val;
	}
		
	function get_bonilaiByProdiSmt(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT * FROM setbonilai
			WHERE kdprodi='$kdprodi' 
			and kdstsemester = '$kdstsemester'";
		$this->jsonFromQuery($q);
	}
	
	function get_nilaimahasiswa(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idkelas = $_POST['idkelas'];
		
		$q = "SELECT a.idnilai, a.kdprodi,a.kdstsemester, 
				a.kdmk, a.nidu, a.nim, b.nmmhs,
				b.kdjnskelamin,b.thnmasuk,
				b.kdstaktivitasmhs,c.nmstawalmhs,
				b.idklsmhs,d.nmklsmhs,
				a.nilaihuruf, a.bobotnilai,
				a.klsparalel, a.nilaihurufsp, a.bobotnilaisp
				FROM nilai a
				LEFT JOIN mahasiswa b ON b.nim=a.nim
				LEFT JOIN (SELECT kdkodtbkod as kode, nmkodtbkod as nmstawalmhs 
					FROM tbkod WHERE kdapltbkod='06')c
					ON c.kode=b.kdstaktivitasmhs
				LEFT JOIN klsmhs d ON d.idklsmhs=b.idklsmhs
				JOIN absenmhs e
				ON a.kdmk = e.kdmk AND a.kdprodi = e.kdprodi AND a.kdstsemester = e.kdstsemester AND a.nidu = e.nidu AND a.nim = e.nim
				LEFT JOIN stmhssemester s
				ON e.nim = s.nim AND e.kdstsemester = s.kdstsemester
				WHERE e.kdprodi='".$kdprodi."'
				and e.kdstsemester='".$kdstsemester."'
				and e.kdmk='".$kdmk."'
				-- and e.nidu='".$nidu."'
				and e.idkelas='".$idkelas."'
				and a.idstsetuju=1
				and s.kdstaktivitasmhs = 'A'
				ORDER BY b.thnmasuk, a.nim"; 
				//mid(a.nim, 8, 1), right(a.nim,4)";
		$this->jsonFromQueryWLimit($q);
	}
	
	function update_nilai(){
		$idnilai = $_POST['idnilai'];
		$nilaihuruf = $_POST['nilaihuruf'];
		$bobotnilai = $_POST['bobotnilai'];
		
		$lastlogin = $this->session->userdata("user_idunlaprivate");
		$lastdatetime = date("Y-m-d H:i:s");
		$lastip = $_SERVER['REMOTE_ADDR'];
		$lastnmcom = $_POST['nmcom'];
        /* $nilaihurufsp;
		$bobotnilaisp;
		
		$query = $this->db->query('SELECT * FROM nilai WHERE idnilai ="'.$idnilai.'"');
		foreach ($query->result() as $row){
		$nilaihurufsp = $row->nilaihurufsp;
		$bobotnilaisp = $row->bobotnilaisp;
		}
		//FILTER NILAI ACC
		if ($bobotnilai > $bobotnilaisp){
			$accnilaihuruf = $nilaihuruf;
			$accbobotnilai = $bobotnilai;
		}elseif ($bobotnilai < $bobotnilaisp){
			$accnilaihuruf = $nilaihurufsp;
			$accbobotnilai = $bobotnilaisp;
		} */
		
		//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->set('lastlogin', $lastlogin);
		$this->db->set('lastdatetime', $lastdatetime);
		$this->db->set('lastip', $lastip);
		$this->db->set('lastnmcom', $lastnmcom);
		$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_nilaisp(){
		$idnilai = $_POST['idnilai'];
		$nilaihurufsp = $_POST['nilaihurufsp'];
		$bobotnilaisp = $_POST['bobotnilaisp'];
		
		$lastloginsp = $this->session->userdata("user_idunlaprivate");
		$lastdatetimesp = date("Y-m-d H:i:s");
		$lastipsp = $_SERVER['REMOTE_ADDR'];
		$lastnmcomsp = $_POST['nmcom'];
		
		$nilaihuruf;
		$bobotnilai;
		
		$query = $this->db->query('SELECT * FROM nilai WHERE idnilai ="'.$idnilai.'"');
		foreach ($query->result() as $row){
		$nilaihuruf = $row->nilaihuruf;
		$bobotnilai = $row->bobotnilai;
		}
		//FILTER NILAI ACC
		if ($bobotnilaisp > $bobotnilai){
			$accnilaihuruf = $nilaihurufsp;
			$accbobotnilai = $bobotnilaisp;
		}elseif ($bobotnilaisp < $bobotnilai){
			$accnilaihuruf = $nilaihuruf;
			$accbobotnilai = $bobotnilai;
		}
		
		//UPDATE
		$this->db->where('idnilai', $idnilai);
		$this->db->set('nilaihurufsp', $nilaihurufsp);
		$this->db->set('bobotnilaisp', $bobotnilaisp);
		$this->db->set('accnilaihuruf', $accnilaihuruf);
		$this->db->set('accbobotnilai', $accbobotnilai);
		$this->db->set('lastloginsp', $lastloginsp);
		$this->db->set('lastdatetimesp', $lastdatetimesp);
		$this->db->set('lastipsp', $lastipsp);
		$this->db->set('lastnmcomsp', $lastnmcomsp);
		$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_klsparalel(){
		$idnilai = $_POST['idnilai'];
		$klsparalel = $_POST['klsparalel'];
		
		//UPDATE
		$this->db->where('idnilai', $idnilai);
		$this->db->set('klsparalel', $klsparalel);
		$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 get_mahasiswa_addnilai(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idkelas = $_POST['idkelas'];
		
		$q = "SELECT * FROM v_stmhssemester 
		where kdstsemester='$kdstsemester' 
		and kdstaktivitasmhs='A' 
		and idkelas='$idkelas' 
		and kdprodi='$kdprodi' 
		and nim not IN
		  (select n.nim from nilai n
			JOIN absenmhs a
				ON n.kdmk = a.kdmk AND n.kdprodi = a.kdprodi 
			AND n.kdstsemester = a.kdstsemester 
			AND n.nidu = a.nidu 
			AND n.nim = a.nim
			WHERE a.kdprodi='$kdprodi'
						and a.kdstsemester='$kdstsemester'
						and a.kdmk='$kdmk'
						and a.nidu='$nidu'
						and a.idkelas='$idkelas'
						and n.idstsetuju=1)";

		$this->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]);
					
				$this->db->query("CALL sp_bypassnilai (?,?,?,?,?,?)",
				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"]="Simpan Data gagal";
        }
        else
        {
            $this->db->trans_commit();
            $return["success"]=true;
            $return["message"]="Simpan Data Berhasil";
        }
        return $return["success"];
    }
	
	function get_nilaimahasiswa_susulan(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$nidu = $_POST['nidu'];
		$idkelas = $_POST['idkelas'];
		
		$q = "SELECT a.idnilai, a.kdprodi,a.kdstsemester, 
				a.kdmk, a.nidu, a.nim, b.nmmhs,
				b.kdjnskelamin,b.thnmasuk,
				b.kdstaktivitasmhs,c.nmstawalmhs,
				b.idklsmhs,d.nmklsmhs,
				a.nilaihuruf, a.bobotnilai,
				a.klsparalel, a.nilaihurufsp, a.bobotnilaisp
				FROM nilai a
				LEFT JOIN mahasiswa b ON b.nim=a.nim
				LEFT JOIN (SELECT kdkodtbkod as kode, nmkodtbkod as nmstawalmhs 
					FROM tbkod WHERE kdapltbkod='06')c
					ON c.kode=b.kdstaktivitasmhs
				LEFT JOIN klsmhs d ON d.idklsmhs=b.idklsmhs
				JOIN absenmhs e
				ON a.kdmk = e.kdmk AND a.kdprodi = e.kdprodi AND a.kdstsemester = e.kdstsemester AND a.nidu = e.nidu AND a.nim = e.nim
				LEFT JOIN stmhssemester s
				ON e.nim = s.nim AND e.kdstsemester = s.kdstsemester
				WHERE e.kdprodi='".$kdprodi."'
				and e.kdstsemester='".$kdstsemester."'
				and e.kdmk='".$kdmk."'
				-- and e.nidu='".$nidu."'
				and e.idkelas='".$idkelas."'
				and a.idstsetuju=1
				and s.kdstaktivitasmhs = 'A'
				ORDER BY b.thnmasuk, mid(a.nim, 8, 1), right(a.nim,4)";
		$this->jsonFromQuery($q);
	}
	
	function delete_insert_ujian_susulan(){
        $this->db->trans_begin();
        $this->db->query("DELETE FROM mhsujiansusulantemp
				where ipaddr='".$_SERVER['REMOTE_ADDR']."' 
				AND userid='".$this->session->userdata("user_idunlaprivate")."'
				AND idjnsujian='".$_POST['idjnsujianx']."'");
       
         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";
			$this->insert_temp_ujian_susulan();
        }
        return $return;
    }
	
	function insert_temp_ujian_susulan(){      // 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]);
					
				$this->db->query("CALL sp_addmhsujiansusulan (?,?,?,?)",
				array($rows2[0],$rows2[1],$this->session->userdata("user_idunlaprivate"),$_SERVER['REMOTE_ADDR']));
     
            }
        
        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"];
    }
	
}
?>