View Code - 20260721

loom_production_entry_importer.php | 20260721_154033.php Initial Bulk Import

<?php
/* ============================================================
   Sheet/CSV/XLSX -> loom_production_entry importer
   Masters used:
     - Karigar  : loom_karigar_master
     - Quality  : loom_quality_master
     - Khata    : khatas (auto by machine range)
     - Machine  : machines (if present) else Mc No. as id
   Company scoped. No global/base edits.
   ============================================================ */
error_reporting(E_ALL); ini_set('display_errors',1);

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

/* ---------------- CONFIG ---------------- */
const UPSERT_MODE = 'skip'; // 'skip' | 'update'

$DEFAULTS = [
  'pbn'        => 0,
  'sbn'        => 0,
  'extra_mtr'  => 0.0,
  'weight'     => 0.0,
  'remark'     => '',
];

$INCLUDE_TOTAL_METER_IF_PRESENT = true; // Sheet ka "Total Meter" ho to use karlo
$STRICT_ROW = true; // required fields missing हों तो row skip

// Expected headers (case-insensitive aliases)
$HDR = [
  'date'        => ['date','dt'],
  'quality'     => ['quality','qual','fabric'],
  'karigar'     => ['karigar','weaver','worker','name','karigar name'],
  'meter'       => ['meter','mtr','mtrs'],
  'taka_no'     => ['taka no','taka no.','taka'],
  'mc_no'       => ['mc no','machine','machine no','loom','loom no','mc'],
  'total_meter' => ['total meter','sum meter','meter total'],
];

/* -------------------- POST: process -------------------- */
if ($_SERVER['REQUEST_METHOD']==='POST') {
  try {
    $commit = isset($_POST['commit']) && $_POST['commit'] === '1';
    $raw = '';

// file upload
    if (!empty($_FILES['datafile']['tmp_name']) && $_FILES['datafile']['error']===UPLOAD_ERR_OK) {
      $tmp = $_FILES['datafile']['tmp_name'];
      $name= $_FILES['datafile']['name'];
      $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));
      if ($ext==='csv' || $ext==='tsv') {
        $raw = file_get_contents($tmp);
        if ($ext==='tsv') $raw = str_replace("\t", ",", $raw);
      } elseif ($ext==='xlsx') {
        if (!class_exists('\\PhpOffice\\PhpSpreadsheet\\IOFactory')) {
          throw new Exception('XLSX support not installed. Upload CSV or paste data.');
        }
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $sheet = $reader->load($tmp)->getActiveSheet();
        $rows = [];
        foreach ($sheet->getRowIterator() as $row) {
          $cells = [];
          foreach ($row->getCellIterator() as $cell) { $cells[] = trim((string)$cell->getCalculatedValue()); }
          for ($i=count($cells)-1;$i>=0;$i--) { if ($cells[$i] !== '') break; unset($cells[$i]); }
          if ($cells) $rows[] = $cells;
        }
        $raw = rows_to_csv_string($rows);
      } else {
        throw new Exception('Please upload CSV/TSV/XLSX only.');
      }
    }
    // paste fallback
    if (!$raw) {
      $raw = trim((string)($_POST['paste'] ?? ''));
      if ($raw==='') throw new Exception('No data provided.');
      if (strpos($raw, "\t")!==false) $raw = str_replace("\t", ",", $raw);
    }

// parse csv
    [$headers, $rows] = parse_csv_text($raw);
    $col = resolve_indices($headers, $HDR);

// ---- Load Master Maps (company-scoped) ----
    $karigarMap = map_karigar_from_lkm($pdo, $company_id);       // normalize_name(name) => id
    $qualityMap = map_quality_from_lqm($pdo, $company_id);       // UPPER(name) => id
    $machineMap = map_machines_optional($pdo, $company_id);      // numeric machine_no => id (if table exists)
    $khataRows  = load_khatas($pdo, $company_id);                // ranges to pick khata_id

// ---- Build target grouped records ----
    [$records, $unresolved] = build_records(
      $rows, $col, $karigarMap, $qualityMap, $machineMap, $khataRows,
      $DEFAULTS, $INCLUDE_TOTAL_METER_IF_PRESENT, $STRICT_ROW
    );

