View Code - 20260721

loom_flat_autofill_masters.php | 20260721_154033.php Initial Bulk Import

<?php
/* ============================================================================
   Auto-fill missing masters from staging, then remap IDs in flat.
   - Adds new rows to `qualities` and `loom_karigar_master` when needed
   - Uses entry_name matching for karigar
   - Prints verbose counts
   ========================================================================== */
header('Content-Type: text/plain; charset=utf-8');
error_reporting(E_ALL); ini_set('display_errors', '1');

require __DIR__.'/../core/db.php';
require __DIR__.'/../modules/auth/auth.php';
require_login();
$u   = auth_user();
$CID = (int)$u['company_id'];

$pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
$pdo->exec("SET SESSION collation_connection='utf8mb4_unicode_ci'");

echo "Company: {$CID}\n";

/* ---------- 0) helpers ---------- */
function cnt(PDO $pdo, $sql){ return (int)$pdo->query($sql)->fetchColumn(); }

/* ---------- 1) Insert missing qualities ---------- */
// distinct quality names in flat that have NULL quality_id
$sqlMissingQualities = "
  SELECT DISTINCT TRIM(f.quality_name) AS qn
  FROM loom_production_flat f
  LEFT JOIN qualities q
    ON q.company_id=f.company_id
   AND LOWER(TRIM(q.quality_name)) COLLATE utf8mb4_unicode_ci =
       LOWER(TRIM(f.quality_name)) COLLATE utf8mb4_unicode_ci
  WHERE f.company_id={$CID}
    AND (f.quality_id IS NULL OR f.quality_id=0)
    AND (f.quality_name IS NOT NULL AND f.quality_name <> '')
    AND q.id IS NULL
";
$names = $pdo->query($sqlMissingQualities)->fetchAll(PDO::FETCH_COLUMN);
echo "Missing qualities to create: ".count($names)."\n";

