<?php
defined('_WPBOOKINGPRO_EXEC') or die;
if(!class_exists("WpBookingPro_servicesModel")) {
    /**
     * Class WpBookingPro_servicesModel
     */
    class WpBookingPro_servicesModel extends WoobookingModelList
    {
        /**
         * @var string
         */
        public $table_name = "service";
        /**
         * @var string
         */
        public $context = "services";
        public function getListQuery()
        {
            $input=Factory::getInput();
            $data=$input->getData();
            $query=$this->db->getQuery(true);
            $query->select("a.*")
                ->from($this->getTableName().' AS a')
                ->leftJoin($this->getTableName('category') . ' AS category ON category.id=a.category_id')
                ->select('category.name as category_name')
                ->leftJoin($this->getTableName('location') . ' AS location ON location.id=a.location_id')
                ->select('location.name as location_name')
            ;
            $keyword=$input->getString('keyword',"");
            if($keyword!==""){
                $query->where('a.name LIKE '.$query->quote("%$keyword%"));
            }
            $list_category=array();            
            if(isset($data['category_id']) &&  is_numeric($data['category_id'])){
                $list_category=array($data['category_id']);
            }else{

                if(!empty($data['category_id']) && count($data['category_id'])>0){
                   $list_category=$data['category_id'];
                }
            }

            if(!empty($list_category)){
                $query->where('a.category_id  IN('.implode($list_category,",").')')
                ;
            }

            if(isset($data['service_id']) && is_array($data['service_id'])&&!empty($data['service_id']) && count($data['service_id'])>0){
                    $query->where('a.id  IN('.implode($data['service_id'],",").')')
                    ;
            } else if(isset($data['service_id']) && trim($data['service_id'])!='') {
                $query->where('a.id='.(int)$data['service_id']);
            }

            if(isset($data['location_id']) && trim($data['location_id'])!==''){
                $query->where('a.location_id='.(int)$data['location_id']);
            }
            if(isset($data['employee_id']) && trim($data['employee_id'])!==''){
                $query->leftJoin($this->getTableName('service_employee') . ' AS service_employee ON service_employee.service_id=a.id')
                    ->where('service_employee.employee_id='.(int)($data['employee_id']))
                ;
            }

            $key_sort_by="a.ordering";
            if(isset($data['key_sort']) && trim($data['key_sort'])!==''){
                $key_sort_by=$data['key_sort'];
            }
            $direction="ASC";
            if(isset($data['direction']) && trim($data['direction'])!==''){
                $direction=$data['direction'];
            }
            $query->order("$key_sort_by $direction");
            return $query;
        }
        public function getTotalRecord(){
            $query=$this->getListQuery();
            $db=Factory::getDBO();
            $list_service=$db->setQuery($query)->loadObjectList();
            return count($list_service);
        }

        public function getList()
        {
            $input=Factory::getInput();
            $data=$input->getData();
            $start=0;
            if(isset($data['start']) && $data['start']!==""){
                $start=(int)$data['start'];
            }
            $limit=10;
            if(isset($data['limit']) && $data['limit']!==""){
                $limit=(int)$data['limit'];
            }
            $query=$this->getListQuery();
            $db=Factory::getDBO();
            $list_service=$db->setQuery($query,$start,$limit)->loadObjectList();
            $query=$this->_db->getQuery(true);
            foreach ($list_service as &$item){
                $query->clear()
                    ->select('employee.*')
                    ->from($this->getTableName('employee') . ' AS employee')
                    ->leftJoin($this->getTableName('service_employee') . ' as service_employee ON service_employee.employee_id=employee.id')
                    ->where('service_employee.service_id=' . (int)$item->id);
                $item->list_employee = $this->db->setQuery($query)->loadObjectList();
                $query->clear()
                    ->select('review.*')
                    ->from($this->getTableName('review') . ' AS review')
                    ->where('review.service_id=' . (int)$item->id);
                $list_reviews = $this->db->setQuery($query)->loadObjectList();
                foreach ($list_reviews as &$review){
                    $review->rating_avg=($review->rating_service + $review->rating_staff + $review->rating_ambience)/3;
                }
                $item->list_reviews=$list_reviews;

            }
            return $list_service;
        }

        /**
         * @return mixed
         * @throws Exception
         */
        public function getListServiceCategory()
        {
            $db = Factory::getDBO();
            $query = $db->getQuery(true);
            $modelCategories = $this->getModel('categories');
            $list_service_category = $modelCategories->getList();
            foreach ($list_service_category as &$category) {
                $query->clear()
                    ->select('service.*')
                    ->from(WoobookingTable::getWBTable('service') . ' AS service')
                    ->where('category_id=' . $category->id);
                $category->list_service = $db->setQuery($query)->loadObjectList();
            }
            return $list_service_category;
        }

        /**
         * @param $list_rate_id
         * @return mixed
         */
        public function getListServiceByListRateId($list_rate_id)
        {
            $db = Factory::getDBO();
            $query = $db->getQuery(true);
            $query->select("service.*")
                ->from($this->getTableName('service') . ' AS service')
                ->leftJoin($this->getTableName('event') . ' AS event ON event.service_id=service.id')
                ->leftJoin($this->getTableName('rate') . ' AS rate ON rate.object_id=event.id')
                ->leftJoin($this->getTableName('gallery') . ' AS gallery ON gallery.object_id=service.id AND type=' . $query->quote('service'))
                ->select('gallery.path AS service_image_intro')
                ->where('rate.id IN(' . implode(",", $list_rate_id) . ')')
                ->where('rate.type=' . $query->quote(WpBookingPro_RateHelper::getEventName()));
            $list = $db->setQuery($query)->loadObjectList();
            return $list;
        }

        /**
         * @param $category_id
         * @return mixed
         */
        public function getListServiceByCategoryId($category_id)
        {
            $db = Factory::getDBO();
            $query = $db->getQuery(true);
            $query->select("service.*")
                ->from($this->getTableName('service') . ' AS service')
                ->leftJoin($this->getTableName('service_employee') . ' AS service_employee ON service_employee.service_id=service.id')
                ->leftJoin($this->getTableName('gallery') . ' AS gallery ON gallery.object_id=service.id AND type=' . $query->quote('service'))
                ->select('gallery.path AS service_image_intro')
                ->where('service.category_id=' . (int)$category_id)
                ->group('service.id');
            $list = $db->setQuery($query)->loadObjectList();
            foreach ($list as &$item) {
                $query->clear()
                    ->select('employee.*')
                    ->from($this->getTableName('employee') . ' AS employee')
                    ->leftJoin($this->getTableName('service_employee') . ' as service_employee ON service_employee.employee_id=employee.id')
                    ->where('service_employee.service_id=' . (int)$item->id);
                $item->list_employee = $this->db->setQuery($query)->loadObjectList();
            }
            return $list;
        }

        /**
         * @param $list_service_id
         * @return mixed
         */
        public function getListServiceByArrayServiceId($list_service_id)
        {
            $query = $this->db->getQuery(true);
            $query->select("service.*")
                ->from($this->getTableName('service') . ' AS service');
            if (is_array($list_service_id) && count($list_service_id) > 0) {
                $query->where('service.id IN(' . implode(',', $list_service_id) . ')');
            }
            $listService = $this->db->setQuery($query)->loadObjectList();
            return $listService;
        }

        /**
         * @param $service_id
         * @return mixed
         */
        public function get_service_available_rate_by_service_id($service_id)
        {
            $query = $this->db->getQuery(true);
            $now = Factory::getDate();
            $query->select('service.*')
                ->from($this->getTableName() . ' AS service')
                ->leftJoin($this->getTableName('event') . ' AS event ON event.service_id=' . (int)$service_id)
                ->select('event.id AS event_id')
                ->where('service.id=' . (int)$service_id);
            $list = $this->db->setQuery($query)->loadObjectList();
            foreach ($list as &$item) {
                $query->clear()
                    ->select('DISTINCT rate.*')
                    ->from($this->getTableName('rate') . ' AS rate')
                    ->where('rate.object_id=' . (int)$item->event_id . ' AND rate.to >=' . $query->quote($now));
                $list_rate = $this->db->setQuery($query)->loadObjectList();
                foreach ($list_rate as &$rate) {
                    $from = Factory::getDate($rate->from);
                    $to = Factory::getDate($rate->to);
                    //h:i:s A
                    $rate->start_time = $from->format('h:i A');
                    $rate->end_time = $to->format('h:i A');
                }
                $item->list_rate = $list_rate;
            }
            return $list;
        }

        public function get_service_available($data) {
            $db = Factory::getDBO();
            $query = $db->getQuery(true);
            $query->clear()
                ->select('DISTINCT service.*')
                ->from($this->getTableName('service').' AS service')
                ->leftJoin($this->getTableName('category').' AS category ON category.id=service.category_id')
                ->where('category.published=1')
                ->where('service.published=1')
                ;
            $service_id = $data['service_id'];
            if (is_array($service_id) && !empty($service_id)) {
                $service_id = array_filter($service_id);
                $query->where('service.id IN (' . implode(",", $service_id) . ')');
            } else {
                if ($service_id) {
                    $query->where('service.id=' . (int)$service_id);
                }
            }
            $keywork = $data['keyword'];
            if (trim($keywork) != "") {
                $query->where('service.name LIKE ' . $query->quote("%$keywork%"));
            }

            $employee_id = $data['employee_id'];
            if (trim($employee_id) != '') {
                $query->leftJoin($this->getTableName('service_employee'). ' AS service_employee ON service_employee.service_id=service.id')
                    ->where('service_employee.employee_id=' . ($employee_id))
                ;
            }

            $location_id = $data['location_id'];
            if (trim($location_id) != '') {
                $query->where('service.location_id=' . (int)$location_id);
            }
            $list_service = $db->setQuery($query)->loadObjectList();
            foreach ($list_service as &$service) {
                $query->clear()
                    ->select('employee.*')
                    ->from($this->getTableName('employee') . ' AS employee')
                    ->leftJoin($this->getTableName('service_employee') . ' as service_employee ON service_employee.employee_id=employee.id')
                    ->where('service_employee.service_id=' . (int)$service->id);
                $service->list_employee = $this->db->setQuery($query)->loadObjectList();
                $query->clear()
                    ->select('location.*')
                    ->from($this->getTableName('location').' AS location')
                    ->where('location.id='.(int)$service->location_id)
                    ;
                $location = $this->db->setQuery($query)->loadObjectList();
                $service->location = reset($location);
                $query->clear()
                    ->select('category.name')
                    ->from($this->getTableName('category').' AS category')
                    ->where('category.id='.(int)$service->category_id)
                ;
                $service->category_name = $this->db->setQuery($query)->loadResult();
            }
            return $list_service;
        }
    }
}
