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/sp/admin

Free Space:

10.28 TB

🔧 Quick Actions

✏️ Editing: profit.php

<?php
// Enable error logging
ini_set('display_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', '/home/u329947844/domains/hifi11.in/public_html/finance/error_log');

// Database connection
include 'includes/db.php';

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

// Handle filter from GET parameters (optional, default to overall data)
$selected_month = isset($_GET['month']) ? intval($_GET['month']) : null;
$selected_year = isset($_GET['year']) ? intval($_GET['year']) : null;
$filter_active = !is_null($selected_month) && !is_null($selected_year);

// Validate selected month and year if provided
if ($filter_active) {
    if ($selected_month < 1 || $selected_month > 12) {
        $selected_month = $current_month;
    }
    if ($selected_year < 2000 || $selected_year > date('Y') + 1) {
        $selected_year = $current_year;
    }
}

// Fetch current balance from total_balance table (latest balance)
$sql_balance = "SELECT balance FROM total_balance ORDER BY id DESC LIMIT 1";
$result_balance = $conn->query($sql_balance);
$current_balance = $result_balance && $result_balance->num_rows > 0 ? $result_balance->fetch_assoc()['balance'] : 1000000.00;

// Define total investment (static as per requirement)
$total_investment = 1000000.00;

// Fetch total loan amount issued
$sql_loan = "SELECT SUM(loan_amount) AS total_loan FROM customers";
if ($filter_active) {
    $sql_loan .= " WHERE MONTH(emi_date) = ? AND YEAR(emi_date) = ?";
    $stmt_loan = $conn->prepare($sql_loan);
    $stmt_loan->bind_param("ii", $selected_month, $selected_year);
    $stmt_loan->execute();
    $result_loan = $stmt_loan->get_result();
} else {
    $result_loan = $conn->query($sql_loan);
}
$total_loan = $result_loan ? $result_loan->fetch_assoc()['total_loan'] ?: 0 : 0;

// Fetch total document charge
$sql_doc_charge = "SELECT SUM(document_charge) AS total_doc_charge FROM customers";
if ($filter_active) {
    $sql_doc_charge .= " WHERE MONTH(emi_date) = ? AND YEAR(emi_date) = ?";
    $stmt_doc_charge = $conn->prepare($sql_doc_charge);
    $stmt_doc_charge->bind_param("ii", $selected_month, $selected_year);
    $stmt_doc_charge->execute();
    $result_doc_charge = $stmt_doc_charge->get_result();
} else {
    $result_doc_charge = $conn->query($sql_doc_charge);
}
$total_doc_charge = $result_doc_charge ? $result_doc_charge->fetch_assoc()['total_doc_charge'] ?: 0 : 0;

// Fetch total EMI and overdue paid
if ($filter_active) {
    $sql_emi_paid = "SELECT SUM(emi_amount + overdue_charges) AS total_paid
                     FROM emi_schedule
                     WHERE status = 'paid' AND MONTH(paid_date) = ? AND YEAR(paid_date) = ?";
    $stmt_emi_paid = $conn->prepare($sql_emi_paid);
    $total_emi_paid = 0;
    if ($stmt_emi_paid) {
        $stmt_emi_paid->bind_param("ii", $selected_month, $selected_year);
        $stmt_emi_paid->execute();
        $result_emi_paid = $stmt_emi_paid->get_result();
        $total_emi_paid = $result_emi_paid->fetch_assoc()['total_paid'] ?: 0;
    } else {
        error_log("Error preparing EMI paid query: " . $conn->error);
    }
} else {
    $sql_emi_paid = "SELECT SUM(emi_amount + overdue_charges) AS total_paid
                     FROM emi_schedule
                     WHERE status = 'paid'";
    $result_emi_paid = $conn->query($sql_emi_paid);
    $total_emi_paid = $result_emi_paid ? $result_emi_paid->fetch_assoc()['total_paid'] ?: 0 : 0;
}

