in this codes (<?php
// accountant/accounts_receivable_aging/index.php

session_start();
require_once '../../db_connect.php'; // Adjust path as we're in a subfolder

// Check if accountant is logged in
if (!isset($_SESSION['accountant_id'])) {
    header("Location: ../login.php"); // Adjust path to login page
    exit();
}

// Handle any actions if needed (e.g., export CSV)
// ... (Add any action handling code here)

// Fetch filters if any
$from_date = isset($_GET['from_date']) ? $_GET['from_date'] : '';
$to_date = isset($_GET['to_date']) ? $_GET['to_date'] : '';

// Build the WHERE clause based on filters
$whereClauses = [];
$params = [];

if (!empty($from_date)) {
    $whereClauses[] = 'i.due_date >= :from_date';
    $params[':from_date'] = $from_date;
}

if (!empty($to_date)) {
    $whereClauses[] = 'i.due_date <= :to_date';
    $params[':to_date'] = $to_date;
}

$whereSQL = '';
if (count($whereClauses) > 0) {
    $whereSQL = 'WHERE ' . implode(' AND ', $whereClauses);
}

// Define aging buckets
$aging_buckets = [
    '0-30' => ['min' => 0, 'max' => 30],
    '31-60' => ['min' => 31, 'max' => 60],
    '61-90' => ['min' => 61, 'max' => 90],
    'Over 90' => ['min' => 91, 'max' => PHP_INT_MAX],
];

try {
    // Fetch all invoices with outstanding balances and apply filters
    $sql = "
        SELECT 
            i.id AS invoice_id,
            i.patient_id,
            i.invoice_date,
            i.due_date,
            i.total_amount,
            i.discount,
            i.tax_rate,
            P.name AS patient_name,
            P.email,
            P.phone,
            IFNULL(SUM(PM.amount_paid), 0) AS total_paid,
            (i.total_amount - IFNULL(SUM(PM.amount_paid), 0)) AS balance_due,
            DATEDIFF(CURDATE(), i.due_date) AS days_past_due
        FROM invoices i
        JOIN patients P ON i.patient_id = P.id
        LEFT JOIN payments PM ON i.id = PM.invoice_id
        $whereSQL
        GROUP BY i.id
        HAVING balance_due > 0
        ORDER BY i.due_date ASC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $outstanding_invoices = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo "An error occurred: " . htmlspecialchars($e->getMessage());
    exit();
}

// Initialize aging report data
$aging_report = [
    '0-30' => [],
    '31-60' => [],
    '61-90' => [],
    'Over 90' => [],
];

// Process invoices into aging buckets
foreach ($outstanding_invoices as $invoice) {
    $days_past_due = $invoice['days_past_due'];
    // If due date is in the future, set days_past_due to 0
    if ($days_past_due < 0) {
        $days_past_due = 0;
    }
    foreach ($aging_buckets as $bucket => $range) {
        if ($days_past_due >= $range['min'] && $days_past_due <= $range['max']) {
            $aging_report[$bucket][] = $invoice;
            break;
        }
    }
}

// Function to sanitize output
function sanitize_output($data) {
    return htmlspecialchars($data ?? '', ENT_QUOTES, 'UTF-8');
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <!-- Meta tags for responsive design and character encoding -->
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <!-- Page title -->
    <title>Accounts Receivable Aging Report</title>
    <!-- Import Google Fonts for modern typography -->
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@400;600;700&display=swap" rel="stylesheet">
    <!-- Bootstrap 5 CSS for responsive layout and components -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- DataTables CSS for table features -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/dataTables.bootstrap5.min.css" />
    <!-- Custom CSS -->
    <style>
        /* Custom CSS */
        body {
            background-color: #f5f5f5;
            font-family: 'Poppins', Arial, sans-serif;
            margin: 0;
            padding: 0;
            min-height: 100vh;
            overflow-x: hidden;
        }

        /* Main Container */
        .main-container {
            position: relative;
            z-index: 2;
            padding: 50px 20px;
        }

        /* Dashboard Header */
        .dashboard-header {
            background: linear-gradient(135deg, #4F3F02, #D1C287);
            color: #ffffff;
            padding: 20px;
            border-radius: 10px;
            margin-bottom: 30px;
            box-shadow: 0 4px 10px rgba(0, 0, 0, 0.15);
            text-align: center;
        }

        .dashboard-header h1 {
            font-size: 32px;
            margin: 0;
            font-weight: 700;
            letter-spacing: 1px;
            color: #ffffff;
        }

        /* Table Styling */
        .table thead th {
            background-color: #4F3F02;
            color: #ffffff;
            cursor: pointer;
        }

        .table tbody tr:hover {
            background-color: rgba(209, 194, 135, 0.2);
        }

        /* Button Styling */
        .btn-custom {
            background-color: #4F3F02;
            color: #ffffff;
            border-color: #4F3F02;
        }

        .btn-custom:hover {
            background-color: #D1C287;
            color: #4F3F02;
            border-color: #D1C287;
        }

        .btn-group .btn {
            margin-right: 5px;
        }

        .btn-group .btn:last-child {
            margin-right: 0;
        }

        /* Adjust margins for alerts */
        .alert {
            margin-top: 20px;
        }

        /* Responsive Adjustments */
        @media (max-width: 768px) {
            .dashboard-header h1 {
                font-size: 28px;
            }

            .btn-group {
                flex-wrap: wrap;
            }

            .btn-group .btn {
                margin-bottom: 5px;
            }
        }
    </style>
</head>
<body>

    <!-- Main Container -->
    <div class="container-fluid main-container">
        <!-- Dashboard Header -->
        <div class="dashboard-header">
            <h1>Accounts Receivable Aging Report</h1>
        </div>

        <!-- Success and Error Messages -->
        <?php if (isset($_SESSION['success_message'])): ?>
            <div class="alert alert-success alert-dismissible fade show" role="alert">
                <?php echo sanitize_output($_SESSION['success_message']); ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>
            <?php unset($_SESSION['success_message']); ?>
        <?php endif; ?>

        <?php if (isset($_SESSION['error_message'])): ?>
            <div class="alert alert-danger alert-dismissible fade show" role="alert">
                <?php echo sanitize_output($_SESSION['error_message']); ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>
            <?php unset($_SESSION['error_message']); ?>
        <?php endif; ?>

        <!-- Action Buttons -->
        <div class="d-flex justify-content-between mb-4 flex-wrap">
            <div class="mb-2">
                <a href="../dashboard.php" class="btn btn-secondary">Back to Dashboard</a>
                <!-- Add any other action buttons here if needed -->
            </div>
            <div class="mb-2">
                <!-- Export to CSV or other actions -->
                <!-- <a href="export_csv.php" class="btn btn-custom">Export to CSV</a> -->
            </div>
        </div>

        <!-- Filters -->
        <form method="GET" action="" class="row gx-3 gy-2 align-items-center mb-4 filter-form">
            <div class="col-sm-3">
                <label for="from_date" class="form-label">From Due Date:</label>
                <input type="date" name="from_date" id="from_date" class="form-control" value="<?php echo htmlspecialchars($from_date); ?>">
            </div>
            <div class="col-sm-3">
                <label for="to_date" class="form-label">To Due Date:</label>
                <input type="date" name="to_date" id="to_date" class="form-control" value="<?php echo htmlspecialchars($to_date); ?>">
            </div>
            <div class="col-auto">
                <button type="submit" class="btn btn-custom mt-4">Filter</button>
                <a href="index.php" class="btn btn-secondary mt-4">Reset</a>
            </div>
        </form>

        <!-- Aging Report Tables -->
        <?php foreach ($aging_report as $bucket => $invoices): ?>
            <h3 class="mt-5"><?php echo 'Days Overdue: ' . htmlspecialchars($bucket); ?></h3>
            <?php if (count($invoices) > 0): ?>
                <div class="table-responsive mb-5">
                    <table class="table table-striped table-hover align-middle aging-table">
                        <thead class="table-dark">
                            <tr>
                                <th>Invoice ID</th>
                                <th>Patient Name</th>
                                <th>Invoice Date</th>
                                <th>Due Date</th>
                                <th>Days Past Due</th>
                                <th>Invoice Total</th>
                                <th>Total Paid</th>
                                <th>Balance Due</th>
                                <th>Contact Info</th>
                                <th class="text-center">Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php 
                            $bucket_total = 0;
                            foreach ($invoices as $invoice): 
                                $balance_due = $invoice['balance_due'];
                                $bucket_total += $balance_due;
                            ?>
                                <tr>
                                    <td><?php echo sanitize_output($invoice['invoice_id']); ?></td>
                                    <td><?php echo sanitize_output($invoice['patient_name']); ?></td>
                                    <td><?php echo sanitize_output($invoice['invoice_date']); ?></td>
                                    <td><?php echo sanitize_output($invoice['due_date']); ?></td>
                                    <td><?php echo sanitize_output($invoice['days_past_due']); ?></td>
                                    <td>$<?php echo number_format($invoice['total_amount'], 2); ?></td>
                                    <td>$<?php echo number_format($invoice['total_paid'], 2); ?></td>
                                    <td>$<?php echo number_format($balance_due, 2); ?></td>
                                    <td>
                                        Email: <?php echo sanitize_output($invoice['email']); ?><br>
                                        Phone: <?php echo sanitize_output($invoice['phone']); ?>
                                    </td>
                                    <td class="text-center">
                                        <div class="btn-group" role="group">
                                            <a href="../view_invoice.php?id=<?php echo urlencode($invoice['invoice_id']); ?>" class="btn btn-info btn-sm">View</a>
                                            <a href="send_reminder.php?id=<?php echo urlencode($invoice['invoice_id']); ?>" class="btn btn-warning btn-sm">Send Reminder</a>
                                            <!-- Add more actions if needed -->
                                        </div>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                            <!-- Bucket Total -->
                            <tr>
                                <td colspan="7" class="text-end fw-bold">Total for <?php echo htmlspecialchars($bucket); ?> days:</td>
                                <td class="fw-bold">$<?php echo number_format($bucket_total, 2); ?></td>
                                <td colspan="2"></td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            <?php else: ?>
                <p class="text-muted">No invoices in this category.</p>
            <?php endif; ?>
        <?php endforeach; ?>
    </div>

    <!-- Include jQuery -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js" crossorigin="anonymous"></script>
    <!-- Bootstrap JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <!-- DataTables JS -->
    <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.4/js/dataTables.bootstrap5.min.js"></script>
    <!-- Optional JavaScript -->
    <script>
        $(document).ready(function() {
            // Initialize DataTables for each aging table
            $('.aging-table').DataTable({
                "paging": true,
                "lengthMenu": [10, 25, 50, 100],
                "ordering": true,
                "info": true,
                "searching": true,
                "order": [[4, "desc"]], // Order by days past due descending by default
                "columnDefs": [
                    { "orderable": false, "targets": [8, 9] } // Disable ordering on contact info and actions columns
                ]
            });

            // Auto-dismiss alerts after 5 seconds
            setTimeout(function() {
                var alertNode = document.querySelector('.alert');
                if (alertNode) {
                    var alert = new bootstrap.Alert(alertNode);
                    alert.close();
                }
            }, 5000);
        });
    </script>
</body>
</html>) add in form tax and discount and type full codes