<?php

namespace App\Http\Controllers;
use App\Models\Billing;
use App\Models\Withdraw;
use App\Models\Student;
use App\Models\Enroll;
use App\Models\Course;

use Illuminate\Http\Request;

class ReportController extends Controller
{
    public function query($year)
    {
        $incomeQuery = Billing::selectRaw('sum(amount) as income, month(billings.date) as month, year(billings.date) as year')
                                    ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                    ->leftJoin('branchs', 'enrolls.branch_id', '=', 'branchs.id')
                                    ->whereYear('billings.date', '=', $year)
                                    ->groupBy('month')
                                    ->orderBy('month','asc')
                                    ->get();

        $expensesQuery = Withdraw::selectRaw('sum(amount) as expenses, month(withdraws.date) as month, year(withdraws.date) as year')
                                    ->leftJoin('branchs', 'withdraws.branch_id', '=', 'branchs.id')
                                    ->whereYear('withdraws.date', '=', $year)
                                    ->groupBy('month')
                                    ->orderBy('month','asc')
                                    ->get();

        $studentQuery = Student::selectRaw('count(id) as amount, month(register_date) as month, year(register_date) as year')
                                    ->whereYear('register_date', '=', $year)
                                    ->groupBy('month')
                                    ->orderBy('month','asc')
                                    ->get();

        $courses = Course::select('id', 'name')->orderBy('id', 'asc')->get();
        $coursesArr = array();
        $coursesNameArr = array();
        $coursesIDArr = array();

        foreach($courses as $row) {
            $coursesIDArr[] = $row->id;
            $coursesNameArr[] = $row->name;
            $coursesArr[] = '0';
        }

        $courses = Enroll::selectRaw('course_id, count(enrolls.id) as amount')
                            ->whereYear('date', '=', $year)
                            ->groupBy('course_id')
                            ->get();
        foreach($courses as $row) {
            $key = array_search($row->course_id, $coursesIDArr);
            $coursesArr[$key] = $row->amount;
        }
        // $courseQuery = Enroll::selectRaw('sum(id) as amount')
        //                         ->left;

        $month = array();

        if(date('Y')==$year) {
            $lastMonth = date('m');
        } else {
            $lastMonth = '12';
        }

        $income = array();
        $expenses = array();
        $student = array();

        for($i=0; $i<$lastMonth; $i++) {
            $month[$i] = getMontTH($i);
            $income[$i] = '0';
            $expenses[$i] = '0';
            $student[$i] = '0';
        }
        for($i=0; $i<count($incomeQuery); $i++) {
            $m = $incomeQuery[$i]->month - 1;
            $income[$m] = $incomeQuery[$i]->income;
        }
        for($i=0; $i<count($expensesQuery); $i++) {
            $m = $expensesQuery[$i]->month - 1;
            $expenses[$m] = $expensesQuery[$i]->expenses;
        }
        for($i=0; $i<count($studentQuery); $i++) {
            $m = $studentQuery[$i]->month - 1;
            $student[$m] = $studentQuery[$i]->amount;
        }

        return view('report.index')->with([
            'page_title' => 'รายงาน',
            'year' => $year,
            'month' => json_encode($month),
            'income' => json_encode($income),
            'expenses' => json_encode($expenses),
            'student' => json_encode($student),
            'courses_label' => json_encode($coursesNameArr),
            'courses' => json_encode($coursesArr),
        ]);
    }

    public function index()
    {
        return $this->query(date('Y'));
    }

    public function submit(Request $request)
    {
        $request->validate([
            'year' => 'required|date_format:Y',
        ],[
            'year.required' => 'กรุณากรอกปี',
            'year.date_format:Y' => 'กรุณากรอกรูปแบบวันที่ให้ถูกต้อง',
        ]);
        return $this->query($request->year);
    }

}
