View Code - 20260723

yarn_in_import.php | 20260723_000646.php Initial Domain Snapshot

<?php
/* ===========================================================================
   File: /erp/yarn_in_import.php
   Purpose: Import Yarn IN entries from Excel/CSV (Preview -> Import)
   Scope : Page-local only. No global/base edits.
   Notes : Masters are validated against yarn_company_data (stock_type='Yarn')
   =========================================================================== */

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

/* ---------- Auth + PDO (ACL; NO auto header) ---------- */
require __DIR__ . '/modules/auth/page_acl.php';
require_once __DIR__ . '/modules/activity/activity_logger.php';
$ctx        = page_require_access('yarn_in_import', ['no_header'=>true]); // header manual
$u          = $ctx['user'];
$company_id = (int)$ctx['company_id'];
$user_id    = (int)$u['id'];
$pdo        = $ctx['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; $pdo = $GLOBALS['pdo'] ?? null; }

/* ---------- Ensure target table (idempotent) ---------- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS yarn_in (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT NOT NULL,
  txn_date DATE NOT NULL,
  yarn_type VARCHAR(100) NOT NULL,
  company   VARCHAR(150) NOT NULL,
  denier    VARCHAR(50)  NOT NULL,
  color     VARCHAR(80)  NOT NULL,
  boxes     DECIMAL(12,3) NOT NULL DEFAULT 0,
  weight    DECIMAL(14,3) NOT NULL DEFAULT 0,
  assign_to VARCHAR(150) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_company (company_id),
  INDEX idx_keys (yarn_type,company,denier,color),
  INDEX idx_date (txn_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

/* ---------- Source table ensure (for validations) ---------- */
$pdo->exec("CREATE TABLE IF NOT EXISTS yarn_company_data (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT NOT NULL,
  stock_type VARCHAR(50) NOT NULL,
  yarn_types VARCHAR(120) NULL,
  company_name VARCHAR(150) NULL,
  denier VARCHAR(50) NULL,
  color VARCHAR(80) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_company (company_id),
  INDEX idx_keys (company_id, stock_type, yarn_types, company_name, denier, color)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

/* ---------- Helpers ---------- */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function try_parse_date($v){
  if ($v === null || $v === '') return null;
  if (is_numeric($v)) { $ts = ((int)$v - 25569) * 86400; return gmdate('Y-m-d', $ts); }
  $s = trim((string)$v);
  $s = str_replace('.', '/', $s);
  if (preg_match('~^(\d{1,2})/(\d{1,2})/(\d{2,4})$~', $s, $m)) {
    $d=(int)$m[1]; $M=(int)$m[2]; $y=(int)$m[3]; if ($y<100){ $y+=2000; }
    if ($M>12 && $d<=12){ [$d,$M]=[$M,$d]; }
    return sprintf('%04d-%02d-%02d',$y,$M,$d);
  }
  $t = strtotime($s); return $t ? date('Y-m-d',$t) : null;
}

/* ---------- File read (CSV / XLSX) ---------- */
function _col_to_index($cellRef){
  $letters = preg_replace('/\d+/', '', strtoupper((string)$cellRef));
  $idx = 0; for ($i=0; $i<strlen($letters); $i++){ $idx = $idx * 26 + (ord($letters[$i]) - 64); }
  return max(0, $idx - 1);
}
function xlsx_read_simple($path){
  if (!class_exists('ZipArchive')) throw new RuntimeException("ZipArchive not available; please upload CSV instead.");
  $zip = new ZipArchive();
  if ($zip->open($path) !== true) throw new RuntimeException("Cannot open XLSX file.");
  $shared = []; $ssi = $zip->locateName('xl/sharedStrings.xml');
  if ($ssi !== false) {
    $xml = simplexml_load_string($zip->getFromIndex($ssi));
    foreach ($xml->si as $si) {
      if (isset($si->t)) { $shared[] = (string)$si->t; }
      else { $txt=''; foreach ($si->r as $r){ $txt.=(string)$r->t; } $shared[]=$txt; }
    }
  }
  $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
  if ($sheetXml === false) {
    for ($i=1; $i<=10; $i++){ $sheetXml = $zip->getFromName("xl/worksheets/sheet{$i}.xml"); if ($sheetXml !== false) break; }
    if ($sheetXml === false) { $zip->close(); throw new RuntimeException("Worksheet not found in XLSX."); }
  }
  $sx = simplexml_load_string($sheetXml);
  $rows = [];
  foreach ($sx->sheetData->row as $row) {
    $r = [];
    foreach ($row->c as $c) {
      $ref = (string)$c['r']; $type= (string)$c['t']; $v = (string)$c->v;
      $val = ($type === 's') ? ($shared[(int)$v] ?? '') : $v;
      $r[_col_to_index($ref)] = $val;
    }
    if ($r) { ksort($r); $line = array_values($r); for ($i=count($line)-1; $i>=0; $i--) { if ($line[$i]!=='' && $line[$i]!==null) break; unset($line[$i]); } $rows[] = array_values($line); }
  }
  $zip->close(); return $rows;
}
function read_any_sheet($tmp, $name){
  $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));
  if ($ext === 'csv') {
    $rows=[]; if(($fp=fopen($tmp,'r'))!==false){ while(($r=fgetcsv($fp))!==false){ $rows[]=$r; } fclose($fp); } return $rows;
  }
  if ($ext === 'xlsx' || $ext === 'xlsm') {
    if (class_exists('\PhpOffice\PhpSpreadsheet\IOFactory')) {
      $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($tmp);
      $ss = $reader->load($tmp); $ws = $ss->getActiveSheet(); return $ws->toArray(null, true, true, true);
    }
    return xlsx_read_simple($tmp);
  }
  throw new RuntimeException("Unsupported file type: .$ext — upload CSV/XLSX.");
}
function normalize_table($raw){
  if (!$raw) return [];
  $first = $raw[0] ?? $raw['1'] ?? null; if ($first===null) return [];
  if (is_array($first)) {
    $keys = array_keys($first); $isA1=is_string($keys[0]);
    if ($isA1) {
      $rows=[]; $hdr=[]; $firstRowIdx=null;
      foreach ($raw as $ri=>$r){ $ri_num=(int)$ri; if ($firstRowIdx===null){ $firstRowIdx=$ri_num; $hdr=$r; continue; } $rows[]=$r; }
      $h=[]; foreach ($hdr as $v){ $h[] = strtolower(trim((string)$v)); }
      $out=[]; foreach ($rows as $r){ $row=[]; $i=0; foreach ($r as $v){ $key=$h[$i] ?? ('col'.($i+1)); $row[$key]=$v; $i++; } $out[]=$row; } return $out;
    } else {
      $hdr=$raw[0]; $body=array_slice($raw,1);
      $h=[]; foreach($hdr as $v){ $h[] = strtolower(trim((string)$v)); }
      $out=[]; foreach($body as $r){ $row=[]; $i=0; foreach($r as $v){ $key=$h[$i] ?? ('col'.($i+1)); $row[$key]=$v; $i++; } $out[]=$row; } return $out;
    }
  }
  return [];
}

