<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Pwisuda_Controller extends Controller {
	var $stat;
	
	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
    }
	function jsonFromQuery($q) {
		$this->rhlib->jsonFromQuery($q);
	}
	function jsonFromQueryWLimit($q){
		$this->rhlib->jsonFromQueryWLimit($q);
	}
	
	function retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function retValOrZero($val){
		$val = ($val=='')? 0 : $val;
		return $val;
	}
	
	function getStrQueryWSearchStmhsSemester($q){
		$searchBy = "";		
		if(isset($_POST['key'])){
			$key = $_POST['key'];
			if($key!=null && $key!=''){
				$val = "";
				if(isset($_POST['value']))
					$val = $_POST['value'];
				if ($_POST['key']=='nmkelas' || $_POST['key']=='tingsem' || $_POST['key']=='nmstaktivitasmhs' || $_POST['key']=='nmcutidet') {
					if ($val) {
						$searchBy = " WHERE ".$key." = '".$val."'";
					} else {
						$searchBy = " WHERE ".$key." = '' OR ".$key." IS NULL";
					}
				} else {
					$searchBy = " WHERE ".$key." like '%".$val."%'";
				}
			}
		}		
		return "SELECT * FROM(".$q.")A ".$searchBy;
	}
	
	function jsonFromQueryStmhsSemester($q){
		$strQuery = $this->getStrQueryWSearchStmhsSemester($q);
		$query  = $this->db->query($strQuery);
        $data = array();
		$build_array = array ("success"=>false,"results"=>-1,"data"=>array(),"msg"=>"error");

		if ($query->num_rows() == 0)
		{
			$build_array["success"]=true;
			$build_array["results"]=0;
			$build_array["msg"]="no data";
		}
		if ($query->num_rows() > 0) {
            $data = $query->result();
			$ttl = count($data);
			if($ttl > 0){
				$build_array["success"]=true;
				$build_array["results"]=$ttl;
				$build_array["data"]=$data;
				$build_array["msg"]= "";
			}
        }
        echo json_encode($build_array);
	}
		
	
	function get_pwisuda(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$idklppengguna = $_POST['idklppengguna'];
		$q = "SELECT `mahasiswa`.`nim` AS `nim`
     , `mahasiswa`.`nmmhs` AS `nmmhs`
     , `mahasiswa`.`kdjnskelamin` AS `kdjnskelamin`
     , `mahasiswa`.`tgllahir` AS `tgllahir`
     , `mahasiswa`.`thnmasuk` AS `thnmasuk`
     , `mahasiswa`.`idklsmhs` AS `idklsmhs`
     , `mahasiswa`.`tgllulus` AS `tgllulus`
     , `klsmhs`.`nmklsmhs` AS `nmklsmhs`
     , `mahasiswa`.`noskl` AS `noskl`
     , `mahasiswa`.`tglskl` AS `tglskl`
     , `mahasiswa`.`tglpengesahanskl` AS `tglpengesahanskl`
     , `mahasiswa`.`idpimpinan1` AS `idpimpinan1`
     , `pimpinan`.`nmpimpinan` AS `nmpimpinan`
     , `pimpinan`.`gelar` AS `gelar`
     , `mahasiswa`.`kdstaktivitasmhs` AS `kdstaktivitasmhs`
     , `staktifmhs`.`nmkodtbkod` AS `nmstaktivitasmhs`
     , `mahasiswa`.`kdprodi` AS `kdprodi`
     , `prodi`.`nmprodi` AS `nmprodi`
     , `stmhssemester`.`kdstsemester` AS `kdstsemester`
     , `mahasiswa`.`alamatasal` AS `alamatasal`
     , ((SELECT count(0) AS `count(*)`
         FROM
           `syaratwisuda`
         WHERE
           (`syaratwisuda`.`kdstsemester` = `stmhssemester`.`kdstsemester`)) - (SELECT count(0) AS `count(*)`
                                                                                FROM
                                                                                  `klkpsyaratwisuda`
                                                                                WHERE
                                                                                  ((`klkpsyaratwisuda`.`nim` = `mahasiswa`.`nim`)
                                                                                  AND (`klkpsyaratwisuda`.`kdstsemester` = '".$kdstsemester."')))) AS `total_syaratwisuda`
     , ((SELECT count(0) AS `count(*)`
         FROM
           `syaratwisuda`
         WHERE
           (`syaratwisuda`.`kdstsemester` = `stmhssemester`.`kdstsemester`)
		   AND (`syaratwisuda`.`idklppengguna` = '".$idklppengguna."')) - (SELECT count(0) AS `count(*)`
                                                                                FROM
                                                                                  `klkpsyaratwisuda`
                                                                                WHERE
                                                                                  ((`klkpsyaratwisuda`.`nim` = `mahasiswa`.`nim`)
                                                                                  AND (`klkpsyaratwisuda`.`kdstsemester` = '".$kdstsemester."')
																				  AND (`klkpsyaratwisuda`.`idklppengguna` = '".$idklppengguna."')))) AS `tot_statusdet`
FROM
  (((((`mahasiswa`
LEFT JOIN `klsmhs`
ON ((`mahasiswa`.`idklsmhs` = `klsmhs`.`idklsmhs`)))
LEFT JOIN `pimpinan`
ON ((`mahasiswa`.`idpimpinan1` = `pimpinan`.`idpimpinan`)))
LEFT JOIN `prodi`
ON ((`mahasiswa`.`kdprodi` = `prodi`.`kdprodi`)))
JOIN `stmhssemester`
ON (((`mahasiswa`.`nim` = `stmhssemester`.`nim`) AND (`mahasiswa`.`kdstaktivitasmhs` = `stmhssemester`.`kdstaktivitasmhs`))))
LEFT JOIN `tbkod` `staktifmhs`
ON (((`mahasiswa`.`kdstaktivitasmhs` = `staktifmhs`.`kdkodtbkod`) AND (`staktifmhs`.`kdapltbkod` = 5))))
WHERE
  CASE
  WHEN '".$kdprodi."' <> 99 then 
  mahasiswa.kdprodi = '".$kdprodi."'
  AND stmhssemester.kdstsemester = '".$kdstsemester."'
  AND
  ((`stmhssemester`.`kdstaktivitasmhs` = 'L')
  OR (`mahasiswa`.`kdstaktivitasmhs` = 'L'))
  when '".$kdprodi."' = 99 then
  stmhssemester.kdstsemester = '".$kdstsemester."'
  AND
  ((`stmhssemester`.`kdstaktivitasmhs` = 'L')
  OR (`mahasiswa`.`kdstaktivitasmhs` = 'L'))
  END";
		//$this->jsonFromQueryWLimit($q);		
		$this->jsonFromQueryStmhsSemester($q);		
	}
	
	function get_jmlstmhssemester(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT sum(if(a.kdstaktivitasmhs = 'A', 1, 0)) AS aktif
				 , sum(if(a.kdstaktivitasmhs = 'C', 1, 0)) AS cuti
				 , sum(if(a.kdstaktivitasmhs = 'D', 1, 0)) AS dropout
				 , sum(if(a.kdstaktivitasmhs = 'G', 1, 0)) AS doubledegree
				 , sum(if(a.kdstaktivitasmhs = 'K', 1, 0)) AS keluar
				 , sum(if(a.kdstaktivitasmhs = 'L', 1, 0)) AS lulus
				 , sum(if(a.kdstaktivitasmhs = 'N', 1, 0)) AS nonaktif
			FROM
			  stmhssemester a
			LEFT JOIN (SELECT kdkodtbkod AS kode
							, nmkodtbkod AS nmstaktivitasmhs
					   FROM
						 tbkod
					   WHERE
						 kdapltbkod = '05') b
			ON b.kode = a.kdstaktivitasmhs
			LEFT JOIN mahasiswa c
			ON c.nim = a.nim
			LEFT JOIN kelas d
			ON d.idkelas = a.idkelas
			LEFT JOIN semester e
			ON e.idsemester = a.idsemester
			WHERE c.kdprodi='".$kdprodi."'
			and a.kdstsemester='".$kdstsemester."'";
		
		$query  =  $this->db->query($q);
        $data= ''; 
                    
        if ($query->num_rows() > 0)
        {
            $row = $query->row();
			$dataArray = array(
			'aktif'=> $row->aktif,
			'cuti'=> $row->cuti,
			'dropout'=> $row->dropout,
			'doubledegree'=> $row->doubledegree,
			'keluar'=> $row->keluar,
			'lulus'=> $row->lulus,
			'nonaktif'=> $row->nonaktif,
		);
        }
        echo json_encode ($dataArray);
	}
	
	function insert_stmhssemester(){
		$dataArray = $this->getFieldsAndValues();
		$ret = $this->rhlib->insertRecord('stmhssemester',$dataArray);
		$this->update_stat_mhs();
        return $ret;
    }
	
	  function g_JKP(){ //ISTRA
        
        //======================================================================
        $start                  = $this->input->post("start");
        $limit                  = $this->input->post("limit");
        
        $fields                  = $this->input->post("fields");
        $query                  = $this->input->post("query");
      
        $this->db->select("*");
        $this->db->from("klppengguna");
		$where = array();   
		$where['klppengguna.nmklppengguna']= $_POST['nm_klp'];          
	
        $this->db->where($where);
        
        
        if($fields!="" || $query !=""){
            $k=array('[',']','"');
            $r=str_replace($k, '', $fields);
    //        $a[explode(',', $r)];
            $b=explode(',', $r);
            $c=count($b);
            for($i=0;$i<$c;$i++){
                $d[$b[$i]]=$query;
            }

           // $this->db->bracket('open','like');
             $this->db->or_like($d, $query);
           // $this->db->bracket('close','like');
        }
        
        //$this->db->order_by("jdashboard");
                
        if ($start!=null){
            $this->db->limit($limit,$start);
        }else{
            $this->db->limit(50,0);
        }
        
            $q = $this->db->get(); 
        
        
      //  $q = $this->db->get(); 
       
        $data = array();
        if ($q->num_rows() > 0) {
            $data = $q->result();
        }
        $datax = $this->db->count_all('klppengguna');
        $ttl = $datax;
        
        //======================================================================
        
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

      foreach($data as $row) {
            array_push($build_array["data"],array(
                'idklppengguna'=>$row->idklppengguna,
                'kdklppengguna'=>$row->kdklppengguna,
                'nmklppengguna'=>$row->nmklppengguna,
                'deskripsi'=>$row->deskripsi,
                'idjnsdashboard'=>$this->nm_field('nmjnsdashboard', 'jdashboard', 'idjnsdashboard', $row->idjnsdashboard),
                'idstatus'=>$this->nm_field('nmstatus', 'status', 'idstatus', $row->idstatus),
            ));
        }
        echo json_encode($build_array);
        
    }
  
	
	function insert_pwisuda(){
		$dataArray = array(
			'nim'=>$_POST['nim'],
			'kdprodi'=>$_POST['kdprodi'],
			'kdstsemester'=>$_POST['kdstsemester'],
			'st_sidang'=>0,
		);
		$ret = $this->rhlib->insertRecord('mhs_pesertasidang',$dataArray);
		//$this->update_stat_mhs();
        return $ret;
    }
	
	function update_pwisuda_old(){ 				
		$dataArray = array(
			'st_sidang'=>1
		);
		//UPDATE
		$this->db->where('nim', $_POST['nim']);
		$this->db->where('kdstsemester', $_POST['kdstsemester']);
		$this->db->update('stmhssemester', $dataArray); 
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
			//$this->update_stat_mhs();
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
			//$this->update_stat_mhs();
        }
        return $ret;
    }
	
	function update_stat_mhs(){ 				
		$dataArray = array(
			'kdstaktivitasmhs'=> $_POST['stmhs'], 
			'catnonaktif'=> $_POST['catnonaktif'] ? ($_POST['catnonaktif']):null,
		);
		
		//UPDATE
		$this->db->where('nim', $_POST['nim']);
		$this->db->update('mahasiswa', $dataArray); 
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
    }
	
	function update_kelas(){
		$idstmhssemester = $_POST['idstmhssemester'];
		$idkelas = $_POST['idkelas'];
		
		//UPDATE
		$this->db->where('idstmhssemester', $idstmhssemester);
		$this->db->set('idkelas', $idkelas);
		$this->db->update('stmhssemester'); 
        
		$this->update_kelas_absen();
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
	}
	
	function update_kelas_absen(){
		
		//UPDATE
		$this->db->where('nim', $_POST['nim']);
		$this->db->where('kdprodi', $_POST['kdprodi']);
		$this->db->where('kdstsemester', $_POST['kdstsemester']);
		$this->db->set('idkelas', $_POST['idkelas']);
		$this->db->update('absenmhs'); 
        
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
	}
	
	function update_tingsem(){
		$idstmhssemester = $_POST['idstmhssemester'];
		$idsemester = $_POST['idsemester'];
		
		//UPDATE
		$this->db->where('idstmhssemester', $idstmhssemester);
		$this->db->set('idsemester', $idsemester);
		$this->db->update('stmhssemester'); 
        
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
        return $ret;
	}
	
	function getFieldsAndValues(){
		$dataArray = array(
             //'idstmhssemester'=> $_POST['idstmhssemester'],
			 'nim'=> $_POST['nim'], 
			 //'kdprodi' => $_POST['kdprodi'],  
			 'kdstsemester' => $_POST['kdstsemester'],
			 'kdstaktivitasmhs'=> $_POST['stmhs'], 
			 'tgllulus'=> $_POST['tgllulus'] ? ($_POST['tgllulus']):null,
			 'skslulus'=> $_POST['sks'] ? ($_POST['sks']):null,
			 'ipk'=> $_POST['ipk'] ? ($_POST['ipk']):null,
			 'nosk'=> $_POST['nosk'] ? ($_POST['nosk']):null,
			 'tglsk'=> $_POST['tglsk'] ? ($_POST['tglsk']):null,
			 'noijazah'=> $_POST['noseriizh'] ? ($_POST['noseriizh']):null,
			 'kdtskripsi'=> $_POST['tskripsi'] ? ($_POST['tskripsi']):null,
			 'kdjskripsi'=> $_POST['jskripsi'] ? ($_POST['jskripsi']):null,
			 'awalbimbingan'=> $_POST['tglawalbimbing'] ? ($_POST['tglawalbimbing']):null,
			 'akhirbimbingan'=> $_POST['tglakhirbimbing'] ? ($_POST['tglakhirbimbing']):null,
			 'nidu1'=> $_POST['idnidu1'] ? ($_POST['idnidu1']):null,
			 'nidu2'=> $_POST['idnidu2'] ? ($_POST['idnidu2']):null,
			 'nidu3'=> $_POST['idnidu3'] ? ($_POST['idnidu3']):null,
			 'nidu4'=> $_POST['idnidu4'] ? ($_POST['idnidu4']):null,
			 'nidu5'=> $_POST['idnidu5'] ? ($_POST['idnidu5']):null,
             'idsemester'=> $_POST['idsemester'],
             'idkelas'=> $_POST['idkelas'],
			 'catnonaktif'=> $_POST['catnonaktif'] ? ($_POST['catnonaktif']):null,
        );
    	return $dataArray;
	}
	
	function delete_pwisuda(){     
		$where['nim'] = $_POST['nim'];
		$ret = $this->rhlib->deleteRecord('mhs_pesertasidang',$where);
		$ret = $this->rhlib->deleteRecord('klkpsyaratsidang',$where);
        return $ret;
    }
	
	function copy_stmhssemester(){
		$kdprodi = $_POST['kdprodi']; 
		$smtbaru = $_POST['smtbaru'];
		$smtlama = $_POST['smtlama'];

		$exec = $this->db->query("CALL sp_copy_stmhssemester (?,?,?)",
				array($kdprodi,$smtlama,$smtbaru)
				);
				
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Copy Data Berhasil';
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Copy Data Gagal';
        }
        return $ret;
	}
	
	function get_klkpsyaratsidang(){
		$nim = $_POST['nim'];
		$kdstsemester = $_POST['kdstsemester'];
		$kdprodi = $_POST['kdprodi'];
		$nm_klp = $_POST['nm_klp'];
		
		$q = "SELECT syaratsidang.kdstsemester,
		syaratsidang.kdprodi,
		syaratsidang.kdsyaratsidang,
		syaratsidang.nmsyaratsidang,
		syaratsidang.deskripsi,
		syaratsidang.idklppengguna,
		  if(((
			SELECT klp.kdsyaratsidang
			FROM
			  klkpsyaratsidang klp
			WHERE
			  klp.nim = '$nim'
			  AND klp.kdstsemester = '$kdstsemester'
			  AND klp.kdprodi = '$kdprodi'
			  AND klp.kdsyaratsidang = syaratsidang.kdsyaratsidang) = syaratsidang.kdsyaratsidang), 1, 0) AS cek
		FROM syaratsidang
		LEFT JOIN klppengguna
		on syaratsidang.idklppengguna = klppengguna.idklppengguna
		WHERE kdstsemester = '$kdstsemester' AND kdprodi = '$kdprodi'
		AND nmklppengguna = '$nm_klp'";
				
		$this->rhlib->jsonFromQuery($q);
	}
	
	function save_syaratsidang(){
        $par=$this->input->post("par");

        $this->db->trans_begin();
            $rows = explode(";",$par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
					
				$this->db->query("CALL SP_simpan_syaratsidang (?,?,?,?,?,?)",
				array($rows2[0],$rows2[1],$rows2[2],$rows2[3],$rows2[4],$rows2[5]));
     
            }
        
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            $return["success"]=false;
            $return["message"]="Ubah Data gagal";
        }
        else
        {
            $this->db->trans_commit();
            $return["success"]=true;
            $return["message"]="Ubah Data Berhasil";
        }
        return $return["success"];
    }
	
	function get_klkpsyaratwisuda(){
		$nim = $_POST['nim'];
		$kdstsemester = $_POST['kdstsemester'];
		$idklppengguna = $_POST['idklppengguna'];
		
		$q = "SELECT * ,
		  if(((
			SELECT klp.kdsyaratwisuda
			FROM
			  klkpsyaratwisuda klp
			WHERE
			  klp.nim = '$nim'
			  AND klp.kdstsemester = '$kdstsemester'
			  AND klp.kdsyaratwisuda = syaratwisuda.kdsyaratwisuda) = syaratwisuda.kdsyaratwisuda), 1, 0) AS cek
		FROM syaratwisuda
		WHERE kdstsemester = '$kdstsemester' AND idklppengguna = '$idklppengguna'";
				
		$this->rhlib->jsonFromQuery($q);
	}
	
	function save_syaratwisuda(){
        $par=$this->input->post("par");

        $this->db->trans_begin();
            $rows = explode(";",$par);
            $row_count = count($rows);
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
					
				$this->db->query("CALL SP_simpan_syaratwisuda (?,?,?,?,?)",
				array($rows2[0],$rows2[1],$rows2[2],$rows2[3],$rows2[4]));
     
            }
        
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            $return["success"]=false;
            $return["message"]="Ubah Data gagal";
        }
        else
        {
            $this->db->trans_commit();
            $return["success"]=true;
            $return["message"]="Ubah Data Berhasil";
        }
        return $return["success"];
    }
	
	
	function export_excel($kdprodi,$kdstsemester) {
		$querymhs = "SELECT a.nim, b.nmmhs, b.tptlahir, b.tgllahir, b.nmfakultas
					, b.nmprodi , b.thnmasuk, b.alamatsurat FROM mhs_pesertasidang a
					LEFT JOIN vv_mahasiswa_profile2 b
					ON a.nim = b.nim
					WHERE 
					a.kdprodi = '".$kdprodi."' AND a.kdstsemester = '".$kdstsemester."'";
		  
		$data['eksport'] = $this->db->query($querymhs)->result();
		$data['table'] = "excel_daftar_calon_peserta_sidang";
		$data['fieldname'] = array();
		$getloop='loop';
		foreach ($data['eksport'] as $row) {
			if ($getloop=='loop') {
				foreach ($row as $key => $value) {
					array_push($data['fieldname'], $key);
				}
			}
			$getloop='end';
		}
		$this->load->view('exportexcel', $data); 	
	}
	
	
	
	function exportexcelwisudawan($kdprodi,$kdstsemester) {
		$q = "SELECT `mahasiswa`.`nim` AS `nim`
     , `mahasiswa`.`nmmhs` AS `nmmhs`
     , `mahasiswa`.`kdjnskelamin` AS `kdjnskelamin`
     , `mahasiswa`.`tgllahir` AS `tgllahir`
     , `mahasiswa`.`thnmasuk` AS `thnmasuk`
     , `mahasiswa`.`idklsmhs` AS `idklsmhs`
     , `mahasiswa`.`tgllulus` AS `tgllulus`
     , `klsmhs`.`nmklsmhs` AS `nmklsmhs`
     , `mahasiswa`.`noskl` AS `noskl`
     , `mahasiswa`.`tglskl` AS `tglskl`
     , `mahasiswa`.`tglpengesahanskl` AS `tglpengesahanskl`
     , `mahasiswa`.`idpimpinan1` AS `idpimpinan1`
     , `pimpinan`.`nmpimpinan` AS `nmpimpinan`
     , `pimpinan`.`gelar` AS `gelar`
     , `mahasiswa`.`kdstaktivitasmhs` AS `kdstaktivitasmhs`
     , `staktifmhs`.`nmkodtbkod` AS `nmstaktivitasmhs`
     , `mahasiswa`.`kdprodi` AS `kdprodi`
     , `prodi`.`nmprodi` AS `nmprodi`
     , `stmhssemester`.`kdstsemester` AS `kdstsemester`
     , `mahasiswa`.`alamatasal` AS `alamatasal`
     , ((SELECT count(0) AS `count(*)`
         FROM
           `syaratwisuda`
         WHERE
           (`syaratwisuda`.`kdstsemester` = `stmhssemester`.`kdstsemester`)) - (SELECT count(0) AS `count(*)`
                                                                                FROM
                                                                                  `klkpsyaratwisuda`
                                                                                WHERE
                                                                                  ((`klkpsyaratwisuda`.`nim` = `mahasiswa`.`nim`)
                                                                                  AND (`klkpsyaratwisuda`.`kdstsemester` = '".$kdstsemester."')))) AS `total_syaratwisuda`
     , ((SELECT count(0) AS `count(*)`
         FROM
           `syaratwisuda`
         WHERE
           (`syaratwisuda`.`kdstsemester` = `stmhssemester`.`kdstsemester`)
		   AND (`syaratwisuda`.`idklppengguna` = '".$idklppengguna."')) - (SELECT count(0) AS `count(*)`
                                                                                FROM
                                                                                  `klkpsyaratwisuda`
                                                                                WHERE
                                                                                  ((`klkpsyaratwisuda`.`nim` = `mahasiswa`.`nim`)
                                                                                  AND (`klkpsyaratwisuda`.`kdstsemester` = '".$kdstsemester."')
																				  AND (`klkpsyaratwisuda`.`idklppengguna` = '".$idklppengguna."')))) AS `tot_statusdet`
FROM
  (((((`mahasiswa`
LEFT JOIN `klsmhs`
ON ((`mahasiswa`.`idklsmhs` = `klsmhs`.`idklsmhs`)))
LEFT JOIN `pimpinan`
ON ((`mahasiswa`.`idpimpinan1` = `pimpinan`.`idpimpinan`)))
LEFT JOIN `prodi`
ON ((`mahasiswa`.`kdprodi` = `prodi`.`kdprodi`)))
JOIN `stmhssemester`
ON (((`mahasiswa`.`nim` = `stmhssemester`.`nim`) AND (`mahasiswa`.`kdstaktivitasmhs` = `stmhssemester`.`kdstaktivitasmhs`))))
LEFT JOIN `tbkod` `staktifmhs`
ON (((`mahasiswa`.`kdstaktivitasmhs` = `staktifmhs`.`kdkodtbkod`) AND (`staktifmhs`.`kdapltbkod` = 5))))
WHERE
  CASE
  WHEN '".$kdprodi."' <> 99 then 
  mahasiswa.kdprodi = '".$kdprodi."'
  AND stmhssemester.kdstsemester = '".$kdstsemester."'
  AND
  ((`stmhssemester`.`kdstaktivitasmhs` = 'L')
  OR (`mahasiswa`.`kdstaktivitasmhs` = 'L'))
  when '".$kdprodi."' = 99 then
  stmhssemester.kdstsemester = '".$kdstsemester."'
  AND
  ((`stmhssemester`.`kdstaktivitasmhs` = 'L')
  OR (`mahasiswa`.`kdstaktivitasmhs` = 'L'))
  END";
		$data['eksport'] = $this->db->query($q)->result();
		$data['table'] = 'excel_wisudawan';
		$data['kdstsemester'] = $kdstsemester;
		$data['kdprodi'] = $kdprodi;
		$data['fieldname'] = array();
		$getloop='loop';
		foreach ($data['eksport'] as $row) {
			if ($getloop=='loop') {
				foreach ($row as $key => $value) {
					array_push($data['fieldname'], $key);
				}
			}
			$getloop='end';
		}
		$this->load->view('exportexcel', $data); 		
	}
	
}
?>