Enhanced PHP File Manager

⬆️ Go Up (..)

📊 Server Information

Server:

LiteSpeed

System:

Linux 5.14.0-503.34.1.el9_5.x86_64

PHP Version:

8.2.30

User:

u329947844

Directory:

/home/u329947844/domains/hifi11.in/public_html/selvaganapathi

Free Space:

10.27 TB

🔧 Quick Actions

✏️ Editing: overall-reports.php

<?php
include 'includes/head.php';
include 'includes/db.php';

// Clear any pending results to fix "Commands out of sync" error
while ($conn->more_results()) {
    $conn->next_result();
    if ($result = $conn->store_result()) {
        $result->free();
    }
}

// Create monthly_balances table if it doesn't exist
function createMonthlyBalancesTable($conn) {
    $sql = "CREATE TABLE IF NOT EXISTS monthly_balances (
        id INT(11) AUTO_INCREMENT PRIMARY KEY,
        finance_id INT(11) NOT NULL,
        month INT(2) NOT NULL,
        year INT(4) NOT NULL,
        opening_balance DECIMAL(15,2) DEFAULT 0.00,
        closing_balance DECIMAL(15,2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_finance_month_year (finance_id, month, year)
    )";
   
    if ($conn->query($sql) === TRUE) {
        return true;
    } else {
        error_log("Error creating monthly_balances table: " . $conn->error);
        return false;
    }
}

// Initialize monthly_balances table
createMonthlyBalancesTable($conn);

// CORRECTED: Get total initial investment from database WITH FILTERS
function getTotalInitialInvestment($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    $sql = "SELECT SUM(amount) as total_investment FROM initial_investment WHERE finance_id = ?";
    $params = [$finance_id];
    $types = "i";
   
    if ($start_date && $end_date) {
        $sql .= " AND investment_date BETWEEN ? AND ?";
        $params[] = $start_date;
        $params[] = $end_date;
        $types .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql .= " AND YEAR(investment_date) = ? AND MONTH(investment_date) = ?";
        $params[] = $selected_year;
        $params[] = $selected_month;
        $types .= "ii";
        if ($selected_day > 0) {
            $sql .= " AND DAY(investment_date) = ?";
            $params[] = $selected_day;
            $types .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql .= " AND YEAR(investment_date) = ?";
        $params[] = $selected_year;
        $types .= "i";
    }
    // If both are 'all', no date filters applied
    
    $stmt = $conn->prepare($sql);
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->close();
    
    return $row['total_investment'] ?? 0.00;
}

// FIXED: Function to get or calculate previous month's closing balance
function getPreviousMonthClosingBalance($conn, $current_month, $current_year, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    
    $finance_id = 1;
    
    // If we're using custom date range, don't calculate monthly balance
    if ($start_date && $end_date) {
        return 0.00; // For custom date ranges, start from zero
    }
    
    // For specific month view: calculate cumulative balance up to previous month
    if ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        // FIXED: Handle January correctly - previous month is December of previous year
        if ($current_month == 1) {
            $prev_month = 12;
            $prev_year = $current_year - 1;
        } else {
            $prev_month = $current_month - 1;
            $prev_year = $current_year;
        }
        
        // Check if we have calculated balance for previous month
        try {
            $sql = "SELECT closing_balance FROM monthly_balances
                    WHERE finance_id = ? AND month = ? AND year = ?
                    ORDER BY id DESC LIMIT 1";
           
            $stmt = $conn->prepare($sql);
            $stmt->bind_param("iii", $finance_id, $prev_month, $prev_year);
            $stmt->execute();
            $result = $stmt->get_result();
            $row = $result->fetch_assoc();
            $stmt->close();
           
            if ($row && $row['closing_balance'] !== null) {
                return (float)$row['closing_balance'];
            }
           
            // If no record found, calculate it recursively
            return calculateMonthClosingBalance($conn, $prev_month, $prev_year);
           
        } catch (Exception $e) {
            error_log("Error getting previous month balance: " . $e->getMessage());
            // If any error, calculate it recursively
            return calculateMonthClosingBalance($conn, $prev_month, $prev_year);
        }
    }
    
    return 0.00; // Default for other cases
}

// FIXED: Function to calculate closing balance for a specific month
function calculateMonthClosingBalance($conn, $month, $year) {
    $finance_id = 1;
    
    // Get previous month's closing balance recursively
    $previous_balance = getPreviousMonthClosingBalance($conn, $month, $year, '', '', $month, $year, 0);
   
    // Calculate current month's transactions
    $emiDetails = getTotalPrincipalAndInterest($conn, '', '', $month, $year, 0);
    $regularEmiPrincipal = (float)str_replace(',', '', $emiDetails['regular_principal']);
    $regularEmiInterest = (float)str_replace(',', '', $emiDetails['regular_interest']);
    $regularOverdueCollected = (float)str_replace(',', '', getTotalOverdueChargesCollected($conn, '', '', $month, $year, 0));
    $documentCharges = (float)str_replace(',', '', getTotalDocumentCharges($conn, '', '', $month, $year, 0));
    $foreclosureDetails = getTotalForeclosureCollected($conn, '', '', $month, $year, 0);
    $expenses = (float)str_replace(',', '', getTotalExpenses($conn, '', '', $month, $year, 0));
    $loansIssued = (float)str_replace(',', '', getTotalLoanIssued($conn, '', '', $month, $year, 0));
    
    // Get total excess payments for this month
    $excessPayments = (float)str_replace(',', '', getTotalExcessPayments($conn, '', '', $month, $year, 0));
    
    // Get total investment for this specific month
    $monthInvestment = getTotalInitialInvestment($conn, '', '', $month, $year, 0);
   
    // Calculate closing balance
    $regularEmiIncome = $regularEmiPrincipal + $regularEmiInterest + $regularOverdueCollected;
    $foreclosureIncome = (float)str_replace(',', '', $foreclosureDetails['total_amount']);
    $totalIncome = $regularEmiIncome  + $documentCharges + $excessPayments;
    
    $closing_balance = $previous_balance + $monthInvestment + $totalIncome - $expenses - $loansIssued;
   
    // Store in monthly_balances table for future reference
    storeMonthlyBalance($conn, $month, $year, $previous_balance, $closing_balance);
   
    return $closing_balance;
}

