<?php

namespace App\Http\Controllers\Report;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use App\Model\Attendance;
use App\Model\Attendance_line;
use Excel;

class AmountPerInquiryController extends Controller
{
    public function __invoke(Request $request)
    {
        /* return 1; */
        $awal = $request->awal;
        $akhir = $request->akhir;


        $attendance = Attendance::with('checked')->whereBetween('tgl_absen', [$awal, $akhir])->orderBy('tgl_absen')->get();
        $attline = Attendance_line::with('attendance','man_power')->whereIn('attendance_id', $attendance->pluck('id'))->get();
        $attline = $attline->sortBy(function ($item) {
          return $item->man_power['nama'];
        }, SORT_NATURAL|SORT_FLAG_CASE);
        $skill = DB::table('upah_borongans')->get();


        Excel::load('excel/amount_per_inquiry.xls', function($reader) use ($skill, $attendance, $attline, $awal, $akhir){
            $reader->sheet('Sheet1', function($sheet) use ($skill, $attendance, $attline, $awal, $akhir){
                $row = 4;
                $collection = $attline->groupBy('keterangan')->sortKeys();
                foreach($collection as $key => $item) {
                  /* dd($item->toArray()); */
                    $sheet->setCellValue("A$row", $key);

                    $total_upah = 0;
                    foreach($item as $subitem) {
                        $day = \Carbon\Carbon::parse($subitem->attendance->tgl_absen)->format('l');
                        $hour = 8;
                        if($day == 'Saturday' || $day =='Sunday') {
                            $hour = $hour * 2;
                        }

                        $harga_borongan = 0;
                        foreach($skill as $val) {
                            if($val->class == $subitem->class) {
                                $harga_borongan = $val->harga_borongan;
                                break;
                            }
                        }

                        $total_upah += ($hour + $subitem->total_overtime) * $harga_borongan;
                    }



                  $sheet->setCellValue("D$row", $total_upah);
                  $sheet->setBorder("A$row:D$row", 'thin');
                  $row++;
                }
                $endOfRow = $row-1;

                $sheet->setCellValue("A$row", 'GRANDTOTAL');
                $sheet->mergeCells("A$row:C$row");
                $sheet->cells("A$row:C$row", function($cell) {
                    $cell->setAlignment('right');
                });
                $sheet->setcellValue("D$row", "=SUM(D4:D$endOfRow)");
                $sheet->setBorder("D$row:D$row", 'thin');
            });
        })
        ->setFilename('total_amount_per_inquiry' . $request->awal . ' - ' . $request->akhir)
        ->download('xls');
    }
}