View Code - 20260722

saree_stock_report.php | 20260722_120325.php Initial Domain Snapshot

<?php
// saree_stock_repot.php (updated)
// Show company name (not id) and include header/footer (hidden on print).
// Assumes modules/auth/page_acl.php bootstrap available.

//////////// bootstrap /////////////
$acl_candidates = [
    __DIR__ . '/modules/auth/page_acl.php',
    __DIR__ . '/../modules/auth/page_acl.php',
    __DIR__ . '/../../modules/auth/page_acl.php',
    __DIR__ . '/erp/modules/auth/page_acl.php',
];
$acl_loaded = false;
foreach ($acl_candidates as $p) {
    if (is_file($p)) { require_once $p; $acl_loaded = true; break; }
}
if (!$acl_loaded) {
    header('Content-Type: text/plain; charset=utf-8', true, 500);
    echo "Missing modules/auth/page_acl.php - cannot continue.";
    exit;
}

$ctx = page_require_access('saree_stock_report');
$pdo = $ctx['pdo'] ?? null;
$user = $ctx['user'] ?? null;
$company_id = (int)($ctx['company_id'] ?? 0);

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

// try company name from ctx first, else try companies table
$company_name = '';
if (!empty($ctx['company_name'])) {
    $company_name = (string)$ctx['company_name'];
} elseif (!empty($ctx['company']['name'])) {
    $company_name = (string)$ctx['company']['name'];
} elseif ($pdo && $company_id) {
    try {
        // try to read companies table (if exists)
        $q = $pdo->prepare("SELECT name FROM companies WHERE id = :cid LIMIT 1");
        $q->execute([':cid'=>$company_id]);
        $r = $q->fetch(PDO::FETCH_ASSOC);
        if ($r && !empty($r['name'])) $company_name = $r['name'];
    } catch (Exception $e) {
        // ignore - fallback below
    }
}
if ($company_name === '') {
    $company_name = 'Company #' . ($company_id ?: '1');
}

// if no DB
if (!$pdo) {
    echo "<h2>Database connection not available (ctx['pdo'] missing)</h2>";
    exit;
}

// GET params
$selected_quality = trim($_GET['quality'] ?? '');
$show = isset($_GET['show']) ? true : false;

