<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use PDF;
use App\Models\Student;
use App\Models\Billing;
use App\Models\Income;
use App\Models\Withdraw;
use App\Models\VehicleBooking;
use App\Models\Branch;
use Illuminate\Support\Facades\App;

class PDFController extends Controller
{
    public function student($id)
    {
        $data = Student::select('students.id as sid', 'students.*', '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();
        $pdf = PDF::loadView('pdf.student', ['page_title' => 'บัตรประจำตัวผู้เรียนขับรถยนต์', 'student' => $data])->setPaper([0, 0, 425.2, 283.46], 'landscape');
        return $pdf->stream();
    }

    public function schedule($id)
    {
        $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', '=', $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();
        $pdf = PDF::loadView('pdf.schedule', ['page_title' => 'ใบนัดหมายการเรียน', 'bookings' => $bookings, 'student' => $data])->setPaper([0, 0, 425.2, 283.46], 'landscape');
        return $pdf->stream();
    }

    public function receipt($id)
    {
        $receipt = 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, method 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', '=', $id)->first();

        // $pdf = App::make('dompdf.wrapper');
        // $html = view('pdf.receipt', ['receipt' => $receipt])->render();
        // $pdf->loadHTML($html);

        $pdf = PDF::loadView('pdf.receipt', ['page_title' => 'ใบเสร็จรับเงิน', 'receipt' => $receipt])->setPaper('A4', 'portrait');
        return $pdf->stream();
    }

    public function evaluation($id)
    {
        $data = Student::where('students.id', '=', $id)->first();
        $pdf = PDF::loadView('pdf.evaluation', ['page_title' => 'แบบประเมินผู้สอน', 'student' => $data])->setPaper('A4', 'portrait');
        return $pdf->stream();
    }

    public function registration($id)
    {
        $data = Student::where('students.id', '=', $id)->first();
        $pdf = PDF::loadView('pdf.registration', ['page_title' => 'ใบสมัครเรียนฝึกขับรถยนต์', 'student' => $data])->setPaper('A4', 'portrait');
        return $pdf->stream();
    }

    public function getTotalIncomeBranchFromBillingTable($method, $branchID, $month, $year)
    {
        $query = Billing::select(Billing::raw('sum(amount) as total'))
                                                ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                                ->where('enrolls.branch_id', '=', $branchID)
                                                ->whereMonth('billings.date', '=',  $month)
                                                ->whereYear('billings.date', '=', $year)
                                                ->where('method', '=', $method)
                                                ->first()->total;
        return $query;
    }

    public function getTotalIncomeBranchFromIncomeTable($method, $branchID, $month, $year)
    {
        $query = Income::select(Income::raw('sum(amount) as total'))
                        ->where('branch_id', '=', $branchID)->whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', $method)
                        ->first()->total;
        return $query;
    }

    public function finance($bid, $month, $year, $type)
    {
        $monthArr = ['มกราคม', 'กุมภาพันธ์', 'มีนาคม', 'เมษายน', 'พฤษภาคม', 'มิถุนายน', 'กรกฎาคม', 'สิงหาคม', 'กันยายน', 'ตุลาคม', 'พฤศจิกายน', 'ธันวาคม'];
        // $tmp = explode('-', $monthYear);
        // $month = $tmp[0];
        // $year = $tmp[1];

        if($bid=='0') { // รวมทุกสาขา
            $branch = 'รวมทุกสาขา';
            if($type=='รายได้') {
                // Incomes
                $result = Billing::select('amount', 'billings.date as billDate', 'billings.time as billTime', 'name', 'method')
                            ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                            ->leftJoin('branchs', 'enrolls.branch_id', '=', 'branchs.id')
                            ->whereMonth('billings.date', '=', $month)
                            ->whereYear('billings.date', '=', $year)
                            ->orderBy('billings.date','asc')
                            ->orderBy('billings.time','asc')
                            ->get();
                $totalResult = Billing::select(Billing::raw('sum(amount) as total'))->whereMonth('date', '=', $month)->whereYear('date', '=', $year)->first()->total;
                $totalResultCash = Billing::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'เงินสด')->sum('amount');
                $totalResultTransfer = Billing::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'โอน')->sum('amount');
                $totalResultCredit = Billing::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'บัตรเครดิต')->sum('amount');

                // Incomes from another (menu รายรับ)
                $incomeAnotherAll = Income::select('amount', 'date as billDate', 'time as billTime', 'name', 'method', 'title')
                                                ->leftJoin('branchs', 'incomes.branch_id', '=', 'branchs.id')
                                                ->whereMonth('date', '=', $month)
                                                ->whereYear('date', '=', $year)
                                                ->orderBy('date','asc')
                                                ->orderBy('time','asc')
                                                ->get();
                $totalIncomeAnotherAll = Income::select(Income::raw('sum(amount) as total'))->whereMonth('date', '=', $month)->whereYear('date', '=', $year)->first()->total;
                $totalIncomeAnotherAllCash = Income::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'เงินสด')->sum('amount');
                $totalIncomeAnotherAllTransfer = Income::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'โอน')->sum('amount');
                $totalIncomeAnotherAllCredit = Income::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'บัตรเครดิต')->sum('amount');

                // Combine result and incomeAnotherAllBranch to single object
                foreach($incomeAnotherAll as $i) {
                    $result->add($i);
                }
                // Sort by billdate
                $result = $result->sortBy('billDate')->values()->all();

                $totalResult += $totalIncomeAnotherAll;
                $totalResultCash += $totalIncomeAnotherAllCash;
                $totalResultTransfer += $totalIncomeAnotherAllTransfer;
                $totalResultCredit += $totalIncomeAnotherAllCredit;

            } else {
                // Expenses
                $result = Withdraw::select('amount', 'date', 'time', 'name', 'method')
                            ->leftJoin('branchs', 'withdraws.branch_id', '=', 'branchs.id')
                            ->whereMonth('withdraws.date', '=', $month)
                            ->whereYear('withdraws.date', '=', $year)
                            ->orderBy('withdraws.date','asc')
                            ->orderBy('withdraws.time','asc')
                            ->get();
                $totalResult = Withdraw::select(Withdraw::raw('sum(amount) as total'))->whereMonth('date', '=', $month)->whereYear('date', '=', $year)->first()->total;
                $totalResultCash = Withdraw::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'เงินสด')->sum('amount');
                $totalResultTransfer = Withdraw::whereMonth('date', '=', $month)->whereYear('date', '=', $year)->where('method', '=', 'โอน')->sum('amount');
                $totalResultCredit = NULL;
            }
        } else {
            $branch = Branch::find($bid);
            $branch = $branch->name;
            if($type=='รายได้') {
                // Incomes
                $result = Billing::select('amount', 'billings.date as billDate', 'billings.time as billTime', 'name', 'method')
                                            ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                            ->leftJoin('branchs', 'enrolls.branch_id', '=', 'branchs.id')
                                            ->where('branch_id', '=', $bid)
                                            ->whereMonth('billings.date', '=', $month)
                                            ->whereYear('billings.date', '=', $year)
                                            ->orderBy('billings.date','asc')
                                            ->orderBy('billings.time','asc')
                                            ->get();

                $totalResult = Billing::select(Billing::raw('sum(amount) as total'))
                                                ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                                                ->where('enrolls.branch_id', '=', $bid)
                                                ->whereMonth('billings.date', '=', $month)
                                                ->whereYear('billings.date', '=', $year)
                                                ->first()->total;
                $totalResultCash = $this->getTotalIncomeBranchFromBillingTable('เงินสด', $bid, $month, $year);
                $totalResultTransfer = $this->getTotalIncomeBranchFromBillingTable('โอน', $bid, $month, $year);
                $totalResultCredit = $this->getTotalIncomeBranchFromBillingTable('บัตรเครดิต', $bid, $month, $year);
                // $totalResultCash = Billing::select(Billing::raw('sum(amount) as total'))
                //                                 ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                //                                 ->where('enrolls.branch_id', '=', $bid)
                //                                 ->whereMonth('billings.date', '=',  $month)
                //                                 ->whereYear('billings.date', '=', $year)
                //                                 ->where('method', '=', 'เงินสด')
                //                                 ->first()->total;
                // $totalResultTransfer = Billing::select(Billing::raw('sum(amount) as total'))
                //                                 ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                //                                 ->where('enrolls.branch_id', '=', $bid)
                //                                 ->whereMonth('billings.date', '=',  $month)
                //                                 ->whereYear('billings.date', '=', $year)
                //                                 ->where('method', '=', 'โอน')
                //                                 ->first()->total;
                // $totalResultCredit = Billing::select(Billing::raw('sum(amount) as total'))
                //                                 ->leftJoin('enrolls', 'billings.enroll_id', '=', 'enrolls.id')
                //                                 ->where('enrolls.branch_id', '=', $bid)
                //                                 ->whereMonth('billings.date', '=',  $month)
                //                                 ->whereYear('billings.date', '=', $year)
                //                                 ->where('method', '=', 'บัตรเครดิต')
                //                                 ->first()->total;

                // Incomes from another (menu รายรับ)
                $incomeAnother = Income::select('amount', 'date as billDate', 'time as billTime', 'name', 'method', 'title')
                                            ->leftJoin('branchs', 'branch_id', '=', 'branchs.id')
                                            ->where('branch_id', '=', $bid)
                                            ->whereMonth('date', '=', $month)
                                            ->whereYear('date', '=', $year)
                                            ->orderBy('date','asc')
                                            ->orderBy('time','asc')
                                            ->get();
                $totalIncomeAnother = Income::select(Income::raw('sum(amount) as total'))
                                                ->where('branch_id', '=', $bid)
                                                ->whereMonth('date', '=', $month)
                                                ->whereYear('date', '=', $year)
                                                ->first()->total;
                $totalIncomeAnotherCash = $this->getTotalIncomeBranchFromIncomeTable('เงินสด', $bid, $month, $year);
                $totalIncomeAnotherTransfer = $this->getTotalIncomeBranchFromIncomeTable('โอน', $bid, $month, $year);
                $totalIncomeAnotherCredit = $this->getTotalIncomeBranchFromIncomeTable('บัตรเครดิต', $bid, $month, $year);
                // Combine result incomeAllBranch and incomeAnotherAllBranch to single object
                foreach($incomeAnother as $x) {
                    $result->add($x);
                }
                // Sort by billdate
                $result = $result->sortBy('billDate')->values()->all();

                $totalResult += $totalIncomeAnother;
                $totalResultCash += $totalIncomeAnotherCash;
                $totalResultTransfer += $totalIncomeAnotherTransfer;
                $totalResultCredit += $totalIncomeAnotherCredit;

            } else {
                // Expenses
                $result = Withdraw::select('amount', 'date', 'name', 'method')
                                        ->leftJoin('branchs', 'withdraws.branch_id', '=', 'branchs.id')
                                        ->where('branch_id', '=', $bid)
                                        ->whereMonth('withdraws.date', '=', $month)
                                        ->whereYear('withdraws.date', '=', $year)
                                        ->orderBy('withdraws.date','asc')
                                        ->orderBy('withdraws.time','asc')
                                        ->get();
                $totalResult = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                            ->where('withdraws.branch_id', '=', $bid)
                                            ->whereMonth('date', '=', $month)
                                            ->whereYear('date', '=', $year)
                                            ->first()->total;
                $totalResultCash = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                                ->where('branch_id', '=', $bid)
                                                ->whereMonth('date', '=', $month)
                                                ->whereYear('date', '=', $year)
                                                ->where('method', '=', 'เงินสด')
                                                ->first()->total;
                $totalResultTransfer = Withdraw::select(Withdraw::raw('sum(amount) as total'))
                                                    ->where('branch_id', '=', $bid)
                                                    ->whereMonth('date', '=', $month)
                                                    ->whereYear('date', '=', $year)
                                                    ->where('method', '=', 'โอน')
                                                    ->first()->total;
                $totalResultCredit = NULL;
            }
        }

        $pdf = PDF::loadView('pdf.finance', [
                    'page_title' => 'รายงานรายรับ - รายจ่าย '. $branch .' ประจำเดือน',
                    'month' => $monthArr[($month - 1)],
                    'year' => $year,
                    'type' => $type,
                    'data' => $result,
                    'totalData' => $totalResult,
                    'totalDataCash' => $totalResultCash,
                    'totalDataTransfer' => $totalResultTransfer,
                    'totalDataCredit' => $totalResultCredit,
                ])->setPaper('A4', 'portrait');
        return $pdf->stream();
    }
}
