View Code - 20260721

« Back to History

beam_finish_forecast.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_finish_forecast');

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

require_once __DIR__ . '/partials/header.php';

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

/* ================= INPUT ================= */

$type = $_GET['type'] ?? 'primary';
$type = ($type === 'secondary') ? 'secondary' : 'primary';

$beam_column = ($type === 'primary')
  ? 'primary_running_beam'
  : 'secondary_running_beam';

$sort = $_GET['sort'] ?? 'finish';

/* BACKDATE SUPPORT */
$asof = $_GET['asof'] ?? date('Y-m-d');

/* ================= FETCH MACHINES ================= */

$machinesStmt = $pdo->prepare("
  SELECT machine_no, {$beam_column} AS beam_no
  FROM machine_beam_status
  WHERE company_id = ?
  ORDER BY machine_no
");
$machinesStmt->execute([$company_id]);
$machines = $machinesStmt->fetchAll(PDO::FETCH_ASSOC);

/* ================= PREPARED ================= */

$totalStmt = $pdo->prepare("
  SELECT meter
  FROM company_beam_stock
  WHERE company_id=? AND beam_no=?
");

$lifeStmt = $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
");

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

$activeStmt = $pdo->prepare("
  SELECT entry_date,
         SUM(meter_total) as day_total
  FROM production_entry
  WHERE company_id=?
    AND machine_id=?
    AND entry_date <= ?
  GROUP BY entry_date
  HAVING day_total > 0
  ORDER BY entry_date DESC
  LIMIT 7
");

/* ================= FORECAST ENGINE ================= */

$forecast = [];

foreach ($machines as $m) {

$machine_no = $m['machine_no'];
  $beam_no    = $m['beam_no'];

if (!$beam_no) continue;

/* TOTAL METER */
  $totalStmt->execute([$company_id, $beam_no]);
  $total_meter = (float)$totalStmt->fetchColumn();

if ($total_meter <= 0) continue;

/* LIFE EVENTS */
  $lifeStmt->execute([$company_id, $beam_no]);
  $events = $lifeStmt->fetchAll(PDO::FETCH_ASSOC);

if (!$events) continue;

$segments = [];
  $cnt = count($events);

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

$from = $events[$i]['load_date'];

/* IMPORTANT CHANGE */
    $to = ($i+1<$cnt)
        ? $events[$i+1]['load_date']
        : $asof;

$segments[] = [
      'machine_no'=>$events[$i]['machine_no'],
      'from'=>$from,
      'to'=>$to
    ];
  }

/* GLOBAL PRODUCTION */
  $global_production = 0;

foreach ($segments as $seg) {

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

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

$pending = $total_meter - $global_production;

/* LAST 7 ACTIVE DAYS */
  $activeStmt->execute([
    $company_id,
    $machine_no,
    $asof
  ]);

$active_days = $activeStmt->fetchAll(PDO::FETCH_ASSOC);

$sum7 = 0;

foreach ($active_days as $d) {
    $sum7 += (float)$d['day_total'];
  }

$count7 = count($active_days);

$avg = ($count7 > 0) ? ($sum7 / $count7) : 0;

if ($avg > 0 && $pending > 0) {

$days_left = ceil($pending / $avg);

$finish_date = date('Y-m-d', strtotime($asof . " +$days_left days"));

} else {

$days_left = null;
    $finish_date = null;
  }

$forecast[] = [
    'machine'=>$machine_no,
    'beam'=>$beam_no,
    'total'=>$total_meter,
    'produced'=>$global_production,
    'pending'=>$pending,
    'avg'=>$avg,
    'days_left'=>$days_left,
    'finish'=>$finish_date
  ];
}

/* ================= SORTING ================= */

usort($forecast,function($a,$b) use($sort){

if ($sort==='pending') {
    return $a['pending'] <=> $b['pending'];
  }

if (!$a['finish']) return 1;
  if (!$b['finish']) return -1;

return strtotime($a['finish']) <=> strtotime($b['finish']);
});

<h3>Beam Finish Forecast (As of <?=h($asof)?>)</h3>

<input type="date"
       name="asof"
       value="<?=h($asof)?>"
       class="form-control"
       onchange="this.form.submit()">

<option value="primary" <?= $type==='primary'?'selected':'' ?>>
Primary
</option>

<option value="secondary" <?= $type==='secondary'?'selected':'' ?>>
Secondary
</option>

</select>

<option value="finish" <?= $sort==='finish'?'selected':'' ?>>
Sort by Finish Date
</option>

<option value="pending" <?= $sort==='pending'?'selected':'' ?>>
Sort by Pending Meter
</option>

</select>

</form>

</div>

<?php

$summary = [];

foreach ($forecast as $f) {

if ($f['finish']) {
    $summary[$f['finish']][] = $f['machine'];
  }
}

ksort($summary);

<strong>Bhidan Summary:</strong><br>

<?php if ($summary): ?>

<?php foreach ($summary as $date=>$machines): ?>

<?=h($date)?> → <?=implode(', ',$machines)?> bhidan<br>

<?php endforeach; ?>

<?php else: ?>

No upcoming beam finish data.

<?php endif; ?>

</div>

<tr>
<th>Machine</th>
<th>Beam</th>
<th>Total</th>
<th>Produced</th>
<th>Pending</th>
<th>Avg</th>
<th>Days Left</th>
<th>Finish Date</th>
</tr>

</thead>

<tbody>

<?php foreach ($forecast as $f): ?>

<tr>

</tr>

<?php endforeach; ?>

</tbody>

</table>

</div>
</div>

</div>

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