60 lines
2.3 KiB
PHP
60 lines
2.3 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers;
|
|
|
|
use App\Models\ReportingCycle;
|
|
use App\Models\Submission;
|
|
use App\Models\SubmissionResult;
|
|
use Illuminate\Http\Request;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
class ReportController extends Controller
|
|
{
|
|
public function index(Request $request)
|
|
{
|
|
abort_unless(auth()->user()->can('laporan.lihat'), 403);
|
|
|
|
$cycles = ReportingCycle::orderByDesc('renewal_year')->get();
|
|
$cycleId = $request->input('cycle');
|
|
|
|
$resultsQuery = SubmissionResult::query()
|
|
->join('submissions', 'submissions.id', '=', 'submission_results.submission_id')
|
|
->join('data_centres', 'data_centres.id', '=', 'submissions.data_centre_id')
|
|
->join('reporting_cycles', 'reporting_cycles.id', '=', 'submissions.reporting_cycle_id')
|
|
->whereNull('submissions.deleted_at');
|
|
|
|
if ($cycleId) {
|
|
$resultsQuery->where('submissions.reporting_cycle_id', $cycleId);
|
|
}
|
|
|
|
// Jadual hasil karbon mengikut pusat data + kitaran.
|
|
$rows = (clone $resultsQuery)
|
|
->select(
|
|
'data_centres.nama_pusat_data',
|
|
'reporting_cycles.reporting_year',
|
|
'submission_results.result_token',
|
|
'submission_results.value'
|
|
)
|
|
->orderBy('reporting_cycles.reporting_year')
|
|
->orderBy('data_centres.nama_pusat_data')
|
|
->get()
|
|
->groupBy(fn ($r) => $r->nama_pusat_data.'|'.$r->reporting_year);
|
|
|
|
// Trend jumlah karbon (A) mengikut tahun pelaporan.
|
|
$trend = (clone $resultsQuery)
|
|
->where('submission_results.result_token', 'A')
|
|
->select('reporting_cycles.reporting_year', DB::raw('SUM(submission_results.value) as jumlah'))
|
|
->groupBy('reporting_cycles.reporting_year')
|
|
->orderBy('reporting_cycles.reporting_year')
|
|
->pluck('jumlah', 'reporting_year');
|
|
|
|
// Pecahan status serahan.
|
|
$statusBreakdown = Submission::query()
|
|
->when($cycleId, fn ($q) => $q->where('reporting_cycle_id', $cycleId))
|
|
->select('status', DB::raw('count(*) as jumlah'))
|
|
->groupBy('status')->pluck('jumlah', 'status');
|
|
|
|
return view('reports.index', compact('cycles', 'cycleId', 'rows', 'trend', 'statusBreakdown'));
|
|
}
|
|
}
|