<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Tbkod_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_tbkod(){
		$q = "SELECT * FROM tbkod ORDER BY kdapltbkod";
				
		$this->rhlib->jsonFromQueryWLimit($q);
	}
		
	function insert_tbkod(){
		$dataArray = $this->getFieldsAndValues();
		$ret['tbkodpriv'] = $this->rhlib->insertRecord('tbkod',$dataArray);
		$ret['tbkodpub'] = $this->rhlib->insertRecordPublic('tbkod',$dataArray);
        
		if ($ret['tbkodpriv'] && $ret['tbkodpub']) {
			$ret["success"]=true;
		} else {
			$ret["success"]=false;
		}
        echo json_encode($ret);
    }

	function update_tbkod(){ 				
		$fieldsAndValues = $this->getFieldsAndValues();
		
		//UPDATE
		$where['kdapltbkod'] = $_POST['kdapltbkod'];
		$where['kdkodtbkod'] = $_POST['kdkodtbkod'];
		$this->db->where('kdapltbkod', $_POST['kdapltbkod']);
		$this->db->where('kdkodtbkod', $_POST['kdkodtbkod']);
		$this->db->update('tbkod', $fieldsAndValues); 
		
		$ret["success"]=true;	
		
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
			$ret["num"]=$this->db->affected_rows();
			$this->rhlib->updateRecordPublic('tbkod',$fieldsAndValues,$where);
        }
        echo json_encode($ret);
    }
	
	function delete_tbkod(){     
		$where['kdapltbkod'] = $_POST['kdapltbkod'];
		$where['kdkodtbkod'] = $_POST['kdkodtbkod'];
		$ret = $this->rhlib->deleteRecord('tbkod',$where);
		$this->rhlib->deleteRecordPublic('tbkod',$where);
        return $ret;
    }
			
	function getFieldsAndValues(){
		$dataArray = array(
             'kdapltbkod'=> $_POST['kdapltbkod'],
			 'ketertbkod'=> $_POST['ketertbkod'], 
			 'kdkodtbkod'=> $_POST['kdkodtbkod'],
			 'nmkodtbkod'=> $_POST['nmkodtbkod'],
			 'nmdata'=> $_POST['nmdata']
        );		
		return $dataArray;
	}
	
	//import data excel
	function getcountdata($where,$tbl){
	
        $this->db->select("*");
        $this->db->from($tbl); 
		$this->db->where($where);
		
		$query = $this->db->get(); 
		
        return $query->num_rows();
    }

	function getcountdatapublic($where,$tbl){
		$KEDUA= $this->load->database('second', TRUE);
        $KEDUA->select("*");
        $KEDUA->from($tbl); 
		$KEDUA->where($where);
		
		$query = $KEDUA->get(); 
		
        return $query->num_rows();
    }

	function update_import($data,$where,$tbl){ 	
	
		$this->db->where($where);
		$this->db->update($tbl, $data); 
		if($this->db->affected_rows()){
            $ret["success"]=true;
            $ret["msg"]='Update Data Berhasil';
			$ret["num"]=$this->db->affected_rows();
        }else{
            $ret["success"]=false;
            $ret["msg"]= 'Update Data Gagal';
        }
		return $ret;
    }	
	
	function import_from_Excel(){

		$ori_dir = 'resources/files/';
		$upload;
		
		if ($_FILES['f_excel_import_kod']['name']) {
			$upload = move_uploaded_file($_FILES['f_excel_import_kod']['tmp_name'], $ori_dir . $_FILES['f_excel_import_kod']['name']);	
		} 

		if ($upload) {
		
			include_once ( APPPATH."libraries/excel_reader2.php");	

			$file = $this->input->post("f_excel");
		
			$data = new Spreadsheet_Excel_Reader('resources/files/'.$file);
			$baris = $data->rowcount($sheet_index=0);
			
			$countinsert = 0;
			$countupdate = 0;
			
				if (strtolower($data->val(1, 1)) =='kdapltbkod' && strtolower($data->val(1, 3)) =='kdkodtbkod') {
					
					for ($i=2; $i<=$baris; $i++) {

						$dataArray = array(
							 'kdapltbkod'=> $this->retValOrNull($data->val($i, 1)),
							 'ketertbkod'=> $this->retValOrNull($data->val($i, 2)), 
							 'kdkodtbkod'=> $this->retValOrNull($data->val($i, 3)),
							 'nmkodtbkod'=> $this->retValOrNull($data->val($i, 4)),
							 'nmdata'=> $this->retValOrNull($data->val($i, 5))
						);	
						
						$where['kdapltbkod'] = $this->retValOrNull($data->val($i, 1));
						$where['kdkodtbkod'] = $this->retValOrNull($data->val($i, 3));
						
						//private
						$countdatapriv = $this->getcountdata($where,'tbkod');
						if ($countdatapriv > 0) {
							$this->update_import($dataArray,$where,'tbkod');
						} else {
							$privinsert = $this->rhlib->insertRecord('tbkod',$dataArray);
						}
						
						//public
						$countdatapub = $this->getcountdatapublic($where,'tbkod');
						if ($countdatapub > 0) {
							$this->rhlib->updateRecordPublic('tbkod',$dataArray,$where);
						} else {
							$pubinsert = $this->rhlib->insertRecordPublic('tbkod',$dataArray);
						}
						
						if ($countdatapriv > 0 && $countdatapub > 0) {
							$countupdate = $countupdate + 1;
						}
						
						if ($privinsert && $pubinsert) {
							$countinsert = $countinsert + 1;
						}

					}
					
					$return["success"]=true;
					$return["countinsert"]=$countinsert;
					$return["countupdate"]=$countupdate;

					unlink('resources/files/'.$file);
				} else {
					$return["success"]=false;
					$return["countinsert"]=0;
					$return["countupdate"]=0;
				}
			
			echo json_encode($return);
		}
	}
}
?>