« Back to History
auto_beam_installation_sync.php
|
20260721_154033.php
Initial Bulk Import
Copy Code
<?php // tools/auto_beam_installation_sync.php // Usage: // - CLI/cron: php /path/to/erp/tools/auto_beam_installation_sync.php // - Web include: require_once __DIR__.'/tools/auto_beam_installation_sync.php'; // The script will detect $pdo/$company_id context if included; else it will bootstrap DB itself. set_time_limit(0); date_default_timezone_set('Asia/Kolkata'); // optional, adjust to your server timezone $baseDir = realpath(__DIR__ . '/..'); // assume /erp/tools => baseDir = /erp // bootstrap DB if $pdo not present if (!isset($pdo) || !$pdo) { // try to include project DB bootstrap if (file_exists($baseDir . '/core/db.php')) { require_once $baseDir . '/core/db.php'; // core/db.php should set $pdo; if not, adjust accordingly } if (!isset($pdo) || !$pdo) { // fallback: cannot proceed echo "DB connection missing. Ensure core/db.php sets \$pdo.\n"; exit(1); } } // include helper if exists $helperPath = $baseDir . '/modules/helpers/beam_install_helper.php'; if (file_exists($helperPath)) require_once $helperPath; // fallback minimal function if helper not present if (!function_exists('auto_beam_install_insert')) { function auto_beam_install_insert($pdo, $company_id, $data) { $minDate = '2025-12-01'; if (empty($data['installation_date'])) return ['ok'=>false,'msg'=>'installation_date missing']; $d = date('Y-m-d', strtotime($data['installation_date'])); if ($d < $minDate) return ['ok'=>false,'msg'=>"installation_date < {$minDate} ignored"]; $sql = "INSERT INTO beam_installation_record (company_id, source_table, source_id, beam_no, beam_type, machine_no, pasaria_id, pissing_entry_id, installation_date, note, details, created_by, created_at) VALUES (:cid,:src_table,:src_id,:beam_no,:beam_type,:machine_no,:pasaria_id,:pissing_id,:installation_date,:note,:details,:created_by,NOW())"; $stmt = $pdo->prepare($sql); try { $stmt->execute([ ':cid' => $company_id, ':src_table' => $data['source_table'] ?? null, ':src_id' => $data['source_id'] ?? null, ':beam_no' => $data['beam_no'] ?? null, ':beam_type' => $data['beam_type'] ?? null, ':machine_no' => $data['machine_no'] ?? null, ':pasaria_id' => $data['pasaria_id'] ?? null, ':pissing_id' => $data['pissing_entry_id'] ?? null, ':installation_date' => $d, ':note' => $data['note'] ?? null, ':details' => isset($data['details']) ? json_encode($data['details'], JSON_UNESCAPED_UNICODE) : null, ':created_by' => $data['created_by'] ?? null ]); return ['ok'=>true,'msg'=>'Inserted','id'=>$pdo->lastInsertId()]; } catch (Exception $e) { error_log("auto_beam_install_insert fallback error: ".$e->getMessage()); return ['ok'=>false,'msg'=>'DB error: '.$e->getMessage()]; } } } // helpers function get_sync_key($type, $company_id) { return "{$type}:company:{$company_id}"; } // retrieve current companies to process (distinct company_id from pissing_entry/pasaria_entry) $companyIds = []; try { $rows = $pdo->query("SELECT DISTINCT company_id FROM (SELECT company_id FROM pissing_entry UNION SELECT company_id FROM pasaria_entry) x")->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $r) { $companyIds[] = (int)$r['company_id']; } if (empty($companyIds)) { // fallback single company 0/1 - try company 3 as per screenshots (optional) $companyIds = [3]; } } catch (Exception $e) { // if these tables missing, exit gracefully echo "Error fetching companies: ".$e->getMessage()."\n"; exit(1); } // prepare statements used repeatedly $getLastStmt = $pdo->prepare("SELECT last_id FROM sync_state WHERE key_name = :k LIMIT 1"); $upsertStmt = $pdo->prepare("INSERT INTO sync_state (key_name, last_id, updated_at) VALUES (:k, :last, NOW()) ON DUPLICATE KEY UPDATE last_id = :last_u, updated_at = NOW()"); // process per company foreach ($companyIds as $company_id) { echo "=== Company {$company_id} ===\n"; // ---- PISSING ENTRY processing ---- $keyP = get_sync_key('pissing', $company_id); $getLastStmt->execute([':k'=>$keyP]); $lastP = (int)($getLastStmt->fetchColumn() ?: 0); echo " last pissing_id = {$lastP}\n"; // fetch new pissing entries (limit to avoid long runs) $fetchP = $pdo->prepare("SELECT id, entry_date, beam_no, beam_type, machine_no, company_id FROM pissing_entry WHERE company_id = :cid AND id > :last ORDER BY id ASC LIMIT 1000"); $fetchP->execute([':cid'=>$company_id, ':last'=>$lastP]); $newP = $fetchP->fetchAll(PDO::FETCH_ASSOC); $processedP = 0; $maxP = $lastP; foreach ($newP as $row) { $eid = (int)$row['id']; $entryDate = $row['entry_date'] ?? date('Y-m-d'); // only process if installation date >= 2025-12-01 if (date('Y-m-d', strtotime($entryDate)) < '2025-12-01') { echo " skipping pissing id {$eid} because entry_date {$entryDate} < 2025-12-01\n"; $maxP = max($maxP, $eid); continue; } $data = [ 'source_table' => 'pissing_entry', 'source_id' => $eid, 'beam_no' => $row['beam_no'] ?? null, 'beam_type' => $row['beam_type'] ?? null, 'machine_no' => $row['machine_no'] ?? null, 'pissing_entry_id' => $eid, 'installation_date' => $entryDate, 'note' => 'Auto sync from pissing_entry', 'created_by' => null ]; $res = auto_beam_install_insert($pdo, $company_id, $data); if ($res['ok']) { echo " pissing id {$eid} -> inserted beam_installation id {$res['id']}\n"; } else { echo " pissing id {$eid} -> skipped/failed: {$res['msg']}\n"; } $processedP++; $maxP = max($maxP, $eid); } // update last processed pissing id if ($maxP > $lastP) { $upsertStmt->execute([':k'=>$keyP, ':last'=>$maxP, ':last_u'=>$maxP]); echo " updated last pissing_id to {$maxP}\n"; } else { echo " no new pissing processed\n"; } // ---- PASARIA ENTRY processing ---- $keyS = get_sync_key('pasaria', $company_id); $getLastStmt->execute([':k'=>$keyS]); $lastS = (int)($getLastStmt->fetchColumn() ?: 0); echo " last pasaria_id = {$lastS}\n"; // fetch new pasaria entries $fetchS = $pdo->prepare("SELECT id, entry_date, primary_beam_no, secondary_beam_no, machine_no, company_id FROM pasaria_entry WHERE company_id = :cid AND id > :last ORDER BY id ASC LIMIT 1000"); $fetchS->execute([':cid'=>$company_id, ':last'=>$lastS]); $newS = $fetchS->fetchAll(PDO::FETCH_ASSOC); $processedS = 0; $maxS = $lastS; foreach ($newS as $row) { $sid = (int)$row['id']; $entryDate = $row['entry_date'] ?? date('Y-m-d'); if (date('Y-m-d', strtotime($entryDate)) < '2025-12-01') { echo " skipping pasaria id {$sid} because entry_date {$entryDate} < 2025-12-01\n"; $maxS = max($maxS, $sid); continue; } // primary if (!empty($row['primary_beam_no'])) { $data1 = [ 'source_table' => 'pasaria_entry', 'source_id' => $sid, 'beam_no' => $row['primary_beam_no'], 'beam_type' => 'Primary', 'machine_no' => $row['machine_no'] ?? null, 'pasaria_id' => $sid, 'installation_date' => $entryDate, 'note' => 'Auto sync pasaria primary', 'created_by' => null ]; $res1 = auto_beam_install_insert($pdo, $company_id, $data1); if ($res1['ok']) echo " pasaria id {$sid} primary -> inserted id {$res1['id']}\n"; else echo " pasaria id {$sid} primary -> skipped/failed: {$res1['msg']}\n"; } // secondary if (!empty($row['secondary_beam_no'])) { $data2 = [ 'source_table' => 'pasaria_entry', 'source_id' => $sid, 'beam_no' => $row['secondary_beam_no'], 'beam_type' => 'Secondary', 'machine_no' => $row['machine_no'] ?? null, 'pasaria_id' => $sid, 'installation_date' => $entryDate, 'note' => 'Auto sync pasaria secondary', 'created_by' => null ]; $res2 = auto_beam_install_insert($pdo, $company_id, $data2); if ($res2['ok']) echo " pasaria id {$sid} secondary -> inserted id {$res2['id']}\n"; else echo " pasaria id {$sid} secondary -> skipped/failed: {$res2['msg']}\n"; } $processedS++; $maxS = max($maxS, $sid); } if ($maxS > $lastS) { $upsertStmt->execute([':k'=>$keyS, ':last'=>$maxS, ':last_u'=>$maxS]); echo " updated last pasaria_id to {$maxS}\n"; } else { echo " no new pasaria processed\n"; } echo "Company {$company_id}: processed pissing {$processedP} rows, pasaria {$processedS} rows\n\n"; } echo "Sync complete at ".date('Y-m-d H:i:s')."\n";