<?php
/**
 * Created by PhpStorm.
 * User: stana
 * Date: 18.02.2017
 * Time: 21:51
 */

namespace App\Http\Controllers;

use App\Image;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Room;

class FilteringController
{
    public function index()
    {
        $rooms = DB::table('rooms')
            ->join('accommodations', 'accommodations.accommodation_id', 'rooms.accommodation_id')
            ->where('permission', '=', 'true')
            ->select('accommodation_name', 'room_name', 'room_id', 'beds', 'price', 'rooms.accommodation_id', 'rooms.image_id')
            ->orderBy('accommodation_name')
            ->orderBy('room_name')
            ->get();

        $date = new \DateTime();
        $date_start = $date->format('Y-m-d');
        $date_end = $date->modify('+1 day')->format('Y-m-d');

        foreach ($rooms as $room) {
            $room->free = Room::where('accommodation_id', '=', $room->accommodation_id)->sum('beds');
            $room->free_rooms = Room::where('accommodation_id', '=', $room->accommodation_id)->count();
            $room->image = Image::where('image_id', '=', $room->image_id)->pluck('image')->first();
        }

        return view('filtering', ['rooms' => $rooms, 'date_start' => $date_start, 'date_end' => $date_end]);
    }

    public function getResults(Request $request)
    {
        $now = new \DateTime();
        $now = $now->format('Y-m-d H:i:s');

        // DATES ARE EMPTY
        if ($request->date_start == null or $request->date_end == null) {
            return FilteringController::index();
        } // START DATE IS BIGGER OR EQUAL TO END DATE
        elseif (($request->date_start > $request->date_end) OR ($request->date_start == $request->date_end)) {
            flash('Datum příjezdu nesmí být větší nebo stejné datumu odjezdu!', 'danger');
            return redirect()->back()->withInput();
        } // START DATE IS SMALLER THAN CURRENT DATE
        elseif ($request->date_start . ' 23:59:00' < $now) {
            flash('Datum příjezdu nesmí být menší než je aktuální datum!', 'danger');
            return redirect()->back()->withInput();
        }

        $date_start = $request->date_start . " 14:00:00";
        $date_end = $request->date_end . " 10:00:00";

        $rooms = DB::select('select rooms.room_id, rooms.room_name, rooms.image_id, accommodation_name, rooms.accommodation_id, rooms.beds, rooms.price
                                        from accommodations
                                        join rooms using(accommodation_id)
                                        where permission = ? AND NOT EXISTS(
                                            select booked.room_id 
                                            from booked 
                                            where (date BETWEEN ? and ?) 
                                            AND (rooms.room_id = booked.room_id)
                                        ) order by accommodation_name, room_name', ['true', $date_start, $date_end]);

        foreach ($rooms as $room) {
            $free = DB::select('select  sum(beds) as free
                                        from accommodations
                                        join rooms using(accommodation_id)
                                        where permission = ? AND rooms.accommodation_id = ?  AND NOT EXISTS(
                                            select booked.room_id
                                            from booked 
                                            where (date BETWEEN ? and ?) 
                                            AND (booked.room_id = rooms.room_id)
                                        ) ', ['true', $room->accommodation_id, $date_start, $date_end, $room->room_id]);

            $free_rooms = DB::select('select accommodation_id, count(room_id) as free_rooms
                                        from accommodations
                                        join rooms using(accommodation_id)
                                        where permission = ? AND rooms.accommodation_id = ?  AND NOT EXISTS(
                                            select booked.room_id 
                                            from booked 
                                            where (date BETWEEN ? and ?) 
                                            AND (booked.room_id = rooms.room_id)
                                        )', ['true', $room->accommodation_id, $date_start, $date_end, $room->room_id]);
            $room->free_rooms = $free_rooms[0]->free_rooms;
            $room->free = $free[0]->free;

            $room->image = Image::where('image_id', '=', $room->image_id)->pluck('image')->first();
        }
        return view('filtering', ['rooms' => $rooms, 'date_start' => $request->date_start, 'date_end' => $request->date_end]);
    }
}