// Fetch total interest amount for paid EMIs
if ($filter_active) {
    $sql_interest = "SELECT SUM(interest_amount) AS total_interest
                     FROM emi_schedule
                     WHERE status = 'paid' AND MONTH(paid_date) = ? AND YEAR(paid_date) = ?";
    $stmt_interest = $conn->prepare($sql_interest);
    $total_interest = 0;
    if ($stmt_interest) {
        $stmt_interest->bind_param("ii", $selected_month, $selected_year);
        $stmt_interest->execute();
        $result_interest = $stmt_interest->get_result();
        $total_interest = $result_interest->fetch_assoc()['total_interest'] ?: 0;
    } else {
        error_log("Error preparing interest query: " . $conn->error);
    }
} else {
    $sql_interest = "SELECT SUM(interest_amount) AS total_interest
                     FROM emi_schedule
                     WHERE status = 'paid'";
    $result_interest = $conn->query($sql_interest);
    $total_interest = $result_interest ? $result_interest->fetch_assoc()['total_interest'] ?: 0 : 0;
}

// Fetch total expenses
if ($filter_active) {
    $sql_expenses = "SELECT SUM(amount) AS total_expenses
                     FROM expenses
                     WHERE MONTH(expense_date) = ? AND YEAR(expense_date) = ?";
    $stmt_expenses = $conn->prepare($sql_expenses);
    $total_expenses = 0;
    if ($stmt_expenses) {
        $stmt_expenses->bind_param("ii", $selected_month, $selected_year);
        $stmt_expenses->execute();
        $result_expenses = $stmt_expenses->get_result();
        $total_expenses = $result_expenses->fetch_assoc()['total_expenses'] ?: 0;
    } else {
        error_log("Error preparing expenses query: " . $conn->error);
    }
} else {
    $sql_expenses = "SELECT SUM(amount) AS total_expenses FROM expenses";
    $result_expenses = $conn->query($sql_expenses);
    $total_expenses = $result_expenses ? $result_expenses->fetch_assoc()['total_expenses'] ?: 0 : 0;
}

// Fetch total overdue amount for unpaid EMIs
if ($filter_active) {
    $sql_overdue = "SELECT emi_amount, overdue_charges, emi_due_date
                    FROM emi_schedule
                    WHERE status = 'unpaid' AND MONTH(emi_due_date) = ? AND YEAR(emi_due_date) = ?";
    $stmt_overdue = $conn->prepare($sql_overdue);
    $total_overdue = 0;
    if ($stmt_overdue) {
        $stmt_overdue->bind_param("ii", $selected_month, $selected_year);
        $stmt_overdue->execute();
        $result_overdue = $stmt_overdue->get_result();
        while ($row = $result_overdue->fetch_assoc()) {
            $overdue_charges = $row['overdue_charges'];
            if ($row['emi_due_date'] < $current_date) {
                $date_diff = (strtotime($current_date) - strtotime($row['emi_due_date'])) / (60 * 60 * 24);
                $overdue_charges = ($row['emi_amount'] / 1000) * 2 * $date_diff;
            }
            $total_overdue += $overdue_charges;
        }
    } else {
        error_log("Error preparing overdue query: " . $conn->error);
    }
} else {
    $sql_overdue = "SELECT emi_amount, overdue_charges, emi_due_date
                    FROM emi_schedule
                    WHERE status = 'unpaid'";
    $result_overdue = $conn->query($sql_overdue);
    $total_overdue = 0;
    if ($result_overdue) {
        while ($row = $result_overdue->fetch_assoc()) {
            $overdue_charges = $row['overdue_charges'];
            if ($row['emi_due_date'] < $current_date) {
                $date_diff = (strtotime($current_date) - strtotime($row['emi_due_date'])) / (60 * 60 * 24);
                $overdue_charges = ($row['emi_amount'] / 1000) * 2 * $date_diff;
            }
            $total_overdue += $overdue_charges;
        }
    } else {
        error_log("Error preparing overdue query: " . $conn->error);
    }
}

// Calculate profit/loss
$profit_loss = $total_emi_paid - $total_expenses;

// Fetch transaction history from total_balance
if ($filter_active) {
    $sql_transactions = "SELECT transaction_type, amount, balance, description, transaction_date
                         FROM total_balance
                         WHERE MONTH(transaction_date) = ? AND YEAR(transaction_date) = ?
                         ORDER BY transaction_date DESC";
    $stmt_transactions = $conn->prepare($sql_transactions);
    $transactions = false;
    if ($stmt_transactions) {
        $stmt_transactions->bind_param("ii", $selected_month, $selected_year);
        $stmt_transactions->execute();
        $transactions = $stmt_transactions->get_result();
    } else {
        error_log("Error preparing transactions query: " . $conn->error);
    }
} else {
    $sql_transactions = "SELECT transaction_type, amount, balance, description, transaction_date
                         FROM total_balance
                         ORDER BY transaction_date DESC";
    $result_transactions = $conn->query($sql_transactions);
    $transactions = $result_transactions ?: false;
}