// Function to store monthly balance in database
function storeMonthlyBalance($conn, $month, $year, $opening_balance, $closing_balance) {
    $finance_id = 1;
   
    try {
        $sql = "INSERT INTO monthly_balances (finance_id, month, year, opening_balance, closing_balance)
                VALUES (?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE opening_balance = VALUES(opening_balance),
                closing_balance = VALUES(closing_balance), updated_at = CURRENT_TIMESTAMP";
       
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("iiidd", $finance_id, $month, $year, $opening_balance, $closing_balance);
        $stmt->execute();
        $stmt->close();
       
        return true;
    } catch (Exception $e) {
        error_log("Error storing monthly balance: " . $e->getMessage());
        return false;
    }
}

// NEW FUNCTION: Get total excess payments
function getTotalExcessPayments($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    $sql = "SELECT SUM(excess_amount) as total_excess FROM excess_payments 
            WHERE customer_id IN (SELECT id FROM customers WHERE finance_id = ?)";
    $params = [$finance_id];
    $types = "i";
   
    if ($start_date && $end_date) {
        $sql .= " AND payment_date BETWEEN ? AND ?";
        $params[] = $start_date;
        $params[] = $end_date;
        $types .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql .= " AND YEAR(payment_date) = ? AND MONTH(payment_date) = ?";
        $params[] = $selected_year;
        $params[] = $selected_month;
        $types .= "ii";
        if ($selected_day > 0) {
            $sql .= " AND DAY(payment_date) = ?";
            $params[] = $selected_day;
            $types .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql .= " AND YEAR(payment_date) = ?";
        $params[] = $selected_year;
        $types .= "i";
    }
   
    $stmt = $conn->prepare($sql);
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $total = $row['total_excess'] ?? 0;
    $stmt->close();
    
    return number_format($total, 2);
}

// MODIFIED: Get total investment from database WITH FILTERS (only within period)
function getTotalInvestment($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    
    // For custom date range
    if ($start_date && $end_date) {
        $sql = "SELECT SUM(amount) as total_investment FROM initial_investment 
                WHERE finance_id = ? AND investment_date BETWEEN ? AND ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("iss", $finance_id, $start_date, $end_date);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $total_investment = $row['total_investment'] ?? 0.00;
        $stmt->close();
        
        return number_format($total_investment, 2);
    }
    
    // For specific month view
    if ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql = "SELECT SUM(amount) as total_investment FROM initial_investment 
                WHERE finance_id = ? AND YEAR(investment_date) = ? AND MONTH(investment_date) = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("iii", $finance_id, $selected_year, $selected_month);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $total_investment = $row['total_investment'] ?? 0.00;
        $stmt->close();
        
        return number_format($total_investment, 2);
    }
    
    // For year view
    if ($selected_year !== 'all' && $selected_year) {
        $sql = "SELECT SUM(amount) as total_investment FROM initial_investment 
                WHERE finance_id = ? AND YEAR(investment_date) = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ii", $finance_id, $selected_year);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $total_investment = $row['total_investment'] ?? 0.00;
        $stmt->close();
        
        return number_format($total_investment, 2);
    }
    
    // For ALL years (both month and year are 'all')
    if ($selected_month === 'all' && $selected_year === 'all') {
        $sql = "SELECT SUM(amount) as total_investment FROM initial_investment 
                WHERE finance_id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("i", $finance_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $total_investment = $row['total_investment'] ?? 0.00;
        $stmt->close();
        
        return number_format($total_investment, 2);
    }
    
    // Default: all investment
    $sql = "SELECT SUM(amount) as total_investment FROM initial_investment 
            WHERE finance_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $finance_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $total_investment = $row['total_investment'] ?? 0.00;
    $stmt->close();
    
    return number_format($total_investment, 2);
}

// MODIFIED: Get total foreclosure amounts collected
function getTotalForeclosureCollected($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    $sql = "SELECT 
                SUM(principal_amount) as principal_total,
                SUM(interest_amount) as interest_total,
                SUM(overdue_charges) as overdue_total,
                SUM(foreclosure_charge) as foreclosure_charge_total,
                SUM(total_amount) as total_amount
            FROM foreclosures WHERE finance_id = ? AND status = 'paid'";
    $params = [$finance_id];
    $types = "i";
   
    if ($start_date && $end_date) {
        $sql .= " AND paid_date BETWEEN ? AND ?";
        $params[] = $start_date;
        $params[] = $end_date;
        $types .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql .= " AND YEAR(paid_date) = ? AND MONTH(paid_date) = ?";
        $params[] = $selected_year;
        $params[] = $selected_month;
        $types .= "ii";
        if ($selected_day > 0) {
            $sql .= " AND DAY(paid_date) = ?";
            $params[] = $selected_day;
            $types .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql .= " AND YEAR(paid_date) = ?";
        $params[] = $selected_year;
        $types .= "i";
    }
   
    $stmt = $conn->prepare($sql);
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->close();
    
    return [
        'principal' => $row['principal_total'] ?? 0,
        'interest' => $row['interest_total'] ?? 0,
        'overdue' => $row['overdue_total'] ?? 0,
        'foreclosure_charge' => $row['foreclosure_charge_total'] ?? 0,
        'total_amount' => $row['total_amount'] ?? 0
    ];
}

// Get foreclosure breakdown for display
function getForeclosureBreakdownDisplay($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $foreclosure_data = getTotalForeclosureCollected($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
    
    return [
        'principal' => number_format($foreclosure_data['principal'], 2),
        'interest' => number_format($foreclosure_data['interest'], 2),
        'overdue' => number_format($foreclosure_data['overdue'], 2),
        'foreclosure_charge' => number_format($foreclosure_data['foreclosure_charge'], 2),
        'total' => number_format($foreclosure_data['total_amount'], 2)
    ];
}

// Get total REGULAR EMI Received
function getTotalEMIReceived($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    
    // Get regular EMI collections
    $sql_emi = "SELECT SUM(COALESCE(es.principal_paid, 0) + COALESCE(es.interest_paid, 0)) as emi_total
            FROM emi_schedule es
            JOIN customers c ON es.customer_id = c.id
            WHERE es.finance_id = ? AND es.status IN ('paid', 'partial')";
    $params_emi = [$finance_id];
    $types_emi = "i";
   
    if ($start_date && $end_date) {
        $sql_emi .= " AND es.paid_date BETWEEN ? AND ?";
        $params_emi[] = $start_date;
        $params_emi[] = $end_date;
        $types_emi .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql_emi .= " AND YEAR(es.paid_date) = ? AND MONTH(es.paid_date) = ?";
        $params_emi[] = $selected_year;
        $params_emi[] = $selected_month;
        $types_emi .= "ii";
        if ($selected_day > 0) {
            $sql_emi .= " AND DAY(es.paid_date) = ?";
            $params_emi[] = $selected_day;
            $types_emi .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql_emi .= " AND YEAR(es.paid_date) = ?";
        $params_emi[] = $selected_year;
        $types_emi .= "i";
    }
   
    $stmt_emi = $conn->prepare($sql_emi);
    if (!empty($params_emi)) {
        $stmt_emi->bind_param($types_emi, ...$params_emi);
    }
    $stmt_emi->execute();
    $result_emi = $stmt_emi->get_result();
    $row_emi = $result_emi->fetch_assoc();
    $emi_total = $row_emi['emi_total'] ?? 0;
    $stmt_emi->close();
    
    return number_format($emi_total, 2);
}

// Get total principal and interest
function getTotalPrincipalAndInterest($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    
    // Get REGULAR EMI principal and interest ACTUALLY PAID
    $sql_emi = "SELECT SUM(COALESCE(es.principal_paid, 0)) as principal_total,
                       SUM(COALESCE(es.interest_paid, 0)) as interest_total
            FROM emi_schedule es
            JOIN customers c ON es.customer_id = c.id
            WHERE es.finance_id = ? AND es.status IN ('paid', 'partial')";
    $params_emi = [$finance_id];
    $types_emi = "i";
   
    if ($start_date && $end_date) {
        $sql_emi .= " AND es.paid_date BETWEEN ? AND ?";
        $params_emi[] = $start_date;
        $params_emi[] = $end_date;
        $types_emi .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql_emi .= " AND YEAR(es.paid_date) = ? AND MONTH(es.paid_date) = ?";
        $params_emi[] = $selected_year;
        $params_emi[] = $selected_month;
        $types_emi .= "ii";
        if ($selected_day > 0) {
            $sql_emi .= " AND DAY(es.paid_date) = ?";
            $params_emi[] = $selected_day;
            $types_emi .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql_emi .= " AND YEAR(es.paid_date) = ?";
        $params_emi[] = $selected_year;
        $types_emi .= "i";
    }
   
    $stmt_emi = $conn->prepare($sql_emi);
    if (!empty($params_emi)) {
        $stmt_emi->bind_param($types_emi, ...$params_emi);
    }
    $stmt_emi->execute();
    $result_emi = $stmt_emi->get_result();
    $row_emi = $result_emi->fetch_assoc();
    $emi_principal = $row_emi['principal_total'] ?? 0;
    $emi_interest = $row_emi['interest_total'] ?? 0;
    $stmt_emi->close();
    
    // Get Foreclosure principal and interest separately
    $foreclosure_data = getTotalForeclosureCollected($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
    
    // Return both regular EMI and foreclosure amounts separately
    return [
        'regular_principal' => number_format($emi_principal, 2),
        'regular_interest' => number_format($emi_interest, 2),
        'foreclosure_principal' => number_format($foreclosure_data['principal'], 2),
        'foreclosure_interest' => number_format($foreclosure_data['interest'], 2),
        'foreclosure_overdue' => number_format($foreclosure_data['overdue'], 2),
        'foreclosure_charge' => number_format($foreclosure_data['foreclosure_charge'], 2),
        'foreclosure_total' => number_format($foreclosure_data['total_amount'], 2)
    ];
}

// Get total overdue charges collected
function getTotalOverdueChargesCollected($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    
    // Get REGULAR EMI overdue charges
    $sql_emi = "SELECT SUM(COALESCE(es.overdue_charges, 0)) as total
            FROM emi_schedule es
            JOIN customers c ON es.customer_id = c.id
            WHERE es.finance_id = ? AND es.status IN ('paid', 'partial') AND es.overdue_charges > 0";
    $params_emi = [$finance_id];
    $types_emi = "i";
   
    if ($start_date && $end_date) {
        $sql_emi .= " AND es.paid_date BETWEEN ? AND ?";
        $params_emi[] = $start_date;
        $params_emi[] = $end_date;
        $types_emi .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql_emi .= " AND YEAR(es.paid_date) = ? AND MONTH(es.paid_date) = ?";
        $params_emi[] = $selected_year;
        $params_emi[] = $selected_month;
        $types_emi .= "ii";
        if ($selected_day > 0) {
            $sql_emi .= " AND DAY(es.paid_date) = ?";
            $params_emi[] = $selected_day;
            $types_emi .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql_emi .= " AND YEAR(es.paid_date) = ?";
        $params_emi[] = $selected_year;
        $types_emi .= "i";
    }
   
    $stmt_emi = $conn->prepare($sql_emi);
    if (!empty($params_emi)) {
        $stmt_emi->bind_param($types_emi, ...$params_emi);
    }
    $stmt_emi->execute();
    $result_emi = $stmt_emi->get_result();
    $row_emi = $result_emi->fetch_assoc();
    $emi_overdue = $row_emi['total'] ?? 0;
    $stmt_emi->close();
    
    return number_format($emi_overdue, 2);
}

// FIXED: Get current balance with proper filtering
function getCurrentBalance($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    // Get investment for the selected period
    $period_investment = (float)str_replace(',', '', getTotalInvestment($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    
    // Get previous balance only for monthly view
    $previousBalance = 0;
    if ($selected_month !== 'all' && $selected_year !== 'all' && !$start_date && !$end_date) {
        $previousBalance = getPreviousMonthClosingBalance($conn, $selected_month, $selected_year, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
    }
   
    // Get income components for current period
    $emiDetails = getTotalPrincipalAndInterest($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
    
    // Regular EMI components
    $regularPrincipalCollected = (float)str_replace(',', '', $emiDetails['regular_principal']);
    $regularInterestCollected = (float)str_replace(',', '', $emiDetails['regular_interest']);
    $regularOverdueCollected = (float)str_replace(',', '', getTotalOverdueChargesCollected($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    
    // Foreclosure components
    $foreclosurePrincipalCollected = (float)str_replace(',', '', $emiDetails['foreclosure_principal']);
    $foreclosureInterestCollected = (float)str_replace(',', '', $emiDetails['foreclosure_interest']);
    $foreclosureOverdueCollected = (float)str_replace(',', '', $emiDetails['foreclosure_overdue']);
    $foreclosureChargesCollected = (float)str_replace(',', '', $emiDetails['foreclosure_charge']);
    $totalForeclosureCollected = (float)str_replace(',', '', $emiDetails['foreclosure_total']);
    
    $documentCharges = (float)str_replace(',', '', getTotalDocumentCharges($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    
    // Get excess payments
    $excessPayments = (float)str_replace(',', '', getTotalExcessPayments($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
   
    // Get expenses for current period
    $expenses = (float)str_replace(',', '', getTotalExpenses($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
   
    // Get loans issued in current period
    $loansIssued = (float)str_replace(',', '', getTotalLoanIssued($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
   
    // Current Balance = Previous Balance + Investment in period + All Income - Expenses - Loans Issued
    $regularEmiIncome = $regularPrincipalCollected + $regularInterestCollected + $regularOverdueCollected;
    $foreclosureIncome = $totalForeclosureCollected;
    $totalIncome = $regularEmiIncome + $documentCharges + $excessPayments;
    
    $netCashFlow = $totalIncome - $expenses;
    $currentBalance = $previousBalance + $period_investment + $netCashFlow - $loansIssued;
   
    // Store the current month's balance if viewing specific month
    if ($selected_month !== 'all' && $selected_year !== 'all' && !$start_date && !$end_date) {
        storeMonthlyBalance($conn, $selected_month, $selected_year, $previousBalance, $currentBalance);
    }
   
    return number_format($currentBalance, 2);
}

// Get profit/loss calculation
function getProfitLoss($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    // Get income components (EXCLUDING PRINCIPAL for profit/loss calculation)
    $emiDetails = getTotalPrincipalAndInterest($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
    
    // Regular EMI income (excluding principal)
    $regularInterestCollected = (float)str_replace(',', '', $emiDetails['regular_interest']);
    $regularOverdueCollected = (float)str_replace(',', '', getTotalOverdueChargesCollected($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    
    // Foreclosure income (excluding principal)
    $foreclosureInterestCollected = (float)str_replace(',', '', $emiDetails['foreclosure_interest']);
    $foreclosureOverdueCollected = (float)str_replace(',', '', $emiDetails['foreclosure_overdue']);
    $foreclosureChargesCollected = (float)str_replace(',', '', $emiDetails['foreclosure_charge']);
    
    $documentCharges = (float)str_replace(',', '', getTotalDocumentCharges($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
   
    // Get expenses
    $expenses = (float)str_replace(',', '', getTotalExpenses($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
   
    // Profit/Loss = (Interest + Overdue Charges + Document Charges + Foreclosure Income) - Expenses
    $profitLoss = ($regularInterestCollected + $regularOverdueCollected + $documentCharges + 
                   $foreclosureInterestCollected + $foreclosureOverdueCollected + $foreclosureChargesCollected) - $expenses;
   
    return number_format($profitLoss, 2);
}

// Get cash flow breakdown with clear separation
function getCashFlowBreakdown($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    // Get investment for the period
    $period_investment = (float)str_replace(',', '', getTotalInvestment($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    
    // Get previous balance
    $previousBalance = 0;
    if ($selected_month !== 'all' && $selected_year !== 'all' && !$start_date && !$end_date) {
        $previousBalance = getPreviousMonthClosingBalance($conn, $selected_month, $selected_year, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
    }
   
    // Get current period data
    $loansIssued = (float)str_replace(',', '', getTotalLoanIssued($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    
    // Get EMI collections
    $emiDetails = getTotalPrincipalAndInterest($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
    
    // Regular EMI components
    $regularPrincipalCollected = (float)str_replace(',', '', $emiDetails['regular_principal']);
    $regularInterestCollected = (float)str_replace(',', '', $emiDetails['regular_interest']);
    $regularOverdueCollected = (float)str_replace(',', '', getTotalOverdueChargesCollected($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    $documentCharges = (float)str_replace(',', '', getTotalDocumentCharges($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    
    // Foreclosure components
    $foreclosurePrincipalCollected = (float)str_replace(',', '', $emiDetails['foreclosure_principal']);
    $foreclosureInterestCollected = (float)str_replace(',', '', $emiDetails['foreclosure_interest']);
    $foreclosureOverdueCollected = (float)str_replace(',', '', $emiDetails['foreclosure_overdue']);
    $foreclosureChargesCollected = (float)str_replace(',', '', $emiDetails['foreclosure_charge']);
    $totalForeclosureCollected = (float)str_replace(',', '', $emiDetails['foreclosure_total']);
    
    // Get excess payments
    $excessPayments = (float)str_replace(',', '', getTotalExcessPayments($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
    
    $expenses = (float)str_replace(',', '', getTotalExpenses($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day));
   
    // Calculate totals
    $regularEmiIncome = $regularPrincipalCollected + $regularInterestCollected + $regularOverdueCollected;
    $foreclosureIncome = $totalForeclosureCollected;
    $totalIncome = $regularEmiIncome + $documentCharges + $excessPayments;
    
    $netCashFlow = $totalIncome - $expenses;
    $currentBalance = $previousBalance + $period_investment + $netCashFlow - $loansIssued;
   
    return [
        'period_investment' => number_format($period_investment, 2),
        'previous_balance' => number_format($previousBalance, 2),
        'loans_issued' => number_format($loansIssued, 2),
        'regular_emi_income' => number_format($regularEmiIncome, 2),
        'regular_principal' => number_format($regularPrincipalCollected, 2),
        'regular_interest' => number_format($regularInterestCollected, 2),
        'regular_overdue' => number_format($regularOverdueCollected, 2),
        'foreclosure_income' => number_format($foreclosureIncome, 2),
        'foreclosure_principal' => number_format($foreclosurePrincipalCollected, 2),
        'foreclosure_interest' => number_format($foreclosureInterestCollected, 2),
        'foreclosure_overdue' => number_format($foreclosureOverdueCollected, 2),
        'foreclosure_charges' => number_format($foreclosureChargesCollected, 2),
        'document_charges' => number_format($documentCharges, 2),
        'excess_payments' => number_format($excessPayments, 2),
        'total_income' => number_format($totalIncome, 2),
        'total_expenses' => number_format($expenses, 2),
        'net_cash_flow' => number_format($netCashFlow, 2),
        'current_balance' => number_format($currentBalance, 2)
    ];
}

// MODIFIED: Get ALL collection list with NO LIMIT - shows ALL records
function getCollectionList($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    
    // Get REGULAR EMI collections
    $sql_emi = "SELECT c.customer_name, c.agreement_number, 
                       COALESCE(es.principal_paid, 0) as principal_paid,
                       COALESCE(es.interest_paid, 0) as interest_paid,
                       COALESCE(es.overdue_paid, 0) as overdue_paid,
                       es.paid_date, 'EMI' as type, es.emi_bill_number as bill_number,
                       CONCAT(c.agreement_number, '_', COALESCE(es.emi_bill_number, 'NO_BILL')) as unique_key
                FROM emi_schedule es
                JOIN customers c ON es.customer_id = c.id
                WHERE es.finance_id = ? AND es.status IN ('paid', 'partial')";
    $params_emi = [$finance_id];
    $types_emi = "i";
    
    if ($start_date && $end_date) {
        $sql_emi .= " AND es.paid_date BETWEEN ? AND ?";
        $params_emi[] = $start_date;
        $params_emi[] = $end_date;
        $types_emi .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql_emi .= " AND YEAR(es.paid_date) = ? AND MONTH(es.paid_date) = ?";
        $params_emi[] = $selected_year;
        $params_emi[] = $selected_month;
        $types_emi .= "ii";
        if ($selected_day > 0) {
            $sql_emi .= " AND DAY(es.paid_date) = ?";
            $params_emi[] = $selected_day;
            $types_emi .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql_emi .= " AND YEAR(es.paid_date) = ?";
        $params_emi[] = $selected_year;
        $types_emi .= "i";
    }
    
    // Execute EMI query
    $stmt_emi = $conn->prepare($sql_emi);
    if (!empty($params_emi)) {
        $stmt_emi->bind_param($types_emi, ...$params_emi);
    }
    $stmt_emi->execute();
    $result_emi = $stmt_emi->get_result();
    
    $all_data = [];
    
    // Process EMI results
    while ($row = $result_emi->fetch_assoc()) {
        $amount = $row['principal_paid'] + $row['interest_paid'] + $row['overdue_paid'];
        if ($amount > 0) { // Only include if there's actual payment
            $all_data[] = [
                'customer_name' => $row['customer_name'],
                'agreement_number' => $row['agreement_number'],
                'amount' => $amount,
                'paid_date' => $row['paid_date'],
                'type' => $row['type'],
                'bill_number' => $row['bill_number'] ?? '',
                'unique_key' => $row['unique_key'],
                'timestamp' => strtotime($row['paid_date']) // Add timestamp for sorting
            ];
        }
    }
    $stmt_emi->close();
    
    // Get foreclosure collections
    $sql_fore = "SELECT c.customer_name, c.agreement_number, f.total_amount as amount, 
                        f.paid_date, 'Foreclosure' as type, f.bill_number,
                        CONCAT(c.agreement_number, '_', f.bill_number) as unique_key
                 FROM foreclosures f
                 JOIN customers c ON f.customer_id = c.id
                 WHERE f.finance_id = ? AND f.status = 'paid'";
    $params_fore = [$finance_id];
    $types_fore = "i";
    
    if ($start_date && $end_date) {
        $sql_fore .= " AND f.paid_date BETWEEN ? AND ?";
        $params_fore[] = $start_date;
        $params_fore[] = $end_date;
        $types_fore .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql_fore .= " AND YEAR(f.paid_date) = ? AND MONTH(f.paid_date) = ?";
        $params_fore[] = $selected_year;
        $params_fore[] = $selected_month;
        $types_fore .= "ii";
        if ($selected_day > 0) {
            $sql_fore .= " AND DAY(f.paid_date) = ?";
            $params_fore[] = $selected_day;
            $types_fore .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql_fore .= " AND YEAR(f.paid_date) = ?";
        $params_fore[] = $selected_year;
        $types_fore .= "i";
    }
    
    $stmt_fore = $conn->prepare($sql_fore);
    if (!empty($params_fore)) {
        $stmt_fore->bind_param($types_fore, ...$params_fore);
    }
    $stmt_fore->execute();
    $result_fore = $stmt_fore->get_result();
    
    // Process foreclosure results
    while ($row = $result_fore->fetch_assoc()) {
        if ($row['amount'] > 0) { // Only include if there's actual payment
            $all_data[] = [
                'customer_name' => $row['customer_name'],
                'agreement_number' => $row['agreement_number'],
                'amount' => $row['amount'],
                'paid_date' => $row['paid_date'],
                'type' => $row['type'],
                'bill_number' => $row['bill_number'] ?? '',
                'unique_key' => $row['unique_key'],
                'timestamp' => strtotime($row['paid_date']) // Add timestamp for sorting
            ];
        }
    }
    $stmt_fore->close();
    
    // Get excess payments
    $sql_excess = "SELECT c.customer_name, c.agreement_number, ep.excess_amount as amount, 
                          ep.payment_date as paid_date, 'Excess Payment' as type, ep.bill_number,
                          CONCAT(c.agreement_number, '_', ep.bill_number) as unique_key
                   FROM excess_payments ep
                   JOIN customers c ON ep.customer_id = c.id
                   WHERE ep.customer_id IN (SELECT id FROM customers WHERE finance_id = ?)";
    $params_excess = [$finance_id];
    $types_excess = "i";
    
    if ($start_date && $end_date) {
        $sql_excess .= " AND ep.payment_date BETWEEN ? AND ?";
        $params_excess[] = $start_date;
        $params_excess[] = $end_date;
        $types_excess .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql_excess .= " AND YEAR(ep.payment_date) = ? AND MONTH(ep.payment_date) = ?";
        $params_excess[] = $selected_year;
        $params_excess[] = $selected_month;
        $types_excess .= "ii";
        if ($selected_day > 0) {
            $sql_excess .= " AND DAY(ep.payment_date) = ?";
            $params_excess[] = $selected_day;
            $types_excess .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql_excess .= " AND YEAR(ep.payment_date) = ?";
        $params_excess[] = $selected_year;
        $types_excess .= "i";
    }
    
    $stmt_excess = $conn->prepare($sql_excess);
    if (!empty($params_excess)) {
        $stmt_excess->bind_param($types_excess, ...$params_excess);
    }
    $stmt_excess->execute();
    $result_excess = $stmt_excess->get_result();
    
    // Process excess payment results
    while ($row = $result_excess->fetch_assoc()) {
        if ($row['amount'] > 0) { // Only include if there's actual payment
            $all_data[] = [
                'customer_name' => $row['customer_name'],
                'agreement_number' => $row['agreement_number'],
                'amount' => $row['amount'],
                'paid_date' => $row['paid_date'],
                'type' => $row['type'],
                'bill_number' => $row['bill_number'] ?? '',
                'unique_key' => $row['unique_key'],
                'timestamp' => strtotime($row['paid_date']) // Add timestamp for sorting
            ];
        }
    }
    $stmt_excess->close();
    
    // Create merged data by unique key (agreement + bill)
    $merged_data = [];
    foreach ($all_data as $item) {
        $key = $item['unique_key'];
        
        if (!isset($merged_data[$key])) {
            $merged_data[$key] = [
                'customer_name' => $item['customer_name'],
                'agreement_number' => $item['agreement_number'],
                'bill_number' => $item['bill_number'],
                'amount' => $item['amount'],
                'paid_date' => $item['paid_date'],
                'type' => $item['type'],
                'types' => [$item['type']],
                'row_count' => 1,
                'timestamp' => $item['timestamp'] // Store timestamp
            ];
        } else {
            // Check if this is a duplicate (same agreement, bill, and similar type/time period)
            // If it's a foreclosure, it should replace EMI payments for the same bill
            if ($item['type'] === 'Foreclosure' && in_array('EMI', $merged_data[$key]['types'])) {
                // For foreclosure, we don't add EMI amounts as they're included in foreclosure
                $merged_data[$key]['amount'] = $item['amount'];
                $merged_data[$key]['type'] = 'Foreclosure';
                $merged_data[$key]['types'] = ['Foreclosure'];
            } else {
                // For other cases, add the amount
                $merged_data[$key]['amount'] += $item['amount'];
                $merged_data[$key]['row_count']++;
                
                if (!in_array($item['type'], $merged_data[$key]['types'])) {
                    $merged_data[$key]['types'][] = $item['type'];
                }
            }
            
            // Use the LATEST date and timestamp (for display)
            if ($item['timestamp'] > $merged_data[$key]['timestamp']) {
                $merged_data[$key]['paid_date'] = $item['paid_date'];
                $merged_data[$key]['timestamp'] = $item['timestamp'];
            }
        }
    }
    
    // Sort merged data by timestamp ASC (oldest first) - Date 1 to 30 order
    usort($merged_data, function($a, $b) {
        return $a['timestamp'] - $b['timestamp']; // ASC order (1 to 30)
    });
    
    // Generate HTML from merged data
    $html = '';
    $counter = 1;
    $merged_total = 0;
    
    foreach ($merged_data as $item) {
        $merged_total += $item['amount'];
        
        // Determine badge based on types
        $typeBadge = '';
        if (count($item['types']) > 1) {
            $typeBadge = '<span class="badge bg-warning">Mixed</span>';
        } else {
            switch ($item['type']) {
                case 'Foreclosure':
                    $typeBadge = '<span class="badge bg-danger">Foreclosure</span>';
                    break;
                case 'Excess Payment':
                    $typeBadge = '<span class="badge bg-info">Excess</span>';
                    break;
                default:
                    $typeBadge = '<span class="badge bg-success">EMI</span>';
            }
        }
        
        $bill_info = !empty($item['bill_number']) ? '<br><small class="text-muted">Bill: ' . $item['bill_number'] . '</small>' : '';
        
        // Add merged count indicator if more than 1 row was merged
        $merged_indicator = '';
        if ($item['row_count'] > 1) {
            $merged_indicator = '<br><small class="text-muted"><i class="fas fa-layer-group"></i> Merged ' . $item['row_count'] . ' payments</small>';
        }
        
        $html .= "<tr>
                    <td>{$counter}</td>
                    <td>{$item['customer_name']}</td>
                    <td>{$item['agreement_number']}{$bill_info}{$merged_indicator}</td>
                    <td>₹" . number_format($item['amount'], 2) . "</td>
                    <td>{$item['paid_date']}</td>
                    <td>{$typeBadge}</td>
                </tr>";
        $counter++;
    }
    
    // Add total row
    if ($merged_total > 0) {
        $html .= "<tr class='table-secondary'>
                    <td colspan='3'><strong>Total Collections</strong></td>
                    <td><strong>₹" . number_format($merged_total, 2) . "</strong></td>
                    <td colspan='2'></td>
                </tr>";
    } else {
        $html = "<tr><td colspan='6' class='text-center'>No collections found</td></tr>";
    }
    
    return $html;
}

// MODIFIED: Get ALL new customers with NO LIMIT
function getNewCustomers($conn, $start_date = '', $end_date = '', $selected_month = '', $selected_year = '', $selected_day = 0) {
    $finance_id = 1;
    $sql = "SELECT c.customer_name, c.agreement_number, c.loan_amount, l.loan_name, c.emi_date
            FROM customers c
            JOIN loans l ON c.loan_id = l.id
            WHERE c.finance_id = ?";
    $params = [$finance_id];
    $types = "i";
   
    if ($start_date && $end_date) {
        $sql .= " AND c.emi_date BETWEEN ? AND ?";
        $params[] = $start_date;
        $params[] = $end_date;
        $types .= "ss";
    } elseif ($selected_month !== 'all' && $selected_year !== 'all' && $selected_year) {
        $sql .= " AND YEAR(c.emi_date) = ? AND MONTH(c.emi_date) = ?";
        $params[] = $selected_year;
        $params[] = $selected_month;
        $types .= "ii";
        if ($selected_day > 0) {
            $sql .= " AND DAY(c.emi_date) = ?";
            $params[] = $selected_day;
            $types .= "i";
        }
    } elseif ($selected_year !== 'all' && $selected_year) {
        $sql .= " AND YEAR(c.emi_date) = ?";
        $params[] = $selected_year;
        $types .= "i";
    }
   
    // NO LIMIT clause - shows ALL records
    $sql .= " ORDER BY c.id DESC";
    $stmt = $conn->prepare($sql);
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $html = '';
    $count = 0;
    $total_loan = 0;
   
    while ($row = $result->fetch_assoc()) {
        $count++;
        $total_loan += $row['loan_amount'];
        $html .= "<tr>
                    <td>{$count}</td>
                    <td>{$row['customer_name']}</td>
                    <td>{$row['agreement_number']}</td>
                    <td>₹" . number_format($row['loan_amount'], 2) . "</td>
                    <td>{$row['loan_name']}</td>
                    <td>{$row['emi_date']}</td>
                </tr>";
    }
    
    if ($count > 0) {
        // Add summary row
        $html .= "<tr class='table-info fw-bold'>
                    <td colspan='3' class='text-end'>Total Loan Issued:</td>
                    <td>₹" . number_format($total_loan, 2) . "</td>
                    <td colspan='2'>{$count} customers</td>
                  </tr>";
    } else {
        $html = "<tr><td colspan='6' class='text-center'>No new customers found</td></tr>";
    }
    
    $stmt->close();
   
    return $html;
}

// FIXED: Function to initialize all monthly balances
function initializeAllMonthlyBalances($conn) {
    $finance_id = 1;
    
    // Get all distinct years and months from customers and transactions
    $sql = "SELECT DISTINCT YEAR(emi_date) as year, MONTH(emi_date) as month FROM customers WHERE finance_id = ?
            UNION
            SELECT DISTINCT YEAR(paid_date) as year, MONTH(paid_date) as month FROM emi_schedule WHERE finance_id = ?
            UNION
            SELECT DISTINCT YEAR(paid_date) as year, MONTH(paid_date) as month FROM foreclosures WHERE finance_id = ?
            UNION
            SELECT DISTINCT YEAR(investment_date) as year, MONTH(investment_date) as month FROM initial_investment WHERE finance_id = ?
            UNION
            SELECT DISTINCT YEAR(expense_date) as year, MONTH(expense_date) as month FROM expenses WHERE finance_id = ?
            ORDER BY year, month";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("iiiii", $finance_id, $finance_id, $finance_id, $finance_id, $finance_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $months = [];
    while ($row = $result->fetch_assoc()) {
        $months[] = ['year' => $row['year'], 'month' => $row['month']];
    }
    $stmt->close();
    
    // Sort months chronologically
    usort($months, function($a, $b) {
        if ($a['year'] == $b['year']) {
            return $a['month'] - $b['month'];
        }
        return $a['year'] - $b['year'];
    });
    
    // Calculate balance for each month sequentially
    foreach ($months as $month_data) {
        calculateMonthClosingBalance($conn, $month_data['month'], $month_data['year']);
    }
}

// Get current month, year, and date
$current_month = date('m');
$current_year = date('Y');
$current_date = date('Y-m-d');
$current_day = date('d');

// Handle filter from GET parameters
$selected_month = isset($_GET['month']) ? $_GET['month'] : $current_month;
$selected_year = isset($_GET['year']) ? $_GET['year'] : $current_year;
$selected_day = isset($_GET['day']) ? intval($_GET['day']) : 0;
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : '';
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : '';

// Handle 'all' for year
if ($selected_year === 'all') {
    $selected_year = 'all';
} else {
    $selected_year = intval($selected_year);
    if ($selected_year < 2000 || $selected_year > date('Y') + 1) {
        $selected_year = $current_year;
    }
}

if ($selected_month !== 'all' && ($selected_month < 1 || $selected_month > 12)) {
    $selected_month = $current_month;
}

// Calculate days in month based on selection
if ($selected_month === 'all' || $selected_year === 'all') {
    $days_in_month = 31;
} else {
    $days_in_month = cal_days_in_month(CAL_GREGORIAN, $selected_month, $selected_year);
}

if ($selected_day > $days_in_month) {
    $selected_day = 0;
}

if ($start_date && $end_date) {
    $start_date_obj = DateTime::createFromFormat('Y-m-d', $start_date);
    $end_date_obj = DateTime::createFromFormat('Y-m-d', $end_date);
    $max_date = (new DateTime())->modify('+1 year')->format('Y-m-d');
   
    if (!$start_date_obj || !$end_date_obj || $start_date > $end_date || $end_date > $max_date) {
        $start_date = '';
        $end_date = '';
    }
}

// Initialize all monthly balances to ensure historical data is correct
initializeAllMonthlyBalances($conn);

// Check if export/print is requested
if (isset($_GET['action']) && $_GET['action'] == 'print') {
    // Include print report page
    include 'print-report.php';
    exit();
}

$breadcrumb_active = ($start_date && $end_date)
    ? "Overall Report for " . (new DateTime($start_date))->format('d M Y') . " - " . (new DateTime($end_date))->format('d M Y')
    : ($selected_month === 'all' && $selected_year === 'all'
        ? "Overall Report for ALL Time"
        : ($selected_month === 'all'
            ? "Overall Report for " . $selected_year . ($selected_day > 0 ? ' - Day ' . $selected_day : '')
            : "Overall Report for " . date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) . ($selected_day > 0 ? ' - Day ' . $selected_day : '')));

// Get data for display
$totalInvestment = getTotalInvestment($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$totalLoanIssued = getTotalLoanIssued($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$totalEMIReceived = getTotalEMIReceived($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$emiDetails = getTotalPrincipalAndInterest($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$regularPrincipalCollected = $emiDetails['regular_principal'];
$regularInterestCollected = $emiDetails['regular_interest'];
$foreclosurePrincipalCollected = $emiDetails['foreclosure_principal'];
$foreclosureInterestCollected = $emiDetails['foreclosure_interest'];
$foreclosureOverdueCollected = $emiDetails['foreclosure_overdue'];
$foreclosureChargesCollected = $emiDetails['foreclosure_charge'];
$totalForeclosureCollected = $emiDetails['foreclosure_total'];
$regularOverdueCollected = getTotalOverdueChargesCollected($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$totalDocumentCharges = getTotalDocumentCharges($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$foreclosureBreakdown = getForeclosureBreakdownDisplay($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$totalExcessPayments = getTotalExcessPayments($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$totalExpenses = getTotalExpenses($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$outstandingAmount = getOutstandingPrincipalAmount($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$currentBalance = getCurrentBalance($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$profitLoss = getProfitLoss($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$cashFlow = getCashFlowBreakdown($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$collectionList = getCollectionList($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$newCustomers = getNewCustomers($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
?>
<!DOCTYPE html>
<html lang="en" dir="ltr" data-startbar="dark" data-bs-theme="light">
<head>
    <?php include 'includes/head.php'; ?>
    <style>
        .table-responsive {
            max-height: 500px;
            overflow-y: auto;
        }
        .table-responsive table {
            margin-bottom: 0;
        }
        .table th {
            position: sticky;
            top: 0;
            background-color: #f8f9fa;
            z-index: 10;
            box-shadow: 0 2px 2px -1px rgba(0,0,0,0.1);
        }
        .card-body .table-responsive {
            border: 1px solid #dee2e6;
            border-radius: 0.375rem;
        }
        .badge {
            font-size: 0.75em;
        }
        .table-striped tbody tr:nth-of-type(odd) {
            background-color: rgba(0,0,0,.02);
        }
        .table-info {
            background-color: #d1ecf1 !important;
        }
        .btn-print {
            background-color: #6c757d;
            color: white;
            border: none;
        }
        .btn-print:hover {
            background-color: #5a6268;
            color: white;
        }
    </style>
</head>
<body>
    <?php include 'includes/topbar.php'; ?>
   
    <div class="startbar d-print-none">
        <?php include 'includes/leftbar-tab-menu.php'; ?>
        <?php include 'includes/leftbar.php'; ?>
        <div class="startbar-overlay d-print-none"></div>
       
        <div class="page-wrapper">
            <div class="page-content">
                <div class="container-fluid">
                    <div class="row">
                        <div class="col-sm-12">
                            <div class="page-title-box d-md-flex justify-content-md-between align-items-center">
                                <h4 class="page-title">Overall Report</h4>
                                <ol class="breadcrumb mb-0">
                                    <li class="breadcrumb-item"><a href="index.php">Dashboard</a></li>
                                    <li class="breadcrumb-item active"><?php echo $breadcrumb_active; ?></li>
                                </ol>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="card mb-4">
                        <div class="card-header bg-light">
                            <h5 class="card-title mb-0">Filter Overall Report</h5>
                        </div>
                        <div class="card-body">
                            <form method="GET" action="">
                                <div class="row g-3">
                                    <div class="col-md-3">
                                        <label for="month" class="form-label fw-bold">Select Month</label>
                                        <select class="form-control" id="month" name="month">
                                            <option value="all" <?php echo ($selected_month === 'all') ? 'selected' : ''; ?>>All Months</option>
                                            <?php for ($m = 1; $m <= 12; $m++): ?>
                                                <option value="<?php echo $m; ?>" <?php echo ($m == $selected_month) ? 'selected' : ''; ?>>
                                                    <?php echo date('F', mktime(0, 0, 0, $m, 1)); ?>
                                                </option>
                                            <?php endfor; ?>
                                        </select>
                                    </div>
                                    <div class="col-md-3">
                                        <label for="year" class="form-label fw-bold">Select Year</label>
                                        <select class="form-control" id="year" name="year">
                                            <option value="all" <?php echo ($selected_year === 'all') ? 'selected' : ''; ?>>All Years</option>
                                            <?php for ($y = 2020; $y <= date('Y') + 1; $y++): ?>
                                                <option value="<?php echo $y; ?>" <?php echo ($y == $selected_year) ? 'selected' : ''; ?>>
                                                    <?php echo $y; ?>
                                                </option>
                                            <?php endfor; ?>
                                        </select>
                                    </div>
                                    <div class="col-md-3">
                                        <label for="day" class="form-label fw-bold">Select Day</label>
                                        <select class="form-control" id="day" name="day">
                                            <option value="0" <?php echo ($selected_day == 0) ? 'selected' : ''; ?>>All Days</option>
                                            <?php 
                                            $days_in_month = 31; // Default
                                            if ($selected_month !== 'all' && $selected_year !== 'all') {
                                                $days_in_month = cal_days_in_month(CAL_GREGORIAN, $selected_month, $selected_year);
                                            }
                                            for ($d = 1; $d <= $days_in_month; $d++): 
                                            ?>
                                                <option value="<?php echo $d; ?>" <?php echo ($d == $selected_day) ? 'selected' : ''; ?>>
                                                    <?php echo $d; ?>
                                                </option>
                                            <?php endfor; ?>
                                        </select>
                                    </div>
                                    <div class="col-md-3">
                                        <label for="start_date" class="form-label fw-bold">Start Date</label>
                                        <input type="date" class="form-control" id="start_date" name="start_date" value="<?php echo htmlspecialchars($start_date); ?>">
                                    </div>
                                    <div class="col-md-3">
                                        <label for="end_date" class="form-label fw-bold">End Date</label>
                                        <input type="date" class="form-control" id="end_date" name="end_date" value="<?php echo htmlspecialchars($end_date); ?>">
                                    </div>
                                    <div class="col-md-3 d-flex align-items-end">
                                        <button type="submit" class="btn btn-primary me-2">Apply Filter</button>
                                        <div class="dropdown">
                                            <button class="btn btn-success dropdown-toggle" type="button" id="exportDropdown" data-bs-toggle="dropdown" aria-expanded="false">
                                                Export/Print
                                            </button>
                                            <ul class="dropdown-menu" aria-labelledby="exportDropdown">
                                                <li><a class="dropdown-item" href="#" onclick="printReport()">Print Report</a></li>
                                                <li><a class="dropdown-item" href="#" onclick="downloadPDF()">Download PDF</a></li>
                                            </ul>
                                        </div>
                                        <?php if (isset($_GET['month']) || isset($_GET['year']) || isset($_GET['day']) || $start_date || $end_date): ?>
                                            <a href="overall-reports.php" class="btn btn-outline-secondary ms-2">Clear Filter</a>
                                        <?php endif; ?>
                                    </div>
                                </div>
                                <div class="row mt-3">
                                    <div class="col-12">
                                        <div class="alert alert-info mb-0">
                                            <i class="fas fa-info-circle me-2"></i>
                                            <strong>Filter Info:</strong>
                                            <?php 
                                            if ($start_date && $end_date) {
                                                echo "Showing data from " . (new DateTime($start_date))->format('d M Y') . " to " . (new DateTime($end_date))->format('d M Y');
                                            } elseif ($selected_month === 'all' && $selected_year === 'all') {
                                                echo "Showing ALL data from ALL years";
                                            } elseif ($selected_month === 'all' && $selected_year !== 'all') {
                                                echo "Showing all data for " . $selected_year . ($selected_day > 0 ? ' - Day ' . $selected_day : '');
                                            } elseif ($selected_month !== 'all' && $selected_year !== 'all') {
                                                echo "Showing data for " . date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) . ($selected_day > 0 ? ' - Day ' . $selected_day : '');
                                            }
                                            ?>
                                            <br>
                                            <small class="text-muted">Note: Showing ALL records (no limits). Collection list shows merged payments by agreement number and bill.</small>
                                        </div>
                                    </div>
                                </div>
                            </form>
                        </div>
                    </div>
                    
                    
                    <div class="row">
                        <div class="col-12">
                            <div class="card" id="balanceSummaryCard">
                                <div class="card-header d-flex align-items-center justify-content-between">
                                    <h6 class="card-title mb-0" style="font-weight: bold; font-size: 1.1rem; color: #333;">Cash Position Summary (Selected Period Only)</h6>
                                    <div>
                                        
                                        <a href="investment-history.php" class="btn btn-sm btn-outline-info me-2">
                                            <i class="fas fa-history me-1"></i> Investment History
                                        </a>
                                        
                                        <a href="add-investment.php" class="btn btn-sm btn-outline-primary">
                                            <i class="fas fa-plus me-1"></i> Add Investment
                                        </a>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="row">
                                        <div class="col-md-6 col-lg-4">
                                            <div class="card bg-primary text-white mb-3">
                                                <div class="card-body">
                                                    <h5 class="card-title">Investment (Period)</h5>
                                                    <h2 class="card-text">₹<?php echo $totalInvestment; ?></h2>
                                                    <small>Investment made in selected period only</small>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-6 col-lg-4">
                                            <div class="card bg-info text-white mb-3">
                                                <div class="card-body">
                                                    <h5 class="card-title">Balance (Period End)</h5>
                                                    <h2 class="card-text">₹<?php echo $currentBalance; ?></h2>
                                                    <small>Balance at end of selected period</small>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-6 col-lg-4">
                                            <div class="card bg-warning text-white mb-3">
                                                <div class="card-body">
                                                    <h5 class="card-title">Loans Issued (Period)</h5>
                                                    <h2 class="card-text">₹<?php echo $totalLoanIssued; ?></h2>
                                                    <small>Total principal disbursed in period</small>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                   
                                    <div class="row">
                                        <div class="col-md-6 col-lg-3">
                                            <div class="card bg-success text-white mb-3">
                                                <div class="card-body">
                                                    <h5 class="card-title">Regular EMI (Period)</h5>
                                                    <h2 class="card-text">₹<?php echo $totalEMIReceived; ?></h2>
                                                    <small>Regular EMI collected in period</small>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-6 col-lg-3">
                                            <div class="card bg-dark text-white mb-3">
                                                <div class="card-body">
                                                    <h5 class="card-title">Foreclosure (Period)</h5>
                                                    <h2 class="card-text">₹<?php echo $totalForeclosureCollected; ?></h2>
                                                    <small>Foreclosure collected in period</small>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-6 col-lg-3">
                                            <div class="card bg-secondary text-white mb-3">
                                                <div class="card-body">
                                                    <h5 class="card-title">Document Charges (Period)</h5>
                                                    <h2 class="card-text">₹<?php echo $totalDocumentCharges; ?></h2>
                                                    <small>Document charges in period</small>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-6 col-lg-3">
                                            <div class="card bg-danger text-white mb-3">
                                                <div class="card-body">
                                                    <h5 class="card-title">Expenses (Period)</h5>
                                                    <h2 class="card-text">₹<?php echo $totalExpenses; ?></h2>
                                                    <small>Expenses in selected period</small>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                   
                                    <div class="row">
                                        <div class="col-md-6 col-lg-3">
                                            <div class="card bg-purple text-white mb-3" style="background-color: #6f42c1;">
                                                <div class="card-body">
                                                    <h5 class="card-title">Excess Payments (Period)</h5>
                                                    <h2 class="card-text">₹<?php echo $totalExcessPayments; ?></h2>
                                                    <small>Excess payments in period</small>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-6 col-lg-3">
                                            <div class="card bg-dark text-white mb-3">
                                                <div class="card-body">
                                                    <h5 class="card-title">Outstanding Principal (Period)</h5>
                                                    <h2 class="card-text">₹<?php echo $outstandingAmount; ?></h2>
                                                    <small>Principal pending in period</small>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-md-6 col-lg-6">
                                            <div class="card <?php echo ((float)str_replace(',', '', $profitLoss) >= 0) ? 'bg-success' : 'bg-danger'; ?> text-white mb-3">
                                                <div class="card-body text-center">
                                                    <h4 class="card-title">Profit / Loss (Selected Period)</h4>
                                                    <h1 class="card-text">₹<?php echo $profitLoss; ?></h1>
                                                    <small>Profit/Loss for selected period only</small>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                   
                                    
                                    <div class="row mt-4">
                                        <div class="col-12">
                                            <div class="card">
                                                <div class="card-header bg-light">
                                                    <h6 class="card-title mb-0" style="font-weight: bold; font-size: 1.1rem; color: #333;">Cash Flow Breakdown for Selected Period</h6>
                                                </div>
                                                <div class="card-body">
                                                    <div class="row text-center">
                                                        <div class="col-md-3">
                                                            <div class="border p-3 rounded">
                                                                <h6>Previous Balance</h6>
                                                                <h4 class="text-primary">₹<?php echo $cashFlow['previous_balance']; ?></h4>
                                                                <small>Balance from previous month</small>
                                                            </div>
                                                        </div>
                                                        <div class="col-md-3">
                                                            <div class="border p-3 rounded">
                                                                <h6>Period Investment</h6>
                                                                <h4 class="text-success">₹<?php echo $cashFlow['period_investment']; ?></h4>
                                                                <small>Investment in selected period</small>
                                                            </div>
                                                        </div>
                                                        <div class="col-md-3">
                                                            <div class="border p-3 rounded">
                                                                <h6>Cash Inflow</h6>
                                                                <h4 class="text-success">₹<?php echo $cashFlow['total_income']; ?></h4>
                                                                <small>Total income in period</small>
                                                                <div class="mt-2 small">
                                                                    <div>Regular EMI: ₹<?php echo $cashFlow['regular_emi_income']; ?></div>
                                                                    <div>Foreclosure: ₹<?php echo $cashFlow['foreclosure_income']; ?></div>
                                                                    <div>Document: ₹<?php echo $cashFlow['document_charges']; ?></div>
                                                                    <div>Excess: ₹<?php echo $cashFlow['excess_payments']; ?></div>
                                                                </div>
                                                            </div>
                                                        </div>
                                                        <div class="col-md-3">
                                                            <div class="border p-3 rounded">
                                                                <h6>Cash Outflow</h6>
                                                                <h4 class="text-danger">₹<?php echo number_format((float)str_replace(',', '', $cashFlow['total_expenses']) + (float)str_replace(',', '', $cashFlow['loans_issued']), 2); ?></h4>
                                                                <small>Outflow in period</small>
                                                                <div class="mt-2 small">
                                                                    <div>Expenses: ₹<?php echo $cashFlow['total_expenses']; ?></div>
                                                                    <div>Loans Issued: ₹<?php echo $cashFlow['loans_issued']; ?></div>
                                                                </div>
                                                            </div>
                                                        </div>
                                                    </div>
                                                    
                                                    <div class="row mt-3">
                                                        <div class="col-md-4 offset-md-4">
                                                            <div class="border p-3 rounded bg-light">
                                                                <h6 class="text-center">Current Balance</h6>
                                                                <h2 class="text-center text-info">₹<?php echo $cashFlow['current_balance']; ?></h2>
                                                                <small class="text-center d-block">Balance at end of selected period</small>
                                                            </div>
                                                        </div>
                                                    </div>
                                                    
                                                    
                                                    <?php if ((float)str_replace(',', '', $totalEMIReceived) > 0): ?>
                                                    <div class="row mt-4">
                                                        <div class="col-12">
                                                            <div class="card bg-light">
                                                                <div class="card-header">
                                                                    <h6 class="card-title mb-0" style="font-weight: bold; font-size: 1rem; color: #333;">Regular EMI Breakdown (Selected Period)</h6>
                                                                </div>
                                                                <div class="card-body">
                                                                    <div class="row text-center">
                                                                        <div class="col-md-3">
                                                                            <div class="border p-2 rounded bg-white">
                                                                                <h6>Principal</h6>
                                                                                <h5 class="text-primary">₹<?php echo $regularPrincipalCollected; ?></h5>
                                                                            </div>
                                                                        </div>
                                                                        <div class="col-md-3">
                                                                            <div class="border p-2 rounded bg-white">
                                                                                <h6>Interest</h6>
                                                                                <h5 class="text-success">₹<?php echo $regularInterestCollected; ?></h5>
                                                                            </div>
                                                                        </div>
                                                                        <div class="col-md-3">
                                                                            <div class="border p-2 rounded bg-white">
                                                                                <h6>Overdue</h6>
                                                                                <h5 class="text-warning">₹<?php echo $regularOverdueCollected; ?></h5>
                                                                            </div>
                                                                        </div>
                                                                        <div class="col-md-3">
                                                                            <div class="border p-2 rounded bg-white">
                                                                                <h6>Total</h6>
                                                                                <h5 class="text-dark">₹<?php echo $cashFlow['regular_emi_income']; ?></h5>
                                                                            </div>
                                                                        </div>
                                                                    </div>
                                                                </div>
                                                            </div>
                                                        </div>
                                                    </div>
                                                    <?php endif; ?>
                                                    
                                                    
                                                    <?php if ((float)str_replace(',', '', $totalForeclosureCollected) > 0): ?>
                                                    <div class="row mt-4">
                                                        <div class="col-12">
                                                            <div class="card bg-light">
                                                                <div class="card-header">
                                                                    <h6 class="card-title mb-0" style="font-weight: bold; font-size: 1rem; color: #333;">Foreclosure Breakdown (Selected Period)</h6>
                                                                </div>
                                                                <div class="card-body">
                                                                    <div class="row text-center">
                                                                        <div class="col-md-3">
                                                                            <div class="border p-2 rounded bg-white">
                                                                                <h6>Principal</h6>
                                                                                <h5 class="text-primary">₹<?php echo $foreclosurePrincipalCollected; ?></h5>
                                                                            </div>
                                                                        </div>
                                                                        <div class="col-md-3">
                                                                            <div class="border p-2 rounded bg-white">
                                                                                <h6>Interest</h6>
                                                                                <h5 class="text-success">₹<?php echo $foreclosureInterestCollected; ?></h5>
                                                                            </div>
                                                                        </div>
                                                                        <div class="col-md-3">
                                                                            <div class="border p-2 rounded bg-white">
                                                                                <h6>Overdue</h6>
                                                                                <h5 class="text-warning">₹<?php echo $foreclosureOverdueCollected; ?></h5>
                                                                            </div>
                                                                        </div>
                                                                        <div class="col-md-3">
                                                                            <div class="border p-2 rounded bg-white">
                                                                                <h6>Foreclosure Charge</h6>
                                                                                <h5 class="text-info">₹<?php echo $foreclosureChargesCollected; ?></h5>
                                                                            </div>
                                                                        </div>
                                                                    </div>
                                                                    <div class="row mt-3">
                                                                        <div class="col-12 text-center">
                                                                            <h5 class="text-dark">Total Foreclosure Amount: ₹<?php echo $foreclosureBreakdown['total']; ?></h5>
                                                                        </div>
                                                                    </div>
                                                                </div>
                                                            </div>
                                                        </div>
                                                    </div>
                                                    <?php endif; ?>
                                                    
                                                    <div class="row mt-3">
                                                        <div class="col-12">
                                                            <div class="alert alert-info mb-0">
                                                                <strong>Formula for Selected Period:</strong> Current Balance = Previous Balance + Period Investment + Total Income - Expenses - Loans Issued<br>
                                                                <strong>Total Income =</strong> Regular EMI (Principal+Interest+Overdue) + Foreclosure + Document Charges + Excess Payments<br>
                                                                <strong>Important Notes:</strong> 
                                                                <ul class="mb-0">
                                                                    <li>All values shown are ONLY for the selected period/filter</li>
                                                                    <li>Investment shows ONLY investments made in the selected period</li>
                                                                    <li>If no investment was made in selected period, investment will be ₹0.00</li>
                                                                    <li>Previous balance is the closing balance from the month before selected period</li>
                                                                    <li>For January of any year, previous balance is December of previous year's closing balance</li>
                                                                    <li>Profit/Loss calculation excludes principal amounts as they are return of capital</li>
                                                                </ul>
                                                            </div>
                                                        </div>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="row">
                        <div class="col-md-12">
                            <div class="card">
                                <div class="card-header d-flex align-items-center justify-content-between">
                                    <h6 class="card-title mb-0" style="font-weight: bold; font-size: 1.1rem; color: #333;">
                                        Collection List (All Records - Showing ALL Collections)
                                    </h6>
                                    <div class="dropdown">
                                        <button class="btn btn-sm btn-outline-secondary dropdown-toggle" type="button" data-bs-toggle="dropdown" aria-expanded="false">Filter</button>
                                        <ul class="dropdown-menu">
                                            <li><a class="dropdown-item" href="#" onclick="filterCollectionList('all')">All</a></li>
                                            <li><a class="dropdown-item" href="#" onclick="filterCollectionList('today')">Today</a></li>
                                            <li><a class="dropdown-item" href="#" onclick="filterCollectionList('week')">This Week</a></li>
                                            <li><a class="dropdown-item" href="#" onclick="filterCollectionList('month')">This Month</a></li>
                                        </ul>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover table-striped" id="collectionTable">
                                            <thead>
                                                <tr>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">#</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Customer Name</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Agreement No / Bill</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Amount</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Date</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Type</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php echo $collectionList; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    
                    <div class="row mt-4">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header d-flex align-items-center justify-content-between">
                                    <h6 class="card-title mb-0" style="font-weight: bold; font-size: 1.1rem; color: #333;">New Customers (All Records - Showing ALL Customers)</h6>
                                    <div class="dropdown">
                                        <button class="btn btn-sm btn-outline-secondary dropdown-toggle" type="button" data-bs-toggle="dropdown" aria-expanded="false">Filter</button>
                                        <ul class="dropdown-menu">
                                            <li><a class="dropdown-item" href="#" onclick="filterNewCustomers('all')">All</a></li>
                                            <li><a class="dropdown-item" href="#" onclick="filterNewCustomers('today')">Today</a></li>
                                            <li><a class="dropdown-item" href="#" onclick="filterNewCustomers('week')">This Week</a></li>
                                            <li><a class="dropdown-item" href="#" onclick="filterNewCustomers('month')">This Month</a></li>
                                        </ul>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover table-striped" id="newCustomersTable">
                                            <thead>
                                                <tr>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">#</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Customer Name</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Agreement No</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Loan Amount</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Loan Type</th>
                                                    <th style="position: sticky; top: 0; background: #f8f9fa;">Date</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php echo $newCustomers; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                <?php include 'includes/rightbar.php'; ?>
                <?php include 'includes/footer.php'; ?>
            </div>
        </div>
    </div>
    <?php include 'includes/scripts.php'; ?>
   
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            $('#month, #year').on('change', function() {
                const month = $('#month').val();
                const year = $('#year').val();
                const daySelect = $('#day');
                const currentValue = daySelect.val();
                
                if (month !== 'all' && year !== 'all') {
                    const days = new Date(year, month, 0).getDate();
                    
                    daySelect.html('<option value="0">All Days</option>');
                    for (let d = 1; d <= days; d++) {
                        const option = $('<option>').val(d).text(d);
                        if (d == currentValue) option.prop('selected', true);
                        daySelect.append(option);
                    }
                    if (currentValue > days) daySelect.val(0);
                } else {
                    // If month or year is 'all', show up to 31 days
                    daySelect.html('<option value="0">All Days</option>');
                    for (let d = 1; d <= 31; d++) {
                        const option = $('<option>').val(d).text(d);
                        if (d == currentValue) option.prop('selected', true);
                        daySelect.append(option);
                    }
                }
            });
        });

// Print report function
        function printReport() {
            // Get all current filter values
            const month = document.getElementById('month').value;
            const year = document.getElementById('year').value;
            const day = document.getElementById('day').value;
            const startDate = document.getElementById('start_date').value;
            const endDate = document.getElementById('end_date').value;
            
            // Build query string
            const params = new URLSearchParams({
                month: month,
                year: year,
                day: day,
                start_date: startDate,
                end_date: endDate
            });
            
            // Open print page in new window
            window.open('print-report.php?' + params.toString(), '_blank');
        }

// Download PDF function
        function downloadPDF() {
            // Get all current filter values
            const month = document.getElementById('month').value;
            const year = document.getElementById('year').value;
            const day = document.getElementById('day').value;
            const startDate = document.getElementById('start_date').value;
            const endDate = document.getElementById('end_date').value;
            
            // Build query string
            const params = new URLSearchParams({
                month: month,
                year: year,
                day: day,
                start_date: startDate,
                end_date: endDate
            });
            
            // Open print page with download option
            window.open('print-report.php?' + params.toString(), '_blank');
        }

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: overall-reports.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation