View Code - 20260722

beam_stock1.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ============================================================================ 
   File: /erp/beam_stock.php
   Purpose: View Beam Stock + Refresh + CSV + Print + PDF
   Filters: Quality, Warper | Group By: Quality (with aggregate sums)
   Notes: Uses columns `tar`, `taka`, `meter` as present in beam_entry/beam_stock.
   Scope : Page-local only; no global/base edits.
   ============================================================================ */
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'; }
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

/* Legacy header $company['name'] */
if (!isset($company) || !is_array($company)) {
  try {
    $stmt = $pdo->prepare("SELECT COALESCE(NULLIF(name,''), NULLIF(company_name,''), CONCAT('Company #', id))
                             FROM companies WHERE id=? LIMIT 1");
    $stmt->execute([$company_id]);
    $company = ['name' => (string)($stmt->fetchColumn() ?: 'Company #'.$company_id)];
  } catch (Throwable $e) {
    $company = ['name' => 'Company #'.$company_id];
  }
}

/* ---- filters ------------------------------------------------------------ */
$quality_id = isset($_GET['quality_id']) && $_GET['quality_id'] !== '' ? (int)$_GET['quality_id'] : null;
$warper_id  = isset($_GET['warper_id'])  && $_GET['warper_id']  !== '' ? (int)$_GET['warper_id']  : null;
$group_by   = (($_GET['group'] ?? '') === 'quality') ? 'quality' : '';

/* Helper: NOT EXISTS clause to exclude beams that are pissing/pasaria/sent */
$exclude_action_clause = " NOT EXISTS (
    SELECT 1 FROM beam_entry be
     WHERE be.company_id = s.company_id
       AND be.beam_no = s.beam_no
       AND TRIM(LOWER(COALESCE(be.installation_source, ''))) IN ('pissing','pasaria','sent')
)";