if ($names) {
  $insQ = $pdo->prepare("
    INSERT INTO qualities (company_id, code, quality_name, is_active)
    VALUES (:cid, NULL, :name, 1)
  ");
  foreach ($names as $name) {
    try {
      $insQ->execute([':cid'=>$CID, ':name'=>$name]);
      echo "  + quality added: {$name}\n";
    } catch (Throwable $e) {
      echo "  ! skip quality '{$name}': ".$e->getMessage()."\n";
    }
  }
}

/* Remap quality_id */
$st = $pdo->prepare("
  UPDATE loom_production_flat f
  JOIN qualities q
    ON q.company_id=f.company_id
   AND LOWER(TRIM(q.quality_name)) COLLATE utf8mb4_unicode_ci =
       LOWER(TRIM(f.quality_name)) COLLATE utf8mb4_unicode_ci
  SET f.quality_id = q.id
  WHERE f.company_id=? AND (f.quality_id IS NULL OR f.quality_id=0)
");
$st->execute([$CID]);
echo "quality_id filled: affected rows=".$st->rowCount()."\n";

/* ---------- 2) Insert missing karigars (using entry_name) ---------- */
$sqlMissingKarigar = "
  SELECT
    TRIM(f.karigar_name) AS ename,
    MIN(f.machine_no) AS mfrom,
    MAX(f.machine_no) AS mto,
    MAX(f.khata_id)   AS khid,          -- prefer khata from flat (already filled)
    MAX(f.khata_code) AS khcode
  FROM loom_production_flat f
  LEFT JOIN loom_karigar_master k
    ON k.company_id=f.company_id
   AND k.is_active=1
   AND LOWER(TRIM(k.entry_name)) COLLATE utf8mb4_unicode_ci =
       LOWER(TRIM(f.karigar_name)) COLLATE utf8mb4_unicode_ci
  WHERE f.company_id={$CID}
    AND (f.karigar_id IS NULL OR f.karigar_id=0)
    AND (f.karigar_name IS NOT NULL AND f.karigar_name <> '')
    AND k.id IS NULL
  GROUP BY TRIM(f.karigar_name)
";
$rows = $pdo->query($sqlMissingKarigar)->fetchAll(PDO::FETCH_ASSOC);
echo "Missing karigars to create: ".count($rows)."\n";

/* if khata still null in group, try range match on a sample machine */
$getKhataByMachine = $pdo->prepare("
  SELECT id, code FROM khatas
  WHERE company_id=? AND is_active=1
    AND ? BETWEEN machine_from AND machine_to
  LIMIT 1
");

$insK = $pdo->prepare("
  INSERT INTO loom_karigar_master
    (company_id, khata_id, khata_code, karigar_name, entry_name,
     machine_from, machine_to, department_id, role_id, is_active, created_at)
  VALUES
    (:cid, :khid, :khcode, :kname, :entry, :mfrom, :mto, 19, 12, 1, NOW())
");

foreach ($rows as $r) {
  $ename = $r['ename'];
  $mfrom = (int)$r['mfrom'];
  $mto   = (int)($r['mto'] ?: $mfrom);
  $khid  = (int)$r['khid'];
  $khcode= $r['khcode'];

if (!$khid) {
    // best-effort khata via machine range
    $getKhataByMachine->execute([$CID, $mfrom ?: $mto]);
    if ($k = $getKhataByMachine->fetch(PDO::FETCH_ASSOC)) {
      $khid = (int)$k['id'];
      $khcode = $k['code'];
    }
  }

try {
    $insK->execute([
      ':cid'=>$CID,
      ':khid'=>($khid ?: null),
      ':khcode'=>$khcode,
      ':kname'=>$ename,     // base name (without range if any)
      ':entry'=>$ename,     // entry_name EXACTLY as seen in flat
      ':mfrom'=>($mfrom ?: null),
      ':mto'  =>($mto ?: null),
    ]);
    echo "  + karigar added: {$ename}\n";
  } catch (Throwable $e) {
    echo "  ! skip karigar '{$ename}': ".$e->getMessage()."\n";
  }
}

/* Remap karigar_id via entry_name */
$st = $pdo->prepare("
  UPDATE loom_production_flat f
  JOIN loom_karigar_master k
    ON k.company_id=f.company_id AND k.is_active=1
   AND LOWER(TRIM(k.entry_name)) COLLATE utf8mb4_unicode_ci =
       LOWER(TRIM(f.karigar_name)) COLLATE utf8mb4_unicode_ci
  SET f.karigar_id = k.id,
      f.khata_id   = COALESCE(f.khata_id, k.khata_id),
      f.khata_code = COALESCE(f.khata_code, k.khata_code)
  WHERE f.company_id=? AND (f.karigar_id IS NULL OR f.karigar_id=0)
");
$st->execute([$CID]);
echo "karigar_id filled: affected rows=".$st->rowCount()."\n";

/* ---------- 3) Final missing counts ---------- */
$missKar = cnt($pdo,"SELECT COUNT(*) FROM loom_production_flat WHERE company_id={$CID} AND (karigar_id IS NULL OR karigar_id=0)");
$missQlt = cnt($pdo,"SELECT COUNT(*) FROM loom_production_flat WHERE company_id={$CID} AND (quality_id IS NULL OR quality_id=0)");
$missKha = cnt($pdo,"SELECT COUNT(*) FROM loom_production_flat WHERE company_id={$CID} AND (khata_id   IS NULL OR khata_id=0)");
$missTak = cnt($pdo,"SELECT COUNT(*) FROM loom_production_flat WHERE company_id={$CID} AND taka_no_new IS NULL");

echo "\nAfter autofill:\n";
echo "  Missing karigar_id : {$missKar}\n";
echo "  Missing quality_id : {$missQlt}\n";
echo "  Missing khata_id   : {$missKha}\n";
echo "  Missing taka_no_new: {$missTak}\n";

echo "\nDone.\n";