139 lines
4.8 KiB
PHP
139 lines
4.8 KiB
PHP
<?php
|
|
require_once __DIR__ . '/auth.php';
|
|
require_login(true);
|
|
|
|
$bulan = (int) getv('bulan', date('n'));
|
|
$tahun = (int) getv('tahun', date('Y'));
|
|
$rumah_filter = getv('rumah_ibadah_id', '');
|
|
$agama_filter = getv('agama', '');
|
|
$program_filter = getv('program_id', '');
|
|
$status_filter = getv('status_penyaluran', 'semua');
|
|
$jenis_filter = getv('jenis_bantuan', '');
|
|
|
|
if ($bulan < 1 || $bulan > 12 || $tahun < 2000) json_response('err', 'Filter bulan/tahun tidak valid.');
|
|
if (!valid_enum($status_filter, ['semua','belum_diberi','sudah_diberi','belum_memenuhi_target','sesuai_target','melebihi_target'])) json_response('err', 'Status penyaluran tidak valid.');
|
|
|
|
$where = ["pb.status_pengajuan='diterima'", "COALESCE(pb.status_hapus,'normal')<>'disetujui'", "COALESCE(ri.status_hapus,'normal')<>'disetujui'", "prog.bulan=?", "prog.tahun=?", "prog.status_program<>'dibatalkan'"];
|
|
$types = 'ii';
|
|
$params = [$bulan, $tahun];
|
|
|
|
if ($program_filter !== '') {
|
|
$where[] = 'prog.id=?';
|
|
$types .= 'i';
|
|
$params[] = (int)$program_filter;
|
|
}
|
|
if ($jenis_filter !== '') {
|
|
$where[] = 'prog.jenis_bantuan=?';
|
|
$types .= 's';
|
|
$params[] = $jenis_filter;
|
|
}
|
|
if ($agama_filter !== '') {
|
|
$where[] = 'ri.agama=?';
|
|
$types .= 's';
|
|
$params[] = $agama_filter;
|
|
}
|
|
if ($rumah_filter !== '') {
|
|
$where[] = 'ri.id=?';
|
|
$types .= 'i';
|
|
$params[] = (int)$rumah_filter;
|
|
}
|
|
|
|
if ($_SESSION['role'] === 'admin_rumah_ibadah') {
|
|
$ids = get_admin_rumah_ibadah_ids((int)$_SESSION['user_id'], true);
|
|
if (empty($ids)) json_response('ok', 'Admin belum memiliki rumah ibadah diterima.', ['rows'=>[], 'rekap'=>[]]);
|
|
$where[] = build_in_condition('ri.id', $ids, $types, $params);
|
|
} elseif ($_SESSION['role'] === 'penerima') {
|
|
$where[] = 'pb.user_id=?';
|
|
$types .= 'i';
|
|
$params[] = (int)$_SESSION['user_id'];
|
|
}
|
|
|
|
$whereSql = implode(' AND ', $where);
|
|
|
|
$sql = "
|
|
SELECT
|
|
pb.id AS penerima_id,
|
|
pb.nama_penerima,
|
|
pb.no_hp,
|
|
pb.alamat,
|
|
pb.jenis_bantuan AS jenis_bantuan_diminta,
|
|
ri.id AS rumah_ibadah_id,
|
|
ri.nama_rumah_ibadah,
|
|
ri.agama,
|
|
prog.id AS program_id,
|
|
prog.nama_program,
|
|
prog.status_program,
|
|
prog.jenis_bantuan,
|
|
prog.bulan,
|
|
prog.tahun,
|
|
prog.target_frekuensi,
|
|
prog.nominal,
|
|
prog.jumlah_item,
|
|
COALESCE(cnt.jumlah_penyaluran, 0) AS jumlah_penyaluran,
|
|
cnt.tanggal_terakhir,
|
|
cnt.riwayat_penyaluran
|
|
FROM penerima_bantuan pb
|
|
JOIN rumah_ibadah ri ON ri.id = pb.rumah_ibadah_id
|
|
JOIN program_bantuan prog ON prog.rumah_ibadah_id = ri.id
|
|
LEFT JOIN (
|
|
SELECT penerima_id, program_id,
|
|
COUNT(id) AS jumlah_penyaluran,
|
|
MAX(tanggal_penyaluran) AS tanggal_terakhir,
|
|
GROUP_CONCAT(CONCAT(tanggal_penyaluran, ' | ', jenis_bantuan, ' | Rp', COALESCE(nominal,0), ' | Item: ', COALESCE(jumlah_item,''), ' | ke-', COALESCE(bantuan_ke,0), ' | ', COALESCE(keterangan,'')) ORDER BY tanggal_penyaluran SEPARATOR ';;') AS riwayat_penyaluran
|
|
FROM penyaluran_bantuan
|
|
WHERE MONTH(tanggal_penyaluran)=? AND YEAR(tanggal_penyaluran)=?
|
|
GROUP BY penerima_id, program_id
|
|
) cnt ON cnt.penerima_id = pb.id AND cnt.program_id = prog.id
|
|
WHERE $whereSql
|
|
ORDER BY ri.nama_rumah_ibadah ASC, pb.nama_penerima ASC, prog.nama_program ASC
|
|
";
|
|
|
|
// Dua parameter pertama untuk subquery count bulan/tahun, lalu parameter where.
|
|
$bindTypes = 'ii' . $types;
|
|
$bindParams = array_merge([$bulan, $tahun], $params);
|
|
$stmt = $koneksi->prepare($sql);
|
|
if (!$stmt) json_response('err', 'Query laporan gagal disiapkan: ' . $koneksi->error);
|
|
stmt_bind($stmt, $bindTypes, $bindParams);
|
|
$stmt->execute();
|
|
$res = $stmt->get_result();
|
|
|
|
$rows = [];
|
|
$rekap = [
|
|
'total' => 0,
|
|
'belum_diberi' => 0,
|
|
'sudah_diberi' => 0,
|
|
'belum_memenuhi_target' => 0,
|
|
'sesuai_target' => 0,
|
|
'melebihi_target' => 0
|
|
];
|
|
|
|
while ($row = $res->fetch_assoc()) {
|
|
$jumlah = (int)$row['jumlah_penyaluran'];
|
|
$target = max(1, (int)$row['target_frekuensi']);
|
|
if ($jumlah === 0) $status = 'belum_diberi';
|
|
elseif ($jumlah > 0 && $jumlah < $target) $status = 'belum_memenuhi_target';
|
|
elseif ($jumlah === $target) $status = 'sesuai_target';
|
|
else $status = 'melebihi_target';
|
|
|
|
$row['status_penyaluran'] = $status;
|
|
$row['riwayat'] = $row['riwayat_penyaluran'] ? explode(';;', $row['riwayat_penyaluran']) : [];
|
|
|
|
$include = false;
|
|
if ($status_filter === 'semua') $include = true;
|
|
elseif ($status_filter === 'sudah_diberi') $include = $jumlah > 0;
|
|
else $include = ($status_filter === $status);
|
|
|
|
if ($include) {
|
|
$rows[] = $row;
|
|
$rekap['total']++;
|
|
if ($jumlah > 0) $rekap['sudah_diberi']++;
|
|
else $rekap['belum_diberi']++;
|
|
if ($status === 'belum_memenuhi_target') $rekap['belum_memenuhi_target']++;
|
|
if ($status === 'sesuai_target') $rekap['sesuai_target']++;
|
|
if ($status === 'melebihi_target') $rekap['melebihi_target']++;
|
|
}
|
|
}
|
|
|
|
json_response('ok', 'Laporan penyaluran berhasil diambil.', ['rows' => $rows, 'rekap' => $rekap]);
|
|
?>
|