// Handle EMI payment
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['pay_emi'])) {
    $emi_id = intval($_POST['emi_id']);
    $sql_pay_emi = "UPDATE emi_schedule SET status = 'paid', paid_date = ? WHERE id = ?";
    $stmt_pay_emi = $conn->prepare($sql_pay_emi);
    if ($stmt_pay_emi) {
        $stmt_pay_emi->bind_param("si", $current_date, $emi_id);
        if ($stmt_pay_emi->execute()) {
            // Fetch EMI details
            $sql_emi_details = "SELECT emi_amount, overdue_charges, interest_amount, customer_id FROM emi_schedule WHERE id = ?";
            $stmt_emi_details = $conn->prepare($sql_emi_details);
            if ($stmt_emi_details) {
                $stmt_emi_details->bind_param("i", $emi_id);
                $stmt_emi_details->execute();
                $emi_details = $stmt_emi_details->get_result()->fetch_assoc();
                $total_received = $emi_details['emi_amount'] + $emi_details['overdue_charges'] + $emi_details['interest_amount'];
                $customer_id = $emi_details['customer_id'];
                // Fetch document charge for the customer
                $sql_doc_charge = "SELECT document_charge FROM customers WHERE id = ?";
                $stmt_doc_charge = $conn->prepare($sql_doc_charge);
                $doc_charge = 0;
                if ($stmt_doc_charge) {
                    $stmt_doc_charge->bind_param("i", $customer_id);
                    $stmt_doc_charge->execute();
                    $result_doc_charge = $stmt_doc_charge->get_result();
                    $doc_charge = $result_doc_charge->fetch_assoc()['document_charge'] ?: 0;
                }
                $total_received += $doc_charge;
                // Update balance
                $new_balance = $current_balance + $total_received;
                $sql_update_balance = "INSERT INTO total_balance (transaction_type, amount, balance, description, transaction_date)
                                       VALUES (?, ?, ?, ?, ?)";
                $stmt_update_balance = $conn->prepare($sql_update_balance);
                if ($stmt_update_balance) {
                    $transaction_type = $emi_details['overdue_charges'] > 0 ? 'overdue_paid' : 'emi_paid';
                    $description = "EMI payment for Customer ID: $customer_id (EMI ID: $emi_id, Includes Doc Charge: Rs. $doc_charge)";
                    $stmt_update_balance->bind_param("sddss", $transaction_type, $total_received, $new_balance, $description, $current_date);
                    $stmt_update_balance->execute();
                }
            }
            header("Location: profit.php" . ($filter_active ? "?month=$selected_month&year=$selected_year" : ""));
            exit;
        } else {
            error_log("Error paying EMI: " . $conn->error);
        }
    }
}

mysqli_close($conn);
?>

