WD - Dev
135 lines
4.8 KiB
PHP
135 lines
4.8 KiB
PHP
<?php
|
|
include "/www/wwwroot/bilspro/config/connect.php";
|
|
|
|
// Ambil data dari tabel setting_mikrotik
|
|
$stmt = $pdo->prepare("SELECT id, pppa, id_data_server, nama FROM setting_mikrotik WHERE pppa != ''");
|
|
$stmt->execute();
|
|
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
if ($rows) {
|
|
foreach ($rows as $row) {
|
|
$id = $row['id'];
|
|
|
|
$pppa = substr($row['pppa'],0,-1);
|
|
|
|
$entries = explode('|', $pppa);
|
|
// Buat array hasil dari $pppa
|
|
$result = [];
|
|
foreach ($entries as $entry) {
|
|
// Pecah setiap entry berdasarkan '_'
|
|
list($akun, $ip_address, $mac) = explode('_', $entry);
|
|
$result[] = [
|
|
'akun' => $akun,
|
|
'ip_address' => $ip_address,
|
|
'mac_address' => $mac
|
|
];
|
|
}
|
|
// Ambil data akun dari database
|
|
$dataakun = [];
|
|
$stmtPel = $pdo->prepare("SELECT id, akun FROM pelanggan WHERE id_setting_mikrotik = :id");
|
|
$stmtPel->bindValue(':id', $id, PDO::PARAM_INT); // Pastikan parameter tipe aman
|
|
$stmtPel->execute();
|
|
$pelanggan = $stmtPel->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Iterasi data pelanggan
|
|
foreach ($pelanggan as $key) {
|
|
if (isset($key['akun'], $key['id'])) { // Pastikan kedua kunci ada
|
|
$dataakun[] = [
|
|
"akun" => $key['akun'],
|
|
"id" => $key['id']
|
|
];
|
|
}
|
|
}
|
|
// Ambil hanya daftar akun dari $dataakun
|
|
$akunDataakun = array_column($dataakun, 'akun');
|
|
|
|
// Cari akun yang ada di $result tetapi tidak ada di $dataakun
|
|
$akunTidakAdaDiDataakun = array_filter($result, function ($item) use ($akunDataakun) {
|
|
return !in_array($item['akun'], $akunDataakun);
|
|
});
|
|
|
|
// Hasil akhir
|
|
$hasil = [
|
|
'akun_unmanage' => $akunTidakAdaDiDataakun
|
|
];
|
|
|
|
$ppp_unmanage = json_encode($hasil);
|
|
|
|
$updateStmt = $pdo->prepare("UPDATE setting_mikrotik SET ppp_unmanage = :ppp_unmanage WHERE id = :id");
|
|
$updateStmt->execute([':ppp_unmanage' => $ppp_unmanage, ':id' => $id]);
|
|
|
|
|
|
|
|
|
|
// Ambil semua akun pelanggan dalam satu query
|
|
$stmtPel = $pdo->prepare("SELECT id, akun FROM pelanggan WHERE id_setting_mikrotik = :id");
|
|
$stmtPel->bindValue(':id', $id);
|
|
$stmtPel->execute();
|
|
$pelanggan = $stmtPel->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$time = date('Y-m-d H:i:s');
|
|
$updates = [];
|
|
$updateStatusOff = [];
|
|
|
|
foreach ($pelanggan as $key) {
|
|
$akun = $key['akun'];
|
|
$idakun = $key['id'];
|
|
|
|
// Cek apakah akun ada dalam pppa
|
|
if (strpos($pppa, $akun) !== false) {
|
|
// Cari nama awalan dan ambil bagian data setelahnya
|
|
$cari00 = stristr($pppa, $akun);
|
|
// Ambil data hingga tanda '|'
|
|
$cari02 = strtok($cari00, "|");
|
|
// Rubah ke array
|
|
$data = explode("_", $cari02);
|
|
|
|
if (count($data) === 3) {
|
|
$ipadd = $data[1];
|
|
$macadd = $data[2];
|
|
|
|
// Siapkan data untuk update
|
|
$updates[] = [
|
|
'id' => $idakun,
|
|
'status' => 'On',
|
|
'ip_address' => $ipadd,
|
|
'mac_address' => $macadd,
|
|
'time' => $time,
|
|
];
|
|
}
|
|
} else {
|
|
$updateStatusOff[] = $idakun;
|
|
}
|
|
}
|
|
echo $row['nama'] . " =><br>\n";
|
|
|
|
// Update status koneksi yang 'On' sekaligus
|
|
if (!empty($updates)) {
|
|
$updateQuery = "UPDATE pelanggan SET status_koneksi = CASE id ";
|
|
foreach ($updates as $update) {
|
|
$updateQuery .= "WHEN {$update['id']} THEN 'On' ";
|
|
}
|
|
$updateQuery .= "END, ip_address = CASE id ";
|
|
foreach ($updates as $update) {
|
|
$updateQuery .= "WHEN {$update['id']} THEN '{$update['ip_address']}' ";
|
|
}
|
|
$updateQuery .= "END, mac_address = CASE id ";
|
|
foreach ($updates as $update) {
|
|
$updateQuery .= "WHEN {$update['id']} THEN '{$update['mac_address']}' ";
|
|
}
|
|
$updateQuery .= "END, time_update = '{$time}' WHERE id IN (" . implode(',', array_column($updates, 'id')) . ")";
|
|
$pdo->exec($updateQuery);
|
|
}
|
|
|
|
// Update status koneksi yang 'Off'
|
|
if (!empty($updateStatusOff)) {
|
|
$updateOffQuery = "UPDATE pelanggan SET status_koneksi = 'Off' WHERE id IN ('" . implode("', '", $updateStatusOff) . "')";
|
|
$pdo->exec($updateOffQuery);
|
|
}
|
|
|
|
}
|
|
} else {
|
|
echo "No data found.\n";
|
|
}
|
|
?>
|