View Code - 20260722

karigar_json_import.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ============================================================
   File: /erp/tools/karigar_json_import.php
   Purpose: Sheet/CSV/XLSX -> JSON [ {karigar_id, meter, date, ...} ]
   Scope : Local-only, no global edits. Company-scoped lookups.
   ============================================================ */
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);
if (!isset($pdo) || !($pdo instanceof PDO)) { require __DIR__ . '/../core/db.php'; }

/* ------------ CONFIG (tweak here only) ------------------- */
$STRICT_ONLY_VALID_ROWS   = true;   // id & meter missing हों तो row skip
$INCLUDE_EXTRA_FIELDS     = false;  // true करें तो quality, taka_no, machine_no, total_meter भी JSON में आएंगे
$COLUMN_ALIASES = [                // header detection (case-insensitive)
  'date'         => ['date', 'dt'],
  'quality'      => ['quality', 'fabric', 'qual'],
  'karigar'      => ['karigar', 'worker', 'name', 'karigar name'],
  'meter'        => ['meter', 'mtr', 'mtrs', 'total meter'],  // fallback
  'taka_no'      => ['taka no', 'taka no.', 'taka'],
  'machine_no'   => ['mc no', 'machine', 'machine no', 'loom', 'loom no'],
  'total_meter'  => ['total meter', 'sum meter', 'meter total'],
];

