soil-report-ai/dashboard/inbox.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../lib/auth.php';

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

requireLogin();

$pageTitle = 'Report History';
$siteName  = 'Crop Monitor';

$pdo    = getDBConnection();
$userId = getCurrentUserId();

function countRecords(\PDO $pdo, string $table, int $userId, int $status = 0): int {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM `$table` WHERE modx_user_id = ? AND status = ?");
    $stmt->execute([$userId, $status]);
    return (int) $stmt->fetchColumn();
}

$counts = [
    'soil'    => countRecords($pdo, 'soil_records',   $userId, 0),
    'plant'   => countRecords($pdo, 'plant_records',  $userId, 0),
    'water'   => countRecords($pdo, 'water_records',  $userId, 0),
    'animal'  => countRecords($pdo, 'animal_records', $userId, 0),
    'archived'=> countRecords($pdo, 'soil_records',   $userId, 2),
    'deleted' => countRecords($pdo, 'soil_records',   $userId, 3),
];

function fetchHistory(\PDO $pdo, string $table, int $userId, string $select = 'id, rand, lab_no, sample_id, site_id, crop_type, date_sampled'): array {
    $stmt = $pdo->prepare(
        "SELECT {$select}
         FROM `{$table}`
         WHERE modx_user_id = ? AND status = 0
         ORDER BY id DESC LIMIT 100"
    );
    $stmt->execute([$userId]);
    return $stmt->fetchAll();
}

$soilRows   = fetchHistory($pdo, 'soil_records',   $userId);
$plantRows  = fetchHistory($pdo, 'plant_records',  $userId);
$waterRows  = fetchHistory($pdo, 'water_records',  $userId, 'id, rand, lab_no, sample_id, site_id, date_sampled');
$animalRows = fetchHistory($pdo, 'animal_records', $userId, 'id, rand, lab_no, sample_id, NULL AS site_id, date_sampled');

$h = fn($v) => htmlspecialchars((string) $v, ENT_QUOTES, 'UTF-8');

include __DIR__ . '/../layouts/header.php';
include __DIR__ . '/../layouts/navbar.php';
?>

