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; }