public static function getHotelExcursions($type = -1, $hotelId, $startDate, $endDate, $excursionIds = array(), $nrBooked = array(2), $children = 0, $discountCode = null, $checkAvailability = true, $confirmationId = null)
{
$db = JFactory::getDBO();
$excursionFilter = "";
if ($hotelId != -1) {
$excursionFilter = " and e.hotel_id= {$hotelId} ";
}
if (count($excursionIds) > 0) {
$excursionFilter .= " and e.id in (";
foreach ($excursionIds as $id) {
$excursionFilter .= $id . ',';
}
$excursionFilter = substr($excursionFilter, 0, -1);
$excursionFilter .= ")";
}
$availabilityFilter = "";
if ($type != -1) {
$availabilityFilter = " and e.type = " . $type;
}
$languageTag = JRequest::getVar('_lang');
$availabilityFilter .= " and e.is_available = 1";
if (!$checkAvailability) {
$availabilityFilter = "";
}
//get hotel excursions
$query = "select *,e.id as excursion_id,e.name as excursion_name,e.ignored_dates as excursionIgnoredDates,hlt.content as excursion_main_description,er.name as rate_name, er.id as rate_id ,h.reservation_cost_val AS reservation_cost_val, h.reservation_cost_proc AS reservation_cost_proc\r\n\t\t\t\tfrom #__hotelreservation_excursions e\r\n\t\t\t\tinner join #__hotelreservation_excursion_rates er on e.id = er.excursion_id\r\n\t\t\t\tinner join #__hotelreservation_hotels h\tON h.hotel_id = e.hotel_id\r\n\t\t\t\tleft join #__hotelreservation_currencies hc on h.currency_id= hc.currency_id\r\n\t\t\t\tleft join #__hotelreservation_countries hrc on h.country_id= hrc.country_id\r\n\t\t\t\tleft join\r\n\t\t\t\t\t(select * from\r\n\t\t\t\t\t #__hotelreservation_language_translations\r\n\t\t\t\t\t where type = " . EXCURSION_TRANSLATION . "\r\n\t\t\t\t\t and language_tag = '{$languageTag}'\r\n\t\t\t\t\t ) as hlt on hlt.object_id = e.id\r\n\t\t\t\twhere 1 {$availabilityFilter} \r\n\t\t\t\tand e.front_display=1 \r\n\t\t\t\t{$excursionFilter}\r\n\t\t\t\torder by e.excursion_order";
//echo($query);
$db->setQuery($query);
$excursions = $db->loadObjectList();
$number_days = (strtotime($startDate) - strtotime($endDate)) / (60 * 60 * 24);
//get hotel rates
$totalPrice = 0;
$totalExcursionsPrice = 0;
$excursionIds = array();
foreach ($excursions as $excursion) {
$hotelId = $excursion->hotel_id;
array_push($excursionIds, $excursion->excursion_id);
$query = "select * from #__hotelreservation_excursion_rate_prices r\r\n\t\t\twhere rate_id={$excursion->rate_id} and '{$startDate}'<= date and date<='{$endDate}'";
//dmp($query);
$db->setQuery($query);
$excursionRateDetails = $db->loadObjectList();
$excursion->excursionRateDetails = $excursionRateDetails;
//calculate available number of room
$excursion->nrExcursionsAvailable = $excursion->availability;
//dmp("id: ".$room->room_id);
//dmp($room->nrRoomsAvailable);
$excursion->is_disabled = false;
$daily = array();
$number_days = (strtotime($endDate) - strtotime($startDate)) / (60 * 60 * 24);
$isHotelAvailable = true;
if (!HotelService::isHotelAvailable($hotelId, $startDate, $endDate) && $checkAvailability) {
$isHotelAvailable = false;
}
if (!$isHotelAvailable) {
$excursion->is_disabled = true;
}
$d = strtotime($startDate);
$currentDayNr = 1;
$nr_d = 'excursion_day_' . date("N", $d);
if ($excursion->{$nr_d} == 0) {
$excursion->is_disabled = true;
}
//
$ignoredDates = explode(",", $excursion->excursionIgnoredDates);
//determine aspects for each day of booking period
for ($d = strtotime($startDate); $d < strtotime($endDate);) {
$dayString = date('Y-m-d', $d);
if (array_search($dayString, $ignoredDates)) {
$excursion->is_disabled = true;
}
//set default price from rate
$weekDay = date("N", $d);
$string_price = "price_" . $weekDay;
$dayPrice = $excursion->{$string_price};
$childPrice = $excursion->child_price;
//check if a custom price is set
if (count($excursionRateDetails)) {
foreach ($excursionRateDetails as $excursionRateDetail) {
if ($excursionRateDetail->date == $dayString) {
$dayPrice = $excursionRateDetail->price;
$childPrice = $excursionRateDetail->child_price;
$excursion->nrExcursionsAvailable = $excursionRateDetail->availability;
}
}
}
$eId = $excursion->excursion_id;
$nrItemsBooked = 0;
if (isset($nrBooked[$eId])) {
$nrItemsBooked = $nrBooked[$eId];
}
$excursion->nrItemsBooked = $nrItemsBooked;
//apply current discounts
$query = " SELECT\r\n\t\t\t\t\t\t\t\t\tdiscount_id,\r\n\t\t\t\t\t\t\t\t\tdiscount_name,\r\n\t\t\t\t\t\t\t\t\tdiscount_datas,\r\n\t\t\t\t\t\t\t\t\tdiscount_datae,\r\n\t\t\t\t\t\t\t\t\tdiscount_value as discount_value,\r\n\t\t\t\t\t\t\t\t\tpercent,\r\n\t\t\t\t\t\t\t\t\tminimum_number_days,\r\n\t\t\t\t\t\t\t\t\tmaximum_number_days,\r\n\t\t\t\t\t\t\t\t\tminimum_number_persons,\r\n\t\t\t\t\t\t\t\t\tcheck_full_code,\r\n\t\t\t\t\t\t\t\t\tcode,\r\n\t\t\t\t\t\t\t\t\tprice_type\r\n\t\t\t\t\t\t\t\tFROM #__hotelreservation_discounts\r\n\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\t\tis_available = 1 and only_on_offers = 0\r\n\t\t\t\t\t\t\t\t\tAND FIND_IN_SET( " . $excursion->excursion_id . ", excursion_ids )\r\n\t\t\t\t\t\t\t\t\tAND\t'" . date('Y-m-d', $d) . "' BETWEEN discount_datas AND discount_datae\r\n\t\t\t\t\t\t\t\t\tAND\tIF( minimum_number_days > 0, minimum_number_days <= {$currentDayNr}, 1 )\r\n\t\t\t\t\t\t\t\t\tAND\tIF( minimum_number_persons > 0, minimum_number_persons <= {$nrItemsBooked}, 1 )\r\n\t\t\t\t\t\t\t\t\tAND\tIF( maximum_number_days> 0, maximum_number_days >= {$currentDayNr}, 1 )\r\n\t\t\t\t\t\t\t\t\tAND\tIF( minimum_amount> 0, minimum_amount <= {$totalPrice}, 1 )\r\n\t\t\t\t\t\t\t\tORDER BY discount_datas";
$db->setQuery($query);
$discounts = $db->loadObjectList();
$selectedDiscounts = array();
$discountValue = 0;
$discountPercent = 0;
$excursion->hasDiscounts = count($discounts) > 0;
//dmp($discounts);
foreach ($discounts as $discount) {
$match = false;
if (isset($discount->code) && isset($discountCode) && strlen($discount->code) > 0) {
if ($discount->check_full_code == 1) {
$match = $discountCode == $discount->code;
} else {
$match = strpos($discountCode, $discount->code) === 0;
}
}
if ($match || !isset($discount->code) || strlen($discount->code) == 0) {
$selectedDiscounts[] = $discount;
if ($discount->percent) {
$discountPercent += $discount->discount_value;
} else {
$discountValue += $discount->discount_value;
}
}
}
//dmp($selectedDiscounts);
//apply percent
$dayPrice = round($dayPrice - $dayPrice * ($discountPercent / 100), 2);
//apply value
$dayPrice = $dayPrice - $discountValue;
if ($excursion->nrExcursionsAvailable == 0) {
$excursion->is_disabled = true;
}
$day = array('date' => $dayString, 'price' => $dayPrice, 'price_final' => $dayPrice, 'display_price_final' => $dayPrice, 'discounts' => $selectedDiscounts, 'nrExcursionsAvailable' => $excursion->nrExcursionsAvailable);
$totalPrice += $dayPrice;
$currentDayNr += 1;
$totalExcursionsPrice += $dayPrice * $nrItemsBooked;
$daily[$dayString] = $day;
$d = strtotime(date('Y-m-d', $d) . ' + 1 day ');
}
$excursion->daily = $daily;
//average price per excursion
$excursion->excursion_average_price = JHotelUtil::fmt($totalPrice / $number_days, 2);
$excursion->pers_total_price = JHotelUtil::fmt($totalPrice / $number_days, 2);
//dmp($excursion->excursion_average_price);
//set pictures
$query = " SELECT *\r\n\t\t\t\t\tFROM #__hotelreservation_excursion_pictures\r\n\t\t\t\t\tWHERE excursion_id = " . $excursion->excursion_id . " AND picture_enable = 1\r\n\t\t\t\t\tORDER BY picture_id\r\n\t\t\t\t\t";
$db->setQuery($query);
$excursion->pictures = $db->loadObjectList();
}
self::setExcursionDisplayPrice($excursions);
if (is_array($nrBooked)) {
$excursions = self::applyPackageDiscounts($excursions, $totalExcursionsPrice, implode(",", array_keys($nrBooked)), $startDate);
}
$excursions = self::checkExcursionAvailability($excursions, array(), $hotelId, $startDate, $endDate, $confirmationId);
return $excursions;
}
