<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Tbpro_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_tbpro(){
		$q = "SELECT * FROM tbpro ORDER BY kdprotbpro,kdkabtbpro";
				
		$this->rhlib->jsonFromQueryWLimit($q);
	}
		
	function insert_tbpro(){
		$dataArray = $this->getFieldsAndValues();
		$ret['tbpropriv'] = $this->rhlib->insertRecord('tbpro',$dataArray);
		$ret['tbpropub'] = $this->rhlib->insertRecordPublic('tbpro',$dataArray);
		
		if ($ret['tbpropriv'] && $ret['tbpropub']) {
			$ret["success"]=true;
		} else {
			$ret["success"]=false;
		}
        echo json_encode($ret);
    }

	function update_tbpro(){ 				
		$fieldsAndValues = $this->getFieldsAndValues();
		
		//UPDATE
		$where['kdprotbpro'] = $_POST['kdprotbpro'];
		$where['kdkabtbpro'] = $_POST['kdkabtbpro'];
		$this->db->where('kdprotbpro', $_POST['kdprotbpro']);
		$this->db->where('kdkabtbpro', $_POST['kdkabtbpro']);
		$this->db->update('tbpro', $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('tbpro',$fieldsAndValues,$where);
        }
		echo json_encode($ret);
    }
	
	function delete_tbpro(){     
		$where['kdprotbpro'] = $_POST['kdprotbpro'];
		$where['kdkabtbpro'] = $_POST['kdkabtbpro'];
		$ret = $this->rhlib->deleteRecord('tbpro',$where);
		$this->rhlib->deleteRecordPublic('tbpro',$where);
        return $ret;
    }
			
	function getFieldsAndValues(){
		$dataArray = array(
             'kdprotbpro'=> $this->retValOrNull($_POST['kdprotbpro']),
			 'nmprotbpro'=> $this->retValOrNull($_POST['nmprotbpro']), 
			 'kdkabtbpro'=> $this->retValOrNull($_POST['kdkabtbpro']),
			 'nmkabtbpro'=> $this->retValOrNull($_POST['nmkabtbpro']),
			 'luasstbpro'=> $this->retValOrNull($_POST['luasstbpro']),
			 'jmduktbpro'=> $this->retValOrNull($_POST['jmduktbpro']),
			 'jmdpttbpro'=> $this->retValOrNull($_POST['jmdpttbpro'])
        );		
		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_pro']['name']) {
			$upload = move_uploaded_file($_FILES['f_excel_import_pro']['tmp_name'], $ori_dir . $_FILES['f_excel_import_pro']['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)) =='kdprotbpro' && strtolower($data->val(1, 3)) =='kdkabtbpro') {
					
					for ($i=2; $i<=$baris; $i++) {

						$dataArray = array(
							 'kdprotbpro'=> $this->retValOrNull($data->val($i, 1)),
							 'nmprotbpro'=> $this->retValOrNull($data->val($i, 2)), 
							 'kdkabtbpro'=> $this->retValOrNull($data->val($i, 3)),
							 'nmkabtbpro'=> $this->retValOrNull($data->val($i, 4)),
							 'luasstbpro'=> $this->retValOrNull($data->val($i, 5)),
							 'jmduktbpro'=> $this->retValOrNull($data->val($i, 6)),
							 'jmdpttbpro'=> $this->retValOrNull($data->val($i, 7))
						);	
						
						$where['kdprotbpro'] = $this->retValOrNull($data->val($i, 1));
						$where['kdkabtbpro'] = $this->retValOrNull($data->val($i, 3));
						
						//private
						$countdatapriv = $this->getcountdata($where,'tbpro');
						if ($countdatapriv > 0) {
							$this->update_import($dataArray,$where,'tbpro');
						} else {
							$privinsert = $this->rhlib->insertRecord('tbpro',$dataArray);
						}
						
						//public
						$countdatapub = $this->getcountdatapublic($where,'tbpro');
						if ($countdatapub > 0) {
							$this->rhlib->updateRecordPublic('tbpro',$dataArray,$where);
						} else {
							$pubinsert = $this->rhlib->insertRecordPublic('tbpro',$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);
		}
	}
}
?>