database-pertani-web/app/Http/Controllers/Web/Program/RealisasiController.php

<?php

namespace App\Http\Controllers\Web\Program;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Gate;
use Illuminate\View\View;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;

use App\Models\Program\Framework;
use App\Models\Program\Realisasi;

class RealisasiController extends Controller
{
    public function index(Request $request): View
    {
        if (!Gate::any(['super_admin', 'admin'])) {
            abort(403);
        }

        $framework = Framework::when($request->has('frameworkid'), function ($query) use ($request) {
            $query->where('id', $request->frameworkid);
        })->latest()->first();
        if ($framework == null) {
            abort(404);
        }
        
        $ringkasanIsiRealisasi = DB::select('select count(pt.id) count_target, sum(case when rl.id > 0 then 1 else 0 end) count_realisasi
            from program_target pt 
            inner join program_aktivitas pa on pt.aktivitas_id = pa.id 
            inner join program_transformasi ptr on pa.transformasi_id = ptr.id 
            inner join program_framework pf on ptr.framework_id = pf.id
            left join 
            (select target_id, max(id) id from program_realisasi pr 
            group by target_id) rl on pt.id = rl.target_id
            where 
            pt.deleted_at is null
            and pa.deleted_at is null
            and ptr.deleted_at is null
            and pf.id = ?
        ', [$framework->id]);
        $ringkasanIsiRealisasi = collect($ringkasanIsiRealisasi)->map(function ($item) {
            $item->persentase = $item->count_target > 0 ? round(($item->count_realisasi / $item->count_target) * 100, 2) : 0;
            return $item;
        })->first();

        $ringkasanCapaianRealisasi = DB::select("select round(avg(percent_realisasi)::numeric * 100, 2) persentase, 
            sum(case when percent_realisasi >= 0.8 then 1 else 0 end) capaian_80,
            sum(case when percent_realisasi >= 0.5 and percent_realisasi < 0.8 then 1 else 0 end) capaian_50_80,
            sum(case when percent_realisasi < 0.5 then 1 else 0 end) capaian_50
            from 
            (select pt.id target_id, pr.id realisasi_id, 
            pt.tipe,
            case 
                when pt.tipe = 'integer' then pt.target_integer
                when pt.tipe = 'float' then pt.target_float
                when pt.tipe = 'boolean' then pt.target_boolean::integer
            end target,
            case 
                when pt.tipe = 'integer' then pr.realisasi_integer  
                when pt.tipe = 'float' then pr.realisasi_float  
                when pt.tipe = 'boolean' then pr.realisasi_boolean::integer
            end realisasi,
            case 
                when pt.tipe = 'integer' then pr.realisasi_integer::float / pt.target_integer::float
                when pt.tipe = 'float' then pr.realisasi_float / pt.target_float
                when pt.tipe = 'boolean' then pr.realisasi_boolean::integer / pt.target_boolean::integer
            end percent_realisasi
            from program_target pt 
            inner join program_aktivitas pa on pt.aktivitas_id = pa.id 
            inner join program_transformasi ptr on pa.transformasi_id = ptr.id 
            inner join program_framework pf on ptr.framework_id = pf.id
            left join 
            (select target_id, max(id) id from program_realisasi pr 
            group by target_id) rl on pt.id = rl.target_id
            left join program_realisasi pr on rl.id = pr.id and pr.target_id = pt.id 
            where 
            pt.deleted_at is null
            and pa.deleted_at is null
            and ptr.deleted_at is null
            and pf.id = ?) v", 
            [$framework->id]);
        $ringkasanCapaianRealisasi = collect($ringkasanCapaianRealisasi)->first();

        $ringkasanVerifikasi = DB::select("select count(pt.id) count_target, 
            sum(case when count_verifikasi > 0 then 1 else 0 end) count_verifikasi, 
            sum(case when count_verifikasi = 0 or count_verifikasi is null then 1 else 0 end) count_unverifikasi
            from program_target pt 
            inner join program_aktivitas pa on pt.aktivitas_id = pa.id 
            inner join program_transformasi ptr on pa.transformasi_id = ptr.id 
            inner join program_framework pf on ptr.framework_id = pf.id
            left join 
            (select target_id, max(id) id from program_realisasi pr 
            group by target_id) rl on pt.id = rl.target_id
            left join 
            (select realisasi_id, count(pv.id) count_verifikasi from program_verifikasi pv
            group by realisasi_id) rv
            on rv.realisasi_id = rl.id
            where 
            pt.deleted_at is null
            and pa.deleted_at is null
            and ptr.deleted_at is null
            and pf.id = ?", 
            [$framework->id]);
        $ringkasanVerifikasi = collect($ringkasanVerifikasi)->map(function ($item) {
            $item->persentase = $item->count_target > 0 ? round(($item->count_verifikasi / $item->count_target) * 100, 2) : 0;
            return $item;
        })->first();

        return view('program.realisasi.index', compact('framework', 'ringkasanIsiRealisasi', 'ringkasanCapaianRealisasi', 'ringkasanVerifikasi'));
    }

    public function create()
    {
        //
    }

    public function store(Request $request): JsonResponse
    {
        if (!Gate::any(['super_admin', 'admin'])) {
            abort(403);
        }

        $validated = $request->validate([
            'target_id' => ['required', 'integer', 'exists:program_target,id'],
            'realisasi_integer' => ['nullable', 'integer'],
            'realisasi_float' => ['nullable', 'numeric'],
            'realisasi_boolean' => ['nullable', 'boolean'],
        ]);

        $realisasi = Realisasi::create([...$validated, 
            'created_by' => auth()->id(), 
            'updated_by' => auth()->id()
        ]);

        return response()->json([
            'status' => 'success',
            'message' => 'Target berhasil ditambahkan.'
        ], 201);
    }

    public function show(string $id)
    {
        if (!Gate::any(['super_admin', 'admin'])) {
            abort(403);
        }

        $realisasi = Realisasi::with('target', 'target.aktivitas', 'target.aktivitas.transformasi', 'verifikasi')
            ->find($id);
        if (!$realisasi) {
            return response()->json([
                'status' => 'error',
                'message' => 'Data realisasi tidak ditemukan.'
            ], 404);
        }

        return response()->json([
            'status' => 'success',
            'data' => $realisasi
        ]);
    }

    public function edit(string $id)
    {
        //
    }

    public function update(Request $request, string $id): JsonResponse
    {
        if (!Gate::any(['super_admin', 'admin'])) {
            abort(403);
        }

        $realisasi = Realisasi::find($id);
        if (!$realisasi) {
            return response()->json([
                'status' => 'error',
                'message' => 'Data realisasi tidak ditemukan.'
            ], 404);
        }

        $validated = $request->validate([
            'target_id' => ['required', 'integer', 'exists:program_target,id'],
            'realisasi_integer' => ['nullable', 'integer'],
            'realisasi_float' => ['nullable', 'numeric'],
            'realisasi_boolean' => ['nullable', 'boolean'],
        ]);

        $realisasi->update([...$validated,
            'updated_by' => auth()->id(),
        ]);

        return response()->json([
            'status' => 'success',
            'message' => 'Target berhasil diperbarui.'
        ], 200);
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(string $id)
    {
        //
    }
}