View Code - 20260723

loom_production_import.php | 20260723_000646.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/tools/loom_production_import.php
   Title : Loom Production Entry — Import (Excel/CSV) + Preview + Safe Insert
   Scope : Page-local only. NO global/base edits.
   Fixes : Safe optional-column access (no "Undefined array key" warnings)
   Features:
     • CSV + XLSX (PhpSpreadsheet optional; fallback readers included)
     • Header auto-map (case-insensitive, flexible aliases)
     • Robust date parser (Excel serial + common formats)
     • Preview with OK/Error filters, inline validation messages
     • Transactional insert (fast + safe)
     • Duplicate guard (SELECT-based; no schema change required)
     • Optional: Auto-aggregate (per-taka JSON) after import
   Expected columns (aliases ok):
     Date, Quality, Karigar, Meter, Taka No., Mc No., PBN, SBN, Weight, Total Meter, Remark
============================================================================= */
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors', 1);

/* ---------- Auth + PDO (scoped) ---------- */
if (!isset($pdo) || !($pdo instanceof PDO)) { require __DIR__ . '/../core/db.php'; }
require __DIR__ . '/../modules/auth/auth.php';
require_login();
$u          = auth_user();
$company_id = (int)$u['company_id'];

/* Session-level collation hygiene (page-local) */
try {
  $pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_general_ci");
  $pdo->exec("SET SESSION collation_connection = 'utf8mb4_general_ci'");
} catch (Throwable $e) { /* ignore */ }

/* ---------- Optional PhpSpreadsheet ---------- */
$phpss_ok = false;
$autoload = dirname(__DIR__) . '/../vendor/autoload.php';
if (is_file($autoload)) {
  require_once $autoload;
  $phpss_ok = class_exists('PhpOffice\\PhpSpreadsheet\\IOFactory');
}
use PhpOffice\PhpSpreadsheet\IOFactory as SSIO;

/* ---------- Ensure target table (idempotent) ---------- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS loom_production_entry (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id   BIGINT UNSIGNED NOT NULL,
  entry_date   DATE NOT NULL,
  quality      VARCHAR(100) NOT NULL,
  karigar      VARCHAR(100) NOT NULL,
  meter        INT NOT NULL,
  taka_no      INT NOT NULL,
  machine_no   INT NOT NULL,
  pbn          VARCHAR(50) NULL,
  sbn          VARCHAR(50) NULL,
  weight       DECIMAL(10,2) NULL,
  total_meter  INT NULL,
  remark       VARCHAR(255) NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_company_date (company_id, entry_date),
  KEY idx_company_machine (company_id, machine_no),
  KEY idx_company_taka (company_id, taka_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
");

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

/* SAFE cell getter: returns null if index missing */
function cell($row, $idx) { return (is_array($row) && isset($idx) && $idx >= 0 && array_key_exists($idx, $row)) ? $row[$idx] : null; }

function parse_date_any($v){
  if ($v === null || $v === '' || $v === 'NaN' || $v === 'nan') return null;

// Excel serial (Windows base ~ 1899-12-30)
  if (is_numeric($v)) {
    $days = (int)$v;
    if ($days > 10000) {
      $base = new DateTime('1899-12-30', new DateTimeZone('UTC'));
      $base->modify('+' . $days . ' days');
      return $base->format('Y-m-d');
    }
  }

$s = trim((string)$v);
  $s = str_replace('.', '/', $s);

// yyyy-mm-dd already?
  if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $s)) return $s;

// d/m/Y, d-m-Y, m/d/Y, etc.
  $fmts = [
    'd/m/Y','d-m-Y','j/n/Y','j-n-Y','d/m/y','d-m-y','j/n/y','j-n-y',
    'm/d/Y','m-d-Y','n/j/Y','n-j-Y',
    'Y/m/d','Y-m-d'
  ];
  foreach ($fmts as $f) {
    $dt = DateTime::createFromFormat($f, $s);
    if ($dt) return $dt->format('Y-m-d');
  }
  $t = strtotime($s);
  return $t ? date('Y-m-d', $t) : null;
}
function as_int($v, $default = 0){ if ($v === null || $v === '') return $default; return (int)round((float)preg_replace('/[^\d\.\-]/','',$v)); }
function as_float_or_null($v){ if ($v === null || $v === '') return null; return (float)preg_replace('/[^\d\.\-]/','',$v); }

/* Header aliases → canonical keys */
$HEADER_MAP = [
  'date'        => ['date','entry date','tarikh'],
  'quality'     => ['quality','qual','kapda quality','cloth quality'],
  'karigar'     => ['karigar','weaver','operator','karigar name','employee','employee name'],
  'meter'       => ['meter','mtr','meters'],
  'taka_no'     => ['taka no.','taka no','taka','takano'],
  'machine_no'  => ['mc no.','mc no','machine no.','machine','loom','loom no'],
  'pbn'         => ['pbn','primary beam','primary beam no','beam no'],
  'sbn'         => ['sbn','secondary beam','secondary beam no'],
  'weight'      => ['weight','weght','wt'],
  'total_meter' => ['total meter','total','total mtr','sum meter'],
  'remark'      => ['remark','remarks','note'],
];

/* ---------- Simple readers (CSV / XLSX fallback) ---------- */
function read_csv_rows($path, $delim = ','){
  $rows = [];
  if (($fh = fopen($path,'r')) !== false){
    while(($r = fgetcsv($fh, 0, $delim)) !== false){ $rows[] = $r; }
    fclose($fh);
  }
  return $rows;
}
function xlsx_simple_read($path){
  if (!class_exists('ZipArchive')) throw new RuntimeException('ZipArchive not available; upload CSV.');
  $zip = new ZipArchive();
  if ($zip->open($path) !== true) throw new RuntimeException('Cannot open XLSX.');
  // shared strings
  $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 { $buf=''; foreach ($si->r as $r) $buf .= (string)$r->t; $shared[] = $buf; }
    }
  }
  // sheet
  $sheet = $zip->getFromName('xl/worksheets/sheet1.xml');
  if ($sheet === false) {
    for ($i=1;$i<=10;$i++){ $sheet = $zip->getFromName("xl/worksheets/sheet{$i}.xml"); if ($sheet !== false) break; }
    if ($sheet === false) { $zip->close(); throw new RuntimeException('Worksheet not found in XLSX.'); }
  }
  $sx = simplexml_load_string($sheet);
  // A1 col → index
  $colToIdx = function($ref){
    $letters = preg_replace('/\d+/', '', strtoupper((string)$ref));
    $n=0; for ($i=0;$i<strlen($letters);$i++){ $n = $n*26 + (ord($letters[$i])-64); }
    return max(0, $n-1);
  };
  $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;
      $idx = $colToIdx($ref);
      $r[$idx] = $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;
}

/* ---------- Read any sheet to [ [..header..], [..row..], ... ] ---------- */
function read_any_sheet($tmp, $name, $csv_delim=',', $phpss_ok=false){
  $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));
  if ($ext==='csv')  return read_csv_rows($tmp, $csv_delim);
  if ($ext==='xlsx' || $ext==='xls' || $ext==='xlsm') {
    if ($phpss_ok) {
      $reader = SSIO::createReaderForFile($tmp);
      $ss = $reader->load($tmp);
      $ws = $ss->getActiveSheet();
      // 0-based numeric rows
      $rows = $ws->toArray(null, true, true, false);
      return $rows;
    }
    return xlsx_simple_read($tmp);
  }
  throw new RuntimeException("Unsupported file: .$ext (use .csv/.xlsx)");
}

/* ---------- Controller ---------- */
$errors = [];
$flash  = '';
$preview= [];
$stats  = null;

$csv_delim = isset($_POST['csv_delim']) ? ($_POST['csv_delim']==='\t' ? "\t" : $_POST['csv_delim']) : ',';
$auto_aggregate = isset($_POST['auto_aggregate']) ? 1 : 0;

if (isset($_GET['template'])) {
  // Download template (CSV if no PhpSpreadsheet)
  if (!$phpss_ok) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="loom_production_template.csv"');
    $out = fopen('php://output','w');
    fputcsv($out, ['Date','Quality','Karigar','Meter','Taka No.','Mc No.','PBN','SBN','Weight','Total Meter','Remark']);
    fclose($out); exit;
  } else {
    $sheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $ws = $sheet->getActiveSheet();
    $ws->fromArray([['Date','Quality','Karigar','Meter','Taka No.','Mc No.','PBN','SBN','Weight','Total Meter','Remark']], NULL, 'A1');
    $ws->getStyle('A1:K1')->getFont()->setBold(true);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="loom_production_template.xlsx"');
    $w = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($sheet, 'Xlsx');
    $w->save('php://output'); exit;
  }
}

$step = $_POST['step'] ?? '';

if ($step === 'preview' && isset($_FILES['file']) && is_uploaded_file($_FILES['file']['tmp_name'])) {
  try {
    $raw  = read_any_sheet($_FILES['file']['tmp_name'], $_FILES['file']['name'], $csv_delim, $phpss_ok);
    if (count($raw) === 0) throw new RuntimeException('Empty file.');
    $header = $raw[0];
    $hidx = build_header_index($header, $HEADER_MAP);

// Required
    $required = ['date','quality','karigar','meter','taka_no','machine_no'];
    $missing = array_filter($required, fn($k)=> ($hidx[$k] ?? -1) < 0);
    if ($missing) throw new RuntimeException('Missing required columns: '.implode(', ', $missing));

$minDate = null; $maxDate = null;
    for ($i=1; $i<count($raw); $i++) {
      $r = $raw[$i];

// SAFE reads via cell()
      $date  = parse_date_any(          cell($r, $hidx['date'])        );
      $qual  = trim((string)           (cell($r, $hidx['quality']) ?? ''));
      $kari  = trim((string)           (cell($r, $hidx['karigar']) ?? ''));
      $meter = as_int(                  cell($r, $hidx['meter'])        );
      $taka  = as_int(                  cell($r, $hidx['taka_no'])      );
      $mc    = as_int(                  cell($r, $hidx['machine_no'])   );
      $pbn   =                           cell($r, $hidx['pbn']);
      $sbn   =                           cell($r, $hidx['sbn']);
      $wgt   = as_float_or_null(         cell($r, $hidx['weight'])      );
      $tot   = as_int(                   cell($r, $hidx['total_meter']) );
      $rmk   = trim((string)            (cell($r, $hidx['remark']) ?? ''));

$err = [];
      if (!$date)         $err[] = 'Bad/empty Date';
      if ($qual==='')     $err[] = 'Quality required';
      if ($kari==='')     $err[] = 'Karigar required';
      if ($meter<=0)      $err[] = 'Meter must be > 0';
      if ($taka<=0)       $err[] = 'Taka No. must be > 0';
      if ($mc<=0)         $err[] = 'Mc No. must be > 0';

if ($date) {
        if ($minDate===null || $date < $minDate) $minDate = $date;
        if ($maxDate===null || $date > $maxDate) $maxDate = $date;
      }

$preview[] = [
        'row' => $i+1,
        'date'=>$date, 'quality'=>$qual, 'karigar'=>$kari, 'meter'=>$meter,
        'taka_no'=>$taka, 'machine_no'=>$mc, 'pbn'=>$pbn, 'sbn'=>$sbn,
        'weight'=>$wgt, 'total_meter'=>$tot, 'remark'=>$rmk,
        'ok' => empty($err),
        'error' => $err ? implode('; ', $err) : ''
      ];
    }

$_POST['minDate'] = $minDate;
    $_POST['maxDate'] = $maxDate;
  } catch (Throwable $e) { $errors[] = $e->getMessage(); }
}

