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

class Cash_transactions_model extends CI_Model {

	//Datatable start
	var $table = '';
	var $column_order = array( 
							
							); //set column field database for datatable orderable
	var $column_search = array( 
							
							);//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()
	{
		
		

		
		$from_date_query = $from_date_query_expense = $to_date_query = $to_date_query_expense = $users_query  ='';

		$from_date = $this->input->post('from_date');
     	$from_date = system_fromatted_date($from_date);
     	if($from_date!='1970-01-01'){
     		$from_date_query = " and payment_date >= '$from_date'";
     		$from_date_query_expense = " and expense_date >= '$from_date'";
     		
     		
     	}

     	$to_date = $this->input->post('to_date');
     	$to_date = system_fromatted_date($to_date);
     	if($to_date!='1970-01-01'){
     		$to_date_query = " and payment_date <= '$to_date'";
     		$to_date_query_expense = " and expense_date <= '$to_date'";
     	}

     	$users = $this->input->post('users');

     	if(!empty($users)){
 	    	
 	    	$users_query = " and upper(created_by) = '".strtoupper($users)."' ";
    	}


		$i = 0;
	
		foreach ($this->column_search as $item) // loop column 
		{
			if($_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, $_POST['search']['value']);
				}
				else
				{
					//$this->db->or_like($item, $_POST['search']['value']);
				}

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


		} 
		else if(isset($this->order))
		{
			//$order = $this->order;
			//$this->db->order_by(key($order), $order[key($order)]);
		}


		$query =  "SELECT 	
							'SALES_PAYMENT' as SALES_PAYMENT,
							s.id,
							s.payment_date,
							s.payment_code,
							s.payment_type,
							s.payment,
							s.payment_note,
							s.created_by,
							s.created_date,
							s.created_time,
							s.account_id,
							s.store_id,
							COALESCE(a.currency_id, 1) as currency_id,
							COALESCE(c.currency_code, 'USD') as currency_code,
							COALESCE(c.symbol, '$') as currency_symbol,
							1.000000 as exchange_rate,
							s.payment as base_currency_amount
							FROM db_salespayments s
							LEFT JOIN ac_accounts a ON s.account_id = a.id
							LEFT JOIN db_currency c ON a.currency_id = c.id
							WHERE s.store_id = ".get_current_store_id()." 
							$from_date_query
							$to_date_query 
							$users_query 

							UNION ALL

							SELECT 
							'PURCHASE_PAYMENT' as SALES_PAYMENT,
							p.id,
							p.payment_date,
							p.payment_code,
							p.payment_type,
							p.payment,
							p.payment_note,
							p.created_by,
							p.created_date,
							p.created_time,
							p.account_id,
							p.store_id,
							COALESCE(a.currency_id, 1) as currency_id,
							COALESCE(c.currency_code, 'USD') as currency_code,
							COALESCE(c.symbol, '$') as currency_symbol,
							1.000000 as exchange_rate,
							p.payment as base_currency_amount
							FROM db_purchasepayments p
							LEFT JOIN ac_accounts a ON p.account_id = a.id
							LEFT JOIN db_currency c ON a.currency_id = c.id
							WHERE p.store_id = ".get_current_store_id()." 
							$from_date_query
							$to_date_query 
							$users_query 

							UNION ALL

							SELECT 
							'SALES_RETURN_PAYMENT' as SALES_PAYMENT,
							sr.id,
							sr.payment_date,
							sr.payment_code,
							sr.payment_type,
							sr.payment,
							sr.payment_note,
							sr.created_by,
							sr.created_date,
							sr.created_time,
							sr.account_id,
							sr.store_id,
							COALESCE(a.currency_id, 1) as currency_id,
							COALESCE(c.currency_code, 'USD') as currency_code,
							COALESCE(c.symbol, '$') as currency_symbol,
							1.000000 as exchange_rate,
							sr.payment as base_currency_amount
							FROM db_salespaymentsreturn sr
							LEFT JOIN ac_accounts a ON sr.account_id = a.id
							LEFT JOIN db_currency c ON a.currency_id = c.id
							WHERE sr.store_id = ".get_current_store_id()." 
							$from_date_query
							$to_date_query 
							$users_query 

							UNION ALL

							SELECT 
							'PURCHASE_RETURN_PAYMENT' as SALES_PAYMENT,
							pr.id,
							pr.payment_date,
							pr.payment_code,
							pr.payment_type,
							pr.payment,
							pr.payment_note,
							pr.created_by,
							pr.created_date,
							pr.created_time,
							pr.account_id,
							pr.store_id,
							COALESCE(a.currency_id, 1) as currency_id,
							COALESCE(c.currency_code, 'USD') as currency_code,
							COALESCE(c.symbol, '$') as currency_symbol,
							1.000000 as exchange_rate,
							pr.payment as base_currency_amount
							FROM db_purchasepaymentsreturn pr
							LEFT JOIN ac_accounts a ON pr.account_id = a.id
							LEFT JOIN db_currency c ON a.currency_id = c.id
							WHERE pr.store_id = ".get_current_store_id()." 
							$from_date_query
							$to_date_query 
							$users_query 

							UNION ALL

							SELECT 
							'EXPENSE' as SALES_PAYMENT,
							e.id,
							e.expense_date as payment_date,
							e.expense_code as payment_code,
							e.payment_type,
							e.expense_amt as payment,
							e.note as payment_note,
							e.created_by,
							e.created_date,
							e.created_time,
							e.account_id,
							e.store_id,
							COALESCE(e.expense_currency_id, 1) as currency_id,
							COALESCE(c.currency_code, 'USD') as currency_code,
							COALESCE(c.symbol, '$') as currency_symbol,
							COALESCE(e.exchange_rate, 1.000000) as exchange_rate,
							COALESCE(e.base_currency_amount, e.expense_amt) as base_currency_amount
							FROM db_expense e
							LEFT JOIN db_currency c ON e.expense_currency_id = c.id
							WHERE e.store_id = ".get_current_store_id()." 
							$from_date_query_expense
							$to_date_query_expense 
							$users_query 					

							ORDER BY payment_date,created_time ASC
							

