<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Konversi_Controller extends Controller {
	var $stat;
	
	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
    }
	
	function retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function deleteRecord($table,$where){
		return $this->rhlib->deleteRecord($table,$where);
	}
	
	function autoNumber($column, $tbl) {
        $q = "SELECT max(" . $column . ")+1 as max FROM " . $tbl . "";
        $query = $this->db->query($q);
        $max = '';

        if ($query->num_rows() != 0) {
            $row = $query->row();
            $max = $row->max;
        }
        if ($max == null) {
            $max = 1;
        }
        return $max;
    }
	
	function get_jenpdkpendaftar_public(){ //Program Studi lokal
		$q="SELECT * FROM jenpdkpendaftar";        
		$this->rhlib->jsonFromQuery2($q);
    }
	
	function get_count_konversi($nim){
        $q = "SELECT * FROM konversi WHERE nim='$nim' and idstkonversi=1" ;
        $query  = $this->db->query($q);
        $count = $query->num_rows();    

        return $count;
    }
	
	function get_lookup_mhs_pindahan(){
		$KEDUA = $this->load->database('second', TRUE);
		$searchBy = "";		
		if(isset($_POST['key'])){
			$key = $_POST['key'];
			if($key!=null && $key!=''){
				$val = "";
				if(isset($_POST['value']))
					$val = $_POST['value'];
				$searchBy = " WHERE ".$key." like '%".$val."%'";
			}
		}
		
		//$q = $KEDUA->query("SELECT * FROM v_pendaftar_pindahan".$searchBy);
		$q = $this->db->query("SELECT * FROM (SELECT m.nopmb
				 , m.nmmhs AS nama
				 , m.kdptasal
				 , m.nmptasal
				 , m.kdprodiasal AS kdprodi
				 , m.kdprodi AS kdprodi1
				 , m.kdprodi AS kdprodi2
				 , tp.nmpsttbpst AS nmprodi
				 , tk.kdkodtbkod AS idjenpdkpendaftar
				 , tk.nmkodtbkod AS nmjenpdkpendaftar
				 , m.noijazahasal
				 , m.tglijazahasal
				 , m.nim
				 , m.thnmasuk
			FROM
			  mahasiswa m
			LEFT JOIN tbpst tp
			ON m.kdprodiasal = tp.kdpsttbpst
			LEFT JOIN tbkod tk
			ON tp.kdjentbpst = tk.kdkodtbkod AND tk.kdapltbkod = '01'
			WHERE
			  kdstawalmhs = 'P' AND kdstaktivitasmhs= 'A') M".$searchBy);
       
        $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $ttl = count($data);
        
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

		foreach($data as $row) {
			if ($this->get_count_konversi($row->nim)==0) {
				array_push($build_array["data"],array(
					'nopmb'=>$row->nopmb,
					'nama'=>$row->nama,
					'kdptasal'=>$row->kdptasal,
					'nmptasal'=>$row->nmptasal,
					'kdprodi'=>$row->kdprodi,
					'kdprodi1'=>$row->kdprodi1,
					'kdprodi2'=>$row->kdprodi2,
					'nmprodi'=>$row->nmprodi,	
					'idjenpdkpendaftar'=>$row->idjenpdkpendaftar,
					'nmjenpdkpendaftar'=>$row->nmjenpdkpendaftar,
					'noijazalasal'=>$row->noijazalasal,	
					'tglijazahasal'=>$row->tglijazahasal,
					'nim'=>$row->nim,
					'thnmasuk'=>$row->thnmasuk,
				));
			}
        }
            echo json_encode($build_array);
	}
	
	
	function get_v_konversi(){
		$kdprodi = $_POST['kdprodi'];
		$tahun = $_POST['tahun'];
		
		$q = "SELECT * FROM v_konversi where kdprodi='".$kdprodi."' and tahun='".$tahun."'";
				
		$this->rhlib->jsonFromQueryWLimit($q);
	}
	
	function get_prodipilihan(){ //Program Studi lokal

		$q="SELECT a.kdprodi
		 , ifnull(
		   concat(ifnull(rtrim(a.nourutprodi), '-'), '. ', a.nmprodi, ' (', ifnull(a.nmjenjangstudi, '-'), ')')
		   , nmprodi) AS lnmprodi
		 , a.nidu
		 , b.nmdosdgngelar
		FROM
		  v_prodi a
		LEFT JOIN dosen b ON b.nidu=a.nidu
		WHERE
		  a.nmprodi <> '-'
		  AND a.kdjenjangstudi <> 'B'
		  AND a.kdprodi IN('".$this->input->post("kdprodi1")."','".$this->input->post("kdprodi2")."')
		ORDER BY
		  a.nourutprodi
		, a.kdprodi";        
				$this->rhlib->jsonFromQuery($q);
    }

	/* function get_lookup_mhs_pindahan(){
		$q = "SELECT * FROM v_pendaftar_pindahan";
				
		$this->rhlib->jsonFromQueryWLimit2($q);
	} */
	
	function get_mhs_nim(){
		$nopmb= $this->input->post("nopmb"); 
		$q = "SELECT * FROM mahasiswa where nopmb='".$nopmb."'";
				
		$query  = $this->db->query($q);
                    
        if ($query->num_rows() != 0)
        {
			$data = $query->row();
            $row ["nim"] = $data->nim;
			$row ["nmmhs"] = $data->nmmhs;
            echo json_encode($row);
        } else {
			echo json_encode(null);
		}

	}
	
	function get_matkul_kurikulum(){
		$idkn = $_POST['idkn'];
		$kdprodi = $_POST['kdprodi'];
		$thnmasuk = $_POST['thnmasuk'];
		$nim = $_POST['nim'];

		$q = "SELECT a.*
				 , b.nmmkind
				 , concat('SEMESTER ', a.idsemester) AS idsemesternya
				 , if(((SELECT concat(kdmk, '-', kdprodi) AS kdmkprodi
						FROM
						  konversidet
						WHERE idkn='".$idkn."' AND kdmk = a.kdmk AND kdprodi = a.kdprodi) = concat(a.kdmk, '-', a.kdprodi)), 1, 0) AS pilihmk
				, (SELECT nmmkasal FROM konversidet WHERE idkn='".$idkn."' AND kdmk = a.kdmk AND kdprodi = a.kdprodi) as nmmkasal
				, (SELECT sksasal FROM konversidet WHERE idkn='".$idkn."' AND kdmk = a.kdmk AND kdprodi = a.kdprodi) as sksasal
				, (SELECT nilaiasal FROM konversidet WHERE idkn='".$idkn."' AND kdmk = a.kdmk AND kdprodi = a.kdprodi) as nilaiasal
				, (SELECT nilaidiakui FROM konversidet WHERE idkn='".$idkn."' AND kdmk = a.kdmk AND kdprodi = a.kdprodi) as nilaidiakui
				, (SELECT count(kdmk) FROM nilai WHERE kdmk = a.kdmk AND kdprodi = a.kdprodi AND nim='".$nim."') as countnilai
				, 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->jsonFromQuery($q);
		
	}
	
	function delete_konversidet(){     
        $where['idkn'] = $_POST['idkn'];
        $where['kdprodi'] = $_POST['kdprodi'];
		$where['kdmk'] = $_POST['kdmk'];
		$where['kdstsemester'] = $_POST['kdstsemester'];
		$ret = $this->deleteRecord('konversidet',$where);
        return $ret;
    }
	
	function insert_konversi() {
		$dataArray = array(
            'idkn' => $this->autoNumber('idkn', 'konversi'),
            'tglkn' => $this->retValOrNull($_POST['tglkn']),
			'kdprodi' => $this->retValOrNull($_POST['kdprodi']),
            'tahun' => $this->retValOrNull($_POST['tahun']),
            'nim' => $this->retValOrNull($_POST['nim']),
            'idstkonversi' => $this->retValOrNull($_POST['idstkonversi']),
            'userid' => $this->retValOrNull($_POST['userid']),
            'nopmb' => $this->retValOrNull($_POST['nopmb']),
			
			'nmpendaftar' => $this->retValOrNull($_POST['nmpendaftar']),
			'kdptasal' => $this->retValOrNull($_POST['kdptasal']),
            'kdprodiasal' => $this->retValOrNull($_POST['kdprodiasal']),
            'kdjenjangstudiasal' => $this->retValOrNull($_POST['kdjenjangstudiasal']),
            'noijazahasal' => $this->retValOrNull($_POST['noijazahasal']),
            'tglijazahasal' => $this->retValOrNull($_POST['tglijazahasal']),
            'pengkonversi' => $this->retValOrNull($_POST['pengkonversi']),
			
			'acc' => $this->retValOrNull($_POST['acc']),
			'keterangan' => $this->retValOrNull($_POST['keterangan'])
        );
		
		$ret = $this->rhlib->insertRecord('konversi',$dataArray);
        if ($ret) {
			$return["idkn"]=$dataArray['idkn'];
			$return["success"]=true;
			$return["message"]="Simpan Data Berhasil";
		} else {
			$return["idkn"]=null;
			$return["success"]=false;
			$return["message"]="Simpan Data gagal";
		}
		echo json_encode($return);
    }

	function update_konversi(){
		$dataArray = array(
			'keterangan'=>$this->retValOrNull($_POST['keterangan'])
        );

		//UPDATE
		$this->db->where('idkn', $_POST['idkn']);
		$this->db->update('konversi', $dataArray);
		if($this->db->affected_rows()){ 
			$return["idkn"]=$_POST['idkn'];
			$return["success"]=true;
			$return["message"]="Ubah Data Berhasil";
        }else{
			$return["idkn"]=null;
			$return["success"]=false;
			$return["message"]="Ubah Data gagal";
        }
        echo json_encode($return);
    }
	
	function exec_konversi_det(){      // ISTRA
        $parmk=$this->input->post("parmk");

        $this->db->trans_begin();
            $rows = explode(";",$parmk);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
					
				$this->db->query("CALL SP_simpan_konversidet (?,?,?,?,?,?,?,?,?)",
				array($rows2[0],$rows2[1],$rows2[2],$rows2[3],$rows2[4],$rows2[5],$rows2[6],$rows2[7],$rows2[8]));
     
            }
        
        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 batal_konversi(){
		$dataArray = array(
			'idstkonversi'=>$_POST['idstkonversi'],
        );

		//UPDATE
		$this->db->where('idkn', $_POST['idkn']);
		$this->db->update('konversi', $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 insert_konversi_nilai(){      // ISTRA
        $nim = $this->input->post("nim");

        $this->db->trans_begin();
	
		$this->db->query("CALL sp_InsertKonversi_to_Nilai (?)",
		array($nim));

        
        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_bayar_konversi(){
		$nim = $this->input->post("nim");
		
        $q = $this->db->query("SELECT a.*, b.nmcarabyr
				FROM kuitansi a
				LEFT JOIN carabyr b ON b.idcarabyr=a.idcarabyr
				LEFT JOIN jbiaya c on c.idjnsbiaya=a.idjnsbiaya
				WHERE (c.idjnsbiaya = 5 OR c.idjnsbiaya = 11)
				and a.idstkuitansi = 1
				and a.nim = '".$nim."'");

       $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $ttl = count($data);
        
        //======================================================================
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

      foreach($data as $row) {
            array_push($build_array["data"],array(
               
                'nim'=>$row->nim,
				'nokuitansi'=>$row->nokuitansi,
				'tglkuitansi'=>$row->tglkuitansi,
				'idcarabyr'=>$row->idcarabyr,
				'nmcarabyr'=>$row->nmcarabyr,
				'noref'=>$row->noref,
				'useridinput'=>$row->useridinput,
				'jmlbayar'=>$row->jmlbayar,
				
               
                            ));
        }
        echo json_encode($build_array);
	}

	
}
?>