<?php
defined('_WPBOOKINGPRO_EXEC') or die;
use WooBooking\CMS\Utilities\Utility;
if(!class_exists("WpBookingPro_RatesModel")) {
    /**
     * Class WpBookingPro_RatesModel
     */
    class WpBookingPro_RatesModel extends WoobookingModelList
    {
        /**
         * @var string
         */
        public $table_name = "rate";
        /**
         * @var string
         */
        public $context = "rates";

        /**
         * @param $event_id
         * @return mixed
         */
        public function get_rate_available_by_event_id($event_id)
        {
            $query = $this->db->getQuery(true);
            $now = Factory::getDate();
            $query->select("DISTINCT rate.*")
                ->from($this->getTableName('rate') . ' AS rate')
                //->where('rate.to>=' . $query->quote($now->toSql()))
                ->where('rate.object_id=' . (int)$event_id)
                ->where('rate.type=' . $query->quote(WpBookingPro_RateHelper::getEventName()));
            $list = $this->db->setQuery($query)->loadObjectList();
            foreach ($list as $item) {
                $from = Factory::getDate($item->from);
                $to = Factory::getDate($item->to);
                //h:i:s A
                $item->start_time = $from->format('h:i A');
                $item->end_time = $to->format('h:i A');
            }
            foreach ($list as &$item) {
                $item->event_price_format = WpBookingPro_FrontEndHelper::format_currency($item->event_price);
                $start_date = Factory::getDate($item->start_date);
                $end_date = Factory::getDate($item->end_date);
                $since_start = $start_date->diff($end_date);
                $item->rate_duration = WoobookingText::_sprintf('TIME_DURATION', $since_start->h, $since_start->i);
                $query->clear()
                    ->select("employee.*")
                    ->from($this->getTableName('employee') . ' AS employee')
                    ->leftJoin($this->getTableName('event_employee') . ' AS event_employee ON event_employee.employee_id = employee.id')
                    ->where('event_employee.event_id=' . (int)$event_id);
                $list_employee = $this->db->setQuery($query)->loadObjectList();
                foreach ($list_employee as &$employee) {
                    $employee->full_name = Utility::getFullName($employee->first_name, $employee->last_name);
                }
                $item->list_employee = $list_employee;
            }
            return $list;
        }

        /**
         * @param $data
         * @return mixed
         */
        public function get_rate_available_date($data)
        {

            $db = Factory::getDBO();
            $query = $db->getQuery(true);
            $now = Factory::getDate();
            $query->select("DISTINCT rate.*")
                ->from($this->getTableName('rate') . ' AS rate')
                ->where('rate.type=' . $query->quote(WpBookingPro_RateHelper::getEventName()))
                ->where('rate.to>=' . $query->quote($now->toSql()))
                ->group('rate.object_id');
            $query
                ->innerJoin($this->getTableName('event') . ' AS event ON event.id=rate.object_id ')
                ->select("event.name AS name_event,event.price as event_price,event.max_allowed_person,event.min_allowed_person")
                ->leftJoin($this->getTableName('service') . ' AS service ON service.id=event.service_id')
                ->select("service.id AS service_id,service.name AS service_name,service.image AS service_image,service.multiply_number AS multiply_number")
                ->leftJoin($this->getTableName('event_employee') . ' as event_employee ON event_employee.event_id=event.id')
                ->leftJoin($this->getTableName('location') . ' AS location ON location.id=event.location_id')
                ->select('location.address as event_address');

            $service_id = $data['service_id'];
            if (is_array($service_id) && !empty($service_id)) {
                $service_id = array_filter($service_id);
                $query->where('event.service_id IN (' . implode(",", $service_id) . ')');
            } else {
                if ($service_id) {
                    $query->where('event.service_id=' . (int)$service_id);
                }
            }
            $keywork = $data['keyword'];
            if (trim($keywork) != "") {
                $query->where('event.name LIKE ' . $query->quote("%$keywork%"));
            }

            $employee_id = $data['employee_id'];
            if (is_array($employee_id) && !empty($employee_id)) {
                $query->where('event_employee.employee_id IN (' . implode(",", $employee_id) . ')');
            } else {
                if ($employee_id) {
                    $query->where('event_employee.employee_id=' . ($employee_id));
                }
            }

            $location_id = $data['location_id'];
            if (is_array($location_id) && !empty($location_id)) {
                $query->where('event.location_id IN (' . implode(",", $location_id) . ')');
            } else {
                if ($location_id) {
                    $query->where('event.location_id=' . (int)$location_id);
                }
            }

            $date = $data['date'];
            if ($date) {
                $date = Factory::getDate($date);
                $toSqlOfDate = $date->toSql();
                $query->where('rate.to>=' . $query->quote($toSqlOfDate));
                $query->where('rate.from<=' . $query->quote($toSqlOfDate));
            }
            $list_rate = $db->setQuery($query)->loadObjectList();
            foreach ($list_rate as &$rate) {
                $rate->event_price_format = WpBookingPro_FrontEndHelper::format_currency($rate->event_price);
                $start_date = Factory::getDate($rate->start_date);
                $end_date = Factory::getDate($rate->end_date);
                $since_start = $start_date->diff($end_date);
                $rate->rate_duration = WoobookingText::_sprintf('TIME_DURATION', $since_start->h, $since_start->i);
                $query->clear()
                    ->select("employee.*")
                    ->from($this->getTableName('employee') . ' AS employee')
                    ->leftJoin($this->getTableName('event_employee') . ' AS event_employee ON event_employee.employee_id = employee.id')
                    ->where('event_employee.event_id=' . (int)$rate->object_id);
                $list_employee = $this->db->setQuery($query)->loadObjectList();
                foreach ($list_employee as &$employee) {
                    $employee->full_name = Utility::getFullName($employee->first_name, $employee->last_name);
                }
                $rate->list_employee = $list_employee;
            }
            return $list_rate;
        }
        public function get_rate_available_date_booking_training($data)
        {
            $db = Factory::getDBO();
            $query = $db->getQuery(true);
            $now = Factory::getDate();
            $query->select("DISTINCT rate.*")
                ->from($this->getTableName('rate') . ' AS rate')
                ->where('rate.type=' . $query->quote(WpBookingPro_RateHelper::getEventName()))
                ->where('rate.to>=' . $query->quote($now->toSql()));
            $query
                ->innerJoin($this->getTableName('event') . ' AS event ON event.id=rate.object_id ')
                ->select("event.name AS name_event,event.price as event_price,event.max_allowed_person,event.min_allowed_person")
                ->leftJoin($this->getTableName('service') . ' AS service ON service.id=event.service_id')
                ->select("service.id AS service_id,service.name AS service_name,service.image AS service_image,service.multiply_number AS multiply_number")
                ->leftJoin($this->getTableName('event_employee') . ' as event_employee ON event_employee.event_id=event.id')
                ->leftJoin($this->getTableName('location') . ' AS location ON location.id=event.location_id')
                ->select('location.address as event_address');

            $service_id = $data['service_id'];
            if (is_array($service_id) && !empty($service_id)) {
                $service_id = array_filter($service_id);
                $query->where('event.service_id IN (' . implode(",", $service_id) . ')');
            } else {
                if ($service_id) {
                    $query->where('event.service_id=' . (int)$service_id);
                }
            }
            $keywork = $data['keyword'];
            if (trim($keywork) != "") {
                $query->where('event.name LIKE ' . $query->quote("%$keywork%"));
            }

            $employee_id = $data['employee_id'];
            if (is_array($employee_id) && !empty($employee_id)) {
                $query->where('event_employee.employee_id IN (' . implode(",", $employee_id) . ')');
            } else {
                if ($employee_id) {
                    $query->where('event_employee.employee_id=' . ($employee_id));
                }
            }

            $location_id = $data['location_id'];
            if (is_array($location_id) && !empty($location_id)) {
                $query->where('event.location_id IN (' . implode(",", $location_id) . ')');
            } else {
                if ($location_id) {
                    $query->where('event.location_id=' . (int)$location_id);
                }
            }

            $date = $data['date'];
            if ($date) {
                $date = Factory::getDate($date);
                $toSqlOfDate = $date->toSql();
                $query->where('rate.to>=' . $query->quote($toSqlOfDate));
                $query->where('rate.from<=' . $query->quote($toSqlOfDate));
            }
            $list_rate = $db->setQuery($query)->loadObjectList();
            foreach ($list_rate as &$rate) {
                $rate->event_price_format = WpBookingPro_FrontEndHelper::format_currency($rate->event_price);
                $start_date = Factory::getDate($rate->start_date);
                $end_date = Factory::getDate($rate->end_date);
                $since_start = $start_date->diff($end_date);
                $rate->rate_duration = WoobookingText::_sprintf('TIME_DURATION', $since_start->h, $since_start->i);
                $query->clear()
                    ->select("employee.*")
                    ->from($this->getTableName('employee') . ' AS employee')
                    ->leftJoin($this->getTableName('event_employee') . ' AS event_employee ON event_employee.employee_id = employee.id')
                    ->where('event_employee.event_id=' . (int)$rate->object_id);
                $list_employee = $this->db->setQuery($query)->loadObjectList();
                foreach ($list_employee as &$employee) {
                    $employee->full_name = Utility::getFullName($employee->first_name, $employee->last_name);
                }
                $rate->list_employee = $list_employee;
            }
            return $list_rate;
        }
    }
}
