<?php

namespace App\Http\Controllers;

use App\Http\Requests\EditBookingRequest;
use App\Models\Student;
use App\Models\Branch;
use App\Models\Course;
use App\Models\Enroll;
use App\Models\Module;
use App\Models\Billing;
use App\Models\Vehicle;
use App\Models\VehicleBooking;
use App\Models\Task;
use Illuminate\Http\Request;
use \App\Http\Requests\StoreStudentRequest;
use \App\Http\Requests\UpdateStudentRequest;
use PDF;
use Illuminate\Support\Facades\Gate;

use DB;

class StudentController extends Controller
{
    protected $LogController;
    public function __construct(LogController $LogController)
    {
        $this->LogController = $LogController;
    }

    public function run_task()
    {
        // Task check current student
        $task = Task::where('task', '=', 'student')->first();
        if(!empty($task)) {
            if( $task->run_date != date('Y-m-d') ) {  // Never run task check student for today before
                $task->run_date = date('Y-m-d');
                $task->save();
                $this->check_current_student();
            }
        } else {
            $new_task = new Task();
            $new_task->task = "student";
            $new_task->run_date = date('Y-m-d');
            $new_task->save();
            $this->check_current_student();
        }
    }

    public function check_current_student()
    {
        // Update enroll set is_active = 0 when exam_date < now date
        // $students = Student::leftJoin('enrolls', 'enrolls.student_id', '=', 'students.id')->where('students.is_active', '=', '1')->get();
        // for($a=0; $a<count($students); $a++) {

        // }

        Enroll::where('is_active', '=', '1')->whereNotNull('exam_date')->whereRaw("(exam_date <= CURRENT_DATE() - INTERVAL 1 MONTH)")->update(['is_active'=>'0']);
        //Enroll::where('is_active', '=', '1')->whereNotNull('exam_date')->where('exam_date', '<', date('Y-m-d'))->update(['is_active'=>'0']);
        // Update enroll for student who not have exam
        $no_exam = Enroll::select('enrolls.id as eid', 'hour')
                            ->leftJoin('courses', 'courses.id', '=', 'enrolls.course_id')
                            ->where('enrolls.is_active', '=', '1')
                            ->whereNull('exam_date')->get();
        if(count($no_exam)!=0) {
            for($j=0; $j<count($no_exam); $j++) {
                $no_exam_hour = $no_exam[$j]->hour;
                $hour = VehicleBooking::selectRaw('sum(endtime - starttime)/10000 as sumhour')->where('enroll_id', '=', $no_exam[$j]->eid)->first();
                $lastDate = VehicleBooking::select('date')->where('enroll_id', '=', $no_exam[$j]->eid)->orderBy('date', 'desc')->first();
                if( ($no_exam_hour <= $hour->sumhour) && ($lastDate->date < date('Y-m-d')) ) {
                    $inactive = Enroll::find($no_exam[$j]->eid);
                    $inactive->is_active = '0';
                    $inactive->save();
                }
            }
        }

        $check_student = Student::where('is_active', '=', '1')->get();
        for($i=0; $i<count($check_student); $i++) {
            $sid = $check_student[$i]->id;
            $enroll = Enroll::where('student_id', '=', $sid)->where('is_active', '=', '1')->get();
            if(count($enroll)==0) {
                $inactive = Student::find($sid);
                $inactive->is_active = '0';
                $inactive->save();
            }
        }
    }
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $this->run_task();

        $bookings = VehicleBooking::leftJoin('vehicles', 'vehicle_bookings.vehicle_id', '=', 'vehicles.id')->where('enroll_id', '=' , '11')->orderBy('vehicle_bookings.date', 'asc')->get();
        //$students = Student::all();
        $students = Student::select('students.id as sid', 'students.*', 'enrolls.id as eid', 'enrolls.*', 'courses.*', 'b.*')
                            ->leftJoin('enrolls', 'students.id', '=', 'enrolls.student_id')
                            ->leftJoin('courses', 'courses.id', '=', 'enrolls.course_id')
                            ->leftJoin(Billing::raw('(select sum(amount) as paid, enroll_id from billings group by enroll_id) as b'), function($join){
                                $join->on('b.enroll_id', '=', 'enrolls.id');
                            })
                            ->where('students.is_active', '=', '1')
                            ->orderBy('code_month', 'asc')
                            ->orderBy('code_branch', 'asc')
                            ->orderBy('code_no', 'asc')
                            ->get();

