<?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 XuatKetQuaHBCAP3 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(SUM(CASE WHEN form_diem.Loai IN ('TBM1-10', 'TBM2-10', 'TBM3-10') THEN form_diem.Diem ELSE 0 END), 1) AS DiemLop10"),
                DB::raw("ROUND(SUM(CASE WHEN form_diem.Loai IN ('TBM1-11', 'TBM2-11', 'TBM3-11') THEN form_diem.Diem ELSE 0 END), 1) AS DiemLop11"),
                DB::raw("ROUND(SUM(CASE WHEN form_diem.Loai IN ('TBM1-12', 'TBM2-12', 'TBM3-12') THEN form_diem.Diem ELSE 0 END), 1) AS DiemLop12"),
                DB::raw("ROUND(SUM(CASE WHEN form_diem.Loai IN ('TBM1-10', 'TBM2-10', 'TBM3-10', 'TBM1-11', 'TBM2-11', 'TBM3-11', 'TBM1-12', 'TBM2-12', 'TBM3-12') THEN form_diem.Diem ELSE 0 END) / 3, 1) AS TongDiemTB"),
                DB::raw("CASE WHEN form.KetQua = 1 THEN 'Đạt' ELSE 'Chưa Đạt' END AS KetQua")
            )
            ->where('form.MaDotXetTuyen', $this->selectDot)
            ->where('form.Loai','HBCAP3')
            ->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 [
            'Mã Form',
            '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ý',
            'Lớp 10',
            'Lớp 11',
            'Lớp 12',
            'Điểm TB',
            'Kết Quả',
        ];
    }
}
