<?php

namespace App\Exports;

use App\Models\form;
use Maatwebsite\Excel\Concerns\FromCollection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;


class XuatHocBaDiemMax implements FromCollection, WithHeadings
{
    protected $selectDot;

    public function __construct($selectDot)
    {
        $this->selectDot = $selectDot;
    }

    public function collection()
    {
        $data = DB::table('form')
            ->join('nganhhoc', 'form.MaNganhHoc', '=', 'nganhhoc.MaNganhHoc')
            ->join('truong', 'form.MaTruong', '=', 'truong.MaTruong')
            ->leftJoin('form_diem', 'form.MaForm', '=', 'form_diem.MaForm')
            ->select(
                'form.HoTen',
                DB::raw("CASE WHEN form.GioiTinh = 1 THEN 'Nam' ELSE 'Nữ' END AS GioiTinh"),
                'form.NgaySinh',
                'form.CCCD',
                'form.Email',
                'form.SoDienThoai',
                'form.DiaChi',
                'truong.TenTruong',
                'nganhhoc.TenNganhHoc',
                'form.NgayDangKy',
                DB::raw("ROUND(MAX(CASE WHEN form_diem.Loai IN ('M1HK1-10', 'M1HK2-10', 'M1HK1-11', 'M1HK2-11', 'M1HK1-12') THEN form_diem.Diem ELSE 0 END), 1) AS DiemMaxM1"),
                DB::raw("ROUND(MAX(CASE WHEN form_diem.Loai IN ('M2HK1-10', 'M2HK2-10', 'M2HK1-11', 'M2HK2-11', 'M2HK1-12') THEN form_diem.Diem ELSE 0 END), 1) AS DiemMaxM2"),
                DB::raw("ROUND(MAX(CASE WHEN form_diem.Loai IN ('M3HK1-10', 'M3HK2-10', 'M3HK1-11', 'M3HK2-11', 'M3HK1-12') THEN form_diem.Diem ELSE 0 END), 1) AS DiemMaxM3"),
                DB::raw("ROUND((MAX(CASE WHEN form_diem.Loai IN ('M1HK1-10', 'M1HK2-10', 'M1HK1-11', 'M1HK2-11', 'M1HK1-12') THEN form_diem.Diem ELSE 0 END) +
                                MAX(CASE WHEN form_diem.Loai IN ('M2HK1-10', 'M2HK2-10', 'M2HK1-11', 'M2HK2-11', 'M2HK1-12') THEN form_diem.Diem ELSE 0 END) +
                                MAX(CASE WHEN form_diem.Loai IN ('M3HK1-10', 'M3HK2-10', 'M3HK1-11', 'M3HK2-11', 'M3HK1-12') THEN form_diem.Diem ELSE 0 END)), 1) AS DiemTB"),
                DB::raw("CASE WHEN form.KetQua = 1 THEN 'Đạt' ELSE 'Chưa Đạt' END AS KetQua")
            )
            ->where('form.MaDotXetTuyen', $this->selectDot)
            ->where('form.Loai', 'HBDIEMMAX')
            ->groupBy('form.MaForm')
            ->orderBy('form.MaForm', 'asc')
            ->get();

            $index = 1;

            return $data->map(function ($item) use (&$index) {
                $itemArray = (array) $item;
                $itemArray['STT'] = $index++;
                $itemArray = ['STT' => $itemArray['STT']] + $itemArray;
                return $itemArray;
        });
    }


    

    
    public function headings(): array
    {
        return [
            'STT',
            'Họ và Tên',
            'Giới Tính',
            'Ngày Sinh',
            'CCCD',
            'Email',
            'Số Điện Thoại',
            'Địa Chỉ',
            'Tên trường',
            'Ngành đăng ký',
            'Ngày đăng ký',
            'Điểm MAX Môn 1',
            'Điểm MAX Môn 2',
            'Điểm MAX Môn 3',
            'Điểm TB',
            'Kết Quả',
        ];
    }
}
