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.28 TB

🔧 Quick Actions

✏️ Editing: print-report.php

<?php
// print-report.php
error_reporting(E_ALL);
ini_set('display_errors', 1);

include 'includes/db.php';

// Check if db connection exists
if (!$conn) {
    die("Database connection failed: " . mysqli_connect_error());
}

// Clear any pending results
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);

// Get filter parameters with default values
$selected_month = isset($_GET['month']) ? $_GET['month'] : date('m');
$selected_year = isset($_GET['year']) ? $_GET['year'] : date('Y');
$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 = date('Y');
    }
}

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

// 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 = '';
    }
}

// ============== ALL FUNCTIONS FROM OVERALL-REPORTS.PHP ==============

// 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";
    }
    
    $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 getCollectionListForPDF($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'])
            ];
        }
    }
    $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) {
            $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'])
            ];
        }
    }
    $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) {
            $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'])
            ];
        }
    }
    $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']
            ];
        } else {
            // Check if this is a duplicate (same agreement, bill, and similar type/time period)
            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)
    usort($merged_data, function($a, $b) {
        return $a['timestamp'] - $b['timestamp'];
    });
    
    return $merged_data;
}

// MODIFIED: Get ALL new customers with NO LIMIT
function getNewCustomersForPDF($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();
    
    $customers = [];
    while ($row = $result->fetch_assoc()) {
        $customers[] = $row;
    }
    $stmt->close();
    
    return $customers;
}

// 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']);
    }
}

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

// Get all data for report
$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);
$collections = getCollectionListForPDF($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);
$newCustomers = getNewCustomersForPDF($conn, $start_date, $end_date, $selected_month, $selected_year, $selected_day);

// Set report title
if ($start_date && $end_date) {
    $reportTitle = "Overall Report: " . date('d M Y', strtotime($start_date)) . " - " . date('d M Y', strtotime($end_date));
    $filename = "report_" . date('Ymd', strtotime($start_date)) . "_to_" . date('Ymd', strtotime($end_date)) . ".pdf";
} elseif ($selected_month === 'all' && $selected_year === 'all') {
    $reportTitle = "Overall Report: ALL Time";
    $filename = "report_all_time.pdf";
} elseif ($selected_month === 'all') {
    $reportTitle = "Overall Report: Year " . $selected_year . ($selected_day > 0 ? " - Day " . $selected_day : "");
    $filename = "report_year_" . $selected_year . ($selected_day > 0 ? "_day_" . $selected_day : "") . ".pdf";
} else {
    $monthName = date('F', mktime(0, 0, 0, $selected_month, 1));
    $reportTitle = "Overall Report: " . $monthName . " " . $selected_year . ($selected_day > 0 ? " - Day " . $selected_day : "");
    $filename = "report_" . $monthName . "_" . $selected_year . ($selected_day > 0 ? "_day_" . $selected_day : "") . ".pdf";
}

