<?php
namespace WpBookingHotelBlock\block_trending_destinations_hotel;
use WpBookingHotel\Factory as FactoryAlias;
use WpBookingHotel\Mvc\Model\Model as ModelAlias;
use WpBookingHotel\Mvc\Model\ModelList;
use WpBookingHotel\Registry\Registry;
use WpBookingHotel\Utilities\Utility as UtilityAlias;
use WpBookingHotelFrontend\app\helpers\FrontendHelper;

class block_trending_destinations_hotel_helper{

    public static function getList_Hotel_location($filter_by_country=array())
    {

        $db= FactoryAlias::getDBO();
        $query=$db->getQuery(true);
        $query->select('DISTINCT country.*')
            ->from(UtilityAlias::getTableName('country').' AS country ')
            ->where('country.id IN ('.implode(",",$filter_by_country).')')
        ;
        $list=$db->setQuery($query)->loadObjectList();

        foreach ($list as &$item){
            $query->clear()
                ->select('DISTINCT location.*')
                ->from(UtilityAlias::getTableName('location').' AS location')
                ->leftJoin(UtilityAlias::getTableName('state').' AS state ON state.id = location.state_id')
                ->where('state.country_id='.(int)$item->id)
            ;
            $list_location = $db->setQuery($query)->loadObjectList();


            foreach ($list_location as $location) {
                $query->clear()
                    ->select("hotel.*")
                    ->from(UtilityAlias::getTableName("hotel")." AS hotel")
                    ->leftJoin(UtilityAlias::getTableName("hotelproperty")." AS hotelproperty ON hotelproperty.hotel_id=hotel.id")
                    ->where("hotelproperty.object_id =".(int)$location->id)
                    ->where("hotelproperty.type =".$query->quote(FrontendHelper::getWpBookingDestinationKey()));
                $list_hotel = $db->setQuery($query)->loadObjectList();
                foreach ($list_hotel as &$hotel){
                    $query->clear()
                        ->select('DISTINCT review.*')
                        ->from(UtilityAlias::getTableName('review').' AS review')
                        ->leftJoin(UtilityAlias::getTableName('hotelproperty').' AS hotelproperty ON hotelproperty.object_id=review.id')
                        ->where('hotelproperty.hotel_id='.(int)$hotel->id)
                        ->where('hotelproperty.type='.$query->quote(FrontendHelper::getWpBookingReviewKey()));
                    $list_review = $db->setQuery($query)->loadObjectList();
                    $rating_sum = 0;
                    foreach ($list_review as $review) {
                        if(isset($review->rating)) {
                            $rating_sum += $review->rating;
                        }
                    }
                    $query->clear()
                        ->select('wishlist.user_id')
                        ->from(UtilityAlias::getTableName('wishlist').' AS wishlist')
                        ->where('wishlist.hotel_id='.(int)$hotel->id);
                        $hotel->user_wishlist = $db->setQuery($query)->loadColumn();

                    $hotel->list_review = $list_review;
                    $hotel->rating_avg = !empty($list_review)?($rating_sum/count($list_review)):0;
                    $hotel->location_address = isset($location->address)?$location->address:'';

                    $register = new Registry();
                    $register->loadString($hotel->params);
                    $hotel->params = $register;
                }
                $location->list_hotel = $list_hotel;
            }
            $item->list_location = $list_location;
        }
        return $list;
    }


}