<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Kwitansi_Controller extends Controller {
	var $stat;

	public function __construct()
    {
        parent::Controller();
		//include 'excel_reader.php';
        $this->load->library('session');
		$this->load->library('rhlib');
		//$this->load->library('Spreadsheet_Excel_Reader');
	}
	
	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 cek_setbiaya(){
		$kdprodi = $_POST['kdprodi'];
		$thmasuk = $_POST['thmasuk'];
		$idjnskls = $_POST['idjnskls'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdjnsbiaya = $_POST['kdjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];	
		$queryExt = ($kdjnsbiaya!='DPK' && $kdjnsbiaya!='KON')? " AND kdstsemester = '".$kdstsemester."'" : "";
		
		$q = "SELECT count(idsetbiaya) AS jml
				FROM
					setbiaya
				WHERE
				idjnsbiaya = '".$idjnsbiaya."'
				AND idjnskls = '".$idjnskls."'
				AND kdprodi = '".$kdprodi."'
				".$queryExt."
				AND tahunmsk = '".$thmasuk."'";
		
		$query  =  $this->db->query($q);
        $data= ''; 
                    
        if ($query->num_rows() > 0)
        {
            $row = $query->row();
            $data=$row->jml;
        }
        return $data;
	}
	
	function get_biaya_byr(){

		$nim = $_POST['nim'];
		$kdprodi = $_POST['kdprodi'];
		$thmasuk = $_POST['thmasuk'];
		$idjnskls = $_POST['idjnskls'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdjnsbiaya = $_POST['kdjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];		

		$queryExt = ($kdjnsbiaya!='DPK' && $kdjnsbiaya!='KON')? " and kdstsemester = '".$kdstsemester."'" : "";
		
		$count_biaya = $this->cek_setbiaya();
			
			$q = "SELECT biaya, jmlbayar 
				, ifnull((jmlbayar*100/biaya),0) as persenbayar
				, (biaya - jmlbayar) as sisa
				, ifnull((1 - jmlbayar/biaya)*100,0) as persensisa				
				FROM(
					SELECT sum(biaya) as biaya, sum(jmlbayar) as jmlbayar
					FROM(
						SELECT ifnull(biaya,0) as biaya, 0 as jmlbayar
						FROM setbiaya
						WHERE kdprodi='".$kdprodi."'
							and tahunmsk='".$thmasuk."'
							".$queryExt."
							and idjnskls='".$idjnskls."'
							and idjnsbiaya='".$idjnsbiaya."'
						LIMIT 0,1
						UNION
						SELECT 0 as biaya, sum(ifnull(a.jmlbayar,0)) as jmlbayar
						FROM kuitansi a
						WHERE a.idjnsbiaya='".$idjnsbiaya."'
							and a.idstkuitansi = 1
							and a.nim = '".$nim."'
							".$queryExt."
					)A
				)a LIMIT 0,1";
		//}
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			$data = $query->result();
			$ttl = count($data);
			echo json_encode(array ("success"=>true, "result"=>$count_biaya, "data"=>$data));
			return;
		}		
	}
		
	/* function get_biaya_byr(){

		$nim = $_POST['nim'];
		$kdprodi = $_POST['kdprodi'];
		$thmasuk = $_POST['thmasuk'];
		$idjnskls = $_POST['idjnskls'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdjnsbiaya = $_POST['kdjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];		

		$queryExt = ($kdjnsbiaya!='DPK' && $kdjnsbiaya!='KON')? " and a.kdstsemester = '".$kdstsemester."'" : "";
		$queryExt1 = ($kdjnsbiaya!='DPK' && $kdjnsbiaya!='KON')? " and kdstsemester = '".$kdstsemester."'" : "";
		
		$count_biaya = $this->cek_setbiaya();
			
			$q = "SELECT biaya, jmlbayar 
				, ifnull((jmlbayar*100/biaya),0) as persenbayar
				, (biaya - jmlbayar) as sisa
				, ifnull((1 - jmlbayar/biaya)*100,0) as persensisa				
				FROM(
					SELECT sum(biaya) as biaya, sum(jmlbayar) as jmlbayar
					FROM(
						SELECT ifnull(biaya,0) as biaya, 0 as jmlbayar
						FROM setbiaya
						WHERE kdprodi='".$kdprodi."'
							and tahunmsk='".$thmasuk."'
							".$queryExt1."
							and idjnskls='".$idjnskls."'
							and idjnsbiaya='".$idjnsbiaya."'
						LIMIT 0,1
						UNION
						SELECT 0 as biaya, sum(ifnull(a.jmlbayar,0)) as jmlbayar
						FROM kuitansi a
						WHERE a.idjnsbiaya='".$idjnsbiaya."'
							and a.idstkuitansi = 1
							and a.nim = '".$nim."'
						".$queryExt."
					)A
				)a LIMIT 0,1";
		//}
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			$data = $query->result();
			$ttl = count($data);
			echo json_encode(array ("success"=>true, "result"=>$count_biaya, "data"=>$data));
			return;
		}		
	} */
	
	function get_diskon_byr(){
		//nim, kdprodi , thmasuk, idjnskls, idjnsbiaya
		$nim = $_POST['nim'];
		$kdprodi = $_POST['kdprodi'];
		$thmasuk = $_POST['thmasuk'];
		$idjnskls = $_POST['idjnskls'];
		$idjnsbiaya = $_POST['idjnsbiaya'];
		$kdjnsbiaya = $_POST['kdjnsbiaya'];
		$kdstsemester = $_POST['kdstsemester'];	
		$queryExt = ($kdjnsbiaya!='DPK' && $kdjnsbiaya!='KON')? " AND kdstsemester = '".$kdstsemester."'" : "";		
		
			$q = "SELECT *
				FROM
					diskonmhs
				WHERE
					nim = '".$nim."'
					$queryExt
					AND idsetbiaya IN (SELECT idsetbiaya
                     FROM
                       setbiaya
                     WHERE
                       kdprodi = '".$kdprodi."'
                       AND idjnsbiaya = '".$idjnsbiaya."'
                       AND idjnskls = '".$idjnskls."'
                       AND tahunmsk = '".$thmasuk."'
                       AND idstatus = '1')";
		//}
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			//$biaya = $query->row()->biaya;
			$data = $query->result();
			echo json_encode(array ("success"=>true, "data"=>$data));
			return;
		}
		
		//echo json_encode(array ("success"=>false, "biaya"=>"0"));
		echo json_encode(array ("success"=>false,"data"=>array()));		
	}
	
	function get_DPK_byNim(){
		if (isset($_POST['nim'])) {
			$nim = $_POST['nim'];
			$q = "SELECT a.*, b.nmcarabyr,
				ifnull((select sum(jmlbayar) 
						from kuitansi 
						where nim=a.nim and idjnsbiaya=a.idjnsbiaya
						and idstkuitansi = 1),0) as totalDPK
				FROM kuitansi a
				LEFT JOIN carabyr b ON b.idcarabyr=a.idcarabyr
				LEFT JOIN jbiaya c on c.idjnsbiaya=a.idjnsbiaya
				WHERE c.idjnsbiaya='2' 
				and a.nim = '".$nim."'
				and a.idstkuitansi = 1";
			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}

	function get_DPP_byNim(){
		if (isset($_POST['nim']) && isset($_POST['kdprodi']) 
			&& isset($_POST['thmasuk']) && isset($_POST['idjnskls'])) 
		{
			$nim = $_POST['nim'];
			$kdprodi = $_POST['kdprodi'];
			$thmasuk = $_POST['thmasuk'];
			$idjnskls = $_POST['idjnskls'];
			
/* $q = "SELECT nim,kdjnsbiaya,
				'".$thmasuk."' as thmasuk,
				'".$kdprodi."' as kdprodi,
				'".$idjnskls."' as idjnskls,
				biaya, kdstsemester,
				jmlbayar, ifnull((jmlbayar*100/biaya),0) as persenbayar,
				(biaya - jmlbayar) as sisa,
				ifnull((1 - jmlbayar/biaya)*100,0) as persensisa,
				CASE WHEN (biaya - jmlbayar) > 0 THEN 'Belum Lunas'
					ELSE 'Lunas' END as statuslunas
				FROM(
					SELECT nim,idjnsbiaya,kdjnsbiaya,kdstsemester,
					biaya,
					sum(ifnull(a.jmlbayar,0)) as jmlbayar
					FROM(
						SELECT a.nim, a.idjnsbiaya, a.kdstsemester, 
						b.kdjnsbiaya,jmlbayar,
						ifnull(c.biaya,0) as biaya
						FROM kuitansi a
						LEFT JOIN jbiaya b on b.idjnsbiaya=a.idjnsbiaya
						LEFT JOIN (SELECT biaya, idjnsbiaya 
							FROM setbiaya
							WHERE kdprodi='".$kdprodi."'
							and tahunmsk='".$thmasuk."'
							and idjnskls='".$idjnskls."'
						)c on c.idjnsbiaya=a.idjnsbiaya
						WHERE b.kdjnsbiaya='DPP' 
						and a.nim = '".$nim."'
						and a.idstkuitansi = 1
						GROUP BY a.nim,a.kdstsemester
					)A
				)a"; 

$q = "SELECT `kuitansi`.`nim` AS `nim`
     , 'DPP' AS `kdjnsbiaya`
     , '".$thmasuk."' AS `thmasuk`
     , `kuitansi`.`kdstsemester` AS `kdstsemester`
     , `v_tahunakademik`.`nmthnakademik` AS `nmthnakademik`
     , `setbiaya`.`biaya` AS `biaya`
	 , `diskonmhs`.`diskon` AS `diskon`
     , sum(`kuitansi`.`jmlbayar`) AS `jmlbayar`
     , if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) AS `sisa`
     , if(isnull(((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)), ((1 - (sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`)) * 100), ((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)) AS `persensisa`
     , if(isnull(((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)), ((sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`) * 100), ((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)) AS `persenbayar`
     -- , if(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) > 0), 'Belum Lunas', 'Lunas') AS `statuslunas`
	 , if((if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) > 0), 'Belum Lunas', 'Lunas') AS `statuslunas`
FROM
  ((((((`kuitansi`
JOIN `mahasiswa`
ON ((`kuitansi`.`nim` = `mahasiswa`.`nim`)))
JOIN `prodi`
ON ((`mahasiswa`.`kdprodi` = `prodi`.`kdprodi`)))
JOIN `setbiaya`
ON (((`mahasiswa`.`kdprodi` = `setbiaya`.`kdprodi`) AND (`mahasiswa`.`thnmasuk` = `setbiaya`.`tahunmsk`))))
JOIN `stsemester`
ON ((`kuitansi`.`kdstsemester` = `stsemester`.`kdstsemester`)))
JOIN `v_tahunakademik`
ON ((`stsemester`.`kdstsemester` = `v_tahunakademik`.`kdstsemester`)))
LEFT JOIN `diskonmhs`
ON (((`diskonmhs`.`nim` = `mahasiswa`.`nim`) AND (`diskonmhs`.`kdstsemester` = `stsemester`.`kdstsemester`) AND (`diskonmhs`.`idsetbiaya` = `setbiaya`.`idsetbiaya`))))
WHERE
  ((`setbiaya`.`idjnsbiaya` = 1)
  AND (`kuitansi`.`idjnsbiaya` = 1)
  AND (`kuitansi`.`nim` = '".$nim."'))
GROUP BY
  `kuitansi`.`nim`
, `mahasiswa`.`nmmhs`
, `kuitansi`.`kdstsemester`
, `mahasiswa`.`thnmasuk`
, `mahasiswa`.`kdprodi`
, `prodi`.`nmprodi`
, `setbiaya`.`biaya`"; */

$q = "SELECT `kuitansi`.`nim` AS `nim`
	 ,kuitansi.kdstsemester
	 , 'DPP' AS `kdjnsbiaya`
     , '".$thmasuk."' AS `thmasuk`
     , v_tahunakademik.nmthnakademik AS nmthnakademik
     , setbiaya.biaya AS biaya
     , diskonmhs.diskon AS diskon
     , sum(kuitansi.jmlbayar) AS jmlbayar
     , if(isnull(((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)), ((sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`) * 100), ((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)) AS `persenbayar`
     , if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) AS `sisa`
     , if(isnull(((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)), ((1 - (sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`)) * 100), ((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)) AS `persensisa`
     , if((if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) > 0), 'Belum Lunas', 'Lunas') AS `statuslunas`

FROM
  kuitansi
LEFT JOIN v_tahunakademik
ON kuitansi.kdstsemester = v_tahunakademik.kdstsemester
LEFT JOIN setbiaya
ON kuitansi.idjnsbiaya = setbiaya.idjnsbiaya AND kuitansi.kdstsemester = setbiaya.kdstsemester
LEFT JOIN diskonmhs
ON kuitansi.nim = diskonmhs.nim AND kuitansi.kdstsemester = diskonmhs.kdstsemester
AND 
  setbiaya.idsetbiaya = diskonmhs.idsetbiaya

WHERE
  kuitansi.nim = '".$nim."'
  AND kuitansi.idstkuitansi = '1'
  AND setbiaya.kdprodi = '".$kdprodi."'
  AND setbiaya.idjnskls = '".$idjnskls."'
  AND setbiaya.tahunmsk = '".$thmasuk."'
  AND setbiaya.idjnsbiaya ='1'
GROUP BY
  kuitansi.kdstsemester"; 

			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}	
	
	function get_DPPdet(){
		if (isset($_POST['nim']) && isset($_POST['kdstsemester'])){
			$nim = $_POST['nim'];
			$kdstsemester = $_POST['kdstsemester'];
			$q = "SELECT a.*, b.nmcarabyr
				FROM kuitansi a
				LEFT JOIN carabyr b ON b.idcarabyr=a.idcarabyr
				LEFT JOIN jbiaya c on c.idjnsbiaya=a.idjnsbiaya
				WHERE c.idjnsbiaya='1'
				and a.idstkuitansi = 1
				and a.nim = '".$nim."'
				and a.kdstsemester='".$kdstsemester."'";
			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_CT_byNim(){
		if (isset($_POST['nim']) && isset($_POST['kdprodi']) 
			&& isset($_POST['thmasuk']) && isset($_POST['idjnskls'])) 
		{
			$nim = $_POST['nim'];
			$kdprodi = $_POST['kdprodi'];
			$thmasuk = $_POST['thmasuk'];
			$idjnskls = $_POST['idjnskls'];

$q = "SELECT `kuitansi`.`nim` AS `nim`
	 ,kuitansi.kdstsemester
	 , 'DPP' AS `kdjnsbiaya`
     , '".$thmasuk."' AS `thmasuk`
     , v_tahunakademik.nmthnakademik AS nmthnakademik
     , setbiaya.biaya AS biaya
     , diskonmhs.diskon AS diskon
     , sum(kuitansi.jmlbayar) AS jmlbayar
     , if(isnull(((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)), ((sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`) * 100), ((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)) AS `persenbayar`
     , if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) AS `sisa`
     , if(isnull(((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)), ((1 - (sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`)) * 100), ((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)) AS `persensisa`
     , if((if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) > 0), 'Belum Lunas', 'Lunas') AS `statuslunas`

FROM
  kuitansi
LEFT JOIN v_tahunakademik
ON kuitansi.kdstsemester = v_tahunakademik.kdstsemester
LEFT JOIN setbiaya
ON kuitansi.idjnsbiaya = setbiaya.idjnsbiaya AND kuitansi.kdstsemester = setbiaya.kdstsemester
LEFT JOIN diskonmhs
ON kuitansi.nim = diskonmhs.nim AND kuitansi.kdstsemester = diskonmhs.kdstsemester

WHERE
  kuitansi.nim = '".$nim."'
  AND kuitansi.idstkuitansi = '1'
  AND setbiaya.kdprodi = '".$kdprodi."'
  AND setbiaya.idjnskls = '".$idjnskls."'
  AND setbiaya.tahunmsk = '".$thmasuk."'
  AND setbiaya.idjnsbiaya ='3'
GROUP BY
  kuitansi.kdstsemester"; 

			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_TA_byNim(){
		if (isset($_POST['nim']) && isset($_POST['kdprodi']) 
			&& isset($_POST['thmasuk']) && isset($_POST['idjnskls'])) 
		{
			$nim = $_POST['nim'];
			$kdprodi = $_POST['kdprodi'];
			$thmasuk = $_POST['thmasuk'];
			$idjnskls = $_POST['idjnskls'];

$q = "SELECT `kuitansi`.`nim` AS `nim`
	 ,kuitansi.kdstsemester
	 , 'DPP' AS `kdjnsbiaya`
     , '".$thmasuk."' AS `thmasuk`
     , v_tahunakademik.nmthnakademik AS nmthnakademik
     , setbiaya.biaya AS biaya
     , diskonmhs.diskon AS diskon
     , sum(kuitansi.jmlbayar) AS jmlbayar
     , if(isnull(((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)), ((sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`) * 100), ((sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`)) * 100)) AS `persenbayar`
     , if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) AS `sisa`
     , if(isnull(((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)), ((1 - (sum(`kuitansi`.`jmlbayar`) / `setbiaya`.`biaya`)) * 100), ((1 - (sum(`kuitansi`.`jmlbayar`) / (`setbiaya`.`biaya` - `diskonmhs`.`diskon`))) * 100)) AS `persensisa`
     , if((if(isnull(((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)), (`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)), ((`setbiaya`.`biaya` - sum(`kuitansi`.`jmlbayar`)) - `diskonmhs`.`diskon`)) > 0), 'Belum Lunas', 'Lunas') AS `statuslunas`

FROM
  kuitansi
LEFT JOIN v_tahunakademik
ON kuitansi.kdstsemester = v_tahunakademik.kdstsemester
LEFT JOIN setbiaya
ON kuitansi.idjnsbiaya = setbiaya.idjnsbiaya AND kuitansi.kdstsemester = setbiaya.kdstsemester
LEFT JOIN diskonmhs
ON kuitansi.nim = diskonmhs.nim AND kuitansi.kdstsemester = diskonmhs.kdstsemester

WHERE
  kuitansi.nim = '".$nim."'
  AND kuitansi.idstkuitansi = '1'
  AND setbiaya.kdprodi = '".$kdprodi."'
  AND setbiaya.idjnskls = '".$idjnskls."'
  AND setbiaya.tahunmsk = '".$thmasuk."'
  AND setbiaya.idjnsbiaya ='4'
GROUP BY
  kuitansi.kdstsemester"; 

			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_KON_byNim(){
		if (isset($_POST['nim'])) {
			$nim = $_POST['nim'];
			$q = "SELECT a.*, b.nmcarabyr,
				ifnull((select sum(jmlbayar) 
						from kuitansi 
						where nim=a.nim and idjnsbiaya=a.idjnsbiaya
						and idstkuitansi = 1),0) as totalDPK
				FROM kuitansi a
				LEFT JOIN carabyr b ON b.idcarabyr=a.idcarabyr
				LEFT JOIN jbiaya c on c.idjnsbiaya=a.idjnsbiaya
				WHERE c.idjnsbiaya IN('5','11') 
				and a.nim = '".$nim."'
				and a.idstkuitansi = 1";
			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DP9_byNim(){
		if (isset($_POST['nim'])) {
			$nim = $_POST['nim'];
			$q = "SELECT a.*, b.nmcarabyr,
				v_tahunakademik.nmthnakademik,
				ifnull((select sum(jmlbayar) 
						from kuitansi 
						where nim=a.nim and idjnsbiaya=a.idjnsbiaya
						and idstkuitansi = 1),0) as totalDPK
				FROM kuitansi a
				LEFT JOIN carabyr b ON b.idcarabyr=a.idcarabyr
				LEFT JOIN jbiaya c on c.idjnsbiaya=a.idjnsbiaya
				LEFT JOIN v_tahunakademik ON a.kdstsemester = v_tahunakademik.kdstsemester
				WHERE c.idjnsbiaya='6' 
				and a.nim = '".$nim."'
				and a.idstkuitansi = 1";
			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DPKS_byNim(){
		if (isset($_POST['nim'])) {
			$nim = $_POST['nim'];
			$q = "SELECT a.*, b.nmcarabyr,
				ifnull((select sum(jmlbayar) 
						from kuitansi 
						where nim=a.nim and idjnsbiaya=a.idjnsbiaya
						and idstkuitansi = 1),0) as totalDPK
				FROM kuitansi a
				LEFT JOIN carabyr b ON b.idcarabyr=a.idcarabyr
				LEFT JOIN jbiaya c on c.idjnsbiaya=a.idjnsbiaya
				WHERE c.idjnsbiaya='7' 
				and a.nim = '".$nim."'
				and a.idstkuitansi = 1";
			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_CUTI123_byNim(){
		if (isset($_POST['nim'])) {
			$nim = $_POST['nim'];
			$q = "SELECT a.*, b.nmcarabyr, c.nmjnsbiaya
				FROM kuitansi a
				LEFT JOIN carabyr b ON b.idcarabyr=a.idcarabyr
				LEFT JOIN jbiaya c on c.idjnsbiaya=a.idjnsbiaya
				WHERE (c.idjnsbiaya='8' or c.idjnsbiaya='9' or c.idjnsbiaya='10')
				and a.nim = '".$nim."'
				and a.idstkuitansi = 1";
			$this->jsonFromQuery($q);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function lookup_kwitansi(){
		$q = "SELECT a.nokuitansi,a.tglkuitansi, a.nim,b.nmmhs,
			b.kdprodi, g.nmprodi, b.thnmasuk, b.idklsmhs,
			h.idjnskls, i.nmjnskls,
			a.noref, a.jmlbayar, a.useridinput, 
			a.idjnsbiaya,c.kdjnsbiaya, c.nmjnsbiaya,
			a.idcarabyr, d.nmcarabyr, 
			a.idstkuitansi, e.nmstkuitansi,
			a.kdstsemester, f.nmthnakademik 			
			FROM kuitansi a
			LEFT JOIN mahasiswa b on b.nim=a.nim
			LEFT JOIN jbiaya c on c.idjnsbiaya=a.idjnsbiaya
			LEFT JOIN carabyr d ON d.idcarabyr=a.idcarabyr			
			LEFT JOIN stkuitansi e ON e.idstkuitansi=a.idstkuitansi
			LEFT JOIN v_tahunakademik f ON f.kdstsemester = a.kdstsemester
			LEFT JOIN prodi g ON b.kdprodi = g.kdprodi
			LEFT JOIN klsmhs h ON b.idklsmhs = h.idklsmhs
			LEFT JOIN jkls i ON h.idjnskls = i.idjnskls";
		$this->jsonFromQueryWLimit($q);
	}
	
	/* function get_autoNOKWI(){
		$prefix = $_POST['prefix'];
		$q = "SELECT CONCAT('".$prefix."',SUBSTR(nokuitansi, LENGTH(nokuitansi) - 5)) as nokuitansi
			FROM (select CONCAT('00000',nokuitansi) as nokuitansi 
					from( SELECT IFNULL(
							(select MAX(
								CAST(SUBSTR(nokuitansi,LENGTH(nokuitansi)-5, 6)
							 AS UNSIGNED)) + 1
							FROM kuitansi 
							WHERE SUBSTR(nokuitansi, 1, 9) = '".$prefix."')
						, 1) 
					as nokuitansi)a1
				)a2";
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){ 
			$nokuitansi = $query->row()->nokuitansi;		
			echo json_encode(array ("success"=>true,"nokuitansi"=>$nokuitansi));
		}
		else json_encode(array ("success"=>false,"nokuitansi"=>"0"));
	} */
	
	function get_autoNOKWI(){
		$kdbiaya = $_POST['kdbiaya'];
		$thn = $_POST['thn'];
		$q = "SELECT getOtoKui('".$kdbiaya."') as nokuitansi";
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){ 
			$nokuitansi = $query->row()->nokuitansi;		
			echo json_encode(array ("success"=>true,"nokuitansi"=>$nokuitansi));
		}
		else json_encode(array ("success"=>false,"nokuitansi"=>"0"));
	}
	
	function insert_kuitansi(){
		$dataArray = $this->getFieldsAndValues();
		$ret = $this->rhlib->insertRecord('kuitansi',$dataArray);
        return $ret;
	}
	
	function cancel_kuitansi(){
		$ip = $_SERVER['REMOTE_ADDR'];
		$nmcom = $this->retValOrNull($_POST['nmcom']);
		$useridupdate = $this->retValOrNull($_POST['useridupdate']);
		$this->db->trans_begin();        
        $where['nokuitansi']= $_POST['nokuitansi'];
        $this->db->where($where);
		$this->db->set('idstkuitansi', 2);	//idstkuitansi=2, nmstkuitansi=Batal
        $this->db->set('useridupdate', $useridupdate);
		$this->db->set('ipadmin', $ip);
		$this->db->set('nmkomadmin', $nmcom);
		$this->db->set('tglupdate',$_POST['tglupdate']);
		$this->db->update("kuitansi");

        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;	
	}
	
	function getFieldsAndValues(){
		$ip = $_SERVER['REMOTE_ADDR'];
		$dataArray = array(
			'nokuitansi'=> $_POST['nokuitansi'],
			'tglkuitansi'=> $_POST['tglkuitansi'],
			'jamkuitansi'=> $_POST['jamkuitansi'],
			'idcarabyr'=> $_POST['idcarabyr'],
			'noref'=> $_POST['noref'],
			'idjnsbiaya'=> $_POST['idjnsbiaya'],
			'kdstsemester'=> $this->retValOrNull($_POST['kdstsemester']),
			'nim' => $_POST['nim'],
			'jmlbayar' => $_POST['jmlbayar'],
			'catatan' => $_POST['catatan'],
			'tglinput' => $_POST['tglinput'],
			'tglupdate' => $_POST['tglupdate'],
			'useridinput' => $_POST['useridinput'],
			'useridupdate' => $this->retValOrNull($_POST['useridupdate']),
			'idstkuitansi' => $_POST['idstkuitansi'],
			'ipadmin' => $ip,
			'nmkomadmin' => $_POST['nmcom'],
		);
		return $dataArray;
	}
	
}