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

class block_top_destinations_hotel_helper {
    public static function getTopDestination($filter_by_country){
        $db=Factory::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, 0,6)->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();
            $item->list_location = $list_location;
            foreach ($list_location as $location) {
                $query->clear()
                    ->select('COUNT(hotelproperty.hotel_id) AS number_hotel')
                    ->from(UtilityAlias::getTableName('hotelproperty').' AS hotelproperty')
                    ->where('hotelproperty.type ='.$query->quote(FrontendHelper::getWpBookingDestinationKey()))
                    ->where('hotelproperty.object_id ='.(int)(isset($location->id)?(int)$location->id:0))
                ;
                $number_hotel = $db->setQuery($query)->loadResult();
                $location->number_hotel =$number_hotel;

            }
            $query->clear()
                ->select('gallery.*')
                ->from(UtilityAlias::getTableName('gallery').' AS gallery')
                ->where('gallery.object_id='.(int)$item->id)
                ->where('gallery.type='.$query->quote('country'))
            ;
            $gallery = $db->setQuery($query)->loadObjectList();
            $item->gallery = $gallery;

        }
        return $list;
    }
}