            return view('student.index')->with([
                'page_title' => 'การจัดการนักเรียน',
                'students' => $students,
                'bookings' => $bookings
            ]);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        $branchs = Branch::where('is_active', '=', '1')->orderBy('code')->get();
        $courses = Course::where('is_active', '=', '1')->orderBy('name')->get();

        return view('student.create')->with([
            'page_title' => 'เพิ่มนักเรียน',
            'branchs' => $branchs,
            'courses' => $courses
        ]);
    }

    public function create_booking($id)
    {

        // $booking = VehicleBooking::select('vehicle_id')->whereDate('date', '=', '2020-10-19')->whereTime('endtime', '>', '09:00:00')->get()->toArray();
        // $vehicles = Vehicle::where('gear', '=', 'ออโต้')->whereNotIn('id', $booking)->orderBy('id')->get();

       // $vehicles = Vehicle::orderBy('id')->get();
        $enroll = Enroll::select('enrolls.id as eid', 'courses.type as courseType', 'name', 'hour', 'license_test', 'enrolls.type as driveType')
        ->leftJoin('students', 'students.id', '=', 'enrolls.student_id')
        ->leftJoin('courses', 'courses.id', '=', 'enrolls.course_id')
        ->where('enrolls.student_id', '=' ,$id)->first();

        return view('student.create_booking')->with([
            'page_title' => 'เพิ่มนักเรียน (ตารางนัดหมาย)',
            'enroll' => $enroll,
           // 'vehicles' => $vehicles,
        ]);
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(StoreStudentRequest $request)
    {
        // students table
        $date = explode('/', strip_tags($request->registerDate));
        $month = $date[1];
        $year = $date[2];

        $code = Student::select('code_no')
                            ->whereMonth('register_date','=',$month)
                            ->whereYear('register_date','=',$year)
                            ->where('code_branch','=',$request->branch)
                            ->orderBy('code_no','desc')->first();
        if (!empty($code)) {
            $code = $code->code_no + 1;
        } else {
            $code = '1';
        }
        $student = new Student();
        $student->code_no = $code;
        $student->code_month = $month;
        $student->code_branch = $request->branch;
        $student->title = $request->title;
        $student->gender = getGender($request->title);
        $student->firstname = strip_tags($request->firstname);
        $student->lastname = strip_tags($request->lastname);
        $student->nickname = strip_tags($request->nickname);
        $student->idcard = strip_tags($request->idCard);
        $student->birthdate = ($request->birthdate) ? formatDateToDB(strip_tags($request->birthdate)) : NULL ;
        $student->race = strip_tags($request->race);
        $student->nationality = strip_tags($request->nationality);
        $student->religion = strip_tags($request->religion);
        $student->address = strip_tags($request->address);
        $student->district = strip_tags($request->district);
        $student->amphoe = strip_tags($request->amphoe);
        $student->province = strip_tags($request->province);
        $student->phone_1 = strip_tags($request->phone1);
        $student->phone_2 = strip_tags($request->phone2);
        $student->emergency_name = strip_tags($request->refName);
        $student->emergency_phone = strip_tags($request->refPhone);
        $student->license_status = $request->license;
        $student->is_active = '1';
        $student->know_school = strip_tags($request->knowSchool);
        $student->note = strip_tags($request->note);
        $student->register_date = ($request->registerDate) ? formatDateToDB(strip_tags($request->registerDate)) : NULL ;
        $student->save();
        $sid = $student->id;

        // Log
        $description = "เพิ่มนักเรียนชื่อ:". $request->title.strip_tags($request->firstname)." ".strip_tags($request->lastname).", เลขที่:".$code."/".$month."/".$request->branch;
        $this->LogController->log('การจัดการนักเรียน', 'เพิ่มข้อมูล', $description);

        // 1st course register
        $cid = explode('-', $request->course);
        // receuve format (id-price-hour)
        $coursePrice = $cid[1];
        $courseHour = $cid[2];
        $cid = $cid[0];

        $enroll = new Enroll();
        $enroll->student_id = $sid;
        $enroll->course_id = $cid;
        $enroll->branch_id = $request->branch;
        $enroll->purpose = $request->purpose;
        $enroll->date = ($request->registerDate) ? formatDateToDB(strip_tags($request->registerDate)) : NULL ;
        $enroll->course_cost = $coursePrice;
        $enroll->discount = strip_tags(abs($request->discountAmount));
        $enroll->discount_reason = strip_tags($request->discount);
        $enroll->total_cost = $coursePrice - strip_tags(abs($request->discountAmount));
        $enroll->exam_date = ($request->examDate) ? formatDateToDB(strip_tags($request->examDate)) : NULL ;
        $enroll->exam_time = ($request->examTime) ? $request->examTime : NULL ;
        $enroll->save();
        $eid = $enroll->id;

        // billings table
        $billing = new Billing();
        $billing->enroll_id = $eid;
        $billing->amount = strip_tags(abs($request->pay));
        $billing->date = ($request->registerDate) ? formatDateToDB(strip_tags($request->registerDate)) : NULL ;
        $billing->method = $request->method;
        $billing->time = $request->payTime;
        $billing->save();

        // vehicle_booking table
        if($request->purpose == "เก็บชั่วโมงเพื่อสอบ") {
            $drive = "ไม่ขับ";
        } else {
            $drive = "ขับ";
        }
        for($d=1; $d<5; $d++) {
            $driveID = 'drive'.$d;
            if($request->$driveID == '1') {
                if( $request->purpose != "เก็บชั่วโมงเพื่อสอบ" ) {
                    $driveHour = 'hour'.$d;
                    $drive = getModuleDrive($request->$driveHour);
                }

                $vehicleID = 'vehicle'.$d;
                $gearID = 'gear'.$d;
                $dateID = 'date'.$d;
                $starttimeID = 'starttime'.$d;
                $endtimeID = 'endtime'.$d;
                $hourID = 'hour'.$d;

                $booking = new VehicleBooking();
                $booking->enroll_id = $eid;
                $booking->vehicle_id = $request->$vehicleID;
                $booking->drive = $drive;
                $booking->gear = $request->$gearID;
                $booking->date = ($request->$dateID) ? formatDateToDB(strip_tags($request->$dateID)) : NULL ;
                $booking->starttime = $request->$starttimeID.':00';
                $booking->endtime = $request->$endtimeID.':00';
                $booking->course_module = getModule($request->$hourID);
                $booking->save();
            }
        }

        return redirect()->route('student.index')->with('feedback','เพิ่มนักเรียนเรียบร้อย');
    }

    /**
     * Display the specified resource.
     *
     * @param  \App\Models\Student  $student
     * @return \Illuminate\Http\Response
     */
    public function show(Student $student)
    {
        $data = Student::select('students.id as sid', 'students.*', 'enrolls.id as eid', 'enrolls.*',  'courses.type as courseType', 'courses.*', 'b.*', 'branchs.name as branchName')
                            ->leftJoin('enrolls', 'students.id', '=', 'enrolls.student_id')
                            ->leftJoin('courses', 'courses.id', '=', 'enrolls.course_id')
                            ->leftJoin(Billing::raw('(select sum(amount) as paid, enroll_id from billings group by enroll_id) as b'), function($join){
                                $join->on('b.enroll_id', '=', 'enrolls.id');
                            })
                            ->leftJoin('branchs', 'branchs.code', '=', 'students.code_branch')
                            ->where('students.id', '=', $student->id)->first();

        $bookings = VehicleBooking::select('*', 'vehicle_bookings.id as vbid', 'vehicles.id as vid', 'course_modules.module', 'course_modules.module_hour', 'vehicles.*')
                                    ->leftJoin('vehicles', 'vehicle_bookings.vehicle_id', '=', 'vehicles.id')
                                    ->leftJoin('course_modules', 'vehicle_bookings.course_module', '=', 'course_modules.id')
                                    ->where('enroll_id', '=' , $data->eid)
                                    ->orderBy('vehicle_bookings.date', 'asc')->get();

        $bookHour = VehicleBooking::leftJoin('course_modules', 'vehicle_bookings.course_module', '=', 'course_modules.id')
                                    ->where('enroll_id', '=' , $data->eid)
                                    ->sum('course_modules.module_hour');

        $courses = Course::where('is_active', '=', '1')->orderBy('name')->get();
        $modules = Module::where('course_id', '=' ,$data->course_id)->get();

        return view('student.show')->with([
            'page_title' => 'ข้อมูลนักเรียน',
            'student' => $data,
            'bookings' => $bookings,
            'courses' => $courses,
            'modules' => $modules,
            'book_hour' => $bookHour,
        ]);
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  \App\Models\Student  $student
     * @return \Illuminate\Http\Response
     */
    public function edit(Student $student)
    {
        $branchs = Branch::orderBy('code')->get();
        $data = Student::where('students.id', '=', $student->id)->first();
        $age = calAge($student->birthdate);
        return view('student.edit')->with([
            'page_title' => 'แก้ไขข้อมูลนักเรียน',
            'student' => $data,
            'branchs' => $branchs,
            'age' => $age,
        ]);
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \App\Models\Student  $student
     * @return \Illuminate\Http\Response
     */
    public function update(UpdateStudentRequest $request, Student $student)
    {
        // $date = explode('-', formatDateToDB(strip_tags($request->registerDate)));
        // $month = $date[1];

        // $student->code_month = $month;
        // $student->code_branch = $request->branch;
        $student->title = $request->title;
        $student->gender = getGender($request->title);
        $student->firstname = strip_tags($request->firstname);
        $student->lastname = strip_tags($request->lastname);
        $student->nickname = strip_tags($request->nickname);
        $student->idcard = strip_tags($request->idCard);
        $student->birthdate = formatDateToDB(strip_tags($request->birthdate));
        $student->race = strip_tags($request->race);
        $student->nationality = strip_tags($request->nationality);
        $student->religion = strip_tags($request->religion);
        $student->address = strip_tags($request->address);
        $student->district = strip_tags($request->district);
        $student->amphoe = strip_tags($request->amphoe);
        $student->province = strip_tags($request->province);
        $student->phone_1 = strip_tags($request->phone1);
        $student->phone_2 = strip_tags($request->phone2);
        $student->emergency_name = strip_tags($request->refName);
        $student->emergency_phone = strip_tags($request->refPhone);
        $student->license_status = $request->license;
        $student->know_school = strip_tags($request->knowSchool);
        $student->note = strip_tags($request->note);
        $student->register_date = formatDateToDB(strip_tags($request->registerDate));
        $student->save();

        // Log
        $description = "แก้ไขข้อมูลนักเรียนชื่อ:". $request->title.strip_tags($request->firstname)." ".strip_tags($request->lastname);
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        if($student->is_active=='1') {
            return redirect()->route('student.index')->with('feedback','แก้ไขข้อมูลนักเรียนเรียบร้อย');
        } else {
            return redirect()->route('student.graduated')->with('feedback','แก้ไขข้อมูลนักเรียนที่เรียนจบแล้วเรียบร้อย');
        }

    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  \App\Models\Student  $student
     * @return \Illuminate\Http\Response
     */
    public function destroy(Student $student)
    {
        $student->is_active = '0';
        $student->save();

        $enroll = Enroll::where('student_id', '=', $student->id)->first();
        $enroll->is_active = '0';
        $enroll->save();

        VehicleBooking::leftJoin('enrolls', 'enrolls.id', '=', 'vehicle_bookings.enroll_id')->where('enrolls.student_id', '=', $student->id)->delete();

        // Log
        $description = "ลบข้อมูลนักเรียนชื่อ:". $student->title.strip_tags($student->firstname)." ".strip_tags($student->lastname).", เลขที่:".$student->code."/".$student->month."/".$student->branch;
        $this->LogController->log('การจัดการนักเรียน', 'ลบข้อมูล', $description);

        return redirect()->route('student.index')->with('feedback','ลบข้อมูลนักเรียนเรียบร้อย');
    }

    public function payment($id)
    {

        $enroll = Enroll::select('course_cost', 'discount', 'total_cost', 'cour')
                        ->leftJoin('courses', 'courses.id' , '=', 'enrolls.course_id')
                        ->where('student_id', '=', $id)->first();

        $billings = Billing::select('amount', 'billings.date'  )
                    ->leftJoin('enrolls', 'enrolls.id', '=', 'billings.enroll_id')
                    ->leftJoin('students', 'students.id', '=', 'enrolls.student_id')
                    ->where('students.id', '=' ,$id)->get();

        return view('student.payment')->with([
            'page_title' => 'ชำระเงิน',
            'enroll' => $enroll,
            'billings' => $billings
        ]);
    }

    public function add_booking(Request $request)
    {
        $drive = getModuleDrive($request->hour);
        $booking = new VehicleBooking();
        $booking->enroll_id = $request->enroll;
        $booking->vehicle_id = $request->vehicle;
        $booking->gear = $request->gear;
        $booking->drive = $drive;
        $booking->date = ($request->date) ? formatDateToDB(strip_tags($request->date)) : NULL ;
        $booking->starttime = $request->starttime.':00:00';
        $booking->endtime = $request->endtime.':00:00';
        $booking->course_module = getModule($request->hour);
        $booking->save();

        $description = "เพิ่มตารางเรียนข้อมูลนักเรียนชื่อ:". $request->logStudent;
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        return redirect()->route('student.show', $request->sid)->with('feedback','เพิ่มตารางเรียนเรียบร้อย');
    }

    public function edit_booking(EditBookingRequest $request)
    {
        $key = $request->key;
        $vbid = 'vbid'.$key;
        $hour = 'hour'.$key;
        $drive = getModuleDrive($request->$hour);
        $vehicle = 'vehicle'.$key;
        $date = 'date'.$key;
        $starttime = 'starttime'.$key;
        $endtime = 'endtime'.$key;
        $logStudent = 'logStudent'.$key;
        $sid = 'sid'.$key;

        $booking = VehicleBooking::find($request->$vbid);
        $booking->vehicle_id = $request->$vehicle;
        $booking->drive = $drive;
        $booking->date = ($request->date) ? formatDateToDB(strip_tags($request->date)) : NULL ;
        $booking->starttime = $request->$starttime.':00:00';
        $booking->endtime = $request->$endtime.':00:00';
        $booking->course_module = getModule($request->$hour);
        $booking->save();

        $description = "แก้ไขตารางเรียนข้อมูลนักเรียนชื่อ:". $request->$logStudent;
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        return redirect()->route('student.show', $request->$sid)->with('feedback','แก้ไขตารางเรียนเรียบร้อย');
    }

    public function delete_booking(Request $request)
    {
        $booking = VehicleBooking::where('id', '=', $request->vehicleBookingID)->first();
       // $booking = VehicleBooking::findOrFail($request->vehicleBookingID);
        $booking->delete();

        $description = "ลบตารางเรียนข้อมูลนักเรียนชื่อ:". $request->logStudent;
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        return redirect()->route('student.show', $request->sid)->with('feedback','ลบตารางเรียนเรียบร้อย');
    }

    public function update_payment(Request $request)
    {
        $request->validate([
            'pay' => 'required|numeric',
            'paymentMethod' => 'required',
            'payDate' => 'required|date_format:d/m/Y',
            'payTime' => 'required|date_format:H:i:s'
        ],[
            'pay.required' => 'กรุณากรอกจำนวนเงิน',
            'pay.numeric' => 'กรุณากรอกเป็นตัวเลข',
            'paymentMethod.required' => 'กรุณาเลือกรูปแบบการชำระเงิน',
            'payDate.required' => 'กรุณากรอกวันที่',
            'payDate.date_format:d/m/Y' => 'กรุณากรอกรูปแบบวันที่ให้ถูกต้อง วัน/เดือน/ปี',
            'payTime.required' => 'กรุณากรอกเวลา',
            'payTime.date_format:H:i:s' => 'กรุณากรอกรูปแบบเวลาให้ถูกต้อง ชม.:นาที:วินาที',
        ]);

        if($request->payDate != "") {
            $payDate = ($request->payDate) ? formatDateToDB(strip_tags($request->payDate)) : NULL ;
        } else {
            $payDate = date('Y-m-d');
        }

        $billing = new Billing();
        $billing->enroll_id = $request->eid;
        $billing->amount = strip_tags(abs($request->pay));
        $billing->method = $request->paymentMethod;
        $billing->date = $payDate;
        $billing->time = $request->payTime;
        $billing->save();

        // Log
        $description = "เพิ่มการชำระเงินของนักเรียนชื่อ:". $request->payStudent .", จำนวน: ". strip_tags(abs($request->pay)) ." บาท";
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        return back()->with('feedback','เพิ่มการชำระเงินเรียบร้อย');
    }

    public function add_exam(Request $request)
    {
        $request->validate([
            'addExamDate' => 'required|date_format:d/m/Y',
            'addExamTime' => 'required|date_format:H:i:s',
        ],[
            'addExamDate.required' => 'กรุณากรอกวันที่',
            'addExamDate.date_format:m/d/Y' => 'กรุณากรอกรูปแบบวันที่ให้ถูกต้อง',
            'addExamTime.required' => 'กรุณากรอกเวลา',
            'addExamTime.date_format:H:i:s' => 'กรุณากรอกรูปแบบเวลาให้ถูกต้อง',
        ]);

        $enroll = Enroll::find($request->eid);
        $examDate = formatDateToDB(strip_tags($request->addExamDate));
        $examTime = strip_tags($request->addExamTime);
        $enroll->exam_date = $examDate;
        $enroll->exam_time = $examTime;
        $enroll->save();

        // Log
        $description = "เพิ่มวันอบรมและสอบใบขับขี่ข้อมูลนักเรียนชื่อ:". $request->logStudent;
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        return redirect()->route('student.show', $request->sid)->with('feedback','เพิ่มวันอบรมและสอบใบขับขี่เรียบร้อย');
    }

    public function update_exam(Request $request, $id)
    {
        $request->validate([
            'examDate' => 'required|date_format:d/m/Y',
            'examTime' => 'required|date_format:H:i:s',
        ],[
            'examDate.required' => 'กรุณากรอกวันที่',
            'examDate.date_format:m/d/Y' => 'กรุณากรอกรูปแบบวันที่ให้ถูกต้อง',
            'examTime.required' => 'กรุณากรอกเวลา',
            'examTime.date_format:H:i:s' => 'กรุณากรอกรูปแบบเวลาให้ถูกต้อง',
        ]);

        $enroll = Enroll::find($id);
        $sid = $enroll->student_id;
        $examDate = ($request->examDate) ? formatDateToDB(strip_tags($request->examDate)) : NULL ;
        $examTime = ($request->examTime) ? $request->examTime : NULL ;
        $enroll->exam_date = $examDate;
        $enroll->exam_time = $examTime;
        $enroll->save();

        // Log
        $description = "แก้ไขวันอบรมและสอบใบขับขี่ข้อมูลนักเรียนชื่อ:". $request->logStudent;
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        return redirect()->route('student.show', $sid)->with('feedback','เลื่อนวันอบรมและสอบใบขับขี่เรียบร้อย');
    }

    public function graduated()
    {
        $graduated = Student::select('students.id as sid', 'students.*', 'enrolls.id as eid', 'enrolls.*', 'courses.*')
                            ->leftJoin('enrolls', 'students.id', '=', 'enrolls.student_id')
                            ->leftJoin('courses', 'courses.id', '=', 'enrolls.course_id')
                            ->whereYear('students.created_at', '=', date('Y'))
                            ->where('students.is_active', '=', '0')
                            ->orderBy('code_month', 'asc')
                            ->orderBy('code_branch', 'asc')
                            ->orderBy('code_no', 'asc')
                            ->get();
        return view('student.graduated')->with([
                'page_title' => 'นักเรียนที่เรียนจบแล้ว',
                'year' => date('Y'),
                'students' => $graduated
        ]);
    }

    public function graduated_submit(Request $request)
    {
        $graduated = Student::select('students.id as sid', 'students.*', 'enrolls.id as eid', 'enrolls.*', 'courses.*')
                            ->leftJoin('enrolls', 'students.id', '=', 'enrolls.student_id')
                            ->leftJoin('courses', 'courses.id', '=', 'enrolls.course_id')
                            ->whereYear('students.created_at', '=', $request->year)
                            ->where('students.is_active', '=', '0')
                            ->orderBy('code_month', 'asc')
                            ->orderBy('code_branch', 'asc')
                            ->orderBy('code_no', 'asc')
                            ->get();
        return view('student.graduated')->with([
                'page_title' => 'นักเรียนที่เรียนจบแล้ว',
                'year' => $request->year,
                'students' => $graduated
        ]);
    }

    public function delete_graduated(Request $request)
    {
        $student = student::where('id', '=', $request->sid)->first();
        $student->delete();

        $enroll = Enroll::where('student_id', '=', $request->sid)->first();
        $enroll->delete();

        $description = "ลบข้อมูลนักเรียน: ". $request->logStudent;
        $this->LogController->log('การจัดการนักเรียนที่เรียนจบแล้ว', 'ลบข้อมูล', $description);

        return redirect()->route('student.graduated')->with('feedback','ลบนักเรียนเรียบร้อย');
    }

    public function show_graduated($id)
    {
        $data = Student::select('students.id as sid', 'students.*', 'enrolls.id as eid', 'enrolls.*',  'courses.type as courseType', 'courses.*', 'branchs.name as branchName')
                            ->leftJoin('enrolls', 'students.id', '=', 'enrolls.student_id')
                            ->leftJoin('courses', 'courses.id', '=', 'enrolls.course_id')
                            ->leftJoin('branchs', 'branchs.code', '=', 'students.code_branch')
                            ->where('students.id', '=', $id)->first();

        $bookings = VehicleBooking::select('*', 'vehicles.id as vid')->leftJoin('vehicles', 'vehicle_bookings.vehicle_id', '=', 'vehicles.id')->where('enroll_id', '=' , $data->eid)->orderBy('vehicle_bookings.date', 'asc')->get();
        $modules = Module::where('course_id', '=' ,$data->course_id)->get();

        return view('student.show_graduated')->with([
            'page_title' => 'ข้อมูลนักเรียนที่เรียนจบแล้ว',
            'student' => $data,
            'bookings' => $bookings,
            'modules' => $modules,
        ]);
    }

    public function reactivate($id)
    {
        Student::find($id)->update(['is_active'=>'1']);
        return redirect()->route('student.graduated')->with('feedback','เปิดใช้งานอีกครั้งข้อมูลนักเรียนเรียบร้อย');
    }

    public function get_student($purpose, $type)
    {

        $fullBooked = VehicleBooking::selectRaw('hour, student_id, enroll_id, floor(( sum(endtime)-sum(starttime) )/10000) as learnTime')
                            ->leftJoin('enrolls', 'vehicle_bookings.enroll_id', '=', 'enrolls.id')
                            ->leftJoin('courses', 'courses.id', '=', 'enrolls.course_id')
                            ->groupBy('vehicle_bookings.enroll_id')
                            ->havingRaw('floor(( sum(endtime)-sum(starttime) )/10000) >= courses.hour')
                            ->pluck('enroll_id')->toArray();

        $students = Student::select('students.id', 'code_no', 'code_month', 'code_branch', 'title', 'firstname', 'lastname', 'type')
                            ->leftJoin('enrolls', 'enrolls.student_id', '=', 'students.id')
                            ->leftJoin('courses', 'enrolls.course_id', '=', 'courses.id')
                            ->where('enrolls.purpose', '=', $purpose)
                            ->where('students.is_active', '=', '1')
                            ->where('type', '=', $type)
                            ->whereNotIn('enrolls.id', $fullBooked)
                            ->orderBy('code_month', 'asc')
                            ->orderBy('code_branch', 'asc')
                            ->orderBy('code_no', 'asc')
                            ->get();


        $studentData['data'] = $students;
        echo json_encode($studentData);
        exit;
    }

    public function update_course(Request $request, $id)
    {
        $request->validate([
            'course' => 'required',
            'purpose' => 'required',
            'coursePay' => 'required|numeric',
            'courseMethod' => 'required',
            'coursePayDate' => 'required|date_format:d/m/Y',
            'coursePayTime' => 'required|date_format:H:i:s',
        ],[
            'course.required' => 'กรุณาเลือกคอร์สเรียน',
            'purpose.required' => 'กรุณาเลือกรูปแบบการเรียน',
            'coursePay.required' => 'กรุณากรอกจำนวนเงิน',
            'coursePay.numeric' => 'กรุณากรอกเป็นตัวเลข',
            'courseMethod.required' => 'กรุณาเลือกวิธีการชำระเงิน',
            'coursePayDate.required' => 'กรุณากรอกวันที่',
            'coursePayDate.date_format:m/d/Y' => 'กรุณากรอกรูปแบบวันที่ให้ถูกต้อง',
            'coursePayTime.required' => 'กรุณากรอกเวลา',
            'coursePayTime.date_format:H:i:s' => 'กรุณากรอกรูปแบบเวลาให้ถูกต้อง',
        ]);

        $enroll = Enroll::find($id);
        $sid = $enroll->student_id;
        $courseExamDate = ($request->courseExamDate) ? formatDateToDB(strip_tags($request->courseExamDate)) : NULL ;
        $courseExamTime = ($request->courseExamTime) ? $request->courseExamTime : NULL ;
        $course = explode("-", $request->course);
        $enroll->course_id = $course[0];
        $enroll->purpose = $request->purpose;
        $enroll->course_cost = $course[1];
        $enroll->discount = strip_tags(abs($request->discountAmount));
        $enroll->discount_reason = strip_tags($request->discount);
        $enroll->total_cost = $course[1] - strip_tags(abs($request->discountAmount));
        $enroll->exam_date = $courseExamDate;
        $enroll->exam_time = $courseExamTime;
        $enroll->save();

        // billings table
        Billing::where('enroll_id', '=', $id)->delete();
        $billing = new Billing();
        $billing->enroll_id = $id;
        $billing->amount = strip_tags(abs($request->coursePay));
        $billing->date = ($request->coursePayDate) ? formatDateToDB(strip_tags($request->coursePayDate)) : NULL ;
        $billing->method = $request->courseMethod;
        $billing->time = $request->coursePayTime;
        $billing->save();

        // vehicle_booking table
        VehicleBooking::where('enroll_id', '=', $id)->delete();

        // Log
        $description = "เปลี่ยนคอลร์สเรียนของนักเรียนชื่อ:". $request->logStudent;
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        return redirect()->route('student.show', $sid)->with('feedback','เปลี่ยนคอลร์สเรียนของนักเรียนเรียบร้อย');
    }

    public function update_graduate_status(Request $request, $id)
    {
        Student::find($id)->update(['is_active'=>'0']);
        Enroll::where('student_id', '=', $id)->update(['is_active'=>'0']);
        // Log
        $description = "เปลี่ยนสถานะนักเรียนเป็นเรียนจบแล้ว ชื่อ:". $request->gStu ;
        $this->LogController->log('การจัดการนักเรียน', 'แก้ไขข้อมูล', $description);

        return redirect()->route('student.index')->with('feedback','เปลี่ยนสถานะนักเรียนเป็นเรียนจบแล้วเรียบร้อย');
    }
}
