View Code - 20260722

mesr_beam_pending_report.php | 20260722_120325.php Initial Domain Snapshot

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

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

$pdo        = $ctx['pdo'];
$company_id = (int)$ctx['company_id'];
$as_of_date = $_GET['as_of_date'] ?? date('Y-m-d');

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

/* ============================================================
   1️⃣ SNAPSHOT BEAM LIST
============================================================ */
$snapStmt = $pdo->prepare("
    SELECT DISTINCT beam_no
    FROM mesr_month_end_snapshot
    WHERE company_id = ?
      AND beam_no > 0
");
$snapStmt->execute([$company_id]);
$beams = $snapStmt->fetchAll(PDO::FETCH_COLUMN);

$beamMeter      = [];
$beamQuality    = [];
$beamQualityId  = [];
$beamData       = [];

/* ============================================================
   2️⃣ TOTAL METER + QUALITY
   PRIMARY: company_beam_stock
   FALLBACK: beam_entry (latest entry)
============================================================ */
if ($beams) {

$ph = implode(',', array_fill(0, count($beams), '?'));

/* ---------- PRIMARY: company_beam_stock ---------- */
    $stockStmt = $pdo->prepare("
        SELECT cs.beam_no,
               cs.meter,
               cs.quality_id,
               bq.name AS quality_name
        FROM company_beam_stock cs
        LEFT JOIN beam_qualities bq
            ON bq.id = cs.quality_id
           AND bq.company_id = cs.company_id
        WHERE cs.company_id = ?
          AND cs.beam_no IN ($ph)
    ");

$stockStmt->execute(array_merge([$company_id], $beams));

foreach ($stockStmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $beamMeter[$r['beam_no']]     = (float)$r['meter'];
        $beamQuality[$r['beam_no']]   = $r['quality_name'];
        $beamQualityId[$r['beam_no']] = (int)$r['quality_id'];
    }

/* ---------- FALLBACK: beam_entry (latest entry_date) ---------- */
    $missing = array_diff($beams, array_keys($beamMeter));

if ($missing) {

$ph2 = implode(',', array_fill(0, count($missing), '?'));

$entryStmt = $pdo->prepare("
            SELECT be1.beam_no,
                   be1.meter,
                   be1.quality_id,
                   bq.name AS quality_name
            FROM beam_entry be1
            INNER JOIN (
                SELECT beam_no, MAX(entry_date) AS max_date
                FROM beam_entry
                WHERE company_id = ?
                  AND beam_no IN ($ph2)
                GROUP BY beam_no
            ) latest
              ON latest.beam_no = be1.beam_no
             AND latest.max_date = be1.entry_date
            LEFT JOIN beam_qualities bq
              ON bq.id = be1.quality_id
             AND bq.company_id = be1.company_id
            WHERE be1.company_id = ?
        ");

$params = array_merge([$company_id], $missing, [$company_id]);
        $entryStmt->execute($params);

foreach ($entryStmt->fetchAll(PDO::FETCH_ASSOC) as $r) {

if (!isset($beamMeter[$r['beam_no']])) {
                $beamMeter[$r['beam_no']] = (float)$r['meter'];
            }

if (!isset($beamQuality[$r['beam_no']])) {
                $beamQuality[$r['beam_no']] = $r['quality_name'];
                $beamQualityId[$r['beam_no']] = (int)$r['quality_id'];
            }
        }
    }
}

/* ============================================================
   3️⃣ LIFECYCLE + TRUE PRODUCTION
============================================================ */
if ($beams) {

$ph = implode(',', array_fill(0, count($beams), '?'));

$lifeStmt = $pdo->prepare("
        SELECT beam_no, life_events
        FROM beam_life_cycle
        WHERE company_id = ?
          AND beam_no IN ($ph)
    ");
    $lifeStmt->execute(array_merge([$company_id], $beams));
    $lifeRows = $lifeStmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($lifeRows as $row) {

$beam   = $row['beam_no'];
        $events = json_decode($row['life_events'], true);
        if (!$events) continue;

usort($events, function($a, $b){
            return strtotime($a['load_date'] ?? '1970-01-01')
                 <=> strtotime($b['load_date'] ?? '1970-01-01');
        });

for ($i=0; $i<count($events); $i++) {

if (empty($events[$i]['load_date'])) continue;

$machine = $events[$i]['machine_no'];
            $start   = $events[$i]['load_date'];

if (isset($events[$i+1]['load_date'])) {
                $end = date('Y-m-d', strtotime($events[$i+1]['load_date'].' -1 day'));
            } else {
                $end = $as_of_date;
            }

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

$prodStmt->execute([$company_id,$machine,$start,$end]);
            $prod = (float)$prodStmt->fetchColumn();

if (!isset($beamData[$beam])) {
                $beamData[$beam] = [
                    'total_meter' => $beamMeter[$beam] ?? 0,
                    'production'  => 0,
                    'periods'     => []
                ];
            }

$beamData[$beam]['production'] += $prod;

$beamData[$beam]['periods'][] = [
                'machine' => $machine,
                'start'   => $start,
                'end'     => $end,
                'prod'    => $prod
            ];
        }
    }
}

/* ============================================================
   4️⃣ QUALITY SUMMARY
============================================================ */
$qualitySummary = [];

foreach ($beamData as $beam => $d) {

$quality_id = $beamQualityId[$beam] ?? 0;
    $quality    = $beamQuality[$beam] ?? 'Unknown';

if ($quality_id == 0) continue;

$total   = $d['total_meter'];
    $prod    = $d['production'];
    $pending = max(0, $total - $prod);

if (!isset($qualitySummary[$quality_id])) {
        $qualitySummary[$quality_id] = [
            'quality_name' => $quality,
            'total'        => 0,
            'production'   => 0,
            'pending'      => 0
        ];
    }

$qualitySummary[$quality_id]['total']      += $total;
    $qualitySummary[$quality_id]['production'] += $prod;
    $qualitySummary[$quality_id]['pending']    += $pending;
}

/* ============================================================
   5️⃣ SAVE SUMMARY
============================================================ */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_summary'])) {

$summary_month = date('Y-m-01', strtotime($as_of_date));

$insertStmt = $pdo->prepare("
        INSERT INTO mesr_beam_pending_meter_summary_quality_wise
        (company_id, summary_month, quality_id, quality_name,
         total_meter, total_production, total_pending)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            total_meter = VALUES(total_meter),
            total_production = VALUES(total_production),
            total_pending = VALUES(total_pending),
            updated_at = NOW()
    ");

foreach ($qualitySummary as $quality_id => $data) {

$insertStmt->execute([
            $company_id,
            $summary_month,
            $quality_id,
            $data['quality_name'],
            $data['total'],
            $data['production'],
            $data['pending']
        ]);
    }

header("Location: ?as_of_date=".$as_of_date);
    exit;
}

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

<h2>Beam Production Report (Snapshot Based)</h2>

<h3>Beam Wise Detail</h3>
<table class="table table--bordered">
<thead>
<tr>
    <th>Beam</th>
    <th>Total Meter</th>
    <th>Production</th>
    <th>Pending</th>
    <th>Details (Machine Periods)</th>
</tr>
</thead>
<tbody>
<?php foreach ($beamData as $beam => $d):
    $total   = $d['total_meter'];
    $prod    = $d['production'];
    $pending = max(0,$total-$prod);
?>
<tr>
    <td><?=h($beam)?></td>
    <td><?=number_format($total,2)?></td>
    <td><?=number_format($prod,2)?></td>
    <td><b><?=number_format($pending,2)?></b></td>
    <td>
        <?php foreach ($d['periods'] as $p): ?>
            <div>
                M<?=h($p['machine'])?> :
                <?=h($p['start'])?> →
                <?=h($p['end'])?>
                (<?=number_format($p['prod'],2)?> m)
            </div>
        <?php endforeach; ?>
    </td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

<h3>Quality Wise Summary</h3>

<form method="post">
<table class="table table--bordered">
<thead>
<tr>
    <th>Quality</th>
    <th>Total Meter</th>
    <th>Total Production</th>
    <th>Total Pending</th>
</tr>
</thead>
<tbody>
<?php foreach ($qualitySummary as $qid => $s): ?>
<tr>
    <td><?=h($s['quality_name'])?></td>
    <td><?=number_format($s['total'],2)?></td>
    <td><?=number_format($s['production'],2)?></td>
    <td><b><?=number_format($s['pending'],2)?></b></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

<button type="submit" name="save_summary" class="btn btn-success">
    Save Summary
</button>
</form>

</div>

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