<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Data_Controller extends Controller {
	var $stat;

    public function __construct() {
        parent::Controller();
		$this->load->library('rhlib');
    }

    // START HELPER ===========================================================    
    //REMOVED TO LIBRARY
	
	function jsonFromQuery($q) {
		$this->rhlib->jsonFromQuery($q);
	}
	function jsonFromQuery2($q) {
		$this->rhlib->jsonFromQuery2($q);
	}
	function jsonFromQueryWLimit($q){
		$this->rhlib->jsonFromQueryWLimit($q);
	}
	function jsonFromQueryWLimit2($q){
		$this->rhlib->jsonFromQueryWLimit2($q);
	}
	
	function jsonFromQueryWLimitNew($q){
	$KEDUA= $this->load->database('second', TRUE);
        	$start = $_POST['start'];
        $limit = $_POST['limit'];
		if ($start==null){
            $start = 0;
			$limit = 50;
		}
		$data = array();
		$build_array = array ("success"=>false,"results"=>-1,"data"=>array());

		$strQuery = $this->getStrQueryWSearch($q);
		$query1  = $KEDUA->query($strQuery);	//query all rows
        if ($query1->num_rows() == 0){
			$build_array["success"]=true;
			$build_array["results"]= 0;
		}
		if ($query1->num_rows() > 0) {
			$total = $query1->num_rows(); // total data
			$strQuery .=" LIMIT ".$start.", ".$limit; // query dengan limit
			$query2  = $KEDUA->query($strQuery);
            $data = $query2->result();
			$build_array["success"]=true;
			$build_array["results"]=$total;
            //$build_array["count"]=count($data);
			$build_array["data"]=$data;
        }
        echo json_encode($build_array);
	}

	
//GENERAL
	function get_gender(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama FROM tbkod 
				WHERE kdapltbkod='08'";
		$this->jsonFromQuery($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 id
				FROM tbkod WHERE kdapltbkod='51' ORDER BY id";
		$this->jsonFromQuery($q);	
	}
	
	function get_hari(){
		$q = "SELECT idhari as kode, nmhari as nama 
				FROM hari";
		$this->jsonFromQuery($q);	
	}
	
	function get_bloodtype(){
		$q = "SELECT idgoldarah as id, kdgoldarah as kode, nmgoldarah as nama 
			FROM goldarah 
			WHERE idgoldarah<>''";
		$this->jsonFromQuery($q);	
	}
	
	function get_nationality(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama FROM tbkod 
			WHERE kdapltbkod='50'";
		$this->jsonFromQuery($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->jsonFromQueryWLimit($q);		
    }
			
	function get_prov(){ //PROPINSI
		$q="SELECT DISTINCT kdprotbpro as kdprov, nmprotbpro as nmprov 
		from tbpro where kdprotbpro <> ''";
        $this->jsonFromQueryWLimit($q);
    }
	
	function get_negara(){ //PROPINSI
		$q="SELECT DISTINCT CONCAT(rtrim(kdprotbpro),ltrim(kdkabtbpro)) as kdnegara, 
		nmkabtbpro as nmnegara 
		from tbpro 
		WHERE CAST(kdprotbpro AS UNSIGNED) > 93";
        $this->jsonFromQueryWLimit($q);
    }
    
	function get_univ(){ //P.T.
		$q="SELECT kdptitbpti as kdpt, nmptitbpti as nmpt, kotaatbpti as kota 
		FROM tbpti where kdptitbpti <> ''";
        $this->jsonFromQueryWLimit($q);
    }
	
	function get_prodi(){ //Program Studi indonesia
		/* $q="SELECT kdpsttbpst as kdprodi, nmpsttbpst as nmprodi, nomortbpst as nomor 
		from tbpst where kdpsttbpst <> ''"; */
		$q="SELECT kdpsttbpst AS kdprodi
     , nmpsttbpst AS nmprodi
     , nomortbpst AS nomor
     , kdjentbpst AS kdjentbpst
     , nmkodtbkod AS nmkodtbkod
FROM
  tbpst
JOIN tbkod
ON tbpst.kdjentbpst = tbkod.kdkodtbkod AND tbkod.kdapltbkod = '01'
WHERE
  kdpsttbpst <> ''";
        $this->jsonFromQueryWLimit($q);
    }
	
	function get_prodireg(){ //Program Studi lokal
        $q="SELECT kdprodi
     , nourutprodi
     , nmprodi
     , ifnull(
       concat(ifnull(rtrim(nourutprodi), '-'), '. ', nmprodi, ' (', ifnull(nmjenjangstudi, '-'), ')')
       , nmprodi) AS lnmprodi
     , kdfakultas
     , nmfakultas
     , kdjenjangstudi
     , nmjenjangstudi
     , tglawalberdiri
     , email
     , noskakreditasi
     , tglskakreditasi
     , notelpprodi
FROM
  v_prodi
WHERE
  nmprodi <> '-'
  
  AND kdprodi IN (SELECT kdprodi
                    FROM
                      penggunaprodi
                    WHERE
                      userid = '".$this->my_usession->userdata('user_idunlaprivate')."')
ORDER BY
  nourutprodi
, kdprodi";        
        $this->jsonFromQuery($q);
    }
	
	//AND kdjenjangstudi <> 'B'
	
	function get_jenjangstudi(){ 
        $q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='04'";        
        $this->jsonFromQuery($q);
    }
    
	
	function get_years(){
		$q = "SELECT tahun as kode, tahun as nama FROM tahun where tahun<>''
			ORDER BY tahun DESC";
		$this->jsonFromQuery($q);
	}
	
	function get_jam(){
		$q = "SELECT * FROM jam";
		$this->jsonFromQuery($q);
	}
	
	function get_status(){
		$q="select * from status";        
        $this->jsonFromQuery($q);
	}
	
	function get_klsparalel(){
		$q = "SELECT klsparalel FROM klsparalel";
		$this->jsonFromQuery($q);
	}
	
	function get_semester(){
		$q = "SELECT idsemester as kode, kdsemester, nmsemester as nama, tingkat as tingkat,
			  concat(tingkat, '/ ', kdsemester) AS tingsem
			  FROM semester where ifnull(kdsemester,'')<>''";
		$this->jsonFromQuery($q);
	}
    
    function get_semester_srtpernyataan(){
        $q = "SELECT idsemester, concat(tingkat, '/ ', kdsemester) AS tingkat FROM semester";
        $this->jsonFromQuery($q);
    }
    
    //function get_stsemester(){
//        $q = "SELECT a.* FROM stsemester a LEFT JOIN thnakademik b ON a.idthnakademik=b.idthnakademik";
//        $this->jsonFromQuery($q);
//    }
	
	function get_stsmt(){ //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->jsonFromQuery($q);
    }
	
	function get_jkurikulum(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama
				FROM tbkod where kdapltbkod='11'";
		$this->jsonFromQuery($q);
	}
	
	function get_jmatkul(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama
				FROM tbkod where kdapltbkod='28'";
		$this->jsonFromQuery($q);
	}
	
	function get_stmatkul(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama
				FROM tbkod where kdapltbkod='14'";
		$this->jsonFromQuery($q);
	}
	
	function get_kelmatkul(){
		$q = "SELECT kdkodtbkod as kode, 
			CASE WHEN ifnull(nmdata,'')='' THEN nmkodtbkod
				ELSE nmdata END as nama
			FROM tbkod WHERE kdapltbkod='10'";
		$this->jsonFromQuery($q);
	}
	
	function get_stabsenmhs(){
		$q = "SELECT idstabsenmhs,kdstabsensimhs,nmstabsensimhs,
			CONCAT(nmstabsensimhs,' (',IFNULL(kdstabsensimhs,''),')')as stabsensimhs,
			deskripsi	
			FROM stabsenmhs
			UNION SELECT null,'-','-','-','-'";
		$this->jsonFromQuery($q);
	}
	
	function get_stabsensidos(){
		$q = "SELECT * FROM stabsensidos";
		$this->jsonFromQuery($q);
	}
	
	function get_jujian(){
		$q = "SELECT * FROM jujian Limit 0,2";
		$this->jsonFromQuery($q);
	}
	
	function get_setbonilai(){
		$q = "SELECT * FROM setbonilai";
		$this->jsonFromQuery($q);
	}
	
	function get_carabyr(){
		$q = "SELECT idcarabyr,kdcarabyr,nmcarabyr,deskripsi
			FROM carabyr WHERE idcarabyr<>''";
		$this->jsonFromQuery($q);
	}
	
	function get_jbiaya(){
		$q = "SELECT idjnsbiaya,kdjnsbiaya,nmjnsbiaya,deskripsi
			FROM jbiaya WHERE idjnsbiaya<>''";
		$this->jsonFromQuery($q);
	}
	
	function get_jdiskon(){
		$q = "SELECT * FROM jdiskon";
		$this->jsonFromQuery($q);
	}
		
// MAHASISWA	
	function get_parentjob(){ //
        $q="SELECT idpekerjaanortu as id,kdpekerjaanortu as kode,
		nmpekerjaanortu as nama, deskripsi 
		from pekerjaanortu where idpekerjaanortu <> ''";        
        $this->jsonFromQuery($q);
    }
	
	function get_parentedu(){ //
        $q="SELECT idpendidikanortu as id, kdpendidikanortu as kode,
		nmpendidikanortu as nama, deskripsi 
		from pendidikanortu where idpendidikanortu <> ''";        
        $this->jsonFromQuery($q);
    }
		
	function get_promotor(){ //Dosen/Promotor
        $q="SELECT nidu as iddosen, nidn, nmdosdgngelar as nmdosen from dosen where nidn <> '' order by nmdosdgngelar"; 
        $this->jsonFromQuery($q);
    }
			
	function get_shift(){ 
        $q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='64'";        
        $this->jsonFromQuery($q);
    }
	
	function data_shift(){ 
        $q="SELECT * FROM jkls";        
        $this->jsonFromQuery($q);
    }
	
	function get_jkls() {
        $sql = "SELECT idjnskls,kdjnskls, nmjnskls,deskripsi
			FROM jkls";
        $this->jsonFromQuery($sql);
    }

	function get_klsmhs(){ 
        $q="SELECT idklsmhs, kdklsmhs, kdklsmhs as nmklsmhs, deskripsi, idjnskls, concat(kdklsmhs, '-', nmklsmhs) AS kdnm
		FROM klsmhs WHERE idklsmhs<>'' AND idstatus='1'";        
        $this->jsonFromQuery($q);
    }
	
	function get_cutidet() {
        $sql = "SELECT * FROM cutidet ORDER BY idcutidet";
        $this->jsonFromQuery($sql);
    }
	
	function get_class(){ 
        $q="SELECT idklsmhs as id, kdklsmhs as kode, nmklsmhs as nama, deskripsi 
		FROM klsmhs WHERE idklsmhs<>'' AND idstatus='1'";        
        $this->jsonFromQuery($q);
    }

	function get_stfirst(){
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='06'";
		$this->jsonFromQuery($q);
	}
	
	function get_staktiv(){
		$kode = ($_POST['kdstaktivitas'] || $_POST['kdstaktivitas'] !=null) ? "AND kdkodtbkod = '".$_POST['kdstaktivitas']."'":"";
		$q = "SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='05' ".$kode;
		$this->jsonFromQuery($q);
	}
	
	function get_jslta(){
		$q = "SELECT idjnsslta as id, kdjnsslta as kode, 
		nmjnsslta as nama, deskripsi 
		FROM jslta WHERE idjnsslta<>''";
		$this->jsonFromQuery($q);
	}
	
	function get_stakrslta(){
		$q = "SELECT idstakreditasislta as id, kdstakreditasislta as kode, 
		nmstakreditasislta as nama, deskripsi 
		FROM stakreditasislta WHERE idstakreditasislta<>''";
		$this->jsonFromQuery($q);
	}
	
	function get_jpt(){
		$q = "SELECT idjnspt as id, kdjnspt as kode, nmjnspt as nama, deskripsi 
		FROM jpt WHERE idjnspt<>''";
		$this->jsonFromQuery($q);
	}
	
	function get_stakrpt(){
		$q = "SELECT idstakreditasi as id, kdstakreditasi as kode, 
		nmstakreditasi as nama, deskripsi 
		FROM stakreditasi WHERE idstakreditasi<>''";
		$this->jsonFromQuery($q);
	}
	
	function get_kuakrpt(){
		$q = "SELECT DISTINCT idkuakreditasi as id, kdkuakreditasi as kode, 
		nmkuakreditasi as nama, deskripsi 
		FROM kuakreditasi WHERE idkuakreditasi<>''";
		$this->jsonFromQuery($q);
	}
    
	function get_mhsjob(){ //
        $q="SELECT idpekerjaanmhs as id,kdpekerjaanmhs as kode,
		nmpekerjaanmhs as nama, deskripsi 
		from pekerjaanmhs where idpekerjaanmhs <> ''";        
        $this->jsonFromQuery($q);
    }
	
	function get_biaya(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='16'";        
        $this->jsonFromQuery($q);
	}
	
	function look_mhs(){
		$q = "SELECT nim,nmmhs,kdjnskelamin,a.kdprodi, b.nmprodi,thnmasuk, 
			c.nmstawal,d.nmstaktiv
			FROM mahasiswa a
			LEFT JOIN prodi b ON b.kdprodi=a.kdprodi
			LEFT JOIN
			(SELECT kdkodtbkod as kdstawal, nmkodtbkod as nmstawal 
			FROM tbkod WHERE kdapltbkod='06')c
			ON a.kdstawalmhs=c.kdstawal
			LEFT JOIN
			(SELECT kdkodtbkod as kdstaktiv, nmkodtbkod as nmstaktiv 
			FROM tbkod WHERE kdapltbkod='05')d
			ON a.kdstaktivitasmhs=d.kdstaktiv";
		$this->jsonFromQueryWLimit($q);
	}
    
    function get_stprodi(){
        $q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='13'";        
        $this->jsonFromQuery($q);     
        
    }
    
    function get_frepemutakhiran(){
        $q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='29'";        
        $this->jsonFromQuery($q);
    }
    
    function get_pelpemutakhiran(){
        $q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='30'";        
        $this->jsonFromQuery($q);
    }
				
// DOSEN
	function get_dosen3(){ //Dosen/Promotor
        $q="SELECT nidu, nidn, ifnull(nmdosdgngelar,nmdostpgelar) as nmdosen, notelp
			FROM dosen";        
        $this->jsonFromQueryWLimit($q);
    }
	function get_dosenKP(){ //Dosen/Promotor
		$where = ($this->input->post('kdprodi')) ? "where kdprodi=".$this->input->post('kdprodi')."": "";
        $q="SELECT *
			FROM dosen $where";        
        $this->jsonFromQueryWLimit($q);
    }
	function get_dosenP(){ //Dosen/Promotor
		$where = ($this->input->post('kdfakultas')) ? "where kdfakultas=".$this->input->post('kdfakultas')."": "";
        $q="SELECT * FROM v_dosen_cari $where ORDER BY nidu";        
        $this->jsonFromQueryWLimit($q);
    }
	function get_lookup_dos(){
		$q = "SELECT a.kdprodi,a.kdstdosen,a.nidu,a.nidn,a.nmdostpgelar,a.nmdosdgngelar,
			a.kdjabakademik,
			ifnull(a.nmdosdgngelar,a.nmdostpgelar) as nmdosen,					
			b.nmfakultas, b.nourutprodi, b.nmprodi, b.lnmprodi, b.kdjenjangstudi, b.nmjenjangstudi,
			c.nmstikj, d.nmstaktiv, e.nmjabak
			FROM dosen a
			LEFT JOIN (SELECT kdprodi, nourutprodi, nmprodi, 
					CONCAT(RTRIM(nourutprodi),'. ',nmprodi,' (',nmjenjangstudi,')') as lnmprodi,
					nmfakultas, kdjenjangstudi, nmjenjangstudi
				FROM v_prodi where kdprodi <> '')b
				ON b.kdprodi=a.kdprodi
			LEFT JOIN (SELECT kdkodtbkod as kdstikj, nmkodtbkod as nmstikj
				FROM tbkod WHERE kdapltbkod='03')c
				ON a.kdstikatankerjadosen = c.kdstikj
			LEFT JOIN (SELECT kdkodtbkod as kdstaktiv, nmkodtbkod as nmstaktiv
				FROM tbkod WHERE kdapltbkod='15')d
				ON a.kdstaktivitasdosen = d.kdstaktiv
			LEFT JOIN (SELECT kdkodtbkod as kdjabak, nmkodtbkod as nmjabak 
				FROM tbkod WHERE kdapltbkod='02')e 
				ON e.kdjabak=a.kdjabakademik
			WHERE a.kdstaktivitasdosen='A'";
			
		$this->jsonFromQueryWLimit($q);
	
	}
		
	function get_status_dos(){
		$q="SELECT kdstdosen as kode, nmstdosen as nama 
		FROM stdosen WHERE kdstdosen<>''";        
        $this->jsonFromQuery($q);
	}
	
	function get_jabak(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='02'";        
        $this->jsonFromQuery($q);
	}
	
	function get_pdktop(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='01'";        
        $this->jsonFromQuery($q);
	}
	
	function get_stikj(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='03'";        
        $this->jsonFromQuery($q);
	}
	
    //status dosen//
	function get_staktivdos(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='15'";        
        $this->jsonFromQuery($q);
	}
    function get_jstudi(){
		$q="SELECT kdkodtbkod as kode, nmkodtbkod as nama 
		FROM tbkod WHERE kdapltbkod='04'";        
        $this->jsonFromQuery($q);
	}
	
	// ============ CREATE BY WALDI, UPDATE BY RH ========================================
	
	function get_fakultas(){
		$q="select * from fakultas";        
        $this->jsonFromQuery($q);
	}
    
    function get_stakreditasi(){
		$q="select * from stakreditasi";        
        $this->jsonFromQuery($q);
	}
    
    function get_stberhentimhs(){
		$q="select * from stberhentimhs";        
        $this->jsonFromQuery($q);
	}
    
    function get_pimpinan(){
		$q="select * from pimpinan where jabatan = Rektor";        
        $this->jsonFromQuery($q);
	}

	function get_pimpinan_srtketkul(){
		$q="select * from pimpinan where idpimpinan = 7";        
        $this->jsonFromQuery($q);
	}
    function get_kopertis(){
		$q="select * from kopertis";        
        $this->jsonFromQuery($q);
	}
    
    function get_stsetuju(){
		$q="select * from stsetuju";        
        $this->jsonFromQuery($q);
	}
	
	function get_pt(){
		$q="select * from pt";        
        $this->jsonFromQuery($q);
	}
	
	function get_ruangan(){
		$q="SELECT a.*
			FROM v_ruangan a";        
        $this->jsonFromQuery($q);
	}
	
	function get_jruangan(){
		$q="select * from jruangan";        
        $this->jsonFromQuery($q);
	}
	
	function get_truangan(){
		$q="select * from tiperuangan";        
        $this->jsonFromQuery($q);
	}
	
	function get_tipekelas(){
		$q="select * from tipekelas";        
        $this->jsonFromQuery($q);
	}
			
	function get_stdosen(){
		$q="select * from stdossemester";
		$this->jsonFromQuery($q);
	}
	
	function get_jsemester(){
		$q="select * from jsemester";        
        $this->jsonFromQuery($q);
	}
	
	function get_thnakademik(){
		$q="select * from thnakademik order by nmthnakademik desc";        
        $this->jsonFromQuery($q);
	}
    
    function get_thnakademik2(){
		$q="select * from thnakademik order by nmthnakademik desc";        
        $this->jsonFromQuery($q);
	}
	

	
	function get_jdosen(){
		$q="select idjnsdosen as id, kdjnsdosen as kode, nmjnsdosen as nama, deskripsi from jdosen";        
        $this->jsonFromQuery($q);
	}
	
	function get_pmb(){
		
		/* $q="SELECT DISTINCT g.nmjenpdkpendaftar,g.fakultasasal,
		g.kdptasal,g.nmptasal,g.kdprodiasal,g.nmprodiasal,g.nimasal,
		h.kdstsemester,j.kdstatus,i.kdjnskls, f.kdstmskmhs,
		a.nmstmskmhs, a.nopmb, a.thnlulusslta ,a.tglpmb, a.nama, a.kdjk, a.tgllahir, a.tptlahir, a.notelp, a.nmslta, a.nemslta, a.rangkingslta, a.kdkotkabslta,
        a.alamatasal, a.kelurahanasal, a.kecamatanasal, a.kdkotkabasal, a.kdpossurat, a.idklsmhs1, a.idstmskmhs, a.noijazahslta,
        b.nmprodi, c.nmstusm , a.idstusm, a.kdprodi1, a.alamatsurat, a.nmortu, a.idpekerjaanortu, a.alamatortu,
		d.nmkabtbpro, e.nmkabtbpro as nmkotkabslta
		from v_pmb a LEFT JOIN prodi b ON b.kdprodi=a.kdprodi1
		LEFT JOIN stusm c ON c.idstusm=a.idstusm
		LEFT JOIN tbpro d ON CONCAT(rtrim(d.kdprotbpro),ltrim(d.kdkabtbpro)) = a.kdkotkabasal
		LEFT JOIN tbpro e ON CONCAT(rtrim(e.kdkabtbpro),ltrim(e.kdprotbpro)) = a.kdkotkabslta
		LEFT JOIN v_pmb_pendaftaran f ON f.idstmskmhs = a.idstmskmhs
		LEFT JOIN v_pmb_ptasal g ON g.nopmb = a.nopmb
		LEFT JOIN jadwalpmb h ON h.idjadwalpmb = a.idjadwalpmb
		LEFT JOIN v_klsmhs i ON i.idklsmhs = a.idklsmhs
		LEFT JOIN v_jadwalpmb j ON j.idjadwalpmb = a.idjadwalpmb
		
		where (a.idstusm = 1 OR a.idstusm = 3) AND (isnull(a.nim) OR a.nim='')"; */
		
		$q="SELECT DISTINCT g.nmjenpdkpendaftar
              , g.fakultasasal
              , g.kdptasal
              , g.nmptasal
              , g.kdprodiasal
              , g.nmprodiasal
              , g.kdjentbpst
              , g.nmjentbpst
              , g.nimasal
              , h.kdstsemester
              , 'A' as kdstatus
              , i.kdjnskls
              , f.kdstmskmhs
              , a.nmstmskmhs
              , a.nopmb
              , a.thnlulusslta
              , a.tglpmb
			  , a.noktp
              , a.nama
              , a.kdjk
			  , date_format(a.tgllahir,'%Y-%m-%d') as tgllahir
              , a.tptlahir
              , a.notelp
              , a.nmslta
              , a.nemslta
              , a.rangkingslta
              , a.kdkotkabslta
              , a.alamatasal
              , a.kelurahanasal
              , a.kecamatanasal
              , a.kdkotkabasal
              , a.kdpossurat
              , a.idklsmhs1
              , a.idstmskmhs
              , a.noijazahslta
              , b.nmprodi
              , c.nmstusm
              , a.idstusm
              , a.kdprodi1
              , a.alamatsurat
              , a.nmortu
              , a.idpekerjaanortu
              , a.alamatortu
			  , a.nmortuayah
              , a.idpekerjaanortuayah
              , a.alamatortuayah
              , d.nmkabtbpro
              , e.nmkabtbpro AS nmkotkabslta
			  , a.noijazalasal
			  , date_format(a.tglijazahasal,'%Y-%m-%d') as tglijazahasal
FROM
  v_pmb a
LEFT JOIN prodi b
ON b.kdprodi = a.kdprodi1
LEFT JOIN stusm c
ON c.idstusm = a.idstusm
LEFT JOIN tbpro d
ON concat(rtrim(d.kdprotbpro), ltrim(d.kdkabtbpro)) = a.kdkotkabasal
LEFT JOIN tbpro e
ON concat(rtrim(e.kdkabtbpro), ltrim(e.kdprotbpro)) = a.kdkotkabslta
LEFT JOIN stmskmhs f
ON f.idstmskmhs = a.idstmskmhs
LEFT JOIN v_pmb_ptasal g
ON g.nopmb = a.nopmb
LEFT JOIN jadwalpmb h
ON h.idjadwalpmb = a.idjadwalpmb
LEFT JOIN v_klsmhs i
ON i.idklsmhs = a.idklsmhs
JOIN kuitansipmb j
ON j.nokuitansipmb = a.nokuitansipmb

WHERE
  a.idstusm IN (1,3)
  AND a.idstpmb = 1
  AND j.idstkuitansi = 1
  AND (isnull(a.nim)
  OR a.nim = '')";
		$this->jsonFromQueryWLimit2($q);
	
	}
	
	/*=============== KEUANGAN =====================*/
	function lookup_mhs_keu(){
		$q = "SELECT DISTINCT a.nim, a.nmmhs, a.kdjnskelamin, a.thnmasuk, a.idklsmhs,
			a.kdstawalmhs, a.kdstaktivitasmhs, a.catnonaktif,
			b.nmfakultas, b.kdprodi, b.nourutprodi, b.nmprodi, 
			ifnull(b.lnmprodi,b.nmprodi) as lnmprodi, b.nmjenjangstudi,
			c.nmstawal, d.nmstaktiv, e.nmklsmhs, concat(e.kdklsmhs,'.',e.nmklsmhs) AS kdnmklsmhs, 
			f.idjnskls, f.nmjnskls
			FROM mahasiswa a
			LEFT JOIN (SELECT kdprodi, nourutprodi, nmprodi, 
					CONCAT(RTRIM(nourutprodi),'. ',nmprodi,' (',nmjenjangstudi,')') as lnmprodi,
					nmfakultas, kdjenjangstudi, nmjenjangstudi,
					tglawalberdiri, email, noskakreditasi, tglskakreditasi, notelpprodi
				FROM v_prodi where kdprodi <> '')b
			ON b.kdprodi=a.kdprodi
			LEFT JOIN (SELECT kdkodtbkod as kdstawal, nmkodtbkod as nmstawal 
				FROM tbkod WHERE kdapltbkod='06')c
				ON a.kdstawalmhs=c.kdstawal
			LEFT JOIN (SELECT kdkodtbkod as kdstaktiv, nmkodtbkod as nmstaktiv 
				FROM tbkod WHERE kdapltbkod='05')d
				ON a.kdstaktivitasmhs=d.kdstaktiv 
			LEFT JOIN klsmhs e ON a.idklsmhs=e.idklsmhs
			LEFT JOIN jkls f ON f.idjnskls=e.idjnskls";
			//WHERE b.kdjenjangstudi = 'C'";	//kdjenjangstudi='C' -> ONLY S1
		$this->jsonFromQueryWLimit($q);
	}
	
	function get_stva(){
		$q="select * from stva";        
        $this->jsonFromQuery($q);
	}
	
	
	function get_pimpinan_surat(){
		$q=($_POST['kdfakultas']) ? "SELECT * FROM pimpinan WHERE kdfakultas='".$_POST['kdfakultas']."'":"SELECT * FROM pimpinan";
		//$q="select * from pimpinan where jabatan='".$_POST['jabatan']."'";        
		//$q="select * from pimpinan";    			
        $this->jsonFromQuery($q);
	}
    
    function get_tskripsi(){
		$q="select * from tskripsi";        
        $this->jsonFromQuery($q);
	}
	
	function get_jskripsi(){
		$q="select * from jskripsi";        
        $this->jsonFromQuery($q);
	}
    
    function get_rekdek(){
		$q="select idpimpinan, jabatan from pimpinan group by jabatan";        
        $this->jsonFromQuery($q);
	}
    
    function get_tandatangan(){
		 $q="select * from pimpinan where kdfakultas='".$_POST['kdfakultas']."' AND jabatan='".$_POST['jabatan']."'";        
        $this->jsonFromQuery($q);
	}
    
    function get_kelas(){
		 $q="select kelas.*, idkelas as id, nmkelas as nama from kelas where kdprodi IN (SELECT kdprodi
                    FROM
                      penggunaprodi
                    WHERE
                      userid = '".$this->my_usession->userdata('user_idunlaprivate')."')";        
        $this->jsonFromQuery($q);
	}
    
    function get_bdnhkm(){
		 $q="select * from badanhukum";        
        $this->jsonFromQuery($q);
	}
	
	function get_lvlsyaratmk(){
		$q = "SELECT idlvlsyaratmk as id, kdlvlsyaratmk as kode, nmlvlsyaratmk as nama
				FROM lvlsyaratmk";
		$this->jsonFromQuery($q);
	}
	
	function get_jkrs(){
		$q = "SELECT * FROM jkrs";
		$this->jsonFromQuery($q);
	}
	
	function get_biaya_srtpernyataan(){
		$q = "SELECT idsetbiaya, biaya FROM biaya WHERE kdprodi='".$_POST['getprodi']."' AND kdstsemester='".$_POST['getsemester']."' AND idjnsbiaya='".$_POST['getjbiaya']."'";
		$this->jsonFromQuery($q);
	}
	
	function get_siskrs(){
		$q = "SELECT * FROM siskrs";
		$this->jsonFromQuery($q);
	}
	
	function get_warek1(){
		$where = ($_POST['jabatan']) ? "WHERE jabatan = '".$_POST['jabatan']."'":"WHERE jabatan = 2";
		if ($_POST['fakultas']) {
			$where.= " AND kdfakultas='".$_POST['fakultas']."'";
		}
		$q="SELECT * FROM pimpinan ".$where;        
        $this->jsonFromQuery($q);
	}
	
	function get_status_masuk(){
		$q="SELECT * from stmskmhs";
		$this->jsonFromQuery2($q);
	}
	
	function get_klpruangan(){
		$q="SELECT * from klpruangan";
		$this->jsonFromQuery($q);
	}
		
	function get_set_tahapanbyr_dpk(){
		$q="SELECT * from setting where idklpset='4'";
		$this->jsonFromQuery($q);
	}
	
	function get_set_tahapanbyr_dpp(){
		$q="SELECT * from setting where idklpset='5'";
		$this->jsonFromQuery($q);
	}
	
	function get_stkonversi(){
		$q="SELECT * from stkonversi";
		$this->jsonFromQuery($q);
	}
	
	function get_kelas_combo(){
		 $q="select kelas.*, idkelas as id, nmkelas as nama 
		 from kelas 
		 where kdprodi= '".$this->input->post('kdprodi')."'
		 and idstatus=1";        
        $this->jsonFromQuery($q);
	}
	
	function get_jabatan(){ 
        $q="SELECT * FROM jabatan ORDER BY idjabatan";        
        $this->jsonFromQuery($q);
    }
    
	function get_semester_ganjilgenap(){ //status smester
        $q="SELECT idsemester as kode, kdsemester, nmsemester as nama, tingkat as tingkat,
			  concat(nmsemester, '/ ', tingkat) AS tingsem FROM semester WHERE idjnssemester = '".$this->input->post('jsemester')."' ORDER BY idsemester";        
        $this->jsonFromQuery($q);
    }
	function get_stakreditasi_tbkod(){
		$q="SELECT kdkodtbkod AS kdstakreditasi
			 , nmkodtbkod AS nmstakreditasi
		FROM
		  tbkod
		WHERE
		  kdapltbkod = '07'";        
        $this->jsonFromQuery($q);
	}
	function get_jabatan_dekan(){ 
        $q="SELECT * FROM jabatan WHERE idjabatan >=5 ORDER BY idjabatan";        
        $this->rhlib->jsonFromQuery($q);
    }
	// END HELPER =============================================================
    
}