<div id="layoutSidenav">
    <div id="layoutSidenav_nav">
        <?php include __DIR__ . '/../layouts/sidebar.php'; ?>
    </div>
    <div id="layoutSidenav_content">
        <main>
            <div class="container-fluid px-4">
                <h1 class="mt-4"><?= $h($pageTitle) ?></h1>
                <ol class="breadcrumb mb-4">
                    <li class="breadcrumb-item"><a href="/dashboard/dashboard.php">Dashboard</a></li>
                    <li class="breadcrumb-item active">Report History</li>
                </ol>

                <div class="row inbox-mail">
                    <!-- Left sidebar -->
                    <div class="col-12 col-md-3">
                        <div class="input-group mb-3">
                            <input type="text" class="form-control" placeholder="Search...">
                            <span class="input-group-text"><i class="fa fa-search"></i></span>
                        </div>

                        <div class="alert alert-danger py-0"><h4>Reports</h4></div>

                        <div class="list-group" id="v-pills-tab" role="tablist">
                            <a id="soil-reports-tab" data-bs-toggle="list" href="#soil-reports"
                               role="tab" aria-controls="soil-reports"
                               class="text-secondary list-group-item list-group-item-action list-group-item-light active">
                                <i class="fas fa-icicles fa-rotate-180"></i> Soil Analysis
                                <span class="badge bg-secondary float-end"><?= $counts['soil'] ?></span>
                            </a>
                            <a id="plant-reports-tab" data-bs-toggle="list" href="#plant-reports"
                               role="tab" aria-controls="plant-reports"
                               class="text-secondary list-group-item list-group-item-action list-group-item-light">
                                <i class="fab fa-pagelines"></i> Plant Analysis
                                <span class="badge bg-success float-end"><?= $counts['plant'] ?></span>
                            </a>
                            <a id="water-reports-tab" data-bs-toggle="list" href="#water-reports"
                               role="tab" aria-controls="water-reports"
                               class="text-secondary list-group-item list-group-item-action list-group-item-light">
                                <i class="fa fa-tint"></i> Water Analysis
                                <span class="badge bg-primary float-end"><?= $counts['water'] ?></span>
                            </a>
                            <a id="dietary-reports-tab" data-bs-toggle="list" href="#dietary-reports"
                               role="tab" aria-controls="dietary-reports"
                               class="text-secondary list-group-item list-group-item-action list-group-item-light">
                                <i class="fas fa-dog"></i> Animal Dietary Balance
                                <span class="badge bg-warning text-dark float-end"><?= $counts['animal'] ?></span>
                            </a>
                            <a id="compost-reports-tab" data-bs-toggle="list" href="#compost-reports"
                               role="tab" aria-controls="compost-reports"
                               class="text-secondary list-group-item list-group-item-action list-group-item-light">
                                <i class="fas fa-cloud"></i> Compost Test Data
                                <span class="badge bg-secondary float-end">0</span>
                            </a>
                        </div>

                        <br>

                        <div class="list-group">
                            <span class="list-group-item fw-bold">Folders</span>
                            <a href="#" class="list-group-item list-group-item-action list-group-item-light text-warning">
                                <i class="fa fa-folder text-warning"></i> Archived
                                <span class="badge bg-warning text-dark float-end"><?= $counts['archived'] ?></span>
                            </a>
                            <a href="#" class="list-group-item list-group-item-action list-group-item-light text-danger">
                                <i class="fa fa-folder text-danger"></i> Deleted
                                <span class="badge bg-danger float-end"><?= $counts['deleted'] ?></span>
                            </a>
                        </div>
                    </div>

                    <!-- Tab content -->
                    <div class="col-12 col-md-9 tab-content">

                        <!-- Soil -->
                        <div class="tab-pane fade show active" id="soil-reports" role="tabpanel">
                            <h5 class="mt-2">Soil Analysis Records</h5>
                            <table class="table table-hover table-sm">
                                <thead class="table-dark">
                                    <tr>
                                        <th>Lab No</th><th>Sample ID</th><th>Site ID</th>
                                        <th>Crop</th><th>Date Sampled</th><th class="text-end">Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                <?php if (empty($soilRows)): ?>
                                    <tr><td colspan="6" class="text-center text-muted">No records found.</td></tr>
                                <?php else: foreach ($soilRows as $r): ?>
                                    <tr>
                                        <td><?= $h($r['lab_no']) ?></td>
                                        <td><?= $h($r['sample_id']) ?></td>
                                        <td><?= $h($r['site_id']) ?></td>
                                        <td><?= $h($r['crop_type']) ?></td>
                                        <td><?= $h($r['date_sampled']) ?></td>
                                        <td class="text-end">
                                            <a href="/dashboard/crop-analysis/soil-test-data/soil-analysis.php?rid=<?= (int)$r['id'] ?>&rand=<?= (float)$r['rand'] ?>"
                                               class="btn btn-sm btn-outline-success">View</a>
                                        </td>
                                    </tr>
                                <?php endforeach; endif; ?>
                                </tbody>
                            </table>
                        </div>

                        <!-- Plant -->
                        <div class="tab-pane fade" id="plant-reports" role="tabpanel">
                            <h5 class="mt-2">Plant Analysis Records</h5>
                            <table class="table table-hover table-sm">
                                <thead class="table-dark">
                                    <tr>
                                        <th>Lab No</th><th>Sample ID</th><th>Site ID</th>
                                        <th>Crop</th><th>Date Sampled</th><th class="text-end">Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                <?php if (empty($plantRows)): ?>
                                    <tr><td colspan="6" class="text-center text-muted">No records found.</td></tr>
                                <?php else: foreach ($plantRows as $r): ?>
                                    <tr>
                                        <td><?= $h($r['lab_no']) ?></td>
                                        <td><?= $h($r['sample_id']) ?></td>
                                        <td><?= $h($r['site_id']) ?></td>
                                        <td><?= $h($r['crop_type']) ?></td>
                                        <td><?= $h($r['date_sampled']) ?></td>
                                        <td class="text-end">
                                            <a href="/dashboard/crop-analysis/plant-test-data/plant-analysis.php?rid=<?= (int)$r['id'] ?>&rand=<?= (float)$r['rand'] ?>"
                                               class="btn btn-sm btn-outline-success">View</a>
                                        </td>
                                    </tr>
                                <?php endforeach; endif; ?>
                                </tbody>
                            </table>
                        </div>

                        <!-- Water -->
                        <div class="tab-pane fade" id="water-reports" role="tabpanel">
                            <h5 class="mt-2">Water Analysis Records</h5>
                            <table class="table table-hover table-sm">
                                <thead class="table-dark">
                                    <tr>
                                        <th>Lab No</th><th>Sample ID</th><th>Site ID</th>
                                        <th>Date Sampled</th><th class="text-end">Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                <?php if (empty($waterRows)): ?>
                                    <tr><td colspan="5" class="text-center text-muted">No records found.</td></tr>
                                <?php else: foreach ($waterRows as $r): ?>
                                    <tr>
                                        <td><?= $h($r['lab_no']) ?></td>
                                        <td><?= $h($r['sample_id']) ?></td>
                                        <td><?= $h($r['site_id']) ?></td>
                                        <td><?= $h($r['date_sampled']) ?></td>
                                        <td class="text-end">
                                            <a href="/dashboard/crop-analysis/water-test-data/water-analysis-pdf.php?rid=<?= (int)$r['id'] ?>&rand=<?= (float)$r['rand'] ?>"
                                               class="btn btn-sm btn-outline-success">View</a>
                                        </td>
                                    </tr>
                                <?php endforeach; endif; ?>
                                </tbody>
                            </table>
                        </div>

                        <!-- Animal Dietary -->
                        <div class="tab-pane fade" id="dietary-reports" role="tabpanel">
                            <h5 class="mt-2">Animal Dietary Balance Records</h5>
                            <table class="table table-hover table-sm">
                                <thead class="table-dark">
                                    <tr>
                                        <th>Lab No</th><th>Sample ID</th><th>Site ID</th>
                                        <th>Date Sampled</th><th class="text-end">Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                <?php if (empty($animalRows)): ?>
                                    <tr><td colspan="5" class="text-center text-muted">No records found.</td></tr>
                                <?php else: foreach ($animalRows as $r): ?>
                                    <tr>
                                        <td><?= $h($r['lab_no']) ?></td>
                                        <td><?= $h($r['sample_id']) ?></td>
                                        <td><?= $h($r['site_id']) ?></td>
                                        <td><?= $h($r['date_sampled']) ?></td>
                                        <td class="text-end">
                                            <a href="/dashboard/crop-analysis/animal-dietary-balance/animal-dietary-balance.php?rid=<?= (int)$r['id'] ?>&rand=<?= (float)$r['rand'] ?>"
                                               class="btn btn-sm btn-outline-success">View</a>
                                        </td>
                                    </tr>
                                <?php endforeach; endif; ?>
                                </tbody>
                            </table>
                        </div>

                        <!-- Compost -->
                        <div class="tab-pane fade" id="compost-reports" role="tabpanel">
                            <h5 class="mt-2">Compost Test Records</h5>
                            <p class="text-muted">Compost records pending migration.</p>
                        </div>

                    </div>
                </div>
            </div>
        </main>

        <?php include __DIR__ . '/../layouts/footer.php'; ?>
    </div>
</div>