<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class C_matkulabsennilaimhsdos extends Controller {
	var $stat;
	
	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
    }
	
	function retValOrNull($val){
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function get_stabsenmhs(){
		$q = "SELECT idstabsenmhs,kdstabsensimhs,nmstabsensimhs,
			CONCAT(nmstabsensimhs,' (',IFNULL(kdstabsensimhs,''),')')as stabsensimhs,
			deskripsi	
			FROM stabsenmhs
			UNION SELECT null,'-','-','-','-'";
		$this->rhlib->jsonFromQuery2($q);	
	}
	
	function get_matkuldos(){
		$kdstsemester = $_POST['kdstsemester'];
		$nidu = $_POST['nidu'];
			  
		$q = "SELECT a.kdstsemester
			 , a.nidu
			 , a.kdmk
			 , m.nmmkind
			 , s.jmlsks
			 , a.kdprodi
			 , p.nmprodi
			 , a.idkelas
			 , k.nmkelas
		FROM
		  absendos a
		JOIN struprogdet sd
		ON a.kdmk = sd.kdmk AND a.kdprodi = sd.kdprodi AND a.kdstsemester = sd.kdstsemester
		AND a.nidu = sd.nidu AND a.idkelas = sd.idkelas -- AND sd.idjnsdosen = 1
		LEFT JOIN dosenpengganti dp
		ON sd.idstruprogdet = dp.idstruprogdet
		AND dp.idstatus = 1
		LEFT JOIN matakuliah m
		ON a.kdmk = m.kdmk AND a.kdprodi = m.kdprodi
		LEFT JOIN struprog s
		ON a.kdmk = s.kdmk AND a.kdprodi = s.kdprodi AND a.kdstsemester = s.kdstsemester
		LEFT JOIN prodi p
		ON a.kdprodi = p.kdprodi
		LEFT JOIN kelas k
		ON a.idkelas = k.idkelas
		WHERE
		  a.kdstsemester = '$kdstsemester'
		  AND a.nidu = '$nidu'
		  AND dp.nidu IS NULL

		  UNION

		  SELECT sd1.kdstsemester
			 , dp1.nidu
			 , sd1.kdmk
			 , m1.nmmkind
			 , s1.jmlsks
			 , sd1.kdprodi
			 , p1.nmprodi
			 , sd1.idkelas
			 , k1.nmkelas
		FROM
		  dosenpengganti dp1
		LEFT JOIN struprogdet sd1
		ON dp1.idstruprogdet = sd1.idstruprogdet
		LEFT JOIN absendos a1
		ON a1.kdmk = sd1.kdmk AND a1.kdprodi = sd1.kdprodi AND a1.kdstsemester = sd1.kdstsemester
		AND a1.nidu = sd1.nidu AND a1.idkelas = sd1.idkelas AND sd1.idjnsdosen = 1
		LEFT JOIN matakuliah m1
		ON sd1.kdmk = m1.kdmk AND sd1.kdprodi = m1.kdprodi
		LEFT JOIN struprog s1
		ON sd1.kdmk = s1.kdmk AND sd1.kdprodi = s1.kdprodi AND sd1.kdstsemester = s1.kdstsemester
		LEFT JOIN prodi p1
		ON sd1.kdprodi = p1.kdprodi
		LEFT JOIN kelas k1
		ON sd1.idkelas = k1.idkelas
		WHERE
		  dp1.idstatus = 1
		  AND sd1.kdstsemester = '$kdstsemester'
		  AND dp1.nidu = '$nidu'";
			
		$this->rhlib->jsonFromQuery2($q);	
	}
	
	function get_absenmhs(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$idkelas = $_POST['idkelas'];
		
		$q = "SELECT a.*
					 , b.nmmhs
					 , b.kdjnskelamin
					 , b.thnmasuk
				FROM
				  absenmhs a
				LEFT JOIN mahasiswa b
				ON b.nim = a.nim
				LEFT JOIN nilai n
				ON a.kdmk = n.kdmk
				AND a.kdprodi = n.kdprodi
				AND a.kdstsemester = n.kdstsemester
				AND a.nidu = n.nidu
				AND a.nim = n.nim
				LEFT JOIN stmhssemester s
				ON a.nim = s.nim AND a.kdstsemester = s.kdstsemester
				WHERE
				  a.kdprodi = '$kdprodi'
				  AND a.kdstsemester = '$kdstsemester'
				  AND a.kdmk = '$kdmk'
				  AND a.idkelas = '$idkelas'
				  AND s.kdstaktivitasmhs = 'A'
				  AND n.idstsetuju=1
				ORDER BY a.nim";
				  
		$this->rhlib->jsonFromQuery2($q);	
	}
	
	function update_absenmhs(){
		$KEDUA= $this->load->database('second', TRUE);
		$field = $_POST['field'];
		$value = ($_POST['value']) ? $_POST['value']:null;
		
		//UPDATE
		$KEDUA->where('kdprodi', $_POST['kdprodi']);
		$KEDUA->where('kdstsemester', $_POST['kdstsemester']);
		$KEDUA->where('kdmk', $_POST['kdmk']);
		$KEDUA->where('nidu', $_POST['nidu']);
		$KEDUA->where('nim', $_POST['nim']);
		$KEDUA->where('idkelas', $_POST['idkelas']);
		$KEDUA->set($field, $value);
		$KEDUA->update('absenmhs'); 
		
		if($KEDUA->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
	}
	
	function update_absenmhsAll(){	
		$KEDUA= $this->load->database('second', TRUE);
		//UPDATE
		$KEDUA->where('kdprodi', $_POST['kdprodi']);
		$KEDUA->where('kdstsemester', $_POST['kdstsemester']);
		$KEDUA->where('kdmk', $_POST['kdmk']);
		$KEDUA->where('nidu', $_POST['nidu']);
		$KEDUA->where('nim', $_POST['nim']);
		$KEDUA->where('idkelas', $_POST['idkelas']);
		
		for($i=0; $i<16; $i++){
			$j = $i + 1;
			$valabsenmhs = ($_POST['p'.$j]) ? $_POST['p'.$j]:null;
			$KEDUA->set('p'.$j, $valabsenmhs);
		}
		
		$KEDUA->update('absenmhs'); 
		if($KEDUA->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
	}
	
	//============================================
	
	function get_bonilaiByProdiSmt(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT * FROM setbonilai
			WHERE kdprodi='$kdprodi' 
			and kdstsemester = '$kdstsemester'";
		$this->rhlib->jsonFromQuery2($q);	
	}
	
	function get_nilaimahasiswa(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdmk = $_POST['kdmk'];
		$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.idkelas='".$idkelas."'
				and a.idstsetuju=1
				and s.kdstaktivitasmhs = 'A'
				ORDER BY b.thnmasuk, mid(a.nim, 8, 1), right(a.nim,4)";
		$this->rhlib->jsonFromQuery2($q);	
	}
	
	function update_nilai(){
		$KEDUA= $this->load->database('second', TRUE);
		
		$idnilai = $_POST['idnilai'];
		$nilaihuruf = $_POST['nilaihuruf'];
		$bobotnilai = $_POST['bobotnilai'];
		
		$lastlogin = $this->session->userdata("user_id1unla");
		$lastdatetime = date("Y-m-d H:i:s");
		$lastip = $_SERVER['REMOTE_ADDR'];
		$lastnmcom = $_POST['nmcom'];
		
		//UPDATE
		$KEDUA->where('idnilai', $idnilai);
		$KEDUA->set('nilaihuruf', $nilaihuruf);
		$KEDUA->set('bobotnilai', $bobotnilai);
		$KEDUA->set('accnilaihuruf', $nilaihuruf);
		$KEDUA->set('accbobotnilai', $bobotnilai);
		$KEDUA->set('lastlogin', $lastlogin);
		$KEDUA->set('lastdatetime', $lastdatetime);
		$KEDUA->set('lastip', $lastip);
		$KEDUA->set('lastnmcom', $lastnmcom);
		//Penambahan 1 - RZ
		$KEDUA->set('firstcreated_by', $lastlogin);
		$KEDUA->set('firstcreated_at', $lastdatetime);
		$KEDUA->set('firstcreated_ip', $lastip);
		$KEDUA->set('firstcreated_com', $lastnmcom);
		$KEDUA->update('nilai'); 
        
		if($KEDUA->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
	}
	
	function update_nilaisp(){
		$KEDUA= $this->load->database('second', TRUE);
		
		$idnilai = $_POST['idnilai'];
		$nilaihurufsp = $_POST['nilaihurufsp'];
		$bobotnilaisp = $_POST['bobotnilaisp'];
		
		$lastloginsp = $this->session->userdata("user_id1unla");
		$lastdatetimesp = date("Y-m-d H:i:s");
		$lastipsp = $_SERVER['REMOTE_ADDR'];
		$lastnmcomsp = $_POST['nmcom'];
		
		$nilaihuruf;
		$bobotnilai;
		
		$query = $KEDUA->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
		$KEDUA->where('idnilai', $idnilai);
		$KEDUA->set('nilaihurufsp', $nilaihurufsp);
		$KEDUA->set('bobotnilaisp', $bobotnilaisp);
		$KEDUA->set('accnilaihuruf', $accnilaihuruf);
		$KEDUA->set('accbobotnilai', $accbobotnilai);
		$KEDUA->set('lastloginsp', $lastloginsp);
		$KEDUA->set('lastdatetimesp', $lastdatetimesp);
		$KEDUA->set('lastipsp', $lastipsp);
		$KEDUA->set('lastnmcomsp', $lastnmcomsp);
		$KEDUA->update('nilai'); 
		if($KEDUA->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
	}

}
?>