							";
		//echo $query;exit;
		return $query;

	}

	
	function get_datatables()
	{

		
		$query = $this->_get_datatables_query();

		if($_POST['length'] != -1){
			$start_end_query = " limit  ".$_POST['start'].", ".$_POST['length']." ";
			$query .= $start_end_query;
		}
		//echo $query;exit;
		$query = $this->db->query($query);

		return $query->result();
	}

	function count_filtered()
	{
		$query = $this->_get_datatables_query();
		$query = $this->db->query($query);
		return $query->num_rows();
	}

	public function count_all()
	{
		//$this->db->where("store_id",get_current_store_id());
		//$this->db->from($this->table);
		//return $this->db->count_all_results();
		$query = $this->_get_datatables_query();
		$query = $this->db->query($query);
		return $query->num_rows();
	}

	public function link_account(){
		extract((array_merge($this->data,$_POST,$_GET)));
		//print_r($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));exit();
    	
		// Log the extracted variables
		log_message('debug', 'Link Account - account_of: ' . (isset($account_of) ? $account_of : 'not set'));
		log_message('debug', 'Link Account - rec_id: ' . (isset($rec_id) ? $rec_id : 'not set'));
		log_message('debug', 'Link Account - account_id: ' . (isset($account_id) ? $account_id : 'not set'));
		
		$this->db->trans_begin();

		if($account_of==1){
			$get_details = $this->db->select("*")->where("id",$rec_id)->get("db_salespayments")->row();

			//Sales Payments
			$transaction_type = 'SALES PAYMENT';
			$debit_account_id = null;
			$credit_account_id = $account_id;
			$debit_amt = 0;
			$credit_amt = $get_details->payment;
			$customer_id = $get_details->customer_id;
			$supplier_id = null;
			$payment_code=get_init_code('sales_payment');
			$payment_note = $get_details->payment_note;
			$transaction_date = $get_details->payment_date;

			//ACCOUNT RESET
			$this->db->where("ref_salespayments_id in ($rec_id)");
			$this->db->select("debit_account_id,credit_account_id");
			$this->db->group_by("debit_account_id,credit_account_id");
			$reset_accounts = $this->db->get("ac_transactions");
			//ACCOUNT RESET END

			//Delete that account trasaction only
			$this->db->where("ref_salespayments_id",$rec_id)->delete("ac_transactions");

			//Updates Sales Payment Account ID
			$this->db->set("account_id",$account_id)->where("id",$rec_id)->update("db_salespayments");
		}
		if($account_of==2){
			$get_details = $this->db->select("*")->where("id",$rec_id)->get("db_purchasepayments")->row();

			//Purchase Payments
			$transaction_type = 'PURCHASE PAYMENT';
			$debit_account_id = $account_id;
			$credit_account_id = null;
			$debit_amt = $get_details->payment;
			$credit_amt = 0;
			$customer_id = null;
			$supplier_id = $get_details->supplier_id;
			$payment_code=get_init_code('purchase_payment');
			$payment_note = $get_details->payment_note;
			$transaction_date = $get_details->payment_date;

			//ACCOUNT RESET
			$this->db->where("ref_purchasepayments_id in ($rec_id)");
			$this->db->select("debit_account_id,credit_account_id");
			$this->db->group_by("debit_account_id,credit_account_id");
			$reset_accounts = $this->db->get("ac_transactions");
			//ACCOUNT RESET END

			//Delete that account trasaction only
			$this->db->where("ref_purchasepayments_id",$rec_id)->delete("ac_transactions");

			//Updates Purchase Payment Account ID
			$this->db->set("account_id",$account_id)->where("id",$rec_id)->update("db_purchasepayments");

		}
		if($account_of==3){
			$get_details = $this->db->select("*")->where("id",$rec_id)->get("db_salespaymentsreturn")->row();

			//Sales Return Payments
			$transaction_type = 'SALES PAYMENT RETURN';
			$debit_account_id = $account_id;
			$credit_account_id = null;
			$debit_amt = $get_details->payment;
			$credit_amt = 0;
			$customer_id = $get_details->customer_id;
			$supplier_id = null;
			$payment_code=get_init_code('sales_return_payment');
			$payment_note = $get_details->payment_note;
			$transaction_date = $get_details->payment_date;

			//ACCOUNT RESET
			$this->db->where("ref_salespaymentsreturn_id in ($rec_id)");
			$this->db->select("debit_account_id,credit_account_id");
			$this->db->group_by("debit_account_id,credit_account_id");
			$reset_accounts = $this->db->get("ac_transactions");
			//ACCOUNT RESET END

			//Delete that account trasaction only
			$this->db->where("ref_salespaymentsreturn_id",$rec_id)->delete("ac_transactions");

			//Updates SAles Payment return Account ID
			$this->db->set("account_id",$account_id)->where("id",$rec_id)->update("db_salespaymentsreturn");

		}
		if($account_of==4){
			$get_details = $this->db->select("*")->where("id",$rec_id)->get("db_purchasepaymentsreturn")->row();

			//Purchase Return Payments
			$transaction_type = 'PURCHASE PAYMENT RETURN';
			$debit_account_id = null;
			$credit_account_id = $account_id;
			$debit_amt = 0;
			$credit_amt = $get_details->payment;
			$customer_id = null;
			$supplier_id = $get_details->supplier_id;
			$payment_code=get_init_code('purchase_return_payment');
			$payment_note = $get_details->payment_note;
			$transaction_date = $get_details->payment_date;

			//ACCOUNT RESET
			$this->db->where("ref_purchasepaymentsreturn_id in ($rec_id)");
			$this->db->select("debit_account_id,credit_account_id");
			$this->db->group_by("debit_account_id,credit_account_id");
			$reset_accounts = $this->db->get("ac_transactions");
			//ACCOUNT RESET END

			//Delete that account trasaction only
			$this->db->where("ref_purchasepaymentsreturn_id",$rec_id)->delete("ac_transactions");

			//Updates purchase Payment return Account ID
			$this->db->set("account_id",$account_id)->where("id",$rec_id)->update("db_purchasepaymentsreturn");

		}
		if($account_of==5){
			$get_details = $this->db->select("*")->where("id",$rec_id)->get("db_expense")->row();

			//Expenses
			$transaction_type = 'EXPENSE PAYMENT';
			$debit_account_id = $account_id;
			$credit_account_id = null;
			$debit_amt = $get_details->expense_amt;
			$credit_amt = 0;
			$customer_id = null;
			$supplier_id = null;
			$payment_code=get_init_code('expense_payment');
			$payment_note = $get_details->note;
			$transaction_date = $get_details->expense_date;

			//ACCOUNT RESET
			$this->db->where("ref_expense_id in ($rec_id)");
			$this->db->select("debit_account_id,credit_account_id");
			$this->db->group_by("debit_account_id,credit_account_id");
			$reset_accounts = $this->db->get("ac_transactions");
			//ACCOUNT RESET END

			//Delete that account trasaction only
			$this->db->where("ref_expense_id",$rec_id)->delete("ac_transactions");

			//Updates expense Payment Account ID
			$this->db->set("account_id",$account_id)->where("id",$rec_id)->update("db_expense");
		}


		//ACCOUNT RESET
        if($reset_accounts->num_rows()>0){
        	foreach ($reset_accounts->result() as $res1) {
        		if(!update_account_balance($res1->debit_account_id)){
					return 'failed';
				}

				if(!update_account_balance($res1->credit_account_id)){
					return 'failed';
				}

        	}
        }
        //ACCOUNT RESET END


		//Set the payment to specified account
		if(!empty($account_id)){
			// Get account currency for proper conversion
			$account_currency = $this->db->select('currency_id')->where('id', $account_id)->get('ac_accounts')->row();
			$account_currency_id = $account_currency ? $account_currency->currency_id : get_base_currency_id();
			
			// Get transaction currency (from original transaction)
			$transaction_currency_id = get_base_currency_id();
			$exchange_rate = 1.000000;
			$base_currency_amount = $debit_amt + $credit_amt;
			
			// For expenses, get currency info from expense record
			if($account_of == 5) {
				$expense_currency = $this->db->select('expense_currency_id, exchange_rate, base_currency_amount')
					->where('id', $rec_id)->get('db_expense')->row();
				if($expense_currency) {
					$transaction_currency_id = $expense_currency->expense_currency_id ?: get_base_currency_id();
					$exchange_rate = $expense_currency->exchange_rate ?: 1.000000;
					$base_currency_amount = $expense_currency->base_currency_amount ?: ($debit_amt + $credit_amt);
				}
			}
			
			// Calculate conversion if currencies are different
			if($transaction_currency_id != $account_currency_id) {
				$conversion_rate = get_exchange_rate($transaction_currency_id, $account_currency_id);
				if($conversion_rate !== false && $conversion_rate > 0) {
					$exchange_rate = $conversion_rate;
					// Convert the transaction amount to account currency
					$converted_debit_amt = $debit_amt * $conversion_rate;
					$converted_credit_amt = $credit_amt * $conversion_rate;
					$base_currency_amount = $converted_debit_amt + $converted_credit_amt;
					
					// Update the amounts to use converted values
					$debit_amt = $converted_debit_amt;
					$credit_amt = $converted_credit_amt;
				} else {
					// If no exchange rate found, log error and use original amounts
					log_message('error', 'No exchange rate found for conversion from currency ' . $transaction_currency_id . ' to ' . $account_currency_id);
					$exchange_rate = 1.000000;
				}
			}
			
			// Log transaction data for debugging
			log_message('debug', 'Linking account - Transaction Type: ' . $transaction_type . ', Account ID: ' . $account_id);
			log_message('debug', 'Debit: ' . $debit_amt . ', Credit: ' . $credit_amt . ', Currency: ' . $transaction_currency_id);
			
			//ACCOUNT INSERT
			$insert_bit = insert_account_transaction(array(
														'transaction_type'  		=> $transaction_type,
														'reference_table_id'  		=> $rec_id,
														'debit_account_id'  		=> $debit_account_id,
														'credit_account_id'  		=> $credit_account_id,
														'debit_amt'  				=> $debit_amt,
														'credit_amt'  				=> $credit_amt,
														'transaction_currency_id' 	=> $transaction_currency_id,
														'exchange_rate' 			=> $exchange_rate,
														'base_currency_debit_amt' 	=> $debit_amt * $exchange_rate,
														'base_currency_credit_amt' 	=> $credit_amt * $exchange_rate,
														'process'  					=> 'SAVE',
														'note'  					=> $payment_note,
														'transaction_date'  		=> $transaction_date,
														'payment_code'  			=> $payment_code,
														'customer_id'  				=> $customer_id,
														'supplier_id'  				=> $supplier_id,
												));
			if(!$insert_bit){
				log_message('error', 'Failed to insert account transaction for account linking');
				return "failed";
			}
		}
		//end
		

		
		
		

		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			log_message('error', 'Transaction rolled back - Database error occurred');
			return "failed";
		} else {
			$this->db->trans_commit();
			log_message('debug', 'Account linking transaction committed successfully');
			return "success";
		}

	}

	
}
