<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Daftarmhswisuda_Controller extends Controller {
	var $stat;
	
	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
    }
	function jsonFromQueryWLimit($q) {
		$this->rhlib->jsonFromQueryWLimit($q);
	}

	function get_strquery($kdprodi, $kdstsemester, $urlfoto){
		$addfield = ($urlfoto) ? ", concat('$urlfoto',ifnull(c.foto,'-')) AS pathfoto":"";
		return "SELECT c.*
			 , b.nmstaktivitasmhs
			 , d.nmkodtbkod AS nmstmasuk
			 , e.nmprodi
			 ".$addfield."
		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 tbkod d
		ON c.kdstawalmhs = d.kdkodtbkod AND d.kdapltbkod = '06'
		LEFT JOIN v_prodi e
		ON c.kdprodi = e.kdprodi
		LEFT JOIN (
		SELECT kdstsemester
			 , count(kdsyaratwisuda) AS csyarat
		FROM
		  syaratwisuda
		GROUP BY
		  kdstsemester) f
		ON f.kdstsemester = '".$kdstsemester."'
		LEFT JOIN (
		SELECT kdstsemester
			 , nim
			 , count(kdsyaratwisuda) AS csyaratkl
		FROM
		  klkpsyaratwisuda
		GROUP BY
		  kdstsemester) g
		ON g.kdstsemester = '".$kdstsemester."' AND g.nim = a.nim
		WHERE
		CASE 
		WHEN '".$kdprodi."' <> 99 THEN
		  c.kdprodi = '".$kdprodi."'
		  AND a.kdstsemester = '".$kdstsemester."'
		  AND ifnull(f.csyarat, 0) > 0
		  AND ifnull(f.csyarat, 0) = ifnull(g.csyaratkl, 0)
		WHEN '".$kdprodi."' = 99 THEN
		  AND a.kdstsemester = '".$kdstsemester."'
		  AND ifnull(f.csyarat, 0) > 0
		  AND ifnull(f.csyarat, 0) = ifnull(g.csyaratkl, 0)
		 END 
		ORDER BY
		  a.nim";
	}
	
	function get_daftarmhswisuda(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$urlfoto = $this->get_url_foto_mhs();
		$q = $this->get_strquery($kdprodi, $kdstsemester, $urlfoto);

		$this->jsonFromQueryWLimit($q);	
	}
	
	function exportexcelwisudawan($kdprodi,$kdstsemester) {
		$q = $this->get_strquery($kdprodi, $kdstsemester, null);
		$data['eksport'] = $this->db->query($q)->result();
		$data['table'] = 'excel_wisudawan';
		$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 get_url_foto_mhs(){
		$q = "SELECT nilai as url from setting where idklpset='6' and idset='36'";
        $query  = $this->db->query($q);
        $url= ''; 
                    
        if ($query->num_rows() != 0)
        {
            $row = $query->row();
            $url=$row->url;
        }
        return $url;
	}
	
}
?>