<?php
class M_expense_cashbon extends CI_Model {
    
	public $table = "at_expensecashbon";
	public $detail = "at_ebondetail";
	public $journal = "at_ebonjournal";
	public $department = "am_department";
	public $employee = "am_employee";
	public $account = "at_compaccountgl";
	
	function count_cashbon_number($bulan, $cek_tahun, $BranchCode){
	    $this->db->select('*');
		$this->db->from('at_expensecashbon');
		$this->db->where('MONTH(EBonDate)', $bulan);
		$this->db->where('YEAR(EBonDate)', $cek_tahun);
		$this->db->where('BranchCode', $BranchCode);
		return $this->db->get()->num_rows();
	}
	
	function get_last_cashbon_number($BranchCode){
		$qty="SELECT MAX(EBonNumber) as EBonNumber FROM at_expensecashbon WHERE BranchCode='$BranchCode'";
		return $this->db->query($qty)->result();
	}
	
	function get_department($CompanyCode){
		$this->db->select('*');
		$this->db->from($this->department);
		$this->db->where('CompanyCode', $CompanyCode);
		$query = $this->db->get();
		
		$result = $query->result();
		return json_encode($result);
	}
	
	function get_department_edit($replace_code, $var_Company_code){
		$this->db->select('*');
		$this->db->from($this->department);
		$this->db->where('CompanyCode', $replace_code);
		$this->db->where('DepartmentCode', $var_Company_code);
		$query = $this->db->get();
		
		$result = $query->result();
		return json_encode($result);
	}
	
