78 lines
2.5 KiB
PHP
78 lines
2.5 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers;
|
|
|
|
use App\Http\Controllers\Controller;
|
|
use Illuminate\Http\Request;
|
|
use DB;
|
|
|
|
class DashboardController extends Controller
|
|
{
|
|
public function index()
|
|
{
|
|
$total_pengguna = DB::table('akun')
|
|
->where('role', 2)
|
|
->count('id');
|
|
$total_pengajuan_surat = DB::table('izin_pembelian_solar')->count()
|
|
+ DB::table('izin_usaha')->count()
|
|
+ DB::table('skck')->count()
|
|
+ DB::table('keterangan_kematian')->count()
|
|
+ DB::table('keterangan_kehilangan')->count();
|
|
$total_berita = DB::table('berita')->count('id');
|
|
$total_aspirasi_keluhan = DB::table('aspirasi')->count()
|
|
+ DB::table('keluhan')->count();
|
|
|
|
$tahun_pengajuan_surat = DB::select("
|
|
SELECT DISTINCT YEAR(created_at) AS year FROM (
|
|
SELECT created_at FROM izin_pembelian_solar
|
|
UNION ALL
|
|
SELECT created_at FROM izin_usaha
|
|
UNION ALL
|
|
SELECT created_at FROM skck
|
|
UNION ALL
|
|
SELECT created_at FROM keterangan_kematian
|
|
UNION ALL
|
|
SELECT created_at FROM keterangan_kehilangan
|
|
) AS combined_data
|
|
ORDER BY year DESC
|
|
");
|
|
|
|
return view('Dashboard', [
|
|
'total_pengguna' => $total_pengguna,
|
|
'total_pengajuan_surat' => $total_pengajuan_surat,
|
|
'total_berita' => $total_berita,
|
|
'total_aspirasi_keluhan' => $total_aspirasi_keluhan,
|
|
'tahun_pengajuan_surat' => $tahun_pengajuan_surat]);
|
|
}
|
|
|
|
public function getMonthlyData(Request $request)
|
|
{
|
|
$selectedYear = $request->input('year', date('Y'));
|
|
|
|
$data = DB::select("
|
|
SELECT MONTH(created_at) AS bulan, COUNT(*) AS jumlah
|
|
FROM (
|
|
SELECT created_at FROM izin_pembelian_solar
|
|
UNION ALL
|
|
SELECT created_at FROM izin_usaha
|
|
UNION ALL
|
|
SELECT created_at FROM skck
|
|
UNION ALL
|
|
SELECT created_at FROM keterangan_kematian
|
|
UNION ALL
|
|
SELECT created_at FROM keterangan_kehilangan
|
|
) AS combined_data
|
|
WHERE YEAR(created_at) = ?
|
|
GROUP BY bulan
|
|
ORDER BY bulan
|
|
", [$selectedYear]);
|
|
|
|
$monthlyData = array_fill(1, 12, 0);
|
|
|
|
foreach ($data as $row) {
|
|
$monthlyData[$row->bulan] = $row->jumlah;
|
|
}
|
|
|
|
return response()->json(array_values($monthlyData));
|
|
}
|
|
} |