<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Data_Controller extends Controller {
	var $stat;

    public function __construct()
    {
        parent::Controller();
    }

    // START HELPER ===========================================================    
    function getData($q) {
		$query  = $this->db->query($q);
        $data = array();
        if ($query->num_rows() > 0) {
            $data = $query->result();
        }        
        $ttl = count($data);
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

        if($ttl>0){
            $build_array["data"]=$data;
        }
        echo json_encode($build_array);
	}
	
	function getDataPrivate($q) {
		$KEDUA  = $this->load->database('second', TRUE);
		$query  = $KEDUA->query($q);
        $data = array();
        if ($query->num_rows() > 0) {
            $data = $query->result();
        }        
        $ttl = count($data);
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

        if($ttl>0){
            $build_array["data"]=$data;
        }
        echo json_encode($build_array);
	}
	
	function get_gender(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama FROM tbkod 
				WHERE kdapltbkod='08'";
		$this->getData($q);	
	}
	
	function get_religion(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama ,
				CASE WHEN kdkodtbkod='I' THEN '1' 
					WHEN kdkodtbkod='K' THEN '2'
					WHEN kdkodtbkod='P' THEN '3'
					WHEN kdkodtbkod='H' THEN '4'
					WHEN kdkodtbkod='B' THEN '5'
					ELSE '9'
				END as nourut
				FROM tbkod WHERE kdapltbkod='51' ORDER BY nourut";
		$this->getData($q);	
	}
	
	function get_bloodtype(){
		$q = "SELECT idgoldarah as id, kdgoldarah as kode, nmgoldarah as nama FROM goldarah 
				WHERE idgoldarah<>''";
		$this->getData($q);	
	}
	
	function get_nationality(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama FROM tbkod 
			WHERE kdapltbkod='50'";
		$this->getData($q);	
	}
	
	function get_city(){ //KOTA
        $q="SELECT CONCAT(rtrim(kdprotbpro),'.',ltrim(kdkabtbpro)) as kdkota, 
			nmkabtbpro as nmkota, nmprotbpro as nmprov 
			from tbpro where kdkabtbpro <> ''";        
        $this->getData($q);		
    }
	
	function get_prov(){ //PROPINSI
        $q="SELECT DISTINCT kdprotbpro as kdprov, nmprotbpro as nmprov 
		from tbpro where kdprotbpro <> ''";        
        $this->getData($q);
    }
    
	function get_parentjob(){ //
        $q="SELECT idpekerjaanortu as id,kdpekerjaanortu as kode,
		nmpekerjaanortu as nama, deskripsi 
		from pekerjaanortu where idpekerjaanortu <> ''";        
        $this->getData($q);
    }
	
	function get_parentedu(){ //
        $q="SELECT idpendidikanortu as id, kdpendidikanortu as kode,
		nmpendidikanortu as nama, deskripsi 
		from pendidikanortu where idpendidikanortu <> ''";        
        $this->getData($q);
    }
	
	function get_univ(){ //P.T.
        $q="SELECT kdptitbpti as kdpt, nmptitbpti as nmpt, kotaatbpti as kota from tbpti where kdptitbpti <> ''";        
        $this->getData($q);
    }
	
	function get_prodi(){ //Program Studi indonesia
        $q="SELECT kdpsttbpst as kdprodi, nmpsttbpst as nmprodi, nomortbpst as nomor from tbpst where kdpsttbpst <> ''";        
        $this->getData($q);
    }
	
	function get_prodireg(){ //Program Studi lokal
        $q="SELECT kdprodi, nmprodi, CONCAT(RTRIM(kdprodi),'. ',nmprodi) as lnmprodi,
		kdjenjangstudi, tglawalberdiri, email, noskakreditasi, tglskakreditasi, notelpprodi
		FROM prodi where kdprodi <> '' AND kdjenjangstudi <> 'B'";        
        $this->getData($q);
    }
	
	function get_dosen(){ //Dosen/Promotor
        $q="SELECT nidn as iddosen , nmdosdgngelar as nmdosen from dosen where nidn <> ''";        
        $this->getData($q);
    }
	
	function get_stsmt(){ //status smester
        $q="SELECT kdstsemester,kdstsemester as kdsmt, nmthnakademik as nmsmt
		from v_tahunakademik where kdstsemester <> ''";        
        $this->getData($q);
    }
	
	function get_stsmt_private(){ //status smester
        $q="SELECT a.kdstsemester, a.idthnakademik, a.idjnssemester as idjsmt, 
			a.idstatus, a.deskripsi, stpmb,			
			b.nmthnakademik,
			c.nmjnssemester,
			CONCAT(b.nmthnakademik, ' - ', c.nmjnssemester) as nmsmt
			FROM stsemester a 
			LEFT JOIN thnakademik b ON b.idthnakademik=a.idthnakademik
			LEFT JOIN jsemester c ON c.idjnssemester=a.idjnssemester
			WHERE a.kdstsemester <> '' and a.idthnakademik<>''
			ORDER BY a.kdstsemester DESC";        
        $this->getDataPrivate($q);
    }
	
	function get_smester(){ //status smester
        $q="SELECT *, concat(nmsemester,'/ ',kdsemester) as semester FROM semester WHERE idjnssemester 
		IN (SELECT idjnssemester FROM stsemester WHERE idstatus = 1) ORDER BY idsemester";        
        $this->getDataPrivate($q);
    }
	
	function get_cutidet() {
        $sql = "SELECT * FROM cutidet ORDER BY idcutidet";
        $this->getDataPrivate($sql);
    }
	
	function get_shift(){ 
        $q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='64'";        
        $this->getData($q);
    }
	
	function get_class(){ 
        $q="SELECT idklsmhs as id, kdklsmhs as kode , nmklsmhs as nama, deskripsi 
		FROM klsmhs WHERE idklsmhs<>'' AND idstatus='1'";        
        $this->getData($q);
    }
	
	function get_years(){
		$q = "SELECT tahun FROM tahun where tahun<>''";
		$this->getData($q);
	}
	
	function get_stfirst(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='06'";
		$this->getData($q);
	}
	
	function get_staktiv(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='05' AND kdkodtbkod<>'G'";
		$this->getData($q);
	}
	
	function get_jslta(){
		$q = "SELECT idjnsslta as id, kdjnsslta as kode, nmjnsslta as nama, deskripsi 
		FROM jslta WHERE idjnsslta<>''";
		$this->getData($q);
	}
	
	function get_stakrslta(){
		$q = "SELECT idstakreditasislta as id, kdstakreditasislta as kode, 
		nmstakreditasislta as nama, deskripsi 
		FROM stakreditasislta WHERE idstakreditasislta<>''";
		$this->getData($q);
	}
	
	function get_jpt(){
		$q = "SELECT idjnspt as id, kdjnspt as kode, nmjnspt as nama, deskripsi 
		FROM jpt WHERE idjnspt<>''";
		$this->getData($q);
	}
	
	function get_stakrpt(){
		$q = "SELECT idstakreditasi as id, kdstakreditasi as kode, 
		nmstakreditasi as nama, deskripsi 
		FROM stakreditasi WHERE idstakreditasi<>''";
		$this->getData($q);
	}
	
	function get_kuakrpt(){
		$q = "SELECT DISTINCT idkuakreditasi as id, kdkuakreditasi as kode, 
		nmkuakreditasi as nama, deskripsi 
		FROM kuakreditasi WHERE idkuakreditasi<>''";
		$this->getData($q);
	}
    
	function get_mhsjob(){ //
        $q="SELECT idpekerjaanmhs as id,kdpekerjaanmhs as kode,
		nmpekerjaanmhs as nama, deskripsi 
		from pekerjaanmhs where idpekerjaanmhs <> ''";        
        $this->getData($q);
    }
	
	function get_biaya(){
		$q="SELECT kdkodtbkod as kdbiayastudi, nmkodtbkod as nmbiayastudi 
		FROM tbkod WHERE kdapltbkod='16'";        
        $this->getData($q);
	}
	
	function get_jkls() {
        $sql = "SELECT idjnskls,kdjnskls, nmjnskls,deskripsi
			FROM jkls";
        $this->getData($sql);
    }
	
	function get_klsmhs(){ 
        $q="SELECT idklsmhs, kdklsmhs, nmklsmhs, deskripsi, idjnskls, concat(kdklsmhs, '-', nmklsmhs) AS kdnm
		FROM klsmhs WHERE idklsmhs<>'' AND idstatus='1'";        
        $this->getDataPrivate($q);
    }
	
	function get_set_tahapanbyr_dpk(){
		$q="SELECT * from setting where idklpset='4'";
		$this->getDataPrivate($q);
	}
	
	function get_set_tahapanbyr_dpp(){
		$q="SELECT * from setting where idklpset='5'";
		$this->getDataPrivate($q);
	}
	
	function get_jjurnal() {
        $sql = "SELECT * FROM jnsjurnal";
        $this->getData($sql);
    }
	
	function get_jujian(){
		$KEDUA= $this->load->database('second', TRUE);
        $sql="SELECT * FROM jujian limit 0,2";
		$query  = $KEDUA->query($sql);
		
		$data = array();
        if ($query->num_rows() > 0) {
            $data = $query->result();
        }        
        $ttl = count($data);
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

        if($ttl>0){
            $build_array["data"]=$data;
        }
        echo json_encode($build_array);
	}
	
	//========DOSEN========//
	function get_status_dos(){
		$q="SELECT kdstdosen as kode, nmstdosen as nama 
		FROM stdosen WHERE kdstdosen<>''";        
        $this->getDataPrivate($q);
	}
	function get_jabak(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='02'";        
        $this->getDataPrivate($q);
	}
	
	function get_pdktop(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='01'";        
        $this->getDataPrivate($q);
	}
	
	function get_stikj(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='03'";        
        $this->getDataPrivate($q);
	}
	
	function get_staktivdos(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='15'";        
        $this->getDataPrivate($q);
	}
	
	function get_jstudi(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='04'";        
        $this->getDataPrivate($q);
	}
	
	function get_jenjangstudi(){ 
        $q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='04'";        
        $this->getDataPrivate($q);
    }
	//====================//
	
	
	// END HELPER =============================================================
	function get_chart_dpk_prodi_temp(){
		$KEDUA= $this->load->database('second', TRUE);
		$thmasuk = $this->input->post("thmasuk");
		$idklsmhs = $this->input->post("idklsmhs");
		$kdprodi = $this->input->post("kdprodi");
		
        $query  = $KEDUA->query("CALL sp_getDpkProdi_chart (?,?,?)",array($thmasuk,$idklsmhs,$kdprodi));
        $data = array();
        if ($query->num_rows() > 0) {
            $data = $query->result();
        }
        $ttl = count($data);
        
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());
		
		$biaya = 0;
		$sisa = 0;
		$jmlbayar = 0;
		foreach($data as $row) {
				$biaya += $row->biaya;
				$sisa += $row->sisa;
				$jmlbayar += $row->jmlbayar;
        }
		
		 array_push($build_array["data"],array(
				'item'=>'biaya', 
				'value'=>$biaya,
            ));
			
		array_push($build_array["data"],array(
				'item'=>'sisa', 
				'value'=>$sisa,
            ));
		
		 array_push($build_array["data"],array(
				'item'=>'jmlbayar', 
				'value'=>$jmlbayar,
            ));
			
            echo json_encode($build_array);
	}
	
	function get_chart_dpk_fak_temp(){
        $KEDUA= $this->load->database('second', TRUE);
			$thmasuk = $this->input->post("thmasuk");
			$idklsmhs = $this->input->post("idklsmhs");
			$kdfakultas = $this->input->post("kdfakultas");
			
        $query  = $KEDUA->query("CALL sp_getDpkFakultas_chart (?,?,?)",array($thmasuk,$idklsmhs,$kdfakultas));
        $data = array();
        if ($query->num_rows() > 0) {
            $data = $query->result();
        }
        $ttl = count($data);
        
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());
		
		$biaya = 0;
		$sisa = 0;
		$jmlbayar = 0;
		foreach($data as $row) {
				$biaya = $row->biaya;
				$sisa = $row->sisa;
				$jmlbayar = $row->jmlbayar;
        }
		
		 array_push($build_array["data"],array(
				'item'=>'biaya', 
				'value'=>$biaya,
            ));
			
		array_push($build_array["data"],array(
				'item'=>'sisa', 
				'value'=>$sisa,
            ));
		
		 array_push($build_array["data"],array(
				'item'=>'jmlbayar', 
				'value'=>$jmlbayar,
            ));
			
            echo json_encode($build_array);
	}
  
}