View Code - 20260722

yarn_current_stock.php | 20260722_120325.php Initial Domain Snapshot

<?php
$page_title = 'Yarn Stock Report';

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

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

$st = $pdo->prepare("SELECT name FROM companies WHERE id = ?");
$st->execute([$company_id]);
$company_nm = $st->fetchColumn();

/* ================= FILTER INPUT ================= */
$material  = $_GET['material'] ?? '';
$yarn_type = $_GET['yarn_type'] ?? '';
$party     = $_GET['party_company'] ?? '';
$denier    = $_GET['denier'] ?? '';
$color     = $_GET['color'] ?? '';
$zero      = $_GET['zero'] ?? 'without'; // with | without

$today_txt = date('d-M-Y');

/* ================= BASE WHERE ================= */
$where = ['company_id = :cid'];
$params = [':cid'=>$company_id];

if ($material)  { $where[]='material = :m';        $params[':m']=$material; }
if ($yarn_type) { $where[]='yarn_type = :yt';      $params[':yt']=$yarn_type; }
if ($party)     { $where[]='party_company = :pc';  $params[':pc']=$party; }
if ($denier)    { $where[]='denier = :d';          $params[':d']=$denier; }
if ($color)     { $where[]='color = :c';           $params[':c']=$color; }

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

/* ================= MAIN SQL (WEIGHT + BOX COUNT) ================= */
$sql = "
SELECT
  material,
  yarn_type,
  party_company,
  denier,
  color,
  COUNT(DISTINCT box_no)        AS total_boxes,
  SUM(in_wt) - SUM(out_wt)      AS current_weight
FROM (

SELECT
    material COLLATE utf8mb4_unicode_ci      AS material,
    yarn_type COLLATE utf8mb4_unicode_ci     AS yarn_type,
    party_company COLLATE utf8mb4_unicode_ci AS party_company,
    denier COLLATE utf8mb4_unicode_ci        AS denier,
    color COLLATE utf8mb4_unicode_ci         AS color,
    box_no,
    weight AS in_wt,
    0 AS out_wt
  FROM yarn_in_boxwise
  WHERE $where_sql

UNION ALL

SELECT
    material COLLATE utf8mb4_unicode_ci      AS material,
    yarn_type COLLATE utf8mb4_unicode_ci     AS yarn_type,
    party_company COLLATE utf8mb4_unicode_ci AS party_company,
    denier COLLATE utf8mb4_unicode_ci        AS denier,
    color COLLATE utf8mb4_unicode_ci         AS color,
    box_no,
    0 AS in_wt,
    weight AS out_wt
  FROM yarn_out_boxwise
  WHERE $where_sql

) t
GROUP BY material,yarn_type,party_company,denier,color
";

if ($zero === 'without') {
  $sql .= " HAVING current_weight > 0";
}

$sql .= " ORDER BY current_weight DESC";

