View Code - 20260723

yarn_value_report.php | 20260723_000646.php Initial Domain Snapshot

<?php
/* File: /erp/yarn_value_report.php
   Purpose: Yarn Value Report — same behavior as yarn_stock but adds "Value" column.
            Rate lookup: match ONLY on denier + color (company ignored) as requested.
   Notes:
    - Uses project ACL bootstrap (page_require_access)
    - Uses $ctx['pdo'] and $company_id
    - No core/config edits
    - CSV and PDF export include value column and totals
*/
error_reporting(E_ALL); ini_set('display_errors', 1);

$print_only = (isset($_GET['print']) && (int)$_GET['print'] === 1);
$USE_HEADER = !$print_only;

require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx        = page_require_access('yarn_entry');
$company_id = (int)($ctx['company_id'] ?? 0);
$u          = $ctx['user'] ?? null;

$pdo = $ctx['pdo'] ?? null;
if (!($pdo instanceof PDO)) {
  // try fallback bootstrap similar to other pages
  foreach ([__DIR__.'/core/db.php', __DIR__.'/db.php', __DIR__.'/config/db.php', __DIR__.'/config.php'] as $p) {
    if (is_file($p)) { @include_once $p; }
  }
  if (!($pdo instanceof PDO)) { http_response_code(500); exit('DB bootstrap failed.'); }
}

/* ---- Helpers ---- */
function table_exists(PDO $pdo, string $t): bool {
  try { $pdo->query("SELECT 1 FROM `$t` LIMIT 1"); return true; } catch(Throwable $e){ return false; }
}
function table_cols(PDO $pdo, string $t): array {
  static $cache = [];
  if (isset($cache[$t])) return $cache[$t];
  try { $rows = $pdo->query("DESCRIBE `$t`")->fetchAll(PDO::FETCH_ASSOC); $cache[$t] = array_map(fn($r)=>$r['Field'],$rows); } catch(Throwable $e){ $cache[$t]=[]; }
  return $cache[$t];
}
function has_col(PDO $pdo, string $t, string $c): bool {
  try { return in_array($c, table_cols($pdo,$t), true); } catch(Throwable $e){ return false; }
}
function resolve_company_name(PDO $pdo, $u, int $company_id): string {
  if (isset($GLOBALS['company']['name']) && $GLOBALS['company']['name']) return (string)$GLOBALS['company']['name'];
  if (is_array($u) && !empty($u['company_name'])) return (string)$u['company_name'];
  foreach (['companies','company','company_master'] as $tbl) {
    if (table_exists($pdo, $tbl) && has_col($pdo,$tbl,'name')) {
      try {
        $st=$pdo->prepare("SELECT name FROM `$tbl` WHERE id=? LIMIT 1");
        $st->execute([$company_id]); if($r=$st->fetch()){ return (string)$r['name']; }
      } catch(Throwable $e){}
    }
  }
  return 'Company';
}
function in_out_tables_for(PDO $pdo, string $stock_type): array {
  $stock_type = ucfirst(strtolower($stock_type));
  $unified = table_exists($pdo,'yarn_in') && has_col($pdo,'yarn_in','stock_type') && table_exists($pdo,'yarn_out') && has_col($pdo,'yarn_out','stock_type');
  if ($unified) return ['yarn_in','yarn_out', true];
switch ($stock_type) {
    case 'Tfo':   return ['tfo_in','tfo_out', false];
    case 'Tpm':   return ['tpm_in','tpm_out', false];
    case 'Zari':  return ['zari_in','zari_out', false];
    case 'Kasab': return ['kasab_in','kasab_out', false];
    default:      return ['yarn_in','yarn_out', false];
}

}

