View Code - 20260722

beam_pending_meter_reportv2.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* beam_pending_meter_report.php
   Beam Pending Meter Report — newest two beams per machine (pissing + pasaria)
   Fix: force utf8mb4_unicode_ci collation on UNION branches to avoid illegal mix of collations.
*/

header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL);
ini_set('display_errors', 1);

require __DIR__ . '/modules/auth/auth.php';
require_login();
$u = auth_user();
$company_id = (int)$u['company_id'];

$pdo = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }
if (!$pdo instanceof PDO) { die('DB not ready'); }

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

/* inputs */
$estimate_to = isset($_GET['estimate_to']) ? trim($_GET['estimate_to']) : date('Y-m-d');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/',$estimate_to)) $estimate_to = date('Y-m-d');
$export = isset($_GET['export']) ? $_GET['export'] : '';

/* SQL: force same collation for all text returned by each UNION branch
   - CONVERT(... USING utf8mb4) COLLATE utf8mb4_unicode_ci
   - DATE(entry_date) (MariaDB compatible)
   - Uses ROW_NUMBER(); if your MariaDB < 10.2 you'll need a non-window rewrite.
*/
$sql = "
WITH events AS (
  /* pissing (primary) — LOOM rows only */
  SELECT
    company_id,
    CONVERT(COALESCE(TRIM(machine_no),'') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS machine_no,
    CONVERT(TRIM(beam_no) USING utf8mb4) COLLATE utf8mb4_unicode_ci           AS beam_no,
    DATE(entry_date) AS entry_date
  FROM pissing_entry p
  WHERE p.company_id = :company_id
    AND TRIM(COALESCE(p.machine,'')) = 'LOOM'
    AND TRIM(COALESCE(p.beam_no,'')) <> ''

UNION ALL

/* pasaria primary beam (fallback) */
  SELECT
    company_id,
    CONVERT(COALESCE(CAST(machine_no AS CHAR), '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS machine_no,
    CONVERT(TRIM(primary_beam_no) USING utf8mb4) COLLATE utf8mb4_unicode_ci                   AS beam_no,
    DATE(entry_date) AS entry_date
  FROM pasaria_entry pa
  WHERE pa.company_id = :company_id
    AND TRIM(COALESCE(primary_beam_no,'')) <> ''

UNION ALL

/* pasaria secondary beam (fallback) */
  SELECT
    company_id,
    CONVERT(COALESCE(CAST(machine_no AS CHAR), '') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS machine_no,
    CONVERT(TRIM(secondary_beam_no) USING utf8mb4) COLLATE utf8mb4_unicode_ci                 AS beam_no,
    DATE(entry_date) AS entry_date
  FROM pasaria_entry pa2
  WHERE pa2.company_id = :company_id
    AND TRIM(COALESCE(secondary_beam_no,'')) <> ''
),

beam_last AS (
  SELECT company_id, machine_no, beam_no, MAX(entry_date) AS last_event_date
  FROM events
  WHERE beam_no IS NOT NULL AND TRIM(beam_no) <> ''
  GROUP BY company_id, machine_no, beam_no
),

ranked AS (
  SELECT
    bl.company_id,
    bl.machine_no,
    bl.beam_no,
    bl.last_event_date,
    ROW_NUMBER() OVER (PARTITION BY bl.company_id, bl.machine_no ORDER BY bl.last_event_date DESC, bl.beam_no DESC) AS rn
  FROM beam_last bl
)

SELECT
  r.company_id,
  r.machine_no,
  r.beam_no,
  r.last_event_date,
  be.meter                     AS beam_meter,
  be.taka                      AS beam_taka,
  be.quality_id                AS beam_quality_id,
  bq.name                      AS beam_quality_name,
  COALESCE(ma.avg_per_day,0)   AS avg_per_day,

/* real production AFTER this event (sum meter_total) */
  ( SELECT COALESCE(SUM(pe.meter_total),0)
    FROM production_entry pe
    WHERE pe.company_id = r.company_id
      AND pe.machine_id = (r.machine_no + 0)
      AND pe.entry_date > r.last_event_date
  ) AS real_after_pissing,

GREATEST(0, DATEDIFF(:estimate_to, r.last_event_date)) AS days_missing,

(GREATEST(0, DATEDIFF(:estimate_to, r.last_event_date)) * COALESCE(ma.avg_per_day,0)) AS estimated_since,

(
    ( SELECT COALESCE(SUM(pe2.meter_total),0)
      FROM production_entry pe2
      WHERE pe2.company_id = r.company_id
        AND pe2.machine_id = (r.machine_no + 0)
        AND pe2.entry_date > r.last_event_date
    )
    +
    (GREATEST(0, DATEDIFF(:estimate_to, r.last_event_date)) * COALESCE(ma.avg_per_day,0))
  ) AS total_since_pissing,

(be.meter - (
     ( SELECT COALESCE(SUM(pe3.meter_total),0)
       FROM production_entry pe3
       WHERE pe3.company_id = r.company_id
         AND pe3.machine_id = (r.machine_no + 0)
         AND pe3.entry_date > r.last_event_date
     )
     + (GREATEST(0, DATEDIFF(:estimate_to, r.last_event_date)) * COALESCE(ma.avg_per_day,0))
  )) AS pending,

CASE
    WHEN LOWER(TRIM(r.beam_no)) = 'cut' THEN 'cut'
    WHEN COALESCE(ma.avg_per_day,0) <= 0 THEN 'no_machine_avg'
    ELSE 'ok'
  END AS note

FROM ranked r
LEFT JOIN beam_entry be
  ON be.company_id = r.company_id
  AND CONVERT(TRIM(CAST(be.beam_no AS CHAR)) USING utf8mb4) COLLATE utf8mb4_unicode_ci = TRIM(r.beam_no)
LEFT JOIN beam_qualities bq
  ON bq.company_id = be.company_id
  AND bq.id = be.quality_id
LEFT JOIN machine_avg ma
  ON ma.company_id = r.company_id
  AND ma.machine_id = (r.machine_no + 0)
WHERE r.rn <= 2
ORDER BY (r.machine_no + 0) ASC, r.last_event_date DESC
";

/* execute */
$params = [':company_id' => $company_id, ':estimate_to' => $estimate_to];
try {
  $st = $pdo->prepare($sql);
  $st->execute($params);
  $rows = $st->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
  echo "<pre>SQL Error: " . h($e->getMessage()) . "\n\nQuery:\n" . h($sql) . "</pre>";
  exit;
}

/* CSV export */
if ($export === 'csv') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename=beam_pending_'.date('Ymd').'.csv');
  $out = fopen('php://output','w');
  fputcsv($out, ['company_id','machine_no','beam_no','last_event_date','beam_meter','beam_taka','quality','avg_per_day','real_after_pissing','days_missing','estimated_since','total_since_pissing','pending','note']);
  foreach($rows as $r){
    fputcsv($out, [
      $r['company_id'],
      $r['machine_no'],
      $r['beam_no'],
      $r['last_event_date'],
      $r['beam_meter'],
      $r['beam_taka'],
      $r['beam_quality_name'],
      $r['avg_per_day'],
      $r['real_after_pissing'],
      $r['days_missing'],
      $r['estimated_since'],
      $r['total_since_pissing'],
      $r['pending'],
      $r['note']
    ]);
  }
  fclose($out);
  exit;
}

/* render simple page */
$style = <<<CSS
body{font-family:system-ui,Arial;margin:16px;background:#fafafa;color:#222;}
.card{background:#fff;padding:18px;border-radius:10px;box-shadow:0 6px 18px rgba(0,0,0,0.06);}
.controls{display:flex;gap:8px;align-items:center;margin-bottom:12px;}
.btn{background:#1e8b57;color:#fff;padding:8px 12px;border-radius:8px;border:0;cursor:pointer;}
.table{width:100%;border-collapse:collapse;margin-top:10px;}
.table th,.table td{border:1px solid #eee;padding:8px;font-size:13px;text-align:left;}
.table th{background:#f3fbf5;}
@media print{.controls{display:none;} body{margin:0;} .card{box-shadow:none}}
CSS;
ob_start();
if (is_file(__DIR__.'/partials/header.php')) include __DIR__.'/partials/header.php';
$headerHtml = ob_get_clean();
?>
<!doctype html>
<html>
<head>
  <meta charset="utf-8">
  <title>Beam Pending Meter Report</title>
  <style><?php echo $style; ?></style>
</head>
<body>
  <div class="card">
    <?php echo $headerHtml; ?>
    <h2>Beam Pending Meter Report — newest two beams per machine</h2>
    <p>Estimate up-to: <b><?php echo h($estimate_to); ?></b></p>

<div class="controls">
      <form method="get" style="display:flex;gap:8px;align-items:center;">
        <label>Estimate up-to:
          <input type="date" name="estimate_to" value="<?php echo h($estimate_to); ?>">
        </label>
        <button class="btn" type="submit">Show</button>
        <a class="btn" href="?<?php $q=$_GET; $q['export']='csv'; echo h(http_build_query($q)); ?>">Export CSV</a>
        <button class="btn" onclick="window.print();return false;">Print</button>
      </form>
    </div>

<table class="table" aria-label="Beam pending">
      <thead>
        <tr>
          <th>Machine</th><th>Beam No</th><th>Quality</th><th>Beam meter</th>
          <th>Last event</th><th>Avg/day</th><th>Real after</th><th>Days</th>
          <th>Estimated</th><th>Total since</th><th>Pending</th><th>Note</th>
        </tr>
      </thead>
      <tbody>
        <?php if (empty($rows)): ?>
          <tr><td colspan="12">No data</td></tr>
        <?php else: foreach($rows as $r): ?>
          <tr>
            <td><?php echo h($r['machine_no']); ?></td>
            <td><?php echo h($r['beam_no']); ?></td>
            <td><?php echo h($r['beam_quality_name'] ?: '-'); ?></td>
            <td><?php echo $r['beam_meter']!==null ? number_format((float)$r['beam_meter'],3,'.','') : '-'; ?></td>
            <td><?php echo h($r['last_event_date']); ?></td>
            <td><?php echo number_format((float)$r['avg_per_day'],3,'.',''); ?></td>
            <td><?php echo number_format((float)$r['real_after_pissing'],3,'.',''); ?></td>
            <td><?php echo (int)$r['days_missing']; ?></td>
            <td><?php echo number_format((float)$r['estimated_since'],3,'.',''); ?></td>
            <td><?php echo number_format((float)$r['total_since_pissing'],3,'.',''); ?></td>
            <td><?php echo $r['beam_meter']===null?'-':number_format((float)$r['pending'],3,'.',''); ?></td>
            <td><?php echo h($r['note']); ?></td>
          </tr>
        <?php endforeach; endif; ?>
      </tbody>
    </table>
  </div>
</body>
</html>