View Code - 20260721

packing_material_consume_report.php | 20260721_154033.php Initial Bulk Import

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

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

function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function fmt_n($v, $dec = 2){ return (float)$v == 0 ? '' : number_format((float)$v, $dec, '.', ','); }

// ── Month selector ─────────────────────────────────────────────────────────
$selected_month = isset($_GET['month']) ? $_GET['month'] : date('Y-m');
// normalise to first-of-month date string (format stored in DB: YYYY-MM-DD)
$sel_month_date  = $selected_month . '-01';
// next month
$next_month_date = date('Y-m-d', strtotime($sel_month_date . ' +1 month'));

// ── 1. Opening stock of selected month ─────────────────────────────────────
$op_stmt = $pdo->prepare("
    SELECT material_type_id, size,
           SUM(opening_kg)  AS opening_kg,
           SUM(opening_pcs) AS opening_pcs
    FROM packing_material_opening_stock
    WHERE company_id = ? AND month = ?
    GROUP BY material_type_id, size
");
$op_stmt->execute([$company_id, $sel_month_date]);
$opening_rows = $op_stmt->fetchAll(PDO::FETCH_ASSOC);

// ── 2. Total IN during selected month ──────────────────────────────────────
$in_stmt = $pdo->prepare("
    SELECT material_type_id, size,
           SUM(in_kg)  AS in_kg,
           SUM(in_pcs) AS in_pcs
    FROM packing_material_in
    WHERE company_id = ? AND month = ?
    GROUP BY material_type_id, size
");
$in_stmt->execute([$company_id, $sel_month_date]);
$in_rows = $in_stmt->fetchAll(PDO::FETCH_ASSOC);

// ── 3. Opening stock of NEXT month (= closing of selected month) ───────────
$cl_stmt = $pdo->prepare("
    SELECT material_type_id, size,
           SUM(opening_kg)  AS closing_kg,
           SUM(opening_pcs) AS closing_pcs
    FROM packing_material_opening_stock
    WHERE company_id = ? AND month = ?
    GROUP BY material_type_id, size
");
$cl_stmt->execute([$company_id, $next_month_date]);
$closing_rows = $cl_stmt->fetchAll(PDO::FETCH_ASSOC);

// ── Merge into a keyed array ────────────────────────────────────────────────
// key = "material_type_id|size"
$data = [];

foreach ($opening_rows as $r) {
    $k = $r['material_type_id'] . '|' . $r['size'];
    $data[$k]['material_type_id'] = $r['material_type_id'];
    $data[$k]['size']             = $r['size'];
    $data[$k]['opening_kg']       = (float)$r['opening_kg'];
    $data[$k]['opening_pcs']      = (float)$r['opening_pcs'];
}
foreach ($in_rows as $r) {
    $k = $r['material_type_id'] . '|' . $r['size'];
    if (!isset($data[$k])) { $data[$k]['material_type_id']=$r['material_type_id']; $data[$k]['size']=$r['size']; $data[$k]['opening_kg']=0; $data[$k]['opening_pcs']=0; }
    $data[$k]['in_kg']  = (float)$r['in_kg'];
    $data[$k]['in_pcs'] = (float)$r['in_pcs'];
}
foreach ($closing_rows as $r) {
    $k = $r['material_type_id'] . '|' . $r['size'];
    if (!isset($data[$k])) { $data[$k]['material_type_id']=$r['material_type_id']; $data[$k]['size']=$r['size']; $data[$k]['opening_kg']=0; $data[$k]['opening_pcs']=0; }
    $data[$k]['closing_kg']  = (float)$r['closing_kg'];
    $data[$k]['closing_pcs'] = (float)$r['closing_pcs'];
}

// Fill missing keys with 0
foreach ($data as $k => &$d) {
    $d['opening_kg']  = $d['opening_kg']  ?? 0;
    $d['opening_pcs'] = $d['opening_pcs'] ?? 0;
    $d['in_kg']       = $d['in_kg']       ?? 0;
    $d['in_pcs']      = $d['in_pcs']      ?? 0;
    $d['closing_kg']  = $d['closing_kg']  ?? 0;
    $d['closing_pcs'] = $d['closing_pcs'] ?? 0;
    // consume = opening + in - closing
    $d['consume_kg']  = $d['opening_kg']  + $d['in_kg']  - $d['closing_kg'];
    $d['consume_pcs'] = $d['opening_pcs'] + $d['in_pcs'] - $d['closing_pcs'];
}
unset($d);

ksort($data);

// ── Material type names ─────────────────────────────────────────────────────
$type_ids = array_unique(array_column($data, 'material_type_id'));
$type_names = [];
if ($type_ids) {
    $in_ph = implode(',', array_fill(0, count($type_ids), '?'));
    $tn_stmt = $pdo->prepare("SELECT id, material_type, simple_name, quality FROM packing_material_type WHERE id IN ($in_ph)");
    $tn_stmt->execute($type_ids);
    foreach ($tn_stmt->fetchAll(PDO::FETCH_ASSOC) as $tn) {
        $label = $tn['material_type'];
        if (!empty($tn['quality'])) $label .= ' / ' . $tn['quality'];
        $type_names[$tn['id']] = ['label' => $label, 'simple' => $tn['simple_name'] ?? ''];
    }
}

// ── Month list for selector (all distinct months in opening stock) ──────────
$months_stmt = $pdo->prepare("SELECT DISTINCT month FROM packing_material_opening_stock WHERE company_id = ? ORDER BY month DESC");
$months_stmt->execute([$company_id]);
$available_months = $months_stmt->fetchAll(PDO::FETCH_COLUMN);

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

@media print {
  @page { size: A4 landscape; margin: 6mm 6mm; }
  body, html { font-size: 7.5pt !important; }
  .no-print, nav, .navbar, .sidebar, header, footer,
  form, .alert-info, button, .btn { display: none !important; }
  .container-fluid { padding: 0 !important; }
  .table-responsive { overflow: visible !important; }
  .consume-tbl { width: 100% !important; font-size: 7pt !important; border-collapse: collapse !important; table-layout: fixed; }
  .consume-tbl col.col-sr   { width: 4% !important; }
  .consume-tbl col.col-name { width: 20% !important; }
  .consume-tbl col.col-size { width: 8% !important; }
  .consume-tbl col.col-data { width: 8.5% !important; }
  .consume-tbl th, .consume-tbl td { padding: 2px 3px !important; white-space: normal !important; word-break: break-word; line-height: 1.2; }
  .consume-tbl small { font-size: 6pt !important; }
  .consume-tbl thead tr:first-child th {
    background: #1a1a2e !important;
    color: #222 !important;
    font-weight: bold !important;
    -webkit-print-color-adjust: exact;
    print-color-adjust: exact;
  }
  .consume-tbl thead tr:last-child th {
    background: #f0f2f5 !important;
    color: #222 !important;
    font-weight: bold !important;
    -webkit-print-color-adjust: exact;
    print-color-adjust: exact;
  }
  .group-open    { background: #eef4ff !important; -webkit-print-color-adjust: exact; print-color-adjust: exact; }
  .group-in      { background: #f0fff4 !important; -webkit-print-color-adjust: exact; print-color-adjust: exact; }
  .group-close   { background: #fff8ee !important; -webkit-print-color-adjust: exact; print-color-adjust: exact; }
  .group-consume { background: #fdf2f8 !important; -webkit-print-color-adjust: exact; print-color-adjust: exact; }
  .print-header { display: flex !important; justify-content: space-between; align-items: baseline; margin-bottom: 3px; }
  .print-header h6 { font-size: 8pt !important; font-weight: bold; margin: 0; }
  .print-header span { font-size: 6.5pt !important; color: #555; }
  .info-bar { display: block !important; font-size: 6pt !important; margin-top: 2px; }
}
.print-header { display: none; }
.info-bar { display: none; }
</style>

<div class="container-fluid py-4">
  <div class="print-header">
    <h6>Packing Material Consume Report — <?= date('F Y', strtotime($sel_month_date)) ?></h6>
    <span>Formula: Opening + IN − Next Month Opening = Consume</span>
  </div>

<div class="d-flex align-items-center gap-3 mb-3 flex-wrap no-print">
    <h5 class="fw-bold mb-0">Packing Material Consume Report</h5>
    <form method="get" class="d-flex align-items-center gap-2">
      <label class="fw-semibold mb-0" style="font-size:13px;">Month:</label>
      <select name="month" class="form-select form-select-sm" style="width:160px;" onchange="this.form.submit()">
        <?php foreach ($available_months as $am):
            $am_val = substr($am, 0, 7); // YYYY-MM
        ?>
          <option value="<?= h($am_val) ?>" <?= $am_val === $selected_month ? 'selected' : '' ?>>
            <?= date('F Y', strtotime($am)) ?>
          </option>
        <?php endforeach; ?>
      </select>
    </form>
    <span class="text-muted" style="font-size:13px;">
      Formula: <strong>Opening + IN − Next Month Opening = Consume</strong>
    </span>
    <button onclick="window.print()" class="btn btn-sm btn-outline-secondary ms-auto">
      <i class="fas fa-print me-1"></i>Print
    </button>
  </div>

<?php if (empty($data)): ?>
    <div class="alert alert-warning">No data found for selected month.</div>
  <?php else: ?>
  <div class="table-responsive">
    <table class="table table-bordered consume-tbl mb-0">
      <colgroup>
        <col class="col-sr">
        <col class="col-name">
        <col class="col-size">
        <col class="col-data"><col class="col-data">
        <col class="col-data"><col class="col-data">
        <col class="col-data"><col class="col-data">
        <col class="col-data"><col class="col-data">
      </colgroup>
      <thead>
        <tr>
          <th rowspan="2">SR</th>
          <th rowspan="2">Material Type</th>
          <th rowspan="2">Size</th>
          <th class="group-open"  style="border-left:3px solid #4361ee;">Opening Stock</th>
          <th class="group-in"    style="border-left:3px solid #2d6a4f;">Total IN</th>
          <th class="group-close" style="border-left:3px solid #e07c24;">Closing Stock</th>
          <th class="group-consume" style="border-left:3px solid #9c27b0;">Consume</th>
        </tr>
        
        </tr>
      </thead>
      <tbody>
        <?php
        $sr = 0;
        $tot = ['opening_kg'=>0,'opening_pcs'=>0,'in_kg'=>0,'in_pcs'=>0,'closing_kg'=>0,'closing_pcs'=>0,'consume_kg'=>0,'consume_pcs'=>0];
        foreach ($data as $d):
            $sr++;
            foreach (['opening_kg','opening_pcs','in_kg','in_pcs','closing_kg','closing_pcs','consume_kg','consume_pcs'] as $col)
                $tot[$col] += $d[$col];
            $type_info  = $type_names[$d['material_type_id']] ?? ['label' => 'Type #' . $d['material_type_id'], 'simple' => ''];
            $type_label = $type_info['label'];
            $type_simple = $type_info['simple'];
            $c_kg_cls  = $d['consume_kg']  < 0 ? 'neg' : ($d['consume_kg']  == 0 ? 'zero' : 'pos');
            $c_pcs_cls = $d['consume_pcs'] < 0 ? 'neg' : ($d['consume_pcs'] == 0 ? 'zero' : 'pos');
        ?>
        <tr>
          <td class="text-center"><?= $sr ?></td>
          <td><strong><?= h($type_label) ?></strong><?php if (!empty($type_simple)): ?><br><small class="text-muted"><?= h($type_simple) ?></small><?php endif; ?></td>
          <td><?= h($d['size']) ?></td>
          <td class="group-open  text-end">
            <?php
              $open = [];
              if ($d['opening_kg'] > 0) $open[] = fmt_n($d['opening_kg'], 3) . ' kg';
              if ($d['opening_pcs'] > 0) $open[] = fmt_n($d['opening_pcs'], 0) . ' pcs';
              echo implode(', ', $open);
            ?>
          </td>
          <td class="group-in  text-end">
            <?php
              $in = [];
              if ($d['in_kg'] > 0) $in[] = fmt_n($d['in_kg'], 3) . ' kg';
              if ($d['in_pcs'] > 0) $in[] = fmt_n($d['in_pcs'], 0) . ' pcs';
              echo implode(', ', $in);
            ?>
          </td>
          <td class="group-close  text-end">
            <?php
              $close = [];
              if ($d['closing_kg'] > 0) $close[] = fmt_n($d['closing_kg'], 3) . ' kg';
              if ($d['closing_pcs'] > 0) $close[] = fmt_n($d['closing_pcs'], 0) . ' pcs';
              echo implode(', ', $close);
            ?>
          </td>
          <td class="group-consume text-end">
            <?php
              $consume = [];
              if ($d['consume_kg'] > 0) $consume[] = fmt_n($d['consume_kg'], 3) . ' kg';
              if ($d['consume_pcs'] > 0) $consume[] = fmt_n($d['consume_pcs'], 0) . ' pcs';
              echo implode(', ', $consume);
            ?>
          </td>
        </tr>
        <?php endforeach; ?>
      </tbody>
      
    </table>
  </div>
  <div class="text-muted mt-2 no-print" style="font-size:12px;">
    <strong>Selected:</strong> <?= date('F Y', strtotime($sel_month_date)) ?>
    &nbsp;|&nbsp; <strong>Opening used:</strong> <?= date('F Y', strtotime($sel_month_date)) ?>
    &nbsp;|&nbsp; <strong>Closing used (next month opening):</strong> <?= date('F Y', strtotime($next_month_date)) ?>
  </div>
  <div class="info-bar">
    Selected: <?= date('F Y', strtotime($sel_month_date)) ?> | Closing: <?= date('F Y', strtotime($next_month_date)) ?>
  </div>
  <?php endif; ?>
</div>

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