/* fetch distinct options from yarn_company_data */
function fetchDistinct(PDO $pdo, $company_id, $col, $parents=[], $stock_type_filter=null) {
  $map = ['yarn_type'=>'yarn_types','company'=>'company_name','denier'=>'denier','color'=>'color'];
  $filters = ['company_id = :cid']; $params = [':cid'=>$company_id];
  if ($stock_type_filter && in_array('stock_type', table_cols($pdo,'yarn_company_data') ?? [], true)) {
    if (strtolower($stock_type_filter) !== 'all') {
      $filters[] = 'TRIM(stock_type) = TRIM(:_st)'; $params[':_st'] = $stock_type_filter;
    }
  }
  foreach (['yarn_type','company','denier'] as $k) {
    if (!empty($parents[$k])) { $filters[]="`{$map[$k]}`= :$k"; $params[":$k"]=$parents[$k]; }
  }
  $sql="SELECT DISTINCT `{$map[$col]}` AS v FROM yarn_company_data WHERE ".implode(' AND ', $filters)." ORDER BY v";
  $st=$pdo->prepare($sql); $st->execute($params); $out=[]; while($r=$st->fetch()){ if($r['v']!=='') $out[]=$r['v']; }
  return $out;
}

/* ---------- Core: build stock WITH value (rate lookup by denier+color only) ---------- */
function build_stock_with_value(PDO $pdo, int $company_id, array $filters, string $stock_type, bool $hide_zero=false): array {
  $stock_type = ucfirst(strtolower($stock_type));
$types_to_process = (strtolower($stock_type) === 'all')
    ? ['Yarn','TFO','TPM','Zari','Kasab']
    : [$stock_type];

$merged_rows = []; $tot_boxes=0.0; $tot_weight=0.0; $tot_value=0.0;
  foreach ($types_to_process as $stype) {
    [$in_tbl, $out_tbl, $use_stock_type] = in_out_tables_for($pdo, $stype);

$where_in = ["company_id = :cid_in"]; $params_in = [':cid_in'=>$company_id];
    $where_out = ["company_id = :cid_out"]; $params_out = [':cid_out'=>$company_id];
    if ($use_stock_type) {
      $where_in[] = "stock_type = :stype_in"; $params_in[':stype_in'] = ucfirst(strtolower($stype));
      $where_out[] = "stock_type = :stype_out"; $params_out[':stype_out'] = ucfirst(strtolower($stype));
    }
    foreach (['yarn_type'=>'yt','company'=>'co','denier'=>'de','color'=>'cl'] as $col => $tag) {
      if (($filters[$col] ?? '') !== '') {
        $where_in[] = "$col = :{$tag}_in"; $params_in[":{$tag}_in"] = $filters[$col];
        $where_out[] = "$col = :{$tag}_out"; $params_out[":{$tag}_out"] = $filters[$col];
      }
    }

$sql = "
      SELECT
        i.yarn_type, i.company, i.denier, i.color,
        i.boxes_in, i.weight_in,
        COALESCE(o.boxes_out,0)  AS boxes_out,
        COALESCE(o.weight_out,0) AS weight_out
      FROM
        (SELECT yarn_type, company, denier, color, SUM(boxes) AS boxes_in, SUM(weight) AS weight_in
         FROM `$in_tbl`
         WHERE ".implode(' AND ', $where_in)."
         GROUP BY yarn_type, company, denier, color) i
      LEFT JOIN
        (SELECT yarn_type, company, denier, color, SUM(boxes) AS boxes_out, SUM(weight) AS weight_out
         FROM `$out_tbl`
         WHERE ".implode(' AND ', $where_out)."
         GROUP BY yarn_type, company, denier, color) o
      ON i.yarn_type=o.yarn_type AND i.company=o.company AND i.denier=o.denier AND i.color=o.color
      ORDER BY i.yarn_type, i.company, i.denier, i.color
    ";
    $st = $pdo->prepare($sql);
    $st->execute($params_in + $params_out);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC);

// rate cache keyed by denier|color (company ignored)
    $rateCache = [];

foreach ($rows as $r) {
      $cur_boxes = max(0,(float)$r['boxes_in'] - (float)$r['boxes_out']);
      $cur_weight = max(0,(float)$r['weight_in'] - (float)$r['weight_out']);
      $avg = $cur_boxes>0 ? $cur_weight/$cur_boxes : 0.0;
      if ($hide_zero && $cur_boxes==0 && $cur_weight==0) continue;

// cache key by denier|color
      $key = trim($r['denier']).'|'.trim($r['color']);
      if (!array_key_exists($key,$rateCache)) {
        try {
          // match ONLY denier and color as requested (company ignored)
          $q = "SELECT rate FROM yarn_rates WHERE TRIM(denier) = :denier AND TRIM(color) = :color LIMIT 1";
          $stmt = $pdo->prepare($q);
          $stmt->execute([':denier'=>trim($r['denier']), ':color'=>trim($r['color'])]);
          $rr = $stmt->fetch(PDO::FETCH_ASSOC);
          $rateCache[$key] = ($rr && is_numeric($rr['rate'])) ? (float)$rr['rate'] : 0.0;
        } catch(Throwable $e){ $rateCache[$key] = 0.0; }
      }
      $rate = $rateCache[$key];

$value = $cur_weight * $rate;

$status = 'OK';
      if ($cur_boxes==0 && $cur_weight>0) $status='Check';
      if ($cur_boxes==0 && $cur_weight==0) $status='Empty';

$merged_rows[] = [
        'stock_type'=>$stype,
        'yarn_type'=>$r['yarn_type'],
        'company'=>$r['company'],
        'denier'=>$r['denier'],
        'color'=>$r['color'],
        'cur_boxes'=>$cur_boxes,
        'cur_weight'=>$cur_weight,
        'avg_per_box'=>$avg,
        'rate'=>$rate,
        'value'=>$value,
        'status'=>$status,
        '_boxes_in'=>(float)$r['boxes_in'],
        '_weight_in'=>(float)$r['weight_in'],
        '_boxes_out'=>(float)$r['boxes_out'],
        '_weight_out'=>(float)$r['weight_out'],
      ];

$tot_boxes += $cur_boxes;
      $tot_weight += $cur_weight;
      $tot_value += $value;
    }
  }

