<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Bank_reconciliation extends CI_Model{

	//Example function
	/*public function getReconciliation($compID){
		$query = $this->db->select('*')
						->from('comp_payment as cp')
						//->join('comp_chart_of_acct as ca',"ca.")
						->where(array('companyID'=>$compID))
						->get();
		if($this->db->affected_rows()){
 			print_r($query->result());
 			exit();
			return $query->result();
		}else{
			return false;
		}	
	}*/
	
	//Gather bank statements according to date and bank ID
	public function getStatByIdDate($bankID = '',$date = ''){
		$date = $date != ''?date("Y-m",strtotime($date)):date("Y-m",time());
		$query = $this->db->select("cp.reconcile,DATE_FORMAT(cp.paymentDate,'%d-%m-%Y') as paymentDate,cp.ID,CONCAT(cp.formNo,'[',lpad(cp.ID,8,0),']') as transNo,cp.referenceNo,cp.accountDescription,cp.amountPaid,",false)
						->from('comp_payment cp')
						//->join('comp_payment_others co','co.')
						->join('comp_chart_of_acct ca','ca.ID = cp.accountID','left')
						->where(array('cp.companyID'=>element('compID', $this->session->userdata('logged_in')),'bankID'=>$bankID))
						->like(array('paymentDate'=>$date))
						->get();
		
		$query1 = $this->db->select("cp.reconcile,DATE_FORMAT(cp.paymentDate,'%d-%m-%Y') as paymentDate,cp.ID,CONCAT(cp.formNo,'[',lpad(cp.ID,8,0),']') as transNo,cp.referenceNo,cp.accountDescription,cp.amountPaid,",false)
						->from('comp_payment cp')
						//->join('comp_payment_others co','co.')
						->join('comp_chart_of_acct ca','ca.ID = cp.accountID','left')
						->where(array('cp.companyID'=>element('compID', $this->session->userdata('logged_in')),'bankID'=>$bankID,'reconcile'=>0,'paymentDate <'=>date("Y-m-01",strtotime($date))))
						->get();
		
		
		if($query->num_rows() > 0){
			$data['statements'] = array_merge($query->result(),$query1->result());
			$query = $this->db->select()
							->order_by('createdTS','DESC')
							->where(array('bankID'=>$bankID))
							->get('comp_bank_reconcile',1);
			$data['reconcile'] = $query->result();
			return $data;
		}else{
			return "";
		}
	}
	
	//Set conciliation status
	public function setConcile($id){
		$this->db->where('ID',$id)
				->update('comp_payment',array('reconcile'=>1));
		if($this->db->affected_rows() >= 1){
			return true;
		}else{
			return false;
		}
	}
	
	//Re conciliation
	public function reConcile($data){
		$this->db->insert('comp_bank_reconcile', $data);
		
		if($this->db->affected_rows() >= 1){
			return true;
		}else{
			return false;
		}
	}
}

