<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Admin\Vsaku;
use App\Models\VPengusul;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Session;

class LaporanController extends Controller
{

    public function index(Request $request) {

        $filter_periode = DB::table('ms_periode')->orderBy('periode','asc')->get();

        Session::put('periode', $request->periode); 

        if(empty(Session::get('periode'))) {

            $periode = DB::table('ms_periode')->where('is_aktif', 1)->first();
            Session::put('periode', $periode->periode);

        } else {
            $periode = DB::table('ms_periode')->where('periode', Session::get('periode'))->first();

        }

        // Ambil data dari connection VPengusul
        $pengusul = VPengusul::whereBetween('tanggal_keputusan', [
                $periode->tanggal_awal, 
                $periode->tanggal_akhir
            ])
            ->get(['id_pengajuan', 'tanggal_keputusan']);

        // Ambil ID pengajuan dari VPengusul
        $idPengajuan = $pengusul->pluck('id_pengajuan');

        // Query di database utama
        $rs = DB::table('tr_penelaah as p')
            ->join('tr_pengajuan as pg', 'pg.id', '=', 'p.id_pengajuan')
            ->whereIn('pg.id_pengajuan', $idPengajuan)
            ->select(
                'p.nama',
                'p.nip',
                'p.unit',
                DB::raw('COUNT(pg.id_pengajuan) as jumlah')
            )
            ->groupBy('p.nama', 'p.nip', 'p.unit')
            ->get();

        $data = [
            'rs' => $rs,
            'periode' => $filter_periode
        ];

        return view('laporan.index', $data);
    }

    public function download(Request $request) {

        if(empty(Session::get('periode'))) {

            $periode = DB::table('ms_periode')->where('is_aktif', 1)->first();
            Session::put('periode', $periode->periode);

        } else {
            $periode = DB::table('ms_periode')->where('periode', Session::get('periode'))->first();

        }

        $data['nama_file'] = 'Laporan Reviewer Sipeka Periode '. $periode->periode;

        $data['rs'] = DB::table('v_saku_new')
            ->select('nama', 'nip', 'unit', DB::raw('COUNT(id_pengajuan) as jumlah'))
            ->whereBetween('tanggal_keputusan', [$periode->tanggal_awal, $periode->tanggal_akhir])
            ->groupBy('nama', 'nip', 'unit')
            ->get();

        return view('laporan.excel', $data);
    }
}
