<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Setbiaya_Controller extends Controller {

    var $stat;

    public function __construct() {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
    }

    var $_jsVars = array();

    public function setJsVar($name, $value) {
        $this->_jsVars[$name] = $value;
    }    

    function autoNumber($column, $tbl) {
        $q = "SELECT max(" . $column . ")+1 as max FROM " . $tbl . "";
        $query = $this->db->query($q);
        $max = '';

        if ($query->num_rows() != 0) {
            $row = $query->row();
            $max = $row->max;
        }
        if ($max == null) {
            $max = 0;
        }
        return $max;
    }

    function get_data() {
        $kdprodi = $_POST['kdprodi'];
       
        $q = "SELECT * FROM v_setbiaya where kdprodi='$kdprodi'"; //$var";
        
        $this->rhlib->jsonFromQueryWLimit($q);
    }
	
	function get_biayatahap() {
		$q = "SELECT * FROM v_setbiaya WHERE idsetbiaya='".$_POST['idsetbiaya']."'";
		$this->rhlib->jsonFromQueryWLimit($q);
    }
	
	function get_count_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['result']=$row->jml;
        } else {
			$data['result']=0;
		}
        echo json_encode($data);
	}

    function insert_setbiaya() {
        $dataArray = $this->getFieldsAndValues();
		$ret = $this->rhlib->insertRecord('setbiaya',$dataArray);
        if ($ret) {
			$return["idsetbiaya"]=$dataArray['idsetbiaya'];
			echo json_encode($return);
		}
    }

    function update_setbiaya() {
        $filedAndVal = array(
            'idjnsbiaya' => $_POST['idjnsbiaya'],
            'tahunmsk' => $_POST['tahunmsk'],
			'kdstsemester' => $this->retValOrNull($_POST['kdstsemester']),
            'kdprodi' => $_POST['kdprodi'],
            'idjnskls' => $_POST['idjnskls'],
            'biaya' => $_POST['biaya'],
            'idstatus' => $_POST['idstatus'],
            'catatan' => $_POST['catatan']
        );

        //UPDATE
        $this->db->where('idsetbiaya', $_POST['idsetbiaya']);
        $this->db->update('setbiaya', $filedAndVal);
        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_setbiaya(){       //ISTRA
        $where['idsetbiaya']=$_POST['idsetbiaya'];
		$ret = $this->rhlib->deleteRecord('setbiaya',$where);
        return $ret;
    }
    
    function retValOrNull($val) {
        //especially for combo & looukup with no item selected
        $val = ($val == '') ? null : $val;
        return $val;
    }

    function getFieldsAndValues() {
        $data = array(
            'idsetbiaya' => $this->autoNumber('idsetbiaya', 'setbiaya'),
            'idjnsbiaya' => $_POST['idjnsbiaya'],
            'tahunmsk' => $_POST['tahunmsk'],
			'kdstsemester' => $this->retValOrNull($_POST['kdstsemester']),
            'kdprodi' => $_POST['kdprodi'],
            'idjnskls' => $_POST['idjnskls'],
            'biaya' => $_POST['biaya'],
            'idstatus' => $_POST['idstatus'],
            'catatan' => $_POST['catatan']
        );
        return $data;
    }
	
	//=============================BIAYA TAHAP======================================
	
	function get_sttahapan_lookup(){
		$q = "SELECT * FROM sttahapan WHERE idsttahapan NOT IN 
		(SELECT idsttahapan FROM setbiayatahapan WHERE idsetbiaya='".$_POST['idsetbiaya']."') ORDER BY kdsttahapan";
				
		$this->rhlib->jsonFromQueryWLimit($q);
	}
	
	function insert_biaya_tahap(){
		$dataArray = $this->getFieldsAndValuesTahap();
		$ret = $this->rhlib->insertRecord('setbiayatahapan',$dataArray);
        return $ret;
    }
	
	function update_biaya_tahap() {
		$field = $_POST['field'];
		$value = $_POST['value'];
		
        //UPDATE
        $this->db->where('idsttahapan', $_POST['idsttahapan']);
		$this->db->where('idsetbiaya', $_POST['idsetbiaya']);
		$this->db->set($field, $value);
        $this->db->update('setbiayatahapan');
        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_biaya_tahap(){       //ISTRA
        $where['idsttahapan']=$_POST['idsttahapan'];
		$ret = $this->rhlib->deleteRecord('setbiayatahapan',$where);
        return $ret;
    }
	
	function getFieldsAndValuesTahap(){
		$dataArray = array(
             'idsetbiaya'=> $_POST['idsetbiaya'],
			 'idsttahapan'=> $_POST['idsttahapan']
        );		
		return $dataArray;
	}
	
	function getsum_tahapnot() {
        $q = "SELECT ifnull(sum(nominal),0) AS nominal FROM setbiayatahapan 
			WHERE idsttahapan <>'".$_POST['idsttahapan']."' AND idsetbiaya='".$_POST['idsetbiaya']."'";
        $query = $this->db->query($q);

        if ($query->num_rows() != 0) {
            $row = $query->row();
            echo $row->nominal;
        }

    }
	
	function jsonFromQuery($q) {
		$this->rhlib->jsonFromQuery($q);
	}
	
	function get_jbiayadppdpk(){
		$q = "SELECT idjnsbiaya,kdjnsbiaya,nmjnsbiaya,deskripsi
			FROM jbiaya WHERE idjnsbiaya IN(1,2)";
		$this->jsonFromQuery($q);
	}
	
	function get_tagihan_kuliah(){
		$kdprodi = $_POST['kdprodi'];
		$kdstsemester = $_POST['kdstsemester'];
		$idjnsbiaya = $_POST['idjnsbiaya'];

		if ($idjnsbiaya=='1') {
			$this->rhlib->jsonFromQuery("SELECT s.nim AS nim
				 , m.nmmhs AS nmmhs
				 , m.novadpp AS nova
				 , p.nmprodi AS nmprodi
				 , ifnull(sb.biaya, 0) AS biaya
				 , ifnull(d.diskon, 0) AS diskon
				 , cast((ifnull(sb.biaya, 0) - ifnull(d.diskon, 0)) * ifnull(st.persentase, 0) / 100 AS UNSIGNED) AS nominal
			FROM
			  stmhssemester s
			LEFT JOIN mahasiswa m
			ON s.nim = m.nim
			LEFT JOIN prodi p
			ON m.kdprodi = p.kdprodi
			LEFT JOIN klsmhs k
			ON m.idklsmhs = k.idklsmhs
			LEFT JOIN setbiaya sb
			ON m.kdprodi = sb.kdprodi
			AND m.thnmasuk = sb.tahunmsk
			AND sb.idjnskls = k.idjnskls
			AND sb.kdstsemester = s.kdstsemester
			AND sb.idjnsbiaya = 1
			AND sb.idstatus = 1
			LEFT JOIN setbiayatahapan st
			ON st.idjnsbiaya = sb.idjnsbiaya
			AND st.kdstsemester = s.kdstsemester
			AND st.idsttahapan = 1
			LEFT JOIN diskonmhs d
			ON sb.idsetbiaya = d.idsetbiaya AND s.nim = d.nim AND s.kdstsemester = d.kdstsemester
			WHERE
			  s.kdstsemester = '$kdstsemester'
			  AND m.kdprodi = '$kdprodi'
			GROUP BY
			  s.nim
			, m.thnmasuk");
		} else if ($idjnsbiaya=='2') {
			$this->rhlib->jsonFromQuery("SELECT s.nim AS nim
				 , m.nmmhs AS nmmhs
				 , m.novadpk AS nova
				 , p.nmprodi AS nmprodi
				 , ifnull(sb.biaya, 0) AS biaya
				 , ifnull(d.diskon, 0) AS diskon
				 , ifnull(st.nominal, 0) AS nominal
			FROM
			  stmhssemester s
			LEFT JOIN mahasiswa m
			ON s.nim = m.nim
			LEFT JOIN prodi p
			ON m.kdprodi = p.kdprodi
			LEFT JOIN klsmhs k
			ON m.idklsmhs = k.idklsmhs
			LEFT JOIN setbiaya sb
			ON m.kdprodi = sb.kdprodi
			AND m.thnmasuk = sb.tahunmsk
			AND sb.idjnskls = k.idjnskls
			AND sb.idjnsbiaya = 2
			AND sb.idstatus = 1
			LEFT JOIN setbiayatahapan st
			ON st.idjnsbiaya = sb.idjnsbiaya
			AND s.idsemester = st.idsttahapan
			AND st.thnmasuk = m.thnmasuk
			LEFT JOIN diskonmhs d
			ON sb.idsetbiaya = d.idsetbiaya AND s.nim = d.nim AND s.kdstsemester = d.kdstsemester
			WHERE
			  s.kdstsemester = '$kdstsemester'
			  AND m.kdprodi = '$kdprodi'
			GROUP BY
			  s.nim");
		}
		
	}
	
	function export_excel_tagihan($kdprodi,$kdstsemester,$idjnsbiaya,$kdjnsbiaya) {

		if ($idjnsbiaya=='1') {
			$q = "SELECT s.nim AS nim
				 , m.nmmhs AS nmmhs
				 , m.novadpp AS nova
				 , p.nmprodi AS nmprodi
				 -- , ifnull(sb.biaya, 0) AS biaya
				 -- , ifnull(d.diskon, 0) AS diskon
				 , cast((ifnull(sb.biaya, 0) - ifnull(d.diskon, 0)) * ifnull(st.persentase, 0) / 100 AS UNSIGNED) AS nominal
			FROM
			  stmhssemester s
			LEFT JOIN mahasiswa m
			ON s.nim = m.nim
			LEFT JOIN prodi p
			ON m.kdprodi = p.kdprodi
			LEFT JOIN klsmhs k
			ON m.idklsmhs = k.idklsmhs
			LEFT JOIN setbiaya sb
			ON m.kdprodi = sb.kdprodi
			AND m.thnmasuk = sb.tahunmsk
			AND sb.idjnskls = k.idjnskls
			AND sb.kdstsemester = s.kdstsemester
			AND sb.idjnsbiaya = 1
			AND sb.idstatus = 1
			LEFT JOIN setbiayatahapan st
			ON st.idjnsbiaya = sb.idjnsbiaya
			AND st.kdstsemester = s.kdstsemester
			AND st.idsttahapan = 1
			LEFT JOIN diskonmhs d
			ON sb.idsetbiaya = d.idsetbiaya AND s.nim = d.nim AND s.kdstsemester = d.kdstsemester
			WHERE
			  s.kdstsemester = '$kdstsemester'
			  AND m.kdprodi = '$kdprodi'
			GROUP BY
			  s.nim
			, m.thnmasuk";
		} else if ($idjnsbiaya=='2') {
			$q = "SELECT s.nim AS nim
				 , m.nmmhs AS nmmhs
				 , m.novadpk AS nova
				 , p.nmprodi AS nmprodi
				 -- , ifnull(sb.biaya, 0) AS biaya
				 -- , ifnull(d.diskon, 0) AS diskon
				 , ifnull(st.nominal, 0) AS nominal
			FROM
			  stmhssemester s
			LEFT JOIN mahasiswa m
			ON s.nim = m.nim
			LEFT JOIN prodi p
			ON m.kdprodi = p.kdprodi
			LEFT JOIN klsmhs k
			ON m.idklsmhs = k.idklsmhs
			LEFT JOIN setbiaya sb
			ON m.kdprodi = sb.kdprodi
			AND m.thnmasuk = sb.tahunmsk
			AND sb.idjnskls = k.idjnskls
			AND sb.idjnsbiaya = 2
			AND sb.idstatus = 1
			LEFT JOIN setbiayatahapan st
			ON st.idjnsbiaya = sb.idjnsbiaya
			AND s.idsemester = st.idsttahapan
			AND st.thnmasuk = m.thnmasuk
			LEFT JOIN diskonmhs d
			ON sb.idsetbiaya = d.idsetbiaya AND s.nim = d.nim AND s.kdstsemester = d.kdstsemester
			WHERE
			  s.kdstsemester = '$kdstsemester'
			  AND m.kdprodi = '$kdprodi'
			GROUP BY
			  s.nim";
		}
		
		$listfield = array('NPM','Nama Mahasiswa','Program Studi','No. Virtual Account','Nominal');
		
		$data['eksport'] = $this->db->query($q)->result();
		$data['table'] = 'TAGIHAN-'.$kdprodi.'-'.$kdstsemester.'-'.$kdjnsbiaya;
		$data['fieldname'] = $listfield;
		$this->load->view('exportexcel', $data); 	
	} 

}