View Code - 20260722

zari_machine_stock_report.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ===========================================================================
   File: /erp/zari_machine_stock_report.php
   Purpose: Zari Machine Stock — Report (Totals by material_category + material_name + print/PDF)
   Notes:
     - Maps NULL/empty material_name -> 'Copper'
     - Uses entry_month (DATE) for month filtering if present; falls back to recorded_at
     - Totals section aggregated by material_category + material_name (as requested)
   =========================================================================== */

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

/* ---------- Auth + DB (bootstrap) ---------- */
require __DIR__ . '/modules/auth/auth.php';
require_login();
$u          = auth_user();
$COMPANY_ID = (int)($u['company_id'] ?? 0);
$USER_ID    = (int)($u['id'] ?? 0);
$pdo        = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

/* ---------- CSRF helpers ---------- */
if (session_status() !== PHP_SESSION_ACTIVE) session_start();
if (empty($_SESSION['csrf'])) $_SESSION['csrf'] = bin2hex(random_bytes(16));
function csrf_field(){ echo '<input type="hidden" name="csrf" value="'.htmlspecialchars($_SESSION['csrf']).'">'; }
function check_csrf(){ if (($_POST['csrf'] ?? '') !== ($_SESSION['csrf'] ?? '')) { http_response_code(400); exit('Bad CSRF'); } }

/* ---------- Small helpers ---------- */
function j($v){ return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
function num($n,$dec=3){ return number_format((float)$n, $dec, '.', ''); }
function redirect_self($extra=''){
  $q = $_GET; if($extra) parse_str($extra, $tmp); if(!empty($tmp)) $q = array_merge($q,$tmp);
  $url = strtok($_SERVER['REQUEST_URI'],'?'); if(!empty($q)) $url .= '?'.http_build_query($q);
  header("Location: $url"); exit;
}

/* ---------- Handle delete (POST) ---------- */
$flash = ''; $err = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'delete') {
  check_csrf();
  try {
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) throw new Exception('Invalid id');
    $st = $pdo->prepare("DELETE FROM zari_machine_stock WHERE id = ? AND company_id = ?");
    $st->execute([$id, $COMPANY_ID]);
    $flash = "Record deleted.";
  } catch (Exception $e) {
    $err = $e->getMessage();
  }
  if ($flash) redirect_self('flash='.urlencode($flash));
  if ($err) redirect_self('err='.urlencode($err));
}

/* ---------- Filters (GET) ---------- */
$F_machine = isset($_GET['machine_no']) && $_GET['machine_no'] !== '' ? (int)$_GET['machine_no'] : 0;
$F_material_category = isset($_GET['material_category']) ? trim($_GET['material_category']) : '';
$F_material_name = isset($_GET['material_name']) ? trim($_GET['material_name']) : ''; // '' means All
$F_month = isset($_GET['f_month']) ? trim($_GET['f_month']) : ''; // YYYY-MM
$Q = isset($_GET['q']) ? trim($_GET['q']) : '';

/* -------- Build WHERE (map NULL/'' material_name -> 'Copper' in comparisons) -------- */
$where = ["company_id = :cid"];
$params = [':cid' => $COMPANY_ID];

if ($F_machine > 0) { $where[] = "machine_no = :mno"; $params[':mno'] = $F_machine; }
if ($F_material_category !== '') { $where[] = "material_category = :matcat"; $params[':matcat'] = $F_material_category; }
if ($F_material_name !== '') { $where[] = "COALESCE(NULLIF(TRIM(material_name),''),'Copper') = :mname"; $params[':mname'] = $F_material_name; }

// Month filter: prefer entry_month (DATE) formatted to YYYY-MM. Fallback to recorded_at's YYYY-MM.
// NOTE: If your schema uses a different column name for the intended month (e.g. for_month), replace entry_month accordingly.
if ($F_month) {
  $where[] = "COALESCE(DATE_FORMAT(entry_month, '%Y-%m'), DATE_FORMAT(recorded_at, '%Y-%m')) = :mon";
  $params[':mon'] = $F_month;
}

if ($Q) { $where[] = "(COALESCE(NULLIF(TRIM(material_name),''),'Copper') LIKE :q OR CAST(machine_no AS CHAR) LIKE :q OR position LIKE :q)"; $params[':q'] = '%'.$Q.'%'; }

$whereSql = implode(' AND ', $where);