<!DOCTYPE html>
<html lang="en" dir="ltr" data-startbar="dark" data-bs-theme="light">
<head>
    <?php include 'includes/head.php'; ?>
    
    <style>
        body {
            background-color: #f8fafc;
          
            font-size: 16px;
        }
        .card {
            border: none;
            border-radius: 10px;
            box-shadow: 0 2px 8px rgba(0, 0, 0, 0.1);
            margin-bottom: 1.5rem;
            transition: transform 0.3s, box-shadow 0.3s;
        }
        .card:hover {
            transform: translateY(-4px);
            box-shadow: 0 4px 12px rgba(0, 0, 0, 0.15);
        }
        .card-header {
            background-color: #ffffff;
            border-bottom: 1px solid #e5e7eb;
            padding: 1rem;
            border-radius: 10px 10px 0 0;
        }
        .card-title {
            font-size: calc(1.1rem + 0.3vw);
            font-weight: 600;
            color: #1f2937;
            margin-bottom: 0;
        }
        .metric-card {
            border-radius: 8px;
            padding: 1rem;
            text-align: center;
            background-color: #ffffff;
            box-shadow: 0 2px 6px rgba(0, 0, 0, 0.08);
            position: relative;
            min-height: 120px;
        }
        .metric-card h5 {
            font-size: calc(0.9rem + 0.2vw);
            font-weight: 500;
            color: #374151;
            margin-bottom: 0.5rem;
        }
        .metric-card p {
            font-size: calc(1.2rem + 0.3vw);
            font-weight: 600;
            color: #1f2937;
            margin-bottom: 0.25rem;
        }
        .metric-card small {
            font-size: calc(0.7rem + 0.1vw);
            color: #6b7280;
        }
        .btn-primary {
            background: linear-gradient(to right, #2563eb, #3b82f6);
            border: none;
            border-radius: 6px;
            padding: 0.5rem 1.25rem;
            font-size: calc(0.85rem + 0.1vw);
            min-height: 44px;
            transition: background 0.3s, transform 0.3s;
        }
        .btn-primary:hover {
            background: linear-gradient(to right, #1d4ed8, #2563eb);
            transform: translateY(-2px);
        }
        .btn-light {
            background-color: #f3f4f6;
            border-color: #d1d5db;
            color: #374151;
            border-radius: 6px;
            padding: 0.5rem 1.25rem;
            font-size: calc(0.85rem + 0.1vw);
            min-height: 44px;
            transition: background-color 0.3s, transform 0.3s;
        }
        .btn-light:hover {
            background-color: #e5e7eb;
            transform: translateY(-2px);
        }
        .btn-sm.btn-success {
            background: linear-gradient(to right, #10b981, #34d399);
            border: none;
            border-radius: 6px;
            padding: 0.4rem 0.75rem;
            font-size: calc(0.75rem + 0.1vw);
            min-height: 36px;
            transition: background 0.3s, transform 0.3s;
        }
        .btn-sm.btn-success:hover {
            background: linear-gradient(to right, #059669, #10b981);
            transform: translateY(-2px);
        }
        .form-control {
            border: 1px solid #d1d5db;
            border-radius: 6px;
            padding: 0.5rem 0.75rem;
            font-size: calc(0.85rem + 0.1vw);
            min-height: 44px;
            transition: border-color 0.3s, box-shadow 0.3s;
        }
        .form-control:focus {
            border-color: #2563eb;
            box-shadow: 0 0 0 3px rgba(37, 99, 235, 0.1);
            outline: none;
        }
        .form-label {
            font-size: calc(0.8rem + 0.1vw);
            font-weight: 500;
            color: #374151;
            margin-bottom: 0.3rem;
        }
        .table th {
            background-color: #f9fafb;
            color: #1f2937;
            font-weight: 600;
            font-size: calc(0.85rem + 0.1vw);
            padding: 0.75rem;
            border-bottom: 2px solid #e5e7eb;
            position: sticky;
            top: 0;
            z-index: 10;
        }
        .table td {
            padding: 0.75rem;
            font-size: calc(0.8rem + 0.1vw);
            color: #4b5563;
            border-bottom: 1px solid #e5e7eb;
        }
        .table-striped tbody tr:nth-of-type(odd) {
            background-color: #f9fafb;
        }
        .table-responsive {
            max-height: 400px;
            overflow-y: auto;
        }
        @media (max-width: 767px) {
            .table-responsive {
                max-height: none;
            }
            .table {
                display: block;
            }
            .table thead {
                display: none;
            }
            .table tbody, .table tr, .table td {
                display: block;
                width: 100%;
            }
            .table tr {
                margin-bottom: 1rem;
                border-bottom: 1px solid #e5e7eb;
            }
            .table td {
                padding: 0.5rem;
                position: relative;
                text-align: left;
            }
            .table td:before {
                content: attr(data-label);
                font-weight: 600;
                color: #1f2937;
                display: block;
                margin-bottom: 0.25rem;
            }
            .table td:last-child {
                border-bottom: none;
            }
        }
        .alert-info {
            background-color: #dbeafe;
            border-color: #bfdbfe;
            color: #1e40af;
            border-radius: 6px;
            padding: 0.75rem;
            font-size: calc(0.85rem + 0.1vw);
        }
        .dropdown-menu {
            border-radius: 6px;
            box-shadow: 0 4px 12px rgba(0, 0, 0, 0.1);
            border: none;
            min-width: 150px;
        }
        .dropdown-item {
            font-size: calc(0.8rem + 0.1vw);
            padding: 0.5rem 1rem;
            color: #374151;
        }
        .dropdown-item:hover {
            background-color: #f3f4f6;
        }
        .breadcrumb {
            background-color: transparent;
            padding: 0.5rem 0;
            font-size: calc(0.8rem + 0.1vw);
        }
        .breadcrumb-item.active {
            color: #2563eb;
            font-weight: 500;
        }
        .text-primary {
            color: #2563eb !important;
        }
        .text-muted {
            color: #6b7280 !important;
        }
        .text-success {
            color: #10b981 !important;
        }
        .text-danger {
            color: #ef4444 !important;
        }
        [aria-label] {
            cursor: pointer;
        }
    </style>
</head>

<div class="startbar d-print-none">
        <div class="brand">
            <a href="index.php" class="logo">
                <span>
                    <img src="assets/images/logo-sm.png" alt="logo-small" class="logo-sm">
                </span>
                <span class="">
                    <img src="assets/images/logo-light.png" alt="logo-large" class="logo-lg logo-light">
                    <img src="assets/images/logo-dark.png" alt="logo-large" class="logo-lg logo-dark">
                </span>
            </a>
        </div>
        <?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">
                    
                    <?php
                    $page_title = "Profit & Loss";
                    $breadcrumb_active = "Profit & Loss";
                    ?>
                    <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"><?php echo $page_title; ?> <?php echo $filter_active ? 'for ' . date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : '(Overall)'; ?></h4>
                                <div>
                                    <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>

<div class="row align-items-center mb-3">
                        <div class="col">
                            <h3 class="mb-0">Financial Overview</h3>
                            <small class="text-muted">Summary of profit, loss, and balance details.</small>
                        </div>
                        <div class="col-auto">
                            <a href="reports.php" class="btn btn-light" aria-label="View Reports">View Reports</a>
                            <a href="calendar.php" class="btn btn-light ms-1" aria-label="View Calendar">View Calendar</a>
                        </div>
                    </div>

<div class="card mb-4">
                        <div class="card-body">
                            <form method="GET" action="profit.php">
                                <div class="row g-3">
                                    <div class="col-12 col-md-4">
                                        <label for="month" class="form-label">Select Month</label>
                                        <select class="form-control" id="month" name="month" aria-label="Select Month">
                                            <option value="" <?php echo is_null($selected_month) ? '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 } ?>
                                        </select>
                                    </div>
                                    <div class="col-12 col-md-4">
                                        <label for="year" class="form-label">Select Year</label>
                                        <select class="form-control" id="year" name="year" aria-label="Select Year">
                                            <option value="" <?php echo is_null($selected_year) ? '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 } ?>
                                        </select>
                                    </div>
                                    <div class="col-12 col-md-4 d-flex align-items-end">
                                        <button type="submit" class="btn btn-primary" aria-label="Apply Filter">Filter</button>
                                        <?php if ($filter_active) { ?>
                                            <a href="profit.php" class="btn btn-light ms-2" aria-label="Clear Filter">Clear Filter</a>
                                        <?php } ?>
                                    </div>
                                </div>
                            </form>
                        </div>
                    </div>

<h4 class="mb-3">Balance Summary</h4>
                    <div class="card mb-4">
                        <div class="card-body">
                            <div class="row g-3">
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>Total Investment</h5>
                                        <p>₹<?php echo number_format($total_investment, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>Current Balance</h5>
                                        <p>₹<?php echo number_format($current_balance, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>Total Loan Issued</h5>
                                        <p>₹<?php echo number_format($total_loan, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>EMI Received</h5>
                                        <p>₹<?php echo number_format($total_emi_paid, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>Interest Received</h5>
                                        <p>₹<?php echo number_format($total_interest, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>Document Charges</h5>
                                        <p>₹<?php echo number_format($total_doc_charge, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>Total Expenses</h5>
                                        <p>₹<?php echo number_format($total_expenses, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>Overdue Amount</h5>
                                        <p>₹<?php echo number_format($total_overdue, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                                <div class="col-12 col-md-4 col-lg-3">
                                    <div class="metric-card">
                                        <h5>Profit/Loss</h5>
                                        <p class="<?php echo $profit_loss >= 0 ? 'text-success' : 'text-danger'; ?>">₹<?php echo number_format($profit_loss, 2); ?></p>
                                        <small class="text-muted"><?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?></small>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

<h4 class="mb-3">Transaction History</h4>
                    <div class="card mb-4">
                        <div class="card-header">
                            <div class="row align-items-center">
                                <div class="col">
                                    <h4 class="card-title">Transaction Details</h4>
                                </div>
                                <div class="col-auto">
                                    <div class="dropdown">
                                        <a href="#" class="btn btn-light dropdown-toggle" data-bs-toggle="dropdown" aria-label="Filter Transactions" aria-expanded="false">
                                            <i class="las la-calendar fs-5 me-1"></i> <?php echo $filter_active ? date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) : 'Overall'; ?><i class="las la-angle-down ms-1"></i>
                                        </a>
                                        <div class="dropdown-menu dropdown-menu-end">
                                            <a class="dropdown-item" href="profit.php">Overall</a>
                                            <a class="dropdown-item" href="?month=<?php echo $current_month; ?>&year=<?php echo $current_year; ?>">This Month</a>
                                            <a class="dropdown-item" href="?month=<?php echo date('m', strtotime('-1 month')); ?>&year=<?php echo date('Y', strtotime('-1 month')); ?>">Last Month</a>
                                            <a class="dropdown-item" href="?year=<?php echo $current_year; ?>">This Year</a>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="card-body">
                            <?php if ($transactions && $transactions->num_rows > 0) { ?>
                                
                                <div class="table-responsive d-none d-md-block">
                                    <table id="tblTransactions" class="table table-striped table-bordered align-middle w-100">
                                        <thead class="table-light">
                                            <tr>
                                                <th style="width: 20%;">Type</th>
                                                <th style="width: 20%;">Date</th>
                                                <th style="width: 15%;">Amount</th>
                                                <th style="width: 15%;">Balance</th>
                                                <th style="width: 30%;">Description</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php while ($row = $transactions->fetch_assoc()) { ?>
                                                <tr>
                                                    <td data-label="Type"><?php echo ucfirst(str_replace('_', ' ', $row['transaction_type'])); ?></td>
                                                    <td data-label="Date"><?php echo $row['transaction_date']; ?></td>
                                                    <td data-label="Amount">Rs. <?php echo number_format($row['amount'], 2); ?></td>
                                                    <td data-label="Balance">Rs. <?php echo number_format($row['balance'], 2); ?></td>
                                                    <td data-label="Description"><?php echo htmlspecialchars($row['description']); ?></td>
                                                </tr>
                                            <?php } ?>
                                        </tbody>
                                    </table>
                                </div>
                                
                                <div class="d-md-none">
                                    <?php
                                    mysqli_data_seek($transactions, 0);
                                    while ($row = $transactions->fetch_assoc()) {
                                    ?>
                                        <div class="transaction-card">
                                            <div class="card-body">
                                                <h5 class="card-title"><?php echo ucfirst(str_replace('_', ' ', $row['transaction_type'])); ?></h5>
                                                <p><strong>Date:</strong> <?php echo $row['transaction_date']; ?></p>
                                                <p><strong>Amount:</strong> Rs. <?php echo number_format($row['amount'], 2); ?></p>
                                                <div class="collapse" id="collapse-<?php echo md5($row['transaction_date'] . $row['amount']); ?>">
                                                    <div class="collapse-content">
                                                        <p><strong>Balance:</strong> Rs. <?php echo number_format($row['balance'], 2); ?></p>
                                                        <p><strong>Description:</strong> <?php echo htmlspecialchars($row['description']); ?></p>
                                                    </div>
                                                </div>
                                                <a href="#" class="text-primary text-decoration-underline" data-bs-toggle="collapse" data-bs-target="#collapse-<?php echo md5($row['transaction_date'] . $row['amount']); ?>" aria-expanded="false" aria-label="Toggle Transaction Details">Show Details</a>
                                            </div>
                                        </div>
                                    <?php } ?>
                                </div>
                            <?php } else { ?>
                                <div class="alert alert-info" role="alert">
                                    No transactions found for the selected period.
                                </div>
                            <?php } ?>
                        </div>
                    </div>

<h4 class="mb-3">Outstanding EMIs</h4>
                    <div class="card">
                        <div class="card-header">
                            <div class="row align-items-center">
                                <div class="col">
                                    <h4 class="card-title">EMI Details (To Collect)</h4>
                                </div>
                                <div class="col-auto">
                                    <a href="manage-customers.php" class="btn btn-light" aria-label="View Customers">
                                        <i class="las la-users fs-5 me-1"></i> View Customers
                                    </a>
                                </div>
                            </div>
                        </div>
                        <div class="card-body">
                            <?php
                            if ($filter_active) {
                                $sql_customers = "SELECT c.id, c.customer_name, c.customer_number, 
                                                 COALESCE(SUM(e.emi_amount + e.overdue_charges), 0) AS outstanding
                                          FROM customers c
                                          LEFT JOIN emi_schedule e ON c.id = e.customer_id
                                          WHERE MONTH(e.emi_due_date) = ? AND YEAR(e.emi_due_date) = ? AND e.status = 'unpaid'
                                          GROUP BY c.id, c.customer_name, c.customer_number";
                                $stmt_customers = $conn->prepare($sql_customers);
                                if ($stmt_customers) {
                                    $stmt_customers->bind_param("ii", $selected_month, $selected_year);
                                    $stmt_customers->execute();
                                    $customers = $stmt_customers->get_result();
                                }
                            } else {
                                $sql_customers = "SELECT c.id, c.customer_name, c.customer_number, 
                                                 COALESCE(SUM(e.emi_amount + e.overdue_charges), 0) AS outstanding
                                          FROM customers c
                                          LEFT JOIN emi_schedule e ON c.id = e.customer_id
                                          WHERE e.status = 'unpaid'
                                          GROUP BY c.id, c.customer_name, c.customer_number";
                                $customers = $conn->query($sql_customers);
                            }
                            if ($customers && $customers->num_rows > 0) { ?>
                                
                                <div class="table-responsive d-none d-md-block">
                                    <table class="table table-striped table-bordered align-middle w-100">
                                        <thead class="table-light">
                                            <tr>
                                                <th style="width: 40%;">Customer</th>
                                                <th style="width: 30%;">Outstanding</th>
                                                <th style="width: 30%;">Action</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php while ($row = $customers->fetch_assoc()) {
                                                $sql_customer_overdue = $filter_active ?
                                                    "SELECT emi_amount, emi_due_date, overdue_charges, id AS emi_id
                                                     FROM emi_schedule
                                                     WHERE customer_id = ? AND status = 'unpaid'
                                                     AND MONTH(emi_due_date) = ? AND YEAR(emi_due_date) = ?" :
                                                    "SELECT emi_amount, emi_due_date, overdue_charges, id AS emi_id
                                                     FROM emi_schedule
                                                     WHERE customer_id = ? AND status = 'unpaid'";
                                                $stmt_customer_overdue = $conn->prepare($sql_customer_overdue);
                                                if ($stmt_customer_overdue) {
                                                    if ($filter_active) {
                                                        $stmt_customer_overdue->bind_param("iii", $row['id'], $selected_month, $selected_year);
                                                    } else {
                                                        $stmt_customer_overdue->bind_param("i", $row['id']);
                                                    }
                                                    $stmt_customer_overdue->execute();
                                                    $result_customer_overdue = $stmt_customer_overdue->get_result();
                                                    $outstanding = 0;
                                                    $last_emi_id = 0;
                                                    while ($overdue_row = $result_customer_overdue->fetch_assoc()) {
                                                        $overdue_charges = $overdue_row['overdue_charges'];
                                                        if ($overdue_row['emi_due_date'] < $current_date) {
                                                            $date_diff = (strtotime($current_date) - strtotime($overdue_row['emi_due_date'])) / (60 * 60 * 24);
                                                            $overdue_charges = ($overdue_row['emi_amount'] / 1000) * 2 * $date_diff;
                                                        }
                                                        $outstanding += $overdue_row['emi_amount'] + $overdue_charges;
                                                        $last_emi_id = $overdue_row['emi_id'];
                                                    }
                                                    if ($outstanding > 0) {
                                            ?>
                                                        <tr>
                                                            <td data-label="Customer">
                                                                <div class="d-flex align-items-center">
                                                                    <div class="flex-grow-1 text-truncate">
                                                                        <h6 class="m-0 text-truncate"><?php echo htmlspecialchars($row['customer_name']); ?></h6>
                                                                        <a href="manage-customers.php?id=<?php echo $row['id']; ?>" class="font-12 text-muted text-decoration-underline">CUST-<?php echo $row['id']; ?></a>
                                                                    </div>
                                                                </div>
                                                            </td>
                                                            <td data-label="Outstanding" class="text-end">
                                                                <span class="text-primary fw-medium">₹<?php echo number_format($outstanding, 2); ?></span>
                                                            </td>
                                                            <td data-label="Action" class="text-end">
                                                                <form method="POST" action="profit.php">
                                                                    <input type="hidden" name="emi_id" value="<?php echo $last_emi_id; ?>">
                                                                    <button type="submit" name="pay_emi" class="btn btn-sm btn-success" aria-label="Pay EMI">Pay</button>
                                                                </form>
                                                            </td>
                                                        </tr>
                                                    <?php }
                                                    $stmt_customer_overdue->close();
                                                }
                                            } ?>
                                        </tbody>
                                    </table>
                                </div>
                                
                                <div class="d-md-none">
                                    <?php
                                    mysqli_data_seek($customers, 0);
                                    while ($row = $customers->fetch_assoc()) {
                                        $sql_customer_overdue = $filter_active ?
                                            "SELECT emi_amount, emi_due_date, overdue_charges, id AS emi_id
                                             FROM emi_schedule
                                             WHERE customer_id = ? AND status = 'unpaid'
                                             AND MONTH(emi_due_date) = ? AND YEAR(emi_due_date) = ?" :
                                            "SELECT emi_amount, emi_due_date, overdue_charges, id AS emi_id
                                             FROM emi_schedule
                                             WHERE customer_id = ? AND status = 'unpaid'";
                                        $stmt_customer_overdue = $conn->prepare($sql_customer_overdue);
                                        if ($stmt_customer_overdue) {
                                            if ($filter_active) {
                                                $stmt_customer_overdue->bind_param("iii", $row['id'], $selected_month, $selected_year);
                                            } else {
                                                $stmt_customer_overdue->bind_param("i", $row['id']);
                                            }
                                            $stmt_customer_overdue->execute();
                                            $result_customer_overdue = $stmt_customer_overdue->get_result();
                                            $outstanding = 0;
                                            $last_emi_id = 0;
                                            while ($overdue_row = $result_customer_overdue->fetch_assoc()) {
                                                $overdue_charges = $overdue_row['overdue_charges'];
                                                if ($overdue_row['emi_due_date'] < $current_date) {
                                                    $date_diff = (strtotime($current_date) - strtotime($overdue_row['emi_due_date'])) / (60 * 60 * 24);
                                                    $overdue_charges = ($overdue_row['emi_amount'] / 1000) * 2 * $date_diff;
                                                }
                                                $outstanding += $overdue_row['emi_amount'] + $overdue_charges;
                                                $last_emi_id = $overdue_row['emi_id'];
                                            }
                                            if ($outstanding > 0) {
                                    ?>
                                                <div class="outstanding-card">
                                                    <div class="card-body">
                                                        <h5 class="card-title"><?php echo htmlspecialchars($row['customer_name']); ?></h5>
                                                        <p><strong>Customer ID:</strong> CUST-<?php echo $row['id']; ?></p>
                                                        <p><strong>Number:</strong> <?php echo htmlspecialchars($row['customer_number']); ?></p>
                                                        <p><strong>Outstanding:</strong> ₹<?php echo number_format($outstanding, 2); ?></p>
                                                        <form method="POST" action="profit.php">
                                                            <input type="hidden" name="emi_id" value="<?php echo $last_emi_id; ?>">
                                                            <button type="submit" name="pay_emi" class="btn btn-sm btn-success" aria-label="Pay EMI">Pay</button>
                                                        </form>
                                                    </div>
                                                </div>
                                            <?php }
                                            $stmt_customer_overdue->close();
                                        }
                                    } ?>
                                </div>
                            <?php } else { ?>
                                <div class="alert alert-info" role="alert">
                                    No outstanding EMIs for this period.
                                </div>
                            <?php } ?>
                            <?php if (isset($stmt_customers)) $stmt_customers->close(); ?>
                        </div>
                    </div>

</div>

<?php include 'includes/rightbar.php'; ?>
                <?php include 'includes/footer.php'; ?>
            </div>
            
        </div>

<?php include 'includes/scripts.php'; ?>

<link rel="stylesheet" href="https://cdn.datatables.net/1.13.8/css/dataTables.bootstrap5.min.css">
        <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.8/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.8/js/dataTables.bootstrap5.min.js"></script>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: profit.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation