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

class Mmain extends CI_Model
{

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

	public function getAllTransactions()
	{
		$this->db->select('transaction.*');
		$query = $this->db->get('transaction');
		return $query->result_array();
	}

	public function saveTransaction($data)
	{
		$this->db->insert('transaction', $data);
		return $this->db->insert_id();
	}
	public function searchTransactions($criteria) {
		$this->db->select('transaction.*');
        if ($criteria['from'] && $criteria['to']) {
            $from_date = DateTime::createFromFormat('d-m-Y', $criteria['from'])->format('Y-m-d');
            $to_date = DateTime::createFromFormat('d-m-Y', $criteria['to'])->format('Y-m-d');
            $this->db->where('trip_date >=', $from_date);
            $this->db->where('trip_date <=', $to_date);
        }
		// echo '<pre>'; print_r($criteria); die;
        if (!empty($criteria['taxi_number'])) {
            $this->db->where('taxi_number', $criteria['taxi_number']);
        }

        if (!empty($criteria['terminal_id'])) {
            $this->db->where('terminal_id', $criteria['terminal_id']);
        }

        if (!empty($criteria['driver_id'])) {
            $this->db->where('driver_id', $criteria['driver_id']);
        }

        $query = $this->db->get('transaction');
        return $query->result_array();
    }
	public function getUniqueTaxiNumbers() {
        $this->db->distinct();
        $this->db->select('taxi_number');
		$this->db->order_by('taxi_number', 'ASC');
        $query = $this->db->get('transaction'); // Replace 'your_table_name' with the actual table name
        return $query->result_array();
    }
	public function getUniqueTerminals() {
        $this->db->distinct();
        $this->db->select('terminal_id');
		$this->db->order_by('terminal_id', 'ASC');
        $query = $this->db->get('transaction'); // Replace 'your_table_name' with the actual table name
        return $query->result_array();
    }
	public function getUniqueDriverIds() {
        $this->db->distinct();
        $this->db->select('driver_id');
		$this->db->order_by('driver_id', 'ASC');
        $query = $this->db->get('transaction'); // Replace 'your_table_name' with the actual table name
        return $query->result_array();
    }

	public function getTransactionsData($criteria)
	{
		if ($criteria['from'] && $criteria['to']) {
            $from_date = DateTime::createFromFormat('d-m-Y', $criteria['from'])->format('Y-m-d');
            $to_date = DateTime::createFromFormat('d-m-Y', $criteria['to'])->format('Y-m-d');
			$where = " WHERE trip_date >='". $from_date ."' AND trip_date <= '". $to_date ."'";
		} else {
			$where = '';
		}
		$result = array();
 
        $sql = "SELECT taxi_number, batch_no, transaction_type, trip_date, terminal_id, driver_id, SUM(amount) AS tot_amt, SUM(subsidy) AS subsidy_tot_amt, SUM(commission) AS com_tot_amt FROM transaction  ".$where." GROUP BY taxi_number,batch_no,terminal_id ORDER BY trip_date DESC";
		// print_r($sql);die;
        $query = $this->db->query($sql);
		$rows = $query->result_array();	        
 
        $i = 0;
        foreach($rows as $row){
            $sql1 = "SELECT transaction_id, file_type, taxi_number, transaction.batch_no, transaction_type, trip_date, trip_time, rrn, terminal_id, driver_id, amount, balance, commission, subsidy, br.posting_date, br.reference, uploaded_by, uploaded_ts FROM transaction JOIN batch_reference AS br ON transaction.batch_no = br.batch_no WHERE taxi_number = '".$row['taxi_number']."' AND transaction.batch_no = '".$row['batch_no']."' AND terminal_id = '".$row['terminal_id']."'";

			// print_r($sql1);die;
			
            $query1 = $this->db->query($sql1);
		    $rows1 = $query1->result_array();
 
            $result[$i] = $row;
            $result[$i]['transactionDetails'] = $rows1;
 
            $i++;
        }
 
        //echo "<pre>"; print_r($result); die;
 
        if($result){
            return $result;
        } else {
            return false;
        }
	}

	public function saveTransactionLog($data)
	{
		$this->db->insert('transaction_log', $data);
		return $this->db->insert_id();
	}

