WD - Dev
134 lines
4.6 KiB
PHP
134 lines
4.6 KiB
PHP
<?php
|
|
require '../vendor/autoload.php'; // Pastikan path ini sesuai dengan autoload.php Anda
|
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataType;
|
|
|
|
// Koneksi ke database
|
|
require __DIR__ . '/../config/connect.php';
|
|
require __DIR__ . '/../config/encrypt.php';
|
|
|
|
// Dekripsi parameter data
|
|
$datadecrypt = decrypt_url($_GET['data'] ?? '');
|
|
$dataurl = json_decode($datadecrypt, true);
|
|
|
|
$SqlSvData = $dataurl[0] ?? '';
|
|
|
|
// Kondisi awal
|
|
$where = "WHERE {$SqlSvData} status = 'aktif'";
|
|
|
|
// Query dasar
|
|
$query = "
|
|
SELECT nama, nik, akun, user_hotspot, alamat, latitude, longitude, nomor_whatsapp, email,
|
|
nama_server, nama_mikrotik, nama_paket, status_koneksi, ip_address, time_update, time_start
|
|
FROM v_pelanggan
|
|
{$where}
|
|
";
|
|
|
|
|
|
$dataparam = json_decode($_POST['datatable_params'], true);
|
|
// Tambahkan kondisi pencarian
|
|
$search = $dataparam['search']['value'] ?? '';
|
|
if (!empty($search)) {
|
|
$where .= " AND (
|
|
nama LIKE :search OR
|
|
nik LIKE :search OR
|
|
akun LIKE :search OR
|
|
status_koneksi LIKE :search OR
|
|
nomor_whatsapp LIKE :search OR
|
|
nama_paket LIKE :search OR
|
|
nama_server LIKE :search OR
|
|
alamat LIKE :search OR
|
|
time_start LIKE :search
|
|
)";
|
|
|
|
$query = "
|
|
SELECT nama, nik, akun, user_hotspot, alamat, latitude, longitude, nomor_whatsapp, email,
|
|
nama_server, nama_mikrotik, nama_paket, status_koneksi, ip_address, time_update, time_start
|
|
FROM v_pelanggan
|
|
{$where}
|
|
";
|
|
}
|
|
|
|
// Siapkan statement
|
|
$stmt = $pdo->prepare($query);
|
|
|
|
// Bind parameter pencarian
|
|
if (!empty($search)) {
|
|
$stmt->bindValue(':search', "%{$search}%", PDO::PARAM_STR);
|
|
}
|
|
|
|
$stmt->execute();
|
|
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Buat Spreadsheet
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
|
|
// Set header kolom
|
|
$sheet->setCellValue('A1', 'No')
|
|
->setCellValue('B1', 'Nama')
|
|
->setCellValue('C1', 'NIK')
|
|
->setCellValue('D1', 'ID Pelanggan')
|
|
->setCellValue('E1', 'Akun Hotspot')
|
|
->setCellValue('F1', 'Alamat')
|
|
->setCellValue('G1', 'Latitude')
|
|
->setCellValue('H1', 'Longitude')
|
|
->setCellValue('I1', 'Nomor Whatsapp')
|
|
->setCellValue('J1', 'Email')
|
|
->setCellValue('K1', 'Mulai Berlangganan')
|
|
->setCellValue('L1', 'Server')
|
|
->setCellValue('M1', 'Mikrotik')
|
|
->setCellValue('N1', 'Paket Berlangganan')
|
|
->setCellValue('O1', 'Status Koneksi')
|
|
->setCellValue('P1', 'IP Address')
|
|
->setCellValue('Q1', 'Last Update')
|
|
->setCellValue('R1', 'RX Power ONU')
|
|
->setCellValue('S1', 'TX Power ONU');
|
|
|
|
// Isi data
|
|
$row = 2;
|
|
$no = 1;
|
|
foreach ($data as $item) {
|
|
// Kolom No otomatis
|
|
$sheet->setCellValue('A' . $row, $no);
|
|
|
|
// Kolom text sensitif (pakai Explicit supaya tidak error di Excel)
|
|
$sheet->setCellValueExplicit('B' . $row, $item['nama'] ?? '', DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('C' . $row, $item['nik'] ?? '', DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('D' . $row, $item['akun'] ?? '', DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('E' . $row, $item['user_hotspot'] ?? '', DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('F' . $row, $item['alamat'] ?? '', DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('G' . $row, $item['latitude'] ?? '', DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('H' . $row, $item['longitude'] ?? '', DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('I' . $row, $item['nomor_whatsapp'] ?? '', DataType::TYPE_STRING);
|
|
$sheet->setCellValueExplicit('J' . $row, $item['email'] ?? '', DataType::TYPE_STRING);
|
|
|
|
// Kolom tanggal & teks biasa
|
|
$sheet->setCellValue('K' . $row, $item['time_start'] ?? '');
|
|
$sheet->setCellValue('L' . $row, $item['nama_server'] ?? '');
|
|
$sheet->setCellValue('M' . $row, $item['nama_mikrotik'] ?? '');
|
|
$sheet->setCellValue('N' . $row, $item['nama_paket'] ?? '');
|
|
$sheet->setCellValue('O' . $row, $item['status_koneksi'] ?? '');
|
|
$sheet->setCellValue('P' . $row, $item['ip_address'] ?? '');
|
|
$sheet->setCellValue('Q' . $row, $item['time_update'] ?? '');
|
|
|
|
// RX & TX Power dummy
|
|
$sheet->setCellValue('R' . $row, 'inf');
|
|
$sheet->setCellValue('S' . $row, 'inf');
|
|
|
|
$row++;
|
|
$no++;
|
|
}
|
|
|
|
// Set header download
|
|
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
|
header('Content-Disposition: attachment;filename="data_export_pelanggan_aktif.xlsx"');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
// Output file
|
|
$writer = new Xlsx($spreadsheet);
|
|
$writer->save('php://output');
|
|
exit;
|