<?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 from all payment according to date and bank ID
	public function getStatPaymentBankIdDate($bankID = '',$acctCode = '',$date = ''){
		//$date = ($date != '')?date("Y-m",strtotime($date)):date("Y-m",time());
		$query = $this->db->select("a.referenceNo,a.tableName,DATE_FORMAT(a.transDate,'%d-%m-%Y') as transDate,a.ID,a.formNo,a.description,a.debitAmount,a.creditAmount",false)
						->from('view_bank_reconcile a')
						->where(array(
									'a.companyID'=>element('compID', $this->session->userdata('logged_in')),
									'a.bankID'=>$bankID,
								))
						->like(array('a.transDate'=>$date))
						->order_by('a.transDate','DESC')
						->get();
		$query1 = $this->db->select("IFNULL(a.referenceNo,'---') referenceNo,a.tableName,DATE_FORMAT(a.transDate,'%d-%m-%Y') as transDate,group_concat(a.ID separator ',') as ID,a.formNo,a.description,SUM(a.debitAmount) debitAmount,SUM(a.creditAmount) creditAmount",false)
						->from('view_bank_reconcile a')
						->where(array(
									'a.companyID'=>element('compID', $this->session->userdata('logged_in')),
									'a.bankID'=>$bankID,
									'a.transDate <'=>date("Y-m-d",strtotime($date."+1 days"))))
						//->order_by('a.transDate','DESC')
						//->distinct()
						->group_by('referenceNo,tableName,transDate,formNo')
						->get();
		//echo $this->db->last_query();
		//exit();
		$query2 = $this->db->select()
						->order_by('bRecon.createdTS','DESC')
						->where(array('bRecon.bankLinkAcctCode'=>$acctCode))
						->get('comp_bank_reconcile bRecon',1);
		$data['reconcile'] = $query2->result();
		if(!empty($data['reconcile'])){
			$nxtDate = strtotime($data['reconcile'][0]->reconcileDate);
			$data['reconcile'][0]->nxtReconcileDate = date("Y-m-d",strtotime("+1 month",$nxtDate));
		}

		//Get GL Balance 
		$this->load->model('Compfinancialyear');
		$finanYear = $this->Compfinancialyear->compfinancialyear_date($date);
		if(empty($finanYear))	return "";
		$this->load->model('Journal');		
		$this->load->model('CompAcctDet');
		$data['glBalance'] = $this->CompAcctDet->AcctDet_allfy1_period_acctNo($finanYear[0]->financialYear, $finanYear[0]->period, $acctCode);
		$glBalance = $data['glBalance'][0]->yeardebit - $data['glBalance'][0]->yearcredit;
		$data['glBalance1'] = $this->Journal->journalDetails_yearfromtoperiodacctNo($finanYear[0]->financialYear, $finanYear[0]->period, $finanYear[0]->period, $acctCode);
		
		if(!empty($data['glBalance1'])){
			foreach($data['glBalance1'] as $row){
				$glBalance = $glBalance + $row->amount_dr - $row->amount_cr;
			}
		}
		$data['glBalance'] = $glBalance;
		if($query->num_rows() > 0 || $query1->num_rows() > 0 || $query2->num_rows() > 0){
			$data['statements'] = $query1->result();
			return $data;
		}else{
			return $data;
		}
	}


	//Gather bank statements from journal according to date and bank ID
	public function getStatJournalIdDate($acctCode = '',$date = ''){
		$date = ($date != '')?date("Y-m",strtotime($date)):date("Y-m",time());
		$query = $this->db->select("jDet.bank_reconcile,DATE_FORMAT(j.effective_date,'%d-%m-%Y') as transDate,jDet.ID,jDet.formNo,jDet.description,jDet.amount_dr,jDet.amount_cr",false)
						->from('comp_journal_detail jDet')
						->join('comp_journal j','j.journalID = jDet.journalID AND j.companyID = jDet.companyID','LEFT')
						->where(array(
									'jDet.companyID'=>element('compID', $this->session->userdata('logged_in')),
									'jDet.bank_reconcile'=>0,
									'jDet.acctCode'=>$acctCode,
									'j.status'=>1
								))
						->like(array('j.effective_date'=>$date))
						->order_by('j.effective_date','DESC')
						->get();
		$query1 = $this->db->select("j.journalID jID,jDet.bank_reconcile,DATE_FORMAT(j.effective_date,'%d-%m-%Y') as transDate,jDet.ID journalID,jDet.formNo,jDet.description,jDet.amount_dr,jDet.amount_cr",false)
						->from('comp_journal_detail jDet')
						->join('comp_journal j','j.journalID = jDet.journalID AND j.companyID = jDet.companyID','LEFT')
						->where(array(
							'jDet.companyID'=>element('compID', $this->session->userdata('logged_in')),
							'jDet.acctCode'=>$acctCode,
							'jDet.bank_reconcile'=>0,
							'j.status'=>1,
							'j.effective_date <'=>date("Y-m-01",strtotime($date))))
						->order_by('j.effective_date','DESC')
						->distinct()
						->get();
		
		$query2 = $this->db->select()
						->order_by('bRecon.createdTS','DESC')
						->where(array('bRecon.bankLinkAcctCode'=>$acctCode))
						->get('comp_bank_reconcile bRecon',1);
		$data['reconcile'] = $query2->result();

		//Get GL Balance 
		$this->load->model('Compfinancialyear');
		$finanYear = $this->Compfinancialyear->compfinancialyear_date($date);
		$this->load->model('Journal');		
		$this->load->model('CompAcctDet');
		$data['glBalance'] = $this->CompAcctDet->AcctDet_allfy1_period_acctNo($finanYear[0]->financialYear, $finanYear[0]->period, $acctCode);
		$glBalance = $data['glBalance'][0]->yeardebit - $data['glBalance'][0]->yearcredit;
		$data['glBalance1'] = $this->Journal->journalDetails_yearfromtoperiodacctNo($finanYear[0]->financialYear, 1, $finanYear[0]->period, $acctCode);

		if(!empty($data['glBalance1'])){
			foreach($data['glBalance1'] as $row){
				$glBalance = $glBalance + $row->amount_dr - $row->amount_cr;
			}
		}
		$data['glBalance'] = $glBalance;
		if($query->num_rows() > 0 || $query1->num_rows() > 0 || $query2->num_rows() > 0){
			$data['statements'] = (object)array_merge((array)$query->result(),(array)$query1->result());
			return $data;
		}else{
			return "";
		}
	}

	//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",strtotime($date))))
						->get();
		echo $this->db->last_query();
		
		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,$conID){
		$this->db->where('ID',$id)
				->update('comp_journal_detail',array('bank_reconcile'=>$conID));
		if($this->db->affected_rows() >= 1){
			return true;
		}else{
			return false;
		}
	}
	//Set conciliation to corresponding payment table
	/*public function setConcileTable($id,$conID){
		$tblInfo = explode('->',$id);
		$this->db->where('ID',$tblInfo[0])
				->update($tblInfo[1],array('bankReconcileID'=>$conID));
		if($this->db->affected_rows() >= 1){
			return true;
		}else{
			return false;
		}
	}*/

	//Set conciliation to comp_journal_detail
	public function setConcileTable($id,$conID){
		$this->db->where('ID',$id)
				->update("comp_journal_detail",array('bankReconcileID'=>$conID));
		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 $this->db->insert_id();
		}else{
			return false;
		}
	}

	//Update conciliation
	public function updateConcile($id,$data){
		$this->db->where('ID',$id)
				->update('comp_bank_reconcile', $data);
		
		if($this->db->affected_rows() >= 1){
			return true;
		}else{
			return false;
		}
	}
	//Reset previous reconcile journal entries
	public function resetConcil($id){
		$tables = array(
						"comp_cash_sales t1",
						"comp_receive_payment t1",
						"comp_receive_payment_others t1",
						"comp_payment t1",
						"comp_payment_others t1",
						"comp_unpresented_check t1",
						"comp_journal_detail t1",
					);
		$affected_rows = 0;
		foreach($tables as $tbl){
			$this->db->set('t1.bankReconcileID',0)
				->where('t1.bankReconcileID',$id)
				->update($tbl);	
			$affected_rows += $this->db->affected_rows();
		}
		$this->db->where('reconID',$id)->delete('comp_bank_reconcile_track_back');
		if($affected_rows > 0){

			return true;
		}
	}
	//Gather all reconcile data
	public function getAllConcile($startDate,$endDate,$bankCode){
		$this->db->select('a.*,b.ID bankID,b.bankName,b.bankShortName')
				->from('comp_bank_reconcile a')
				->join('comp_bank b','b.linkAccountID=a.bankLinkAcctCode','LEFT')
				->order_by('reconcileDate',"DESC")
				->where(array(
							'a.companyID'=>element('compID', $this->session->userdata('logged_in')),
							'reconcileDate >='=>$startDate,
							'reconcileDate <='=>$endDate
						));
		if($bankCode != 'all'){
			$this->db->where('bankLinkAcctCode',$bankCode);
		}
		$query = $this->db->get();
		if($query->num_rows() > 0){
			return $query->result();
		}else{
			return false;
		}
	}

	//Gather bank statements according to date and bank ID
	public function getReconcileById($ID = ''){
		if($ID == '')	return false;
		
		$query = $this->db->select()
						->where(array(
								'ID'=>$ID
							))
						->get('comp_bank_reconcile');
		if($query->num_rows() > 0){
			$data['concile'] = $query->result();
			$acctCode = $data['concile'][0]->bankLinkAcctCode;
			$query = $this->db->select('ID')->where('linkAccountID',$acctCode)->get('comp_bank');
			$bankID = $query->result();
			
			$query1 = $this->db->select("a.bankReconcileID,a.referenceNo,a.tableName,DATE_FORMAT(a.transDate,'%d-%m-%Y') as transDate,a.ID,a.formNo,a.description,a.debitAmount,a.creditAmount",false)
						->from('view_bank_reconcile_reverse a')
						->where(array(
									'companyID'=>element('compID', $this->session->userdata('logged_in')),
									'bankReconcileID'=>$ID,
								))
						->order_by('a.transDate','DESC')
						->get();
			$query2 = $this->db->select("0 as bankReconcileID,a.referenceNo,a.tableName,DATE_FORMAT(a.transDate,'%d-%m-%Y') as transDate,a.ID,a.formNo,a.description,a.debitAmount,a.creditAmount",false)
							->from('view_bank_reconcile a')
							->where(array(
										'companyID'=>element('compID', $this->session->userdata('logged_in')),
										'bankID'=>$bankID[0]->ID,
									))
							->order_by('a.transDate','DESC')
							->distinct()
							->get();
			/*$query1 = $this->db->select("jDet.bank_reconcile,DATE_FORMAT(j.effective_date,'%d-%m-%Y') as transDate,jDet.ID,jDet.formNo,jDet.description,jDet.amount_dr,jDet.amount_cr",false)
							->from('comp_journal_detail jDet')
							->join('comp_journal j','j.journalID = jDet.journalID','LEFT')
							->where('jDet.bank_reconcile',$ID)
							->order_by('j.effective_date','DESC')
							->distinct()
							->get();

			$query2 = $this->db->select("jDet.bank_reconcile,DATE_FORMAT(j.effective_date,'%d-%m-%Y') as transDate,jDet.ID,jDet.formNo,jDet.description,jDet.amount_dr,jDet.amount_cr",false)
							->from('comp_journal_detail jDet')
							->join('comp_journal j','j.journalID = jDet.journalID','LEFT')
							->where(array('jDet.companyID'=>element('compID', $this->session->userdata('logged_in')),'jDet.acctCode'=>$acctCode,'jDet.bank_reconcile'=>0,'j.effective_date <'=>date("Y-m-d")))
							->order_by('j.effective_date','DESC')
							->distinct()
							->get();*/
			$data['datatbls'] = array_merge($query1->result(),$query2->result());
			//print_r($data['datatbls']);
			//exit();
			return $data;
		}else{
			return "";
		}
	}

	//Store other entries to corresponding table
	/*public function setTrackBack($trackID,$concileID){
		if($trackID == '')	return 0;
		$IDs = explode(',',$trackID);
		foreach($IDs as $id){
			$tblInfo = explode('->',$id);
			$query = $this->db->select('transDate,referenceNo,description,debitAmount,creditAmount')	
							->where(array(
									'ID' 		=> $tblInfo[0],
									'tableName' => $tblInfo[1],
									'companyID' => element('compID', $this->session->userdata('logged_in')),
								))
							->get('view_bank_reconcile');
			$data = $query->result();
			$trackData = array(
							'reconID'		=> $concileID,
							'refNo'			=> $data[0]->referenceNo,
							'transDate'		=> $data[0]->transDate,
							'description'	=> $data[0]->description,
							'debitAmount'	=> $data[0]->debitAmount,
							'creditAmount'	=> $data[0]->creditAmount,
							'createdBy'		=> element('role', $this->session->userdata('logged_in')),
						);
			$this->db->insert('comp_bank_reconcile_track_back',$trackData);
		}

	}*/


	//Store other entries from comp_journal_detail (sayem(22-10-2015))
	public function setTrackBack($trackID,$concileID){
		if($trackID == '')	return 0;
		$IDs = explode(',',$trackID);
		foreach($IDs as $id){
			$tblInfo = $id;
			$query = $this->db->select('transDate,referenceNo,description,debitAmount,creditAmount')	
							->where(array(
									'ID' 		=> $tblInfo,
									'tableName' => "comp_journal_detail",
									'companyID' => element('compID', $this->session->userdata('logged_in')),
								))
							->get('view_bank_reconcile');
			$data = $query->result();
			$trackData = array(
							'reconID'		=> $concileID,
							'refNo'			=> $data[0]->referenceNo,
							'transDate'		=> $data[0]->transDate,
							'description'	=> $data[0]->description,
							'debitAmount'	=> $data[0]->debitAmount,
							'creditAmount'	=> $data[0]->creditAmount,
							'createdBy'		=> element('role', $this->session->userdata('logged_in')),
						);
			$this->db->insert('comp_bank_reconcile_track_back',$trackData);
		}

	}

	// Remove bank-reconciliation entry
	public function remove($reconID){
		$this->db->where('ID',$reconID)->delete('comp_bank_reconcile');
		if($this->db->affected_rows() > 0){
			return true;
		}else{
			return false;
		}
	}

	//Gather reconcialiation information
	public function getConcileInfo($ID){
		$query = $this->db->select("c.*,'BALANCE AS PER BANK STATEMENT' as bankHdr,CONCAT('[',SUBSTRING(a.acctCode,1,4),'-',SUBSTRING(a.acctCode,5,3),'-',SUBSTRING(a.acctCode,8,2),'] ',b.bankName,' (',b.bankAccountNo,')') as bankName,'' as blank",false) 
						->where(array(
								'c.ID'=>$ID
							))
						->join('comp_bank b','b.linkAccountID = c.bankLinkAcctCode',"LEFT")
						->join('comp_chart_of_acct a','a.ID = b.linkAccountID','LEFT')
						->from('comp_bank_reconcile c')
						->get();

		if($query->num_rows()>0){
			return $query->result();
		}else{
			return false;
		}
	}

	//Get Deposits not credited by bank
	public function getCreditDeposits($ID){
		$query = $this->db->select("a.*,SUM(a.debitAmount) debitAmount,DATE_FORMAT(a.transDate,'%d-%m-%Y') transDate,'' blank",false)
						->where(array('reconID'=>$ID,'debitAmount >' =>0))
						->group_by('refNo,description,transDate')
						->get('comp_bank_reconcile_track_back a');
		$data['deposit'] = $query->result();
		$query = $this->db->select("a.*,SUM(a.creditAmount) creditAmount,DATE_FORMAT(a.transDate,'%d-%m-%Y') transDate,'' blank",false)
						->where(array('reconID'=>$ID,'creditAmount >' =>0))
						->group_by('refNo,description,transDate')
						->get('comp_bank_reconcile_track_back a');
		$data['unpresented'] = $query->result();
		if(!empty($data['deposit']) && !empty($data['unpresented'])){
			return $data;
		}
		elseif(empty($data['deposit'])){
			unset($data['deposit']);
			return $data;
		}elseif(empty($data['unpresented'])){
			unset($data['unpresented']);
			return $data;
		}else{
			return false;
		}
	}

	//Gather unpresented cheque list
	public function unpresented_list(){
		$query = $this->db->select("a.*,a.transNo AS `formNo`,b.bankName,c.acctCode,",false)
						->where(array(
									'a.companyID' => element('compID', $this->session->userdata('logged_in')),
									'a.is_delete' => 0
								))
						->join('comp_bank b','b.ID = a.bankID','left')
						->join('comp_chart_of_acct c','c.ID = b.linkAccountID','left')
						->get('comp_unpresented_check a')->result();

		if(!empty($query)){
			return $query;
		}else{
			return false;
		}
	}

	//Remove unpresented cheque
	public function chequeRemove($id){
		$this->db->set('is_delete',1)->where('ID',$id)->update('comp_unpresented_check');
		if($this->db->affected_rows() > 0){
			return true;
		}else{
			return false;
		}
	}

	//Insert 
	public function insert($data){
		$this->db->insert($data['tbName'],$data['data']);
		if($this->db->affected_rows() > 0){
			return true;
		}else{
			return false;
		}
	}
	//Update 
	public function update($data){
		$this->db->set($data['data'])->where('ID',$data['ID'])->update($data['tbName']);
		if($this->db->affected_rows() > 0){
			return true;
		}else{
			return false;
		}
	}

	//Gather chequeinformation
	public function getChequeInfo($id){
		$query = $this->db->where('ID', $id )->get('comp_unpresented_check')->result();
		if(!empty($query)){
			return $query;
		}else{
			return false;
		}
	}
}