<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Report_Controller extends Controller {
	var $stat;

	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
	}
			
	function getJsonFromQuery($q){
		$query  = $this->db->query($q);
        $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 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 getDataLimited($q){
		$start = $_POST['start'];
        $limit = $_POST['limit'];
		
		if ($start==null){
            $start = 0;
			$limit = 20;
		}	
		$data = array();
		$build_array = array ("success"=>false,"results"=>0,"data"=>array());			
        
		$query1  = $this->db->query($q);	//query all rows        
        if ($query1->num_rows() > 0) {
			$total = $query1->num_rows(); // total data			
			$q .=" LIMIT ".$start.", ".$limit; // query dengan limit
			$query2  = $this->db->query($q);
            $data = $query2->result();
			$build_array["success"]=true;
			$build_array["results"]=$total;
            //$build_array["count"]=count($data);
			$build_array["data"]=$data;
        }
        echo json_encode($build_array);
	}
	
	function retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
		
	function get_DpkProdi(){
		if (isset($_POST['thmasuk']) && isset($_POST['idjnskls'])){
			$thmasuk = $_POST['thmasuk'];
			$idjnskls = $_POST['idjnskls'];
			$q = "SELECT kdfakultas,nmfakultas,IFNULL(fakultas,nmfakultas) as fakultas,
					kdprodi,nourutprodi,nmprodi, IFNULL(lnmprodi,nmprodi) as lnmprodi,
					biaya,jmlbayar,ifnull(jmlbayar*100/biaya,0) as persenbayar,
					(biaya-jmlbayar) as sisa, ifnull((1 - jmlbayar/biaya)*100,0) as persensisa
				FROM(
					SELECT AA.kdfakultas,BB.nmfakultas,
					CONCAT(BB.kdfakultas,'. FAKULTAS ',BB.nmfakultas) as fakultas,
					AA.kdprodi,AA.nourutprodi,AA.nmprodi,
					CONCAT(AA.nourutprodi,'. ',AA.nmprodi, ' (',CC.nmjenjangstudi,')') as lnmprodi,
					ifnull(MBYR.biaya,0) as biaya,ifnull(MBYR.jmlbayar,0) as jmlbayar
					FROM prodi AA
					LEFT JOIN fakultas BB on BB.kdfakultas=AA.kdfakultas
					LEFT JOIN 
						(select kdkodtbkod as kdjenjangstudi,nmkodtbkod as nmjenjangstudi from tbkod where kdapltbkod=4)CC
						ON CC.kdjenjangstudi=AA.kdjenjangstudi
					LEFT JOIN
					(
						select a.kdprodi, sum(a.biaya) as biaya, sum(a.jmlbayar) as jmlbayar
						from
						(
							SELECT A.nim, A.kdprodi, IFNULL(C.biaya,0) as biaya, 
							IFNULL(D.jmlbayar,0) as jmlbayar
							FROM mahasiswa A
							LEFT JOIN klsmhs B ON B.idklsmhs=A.idklsmhs
							LEFT JOIN
							(
								select a.idjnsbiaya, a.biaya,a.idjnskls,a.kdprodi,a.tahunmsk
								from setbiaya a
								left join jbiaya b on b.idjnsbiaya=a.idjnsbiaya
								where a.idstatus=1
								and b.kdjnsbiaya='DPK'
							)C on C.kdprodi=A.kdprodi and C.tahunmsk=A.thnmasuk and C.idjnskls=B.idjnskls
							LEFT JOIN
							(
								select nim,idjnsbiaya,sum(jmlbayar) as jmlbayar
								from kuitansi
								where idstkuitansi=1
								group by nim,idjnsbiaya
							)D on D.nim=A.nim and D.idjnsbiaya=C.idjnsbiaya
							WHERE A.thnmasuk='".$thmasuk."'
							AND B.idjnskls='".$idjnskls."'
						)a GROUP BY a.kdprodi
					)MBYR ON MBYR.kdprodi=AA.kdprodi
					WHERE BB.kdfakultas<>'0'
				)FINAL
				ORDER BY kdfakultas,nourutprodi";
			$this->getJsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
		
	function get_DPKdet(){
		if ( isset($_POST['thmasuk']) && isset($_POST['idjnskls'])
			&& isset($_POST['kdprodi']) )
		{
			$thmasuk = $_POST['thmasuk'];
			$idjnskls = $_POST['idjnskls'];
			$kdprodi = $_POST['kdprodi'];
			$persen = (isset($_POST['persen']))? $_POST['persen'] : 0;
			$q = "SELECT nim, nmmhs,kdprodi,
					biaya,jmlbayar,ifnull(jmlbayar*100/biaya,0) as persenbayar,
					(biaya-jmlbayar) as sisa, ifnull((1 - jmlbayar/biaya)*100,0) as persensisa,
					CASE WHEN (jmlbayar < biaya) THEN 'Belum Lunas'
						WHEN (jmlbayar=biaya) THEN 'Lunas'
					ELSE 'N/A' END AS stlunas
					FROM(
						SELECT A.nim, A.nmmhs, A.kdprodi, IFNULL(C.biaya,0) as biaya, 
						IFNULL(D.jmlbayar,0) as jmlbayar
						FROM mahasiswa A
						LEFT JOIN klsmhs B ON B.idklsmhs=A.idklsmhs
						LEFT JOIN
						(
							select a.idjnsbiaya, a.biaya,a.idjnskls,a.kdprodi,a.tahunmsk
							from setbiaya a
							left join jbiaya b on b.idjnsbiaya=a.idjnsbiaya
							where a.idstatus=1
							and b.kdjnsbiaya='DPK'
						)C on C.kdprodi=A.kdprodi and C.tahunmsk=A.thnmasuk and C.idjnskls=B.idjnskls
						LEFT JOIN
						(
							select nim,idjnsbiaya,sum(jmlbayar) as jmlbayar
							from kuitansi
							where idstkuitansi=1
							group by nim,idjnsbiaya
						)D on D.nim=A.nim and D.idjnsbiaya=C.idjnsbiaya
						WHERE A.thnmasuk='".$thmasuk."'
						AND A.kdprodi='".$kdprodi."'
						AND B.idjnskls='".$idjnskls."'
					)FINAL
					where ifnull(jmlbayar*100/biaya,0)>=".$persen."
					ORDER BY nim";
			$this->getJsonFromQuery($q);
		}
		//else 
		json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}

	function get_DppProdi(){
		if (isset($_POST['kdstsemester']) && isset($_POST['idjnskls'])){
			$kdstsemester = $_POST['kdstsemester'];
			$idjnskls = $_POST['idjnskls'];
			$q = "SELECT kdfakultas,nmfakultas,IFNULL(fakultas,nmfakultas) as fakultas,
					kdprodi,nourutprodi,nmprodi, IFNULL(lnmprodi,nmprodi) as lnmprodi,
					biaya,jmlbayar,ifnull(jmlbayar*100/biaya,0) as persenbayar,
					(biaya-jmlbayar) as sisa, ifnull((1 - jmlbayar/biaya)*100,0) as persensisa
				FROM(
					SELECT AA.kdfakultas,BB.nmfakultas,
					CONCAT(BB.kdfakultas,'. FAKULTAS ',BB.nmfakultas) as fakultas,
					AA.kdprodi,AA.nourutprodi,AA.nmprodi,
					CONCAT(AA.nourutprodi,'. ',AA.nmprodi, ' (',CC.nmjenjangstudi,')') as lnmprodi,
					ifnull(MBYR.biaya,0) as biaya,ifnull(MBYR.jmlbayar,0) as jmlbayar
					FROM prodi AA
					LEFT JOIN fakultas BB on BB.kdfakultas=AA.kdfakultas
					LEFT JOIN 
						(select kdkodtbkod as kdjenjangstudi,nmkodtbkod as nmjenjangstudi from tbkod where kdapltbkod=4)CC
						ON CC.kdjenjangstudi=AA.kdjenjangstudi
					LEFT JOIN
					(
						select a.kdprodi, sum(a.biaya) as biaya, sum(a.jmlbayar) as jmlbayar
						from
						(
							SELECT A.nim, A.kdprodi, IFNULL(C.biaya,0) as biaya, 
							IFNULL(D.jmlbayar,0) as jmlbayar
							FROM mahasiswa A
							LEFT JOIN klsmhs B ON B.idklsmhs=A.idklsmhs
							LEFT JOIN
							(
								select a.idjnsbiaya, a.biaya,a.idjnskls,a.kdprodi,a.tahunmsk
								from setbiaya a
								left join jbiaya b on b.idjnsbiaya=a.idjnsbiaya
								where a.idstatus=1
								and b.kdjnsbiaya='DPP'
							)C on C.kdprodi=A.kdprodi and C.tahunmsk=A.thnmasuk and C.idjnskls=B.idjnskls
							LEFT JOIN
							(
								select nim,idjnsbiaya,sum(jmlbayar) as jmlbayar
								from kuitansi
								where idstkuitansi=1
								and kdstsemester='".$kdstsemester."'
								group by nim,idjnsbiaya
							)D on D.nim=A.nim and D.idjnsbiaya=C.idjnsbiaya
							WHERE B.idjnskls='".$idjnskls."'
						)a GROUP BY a.kdprodi
					)MBYR ON MBYR.kdprodi=AA.kdprodi
					WHERE BB.kdfakultas<>'0'
				)FINAL
				ORDER BY kdfakultas,nourutprodi";
			$this->getJsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DPP2det(){	//USING SP
		if ( isset($_POST['kdstsemester']) && isset($_POST['idjnskls'])
			&& isset($_POST['kdprodi']) )
		{
			$kdprodi = $_POST['kdprodi'];
			$idjnskls = $_POST['idjnskls'];
			$kdstsemester = $_POST['kdstsemester'];
			$persen = (isset($_POST['persen']))? $_POST['persen'] : 0;
			
			$sql = "CALL sp_GetDPPdet(?,?,?,?)";
			$parameters = array($kdprodi, $idjnskls, $kdstsemester, $persen);
			$this->getJsonFromSP($sql, $parameters);
		}
		//else 
		json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}

	
	function get_DPPdet(){	//WILL REPLACED BY function get_DPP2det() use SP
		if ( isset($_POST['kdstsemester']) && isset($_POST['idjnskls'])
			&& isset($_POST['kdprodi']) )
		{
			$kdstsemester = $_POST['kdstsemester'];
			$idjnskls = $_POST['idjnskls'];
			$kdprodi = $_POST['kdprodi'];
			$persen = (isset($_POST['persen']))? $_POST['persen'] : 0;
			$q = "SELECT nim, nmmhs,kdprodi,
					biaya,jmlbayar,ifnull(jmlbayar*100/biaya,0) as persenbayar,
					(biaya-jmlbayar) as sisa, ifnull((1 - jmlbayar/biaya)*100,0) as persensisa,
					CASE WHEN (jmlbayar < biaya) THEN 'Belum Lunas'
						WHEN (jmlbayar=biaya) THEN 'Lunas'
					ELSE 'N/A' END AS stlunas
					FROM(
						SELECT A.nim, A.nmmhs, A.kdprodi, IFNULL(C.biaya,0) as biaya, 
						IFNULL(D.jmlbayar,0) as jmlbayar
						FROM mahasiswa A
						LEFT JOIN klsmhs B ON B.idklsmhs=A.idklsmhs
						LEFT JOIN
						(
							select a.idjnsbiaya, a.biaya,a.idjnskls,a.kdprodi,a.tahunmsk
							from setbiaya a
							left join jbiaya b on b.idjnsbiaya=a.idjnsbiaya
							where a.idstatus=1
							and b.kdjnsbiaya='DPP'
						)C on C.kdprodi=A.kdprodi and C.tahunmsk=A.thnmasuk and C.idjnskls=B.idjnskls
						LEFT JOIN
						(
							select nim,idjnsbiaya,sum(jmlbayar) as jmlbayar
							from kuitansi
							where idstkuitansi=1
							and kdstsemester='".$kdstsemester."'
							group by nim,idjnsbiaya
						)D on D.nim=A.nim and D.idjnsbiaya=C.idjnsbiaya
						WHERE A.kdprodi='".$kdprodi."'
						AND B.idjnskls='".$idjnskls."'
					)FINAL
					where ifnull(jmlbayar*100/biaya,0)>=".$persen."
					ORDER BY nim";
			$this->getJsonFromQuery($q);
		}
		//else 
		json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}

}