<?php

namespace App\Http\Controllers\Backend2;

use App\Components\Functions;
use App\Http\Controllers\Backend\AdminController;
use App\Models\Contract;
use App\Models\Deposit;
use App\Models\ElectricWater;
use App\Models\Hostel;
use App\Models\RenterRoom;
use App\Models\Room;
use App\User;
use Carbon\Carbon;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Yajra\Datatables\Datatables;

class ReportController extends AdminController
{
    //
    public function index()
    {
        return view('admin2.report.index');
    }

    public function deposit()
    {
        return view('admin2.report.deposits');
    }

    public function depositAjax(Request $request)
    {
        $isExcel = $request->input('is_excel');
        $ownerId = auth('backend')->user()->id;

        if (auth('backend')->user()->type == User::STAFF) {
            $ownerId = auth('backend')->user()->staff_owner_id;
        }

        $hostels = Hostel::query()
            ->where('owner_id', $ownerId)
            ->with('rooms')
            ->get();

        $sumDeposits = Deposit::query()
            ->has('room')
           ->where(function($q) {
               $q->orHas('reserve');
               $q->orHas('contractValid');
           })
            ->whereIn('hostel_id', $hostels->pluck('id')->toArray())
            ->sum('amount');

        if ($isExcel == 1) {
            return \Excel::create('bao-cao-tien-coc', function ($excel) use ($isExcel, $hostels, $sumDeposits) {

                $excel->sheet('bao-cao', function ($sheet) use ($hostels, $isExcel, $sumDeposits) {
                    $sheet->setAllBorders('medium');

                    $sheet->setStyle(array(
                        'font' => array(
                            'name' => 'Times New Roman',
                            'size' => 11,
                        ),
                        'borders' => [
                            'allborders' => [
                                'style' => 'medium'
                            ]
                        ]
                    ));
                    $sheet->loadView('admin2.report.deposit_table', compact('hostels', 'sumDeposits', 'isExcel'));


                });
            })->download('xlsx');
        }

        return response([
            'status' => 1,
            'data' => view('admin2.report.deposit_table', compact('hostels', 'sumDeposits'))->render()
        ]);
    }

    public function rentStatusAjax(Request $request)
    {
        $isExcel = $request->input('is_excel');
        $ownerId = auth('backend')->user()->id;

        if (auth('backend')->user()->type == User::STAFF) {
            $ownerId = auth('backend')->user()->staff_owner_id;
        }
        $hostels = Hostel::query()
            ->where('owner_id', $ownerId)
            ->with('rooms')
            ->get();
        if ($isExcel == 1) {
            return \Excel::create('bao-cao-tong-hop-tinh-trang-thue', function ($excel) use ($isExcel, $hostels) {

                $excel->sheet('bao-cao', function ($sheet) use ($hostels, $isExcel) {
                    $sheet->setAllBorders('medium');

                    $sheet->setStyle(array(
                        'font' => array(
                            'name' => 'Times New Roman',
                            'size' => 11,
                        ),
                        'borders' => [
                            'allborders' => [
                                'style' => 'medium'
                            ]
                        ]
                    ));
                    $sheet->loadView('admin2.report.rent_status_ajax', compact('hostels', 'isExcel'));


                });
            })->download('xlsx');
        }

        return response([
            'status' => 1,
            'data' => view('admin2.report.rent_status_ajax', compact('hostels'))->render()
        ]);
    }