/* ------------ PAGE (upload + paste) ---------------------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  try {
    $raw = '';
    $filename = '';

// 1) File upload path
    if (!empty($_FILES['datafile']['tmp_name']) && $_FILES['datafile']['error'] === UPLOAD_ERR_OK) {
      $tmp  = $_FILES['datafile']['tmp_name'];
      $name = $_FILES['datafile']['name'];
      $filename = $name;
      $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));

if ($ext === 'csv' || $ext === 'tsv') {
        $raw = file_get_contents($tmp);
        if ($raw === false) throw new Exception('Unable to read uploaded file.');
        if ($ext === 'tsv') {
          // normalize TSV to CSV-ish by replacing tabs with commas just for text split fallback
          $raw = str_replace("\t", ",", $raw);
        }
      } elseif ($ext === 'xlsx') {
        // Optional XLSX support via PhpSpreadsheet (if installed)
        if (class_exists('\\PhpOffice\\PhpSpreadsheet\\IOFactory')) {
          $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
          $reader->setReadDataOnly(true);
          $spreadsheet = $reader->load($tmp);
          $sheet = $spreadsheet->getActiveSheet();
          // Convert to CSV string
          $rows = [];
          foreach ($sheet->getRowIterator() as $row) {
            $cells = [];
            foreach ($row->getCellIterator() as $cell) {
              $cells[] = trim((string)$cell->getCalculatedValue());
            }
            // trim trailing empty cells
            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('XLSX support not available. Please upload CSV or install PhpSpreadsheet.');
        }
      } else {
        throw new Exception('Please upload CSV/TSV/XLSX only.');
      }
    }

// 2) Paste input fallback
    if (!$raw) {
      $raw = trim((string)($_POST['paste'] ?? ''));
      if ($raw === '') throw new Exception('No data provided.');
      // Try to detect tabs and convert to CSV-ish
      if (strpos($raw, "\t") !== false) $raw = str_replace("\t", ",", $raw);
    }

// 3) Parse CSV-ish text -> rows[]
    [$headers, $rows] = parse_csv_text($raw);

// 4) Resolve column indices from header names
    $colIndex = resolve_column_indices($headers, $COLUMN_ALIASES);

// 5) Load karigar name->id map from DB (best-effort)
    $karigarMap = load_karigar_map($pdo, $company_id); // e.g. ['RAVII'=>68, 'PATIL'=>71]
    // If empty, you can hardcode: $karigarMap = ['RAVII'=>68,'PATIL'=>71];

// 6) Convert to JSON rows
    $jsonRows = convert_rows_to_json($rows, $colIndex, $karigarMap, $STRICT_ONLY_VALID_ROWS, $INCLUDE_EXTRA_FIELDS);

// 7) Output JSON
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($jsonRows, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
    exit;

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

/* ---------- UI (simple form) ---------- */
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Karigar JSON Import</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  body{font-family:system-ui,-apple-system,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}
  code{background:#f3f3f3;padding:2px 6px;border-radius:6px}
</style>
</head>
<body>
<div class="wrap">
  <h2>Sheet/CSV → JSON (Karigar, Meter, Date)</h2>
  <div class="muted">Upload CSV/XLSX <b>or</b> paste rows (Excel se copy). Output JSON download/preview ho jayega.</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">
      <button class="btn">Convert</button>
    </div>
    <div class="card">
      <div class="muted">YA phir yahan paste karein (columns: Date, Quality, Karigar, Meter, Taka No., Mc No., Total Meter)</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">Convert</button></div>
    </div>
    <div class="muted" style="margin-top:10px">
      JSON default: <code>[{"karigar_id":68,"meter":8,"date":"2025-08-01"}]</code>.
      Extra fields include karne ho to file top par <code>$INCLUDE_EXTRA_FIELDS = true</code> कर दें.
    </div>
  </form>
</div>
</body>
</html>
<?php
/* ===================== Functions ====================== */

/** Parse CSV text to [headers[], rows[][]] (auto header detect) */
function parse_csv_text(string $text): array {
  $text = trim($text);
  // normalize newlines
  $text = preg_replace("/\r\n|\r/u", "\n", $text);

$lines = explode("\n", $text);
  $rows  = [];
  foreach ($lines as $line) {
    // split by comma, but allow quoted fields
    $row = str_getcsv($line);
    // fallback: if single col but tabs present
    if (count($row) === 1 && strpos($line, "\t") !== false) {
      $row = preg_split("/\t/", $line);
    }
    // trim cells
    $row = array_map(fn($v)=>trim((string)$v), $row);
    // drop trailing empties
    for ($i=count($row)-1; $i>=0; $i--) { if ($row[$i] !== '') break; unset($row[$i]); }
    $rows[] = array_values($row);
  }
  // detect header: if first row has any known alias words
  $first = $rows[0] ?? [];
  $hasHeader = false;
  $headerKeywords = ['date','quality','karigar','meter','taka','mc','machine','total'];
  foreach ($first as $cell) {
    $c = strtolower(preg_replace('/[^a-z0-9]+/i',' ', $cell));
    foreach ($headerKeywords as $kw) {
      if (strpos($c, $kw) !== false) { $hasHeader = true; break 2; }
    }
  }
  if ($hasHeader) {
    $headers = $first;
    array_shift($rows);
  } else {
    // synthesize headers with common order for your sheet
    $headers = ['Date','Quality','Karigar','Meter','Taka No.','Mc No.','Total Meter'];
  }
  return [$headers, $rows];
}

/** Resolve column indices from headers via aliases map (case-insensitive) */
function resolve_column_indices(array $headers, array $ALIASES): array {
  $idx = [];
  $norm = [];
  foreach ($headers as $i=>$h) {
    $norm[$i] = strtolower(trim(preg_replace('/\s+|\.+/',' ', $h)));
  }
  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; }
      }
      // loose contains match (e.g., "taka no")
      if ($idx[$key]===null && strpos($h, strtolower($cands[0])) !== false) { $idx[$key] = $i; }
    }
  }
  return $idx;
}

/** Load karigar name -> id map from DB (best effort) */
function load_karigar_map(PDO $pdo, int $company_id): array {
  $map = [];

// find a plausible table that has id + name (and maybe company_id)
  $candidates = ['karigars','karigar','workers','employees','loom_karigar','weavers'];
  foreach ($candidates as $tbl) {
    if (!table_exists($pdo, $tbl)) continue;
    $cols = cols_of($pdo, $tbl);
    if (!isset($cols['id']) || !isset($cols['name'])) continue;

try{
      if (isset($cols['company_id'])) {
        $st = $pdo->prepare("SELECT id, name FROM `$tbl` WHERE company_id=?");
        $st->execute([$company_id]);
      } else {
        $st = $pdo->query("SELECT id, name FROM `$tbl`");
      }
      foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $name = strtoupper(normalize_karigar_name((string)$r['name']));
        if ($name!=='') $map[$name] = (int)$r['id'];
      }
      if ($map) return $map;
    } catch(Throwable $e){}
  }