/* ---- CSV export --------------------------------------------------------- */
if (($_GET['act'] ?? '') === 'csv') {
  $params = [$company_id];
  $where  = " s.company_id=? ";
  if ($quality_id !== null) { $where .= " AND s.quality_id=? "; $params[] = $quality_id; }
  if ($warper_id  !== null) { $where .= " AND s.warper_id=? ";  $params[] = $warper_id; }

// append exclude clause
  $where .= " AND " . $exclude_action_clause;

header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename=beam_stock_'.$company_id.'.csv');
  $out = fopen('php://output','w');

if ($group_by === 'quality') {
    fputcsv($out, ['Quality','Beam Count','Total Meters','Total Taka','Total Tar']);
    $st = $pdo->prepare("SELECT COALESCE(s.quality_name,'(No Quality)') AS q, 
                               COUNT(*) AS cnt,
                               SUM(COALESCE(s.meter,0)) AS total_meters,
                               SUM(COALESCE(s.taka,0)) AS total_taka,
                               SUM(COALESCE(s.tar,0)) AS total_tar
                          FROM beam_stock s
                         WHERE $where
                      GROUP BY COALESCE(s.quality_name,'(No Quality)')
                      ORDER BY q");
    $st->execute($params);
    while($r = $st->fetch(PDO::FETCH_ASSOC)){
      fputcsv($out, [$r['q'], $r['cnt'], $r['total_meters'], $r['total_taka'], $r['total_tar']]);
    }
  } else {
    // include tar, taka, meter in CSV
    fputcsv($out, ['Beam No','Quality','Warper','First Entry Date','Tar','Taka','Meter']);
    $st = $pdo->prepare("SELECT s.beam_no, s.quality_name, s.warper_name, s.first_entry_date,
                                s.tar, s.taka, s.meter
                           FROM beam_stock s
                          WHERE $where
                       ORDER BY s.beam_no");
    $st->execute($params);
    while($r = $st->fetch(PDO::FETCH_ASSOC)){
      fputcsv($out, [
        $r['beam_no'],
        $r['quality_name'],
        $r['warper_name'],
        $r['first_entry_date'],
        $r['tar'],
        $r['taka'],
        $r['meter']
      ]);
    }
  }
  fclose($out); exit;
}

/* ---- PDF export (if dompdf installed) ---------------------------------- */
if (($_GET['act'] ?? '') === 'pdf') {
  // lightweight fetch (no group)
  $params = [$company_id];
  $where  = " s.company_id=? ";
  if ($quality_id !== null) { $where .= " AND s.quality_id=? "; $params[] = $quality_id; }
  if ($warper_id  !== null) { $where .= " AND s.warper_id=? ";  $params[] = $warper_id; }

// append exclude clause
  $where .= " AND " . $exclude_action_clause;

$st = $pdo->prepare("SELECT s.beam_no, s.quality_name, s.warper_name, s.first_entry_date,
                              s.tar, s.taka, s.meter
                         FROM beam_stock s
                        WHERE $where
                     ORDER BY s.beam_no");
  $st->execute($params);
  $rows = $st->fetchAll(PDO::FETCH_ASSOC);

$html = '<h2 style="margin:0 0 8px 0">'.h($company['name']).' — Beam Stock</h2>';
  $html .= '<table width="100%" border="1" cellspacing="0" cellpadding="6" style="border-collapse:collapse;font-size:12px">
            <thead><tr>
              <th align="left">Beam No</th><th align="left">Quality</th>
              <th align="left">Warper</th><th align="left">First Entry Date</th>
              <th align="left">Tar</th><th align="left">Taka</th><th align="left">Meter</th>
            </tr></thead><tbody>';
  if(!$rows){
    $html .= '<tr><td colspan="7">No data.</td></tr>';
  }else{
    foreach($rows as $r){
      $html .= '<tr>
        <td>'.h($r['beam_no']).'</td>
        <td>'.h($r['quality_name']).'</td>
        <td>'.h($r['warper_name']).'</td>
        <td>'.h($r['first_entry_date']).'</td>
        <td>'.h($r['tar']).'</td>
        <td>'.h($r['taka']).'</td>
        <td>'.h($r['meter']).'</td>
      </tr>';
    }
  }
  $html .= '</tbody></table>';

// Try dompdf
  try {
    if (is_file(__DIR__.'/vendor/autoload.php')) { require __DIR__.'/vendor/autoload.php'; }
    if (class_exists('\\Dompdf\\Dompdf')) {
      $dompdf = new \Dompdf\Dompdf(['isRemoteEnabled'=>false]);
      $dompdf->loadHtml($html, 'UTF-8');
      $dompdf->setPaper('A4', 'portrait');
      $dompdf->render();
      $dompdf->stream('beam_stock_'.$company_id.'.pdf');
      exit;
    }
  } catch(Throwable $e){ /* ignore fallback below */ }

// Fallback: show message
  header('Content-Type: text/html; charset=utf-8');
  echo "<p style='font-family:system-ui'>PDF export requires <code>dompdf/dompdf</code>.
        Install with: <code>composer require dompdf/dompdf</code></p>";
  echo $html;
  exit;
}

/* ---- dropdown data from beam_stock (exclude acted beams) --------------- */
$qualities = []; $warpers = [];
try{
  $st = $pdo->prepare("SELECT DISTINCT s.quality_id, COALESCE(s.quality_name,'(No Quality)') AS q
                         FROM beam_stock s
                        WHERE s.company_id=? AND " . $exclude_action_clause . "
                     ORDER BY q");
  $st->execute([$company_id]);
  while($r = $st->fetch(PDO::FETCH_ASSOC)){ $qualities[(int)$r['quality_id']] = $r['q']; }

$st = $pdo->prepare("SELECT DISTINCT s.warper_id, COALESCE(s.warper_name,'(No Warper)') AS w
                         FROM beam_stock s
                        WHERE s.company_id=? AND " . $exclude_action_clause . "
                     ORDER BY w");
  $st->execute([$company_id]);
  while($r = $st->fetch(PDO::FETCH_ASSOC)){ $warpers[(int)$r['warper_id']] = $r['w']; }
}catch(Throwable $e){}

/* ---- data for UI -------------------------------------------------------- */
$params = [$company_id];
$where  = " s.company_id=? ";
if ($quality_id !== null) { $where .= " AND s.quality_id=? "; $params[] = $quality_id; }
if ($warper_id  !== null) { $where .= " AND s.warper_id=? ";  $params[] = $warper_id; }

// append exclude clause so UI only shows available beams
$where .= " AND " . $exclude_action_clause;

$rows = []; $group_rows = [];
try{
  if ($group_by === 'quality') {
    $st = $pdo->prepare("SELECT COALESCE(s.quality_name,'(No Quality)') AS quality, 
                               COUNT(*) AS beam_count,
                               SUM(COALESCE(s.meter,0)) AS total_meters,
                               SUM(COALESCE(s.taka,0)) AS total_taka,
                               SUM(COALESCE(s.tar,0)) AS total_tar
                          FROM beam_stock s
                         WHERE $where
                      GROUP BY COALESCE(s.quality_name,'(No Quality)')
                      ORDER BY quality");
    $st->execute($params);
    $group_rows = $st->fetchAll(PDO::FETCH_ASSOC);
  } else {
    // include tar, taka, meter in UI rows
    $st = $pdo->prepare("SELECT s.beam_no, s.quality_name, s.warper_name, s.first_entry_date, s.refreshed_at,
                                s.tar, s.taka, s.meter
                           FROM beam_stock s
                          WHERE $where
                       ORDER BY s.beam_no");
    $st->execute($params);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC);
  }
}catch(Throwable $e){}

$__header = __DIR__ . '/partials/header.php';
if (is_file($__header)) { $PAGE='beam_stock'; include_once $__header; }

$msg = $_GET['msg'] ?? '';
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1">
<title>Beam Stock</title>
<style>
:root{--p:#34A853;--bg:#F5FFF7;--b:#e5e7eb;--t:#202124}
*{box-sizing:border-box} body{margin:0;font:14px system-ui,-apple-system,Segoe UI,Roboto,Arial;background:var(--bg);color:var(--t)}
.wrap{max-width:1100px;margin:16px auto;padding:12px}
.card{background:#fff;border:1px solid var(--b);border-radius:14px;box-shadow:0 8px 20px rgba(0,0,0,.06);padding:16px}
.btn{padding:10px 14px;border:0;border-radius:10px;font-weight:700;cursor:pointer}
.btn-pri{background:var(--p);color:#fff}
.btn-sec{background:#fff;border:1px solid var(--b)}
.grid{display:grid;gap:12px;grid-template-columns:repeat(6,1fr)}
@media(max-width:1100px){.grid{grid-template-columns:1fr 1fr 1fr 1fr}}
@media(max-width:760px){.grid{grid-template-columns:1fr 1fr}}
@media(max-width:520px){.grid{grid-template-columns:1fr}}
select,input{width:100%;padding:10px;border:1px solid #d0d7de;border-radius:10px}
table{width:100%;border-collapse:collapse;margin-top:12px;background:#fff}
th,td{padding:10px;border-bottom:1px solid #eef2f7;text-align:left;font-size:13px}
.ok{margin:10px 0;background:#e6f4ea;border:1px solid #cde7d7;color:#146c2e;padding:8px 10px;border-radius:10px}
.tools{display:flex;gap:8px;flex-wrap:wrap;margin-bottom:12px}
.small{color:#64748b;font-size:12px}
@media print{
  .tools, form, .ok { display:none !important; }
  body{ background:#fff; }
}
</style>
<script>
function doPrint(){ window.print(); }
</script>
</head>
<body>
<div class="wrap">
  <div class="card">
    <h2>Beam Stock</h2>
    <?php if($msg):?><div class="ok"><?=h($msg)?></div><?php endif;?>

<div class="tools">
      <form method="post" action="/erp/beam_stock_refresh.php" style="display:inline">
        <button class="btn btn-pri" type="submit">Refresh Stock</button>
      </form>
      <a class="btn btn-sec" href="?<?=http_build_query(array_merge($_GET,['act'=>'csv']))?>">Download CSV</a>
      <a class="btn btn-sec" href="?<?=http_build_query(array_merge($_GET,['act'=>'pdf']))?>">Download PDF</a>
      <button class="btn btn-sec" type="button" onclick="doPrint()">Print</button>
    </div>

<form method="get" class="grid" style="margin-bottom:8px">
      <div style="grid-column: span 2;">
        <label>Filter by Quality</label>
        <select name="quality_id">
          <option value="">-- All --</option>
          <?php foreach($qualities as $qid=>$qname):
            $sel = ($quality_id!==null && (int)$qid===$quality_id) ? 'selected' : ''; ?>
            <option value="<?= (int)$qid ?>" <?= $sel ?>><?= h($qname) ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div style="grid-column: span 2;">
        <label>Filter by Warper</label>
        <select name="warper_id">
          <option value="">-- All --</option>
          <?php foreach($warpers as $wid=>$wname):
            $sel = ($warper_id!==null && (int)$wid===$warper_id) ? 'selected' : ''; ?>
            <option value="<?= (int)$wid ?>" <?= $sel ?>><?= h($wname) ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div>
        <label>Group By</label>
        <select name="group">
          <option value="" <?= $group_by===''?'selected':'' ?>>None</option>
          <option value="quality" <?= $group_by==='quality'?'selected':'' ?>>Quality</option>
        </select>
      </div>
      <div style="display:flex;align-items:flex-end">
        <button class="btn btn-sec" type="submit">Apply</button>
      </div>
    </form>

<?php if ($group_by==='quality'): ?>
      <table>
        <thead><tr>
          <th>Quality</th>
          <th>Beam Count</th>
          <th>Total Meters</th>
          <th>Total Taka</th>
          <th>Total Tar</th>
        </tr></thead>
        <tbody>
          <?php if(!$group_rows): ?>
            <tr><td colspan="5" class="small">No data. Click Refresh.</td></tr>
          <?php else: foreach($group_rows as $r): ?>
            <tr>
              <td><?= h($r['quality']) ?></td>
              <td><?= (int)$r['beam_count'] ?></td>
              <td><?= (float)$r['total_meters'] ?></td>
              <td><?= (float)$r['total_taka'] ?></td>
              <td><?= (float)$r['total_tar'] ?></td>
            </tr>
          <?php endforeach; endif; ?>
        </tbody>
      </table>
    <?php else: ?>
      <table>
        <thead><tr>
          <th>Beam No</th>
          <th>Quality</th>
          <th>Warper</th>
          <th>First Entry Date</th>
          <th>Tar</th>
          <th>Taka</th>
          <th>Meter</th>
          <th class="small">Refreshed</th>
        </tr></thead>
        <tbody>
          <?php if(!$rows): ?>
            <tr><td colspan="8" class="small">No beams in stock. Click Refresh.</td></tr>
          <?php else: foreach($rows as $r): ?>
            <tr>
              <td><?= h($r['beam_no']) ?></td>
              <td><?= h($r['quality_name'] ?? '') ?></td>
              <td><?= h($r['warper_name']  ?? '') ?></td>
              <td><?= h($r['first_entry_date'] ?? '') ?></td>
              <td><?= h($r['tar'] ?? '') ?></td>
              <td><?= h($r['taka'] ?? '') ?></td>
              <td><?= h($r['meter'] ?? '') ?></td>
              <td class="small"><?= h($r['refreshed_at'] ?? '') ?></td>
            </tr>
          <?php endforeach; endif; ?>
        </tbody>
      </table>
    <?php endif; ?>

<p class="small" style="margin-top:8px">
      Rule: Stock = <code>beam_entry</code> me woh beams jo
      <code>pasaria_entry</code> (primary/secondary) aur <code>pissing_entry</code> (beam_no)
      me <b>nahi</b> hain. “Refresh Stock” dabane par table rebuild hota hai.
    </p>
  </div>
</div>
</body>
</html>