// ---- Commit inserts / updates ----
    $result = ['inserted'=>0,'updated'=>0,'duplicates'=>0,'errors'=>[]];
    if ($commit) {
      $pdo->beginTransaction();
      try {
        foreach ($records as $r) {
          $exists_id = find_existing($pdo, $company_id, $r);
          if ($exists_id) {
            if (UPSERT_MODE==='update') {
              $ok = update_row($pdo, $exists_id, $r);
              $ok ? $result['updated']++ : $result['errors'][]="update failed id=$exists_id";
            } else {
              $result['duplicates']++;
            }
          } else {
            $ok = insert_row($pdo, $company_id, $created_by, $r);
            $ok ? $result['inserted']++ : $result['errors'][]="insert failed (taka ".$r['taka_no'].")";
          }
        }
        $pdo->commit();
      } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
      }
    }

// ---- Output summary ----
    header('Content-Type: text/html; charset=utf-8');
    echo "<pre style='white-space:pre-wrap;font:14px/1.35 monospace'>";
    echo "Rows parsed: ".count($rows)."\n";
    echo "Target records (grouped): ".count($records)."\n\n";
    if ($unresolved['karigar']) echo "UNRESOLVED karigar names:\n- ".implode("\n- ", array_keys($unresolved['karigar']))."\n\n";
    if ($unresolved['machine']) echo "UNRESOLVED machine (Mc No.):\n- ".implode("\n- ", array_keys($unresolved['machine']))."\n\n";
    if ($unresolved['quality']) echo "UNRESOLVED quality names:\n- ".implode("\n- ", array_keys($unresolved['quality']))."\n\n";
    echo "Sample payload (first 3):\n";
    foreach (array_slice($records,0,3) as $r) {
      echo json_encode($r, JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE)."\n---\n";
    }
    if ($commit) {
      echo "\nCOMMIT RESULT:\nInserted: {$result['inserted']}\nUpdated: {$result['updated']}\nSkipped (dup): {$result['duplicates']}\n";
      if (!empty($result['errors'])) { echo "Errors:\n"; foreach ($result['errors'] as $e) echo "- $e\n"; }
    } else {
      echo "\n(Dry-run only. Tick 'Commit inserts' to write into DB.)";
    }
    echo "</pre>";
    exit;

} catch (Throwable $e) {
    http_response_code(400);
    header('Content-Type: text/plain; charset=utf-8');
    echo "ERROR: ".$e->getMessage();
    exit;
  }
}

