<?php
require_once "labClass.php";
$con = new labClass(); 

$records = $con->cashExR();

// Group records by date
$grouped = [];
foreach ($records as $r) {
    $grouped[$r['sdate']][] = $r;
}

// Overall totals
$overallCash = 0;
$overallExpense = 0;
?>

<div class="mt-4">
  <h5 class="text-primary fw-bold text-center mb-3">CASH & EXPENSES REPORT</h5>

  <table class="table table-bordered table-striped">
    <thead class="table-primary">
      <tr class="text-center">
        <th width="10%">Date</th>
        <th width="10%">Category</th>
        <th width="20%">Source / Expense</th>
        <th width="35%">Description</th>
        <th width="15%" class="text-end">Amount (TZS)</th> 
         
      </tr>
    </thead>
    <tbody>
      <?php foreach ($grouped as $date => $rows): ?>
        <?php
          // Sort Cash first, Expense second
          usort($rows, function($a, $b) {
              return strcmp($a['category'], $b['category']);
          });

          // Compute date totals
          $totalCash = 0;
          $totalExpense = 0;
          foreach ($rows as $r) {
              if ($r['category'] === 'Cash') $totalCash += $r['amount'];
              else $totalExpense += $r['amount'];
          }

          $dateBalance = $totalCash - $totalExpense;
          $overallCash += $totalCash;
          $overallExpense += $totalExpense;
        ?>

        <!-- Date Header Row -->
        <tr style="background-color:#e3f2fd; font-weight:bold;">
          <td colspan="6" class="text-primary">
            DATE: <?= htmlspecialchars($date) ?> 
          </td>
        </tr>

        <!-- Transactions -->
        <?php $c=0; 
        foreach ($rows as $r):
        $c++; ?>
        <tr>
          <td> </td>
          
          <td><?= htmlspecialchars($r['category']) ?></td>
          <td><?= htmlspecialchars($r['source']) ?></td>
          <td><?= htmlspecialchars($r['description']) ?></td>
          <td class="text-end"><?= number_format($r['amount'], 2) ?></td>
        
       
          
        </tr>
        <?php endforeach; ?>

        <!-- Date Totals -->
        <tr class="fw-bold text-end table-info">
          <td colspan="3" class="text-center">Subtotal (<?= htmlspecialchars($date) ?>)</td>
          <td colspan="2">
            <div><span class="text-success">Total Cash:</span> <?= number_format($totalCash, 2) ?></div>
            <div><span class="text-danger">Total Expenses:</span> <?= number_format($totalExpense, 2) ?></div>
            <div><span class="text-primary">Net Balance:</span> <?= number_format($dateBalance, 2) ?></div>
          </td>
         <td>
             
          </td>
        </tr>

      <?php endforeach; ?>

      <!-- Overall Total -->
      <tr class="fw-bold text-end table-primary">
        <td colspan="3" class="text-center">OVERALL TOTAL</td>
        <td colspan="2">
          <div><span class="text-success">Total Cash:</span> <?= number_format($overallCash, 2) ?></div>
          <div><span class="text-danger">Total Expenses:</span> <?= number_format($overallExpense, 2) ?></div>
          <div><span class="text-primary">Net Balance:</span> <?= number_format($overallCash - $overallExpense, 2) ?></div>
        </td>
         <td>
             
          </td>
      </tr>
    </tbody>
  </table>
</div>

 
 
 
