View Code - 20260721

beam_shortage_report.php | 20260721_154032.php Initial Bulk Import

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

require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('beam_book_finish');

$company_id = (int)$ctx['company_id'];
$pdo        = $ctx['pdo'];

function h($s){
  return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8');
}

/* ================= DATE FILTER ================= */
$from_date = $_GET['from_date'] ?? '';
$to_date   = $_GET['to_date'] ?? '';
$use_date_filter = ($from_date && $to_date);

/* ================= COMPANY NAME ================= */
$stmt = $pdo->prepare("SELECT name FROM companies WHERE id=?");
$stmt->execute([$company_id]);
$company_name = $stmt->fetchColumn() ?: 'Company';

/* =========================================================
   GET FINISHED BEAMS
   ========================================================= */

$sql = "
SELECT
  blc.beam_no,
  jt.machine_no,
  jt.slot,
  jt.finish_date
FROM beam_life_cycle blc
JOIN JSON_TABLE(
  blc.life_events,
  '$[*]' COLUMNS(
    action      VARCHAR(20) PATH '$.action',
    machine_no  VARCHAR(20) PATH '$.machine_no',
    slot        VARCHAR(20) PATH '$.slot',
    finish_date DATE        PATH '$.finish_date',
    load_date   DATE        PATH '$.load_date'
  )
) jt
WHERE blc.company_id=?
  AND jt.action='finish'
";

$params = [$company_id];

if ($use_date_filter) {
  $sql .= " AND jt.finish_date BETWEEN ? AND ?";
  $params[] = $from_date;
  $params[] = $to_date;
}

$sql .= " ORDER BY jt.finish_date DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$finishedBeams = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* =========================================================
   PREPARE PRODUCTION CALCULATION (Beam Register Logic)
   ========================================================= */

$prodStmt = $pdo->prepare("
  SELECT COALESCE(SUM(meter_total),0)
  FROM production_entry
  WHERE company_id=?
    AND machine_id=?
    AND entry_date BETWEEN ? AND ?
");

$rows = [];

foreach ($finishedBeams as $f) {

// total meter from stock
  $stockStmt = $pdo->prepare("
    SELECT meter FROM company_beam_stock
    WHERE company_id=? AND beam_no=?
    LIMIT 1
  ");
  $stockStmt->execute([$company_id, $f['beam_no']]);
  $total = (float)$stockStmt->fetchColumn();
  if ($total <= 0) continue;

// get full lifecycle events
  $lc = $pdo->prepare("
    SELECT jt.machine_no, jt.load_date
    FROM beam_life_cycle blc
    JOIN JSON_TABLE(
      blc.life_events,
      '$[*]' COLUMNS(
        machine_no VARCHAR(20) PATH '$.machine_no',
        load_date  DATE        PATH '$.load_date'
      )
    ) jt
    WHERE blc.company_id=? AND blc.beam_no=?
    ORDER BY jt.load_date
  ");
  $lc->execute([$company_id, $f['beam_no']]);
  $events = $lc->fetchAll(PDO::FETCH_ASSOC);

if (!$events) continue;

$sum_all = 0;
  $cnt = count($events);

for ($i=0; $i<$cnt; $i++) {

$from = $events[$i]['load_date'];
    $to   = ($i+1 < $cnt)
      ? $events[$i+1]['load_date']
      : $f['finish_date'];

$prodStmt->execute([
      $company_id,
      $events[$i]['machine_no'],
      $from,
      $to
    ]);

$sum_all += (float)$prodStmt->fetchColumn();
  }

$produced = $sum_all;
  $shortage = $total - $produced;
  $percent  = $total > 0 ? round(($shortage / $total) * 100, 2) : 0;

$rows[] = [
    'machine_no' => $f['machine_no'],
    'slot'       => ucfirst($f['slot']) . ' Beam',
    'beam_no'    => $f['beam_no'],
    'finish_date'=> $f['finish_date'],
    'total'      => $total,
    'produced'   => $produced,
    'shortage'   => $shortage,
    'percent'    => $percent,
  ];
}

/* =========================================================
   PAGE START
   ========================================================= */

$page_title = $company_name . ' : Beam Shortage Report';
require_once __DIR__ . '/partials/header.php';
?>

<h3 class="mb-4"><?=h($company_name)?> : Beam Shortage Report</h3>

<form method="get" class="row g-3 mb-4">
  <div class="col-md-3">
    <label class="form-label">From</label>
    <input type="date" name="from_date" value="<?=h($from_date)?>" class="form-control">
  </div>
  <div class="col-md-3">
    <label class="form-label">To</label>
    <input type="date" name="to_date" value="<?=h($to_date)?>" class="form-control">
  </div>
  <div class="col-md-6 d-flex align-items-end gap-2">
    <button class="btn btn-primary">Filter</button>
    <a href="beam_shortage_report.php" class="btn btn-secondary">Reset</a>
    <button onclick="window.print()" type="button" class="btn btn-dark">Print</button>
  </div>
</form>

<table class="table table-bordered table-hover mb-0">
<thead class="table-light">
<tr>
  <th>Machine</th>
  <th>Beam</th>
  <th>Beam No</th>
  <th>Finish Date</th>
  <th class="text-end">Total</th>
  <th class="text-end">Produced</th>
  <th class="text-end">Shortage</th>
  <th class="text-end">%</th>
</tr>
</thead>
<tbody>

<?php if (!$rows): ?>
<tr><td colspan="8" class="text-center py-4">No records found</td></tr>
<?php endif; ?>

<?php foreach ($rows as $r):

$rowClass = '';

if ($r['shortage'] < 0) {
  $rowClass = 'table-primary';        // Blue
} elseif ($r['percent'] <= 5) {
  $rowClass = 'table-success';        // Green
} elseif ($r['percent'] > 5 && $r['percent'] <= 9) {
  $rowClass = 'table-warning';        // Yellow
} elseif ($r['percent'] > 9) {
  $rowClass = 'table-danger';         // Red
}
?>

<?php endforeach; ?>

</tbody>
</table>

</div>
</div>

</div>

<?php require_once __DIR__ . '/partials/footer.php'; ?>