<?php

namespace Application\Model\Param\Storage;
use Application\Model\Param\Storage;
use Khansia\Generic\Result as Result;
use Laminas\Db\ResultSet\ResultSet;

class Mysql extends \Khansia\Db\Storage implements Skeleton {
    
    private $_result;
	
	const LOP 				= 'lop_cluster';
	const JOIN_LEFT			= 'left';
	 
    public function array_change_key_case_recursive($input, $case = CASE_LOWER){ 
        if(!is_array($input)){ 
            trigger_error("Invalid input array '{$array}'",E_USER_NOTICE); exit; 
        } 
        // CASE_UPPER|CASE_LOWER 
        if(null === $case){ 
            $case = CASE_LOWER; 
        } 
        if(!in_array($case, array(CASE_UPPER, CASE_LOWER))){ 
            trigger_error("Case parameter '{$case}' is invalid.", E_USER_NOTICE); exit; 
        } 
        $input = array_change_key_case($input, $case); 
        foreach($input as $key=>$array){ 
            if(is_array($array)){ 
                $input[$key] = $this->array_change_key_case_recursive($array, $case); 
            } 
        } 
        return $input; 
    } 
    
    public function __construct(\Laminas\Db\Adapter\Adapter $adapter, $config = array()) {

		parent::__construct($adapter, $config);
		 /* get conn instance */
      $this->_conn = $adapter->getDriver()->getConnection()->getResource();
   
	  
        //print_r($config);
        if (isset($config['tables'])) {
            $tables = $config['tables'];
            foreach ($tables as $key => $value) {
                if (array_key_exists($key, $this->_tables) && $value) {
                    $this->_tables[$key] = $this->_($value);
                }
            }
        }
    }
    
    public function fetchAll(\Laminas\Db\Sql\Select $select, $raw = true){

        $statement = $this->_sql->prepareStatementForSqlObject($select);
        if ($result = $statement->execute()) {
            $resultset = new \Laminas\Db\ResultSet\ResultSet();
            $data = $resultset->initialize($result)->toArray();
            return $data;
        }

        return false;
    }