/* ---------- Masters cache ---------- */
function master_exists(PDO $pdo, int $company_id, string $type, string $company, string $denier, string $color): bool {
  $sql = "SELECT 1 FROM yarn_company_data
          WHERE company_id=? AND stock_type='Yarn'
            AND TRIM(yarn_types)=TRIM(?) AND TRIM(company_name)=TRIM(?)
            AND TRIM(denier)=TRIM(?) AND TRIM(color)=TRIM(?)
          LIMIT 1";
  $st = $pdo->prepare($sql);
  $st->execute([$company_id,$type,$company,$denier,$color]);
  return (bool)$st->fetchColumn();
}

/* ---------- Template download ---------- */
if (isset($_GET['template'])) {
  $xlsxOk = class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet');
  $hdr = ['txn_date','yarn_type','company','denier','color','boxes','weight','assign_to(optional)'];
  activity_log([
    'company_id' => $company_id ?? 0,
    'user_id' => $u['id'] ?? ($_SESSION['user_id'] ?? 0),
    'module' => 'report',
    'action_name' => 'export',
    'entity_type' => 'excel_export',
    'entity_id' => 0,
    'remarks' => 'Export generated'
  ]);
  if (!$xlsxOk) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="yarn_in_template.csv"');
    $out = fopen('php://output','w'); fputcsv($out, $hdr); fclose($out); exit;
  } else {
    $sheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $ws = $sheet->getActiveSheet(); $ws->fromArray([$hdr], NULL, 'A1'); $ws->getStyle('A1:H1')->getFont()->setBold(true);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="yarn_in_template.xlsx"');
    $w = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($sheet, 'Xlsx'); $w->save('php://output'); exit;
  }
}

/* ---------- Controller ---------- */
$action       = $_POST['action'] ?? '';
$preview      = [];
$errors       = [];
$flash        = '';
$import_stats = null;

if ($action === 'preview' && isset($_FILES['file'])) {
  try{
    $raw  = read_any_sheet($_FILES['file']['tmp_name'], $_FILES['file']['name']);
    $rows = normalize_table($raw);

// column aliases (case-insensitive)
    $alias = [
      'txn_date'  => ['txn_date','date','entry_date','txn date','date of entry'],
      'yarn_type' => ['yarn_type','type','yarn type'],
      'company'   => ['company','company_name','party','party name'],
      'denier'    => ['denier','denier/dty','den','dty'],
      'color'     => ['color','shade','clr'],
      'boxes'     => ['boxes','box','cartons','ctn','ctns'],
      'weight'    => ['weight','wt','kgs','kg'],
      'assign_to' => ['assign_to','assign','location','to (optional)']
    ];
    $normKey = function($k) use ($alias){
      $k = strtolower(trim((string)$k));
      foreach ($alias as $to=>$cands){ if (in_array($k,$cands,true)) return $to; }
      return $k;
    };

$normRows=[];
    foreach ($rows as $r){
      $nr=[];
      foreach ($r as $k=>$v){ $nr[$normKey($k)] = is_string($v)?trim($v):$v; }
      $isEmpty=true; foreach (['txn_date','yarn_type','company','denier','color','boxes','weight'] as $ck){ if (!empty($nr[$ck])){ $isEmpty=false; break; } }
      if (!$isEmpty) $normRows[] = $nr;
    }
    $rows = $normRows;

// Resolve & validate
    $preview = [];
    foreach ($rows as $i=>$r){
      $txn_date = try_parse_date($r['txn_date'] ?? null);
      $type     = trim((string)($r['yarn_type'] ?? ''));
      $company  = trim((string)($r['company']   ?? ''));
      $denier   = trim((string)($r['denier']    ?? ''));
      $color    = trim((string)($r['color']     ?? ''));
      $boxes    = ($r['boxes']  === '' ? null : (float)$r['boxes']);
      $weight   = ($r['weight'] === '' ? null : (float)$r['weight']);
      $assign   = trim((string)($r['assign_to'] ?? ''));

$err = [];
      if (!$txn_date)             $err[]='Bad/empty date';
      if ($type==='')             $err[]='Yarn type missing';
      if ($company==='')          $err[]='Company missing';
      if ($denier==='')           $err[]='Denier missing';
      if ($color==='')            $err[]='Color missing';
      if (!is_numeric($boxes) || $boxes<=0)   $err[]='Boxes must be > 0';
      if (!is_numeric($weight)|| $weight<=0)  $err[]='Weight must be > 0';

// master check
      if ($type!=='' && $company!=='' && $denier!=='' && $color!==''){
        if (!master_exists($pdo,$company_id,$type,$company,$denier,$color)){
          $err[]='Combination not found in yarn_company_data';
        }
      }

$preview[] = [
        'row' => $i+2,
        'txn_date'=>$txn_date, 'yarn_type'=>$type, 'company'=>$company,
        'denier'=>$denier, 'color'=>$color, 'boxes'=>$boxes, 'weight'=>$weight,
        'assign_to' => ($assign!=='' ? $assign : null),
        'ok' => empty($err),
        'error' => $err ? implode('; ', $err) : ''
      ];
    }
  }catch(Throwable $e){
    $errors[] = $e->getMessage();
  }
}

if ($action === 'import' && isset($_POST['payload'])) {
  $data = json_decode($_POST['payload'], true);
  if (!is_array($data)) {
    $errors[]='Bad payload';
  } else {
    $ok = 0; $fail = 0; $failRows=[];
    $ins = $pdo->prepare("INSERT INTO yarn_in
      (company_id, txn_date, yarn_type, company, denier, color, boxes, weight, assign_to)
      VALUES (?,?,?,?,?,?,?,?,?)");
    foreach ($data as $r){
      if (empty($r['ok'])) { $fail++; $failRows[]=$r['row']; continue; }
      try{
        $ins->execute([
          $company_id,
          $r['txn_date'],
          $r['yarn_type'],
          $r['company'],
          $r['denier'],
          $r['color'],
          $r['boxes'],
          $r['weight'],
          $r['assign_to']
        ]);
        $ok++;
      }catch(Throwable $e){
        $fail++; $failRows[]=$r['row'];
      }
    }
    if ($ok > 0) {
      activity_log([
        'company_id' => $company_id ?? 0,
        'user_id' => $u['id'] ?? ($_SESSION['user_id'] ?? 0),
        'module' => 'yarn',
        'action_name' => 'create',
        'entity_type' => 'yarn_in_import',
        'entity_id' => 0,
        'remarks' => 'Yarn in import completed'
      ]);
    }
    $flash = "Imported: {$ok} rows" . ($fail? " | Failed: {$fail} (rows ".implode(', ',$failRows).")":"");
    $import_stats = ['ok'=>$ok,'fail'=>$fail,'failRows'=>$failRows];
  }
}

/* ---------- UI ---------- */
$PAGE_TITLE = 'Yarn IN Import';
$PAGE_ID    = 'yarn_in_import';
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title><?=h($PAGE_TITLE)?></title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  :root{ --mm-green:#34A853; --mm-bg:#F5FFF7; --mm-gray:#5F6368; }
  body{font-family:system-ui,Segoe UI,Roboto,Arial;background:var(--mm-bg);margin:0;color:#222}
  .wrap{max-width:1000px;margin:18px auto;padding:0 16px 24px}
  .card{background:#fff;border:1px solid #eee;border-radius:14px;box-shadow:0 2px 8px rgba(0,0,0,.04);padding:16px;margin-top:16px}
  h1{margin:0 0 6px}
  .muted{color:#666;font-size:12px}
  .btn{display:inline-flex;align-items:center;gap:8px;padding:8px 12px;border:1px solid #d1d5db;border-radius:10px;background:#fff;color:#111;font-size:14px;text-decoration:none}
  .btn:hover{background:#f7fff8;border-color:#b7dfc1}
  .btn.primary{background:var(--mm-green);color:#fff;border-color:transparent}
  table{width:100%;border-collapse:collapse;font-size:13px}
  th,td{border:1px solid #eee;padding:6px 8px;text-align:left}
  th{background:#fafafa}
  .ok{color:#0a0} .bad{color:#b00}
  .flex{display:flex;gap:8px;flex-wrap:wrap;align-items:center}
</style>
</head>
<body>
<?php
$__header = __DIR__ . '/partials/header.php';
if (file_exists($__header)) {
  $PAGE = $PAGE_ID;
  include_once $__header;
}
?>
<div class="wrap">
  <?php if ($flash): ?><div class="card ok"><?=h($flash)?></div><?php endif; ?>
  <?php if ($errors): ?><div class="card bad"><?=h(implode(' | ', $errors))?></div><?php endif; ?>

<div class="card">
    <div class="flex" style="justify-content:space-between">
      <div>
        <h1><?=h($PAGE_TITLE)?></h1>
        <div class="muted">Columns required: <b>txn_date, yarn_type, company, denier, color, boxes, weight</b> (assign_to optional)</div>
      </div>
      <div class="flex">
        <a class="btn" href="?template=1">Download Template (.xlsx/.csv)</a>
        <a class="btn" href="/erp/yarn_entry.php">Go to Yarn Entry</a>
      </div>
    </div>

<form method="post" enctype="multipart/form-data" style="margin-top:12px">
      <input type="hidden" name="action" value="preview">
      <div class="flex">
        <input type="file" name="file" accept=".xlsx,.xlsm,.xls,.csv" required>
        <button class="btn primary">Preview</button>
      </div>
    </form>
  </div>

<?php if ($preview): ?>
    <?php $okCount = count(array_filter($preview, fn($r)=>!empty($r['ok']))); $badCount= count($preview) - $okCount; ?>
    <div class="card">
      <div class="flex" style="justify-content:space-between">
        <div><b>Preview</b> — OK: <span class="ok"><?=$okCount?></span>, Errors: <span class="bad"><?=$badCount?></span></div>
        <?php if ($okCount>0): ?>
          <form method="post" id="impForm">
            <input type="hidden" name="action" value="import">
            <input type="hidden" name="payload" value="<?=h(json_encode($preview, JSON_UNESCAPED_UNICODE))?>">
            <button class="btn primary">Import OK Rows</button>
          </form>
        <?php endif; ?>
      </div>
      <div style="overflow:auto; max-height:60vh; margin-top:10px">
        <table>
          <thead>
            <tr>
              <th>#(Row)</th>
              <th>txn_date</th><th>yarn_type</th><th>company</th><th>denier</th><th>color</th>
              <th>boxes</th><th>weight</th><th>assign_to</th>
              <th>Status</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($preview as $r): ?>
              <tr>
                <td><?= (int)$r['row'] ?></td>
                <td><?= h($r['txn_date']) ?></td>
                <td><?= h($r['yarn_type']) ?></td>
                <td><?= h($r['company']) ?></td>
                <td><?= h($r['denier']) ?></td>
                <td><?= h($r['color']) ?></td>
                <td><?= h($r['boxes']) ?></td>
                <td><?= h($r['weight']) ?></td>
                <td><?= h($r['assign_to']) ?></td>
                <td class="<?= $r['ok']?'ok':'bad' ?>"><?= h($r['ok']?'OK':$r['error']) ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
      <div class="muted" style="margin-top:6px">
        * Masters check: values must exist in <b>yarn_company_data</b> with <b>stock_type='Yarn'</b> (company scoped).
      </div>
    </div>
  <?php endif; ?>
</div>
</body>
</html>