« Back to History
loom_taka_aggregate.php
|
20260722_120325.php
Initial Domain Snapshot
Copy Code
<?php /* ============================================================================= File: /erp/tools/loom_taka_aggregate.php Purpose: Flat rows -> Per-Taka JSON with auto unique taka_label Source : loom_production_entry (flat import table) Target : loom_taka_entry (per-taka JSON table) 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']; /* --- UI Filters ---------------------------------------------------------- */ $from = isset($_GET['from']) ? trim($_GET['from']) : ''; $to = isset($_GET['to']) ? trim($_GET['to']) : ''; $do = isset($_GET['run']) ? '1' : '0'; /* --- Helpers: alpha sequence & label resolver --------------------------- */ /** '', null -> 'a', 'a'->'b', ... 'z'->'aa', 'aa'->'ab' ... */ function next_alpha($letters) { if ($letters === '' || $letters === null) return 'a'; $n = strlen($letters); $arr = str_split($letters); for ($i = $n - 1; $i >= 0; $i--) { if ($arr[$i] !== 'z') { $arr[$i] = chr(ord($arr[$i]) + 1); for ($j = $i + 1; $j < $n; $j++) $arr[$j] = 'a'; return implode('', $arr); } } return str_repeat('a', $n + 1); } /** Find next available taka_label for (company, date, machine, orig_taka_no) */ function compute_taka_label(PDO $pdo, int $company_id, string $entry_date, int $machine_no, int $orig_taka_no, array $batchAlreadyUsed = []) { $q = $pdo->prepare(" SELECT taka_label FROM loom_taka_entry WHERE company_id = :cid AND entry_date = :dt AND machine_no = :mc AND orig_taka_no = :orig "); $q->execute([':cid'=>$company_id, ':dt'=>$entry_date, ':mc'=>$machine_no, ':orig'=>$orig_taka_no]); $existing = array_column($q->fetchAll(PDO::FETCH_ASSOC), 'taka_label'); // Also avoid duplicates inside current run $existing = array_merge($existing, $batchAlreadyUsed); $base = (string)$orig_taka_no; // try plain number first if (!in_array($base, $existing, true)) return $base; // Find highest alpha prefix already used (e.g., a1, b1, z1, aa1) $highest = ''; foreach ($existing as $lab) { if (preg_match('/^([a-z]+)'.preg_quote($base,'/').'$/', $lab, $m)) { $letters = $m[1]; if (strlen($letters) > strlen($highest) || (strlen($letters)==strlen($highest) && strcmp($letters, $highest) > 0)) { $highest = $letters; } } } $next = next_alpha($highest); return $next.$base; } /* --- Build WHERE -------------------------------------------------------- */ $where = "WHERE company_id = :cid"; $params = [':cid'=>$company_id]; if ($from !== '') { $where .= " AND entry_date >= :from"; $params[':from'] = $from; } if ($to !== '') { $where .= " AND entry_date <= :to"; $params[':to'] = $to; } /* --- Fetch flat rows from loom_production_entry ------------------------- */ /* Expected columns: id, company_id, entry_date(date), quality, karigar, meter, taka_no, machine_no, pbn, sbn, weight, total_meter?, remark */ $sql = "SELECT entry_date, quality, karigar, meter, taka_no, machine_no, pbn, sbn, weight, remark FROM loom_production_entry $where ORDER BY entry_date, machine_no, taka_no, id"; $stmt = $pdo->prepare($sql); $groups = []; // key: date|machine|orig_taka_no $summary = ['rows'=>0,'groups'=>0,'inserted'=>0,'updated'=>0]; if ($do === '1') { $stmt->execute($params); while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) { $summary['rows']++; $date = $r['entry_date']; $mc = (int)$r['machine_no']; $orig = (int)$r['taka_no']; $key = $date.'|'.$mc.'|'.$orig; if (!isset($groups[$key])) { $groups[$key] = [ 'entry_date' => $date, 'machine_no' => $mc, 'orig_taka' => $orig, 'quality' => (string)$r['quality'], 'pbn' => $r['pbn'], 'sbn' => $r['sbn'], 'weight' => null, 'remark' => null, 'total_meter' => 0, 'lines' => [], ]; } $meter = is_null($r['meter']) ? 0 : (int)$r['meter']; $groups[$key]['total_meter'] += $meter; if ($groups[$key]['weight'] === null && $r['weight'] !== null && $r['weight'] !== '') { $groups[$key]['weight'] = (float)$r['weight']; } if (!$groups[$key]['remark'] && !empty($r['remark'])) { $groups[$key]['remark'] = $r['remark']; } $groups[$key]['lines'][] = [ 'karigar' => (string)$r['karigar'], 'meter' => $meter, 'pbn' => $r['pbn'], 'sbn' => $r['sbn'], 'note' => $r['remark'], ]; } /* --- Upsert groups into loom_taka_entry -------------------------------- */ $ins = $pdo->prepare(" INSERT INTO loom_taka_entry (company_id, entry_date, machine_no, quality, pbn, sbn, orig_taka_no, taka_no, taka_label, total_meter, weight, remark, lines_json) VALUES (:cid, :dt, :mc, :qlt, :pbn, :sbn, :orig_taka, :taka_no, :taka_label, :tot, :wt, :rmk, :j) ON DUPLICATE KEY UPDATE quality = VALUES(quality), pbn = VALUES(pbn), sbn = VALUES(sbn), total_meter = VALUES(total_meter), weight = VALUES(weight), remark = VALUES(remark), lines_json = VALUES(lines_json) "); $batchUsedByKey = []; // key => [labels used this run] foreach ($groups as $key => $g) { $date = $g['entry_date']; $mc = $g['machine_no']; $orig = $g['orig_taka']; if (!isset($batchUsedByKey[$key])) $batchUsedByKey[$key] = []; $label = compute_taka_label($pdo, $company_id, $date, $mc, $orig, $batchUsedByKey[$key]); $batchUsedByKey[$key][] = $label; $json = json_encode(['lines'=>$g['lines'], 'source_row_count'=>count($g['lines'])], JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES); // Detect insert vs update by trying a select on label $chk = $pdo->prepare("SELECT id FROM loom_taka_entry WHERE company_id=? AND entry_date=? AND machine_no=? AND taka_label=?"); $chk->execute([$company_id, $date, $mc, $label]); $isUpdate = (bool)$chk->fetchColumn(); $ins->execute([ ':cid' => $company_id, ':dt' => $date, ':mc' => $mc, ':qlt' => $g['quality'], ':pbn' => $g['pbn'], ':sbn' => $g['sbn'], ':orig_taka' => $orig, ':taka_no' => $orig, // keep numeric copy ':taka_label'=> $label, // unique label (1, a1, b1…) ':tot' => (int)$g['total_meter'], ':wt' => $g['weight'], ':rmk' => $g['remark'], ':j' => $json, ]); if ($isUpdate) $summary['updated']++; else $summary['inserted']++; } $summary['groups'] = count($groups); } ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>Per-Taka Aggregator (JSON + unique label)</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:980px} h1{margin:0 0 12px;font-size:20px} label{display:block;margin:8px 0 4px} input[type=date]{background:#0d1117;border:1px solid #2a3137;border-radius:10px;padding:8px 10px;color:#e8e8e8} .row{display:flex;gap:12px;align-items:center;flex-wrap:wrap} .btn{background:#1a2128;border:1px solid #2a3137;border-radius:10px;padding:10px 14px;color:#e8e8e8;cursor:pointer} .btn:hover{background:#22303a} .muted{color:#9aa4af} table{width:100%;border-collapse:collapse;margin-top:14px} th,td{border-bottom:1px solid #2a3137;padding:8px 6px;text-align:left;font-size:14px} code{background:#0d1117;border:1px solid #2a3137;padding:2px 6px;border-radius:8px} </style> </head> <body> <div class="card"> <h1>Per-Taka Aggregator</h1> <form method="get"> <div class="row"> <div> <label>From</label> <input type="date" name="from" value="<?=htmlspecialchars($from)?>"> </div> <div> <label>To</label> <input type="date" name="to" value="<?=htmlspecialchars($to)?>"> </div> <div style="margin-top:28px"> <button class="btn" type="submit" name="run" value="1">Aggregate Now</button> <a class="btn" href="/erp/tools/loom_taka_migrate.php">Run Migration</a> </div> </div> <p class="muted">Source: <code>loom_production_entry</code> → Target: <code>loom_taka_entry</code> (company_id=<?=$company_id?>)</p> </form> <?php if ($do==='1'): ?> <table> <tr> <th>Flat Rows</th> <th>Groups (date|machine|orig taka)</th> <th>Inserted</th> <th>Updated</th> </tr> <tr> <td><?= (int)$summary['rows'] ?></td> <td><?= (int)$summary['groups'] ?></td> <td><?= (int)$summary['inserted'] ?></td> <td><?= (int)$summary['updated'] ?></td> </tr> </table> <p class="muted">Labels auto-resolve like <code>1</code>, <code>a1</code>, <code>b1</code>, … <code>aa1</code> per (date+machine+orig_taka_no).</p> <?php endif; ?> </div> </body> </html>