<?php
class M_journalvoucher extends CI_Model {
    
	public $table = "at_journalvoucher";
	public $detail = "at_jvchdetail";
	public $department = "am_department";
	public $account = "at_compaccountgl";
	
	function count_journal_number($bulan, $cek_tahun, $BranchCode){
	    $this->db->select('*');
		$this->db->from('at_journalvoucher');
		$this->db->where('MONTH(JVchDate)', $bulan);
		$this->db->where('YEAR(JVchDate)', $cek_tahun);
		$this->db->where('BranchCode', $BranchCode);
		return $this->db->get()->num_rows();
	}
	
	function get_last_journal_number($BranchCode){
		$qty="SELECT MAX(JVchNumber) as JVchNumber FROM at_journalvoucher WHERE BranchCode='$BranchCode'";
		return $this->db->query($qty)->result();
	}
	
	function create_jv_number($date){
        $branch_code    = $this->session->userdata('branch');
        $month          = date('m', strtotime($date));
        $year           = date('y', strtotime($date));
        
        $jv_number    = $branch_code."/".$month.$year."JV/";
	
        $this->db->like('JVchNumber', $jv_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;
        }
		
        return  $jv_number.$number;
    }
	
	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_akun($BranchCode){
	    $AccountNo	     = ($this->input->post('AccountNo')) ? $this->input->post('AccountNo') : '';
		$AccountName	 = ($this->input->post('AccountName')) ? $this->input->post('AccountName') : '';
		
		$this->db->select('at_compaccountgl.AccountNumber, am_accountgl.AccountName, am_accountgl.AccountSubTypeID, am_accountgl.CurrencyCode');
		$this->db->from($this->account);		
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		$this->db->like('am_accountgl.AccountNumber', $AccountNo);
		$this->db->like('am_accountgl.AccountName', $AccountName);	
		$this->db->where('at_compaccountgl.BranchCode', $BranchCode);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.TransStatus', 2);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function insert_journal_voucher($data_journal){
	    $this->db->trans_begin();
		$this->db->insert($this->table, $data_journal);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			$this->db->trans_commit();
			return 1;
		}
	}
	
	function insert_detail_journal_voucher($detail_voucher){
		$this->db->trans_begin();
		$this->db->insert($this->detail, $detail_voucher);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			$this->db->trans_commit();
			return 1;
		}
	}
	
	function insert_journaljvc_voucher($jaournal){
		$this->db->trans_begin();
		$this->db->insert('at_jvchjournal', $jaournal);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			$this->db->trans_commit();
			return 1;
		}
	}
	
	function get_list($id){
        $query = $this->db->get_where($this->table, array('JVchNumber' => $id));
		if($query->num_rows()>0){
			return $query->row();
		}
		else{
			return false;
		}      
    }

    function get_detail_jvoucher($jvc){
	    $branch = $this->session->userdata('branch');
		
		$this->db->select('*,am_vendor.VendorName, am_customer.CustomerName');
		$this->db->from($this->detail);
		$this->db->join('at_compaccountgl','at_jvchdetail.JVchAccount=at_compaccountgl.AccountNumber');
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		
		$this->db->join('am_vendor','at_jvchdetail.VendorCode = am_vendor.VendorCode', 'LEFT');
		$this->db->join('am_customer','at_jvchdetail.CustomerCode = am_customer.CustomerCode', 'LEFT');
		$this->db->join('am_currency','at_jvchdetail.CurrencyCode = am_currency.CurrencyCode', 'LEFT');
		
		$this->db->where('at_compaccountgl.BranchCode', $branch);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('at_jvchdetail.JVchNumber', $jvc);
		$query = $this->db->get();
		return $query->result();
	}
	
	function get_jvch_type($id){
		$this->db->select_min('JVchType');
		$this->db->from('at_jvchdetail');
		$this->db->where('JVchNumber', $id);
		$query = $this->db->get();

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

            return $JVchType;
        }
        else {
            return FALSE;
        }
	}
	
	function get_id_on_detail_jvch($id){
		$this->db->select_min('JVchDetailID');
		$this->db->from('at_jvchdetail');
		$this->db->where('JVchDetailID', $id);
		$query = $this->db->get();

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

            return $JVchDetailID;
        }
        else {
            return FALSE;
        }
	}
	
	function update_journal_voucher($data_journal, $JVchNumber){
		$this->db->where('JVchNumber', $JVchNumber);
		return $this->db->update('at_journalvoucher', $data_journal);
	}
	
	function update_detail_journal_voucher($detail_voucher, $id){
		$this->db->where('JVchDetailID', $id);
		return $this->db->update('at_jvchdetail', $detail_voucher);
	}
	
	function update_journaljvc_debit_voucher($jaournal_debit, $AccountNumber){
		$this->db->update('at_jvchjournal', $jaournal_debit, array('JVchAccountNumber' => $AccountNumber, 'JVchSaldoCredit' => 0));

        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
	}
	
	function update_journaljvc_credit_voucher($jaournal_credit, $AccountNumber){
		$this->db->update('at_jvchjournal', $jaournal_credit, array('JVchAccountNumber' => $AccountNumber, 'JVchSaldoDebet' => 0));

        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
	}
	
	function delete_jv_journal($JVchNumber){
		$this->db->where('JVchNumber', $JVchNumber);
		return $this->db->delete('at_jvchjournal');
	}
	
	function get_journal_id($JVchNumber){
		$this->db->select('JVchJournalID');
		$this->db->from('at_jvchjournal');
		$this->db->where('JVchNumber', $JVchNumber);
		return $this->db->get()->result();
	}
	
	function get_date_journal($journal_number){
		$this->db->select('*');
		$this->db->from('at_journalvoucher');
		$this->db->where('JVchNumber', $journal_number);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_all_journal($journal_number,$BranchCode){
		$this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, at_jvchjournal.JVchSaldoCredit, 
						   at_jvchjournal.JVchSaldoDebet, at_jvchjournal.JVchDesc, at_jvchjournal.JVchNumber');
		$this->db->from('at_compaccountgl');
		$this->db->join('at_jvchjournal','at_compaccountgl.AccountNumber=at_jvchjournal.JVchAccountNumber');
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		$this->db->where('at_jvchjournal.JVchNumber', $journal_number);
		$this->db->where('at_compaccountgl.BranchCode', $BranchCode);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function cek_status_app_mgr($voucher_no){
		$this->db->select('AppLevel1Status');
		$this->db->from('at_journalvoucher');
		$this->db->where('JVchNumber', $voucher_no);
		$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($voucher_no){
		$this->db->select('AppLevel2Status');
		$this->db->from('at_journalvoucher');
		$this->db->where('JVchNumber', $voucher_no);
		$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 approval_status($data,$voucher_no){
		$this->db->where('JVchNumber', $voucher_no);
		return $this->db->update('at_journalvoucher', $data);
	}
	
	function get_cust_outstanding($txt_vendor_customer, $branch){
		$this->db->select('*');
		$this->db->from('am_customer');
		$this->db->where('CustomerCode', $txt_vendor_customer);
		$this->db->where('BranchCode', $branch);
		$query = $this->db->get();
		
		$result=$query->row();
		return $result;
	}
	
	 function get_vendor($currency){      
	 	$branch = $this->session->userdata('branch');  
        $this->db->select('am_vendor.*,am_currency.CurrencyRate');
        $this->db->from('am_vendor');
		$this->db->join('am_currency','am_vendor.CurrencyCode=am_currency.CurrencyCode','LEFT');
 
    
        $this->db->where('am_vendor.TransStatus !=', 3);
		$this->db->where('am_vendor.BranchCode =', $branch);
		$this->db->where('am_vendor.CurrencyCode =', $currency);
        $this->db->order_by('am_vendor.VendorCode', 'ASC');                
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function get_customer($currency){      
	 	$branch = $this->session->userdata('branch');  
        $this->db->select('am_customer.*,am_currency.CurrencyRate');
        $this->db->from('am_customer');
		$this->db->join('am_currency','am_customer.CurrencyCode=am_currency.CurrencyCode','LEFT');
 
    
        $this->db->where('am_customer.TransStatus !=', 3);
		$this->db->where('am_customer.BranchCode =', $branch);
		$this->db->where('am_customer.CurrencyCode =', $currency);
        $this->db->order_by('am_customer.CustomerCode', 'ASC');                
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function get_vendor_edit($branhc_replace){
        $this->db->select('am_vendor.*,am_currency.CurrencyRate');
        $this->db->from('am_vendor');
		$this->db->join('am_currency','am_vendor.CurrencyCode=am_currency.CurrencyCode','LEFT');
 
    
        $this->db->where('am_vendor.TransStatus !=', 3);
		$this->db->where('am_vendor.BranchCode =', $branhc_replace);
		//$this->db->where('am_vendor.CurrencyCode =', $curren_code);
        $this->db->order_by('am_vendor.VendorCode', 'ASC');                
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function get_customer_edit($branhc_replace){      
        $this->db->select('am_customer.*,am_currency.CurrencyRate');
        $this->db->from('am_customer');
		$this->db->join('am_currency','am_customer.CurrencyCode=am_currency.CurrencyCode','LEFT');
 
    
        $this->db->where('am_customer.TransStatus !=', 3);
		$this->db->where('am_customer.BranchCode =', $branhc_replace);
		//$this->db->where('am_customer.CurrencyCode =', $curren_code);
        $this->db->order_by('am_customer.CustomerCode', 'ASC');                
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function update_trans_status($id, $data){
		$this->db->where('JVchNumber', $id);
		return $this->db->update('at_journalvoucher', $data);
	}
	
	function get_vend_outstanding($txt_vendor_customer,$branch){
		$this->db->select('*');
		$this->db->from('am_vendor');
		$this->db->where('VendorCode', $txt_vendor_customer);
		$this->db->where('BranchCode', $branch);
		$query = $this->db->get();
		
		$result=$query->row();
		return $result;
	}
	
	function get_jurnal($id){
		$this->db->select('*');
		$this->db->from('at_jvchdetail');
		$this->db->where('JVchDetailID', $id);
		$query = $this->db->get();
		
		$result=$query->row();
		return $result;
	}
	
	function status_finished($app_finished, $voucher_no){
		$this->db->where('JVchNumber', $voucher_no);
		return $this->db->update('at_journalvoucher', $app_finished);
	}
	
	function cek_app($voucher_no){
		$this->db->select('AppLevel1Status, AppLevel2Status');
		$this->db->from('at_journalvoucher');
		$this->db->where('JVchNumber', $voucher_no);
		$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 delete_detail_by_id($id){
        $this->db->delete($this->detail, array('JVchDetailID' => $id));	

        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }
	
	function delete_detail_jurnal_by_id($nomor_transaksi,$nomor_akun){
        $this->db->delete('at_jvchjournal', array('JVchNumber' => $nomor_transaksi, 'JVchAccountNumber' => $nomor_akun));	

        if ($this->db->trans_status() == FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }
}