query("SELECT * FROM jenis_iuran ORDER BY nama ASC")->fetchAll();
// Handle Export CSV
if (isset($_GET['export'])) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="laporan_siwarga_'.date('Ymd').'.csv"');
$output = fopen('php://output', 'w');
fputcsv($output, ['Tanggal', 'Tipe', 'Kategori/Iuran', 'Warga', 'Keterangan', 'Masuk', 'Keluar']);
$sql = "
SELECT tanggal_bayar as tgl, 'Masuk' as tipe, j.nama as kategori, w.nama as warga, p.catatan as ket, p.jumlah as masuk, 0 as keluar
FROM pembayaran p
JOIN warga w ON p.warga_id = w.id
JOIN jenis_iuran j ON p.jenis_iuran_id = j.id
WHERE p.tanggal_bayar BETWEEN ? AND ?
";
$params = [$start_date, $end_date];
if ($jenis_iuran_id) {
$sql .= " AND p.jenis_iuran_id = ?";
$params[] = $jenis_iuran_id;
}
$sql .= " UNION ALL
SELECT tanggal as tgl, 'Keluar' as tipe, IFNULL(j.nama, 'Kas Umum') as kategori, '-' as warga, keterangan as ket, 0 as masuk, jumlah as keluar
FROM pengeluaran e
LEFT JOIN jenis_iuran j ON e.jenis_iuran_id = j.id
WHERE e.tanggal BETWEEN ? AND ?
";
$params[] = $start_date;
$params[] = $end_date;
if ($jenis_iuran_id) {
$sql .= " AND e.jenis_iuran_id = ?";
$params[] = $jenis_iuran_id;
}
$sql .= " ORDER BY tgl ASC";
$stmt = $db->prepare($sql);
$stmt->execute($params);
while ($row = $stmt->fetch()) {
fputcsv($output, [
$row['tgl'], $row['tipe'], $row['kategori'], $row['warga'], $row['ket'], $row['masuk'], $row['keluar']
]);
}
fclose($output);
exit;
}
// Fetch Report Data
$params = [$start_date, $end_date];
$where_p = "WHERE p.tanggal_bayar BETWEEN ? AND ?";
$where_e = "WHERE e.tanggal BETWEEN ? AND ?";
if ($jenis_iuran_id) {
$where_p .= " AND p.jenis_iuran_id = ?";
$where_e .= " AND e.jenis_iuran_id = ?";
$params = [$start_date, $end_date, $jenis_iuran_id];
}
$pemasukan = $db->prepare("
SELECT p.*, w.nama as nama_warga, j.nama as nama_iuran
FROM pembayaran p
JOIN warga w ON p.warga_id = w.id
JOIN jenis_iuran j ON p.jenis_iuran_id = j.id
$where_p
ORDER BY p.tanggal_bayar DESC
");
$pemasukan->execute($params);
$data_pemasukan = $pemasukan->fetchAll();
$pengeluaran = $db->prepare("
SELECT e.*, j.nama as nama_iuran
FROM pengeluaran e
LEFT JOIN jenis_iuran j ON e.jenis_iuran_id = j.id
$where_e
ORDER BY e.tanggal DESC
");
$pengeluaran->execute($params);
$data_pengeluaran = $pengeluaran->fetchAll();
$total_masuk = array_sum(array_column($data_pemasukan, 'jumlah'));
$total_keluar = array_sum(array_column($data_pengeluaran, 'jumlah'));
?>