View Code - 20260722

loom_flat_prepare.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ============================================================================
   DEBUG PREPARE: fill IDs in loom_production_flat + assign taka_no_new
   - Verbose output + try/catch around each step to reveal 500 errors
   - Uses entry_name for karigar mapping
   - No ALTER TABLEs here (schema assumed already in place)
   ========================================================================== */
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'];

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

try {
  $pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
  $pdo->exec("SET SESSION collation_connection='utf8mb4_unicode_ci'");
  echo "OK: session collation set\n";
} catch (Throwable $e) { echo "COLLATION ERR: ".$e->getMessage()."\n"; }

function count_q(PDO $pdo, $sql) {
  $c = $pdo->query($sql)->fetchColumn();
  return (int)$c;
}

/* Quick sanity of required columns */
try {
  $cols = [];
  $rs = $pdo->query("SHOW COLUMNS FROM loom_production_flat");
  foreach ($rs as $r) { $cols[] = $r['Field']; }
  $need = ['quality_id','karigar_id','khata_id','khata_code','machine_id','taka_no_new','karigar_name','quality_name','machine_no','taka_no','entry_date'];
  $miss = array_diff($need, $cols);
  if ($miss) { echo "MISSING COLUMNS in loom_production_flat: ".implode(', ', $miss)."\n"; }
  else { echo "OK: flat has required columns\n"; }
} catch (Throwable $e) { echo "CHECK COLS ERR: ".$e->getMessage()."\n"; }

/* Masters quick counts */
try {
  $kar_cnt = count_q($pdo, "SELECT COUNT(*) FROM loom_karigar_master WHERE company_id=$CID");
  $qlt_cnt = count_q($pdo, "SELECT COUNT(*) FROM qualities WHERE company_id=$CID");
  $kha_cnt = count_q($pdo, "SELECT COUNT(*) FROM khatas WHERE company_id=$CID");
  echo "Masters: karigar=$kar_cnt, qualities=$qlt_cnt, khatas=$kha_cnt\n";
} catch (Throwable $e) { echo "MASTER COUNT ERR: ".$e->getMessage()."\n"; }

/* 1) quality_id from qualities */
try {
  $sql = "
    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 = $pdo->prepare($sql); $st->execute([$CID]);
  echo "quality_id filled: affected rows=".$st->rowCount()."\n";
} catch (Throwable $e) { echo "QUALITY MAP ERR: ".$e->getMessage()."\n"; }

/* 2) karigar_id via entry_name */
try {
  // Note: ensure entry_name exists
  $hasEntry = $pdo->query("SHOW COLUMNS FROM loom_karigar_master LIKE 'entry_name'")->fetch();
  echo "karigar_master.entry_name: ".($hasEntry?'OK':'MISSING')."\n";

$sql = "
    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 = $pdo->prepare($sql); $st->execute([$CID]);
  echo "karigar_id filled (via entry_name): affected rows=".$st->rowCount()."\n";
} catch (Throwable $e) { echo "KARIGAR MAP ERR: ".$e->getMessage()."\n"; }

/* 3) khata via machine range (fallback) */
try {
  $sql = "
    UPDATE loom_production_flat f
    JOIN khatas kh
      ON kh.company_id=f.company_id AND kh.is_active=1
     AND f.machine_no BETWEEN kh.machine_from AND kh.machine_to
    SET f.khata_id=kh.id, f.khata_code=kh.code
    WHERE f.company_id=? AND (f.khata_id IS NULL OR f.khata_id=0)
  ";
  $st = $pdo->prepare($sql); $st->execute([$CID]);
  echo "khata_id filled by machine range: affected rows=".$st->rowCount()."\n";
} catch (Throwable $e) { echo "KHATA MAP ERR: ".$e->getMessage()."\n"; }

/* 4) machine_id if machine master present (optional) */
try {
  $hasMachine = $pdo->query("SHOW TABLES LIKE 'company_machine_master'")->fetch();
  if ($hasMachine) {
    $sql = "
      UPDATE loom_production_flat f
      JOIN company_machine_master m
        ON m.company_id=f.company_id AND m.loom_no=f.machine_no
      SET f.machine_id = m.id
      WHERE f.company_id=? AND (f.machine_id IS NULL OR f.machine_id=0)
    ";
    $st = $pdo->prepare($sql); $st->execute([$CID]);
    echo "machine_id filled: affected rows=".$st->rowCount()."\n";
  } else {
    echo "machine master not found (skipped)\n";
  }
} catch (Throwable $e) { echo "MACHINE MAP ERR: ".$e->getMessage()."\n"; }

/* 5) Assign taka_no_new (khata-wise) */
try {
  $last = $pdo->prepare("
    SELECT khata_id, COALESCE(MAX(taka_no),0) AS last_no
    FROM loom_production_entry
    WHERE company_id=?
    GROUP BY khata_id
  ");
  $last->execute([$CID]);
  $lastMap = [];
  while ($r = $last->fetch(PDO::FETCH_ASSOC)) { $lastMap[(int)$r['khata_id']] = (int)$r['last_no']; }
  echo "Fetched last_no per khata: ".count($lastMap)." khatas\n";

$q = $pdo->prepare("
    SELECT id, khata_id
    FROM loom_production_flat
    WHERE company_id=? AND taka_no_new IS NULL
      AND khata_id IS NOT NULL AND khata_id<>0
    ORDER BY entry_date, machine_no, taka_no, id
  ");
  $q->execute([$CID]);

$ctr = []; $upd = $pdo->prepare("UPDATE loom_production_flat SET taka_no_new=? WHERE id=?");
  $assigned = 0;
  while ($r = $q->fetch(PDO::FETCH_ASSOC)) {
    $k = (int)$r['khata_id']; if (!$k) continue;
    if (!isset($ctr[$k])) $ctr[$k] = 0;
    $ctr[$k]++;
    $newNo = ($lastMap[$k] ?? 0) + $ctr[$k];
    $upd->execute([$newNo, $r['id']]);
    $assigned++;
  }
  echo "taka_no_new assigned rows: {$assigned}\n";
} catch (Throwable $e) { echo "ASSIGN TAKA ERR: ".$e->getMessage()."\n"; }

/* 6) Final missing counts */
try {
  $missKar = count_q($pdo, "SELECT COUNT(*) FROM loom_production_flat WHERE company_id=$CID AND (karigar_id IS NULL OR karigar_id=0)");
  $missQlt = count_q($pdo, "SELECT COUNT(*) FROM loom_production_flat WHERE company_id=$CID AND (quality_id IS NULL OR quality_id=0)");
  $missKha = count_q($pdo, "SELECT COUNT(*) FROM loom_production_flat WHERE company_id=$CID AND (khata_id   IS NULL OR khata_id=0)");
  $missTak = count_q($pdo, "SELECT COUNT(*) FROM loom_production_flat WHERE company_id=$CID AND taka_no_new IS NULL");
  echo "Missing => karigar:$missKar, quality:$missQlt, khata:$missKha, taka_no_new:$missTak\n";
} catch (Throwable $e) { echo "FINAL COUNT ERR: ".$e->getMessage()."\n"; }

echo "Done.\n";