<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Dosen_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 getJsonFromSP($sql, $parameters){
		$query = $this->db->query($sql, $parameters);
        $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 retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function get_dos_byId(){
		$q = "SELECT a.*
				FROM dosen a
				WHERE nidu='".$_POST['nidu']."'";
		$query  = $this->db->query($q);
        $data = array();
		$build_array = array ("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data");

		if ($query->num_rows() > 0) {
            $data = $query->result();
			$ttl = count($data);
			if($ttl == 1){
				$build_array["success"]=true;
				$build_array["msg"]= "";
			}
			$build_array["results"]=$ttl;
			$build_array["data"]=$data;
			$build_array["msg"]= "data lebih dari satu!";
        }
        echo json_encode($build_array);
	}
	
	//===Daftar Dosen===//
	function get_daftar_dosen(){
		$kdprodi = $_POST['kdprodi'];
		$q = "SELECT `dosen`.`nidu` AS `nidu`
				 , `dosen`.`nmdosdgngelar` AS `nmdosdgngelar`
				 , `dosen`.`kdjnskelamin` AS `kdjnskelamin`
				 , `prodi`.`nmprodi` AS `nmprodi`
				 , `fakultas`.`nmfakultas` AS `nmfakultas`
				 , concat(`dosen`.`tptlahir`, ', ', `dosen`.`tgllahir`) AS `ttl`
				 , `a`.`nmkodtbkod` AS `jabatanakademik`
				 , `b`.`nmkodtbkod` AS `stikatankerjadosen`
				 , `c`.`nmkodtbkod` AS `stdosen`
				 , `dosen`.`alamat` AS `alamat`
				 , `dosen`.`notelp` AS `notelp`
				 , `dosen`.`email` AS `email`
				 , `dosen`.`website` AS `website`
			FROM
			  (((((`dosen`
			LEFT JOIN `prodi`
			ON ((`prodi`.`kdprodi` = `dosen`.`kdprodi`)))
			LEFT JOIN `fakultas`
			ON ((`fakultas`.`kdfakultas` = `prodi`.`kdfakultas`)))
			LEFT JOIN `tbkod` `a`
			ON (((`a`.`kdkodtbkod` = `dosen`.`kdjabakademik`) AND (`a`.`kdapltbkod` = 2))))
			LEFT JOIN `tbkod` `b`
			ON (((`b`.`kdkodtbkod` = `dosen`.`kdstikatankerjadosen`) AND (`b`.`kdapltbkod` = 3))))
			LEFT JOIN `tbkod` `c`
			ON (((`c`.`kdkodtbkod` = `dosen`.`kdstaktivitasdosen`) AND (`b`.`kdapltbkod` = 15))))
			WHERE dosen.kdprodi='".$kdprodi."'
			ORDER BY
			  `dosen`.`nidu`";
		$this->rhlib->jsonFromQueryWLimit($q);
	}
	
	//===export excel===//
	function get_export_tbl_excel(){
		if (isset($_POST['kdprodi'])){
			$kdprodi = $_POST['kdprodi'];
			
			$sql = "CALL sp_getDaftarDsn_excel(?)";
			$parameters = array($kdprodi);
			$this->getJsonFromSP($sql, $parameters);
			
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	//--------------------excel-----------------------//
	function getAll($table,$order,$desasc) {
        if($order != '' || $desasc != ''){
            $this->db->order_by($order,$desasc);
        }
        $query = $this->db->get($table);
		
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return array();
		} 
    }
	
	function getNumRows($table) {
        $query = $this->db->get($table);
        return $query->num_rows();
    }
	
	function export_excel($tbl) {
		$tablename=strtolower($tbl);
		$data['eksport'] = $this->getAll($tablename,'','');
		$data['table'] = $tablename;
		$data['fieldname'] = $this->db->list_fields($tablename);
		$data['numrows'] = $this->getNumRows($tablename);
		$this->load->view('exportexcel', $data); 	
	} 
	
	//====================//
	
	
    //status dosen (dosen perprodi)//
	function lookup_add_dosprodi(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$q = "SELECT a.kdprodi,a.kdstdosen,a.nidu,a.nidn,a.nmdostpgelar,a.nmdosdgngelar,
			a.kdjabakademik, a.kdjnskelamin, b.nmjabak,
			ifnull(a.nmdosdgngelar,a.nmdostpgelar) as nmdosen,					
			c.nmstikj, d.nmstaktiv
			FROM dosen a
			LEFT JOIN (SELECT kdkodtbkod as kdjabak, nmkodtbkod as nmjabak 
				FROM tbkod WHERE kdapltbkod='02')b 
				ON b.kdjabak=a.kdjabakademik
			LEFT JOIN (SELECT kdkodtbkod as kdstikj, nmkodtbkod as nmstikj
				FROM tbkod WHERE kdapltbkod='03')c
				ON a.kdstikatankerjadosen = c.kdstikj
			LEFT JOIN (SELECT kdkodtbkod as kdstaktiv, nmkodtbkod as nmstaktiv
				FROM tbkod WHERE kdapltbkod='15')d
				ON a.kdstaktivitasdosen = d.kdstaktiv
			WHERE a.kdprodi='".$kdprodi."'
			AND a.nidu NOT IN (SELECT nidu from dosenwali
						WHERE kdprodi='".$kdprodi."' 
						AND kdstsemester='".$kdstsemester."')";
					
		$this->jsonFromQueryWLimit($q);
	}
    
	function get_autoNIDU(){
		$prefix = $_POST['prefix'];
		$q = "SELECT CONCAT('".$prefix."',SUBSTR(nidu, LENGTH(nidu) - 2)) as nidu
			FROM (select CONCAT('00',nidu) as nidu 
					from( SELECT IFNULL(
							(select MAX(
								CAST(SUBSTR(nidu,LENGTH(nidu)-2, 3)
							 AS UNSIGNED)) + 1
							FROM dosen 
							WHERE SUBSTR(nidu, 1, 3) = '".$prefix."')
						, 1) 
					as nidu)a1
				)a2";
		$query = $this->db->query($q);
		if ($query->num_rows() == 1) 
			$nidu = $query->row()->nidu;
		
		echo json_encode(array ("success"=>true,"nidu"=>$nidu));	
	}
		
	function get_dos_long(){
		$q = "SELECT a.*, 
			b.nmfakultas, b.nourutprodi, b.nmprodi, b.lnmprodi, b.nmjenjangstudi,
			c.nmjabak, d.nmpdktop,
			e.nmstikj, f.nmstaktiv, g.nmpt, h.nmkotkab
			FROM dosen a
			LEFT JOIN (SELECT kdprodi, nourutprodi, nmprodi, 
					CONCAT(RTRIM(nourutprodi),'. ',nmprodi,' (',nmjenjangstudi,')') as lnmprodi,
					nmfakultas, kdjenjangstudi, nmjenjangstudi
				FROM v_prodi where kdprodi <> '')b
			ON b.kdprodi=a.kdprodi
			LEFT JOIN (SELECT kdkodtbkod as kdjabak, nmkodtbkod as nmjabak
				FROM tbkod WHERE kdapltbkod='02') c
				ON a.kdjabakademik = c.kdjabak
			LEFT JOIN (SELECT kdkodtbkod as kdpdktop, nmkodtbkod as nmpdktop
				FROM tbkod WHERE kdapltbkod='01')d
				ON a.kdpendidikantertinggi = d.kdpdktop
			LEFT JOIN (SELECT kdkodtbkod as kdstikj, nmkodtbkod as nmstikj
				FROM tbkod WHERE kdapltbkod='03')e
				ON a.kdstikatankerjadosen = e.kdstikj
			LEFT JOIN (SELECT kdkodtbkod as kdstaktiv, nmkodtbkod as nmstaktiv
				FROM tbkod WHERE kdapltbkod='15')f
				ON a.kdstaktivitasdosen = f.kdstaktiv
			LEFT JOIN (SELECT kdptitbpti as kdpt, nmptitbpti as nmpt
				FROM tbpti where kdptitbpti <> '')g
				ON a.kdpt = g.kdpt 
			LEFT JOIN (SELECT CONCAT(rtrim(kdprotbpro),ltrim(kdkabtbpro)) as kdkotkab, 
				nmkabtbpro as nmkotkab FROM tbpro 
				where kdkabtbpro <> '')h
				ON a.kdkotkab = h.kdkotkab";

		$this->jsonFromQueryWLimit($q);
	}

	function get_dos_short(){
		$q = "SELECT * FROM v_dosen_cari";
		$this->jsonFromQuery($q);
	}
	
	function get_riwayatpdk_dos(){		
		$q = "SELECT a.*, b.nmprodi, c.nmjenjangstudi,
			d.nmnegara
			FROM riwayatpdkdosen a
			LEFT JOIN (SELECT kdpsttbpst as kdprodi, nmpsttbpst as nmprodi, 
				nomortbpst as nourut 
				FROM tbpst WHERE kdpsttbpst <>'')b
				ON a.nourut=b.kdprodi
			LEFT JOIN (SELECT kdkodtbkod as kdjenjang, nmkodtbkod as nmjenjangstudi 
				FROM tbkod WHERE kdapltbkod='04')c
				ON a.kdjenjangstudi=c.kdjenjang
			LEFT JOIN (SELECT DISTINCT CONCAT(rtrim(kdprotbpro),ltrim(kdkabtbpro)) as kdnegara, 
					nmkabtbpro as nmnegara 
					FROM tbpro  WHERE CAST(kdprotbpro AS UNSIGNED) > 93)d
					ON d.kdnegara=a.kdnegara
			WHERE a.nidu = '".$_POST['nidu']."'" ;
		$this->jsonFromQuery($q);
	}

	function insert_dos(){
		$dataArray = $this->getDataDosen();
        $dataArrayPublic = $this->getFieldsAndValuesPublic();
		$ret = $this->rhlib->insertRecord('dosen',$dataArray);
        //$this->rhlib->insertRecordPublic('pengguna',$dataArrayPublic);
        return $ret;
    }
	
	function insert_riwayatpdk(){
		$dataArray = $this->getDataRiwayatpdk();
		$ret = $this->rhlib->insertRecord('riwayatpdkdosen',$dataArray);
        return $ret;
    }
	function update_riwayatpdk(){ 				
		$dataArray = $this->getDataRiwayatpdk();
		
		//UPDATE
		$this->db->where('idriwayatpdkdosen', $_POST['idriwayatpdkdosen']);
		$this->db->update('riwayatpdkdosen', $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 delete_riwayatpdk(){
		$where['idriwayatpdkdosen'] = $_POST['idriwayatpdkdosen'];
		$ret = $this->rhlib->deleteRecord('riwayatpdkdosen',$where);
        return $ret;
    }
	
	function update_dos(){
		$dataArray = $this->getDataDosen();

		//UPDATE
		$this->db->where('iddsn', $_POST['iddsn']);
		$this->db->update('dosen', $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 getDataDosen()
	{
		$login = $this->session->userdata("user_idunlaprivate");
		$datetime = date("Y-m-d H:i:s");
		$ip = $_SERVER['REMOTE_ADDR'];
		
		$dataArray = array(
		//PERSONAL
			'last_login'=> $login,
			'last_date'=> $datetime, 
			'last_ip'=> $ip, 
			 
			'kdprodi'=> $this->retValOrNull($_POST['kdprodi']),
            'nidu'=> str_replace(" ","",$_POST['nidu']),
            'nup'=> str_replace(" ","",$_POST['nup']),
			'nidn'=> str_replace(" ","",$_POST['nidn']),
			'nmdostpgelar'=> strtoupper($_POST['nmdostpgelar']),
			'nmdosdgngelar'=> strtoupper($_POST['nmdosdgngelar']),
			'gelardpn'=> $_POST['gelardpn'],
			'gelarblk'=> $_POST['gelarblk'],
			'gelartertinggi'=> $_POST['gelartertinggi'],
			'tptlahir'=> $_POST['tptlahir'],
			'tgllahir'=> $_POST['tgllahir'],
			'kdjnskelamin'=> $this->retValOrNull($_POST['kdjnskelamin']),
			'kdjabakademik'=> $this->retValOrNull($_POST['kdjabakademik']),
			'noktp'=> $_POST['noktp'],
			'kdpendidikantertinggi'=> $this->retValOrNull($_POST['kdpendidikantertinggi']),
			'kdstikatankerjadosen'=> $this->retValOrNull($_POST['kdstikatankerjadosen']),
			'kdstaktivitasdosen'=> $this->retValOrNull($_POST['kdstaktivitasdosen']),
			'kdstsemester'=> $this->retValOrNull($_POST['kdstsemester']),
			'nippns'=> $_POST['nippns'],
			'kdpt'=> $this->retValOrNull($_POST['kdpt']),
			'idgoldarah'=> $this->retValOrNull($_POST['idgoldarah']),
			'kdwarga'=> $this->retValOrNull($_POST['kdwarga']),
			'foto'=> $_POST['foto'],
			'alamat'=> $_POST['alamat'],
			'kelurahan'=> $_POST['kelurahan'],
			'kecamatan'=> $_POST['kecamatan'],
			'kdkotkab'=> $_POST['kdkotkab'],
			'kdpos'=> $_POST['kdpos'],
			'notelp'=> $_POST['notelp'],
			'email'=> $_POST['email'],
			'facebook'=> $_POST['facebook'],
			'twitter'=> $_POST['twitter'],
			'pinbb'=> $_POST['pinbb'],
			'website'=> $_POST['website'],
			'catatan'=> $_POST['catatan'],
        );

		return $dataArray;
	}

    
    //get value public
    function getFieldsAndValuesPublic(){
		$dataArray = array(
             'userid'=> str_replace(" ","",$_POST['nidu']), 
			 'password'=> $_POST['pass'],
			 'nmlengkap'=> strtoupper($_POST['nmdostpgelar']), 
			 'email'=> $this->retValOrNull($_POST['email']),
			 'nohp'=> $_POST['notelp'],
             'idjnspengguna'=> '1',
             'noref'=> str_replace(" ","",$_POST['nidu']), 
             'idklppengguna'=> '3',
             'idstatus'=> '1',
             'tgldaftar'=> $_POST['tglhariini'],
        );
		
		return $dataArray;
	}
    
	function delete_dosen(){     
		$where['nidu'] = $_POST['nidu'];
		$wherepublic['userid'] = $_POST['nidu'];
		$ret = $this->rhlib->deleteRecord('dosen',$where);
		$this->rhlib->deleteRecordPublic('penggunaprodi',$wherepublic);
		$this->rhlib->deleteRecordPublic('pengguna',$wherepublic);
        return $ret;
    }

	function getDataRiwayatpdk(){
		$dataArray = array(
			'nidu'=> $_POST['nidu'],
			'nourut'=> $_POST['nourut'],
			'kdjenjangstudi'=> $_POST['kdjenjangstudi'],
			'gelarakademik'=> $_POST['gelarakademik'],
			'kdpt'=> $_POST['kdpt'],
			'nmpt'=> $_POST['nmpt'],
			'kdbdgilmu'=> $_POST['kdbdgilmu'],
			'nmbdgilmu'=> $_POST['nmbdgilmu'],
			'kotapt'=> $_POST['kotapt'],
			'kdnegara'=> $_POST['kdnegara'],
			'tglijazah'=> $_POST['tglijazah'],
		);
		return $dataArray;
	}
	
	function upload(){
	$ori_dir = 'resources/img/ori/o_dosen/';
	$thumb_dir = 'resources/img/thumbs/t_dosen/';
			
	$oldphoto = $this->input->post("oldphoto");
	$newphoto = $this->input->post("newphoto");
		
	$allowedType = array(
        'image/jpeg',
        'image/pjpeg',
        'image/png',
        'image/gif',
        'image/x-png');
		
	$return["success"]='true';
	$return["newfile"]=$oldphoto;
	
		if ($oldphoto != $newphoto) {
			
			if (in_array($_FILES['file_gambar']['type'], $allowedType)) { // jika ekstensi file valid	
					
					if ($_FILES['file_gambar']['size'] <= 800000) { // jika foto kurang dari 800kb	

						$newnamephoto= str_replace(" ", "_", $_FILES['file_gambar']['name']);
						
						$isUpload = move_uploaded_file($_FILES['file_gambar']['tmp_name'], $ori_dir . $newnamephoto);
						
						$this->createThumb($newnamephoto, $ori_dir, $thumb_dir, $_FILES['file_gambar']['type']);

						if ($oldphoto && $isUpload) {
							unlink("resources/img/ori/o_dosen/$oldphoto");
							unlink("resources/img/thumbs/t_dosen/thumb_$oldphoto");
						}
						
						$return["success"]='true';
						$return["newfile"]=$newnamephoto;
						
					} else { // jika foto lebih dari 800kb	
					
					$return["success"]='false1';
				
				}

			} else {
			
			$return["success"]='false2';
			
			}

		}
		
		$return["nama"]=$_FILES['file_gambar']['name'];
			
		echo json_encode($return);	

    } 
	
	function createThumb($img_file, $ori_path, $thumb_path, $img_type)
        {

            // get the image source
            $path = $ori_path;
            $img = $path . $img_file;
            switch ($img_type) {
                case "image/jpeg":
                    $img_src = @imagecreatefromjpeg($img);
                    break;
                case "image/pjpeg":
                    $img_src = @imagecreatefromjpeg($img);
                    break;
                case "image/png":
                    $img_src = @imagecreatefrompng($img);
                    break;
                case "image/x-png":
                    $img_src = @imagecreatefrompng($img);
                    break;
                case "image/gif":
                    $img_src = @imagecreatefromgif($img);
                    break;
            }
            $img_width = imagesx($img_src);
            $img_height = imagesy($img_src);

            $square_size = 100;

            // check width, height, or square
            if ($img_width == $img_height) {
                // square
                $tmp_width = $square_size;
                $tmp_height = $square_size;
            } else
                if ($img_height < $img_width) {
                    // wide
                    $tmp_height = $square_size;
                    $tmp_width = intval(($img_width / $img_height) * $square_size);
                    if ($tmp_width % 2 != 0) {
                        $tmp_width++;
                    }
                } else
                    if ($img_height > $img_width) {
                        $tmp_width = $square_size;
                        $tmp_height = intval(($img_height / $img_width) * $square_size);
                        if ($tmp_height % 2 != 0) {
                            $tmp_height++;
                        }
                    }

            $img_new = imagecreatetruecolor($tmp_width, $tmp_height);
            imagecopyresampled($img_new, $img_src, 0, 0, 0, 0, $tmp_width, $tmp_height, $img_width,
                $img_height);

            // create temporary thumbnail and locate on the server
            $thumb = $thumb_path . "thumb_" . $img_file;
            switch ($img_type) {
                case "image/jpeg":
                    imagejpeg($img_new, $thumb);
                    break;
                case "image/pjpeg":
                    imagejpeg($img_new, $thumb);
                    break;
                case "image/png":
                    imagepng($img_new, $thumb);
                    break;
                case "image/x-png":
                    imagepng($img_new, $thumb);
                    break;
                case "image/gif":
                    imagegif($img_new, $thumb);
                    break;
            }

            // get tmp_image
            switch ($img_type) {
                case "image/jpeg":
                    $img_thumb_square = imagecreatefromjpeg($thumb);
                    break;
                case "image/pjpeg":
                    $img_thumb_square = imagecreatefromjpeg($thumb);
                    break;
                case "image/png":
                    $img_thumb_square = imagecreatefrompng($thumb);
                    break;
                case "image/x-png":
                    $img_thumb_square = imagecreatefrompng($thumb);
                    break;
                case "image/gif":
                    $img_thumb_square = imagecreatefromgif($thumb);
                    break;
            }

            $thumb_width = imagesx($img_thumb_square);
            $thumb_height = imagesy($img_thumb_square);

            if ($thumb_height < $thumb_width) {
                // wide
                $x_src = ($thumb_width - $square_size) / 2;
                $y_src = 0;
                $img_final = imagecreatetruecolor($square_size, $square_size);
                imagecopy($img_final, $img_thumb_square, 0, 0, $x_src, $y_src, $square_size, $square_size);
            } else
                if ($thumb_height > $thumb_width) {
                    // landscape
                    $x_src = 0;
                    $y_src = ($thumb_height - $square_size) / 2;
                    $img_final = imagecreatetruecolor($square_size, $square_size);
                    imagecopy($img_final, $img_thumb_square, 0, 0, $x_src, $y_src, $square_size, $square_size);
                } else {
                    $img_final = imagecreatetruecolor($square_size, $square_size);
                    imagecopy($img_final, $img_thumb_square, 0, 0, 0, 0, $square_size, $square_size);
                }

                switch ($img_type) {
                    case "image/jpeg":
                        @imagejpeg($img_final, $thumb);
                        break;
                    case "image/pjpeg":
                        @imagejpeg($img_final, $thumb);
                        break;
                    case "image/png":
                        @imagepng($img_final, $thumb);
                        break;
                    case "image/x-png":
                        @imagepng($img_final, $thumb);
                        break;
                    case "image/gif":
                        @imagegif($img_final, $thumb);
                        break;
                }
        }
		
	function get_url_public_dos(){ //ISTRA
        $q="SELECT nilai from setting where kdset='PHOTODOSO'";
        
        $query  = $this->db->query($q);
        $data = array();
        if ($query->num_rows() > 0) {
            $data = $query->result();
        }
        
        $ttl = count($data);
        $build_array = array ("success"=>true,"results"=>$ttl,"data"=>array());

        if($ttl>0){
            $build_array["data"]=$data;
        }
        echo json_encode($build_array);
    }
}