// Get finance company name
$finance_name = "Sri Selvaganapathi Finance";
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo $reportTitle; ?></title>
    <style>
        /* Print and PDF styles */
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }
        
        body {
            font-family: 'Arial', sans-serif;
            background: #fff;
            color: #333;
            line-height: 1.4;
            padding: 20px;
        }
        
        .report-container {
            max-width: 1200px;
            margin: 0 auto;
            background: #fff;
        }
        
        /* Header styles */
        .report-header {
            text-align: center;
            margin-bottom: 30px;
            padding-bottom: 20px;
            border-bottom: 2px solid #333;
        }
        
        .company-name {
            font-size: 24px;
            font-weight: bold;
            color: #000;
            margin-bottom: 5px;
        }
        
        .report-title {
            font-size: 18px;
            color: #444;
            margin-bottom: 5px;
        }
        
        .report-date {
            font-size: 12px;
            color: #666;
        }
        
        /* Summary cards */
        .summary-section {
            margin-bottom: 30px;
        }
        
        .section-title {
            font-size: 18px;
            font-weight: bold;
            margin-bottom: 15px;
            padding-bottom: 5px;
            border-bottom: 1px solid #333;
        }
        
        .cards-grid {
            display: grid;
            grid-template-columns: repeat(4, 1fr);
            gap: 15px;
            margin-bottom: 15px;
        }
        
        .cards-grid-3 {
            display: grid;
            grid-template-columns: repeat(3, 1fr);
            gap: 15px;
            margin-bottom: 15px;
        }
        
        .cards-grid-2 {
            display: grid;
            grid-template-columns: repeat(2, 1fr);
            gap: 15px;
            margin-bottom: 15px;
        }
        
        .card {
            border: 1px solid #ddd;
            border-radius: 5px;
            overflow: hidden;
            page-break-inside: avoid;
        }
        
        .card-header {
            padding: 10px;
            font-weight: bold;
            text-align: center;
            color: #fff;
        }
        
        .card-body {
            padding: 15px;
            text-align: center;
            background: #f9f9f9;
        }
        
        .card-amount {
            font-size: 18px;
            font-weight: bold;
            margin-bottom: 5px;
        }
        
        .card-label {
            font-size: 12px;
            color: #666;
        }
        
        /* Color classes */
        .bg-primary { background-color: #007bff; }
        .bg-success { background-color: #28a745; }
        .bg-info { background-color: #17a2b8; }
        .bg-warning { background-color: #ffc107; color: #000; }
        .bg-danger { background-color: #dc3545; }
        .bg-dark { background-color: #343a40; }
        .bg-secondary { background-color: #6c757d; }
        .bg-purple { background-color: #6f42c1; }
        
        /* Tables */
        .table-container {
            margin-bottom: 30px;
            page-break-inside: avoid;
        }
        
        table {
            width: 100%;
            border-collapse: collapse;
            font-size: 12px;
        }
        
        th {
            background: #343a40;
            color: #fff;
            padding: 8px;
            text-align: center;
            font-weight: bold;
        }
        
        td {
            padding: 6px 8px;
            border: 1px solid #ddd;
        }
        
        tr:nth-child(even) {
            background: #f9f9f9;
        }
        
        .total-row {
            font-weight: bold;
            background: #e9ecef !important;
        }
        
        /* Cash flow section */
        .cashflow-section {
            margin-bottom: 30px;
        }
        
        .cashflow-grid {
            display: grid;
            grid-template-columns: repeat(2, 1fr);
            gap: 20px;
            margin-bottom: 20px;
        }
        
        .cashflow-card {
            border: 1px solid #ddd;
            border-radius: 5px;
        }
        
        .cashflow-header {
            padding: 10px;
            font-weight: bold;
            color: #fff;
            text-align: center;
        }
        
        .cashflow-body {
            padding: 15px;
            background: #f9f9f9;
        }
        
        .cashflow-row {
            display: flex;
            justify-content: space-between;
            padding: 5px 0;
            border-bottom: 1px dotted #ddd;
        }
        
        .cashflow-total {
            font-weight: bold;
            font-size: 16px;
            padding: 10px 0;
            border-top: 2px solid #333;
        }
        
        .current-balance {
            text-align: center;
            margin-top: 20px;
            padding: 15px;
            background: #17a2b8;
            color: #fff;
            border-radius: 5px;
        }
        
        .current-balance .label {
            font-size: 14px;
        }
        
        .current-balance .amount {
            font-size: 24px;
            font-weight: bold;
        }
        
        /* Footer */
        .report-footer {
            margin-top: 30px;
            padding-top: 20px;
            border-top: 1px solid #ddd;
            font-size: 11px;
            color: #666;
            text-align: center;
        }
        
        .notes-section {
            margin-top: 20px;
            padding: 15px;
            background: #f8f9fa;
            border-left: 3px solid #17a2b8;
        }
        
        .notes-section h4 {
            margin-bottom: 10px;
        }
        
        .notes-section ul {
            list-style: none;
            padding-left: 0;
        }
        
        .notes-section li {
            margin-bottom: 5px;
            font-size: 11px;
        }
        
        /* Page break */
        .page-break {
            page-break-before: always;
        }
        
        /* Print styles */
        @media print {
            body {
                padding: 0;
            }
            
            .no-print {
                display: none;
            }
            
            .card {
                break-inside: avoid;
            }
            
            table {
                break-inside: avoid;
            }
        }
        
        /* Download button */
        .download-btn {
            position: fixed;
            top: 20px;
            right: 20px;
            padding: 10px 20px;
            background: #007bff;
            color: #fff;
            border: none;
            border-radius: 5px;
            cursor: pointer;
            font-size: 14px;
            z-index: 1000;
        }
        
        .download-btn:hover {
            background: #0056b3;
        }
    </style>
</head>
<body>
    <button class="download-btn no-print" onclick="downloadPDF()">Download PDF</button>
    
    <div class="report-container" id="report-content">
        
        <div class="report-header">
            <div class="company-name"><?php echo $finance_name; ?></div>
            <div class="report-title"><?php echo $reportTitle; ?></div>
            <div class="report-date">Generated on: <?php echo date('d-m-Y H:i:s'); ?></div>
        </div>
        
        
        <div class="summary-section">
            <div class="section-title">Cash Position Summary</div>
            
            
            <div class="cards-grid">
                <div class="card">
                    <div class="card-header bg-primary">Investment (Period)</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $totalInvestment; ?></div>
                        <div class="card-label">Investment in selected period</div>
                    </div>
                </div>
                <div class="card">
                    <div class="card-header bg-info">Balance (Period End)</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $currentBalance; ?></div>
                        <div class="card-label">Balance at period end</div>
                    </div>
                </div>
                <div class="card">
                    <div class="card-header bg-warning">Loans Issued</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $totalLoanIssued; ?></div>
                        <div class="card-label">Principal disbursed</div>
                    </div>
                </div>
                <div class="card">
                    <div class="card-header bg-success">Regular EMI</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $totalEMIReceived; ?></div>
                        <div class="card-label">EMI collected</div>
                    </div>
                </div>
            </div>
            
            
            <div class="cards-grid">
                <div class="card">
                    <div class="card-header bg-dark">Foreclosure</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $emiDetails['foreclosure_total']; ?></div>
                        <div class="card-label">Foreclosure amount</div>
                    </div>
                </div>
                <div class="card">
                    <div class="card-header bg-secondary">Document Charges</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $totalDocumentCharges; ?></div>
                        <div class="card-label">Document fees</div>
                    </div>
                </div>
                <div class="card">
                    <div class="card-header bg-danger">Expenses</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $totalExpenses; ?></div>
                        <div class="card-label">Total expenses</div>
                    </div>
                </div>
                <div class="card">
                    <div class="card-header bg-purple">Excess Payments</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $totalExcessPayments; ?></div>
                        <div class="card-label">Excess payments</div>
                    </div>
                </div>
            </div>
            
            
            <div class="cards-grid-3">
                <div class="card">
                    <div class="card-header bg-dark">Outstanding Principal</div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $outstandingAmount; ?></div>
                        <div class="card-label">Pending principal</div>
                    </div>
                </div>
                <div class="card">
                    <div class="card-header <?php echo (floatval(str_replace(',', '', $profitLoss)) >= 0) ? 'bg-success' : 'bg-danger'; ?>">
                        Profit / Loss
                    </div>
                    <div class="card-body">
                        <div class="card-amount">₹<?php echo $profitLoss; ?></div>
                        <div class="card-label">Net profit/loss</div>
                    </div>
                </div>
            </div>
        </div>
        
        
        <div class="cashflow-section page-break">
            <div class="section-title">Cash Flow Breakdown</div>
            
            
            <div class="cashflow-grid">
                <div class="cashflow-card">
                    <div class="cashflow-header bg-primary">Opening Position</div>
                    <div class="cashflow-body">
                        <div class="cashflow-row">
                            <span>Previous Balance:</span>
                            <span>₹<?php echo $cashFlow['previous_balance']; ?></span>
                        </div>
                        <div class="cashflow-row">
                            <span>Period Investment:</span>
                            <span>₹<?php echo $cashFlow['period_investment']; ?></span>
                        </div>
                    </div>
                </div>
                
                
                <div class="cashflow-card">
                    <div class="cashflow-header bg-success">Cash Inflow (Selected Period)</div>
                    <div class="cashflow-body">
                        <div class="cashflow-row">
                            <span>Regular EMI:</span>
                            <span>₹<?php echo $cashFlow['regular_emi_income']; ?></span>
                        </div>
                        <div class="cashflow-row" style="padding-left: 20px;">
                            <span>- Principal:</span>
                            <span>₹<?php echo $cashFlow['regular_principal']; ?></span>
                        </div>
                        <div class="cashflow-row" style="padding-left: 20px;">
                            <span>- Interest:</span>
                            <span>₹<?php echo $cashFlow['regular_interest']; ?></span>
                        </div>
                        <div class="cashflow-row" style="padding-left: 20px;">
                            <span>- Overdue:</span>
                            <span>₹<?php echo $cashFlow['regular_overdue']; ?></span>
                        </div>
                        <div class="cashflow-row">
                            <span>Foreclosure:</span>
                            <span>₹<?php echo $cashFlow['foreclosure_income']; ?></span>
                        </div>
                        <div class="cashflow-row">
                            <span>Document Charges:</span>
                            <span>₹<?php echo $cashFlow['document_charges']; ?></span>
                        </div>
                        <div class="cashflow-row">
                            <span>Excess Payments:</span>
                            <span>₹<?php echo $cashFlow['excess_payments']; ?></span>
                        </div>
                        <div class="cashflow-row cashflow-total">
                            <span>Total Inflow:</span>
                            <span>₹<?php echo $cashFlow['total_income']; ?></span>
                        </div>
                    </div>
                </div>
            </div>
            
            
            <div class="cashflow-grid">
                <div class="cashflow-card">
                    <div class="cashflow-header bg-danger">Cash Outflow (Selected Period)</div>
                    <div class="cashflow-body">
                        <div class="cashflow-row">
                            <span>Expenses:</span>
                            <span>₹<?php echo $cashFlow['total_expenses']; ?></span>
                        </div>
                        <div class="cashflow-row">
                            <span>Loans Issued:</span>
                            <span>₹<?php echo $cashFlow['loans_issued']; ?></span>
                        </div>
                        <?php 
                        $totalOutflow = number_format(
                            floatval(str_replace(',', '', $cashFlow['total_expenses'])) + 
                            floatval(str_replace(',', '', $cashFlow['loans_issued'])), 
                            2
                        );
                        ?>
                        <div class="cashflow-row cashflow-total">
                            <span>Total Outflow:</span>
                            <span>₹<?php echo $totalOutflow; ?></span>
                        </div>
                    </div>
                </div>
                
                
                <div class="cashflow-card">
                    <div class="cashflow-header bg-info">Net Position</div>
                    <div class="cashflow-body">
                        <?php 
                        $netFlow = floatval(str_replace(',', '', $cashFlow['total_income'])) - 
                                  floatval(str_replace(',', '', $cashFlow['total_expenses']));
                        ?>
                        <div class="cashflow-row">
                            <span>Net Cash Flow:</span>
                            <span>₹<?php echo number_format($netFlow, 2); ?></span>
                        </div>
                        <div class="cashflow-row">
                            <span>Net Change (inc. loans):</span>
                            <span>₹<?php echo number_format($netFlow - floatval(str_replace(',', '', $cashFlow['loans_issued'])), 2); ?></span>
                        </div>
                    </div>
                </div>
            </div>
            
            
            <div class="current-balance">
                <div class="label">Current Balance (Period End)</div>
                <div class="amount">₹<?php echo $cashFlow['current_balance']; ?></div>
            </div>
        </div>
        
        
        <div class="table-container">
            <div class="section-title">Regular EMI Breakdown</div>
            <table>
                <thead>
                    <tr>
                        <th>Principal</th>
                        <th>Interest</th>
                        <th>Overdue</th>
                        <th>Total</th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td style="text-align: right;">₹<?php echo $cashFlow['regular_principal']; ?></td>
                        <td style="text-align: right;">₹<?php echo $cashFlow['regular_interest']; ?></td>
                        <td style="text-align: right;">₹<?php echo $cashFlow['regular_overdue']; ?></td>
                        <td style="text-align: right; font-weight: bold;">₹<?php echo $cashFlow['regular_emi_income']; ?></td>
                    </tr>
                </tbody>
            </table>
        </div>
        
        
        <?php if (floatval(str_replace(',', '', $emiDetails['foreclosure_total'])) > 0): ?>
        <div class="table-container">
            <div class="section-title">Foreclosure Breakdown</div>
            <table>
                <thead>
                    <tr>
                        <th>Principal</th>
                        <th>Interest</th>
                        <th>Overdue</th>
                        <th>Charges</th>
                        <th>Total</th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td style="text-align: right;">₹<?php echo $emiDetails['foreclosure_principal']; ?></td>
                        <td style="text-align: right;">₹<?php echo $emiDetails['foreclosure_interest']; ?></td>
                        <td style="text-align: right;">₹<?php echo $emiDetails['foreclosure_overdue']; ?></td>
                        <td style="text-align: right;">₹<?php echo $emiDetails['foreclosure_charge']; ?></td>
                        <td style="text-align: right; font-weight: bold;">₹<?php echo $emiDetails['foreclosure_total']; ?></td>
                    </tr>
                </tbody>
            </table>
        </div>
        <?php endif; ?>
        
        
        <div class="table-container page-break">
            <div class="section-title">Collection List (<?php echo count($collections); ?> records)</div>
            <?php if (count($collections) > 0): ?>
            <table>
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Customer Name</th>
                        <th>Agreement No / Bill</th>
                        <th>Amount</th>
                        <th>Date</th>
                        <th>Type</th>
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    $counter = 1;
                    $totalAmount = 0;
                    foreach ($collections as $item): 
                        $totalAmount += $item['amount'];
                        $typeDisplay = $item['type'];
                        if (count($item['types']) > 1) {
                            $typeDisplay = 'Mixed';
                        }
                    ?>
                    <tr>
                        <td style="text-align: center;"><?php echo $counter++; ?></td>
                        <td><?php echo htmlspecialchars($item['customer_name']); ?></td>
                        <td>
                            <?php echo $item['agreement_number']; ?>
                            <?php if (isset($item['bill_number']) && $item['bill_number']): ?>
                                / B:<?php echo $item['bill_number']; ?>
                            <?php endif; ?>
                            <?php if ($item['row_count'] > 1): ?>
                                <br><small>(Merged <?php echo $item['row_count']; ?> payments)</small>
                            <?php endif; ?>
                        </td>
                        <td style="text-align: right;">₹<?php echo number_format($item['amount'], 2); ?></td>
                        <td style="text-align: center;"><?php echo date('d-m-Y', strtotime($item['paid_date'])); ?></td>
                        <td style="text-align: center;"><?php echo $typeDisplay; ?></td>
                    </tr>
                    <?php endforeach; ?>
                    <tr class="total-row">
                        <td colspan="3" style="text-align: right; font-weight: bold;">Total Collections:</td>
                        <td style="text-align: right; font-weight: bold;">₹<?php echo number_format($totalAmount, 2); ?></td>
                        <td colspan="2"></td>
                    </tr>
                </tbody>
            </table>
            <?php else: ?>
            <p style="text-align: center; padding: 20px; background: #f8f9fa;">No collections found for the selected period.</p>
            <?php endif; ?>
        </div>
        
        
        <div class="table-container">
            <div class="section-title">New Customers (<?php echo count($newCustomers); ?> customers)</div>
            <?php if (count($newCustomers) > 0): ?>
            <table>
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Customer Name</th>
                        <th>Agreement No</th>
                        <th>Loan Amount</th>
                        <th>Loan Type</th>
                        <th>Date</th>
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    $counter = 1;
                    $totalLoan = 0;
                    foreach ($newCustomers as $customer): 
                        $totalLoan += $customer['loan_amount'];
                    ?>
                    <tr>
                        <td style="text-align: center;"><?php echo $counter++; ?></td>
                        <td><?php echo htmlspecialchars($customer['customer_name']); ?></td>
                        <td><?php echo $customer['agreement_number']; ?></td>
                        <td style="text-align: right;">₹<?php echo number_format($customer['loan_amount'], 2); ?></td>
                        <td><?php echo $customer['loan_name']; ?></td>
                        <td style="text-align: center;"><?php echo date('d-m-Y', strtotime($customer['emi_date'])); ?></td>
                    </tr>
                    <?php endforeach; ?>
                    <tr class="total-row">
                        <td colspan="3" style="text-align: right; font-weight: bold;">Total Loans Issued:</td>
                        <td style="text-align: right; font-weight: bold;">₹<?php echo number_format($totalLoan, 2); ?></td>
                        <td colspan="2"><?php echo count($newCustomers); ?> customers</td>
                    </tr>
                </tbody>
            </table>
            <?php else: ?>
            <p style="text-align: center; padding: 20px; background: #f8f9fa;">No new customers found for the selected period.</p>
            <?php endif; ?>
        </div>
        
        
        <div class="notes-section">
            <h4>Important Notes:</h4>
            <ul>
                <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>
                <li>• Current Balance = Previous Balance + Period Investment + Total Income - Expenses - Loans Issued</li>
                <li>• Total Income = Regular EMI + Foreclosure + Document Charges + Excess Payments</li>
            </ul>
        </div>
        
        
        <div class="report-footer">
            <div>Generated by Finance Management System on <?php echo date('d-m-Y H:i:s'); ?></div>
            <div>This is a system generated report</div>
        </div>
    </div>
    
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/html2pdf.js/0.10.1/html2pdf.bundle.min.js"></script>
    <script>
        function downloadPDF() {
            const element = document.getElementById('report-content');
            const opt = {
                margin:        [0.5, 0.5, 0.5, 0.5],
                filename:     '<?php echo $filename; ?>',
                image:        { type: 'jpeg', quality: 0.98 },
                html2canvas:  { scale: 2, letterRendering: true, useCORS: true, logging: false },
                jsPDF:        { unit: 'in', format: 'a4', orientation: 'portrait' }
            };
            
            // Show loading message
            const btn = document.querySelector('.download-btn');
            const originalText = btn.innerText;
            btn.innerText = 'Generating PDF...';
            btn.disabled = true;
            
            // Generate PDF
            html2pdf().set(opt).from(element).save().then(() => {
                btn.innerText = originalText;
                btn.disabled = false;
            }).catch(error => {
                console.error('PDF generation error:', error);
                alert('Error generating PDF. Please try again.');
                btn.innerText = originalText;
                btn.disabled = false;
            });
        }
        
        // Print function
        function printReport() {
            window.print();
        }
    </script>
</body>
</html>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: print-report.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation