View Code - 20260722

yarn_company_import.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/yarn_company_import.php
   Purpose: Import Yarn Company Data (company-scoped)
            - stock_type MUST exist in yarn_stock_types (company_id IN (0, your_company))
            - Columns: stock_type, yarn_types, company_name, denier(any string), color, created_at(optional)
   Scope  : Page-local only. No global/base edits.
   Flow   : Download template → Upload → Preview → Import OK rows
   ============================================================================= */
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors',1);

/* ---------- Auth + PDO ---------- */
require __DIR__ . '/modules/auth/auth.php';
require_once __DIR__ . '/modules/activity/activity_logger.php';
require_login();
$u          = auth_user();
$company_id = (int)$u['company_id'];
$user_id    = (int)$u['id'];
$pdo        = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }

/* ---------- Ensure target table (idempotent) ---------- */
/* NOTE: denier is VARCHAR(50) to allow values like '110 Lichi', '150 ATY', '120/72', '75D' */
$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) NOT NULL,
  company_name VARCHAR(120) NOT NULL,
  denier       VARCHAR(50)  NULL,
  color        VARCHAR(60)  NULL,
  created_at   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_company (company_id),
  INDEX idx_company_type (company_id, yarn_types),
  INDEX idx_company_vendor (company_id, company_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

/* In case the table already exists with INT denier, try to widen it (safe no-op otherwise) */
try { $pdo->exec("ALTER TABLE yarn_company_data MODIFY denier VARCHAR(50) NULL"); } catch(Throwable $e) { /* ignore */ }

/* ---------- Helpers ---------- */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function norm($s){ return preg_replace('/\s+/', ' ', strtolower(trim((string)$s))); }

/* Accept: Excel serial, yyyy-mm-dd, dd/mm/yyyy, mm/dd/yyyy, dd-mm-yyyy, yyyy-mm-dd hh:mm */
function try_parse_dt($v){
  if ($v === null || $v === '') return null;
  if (is_numeric($v)) { $ts = ((int)$v - 25569) * 86400; return gmdate('Y-m-d H:i:s', $ts); }
  $s = trim((string)$v);
  $s = str_replace('.', '/', $s);
  if (preg_match('~^(\d{1,2})/(\d{1,2})/(\d{2,4})(?:[\sT](\d{1,2}):(\d{2})(?::(\d{2}))?)?$~', $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]; }
    $H=(int)($m[4]??0); $i=(int)($m[5]??0); $s2=(int)($m[6]??0);
    return sprintf('%04d-%02d-%02d %02d:%02d:%02d',$y,$M,$d,$H,$i,$s2);
  }
  $t = strtotime($s);
  return $t ? date('Y-m-d H:i:s',$t) : null;
}

/* ---------- Load stock types (master) ---------- */
function load_stock_types(PDO $pdo, int $company_id): array {
  $st = $pdo->prepare("
    SELECT name FROM yarn_stock_types
    WHERE company_id IN (0, ?)
  ");
  $st->execute([$company_id]);
  $names = [];
  foreach ($st->fetchAll(PDO::FETCH_COLUMN, 0) as $n){
    $names[norm($n)] = $n; // normalized → original
  }
  return $names;
}

/* ---------- Simple XLSX reader (fallback without PhpSpreadsheet) ---------- */
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;
}

/* ---------- File read (CSV / XLSX) ---------- */
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 (in_array($ext, ['xlsx','xlsm','xls'], true)) {
    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); // A1 style
    }
    return xlsx_read_simple($tmp);
  }
  throw new RuntimeException("Unsupported file type: .$ext — upload CSV/XLSX.");
}

/* ---------- Normalize table (map headers → keys) ---------- */
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){ $row[$h[$i] ?? ('col'.($i+1))] = $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){ $row[$h[$i] ?? ('col'.($i+1))]=$v; $i++; } $out[]=$row; }
      return $out;
    }
  }
  return [];
}

/* ---------- Template download ---------- */
if (isset($_GET['template'])) {
  $xlsxOk = class_exists('\PhpOffice\PhpSpreadsheet\Spreadsheet');
  $cols = ['stock_type','yarn_types','company_name','denier','color','created_at'];
  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_company_template.csv"');
    $out = fopen('php://output','w'); fputcsv($out, $cols); fclose($out); exit;
  } else {
    $sheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $ws = $sheet->getActiveSheet();
    $ws->fromArray([$cols], NULL, 'A1');
    $ws->getStyle('A1:F1')->getFont()->setBold(true);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="yarn_company_template.xlsx"');
    $w = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($sheet, 'Xlsx'); $w->save('php://output'); exit;
  }
}

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

/* Load allowed stock types once */
$STOCK_TYPES = load_stock_types($pdo, $company_id); // normalized → original

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

// column aliases (loose matching)
    $alias = [
      'stock_type'   => ['stock_type','stock type','stock'],
      'yarn_types'   => ['yarn_types','yarn type','yarn types','type','types'],
      'company_name' => ['company_name','company','vendor','supplier','company name'],
      'denier'       => ['denier','denier(d)','d','dnr'],
      'color'        => ['color','colour','clr'],
      'created_at'   => ['created_at','date','created','entry date','created at'],
    ];
    $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;
    };

// normalize rows
    $normRows=[];
    foreach ($rows as $r){
      $nr=[]; foreach ($r as $k=>$v){ $nr[$normKey($k)] = $v; }
      $isEmpty=true; foreach (['yarn_types','company_name','stock_type','denier','color','created_at'] as $ck){ if (!empty($nr[$ck])){ $isEmpty=false; break; } }
      if (!$isEmpty) $normRows[] = $nr;
    }
    $rows = $normRows;

// Resolve + validate
    foreach ($rows as $i=>$r){
      $stock_type_raw = trim((string)($r['stock_type'] ?? ''));
      $stock_type_key = norm($stock_type_raw);
      $yarn_types     = trim((string)($r['yarn_types'] ?? ''));
      $company_nm     = trim((string)($r['company_name'] ?? ''));
      $denier_raw     = trim((string)($r['denier'] ?? ''));     // string allowed
      $color          = trim((string)($r['color'] ?? ''));
      $created_raw    = $r['created_at'] ?? '';

$err = [];
      if ($stock_type_raw === '') { $err[]='stock_type required'; }
      elseif (!isset($STOCK_TYPES[$stock_type_key])) { $err[]='stock_type not found in yarn_stock_types'; }

if ($yarn_types==='')  { $err[] = 'yarn_types required'; }
      if ($company_nm==='')  { $err[] = 'company_name required'; }
      // denier: any string is allowed; keep as-is (or NULL if empty)
      $denier = ($denier_raw === '' ? null : $denier_raw);

$created_at = null;
      if ($created_raw !== '' && $created_raw !== null) {
        $created_at = try_parse_dt($created_raw);
        if (!$created_at){ $err[]='bad created_at'; }
      }

$preview[] = [
        'row' => $i+2,
        'stock_type'   => isset($STOCK_TYPES[$stock_type_key]) ? $STOCK_TYPES[$stock_type_key] : $stock_type_raw,
        'yarn_types'   => $yarn_types,
        'company_name' => $company_nm,
        'denier'       => $denier,
        'color'        => $color,
        'created_at'   => $created_at,
        '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_company_data
      (company_id, stock_type, yarn_types, company_name, denier, color, created_at)
      VALUES (?,?,?,?,?,?, COALESCE(?, NOW()))");
    foreach ($data as $r){
      if (empty($r['ok'])) { $fail++; $failRows[]=$r['row']; continue; }
      try{
        $ins->execute([
          $company_id,
          $r['stock_type'],
          $r['yarn_types'],
          $r['company_name'],
          ($r['denier'] === '' ? null : $r['denier']),
          ($r['color']  === '' ? null : $r['color']),
          $r['created_at']
        ]);
        $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_company_import',
        'entity_id' => 0,
        'remarks' => 'Yarn company import completed'
      ]);
    }
    $flash = "Imported: {$ok} rows" . ($fail? " | Failed: {$fail} (rows ".implode(', ',$failRows).")":"");
    $import_stats = ['ok'=>$ok,'fail'=>$fail,'failRows'=>$failRows];
  }
}

/* ---------- UI ---------- */
$PAGE_TITLE = 'Yarn Company Import';
$PAGE_ID    = 'yarn_company_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}
  .chip{display:inline-block;padding:2px 8px;border:1px solid #ddd;border-radius:999px;margin:2px;font-size:12px}
</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">
          Required: <b>stock_type</b> (must exist in <code>yarn_stock_types</code>), <b>yarn_types</b>, <b>company_name</b>.<br>
          Optional: <b>denier</b> (any text like '110 Lichi', '150 ATY', '120/72', '75D'), <b>color</b>, <b>created_at</b> (date/time).<br>
          Empty <b>created_at</b> → saved as <b>NOW()</b>.
        </div>
        <div style="margin-top:6px">
          <span class="muted">Allowed stock types:</span>
          <?php foreach ($STOCK_TYPES as $orig): ?>
            <span class="chip"><?=h($orig)?></span>
          <?php endforeach; ?>
        </div>
      </div>
      <div class="flex">
        <a class="btn" href="?template=1">Download Template (.xlsx/.csv)</a>
        <a class="btn" href="/erp/">Back to Home</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>stock_type</th>
              <th>yarn_types</th>
              <th>company_name</th>
              <th>denier</th>
              <th>color</th>
              <th>created_at</th>
              <th>Status</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($preview as $r): ?>
              <tr>
                <td><?= (int)$r['row'] ?></td>
                <td><?= h($r['stock_type']) ?></td>
                <td><?= h($r['yarn_types']) ?></td>
                <td><?= h($r['company_name']) ?></td>
                <td><?= h($r['denier']) ?></td>
                <td><?= h($r['color']) ?></td>
                <td><?= h($r['created_at']) ?></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">
        * stock_type validation: value must exist in <b>yarn_stock_types</b> for your company (or global company_id=0).  
        * Uniqueness चाहिए तो बाद में UNIQUE key जोड़ें (e.g. <i>(company_id, stock_type, yarn_types, company_name, denier, color)</i>).
      </div>
    </div>
  <?php endif; ?>
</div>
</body>
</html>