<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Import_Dpp_Controller extends Controller {
	var $stat;
	
	public function __construct()
    {
        parent::Controller();
        $this->load->library('session');
		$this->load->library('rhlib');
    }
	
	function retValOrNull($val){
		//especially for combo & looukup with no item selected
		$val = ($val=='')? null : $val;
		return $val;
	}
	
	function get_importvabank(){
		$q = "SELECT importvabank.*, pengguna.nmlengkap FROM importvabank 
		LEFT JOIN pengguna ON pengguna.userid = importvabank.userid
		ORDER BY idva";
				
		$this->rhlib->jsonFromQuery($q);
	}
	
	function upload_data(){
		$ori_dir = 'resources/files/';
		if ($_FILES['f_excel']['name']) {
			move_uploaded_file($_FILES['f_excel']['tmp_name'], $ori_dir . $_FILES['f_excel']['name']);	
			
			$result["success"]=true;
			echo json_encode($result);
		} 
	}
	
	function preview_data(){
	
		include_once ( APPPATH."libraries/excel_reader2.php");	

		$file = $this->input->post("excel");
		
		$data = array();
	
	
			$data = new Spreadsheet_Excel_Reader('resources/files/'.$file);
			$baris = $data->rowcount($sheet_index=0);
	
				$build_array = array ("success"=>true,"results"=>$baris-1,"data"=>array());

		
				for ($i=2; $i<=$baris; $i++) {

					$kdstsemester = str_replace('/','',substr($data->val($i, 5),3,6));
					$nmthnakademik = $this->rhlib->get_par_val("v_tahunakademik","nmthnakademik","kdstsemester = '$kdstsemester'");
					
					$kodebank = substr($data->val($i, 3),15,3);
					$row_biaya = $this->rhlib->get_row_pars("jbiaya","idjnsbiaya,kdjnsbiaya","kodebank = '$kodebank'");
					
					$idjnsbiaya = $row_biaya->idjnsbiaya;
					$kdjnsbiaya = $row_biaya->kdjnsbiaya;
					
					$nim = '4115'.substr($data->val($i, 3),5,10);
					$nmmhs = $this->rhlib->get_par_val("mahasiswa","nmmhs","nim = '$nim'");
					array_push($build_array["data"],array(
						'nokuitansi'=>'<i>Auto Generate</i>',
						'tglkuitansi'=>substr($data->val($i, 2),0,10),
						'jamkuitansi'=>substr($data->val($i, 2),11,8),
						'noref'=>substr($data->val($i, 3),0,18),
						'idjnsbiaya'=>$idjnsbiaya,
						'kdjnsbiaya'=>$kdjnsbiaya,
						'kdstsemester'=>$kdstsemester,
						'nmthnakademik'=>$nmthnakademik,
						'nim'=>$nim,
                        'nmmhs'=>$nmmhs,
                        'jmlbayar'=>str_replace(',','',substr($data->val($i, 6), 0, strlen($data->val($i, 6)) - 3)),
                        'useridinput'=>'-',
                        'idstkuitansi'=>'1',
						'nmstkuitansi'=>$this->rhlib->get_par_val("stkuitansi","nmstkuitansi","idstkuitansi = '1'")
                         ));

				}
				
				
				echo json_encode($build_array);

			unlink('resources/files/'.$file);

	}
	
	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=1;
        }
        return $max;
    } 

	function getotokui($kdjnsbiaya){
        $q = "select getOtoKui('".$kdjnsbiaya."') as newkui";
        $query  = $this->db->query($q);
        $newkui = ''; 
                    
        if ($query->num_rows() != 0)
        {
            $row = $query->row();
            $newkui=$row->newkui;
        }

        return $newkui;
    }	
	
	function import_transfer(){
	
		$idvax=$this->autoNumber('idva','importvabank');
	
		$dataArray = array(
			'idva'=> $idvax,
			'tglva'=> $_POST['tglva'],
			'jamva'=> $_POST['jamva'],
			'keterangan'=> $this->retValOrNull($_POST['keterangan']),
			'nmfile'=> $_POST['nmfile'],
			'userid'=> $_POST['userid'],
		);

		$ret = $this->rhlib->insertRecord('importvabank',$dataArray);
		
		if ($ret) {
			$result = $this->import_transfer_detail($idvax);
		} else {
			$result["success"]=false;
            $result["msg"]= 'Simpan Data Gagal';
		}
		
		echo json_encode($result);

    }
	
	function import_transfer_detail($idheader){
	
		$record=$this->input->post("record_par");
		
		$success = 0;
		$fail = 0;
		
		    $rows = explode(";",$record);
            $row_count = count($rows);
			
			
			
            for($ri=0;$ri<$row_count;$ri++){
                $rows2 = explode("x",$rows[$ri]);
				
				$temptgl    = explode('/',substr($rows2[0],0,10));
				$tglkuitansi  = $temptgl[2]."-".$temptgl[1]."-".$temptgl[0];

					$dataArray = array(
						'nokuitansi'=> $this->getotokui($rows2[4]),
						'tglkuitansi'=> $tglkuitansi,
						'jamkuitansi'=> $rows2[1],
						'idcarabyr'=> '2',
						'noref'=> $rows2[2],
						'idjnsbiaya'=> $rows2[3],
						'kdstsemester'=> $rows2[5],
						'nim'=> $rows2[6],
						'jmlbayar'=> $rows2[7],
						'catatan'=> $rows2[8],
						'tglinput'=> date('Y-m-d'),
						'useridinput'=> $rows2[9],
						'idstkuitansi'=> $rows2[10],
						'idva'=> $idheader,
					);
					
                    $this->db->insert('kuitansi', $dataArray);
					
					if($this->db->affected_rows()){
						$success=$success+1;
					}else{
						$fail=$fail+1;
					}
					

             }
		
		$return['sucessed']=$success;
        $return['failed']=$fail;
		
        return $return;

    }
	
	function get_imported(){
		$q = "select k.* , j.kdjnsbiaya, v.nmthnakademik, s.nmstkuitansi, m.nmmhs from kuitansi k
		  left join mahasiswa m on k.nim = m.nim
		  left join jbiaya j on k.idjnsbiaya = j.idjnsbiaya
		  left join v_tahunakademik v ON k.kdstsemester = v.kdstsemester
		  left join stkuitansi s ON k.idstkuitansi = s.idstkuitansi
		  WHERE k.idva ='".$_POST['idva']."'";
				
		$this->rhlib->jsonFromQuery($q);
	}

	function update_transfer(){ 	
			
		$dataArray = array(
			'keterangan'=> $_POST['keterangan'],
		);
		
		//UPDATE
		$this->db->where('idva', $_POST['idva']);
		$this->db->update('importvabank', $dataArray); 
		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_va(){     
		$where['idva'] = $_POST['idvax'];
		$ret = $this->rhlib->deleteRecord('importvabank',$where);	
        return $ret;
    }				
	
	
}
?>