<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class RptKeu_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_DpkAllProdi(){
		if (isset($_POST['thmasuk']) && isset($_POST['idklsmhs'])){
			$thmasuk = $_POST['thmasuk'];
			$idklsmhs = $_POST['idklsmhs'];
			
			$sql = "CALL sp_GetDpkAllProdi(?,?)";
			$parameters = array($thmasuk, $idklsmhs);
			$this->getJsonFromSP($sql, $parameters);
			
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DpkAllProdiExcel(){
			if (isset($_POST['thmasuk']) && isset($_POST['idklsmhs'])){
			$thmasuk = $_POST['thmasuk'];
			$idklsmhs = $_POST['idklsmhs'];
			
			$sql = "CALL sp_GetDpkAllProdi_excel(?,?)";
			$parameters = array($thmasuk, $idklsmhs);
			$this->getJsonFromSP($sql, $parameters);
			
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
		
	function get_DpkDet(){
		if ( isset($_POST['thmasuk']) && isset($_POST['idklsmhs'])
			&& isset($_POST['kdprodi']) )
		{
			$thmasuk = $_POST['thmasuk'];
			$idklsmhs = $_POST['idklsmhs'];
			$kdprodi = $_POST['kdprodi'];
			$persen1 =  $_POST['persen1'];
			$kondisi1 = $_POST['kondisi1'];
			$persen2 =  $_POST['persen2'];
			$kondisi2 = $_POST['kondisi2'];
			
			$sql = "CALL sp_GetDpkDet(?,?,?,?,?,?,?)";
			$parameters = array($thmasuk, $idklsmhs, $kdprodi, $persen1, $kondisi1, $persen2, $kondisi2);
			$this->getJsonFromSP($sql, $parameters);
		}
		//else 
		json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DpkDetExcel(){
		if ( isset($_POST['thmasuk']) && isset($_POST['idklsmhs'])
			&& isset($_POST['kdprodi']) )
		{
			$thmasuk = $_POST['thmasuk'];
			$idklsmhs = $_POST['idklsmhs'];
			$kdprodi = $_POST['kdprodi'];
			$persen1 =  $_POST['persen1'];
			$kondisi1 = $_POST['kondisi1'];
			$persen2 =  $_POST['persen2'];
			$kondisi2 = $_POST['kondisi2'];
			$isallprodi = $_POST['isallprodi'];
			
			$sql = "CALL sp_GetDpkDet_excel(?,?,?,?,?,?,?,?)";
			$parameters = array($thmasuk, $idklsmhs, $kdprodi, $persen1, $kondisi1, $persen2, $kondisi2, $isallprodi);
			$this->getJsonFromSP($sql, $parameters);
		}
		//else 
		json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}

	function get_DppAllProdi(){
		if (isset($_POST['kdstsemester']) && isset($_POST['idklsmhs'])){
			$kdstsemester = $_POST['kdstsemester'];
			$idklsmhs = $_POST['idklsmhs'];
			
			$sql = "CALL sp_getdppallprodinew(?,?)";
			$parameters = array($idklsmhs, $kdstsemester);
			$this->getJsonFromSP($sql, $parameters);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DppAllProdiExcel(){
		if (isset($_POST['kdstsemester']) && isset($_POST['idklsmhs'])){
			$kdstsemester = $_POST['kdstsemester'];
			$idklsmhs = $_POST['idklsmhs'];
			
			$sql = "CALL sp_getdppallprodinew(?,?)";
			$parameters = array($idklsmhs, $kdstsemester);
			$this->getJsonFromSP($sql, $parameters);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DppDet(){	//USING SP
		if ( isset($_POST['kdstsemester']) && isset($_POST['idklsmhs'])
			&& isset($_POST['kdprodi']) )
		{
			$kdprodi = $_POST['kdprodi'];
			$idklsmhs = $_POST['idklsmhs'];
			$kdstsemester = $_POST['kdstsemester'];
			$persen1 =  $_POST['persen1'];
			$kondisi1 = $_POST['kondisi1'];
			$persen2 =  $_POST['persen2'];
			$kondisi2 = $_POST['kondisi2'];
			
			$sql = "CALL sp_getDppDet(?,?,?,?,?,?,?)";
			$parameters = array($kdprodi, $idklsmhs, $kdstsemester, $persen1, $kondisi1, $persen2, $kondisi2);
			$this->getJsonFromSP($sql, $parameters);
		}
		//else 
		json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DppDetExcel(){	//USING SP
		if ( isset($_POST['kdstsemester']) && isset($_POST['idklsmhs'])
			&& isset($_POST['kdprodi']) )
		{
			$kdprodi = $_POST['kdprodi'];
			$idklsmhs = $_POST['idklsmhs'];
			$kdstsemester = $_POST['kdstsemester'];
			$persen1 =  $_POST['persen1'];
			$kondisi1 = $_POST['kondisi1'];
			$persen2 =  $_POST['persen2'];
			$kondisi2 = $_POST['kondisi2'];
			$isallprodi = $_POST['isallprodi'];
			
			$sql = "CALL sp_getDppDet_excel(?,?,?,?,?,?,?,?)";
			$parameters = array($kdprodi, $idklsmhs, $kdstsemester, $persen1, $kondisi1, $persen2, $kondisi2, $isallprodi);
			$this->getJsonFromSP($sql, $parameters);
		}
		//else 
		json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_TgkAllProdi(){
		if (isset($_POST['idjnskls'])){
			$idjnskls = $_POST['idjnskls'];
			
			$sql = "CALL sp_getTunggakanAllProdi(?)";
			$parameters = array($idjnskls);
			$this->getJsonFromSP($sql, $parameters);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_TgkDet(){	//USING SP
		if ( isset($_POST['idjnskls']) && isset($_POST['kdprodi']) )
		{
			$idjnskls = $_POST['idjnskls'];
			$kdprodi = $_POST['kdprodi'];
			
			$sql = "CALL sp_getTunggakanDet(?,?)";
			$parameters = array($idjnskls, $kdprodi);
			$this->getJsonFromSP($sql, $parameters);
		}
		//else 
		json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	//============Start Laporan Keuangan Per Jenis Biaya============//
	function get_KeuPerJbiaya(){
		if (isset($_POST['tglawal']) && isset($_POST['tglakhir'])){
			$jbiaya = $_POST['jbiaya'];
			$fakultas = $_POST['fakultas'];
			$prodi = ($_POST['prodi'] == '') ? null:$_POST['prodi'];
			$tgl1 = $_POST['tglawal'];
			$tgl2 = $_POST['tglakhir'];
			
			$sql = "CALL sp_getKeuPerJbiaya(?,?,?,?,?)";
			$parameters = array($jbiaya, $fakultas, $prodi, $tgl1, $tgl2);
			$this->getJsonFromSP($sql, $parameters);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_KeuPerJbiayaExcel(){
		if (isset($_POST['tglawal']) && isset($_POST['tglakhir'])){
			$jbiaya = $_POST['jbiaya'];
			$fakultas = $_POST['fakultas'];
			$prodi = ($_POST['prodi'] == '') ? null:$_POST['prodi'];
			$tgl1 = $_POST['tglawal'];
			$tgl2 = $_POST['tglakhir'];
			
			$sql = "CALL sp_getKeuPerJbiaya_excel(?,?,?,?,?)";
			$parameters = array($jbiaya, $fakultas, $prodi, $tgl1, $tgl2);
			$this->getJsonFromSP($sql, $parameters);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_sum_KeuPerJbiaya(){
	
		$parameters = array(
					'bayar1'=> $_POST['carabayar1'],
					'bayar2'=> $_POST['carabayar2'],
					'bayar3'=> $_POST['carabayar3'],
					'tgl1'=> $_POST['tglawal'],
					'tgl2'=> $_POST['tglakhir'],
					'jbiaya'=> $_POST['jbiaya'],
					'fakultas'=> $_POST['fakultas'],
					'prodi'=> $_POST['prodi'],
					);
		
        $q = "CALL sp_getSumKeuPerJbiaya(?,?,?,?,?,?,?,?)";
        $query  = $this->db->query($q, $parameters);
        $rec = ''; 
                    
        if ($query) {
            $row = $query->row();
			$build_array = array (
				"sum1"=>($row->sumcash) ? $row->sumcash:0,
				"sum2"=>($row->sumtransfer) ? $row->sumtransfer:0,
				"sum3"=>($row->sumviabank) ? $row->sumviabank:0,
				"sumall"=>($row->sumall) ? $row->sumall:0,
			);
			
        } else {
			$build_array = array (
				"sum1"=>0,
				"sum2"=>0,
				"sum3"=>0,
				"sumall"=>0,
			);
		}
		
		echo json_encode($build_array);
    }
	//============End Laporan Keuangan Per Jenis Biaya============//
	
	function get_DppDpk(){
		if (isset($_POST['tglawal']) && isset($_POST['tglakhir'])){
			$tgl1 = $_POST['tglawal'];
			$tgl2 = $_POST['tglakhir'];
			
			$sql = "CALL sp_getDppDpk(?,?)";
			$parameters = array($tgl1, $tgl2);
			$this->getJsonFromSP($sql, $parameters);
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_sum_dppdpk(){
	
		$parameters = array(
					'bayar1'=> $_POST['carabayar1'],
					'bayar2'=> $_POST['carabayar2'],
					'bayar3'=> $_POST['carabayar3'],
					'tgl1'=> $_POST['tglawal'],
					'tgl2'=> $_POST['tglakhir'],
					);
		
        $q = "CALL sp_getSumDppDpk(?,?,?,?,?)";
        $query  = $this->db->query($q, $parameters);
        $rec = ''; 
                    
        if ($query) {
            $row = $query->row();
			$build_array = array (
				"sum1"=>($row->sumcash) ? $row->sumcash:0,
				"sum2"=>($row->sumtransfer) ? $row->sumtransfer:0,
				"sum3"=>($row->sumviabank) ? $row->sumviabank:0,
				"sumall"=>($row->sumall) ? $row->sumall:0,
			);
			
        } else {
			$build_array = array (
				"sum1"=>0,
				"sum2"=>0,
				"sum3"=>0,
				"sumall"=>0,
			);
		}
		
		echo json_encode($build_array);
    } 	
	
	function get_DpkPerangkatan(){
		if (isset($_POST['thmasuk']) && isset($_POST['kdprodi'])&& isset($_POST['idklsmhs'])){
			$thmasuk = $_POST['thmasuk'];
			$kdprodi = $_POST['kdprodi'];
			$idklsmhs = $_POST['idklsmhs'];
			
			$sql = "CALL sp_getDpkDet_perangkatan(?,?,?)";
			$parameters = array($thmasuk, $kdprodi, $idklsmhs);
			$this->getJsonFromSP($sql, $parameters);
			
			
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DpkPerangkatanExcel(){
		if (isset($_POST['thmasuk']) && isset($_POST['kdprodi'])&& isset($_POST['idklsmhs'])){
			$thmasuk = $_POST['thmasuk'];
			$kdprodi = $_POST['kdprodi'];
			$idklsmhs = $_POST['idklsmhs'];
			
			$sql = "CALL sp_getDpkDet_perangkatan_excel(?,?,?)";
			$parameters = array($thmasuk, $kdprodi, $idklsmhs);
			$this->getJsonFromSP($sql, $parameters);
			
			
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DpkPerMhs(){
		if (isset($_POST['nim'])/*  && isset($_POST['thnmasuk']) && isset($_POST['jumlah']) && isset($_POST['tunggakan']) && isset($_POST['stbayar']) */){
			$nim = $_POST['nim'];
			/* $thnmasuk = $_POST['thnmasuk'];
			$jumlah = $_POST['jumlah'];
			$tunggakan = $_POST['tunggakan'];
			$stbayar = $_POST['stbayar'];
			$diskon = $_POST['diskon'];
			$sisa = $_POST['sisa']; */
			
			$sql = "CALL sp_getDpkDet_perMhs(?)";
			$parameters = array($nim);
			$this->getJsonFromSP($sql, $parameters);
			
			
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_DppPerMhs(){
		if (isset($_POST['nim'])/*  && isset($_POST['thnmasuk']) && isset($_POST['jumlah']) && isset($_POST['tunggakan']) && isset($_POST['stbayar']) */){
			$nim = $_POST['nim'];
			/* $thnmasuk = $_POST['thnmasuk'];
			$jumlah = $_POST['jumlah'];
			$tunggakan = $_POST['tunggakan'];
			$stbayar = $_POST['stbayar'];
			$diskon = $_POST['diskon'];
			$sisa = $_POST['sisa']; */
			
			$sql = "CALL sp_getDppDet_perMhs(?)";
			$parameters = array($nim);
			$this->getJsonFromSP($sql, $parameters);
			
			
		}
		else json_encode(array("success"=>false,"results"=>0,"data"=>array(),"msg"=>"no data"));
	}
	
	function get_smstskrg(){
		$tahunangkatan = $_POST['tahunangkatan'];	
			
		$q = "SELECT count(kdstsemester) AS semester
				FROM
					stsemester
				WHERE
					kdstsemester BETWEEN '".$tahunangkatan."' AND (SELECT kdstsemester FROM stsemester WHERE idstatus = '1')";
					  
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			$data = $query->result();
			$ttl = count($data);
			echo json_encode(array ("success"=>true, "result"=>0, "data"=>$data));
			return;
		}		
	}
	
	function get_jmlthp(){
		$tahunangkatan = $_POST['tahunangkatan'];	
		$q = "SELECT count(idsttahapan) AS jmlthpn, sum(nominal) AS jmlbiaya FROM setbiayatahapan
			WHERE setbiayatahapan.idjnsbiaya=2 AND setbiayatahapan.thnmasuk='$tahunangkatan'";
					  
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			$data = $query->result();
			$ttl = count($data);
			echo json_encode(array ("success"=>true, "result"=>0, "data"=>$data));
			return;
		}		
	}
	
	function get_jmlmhsdpk(){
		$tahunangkatan = $_POST['tahunangkatan'];
		$kdprodi = $_POST['kdprodi'];		
			
		$q = "SELECT COUNT(nim) AS jummhsdpk FROM mahasiswa WHERE thnmasuk = '".$tahunangkatan."' AND kdprodi = '".$kdprodi."'";
					  
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			$data = $query->result();
			$ttl = count($data);
			echo json_encode(array ("success"=>true, "result"=>0, "data"=>$data));
			return;
		}		
	}
	
	function get_jmlmhsdpp(){
		$thakademik = $_POST['thakademik'];
		$kdprodi = $_POST['kdprodi'];		
			
		$q = "SELECT count(stmhssemester.nim) AS jummhsdpp
				FROM
				  stmhssemester
				INNER JOIN mahasiswa
				ON mahasiswa.nim = stmhssemester.nim AND stmhssemester.kdstsemester = '".$thakademik."'
				WHERE
				  stmhssemester.kdstsemester = '".$thakademik."'
				  AND mahasiswa.kdprodi = '".$kdprodi."'";
					  
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			$data = $query->result();
			$ttl = count($data);
			echo json_encode(array ("success"=>true, "result"=>0, "data"=>$data));
			return;
		}		
	}
	
	function get_smtskrgberjalan(){
		$smtskrgbrjln = $_POST['smtskrgbrjln'];		

		$q = "SELECT nmthnakademik FROM v_tahunakademik WHERE kdstsemester = '".$smtskrgbrjln."'";
					  
		$query = $this->db->query($q);
		if ($query->num_rows() == 1){
			$data = $query->result();
			$ttl = count($data);
			echo json_encode(array ("success"=>true, "result"=>0, "data"=>$data));
			return;
		}		
	}
	
	function get_DpkPerMhsSmt(){
		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 ='2'
		GROUP BY
		  kuitansi.kdstsemester"; 

			$this->jsonFromQuery($q);
		}
		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); 	
	} 
	
	function exportexceldppangkatan($kdprodi,$kdstsemester,$thnmasuk,$klsmhs,$persen1,$kondisi1,$persen2,$kondisi2,$isallprodi) {
		$persen1 = ($persen1!='-') ? intval($persen1):null;
		$persen2 = ($persen2!='-') ? intval($persen2):null;
		
		//descrip kondisi1
		if ($kondisi1==1) {
			$kondisi1 = '=';
		} else if ($kondisi1==2) {
			$kondisi1 = '<>';
		} else if ($kondisi1==3) {
			$kondisi1 = '>';
		} else if ($kondisi1==4) {
			$kondisi1 = '>=';
		} else if ($kondisi1==5) {
			$kondisi1 = '<';
		} else if ($kondisi1==6) {
			$kondisi1 = '<=';
		} else {
			$kondisi1 = null;
		}
		
		//descrip kondisi2
		if ($kondisi2==1) {
			$kondisi2 = '=';
		} else if ($kondisi2==2) {
			$kondisi2 = '<>';
		} else if ($kondisi2==3) {
			$kondisi2 = '>';
		} else if ($kondisi2==4) {
			$kondisi2 = '>=';
		} else if ($kondisi2==5) {
			$kondisi2 = '<';
		} else if ($kondisi2==6) {
			$kondisi2 = '<=';
		} else {
			$kondisi2 = null;
		}
		
		$data['eksport'] = $this->db->query("CALL sp_getDppDetPerTahun_excel(?,?,?,?,?,?,?,?,?)", 
							array($kdprodi,$kdstsemester,$thnmasuk,$klsmhs,$persen1,$kondisi1,$persen2,$kondisi2,$isallprodi)
						)->result();
		$data['table'] = 'excel_dppdet_angkatan';
		$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 exportascmsdppangkatan($kdprodi,$kdstsemester,$thnmasuk,$klsmhs,$isallprodi,$nobriva,$kodebiaya) {
		
		$data['eksport'] = $this->db->query("CALL sp_getDppDetPerTahun_excel_cms(?,?,?,?,?,?,?,?,?)", 
							array($kdprodi,$kdstsemester,$thnmasuk,$klsmhs,0,'>=',null,null,$isallprodi)
						)->result();
		
		$list_fields = array('No',
							'Brivano',
							'CustCode',
							'CustName',
							'Type',
							'OverwriteAddRemove',
							'Amount',
							'LastPeriode',
							'Keterangan',);
							
		$build_array = array();
		$no = 1;
		$oar = '';
		foreach ($data['eksport'] as $row) {
			if ($row->tunggakan > 0) {
				if ($row->kdstaktivitasmhs=='A') {
					$oar = 'O';
				} else {
					$oar = 'R';
				}
				array_push($build_array,array(
						'no'=>$no,
						'nobriva'=>$nobriva,
						'nim'=>substr($row->nim,4,10).$kodebiaya,
						'nmmhs'=>$row->nmmhs,
						'type'=>'K',
						'oar'=>$oar,
						'tunggakan'=>$row->tunggakan,
						'lastperiode'=>'',
						'keterangan'=>'DPP'.substr($kdstsemester,0,4).'/'.substr($kdstsemester,-1),
				));
			$no++;
			}
		}
		
		
		$data['fieldname'] = $list_fields;
		$data['eksport'] = $build_array;
		$data['table'] = "excel_cmsdpp_angkatan";
		$this->load->view('exportexcel', $data); 		
	}
	
	function exportascmsdpp($kdprodi,$kdstsemester,$klsmhs,$isallprodi,$nobriva,$kodebiaya) {
		
		$data['eksport'] = $this->db->query("CALL sp_getDppDet_excel_cms(?,?,?,?,?,?,?,?)", 
							array($kdprodi,$kdstsemester,$klsmhs,0,'>=',null,null,$isallprodi)
						)->result();
		
		$list_fields = array('No',
							'Brivano',
							'CustCode',
							'CustName',
							'Type',
							'OverwriteAddRemove',
							'Amount',
							'LastPeriode',
							'Keterangan',);
							
		$build_array = array();
		$no = 1;
		$oar = '';
		foreach ($data['eksport'] as $row) {
			if ($row->tunggakan > 0) {
				if ($row->kdstaktivitasmhs=='A') {
					$oar = 'O';
				} else {
					$oar = 'R';
				}
				array_push($build_array,array(
						'no'=>$no,
						'nobriva'=>$nobriva,
						'nim'=>substr($row->nim,4,10).$kodebiaya,
						'nmmhs'=>$row->nmmhs,
						'type'=>'K',
						'oar'=>$oar,
						'tunggakan'=>$row->tunggakan,
						'lastperiode'=>'',
						'keterangan'=>'DPP'.substr($kdstsemester,0,4).'/'.substr($kdstsemester,-1),
				));
			$no++;
			}
		}
		
		
		$data['fieldname'] = $list_fields;
		$data['eksport'] = $build_array;
		$data['table'] = "excel_cmsdpp";
		$this->load->view('exportexcel', $data); 		
	}
	
	function exportascmsdpk($thmasuk, $idklsmhs, $kdprodi, $isallprodi,$nobriva,$kodebiaya) {
		$data['eksport'] = $this->db->query("CALL sp_getDpkDet_excel_cms(?,?,?,?,?,?,?,?)", 
							array($thmasuk, $idklsmhs, $kdprodi, 0, '>=', null, null, $isallprodi)
						)->result();
		
		$list_fields = array('No',
							'Brivano',
							'CustCode',
							'CustName',
							'Type',
							'OverwriteAddRemove',
							'Amount',
							'LastPeriode',
							'Keterangan',);
							
		$build_array = array();
		$no = 1;
		$oar = '';
		foreach ($data['eksport'] as $row) {
			if ($row->tunggakan > 0) {
				if ($row->kdstaktivitasmhs=='A') {
					$oar = 'O';
				} else {
					$oar = 'R';
				}
				array_push($build_array,array(
						'no'=>$no,
						'nobriva'=>$nobriva,
						'nim'=>substr($row->nim,4,10).$kodebiaya,
						'nmmhs'=>$row->nmmhs,
						'type'=>'K',
						'oar'=>$oar,
						'tunggakan'=>$row->tunggakan,
						'lastperiode'=>'',
						'keterangan'=>'DPK'.substr($row->kdstsemester,0,4).'/'.substr($row->kdstsemester,-1),
				));
			$no++;
			}
		}
		
		
		$data['fieldname'] = $list_fields;
		$data['eksport'] = $build_array;
		$data['table'] = "excel_cmsdpk";
		$this->load->view('exportexcel', $data); 		
	}
	
	function get_keuwisuda(){
		$kdfakultas = $_POST['kdfakultas'];
		$kdprodi = $_POST['kdprodi'];
		
		$tgl1 = $_POST['tglawal'];
		$tgl2 = $_POST['tglakhir'];
		
		$fields = $_POST['fields'];
		$query = $_POST['query'];
		
		$this->db->select("*");
		$this->db->from("v_kuitansiwisuda"); 
		
		if($kdfakultas!='99'){
			$this->db->where('kdfakultas', $kdfakultas);
		}
		
		if($kdprodi!='9999'){
			$this->db->where('kdprodi', $kdprodi);
		}
		
		$this->db->where("date(tglkuiwisudaori) between '". $tgl1 ."' and '". $tgl2."'" );
		$this->db->where('idstkuitansi', 1);
		
		$likes = "";
        if($fields!="" || $query !=""){
            $k=array('[',']','"');
            $r=str_replace($k, '', $fields);
            $b=explode(',', $r);
            $c=count($b);
            for($i=0;$i<$c;$i++){
                $d[$b[$i]]=$query;
				if ($i==0) {
					$likes .= "(".$b[$i]." LIKE '%".$query."%'";
				} else {
					$likes .= " OR ".$b[$i]." LIKE '%".$query."%'";
				}
            }
			$likes .= ")";
			$this->db->where($likes);
        }
		
		$q = $this->db->get(); 
		
		$data = array();
		if ($q->num_rows() > 0) {
			$data = $q->result();
			echo json_encode(array("success"=>true,"results"=>count($data),"data"=>$data,"msg"=>"data found"));
		} else {
			echo json_encode(array("success"=>true,"results"=>0,"data"=>array(),"msg"=>"no data"));
		}

		
	}
	
	function exportkeuwisuda($kdfakultas,$kdprodi,$tgl1,$tgl2) {
		
		$this->db->select("nmfakultas,
						nmprodi,
						nokuiwisuda,
						tglkuiwisuda,
						nim,
						nmmhs,
						nmsetbiayawisuda,
						nmcarabyr,
						bayar,
						usernameinput");
						
		$this->db->from("v_kuitansiwisuda"); 
		
		if($kdfakultas!='99'){
			$this->db->where('kdfakultas', $kdfakultas);
		}
		
		if($kdprodi!='9999'){
			$this->db->where('kdprodi', $kdprodi);
		}
		
		$this->db->where("date(tglkuiwisudaori) between '". $tgl1 ."' and '". $tgl2."'" );
		$this->db->where('idstkuitansi', 1);
		
		$q = $this->db->get(); 
		$data = array();
		if ($q->num_rows() > 0) {
			$data = $q->result();
		}

		$list_fields = array('Fakultas',
							'Program',
							'No. Kuitansi',
							'Tgl. Kuitansi',
							'NPM',
							'Nama Mahasiswa',
							'Pembayaran',
							'Cara Bayar',
							'Nominal',
							'User Input');

		$data['eksport'] = $data;
		$data['fieldname'] = $list_fields;
		$data['table'] = "excel_kuiwisuda";
		$this->load->view('exportexcel', $data); 		
	}
	//=================================//
}