490 lines
20 KiB
PHP
490 lines
20 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers\Admin;
|
|
|
|
use App\Http\Controllers\Controller;
|
|
use App\Models\UangSaku;
|
|
use App\Models\Santri;
|
|
use Illuminate\Http\Request;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Cache;
|
|
use Carbon\Carbon;
|
|
|
|
class UangSakuController extends Controller
|
|
{
|
|
// ────────────────────────────────────────────────────────────────
|
|
// PRIVATE: cek apakah user aktif adalah pamong
|
|
// ────────────────────────────────────────────────────────────────
|
|
private function isPamong(): bool
|
|
{
|
|
return auth()->user()->role === 'pamong';
|
|
}
|
|
|
|
private function requirePamong(): void
|
|
{
|
|
if (! $this->isPamong()) {
|
|
abort(403, 'Akses ditolak. Hanya Pamong yang dapat melakukan aksi ini.');
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Tampilkan daftar uang saku — Grouped per Santri
|
|
* Default: bulan ini
|
|
*/
|
|
public function index(Request $request)
|
|
{
|
|
$search = $request->get('search');
|
|
|
|
$dari = $request->get('dari', now()->startOfMonth()->format('Y-m-d'));
|
|
$sampai = $request->get('sampai', now()->endOfMonth()->format('Y-m-d'));
|
|
$sort = $request->get('sort', 'nama');
|
|
|
|
// ── KPI ringkasan periode ───────────────────────────────────
|
|
$kpiQuery = UangSaku::whereBetween('tanggal_transaksi', [$dari, $sampai]);
|
|
$kpi = [
|
|
'total_transaksi' => (clone $kpiQuery)->count(),
|
|
'total_pemasukan' => (float)(clone $kpiQuery)->where('jenis_transaksi', 'pemasukan')->sum('nominal'),
|
|
'total_pengeluaran' => (float)(clone $kpiQuery)->where('jenis_transaksi', 'pengeluaran')->sum('nominal'),
|
|
'total_santri' => (clone $kpiQuery)->distinct('id_santri')->count('id_santri'),
|
|
];
|
|
$kpi['selisih'] = $kpi['total_pemasukan'] - $kpi['total_pengeluaran'];
|
|
|
|
// ── KPI Real-time: total saldo semua santri ─────────────────
|
|
$kpi['total_saldo_realtime'] = (float) DB::table('uang_saku as a')
|
|
->whereNotExists(function ($q) {
|
|
$q->from('uang_saku as b')
|
|
->whereColumn('b.id_santri', 'a.id_santri')
|
|
->where(function ($inner) {
|
|
$inner->whereColumn('b.tanggal_transaksi', '>', 'a.tanggal_transaksi')
|
|
->orWhere(function ($tie) {
|
|
$tie->whereColumn('b.tanggal_transaksi', '=', 'a.tanggal_transaksi')
|
|
->whereColumn('b.id', '>', 'a.id');
|
|
});
|
|
});
|
|
})
|
|
->sum('saldo_sesudah');
|
|
|
|
// ── Query santri ────────────────────────────────────────────
|
|
$santriQuery = Santri::aktif()
|
|
->select('id_santri', 'nama_lengkap')
|
|
->has('uangSaku');
|
|
|
|
if ($search) {
|
|
$santriQuery->where(function ($q) use ($search) {
|
|
$q->where('nama_lengkap', 'like', "%{$search}%")
|
|
->orWhere('id_santri', 'like', "%{$search}%");
|
|
});
|
|
}
|
|
|
|
$santriQuery->orderBy('nama_lengkap');
|
|
$santriList = $santriQuery->paginate(20)->appends(request()->query());
|
|
$ids = $santriList->pluck('id_santri');
|
|
|
|
// ── Saldo terakhir per santri (NOT EXISTS) ──────────────────
|
|
$latestIds = DB::table('uang_saku as a')
|
|
->whereIn('a.id_santri', $ids)
|
|
->whereNotExists(function ($q) {
|
|
$q->from('uang_saku as b')
|
|
->whereColumn('b.id_santri', 'a.id_santri')
|
|
->where(function ($inner) {
|
|
$inner->whereColumn('b.tanggal_transaksi', '>', 'a.tanggal_transaksi')
|
|
->orWhere(function ($tie) {
|
|
$tie->whereColumn('b.tanggal_transaksi', '=', 'a.tanggal_transaksi')
|
|
->whereColumn('b.id', '>', 'a.id');
|
|
});
|
|
});
|
|
})
|
|
->select('a.id_santri', 'a.id')
|
|
->pluck('a.id', 'a.id_santri');
|
|
|
|
$saldoMap = UangSaku::whereIn('id', $latestIds->values())
|
|
->get()
|
|
->keyBy('id_santri');
|
|
|
|
// ── Statistik per santri mengikuti PERIODE filter ───────────
|
|
$periodeStats = UangSaku::whereIn('id_santri', $ids)
|
|
->whereBetween('tanggal_transaksi', [$dari, $sampai])
|
|
->select(
|
|
'id_santri',
|
|
DB::raw('SUM(CASE WHEN jenis_transaksi="pemasukan" THEN nominal ELSE 0 END) as pemasukan_periode'),
|
|
DB::raw('SUM(CASE WHEN jenis_transaksi="pengeluaran" THEN nominal ELSE 0 END) as pengeluaran_periode'),
|
|
DB::raw('COUNT(*) as total_periode')
|
|
)
|
|
->groupBy('id_santri')
|
|
->get()
|
|
->keyBy('id_santri');
|
|
|
|
// ── Transaksi terbaru per santri (max 5) ────────────────────
|
|
$transaksiMap = UangSaku::whereIn('id_santri', $ids)
|
|
->orderByDesc('tanggal_transaksi')
|
|
->orderByDesc('id')
|
|
->get()
|
|
->groupBy('id_santri')
|
|
->map(fn($g) => $g->take(5));
|
|
|
|
// ── Attach data ke santri objects ───────────────────────────
|
|
$collection = $santriList->getCollection()->map(function ($santri) use ($saldoMap, $periodeStats, $transaksiMap) {
|
|
$saldoRow = $saldoMap[$santri->id_santri] ?? null;
|
|
$periode = $periodeStats[$santri->id_santri] ?? null;
|
|
|
|
$santri->saldo_terakhir = $saldoRow ? (float)$saldoRow->saldo_sesudah : 0;
|
|
$santri->transaksi_terakhir_tgl = $saldoRow ? $saldoRow->tanggal_transaksi : null;
|
|
$santri->pemasukan_periode = $periode ? (float)$periode->pemasukan_periode : 0;
|
|
$santri->pengeluaran_periode = $periode ? (float)$periode->pengeluaran_periode : 0;
|
|
$santri->transaksi_periode = $periode ? (int)$periode->total_periode : 0;
|
|
$santri->transaksi_terbaru = $transaksiMap[$santri->id_santri] ?? collect();
|
|
return $santri;
|
|
});
|
|
|
|
$sorted = match($sort) {
|
|
'saldo_asc' => $collection->sortBy('saldo_terakhir'),
|
|
'saldo_desc' => $collection->sortByDesc('saldo_terakhir'),
|
|
'transaksi_desc' => $collection->sortByDesc('transaksi_periode'),
|
|
'terakhir' => $collection->sortByDesc('transaksi_terakhir_tgl'),
|
|
default => $collection->sortBy('nama_lengkap'),
|
|
};
|
|
|
|
$santriList->setCollection($sorted->values());
|
|
|
|
// Kirim flag ke view agar tampilan bisa menyesuaikan
|
|
$canCrud = $this->isPamong();
|
|
|
|
return view('admin.uang-saku.index', compact('santriList', 'kpi', 'dari', 'sampai', 'sort', 'canCrud'));
|
|
}
|
|
|
|
/**
|
|
* AJAX: Info santri untuk form create/edit
|
|
*/
|
|
public function santriInfo($id_santri)
|
|
{
|
|
$santri = Santri::where('id_santri', $id_santri)->firstOrFail();
|
|
$bulanIni = now();
|
|
|
|
$lastTx = UangSaku::where('id_santri', $id_santri)
|
|
->orderByDesc('tanggal_transaksi')
|
|
->orderByDesc('id')
|
|
->first();
|
|
|
|
$saldo = $lastTx ? (float)$lastTx->saldo_sesudah : 0;
|
|
|
|
$pemasukanBulanIni = UangSaku::where('id_santri', $id_santri)
|
|
->where('jenis_transaksi', 'pemasukan')
|
|
->whereMonth('tanggal_transaksi', $bulanIni->month)
|
|
->whereYear('tanggal_transaksi', $bulanIni->year)
|
|
->sum('nominal');
|
|
|
|
$pengeluaranBulanIni = UangSaku::where('id_santri', $id_santri)
|
|
->where('jenis_transaksi', 'pengeluaran')
|
|
->whereMonth('tanggal_transaksi', $bulanIni->month)
|
|
->whereYear('tanggal_transaksi', $bulanIni->year)
|
|
->sum('nominal');
|
|
|
|
$transaksiTerakhir = UangSaku::where('id_santri', $id_santri)
|
|
->orderByDesc('tanggal_transaksi')
|
|
->orderByDesc('id')
|
|
->limit(3)
|
|
->get()
|
|
->map(fn($t) => [
|
|
'tanggal' => $t->tanggal_transaksi->format('d/m/Y'),
|
|
'jenis' => $t->jenis_transaksi,
|
|
'nominal' => number_format($t->nominal, 0, ',', '.'),
|
|
'keterangan' => $t->keterangan ?? '-',
|
|
]);
|
|
|
|
return response()->json([
|
|
'nama' => $santri->nama_lengkap,
|
|
'saldo_terakhir' => number_format($saldo, 0, ',', '.'),
|
|
'saldo_raw' => $saldo,
|
|
'total_pemasukan_bulan_ini' => number_format($pemasukanBulanIni, 0, ',', '.'),
|
|
'total_pengeluaran_bulan_ini' => number_format($pengeluaranBulanIni, 0, ',', '.'),
|
|
'transaksi_terakhir' => $transaksiTerakhir,
|
|
]);
|
|
}
|
|
|
|
public function create()
|
|
{
|
|
$this->requirePamong();
|
|
|
|
$santriList = Santri::where('status', 'Aktif')
|
|
->select('id_santri', 'nama_lengkap')
|
|
->orderBy('nama_lengkap')
|
|
->get();
|
|
|
|
return view('admin.uang-saku.create', compact('santriList'));
|
|
}
|
|
|
|
public function store(Request $request)
|
|
{
|
|
$this->requirePamong();
|
|
|
|
$validated = $request->validate([
|
|
'id_santri' => 'required|exists:santris,id_santri',
|
|
'jenis_transaksi' => 'required|in:pemasukan,pengeluaran',
|
|
'nominal' => 'required|numeric|min:1|max:99999999',
|
|
'keterangan' => 'nullable|string|max:500',
|
|
'tanggal_transaksi' => 'required|date',
|
|
]);
|
|
|
|
DB::beginTransaction();
|
|
try {
|
|
UangSaku::create($validated);
|
|
$this->recalculateSaldoAfter($validated['id_santri'], $validated['tanggal_transaksi']);
|
|
|
|
if ($validated['jenis_transaksi'] === 'pengeluaran'
|
|
&& $this->hasSaldoNegatif($validated['id_santri'], $validated['tanggal_transaksi'])) {
|
|
DB::rollBack();
|
|
return back()->withInput()->with(
|
|
'error',
|
|
'Transaksi gagal: Saldo tidak mencukupi. ' .
|
|
'Jumlah pengeluaran melebihi saldo yang tersedia pada tanggal tersebut.'
|
|
);
|
|
}
|
|
|
|
DB::commit();
|
|
Cache::forget('santri_aktif_uang_saku');
|
|
return redirect()->route('admin.uang-saku.index')
|
|
->with('success', 'Transaksi uang saku berhasil ditambahkan.');
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
return back()->withInput()->with('error', 'Gagal menambahkan transaksi: ' . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
public function show($id)
|
|
{
|
|
$transaksi = UangSaku::with('santri')->findOrFail($id);
|
|
$canCrud = $this->isPamong();
|
|
return view('admin.uang-saku.show', compact('transaksi', 'canCrud'));
|
|
}
|
|
|
|
public function edit($id)
|
|
{
|
|
$this->requirePamong();
|
|
|
|
$transaksi = UangSaku::with('santri')->findOrFail($id);
|
|
$santriList = Santri::where('status', 'Aktif')
|
|
->select('id_santri', 'nama_lengkap')
|
|
->orderBy('nama_lengkap')
|
|
->get();
|
|
return view('admin.uang-saku.edit', compact('transaksi', 'santriList'));
|
|
}
|
|
|
|
public function update(Request $request, $id)
|
|
{
|
|
$this->requirePamong();
|
|
|
|
$transaksi = UangSaku::findOrFail($id);
|
|
$validated = $request->validate([
|
|
'jenis_transaksi' => 'required|in:pemasukan,pengeluaran',
|
|
'nominal' => 'required|numeric|min:1|max:99999999',
|
|
'keterangan' => 'nullable|string|max:500',
|
|
'tanggal_transaksi' => 'required|date',
|
|
]);
|
|
|
|
$tanggalLama = $transaksi->tanggal_transaksi->format('Y-m-d');
|
|
$tanggalBaru = $validated['tanggal_transaksi'];
|
|
$tanggalMulai = min($tanggalLama, $tanggalBaru);
|
|
|
|
DB::beginTransaction();
|
|
try {
|
|
$transaksi->fill($validated)->saveQuietly();
|
|
$this->recalculateSaldoAfter($transaksi->id_santri, $tanggalMulai);
|
|
|
|
if ($this->hasSaldoNegatif($transaksi->id_santri, $tanggalMulai)) {
|
|
DB::rollBack();
|
|
return back()->withInput()->with(
|
|
'error',
|
|
'Perubahan gagal: Perubahan ini menyebabkan saldo menjadi negatif. ' .
|
|
'Pengeluaran tidak boleh melebihi saldo yang tersedia.'
|
|
);
|
|
}
|
|
|
|
DB::commit();
|
|
Cache::forget('santri_aktif_uang_saku');
|
|
return redirect()->route('admin.uang-saku.index')
|
|
->with('success', 'Transaksi berhasil diperbarui.');
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
return back()->withInput()->with('error', 'Gagal memperbarui transaksi: ' . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
public function destroy($id)
|
|
{
|
|
$this->requirePamong();
|
|
|
|
$transaksi = UangSaku::findOrFail($id);
|
|
$idSantri = $transaksi->id_santri;
|
|
$tanggal = $transaksi->tanggal_transaksi->format('Y-m-d');
|
|
|
|
DB::beginTransaction();
|
|
try {
|
|
$transaksi->delete();
|
|
$this->recalculateSaldoAfter($idSantri, $tanggal);
|
|
DB::commit();
|
|
Cache::forget('santri_aktif_uang_saku');
|
|
return redirect()->route('admin.uang-saku.index')
|
|
->with('success', 'Transaksi berhasil dihapus.');
|
|
} catch (\Exception $e) {
|
|
DB::rollBack();
|
|
return back()->with('error', 'Gagal menghapus transaksi: ' . $e->getMessage());
|
|
}
|
|
}
|
|
|
|
public function riwayat(Request $request, $id_santri)
|
|
{
|
|
$santri = Santri::where('id_santri', $id_santri)->firstOrFail();
|
|
|
|
$tanggalDari = $request->filled('tanggal_dari')
|
|
? $request->tanggal_dari
|
|
: now()->startOfMonth()->format('Y-m-d');
|
|
$tanggalSampai = $request->filled('tanggal_sampai')
|
|
? $request->tanggal_sampai
|
|
: now()->endOfMonth()->format('Y-m-d');
|
|
|
|
// Transaksi dalam periode (paginated)
|
|
$transaksi = UangSaku::where('id_santri', $id_santri)
|
|
->whereBetween('tanggal_transaksi', [$tanggalDari, $tanggalSampai])
|
|
->orderBy('tanggal_transaksi', 'desc')
|
|
->orderBy('id', 'desc')
|
|
->paginate(20)
|
|
->appends($request->query());
|
|
|
|
// Total pemasukan & pengeluaran periode
|
|
$totalPemasukan = UangSaku::where('id_santri', $id_santri)
|
|
->where('jenis_transaksi', 'pemasukan')
|
|
->whereBetween('tanggal_transaksi', [$tanggalDari, $tanggalSampai])
|
|
->sum('nominal');
|
|
|
|
$totalPengeluaran = UangSaku::where('id_santri', $id_santri)
|
|
->where('jenis_transaksi', 'pengeluaran')
|
|
->whereBetween('tanggal_transaksi', [$tanggalDari, $tanggalSampai])
|
|
->sum('nominal');
|
|
|
|
// Saldo aktual real-time (kumulatif semua waktu)
|
|
$lastTx = UangSaku::where('id_santri', $id_santri)
|
|
->orderByDesc('tanggal_transaksi')
|
|
->orderByDesc('id')
|
|
->first();
|
|
$saldoTerakhir = $lastTx ? (float)$lastTx->saldo_sesudah : 0;
|
|
|
|
// Saldo awal periode = saldo_sesudah transaksi terakhir SEBELUM tanggalDari
|
|
$txSebelumPeriode = UangSaku::where('id_santri', $id_santri)
|
|
->where('tanggal_transaksi', '<', $tanggalDari)
|
|
->orderByDesc('tanggal_transaksi')
|
|
->orderByDesc('id')
|
|
->first();
|
|
$saldoAwalPeriode = $txSebelumPeriode ? (float)$txSebelumPeriode->saldo_sesudah : 0;
|
|
|
|
// Saldo akhir periode = saldo_sesudah transaksi terakhir s.d. tanggalSampai
|
|
$txAkhirPeriode = UangSaku::where('id_santri', $id_santri)
|
|
->where('tanggal_transaksi', '<=', $tanggalSampai)
|
|
->orderByDesc('tanggal_transaksi')
|
|
->orderByDesc('id')
|
|
->first();
|
|
$saldoAkhirPeriode = $txAkhirPeriode ? (float)$txAkhirPeriode->saldo_sesudah : 0;
|
|
|
|
// ── DATA GRAFIK: PERJALANAN SALDO ────────────────────────────
|
|
$saldoPerHari = UangSaku::where('id_santri', $id_santri)
|
|
->whereBetween('tanggal_transaksi', [$tanggalDari, $tanggalSampai])
|
|
->select(
|
|
DB::raw('DATE(tanggal_transaksi) as tgl'),
|
|
DB::raw('SUBSTRING_INDEX(GROUP_CONCAT(saldo_sesudah ORDER BY id DESC), ",", 1) as saldo_akhir_hari')
|
|
)
|
|
->groupBy('tgl')
|
|
->orderBy('tgl')
|
|
->get()
|
|
->keyBy('tgl');
|
|
|
|
$periodeDari = Carbon::parse($tanggalDari);
|
|
$periodeSampai = Carbon::parse($tanggalSampai);
|
|
|
|
$dataGrafikSaldo = [];
|
|
$saldoBerjalan = $saldoAwalPeriode;
|
|
$current = $periodeDari->copy();
|
|
|
|
$dataGrafikSaldo[] = [
|
|
'tanggal' => $periodeDari->format('Y-m-d'),
|
|
'saldo' => $saldoAwalPeriode,
|
|
'is_awal' => true,
|
|
];
|
|
|
|
while ($current->lte($periodeSampai)) {
|
|
$tgl = $current->format('Y-m-d');
|
|
if (isset($saldoPerHari[$tgl])) {
|
|
$saldoBerjalan = (float)$saldoPerHari[$tgl]->saldo_akhir_hari;
|
|
}
|
|
if ($current->eq($periodeDari)) {
|
|
if (isset($saldoPerHari[$tgl])) {
|
|
$dataGrafikSaldo[0]['saldo'] = (float)$saldoPerHari[$tgl]->saldo_akhir_hari;
|
|
$dataGrafikSaldo[0]['is_awal'] = false;
|
|
}
|
|
} else {
|
|
$dataGrafikSaldo[] = [
|
|
'tanggal' => $tgl,
|
|
'saldo' => $saldoBerjalan,
|
|
'is_awal' => false,
|
|
];
|
|
}
|
|
$current->addDay();
|
|
}
|
|
|
|
$canCrud = $this->isPamong();
|
|
|
|
return view('admin.uang-saku.riwayat', compact(
|
|
'santri', 'transaksi',
|
|
'totalPemasukan', 'totalPengeluaran',
|
|
'saldoAwalPeriode', 'saldoAkhirPeriode', 'saldoTerakhir',
|
|
'dataGrafikSaldo',
|
|
'tanggalDari', 'tanggalSampai',
|
|
'periodeDari', 'periodeSampai',
|
|
'canCrud'
|
|
));
|
|
}
|
|
|
|
// ────────────────────────────────────────────────────────────────
|
|
// PRIVATE HELPERS
|
|
// ────────────────────────────────────────────────────────────────
|
|
|
|
private function hasSaldoNegatif(string $idSantri, string $tanggal): bool
|
|
{
|
|
return UangSaku::where('id_santri', $idSantri)
|
|
->where('tanggal_transaksi', '>=', $tanggal)
|
|
->where('saldo_sesudah', '<', 0)
|
|
->exists();
|
|
}
|
|
|
|
private function recalculateSaldoAfter($idSantri, $tanggal)
|
|
{
|
|
$tanggal = $tanggal instanceof Carbon
|
|
? $tanggal->format('Y-m-d')
|
|
: $tanggal;
|
|
|
|
$transaksiSetelah = UangSaku::where('id_santri', $idSantri)
|
|
->where('tanggal_transaksi', '>=', $tanggal)
|
|
->orderBy('tanggal_transaksi')
|
|
->orderBy('created_at')
|
|
->orderBy('id')
|
|
->get();
|
|
|
|
foreach ($transaksiSetelah as $index => $trans) {
|
|
if ($index === 0) {
|
|
$prev = UangSaku::where('id_santri', $idSantri)
|
|
->where('tanggal_transaksi', '<', $tanggal)
|
|
->orderByDesc('tanggal_transaksi')
|
|
->orderByDesc('created_at')
|
|
->orderByDesc('id')
|
|
->first();
|
|
$trans->saldo_sebelum = $prev ? (float)$prev->saldo_sesudah : 0;
|
|
} else {
|
|
$trans->saldo_sebelum = (float)$transaksiSetelah[$index - 1]->saldo_sesudah;
|
|
}
|
|
|
|
$trans->saldo_sesudah = $trans->jenis_transaksi === 'pemasukan'
|
|
? $trans->saldo_sebelum + (float)$trans->nominal
|
|
: $trans->saldo_sebelum - (float)$trans->nominal;
|
|
|
|
$trans->saveQuietly();
|
|
}
|
|
}
|
|
} |