« Back to History
loom_quality_master_seed.php
|
20260722_120325.php
Initial Domain Snapshot
Copy Code
<?php /* ========================================================= File: /erp/tools/loom_quality_master_seed.php Purpose: Create + Seed loom_quality_master for current company Scope : Company-scoped, no global/base changes Auth : modules/auth/page_acl.php ========================================================= */ error_reporting(E_ALL); ini_set('display_errors', 1); /* (A) Auth + ACL (brings $pdo) */ require __DIR__ . '/../modules/auth/page_acl.php'; $ctx = page_require_access('loom_quality_master_seed'); // give this slug access to Owner/Admin as you wish $u = $ctx['user']; $company_id = (int)$ctx['company_id']; $user_id = (int)$u['id']; $pdo = $ctx['pdo'] ?? null; if(!$pdo){ require __DIR__ . '/../core/db.php'; } /* (B) Helpers */ function q(PDO $pdo, $sql, $a=[]){ $st=$pdo->prepare($sql); $st->execute($a); return $st; } function dmy_to_sql($s){ $s = trim((string)$s); if($s==='') return null; // 27-Jul-2025, 3-Aug-2025 etc. $dt = DateTime::createFromFormat('j-M-Y', $s); if(!$dt){ $dt = DateTime::createFromFormat('j-M-y', $s); } return $dt ? $dt->format('Y-m-d') : null; } /* (C) Create table if missing (DDL) */ $pdo->exec(" CREATE TABLE IF NOT EXISTS `loom_quality_master` ( `id` BIGINT PRIMARY KEY AUTO_INCREMENT, `company_id` BIGINT NOT NULL, `machine_id` BIGINT NOT NULL, `machine_code` VARCHAR(50) NOT NULL, `quality` VARCHAR(100) NULL, `quality_pasaria` VARCHAR(100) NULL, `pasaria_date` DATE NULL, `notes` VARCHAR(255) NULL, `created_by` BIGINT NULL, `updated_by` BIGINT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `uniq_company_machine` (`company_id`,`machine_id`), KEY `idx_company_code` (`company_id`,`machine_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); /* (D) Given data (Mc No., Quality, Quality Pasaria, Pasaria Date) — TSV */ $data = <<<TSV 1 GP GP 27-Jul-2025 2 GP 3 Balaton 4 GP 5 GP GP 1-Jun-2025 6 Balaton 7 GP 8 GP 9 GP 10 GP GP 3-Aug-2025 11 GP 12 GP GP 8-Jul-2025 13 GP GP 2-Jun-2025 14 Balaton 15 GP 16 Balaton GP 3-Aug-2025 17 Balaton 18 GP 19 GP 20 Balaton 21 Balaton 22 GP 23 GP 24 GP 25 GP GP 22-Jul-2025 26 GP GP 4-Jul-2025 27 GP 28 Balaton 29 GP 30 GP 31 GP GP 29-Jul-2025 32 GP GP 9-Jul-2025 33 GP GP 18-Jul-2025 34 GP GP 3-Jun-2025 35 GP GP 5-Aug-2025 36 GP 37 GP GP 14-Jul-2025 38 GP 39 GP GP 29-Jun-2025 40 GP GP 16-Jun-2025 41 GP 42 Butti 43 GP 22-Jul-2025 44 GP 45 Butti 46 GP GP 17-Jun-2025 47 GP GP 3-Aug-2025 48 GP GP 18-Jul-2025 49 GP GP 16-Jul-2025 50 Butti Butti 10-Jun-2025 51 GP GP 25-Jul-2025 52 GP GP 8-Jul-2025 53 GP GP 5-Jul-2025 54 GP GP 3-Aug-2025 55 GP 56 GP GP 19-Jul-2025 57 GP GP 29-Jul-2025 58 GP GP 3-Jul-2025 59 GP GP 24-Jul-2025 60 Butti 61 GP GP 6-Jul-2025 62 GP GP 12-Jul-2025 63 GP 64 Butti 65 GP GP 8-Jul-2025 66 GP 67 Butti Butti 11-Jun-2025 68 GP GP 18-Jul-2025 69 GP GP 5-Jul-2025 70 GP GP 11-Jul-2025 71 GP GP 12-Jul-2025 72 GP 22-Jul-2025 73 GP GP 27-Jun-2025 74 GP GP 12-Jul-2025 75 GP GP 3-Aug-2025 76 GP GP 15-Jul-2025 77 GP 78 GP GP 24-Jun-2025 79 Organza 80 GP GP 3-Aug-2025 81 GP GP 26-Jul-2025 82 GP 83 GP 84 GP 85 GP 86 Catonic Catonic 28-Jul-2025 87 GP 88 GP GP 6-Aug-2025 89 GP GP 4-Aug-2025 90 GP GP 22-Jun-2025 91 GP GP 27-Jul-2025 92 GP 93 GP 94 GP 95 GP 96 GP GP 29-Jul-2025 97 Organza 98 GP 99 GP GP 22-Jun-2025 100 GP 101 GP 102 Organza 103 GP 104 GP 105 GP 106 GP 107 GP 108 Organza 109 GP 110 GP 111 Organza 3-Aug-2025 112 GP 113 Organza 114 GP 115 GP 116 GP TSV; /* (E) Prepare statements */ $selMach = q($pdo, "SELECT id, code FROM machines WHERE company_id=? AND (LOWER(code)=LOWER(?) OR code=?) LIMIT 1", [$company_id,'','']); // dummy to create PDOStatement $selMachSql = "SELECT id, code FROM machines WHERE company_id=? AND (LOWER(code)=LOWER(?) OR code=?) LIMIT 1"; $upsertSql = " INSERT INTO loom_quality_master (company_id, machine_id, machine_code, quality, quality_pasaria, pasaria_date, created_by, updated_by) VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE quality = VALUES(quality), quality_pasaria = VALUES(quality_pasaria), pasaria_date = VALUES(pasaria_date), updated_by = VALUES(updated_by), updated_at = NOW() "; $upsert = $pdo->prepare($upsertSql); $inserted=0; $updated=0; $missing=[]; /* (F) Parse + seed */ $lines = preg_split("/\r\n|\n|\r/", trim($data)); foreach($lines as $ln){ if(trim($ln)==='') continue; $cols = preg_split("/\t+/", $ln); // Expected: [mc_no, quality, quality_pasaria, pasaria_date?] $mc_no = isset($cols[0]) ? (int)trim($cols[0]) : 0; $quality = isset($cols[1]) ? trim($cols[1]) : ''; $quality_pas = isset($cols[2]) ? trim($cols[2]) : ''; $pasaria_date_s = isset($cols[3]) ? trim($cols[3]) : ''; if($mc_no<=0) continue; $code = sprintf('loom %03d', $mc_no); // matches your machines.code in screenshot $st = $pdo->prepare($selMachSql); $st->execute([$company_id, $code, $code]); $m = $st->fetch(PDO::FETCH_ASSOC); if(!$m){ // try alt case (e.g., 'Loom 001') just in case someone typed different case/space $alt = sprintf('Loom %03d', $mc_no); $st = $pdo->prepare($selMachSql); $st->execute([$company_id, $alt, $alt]); $m = $st->fetch(PDO::FETCH_ASSOC); } if(!$m){ $missing[] = $mc_no; continue; } $sqlDate = dmy_to_sql($pasaria_date_s); $upsert->execute([ $company_id, (int)$m['id'], (string)$m['code'], ($quality!=='' ? $quality : null), ($quality_pas!=='' ? $quality_pas : null), $sqlDate, $user_id, $user_id ]); // Heuristic: ON DUP KEY UPDATE returns 1 on insert, 2 on update (if data changed) $rc = $upsert->rowCount(); if($rc>=2){ $updated++; } else { $inserted++; } } /* (G) Output summary (simple) */ echo "<h2>loom_quality_master — Seed Summary</h2>"; echo "<p>Company: {$company_id}</p>"; echo "<ul>"; echo "<li>Inserted: {$inserted}</li>"; echo "<li>Updated: {$updated}</li>"; if(!empty($missing)){ sort($missing); $missList = implode(', ', $missing); echo "<li>Machines not found by code (loom ###): {$missList}</li>"; } echo "</ul>"; echo "<p>Done.</p>";