    public function deleteGlobal($tabel, $where){

        $result = new Result();

        try {

            $stmt   = $this->_db->query("delete from $tabel where $where ");

            $stmt->execute();

            $getData = $this->loadTable('1', $tabel, $where);

            if($getData->code == $result::CODE_SUCCESS){
                /**
                 * jika data ditemukan
                 * @return code failed 
                 * rollback data
                 */
                // $transaction->rollback();
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }else{
                /**
                 * jika data tidak diteumkan maka
                 * @return code error 
                 * commit data
                 */
                // $transaction->commit();            
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }

        return $result;

    }

    public function loadTable($tabel, $column,  $where = null){

        $result = new Result();

        try {

            $sql      = " SELECT $column FROM $tabel ";

            if($where){
                $sql .= " WHERE $where ";
            }
            // print_r($sql);die;
            
			$stmt     = $this->_db->query($sql);
			$getData  = $stmt->execute();
			
			$listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
            // print_r(count($listdata));die;
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
                // $result->data = count($listdata);
            }

            // $result->code = $result::CODE_SUCCESS;
            // $result->info = $result::INFO_SUCCESS;
            // $result->data = count($listdata);
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loadTableSS($tabel, $column,  $where = null){

        $result = new Result();

        try {

            $sql      = " SELECT $column FROM $tabel ";

            if($where){
                $sql .= " WHERE $where ";
            }
            // print_r($sql);die;
            
			$stmt     = $this->_db->query($sql);
			$getData  = $stmt->execute();
			
			$listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
            // print_r(count($listdata));die;
            // if (count($listdata) < 1) {
            //     $result->code = $result::CODE_SUCCESS;
            //     $result->info = $result::INFO_SUCCESS;
            //     $result->data = 0;
            // } else {
            //     $result->code = $result::CODE_FAILED;
            //     $result->info = $result::INFO_FAILED;
            //     $result->data = count($listdata);
            // }

            $result->code = $result::CODE_SUCCESS;
            $result->info = $result::INFO_SUCCESS;
            $result->data = count($listdata);
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function countTable($tabel, $column,  $where = null){

        $result = new Result();

        try {

            $sql      = " SELECT COUNT($column) FROM $tabel ";

            if($where){
                $sql .= " WHERE $where ";
            }
            // print_r($sql);die;
            
			$stmt     = $this->_db->query($sql);
			$getData  = $stmt->execute();
			
			$listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loadnilaiperprodi($param){

        $result = new Result();
        // print_r($param);die;
        try {
	        $tglawalraw = explode("-", $param->tglawal);
            $tglawal    = $tglawalraw[2]."-".$tglawalraw[1]."-".$tglawalraw[0];

	        $tglakhirraw = explode("-", $param->tglakhir);
            $tglakhir    = $tglakhirraw[2]."-".$tglakhirraw[1]."-".$tglakhirraw[0];

            $filterSemester = '';
            if($param->semester != "" || $param->semester != null){
                $semExp   = explode("~", $param->semester);
                $semester = $semExp[0];
                $tahunAj  = $semExp[1]; 
                $filterSemester .= " AND j.semester = '$semester' AND j.tahun_ajaran = '$tahunAj' ";
            }

            $sql      = "   SELECT * FROM sipenkp_kerja_praktik kp
                            INNER JOIN sipenkp_nilai_mahasiswa nm ON kp.id_nilai = nm.id_nilai
                            INNER JOIN sipenkp_jadwal j ON kp.id_jadwal = j.id_jadwal
                            INNER JOIN t_mahasiswa m ON m.nim = kp.nim
                            INNER JOIN t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                            INNER JOIN sipenkp_pembimbing p ON p.id_pembimbing_mhs = kp.id_pembimbing
                            INNER JOIN (SELECT nopeg AS nopegpenguji, nama AS dosen_penguji FROM t_peg_itb) dp2 on j.penguji_1 = nopegpenguji
                            LEFT JOIN (SELECT nopeg AS nopegpenguji2, nama AS dosen_penguji2 FROM t_peg_itb) dp3 on j.penguji_2 = nopegpenguji2
                            INNER JOIN (SELECT nopeg AS nopegpembimbing, nama AS dosen_pembimbing FROM t_peg_itb) p2 on p.no_peg = nopegpembimbing
                            WHERE kp.status_kp = '122' and kp.status_nilai_penguji = 1 
                            AND kp.status_nilai_perusahaan = 1 
                            and LEFT(kp.nim, 3) = '$param->kodeprodi' 
                            $filterSemester
                            and j.tanggal BETWEEN '$tglawal' AND '$tglakhir'";

            // if($where){
            //     $sql .= " WHERE $where ";
            // }
            // print_r($sql);die;
            
			$stmt     = $this->_db->query($sql);
			$getData  = $stmt->execute();
			
			$listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    /* cek duplikasi data */
    public function loadDateNow(){
       
        $result = new Result();

        try {
            
            // $stmt      = $this->_db->query(" SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH12:MI:SS') AS DATES ");
            $stmt      = $this->_db->query(" SELECT CURRENT_TIMESTAMP AS DATES ");
                        
            $proced    = $stmt->execute();
    
            $res       = $proced->current();
          
            if ($res) {
                
                // $this->date = date('Y-m-d H:i:s', strtotime($res['dates']));

                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $res['dates'];
                
                
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Laminas\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    
    public function saveGlobal($atribut, $table, $posgre = null){
        
        $result     = new Result();
        // print_r($atribut);die;
        try {

            $insert     = $this->_sql->insert()
                            ->into($table)
                            ->values($atribut);
            // echo str_replace('"','',$insert->getSqlString());
            // die;
           
            $res        = $this->execute($insert);
            
            if($posgre){

                $sequence  = $table.'_seq';
                
                $stmt      = $this->_db->query(" SELECT Currval('$sequence') LIMIT 1 ");

                $proced    = $stmt->execute();

                $res       = $proced->current();
            
                if($res['currval']){
                    $result->code = $result::CODE_SUCCESS;
                    $result->info = $result::INFO_SUCCESS;
                    $result->data = $res['currval'];
                }else{
                    $result->code = $result::CODE_FAILED;
                    $result->info = $result::INFO_FAILED;
                }

                return $result;

            }else{
                if($res->getGeneratedValue()){
                    $result->code = $result::CODE_SUCCESS;
                    $result->info = $result::INFO_SUCCESS;
                    $result->data = $res->getGeneratedValue();
                }else{
                    $result->code = $result::CODE_FAILED;
                    $result->info = $result::INFO_FAILED;
                }
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        
        return $result;

    }

    public function updateGlobal($tabel, $data, $where){
        
        $result = new Result();

        try {

            $update = $this->_sql->update()
                        ->table($tabel)
                        ->set($data)
                        ->where($where);

            // echo str_replace('"','',$update->getSqlString());die;
            // print_r($update);die;
            $return = $this->execute($update);

            // print_r($return->getAffectedRows());die;
            if($return->getAffectedRows()){
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
            }else{
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }

        return $result;
    }

    public function getLastSeqPostgree($tabel, $column){

        $result     = new Result();

        try {

            $sql        = " select max($column) as total from $tabel ";
            
            $stmt       = $this->_db->query($sql);

            $proced     = $stmt->execute();

            $seq        = $proced->current();
            // print_r($seq);die;
            if ($seq) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $seq['total'];            
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = 'Seq error';
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }

        return $result;

    }

    /* cek duplikasi data */
    public function checkDuplicateData($table, $column, $msg){
       
        $result = new Result();

        try {
            
            $select = $this->select()
                    ->from($table)
                    ->where($column);
            //echo str_replace('"','',$select->getSqlString());die;
            $return = $this->fetchAll($select);
            if ($return) {
                
                $result->code = 100;
                $result->info = 'DUPLICATE "'.$msg.'" ';
                $result->data = $return;
                
                
            } else {
                $result->code = 0;
                $result->info = 'nok';
            }
            
        }catch (\Laminas\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }


    public function loadParam($paramtype = NULL, $param_val3 = null, $param_parent = null){
        
        $result = new Result();
        
        try {
				$select = $this->select()
						->from(array('' => 'master_parameter'));
                if($paramtype){
                    $select->where(array($this->_('param_type', '') . " = '" .$paramtype."'"));
                }
                if($param_val3){
                    $select->where(array($this->_('param_val3', '') . " = '" .$param_val3."'"));
                }
                if($param_parent){
                    $select->where(array($this->_('param_parent', '') . " = '" .$param_parent."'"));
                }

                $select->order(array('idm_parameter ASC'));
			// echo str_replace('"','',$select->getSqlString());die;

			$return = $this->fetchAll($select);

            if ($return) {

                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $return;


            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }

        }catch (\Laminas\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loadSurveyProfilePartialAdmin($judul = null, $publish = null, $start = null, $limit = null, $count = null, $order = null, $sort = null){
        
        $result = new Result();
        
        try{

            if($order == 1){
                $order_name = 'judul';
            }else{
                $order_name = 'create_date';
            }

            if($sort == 1){
                $name_sort = 'ASC';
            }else{
                $name_sort = 'DESC';
            }
        
           

            $listdata = array();

            if($count){
                
                $sql = "SELECT COUNT(*) AS total FROM order_profile WHERE id_profile IS NOT NULL";

                if($judul){
                    $sql .= " AND judul IILIKE '%$judul%' ";
                }

                if($publish){
                    $sql .= " AND publish ='$publish' ";
                }

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val['total']);
    
                }

            }else{
                $sql = "SELECT * FROM order_profile WHERE id_profile IS NOT NULL";

                if($judul){
                    $sql .= " AND judul IILIKE '%$judul%' ";
                }

                if($publish){
                    $sql .= " AND publish ='$publish' ";
                }

                if($start != null){
					$sql .= " ORDER BY $order_name $name_sort LIMIT $limit OFFSET $start  ";
				}else{
					$sql .= " ORDER BY $order_name $name_sort  ";
				}
                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadElementQue($profileID, $lvl){
        
        $result = new Result();
        
        try{

            $sql = " SELECT 
                        j.paramid,
                        j.object_label AS questions,
                        string_agg ( DISTINCT b.object_label, '<br/> ' ) AS object_string,
                        string_agg ( DISTINCT b.object_value, '<br/> ' ) AS object_value,
                        string_agg ( DISTINCT b.object_value, ',' ) AS is_opt,
                        string_agg ( DISTINCT b.paramid::VARCHAR, ', ' ) AS is_values,
                        j.x_answer,
                        j.x_difficult,
                        j.x_quality,
                        j.x_status,
                        j.sortingx
                    FROM
                        (
                        SELECT DISTINCT
                            op.id_profile,
                            mp.paramid,
                            mp.object_label,
                            mp.x_answer,
                            mp.x_quality,
                            mp.x_difficult,
                            mp.x_status,
                            mp.sortingx
                        FROM
                            order_profile op,
                            management_parameter mp 
                        WHERE
                            op.id_profile = mp.profile_id 
                            AND id_profile = $profileID AND has_parent = 0 ";

                            if($lvl != 0){
                                $sql .= " AND mp.x_difficult=$lvl ";
                            }

                            $sql .= " ORDER BY mp.sortingx ASC ";

            $sql .=     ") j,
                        ( SELECT has_parent, paramid, object_label, object_value FROM management_parameter WHERE profile_id = $profileID AND has_parent != 0 ) b 
                    WHERE
                        j.paramid = b.has_parent 
                    GROUP BY
                        j.paramid,
                        j.object_label,
                        j.x_answer,
                        j.x_difficult,
                        j.x_quality,
                        j.x_status,
                        j.sortingx ";
           
            $stmt     = $this->_db->query($sql);           
            $getData  = $stmt->execute();
            
            $listdata = array();
            foreach($getData as $val){
                array_push($listdata, $val);
            }
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadQueSort($profileID){
        
        $result = new Result();
        
        try{

            $sql = " SELECT DISTINCT
                        op.id_profile,
                        mp.paramid,
                        mp.object_label,
                        mp.x_answer,
                        mp.x_quality,
                        mp.x_difficult,
                        mp.sortingx,
                        mp.x_status
                    FROM
                        order_profile op,
                        management_parameter mp 
                    WHERE
                        op.id_profile = mp.profile_id 
                        AND id_profile = $profileID AND has_parent = 0 ORDER BY mp.sortingx ASC ";
            
            $stmt     = $this->_db->query($sql);           
            $getData  = $stmt->execute();
            
            $listdata = array();
            foreach($getData as $val){
                array_push($listdata, $val);
            }
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadMateriPartial($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = " SELECT COUNT(*) as total FROM materi_data WHERE idmateri != 0";

            if($search){
                $sql1 .= " AND titles ILIKE '%$search%' ";
            }

            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

                $sql = " SELECT
                                md.idmateri,
                                md.titles,
                                md.create_date,
                                ma.file_name,
                                ma.file_type,
                                ma.file_dir,
                                CONCAT(ma.file_size,' Mb') AS file_size,
                                ma.file_dir_c,
                                ma.file_ext
                            FROM
                                materi_data md,
                                materi_attachments ma 
                            WHERE
                                md.idmateri != 0 
                                AND md.idmateri = ma.idmateri";

            if($search){
                $sql .= " AND md.titles ILIKE '%$search%' ";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadDosen($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = " SELECT COUNT(*) as total FROM dosen WHERE id_dosen != 0 ";

            if($search){
                $sql1 .= " AND nama_dosen ILIKE '%$search%' OR nid ILIKE '%$search%'";
            }

            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = " SELECT
                            *
                        FROM
                            dosen
                            WHERE id_dosen !=0
                            ";

            if($search){
                $sql .= " AND nama_dosen ILIKE '%$search%' OR nid ILIKE '%$search%'";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadMahasiswaS($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $objectFilter = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $filters = "";
            if($objectFilter){

                $role  = $objectFilter['role'];
                $prodi = $objectFilter['prodi']; 
                if($role == 10){
                    $filters = " AND t_prodi.id_prodi = '$prodi' ";
                }
            }

            $sql1 = "   SELECT COUNT(*) AS total FROM 
                            (SELECT nim, nama, file_foto FROM t_mahasiswa) m
                        INNER JOIN 
                            t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                        INNER JOIN 
                            (SELECT * FROM sipenkp_kerja_praktik ) kp ON m.nim = kp.nim
                        FULL OUTER JOIN 
                            (SELECT idberkas,nim_mhs, isvalidtu FROM sipenkp_berkas_mahasiswa) bm on kp.id_berkas = bm.idberkas
                        FULL OUTER JOIN 
                            (SELECT param_type as statusvalid, param_val1 FROM master_parameter) mp on bm.isvalidtu = mp.param_val1
                        -- FULL OUTER JOIN 
                        --     (SELECT nim as n1, idberkas AS ownerf1, file_name as file1, file_dir as dir1, file_size as size1 FROM sipenkp_berkas) b1 ON kp.id_berkas = b1.ownerf1
                        
                        WHERE 
                            idberkas != 0 and kp.status_kp = '121'
                            $filters ";
                        
            if($search){
                $sql1 .= " AND (m.nama ILIKE '%$search%' OR m.nim ILIKE '%$search%')";
            }
            
            $stmt     = $this->_db->query($sql1);
           
            $getData  = $stmt->execute();
            // print_r($getData);die;
            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "SELECT * FROM 
                        (SELECT nim, nama, file_foto FROM t_mahasiswa) m
                    INNER JOIN 
                        t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                    INNER JOIN 
                        (SELECT * FROM sipenkp_kerja_praktik ) kp ON m.nim = kp.nim
                    FULL OUTER JOIN 
                        (SELECT idberkas,nim_mhs, isvalidtu FROM sipenkp_berkas_mahasiswa) bm on kp.id_berkas = bm.idberkas
                    FULL OUTER JOIN 
                        (SELECT param_type as statusvalid, param_val1 FROM master_parameter) mp on bm.isvalidtu = mp.param_val1
                    -- FULL OUTER JOIN 
                    --     (SELECT nim as n1, idberkas AS ownerf1, file_name as file1, file_dir as dir1, file_size as size1 FROM sipenkp_berkas) b1 ON kp.id_berkas = b1.ownerf1
                    
                    WHERE 
                        idberkas != 0 and kp.status_kp = '121'
                        $filters
                        ";
                            
            if($search){
                $sql .= " AND (m.nama ILIKE '%$search%' OR m.nim ILIKE '%$search%') ";
            }
            // print_r($sql);die;
            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function daftarMahasiswa($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = '   SELECT COUNT(*) AS total FROM t_mahasiswa 
            INNER JOIN t_prodi ON LEFT(t_mahasiswa.nim, 3) = t_prodi.id_prodi';

            if($search){
                $sql1 .= " WHERE ".'nim'." ILIKE '%$search%' OR ".'nama'." ILIKE '%$search%'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }
           
            $totalFilter = $rows;

            $sql = 'SELECT * FROM t_mahasiswa 
                        INNER JOIN t_prodi ON LEFT(t_mahasiswa.nim, 3) = t_prodi.id_prodi';
                            
            if($search){
                $sql .= " WHERE ".'nama'." ILIKE '%$search%' OR ".'nim'." ILIKE '%$search%'";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }



            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function listbimbingan($nopeg = null, $col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = "   SELECT count(*) as total FROM sipenkp_kerja_praktik kp
                        INNER JOIN t_mahasiswa m ON m.nim = kp.nim
                        INNER JOIN sipenkp_pembimbing p ON p.id_pembimbing_mhs = kp.id_pembimbing
                        WHERE  kp.status_kp = '121' AND p.no_peg = '$nopeg'";

            if($search){
                $sql1 .= " AND ".'kp.nim'." ILIKE '%$search%' OR ".'m.nama'." ILIKE '%$search%' OR ".'kp.judul_laporan'." ILIKE '%$search%'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "SELECT * FROM sipenkp_kerja_praktik kp
                    INNER JOIN t_mahasiswa m ON m.nim = kp.nim
                    INNER JOIN sipenkp_pembimbing p ON p.id_pembimbing_mhs = kp.id_pembimbing
                    WHERE  kp.status_kp = '121' AND p.no_peg = '$nopeg'";
                            
            if($search){
                $sql .= " AND ".'m.nama'." ILIKE '%$search%' OR ".'kp.nim'." ILIKE '%$search%' OR ".'kp.judul_laporan'." ILIKE '%$search%'";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }


            

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loaduserlist($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = '   SELECT COUNT(*) AS total FROM user_data_header
                        INNER JOIN (SELECT name as nama_role, access_role_code FROM user_data_role) r on user_data_header."role" = CAST(r.access_role_code AS INTEGER)';

            if($search){
                $sql1 .= " WHERE ".'username'." ILIKE '%$search%' OR ".'name'." ILIKE '%$search%'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = 'SELECT * FROM user_data_header
                    INNER JOIN (SELECT name as nama_role, access_role_code FROM user_data_role) r on user_data_header."role" = CAST(r.access_role_code AS INTEGER)';
                            
            if($search){
                $sql .= " WHERE ".'nama'." ILIKE '%$search%' OR ".'nim'." ILIKE '%$search%'";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }



            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function listpengajuan($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $nim, $isData = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;
            $prodi      = $isData->prodi;
            $iPoly      = $isData->iPoly;

            // print_r($isData);die;
            $sql1 = "   SELECT COUNT(*) as total FROM sipenkp_pengajuan_mahasiswa pm
                        INNER JOIN t_prodi prod ON LEFT(pm.nim, 3) = prod.id_prodi
                        INNER JOIN (SELECT param_val2, param_type as status FROM master_parameter) mp on pm.status_pengajuan = mp.param_val2 INNER JOIN t_mahasiswa m on pm.nim = m.nim
                        FULL OUTER JOIN (SELECT id_perusahaanmhs, id_perusahaan, nim as nim_mhs, file_path, file_path_c FROM sipenkp_perusahaan_mahasiswa) pmah on pmah.nim_mhs = m.nim
                        FULL OUTER JOIN sipenkp_perusahaan p on p.id_perusahaan = pmah.id_perusahaan ";
            
            // if($nim == null){
            //     $sql1 .= "INNER JOIN ".'t_mahasiswa'." m on pm.nim = m.".'nim ';
            // }
            
            $sql1 .= "WHERE pm.id_pengajuan != 0  ";
            // if($nim == null){
            //     $sql1 .= " AND pm.status_pengajuan = 91 or pm.status_pengajuan = 92 or pm.status_pengajuan = 102 or pm.status_pengajuan = 101 or pm.status_pengajuan = 103 ";
            // }
            if($prodi != null){
                $sql1 .= "AND prod.id_prodi = '$prodi'";
            }
                            
            if($nim){
                $sql1 .= "AND pm.nim = '$nim'";
            }
            // print_r($sql);die;
            if($search){
                if(is_numeric($search)){
                    $sql1 .= " AND m.".'nim'." = '$search' ";
                }else{
                    $sql1 .= " AND (m.".'nama'." ILIKE '%$search%' OR pm.nama_perusahaan ILIKE '%$search%') ";
                }
            }



          
            

            // print_r($sql1);die;
            
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
            // print_r($sql1);die;
           
            $totalFilter = $rows;

            $sql = "    SELECT * FROM sipenkp_pengajuan_mahasiswa pm
                        INNER JOIN t_prodi prod ON LEFT(pm.nim, 3) = prod.id_prodi
                        INNER JOIN (SELECT param_val2, param_type as status FROM master_parameter) mp on pm.status_pengajuan = mp.param_val2 INNER JOIN t_mahasiswa m on pm.nim = m.nim
                        FULL OUTER JOIN (SELECT id_perusahaanmhs, id_perusahaan, nim as nim_mhs, file_path, file_path_c FROM sipenkp_perusahaan_mahasiswa) pmah on pmah.id_perusahaanmhs = pm.id_perusahaanmhs
                        FULL OUTER JOIN sipenkp_perusahaan p ON p.id_perusahaan = pmah.id_perusahaan
                    ";
            
            // if($nim == null){
            //     $sql .= "INNER JOIN ".'t_mahasiswa'." m on pm.nim = m.".'nim ';
            // }
            
            $sql .= "WHERE pm.id_pengajuan != 0   ";
            // if($nim == null){
            //     $sql .= " AND pm.status_pengajuan = 91 or pm.status_pengajuan = 92 or pm.status_pengajuan = 102 or pm.status_pengajuan = 101 or pm.status_pengajuan = 103 ";
            // }
                            // print_r($sql);die;
            if($nim){
                $sql .= "AND pm.nim = '$nim'";
            }
            // print_r($sql);die;

            if($prodi != null){
                $sql .= "AND prod.id_prodi = '$prodi'";
            }

            if($search){
                if(is_numeric($search)){
                    $sql .= " AND m.".'nim'." = '$search' ";
                }else{
                    $sql .= "  AND (m.".'nama'." ILIKE '%$search%' OR pm.nama_perusahaan ILIKE '%$search%') ";
                }
                
            }

            // print_r($sql);die;

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." DESC  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                    array_push($listdata, $val);
                
                

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function listpengajuanditolak($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $nim, $isData = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;
            $prodi      = $isData->prodi;
            $iPoly      = $isData->iPoly;

            // print_r($isData);die;

            $sql1 = "   SELECT COUNT(*) as total FROM sipenkp_pengajuan_mahasiswa pm
                        INNER JOIN t_prodi prod ON LEFT(pm.nim, 3) = prod.id_prodi
                        INNER JOIN (SELECT param_val2, param_type as status FROM master_parameter) mp on pm.status_pengajuan = mp.param_val2 INNER JOIN t_mahasiswa m on pm.nim = m.nim
                        FULL OUTER JOIN (SELECT id_perusahaanmhs, id_perusahaan, nim as nim_mhs, file_path, file_path_c FROM sipenkp_perusahaan_mahasiswa) pmah on pmah.nim_mhs = m.nim
                        FULL OUTER JOIN sipenkp_perusahaan p on p.id_perusahaan = pmah.id_perusahaan ";
            
            // if($nim == null){
            //     $sql1 .= "INNER JOIN ".'t_mahasiswa'." m on pm.nim = m.".'nim ';
            // }
            
            $sql1 .= "WHERE pm.id_pengajuan != 0 AND status_pengajuan = '102' ";
            // if($nim == null){
            //     $sql1 .= " AND pm.status_pengajuan = 91 or pm.status_pengajuan = 92 or pm.status_pengajuan = 102 or pm.status_pengajuan = 101 or pm.status_pengajuan = 103 ";
            // }
            if($prodi != null){
                $sql1 .= "AND prod.id_prodi = '$prodi'";
            }
                            
            if($nim){
                $sql1 .= "AND pm.nim = '$nim'";
            }
            // print_r($sql);die;
            if($search){
                if(is_numeric($search)){
                    $sql1 .= " AND m.".'nim'." = '$search' ";
                }else{
                    $sql1 .= "  AND (m.".'nama'." ILIKE '%$search%' OR pm.nama_perusahaan ILIKE '%$search%') ";
                }
            }



          
            

            // print_r($sql1);die;
            
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
            // print_r($sql1);die;
           
            $totalFilter = $rows;

            $sql = "    SELECT * FROM sipenkp_pengajuan_mahasiswa pm
                        INNER JOIN t_prodi prod ON LEFT(pm.nim, 3) = prod.id_prodi
                        INNER JOIN (SELECT param_val2, param_type as status FROM master_parameter) mp on pm.status_pengajuan = mp.param_val2 INNER JOIN t_mahasiswa m on pm.nim = m.nim
                        FULL OUTER JOIN (SELECT id_perusahaanmhs, id_perusahaan, nim as nim_mhs, file_path, file_path_c FROM sipenkp_perusahaan_mahasiswa) pmah on pmah.id_perusahaanmhs = pm.id_perusahaanmhs
                        FULL OUTER JOIN sipenkp_perusahaan p ON p.id_perusahaan = pmah.id_perusahaan
                    ";
            
            // if($nim == null){
            //     $sql .= "INNER JOIN ".'t_mahasiswa'." m on pm.nim = m.".'nim ';
            // }
            
            $sql .= "WHERE pm.id_pengajuan != 0  AND status_pengajuan = '102'  ";
            // if($nim == null){
            //     $sql .= " AND pm.status_pengajuan = 91 or pm.status_pengajuan = 92 or pm.status_pengajuan = 102 or pm.status_pengajuan = 101 or pm.status_pengajuan = 103 ";
            // }
                            // print_r($sql);die;
            if($prodi != null){
                // print_r("here");die;
                $sql .= "AND prod.id_prodi = '$prodi'";
            }
            
            if($nim){
                $sql .= "AND pm.nim = '$nim'";
            }
            // print_r($sql);die;
            if($search){
                
                if(is_numeric($search)){
                    $sql .= " AND m.".'nim'." = '$search' ";
                }else{
                    $sql .= "  AND (m.".'nama'." ILIKE '%$search%' OR pm.nama_perusahaan ILIKE '%$search%') ";
                }
            }

            // print_r($sql);die;

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." DESC  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                    array_push($listdata, $val);
                
                

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function listperusahaan($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $nim){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(pm.nama_perusahaan) as total
                    FROM 
                        sipenkp_pengajuan_mahasiswa pm
                    WHERE
                        pm.status_pengajuan != 102 
                    ";
            
            
            // print_r($sql1);die;
            // $sql1 .= "WHERE pm.id_pengajuan != 0  ";
        
                            
            
            
            if($search){
                $sql1 .= " AND pm.".'nama_perusahaan'." ILIKE '%$search%' ";
            }



          
            

            // print_r($sql1);die;
            
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
            // print_r($sql1);die;
           
            $totalFilter = $rows;

            $sql = " SELECT 
                        p.nama_perusahaan_kp, 
                        p.alamat,
                        p.kode_pos,
                        p.no_telp,
                        COUNT(*) FILTER (WHERE pm.id_pengajuan = kp.id_pengajuan AND kp.status_kp != '123') AS jumlah_mahasiswa
                        
                    FROM 
                        sipenkp_perusahaan p,
                        sipenkp_pengajuan_mahasiswa pm,
                        sipenkp_kerja_praktik kp
                    WHERE
                        pm.nama_perusahaan = p.nama_perusahaan_kp AND pm.status_pengajuan != 102
                    GROUP BY 
                        p.nama_perusahaan_kp, 
                        p.alamat, 
                        p.no_telp, 
                        p.kode_pos 

                    ";
            
            
            
            
            if($search){
                
                $sql .= " AND pm.".'nama_perusahaan_kp'." ILIKE '%$search%' ";
            }

            // print_r($sql);die;

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." DESC  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                    array_push($listdata, $val);
                
                

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function listpilihpembimbing($kodeprodi = null, $col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $nim){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = "   SELECT COUNT(*) AS total FROM  sipenkp_kerja_praktik kp
                        INNER JOIN t_mahasiswa m on kp.nim = m.nim
                        INNER JOIN t_prodi p ON LEFT(m.nim, 3) = p.id_prodi
                        FULL OUTER JOIN (SELECT id_pembimbing_mhs, nim as nim_mhs, no_peg, status_pembimbing FROM sipenkp_pembimbing) sp on sp.id_pembimbing_mhs = kp.id_pembimbing
                        FULL OUTER JOIN (SELECT nopeg, nama AS nama_dosen, id_sta_peg, gelar_dpn, gelar_blk FROM t_peg_itb) dosen on dosen.nopeg = sp.no_peg 
                        FULL OUTER JOIN master_parameter mp ON sp.status_pembimbing = mp.param_val1
                        WHERE kp.id_kp != 0 AND kp.status_kp = '121'";

            if($kodeprodi){
                $sql1 .= " AND LEFT(m.nim,3) = '$kodeprodi' ";
            }
           
            if($search){
                $sql1 .= " AND m.".'nama'." ILIKE '%$search%' OR p.nama_prodi ILIKE '%$search%' OR m.nim = '$search' AND kp.status_kp = '121'";
            }

          
            

            // print_r($sql1);die;
            
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
            // print_r($sql1);die;
           
            $totalFilter = $rows;

            $sql = "    SELECT * FROM sipenkp_kerja_praktik kp
                        INNER JOIN t_mahasiswa m on kp.nim = m.nim
                        INNER JOIN t_prodi p ON LEFT(m.nim, 3) = p.id_prodi
                        FULL OUTER JOIN (SELECT id_pembimbing_mhs, nim as nim_mhs, no_peg, status_pembimbing FROM sipenkp_pembimbing) sp on sp.id_pembimbing_mhs = kp.id_pembimbing
                        FULL OUTER JOIN (SELECT nopeg, nama AS nama_dosen, id_sta_peg, gelar_dpn, gelar_blk FROM t_peg_itb) dosen on dosen.nopeg = sp.no_peg 
                        FULL OUTER JOIN master_parameter mp ON sp.status_pembimbing = mp.param_val1
                        WHERE kp.id_kp != 0 AND kp.status_kp = '121' "; 
            
            // print_r($sql);die;
            if($kodeprodi){
                $sql .= " AND LEFT(m.nim,3) = '$kodeprodi' ";
            }
            if($search){
                $sql .= " AND (m.".'nama'." ILIKE '%$search%' OR p.nama_prodi ILIKE '%$search%' OR m.nim = '$search')";
            }

            // print_r($sql);die;

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function reviewpengajuan($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $nim){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = " SELECT COUNT(*) as total FROM sipenkp_pengajuan_mahasiswa pm
                        INNER JOIN (SELECT param_val2, param_type as status FROM master_parameter) mp on pm.status_pengajuan = mp.param_val2 INNER JOIN ".'t_mahasiswa'." m on pm.nim = m.".'nim'."
                        FULL OUTER JOIN sipenkp_perusahaan_mahasiswa pmah on pmah.nim = m.".'nim'."
                        FULL OUTER JOIN sipenkp_perusahaan p on p.id_perusahaan = pmah.id_perusahaan  ";
            
            // if($nim == null){
            //     $sql1 .= "INNER JOIN ".'t_mahasiswa'." m on pm.nim = m.".'nim ';
            // }
            
            $sql1 .= "WHERE pm.id_pengajuan != 0  ";
            if($nim == null){
                $sql1 .= "AND (pm.status_pengajuan = 102 or pm.status_pengajuan = 101 or pm.status_pengajuan = 103) ";
            }
                            
            if($nim){
                $sql1 .= "AND pm.nim = '$nim'";
            }
            // print_r($sql);die;
            if($search){
                $sql1 .= " AND m.".'nama'." ILIKE '%$search%' ";
            }

          
            

            // print_r($sql1);die;
            
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
            // print_r($sql1);die;
           
            $totalFilter = $rows;

            $sql = " SELECT * FROM sipenkp_pengajuan_mahasiswa pm
                        INNER JOIN (SELECT param_val2, param_type as status FROM master_parameter) mp on pm.status_pengajuan = mp.param_val2 INNER JOIN ".'t_mahasiswa'." m on pm.nim = m.".'nim'."
                        FULL OUTER JOIN sipenkp_perusahaan_mahasiswa pmah on pmah.nim = m.".'nim'."
                        FULL OUTER JOIN sipenkp_perusahaan p on p.id_perusahaan = pmah.id_perusahaan  ";
            
            // if($nim == null){
            //     $sql .= "INNER JOIN ".'t_mahasiswa'." m on pm.nim = m.".'nim ';
            // }
            
            $sql .= "WHERE pm.id_pengajuan != 0   ";
            // if($nim == null){
            //     $sql .= " AND pm.status_pengajuan = 91 or pm.status_pengajuan = 92 or pm.status_pengajuan = 102 or pm.status_pengajuan = 101 or pm.status_pengajuan = 103 ";
            // }
                            
            if($nim){
                $sql .= "AND pm.nim = '$nim'";
            }
            // print_r($sql);die;
            if($search){
                
                $sql .= " AND m.".'nama'." ILIKE '%$search%' ";
            }

            // print_r($sql);die;

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadjadwallist($objectFilter = null, $col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            if($object){
                
                $role  = $object['role'];
                $prodi = $object['prodi']; 

                // if($role == 10){
                //     $filters = 
                // }
            }


            $sql1 = "   SELECT COUNT (*) as total FROM sipenkp_jadwal j 
                        INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                        INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                        INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                        LEFT JOIN (SELECT nopeg AS nidp3, nama AS namapenguji2 FROM t_peg_itb) dp3 on j.penguji_2 = nidp3
                        INNER JOIN (SELECT nopeg AS nidp2, nama AS dosen_pembimbing FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                        INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim 
                        WHERE j.id_jadwal != 0 AND kp.status_kp = '121'
                        ";
            if($prodi){
                $sql1 .= " AND LEFT(m.nim,3) = '$prodi' ";
            }
            
            if($search){
                $sql1 .= " AND m.".'nim'." IILIKE '%$search%' OR m.".'nama'." IILIKE '%$search%' AND kp.status_kp = '121'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "SELECT * FROM sipenkp_jadwal j 
                    INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                    INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                    INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                    LEFT JOIN (SELECT nopeg AS nidp3, nama AS namapenguji2 FROM t_peg_itb) dp3 on j.penguji_2 = nidp3
                    INNER JOIN (SELECT nopeg AS nidp2, nama AS dosen_pembimbing FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                    INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim 
                    WHERE j.id_jadwal != 0 AND kp.status_kp = '121'
                    ";
            if($prodi){
                $sql .= " AND LEFT(m.nim,3) = '$prodi' ";
            }  
            if($search){
                $sql .= " AND m.".'nim'." IILIKE '%$search%' OR m.".'nama'." IILIKE '%$search%' AND kp.status_kp = '121'";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadjadwalperm($id){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            //kalo ada id
            
                
                $sql = "SELECT * FROM sipenkp_jadwal j 
                        INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                        INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                        INNER JOIN (SELECT nopeg AS nidp1, concat(gelar_dpn, ' ', nama, ', ', gelar_blk) AS namapenguji1, email AS emailpenguji, gelar_dpn as dpenguji, gelar_blk as bpenguj FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                        LEFT JOIN (SELECT nopeg AS nidp3, concat(gelar_dpn, ' ', nama, ', ', gelar_blk) AS namapenguji2, email AS emailpenguji2, gelar_dpn as dpenguji2, gelar_blk as bpenguj2 FROM t_peg_itb) dp3 on j.penguji_2 = nidp3
                        INNER JOIN (SELECT nopeg AS nidp2, concat(gelar_dpn, ' ', nama, ', ', gelar_blk) AS dosen_pembimbing, email AS emailpembimbing, gelar_dpn as dpembimbing, gelar_blk as bpembimbing FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                        INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim 
                        WHERE j.id_jadwal != 0 AND kp.status_kp = '121' AND kp.id_kp = $id";

                
                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val);
    
                }

            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadnilaiperusahaan($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $objectFilter = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $filters = '';
            if($objectFilter){
                $role  = $objectFilter['role'];
                $prodi = $objectFilter['prodi']; 

                if($role == 10){
                    $filters = " AND LEFT(m.nim, 3) = '$prodi' ";
                }
            }

            $sql1 = "   SELECT COUNT(*) AS total FROM sipenkp_jadwal j 
                        INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                        INNER JOIN sipenkp_pengajuan_mahasiswa pm ON kp.id_pengajuan = pm.id_pengajuan 
                        INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                        INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim
                        INNER JOIN sipenkp_perusahaan_mahasiswa sp ON pm.id_perusahaanmhs = sp.id_perusahaanmhs
                        INNER JOIN sipenkp_perusahaan spm ON spm.id_perusahaan = sp.id_perusahaan
                        WHERE j.id_jadwal != 0 AND kp.status_kp = '121' $filters 
                        ";

            if($search){
                $sql1 .= " AND (m.".'nim'." ILIKE '%$search%' OR m.".'nama'." ILIKE '%$search%')";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "SELECT * FROM sipenkp_jadwal j 
                    INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                    INNER JOIN sipenkp_pengajuan_mahasiswa pm ON kp.id_pengajuan = pm.id_pengajuan 
                    INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                    INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim
                    INNER JOIN sipenkp_perusahaan_mahasiswa sp ON pm.id_perusahaanmhs = sp.id_perusahaanmhs
                    INNER JOIN sipenkp_perusahaan spm ON spm.id_perusahaan = sp.id_perusahaan
                    WHERE j.id_jadwal != 0 AND kp.status_kp = '121' 
                    $filters ";
                            
            if($search){
                $sql .= " AND (m.".'nim'." ILIKE '%$search%' OR m.".'nama'." ILIKE '%$search%') ";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadnilaipenguji($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = "   SELECT COUNT (*) as total FROM sipenkp_jadwal j 
                        INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                        INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                        INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                        INNER JOIN (SELECT nopeg AS nidp2, nama AS dosen_pembimbing FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                        INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim 
                        WHERE j.id_jadwal != 0 AND kp.status_kp = '121' 
                        ";

            if($search){
                $sql1 .= " AND ((m.".'nim'." ILIKE '%$search%' OR m.".'nama'." ILIKE '%$search%'))";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "SELECT * FROM sipenkp_jadwal j 
                    INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                    INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                    INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                    INNER JOIN (SELECT nopeg AS nidp2, nama AS dosen_pembimbing FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                    INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim 
                    WHERE j.id_jadwal != 0 AND kp.status_kp = '121' 
                    ";
                            
            if($search){
                $sql .= " AND ((m.".'nim'." ILIKE '%$search%' OR m.".'nama'." ILIKE '%$search%')) ";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadnilaipengujiasdsn($peran, $nopeg, $col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = "   SELECT COUNT (*) as total FROM sipenkp_jadwal j 
                        INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                        INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                        LEFT  JOIN ( SELECT nopeg AS nidp3, nama AS namapenguji2 FROM t_peg_itb ) dp3 ON j.penguji_2 = nidp3
                        INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                        INNER JOIN (SELECT nopeg AS nidp2, nama AS dosen_pembimbing FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                        INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim 
                        WHERE j.id_jadwal != 0 AND (kp.status_kp = '121' OR kp.status_kp = '122')  ";
           
           if($peran == 'pembimbing'){
                $sql1 .= " AND p2.nidp2 = '$nopeg'";
            } else if($peran == 'penguji'){
                $sql1 .= " AND (dp2.nidp1 = '$nopeg' OR dp3.nidp3 = '$nopeg') ";
            }

            if($search){
                $sql1 .= " AND (m.".'nim'." ILIKE '%$search%' OR m.".'nama'." ILIKE '%$search%')";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "SELECT
                        z.* ,
                        CASE
                            
                            WHEN z.nidp1 = '$nopeg' THEN
                            'penguji_1' ELSE 'penguji_2' 
                        END AS penguji_ke 
                    FROM
                        (
                        SELECT
                            * 
                        FROM
                            sipenkp_jadwal j
                            INNER JOIN sipenkp_kerja_praktik kp ON j.id_jadwal = kp.id_jadwal
                            INNER JOIN sipenkp_pembimbing P ON kp.id_pembimbing = P.id_pembimbing_mhs
                            INNER JOIN ( SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb ) dp2 ON j.penguji_1 = nidp1
                            LEFT JOIN ( SELECT nopeg AS nidp3, nama AS namapenguji2 FROM t_peg_itb ) dp3 ON j.penguji_2 = nidp3
                            INNER JOIN ( SELECT nopeg AS nidp2, nama AS dosen_pembimbing FROM t_peg_itb ) p2 ON P.no_peg = p2.nidp2
                            INNER JOIN t_mahasiswa M ON j.nim_mhs = M.nim
                            INNER JOIN t_prodi ON LEFT ( M.nim, 3 ) = t_prodi.id_prodi
                            INNER JOIN sipenkp_nilai_mahasiswa snm ON kp.id_nilai = snm.id_nilai 
                        WHERE
                            j.id_jadwal != 0 
                            AND ( kp.status_kp = '121' OR kp.status_kp = '122' ) ";

            if($peran == 'pembimbing'){
                $sql .= " AND p2.nidp2 = '$nopeg'";
            } else if($peran == 'penguji'){
                $sql .= " AND (dp2.nidp1 = '$nopeg' OR dp3.nidp3 = '$nopeg')";
            }
                            
            if($search){
                $sql .= " AND (m.".'nim'." ILIKE '%$search%' OR m.".'nama'." ILIKE '%$search%') ";
            }

            $sql .= " ) AS z";
            // print_r($sql);die;
            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function datanilaiakhir($kodeprodi = null, $col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = "   SELECT COUNT(*) as total FROM sipenkp_kerja_praktik kp
                        INNER JOIN sipenkp_nilai_mahasiswa nm ON kp.id_nilai = nm.id_nilai
                        INNER JOIN sipenkp_jadwal j ON kp.id_jadwal = j.id_jadwal
                        INNER JOIN t_mahasiswa m ON m.nim = kp.nim
                        INNER JOIN t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                        INNER JOIN sipenkp_pembimbing p ON p.id_pembimbing_mhs = kp.id_pembimbing
                        INNER JOIN (SELECT nopeg AS nopegpenguji, nama AS dosen_penguji, gelar_blk as pengujigb, gelar_dpn as pengujigd FROM t_peg_itb) dp2 on j.penguji_1 = nopegpenguji
                        INNER JOIN (SELECT nopeg AS nopegpembimbing, nama AS dosen_pembimbing, gelar_blk as pembimbinggb, gelar_dpn as pembimbinggd FROM t_peg_itb) p2 on p.no_peg = nopegpembimbing
                        LEFT JOIN ( SELECT nopeg AS nopegpenguji2, nama AS dosen_penguji2, gelar_blk AS pengujigb2, gelar_dpn AS pengujigd2 FROM t_peg_itb ) dp3 ON j.penguji_2 = nopegpenguji2
                        WHERE kp.status_kp != '123' and kp.status_nilai_penguji = 1  
                        -- AND kp.status_nilai_pembimbing = 1 
                        AND kp.status_nilai_perusahaan = 1 
                        -- AND kp.status_nilai_penguji_2 = 1
                                    ";
            if($kodeprodi){
                $sql1 .= " AND LEFT(m.nim,3) = '$kodeprodi' ";
            }
            if($search){
                $sql1 .= " AND (m.".'nim'." ILIKE '%$search%' OR m.".'nama'." ILIKE '%$search%')";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "SELECT * FROM sipenkp_kerja_praktik kp
                    INNER JOIN sipenkp_nilai_mahasiswa nm ON kp.id_nilai = nm.id_nilai
                    INNER JOIN sipenkp_jadwal j ON kp.id_jadwal = j.id_jadwal
                    INNER JOIN t_mahasiswa m ON m.nim = kp.nim
                    INNER JOIN t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                    INNER JOIN sipenkp_pembimbing p ON p.id_pembimbing_mhs = kp.id_pembimbing
                    INNER JOIN (SELECT nopeg AS nopegpenguji, nama AS dosen_penguji, gelar_blk as pengujigb, gelar_dpn as pengujigd FROM t_peg_itb) dp2 on j.penguji_1 = nopegpenguji
                    INNER JOIN (SELECT nopeg AS nopegpembimbing, nama AS dosen_pembimbing, gelar_blk as pembimbinggb, gelar_dpn as pembimbinggd FROM t_peg_itb) p2 on p.no_peg = nopegpembimbing  
                    LEFT JOIN ( SELECT nopeg AS nopegpenguji2, nama AS dosen_penguji2, gelar_blk AS pengujigb2, gelar_dpn AS pengujigd2 FROM t_peg_itb ) dp3 ON j.penguji_2 = nopegpenguji2  
                    WHERE 
                        kp.status_kp != '123' and 
                        kp.status_nilai_penguji = 1 AND 
                        -- kp.status_nilai_pembimbing = 1 AND 
                        kp.status_nilai_perusahaan = 1
                        -- AND kp.status_nilai_penguji_2 = 1
                            ";
            if($kodeprodi){
                $sql .= " AND LEFT(m.nim,3) = '$kodeprodi' ";
            }       
            if($search){
                $sql .= " AND (m.".'nim'." ILIKE '%$search%' OR m.".'nama'." ILIKE '%$search%')";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadmhsvalid($kodeprodi = null, $col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $objectFilter = null){
        
        $result = new Result();
        
        try{
            $whereTU = '51';
            $rows = 0;

            $fiters = '';
            if($objectFilter){
                
                $role  = $objectFilter['role'];
                $prodi = $objectFilter['prodi']; 

                // if($role == 10){
                //     $filters = 
                // }
            }

            $sql1 =  " SELECT COUNT(*) AS total
                        FROM (SELECT nim, nama FROM t_mahasiswa) m
                        INNER JOIN t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                        INNER JOIN sipenkp_kerja_praktik kp ON kp.nim = m.nim
                        FULL OUTER JOIN (SELECT idberkas,nim_mhs, isvalidtu , isjadwal FROM sipenkp_berkas_mahasiswa) bm on kp.id_berkas= bm.idberkas
                        FULL OUTER JOIN (SELECT param_type AS statusvalid, param_val1 FROM master_parameter) mp on bm.isjadwal = mp.param_val1
                        WHERE idberkas != 0 and bm.isvalidtu = '51' AND kp.status_kp = '121'";

            if($prodi){
                $sql1 .= "AND t_prodi.id_prodi = '$prodi'";
            }

            if($search){
                $sql1 .= " AND m.".'nama'." ILIKE '%$search%' OR m.nim ILIKE '%$search%'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "    SELECT *
                        FROM (SELECT nim, nama FROM t_mahasiswa) m
                        INNER JOIN t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                        INNER JOIN sipenkp_kerja_praktik kp ON kp.nim = m.nim
                        FULL OUTER JOIN (SELECT idberkas,nim_mhs, isvalidtu , isjadwal FROM sipenkp_berkas_mahasiswa) bm on kp.id_berkas= bm.idberkas
                        FULL OUTER JOIN (SELECT param_type AS statusvalid, param_val1 FROM master_parameter) mp on bm.isjadwal = mp.param_val1
                        WHERE idberkas != 0 and bm.isvalidtu = '51' AND kp.status_kp = '121'";
                           
                           
            if($prodi){
                $sql .= "AND t_prodi.id_prodi = '$prodi'";
            }
            // print_r($sql);die;
            if($search){
                $sql .= "AND m.".'nama'." ILIKE '%$search%' OR m.nim ILIKE '%$search%' ";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function buatjadwallist($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = '   SELECT COUNT(*) AS total
                        FROM (SELECT id_mhs, nim, nama, jurusan FROM mahasiswa) m
                        FULL OUTER JOIN (SELECT idberkas,nim_mhs, isvalidtu FROM berkas_mahasiswa) bm on m.nim = bm.nim_mhs
                        FULL OUTER JOIN (SELECT param_type, param_val1 FROM master_parameter) mp on bm.isvalidtu = mp.param_val1
                        FULL OUTER JOIN (SELECT idberkas AS ownerf1, file_name as file1, file_dir as dir1 FROM berkas) b1 ON m.nim = b1.ownerf1
                        -- FULL OUTER JOIN (SELECT idberkas AS ownerf2, file_name as file2, file_dir as dir2 FROM berkas_2) b2 ON m.nim = b2.ownerf2
                        -- FULL OUTER JOIN (SELECT idberkas AS ownerf3, file_name as file3, file_dir as dir3 FROM berkas_3) b3 ON m.nim = b3.ownerf3
                        -- FULL OUTER JOIN (SELECT idberkas AS ownerf4, file_name as file4, file_dir as dir4 FROM berkas_4) b4 ON m.nim = b4.ownerf4
                        WHERE m.id_mhs != 0 AND idberkas != 0 ';

            if($search){
                $sql1 .= " AND (m.nama ILIKE '%$search%' OR m.nim ILIKE '%$search%')";
            }
            
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
           
            $totalFilter = $rows;

            $sql = "SELECT *
                    FROM (SELECT id_mhs, nim, nama, jurusan FROM mahasiswa) m
                    FULL OUTER JOIN (SELECT idberkas,nim_mhs, isvalidtu FROM berkas_mahasiswa) bm on m.nim = bm.nim_mhs
                    FULL OUTER JOIN (SELECT param_type AS statusvalid, param_val1 FROM master_parameter) mp on bm.isvalidtu = mp.param_val1
                    FULL OUTER JOIN (SELECT idberkas AS ownerf1, file_name as file1, file_dir as dir1 FROM berkas) b1 ON m.nim = b1.ownerf1
                    -- FULL OUTER JOIN (SELECT idberkas AS ownerf2, file_name as file2, file_dir as dir2 FROM berkas_2) b2 ON m.nim = b2.ownerf2
                    -- FULL OUTER JOIN (SELECT idberkas AS ownerf3, file_name as file3, file_dir as dir3 FROM berkas_3) b3 ON m.nim = b3.ownerf3
                    -- FULL OUTER JOIN (SELECT idberkas AS ownerf4, file_name as file4, file_dir as dir4 FROM berkas_4) b4 ON m.nim = b4.ownerf4
                    WHERE m.id_mhs != 0 AND idberkas != 0
                        
                        ";
                            
            if($search){
                $sql .= " AND (m.nama ILIKE '%$search%' OR m.nim ILIKE '%$search%') ";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadMappingMateri($profileID){
        
        $result = new Result();
        
        try{

            $sql = "  SELECT
                        md.idmateri,
                        md.titles,
                        md.create_date,
                        ma.file_name,
                        ma.file_type,
                        ma.file_dir,
                        CONCAT ( ma.file_size, ' Mb' ) AS file_size,
                        ma.file_dir_c,
                        ma.file_ext 
                    FROM
                        order_materi mo,
                        materi_data md,
                        materi_attachments ma 
                    WHERE
                        mo.idmateri = md.idmateri 
                        AND md.idmateri = ma.idmateri 
                        AND mo.profileid = $profileID
                    ORDER BY
                        mo.create_date DESC ";
            
            $stmt     = $this->_db->query($sql);           
            $getData  = $stmt->execute();
            
            $listdata = array();
            foreach($getData as $val){
                array_push($listdata, $val);
            }
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadUser($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            if($id){
                
                $sql = "SELECT * FROM user_data_header WHERE iduser = $id";

                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val['total']);
    
                }

            }else{
                $sql = "SELECT * FROM user_data_header ORDER BY create_dtm ASC";

                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadMahasiswa($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            if($id){
                
                $sql = "SELECT * FROM ".'t_mahasiswa m'. ' INNER JOIN sipenkp_kerja_praktik kp ON m.nim = kp.nim INNER JOIN t_prodi ON LEFT (m.nim, 3) = t_prodi.id_prodi' 
                .' INNER JOIN sipenkp_pembimbing sp ON sp.nim = m.nim'.' INNER JOIN (SELECT nopeg, nama AS "NAMA_PEMB", gelar_blk, gelar_dpn FROM t_peg_itb) tp ON tp.nopeg = sp.no_peg '.
                " WHERE ".'m.nim'." = '$id'";
                // print_r($sql);die;
                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val);
    
                }

            }else{
                $sql = "SELECT * FROM ".' t_mahasiswa';

                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function selectmhs($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
                $sql = "SELECT * FROM ".'t_mahasiswa m'. ' INNER JOIN sipenkp_kerja_praktik kp ON m.nim = kp.nim INNER JOIN t_prodi ON LEFT (m.nim, 3) = t_prodi.id_prodi' 
                .' INNER JOIN sipenkp_pembimbing sp ON sp.nim = m.nim'.' INNER JOIN (SELECT nopeg, nama AS "NAMA_PEMB", gelar_blk, gelar_dpn FROM t_peg_itb) tp ON tp.nopeg = sp.no_peg '.
                " WHERE ".'m.nim'." = '$id'";
                // print_r($sql);die;
                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val);
    
                }

            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadmhsbyprodi($id = null){
        
        $result = new Result();
        
        try{
            $where = "status_pengajuan = 102";
            $data  = $this->loadTable('sipenkp_pengajuan_mahasiswa','*', $where);
            $exist = $data->data;
            // print_r($exist);die;
            $listdata  = array();
            $listcheck = array();
            $res       = array();

            

                $sql = "SELECT 
                                * 
                        FROM 
                                user_data_header, 
                                t_mahasiswa 
                        WHERE 
                                NOT EXISTS(SELECT DISTINCT nim FROM sipenkp_pengajuan_mahasiswa WHERE status_pengajuan NOT IN(102, 105, 106, 123, NULL) AND status_pengajuan != 122 AND t_mahasiswa.nim = sipenkp_pengajuan_mahasiswa.nim)
                                AND
                                user_data_header.nim = t_mahasiswa.nim      
                                AND 
                                LEFT(t_mahasiswa.nim, 3) = '$id' 
                        ORDER BY t_mahasiswa.nim";
                // print_r($sql);die;
                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();
                
                foreach($getData as $val){
                    // $obj = new stdClass();
                    // $obj->nim  = $val['nim'];
                    // $obj->nama = $val['nama'];
                    // print_r($obj);die;
                    array_push($listdata, $val);
                }
                
                
                
                
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadperusahaan($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            $sql = "SELECT DISTINCT
                        nama_perusahaan, 
                        alamat_perusahaan,
                        kode_pos,
                        no_telp,
                        COUNT(nama_perusahaan) AS jumlah_mahasiswa
                    FROM 
                        sipenkp_pengajuan_mahasiswa 
                    WHERE
                        status_pengajuan = 104
                    GROUP BY 
                        nama_perusahaan, 
                        alamat_perusahaan, 
                        no_telp, 
                        kode_pos 
                    ORDER BY 
                        COUNT(nama_perusahaan) DESC";
            // print_r($sql);die;
            

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);

            }

            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loaddashboardadmin($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
                $sql = "SELECT 
                            COUNT(*) FILTER (WHERE status_kp = '121' ) as proses_kp,
                            (SELECT COUNT(*) FROM t_mahasiswa) as total_mahasiswa,
                            COUNT(*) FILTER (WHERE status_kp = '122') as selesai_kp,
                            COUNT(*) FILTER (WHERE status_kp = '123') as gagal_kp
                        FROM 
                            sipenkp_kerja_praktik";
                // print_r($sql);die;
                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val);
    
                }

            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loaddashboarddosen($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
                $sql = "SELECT 
                            COUNT(*) FILTER (WHERE kp.status_kp = '121' AND p.no_peg = '$id' AND kp.nim = p.nim ) as jumlah_bimbingan,
                            (SELECT COUNT(*) FROM  sipenkp_jadwal WHERE penguji_1 = '$id' AND tanggal > NOW()) as jumlah_jadwal,
                            (SELECT COUNT(*) FROM  sipenkp_kerja_praktik kp, sipenkp_pembimbing p WHERE kp.id_pembimbing = p.id_pembimbing_mhs and p.no_peg = '$id' and kp.status_kp = '122' and kp.tenggat_kp > now()) as mhs_selesai,
                            (SELECT COUNT(*) FROM  sipenkp_kerja_praktik kp, sipenkp_pembimbing p WHERE kp.id_pembimbing = p.id_pembimbing_mhs and p.no_peg = '$id' and kp.status_kp = '123' and kp.tenggat_kp > now()) as mhs_gagal
                            
                        FROM 
                            sipenkp_kerja_praktik kp,
                            sipenkp_pembimbing p
                            
                        where
                            kp.tenggat_kp > NOW() ";
                // print_r($sql);die;
                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val);
    
                }

            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loaddashboardmahasiswa($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
                $sql = "SELECT 
                        (SELECT TO_CHAR(tenggat_kp :: DATE, 'dd/mm/yyyy') FROM sipenkp_kerja_praktik kp WHERE kp.nim = '$id' ORDER BY create_date LIMIT 1) as tenggat,
                        (SELECT TO_CHAR(tenggat_pengajuan :: DATE, 'dd/mm/yyyy') as periode FROM sipenkp_pengajuan_mahasiswa kp WHERE  kp.nim = '$id' ORDER BY create_date_pengajuan DESC LIMIT 1),
                        (SELECT TO_CHAR(tanggal :: DATE, 'dd/mm/yyyy') as tanggal FROM sipenkp_jadwal WHERE nim_mhs = '$id' ORDER BY create_date LIMIT 1),
                        (SELECT nilai_mutu FROM sipenkp_nilai_mahasiswa WHERE nim = '$id' )
                
                        ";
                // print_r($sql);die;
                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val);
    
                }

            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadCalendarData($tgl){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            if($tgl){
                // $sql = 'SELECT  FROM sipenkp_jadwal j 
                //     INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                //     INNER JOIN (SELECT nopeg AS nidp2, nama AS namapenguji2 FROM t_peg_itb) dp3 on j.penguji_2 = nidp2
                //     INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim 
                //     WHERE j.id_jadwal != 0 AND j.tanggal = '."'$tgl'";

                //validasi jadwal
                $sql = 'SELECT * FROM sipenkp_jadwal j 
                        INNER JOIN sipenkp_kerja_praktik kp ON j.nim_mhs = kp.nim
                        INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                        INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                        INNER JOIN (SELECT nopeg AS nidp2, nama AS dosen_pembimbing FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                        INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim  
                        WHERE j.id_jadwal != 0 and j.tanggal = '."'$tgl'".'';
                // print_r($sql);die;

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();
                
                foreach($getData as $val){

                    array_push($listdata, $val);
                }

                if(!$listdata){
                    $date=date_create($tgl);
                    date_sub($date,date_interval_create_from_date_string("40 days"));
                    array_push($listdata, array('tanggal' => date_format($date,"Y-m-d"), 'jam' => '23:59:00'));
                    
                    // print_r(date_format($date,"Y-m-d"));die;
                }
                // print_r($listdata);die;
            }else{
                //load kalender
                $sql = "SELECT * FROM sipenkp_jadwal j 
                    INNER JOIN sipenkp_kerja_praktik kp ON j.nim_mhs = kp.nim
                    INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                    INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1 FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                    INNER JOIN (SELECT nopeg AS nidp2, nama AS dosen_pembimbing FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                    INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim 
                    WHERE j.id_jadwal != 0 AND kp.status_kp = '121'";
                    // print_r($sql);die;
                    $stmt     = $this->_db->query($sql);
                    $getData  = $stmt->execute();
                    // print_r($getData);die;
                    foreach($getData as $val){
                        // print_r($val);die;
                        array_push($listdata, $val);

                    }
            }

            // print_r($sql);die;
            // $stmt     = $this->_db->query($sql);
            // $getData  = $stmt->execute();

            // foreach($getData as $val){

            //     array_push($listdata, $val);

            // }
            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loaddatajadwal($nim){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                $sql = 'SELECT * FROM sipenkp_jadwal j 
                        INNER JOIN sipenkp_kerja_praktik kp ON j.nim_mhs = kp.nim
                        INNER JOIN sipenkp_pembimbing p ON kp.id_pembimbing = p.id_pembimbing_mhs 
                        INNER JOIN (SELECT nopeg AS nidp1, nama AS namapenguji1, gelar_dpn as gdpem, gelar_blk as gbpem FROM t_peg_itb) dp2 on j.penguji_1 = nidp1
                        INNER JOIN (SELECT nopeg AS nidp2, nama AS dosen_pembimbing, gelar_dpn as gdpeng, gelar_blk as gbpeng FROM t_peg_itb) p2 on p.no_peg = p2.nidp2
                        INNER JOIN t_mahasiswa m on j.nim_mhs = m.nim  
                        WHERE kp.nim = '."'$nim'".'';
                
                
                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
                }

                
            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loaddsn($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            if($id){
                // print_r("die");die;
                

            }else{
                $sql = "SELECT * FROM ".'t_peg_itb'." WHERE ".'id_sta_peg'."  ILIKE '%Dosen%'";

                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadpengujidijadwal($nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            if($id){
                // print_r("die");die;
                

            }else{
                $sql = "SELECT
                            penguji_1,
                            penguji_2 
                        FROM
                            sipenkp_jadwal 
                        WHERE
                            nim_mhs = '$nim' 
                        ORDER BY
                            create_date DESC 
                            LIMIT 1";

                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadpembimbing($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            if($id){
                $sql = "SELECT * FROM sipenkp_kerja_praktik kp
                        INNER JOIN sipenkp_pembimbing p ON kp.nim = p.nim
                        INNER JOIN (SELECT ".'nopeg'.", ".'nama'." AS pembimbing, ".'inisial'.", ".'id_elo'.", ".'nip'.", ".'gelar_dpn'.", ".'gelar_blk'." FROM ".'t_peg_itb'.") dsn ON p.no_peg = dsn.".'nopeg'."
                        WHERE kp.nim = '$id'";

                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
                

            }else{
                $sql = "SELECT * FROM ".'t_peg_itb'." WHERE ".'id_sta_peg'."  ILIKE '%Dosen%'";

                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function detailPengajuan($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            $sql = 'SELECT * FROM sipenkp_pengajuan_mahasiswa pm
                    INNER JOIN (SELECT param_val2, param_type as status FROM master_parameter) mp on pm.status_pengajuan = mp.param_val2 
                    INNER JOIN t_mahasiswa m on pm.nim = m.nim 
                    INNER JOIN t_prodi ON LEFT (m.nim, 3) = t_prodi.id_prodi 
                    WHERE m.nim = '."'$id'".'  ORDER BY create_date_pengajuan DESC LIMIT 1';
                // print_r($sql);die;
                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val);
    
                }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadAttach($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            if($id){
                
                $sql = "SELECT 
                            b1.file_dir as dir1,
                            b1.file_name as name1, 
                            b2.file_dir as dir2, 
                            b2.file_name as name2, 
                            b3.file_dir as dir3, 
                            b3.file_name as name3, 
                            b4.file_dir as dir4, 
                            b4.file_name as name4 
                        FROM 
                            berkas b1, 
                            berkas_2 b2, 
                            berkas_3 b3, 
                            berkas_4 b4 
                        WHERE 
                            b1.idberkas = '$id' AND 
                            b2.idberkas = '$id' AND 
                            b3.idberkas = '$id' AND 
                            b4.idberkas = '$id' ";
                
                // print_r($sql);die;
                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val);
    
                }

            }else{
                $sql = "SELECT * FROM mahasiswa";

                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadbobot($id = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            //kalo ada id
            if($id){
                
                $sql = "SELECT * FROM mahasiswa WHERE iduser = $id";

                

                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){
                    // print_r($val);die;

                    array_push($listdata, $val['total']);
    
                }

            }else{
                
                $sql = "SELECT * FROM sipenkp_bobot_penilaian ORDER BY code_penilaian, create_date";

                // print_r($sql);die;
                $stmt     = $this->_db->query($sql);
                $getData  = $stmt->execute();

                foreach($getData as $val){

                    array_push($listdata, $val);
    
                }
            }

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function editbobotbatch($tabel, $data){
        
        $result = new Result();

        try {

            $update = " UPDATE sipenkp_bobot_penilaian
                            SET bobot = CASE nama_bobot ";
           
            foreach($data as $key => $value){
                $update .= "WHEN '$key' THEN ".$value;
            }
            $update.=" END";
            
            // print_r($update);die;
            $stmt     = $this->_db->query($update);
            $return  = $stmt->execute();
            
            if($return->getAffectedRows()){
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
            }else{
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }

        return $result;
    }

    public function updatestatustenggat(){
        
        $result = new Result();

        try {

            
            $update = " UPDATE sipenkp_pengajuan_mahasiswa 
                        SET status_pengajuan = '102' 
                        WHERE status_pengajuan = '92' AND 
                        tenggat_pengajuan <= NOW()
            ";
           
            $stmt     = $this->_db->query($update);
            $return  = $stmt->execute();

            $update2 = "UPDATE sipenkp_kerja_praktik 
                        SET status_kp = '123' 
                        WHERE status_kp = '121' AND 
                        tenggat_kp <= NOW()";
                        
            $stmt     = $this->_db->query($update2);
            $return  = $stmt->execute();
            
            if($return->getAffectedRows()){
           
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                
                
            }else{
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }

        return $result;
    }

    public function submitnilaiperusahaan($tabel, $data, $idnilai, $iduser){
        
        $result = new Result();

        try {

            $insert = " INSERT INTO sipenkp_penilaian_perusahaan 
                        (nama_nilai_perusahaan, nilai_perusahaan, id_user, id_nilai) VALUES ";
            // $length = count($data);

            $counter  = 1;
            foreach($data as $key => $value){
                // $i += 1;
                if($counter < count($data)){
                    $insert .= "('$key', $value, $iduser, $idnilai),";
                } else {
                    $insert .= "('$key', $value, $iduser, $idnilai);";
                }
                $counter++;
                // print_r($i);
            }
            // $insert.=" END";
            
            // print_r($insert);die;
            $stmt     = $this->_db->query($insert);
            $return  = $stmt->execute();
            
            // if($return->getAffectedRows()){
            //     $result->code = $result::CODE_SUCCESS;
            //     $result->info = $result::INFO_SUCCESS;
            // }else{
            //     $result->code = $result::CODE_FAILED;
            //     $result->info = $result::INFO_FAILED;
            // }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }

        return $result;
    }

    public function submitnilaipenguji($tabel = null, $data , $idnilai , $iduser , $ididentity, $identity){
        
        $result = new Result();
        // print_r('kalo disini?');die;
        try {
            // print_r($ididentiy);die;
            if($identity == 'pembimbing'){
                $insert = " INSERT INTO sipenkp_penilaian_pembimbing 
                (nama_nilai_pembimbing, nilai_pembimbing, id_user, id_nilai, nopeg) VALUES ";
            } else if($identity == 'penguji'){
                $insert = " INSERT INTO sipenkp_penilaian_penguji
                (nama_nilai_penguji, nilai_penguji, id_user, id_nilai, nopeg) VALUES ";
            }

            // print_r($tabel);die;


            // $insert = " INSERT INTO sipenkp_penilaian_perusahaan 
            //             (nama_nilai_perusahaan, nilai_perusahaan, id_user, id_nilai, nopeg) VALUES ";
            // $length = count($data);

            $counter  = 1;
            foreach($data as $key => $value){
                // $i += 1;
                if($counter < count($data)){
                    $insert .= "('$key', $value, $iduser, $idnilai, '$ididentity'),";
                } else {
                    $insert .= "('$key', $value, $iduser, $idnilai, '$ididentity');";
                }
                $counter++;
                // print_r($i);
            }
            // $insert.=" END";
            
            // print_r($insert);die;
            $stmt     = $this->_db->query($insert);
            $return  = $stmt->execute();
            
            if($return->getAffectedRows()){
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
            }else{
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }

        return $result;
    }

    public function deletebobot($tabel, $data){
        
        $result = new Result();

        try {

            $delete     = " DELETE from sipenkp_bobot_penilaian WHERE id_bobot IN ( ";
            
            $counter    = 1;
            foreach($data as $id){
                $delete .= "$id";
                
                if($counter <= count($data) - 1){
                    $delete .= ",";
                }
                $counter++;
            }

            $delete .= ")";
            
            
            // print_r($counter."   ".count($data));die;
            // print_r($delete);die;
            $stmt     = $this->_db->query($delete);
            $return  = $stmt->execute();
            
            if($return->getAffectedRows()){
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
            }else{
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }

        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }

        return $result;
    }

    public function loadStatusBerkas($nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = " SELECT
                        COUNT(*) 
                        FROM
                            mahasiswa m,
                            berkas b
                        
                        WHERE 
                            m.nim = '$nim' and b.nim='$nim'";
            // print_r($nim);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val['count']);
                // print_r($listdata);die;
            }

            

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadStatusBerkasM($nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT * FROM sipenkp_berkas_mahasiswa 
                    INNER JOIN sipenkp_kerja_praktik on sipenkp_kerja_praktik.id_berkas = sipenkp_berkas_mahasiswa.idberkas
                    INNER JOIN sipenkp_berkas ON sipenkp_berkas.nim = sipenkp_berkas_mahasiswa.nim_mhs
                    INNER JOIN master_parameter on sipenkp_berkas_mahasiswa.isvalidtu = master_parameter.param_val1
                    WHERE nim_mhs = '$nim' and sipenkp_kerja_praktik.status_kp != '123'";
            // print_r($nim);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }
            // print_r($listdata);die;

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loaddatakp($nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT * FROM sipenkp_kerja_praktik kp
                    -- INNER JOIN sipenkp_jadwal j ON kp.id_jadwal = j.id_jadwal
                    -- INNER JOIN sipenkp_berkas b ON kp.nim = b.nim
                    INNER JOIN t_mahasiswa m ON m.nim = kp.nim
                    INNER JOIN t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                    INNER JOIN sipenkp_pembimbing p ON p.id_pembimbing_mhs = kp.id_pembimbing
                    WHERE kp.nim = '$nim' and kp.status_kp != '123'";
            // print_r($nim);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadproseskp($nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT * FROM sipenkp_kerja_praktik kp
            FULL OUTER JOIN sipenkp_pengajuan_mahasiswa pm ON kp.id_pengajuan = pm.id_pengajuan
            FULL OUTER JOIN sipenkp_pembimbing pem ON kp.id_pembimbing = pem.id_pembimbing_mhs
            FULL OUTER JOIN sipenkp_berkas_mahasiswa bm ON kp.id_berkas = bm.idberkas
            FULL OUTER JOIN sipenkp_jadwal j ON kp.id_jadwal = j.id_jadwal 
            FULL OUTER JOIN (SELECT id_nilai, id_kp as idkp, nilai_akhir, nilai_mutu FROM sipenkp_nilai_mahasiswa) nm ON nm.idkp = kp.id_kp
            FULL OUTER JOIN (SELECT nopeg AS nopegpenguji, nama AS dosen_penguji, gelar_dpn as dpenguji, gelar_blk as bpenguj FROM t_peg_itb) dp2 on j.penguji_1 = nopegpenguji
            FULL OUTER JOIN (SELECT nopeg AS nopegpembimbing, nama AS dosen_pembimbing, gelar_dpn as dpembimbing, gelar_blk as bpembimbing FROM t_peg_itb) p2 on pem.no_peg = nopegpembimbing 
            FULL OUTER JOIN t_mahasiswa m ON kp.nim = m.nim
            INNER JOIN t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
            WHERE pm.id_pengajuan != 0 AND pm.id_pengajuan = kp.id_pengajuan AND kp.status_kp != '123' AND m.nim = '$nim' ORDER BY pm.create_date_pengajuan DESC LIMIT 1";
            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadpengajuans($nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT * FROM sipenkp_pengajuan_mahasiswa pm
                    INNER JOIN (SELECT param_val2, param_type as status FROM master_parameter) mp on pm.status_pengajuan = mp.param_val2 
                    INNER JOIN t_mahasiswa m on pm.nim = m.nim
                    FULL OUTER JOIN (SELECT id_perusahaanmhs, id_perusahaan, nim as nim_mhs, file_path, file_path_c FROM sipenkp_perusahaan_mahasiswa) pmah on pmah.id_perusahaanmhs = pm.id_perusahaanmhs
                    FULL OUTER JOIN sipenkp_perusahaan p ON p.id_perusahaan = pmah.id_perusahaan
                    WHERE pm.nim = '$nim' ORDER BY pm.create_date_pengajuan DESC LIMIT 1
                    ";
            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loaddatakps($nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT * FROM sipenkp_kerja_praktik kp
                    INNER JOIN sipenkp_jadwal j ON kp.id_jadwal = j.id_jadwal
                    INNER JOIN sipenkp_berkas b ON kp.nim = b.nim
                    INNER JOIN t_mahasiswa m ON m.nim = kp.nim
                    INNER JOIN t_prodi ON LEFT(m.nim, 3) = t_prodi.id_prodi
                    INNER JOIN sipenkp_pembimbing p ON p.id_pembimbing_mhs = kp.id_pembimbing
                    WHERE kp.nim = '$nim' and kp.status_kp != '123'";
            // print_r($nim);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function nilaiperusahaan($idkp = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT * FROM sipenkp_kerja_praktik kp
            INNER JOIN sipenkp_nilai_mahasiswa nm ON kp.id_nilai = nm.id_nilai
            INNER JOIN sipenkp_penilaian_perusahaan pp ON nm.id_nilai = pp.id_nilai
            WHERE kp.id_kp = $idkp";
            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function nilaipenguji($idkp = null, $nopeg = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();
            $filterpenguji = '';
            if($nopeg == 'generate'){
                $filterpenguji = '';
            }else{
                $filterpenguji = "AND pp.nopeg = '$nopeg' ";
            }
                
            $sql = "SELECT * FROM sipenkp_kerja_praktik kp
            INNER JOIN sipenkp_nilai_mahasiswa nm ON kp.id_nilai = nm.id_nilai
            INNER JOIN sipenkp_penilaian_penguji pp ON nm.id_nilai = pp.id_nilai
            WHERE kp.id_kp = $idkp $filterpenguji ORDER BY pp.nopeg ";
            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function nilaipembimbing($idkp = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT * FROM sipenkp_kerja_praktik kp
            INNER JOIN sipenkp_nilai_mahasiswa nm ON kp.id_nilai = nm.id_nilai
            INNER JOIN sipenkp_penilaian_pembimbing pp ON nm.id_nilai = pp.id_nilai
            WHERE kp.id_kp = $idkp ORDER BY pp.nama_nilai_pembimbing ASC";
            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadfilebimbingan($nim = null,$nopeg = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT * FROM sipenkp_bimbingan b
            INNER JOIN sipenkp_kerja_praktik kp ON b.nim = kp.nim
            WHERE b.nim = '$nim' AND b.nopeg = '$nopeg' AND kp.status_kp != '123' AND b.is_group IS NULL";
            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadfilebimbingangroup($nim = null,$nopeg = null, $data = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            // $sql = "SELECT * FROM sipenkp_bimbingan b
            // LEFT JOIN sipenkp_kerja_praktik kp ON b.nim = kp.nim
            // WHERE  b.nopeg = '$nopeg' AND kp.status_kp != '123' AND kp.status_kp != '123' AND b.is_group = 1";

            $sql = "SELECT
                        DISTINCT 
                        nopeg, file_bimbingan, tanggal_bimbingan, dir_file, uploaded, keterangan_bimbingan, insert_by, name
                    FROM
                        sipenkp_bimbingan b
                        LEFT JOIN sipenkp_kerja_praktik kp ON b.nim = kp.nim 
                                                LEFT JOIN (SELECT iduser, name FROM user_data_header) uh ON uh.iduser = b.insert_by 
                    WHERE
                        b.nopeg = '$nopeg' 
                        AND kp.status_kp = '121' 
                        AND kp.status_kp != '123' 
                        AND b.is_group = 1
                    ORDER BY
                        tanggal_bimbingan ASC";
            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function ishavepembimbing($nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = " SELECT COUNT(*) as total FROM sipenkp_pembimbing WHERE nim = '$nim'";
            // print_r($nim);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val['total']);
                // print_r($listdata);die;
            }

            $sql2     = " SELECT * FROM sipenkp_pembimbing as pem
                          INNER JOIN sipenkp_kerja_praktik kp ON pem.id_pembimbing_mhs = kp.id_pembimbing 
                          INNER JOIN ".'t_peg_itb'." as peg ON peg.".'nopeg'." = pem.no_peg 
                          WHERE kp.nim = '$nim'";

            $stmt2     = $this->_db->query($sql2);
            $getData2  = $stmt2->execute();
            
            foreach($getData2 as $val){
                array_push($listdata, $val);
            }
            // print_r($listdata);die;

            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadataKaprodi($kodeprodi){

        $result = new Result();
    
        try {
    
            $sql      = "   SELECT * FROM user_data_header uh
                            INNER JOIN t_peg_itb peg ON peg.nopeg = uh.nopeg
                            INNER JOIN t_prodi tp ON tp.id_prodi = uh.nim 
                            WHERE uh.role = 210 ";

            if($kodeprodi){
                $sql .= "AND uh.nim = '$kodeprodi'";
            }
            
            // print_r($sql);die;
            
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();
    
            foreach($getData as $val){
    
                array_push($listdata, $val);
    
            }
            // print_r($listdata);die;
    
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loaddatamahasiswa($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $filter = null)
    {   
        // print_r($filter);die;
        $result = new Result();
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(*) as total
                        FROM 
                        t_mahasiswa m, t_prodi prod
                    WHERE 
                        NOT EXISTS(SELECT iduser FROM user_data_header uh WHERE uh.username = m.nim OR uh.nim = m.nim)
                        AND prod.id_prodi =LEFT(m.nim, 3)";
            
            if($search){
                $sql1 .= 'AND m.nim'." LIKE '%$search%'";
            }
            if($filter){
                $sql1 .= " AND prod.id_prodi = '$filter'";
            }

            $stmt     = $this->_db->query($sql1);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }

            $totalFilter = $rows;
            
            $sql ="	SELECT 
                        m.nim,
                        m.nama,
                        m.file_foto,
                        m.email,
                        m.sks,
                        m.ip,
                        prod.nama_prodi
                    FROM 
                        t_mahasiswa m, t_prodi prod
                    WHERE 
                        NOT EXISTS(SELECT iduser FROM user_data_header uh WHERE uh.username = m.nim OR uh.nim = m.nim)
                        AND prod.id_prodi =LEFT(m.nim, 3)";
            
            
            if($search){
                $sql .=  'AND m.nim'." LIKE '%$search%'";
            }
            
            if($filter){
                $sql .= " AND prod.id_prodi = '$filter'";
            }

            if($length){
                if($length == -1){
                    $length = $totalFilter;
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
              
            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loadusermahasiswa($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $filter=null)
    {   
        $result = new Result();
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(*) as total
                    FROM
                        user_data_header uh, t_mahasiswa m, t_prodi prod
                    WHERE 
                        m.nim = uh.username
                        AND uh.role = 200
                        AND prod.id_prodi = LEFT(m.nim, 3)";
            
            if($search){
                $sql1 .= 'AND m.nim'." LIKE '%$search%'";
            }
            if($filter){
                $sql1 .= " AND prod.id_prodi = '$filter'";
            }

            
            $stmt     = $this->_db->query($sql1);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }

            $totalFilter = $rows;
            
            $sql ="	SELECT 
                        uh.iduser,
                        uh.username,
                        uh.nim,
                        uh.name,
                        m.file_foto,
						prod.nama_prodi,
                        m.email,
                        m.sks,
                        m.ip,
                        uh.update_date,
                        uh.status
                    FROM
                        user_data_header uh, t_mahasiswa m, t_prodi prod
                    WHERE 
                        m.nim = uh.username
                        AND uh.role = 200
                        AND prod.id_prodi = LEFT(m.nim, 3)";
            
            if($search){
                $sql .=  'AND m.nim'." LIKE '%$search%'";
            }
            if($filter){
                $sql .= " AND prod.id_prodi = '$filter'";
            }
            
            // print_r($sql);die;
            if($length){
                if($length == -1){
                    $length = $totalFilter;
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }
            
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
              
            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function hardSaveGlobal($dataarr=null, $table=null){
        $result = new Result();

        try{
            $rows = 0;
            
            $columns = implode(", ",array_keys($dataarr));
            $values  = array_values($dataarr);
            // print_r($values);die;
            $x = 0;

            $valuess = array();

            foreach ($values as $val){
                
                if(is_numeric($val)){
                    $valuess[$x] = $val;
                }else{
                    $valuess[$x] = "'$val'";
                }

                $x++;
            }
            //benerin gan
            $values = implode(", ", array_values($valuess));
            // print_r($valuess);
            // print_r($values);die;
            $sql = 'INSERT INTO '.$table.' ('.$columns.') VALUES ('.$values.') RETURNING *';
            // $sql = "INSERT INTO $table 
            // ($columns) VALUES 
            // ($values) RETURNING *";
            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            $listdata = array();

            foreach($getData as $val){
                array_push($listdata, $val);    
            }
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function hardSaveGlobalMahasiswa($dataarr=null, $table=null, $conflict=null){
        $result = new Result();

        try{
            $rows = 0;
            $sql = '';
            $datamasuk = 0;
            foreach($dataarr as $val){
                $columns    = implode(", ",array_keys($val));

                $x = 0;
                $valuess = array();

                $eda = "asa ss";
                

                foreach ($val as $v){

                    
                    if(preg_match('~[\']~', $v)){
                        
                        $a = preg_replace('~[\']~', "\\'", $v);

                        $a = 'E'."'$a'";
                        $valuess[$x] = $a;

                    }else{
                        if(is_numeric($v)){
                            $valuess[$x] = $v;
                        }else{
                            $valuess[$x] = "'$v'";
                        }
                    }

                    $x++;

                }
                
                $valuess     = implode(", ",array_values($valuess));
                $sql =  ' INSERT INTO '.$table.' ('.$columns.') VALUES ('.$valuess.') on CONFLICT ('.$conflict.') do nothing;';

                $stmt     = $this->_db->query($sql);
                $return   = $stmt->execute();

                $datamasuk += $return->getAffectedRows();
            }   
            
            if($datamasuk){
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $datamasuk;
            }else{
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
           
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function hardSaveGlobalMahasiswaxlsx($dataarr=null, $table=null, $conflict=null){
        $result = new Result();
        // print_r(count($dataarr));die;
        // $date = strtotime('01-01-2019');
        // print_r(date('Y-m-d', $date));die;
        try{
            $rows      = 0;
            $sql       = '';
            $datamasuk = 0;
            $datamasak = 0;
            $iterator  = 0;
            $heads     = '';
            $arrheads  = array();
            $sqlist    = array();
            foreach($dataarr as $val){
                $sql = '';
                if($iterator == 0){
                    $heads      = implode(", ",array_values($val));
                    $arrheads   = $val;
                } else {
                    $sql .= "INSERT INTO $table ($heads) VALUES ";
                    $rowdata = '';
                    for($i = 0; $i < count($val); $i++){
                        if($i == count($val)-1){
                            if($val[$i] == ''){
                                $rowdata .= 'NULL';
                            } else {
                                if(preg_match('~[\']~', $val[$i])){
                                    $a = preg_replace('~[\']~', "''", $val[$i]);
                                    $a = "'$a'";
                                    $rowdata .= $a;
                                    // print_r($rowdata);die;
                                }else{
                                    if(is_numeric($val[$i])){
                                        $rowdata .= $val[$i];
                                    }else{
                                        $vals    = $val[$i];
                                        $rowdata .= "'$vals'";
                                    }
                                }
                                
                            }
                        }else{
                            if($val[$i] == ''){
                                $rowdata .= "NULL, ";
                            } else {
                                if(preg_match('~[\']~', $val[$i])){
                                    $a = preg_replace('~[\']~', "''", $val[$i]);
                                    $a = "'$a',";
                                    // print_r($a);die;
                                    $rowdata .= $a;
                                }else{
                                    if(is_numeric($val[$i])){
                                        if($arrheads[$i] == 'tgl_masuk_itb'){
                                            $date     = strtotime('01-01-'.$val[$i]);
                                            $actdate  = date('Y-m-d', $date);
                                            $rowdata  .= "'$actdate',";
                                        }else{
                                            $rowdata .= $val[$i].", ";
                                        }
                                    }else{
                                        $vals    = $val[$i];
                                        $rowdata .= "'$vals', ";
                                    }
                                }
                                
                            }
                        }
                    }
                    
                    $sql        .= "($rowdata) ON CONFLICT ($conflict) do nothing";
                    array_push($sqlist, $sql);
                }
                
                $iterator++;
                
                
            }   
            
            // print_r($sqlist);die;
            for($s = 0; $s < count($sqlist); $s++){
                $stmt      = $this->_db->query($sqlist[$s]);
                $return    = $stmt->execute();
                $datamasuk += $return->getAffectedRows();
            }
            
            
            if($datamasuk){
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $datamasuk;
            }else{
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
           
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function hardSaveGlobalAkademik($dataarr=null, $table=null, $conflict=null){
        $result = new Result();
        // print_r(count($dataarr));die;
        // $date = strtotime('01-01-2019');
        // print_r(date('Y-m-d', $date));die;
        try{
            $rows      = 0;
            $sql       = '';
            $datamasuk = 0;
            $datamasak = 0;
            $iterator  = 0;
            $heads     = '';
            $arrheads  = array();
            $sqlist    = array();
            foreach($dataarr as $val){
                $sql = '';
                if($iterator == 0){
                    $heads      = implode(", ",array_values($val));
                    $arrheads   = $val;
                } else {
                    $sql .= "INSERT INTO $table ($heads) VALUES ";
                    $rowdata = '';
                    for($i = 0; $i < count($val); $i++){
                        if($i == count($val)-1){
                            if($val[$i] == ''){
                                $rowdata .= 'NULL';
                            } else {
                                if(preg_match('~[\']~', $val[$i])){
                                    $a = preg_replace('~[\']~', "''", $val[$i]);
                                    $a = "'$a'";
                                    $rowdata .= $a;
                                    // print_r($rowdata);die;
                                }else{
                                    if(is_numeric($val[$i])){
                                        $rowdata .= $val[$i];
                                    }else{
                                        $vals    = $val[$i];
                                        $rowdata .= "'$vals'";
                                    }
                                }
                                
                            }
                        }else{
                            if($val[$i] == ''){
                                $rowdata .= "NULL, ";
                            } else {
                                if(preg_match('~[\']~', $val[$i])){
                                    $a = preg_replace('~[\']~', "''", $val[$i]);
                                    $a = "'$a',";
                                    // print_r($a);die;
                                    $rowdata .= $a;
                                }else{
                                    if(is_numeric($val[$i])){
                                        if($arrheads[$i] == 'tgl_masuk_itb'){
                                            $date     = strtotime('01-01-'.$val[$i]);
                                            $actdate  = date('Y-m-d', $date);
                                            $rowdata  .= "'$actdate',";
                                        }else{
                                            $rowdata .= $val[$i].", ";
                                        }
                                    }else{
                                        $vals    = $val[$i];
                                        $rowdata .= "'$vals', ";
                                    }
                                }
                                
                            }
                        }
                    }
                    
                    $sql        .= "($rowdata)";
                    array_push($sqlist, $sql);
                }
                
                $iterator++;
                
                
            }   
            
            // print_r($sqlist);die;
            for($s = 0; $s < count($sqlist); $s++){
                $stmt      = $this->_db->query($sqlist[$s]);
                $return    = $stmt->execute();
                $datamasuk += $return->getAffectedRows();
            }
            
            
            if($datamasuk){
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $datamasuk;
            }else{
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
           
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loaduserdosen($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null)
    {   
        $result = new Result();
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(*) as total
                        FROM 
                        user_data_header uh, 
                        t_peg_itb p
                    WHERE 
                        role = 50
                        AND  p.nopeg = uh.username
                        AND LEFT(p.id_sta_peg, 5) = 'Dosen'
                        -- AND pembimbing_ta = 'S1'
                        AND uh.kaprodi is null ";
            
            if($search){
                $sql1 .= 'AND (uh.username'." LIKE '%$search%' OR uh.name ILIKE '%$search%')";
            }

            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }

            $totalFilter = $rows;
            
            $sql ="	SELECT 
                        uh.iduser,
                        p.nopeg,
                        uh.username,
                        concat(p.gelar_dpn, ' ',p.nama, ' ', p.gelar_blk) as nama,
                        p.file_foto,
                        p.inisial,
                        p.pembimbing_ta,
                        uh.update_date,
	                    uh.status,
                        p.gelar_dpn,
                        p.gelar_blk,
                        p.nama as justnama
                    FROM 
                        user_data_header uh, 
                        t_peg_itb p
                    WHERE 
                        role = 50
                        AND  p.nopeg = uh.username
                        AND LEFT(p.id_sta_peg, 5) = 'Dosen'
                        -- AND pembimbing_ta = 'S1'
                        AND uh.kaprodi is null ";
            
            if($search){
                $sql .=  'AND (uh.username'." LIKE '%$search%' OR uh.name ILIKE '%$search%')";
            }
            
            
            if($length){
                if($length == -1){
                    $length = $totalFilter;
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }
            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
              
            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loaduserakademik($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null)
    {   
        $result = new Result();
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(*) as total
                        FROM 
                        user_data_header uh, 
                        t_peg_itb p
                    WHERE 
                        role = 10
                        AND  (p.nopeg = uh.username OR p.username = uh.username)
                        AND (LEFT(p.id_sta_peg, 3) = 'Adm' OR p.id_sta_peg = 'Karyawan' OR p.id_sta_peg = 'Tenaga Kontrak')
                        -- AND pembimbing_ta = 'S1'
                        AND uh.kaprodi is null ";
            
            if($search){
                $sql1 .= 'AND (uh.username'." LIKE '%$search%' OR uh.name ILIKE '%$search%')";
            }

            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }

            $totalFilter = $rows;
            
            $sql ="	SELECT 
                        uh.iduser,
                        p.nopeg,
                        uh.username,
                        concat(p.gelar_dpn, ' ',p.nama, ' ', p.gelar_blk) as nama,
                        p.file_foto,
                        p.inisial,
                        p.pembimbing_ta,
                        uh.update_date,
	                    uh.status,
                        CASE 
                            WHEN uh.prodi = '131' THEN
                                'TEKNIK MESIN'
                            WHEN uh.prodi = '136' THEN
                                'TEKNIK DIRGANTARA'
                            WHEN uh.prodi = '137' THEN
                                'TEKNIK MATERIAL'
                            ELSE
                                '-'
                        END AS prodi
                    FROM 
                        user_data_header uh, 
                        t_peg_itb p
                    WHERE 
                        role = 10
                        AND  (p.nopeg = uh.username OR p.username = uh.username)
                        AND (LEFT(p.id_sta_peg, 3) = 'Adm' OR p.id_sta_peg = 'Karyawan' OR p.id_sta_peg = 'Tenaga Kontrak')
                        -- AND pembimbing_ta = 'S1'
                        AND uh.kaprodi is null ";
            
            if($search){
                $sql .=  'AND (uh.username'." LIKE '%$search%' OR uh.name ILIKE '%$search%')";
            }
            
            
            if($length){
                if($length == -1){
                    $length = $totalFilter;
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }
            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
              
            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loaddatadosen($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null)
    {   
        $result = new Result();
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(*) as total
                    FROM 
                        t_peg_itb p
                    WHERE 
                        LEFT(p.id_sta_peg, 5) = 'Dosen'
                        -- AND pembimbing_ta = 'S1'
                        AND NOT EXISTS (SELECT iduser FROM user_data_header uh WHERE  uh.username = p.nopeg)";
            
            if($search){
                $sql1 .= 'AND p.nopeg'." LIKE '%$search%'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }

            $totalFilter = $rows;
            
            $sql ="	SELECT 
                        p.nopeg,
                        concat(p.gelar_dpn, ' ',p.nama, ' ', p.gelar_blk) as nama,
                        p.inisial,
                        p.pembimbing_ta
                    FROM 
                        t_peg_itb p
                    WHERE 
                        LEFT(p.id_sta_peg, 5) = 'Dosen'
                        -- AND pembimbing_ta = 'S1'
                        AND NOT EXISTS (SELECT iduser FROM user_data_header uh WHERE  uh.username = p.nopeg)";
            
            if($search){
                $sql .=  'AND (p.nopeg'." LIKE '%$search%' OR p.nama ILIKE '%$search%')";
            }
            
            if($length){
                if($length == -1){
                    $length = $totalFilter;
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }
            
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
              
            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loaddataakademik($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null)
    {   
        $result = new Result();
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(*) as total
                    FROM 
                        t_peg_itb p
                    WHERE 
                        (LEFT(p.id_sta_peg, 3) = 'Adm' OR p.id_sta_peg = 'Karyawan' OR p.id_sta_peg = 'Tenaga Kontrak') 
                        -- AND pembimbing_ta = 'S1'
                        AND NOT EXISTS (SELECT iduser FROM user_data_header uh WHERE uh.username = p.nopeg OR uh.username = p.username)";
            
            if($search){
                $sql1 .= 'AND p.nopeg'." LIKE '%$search%'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }

            $totalFilter = $rows;
            
            $sql ="	SELECT 
                        p.nopeg,
                        concat(p.gelar_dpn, ' ',p.nama, ' ', p.gelar_blk) as nama,
                        p.inisial,
                        p.pembimbing_ta,
                        p.username,
                        p.password
                    FROM 
                        t_peg_itb p
                    WHERE 
                        (LEFT(p.id_sta_peg, 3) = 'Adm' OR p.id_sta_peg = 'Karyawan' OR p.id_sta_peg = 'Tenaga Kontrak') 
                        -- AND pembimbing_ta = 'S1'
                        AND NOT EXISTS (SELECT iduser FROM user_data_header uh WHERE  uh.username = p.nopeg OR uh.username = p.username)";
            
            if($search){
                $sql .=  'AND (p.nopeg'." LIKE '%$search%' OR p.nama ILIKE '%$search%')";
            }
            
            if($length){
                if($length == -1){
                    $length = $totalFilter;
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }
            
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
              
            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loaduserkaprodi($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null)
    {   
        $result = new Result();
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(*) as total
                    FROM
                        user_data_header uh, t_peg_itb p, t_prodi pr
                    WHERE
                        p.nopeg = uh.nopeg
                        AND uh.kaprodi IS NOT NULL
                        AND uh.role = 210
                        AND pr.id_prodi = uh.kaprodi";
            
            if($search){
                $sql1 .= 'AND uh.username'." LIKE '%$search%'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }

            $totalFilter = $rows;
            
            $sql ="	SELECT 
                        uh.iduser,
                        uh.username,
                        concat(gelar_dpn, ' ', p.nama, ' ', gelar_blk) as nama,
                        pr.nama_prodi,
                        uh.update_date,
                        uh.status
                    FROM
                        user_data_header uh, t_peg_itb p, t_prodi pr
                    WHERE
                        p.nopeg = uh.nopeg
                        AND uh.kaprodi IS NOT NULL
                        AND uh.role = 210
                        AND pr.id_prodi = uh.kaprodi";
            
            if($search){
                $sql .=  'AND uh.username'." LIKE '%$search%'";
            }
            
            if($length){
                if($length == -1){
                    $length = $totalFilter;
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }
            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
              
            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loaduserkoordinator($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null)
    {   
        $result = new Result();
        try{
            
            $rows = 0;

            $sql1 ="SELECT 
                        COUNT(*) as total
                    FROM
                        user_data_header uh, t_peg_itb p, t_prodi pr
                    WHERE
                        p.nopeg = uh.nopeg
                        AND uh.koordinator IS NOT NULL
                        AND uh.role = 300
                        AND pr.id_prodi = uh.koordinator";
            
            if($search){
                $sql1 .= 'AND uh.username'." LIKE '%$search%'";
            }
            // print_r($sql1);die;
            $stmt     = $this->_db->query($sql1);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                $rows   = $val['total'];
            }

            $totalFilter = $rows;
            
            $sql ="	SELECT 
                        uh.iduser,
                        uh.username,
                        concat(gelar_dpn, ' ', p.nama, ' ', gelar_blk) as nama,
                        pr.nama_prodi,
                        uh.update_date,
                        uh.status
                    FROM
                        user_data_header uh, t_peg_itb p, t_prodi pr
                    WHERE
                        p.nopeg = uh.nopeg
                        AND uh.koordinator IS NOT NULL
                        AND uh.role = 300
                        AND pr.id_prodi = uh.koordinator";
            
            if($search){
                $sql .=  'AND uh.username'." LIKE '%$search%'";
            }
            
            if($length){
                if($length == -1){
                    $length = $totalFilter;
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }
            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }
              
            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
            
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    }

    public function loadlistapproval($kodeprodi = null, $col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $nim){
        
        $result = new Result();
        
        try{
            
            $rows = 0;

            $sql1 = "SELECT 
                    COUNT(*) AS total
                FROM 
                    sipenkp_kerja_praktik kp,
                    t_mahasiswa mhs,
                    t_prodi tp,
                    sipenkp_pembimbing sp,
                    t_peg_itb tpi
                WHERE 
                    kp.status_kp = '121' AND
                    mhs.nim = kp.nim AND
                    LEFT(mhs.nim, 3) = tp.id_prodi AND
                    kp.id_pembimbing = sp.id_pembimbing_mhs AND
                    sp.no_peg = tpi.nopeg AND
                    approve_proses_bimbingan = 1";

            
            if($kodeprodi){
                $sql1 .= " AND LEFT(mhs.nim,3) = '$kodeprodi' ";
            }
           
            if($search){
                $sql1 .= " AND mhs.".'nama'." ILIKE '%$search%' OR p.nama_prodi ILIKE '%$search%'";
            }

          
            

            // print_r($sql1);die;
            
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
            // print_r($sql1);die;
           
            $totalFilter = $rows;

            $sql = "SELECT 
                        mhs.nama,
                        mhs.nim,
                        kp.judul_laporan,
                        kp.approve_proses_bimbingan,
                        kp.approve_maju_seminar,
                        kp.id_pembimbing,
                        tp.nama_prodi,
                        kp.create_date,
                        tpi.nama AS nama_dosen,
                        kp.id_kp
                    FROM 
                        sipenkp_kerja_praktik kp,
                        t_mahasiswa mhs,
                        t_prodi tp,
                        sipenkp_pembimbing sp,
                        t_peg_itb tpi
                    WHERE 
                        kp.status_kp = '121' AND
                        mhs.nim = kp.nim AND
                        LEFT(mhs.nim, 3) = tp.id_prodi AND
                        kp.id_pembimbing = sp.id_pembimbing_mhs AND
                        sp.no_peg = tpi.nopeg AND
                        approve_proses_bimbingan = 1"; 
            
            // print_r($sql);die;
            if($kodeprodi){
                $sql .= " AND LEFT(mhs.nim,3) = '$kodeprodi' ";
            }
            if($search){
                $sql .= " AND (mhs.".'nama'." ILIKE '%$search%' OR p.nama_prodi ILIKE '%$search%')";
            }

            // print_r($sql);die;

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function getmahasiswaforjadwal($nim = null){
        
        $result = new Result();
        
        try{
            
            $listdata  = array();
            $listcheck = array();
            $res       = array();

            $nimnol = $nim[0];
            // $arraycheck = array('13717025', '13717045');
            $resarray   = array();
            for($i = 0; $i < count($nim); $i++){
                $nims = $nim[$i];
                $tostring = "'$nims'";
                array_push($resarray, $tostring);
            }

            $strnim = implode(",", $resarray);
               
            $sql = "SELECT 
                kp.id_kp,
                kp.nim,
                m.nama,
                peg.nama AS nama_pembimbing,
                p.no_peg,
                tp.nama_prodi,
                bm.isvalidtu,
                kp.id_berkas
            FROM 
                sipenkp_kerja_praktik kp,
                sipenkp_berkas_mahasiswa bm,
                t_mahasiswa m,
                t_prodi tp,
                sipenkp_pembimbing p,
                t_peg_itb peg
            WHERE
                p.id_pembimbing_mhs = kp.id_pembimbing AND
                p.no_peg = peg.nopeg AND
                LEFT(m.nim, 3) = tp.id_prodi AND
                kp.nim = m.nim AND
                (kp.id_berkas IS NOT NULL OR kp.id_berkas != 0) AND
                bm.idberkas = kp.id_berkas AND
                bm.isvalidtu = '51' AND
                kp.status_kp = '121' AND
                bm.isjadwal = '72' AND
                LEFT(m.nim, 3) = LEFT('$nimnol', 3) AND
                m.nim NOT IN ($strnim)";
                // print_r($sql);die;
                

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            foreach($getData as $val){
                // $obj = new stdClass();
                // $obj->nim  = $val['nim'];
                // $obj->nama = $val['nama'];
                // print_r($obj);die;
                array_push($listdata, $val);
            }
                
            // print_r($listdata);die;
                
                
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function loadSemester(){
        
        $result = new Result();
        
        try{
            
            $listdata  = array();
            $listcheck = array();
            $res       = array();
               
            $sql = "SELECT DISTINCT
                        semester,
                        tahun_ajaran 
                    FROM
                        sipenkp_jadwal 
                    WHERE
                        semester IS NOT NULL 
                        AND tahun_ajaran IS NOT NULL
                        ORDER BY 
                        tahun_ajaran, 
                        semester DESC";
                // print_r($sql);die;
                

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            foreach($getData as $val){
                // $obj = new stdClass();
                // $obj->nim  = $val['nim'];
                // $obj->nama = $val['nama'];
                // print_r($obj);die;
                array_push($listdata, $val);
            }
                
            // print_r($listdata);die;
                
                
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function isKaprodi($iduser = null, $role = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            $selectedRole = 50;

            if($role == 50){
                $selectedRole = 210;
            }
           
            $sql = "SELECT
                        tablink.username,
                        tablink.password
                    FROM
                        user_data_header tabUser,
                        user_data_header tabLink 
                    WHERE
                        tabUser.iduser = $iduser 
                        AND tabUser.nopeg = tabLink.nopeg AND tabLink.role = $selectedRole";
            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function isKoordinator($iduser = null, $role = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            $selectedRole = 50;

            
            // print_r($role);die;
            if($role == 50){
                // print_r("here");die;
                $selectedRole = 300;
            }
            // print_r($selectedRole);die;
            $sql = "SELECT
                        tablink.username,
                        tablink.password
                    FROM
                        user_data_header tabUser,
                        user_data_header tabLink 
                    WHERE
                        tabUser.iduser = $iduser 
                        AND tabUser.nopeg = tabLink.nopeg AND tabLink.role = $selectedRole";
            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function getpengajuan($passData = null){
        
        $result = new Result();
        
        try{
            
            $listdata  = array();
            $listcheck = array();
            $res       = array();
               

            $prodi           = $passData->prodi;
            // print_r($prodi);die;
            $sql = "SELECT
                        pm.nim,
                        nama,
                        nama_prodi,
                        nama_perusahaan,
                        alamat_perusahaan,
                        nama_penerima_perusahaan,
                        divisi_penerima,
                        to_char(create_date_pengajuan, 'DD Mon YYYY') AS tanggal,
                        status
                    FROM
                        sipenkp_pengajuan_mahasiswa pm
                        INNER JOIN t_prodi prod ON LEFT ( pm.nim, 3 ) = prod.id_prodi
                        INNER JOIN ( SELECT param_val2, param_type AS status FROM master_parameter ) mp ON pm.status_pengajuan = mp.param_val2
                        INNER JOIN t_mahasiswa M ON pm.nim = M.nim
                        FULL OUTER JOIN ( SELECT id_perusahaanmhs, id_perusahaan, nim AS nim_mhs, file_path, file_path_c FROM sipenkp_perusahaan_mahasiswa ) pmah ON pmah.id_perusahaanmhs = pm.id_perusahaanmhs
                        FULL OUTER JOIN sipenkp_perusahaan P ON P.id_perusahaan = pmah.id_perusahaan";
                            // print_r($sql);die;

            if($prodi != null){
                $sql .= " WHERE prod.id_prodi = '$prodi'";
            }               

            // print_r($sql);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            foreach($getData as $val){
                
                array_push($listdata, $val);
            }   
                
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }

    public function getListKP($col = null, $orderCol = null, $orderDir = null, $start = null, $length = null, $draw = null, $search = null, $nim = null, $isData = null){
        
        $result = new Result();
        
        try{
            
            $rows = 0;
            
            $kodeprodi = $isData->kodeprodi;
            // print_r($kodeprodi);die;
            $sql1 = "SELECT 
                        COUNT(*) AS total
                    FROM 
                        t_mahasiswa m 
                        LEFT JOIN sipenkp_kerja_praktik kp ON kp.nim = m.nim
                        LEFT JOIN sipenkp_pembimbing pem ON kp.id_pembimbing = pem.id_pembimbing_mhs
                        LEFT JOIN t_prodi p ON LEFT(m.nim, 3) = p.id_prodi
                        LEFT JOIN (SELECT nopeg AS nopegpembimbing, nama AS dosen_pembimbing, gelar_dpn as dpembimbing, gelar_blk as bpembimbing FROM t_peg_itb) p2 on pem.no_peg = nopegpembimbing 
                        INNER JOIN (SELECT DISTINCT nim FROM user_data_header WHERE role = 200) uh ON m.nim = uh.nim
                    WHERE 
                        p.id_prodi = '$kodeprodi' 
                        AND (kp.status_kp != '123' OR kp.status_kp IS NULL)
                        -- AND id_pengajuan is not null
                         ";
 
            if($search){
                $sql1 .= " AND m.".'nama'." ILIKE '%$search%' OR m.nim ILIKE '%$search%'";
            }
            
            $stmt     = $this->_db->query($sql1);

            $getData  = $stmt->execute();

            foreach($getData as $val){

                $rows   = $val['total'];

            }
            // print_r($sql1);die;
           
            $totalFilter = $rows;

            $sql = "SELECT 
                        m.nim,
                        m.nama,
                        p.nama_prodi,
                        dosen_pembimbing 
                    FROM 
                        t_mahasiswa m 
                        LEFT JOIN sipenkp_kerja_praktik kp ON kp.nim = m.nim
                        LEFT JOIN sipenkp_pembimbing pem ON kp.id_pembimbing = pem.id_pembimbing_mhs
                        LEFT JOIN t_prodi p ON LEFT(m.nim, 3) = p.id_prodi
                        LEFT JOIN (SELECT nopeg AS nopegpembimbing, nama AS dosen_pembimbing, gelar_dpn as dpembimbing, gelar_blk as bpembimbing FROM t_peg_itb) p2 on pem.no_peg = nopegpembimbing 
                        INNER JOIN (SELECT DISTINCT nim FROM user_data_header WHERE role = 200) uh ON m.nim = uh.nim
                    WHERE 
                        p.id_prodi = '$kodeprodi' 
                        AND (kp.status_kp != '123' OR kp.status_kp IS NULL)
                        -- AND id_pengajuan is not null
                         "; 
            
           
            if($search){
                $sql .= " AND (m.".'nama'." ILIKE '%$search%' OR m.nim ILIKE '%$search%') ";
            }

            // print_r($sql);die;

            if($length){
                if($length == -1){
                    $length = $totalFilter; // show all 
                }
                $sql   .= " ORDER BY ".$orderCol." ".$orderDir."  LIMIT ". $length."  OFFSET ".$start."  ";
            }

            // print_r($sql);die;
            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();
            
            $listdata = array();

            foreach($getData as $val){

                array_push($listdata, $val);

            }

            $dataArray =array(
				"draw"              =>  intval($draw),
				"recordsTotal"      =>  intval($totalFilter),
				"recordsFiltered"   =>  intval($totalFilter),
				"data"              =>  $listdata
            );
           
            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $dataArray;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }


    public function loadmgroup($nopeg = null, $nim = null){
        
        $result = new Result();
        
        try{
        
            $listdata = array();

            
                
            $sql = "SELECT
                        kp.id_kp,
                        kp.nim,
                        m.nama,
                        concat(peg.gelar_dpn, ' ', peg.nama, ' ', peg.gelar_blk) as nama_pembimbing,
                        no_peg as nopeg
                    FROM
                        sipenkp_pembimbing pem
                        INNER JOIN sipenkp_kerja_praktik kp ON pem.id_pembimbing_mhs = kp.id_pembimbing
                        INNER JOIN t_mahasiswa m ON m.nim = kp.nim
                        INNER JOIN t_peg_itb peg ON pem.no_peg = peg.nopeg
                    WHERE
                        no_peg = '$nopeg'
                        AND status_kp = '121'";
            // print_r($nim);die;

            $stmt     = $this->_db->query($sql);
            $getData  = $stmt->execute();

            foreach($getData as $val){
                // print_r($val);die;

                array_push($listdata, $val);
                // print_r($listdata);die;
            }


            if ($listdata) {
                $result->code = $result::CODE_SUCCESS;
                $result->info = $result::INFO_SUCCESS;
                $result->data = $listdata;
            } else {
                $result->code = $result::CODE_FAILED;
                $result->info = $result::INFO_FAILED;
            }
            
        }catch (\Zend\Db\Adapter\Exception\RuntimeException $ex) {
            $result->code = 3;
            $result->info = 'ERROR : ' . $ex->getMessage();
        } catch (\Exception $ex) {
            $result->code = 4;
            $result->info = 'ERROR : ' . $ex->getMessage();
        }
        return $result;
    
    }
}




