215 lines
8.3 KiB
PHP
215 lines
8.3 KiB
PHP
<?php
|
|
require_once __DIR__ . '/layout_header.php';
|
|
$db = db();
|
|
|
|
$jenis_iuran_id = $_GET['iuran_id'] ?? null;
|
|
$start_date = $_GET['start_date'] ?? date('Y-m-01');
|
|
$end_date = $_GET['end_date'] ?? date('Y-m-d');
|
|
|
|
// Fetch Jenis Iuran for filter
|
|
$iuran_list = $db->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'));
|
|
?>
|
|
|
|
<div class="top-bar">
|
|
<h1 class="h3 fw-bold m-0">Laporan Keuangan</h1>
|
|
<a href="?export=1&iuran_id=<?= $jenis_iuran_id ?>&start_date=<?= $start_date ?>&end_date=<?= $end_date ?>" class="btn btn-outline-success">
|
|
<i class="bi bi-file-earmark-spreadsheet"></i> Export CSV
|
|
</a>
|
|
</div>
|
|
|
|
<div class="card mb-4">
|
|
<form method="GET" class="row g-3 align-items-end">
|
|
<div class="col-md-3">
|
|
<label class="form-label small">Jenis Iuran</label>
|
|
<select name="iuran_id" class="form-select">
|
|
<option value="">Semua Iuran</option>
|
|
<?php foreach ($iuran_list as $i): ?>
|
|
<option value="<?= $i['id'] ?>" <?= $jenis_iuran_id == $i['id'] ? 'selected' : '' ?>><?= htmlspecialchars($i['nama']) ?></option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div class="col-md-3">
|
|
<label class="form-label small">Dari Tanggal</label>
|
|
<input type="date" name="start_date" class="form-control" value="<?= $start_date ?>">
|
|
</div>
|
|
<div class="col-md-3">
|
|
<label class="form-label small">Sampai Tanggal</label>
|
|
<input type="date" name="end_date" class="form-control" value="<?= $end_date ?>">
|
|
</div>
|
|
<div class="col-md-3">
|
|
<button type="submit" class="btn btn-primary w-100">Filter Laporan</button>
|
|
</div>
|
|
</form>
|
|
</div>
|
|
|
|
<div class="row g-4 mb-4">
|
|
<div class="col-md-4">
|
|
<div class="card text-center">
|
|
<div class="text-muted small mb-1">Total Pemasukan</div>
|
|
<div class="h4 fw-bold text-primary">Rp <?= number_format($total_masuk, 0, ',', '.') ?></div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-4">
|
|
<div class="card text-center">
|
|
<div class="text-muted small mb-1">Total Pengeluaran</div>
|
|
<div class="h4 fw-bold text-danger">Rp <?= number_format($total_keluar, 0, ',', '.') ?></div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-4">
|
|
<div class="card text-center">
|
|
<div class="text-muted small mb-1">Saldo Akhir Periode</div>
|
|
<div class="h4 fw-bold text-success">Rp <?= number_format($total_masuk - $total_keluar, 0, ',', '.') ?></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-4">
|
|
<div class="col-md-6">
|
|
<div class="card">
|
|
<h2 class="h5 fw-bold mb-4">Rincian Pemasukan</h2>
|
|
<div class="table-responsive">
|
|
<table class="table table-sm table-hover">
|
|
<thead>
|
|
<tr>
|
|
<th>Tgl</th>
|
|
<th>Warga / Iuran</th>
|
|
<th class="text-end">Jumlah</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($data_pemasukan as $p): ?>
|
|
<tr>
|
|
<td><span class="small"><?= date('d/m', strtotime($p['tanggal_bayar'])) ?></span></td>
|
|
<td>
|
|
<div class="fw-bold small"><?= htmlspecialchars($p['nama_warga']) ?></div>
|
|
<div class="text-muted" style="font-size: 0.7rem;"><?= htmlspecialchars($p['nama_iuran']) ?> (<?= $p['periode'] ?>)</div>
|
|
</td>
|
|
<td class="text-end fw-bold text-primary small">Rp <?= number_format($p['jumlah'], 0, ',', '.') ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
<?php if (empty($data_pemasukan)): ?>
|
|
<tr><td colspan="3" class="text-center py-4 text-muted">Tidak ada data.</td></tr>
|
|
<?php endif; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-6">
|
|
<div class="card">
|
|
<h2 class="h5 fw-bold mb-4">Rincian Pengeluaran</h2>
|
|
<div class="table-responsive">
|
|
<table class="table table-sm table-hover">
|
|
<thead>
|
|
<tr>
|
|
<th>Tgl</th>
|
|
<th>Kategori / Keterangan</th>
|
|
<th class="text-end">Jumlah</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($data_pengeluaran as $e): ?>
|
|
<tr>
|
|
<td><span class="small"><?= date('d/m', strtotime($e['tanggal'])) ?></span></td>
|
|
<td>
|
|
<div class="fw-bold small"><?= htmlspecialchars($e['kategori']) ?></div>
|
|
<div class="text-muted" style="font-size: 0.7rem;"><?= htmlspecialchars($e['keterangan']) ?></div>
|
|
</td>
|
|
<td class="text-end fw-bold text-danger small">Rp <?= number_format($e['jumlah'], 0, ',', '.') ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
<?php if (empty($data_pengeluaran)): ?>
|
|
<tr><td colspan="3" class="text-center py-4 text-muted">Tidak ada data.</td></tr>
|
|
<?php endif; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<?php require_once __DIR__ . '/layout_footer.php'; ?>
|