/* ================= PRINT VIEW ================= */
if (isset($_GET['print_view'])) {
  $st = $pdo->prepare($sql);
  $st->execute($params);
  $rows = $st->fetchAll(PDO::FETCH_ASSOC);
  ?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title><?=htmlspecialchars($company_nm)?> – Yarn Stock Report</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
@media print {
  @page { size: A4 landscape; margin: 10mm; }
  body { background: #fff !important; font-size: 11px; }
  .no-print { display: none !important; }
  table { border-collapse: collapse !important; width: 100% !important; }
  th, td { border: 1px solid #000 !important; padding: 4px 6px !important; }
}
</style>
</head>
<body class="p-3">

<div class="text-center mb-3">
  <h3 class="fw-bold mb-1"><?=htmlspecialchars($company_nm)?></h3>
  <h5 class="text-secondary m-0">Yarn Stock Report – <?=$today_txt?></h5>
</div>

<table class="table table-bordered align-middle">
<thead class="table-dark text-center">
<tr>
  <th>Material</th>
  <th>Yarn</th>
  <th>Company</th>
  <th>Denier</th>
  <th>Color</th>
  <th>Total Boxes</th>
  <th>Current Weight</th>
</tr>
</thead>
<tbody>
<?php 
$t_boxes = 0; $t_weight = 0;
foreach($rows as $r): 
  $t_boxes += $r['total_boxes'];
  $t_weight += $r['current_weight'];
?>
<tr>
  <td><?=htmlspecialchars($r['material'])?></td>
  <td><?=htmlspecialchars($r['yarn_type'])?></td>
  <td><?=htmlspecialchars($r['party_company'])?></td>
  <td><?=htmlspecialchars($r['denier'])?></td>
  <td><?=htmlspecialchars($r['color'])?></td>
  <td class="text-end"><?=number_format($r['total_boxes'])?></td>
  <td class="text-end fw-bold"><?=number_format($r['current_weight'],3)?></td>
</tr>
<?php endforeach; ?>
</tbody>
<tfoot class="table-secondary fw-bold text-end">
  <tr>
    <td colspan="5" class="text-center">Total</td>
    <td><?=number_format($t_boxes)?></td>
    <td><?=number_format($t_weight,3)?></td>
  </tr>
</tfoot>
</table>

/* ================= PDF EXPORT ================= */
if (isset($_GET['export']) && $_GET['export']==='pdf') {

ob_start();
  $st = $pdo->prepare($sql);
  $st->execute($params);
?>
<html>
<head>
<style>
body { font-family: Arial, sans-serif; font-size: 11px; }
table { width: 100%; border-collapse: collapse; margin-top: 10px; }
th, td { border: 1px solid #333; padding: 5px; }
th { background-color: #f2f2f2; }
.text-center { text-align: center; }
.text-end { text-align: right; }
</style>
</head>
<body>
<div class="text-center">
  <h2 style="margin:0;"><?=htmlspecialchars($company_nm)?></h2>
  <h4 style="margin:5px 0;">Yarn Stock Report – <?=$today_txt?></h4>
</div>

<table>
<thead>
<tr>
  <th>Material</th>
  <th>Yarn</th>
  <th>Company</th>
  <th>Denier</th>
  <th>Color</th>
  <th>Total Boxes</th>
  <th>Current Weight</th>
</tr>
</thead>
<tbody>
<?php 
$t_boxes = 0; $t_weight = 0;
foreach($st as $r): 
  $t_boxes += $r['total_boxes'];
  $t_weight += $r['current_weight'];
?>
<tr>
  <td><?=htmlspecialchars($r['material'])?></td>
  <td><?=htmlspecialchars($r['yarn_type'])?></td>
  <td><?=htmlspecialchars($r['party_company'])?></td>
  <td><?=htmlspecialchars($r['denier'])?></td>
  <td><?=htmlspecialchars($r['color'])?></td>
  <td class="text-end"><?=number_format($r['total_boxes'])?></td>
  <td class="text-end"><?=number_format($r['current_weight'],3)?></td>
</tr>
<?php endforeach; ?>
</tbody>
<tfoot>
  <tr style="font-weight:bold; background:#eee;">
    <td colspan="5" class="text-center">Total</td>
    <td class="text-end"><?=number_format($t_boxes)?></td>
    <td class="text-end"><?=number_format($t_weight,3)?></td>
  </tr>
</tfoot>
</table>
</body>
</html>
<?php
  $html = ob_get_clean();

$autoload = __DIR__.'/lib/dompdf/autoload.inc.php';
  if (file_exists($autoload)) require_once $autoload;

if (class_exists('Dompdf\\Dompdf')) {
    $pdf = new Dompdf\Dompdf();
    $pdf->setPaper('A4','landscape');
    $pdf->loadHtml($html);
    $pdf->render();
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="yarn_stock_report.pdf"');
    echo $pdf->output();
  } else {
    echo $html;
  }
  exit;
}

/* ================= SCREEN VIEW ================= */
$st = $pdo->prepare($sql);
$st->execute($params);
$rows = $st->fetchAll(PDO::FETCH_ASSOC);

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

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">

<div class="container-fluid py-3">
  
  
  <div class="card shadow-sm border-0 mb-4 no-print">
    <div class="card-header bg-primary text-white d-flex justify-content-between align-items-center">
      <h5 class="mb-0 fw-bold">📊 Yarn Stock Report</h5>
      <span class="badge bg-light text-primary"><?= $today_txt ?></span>
    </div>
    <div class="card-body bg-light">
      <form method="get" action="">
        <div class="row g-2 align-items-end">
          <div class="col-md-2 col-sm-6">
            <label class="form-label small fw-semibold">Material</label>
            <input type="text" name="material" class="form-control form-control-sm" value="<?=htmlspecialchars($material)?>" placeholder="Filter Material">
          </div>
          <div class="col-md-2 col-sm-6">
            <label class="form-label small fw-semibold">Yarn</label>
            <input type="text" name="yarn_type" class="form-control form-control-sm" value="<?=htmlspecialchars($yarn_type)?>" placeholder="Filter Yarn">
          </div>
          <div class="col-md-2 col-sm-6">
            <label class="form-label small fw-semibold">Company</label>
            <input type="text" name="party_company" class="form-control form-control-sm" value="<?=htmlspecialchars($party)?>" placeholder="Filter Company">
          </div>
          <div class="col-md-2 col-sm-6">
            <label class="form-label small fw-semibold">Denier</label>
            <input type="text" name="denier" class="form-control form-control-sm" value="<?=htmlspecialchars($denier)?>" placeholder="Filter Denier">
          </div>
          <div class="col-md-2 col-sm-6">
            <label class="form-label small fw-semibold">Color</label>
            <input type="text" name="color" class="form-control form-control-sm" value="<?=htmlspecialchars($color)?>" placeholder="Filter Color">
          </div>
          <div class="col-md-2 col-sm-6">
            <label class="form-label small fw-semibold">Zero Filter</label>
            <select name="zero" class="form-select form-select-sm">
              <option value="without" <?=$zero==='without'?'selected':''?>>Without Zero</option>
              <option value="with" <?=$zero==='with'?'selected':''?>>With Zero</option>
            </select>
          </div>
        </div>

<div class="mt-3 text-end d-flex gap-2 justify-content-end">
          <button type="submit" class="btn btn-sm btn-primary px-3">🔍 Load Data</button>
          <a href="yarn_stock_report.php" class="btn btn-sm btn-outline-secondary px-3">🔄 Reset</a>
          
          
          <?php 
            $query_string = $_GET;
            unset($query_string['print_view']);
            unset($query_string['export']);
          ?>
          <a href="?<?=http_build_query($query_string)?>&print_view=1" target="_blank" class="btn btn-sm btn-info text-white px-3">🖨️ Print View</a>
          <button type="submit" name="export" value="pdf" class="btn btn-sm btn-danger px-3">📄 Export PDF</button>
        </div>
      </form>
    </div>
  </div>

<div class="card shadow-sm border-0">
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table table-bordered table-striped table-hover align-middle m-0">
          <thead class="table-dark">
            <tr>
              <th>Material</th>
              <th>Yarn</th>
              <th>Company</th>
              <th>Denier</th>
              <th>Color</th>
              <th class="text-end">Total Boxes</th>
              <th class="text-end">Current Stock (Wt)</th>
            </tr>
          </thead>
          <tbody>
            <?php 
            $tot_boxes = 0;
            $tot_weight = 0;
            if(!empty($rows)): 
              foreach($rows as $r): 
                $tot_boxes += $r['total_boxes'];
                $tot_weight += $r['current_weight'];
            ?>
            <tr>
              <td><?=htmlspecialchars($r['material'])?></td>
              <td><?=htmlspecialchars($r['yarn_type'])?></td>
              <td><?=htmlspecialchars($r['party_company'])?></td>
              <td><?=htmlspecialchars($r['denier'])?></td>
              <td><?=htmlspecialchars($r['color'])?></td>
              <td class="text-end fw-semibold"><?=number_format($r['total_boxes'])?></td>
              <td class="text-end fw-bold text-primary"><?=number_format($r['current_weight'], 3)?></td>
            </tr>
            <?php 
              endforeach; 
            else: 
            ?>
            <tr>
              <td colspan="7" class="text-center text-muted py-4">No yarn stock records found.</td>
            </tr>
            <?php endif; ?>
          </tbody>
          <?php if(!empty($rows)): ?>
          <tfoot class="table-secondary fw-bold">
            <tr>
              <td colspan="5" class="text-center">Grand Total</td>
              <td class="text-end"><?=number_format($tot_boxes)?></td>
              <td class="text-end text-primary"><?=number_format($tot_weight, 3)?></td>
            </tr>
          </tfoot>
          <?php endif; ?>
        </table>
      </div>
    </div>
  </div>

</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>

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