/* -------------------------- UI -------------------------- */
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Import Production → loom_production_entry</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  body{font-family:system-ui,Segoe UI,Roboto,Arial,sans-serif;background:#f6fbf7;margin:0}
  .wrap{max-width:880px;margin:24px auto;background:#fff;padding:16px 18px;border-radius:12px;box-shadow:0 10px 28px rgba(0,0,0,.06)}
  h2{margin:0 0 8px}
  .muted{color:#666;font-size:13px}
  textarea{width:100%;min-height:160px;border:1px solid #ddd;border-radius:10px;padding:10px}
  .row{display:flex;gap:12px;align-items:center;flex-wrap:wrap}
  .btn{background:#34A853;color:#fff;border:0;border-radius:10px;padding:10px 14px;font-weight:600;cursor:pointer}
  .card{background:#fafafa;border:1px dashed #ddd;border-radius:10px;padding:10px;margin-top:10px}
  label{font-size:13px}
</style>
</head>
<body>
<div class="wrap">
  <h2>Import Production → loom_production_entry</h2>
  <div class="muted">Upload CSV/XLSX <b>or</b> paste rows (columns: Date, Quality, Karigar, Meter, Taka No., Mc No., Total Meter)</div>
  <form method="post" enctype="multipart/form-data" style="margin-top:12px">
    <div class="row">
      <input type="file" name="datafile" accept=".csv,.tsv,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
      <label><input type="checkbox" name="commit" value="1"> Commit inserts</label>
      <button class="btn">Process</button>
    </div>
    <div class="card">
      <div class="muted">Or paste data here (comma or tab separated):</div>
      <textarea name="paste" placeholder="7/1/2025,GP,RAJU SURYAVANSHI 13-16,6,2,13,6
7/1/2025,Balaton,RAVII 17-20,24,8,20,39"></textarea>
      <div style="margin-top:8px"><button class="btn">Process</button></div>
    </div>
  </form>
</div>
</body>
</html>
<?php
/* ====================== FUNCTIONS ======================= */
function parse_csv_text(string $text): array {
  $text = trim(preg_replace("/\r\n|\r/u", "\n", $text));
  $lines = explode("\n", $text);
  $rows  = [];
  foreach ($lines as $line) {
    $row = str_getcsv($line);
    if (count($row)===1 && strpos($line,"\t")!==false) $row = preg_split("/\t/",$line);
    $row = array_map(fn($v)=>trim((string)$v), $row);
    for ($i=count($row)-1;$i>=0;$i--) { if ($row[$i] !== '') break; unset($row[$i]); }
    if ($row) $rows[] = array_values($row);
  }
  // header detection
  $first = $rows[0] ?? [];
  $hasHeader = false;
  $keys = ['date','quality','karigar','meter','taka','mc','machine','total'];
  foreach ($first as $c) {
    $c2 = strtolower(preg_replace('/[^a-z0-9]+/i',' ', $c));
    foreach ($keys as $k) if (strpos($c2,$k)!==false){ $hasHeader=true; break 2; }
  }
  if ($hasHeader){ $headers=$first; array_shift($rows); }
  else { $headers=['Date','Quality','Karigar','Meter','Taka No.','Mc No.','Total Meter']; }
  return [$headers,$rows];
}
function resolve_indices(array $headers, array $ALIASES): array {
  $norm=[]; foreach($headers as $i=>$h){ $norm[$i]=strtolower(trim(preg_replace('/\s+|\.+/',' ',$h))); }
  $idx=[]; foreach($ALIASES as $key=>$cands){
    $idx[$key]=null;
    foreach($norm as $i=>$h){
      foreach($cands as $cand){
        if ($h === strtolower($cand)) { $idx[$key]=$i; break 2; }
      }
      if ($idx[$key]===null && strpos($h, strtolower($cands[0]))!==false) $idx[$key]=$i;
    }
  }
  return $idx;
}
function table_exists(PDO $pdo, string $name): bool {
  try{ $q=$pdo->prepare("SHOW TABLES LIKE ?"); $q->execute([$name]); return (bool)$q->fetchColumn(); }catch(Throwable $e){ return false; }
}
function cols_of(PDO $pdo, string $table): array {
  $out=[]; try{ foreach($pdo->query("SHOW COLUMNS FROM `$table`") as $r){ $out[$r['Field']]=true; } }catch(Throwable $e){}
  return $out;
}

/* ---- Masters: loom_karigar_master / loom_quality_master / khatas ---- */
function map_karigar_from_lkm(PDO $pdo, int $company_id): array {
  $tbl = 'loom_karigar_master';
  if (!table_exists($pdo,$tbl)) return [];
  $cols = cols_of($pdo,$tbl);
  // guess name column
  $nameCol = null;
  foreach (['name','karigar_name','weaver_name','worker_name','karigar'] as $c) if (isset($cols[$c])) { $nameCol=$c; break; }
  if (!$nameCol || !isset($cols['id'])) return [];
  $sql = isset($cols['company_id'])
       ? "SELECT id, `$nameCol` AS nm FROM `$tbl` WHERE company_id=?"
       : "SELECT id, `$nameCol` AS nm FROM `$tbl`";
  $st = isset($cols['company_id']) ? $pdo->prepare($sql) : $pdo->query($sql);
  if (isset($cols['company_id'])) $st->execute([$company_id]);
  $map=[];
  foreach (($st instanceof PDOStatement ? $st->fetchAll(PDO::FETCH_ASSOC) : []) as $r) {
    $map[normalize_name($r['nm'])] = (int)$r['id'];
  }
  return $map;
}
function map_quality_from_lqm(PDO $pdo, int $company_id): array {
  $tbl = 'loom_quality_master';
  if (!table_exists($pdo,$tbl)) return [];
  $cols = cols_of($pdo,$tbl);
  $nameCol = null;
  foreach (['name','quality','quality_name','title'] as $c) if (isset($cols[$c])) { $nameCol=$c; break; }
  if (!$nameCol || !isset($cols['id'])) return [];
  $sql = isset($cols['company_id'])
       ? "SELECT id, `$nameCol` AS nm FROM `$tbl` WHERE company_id=?"
       : "SELECT id, `$nameCol` AS nm FROM `$tbl`";
  $st = isset($cols['company_id']) ? $pdo->prepare($sql) : $pdo->query($sql);
  if (isset($cols['company_id'])) $st->execute([$company_id]);
  $map=[];
  foreach (($st instanceof PDOStatement ? $st->fetchAll(PDO::FETCH_ASSOC) : []) as $r) {
    $map[strtoupper(trim($r['nm']))] = (int)$r['id'];
  }
  return $map;
}
function map_machines_optional(PDO $pdo, int $company_id): array {
  // optional; if machines table not present, return empty -> fallback to mc_no
  $cands = ['machines','machine','looms'];
  foreach ($cands as $tbl) {
    if (!table_exists($pdo,$tbl)) continue;
    $cols = cols_of($pdo,$tbl);
    if (!isset($cols['id'])) continue;
    $noCol = null;
    foreach (['machine_no','loom_no','mc_no','no','number'] as $c) if (isset($cols[$c])) { $noCol=$c; break; }
    if (!$noCol) continue;
    $sql = isset($cols['company_id'])
         ? "SELECT id, `$noCol` AS no FROM `$tbl` WHERE company_id=?"
         : "SELECT id, `$noCol` AS no FROM `$tbl`";
    $st = isset($cols['company_id']) ? $pdo->prepare($sql) : $pdo->query($sql);
    if (isset($cols['company_id'])) $st->execute([$company_id]);
    $map=[];
    foreach (($st instanceof PDOStatement ? $st->fetchAll(PDO::FETCH_ASSOC) : []) as $r) {
      $key = normalize_mc_no($r['no']);
      if ($key!=='') $map[$key] = (int)$r['id'];
    }
    if ($map) return $map;
  }
  return [];
}
function load_khatas(PDO $pdo, int $company_id): array {
  $tbl = 'khatas';
  if (!table_exists($pdo,$tbl)) return [];
  $cols = cols_of($pdo,$tbl);
  foreach (['id','company_id','machine_from','machine_to'] as $c) if (!isset($cols[$c])) return [];
  $sql = "SELECT id, machine_from, machine_to FROM `$tbl` WHERE company_id=? AND (is_active=1 OR is_active IS NULL) ORDER BY id";
  $st = $pdo->prepare($sql); $st->execute([$company_id]);
  return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}
function pick_khata_id(array $khatas, int $mc_no): int {
  foreach ($khatas as $k) {
    $from = (int)$k['machine_from']; $to = (int)$k['machine_to'];
    if ($mc_no >= $from && $mc_no <= $to) return (int)$k['id'];
  }
  // fallback: first khata if any
  return isset($khatas[0]['id']) ? (int)$khatas[0]['id'] : 1;
}

/* ---- Build records ---- */
function build_records(
  array $rows, array $col,
  array $karigarMap, array $qualityMap, array $machineMap, array $khatas,
  array $defs, bool $respectTotal, bool $strict
): array {
  $groups = []; $unresolved=['karigar'=>[],'machine'=>[],'quality'=>[]];

foreach ($rows as $r) {
    $dateIso = normalize_date(cell($r, $col['date']));
    $qualLbl = strtoupper(trim((string)cell($r, $col['quality'])));
    $nameLbl = (string)cell($r, $col['karigar']);
    $meterRaw= (string)cell($r, $col['meter']);
    $takaRaw = (string)cell($r, $col['taka_no']);
    $mcLbl   = normalize_mc_no(cell($r, $col['mc_no']));
    $totRaw  = (string)cell($r, $col['total_meter']);

$karigar_id = ($nameLbl!=='') ? ($karigarMap[normalize_name($nameLbl)] ?? null) : null;
    $quality_id = ($qualLbl!=='') ? ($qualityMap[$qualLbl] ?? null) : null;

// machine: map via table else use mc number
    $mc_no = $mcLbl !== '' ? (int)$mcLbl : null;
    $machine_id = null;
    if ($mc_no !== null) {
      $machine_id = $machineMap ? ($machineMap[(string)$mc_no] ?? null) : $mc_no;
    }

if ($karigar_id===null && $nameLbl!=='') $unresolved['karigar'][normalize_name($nameLbl)]=true;
    if ($quality_id===null && $qualLbl!=='') $unresolved['quality'][$qualLbl]=true;
    if ($machine_id===null && $mcLbl!=='')   $unresolved['machine'][$mcLbl]=true;

$meter = is_numeric($meterRaw) ? (float)$meterRaw : null;
    $taka_no = is_numeric($takaRaw) ? (int)$takaRaw : null;
    $total_meter = is_numeric($totRaw) ? (float)$totRaw : null;

if ($strict && (!$dateIso || !$machine_id || !$quality_id || !$taka_no || !$karigar_id || $meter===null)) {
      continue;
    }

$khata_id = ($mc_no !== null) ? pick_khata_id($khatas, $mc_no) : 1;

$key = implode('|', [$dateIso,$machine_id,$quality_id,$taka_no]);
    if (!isset($groups[$key])) {
      $groups[$key] = [
        'entry_date' => $dateIso,
        'machine_id' => $machine_id,
        'khata_id'   => $khata_id,
        'quality_id' => $quality_id,
        'taka_no'    => $taka_no,
        'pbn'        => (int)$defs['pbn'],
        'sbn'        => (int)$defs['sbn'],
        'extra_mtr'  => (float)$defs['extra_mtr'],
        'weight'     => (float)$defs['weight'],
        'remark'     => (string)$defs['remark'],
        'lines'      => [],
        'sum_meter'  => 0.0,
        'sheet_total'=> $total_meter,
      ];
    }
    if ($karigar_id && $meter!==null) {
      $groups[$key]['lines'][] = ['karigar_id'=>$karigar_id, 'meter'=>$meter, 'date'=>$dateIso];
      $groups[$key]['sum_meter'] += $meter;
    }
  }

$records = [];
  foreach ($groups as $g) {
    $meter_total = $g['sum_meter'];
    if ($respectTotal && $g['sheet_total'] !== null && $g['sheet_total'] > 0) {
      $meter_total = (float)$g['sheet_total'];
    }
    $records[] = [
      'entry_date' => $g['entry_date'],
      'machine_id' => (int)$g['machine_id'],
      'khata_id'   => (int)$g['khata_id'],
      'quality_id' => (int)$g['quality_id'],
      'taka_no'    => (int)$g['taka_no'],
      'pbn'        => (int)$g['pbn'],
      'sbn'        => (int)$g['sbn'],
      'extra_mtr'  => (float)$g['extra_mtr'],
      'lines_json' => json_encode($g['lines'], JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES),
      'meter_total'=> (float)$meter_total,
      'weight'     => (float)$g['weight'],
      'remark'     => (string)$g['remark'],
    ];
  }

foreach ($unresolved as $k=>$m) $unresolved[$k] = array_keys($m);
  return [$records, $unresolved];
}

/* ---- DB ops ---- */
function find_existing(PDO $pdo, int $company_id, array $r){
  $sql="SELECT id FROM loom_production_entry
        WHERE company_id=? AND entry_date=? AND machine_id=? AND quality_id=? AND taka_no=?
        LIMIT 1";
  $st=$pdo->prepare($sql);
  $st->execute([$company_id,$r['entry_date'],$r['machine_id'],$r['quality_id'],$r['taka_no']]);
  return (int)$st->fetchColumn() ?: 0;
}
function insert_row(PDO $pdo, int $company_id, int $created_by, array $r): bool {
  $sql="INSERT INTO loom_production_entry
        (company_id, entry_date, machine_id, khata_id, quality_id, taka_no, pbn, sbn, extra_mtr, lines_json, meter_total, weight, remark, created_by)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  $st=$pdo->prepare($sql);
  return $st->execute([
    $company_id, $r['entry_date'], $r['machine_id'], $r['khata_id'], $r['quality_id'], $r['taka_no'],
    $r['pbn'], $r['sbn'], $r['extra_mtr'], $r['lines_json'], $r['meter_total'], $r['weight'], $r['remark'], $created_by
  ]);
}
function update_row(PDO $pdo, int $id, array $r): bool {
  $sql="UPDATE loom_production_entry
        SET lines_json=?, meter_total=?, extra_mtr=?, weight=?, remark=?
        WHERE id=?";
  $st=$pdo->prepare($sql);
  return $st->execute([$r['lines_json'],$r['meter_total'],$r['extra_mtr'],$r['weight'],$r['remark'],$id]);
}

/* ---- helpers ---- */
function normalize_date(?string $s): ?string {
  $s = trim((string)$s); if ($s==='') return null;
  $s = str_replace(['.',' '], ['/','/'], $s);
  $try=['n/j/Y','m/d/Y','n-j-Y','m-d-Y','Y-m-d','d/m/Y','d-m-Y'];
  foreach($try as $fmt){ $dt=DateTime::createFromFormat($fmt,$s); if($dt) return $dt->format('Y-m-d'); }
  $ts=strtotime($s); return $ts?date('Y-m-d',$ts):null;
}
function normalize_name(string $label): string {
  $label = trim(preg_replace('/\s+/',' ', strtoupper($label)));
  if ($label==='') return '';
  if (preg_match('/^(.+?)\s+\d{1,3}\s*-\s*\d{1,3}$/u',$label,$m))
    return trim($m[1]);     // strip trailing "13-16"
  return $label;
}
function normalize_mc_no($v): string { return preg_replace('/\D+/','', (string)$v); }
function cell(array $row, $i){ return ($i===null || !isset($row[$i])) ? null : trim((string)$row[$i]); }
function rows_to_csv_string(array $rows): string {
  $f = fopen('php://temp','r+');
  foreach ($rows as $r) { fputcsv($f,$r); }
  rewind($f); $csv=stream_get_contents($f); fclose($f);
  return $csv;
}
?>