<?php

namespace App\Http\Controllers\Export;

use DB;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;

class ExportJamController implements FromView
{
    protected $dari;
    protected $sampai;
    protected $cluster;

    function __construct($dari, $sampai, $cluster)
    {
        $this->dari = $dari;
        $this->sampai = $sampai;
        $this->cluster = $cluster;
    }

    public function view(): View
    {
        error_reporting(E_ALL ^ (E_NOTICE | E_WARNING));
        $dari  = date('Y-m-d', strtotime($this->dari));
        $sampai  = date('Y-m-d', strtotime($this->sampai));
        $cluster = $this->cluster;

        $data = DB::select('select s.nik, s.nama,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "01" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h1in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "01" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h1out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "02" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h2in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "02" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h2out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "03" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h3in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "03" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h3out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "04" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h4in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "04" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h4out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "05" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h5in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "05" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h5out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "06" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h6in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "06" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h6out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "07" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h7in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "07" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h7out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "08" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h8in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "08" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h8out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "09" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h9in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "09" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h9out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "10" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h10in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "10" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h10out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "11" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h11in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "11" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h11out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "12" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h12in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "12" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h12out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "13" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h13in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "13" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h13out,
        
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "14" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h14in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "14" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h14out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "15" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h15in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "15" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h15out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "16" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h16in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "16" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h16out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "17" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h17in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "17" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h17out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "18" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h18in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "18" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h18out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "19" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h19in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "19" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h19out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "20" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h20in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "20" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h20out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "21" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h21in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "21" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h21out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "22" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h22in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "22" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h22out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "23" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h23in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "23" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h23out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "24" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h24in, 
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "24" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h24out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "25" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h25in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "25" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h25out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "26" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h26in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "26" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h26out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "27" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h27in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "27" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h27out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "28" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h28in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "28" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h28out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "29" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h29in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "29" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h29out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "30" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h30in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "30" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h30out,

        MAX(CASE WHEN date_format(b.tanggal, "%d") = "31" THEN date_format(b.berangkat, "%H.%i") ELSE "" END ) as h31in,
        MAX(CASE WHEN date_format(b.tanggal, "%d") = "31" THEN date_format(b.pulang, "%H.%i") ELSE "" END ) as h31out
        
        from users s
        LEFT join tb_presensi b on s.id = b.id_user
        LEFT join tb_jammasuk j on s.id = j.id_user
        where b.tanggal between "' . $dari . '" and "' . $sampai . '" and s.cluster = "' . $cluster . '"
        group by s.id, b.id_user
        order by s.nama ASC');
        //dd($data);
        return view('admin.laporan.export-rekap-jam', [
            'detail' => $data
        ]);
    }
}
