View Code - 20260721

loom_taka_migrate.php | 20260721_154033.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/tools/loom_taka_migrate.php
   Purpose: Create/Heal target table for per-taka JSON with unique taka_label
   Scope : Page-local only. NO global/base edits.
============================================================================= */
error_reporting(E_ALL); ini_set('display_errors', 1);

/* --- DB + Auth (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'];

/* --- Helpers ------------------------------------------------------------- */
function column_exists(PDO $pdo, $table, $col) {
  $stmt = $pdo->prepare("SELECT COUNT(*) c FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?");
  $stmt->execute([$table, $col]);
  return (int)$stmt->fetchColumn() > 0;
}
function index_exists(PDO $pdo, $table, $index) {
  $stmt = $pdo->prepare("SHOW INDEX FROM `$table` WHERE Key_name = ?");
  $stmt->execute([$index]);
  return (bool)$stmt->fetch(PDO::FETCH_ASSOC);
}

$messages = [];

/* --- Step 1: Create table if not exists --------------------------------- */
$messages[] = "Ensuring table loom_taka_entry exists…";
$pdo->exec("
CREATE TABLE IF NOT EXISTS loom_taka_entry (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id    BIGINT UNSIGNED NOT NULL,
  entry_date    DATE NOT NULL,
  machine_no    INT NOT NULL,
  quality       VARCHAR(100) NOT NULL,
  pbn           VARCHAR(50) NULL,
  sbn           VARCHAR(50) NULL,

-- numeric taka (for legacy/back-compat) + original numeric ref
  taka_no       INT NULL,
  orig_taka_no  INT NULL,

-- clash-free human label like 1, a1, b1, aa1...
  taka_label    VARCHAR(32) NOT NULL,

total_meter   INT NOT NULL,
  weight        DECIMAL(10,2) NULL,
  remark        VARCHAR(255) NULL,
  lines_json    JSON NOT NULL,

created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

/* --- Step 2: Add columns if missing ------------------------------------- */
$need = [
  ['orig_taka_no','INT NULL'],
  ['taka_label','VARCHAR(32) NOT NULL'],
  ['taka_no','INT NULL'],          // keep numeric field for back-compat
  ['lines_json','JSON NOT NULL'],
];
foreach ($need as [$c,$def]) {
  if (!column_exists($pdo, 'loom_taka_entry', $c)) {
    $pdo->exec("ALTER TABLE loom_taka_entry ADD COLUMN $c $def");
    $messages[] = "Added column: $c";
  }
}

/* --- Step 3: Backfill taka_label / orig_taka_no from old rows ------------ */
$messages[] = "Backfilling taka_label and orig_taka_no where NULL…";
$pdo->exec("
  UPDATE loom_taka_entry
  SET orig_taka_no = IFNULL(orig_taka_no, taka_no),
      taka_label   = IFNULL(taka_label, CAST(taka_no AS CHAR))
  WHERE taka_label IS NULL OR orig_taka_no IS NULL
");

/* --- Step 4: Fix unique index (on label) -------------------------------- */
if (index_exists($pdo,'loom_taka_entry','uniq_company_taka')) {
  $pdo->exec("ALTER TABLE loom_taka_entry DROP INDEX uniq_company_taka");
  $messages[] = "Dropped old unique index uniq_company_taka";
}
if (!index_exists($pdo,'loom_taka_entry','uniq_company_taka_label')) {
  $pdo->exec("ALTER TABLE loom_taka_entry ADD UNIQUE KEY uniq_company_taka_label (company_id, entry_date, machine_no, taka_label)");
  $messages[] = "Added unique index uniq_company_taka_label(company_id, entry_date, machine_no, taka_label)";
}

?>
<!doctype html>
<html>
<head>
  <meta charset="utf-8">
  <title>loom_taka_entry — Migration</title>
  <style>
    body{font-family:system-ui,Segoe UI,Roboto,Arial;padding:24px;background:#0b0f12;color:#e8e8e8}
    .card{background:#12171c;border:1px solid #2a3137;border-radius:14px;padding:18px;max-width:880px}
    h1{margin:0 0 12px;font-size:20px}
    .ok{color:#8ef19a} .muted{color:#9aa4af}
    code,pre{background:#0d1117;border:1px solid #2a3137;padding:8px 10px;border-radius:10px}
    a.btn{display:inline-block;margin-top:14px;padding:10px 14px;border-radius:10px;border:1px solid #2a3137;text-decoration:none;color:#e8e8e8}
    a.btn:hover{background:#1a2128}
  </style>
</head>
<body>
  <div class="card">
    <h1>Migration: <code>loom_taka_entry</code></h1>
    <ul>
      <?php foreach($messages as $m){ echo "<li class='ok'>".$m."</li>"; } ?>
    </ul>
    <p class="muted">Company scope is handled at write time; this migration just prepares the table.</p>
    <a class="btn" href="/erp/tools/loom_taka_aggregate.php">Go to Aggregator</a>
  </div>
</body>
</html>