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

class Account_transactions_model extends CI_Model {

	//Datatable start
	var $table = 'ac_transactions as a';
	var $column_order = array( 
							'a.id',
							'a.transaction_date',
							'a.transaction_type',
							'a.debit_account_id',
							'a.credit_account_id',
							'a.debit_amt',
							'a.credit_amt',
							'a.transaction_currency_id',
							'a.exchange_rate',
							'a.base_currency_debit_amt',
							'a.base_currency_credit_amt',
							'a.note',
							'a.created_by',
							'a.created_date',
							'a.payment_code',
							'a.customer_id',
							'a.suppliers_id',
							'a.ref_salespayments_id',
							'a.ref_salespaymentsreturn_id',
							'a.ref_purchasepayments_id',
							'a.ref_purchasepaymentsreturn_id',
							'a.ref_expense_id',
							); //set column field database for datatable orderable
	var $column_search = array( 
							'a.id',
							'a.transaction_date',
							'a.transaction_type',
							'a.debit_account_id',
							'a.credit_account_id',
							'a.debit_amt',
							'a.credit_amt',
							'a.transaction_currency_id',
							'a.exchange_rate',
							'a.base_currency_debit_amt',
							'a.base_currency_credit_amt',
							'a.note',
							'a.created_by',
							'a.created_date',
							'a.payment_code',
							'a.customer_id',
							'a.suppliers_id',
							'a.ref_salespayments_id',
							'a.ref_salespaymentsreturn_id',
							'a.ref_purchasepayments_id',
							'a.ref_purchasepaymentsreturn_id',
							'a.ref_expense_id',
							);//set column field database for datatable searchable 
	var $order = array('a.id' => 'asc'); // default order 

	public function __construct()
	{
		parent::__construct();
	}

	private function _get_datatables_query()
	{
		// Use the simplest possible query first
		$this->db->select('a.*');
		$this->db->from($this->table);
		//if(!is_admin()){
			$this->db->where("a.store_id",get_current_store_id());
		//}
		$account_id = $this->input->post('account_id');
		if (empty($account_id)) $account_id = isset($_POST['account_id']) ? $_POST['account_id'] : null;
		if($account_id){
			$this->db->where("(a.credit_account_id=$account_id or a.debit_account_id=$account_id)");
		}

		$from_date = $this->input->post('from_date');
		if (empty($from_date)) $from_date = isset($_POST['from_date']) ? $_POST['from_date'] : null;
     	$from_date = system_fromatted_date($from_date);
     	if($from_date!='1970-01-01'){
     		$this->db->where("a.transaction_date>=",$from_date);
     	}

     	$to_date = $this->input->post('to_date');
		if (empty($to_date)) $to_date = isset($_POST['to_date']) ? $_POST['to_date'] : null;
     	$to_date = system_fromatted_date($to_date);
     	if($to_date!='1970-01-01'){
     		$this->db->where("a.transaction_date<=",$to_date);
     	}

     	$users = $this->input->post('users');
		if (empty($users)) $users = isset($_POST['users']) ? $_POST['users'] : null;
     	if($users && !empty($users)){
 	    	$this->db->where("upper(a.created_by)",strtoupper($users));
    	}

    	//echo $this->db->get_compiled_select();exit;

		$i = 0;
	
		foreach ($this->column_search as $item) // loop column 
		{
			if($this->input->post('search') && $this->input->post('search')['value']) // if datatable send POST for search
			{
				
				if($i===0) // first loop
				{
					$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
					$this->db->like($item, $this->input->post('search')['value']);
				}
				else
				{
					$this->db->or_like($item, $this->input->post('search')['value']);
				}

				if(count($this->column_search) - 1 == $i) //last loop
					$this->db->group_end(); //close bracket
			}
			$i++;
		}
		
		if($this->input->post('order')) // here order processing
		{
			$order_data = $this->input->post('order');
			$this->db->order_by($this->column_order[$order_data['0']['column']], $order_data['0']['dir']);
		} 
		else if(isset($this->order))
		{
			$order = $this->order;
			$this->db->order_by(key($order), $order[key($order)]);
		}
	}

	function get_datatables()
	{
		try {
			$this->_get_datatables_query();
			$length = $this->input->post('length');
			$start = $this->input->post('start');
			if (empty($length)) $length = isset($_POST['length']) ? $_POST['length'] : 10;
			if (empty($start)) $start = isset($_POST['start']) ? $_POST['start'] : 0;
			if($length != -1)
			$this->db->limit($length, $start);
			
			$query = $this->db->get();
			log_message('debug', 'Account Transactions Query executed - Rows: ' . $query->num_rows());
			log_message('debug', 'Account Transactions Query SQL: ' . $this->db->last_query());
			
			return $query->result();
		} catch (Exception $e) {
			log_message('error', 'Account Transactions Model Error: ' . $e->getMessage());
			log_message('error', 'Account Transactions Model Error File: ' . $e->getFile() . ' Line: ' . $e->getLine());
			log_message('error', 'Account Transactions Model Error SQL: ' . $this->db->last_query());
			throw $e;
		}
	}

	function count_filtered()
	{
		// Create a new query instance to avoid interference
		$this->db->select('COUNT(*) as count');
		$this->db->from($this->table);
		$this->db->where("a.store_id", get_current_store_id());
		
		$account_id = $this->input->post('account_id');
		if (empty($account_id)) $account_id = isset($_POST['account_id']) ? $_POST['account_id'] : null;
		if($account_id){
			$this->db->where("(a.credit_account_id=$account_id or a.debit_account_id=$account_id)");
		}

		$from_date = $this->input->post('from_date');
     	$from_date = system_fromatted_date($from_date);
     	if($from_date!='1970-01-01'){
     		$this->db->where("a.transaction_date>=",$from_date);
     	}

     	$to_date = $this->input->post('to_date');
     	$to_date = system_fromatted_date($to_date);
     	if($to_date!='1970-01-01'){
     		$this->db->where("a.transaction_date<=",$to_date);
     	}

     	$users = $this->input->post('users');
     	if($users && !empty($users)){
     		$this->db->where("upper(a.created_by)",strtoupper($users));
     	}

		$query = $this->db->get();
		return $query->num_rows();
	}

	public function count_all()
	{
		$this->db->where("a.store_id",get_current_store_id());
		$this->db->from($this->table);
		return $this->db->count_all_results();
	}

	
}