// fetch qualities (lookup or distinct)
$qualities = [];
try {
    $r = $pdo->query("SHOW TABLES LIKE 'saree_qualities'")->fetchAll();
    if ($r) {
        $st = $pdo->prepare("SELECT DISTINCT name FROM saree_qualities WHERE company_id = :cid ORDER BY name");
        $st->execute([':cid'=>$company_id]);
        $qualities = array_column($st->fetchAll(PDO::FETCH_ASSOC),'name');
    } else {
        $st = $pdo->prepare("
            SELECT DISTINCT TRIM(quality) AS name FROM saree_stock_in WHERE company_id = :cid AND quality IS NOT NULL AND quality <> ''
            UNION
            SELECT DISTINCT TRIM(quality) FROM saree_stock_out WHERE company_id = :cid AND quality IS NOT NULL AND quality <> ''
            ORDER BY name
        ");
        $st->execute([':cid'=>$company_id]);
        $qualities = array_column($st->fetchAll(PDO::FETCH_ASSOC),'name');
    }
} catch (Exception $e) {
    // ignore
}
if ($selected_quality === '' && count($qualities) > 0) $selected_quality = $qualities[0];

// build report if requested
$colors = [];
$designs = [];
$data = [];
$grandTotals = [];

if ($show && $selected_quality !== '') {
    // colors
    $st = $pdo->prepare("
        SELECT DISTINCT TRIM(color) AS color FROM (
          SELECT color, company_id FROM saree_stock_in
          UNION ALL
          SELECT color, company_id FROM saree_stock_out
        ) t WHERE company_id = :cid AND color IS NOT NULL AND color <> '' ORDER BY color
    ");
    $st->execute([':cid'=>$company_id]);
    $colors = array_column($st->fetchAll(PDO::FETCH_ASSOC),'color');

// designs
    $st = $pdo->prepare("
        SELECT DISTINCT TRIM(design) AS design FROM (
          SELECT design, company_id FROM saree_stock_in
          UNION ALL
          SELECT design, company_id FROM saree_stock_out
        ) t WHERE company_id = :cid AND design IS NOT NULL AND design <> '' ORDER BY design
    ");
    $st->execute([':cid'=>$company_id]);
    $designs = array_column($st->fetchAll(PDO::FETCH_ASSOC),'design');

// sum statements
    $sumInStmt = $pdo->prepare("
        SELECT IFNULL(SUM(saree_qty),0) AS s FROM saree_stock_in
        WHERE company_id = :cid AND quality = :quality AND design = :design AND color = :color
    ");
    $sumOutStmt = $pdo->prepare("
        SELECT IFNULL(SUM(saree_qty),0) AS s FROM saree_stock_out
        WHERE company_id = :cid AND quality = :quality AND design = :design AND color = :color
    ");
    foreach ($designs as $design) {
        $rowTotal = 0;
        foreach ($colors as $color) {
            $sumInStmt->execute([':cid'=>$company_id, ':quality'=>$selected_quality, ':design'=>$design, ':color'=>$color]);
            $in = (int)$sumInStmt->fetchColumn(0);
            $sumOutStmt->execute([':cid'=>$company_id, ':quality'=>$selected_quality, ':design'=>$design, ':color'=>$color]);
            $out = (int)$sumOutStmt->fetchColumn(0);
            $net = $in - $out;
            $data[$design][$color] = $net;
            $rowTotal += $net;
            $grandTotals[$color] = ($grandTotals[$color] ?? 0) + $net;
            $grandTotals['__grand'] = ($grandTotals['__grand'] ?? 0) + $net;
        }
        $data[$design]['__row_total'] = $rowTotal;
    }
    foreach ($colors as $c) if (!isset($grandTotals[$c])) $grandTotals[$c] = 0;
    if (!isset($grandTotals['__grand'])) $grandTotals['__grand'] = 0;
}

// helper include first existing
function require_first_existing(array $paths) {
    foreach ($paths as $p) {
        if (is_file($p)) { require_once $p; return true; }
    }
    return false;
}

// header/footer candidates
$header_candidates = [
    __DIR__ . '/erp/partials/header.php',
    __DIR__ . '/partials/header.php',
    __DIR__ . '/../erp/partials/header.php',
    __DIR__ . '/../../erp/partials/header.php',
];
$footer_candidates = [
    __DIR__ . '/erp/partials/footer.php',
    __DIR__ . '/partials/footer.php',
    __DIR__ . '/../erp/partials/footer.php',
    __DIR__ . '/../../erp/partials/footer.php',
];
$header_included = require_first_existing($header_candidates); // include if exists
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title><?php echo h($company_name); ?> Saree Stock Report <?php echo date('d-M-Y'); ?></title>
<meta name="viewport" content="width=device-width,initial-scale=1">
<style>
  body{font-family:Arial,Helvetica,sans-serif;background:#fff;margin:18px;color:#222}
  .report-wrap{max-width:1100px;margin:0 auto}
  .hdr{text-align:center;margin-bottom:12px}
  .hdr h1{margin:8px 0;font-size:28px;color:#444}
  .hdr .sub{font-size:20px;color:#666;margin-bottom:4px}
  .controls{display:flex;gap:12px;align-items:center;margin-bottom:12px;flex-wrap:wrap}
  label{font-size:14px;color:#333;margin-right:6px}
  select,input[type=date]{padding:8px;border-radius:6px;border:1px solid #ddd;font-size:14px}
  button{padding:8px 12px;border-radius:6px;border:none;background:#2fbe4a;color:#fff;font-weight:600;cursor:pointer}
  button.secondary{background:#3498db}
  table{width:100%;border-collapse:collapse;font-size:13px;margin-top:8px}
  table thead th{background:#4f5b61;color:#fff;padding:8px;border:1px solid #d0d0d0;text-align:left}
  table tbody td{padding:6px;border:1px solid #e7e7e7;text-align:right}
  table tbody td.left{ text-align:left }
  tfoot td{background:#f0f0f0;font-weight:bold;padding:8px}
  .small{font-size:12px;color:#666}
  .actions{margin-top:12px;display:flex;gap:10px;flex-wrap:wrap}
  /* ensure header/footer and controls hidden in print */
  .no-print{ display:block; }
  @media print {
    .no-print{ display:none !important; }
    body{margin:0}
    table thead th{color:#000}
  }
</style>
</head>
<body>
  
  <div class="no-print">
    <?php
      // header already included earlier by require_first_existing; if not, attempt include here too
      if (!$header_included) {
          require_first_existing($header_candidates);
      }
    ?>
  </div>

<div class="report-wrap">
    <div class="hdr">
      <h1><?php echo h($company_name); ?> Saree Stock Report <span class="small"><?php echo date('d-M-Y'); ?></span></h1>
      <div class="sub">Quality : <strong><?php echo h($selected_quality ?: '—'); ?></strong></div>
    </div>

<form method="get" class="no-print" style="margin-bottom:10px;">
      <div class="controls">
        <div>
          <label for="quality">Quality</label>
          <select id="quality" name="quality">
            <?php foreach ($qualities as $q): ?>
              <option value="<?php echo h($q); ?>" <?php echo $q === $selected_quality ? 'selected' : ''; ?>><?php echo h($q); ?></option>
            <?php endforeach; ?>
          </select>
        </div>

<div>
          <label>&nbsp;</label><br>
          <button type="submit" name="show" value="1">Show report</button>
        </div>

<div style="margin-left:auto;display:flex;gap:8px">
          <div class="small" style="align-self:center">Report actions:</div>
          <button type="button" class="no-print" id="btn-print">Print</button>
          <button type="button" class="no-print secondary" id="btn-pdf">Export PDF</button>
          <button type="button" class="no-print" id="btn-excel">Export Excel (CSV)</button>
          <a href="/erp/saree_setwise.php?quality=<?php echo urlencode($selected_quality); ?>" class="no-print" style="text-decoration:none">
            <button type="button" class="no-print secondary">Setwise</button>
          </a>
        </div>
      </div>
    </form>

<?php if ($show && $selected_quality !== ''): ?>
      <div id="report-area">
        <table id="report-table">
          <thead>
            <tr>
              <th style="width:120px" class="left">D.N NO</th>
              <th class="left">D. NAME</th>
              <?php foreach ($colors as $c): ?>
                <th><?php echo h($c); ?></th>
              <?php endforeach; ?>
              <th>TOTAL</th>
            </tr>
          </thead>
          <tbody>
            <?php if (empty($designs)): ?>
              <tr><td class="left" colspan="<?php echo 3 + count($colors); ?>">No data</td></tr>
            <?php else: ?>
              <?php foreach ($designs as $design): 
                $dn_no = ''; $dname = $design;
                if (strpos($design, '|') !== false) {
                  $parts = explode('|', $design, 2);
                  $dn_no = trim($parts[0]);
                  $dname = trim($parts[1]);
                }
                ?>
                <tr>
                  <td class="left"><?php echo h($dn_no); ?></td>
                  <td class="left"><?php echo h($dname); ?></td>
                  <?php foreach ($colors as $c): 
                      $val = $data[$design][$c] ?? 0;
                      $disp = ($val === 0) ? '' : $val;
                  ?>
                    <td><?php echo h($disp); ?></td>
                  <?php endforeach; ?>
                  <td><?php echo h($data[$design]['__row_total'] ?? 0); ?></td>
                </tr>
              <?php endforeach; ?>
            <?php endif; ?>
          </tbody>
          <tfoot>
            <tr>
              <td colspan="<?php echo 2; ?>" style="text-align:right">Grand Total</td>
              <?php foreach ($colors as $c): ?>
                <td><?php echo h($grandTotals[$c] ?? 0); ?></td>
              <?php endforeach; ?>
              <td><?php echo h($grandTotals['__grand'] ?? 0); ?></td>
            </tr>
          </tfoot>
        </table>
      </div>
    <?php else: ?>
      <div class="small">Select quality and click <strong>Show report</strong> to load the stock report.</div>
    <?php endif; ?>

</div>

<div class="no-print">
    <?php require_first_existing($footer_candidates); ?>
  </div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/html2pdf.js/0.9.3/html2pdf.bundle.min.js"></script>
  <script>
  (function(){
    const btnPrint = document.getElementById('btn-print');
    const btnPdf = document.getElementById('btn-pdf');
    const btnExcel = document.getElementById('btn-excel');
    const reportArea = document.getElementById('report-area');
    const reportTable = document.getElementById('report-table');

btnPrint && btnPrint.addEventListener('click', function(){ window.print(); });

btnPdf && btnPdf.addEventListener('click', function(){
      if (!reportArea) { alert('No report to export'); return; }
      if (typeof html2pdf !== 'undefined') {
        const opt = {
          margin: 10,
          filename: 'saree_stock_report_<?php echo date("Ymd"); ?>.pdf',
          image: { type: 'jpeg', quality: 0.98 },
          html2canvas: { scale: 2 },
          jsPDF: { unit: 'mm', format: 'a4', orientation: 'landscape' }
        };
        html2pdf().set(opt).from(reportArea).save();
      } else {
        window.print();
      }
    });

btnExcel && btnExcel.addEventListener('click', function(){
      if (!reportTable) { alert('No report to export'); return; }
      let csv = '';
      const rows = reportTable.querySelectorAll('tr');
      rows.forEach((r) => {
        const cols = r.querySelectorAll('th,td');
        const row = [];
        cols.forEach(c => {
          let txt = c.innerText.replace(/\n/g,' ').trim();
          if (txt.indexOf('"') !== -1) txt = txt.replace(/"/g,'""');
          if (txt.indexOf(',') !== -1 || txt.indexOf('"') !== -1) txt = '"' + txt + '"';
          row.push(txt);
        });
        csv += row.join(',') + '\n';
      });
      const blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
      const fname = 'saree_stock_report_<?php echo date("Ymd"); ?>.csv';
      if (navigator.msSaveBlob) { navigator.msSaveBlob(blob, fname); }
      else {
        const link = document.createElement('a');
        const url = URL.createObjectURL(blob);
        link.setAttribute('href', url);
        link.setAttribute('download', fname);
        link.style.visibility = 'hidden';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
      }
    });
  })();
  </script>
</body>
</html>