/* ---------- Fetch distinct material names for dropdown (map empty->Copper) ---------- */
$mnames = [];
try {
  $mstmt = $pdo->prepare("SELECT DISTINCT COALESCE(NULLIF(TRIM(material_name),''), 'Copper') AS mname
                         FROM zari_machine_stock WHERE company_id = :cid ORDER BY mname ASC");
  $mstmt->execute([':cid'=>$COMPANY_ID]);
  $mrows = $mstmt->fetchAll(PDO::FETCH_ASSOC);
  foreach($mrows as $mr) $mnames[] = $mr['mname'];
} catch (Throwable $e) {
  // ignore, $mnames stays empty
}

/* ---------- Limit ---------- */
$LIMIT = 1000;

/* ---------- Fetch raw rows (COALESCE material_name -> 'Copper') ---------- */
$sql = "SELECT id, machine_no, section_no, position, material_category,
               COALESCE(NULLIF(TRIM(material_name),''),'Copper') AS material_name,
               weight_each, quantity, total_weight, recorded_by, recorded_at, entry_month
        FROM zari_machine_stock
        WHERE $whereSql
        ORDER BY recorded_at DESC, id DESC
        LIMIT :lim";
$stmt = $pdo->prepare($sql);
foreach($params as $k=>$v){ $stmt->bindValue($k,$v); }
$stmt->bindValue(':lim', (int)$LIMIT, PDO::PARAM_INT);
$stmt->execute();
$ROWS = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* ---------- Aggregates (by machine & material name) ---------- */
$aggSql = "SELECT machine_no,
                  COALESCE(NULLIF(TRIM(material_name),''), 'Copper') AS material_name,
                  SUM(quantity) AS total_paddles,
                  SUM(total_weight) AS total_weight
           FROM zari_machine_stock
           WHERE $whereSql
           GROUP BY machine_no, COALESCE(NULLIF(TRIM(material_name),''), 'Copper')
           ORDER BY machine_no, material_name";
$aggStmt = $pdo->prepare($aggSql);
foreach($params as $k=>$v){ $aggStmt->bindValue($k,$v); }
$aggStmt->execute();
$AGG = $aggStmt->fetchAll(PDO::FETCH_ASSOC);

/* ---------- Totals by Material Category + Material Name (map ''->Copper) ---------- */
$matSql = "SELECT material_category,
                  COALESCE(NULLIF(TRIM(material_name),''),'Copper') AS material_name,
                  SUM(quantity) AS total_paddles,
                  SUM(total_weight) AS total_weight
           FROM zari_machine_stock
           WHERE $whereSql
           GROUP BY material_category, COALESCE(NULLIF(TRIM(material_name),''),'Copper')
           ORDER BY material_category, material_name";
$matStmt = $pdo->prepare($matSql);
foreach($params as $k=>$v){ $matStmt->bindValue($k,$v); }
$matStmt->execute();
$MAT_TOTALS = $matStmt->fetchAll(PDO::FETCH_ASSOC);

/* ---------- Printable view handling ---------- */
if (isset($_GET['print_view']) && $_GET['print_view'] == '1') {
    $auto = isset($_GET['auto_print']) && $_GET['auto_print'] == '1';
    ?>
    <!doctype html>
    <html lang="en">
    <head>
      <meta charset="utf-8">
      <title>Printable — Zari Machine Stock Report</title>
      <meta name="viewport" content="width=device-width,initial-scale=1">
      <style>
        body{font-family: Arial, Helvetica, sans-serif; color:#111; margin:18px; background:#fff;}
        h1,h2,h3{margin:0 0 8px;}
        .meta{font-size:13px;margin-bottom:12px;color:#333;}
        table{width:100%;border-collapse:collapse;font-size:12px;margin-bottom:12px;}
        th,td{border:1px solid #ddd;padding:8px;text-align:left;}
        th{background:#f3f7f3;font-weight:700;}
        .right{text-align:right;}
        @media print {
          body{margin:6mm;}
          .no-print{display:none;}
          table th, table td{font-size:11px;padding:6px;}
        }
      </style>
    </head>
    <body>
      <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:8px;">
        <div>
          <h2>Zari Machine Stock — Report</h2>
          <div class="meta">Company: <?= j($COMPANY_ID) ?> • Generated: <?= j(date('Y-m-d H:i')) ?></div>
        </div>
        <div class="no-print" style="text-align:right">
          <button onclick="window.print()" style="padding:8px 12px;border-radius:6px;background:#34A853;color:#fff;border:0;cursor:pointer">Print / Save as PDF</button>
        </div>
      </div>

<h3>Totals by Category & Material Name</h3>
      <table>
        <thead>
          <tr>
            <th>Category</th>
            <th>Material Name</th>
            <th class="right">Total Paddles</th>
            <th class="right">Total Weight (g)</th>
          </tr>
        </thead>
        <tbody>
          <?php if (empty($MAT_TOTALS)): ?>
            <tr><td colspan="4">No data.</td></tr>
          <?php else: foreach($MAT_TOTALS as $mt): ?>
            <tr>
              <td><?= j($mt['material_category']) ?></td>
              <td><?= j($mt['material_name']) ?></td>
              <td class="right"><?= (int)$mt['total_paddles'] ?></td>
              <td class="right"><?= j(num($mt['total_weight'],3)) ?></td>
            </tr>
          <?php endforeach; endif; ?>
        </tbody>
      </table>

<h3>Records</h3>
      <table>
        <thead>
          <tr>
            <th>#</th><th>Recorded At</th><th>Machine</th><th>Section</th><th>Pos</th>
            <th>Material Category</th><th>Material Name</th><th class="right">Wt(each) g</th><th class="right">Qty (paddles)</th><th class="right">Total Wt (g)</th><th>By</th>
          </tr>
        </thead>
        <tbody>
          <?php if (!$ROWS): ?>
            <tr><td colspan="11">No records found.</td></tr>
          <?php else: foreach($ROWS as $r): ?>
            <tr>
              <td><?= (int)$r['id'] ?></td>
              <td><?= j($r['recorded_at']) ?></td>
              <td><?= j($r['machine_no']) ?></td>
              <td><?= j($r['section_no']) ?></td>
              <td><?= j($r['position']) ?></td>
              <td><?= j($r['material_category']) ?></td>
              <td><?= j($r['material_name']) ?></td>
              <td class="right"><?= j(num($r['weight_each'],3)) ?></td>
              <td class="right"><?= (int)$r['quantity'] ?></td>
              <td class="right"><?= j(num($r['total_weight'],3)) ?></td>
              <td><?= (int)$r['recorded_by'] ?></td>
            </tr>
          <?php endforeach; endif; ?>
        </tbody>
      </table>

<?php if ($AGG): ?>
        <h3>Aggregates (by machine & material name)</h3>
        <table>
          <thead><tr><th>Machine</th><th>Material Name</th><th class="right">Total Paddles</th><th class="right">Total Weight (g)</th></tr></thead>
          <tbody>
            <?php foreach($AGG as $a): ?>
              <tr>
                <td><?= j($a['machine_no']) ?></td>
                <td><?= j($a['material_name']) ?></td>
                <td class="right"><?= (int)$a['total_paddles'] ?></td>
                <td class="right"><?= j(num($a['total_weight'],3)) ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      <?php endif; ?>

/* ---------- Normal UI header ---------- */
require_once __DIR__ . '/partials/header.php';
?>

<div class="page-wrap">
  <section class="page-card">
    <header class="page-card__header"><h2>Zari Machine Stock — Report</h2></header>
    <div class="page-card__body">
      <?php if (!empty($_GET['flash'])): ?><div class="notice success"><?= j($_GET['flash']) ?></div><?php endif; ?>
      <?php if (!empty($_GET['err'])): ?><div class="notice error"><?= j($_GET['err']) ?></div><?php endif; ?>

<form method="get" class="form-grid" style="align-items:end;">
        <div class="form-group">
          <label>Machine</label>
          <select name="machine_no" class="input-select">
            <option value="">All</option>
            <?php for($m=1;$m<=7;$m++): $sel = ($F_machine===$m)?'selected':''; ?>
              <option value="<?= $m ?>" <?= $sel ?>>MC <?= $m ?></option>
            <?php endfor; ?>
          </select>
        </div>

<div class="form-group">
          <label>Material Category</label>
          <select name="material_category" class="input-select">
            <option value="">All</option>
            <option value="yarn" <?= $F_material_category==='yarn'?'selected':'' ?>>Yarn</option>
            <option value="zari" <?= $F_material_category==='zari'?'selected':'' ?>>Zari</option>
            <option value="metalic" <?= $F_material_category==='metalic'?'selected':'' ?>>Metalic</option>
          </select>
        </div>

<div class="form-group">
          <label>Material Name</label>
          <select name="material_name" class="input-select">
            <option value="" <?= ($F_material_name==='') ? 'selected' : '' ?>>All</option>
            <?php foreach($mnames as $mn): $sel = ($F_material_name === $mn) ? 'selected' : ''; ?>
              <option value="<?= j($mn) ?>" <?= $sel ?>><?= j($mn) ?></option>
            <?php endforeach; ?>
          </select>
        </div>

<div class="form-group">
          <label>Month (YYYY-MM)</label>
          <input type="month" name="f_month" class="input-text" value="<?= j($F_month) ?>">
        </div>

<div class="form-group">
          <label>Search (material name / machine / position)</label>
          <input type="search" name="q" class="input-text" placeholder="material name or machine no" value="<?= j($Q) ?>">
        </div>

<div class="form-group form-actions">
          <button class="btn btn--primary" type="submit">Apply</button>
          <a class="btn btn--muted" href="<?= strtok($_SERVER['REQUEST_URI'],'?') ?>">Reset</a>
          <a class="btn btn--muted" href="/erp/zari_machine_stock_entry.php">New Entry</a>
          <button type="button" class="btn btn--muted" onclick="openPrintView(false)">Open Print View</button>
          <button type="button" class="btn btn--primary" onclick="openPrintView(true)">Print / Save as PDF</button>
        </div>
      </form>

<div style="margin-top:12px;">
        <h4>Totals by Category & Material Name</h4>
        <div class="table-wrap">
          <table class="table">
            <thead>
              <tr>
                <th>Category</th>
                <th>Material Name</th>
                <th class="right">Total Paddles</th>
                <th class="right">Total Weight (g)</th>
              </tr>
            </thead>
            <tbody>
              <?php if (empty($MAT_TOTALS)): ?>
                <tr><td colspan="4">No data.</td></tr>
              <?php else: foreach($MAT_TOTALS as $mt): ?>
                <tr>
                  <td><?= j($mt['material_category']) ?></td>
                  <td><?= j($mt['material_name']) ?></td>
                  <td class="right"><?= (int)$mt['total_paddles'] ?></td>
                  <td class="right"><?= j(num($mt['total_weight'],3)) ?></td>
                </tr>
              <?php endforeach; endif; ?>
            </tbody>
          </table>
        </div>
      </div>

<?php if ($AGG): ?>
        <div style="margin-top:12px;">
          <h4>Aggregates (by machine & material name)</h4>
          <div class="table-wrap">
            <table class="table">
              <thead><tr><th>Machine</th><th>Material Name</th><th class="right">Total Paddles</th><th class="right">Total Weight (g)</th></tr></thead>
              <tbody>
                <?php foreach($AGG as $a): ?>
                  <tr>
                    <td><?= j($a['machine_no']) ?></td>
                    <td><?= j($a['material_name']) ?></td>
                    <td class="right"><?= (int)$a['total_paddles'] ?></td>
                    <td class="right"><?= j(num($a['total_weight'],3)) ?></td>
                  </tr>
                <?php endforeach; ?>
              </tbody>
            </table>
          </div>
        </div>
      <?php endif; ?>

</div>
  </section>

<section class="page-card">
    <header class="page-card__header"><h3>Records (showing up to <?= $LIMIT ?>)</h3></header>
    <div class="page-card__body">
      <div class="table-wrap">
        <table class="table">
          <thead>
            <tr>
              <th>#</th>
              <th>Recorded At</th>
              <th>Machine</th>
              <th>Section</th>
              <th>Pos</th>
              <th>Material Category</th>
              <th>Material Name</th>
              <th class="right">Wt(each) g</th>
              <th class="right">Qty (paddles)</th>
              <th class="right">Total Wt (g)</th>
              <th>By</th>
              <th>Actions</th>
            </tr>
          </thead>
          <tbody>
            <?php if (!$ROWS): ?>
              <tr><td colspan="12">No records found.</td></tr>
            <?php else: foreach($ROWS as $r): ?>
              <tr>
                <td><?= (int)$r['id'] ?></td>
                <td><?= j($r['recorded_at']) ?></td>
                <td><?= j($r['machine_no']) ?></td>
                <td><?= j($r['section_no']) ?></td>
                <td><?= j($r['position']) ?></td>
                <td><?= j($r['material_category']) ?></td>
                <td><?= j($r['material_name']) ?></td>
                <td class="right"><?= j(num($r['weight_each'],3)) ?></td>
                <td class="right"><?= (int)$r['quantity'] ?></td>
                <td class="right"><?= j(num($r['total_weight'],3)) ?></td>
                <td><?= (int)$r['recorded_by'] ?></td>
                <td class="actions">
                  <a class="btn btn--muted" href="/erp/zari_machine_stock_entry.php?edit=<?= (int)$r['id'] ?>">Edit</a>
                  <form method="post" style="display:inline" onsubmit="return confirm('Delete this record?');">
                    <?php csrf_field(); ?>
                    <input type="hidden" name="action" value="delete">
                    <input type="hidden" name="id" value="<?= (int)$r['id'] ?>">
                    <button class="btn btn--danger" type="submit">Delete</button>
                  </form>
                </td>
              </tr>
            <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </section>

<div class="page-meta">Company: <?= $COMPANY_ID ?> • User: <?= $USER_ID ?></div>
</div>

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