	function get_employee($depCode){
	    $branch = $this->session->userdata('branch');
		$this->db->select('*');
		$this->db->from($this->employee);
		$this->db->where('DepartmentCode', $depCode);
		$this->db->where('BranchCode', $branch);
		$this->db->order_by('EmployeeCode', 'ASC');
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_employee_edit($depCode, $branch_replace){
	    $branch = $this->session->userdata('branch');
		$this->db->select('*');
		$this->db->from($this->employee);
		$this->db->where('DepartmentCode ', $depCode);
		$this->db->where('BranchCode ', $branch_replace);
		$this->db->where('ResignDate ', NULL);
		$this->db->order_by('EmployeeCode', 'ASC');
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_akun($CompanyCode){
	    $jobtitle = $this->session->userdata("jobtitle");
		$BranchCode 	= $this->session->userdata('branch');
		$BranchStatus 	= $this->session->userdata('branch_status');
		
		$this->db->select('*');
		$this->db->from($this->account);
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');		
		$this->db->join('am_branch','at_compaccountgl.BranchCode=am_branch.BranchCode');		
		
		$this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
		$this->db->where('at_compaccountgl.BranchCode', $BranchCode);		
		$n = array(5,7);
		$this->db->where_in('am_accountgl.AccountSubTypeID', $n);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.TransStatus', 2);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	/*function gat_akun_cashbon($CompanyCode){
		$this->db->select('*');
		$this->db->from('am_accountgl');
		$this->db->where('AccountNumber', '1-3-12-000-00');
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}*/
	
	/*Added by Hasan on 2015/03/23*/
	function get_akun_cashbon($company){
		
		$this->db->select('*');
		$this->db->from('sys_expensecategory');
		//$this->db->join('sys_expenseaccount','sys_expenseaccount.ExpCategoryID=sys_expensecategory.ExpCategoryID','INNER');
		//$this->db->join('at_compaccountgl','at_compaccountgl.AccountNumber=sys_expenseaccount.AccountNumber','INNER');
		$this->db->where('sys_expensecategory.CompanyCode',$company);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	/*End of Added*/
	
	function get_currency_code($AccountNumber){
		$this->db->select('CurrencyRate');
		$this->db->from('am_accountgl');
		$this->db->join('am_currency','am_accountgl.CurrencyCode=am_currency.CurrencyCode');
		$this->db->join('at_compaccountgl','am_accountgl.AccountNumber=at_compaccountgl.AccountNumber');
		$this->db->where('at_compaccountgl.AccountNumber', $AccountNumber);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $CurrencyRate = $row->CurrencyRate;
            }

            return $CurrencyRate;
        }
        else {
            return FALSE;
        }	
	}
	
	function cek_app($cashbon_number){
		$this->db->select('AppLevel1Status, AppLevel2Status');
		$this->db->from('at_expensecashbon');
		$this->db->where('EBonNumber', $cashbon_number);
		$this->db->where('AppLevel1Status', 1);
		$this->db->where('AppLevel2Status', 1);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $app = 1;
            }
            return $app;
        }
        else {
		    $app = 0;
            return $app;
        }	
	}
	
	function cek_status_app_mgr($cashbon_number){
		$this->db->select('AppLevel1Status');
		$this->db->from('at_expensecashbon');
		$this->db->where('EBonNumber', $cashbon_number);
		$this->db->where('AppLevel1Status', 1);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AppLevel1Status = $row->AppLevel1Status;
            }
            return $AppLevel1Status;
        }
        else {
		    return FALSE;
        }	
	}
	
	function cek_status_app_dir($cashbon_number){
		$this->db->select('AppLevel2Status');
		$this->db->from('at_expensecashbon');
		$this->db->where('EBonNumber', $cashbon_number);
		$this->db->where('AppLevel2Status', 1);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AppLevel2Status = $row->AppLevel2Status;
            }
            return $AppLevel2Status;
        }
        else {
		    return FALSE;
        }	
	}
	
	function insert_expense_cashbon($data_cashbon){
		$this->db->insert($this->table, $data_cashbon);
		
		if ($this->db->trans_status() === FALSE) {
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function insert_detail_cashbon($detail_cashbon){
		$this->db->insert($this->detail, $detail_cashbon);
		
		if ($this->db->trans_status() === FALSE) {
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function insert_journal_expense_cashbon_debit($data_journal_debit){
		$this->db->insert($this->journal, $data_journal_debit);
		
		if ($this->db->trans_status() === FALSE) {
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function get_sys_expenses_akun($ExpCategoryID){
        $this->db->select('ExpCategoryID');
		$this->db->from('sys_expensecategory');
		$this->db->where('ExpCategoryID',$ExpCategoryID);
		$this->db->where('CompanyCode',$this->session->userdata('company'));
		
		//get SysItemCategoryID
		$query = $this->db->get();
		
		if ($query->num_rows() > 0) {
			$data=$query->row();
			$this->db->select('AccountNumber');
			$this->db->from('sys_expenseaccount');
			$this->db->where('ExpCategoryID',$data->ExpCategoryID);
			
			//get AccountNumber
			$query_account = $this->db->get();
			
			if ($query_account->num_rows() > 0) {
				return $query_account->row();
			}
			else {
				return false;
			}
		}
		else {
			return false;
		}			
	}
	
	function insert_journal_expense_cashbon_credit($data_journal_credit){
		$this->db->trans_begin();
		$this->db->insert($this->journal, $data_journal_credit);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			$this->db->trans_commit();
			return 1;
		}
	}
	
	function get_detail($id){
		$query = $this->db->join('at_ebondetail','at_expensecashbon.EBonNumber=at_ebondetail.EBonNumber');
        $query = $this->db->get_where($this->table, array('at_expensecashbon.EBonNumber' => $id));
		if($query->num_rows()>0){
			return $query->row();
		}
		else{
			return false;
		}      
    }
	
	function get_detail_header($id){
	    $query = $this->db->join('am_department','at_expensecashbon.DepartmentCode=am_department.DepartmentCode');
		$query = $this->db->join('am_employee','at_expensecashbon.EmployeeCode=am_employee.EmployeeCode');
        $query = $this->db->get_where($this->table, array('EBonNumber' => $id));
		if($query->num_rows()>0){
			return $query->row();
		}
		else{
			return false;
		}      
    }

    function get_detail_cashbon($id){
	    $query =   $this->db->join('at_compaccountgl','at_ebondetail.EBonAccountNumber=at_compaccountgl.AccountNumber');
		$query =  $this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		//$query =  $this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		$query = $this->db->get_where($this->detail, array('EBonNumber' => $id));
		if($query->num_rows()>0){
			return $query->row();
		}
		else{
			return false;
		}      
    }	
	
	function get_amount_cashbon($id){
		$this->db->select('EBonAmount');
		$this->db->from('at_ebondetail');
		$this->db->where('EBonNumber', $id);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $EBonAmount = $row->EBonAmount;
            }

            return $EBonAmount;
        }
        else {
            return FALSE;
        }	
	}
	
	function get_detail_item($replace_no){
	    $BranchCode    = $this->session->userdata('branch');
		
	    $this->db->select("am_accountgl.AccountNumber, am_accountgl.AccountName as EAccountNumber,EBonAmount,EBonMemo, sys_expensecategory.ExpCategoryName");
		$this->db->from('at_ebondetail');
		$this->db->join('at_compaccountgl','at_ebondetail.EBonAccountNumber=at_compaccountgl.AccountNumber','INNER');
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber','INNER');
		$this->db->join('sys_expenseaccount','at_ebondetail.EBonAccountNumber=sys_expenseaccount.AccountNumber', 'INNER');
		$this->db->join('sys_expensecategory','sys_expenseaccount.ExpCategoryID=sys_expensecategory.ExpCategoryID', 'INNER');
		$this->db->where('at_ebondetail.EBonNumber' , $replace_no);
		$this->db->where('at_compaccountgl.BranchCode' , $BranchCode);
       	$query  = $this->db->get();
	    $result = $query->result();
		
		return json_encode($result);
    }
	
	function update_expense_cashbon($data_cashbon, $EBonNumber){
		$this->db->where('EBonNumber', $EBonNumber);
		$this->db->update($this->table, $data_cashbon);
		
		if ($this->db->trans_status() === FALSE) {
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function update_detail_cashbon($detail_cashbon, $EBonNumber){
		$this->db->where('EBonNumber', $EBonNumber);
		$this->db->update($this->detail, $detail_cashbon);
		
		if ($this->db->trans_status() === FALSE) {
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function update_journal_expense_cashbon_debit($data_journal_debit, $EBonNumber, $nilai){
		$this->db->where('EBonNumber', $EBonNumber);
		$this->db->where('EBonSignStatus', $nilai);
		$this->db->update($this->journal, $data_journal_debit);
		
		if ($this->db->trans_status() === FALSE) {
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function update_journal_expense_cashbon_credit($data_journal_credit, $EBonNumber, $nilai){
		$this->db->where('EBonNumber', $EBonNumber);
		$this->db->where('EBonSignStatus', $nilai);
		$this->db->update($this->journal, $data_journal_credit);
		
		if ($this->db->trans_status() === FALSE) {
			return 0;
		}
		else {
			return 1;
		}
	}
	
	function update_trans_status($cashbon_number, $data){
		$this->db->where('EBonNumber', $cashbon_number);
		return $this->db->update($this->table, $data);
	}
	
	function approval_status($data, $cashbon_number){
		$this->db->where('EBonNumber', $cashbon_number);
		return $this->db->update($this->table, $data);
	}
	
	function status_finished($app_finished, $cashbon_number){
		$this->db->where('EBonNumber', $cashbon_number);
		return $this->db->update($this->table, $app_finished);
	}
	
	function get_date_journal_cashbon($cashbon_number){
		$this->db->select('at_expensecashbon.*, am_branch.BranchName, am_company.CompanyName');
		$this->db->from('at_expensecashbon');
		$this->db->join('am_branch','am_branch.BranchCode=at_expensecashbon.BranchCode');
		$this->db->join('am_company','am_company.CompanyCode=am_branch.CompanyCode');
		$this->db->where('at_expensecashbon.EBonNumber', $cashbon_number);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_cash_bon_grup($cashbon_number){
		$this->db->select('BranchCode');
		$this->db->from('at_expensecashbon');
		
		$this->db->where('at_expensecashbon.EBonNumber', $cashbon_number);
		$query = $this->db->get();
		
		$result=$query->row();
		return $result;
	}
	
	function get_all_journal_cashbon($cashbon_number, $branch){
		$this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, at_ebonjournal.EBonSaldoCredit, 
						   at_ebonjournal.EBonDate, at_ebonjournal.EBonNumber, at_ebonjournal.EBonSaldoDebet');
		$this->db->from('at_ebonjournal');
		$this->db->join('at_compaccountgl','at_ebonjournal.EBonAccountNumber=at_compaccountgl.AccountNumber');
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		$this->db->where('at_compaccountgl.BranchCode', $branch);
		$this->db->where('at_ebonjournal.EBonNumber', $cashbon_number);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function cek_status_by_number_bon($cashbon_number){
		$this->db->select('TransStatus');
		$this->db->from('at_expensecashbon');
		$this->db->where('EBonNumber', $cashbon_number);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $TransStatus = $row->TransStatus;
            }

            return $TransStatus;
        }
        else {
            return FALSE;
        }	
	}
	
	function create_ebon_number($EBonDate){
		$branch     = $this->session->userdata('branch');
        $month      = date('m', strtotime($EBonDate));
        $year       = date('y', strtotime($EBonDate));
        $ebon_number  = $branch."/".$month.$year."EC/";

        $this->db->like('EBonNumber', $ebon_number);
        $num    = $this->db->get($this->table)->num_rows()+1;
        $long   = strlen($num);
		
        if($long == 1){
            $number='00'.$num;
        }
        elseif($long == 2){
            $number='0'.$num;
        }
        else{
            $number=$num;
        }
	
        $new_ebon_number = $ebon_number.$number;
   
    
        return $new_ebon_number;
	}
	
	function check_code_exist($Number) {
        $query = $this->db->get_where($this->table, array('EBonNumber' => $Number), 1, 0);
		
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }	
    }
}
	