<?php
class Report_Loan_Model extends CI_Model
{
	private $tableName = 'user';


	function __construct()
	{
		/* Call the Model constructor */
		parent::__construct();
		$this->load->database();
		$this->load->helper('url');
		//$this->load->library(array('email'));
	}

	/**
	 * Customer Rating Report: Rates customers based on loan performance (1=Excellent, 5=High Risk)
	 * @return array
	 */
	public function get_customer_ratings()
	{
		$date = date('Y-m-d'); // today

		$sql = "
        SELECT 
            c.cus_id,
            IF(bi.business_name = '', c.full_name, bi.business_name) AS customer_name,
            c.cus_mobile AS mobile_no,

            -- ✅ Last loan only
            l.loan_id AS last_loan_id,
            l.loan_ref,

            -- ✅ Count arrears of last loan
            (
                SELECT COUNT(*) 
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date < '$date'
                AND lt2.status != 'Paid'
            ) AS arrears,

            -- ✅ Rating according to arrears
            CASE 
                WHEN (
                    SELECT COUNT(*) 
                    FROM loan_terms lt2
                    WHERE lt2.loan_id = l.loan_id
                    AND lt2.term_date < '$date'
                    AND lt2.status != 'Paid'
                ) = 0 THEN 1
                WHEN (
                    SELECT COUNT(*) 
                    FROM loan_terms lt2
                    WHERE lt2.loan_id = l.loan_id
                    AND lt2.term_date < '$date'
                    AND lt2.status != 'Paid'
                ) = 3 THEN 2
                WHEN (
                    SELECT COUNT(*) 
                    FROM loan_terms lt2
                    WHERE lt2.loan_id = l.loan_id
                    AND lt2.term_date < '$date'
                    AND lt2.status != 'Paid'
                ) = 5 THEN 3
                ELSE 4
            END AS rating,

            -- ✅ Rating label
            CASE 
                WHEN (
                    SELECT COUNT(*) 
                    FROM loan_terms lt2
                    WHERE lt2.loan_id = l.loan_id
                    AND lt2.term_date < '$date'
                    AND lt2.status != 'Paid'
                ) = 0 THEN 'Excellent'
                WHEN (
                    SELECT COUNT(*) 
                    FROM loan_terms lt2
                    WHERE lt2.loan_id = l.loan_id
                    AND lt2.term_date < '$date'
                    AND lt2.status != 'Paid'
                ) = 3 THEN 'Good'
                WHEN (
                    SELECT COUNT(*) 
                    FROM loan_terms lt2
                    WHERE lt2.loan_id = l.loan_id
                    AND lt2.term_date < '$date'
                    AND lt2.status != 'Paid'
                ) = 5 THEN 'Average'
                ELSE 'High Risk'
            END AS rating_label

        FROM customer c
        LEFT JOIN business_information bi ON c.cus_id = bi.customer_id
        
        -- ✅ LAST LOAN ONLY
        LEFT JOIN loan l ON l.cus_id = c.cus_id 
        AND l.loan_id = (
            SELECT loan_id 
            FROM loan 
            WHERE cus_id = c.cus_id 
            ORDER BY loan_id DESC 
            LIMIT 1
        )

        WHERE l.loan_id IS NOT NULL
        ORDER BY rating ASC, customer_name ASC
    ";

		return $this->db->query($sql)->result_array();
	}


	/**
	 * Customers having birthday in the selected MONTH
	 * @param string $month  Format: YYYY-MM  (default: current month)
	 * @return array
	 */
	public function get_birthday_customers($month = null)
	{
		// Default: current month
		if (empty($month) || !preg_match('/^\d{4}-\d{2}$/', $month)) {
			$month = date('Y-m');
		}

		$month_num = (int)substr($month, 5, 2); // extract MM

		$sql = "
            SELECT 
                c.cus_id,
                IF(bi.business_name = '', c.full_name, bi.business_name) AS customer_name,
                c.cus_mobile,
                c.date_of_birth,
                TIMESTAMPDIFF(YEAR, c.date_of_birth, CURDATE()) AS age,
                DAY(c.date_of_birth) AS birthday_day
            FROM customer c
            LEFT JOIN business_information bi ON c.cus_id = bi.customer_id
            WHERE c.date_of_birth IS NOT NULL
              AND MONTH(c.date_of_birth) = ?
            ORDER BY birthday_day, c.full_name
        ";

		$query = $this->db->query($sql, [$month_num]);
		return $query->result_array();
	}


	/**
	 * Customers who have NOT taken a second loan after their last loan was fully paid.
	 *
	 * @param int|null $collector_id  Filter by collector (optional)
	 * @return array
	 */
	// In your model (e.g., Reports_loan_model.php)
	public function get_customers_no_repeat_loan()
	{
		// 1. Build the sub-query manually (CI2 has no get_compiled_select)
		$sub_sql = "
            SELECT l1.cus_id, l1.loan_id, l1.loan_ref, l1.loan_date, l1.loan_no_of_terms
            FROM loan l1
            INNER JOIN loan_terms lt1 ON l1.loan_id = lt1.loan_id
            WHERE lt1.status = 'Paid'
            GROUP BY l1.cus_id
            HAVING l1.loan_id = MAX(l1.loan_id)
        ";

		// 2. Main query
		$sql = "
            SELECT 
                c.cus_id,
                IF(bi.business_name = '', c.full_name, bi.business_name) AS customer_name,
                l.loan_ref AS last_loan_number,
                l.loan_date AS last_loan_date,
                l.loan_no_of_terms
            FROM customer c
            LEFT JOIN business_information bi ON c.cus_id = bi.customer_id
            INNER JOIN ($sub_sql) AS l ON c.cus_id = l.cus_id
            WHERE NOT EXISTS (
                SELECT 1 FROM loan l2
                WHERE l2.cus_id = c.cus_id
                  AND l2.loan_id > l.loan_id
            )
            ORDER BY c.cus_id
        ";

		$query = $this->db->query($sql);
		return $query->result_array();
	}

	public function get_recovery_report_old($collector_id = null, $date = null)
	{
		$date = $date ?: date('Y-m-d');
		$this->db->select("
            c.cus_id,
            IF(bi.business_name='', c.full_name, bi.business_name) AS customer_name,
            l.loan_ref AS loan_number,
            l.loan_date,
            l.loan_no_of_terms,
            
            (
                SELECT COALESCE(SUM(lt2.term_amount - lt2.paid_amount), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date <= '$date'
                AND lt2.status != 'Paid'
            ) AS total_outstanding,
            (
                SELECT COALESCE(SUM(lt2.term_amount - lt2.paid_amount), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date < '$date'
                AND lt2.status != 'Paid'
            ) AS arrears,
            (
                SELECT COALESCE(DATEDIFF('$date', MIN(lt2.term_date)), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date < '$date'
                AND lt2.status != 'Paid'
            ) AS missing_days,
            (
                SELECT COUNT(*)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date <= '$date'
                AND lt2.paid_amount < lt2.term_amount
                AND lt2.paid_amount > 0
            ) AS no_of_less_payments,
            (
                SELECT COUNT(*)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date <= '$date'
                AND lt2.status != 'Paid'
            ) AS pending_installments,
            (
                SELECT MIN(lt2.term_date)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date > '$date'
                AND lt2.status != 'Paid'
            ) AS next_due_date,
            l.route_id
        ", false);
		$this->db->from('customer c');
		$this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
		$this->db->join('loan l', 'c.cus_id = l.cus_id', 'left');
		$this->db->join('loan_terms lt', 'l.loan_id = lt.loan_id', 'left');
		if ($collector_id) {
			$this->db->join('user u', 'l.route_id = u.preferd_route');
			$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
			$this->db->where('ug.user_group_id', 4);
			$this->db->where('u.user_id', $collector_id);
		}
		$this->db->where('lt.status !=', 'Paid');
		$this->db->group_by('c.cus_id, l.loan_id');
		$this->db->order_by('c.cus_id, l.loan_ref');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function get_recovery_summary_old($collector_id = null, $date = null)
	{
		$date = $date ?: date('Y-m-d');
		$this->db->select("
            COALESCE(SUM(lt.term_amount - lt.paid_amount), 0) AS total_outstanding,
            COALESCE(SUM(CASE WHEN lt.term_date < '$date' THEN lt.term_amount - lt.paid_amount ELSE 0 END), 0) AS total_arrears
        ", false);
		$this->db->from('customer c');
		$this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
		$this->db->join('loan l', 'c.cus_id = l.cus_id', 'left');
		$this->db->join('loan_terms lt', 'l.loan_id = lt.loan_id', 'left');
		if ($collector_id) {
			$this->db->join('user u', 'l.route_id = u.preferd_route');
			$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
			$this->db->where('ug.user_group_id', 4);
			$this->db->where('u.user_id', $collector_id);
		}
		$this->db->where('lt.status !=', 'Paid');
		$query = $this->db->get();
		return $query->row_array();
	}

	public function get_customer_outstanding_arrears_summary($collector_id = null, $date = null)
	{
		$date = $date ?: date('Y-m-d');
		$this->db->select("
            COALESCE(SUM(lt.term_amount - lt.paid_amount), 0) AS total_outstanding,
            COALESCE(SUM(CASE WHEN lt.term_date < '$date' THEN lt.term_amount - lt.paid_amount ELSE 0 END), 0) AS total_arrears
        ", false);
		$this->db->from('customer c');
		$this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
		$this->db->join('loan l', 'c.cus_id = l.cus_id', 'left');
		$this->db->join('loan_terms lt', 'l.loan_id = lt.loan_id', 'left');
		if ($collector_id) {
			$this->db->join('user u', 'l.route_id = u.preferd_route');
			$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
			$this->db->where('ug.user_group_id', 4);
			$this->db->where('u.user_id', $collector_id);
		}
		$this->db->where('lt.status !=', 'Paid');
		$query = $this->db->get();
		return $query->row_array();
	}

	public function get_customer_outstanding_arrears_report($collector_id = null, $date = null)
	{
		$date = $date ?: date('Y-m-d'); // Default to current date
		$this->db->select("
            c.cus_id,
			c.cus_code,
            IF(bi.business_name='', c.full_name, bi.business_name) AS customer_name,
            l.loan_ref AS loan_number,
            l.loan_term_amount,
			l.loan_tot_amount,
            l.loan_no_of_terms,
            (
                SELECT COALESCE(SUM(lt2.term_amount - lt2.paid_amount), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date <= '$date'
                AND lt2.status != 'Paid'
            ) AS total_outstanding,
            (
                SELECT COALESCE(SUM(lt2.term_amount - lt2.paid_amount), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date < '$date'
                AND lt2.status != 'Paid'
            ) AS arrears,
            l.route_id
        ", false);
		$this->db->from('customer c');
		$this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
		$this->db->join('loan l', 'c.cus_id = l.cus_id', 'left');
		$this->db->join('loan_terms lt', 'l.loan_id = lt.loan_id', 'left');
		if ($collector_id) {
			$this->db->join('user u', 'l.route_id = u.preferd_route');
			$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
			$this->db->where('ug.user_group_id', 4); // CASHCOLLECOTOR
			$this->db->where('u.user_id', $collector_id);
		}
		$this->db->where('lt.status !=', 'Paid'); // Only include loans with unpaid terms
		$this->db->group_by('c.cus_id, l.loan_id'); // Group by customer and loan
		$this->db->order_by('c.cus_id, l.loan_ref');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function get_collection_report($collector_id, $date)
	{
		$this->db->select("
            u.user_id,
            CONCAT(u.user_first_name, ' ', u.user_last_name) AS user,
            lt.term_date AS term_date,
            lt.paid_amount,
            lt.not_collected_reason,
            l.loan_ref AS loan_number,
            IF(bi.business_name='',c.full_name,bi.business_name) AS customer_name,
            lt.term_amount AS term_value,
            lt.term_no AS term_no,
            (
                SELECT COALESCE(SUM(lt2.term_amount - lt2.paid_amount), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date < '$date'
                AND lt2.status != 'Paid'
            ) AS previous_outstanding,
            l.route_id
        ", false);
		$this->db->from('user u');
		$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
		$this->db->join('loan l', 'l.route_id = u.preferd_route');
		$this->db->join('loan_terms lt', 'l.loan_id = lt.loan_id');
		$this->db->join('business_information bi', 'l.cus_id = bi.customer_id', 'left');
		$this->db->join('customer c', 'l.cus_id = c.cus_id', 'left');
		$this->db->join('user cu', 'l.cus_id = cu.user_id', 'left'); // Customer names from user table
		$this->db->where('ug.user_group_id', 4); // CASHCOLLECOTOR
		$this->db->where('lt.term_date', $date);
		$this->db->where('lt.status !=', 'Paid'); // Unpaid terms due today
		if ($collector_id) {
			$this->db->where('u.user_id', $collector_id);
		}
		$this->db->order_by('l.route_id, l.loan_ref');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function get_collection_summary($collector_id, $date)
	{
		$this->db->select("
            COALESCE(SUM(lt.paid_amount), 0) AS total_paid,
            COALESCE(SUM(lt.penalty_amount), 0) AS panalty_received
        ", false);
		$this->db->from('user u');
		$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
		$this->db->join('loan l', 'l.route_id = u.preferd_route');
		$this->db->join('loan_terms lt', 'l.loan_id = lt.loan_id');
		$this->db->where('ug.user_group_id', 4);
		$this->db->where('lt.term_date', $date);
		$this->db->where('lt.status !=', 'Paid');
		if ($collector_id) {
			$this->db->where('u.user_id', $collector_id);
		}
		$query = $this->db->get();
		return $query->row_array();
	}

	public function get_collectors()
	{
		$this->db->select("u.user_id, CONCAT(u.user_first_name, ' ', u.user_last_name) AS user_first_name, ug.user_group_name", false);
		$this->db->from('user u');
		$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
		$this->db->where('ug.user_group_id', 4);
		$query = $this->db->get();
		return $query->result();
	}


	public function get_customer_analysis($cus_id = null)
	{
		$this->db->select("
        c.cus_id, c.full_name, 
        COUNT(DISTINCT l.loan_id) AS total_loans,
        IFNULL(SUM(l.loan_tot_amount), 0) AS total_loan_amount,
        IFNULL(SUM(p.pymnt_amount), 0) AS total_paid,
        IFNULL(SUM(lt.penalty_amount), 0) AS total_penalties,
        IFNULL(SUM(lt.term_amount - lt.paid_amount), 0) AS outstanding
    ", false);
		$this->db->from("customer c");
		$this->db->join("loan l", "l.cus_id = c.cus_id", "left");
		$this->db->join("loan_payment p", "p.loan_id = l.loan_id", "left");
		$this->db->join("loan_terms lt", "lt.loan_id = l.loan_id", "left");

		if ($cus_id) {
			$this->db->where("c.cus_id", $cus_id);
		}

		$this->db->group_by("c.cus_id");
		return $this->db->get()->result_array();
	}





	//---------------------------------------------
	//REF : loan instalment report
	public function get_all_loans()
	{
		$this->db->select('loan.loan_id,loan.loan_ref,loan.loan_tot_amount,loan.is_closed');
		$this->db->select("IF(
        bi.business_name IS NULL OR bi.business_name = '',
        c.cus_name,
        bi.business_name
    ) AS cus_name, c.cus_nic", false);
		$this->db->from('loan');
		$this->db->join('customer c', 'c.cus_id = loan.cus_id', 'left');
		$this->db->join('business_information bi', 'loan.cus_id = bi.customer_id', 'left');
		$this->db->where("loan.approval_status", 1);
		$this->db->where("loan.is_closed", 0);
		$query = $this->db->get();
		return $query->result_array();
	}

	function get_loan_installment_summary_cash_loan_wise($wareh_id, $loan_id, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('b.loan_received');
		$this->db->select_sum('b.capital_received');
		$this->db->select_sum('b.loan_interest_recived');
		$this->db->select_sum('b.panalty_received');
		$this->db->select_sum('b.due_received');
		$this->db->select_sum('b.excess_received');
		$this->db->select_sum('b.pymnt_amount');
		$this->db->from('loan_payment b');
		//$this->db->where("b.pymnt_paying_by","Cash");
		$this->db->where("b.pymnt_collected", 1);
		$this->db->where("b.pymnt_returned", 0);


		$this->db->where("b.loan_id", $loan_id);


		if ($srh_from_date) {
			$this->db->where("b.pymnt_date_time >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.pymnt_date_time <=", $srh_to_date);
		}


		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}


	function get_loan_installment_list_loan_wise($wareh_id, $loan_id, $srh_from_date, $srh_to_date)
	{
		$this->db->select('c.cus_name');
		$this->db->select('l.loan_ref');
		$this->db->select('b.*');
		$this->db->select('u.user_first_name');
		$this->db->select('pt.payment_term_name');
		$this->db->select('r.route_name');
		$this->db->from('loan_payment b');
		$this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
		$this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
		$this->db->join('loan l', 'l.loan_id = b.loan_id', 'left');
		$this->db->join('routes r', 'r.route_id = b.route_id', 'left');
		$this->db->join('user u', 'u.user_id = b.user_id', 'left');
		$this->db->join('mstr_payment_terms pt', 'pt.id = b.payment_term', 'left');


		//$this->db->where("b.pymnt_paying_by","Cash");
		$this->db->where("b.pymnt_collected", 1);
		// $this->db->where("b.pymnt_returned",0);	   
		$this->db->where("b.loan_id", $loan_id);

		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id);
		}

		if ($srh_from_date) {
			$this->db->where("b.pymnt_date_time >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.pymnt_date_time <=", $srh_to_date);
		}
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	//--------------------------------------------------------------
	//REF : Not Close Report   
	function get_not_close_loan_summary($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('b.total_interest');
		$this->db->select_sum('b.loan_tot_amount');
		$this->db->select_sum('b.loan_payable_total_amount');
		$this->db->from('loan b');
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_close_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_close_date <=", $srh_to_date);
		} else {
			$this->db->where("b.loan_close_date <=", "0000-00-00");
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id);
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id);
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term);
		}
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}



	function get_not_close_loan_summary_panalty($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('lp.panalty_amount');
		$this->db->from('loan_panalty lp');
		$this->db->join('loan b', 'lp.loan_id = b.loan_id', 'inner');
		$this->db->where("lp.is_removed", 0);
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_close_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_close_date <=", $srh_to_date);
		} else {
			$this->db->where("b.loan_close_date <=", "0000-00-00");
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id);
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id);
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term);
		}

		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}

	function get_not_close_loan_summary_due($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('lp.due_amount');
		$this->db->from('loan_due_amount lp');
		$this->db->join('loan b', 'lp.loan_id = b.loan_id', 'left');
		$this->db->where("lp.is_removed", 0);
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_close_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_close_date <=", $srh_to_date);
		} else {
			$this->db->where("b.loan_close_date <=", "0000-00-00");
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id);
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id);
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term);
		}
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}

	function get_not_close_loan_paid_summary($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('lp.loan_received');
		$this->db->select_sum('lp.capital_received');
		$this->db->select_sum('lp.loan_interest_recived');
		$this->db->select_sum('lp.panalty_received');
		$this->db->select_sum('lp.due_received');
		$this->db->select_sum('lp.excess_received');
		$this->db->select_sum('lp.pymnt_amount');
		$this->db->from('loan_payment lp');
		$this->db->join('loan b', 'lp.loan_id = b.loan_id', 'inner');
		$this->db->where("lp.pymnt_collected", 1);
		$this->db->where("lp.pymnt_returned", 0);
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_close_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_close_date <=", $srh_to_date);
		} else {
			$this->db->where("b.loan_close_date <=", "0000-00-00");
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id);
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id);
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term);
		}
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}

	function get_not_close_loan_list($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		// ✅ Define date range for next 2 weeks
		$today = date('Y-m-d');
		$next_2weeks = date('Y-m-d', strtotime('+14 days'));

		$this->db->select("b.*,IF(bi.business_name='', c.full_name, bi.business_name) AS cus_name,c.cus_code", false);
		$this->db->select('mpt.payment_term_name');
		$this->db->select('r.route_name');
		$this->db->from('loan b');
		$this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
		$this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
		$this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
		$this->db->join('routes r', 'r.route_id = b.route_id', 'left');

		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);

		// ✅ Closing date from today onwards
		$this->db->where("b.loan_close_date >=", $today);

		// ✅ Only up to next 14 days
		$this->db->where("b.loan_close_date <=", $next_2weeks);

		// ✅ Optional filters
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id);
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id);
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term);
		}

		// ✅ Sort by nearest closing date
		$this->db->order_by("b.loan_close_date", "asc");

		$query = $this->db->get();

		return ($query->num_rows() > 0) ? $query->result() : false;
	}


	//---------------------------------------------------------


	function get_not_close_loan_list_2($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select('b.*,c.cus_name');
		$this->db->select('mpt.payment_term_name');
		$this->db->select('r.route_name');
		$this->db->from('loan b');
		$this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
		$this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
		$this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
		$this->db->join('routes r', 'r.route_id = b.route_id', 'left');
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_date <=", $srh_to_date);
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id);
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id);
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term);
		}

		$this->db->order_by("b.loan_id", "desc");
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	//---------------------------------------------------------





	//----------------------------------------------------------
	//REF : Loan report
	function get_loan_summary($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('b.total_interest');
		$this->db->select_sum('b.loan_tot_amount');
		$this->db->select_sum('b.loan_payable_total_amount');
		$this->db->from('loan b');
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_date <=", $srh_to_date);
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id); //
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id); //
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term); //
		}
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}

	function get_loan_summary_panalty($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('lp.panalty_amount');
		$this->db->from('loan_panalty lp');
		$this->db->join('loan b', 'lp.loan_id = b.loan_id', 'inner');
		$this->db->where("lp.is_removed", 0);
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_date <=", $srh_to_date);
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id); //
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id); //
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term); //
		}
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}

	function get_loan_summary_due($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('lp.due_amount');
		$this->db->from('loan_due_amount lp');
		$this->db->join('loan b', 'lp.loan_id = b.loan_id', 'left');
		$this->db->where("lp.is_removed", 0);
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_date <=", $srh_to_date);
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id); //
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id); //
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term); //
		}
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}

	function get_loan_paid_summary($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select_sum('lp.loan_received');
		$this->db->select_sum('lp.capital_received');
		$this->db->select_sum('lp.loan_interest_recived');
		$this->db->select_sum('lp.panalty_received');
		$this->db->select_sum('lp.due_received');
		$this->db->select_sum('lp.excess_received');
		$this->db->select_sum('lp.pymnt_amount');
		$this->db->from('loan_payment lp');
		$this->db->join('loan b', 'lp.loan_id = b.loan_id', 'inner');
		$this->db->where("lp.pymnt_collected", 1);
		$this->db->where("lp.pymnt_returned", 0);
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_date <=", $srh_to_date);
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id); //
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id); //
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term); //
		}
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return false;
		}
	}

	//----------------------------------------------------------


	//----------------------------------------------------------
	public function get_user_summary($user_id, $date, $route)
	{
		$this->db->select('lp.paymnt_id,lp.pymnt_ref_no,lp.pymnt_amount,lp.pymnt_added_date_time,b.loan_ref');
		$this->db->from('loan_payment lp');
		$this->db->join('loan b', 'lp.loan_id = b.loan_id', 'left');
		$this->db->where("lp.pymnt_collected", 1);
		$this->db->where("lp.pymnt_deposited", 1);
		$this->db->where("lp.user_id", $user_id);
		$this->db->where("lp.pymnt_date_time", $date);
		$this->db->where("lp.route_id", $route);
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->result_array();
		} else {
			return false;
		}
	}

	//----------------------------------------------------------
	function getLoan_approved_issued($warehouse_id, $route, $payment_t)
	{
		$this->db->select("b.*,c.cus_phone,IF(bi.business_name='', c.full_name, bi.business_name) AS cus_name", false);
		$this->db->select('mpt.payment_term_name');
		$this->db->from('loan b');
		$this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
		$this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
		$this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
		$this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
		$approval_status_con = "b.approval_status =1";
		$is_closed_con = " AND b.is_closed =0";
		$loan_issue_status_con = " AND b.loan_issue_status =1";
		$loan_payment_term_con = "";
		$route_id_con = "";
		$warehouse_id_con = "";
		if ($warehouse_id) {
			$warehouse_id_con = " AND b.warehouse_id =" . $warehouse_id . " ";
		}
		if ($route) {
			$route_id_con = " AND b.route_id =" . $route . " ";
		}
		if ($payment_t) {
			$loan_payment_term_con = " AND b.loan_payment_term =" . $payment_t . " ";
		}
		$where_condition = $approval_status_con . $is_closed_con . $warehouse_id_con . $route_id_con . $loan_payment_term_con . $loan_issue_status_con;
		$this->db->where($where_condition);
		$this->db->order_by("c.cus_name", "desc");
		$query = $this->db->get();
		return $query->result();
	}



	//---------------------------------------------------------


	function get_expire_closing_date_loans($wareh_id, $route_id, $loan_payment_term, $srh_from_date, $srh_to_date)
	{
		$this->db->select('b.*,c.cus_name');
		$this->db->select('mpt.payment_term_name');
		$this->db->select('r.route_name');
		$this->db->from('loan b');
		//$this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
		$this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
		$this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
		$this->db->join('routes r', 'r.route_id = b.route_id', 'left');
		$this->db->where("b.approval_status", 1);
		$this->db->where("b.is_closed", 0);
		if ($srh_from_date) {
			$this->db->where("b.loan_close_date >=", $srh_from_date);
		}
		if ($srh_to_date) {
			$this->db->where("b.loan_close_date <=", $srh_to_date);
		}
		if ($wareh_id) {
			$this->db->where("b.warehouse_id", $wareh_id);
		}
		if ($route_id) {
			$this->db->where("b.route_id", $route_id);
		}
		if ($loan_payment_term) {
			$this->db->where("b.loan_payment_term", $loan_payment_term);
		}

		$this->db->order_by("b.loan_id", "desc");
		$query = $this->db->get();
		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	//---------------------------------------------------------
	//---------------------------------------------------------



	function currant_operation_loan_list($warehouse_id, $route, $payment_t, $srh_from_date, $srh_to_date, $start, $length, $search_key_val)
	{
		$this->db->select('b.*,c.cus_name,c.cus_address,c.cus_mobile');
		$this->db->select('mpt.payment_term_name');
		$this->db->select('r.route_name');
		$this->db->from('loan b');
		//$this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
		$this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
		$this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
		$this->db->join('routes r', 'r.route_id = b.route_id', 'left');
		$approval_status_con = "b.approval_status =1";
		$is_closed_con = " AND b.is_closed =0";

		$loan_payment_term_con = "";
		$route_id_con = "";
		$warehouse_id_con = "";
		$srh_from_date_con = "";
		$srh_to_date_con = "";

		if ($warehouse_id) {
			$warehouse_id_con = " AND b.warehouse_id =" . $warehouse_id . " ";
		}
		if ($route) {
			$route_id_con = " AND b.route_id =" . $route . " ";
		}
		if ($payment_t) {
			$loan_payment_term_con = " AND b.loan_payment_term =" . $payment_t . " ";
		}
		if ($srh_from_date) {
			$srh_from_date = date('Y-m-d', strtotime($srh_from_date));
			$srh_from_date_con = " AND b.loan_date >= '" . $srh_from_date . "' ";
		}
		if ($srh_to_date) {
			$srh_to_date = date('Y-m-d', strtotime($srh_to_date));
			$srh_to_date_con = " AND b.loan_date <= '" . $srh_to_date . "' ";
		}

		$where_condition = $approval_status_con . $is_closed_con . $warehouse_id_con . $route_id_con . $loan_payment_term_con . $srh_from_date_con .
			$srh_to_date_con;

		if ($search_key_val) {
			$this->db->where($where_condition . ' AND c.cus_code LIKE \'%' . $search_key_val . '%\'');
			$this->db->or_where($where_condition . ' AND c.cus_name LIKE \'%' . $search_key_val . '%\'');
			$this->db->or_where($where_condition . ' AND c.cus_nic LIKE \'%' . $search_key_val . '%\'');
			$this->db->or_where($where_condition . ' AND c.cus_mobile LIKE \'%' . $search_key_val . '%\'');
			$this->db->or_where($where_condition . ' AND b.loan_ref LIKE \'%' . $search_key_val . '%\'');
		} else {
			$this->db->where($where_condition);
		}
		$this->db->order_by("b.loan_id", "desc");
		if ($start != '' && $length != '') {
			$this->db->limit($length, $start);
			$query = $this->db->get();
			//print_r($this->db->last_query()); 
			return $query->result();
		} else {
			$query = $this->db->get();
			return  $query->num_rows();
		}
	}


	//---------------------------------------------------------
	public function get_recovery_report($collector_id = null, $date = null)
	{
		$date = $date ?: date('Y-m-d');
		$this->db->select("
            c.cus_id,
            IF(bi.business_name='', c.full_name, bi.business_name) AS customer_name,
            l.loan_ref AS loan_number,
            l.loan_date,
            l.loan_no_of_terms,
            
            (
                SELECT COALESCE(SUM(lt2.term_amount - lt2.paid_amount), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date <= '$date'
                AND lt2.status != 'Paid'
            ) AS total_outstanding,
            (
                SELECT COALESCE(SUM(lt2.term_amount - lt2.paid_amount), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date < '$date'
                AND lt2.status != 'Paid'
            ) AS arrears,
            (
                SELECT COALESCE(DATEDIFF('$date', MIN(lt2.term_date)), 0)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date < '$date'
                AND lt2.status != 'Paid'
            ) AS missing_days,
            (
                SELECT COUNT(*)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date <= '$date'
                AND lt2.paid_amount < lt2.term_amount
                AND lt2.paid_amount > 0
            ) AS no_of_less_payments,
            (
                SELECT COUNT(*)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date <= '$date'
                AND lt2.status != 'Paid'
            ) AS pending_installments,
            (
                SELECT MIN(lt2.term_date)
                FROM loan_terms lt2
                WHERE lt2.loan_id = l.loan_id
                AND lt2.term_date > '$date'
                AND lt2.status != 'Paid'
            ) AS next_due_date,
            l.route_id
        ", false);
		$this->db->from('customer c');
		$this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
		$this->db->join('loan l', 'c.cus_id = l.cus_id', 'left');
		$this->db->join('loan_terms lt', 'l.loan_id = lt.loan_id', 'left');
		if ($collector_id) {
			$this->db->join('user u', 'l.route_id = u.preferd_route');
			$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
			$this->db->where('ug.user_group_id', 5);
			$this->db->where('u.user_id', $collector_id);
		}

		// Add route filter
		if (!empty($route_id)) {
			$this->db->where('l.route_id', $route_id);
		}

		$this->db->where('lt.status !=', 'Paid');
		$this->db->group_by('c.cus_id, l.loan_id');
		$this->db->order_by('c.cus_id, l.loan_ref');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function get_recovery_summary($collector_id = null, $date = null)
	{
		$date = $date ?: date('Y-m-d');
		$this->db->select("
            COALESCE(SUM(lt.term_amount - lt.paid_amount), 0) AS total_outstanding,
            COALESCE(SUM(CASE WHEN lt.term_date < '$date' THEN lt.term_amount - lt.paid_amount ELSE 0 END), 0) AS total_arrears
        ", false);
		$this->db->from('customer c');
		$this->db->join('business_information bi', 'c.cus_id = bi.customer_id', 'left');
		$this->db->join('loan l', 'c.cus_id = l.cus_id', 'left');
		$this->db->join('loan_terms lt', 'l.loan_id = lt.loan_id', 'left');
		if ($collector_id) {
			$this->db->join('user u', 'l.route_id = u.preferd_route');
			$this->db->join('user_group ug', 'u.group_id = ug.user_group_id');
			$this->db->where('ug.user_group_id', 5);
			$this->db->where('u.user_id', $collector_id);
		}

		// Add route filter
		if (!empty($route_id)) {
			$this->db->where('l.route_id', $route_id);
		}

		$this->db->where('lt.status !=', 'Paid');
		$query = $this->db->get();
		return $query->row_array();
	}


	public function get_marketing_report($selected_month)
	{
		$sql = "
        SELECT 
            DATE_FORMAT(l.loan_date, '%Y-%m') AS month,
            l.loan_date,
            CONCAT(u.user_first_name, ' ', u.user_last_name) AS officer_name,
            bi.business_name AS customer_name,
            l.cus_id,
            l.loan_ref,
            l.loan_tot_amount,
            (l.loan_tot_amount - l.loan_payable_total_amount) AS interest_income,
            ((l.loan_tot_amount - l.loan_payable_total_amount) * (l.marketing_officer_discount / 100)) AS commission,
            ((l.loan_tot_amount - l.loan_payable_total_amount) - ((l.loan_tot_amount - l.loan_payable_total_amount) * (l.marketing_officer_discount / 100))) AS total_income
        FROM loan l
        INNER JOIN (
            SELECT 
                MIN(loan_id) AS first_loan_id,
                cus_id,
                marketing_officer_id
            FROM loan
            WHERE marketing_officer_id > 0
            GROUP BY cus_id, marketing_officer_id
        ) x ON l.loan_id = x.first_loan_id
        INNER JOIN user u ON u.user_id = l.marketing_officer_id
        LEFT JOIN business_information bi ON l.cus_id = bi.customer_id
        WHERE u.group_id = 5
          AND DATE_FORMAT(l.loan_date, '%Y-%m') = ?
        ORDER BY l.loan_date ASC
    ";

		$query = $this->db->query($sql, array($selected_month));
		return $query->result_array();
	}
	
	public function get_income_report($selected_month)
	{
		$sql = "
        SELECT 
            DATE_FORMAT(l.loan_date, '%Y-%m') AS month,
            l.loan_date,
            bi.business_name AS customer_name,
            l.cus_id,
            l.loan_ref,
            l.loan_tot_amount,
            (l.loan_tot_amount - l.loan_payable_total_amount) AS interest_income,
        FROM loan l
        INNER JOIN (
            SELECT 
                MIN(loan_id) AS first_loan_id,
                cus_id,
                marketing_officer_id
            FROM loan
            WHERE marketing_officer_id > 0
            GROUP BY cus_id, marketing_officer_id
        ) x ON l.loan_id = x.first_loan_id
        LEFT JOIN business_information bi ON l.cus_id = bi.customer_id
        WHERE u.group_id = 5
          AND DATE_FORMAT(l.loan_date, '%Y-%m') = ?
        ORDER BY l.loan_date ASC
    ";

		$query = $this->db->query($sql, array($selected_month));
		return $query->result_array();
	}
}