$tot_avg = $tot_boxes>0 ? $tot_weight/$tot_boxes : 0.0;
  return ['rows'=>$merged_rows,'tot_boxes'=>$tot_boxes,'tot_weight'=>$tot_weight,'tot_avg'=>$tot_avg,'tot_value'=>$tot_value];
}

/* ---------- Filters ---------- */
$flt_type   = trim($_GET['yarn_type'] ?? '');
$flt_comp   = trim($_GET['company']   ?? '');
$flt_denier = trim($_GET['denier']    ?? '');
$flt_color  = trim($_GET['color']     ?? '');
$flt_stock_type = trim($_GET['stock_type'] ?? 'Yarn');
if ($flt_stock_type === '') $flt_stock_type = 'Yarn';
$flt_show_zero = trim($_GET['show_zero'] ?? 'with_zero');
$hide_zero = ($flt_show_zero === 'without_zero');

$opts_type   = fetchDistinct($pdo, $company_id, 'yarn_type', [], ($flt_stock_type?:null));
$opts_comp   = fetchDistinct($pdo, $company_id, 'company',   ['yarn_type'=>$flt_type], ($flt_stock_type?:null));
$opts_denier = fetchDistinct($pdo, $company_id, 'denier',    ['yarn_type'=>$flt_type,'company'=>$flt_comp], ($flt_stock_type?:null));
$opts_color  = fetchDistinct($pdo, $company_id, 'color',     ['yarn_type'=>$flt_type,'company'=>$flt_comp,'denier'=>$flt_denier], ($flt_stock_type?:null));

/* ---------- CSV export ---------- */
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
  $today = date('Y-m-d');
  $filename = 'yarn_value_'.$today.'.csv';
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="'.$filename.'"');
  $out = fopen('php://output','w');
  fputcsv($out, ['Stock Type','Yarn Type','Company','Denier','Color','Boxes','Weight','AVG/Box','Rate','Value','Status']);
  $b = build_stock_with_value($pdo, $company_id, ['yarn_type'=>$flt_type,'company'=>$flt_comp,'denier'=>$flt_denier,'color'=>$flt_color], $flt_stock_type, $hide_zero);
  foreach ($b['rows'] as $v) {
    fputcsv($out, [
      $v['stock_type'],$v['yarn_type'],$v['company'],$v['denier'],$v['color'],
      number_format($v['cur_boxes'],3,'.',''),
      number_format($v['cur_weight'],3,'.',''),
      number_format($v['avg_per_box'],3,'.',''),
      number_format($v['rate'],3,'.',''),
      number_format($v['value'],3,'.',''),
      $v['status']
    ]);
  }
  fputcsv($out, ['TOTAL','','','','', number_format($b['tot_boxes'],3,'.',''), number_format($b['tot_weight'],3,'.',''), '', '', number_format($b['tot_value'],3,'.',''), '']);
  fclose($out); exit;
}

/* ---------- PDF export ---------- */
if (isset($_GET['export']) && $_GET['export'] === 'pdf') {
  $companyName = resolve_company_name($pdo,$u,$company_id);
  $todayHuman = date('d M Y');
  ob_start();
  ?>
  <!doctype html><html><head><meta charset="utf-8">
  <style>
    body{font-family:DejaVu Sans,Arial,Helvetica,sans-serif;font-size:12px;margin:18px;color:#222}
    table{width:100%;border-collapse:collapse;margin-top:8px}
    th,td{border:1px solid #ddd;padding:6px 8px;text-align:left}
    th{background:#f6f8fa}
    .right{text-align:right}
    .center{text-align:center}
    h1,h2{margin:0}
    .meta{margin:6px 0 12px;color:#444}
  </style>
  </head><body>
    <h1 class="center"><?=htmlspecialchars($companyName)?> Yarn Value Report</h1>
    <div class="meta center">Date: <?=htmlspecialchars($todayHuman)?> &nbsp; | &nbsp; Stock Type: <?=htmlspecialchars($flt_stock_type)?></div>
  <?php
  $types = (strtolower($flt_stock_type) === 'all') ? ['Yarn','TFO','TPM','Zari'] : [$flt_stock_type];
  foreach ($types as $stype):
    $b_section = build_stock_with_value($pdo, $company_id, ['yarn_type'=>$flt_type,'company'=>$flt_comp,'denier'=>$flt_denier,'color'=>$flt_color], $stype, $hide_zero);
  ?>
    <h2 style="margin-top:12px"><?=htmlspecialchars(strtoupper($stype))?></h2>
    <table>
      <thead><tr>
        <th>Yarn Type</th><th>Company</th><th>Denier</th><th>Color</th>
        <th class="right">Boxes</th><th class="right">Weight</th><th class="right">AVG/Box</th>
        <th class="right">Rate</th><th class="right">Value</th><th>Status</th>
      </tr></thead>
      <tbody>
      <?php if (empty($b_section['rows'])): ?>
        <tr><td colspan="10" class="center">No data for <?=htmlspecialchars($stype)?></td></tr>
      <?php else: foreach($b_section['rows'] as $v): ?>
        <tr>
          <td><?=htmlspecialchars($v['yarn_type'])?></td>
          <td><?=htmlspecialchars($v['company'])?></td>
          <td><?=htmlspecialchars($v['denier'])?></td>
          <td><?=htmlspecialchars($v['color'])?></td>
          <td class="right"><?=number_format($v['cur_boxes'],3)?></td>
          <td class="right"><?=number_format($v['cur_weight'],3)?></td>
          <td class="right"><?=number_format($v['avg_per_box'],3)?></td>
          <td class="right"><?=number_format($v['rate'],3)?></td>
          <td class="right"><?=number_format($v['value'],2)?></td>
          <td><?=htmlspecialchars($v['status'])?></td>
        </tr>
      <?php endforeach; endif; ?>
      </tbody>
    </table>
    <div style="margin:6px 0">Section Total Value: <b><?=number_format($b_section['tot_value'],2)?></b>, Boxes: <b><?=number_format($b_section['tot_boxes'],3)?></b>, Weight: <b><?=number_format($b_section['tot_weight'],3)?></b></div>
  <?php endforeach;
  $html = ob_get_clean();
  // try dompdf
  $dompdf_ok = false;
  foreach (['/vendor/autoload.php','/lib/dompdf/autoload.inc.php'] as $rel) {
    $p = __DIR__.$rel; if (is_file($p)) { require_once $p; $dompdf_ok = true; break; }
  }
  if (class_exists('\\Dompdf\\Dompdf')) $dompdf_ok = true;
  if ($dompdf_ok) {
    $dompdf = new \Dompdf\Dompdf(['isRemoteEnabled'=>true,'isHtml5ParserEnabled'=>true]);
    $dompdf->loadHtml($html);
    $dompdf->setPaper('A4','portrait'); $dompdf->render();
    $dompdf->stream('yarn-value-'.date('Y-m-d').'.pdf',['Attachment'=>true]); exit;
  } else { header('Content-Type: text/html; charset=utf-8'); echo $html; exit; }
}

/* ---- Page render ---- */
$companyName = resolve_company_name($pdo,$u,$company_id);
$todayPrint = date('d M Y');
?><!doctype html><html><head><meta charset="utf-8"><title>Yarn Value Report</title>
<?php if (!$print_only): ?><link rel="stylesheet" href="/erp/public/assets/css/main.css?v=<?=date('Ymd')?>"><?php endif; ?>
</head><body>
<?php if ($USE_HEADER) { $p = __DIR__.'/partials/header.php'; if (file_exists($p)) include $p; } ?>

<div class="wrap" style="max-width:1100px;margin:18px auto">
  <div class="card">
    <div style="display:flex;justify-content:space-between;align-items:center">
      <h2>Yarn Value Report</h2>
      <div>
        <a class="btn" href="?<?=http_build_query(array_merge($_GET,['view'=>'single','print'=>0]))?>">Single</a>
        <a class="btn" href="?<?=http_build_query(array_merge($_GET,['view'=>'all','print'=>0]))?>">All</a>
      </div>
    </div>

<form method="get" style="margin-top:12px;display:flex;gap:8px;flex-wrap:wrap;align-items:center">
      <label>Stock Type
        <select name="stock_type" onchange="this.form.submit()">
          <?php foreach(['All','Yarn','TFO','TPM','Zari','Kasab'] as $s){ $sel=($s===$flt_stock_type)?'selected':''; echo "<option $sel>".htmlspecialchars($s)."</option>"; } ?>
        </select>
      </label>
      <label>Yarn Type
        <select name="yarn_type" onchange="this.form.submit()"><option value="">— All —</option>
          <?php foreach($opts_type as $v){ $sel=($v===$flt_type)?'selected':''; echo "<option value=\"".htmlspecialchars($v)."\" $sel>".htmlspecialchars($v)."</option>"; } ?>
        </select>
      </label>
      <label>Company
        <select name="company" onchange="this.form.submit()"><option value="">— All —</option>
          <?php foreach($opts_comp as $v){ $sel=($v===$flt_comp)?'selected':''; echo "<option value=\"".htmlspecialchars($v)."\" $sel>".htmlspecialchars($v)."</option>"; } ?>
        </select>
      </label>
      <label>Denier
        <select name="denier" onchange="this.form.submit()"><option value="">— All —</option>
          <?php foreach($opts_denier as $v){ $sel=($v===$flt_denier)?'selected':''; echo "<option value=\"".htmlspecialchars($v)."\" $sel>".htmlspecialchars($v)."</option>"; } ?>
        </select>
      </label>
      <label>Color
        <select name="color" onchange="this.form.submit()"><option value="">— All —</option>
          <?php foreach($opts_color as $v){ $sel=($v===$flt_color)?'selected':''; echo "<option value=\"".htmlspecialchars($v)."\" $sel>".htmlspecialchars($v)."</option>"; } ?>
        </select>
      </label>

<label>Show Zero
        <select name="show_zero" onchange="this.form.submit()">
          <option value="with_zero" <?=($flt_show_zero==='with_zero')?'selected':''?>>With Zero</option>
          <option value="without_zero" <?=($flt_show_zero==='without_zero')?'selected':''?>>Without Zero</option>
        </select>
      </label>

<div style="display:flex;gap:8px;align-items:center">
        <button class="btn primary" type="submit">Apply</button>
        <a class="btn ghost" href="?<?=http_build_query(array_merge($_GET,['export'=>'csv']))?>">Export CSV</a>
        <a class="btn" href="?<?=http_build_query(array_merge($_GET,['export'=>'pdf']))?>" target="_blank">Export PDF</a>
        <a class="btn" href="?<?=http_build_query(array_merge($_GET,['print'=>1]))?>" target="_blank">Print</a>
      </div>
    </form>

<div style="margin-top:12px"><strong><?=htmlspecialchars($companyName)?></strong> Yarn Value Report (<?=htmlspecialchars($todayPrint)?>)</div>

<?php
  $types = (strtolower($flt_stock_type) === 'all')
    ? ['Yarn','TFO','TPM','Zari','Kasab']
    : [$flt_stock_type];

foreach ($types as $stype):
        $b_section = build_stock_with_value($pdo, $company_id, ['yarn_type'=>$flt_type,'company'=>$flt_comp,'denier'=>$flt_denier,'color'=>$flt_color], $stype, $hide_zero);
    ?>
      <div style="margin-top:14px">
        <h3 style="margin:0;padding:6px 0;border-top:1px solid #eee;text-align:center"><?=htmlspecialchars(strtoupper($stype))?></h3>
        <div style="margin:6px 0">Total Boxes: <b><?=number_format($b_section['tot_boxes'],3)?></b>
          &nbsp; Total Weight: <b><?=number_format($b_section['tot_weight'],3)?></b>
          &nbsp; Total Value: <b><?=number_format($b_section['tot_value'],2)?></b></div>

<table class="stock-table" style="width:100%;border-collapse:collapse">
          <thead>
            <tr>
              <th>Yarn Type</th><th>Company</th><th>Denier</th><th>Color</th>
              <th style="text-align:right">Boxes</th><th style="text-align:right">Weight</th><th style="text-align:right">AVG/Box</th>
              <th style="text-align:right">Rate</th><th style="text-align:right">Value</th><th>Status</th>
            </tr>
          </thead>
          <tbody>
            <?php if (empty($b_section['rows'])): ?>
              <tr><td colspan="10" style="color:#777">No data for <?=htmlspecialchars($stype)?></td></tr>
            <?php else: foreach($b_section['rows'] as $v): ?>
              <tr>
                <td><?=htmlspecialchars($v['yarn_type'])?></td>
                <td><?=htmlspecialchars($v['company'])?></td>
                <td><?=htmlspecialchars($v['denier'])?></td>
                <td><?=htmlspecialchars($v['color'])?></td>
                <td style="text-align:right"><?=number_format($v['cur_boxes'],3)?></td>
                <td style="text-align:right"><?=number_format($v['cur_weight'],3)?></td>
                <td style="text-align:right"><?=number_format($v['avg_per_box'],3)?></td>
                <td style="text-align:right"><?=number_format($v['rate'],3)?></td>
                <td style="text-align:right"><?=number_format($v['value'],2)?></td>
                <td><?=htmlspecialchars($v['status'])?></td>
              </tr>
            <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
      <?php if (strtolower($flt_stock_type) === 'all'): ?><div style="height:12px;border-bottom:1px solid #eee;margin:18px 0"></div><?php endif; ?>
    <?php endforeach; ?>

</div>
</div>

<?php if ($USE_HEADER) { $fp = __DIR__.'/partials/footer.php'; if (file_exists($fp)) include $fp; } ?>
</body></html>