	public function getTransactionLogData()
	{

		$sql = "SELECT
					file_date,
					master_admin.full_name AS uploaded_by_name,
					MAX(CASE WHEN file_type = 'a' THEN file_name END) AS file_name_a,
					MAX(CASE WHEN file_type = 'b' THEN file_name END) AS file_name_b,
					MAX(CASE WHEN file_type = 'c' THEN file_name END) AS file_name_c,
					MAX(CASE WHEN file_type = 'a' THEN uploaded_ts END) AS uploaded_ts_a,
					MAX(CASE WHEN file_type = 'b' THEN uploaded_ts END) AS uploaded_ts_b,
					MAX(CASE WHEN file_type = 'c' THEN uploaded_ts END) AS uploaded_ts_c,
					MAX(CASE WHEN file_type = 'a' THEN file_path END) AS file_path_a,
					MAX(CASE WHEN file_type = 'b' THEN file_path END) AS file_path_b,
					MAX(CASE WHEN file_type = 'c' THEN file_path END) AS file_path_c
				FROM
					transaction_log
				JOIN master_admin ON master_admin.user_id = transaction_log.uploaded_by
					-- WHERE date(file_date) = '2024-10-05'
				GROUP BY
					file_date
				ORDER BY file_date DESC";

		$query = $this->db->query($sql);
		// echo $this->db->last_query();die;
		return $query->result_array();
	}
	public function searchTransactionLogData($criteria)
	{
		if ($criteria['from'] && $criteria['to']) {
            $from_date = DateTime::createFromFormat('d-m-Y', $criteria['from'])->format('Y-m-d');
            $to_date = DateTime::createFromFormat('d-m-Y', $criteria['to'])->format('Y-m-d');
			$where = " WHERE file_date >='". $from_date ."' AND file_date <= '". $to_date ."'";
		} else {
			$where = '';
		}
		$sql = "SELECT
					file_date,
					master_admin.full_name AS uploaded_by_name,
					MAX(CASE WHEN file_type = 'a' THEN file_name END) AS file_name_a,
					MAX(CASE WHEN file_type = 'b' THEN file_name END) AS file_name_b,
					MAX(CASE WHEN file_type = 'c' THEN file_name END) AS file_name_c,
					MAX(CASE WHEN file_type = 'a' THEN uploaded_ts END) AS uploaded_ts_a,
					MAX(CASE WHEN file_type = 'b' THEN uploaded_ts END) AS uploaded_ts_b,
					MAX(CASE WHEN file_type = 'c' THEN uploaded_ts END) AS uploaded_ts_c,
					MAX(CASE WHEN file_type = 'a' THEN file_path END) AS file_path_a,
					MAX(CASE WHEN file_type = 'b' THEN file_path END) AS file_path_b,
					MAX(CASE WHEN file_type = 'c' THEN file_path END) AS file_path_c
				FROM
					transaction_log 
				JOIN master_admin ON master_admin.user_id = transaction_log.uploaded_by
					".$where."
					
				GROUP BY
					file_date
				ORDER BY file_date DESC";
		
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	public function checkTransactionLogAlreadyExists($data)
	{
		$this->db->select('transaction_log.*');
		$this->db->where('file_type', $data['file_type']);
		$this->db->where('file_date', $data['file_date']);
		$query = $this->db->get('transaction_log');
		return $query->num_rows();
	}

	public function getNegativeTransactionsData()
	{
		$this->db->select('transaction.*');
		$this->db->where('balance <', 0);
		$query = $this->db->get('transaction');
		return $query->result_array();
	}
	public function searchNegativeTransactions($criteria) {
		$this->db->select('transaction.*');
        if ($criteria['from'] && $criteria['to']) {
            $from_date = DateTime::createFromFormat('d-m-Y', $criteria['from'])->format('Y-m-d');
            $to_date = DateTime::createFromFormat('d-m-Y', $criteria['to'])->format('Y-m-d');
            $this->db->where('trip_date >=', $from_date);
            $this->db->where('trip_date <=', $to_date);
        }
		$this->db->where('balance <', 0);
        $query = $this->db->get('transaction');
		// echo $this->db->last_query(); die;
        return $query->result_array();
    }
	public function getTotalNumberOfTransactions()
	{
		$this->db->select('transaction.*');
		// $this->db->where('balance <', 0);
		$query = $this->db->get('transaction');
		return $query->num_rows();
	}
	public function getTotalNumberOfNegativeTransactions()
	{
		$this->db->select('transaction.*');
		$this->db->where('amount <', 0);
		$query = $this->db->get('transaction');
		return $query->num_rows();
	}
	public function getTotalNumberOfSettledNegativeTransactions()
	{
		$this->db->select('transaction.*');
		$this->db->where('amount <', 0);
		$this->db->where('balance', 0.00);
		$query = $this->db->get('transaction');
		return $query->num_rows();
	}
	public function getTotalNumberOfUnsettledNegativeTransactions()
	{
		$this->db->select('transaction.*');
		$this->db->where('amount <', 0);
		$this->db->where('balance <', 0);
		$query = $this->db->get('transaction');
		return $query->num_rows();
	}

	public function getTransactionDataByTransactionId($transactionId)
	{
		$this->db->select('transaction.*');
		$this->db->where('transaction_id', $transactionId);
		$query = $this->db->get('transaction');
		// echo nl2br($this->db->last_query());die;
		return $query->row_array();
	}

	public function saveSettledTransactionData($data) {
        $this->db->insert('settled_transactions', $data);
        // echo $this->db->last_query(); die;
        return $this->db->insert_id();
    }
	public function save_batch_reference($data) {
        $this->db->insert('batch_reference', $data);
        // echo $this->db->last_query(); die;
        return $this->db->insert_id();
    }

	public function getSettledTransactionsData($criteria) {
		$this->db->select('t.*, sum(s.settled_amount) as settled_amount, s.settled_ts, s.remarks');
		$this->db->from('settled_transactions s');
		$this->db->join('transaction t', 's.transaction_id = t.transaction_id', 'LEFT');
		if ($criteria['from'] && $criteria['to']) {
            $from_date = DateTime::createFromFormat('d-m-Y', $criteria['from'])->format('Y-m-d');
            $to_date = DateTime::createFromFormat('d-m-Y', $criteria['to'])->format('Y-m-d');
            $this->db->where('t.trip_date >=', $from_date);
            $this->db->where('t.trip_date <=', $to_date);
        }
		$this->db->group_by('s.transaction_id');
		$this->db->order_by('DATE(s.settled_ts)', 'DESC');
		$query = $this->db->get();
		$rows = $query->result_array();	 

		$i = 0;
        foreach($rows as $row){
            $this->db->select('s.*, t.*');
			$this->db->from('settled_transactions s');
			$this->db->join('transaction t', 's.transaction_id = t.transaction_id', 'LEFT');
			$this->db->where('t.transaction_id', $row['transaction_id']);
			$query = $this->db->get();
			$rows1 = $query->result_array();	 
 
            $result[$i] = $row;
            $result[$i]['transactionDetails'] = $rows1;
 
            $i++;
        }
 
        // echo "<pre>"; print_r($result); die;
 
        if($result){
            return $result;
        } else {
            return false;
        }
    }
	public function searchSettledTransactionsData($criteria) {
		$this->db->select('s.*, t.*');
		$this->db->from('settled_transactions s');
		$this->db->join('transaction t', 's.transaction_id = t.transaction_id', 'LEFT');
		if ($criteria['from'] && $criteria['to']) {
            $from_date = DateTime::createFromFormat('d-m-Y', $criteria['from'])->format('Y-m-d');
            $to_date = DateTime::createFromFormat('d-m-Y', $criteria['to'])->format('Y-m-d');
            $this->db->where('t.trip_date >=', $from_date);
            $this->db->where('t.trip_date <=', $to_date);
        }
		$this->db->order_by('DATE(s.settled_ts)', 'DESC');
		$query = $this->db->get();
		// echo nl2br($this->db->last_query());die;
		return $query->result_array();
    }
	
	public function check_existing_batch_reference($batch_no) {
        $this->db->select('batch_reference.*');
        $this->db->where('batch_no', $batch_no);
        $query = $this->db->get('batch_reference');
        return $query->num_rows();
    }


}