    public function rentStatusDetailAjax(Request $request)
    {
        $isExcel = $request->input('is_excel');
        $ownerId = auth('backend')->user()->id;

        if (auth('backend')->user()->type == User::STAFF) {
            $ownerId = auth('backend')->user()->staff_owner_id;
        }
        $hostels = Hostel::query()
            ->where('owner_id', $ownerId)
            ->with('rooms')
            ->get();

        $hostelAll = Hostel::query()
            ->where('owner_id', $ownerId)
            ->with('rooms')
            ->where('type_rent', Hostel::TYPE_RENT_ALL)
            ->get();

        $roomAlls = Room::query()->whereIn('hostel_id', $hostelAll->pluck('id')->toArray())->count();
        $numberAll = RenterRoom::query()->whereIn('hostel_id', $hostelAll->pluck('id')->toArray())
            ->groupBy('room_id')
            ->count();

        $hostelEvery = Hostel::query()
            ->where('owner_id', $ownerId)
            ->with('rooms')
            ->where('type_rent', Hostel::TYPE_RENT_EVERY)
            ->get();

        $numberEvery = RenterRoom::query()->whereIn('hostel_id', $hostelEvery->pluck('id')->toArray())
            ->count();

        $roomEvery = Room::query()->whereIn('hostel_id', $hostelEvery->pluck('id')->toArray())
            ->sum('max_renters');

        $numberRoom = $roomAlls + $roomEvery;
        $numberRenterRoom = RenterRoom::query()->whereIn('hostel_id', $hostels->pluck('id')->toArray())
            ->count();


        if ($isExcel == 1) {
            return \Excel::create('bao-cao-chi-tiet-tinh-trang-thue', function ($excel) use ($isExcel, $hostels, $numberRoom, $numberRenterRoom) {

                $excel->sheet('bao-cao', function ($sheet) use ($hostels, $isExcel, $numberRoom, $numberRenterRoom) {
                    $sheet->setAllBorders('medium');

                    $sheet->setStyle(array(
                        'font' => array(
                            'name' => 'Times New Roman',
                            'size' => 11,
                        ),
                        'borders' => [
                            'allborders' => [
                                'style' => 'medium'
                            ]
                        ]
                    ));
                    $sheet->loadView('admin2.report.rent_status_detail_ajax', compact('hostels', 'numberRoom', 'numberRenterRoom','isExcel'));


                });
            })->download('xlsx');
        }

        return response([
            'status' => 1,
            'data' => view('admin2.report.rent_status_detail_ajax', compact('hostels', 'numberRoom', 'numberRenterRoom'))->render()
        ]);
    }

    public function debtStatusAjax(Request $request)
    {
        $isExcel = $request->input('is_excel');
        $ownerId = auth('backend')->user()->id;

        if (auth('backend')->user()->type == User::STAFF) {
            $ownerId = auth('backend')->user()->staff_owner_id;
        }
        $hostels = Hostel::query()
            ->where('owner_id', $ownerId)
            ->with([
                'rooms',
                'moneyInfos',
                'collectSpends'
            ])
            ->get();
        if ($isExcel == 1) {
            //return view('admin2.report.debt_status_ajax', compact('hostels', 'isExcel'));
            return \Excel::create('bao-cao-no-khach-hang', function ($excel) use ($isExcel, $hostels) {

                $excel->sheet('bao-cao', function ($sheet) use ($hostels, $isExcel) {
                    $sheet->setAllBorders('medium');

                    $sheet->setStyle(array(
                        'font' => array(
                            'name' => 'Times New Roman',
                            'size' => 11,
                        ),
                        'borders' => [
                            'allborders' => [
                                'style' => 'medium'
                            ]
                        ]
                    ));
                    $sheet->loadView('admin2.report.debt_status_ajax', compact('hostels', 'isExcel'));


                });
            })->download('xlsx');
        }

        return response([
            'status' => 1,
            'data' => view('admin2.report.debt_status_ajax', compact('hostels'))->render()
        ]);
    }

    public function ew()
    {
        return view('admin2.report.ew');
    }

    public function ew2()
    {
        $hostels = Hostel::query()->with('rooms')->where('owner_id', auth('backend')->user()->id)->get();

        return view('admin2.report.ew_2', compact('hostels'));
    }

