<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class C_cetak_kartu_ujian extends Controller {

  /*membuka koneksi database untuk d_mahasiswa*/
    public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
        
    }
       
    function nm_field($column,$tbl,$whereb, $wherea){
        $q = "SELECT ".$column." as nm FROM ".$tbl." where ".$whereb." = '".$wherea."' " ;
        $query  = $this->db->query($q);
        $nm= ''; 
                    
        if ($query->num_rows() != 0)
        {
            $row = $query->row();
            $nm=$row->nm;
        }
        return $nm;
    }  
	
	function getJsonFromSP($sql, $parameters){
	$KEDUA= $this->load->database('second', TRUE);
		$query = $KEDUA->query($sql, $parameters);
        $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 form_mhs(){ //ISTRA
        $KEDUA= $this->load->database('second', TRUE);  
        $userid     = $this->input->post("nim");
        $KEDUA->select("*");
        $KEDUA->from("vv_mahasiswa_profile2"); 
        
        if($userid!=''){
            $where['nim']=$userid;
            $KEDUA->where($where);
        }
   
        $q = $KEDUA->get(); 
       
        if ($q->num_rows() > 0)
        {
            $row = $q->row();

            $kdprodi=$row->kdprodi;
			$idjnskls=$row->idjnskls;
            $tahunmsk=$row->thnmasuk; //in beda
            $kdstsemester=$row->kdstsemester;

        } else {
		
            $kdprodi="-";
			$idjnskls="-";
            $tahunmsk="-";
            $kdstsemester="-";
		}
           
       echo '{"kdprodi":"'.$kdprodi.'","tahunmsk":"'.$tahunmsk.'","kdstsemester":"'.$kdstsemester.'","idjnskls":"'.$idjnskls.'"}';
    }
	
	
	function cek_spp(){
		
		$KEDUA= $this->load->database('second', TRUE);
		
		$nim     = $this->input->post("nim");
		$kdprodi     = $this->input->post("kdprodi");
		$idjnskls     = $this->input->post("idjnskls");
		$thnmasuk     = $this->input->post("thnmasuk");
		$kdstsemester     = $this->input->post("kdstsemester");
		
		$sql = "SELECT
			  kuitansi.nim
			 , ifnull(diskonmhs.diskon,0) AS diskon
			 , setbiaya.biaya AS biaya
			 , sum(kuitansi.jmlbayar) AS jmlbayar
			 , if(isnull(((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)), ((sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`) * 100), ((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)) AS persenbayar
			 , if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) AS `sisa`
			 , if(isnull(((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)), ((1 - (sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`)) * 100), ((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)) AS `persensisa`
			 , if((if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) > 0), 'Belum Lunas', 'Lunas') AS `statuslunas`
			 , jadwalkrs.mindpputs
			 , jadwalkrs.mindppuas
		FROM
		  kuitansi
		LEFT JOIN mahasiswa
		ON kuitansi.nim = mahasiswa.nim
		LEFT JOIN v_tahunakademik
		ON kuitansi.kdstsemester = v_tahunakademik.kdstsemester
		LEFT JOIN setbiaya
		ON kuitansi.idjnsbiaya = setbiaya.idjnsbiaya AND kuitansi.kdstsemester = setbiaya.kdstsemester
		LEFT JOIN jbiaya
		ON setbiaya.idjnsbiaya = jbiaya.idjnsbiaya
		LEFT JOIN diskonmhs
		ON kuitansi.nim = diskonmhs.nim AND kuitansi.kdstsemester = diskonmhs.kdstsemester
		LEFT JOIN jadwalkrs
		ON jadwalkrs.kdprodi = '$kdprodi' AND jadwalkrs.kdstsemester = '$kdstsemester'

		WHERE
		  kuitansi.nim = '$nim'
		  AND kuitansi.idstkuitansi = '1'
		  AND setbiaya.kdprodi = '$kdprodi'
		  AND setbiaya.idjnskls = '$idjnskls'
		  AND setbiaya.tahunmsk = '$thnmasuk'
		  AND setbiaya.idjnsbiaya = '1'
		  AND kuitansi.kdstsemester = '$kdstsemester'
		GROUP BY
		  kuitansi.kdstsemester;";

  
	$query  = $KEDUA->query($sql);
		
		//$data = array();
    if ($query->num_rows() > 0) {
            $data = $query->row();
                
        $ttl = count($data);
        $build_array = $data;

        
        echo json_encode($build_array);
	} else {
		echo json_encode(null);
	}
	
	}
	
	function cek_dpk(){
		if (isset($_POST['nim']) && isset($_POST['thmasuk']) && isset($_POST['kdprodi']) && isset($_POST['kdstsemester'])){
			$nim = $_POST['nim'];
			$thmasuk = $_POST['thmasuk'];
			$kdprodi = $_POST['kdprodi'];
			$kdstsemester = $_POST['kdstsemester'];
			
			$sql = "CALL sp_getDpk_Validasi_UTS_UAS(?)";
			$parameters = array($nim);
			$this->getJsonFromSP($sql, $parameters);
			
			
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_data_cekdpk(){
		$KEDUA= $this->load->database('second', TRUE);
		$nim = $_POST['nim'];
		$thmasuk = $_POST['thnmasuk'];
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
        $query  = $KEDUA->query("CALL sp_getDpk_Validasi_UTS_UAS (?)",array($nim));
		
		if($query->num_rows() > 0) {
			$data = $query->row();
			$build_array = $data;
			echo json_encode($build_array);
		}else{
			echo json_encode(null);
		}
	}

	
}