L4zyL4mb
119 lines
5.1 KiB
PHP
119 lines
5.1 KiB
PHP
<?php
|
||
// api/export_excel.php v2.0
|
||
// ============================================================
|
||
// Export Data Penduduk Miskin ke Excel (format CSV UTF-8 BOM)
|
||
// Tidak memerlukan library eksternal
|
||
// ============================================================
|
||
require_once __DIR__ . '/../includes/config.php';
|
||
|
||
$db = getDB();
|
||
|
||
// ── Filter dari query string ───────────────────────────────
|
||
$kelurahan = $_GET['kelurahan'] ?? null;
|
||
$kecamatan = $_GET['kecamatan'] ?? null;
|
||
$kategori = $_GET['kategori'] ?? null;
|
||
$verifikasi = $_GET['verifikasi'] ?? null;
|
||
$bantuan = $_GET['bantuan'] ?? null;
|
||
|
||
$where = "WHERE 1=1";
|
||
$params = [];
|
||
if ($kelurahan) { $where .= " AND w.kelurahan = ?"; $params[] = $kelurahan; }
|
||
if ($kecamatan) { $where .= " AND w.kecamatan = ?"; $params[] = $kecamatan; }
|
||
if ($kategori) { $where .= " AND pm.kategori_kemiskinan = ?"; $params[] = $kategori; }
|
||
if ($verifikasi) { $where .= " AND pm.status_verifikasi = ?"; $params[] = $verifikasi; }
|
||
if ($bantuan === 'tidak') {
|
||
$where .= " AND (SELECT COUNT(*) FROM bantuan_penduduk bp WHERE bp.penduduk_id = pm.id AND bp.status='aktif') = 0";
|
||
} elseif ($bantuan === 'ada') {
|
||
$where .= " AND (SELECT COUNT(*) FROM bantuan_penduduk bp WHERE bp.penduduk_id = pm.id AND bp.status='aktif') > 0";
|
||
}
|
||
|
||
$stmt = $db->prepare("
|
||
SELECT
|
||
pm.nik_kk, pm.nama_kk, pm.no_hp,
|
||
pm.alamat, pm.rt, pm.rw,
|
||
COALESCE(w.kelurahan,'–') AS kelurahan,
|
||
COALESCE(w.kecamatan,'–') AS kecamatan,
|
||
pm.jumlah_anggota, pm.jumlah_anak_sekolah,
|
||
pm.penghasilan_bulanan, pm.sumber_penghasilan, pm.status_pekerjaan,
|
||
pm.kategori_kemiskinan, pm.kondisi_rumah, pm.kepemilikan_rumah,
|
||
CASE pm.punya_bpjs WHEN 1 THEN 'Ya' ELSE 'Tidak' END AS punya_bpjs,
|
||
pm.jenis_bpjs,
|
||
pm.lat, pm.lng,
|
||
pm.status_verifikasi,
|
||
COALESCE(u.nama,'–') AS nama_surveyor,
|
||
DATE_FORMAT(pm.created_at,'%d/%m/%Y %H:%i') AS tgl_input,
|
||
(SELECT GROUP_CONCAT(jb.kode ORDER BY jb.kode SEPARATOR ', ')
|
||
FROM bantuan_penduduk bp JOIN jenis_bantuan jb ON jb.id = bp.bantuan_id
|
||
WHERE bp.penduduk_id = pm.id AND bp.status='aktif') AS bantuan_diterima
|
||
FROM penduduk_miskin pm
|
||
LEFT JOIN wilayah w ON w.id = pm.wilayah_id
|
||
LEFT JOIN users u ON u.id = pm.surveyor_id
|
||
$where
|
||
ORDER BY w.kelurahan, pm.nama_kk
|
||
");
|
||
$stmt->execute($params);
|
||
$rows = $stmt->fetchAll();
|
||
|
||
// ── Nama file ─────────────────────────────────────────────
|
||
$filter = $kelurahan ? "_" . preg_replace('/\s+/', '-', $kelurahan) : '';
|
||
$filename = "data_kemiskinan{$filter}_" . date('Ymd_His') . ".csv";
|
||
|
||
// ── Output header HTTP ─────────────────────────────────────
|
||
header('Content-Type: text/csv; charset=UTF-8');
|
||
header('Content-Disposition: attachment; filename="' . $filename . '"');
|
||
header('Cache-Control: no-cache, no-store, must-revalidate');
|
||
header('Pragma: no-cache');
|
||
|
||
// BOM UTF-8 agar Excel baca karakter Indonesia dengan benar
|
||
echo "\xEF\xBB\xBF";
|
||
|
||
$out = fopen('php://output', 'w');
|
||
|
||
// ── Header kolom ──────────────────────────────────────────
|
||
fputcsv($out, [
|
||
'No','NIK KK','Nama KK','No HP',
|
||
'Alamat','RT','RW','Kelurahan','Kecamatan',
|
||
'Jml Anggota','Anak Sekolah',
|
||
'Penghasilan/Bln','Sumber Penghasilan','Status Pekerjaan',
|
||
'Kategori Kemiskinan','Kondisi Rumah','Kepemilikan Rumah',
|
||
'Punya BPJS','Jenis BPJS',
|
||
'Latitude','Longitude',
|
||
'Status Verifikasi','Bantuan Diterima',
|
||
'Petugas Input','Tgl Input',
|
||
], ';');
|
||
|
||
// ── Data rows ─────────────────────────────────────────────
|
||
$no = 1;
|
||
foreach ($rows as $r) {
|
||
fputcsv($out, [
|
||
$no++,
|
||
$r['nik_kk'],
|
||
$r['nama_kk'],
|
||
$r['no_hp'] ?? '–',
|
||
$r['alamat'] ?? '–',
|
||
$r['rt'] ?? '–',
|
||
$r['rw'] ?? '–',
|
||
$r['kelurahan'],
|
||
$r['kecamatan'],
|
||
$r['jumlah_anggota'],
|
||
$r['jumlah_anak_sekolah'],
|
||
$r['penghasilan_bulanan'] ? 'Rp ' . number_format($r['penghasilan_bulanan'],0,',','.') : '–',
|
||
$r['sumber_penghasilan'] ?? '–',
|
||
ucwords(str_replace('_',' ', $r['status_pekerjaan'])),
|
||
$r['kategori_kemiskinan'] === 'hampir_miskin' ? 'Nyaris Miskin' : ucwords(str_replace('_',' ', $r['kategori_kemiskinan'])),
|
||
ucwords(str_replace('_',' ', $r['kondisi_rumah'])),
|
||
ucwords(str_replace('_',' ', $r['kepemilikan_rumah'])),
|
||
$r['punya_bpjs'],
|
||
strtoupper(str_replace('_',' ', $r['jenis_bpjs'])),
|
||
$r['lat'],
|
||
$r['lng'],
|
||
ucwords(str_replace('_',' ', $r['status_verifikasi'])),
|
||
$r['bantuan_diterima'] ?? 'Belum Ada',
|
||
$r['nama_surveyor'],
|
||
$r['tgl_input'],
|
||
], ';');
|
||
}
|
||
|
||
fclose($out);
|
||
exit;
|