    public function ewByMonth(Request $request)
    {
        $month = $request->input('month');
        $isExcel = $request->input('is_export');
        if (empty($month)) {
            $month = Carbon::now();
        } else {
            $month = Carbon::createFromFormat('d/m/Y', '01/' . $month);
        }

        $ownerId = auth('backend')->user()->id;

        if (auth('backend')->user()->type == User::STAFF) {
            $ownerId = auth('backend')->user()->staff_owner_id;
        }

        $hostels = Hostel::query()
            ->where('owner_id', $ownerId)
            ->with('rooms')
            ->get();

        $sumElectric = ElectricWater::query()
            ->whereBetween('date_action',
                [
                    $month->copy()->startOfMonth()->startOfDay()->toDateTimeString(),
                    $month->copy()->endOfMonth()->endOfDay()->toDateTimeString()
                ]
            )
            ->whereIn('hostel_id', $hostels->pluck('id')->toArray())
            ->sum('delta_electric');

        $sumWater = ElectricWater::query()
            ->whereBetween('date_action',
                [
                    $month->copy()->startOfMonth()->startOfDay()->toDateTimeString(),
                    $month->copy()->endOfMonth()->endOfDay()->toDateTimeString()
                ]
            )
            ->whereIn('hostel_id', $hostels->pluck('id')->toArray())
            ->sum('delta_water');

        //return view( 'admin2.report.ew_by_month', compact( 'hostels', 'month', 'sumElectric', 'sumWater' ) );

        if ($isExcel == 1) {
            return \Excel::create('bao-cao-su-dung-dien-' . $month->copy()->format('m-Y'), function ($excel) use ($isExcel, $hostels, $month, $sumWater, $sumElectric) {

                $excel->sheet('bao-cao', function ($sheet) use ($hostels, $month, $sumWater, $sumElectric, $isExcel) {
                    $sheet->setAllBorders('medium');

                    $sheet->setStyle(array(
                        'font' => array(
                            'name' => 'Times New Roman',
                            'size' => 11,
                        ),
                        'borders' => [
                            'allborders' => [
                                'style' => 'medium'
                            ]
                        ]
                    ));
                    $sheet->loadView('admin2.report.ew_by_month', compact('hostels', 'month', 'sumElectric', 'sumWater', 'isExcel'));


                });
            })->download('xlsx');
        }


        return response([
            'status' => 1,
            'data' => view('admin2.report.ew_by_month', compact('hostels', 'month', 'sumElectric', 'sumWater'))->render()
        ]);
    }

    public function ew2ByMonth(Request $request)
    {
        $month = $request->input('month');
        $hostelId = $request->input('hostel_id');
        $hostel = Hostel::find($hostelId);
        $isExcel = $request->input('is_export');
        if (empty($month)) {
            $month = Carbon::now();
        } else {
            $month = Carbon::createFromFormat('d/m/Y', '01/' . $month);
        }

        if ($isExcel == 1) {
            return \Excel::create('bao-cao-su-dung-dien-nuoc-' . $month->copy()->format('m-Y'), function ($excel) use ($isExcel, $hostel, $month) {

                $excel->sheet('bao-cao', function ($sheet) use ($isExcel, $hostel, $month) {
                    $sheet->setAllBorders('medium');

                    $sheet->setStyle(array(
                        'font' => array(
                            'name' => 'Times New Roman',
                            'size' => 11,
                        ),
                        'borders' => [
                            'allborders' => [
                                'style' => 'medium'
                            ]
                        ]
                    ));
                    $sheet->loadView('admin2.report.ew_2_by_month', compact('hostel', 'month', 'isExcel'));


                });
            })->download('xlsx');
        }


        return response([
            'status' => 1,
            'data' => view('admin2.report.ew_2_by_month', compact('hostel', 'month'))->render()
        ]);
    }

    public function contracts(Request $request)
    {
        return response([
            'status' => 1,
            'data' => view('admin2.report.contracts')->render()
        ]);
    }

