D1041231084_WebGis_GeoPoverty/api/dashboard.php

<?php
// ============================================================
// api/dashboard.php — Agregasi Data & Statistik Dashboard Eksekutif
// ============================================================
require_once __DIR__ . '/config.php';

$method = $_SERVER['REQUEST_METHOD'];
$db     = getDB();

// Dashboard: data agregat bisa diakses publik (tidak ada data pribadi warga)

if ($method === 'GET') {
    // ── 1. KPI STATS ──────────────────────────────────────────
    // Total KK Terdaftar (aktif)
    $kpiKK = $db->query("SELECT COUNT(*) FROM warga WHERE status = 'aktif'")->fetchColumn();

    // Keluarga Sangat Miskin (aktif)
    $kpiSM = $db->query("SELECT COUNT(*) FROM warga WHERE status = 'aktif' AND kategori = 'sangat_miskin'")->fetchColumn();

    // Total Penyaluran Bantuan & Nominal
    $bantuanStats = $db->query(
        "SELECT COUNT(*) AS total_penyaluran, COALESCE(SUM(nominal), 0) AS total_nominal 
         FROM histori_bantuan"
    )->fetch();
    $kpiBn = $bantuanStats['total_penyaluran'];
    $kpiBnNom = $bantuanStats['total_nominal'];

    // Penyakit Terbanyak
    $kpiPyRow = $db->query(
        "SELECT penyakit, COUNT(*) AS jumlah 
         FROM riwayat_penyakit 
         GROUP BY penyakit 
         ORDER BY jumlah DESC 
         LIMIT 1"
    )->fetch();
    $kpiPy = $kpiPyRow ? $kpiPyRow['penyakit'] : 'Tidak Ada';

    // ── 2. CHART 1: KK Miskin per Kelurahan (Top 10) ──────────
    $chartKelRows = $db->query(
        "SELECT k.nama AS kelurahan, COUNT(w.id) AS jumlah
         FROM warga w
         LEFT JOIN kelurahan k ON w.kelurahan_id = k.id
         WHERE w.status = 'aktif'
         GROUP BY w.kelurahan_id
         ORDER BY jumlah DESC
         LIMIT 10"
    )->fetchAll();
    
    $chartKel = [
        'labels' => array_column($chartKelRows, 'kelurahan'),
        'data'   => array_map('intval', array_column($chartKelRows, 'jumlah'))
    ];

    // ── 3. CHART 2: Distribusi Kategori Kemiskinan ────────────
    $chartKatRows = $db->query(
        "SELECT kategori, COUNT(*) AS jumlah
         FROM warga
         WHERE status = 'aktif'
         GROUP BY kategori"
    )->fetchAll();
    
    $katCounts = ['sangat_miskin' => 0, 'miskin' => 0, 'hampir_miskin' => 0];
    foreach ($chartKatRows as $row) {
        $katCounts[$row['kategori']] = (int)$row['jumlah'];
    }
    $chartKat = [
        'labels' => ['Sangat Miskin', 'Miskin', 'Hampir Miskin'],
        'data'   => [$katCounts['sangat_miskin'], $katCounts['miskin'], $katCounts['hampir_miskin']]
    ];

    // ── 4. CHART 3: Penyaluran per Program Bantuan ────────────
    $chartBnRows = $db->query(
        "SELECT jenis, COUNT(*) AS jumlah
         FROM histori_bantuan
         GROUP BY jenis
         ORDER BY jumlah DESC"
    )->fetchAll();
    
    $chartBn = [
        'labels' => array_column($chartBnRows, 'jenis'),
        'data'   => array_map('intval', array_column($chartBnRows, 'jumlah'))
    ];

    // ── 5. CHART 4: Prevalensi Penyakit ───────────────────────
    $chartPyRows = $db->query(
        "SELECT penyakit, COUNT(*) AS jumlah
         FROM riwayat_penyakit
         GROUP BY penyakit
         ORDER BY jumlah DESC"
    )->fetchAll();
    
    $chartPy = [
        'labels' => array_column($chartPyRows, 'penyakit'),
        'data'   => array_map('intval', array_column($chartPyRows, 'jumlah'))
    ];

    // ── 6. TABEL RANKING KELURAHAN LENGKAP ────────────────────
    $rankingRows = $db->query(
        "SELECT 
            k.nama AS kelurahan,
            k.kecamatan,
            COUNT(CASE WHEN w.kategori = 'sangat_miskin' AND w.status = 'aktif' THEN 1 END) AS sangat_miskin,
            COUNT(CASE WHEN w.kategori = 'miskin' AND w.status = 'aktif' THEN 1 END) AS miskin,
            COUNT(CASE WHEN w.kategori = 'hampir_miskin' AND w.status = 'aktif' THEN 1 END) AS hampir_miskin,
            COUNT(CASE WHEN w.status = 'aktif' THEN 1 END) AS total,
            COALESCE(SUM(hb.nominal), 0) AS total_bantuan,
            AVG(CASE WHEN w.status = 'aktif' AND w.lat IS NOT NULL AND w.lat != 0 THEN w.lat END) AS avg_lat,
            AVG(CASE WHEN w.status = 'aktif' AND w.lng IS NOT NULL AND w.lng != 0 THEN w.lng END) AS avg_lng
         FROM kelurahan k
         LEFT JOIN warga w ON w.kelurahan_id = k.id
         LEFT JOIN histori_bantuan hb ON hb.warga_id = w.id
         GROUP BY k.id
         ORDER BY total DESC, kelurahan ASC"
    )->fetchAll();

    $ranking = [];
    foreach ($rankingRows as $r) {
        $ranking[] = [
            'kelurahan'     => $r['kelurahan'],
            'kecamatan'     => $r['kecamatan'],
            'sangat_miskin' => (int)$r['sangat_miskin'],
            'miskin'        => (int)$r['miskin'],
            'hampir_miskin' => (int)$r['hampir_miskin'],
            'total'         => (int)$r['total'],
            'total_bantuan' => (float)$r['total_bantuan'],
            'lat'           => $r['avg_lat'] ? (float)$r['avg_lat'] : null,
            'lng'           => $r['avg_lng'] ? (float)$r['avg_lng'] : null,
        ];
    }

    // Gabungkan hasil statistik
    $dashboardData = [
        'kpis' => [
            'total_kk'         => $kpiKK,
            'sangat_miskin'    => $kpiSM,
            'total_penyaluran' => $kpiBn,
            'total_nominal'    => $kpiBnNom,
            'penyakit_top'     => $kpiPy
        ],
        'charts' => [
            'kelurahan'  => $chartKel,
            'kategori'   => $chartKat,
            'bantuan'    => $chartBn,
            'penyakit'   => $chartPy
        ],
        'ranking' => $ranking
    ];

    jsonOk($dashboardData);
}

jsonError('Method tidak valid', 405);