WD - Dev
129 lines
4.4 KiB
PHP
129 lines
4.4 KiB
PHP
<?php
|
|
include "/www/wwwroot/bilspro/config/connect.php";
|
|
|
|
// Set Waktu & Limit
|
|
date_default_timezone_set('Asia/Jakarta');
|
|
$time = date('Y-m-d H:i:s');
|
|
|
|
// 1. Ambil data dari tabel setting_mikrotik
|
|
$stmt = $pdo->prepare("SELECT id, pppa, id_data_server, nama FROM setting_mikrotik WHERE pppa != '' AND status = 'Connected'");
|
|
$stmt->execute();
|
|
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if (!$rows) {
|
|
echo "[" . $time . "] No data found.\n";
|
|
exit;
|
|
}
|
|
|
|
// 2. Buat Tabel Temporary (Di Memori) untuk menampung data update
|
|
// Ini jauh lebih cepat daripada query CASE WHEN
|
|
$pdo->exec("CREATE TEMPORARY TABLE temp_pelanggan_update (
|
|
id_pel INT PRIMARY KEY,
|
|
st VARCHAR(5),
|
|
ip VARCHAR(20),
|
|
mac VARCHAR(40)
|
|
)");
|
|
|
|
foreach ($rows as $row) {
|
|
$id_mikrotik = $row['id'];
|
|
$pppa_raw = $row['pppa'];
|
|
|
|
// Hilangkan karakter terakhir jika itu pemisah (seperti pipe atau koma)
|
|
$pppa = rtrim($pppa_raw, "|");
|
|
$entries = explode('|', $pppa);
|
|
|
|
// Parsing data Mikrotik ke array agar mudah dicari
|
|
$mikrotik_online = [];
|
|
foreach ($entries as $entry) {
|
|
$data_part = explode('_', $entry);
|
|
if (count($data_part) >= 3) {
|
|
$mikrotik_online[$data_part[0]] = [
|
|
'ip' => $data_part[1],
|
|
'mac' => $data_part[2]
|
|
];
|
|
}
|
|
}
|
|
|
|
// Ambil semua akun pelanggan yang terikat ke Mikrotik ini
|
|
$stmtPel = $pdo->prepare("SELECT id, akun FROM pelanggan WHERE id_setting_mikrotik = :id");
|
|
$stmtPel->execute([':id' => $id_mikrotik]);
|
|
$pelanggan = $stmtPel->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$updates_on = [];
|
|
$updates_off = [];
|
|
$data_akun_db = [];
|
|
|
|
foreach ($pelanggan as $p) {
|
|
$akun = $p['akun'];
|
|
$id_p = $p['id'];
|
|
$data_akun_db[] = ['akun' => $akun, 'id' => $id_p];
|
|
|
|
if (isset($mikrotik_online[$akun])) {
|
|
// Pelanggan Online
|
|
$updates_on[] = [
|
|
'id' => $id_p,
|
|
'ip' => $mikrotik_online[$akun]['ip'],
|
|
'mac' => $mikrotik_online[$akun]['mac']
|
|
];
|
|
} else {
|
|
// Pelanggan Offline
|
|
$updates_off[] = $id_p;
|
|
}
|
|
}
|
|
|
|
// --- PROSES UPDATE DB ---
|
|
$pdo->beginTransaction();
|
|
try {
|
|
// A. Update ppp_unmanage di setting_mikrotik
|
|
$akun_terdaftar = array_column($data_akun_db, 'akun');
|
|
$unmanaged = [];
|
|
foreach ($mikrotik_online as $akun_mik => $val) {
|
|
if (!in_array($akun_mik, $akun_terdaftar)) {
|
|
$unmanaged[] = [
|
|
'akun' => $akun_mik,
|
|
'ip_address' => $val['ip'],
|
|
'mac_address' => $val['mac']
|
|
];
|
|
}
|
|
}
|
|
$ppp_unmanage_json = json_encode(['akun_unmanage' => $unmanaged]);
|
|
$pdo->prepare("UPDATE setting_mikrotik SET ppp_unmanage = :json WHERE id = :id")
|
|
->execute([':json' => $ppp_unmanage_json, ':id' => $id_mikrotik]);
|
|
|
|
// B. Masukkan data 'On' ke Temporary Table
|
|
if (!empty($updates_on)) {
|
|
$stmtTemp = $pdo->prepare("INSERT INTO temp_pelanggan_update (id_pel, st, ip, mac) VALUES (?, 'On', ?, ?)");
|
|
foreach ($updates_on as $up) {
|
|
$stmtTemp->execute([$up['id'], $up['ip'], $up['mac']]);
|
|
}
|
|
|
|
// Jalankan Update Masal via INNER JOIN (Sangat Cepat)
|
|
$pdo->exec("UPDATE pelanggan p
|
|
INNER JOIN temp_pelanggan_update t ON p.id = t.id_pel
|
|
SET p.status_koneksi = t.st,
|
|
p.ip_address = t.ip,
|
|
p.mac_address = t.mac,
|
|
p.time_update = '$time'");
|
|
|
|
// Kosongkan temp table untuk mikrotik berikutnya dalam loop
|
|
$pdo->exec("TRUNCATE TABLE temp_pelanggan_update");
|
|
}
|
|
|
|
// C. Update status 'Off'
|
|
if (!empty($updates_off)) {
|
|
$ids_off = implode(',', array_map('intval', $updates_off));
|
|
$pdo->exec("UPDATE pelanggan SET status_koneksi = 'Off' WHERE id IN ($ids_off)");
|
|
}
|
|
|
|
$pdo->commit();
|
|
echo "[" . date('H:i:s') . "] {$row['nama']} success. (" . count($updates_on) . " On, " . count($updates_off) . " Off)\n";
|
|
|
|
} catch (Exception $e) {
|
|
$pdo->rollBack();
|
|
echo "[" . date('H:i:s') . "] Error at {$row['nama']}: " . $e->getMessage() . "\n";
|
|
}
|
|
}
|
|
|
|
// Hapus tabel temporary di akhir script
|
|
$pdo->exec("DROP TEMPORARY TABLE IF EXISTS temp_pelanggan_update");
|
|
?>
|