if ($step === 'import' && isset($_POST['payload'])) {
  $data = json_decode($_POST['payload'], true);
  $minDate = $_POST['minDate'] ?? null;
  $maxDate = $_POST['maxDate'] ?? null;

if (!is_array($data)) {
    $errors[] = 'Bad payload.';
  } else {
    $ok=0; $fail=0; $skipped=0; $errs=[];
    try {
      $pdo->beginTransaction();

// Duplicate check statement (soft guard; no schema change)
      $exists = $pdo->prepare("
        SELECT id FROM loom_production_entry
        WHERE company_id=? AND entry_date=? AND machine_no=? AND taka_no=? AND karigar=? AND meter=?
        LIMIT 1
      ");

$ins = $pdo->prepare("
        INSERT INTO loom_production_entry
          (company_id, entry_date, quality, karigar, meter, taka_no, machine_no, pbn, sbn, weight, total_meter, remark)
        VALUES
          (:cid, :dt, :qlt, :kar, :mtr, :taka, :mc, :pbn, :sbn, :wt, :tot, :rmk)
      ");

foreach ($data as $r) {
        if (empty($r['ok'])) { $skipped++; $errs[] = "Row {$r['row']}: skipped (invalid)."; continue; }

$exists->execute([
          $company_id, $r['date'], $r['machine_no'], $r['taka_no'], $r['karigar'], $r['meter']
        ]);
        if ($exists->fetchColumn()) {
          $skipped++; $errs[] = "Row {$r['row']}: duplicate (same date/mc/taka/karigar/meter).";
          continue;
        }

try {
          $ins->execute([
            ':cid'  => $company_id,
            ':dt'   => $r['date'],
            ':qlt'  => $r['quality'],
            ':kar'  => $r['karigar'],
            ':mtr'  => (int)$r['meter'],
            ':taka' => (int)$r['taka_no'],
            ':mc'   => (int)$r['machine_no'],
            ':pbn'  => ($r['pbn'] !== '' ? $r['pbn'] : null),
            ':sbn'  => ($r['sbn'] !== '' ? $r['sbn'] : null),
            ':wt'   => $r['weight'],
            ':tot'  => $r['total_meter'],
            ':rmk'  => ($r['remark'] !== '' ? $r['remark'] : null),
          ]);
          $ok++;
        } catch (Throwable $e) {
          $fail++; $errs[] = "Row {$r['row']}: ".$e->getMessage();
        }
      }

$pdo->commit();
    } catch (Throwable $e) {
      $pdo->rollBack();
      $errors[] = "Transaction failed: ".$e->getMessage();
    }

$stats = compact('ok','fail','skipped');
    $flash = "Inserted: {$ok} • Failed: {$fail} • Skipped: {$skipped}";

// Optional: Auto-aggregate redirect (per-taka JSON)
    if (empty($errors) && $auto_aggregate && $stats['ok'] > 0 && $minDate && $maxDate) {
      header("Location: /erp/tools/loom_taka_aggregate.php?from=".rawurlencode($minDate)."&to=".rawurlencode($maxDate)."&run=1");
      exit;
    }
  }
}

/* ---------- UI ---------- */
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Loom Production — Import</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  body{font-family:system-ui,Segoe UI,Roboto,Arial;background:#0b0f12;color:#e8e8e8;margin:0}
  .wrap{max-width:1000px;margin:22px auto;padding:0 16px 28px}
  .card{background:#12171c;border:1px solid #2a3137;border-radius:16px;padding:16px;margin-top:16px}
  .head{display:flex;justify-content:space-between;align-items:center;gap:8px;flex-wrap:wrap}
  .btn{display:inline-flex;align-items:center;gap:8px;padding:9px 12px;border:1px solid #2a3137;border-radius:10px;background:#1a2128;color:#e8e8e8;text-decoration:none;cursor:pointer}
  .btn:hover{background:#22303a}
  .btn.primary{background:#1e3a2f}
  .muted{color:#9aa4af;font-size:12px}
  input[type=file],select{background:#0d1117;border:1px solid #2a3137;color:#e8e8e8;border-radius:10px;padding:8px 10px}
  table{width:100%;border-collapse:collapse;margin-top:10px}
  th,td{border-bottom:1px solid #2a3137;padding:7px 6px;font-size:13px;text-align:left}
  th{background:#10161b;position:sticky;top:0}
  .ok{color:#8ef19a} .bad{color:#ffb4b4}
  .stats span{background:#0d1117;border:1px solid #2a3137;border-radius:10px;padding:6px 10px;margin-right:8px;display:inline-block}
  .filters{display:flex;gap:6px;align-items:center;flex-wrap:wrap}
  .row{display:flex;gap:10px;align-items:center;flex-wrap:wrap}
</style>
<script>
  function setFilter(mode){
    const rows = document.querySelectorAll('tbody tr[data-ok]');
    rows.forEach(tr=>{
      const ok = tr.getAttribute('data-ok') === '1';
      if (mode==='all') tr.style.display='';
      else if (mode==='ok') tr.style.display = ok ? '' : 'none';
      else if (mode==='err') tr.style.display = ok ? 'none' : '';
    });
  }
</script>
</head>
<body>
<div class="wrap">

<div class="card head">
    <div>
      <h2 style="margin:0">Loom Production — Import</h2>
      <div class="muted">Columns: Date, Quality, Karigar, Meter, Taka No., Mc No., PBN, SBN, Weight, Total Meter, Remark</div>
    </div>
    <div class="row">
      <a class="btn" href="?template=1">Download Template</a>
      <a class="btn" href="/erp/tools/loom_taka_aggregate.php">Per-Taka Aggregator</a>
    </div>
  </div>

<?php if ($flash): ?>
    <div class="card"><div class="stats"><span><?=h($flash)?></span></div></div>
  <?php endif; ?>
  <?php if ($errors): ?>
    <div class="card"><div class="bad"><?=h(implode(' | ', $errors))?></div></div>
  <?php endif; ?>

<div class="card">
    <form method="post" enctype="multipart/form-data">
      <input type="hidden" name="step" value="preview">
      <div class="row" style="margin-bottom:8px">
        <input type="file" name="file" accept=".xlsx,.xls,.xlsm,.csv" required>
        <select name="csv_delim" title="CSV delimiter (CSV only)">
          <option value="," <?= $csv_delim===','?'selected':'' ?>>CSV delimiter: ,</option>
          <option value=";" <?= $csv_delim===';'?'selected':'' ?>>CSV delimiter: ;</option>
          <option value="\t" <?= $csv_delim==="\t"?'selected':'' ?>>CSV delimiter: Tab</option>
        </select>
        <label style="display:flex;align-items:center;gap:6px">
          <input type="checkbox" name="auto_aggregate" value="1" <?= $auto_aggregate?'checked':'' ?>>
          After import, auto-aggregate per-taka
        </label>
        <button class="btn primary" type="submit">Preview</button>
      </div>
      <div class="muted">PhpSpreadsheet: <?= $phpss_ok ? 'available (XLS/XLSX enabled)' : 'not found (use CSV or install vendor/)' ?></div>
    </form>
  </div>

<?php if ($preview): ?>
    <?php
      $okCount = count(array_filter($preview, fn($r)=>!empty($r['ok'])));
      $errCount= count($preview) - $okCount;
      $minDate = $_POST['minDate'] ?? '';
      $maxDate = $_POST['maxDate'] ?? '';
    ?>
    <div class="card">
      <div class="row" style="justify-content:space-between">
        <div class="stats">
          <span>Rows: <?= count($preview) ?></span>
          <span class="ok">OK: <?= $okCount ?></span>
          <span class="bad">Errors: <?= $errCount ?></span>
          <?php if ($minDate && $maxDate): ?>
            <span>Range: <?=h($minDate)?> → <?=h($maxDate)?></span>
          <?php endif; ?>
        </div>
        <div class="filters">
          <button class="btn" onclick="setFilter('all');return false;">All</button>
          <button class="btn" onclick="setFilter('ok');return false;">Only OK</button>
          <button class="btn" onclick="setFilter('err');return false;">Only Errors</button>
        </div>
      </div>

<?php if ($okCount>0): ?>
        <form method="post" style="margin-top:10px">
          <input type="hidden" name="step" value="import">
          <input type="hidden" name="payload" value="<?=h(json_encode($preview, JSON_UNESCAPED_UNICODE))?>">
          <input type="hidden" name="minDate" value="<?=h($minDate)?>">
          <input type="hidden" name="maxDate" value="<?=h($maxDate)?>">
          <input type="hidden" name="auto_aggregate" value="<?= $auto_aggregate ? '1':'0' ?>">
          <button class="btn primary" type="submit">Import OK Rows</button>
        </form>
      <?php endif; ?>

<div style="overflow:auto; max-height:65vh; margin-top:10px">
        <table>
          <thead>
            <tr>
              <th>#</th>
              <th>Date</th><th>Quality</th><th>Karigar</th><th>Meter</th>
              <th>Taka No.</th><th>Mc No.</th><th>PBN</th><th>SBN</th>
              <th>Weight</th><th>Total Meter</th><th>Remark</th>
              <th>Status</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($preview as $r): ?>
              <tr data-ok="<?= $r['ok']? '1':'0' ?>">
                <td><?= (int)$r['row'] ?></td>
                <td><?= h($r['date']) ?></td>
                <td><?= h($r['quality']) ?></td>
                <td><?= h($r['karigar']) ?></td>
                <td><?= h($r['meter']) ?></td>
                <td><?= h($r['taka_no']) ?></td>
                <td><?= h($r['machine_no']) ?></td>
                <td><?= h($r['pbn']) ?></td>
                <td><?= h($r['sbn']) ?></td>
                <td><?= h($r['weight']) ?></td>
                <td><?= h($r['total_meter']) ?></td>
                <td><?= h($r['remark']) ?></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">
        Duplicate check: (company_id, date, machine_no, taka_no, karigar, meter). Match milne par row **skip** hoga.<br>
        Tip: Import ke baad Aggregator chalakar per-taka JSON bana sakte ho.
      </div>
    </div>
  <?php endif; ?>
</div>
</body>
</html>