return $map; // might be empty; user can hardcode
}
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;
}

/** Convert parsed rows to JSON objects */
function convert_rows_to_json(array $rows, array $idx, array $idMap, bool $strict=true, bool $withExtras=false): array {
  $out = [];
  foreach ($rows as $r) {
    // read by indices (some may be null)
    $dateRaw   = col($r, $idx['date']);
    $qualRaw   = col($r, $idx['quality']);
    $nameRaw   = col($r, $idx['karigar']);
    $meterRaw  = col($r, $idx['meter']);
    $takaRaw   = col($r, $idx['taka_no']);
    $mcRaw     = col($r, $idx['machine_no']);
    $tmeterRaw = col($r, $idx['total_meter']);

$dateIso = normalize_date_to_iso($dateRaw);
    $nameNorm = normalize_karigar_name($nameRaw);
    $id = ($nameNorm!=='') ? ($idMap[strtoupper($nameNorm)] ?? null) : null;

$meter = null;
    if ($meterRaw !== null && $meterRaw !== '') {
      // allow decimal or integer
      $meter = is_numeric($meterRaw) ? (float)$meterRaw : null;
    } elseif ($tmeterRaw !== null && $tmeterRaw !== '') {
      $meter = is_numeric($tmeterRaw) ? (float)$tmeterRaw : null; // fallback
    }

if ($strict) {
      if ($id !== null && $meter !== null && $dateIso) {
        $obj = ['karigar_id'=>$id, 'meter'=>$meter, 'date'=>$dateIso];
        if ($withExtras) {
          if ($qualRaw!=='') $obj['quality'] = $qualRaw;
          if ($takaRaw!=='') $obj['taka_no'] = $takaRaw;
          if ($mcRaw!=='')   $obj['machine_no'] = $mcRaw;
          if ($tmeterRaw!=='') $obj['total_meter'] = (float)$tmeterRaw;
        }
        $out[] = $obj;
      }
    } else {
      $obj = ['karigar_id'=>$id, 'meter'=>$meter, 'date'=>$dateIso ?: null];
      if ($withExtras) {
        $obj['quality']     = $qualRaw;
        $obj['taka_no']     = $takaRaw;
        $obj['machine_no']  = $mcRaw;
        $obj['total_meter'] = is_numeric($tmeterRaw)?(float)$tmeterRaw:null;
      }
      if ($id===null)   $obj['karigar_label'] = $nameRaw;
      $out[] = $obj;
    }
  }
  return $out;
}
function col(array $row, $i) { return ($i===null || !isset($row[$i])) ? null : trim((string)$row[$i]); }

/** Date: 7/1/2025, 07/01/2025, 7-1-2025, or 2025-07-01 -> YYYY-MM-DD */
function normalize_date_to_iso(?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','j/n/Y','d/m/Y','j-n-Y','d-m-Y'
  ];
  foreach ($try as $fmt) {
    $dt = DateTime::createFromFormat($fmt, $s);
    if ($dt) return $dt->format('Y-m-d');
  }
  // last resort strtotime
  $ts = strtotime($s);
  return $ts ? date('Y-m-d', $ts) : null;
}

/** "RAVII 17-20" -> "RAVII"; keeps plain names as-is */
function normalize_karigar_name(string $label): string {
  $label = trim(preg_replace('/\s+/',' ', $label));
  if ($label==='') return '';
  // remove patterns like " 12-20" or " 9-12" at end
  if (preg_match('/^(.+?)\s+\d{1,3}\s*-\s*\d{1,3}$/u', $label, $m)) {
    return strtoupper(trim($m[1]));
  }
  return strtoupper($label);
}

/** Create CSV from rows[][] */
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;
}