    public function getContractsByAttribute(Request $request)
    {
        $hostels = Hostel::query()->where('owner_id', auth('backend')->user()->id)->pluck('id')->toArray();

        if (auth('backend')->user()->type == User::STAFF) {
            $hostels = Functions::getHostelArrStaff();
        }

        $items = Contract::query()
            ->with([
                'room',
                'hostel',
                'bed'
            ])
            ->whereIn('contracts.hostel_id', $hostels);

        return Datatables::of($items)
            ->filterColumn('hostel_id', function ($query, $keyword) {
                $query->whereHas('hostel', function ($q) use ($keyword) {
                    $q->where('name', 'LIKE', '%' . $keyword . '%');
                });
            })
            ->filterColumn('room_id', function ($query, $keyword) {
                $query->whereHas('room', function ($q) use ($keyword) {
                    $q->where('name', 'LIKE', '%' . $keyword . '%');
                });
            })
            ->filterColumn('bed_id', function ($query, $keyword) {
                $query->whereHas('bed', function ($q) use ($keyword) {
                    $q->where('name', 'LIKE', '%' . $keyword . '%');
                });
            })
            ->filterColumn('ward_id', function ($query, $keyword) {
                $query->whereHas('ward', function ($q) use ($keyword) {
                    $q->where('name', 'LIKE', '%' . $keyword . '%');
                });
            })
            ->filterColumn('status', function ($query, $keyword) {
               if($keyword == Contract::VALIDATED)
               {
                   $query->where('status', '<>', Contract::LIQUIDATED)
                       ->where('end_date', '>=', Carbon::now());
               } else if ($keyword == Contract::LIQUIDATED)
               {
                   $query->where('status', Contract::LIQUIDATED);
               } else {
                   $query->where('status', '<>', Contract::LIQUIDATED)
                       ->where('end_date', '<', Carbon::now());
               }
            })
            ->filterColumn('period', function ($query, $keyword) {
                if ($keyword == 4) {
                    $keyword = Room::FOUR_MONTH_PERIOD;
                }
                if ($keyword == 6) {
                    $keyword = Room::SIX_MONTH_PERIOD;
                }

                if ($keyword == 12) {
                    $keyword = Room::ONE_YEAR_PERIOD;
                }
                $query->where('period', $keyword);
            })
            ->editColumn('code', function ($item) {
                return $item->code . (empty($item->reference) ? '' : '<br>' . $item->reference);
            })
            ->editColumn('hostel_id', function ($item) {
                return optional($item->hostel)->name;
            })
            ->editColumn('room_id', function ($item) {
                return optional($item->room)->name;
            })
            ->editColumn('bed_id', function ($item) {
                return optional($item->bed)->name;
            })
            ->editColumn('date_contract', function ($item) {
                return $item->date_contract->format('d/m/Y');
            })
            ->editColumn('date_end_contract', function ($item) {
                if (!empty($item->date_end_contract)) {
                    return Carbon::createFromFormat('Y-m-d', $item->date_end_contract)->format('d/m/Y');
                }
            })
            ->editColumn('period', function ($item) {
                return Functions::getPeriodNumber($item->period);
            })
            ->editColumn('end_date', function ($item) {
                if (!empty($item->end_date)) {
                    return $item->end_date->format('d/m/Y');
                }

            })
            ->editColumn('type_rent', function ($item) {
                return $item->type_rent_text;
            })
            ->editColumn('deposit', function ($item) {
                return number_format($item->deposit, 0, '.', '.');
            })
            ->editColumn('room_price', function ($item) {
                return number_format($item->room_price, 0, '.', '.');
            })
            ->editColumn('status', function ($item) {

                return $item->status_text;
            })
            ->editColumn('action', function ($item) {
                $retVal = '';
                if (auth('backend')->user()->can('edit-contract')) {
                    $retVal .= '<a data-id="' . $item->id . '" href="#edit-contract" data-toggle="modal" class="btn btn-sm btn-outline btn-editable dark black edit-contract"><i class="fa fa-edit"></i> Sửa</a>';
                }
                if (auth('backend')->user()->can('delete-contract')) {
                    $retVal .= '<a data-id="' . $item->id . '" class="btn btn-sm btn-outline btn-editable dark black btn-delete-contract"><i class="fa fa-trash"></i> Xóa</a>';
                }

                return $retVal;
            })
            ->addIndexColumn()
            ->make(true);
    }

    public function getDepositsByAttribute(Request $request)
    {
        $hostels = Hostel::query()
            ->where('owner_id', auth('backend')->user()->id)
            ->pluck('id')
            ->toArray();

        if (auth('backend')->user()->type == User::STAFF) {
            $hostels = Functions::getHostelArrStaff();
        }

        $items = Deposit::query()
            ->whereIn('hostel_id', $hostels)
            ->where('amount', '>', 0)
            ->with('contract')
            ->with('hostel')
            ->with('room')
            ->with('reserve');

        return Datatables::of($items)
            ->editColumn('hostel_id', function ($item) {
                return optional($item->hostel)->name;
            })
            ->editColumn('room_id', function ($item) {
                return optional($item->room)->name;
            })
            ->editColumn('amount', function ($item) {
                return number_format($item->amount, 0, '.', '.');
            })
            ->editColumn('date_action', function ($item) {
                return $item->date_action->format('d/m/Y');
            })
            ->editColumn('customer', function ($item) {
                if ($item->reserve) {
                    return $item->reserve->name;
                }

                return $item->contract->name;
            })
            ->addColumn('type', function ($item) {
                if ($item->reserve) {
                    return 'Cọc giữ chỗ';
                }

                return 'Cọc hợp đồng';

            })
            ->addColumn('customer', function ($item) {

            })
            ->addIndexColumn()
            ->make(true);
    }
}