| 1 |
<?php |
| 2 |
|
| 3 |
declare(strict_types=1); |
| 4 |
|
| 5 |
namespace Yatra\Repositories; |
| 6 |
|
| 7 |
use Yatra\Database\Tables\BookingsTable; |
| 8 |
use Yatra\Database\Tables\ClassificationsTable; |
| 9 |
use Yatra\Database\Tables\ReviewsTable; |
| 10 |
use Yatra\Database\Tables\TripClassificationsTable; |
| 11 |
use Yatra\Database\Tables\TripContentTable; |
| 12 |
use Yatra\Database\Tables\TripItineraryDayEntryTable; |
| 13 |
use Yatra\Database\Tables\TripItineraryDaysTable; |
| 14 |
use Yatra\Database\Tables\TripsTable; |
| 15 |
use Yatra\Repositories\TripDownloadRepository; |
| 16 |
use Yatra\Repositories\AttributeRepository; |
| 17 |
use Yatra\Models\Trip; |
| 18 |
use Yatra\Utils\Cache; |
| 19 |
use Yatra\Utils\QueryCache; |
| 20 |
use Yatra\Database\Tables\TripAvailabilityDatesTable; |
| 21 |
use Yatra\Database\Tables\TripAvailabilityRulesTable; |
| 22 |
use Yatra\Constants\ClassificationTypes; |
| 23 |
|
| 24 |
/** |
| 25 |
* Trip Repository |
| 26 |
* Handles database operations for trips with comprehensive field support |
| 27 |
* |
| 28 |
* Expert-level repository design: |
| 29 |
* - Relationship management (destinations, activities) |
| 30 |
* - JSON field handling |
| 31 |
* - Soft delete support |
| 32 |
* - Optimized queries with proper indexing |
| 33 |
*/ |
| 34 |
class TripRepository extends BaseRepository |
| 35 |
{ |
| 36 |
/** |
| 37 |
* Get bookings count map for given trip IDs. |
| 38 |
* |
| 39 |
* The trips table has a `bookings_count` column but it is not reliably maintained. |
| 40 |
* For list views, compute counts from the bookings table in one grouped query. |
| 41 |
* |
| 42 |
* @param int[] $tripIds |
| 43 |
* @param string[]|null $excludeStatuses |
| 44 |
* @return array<int,int> map trip_id => count |
| 45 |
*/ |
| 46 |
public function getBookingsCountMap(array $tripIds, ?array $excludeStatuses = null): array |
| 47 |
{ |
| 48 |
$tripIds = array_values(array_filter(array_map('intval', $tripIds))); |
| 49 |
if (empty($tripIds)) { |
| 50 |
return []; |
| 51 |
} |
| 52 |
|
| 53 |
// Default: ignore cancelled/failed bookings in counts (can be overridden) |
| 54 |
$excludeStatuses = $excludeStatuses ?? apply_filters( |
| 55 |
'yatra_trip_bookings_count_exclude_statuses', |
| 56 |
['cancelled', 'failed'], |
| 57 |
$tripIds |
| 58 |
); |
| 59 |
$excludeStatuses = is_array($excludeStatuses) ? array_values(array_filter(array_map('strval', $excludeStatuses))) : []; |
| 60 |
|
| 61 |
$bookingsTable = BookingsTable::getTableName(); |
| 62 |
|
| 63 |
$idPlaceholders = implode(',', array_fill(0, count($tripIds), '%d')); |
| 64 |
$where = "trip_id IN ({$idPlaceholders})"; |
| 65 |
$params = $tripIds; |
| 66 |
|
| 67 |
if (!empty($excludeStatuses)) { |
| 68 |
$stPlaceholders = implode(',', array_fill(0, count($excludeStatuses), '%s')); |
| 69 |
$where .= " AND status NOT IN ({$stPlaceholders})"; |
| 70 |
$params = array_merge($params, $excludeStatuses); |
| 71 |
} |
| 72 |
|
| 73 |
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- uses $wpdb->prepare with placeholders |
| 74 |
$sql = $this->wpdb->prepare( |
| 75 |
"SELECT trip_id, COUNT(*) AS cnt |
| 76 |
FROM {$bookingsTable} |
| 77 |
WHERE {$where} |
| 78 |
GROUP BY trip_id", |
| 79 |
$params |
| 80 |
); |
| 81 |
|
| 82 |
$rows = $this->wpdb->get_results($sql); |
| 83 |
$map = []; |
| 84 |
foreach ((array) $rows as $row) { |
| 85 |
$tId = (int) ($row->trip_id ?? 0); |
| 86 |
if ($tId > 0) { |
| 87 |
$map[$tId] = (int) ($row->cnt ?? 0); |
| 88 |
} |
| 89 |
} |
| 90 |
|
| 91 |
return $map; |
| 92 |
} |
| 93 |
|
| 94 |
/** |
| 95 |
* Cache for table existence checks to avoid repeated SHOW TABLES queries |
| 96 |
*/ |
| 97 |
private static array $tableExistsCache = []; |
| 98 |
|
| 99 |
/** |
| 100 |
* Rich text fields specific to trips |
| 101 |
*/ |
| 102 |
protected array $richTextFields = ['description']; |
| 103 |
|
| 104 |
/** |
| 105 |
* Integer fields specific to trips |
| 106 |
*/ |
| 107 |
protected array $integerFields = [ |
| 108 |
'id', |
| 109 |
'created_by', |
| 110 |
'updated_by', |
| 111 |
'difficulty_level', |
| 112 |
'duration_days', |
| 113 |
'duration_nights', |
| 114 |
'duration_hours', |
| 115 |
'booking_window_days', |
| 116 |
'booking_deadline_hours', |
| 117 |
'min_travelers', |
| 118 |
'max_travelers', |
| 119 |
'group_size', |
| 120 |
'age_min', |
| 121 |
'age_max', |
| 122 |
'version', |
| 123 |
'views_count', |
| 124 |
'bookings_count', |
| 125 |
'reviews_count' |
| 126 |
]; |
| 127 |
|
| 128 |
/** |
| 129 |
* JSON fields specific to trips |
| 130 |
*/ |
| 131 |
protected array $jsonFields = [ |
| 132 |
'included_items', |
| 133 |
'excluded_items', |
| 134 |
'frontend_tabs', |
| 135 |
'testimonial_review_ids', |
| 136 |
'custom_fields', |
| 137 |
'price_types', |
| 138 |
]; |
| 139 |
|
| 140 |
/** |
| 141 |
* Publish trips whose scheduled_publish_date has passed |
| 142 |
*/ |
| 143 |
public function publishScheduledTrips(string $now): void |
| 144 |
{ |
| 145 |
$table = esc_sql($this->table); |
| 146 |
$affected = $this->wpdb->query( |
| 147 |
$this->wpdb->prepare( |
| 148 |
"UPDATE {$table} |
| 149 |
SET status = 'publish', scheduled_publish_date = NULL, updated_at = %s |
| 150 |
WHERE scheduled_publish_date IS NOT NULL |
| 151 |
AND scheduled_publish_date <= %s |
| 152 |
AND status <> 'publish'", |
| 153 |
$now, |
| 154 |
$now |
| 155 |
) |
| 156 |
); |
| 157 |
if ($affected !== false && (int) $affected > 0) { |
| 158 |
Cache::invalidateAfterBulkTripTableWrites(); |
| 159 |
} |
| 160 |
} |
| 161 |
|
| 162 |
/** |
| 163 |
* Archive trips whose scheduled_unpublish_date has passed |
| 164 |
*/ |
| 165 |
public function archiveScheduledTrips(string $now): void |
| 166 |
{ |
| 167 |
$table = esc_sql($this->table); |
| 168 |
$affected = $this->wpdb->query( |
| 169 |
$this->wpdb->prepare( |
| 170 |
"UPDATE {$table} |
| 171 |
SET status = 'archived', scheduled_unpublish_date = NULL, updated_at = %s |
| 172 |
WHERE scheduled_unpublish_date IS NOT NULL |
| 173 |
AND scheduled_unpublish_date <= %s |
| 174 |
AND status = 'publish'", |
| 175 |
$now, |
| 176 |
$now |
| 177 |
) |
| 178 |
); |
| 179 |
if ($affected !== false && (int) $affected > 0) { |
| 180 |
Cache::invalidateAfterBulkTripTableWrites(); |
| 181 |
} |
| 182 |
} |
| 183 |
|
| 184 |
/** |
| 185 |
* Enable trips when seasonal_auto_enable is set and enable date reached |
| 186 |
*/ |
| 187 |
public function enableSeasonalTrips(string $today, string $now): void |
| 188 |
{ |
| 189 |
$table = esc_sql($this->table); |
| 190 |
$affected = $this->wpdb->query( |
| 191 |
$this->wpdb->prepare( |
| 192 |
"UPDATE {$table} |
| 193 |
SET status = 'publish', updated_at = %s |
| 194 |
WHERE seasonal_auto_enable = 1 |
| 195 |
AND seasonal_enable_date IS NOT NULL |
| 196 |
AND seasonal_enable_date <= %s |
| 197 |
AND status <> 'publish'", |
| 198 |
$now, |
| 199 |
$today |
| 200 |
) |
| 201 |
); |
| 202 |
if ($affected !== false && (int) $affected > 0) { |
| 203 |
Cache::invalidateAfterBulkTripTableWrites(); |
| 204 |
} |
| 205 |
} |
| 206 |
|
| 207 |
/** |
| 208 |
* Disable trips when seasonal_auto_enable is set and disable date reached |
| 209 |
*/ |
| 210 |
public function disableSeasonalTrips(string $today, string $now): void |
| 211 |
{ |
| 212 |
$table = esc_sql($this->table); |
| 213 |
$affected = $this->wpdb->query( |
| 214 |
$this->wpdb->prepare( |
| 215 |
"UPDATE {$table} |
| 216 |
SET status = 'archived', updated_at = %s |
| 217 |
WHERE seasonal_auto_enable = 1 |
| 218 |
AND seasonal_disable_date IS NOT NULL |
| 219 |
AND seasonal_disable_date <= %s |
| 220 |
AND status = 'publish'", |
| 221 |
$now, |
| 222 |
$today |
| 223 |
) |
| 224 |
); |
| 225 |
if ($affected !== false && (int) $affected > 0) { |
| 226 |
Cache::invalidateAfterBulkTripTableWrites(); |
| 227 |
} |
| 228 |
} |
| 229 |
|
| 230 |
/** |
| 231 |
* Get table name |
| 232 |
*/ |
| 233 |
protected function getTableName(): string |
| 234 |
{ |
| 235 |
return TripsTable::getTableName(); |
| 236 |
} |
| 237 |
|
| 238 |
/** |
| 239 |
* Cached single trip row (same key family as {@see CacheService::cacheTrip} consumers). |
| 240 |
*/ |
| 241 |
public function findByIdCached(int $id): ?\stdClass |
| 242 |
{ |
| 243 |
return $this->cacheQueryResult( |
| 244 |
Cache::PREFIX_TRIP_DATA . $id, |
| 245 |
function () use ($id): ?\stdClass { |
| 246 |
return $this->find($id); |
| 247 |
}, |
| 248 |
Cache::DURATION_TRIP_DATA |
| 249 |
); |
| 250 |
} |
| 251 |
|
| 252 |
/** |
| 253 |
* Trip row with relationships loaded — used where {@see TripService::getWithRelationsCached} applied. |
| 254 |
*/ |
| 255 |
public function findWithRelationsCached(int $id): ?\stdClass |
| 256 |
{ |
| 257 |
$key = Cache::PREFIX_QUERY_RESULT . 'trip_with_relations_' . $id; |
| 258 |
|
| 259 |
return $this->cacheQueryResult($key, function () use ($id): ?\stdClass { |
| 260 |
$trip = $this->find($id); |
| 261 |
if ($trip) { |
| 262 |
$this->loadTripRelationships($trip); |
| 263 |
} |
| 264 |
|
| 265 |
return $trip; |
| 266 |
}, Cache::DURATION_TRIP_DATA); |
| 267 |
} |
| 268 |
|
| 269 |
/** |
| 270 |
* Create trip row; {@see afterWrite} handles cache; fires action `yatra_trip_created` once per insert. |
| 271 |
*/ |
| 272 |
public function create(array $data): int |
| 273 |
{ |
| 274 |
$id = parent::create($data); |
| 275 |
do_action('yatra_trip_created', $id); |
| 276 |
|
| 277 |
return $id; |
| 278 |
} |
| 279 |
|
| 280 |
/** |
| 281 |
* Override update method to provide proper field formats |
| 282 |
*/ |
| 283 |
public function update(int $id, array $data): bool |
| 284 |
{ |
| 285 |
$data = $this->sanitizeData($data); |
| 286 |
$data['updated_at'] = current_time('mysql'); |
| 287 |
|
| 288 |
// Build format array based on field types |
| 289 |
$formats = []; |
| 290 |
foreach ($data as $key => $value) { |
| 291 |
if (in_array($key, $this->integerFields, true)) { |
| 292 |
$formats[] = '%d'; |
| 293 |
} elseif (in_array($key, ['created_at', 'updated_at'], true)) { |
| 294 |
$formats[] = '%s'; |
| 295 |
} elseif (in_array($key, ['original_price', 'discounted_price', 'sale_price', 'deposit_amount', 'deposit_percentage', 'avg_rating', 'revenue_total', 'conversion_rate'], true)) { |
| 296 |
$formats[] = '%f'; |
| 297 |
} elseif (in_array($key, ['transportation_included', 'is_featured', 'seasonal_auto_enable', 'has_default_time_slots'], true)) { |
| 298 |
$formats[] = '%d'; // boolean as integer |
| 299 |
} else { |
| 300 |
$formats[] = '%s'; // default to string |
| 301 |
} |
| 302 |
} |
| 303 |
|
| 304 |
$result = $this->wpdb->update( |
| 305 |
$this->getTableName(), |
| 306 |
$data, |
| 307 |
['id' => $id], |
| 308 |
$formats, |
| 309 |
['%d'] |
| 310 |
); |
| 311 |
|
| 312 |
if ($result !== false) { |
| 313 |
$this->afterWrite('update', $id, []); |
| 314 |
} |
| 315 |
|
| 316 |
return $result !== false; |
| 317 |
} |
| 318 |
|
| 319 |
/** |
| 320 |
* Invalidate trip/listing caches. Trip created hook is fired from {@see create()} after relations |
| 321 |
* are irrelevant for cache; update/delete hooks fire here. |
| 322 |
* |
| 323 |
* @param 'create'|'update'|'delete' $operation |
| 324 |
*/ |
| 325 |
protected function afterWrite(string $operation, int $id, array $context = []): void |
| 326 |
{ |
| 327 |
Cache::invalidateAfterTripWrite($operation, $id); |
| 328 |
|
| 329 |
if ($operation === 'update') { |
| 330 |
do_action('yatra_trip_updated', $id); |
| 331 |
} elseif ($operation === 'delete') { |
| 332 |
do_action('yatra_trip_deleted', $id); |
| 333 |
} |
| 334 |
} |
| 335 |
|
| 336 |
/** |
| 337 |
* Find published/active trip by ID |
| 338 |
* |
| 339 |
* @param int $id Trip ID |
| 340 |
* @return \stdClass|null |
| 341 |
*/ |
| 342 |
public function findPublished(int $id): ?\stdClass |
| 343 |
{ |
| 344 |
$table = esc_sql($this->table); |
| 345 |
$query = "SELECT * FROM `{$table}` WHERE id = %d AND status IN ('publish', 'published')"; |
| 346 |
|
| 347 |
if ($this->hasSoftDelete()) { |
| 348 |
$query .= " AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')"; |
| 349 |
} |
| 350 |
|
| 351 |
$result = $this->wpdb->get_row($this->wpdb->prepare($query, $id)); |
| 352 |
|
| 353 |
return $result ?: null; |
| 354 |
} |
| 355 |
|
| 356 |
/** |
| 357 |
* Get downloads for a trip (normalized for UI) |
| 358 |
*/ |
| 359 |
public function getDownloads(int $tripId): array |
| 360 |
{ |
| 361 |
$repo = new TripDownloadRepository(); |
| 362 |
// TripDownloadRepository already normalizes the data correctly |
| 363 |
// No need to normalize again |
| 364 |
return $repo->getByTripId($tripId); |
| 365 |
} |
| 366 |
|
| 367 |
/** |
| 368 |
* Attach attribute filter map for findWithFilters() (trip ↔ attribute rows in trip_classifications). |
| 369 |
* |
| 370 |
* @param array<string,mixed> $filters |
| 371 |
* @param array<int,mixed> $attributeFilters |
| 372 |
* @return array<string,mixed> |
| 373 |
*/ |
| 374 |
public function filterByAttributes(array $filters, array $attributeFilters): array |
| 375 |
{ |
| 376 |
if ($attributeFilters === []) { |
| 377 |
return $filters; |
| 378 |
} |
| 379 |
$filters['attribute_filters'] = $attributeFilters; |
| 380 |
|
| 381 |
return $filters; |
| 382 |
} |
| 383 |
|
| 384 |
/** @var array<string,bool> */ |
| 385 |
private static array $tripColumnExistsCache = []; |
| 386 |
|
| 387 |
protected function tripTableHasColumn(string $column): bool |
| 388 |
{ |
| 389 |
if (isset(self::$tripColumnExistsCache[$column])) { |
| 390 |
return self::$tripColumnExistsCache[$column]; |
| 391 |
} |
| 392 |
$table = $this->getTableName(); |
| 393 |
$n = (int) $this->wpdb->get_var($this->wpdb->prepare( |
| 394 |
'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s', |
| 395 |
$table, |
| 396 |
$column |
| 397 |
)); |
| 398 |
self::$tripColumnExistsCache[$column] = $n > 0; |
| 399 |
|
| 400 |
return self::$tripColumnExistsCache[$column]; |
| 401 |
} |
| 402 |
|
| 403 |
/** |
| 404 |
* SQL expression for trip "current" list price (matches TripPricingService::resolveRegularCurrentPrice). |
| 405 |
*/ |
| 406 |
protected function sqlTripEffectiveListPrice(): string |
| 407 |
{ |
| 408 |
return '(CASE ' |
| 409 |
. 'WHEN CAST(t.discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(t.discounted_price AS DECIMAL(10,2)) ' |
| 410 |
. 'WHEN CAST(t.sale_price AS DECIMAL(10,2)) > 0 THEN CAST(t.sale_price AS DECIMAL(10,2)) ' |
| 411 |
. 'ELSE CAST(t.original_price AS DECIMAL(10,2)) END)'; |
| 412 |
} |
| 413 |
|
| 414 |
/** |
| 415 |
* Find trips with comprehensive filtering, pagination, and relationships |
| 416 |
* |
| 417 |
* @param array $filters Filter criteria |
| 418 |
* @param int $page Current page |
| 419 |
* @param int $perPage Items per page |
| 420 |
* @return array |
| 421 |
*/ |
| 422 |
public function findWithFilters(array $filters = [], int $page = 1, int $perPage = 10): array |
| 423 |
{ |
| 424 |
// Create cache key based on filters and pagination |
| 425 |
$cacheKey = Cache::KEY_TRIPS_WITH_FILTERS . '_' . md5(serialize($filters) . "_page_{$page}_per_page_{$perPage}"); |
| 426 |
|
| 427 |
return $this->cacheQueryResult($cacheKey, function() use ($filters, $page, $perPage) { |
| 428 |
global $wpdb; |
| 429 |
|
| 430 |
// Table references |
| 431 |
$trip_table = $this->getTableName(); |
| 432 |
|
| 433 |
// Use new table classes |
| 434 |
$reviews_table = ReviewsTable::getTableName(); |
| 435 |
$bookings_table = BookingsTable::getTableName(); |
| 436 |
// Build WHERE conditions and parameters |
| 437 |
$wheres = ["t.status IN ('publish', 'published')", "(t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')"]; |
| 438 |
$params = []; |
| 439 |
$joins = []; |
| 440 |
$having_clauses = []; |
| 441 |
$rating_params = []; |
| 442 |
|
| 443 |
$classificationsTable = ClassificationsTable::getTableName(); |
| 444 |
$tripClassificationsTable = TripClassificationsTable::getTableName(); |
| 445 |
|
| 446 |
// Keyword search (trip fields + attribute values marked searchable in metadata) |
| 447 |
if (!empty($filters['search']) && is_string($filters['search'])) { |
| 448 |
$like = '%' . $wpdb->esc_like($filters['search']) . '%'; |
| 449 |
$searchFlag = AttributeRepository::metadataEnabledSqlOnAlias('yatra_c_srch', 'searchable'); |
| 450 |
$wheres[] = '(t.title LIKE %s OR t.short_description LIKE %s OR t.description LIKE %s OR t.slug LIKE %s OR EXISTS ( |
| 451 |
SELECT 1 FROM `' . esc_sql($tripClassificationsTable) . '` yatra_tc_srch |
| 452 |
INNER JOIN `' . esc_sql($classificationsTable) . '` yatra_c_srch |
| 453 |
ON yatra_c_srch.id = yatra_tc_srch.classification_id |
| 454 |
WHERE yatra_tc_srch.trip_id = t.id |
| 455 |
AND yatra_tc_srch.classification_type = %s |
| 456 |
AND yatra_tc_srch.is_active = 1 |
| 457 |
AND yatra_c_srch.type = %s |
| 458 |
AND yatra_c_srch.status = %s |
| 459 |
AND (' . $searchFlag . ') |
| 460 |
AND ( |
| 461 |
LOWER(JSON_UNQUOTE(JSON_EXTRACT(yatra_tc_srch.metadata, \'$.value\'))) LIKE LOWER(%s) |
| 462 |
OR LOWER(yatra_tc_srch.metadata) LIKE LOWER(%s) |
| 463 |
) |
| 464 |
))'; |
| 465 |
$params[] = $like; |
| 466 |
$params[] = $like; |
| 467 |
$params[] = $like; |
| 468 |
$params[] = $like; |
| 469 |
$params[] = 'attribute'; |
| 470 |
$params[] = ClassificationTypes::ATTRIBUTE; |
| 471 |
$params[] = 'publish'; |
| 472 |
$params[] = $like; |
| 473 |
$params[] = $like; |
| 474 |
} |
| 475 |
|
| 476 |
// Trip duration type (DB column) |
| 477 |
if (!empty($filters['trip_type']) && is_string($filters['trip_type'])) { |
| 478 |
$allowedTypes = ['single_day', 'multi_day', 'flexible']; |
| 479 |
if (in_array($filters['trip_type'], $allowedTypes, true)) { |
| 480 |
$wheres[] = 't.trip_type = %s'; |
| 481 |
$params[] = $filters['trip_type']; |
| 482 |
} |
| 483 |
} |
| 484 |
|
| 485 |
// Featured Priority (column on yatra_trips, indexed by idx_featured_priority). |
| 486 |
// Admin form's Featured Priority dropdown is the single source of truth. |
| 487 |
// Legacy `is_featured` shortcode/block flag is normalised upstream into featured_priority='featured'. |
| 488 |
if ( |
| 489 |
!empty($filters['featured_priority']) |
| 490 |
&& is_string($filters['featured_priority']) |
| 491 |
&& in_array($filters['featured_priority'], ['featured', 'new', 'limited'], true) |
| 492 |
&& $this->tripTableHasColumn('featured_priority') |
| 493 |
) { |
| 494 |
$wheres[] = 't.featured_priority = %s'; |
| 495 |
$params[] = $filters['featured_priority']; |
| 496 |
} |
| 497 |
|
| 498 |
// Destination: checkbox classification IDs (OR) or single slug |
| 499 |
if (!empty($filters['destination_ids']) && is_array($filters['destination_ids'])) { |
| 500 |
$ids = array_values(array_filter(array_map('intval', $filters['destination_ids']), static fn (int $id): bool => $id > 0)); |
| 501 |
if ($ids !== []) { |
| 502 |
$placeholders = implode(',', array_fill(0, count($ids), '%d')); |
| 503 |
// Rely on joined classification row type only (some rows may have inconsistent classification_type). |
| 504 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tcdx INNER JOIN {$classificationsTable} destx ON destx.id = tcdx.classification_id AND destx.type = %s WHERE tcdx.trip_id = t.id AND tcdx.is_active = 1 AND tcdx.classification_id IN ({$placeholders}))"; |
| 505 |
$params[] = ClassificationTypes::DESTINATION; |
| 506 |
$params = array_merge($params, $ids); |
| 507 |
} |
| 508 |
} elseif (!empty($filters['destination'])) { |
| 509 |
if (is_array($filters['destination'])) { |
| 510 |
$slugs = array_values(array_filter(array_map('sanitize_title', $filters['destination']))); |
| 511 |
if ($slugs !== []) { |
| 512 |
$placeholders = implode(',', array_fill(0, count($slugs), '%s')); |
| 513 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tcdx INNER JOIN {$classificationsTable} destx ON destx.id = tcdx.classification_id AND destx.type = %s WHERE tcdx.trip_id = t.id AND tcdx.is_active = 1 AND destx.slug IN ({$placeholders}))"; |
| 514 |
$params[] = ClassificationTypes::DESTINATION; |
| 515 |
$params = array_merge($params, $slugs); |
| 516 |
} |
| 517 |
} elseif (is_string($filters['destination']) && $filters['destination'] !== '') { |
| 518 |
$joins[] = "LEFT JOIN {$tripClassificationsTable} tcd ON tcd.trip_id = t.id"; |
| 519 |
$joins[] = "LEFT JOIN {$classificationsTable} dest ON dest.id = tcd.classification_id"; |
| 520 |
$wheres[] = 'dest.type = %s AND dest.slug = %s'; |
| 521 |
$params[] = ClassificationTypes::DESTINATION; |
| 522 |
$params[] = $filters['destination']; |
| 523 |
} |
| 524 |
} |
| 525 |
|
| 526 |
// Activity: IDs or slug |
| 527 |
if (!empty($filters['activity_ids']) && is_array($filters['activity_ids'])) { |
| 528 |
$ids = array_values(array_filter(array_map('intval', $filters['activity_ids']), static fn (int $id): bool => $id > 0)); |
| 529 |
if ($ids !== []) { |
| 530 |
$placeholders = implode(',', array_fill(0, count($ids), '%d')); |
| 531 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tcax INNER JOIN {$classificationsTable} actx ON actx.id = tcax.classification_id AND actx.type = %s WHERE tcax.trip_id = t.id AND tcax.is_active = 1 AND tcax.classification_id IN ({$placeholders}))"; |
| 532 |
$params[] = ClassificationTypes::ACTIVITY; |
| 533 |
$params = array_merge($params, $ids); |
| 534 |
} |
| 535 |
} elseif (!empty($filters['activity'])) { |
| 536 |
if (is_array($filters['activity'])) { |
| 537 |
$slugs = array_values(array_filter(array_map('sanitize_title', $filters['activity']))); |
| 538 |
if ($slugs !== []) { |
| 539 |
$placeholders = implode(',', array_fill(0, count($slugs), '%s')); |
| 540 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tcax INNER JOIN {$classificationsTable} actx ON actx.id = tcax.classification_id AND actx.type = %s WHERE tcax.trip_id = t.id AND tcax.is_active = 1 AND actx.slug IN ({$placeholders}))"; |
| 541 |
$params[] = ClassificationTypes::ACTIVITY; |
| 542 |
$params = array_merge($params, $slugs); |
| 543 |
} |
| 544 |
} elseif (is_string($filters['activity']) && $filters['activity'] !== '') { |
| 545 |
$joins[] = "LEFT JOIN {$tripClassificationsTable} tca ON tca.trip_id = t.id"; |
| 546 |
$joins[] = "LEFT JOIN {$classificationsTable} act ON act.id = tca.classification_id"; |
| 547 |
$wheres[] = 'act.type = %s AND act.slug = %s'; |
| 548 |
$params[] = ClassificationTypes::ACTIVITY; |
| 549 |
$params[] = $filters['activity']; |
| 550 |
} |
| 551 |
} |
| 552 |
|
| 553 |
// Category: IDs or slug |
| 554 |
if (!empty($filters['category_ids']) && is_array($filters['category_ids'])) { |
| 555 |
$ids = array_values(array_filter(array_map('intval', $filters['category_ids']), static fn (int $id): bool => $id > 0)); |
| 556 |
if ($ids !== []) { |
| 557 |
$placeholders = implode(',', array_fill(0, count($ids), '%d')); |
| 558 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tccx INNER JOIN {$classificationsTable} catx ON catx.id = tccx.classification_id AND catx.type = %s WHERE tccx.trip_id = t.id AND tccx.is_active = 1 AND tccx.classification_id IN ({$placeholders}))"; |
| 559 |
$params[] = ClassificationTypes::CATEGORY; |
| 560 |
$params = array_merge($params, $ids); |
| 561 |
} |
| 562 |
} elseif (!empty($filters['trip_category'])) { |
| 563 |
if (is_array($filters['trip_category'])) { |
| 564 |
$slugs = array_values(array_filter(array_map('sanitize_title', $filters['trip_category']))); |
| 565 |
if ($slugs !== []) { |
| 566 |
$placeholders = implode(',', array_fill(0, count($slugs), '%s')); |
| 567 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tccx INNER JOIN {$classificationsTable} catx ON catx.id = tccx.classification_id AND catx.type = %s WHERE tccx.trip_id = t.id AND tccx.is_active = 1 AND catx.slug IN ({$placeholders}))"; |
| 568 |
$params[] = ClassificationTypes::CATEGORY; |
| 569 |
$params = array_merge($params, $slugs); |
| 570 |
} |
| 571 |
} elseif (is_string($filters['trip_category']) && $filters['trip_category'] !== '') { |
| 572 |
$joins[] = "LEFT JOIN {$tripClassificationsTable} tcc ON tcc.trip_id = t.id"; |
| 573 |
$joins[] = "LEFT JOIN {$classificationsTable} cat ON cat.id = tcc.classification_id"; |
| 574 |
$wheres[] = 'cat.type = %s AND cat.slug = %s'; |
| 575 |
$params[] = ClassificationTypes::CATEGORY; |
| 576 |
$params[] = $filters['trip_category']; |
| 577 |
} |
| 578 |
} |
| 579 |
|
| 580 |
// Special offers (OR within group) |
| 581 |
if (!empty($filters['special_offers']) && is_array($filters['special_offers'])) { |
| 582 |
$offerParts = []; |
| 583 |
foreach (array_unique($filters['special_offers']) as $offer) { |
| 584 |
if (!is_string($offer)) { |
| 585 |
continue; |
| 586 |
} |
| 587 |
switch ($offer) { |
| 588 |
case 'discount': |
| 589 |
$offerParts[] = '(t.discounted_price IS NOT NULL OR t.sale_price IS NOT NULL)'; |
| 590 |
break; |
| 591 |
case 'early-bird': |
| 592 |
if ($this->tripTableHasColumn('early_bird_discount_enabled')) { |
| 593 |
$offerParts[] = 't.early_bird_discount_enabled = 1'; |
| 594 |
} |
| 595 |
break; |
| 596 |
case 'last-minute': |
| 597 |
if ($this->tripTableHasColumn('last_minute_discount_enabled')) { |
| 598 |
$offerParts[] = 't.last_minute_discount_enabled = 1'; |
| 599 |
} |
| 600 |
break; |
| 601 |
case 'instant-booking': |
| 602 |
if ($this->tripTableHasColumn('instant_booking')) { |
| 603 |
$offerParts[] = 't.instant_booking = 1'; |
| 604 |
} |
| 605 |
break; |
| 606 |
case 'flexible-dates': |
| 607 |
if ($this->tripTableHasColumn('flexible_dates')) { |
| 608 |
$offerParts[] = 't.flexible_dates = 1'; |
| 609 |
} |
| 610 |
break; |
| 611 |
case 'deposit-available': |
| 612 |
if ($this->tripTableHasColumn('deposit_required')) { |
| 613 |
$offerParts[] = 't.deposit_required = 1'; |
| 614 |
} |
| 615 |
break; |
| 616 |
} |
| 617 |
} |
| 618 |
if ($offerParts !== []) { |
| 619 |
$wheres[] = '(' . implode(' OR ', $offerParts) . ')'; |
| 620 |
} |
| 621 |
} |
| 622 |
|
| 623 |
// Booking options (OR) |
| 624 |
if (!empty($filters['booking_options']) && is_array($filters['booking_options'])) { |
| 625 |
$bookParts = []; |
| 626 |
foreach (array_unique($filters['booking_options']) as $opt) { |
| 627 |
if (!is_string($opt)) { |
| 628 |
continue; |
| 629 |
} |
| 630 |
switch ($opt) { |
| 631 |
case 'instant': |
| 632 |
if ($this->tripTableHasColumn('instant_booking')) { |
| 633 |
$bookParts[] = 't.instant_booking = 1'; |
| 634 |
} |
| 635 |
break; |
| 636 |
case 'flexible': |
| 637 |
if ($this->tripTableHasColumn('flexible_dates')) { |
| 638 |
$bookParts[] = 't.flexible_dates = 1'; |
| 639 |
} |
| 640 |
break; |
| 641 |
case 'pay-later': |
| 642 |
if ($this->tripTableHasColumn('deposit_required')) { |
| 643 |
$bookParts[] = 't.deposit_required = 1'; |
| 644 |
} |
| 645 |
break; |
| 646 |
} |
| 647 |
} |
| 648 |
if ($bookParts !== []) { |
| 649 |
$wheres[] = '(' . implode(' OR ', $bookParts) . ')'; |
| 650 |
} |
| 651 |
} |
| 652 |
|
| 653 |
// Age suitability (OR) |
| 654 |
if (!empty($filters['age_suitability']) && is_array($filters['age_suitability'])) { |
| 655 |
$ageParts = []; |
| 656 |
foreach (array_unique($filters['age_suitability']) as $age) { |
| 657 |
if (!is_string($age)) { |
| 658 |
continue; |
| 659 |
} |
| 660 |
switch ($age) { |
| 661 |
case 'family-friendly': |
| 662 |
$ageParts[] = '(t.age_min IS NULL OR t.age_min <= 5)'; |
| 663 |
break; |
| 664 |
case 'kids-friendly': |
| 665 |
$ageParts[] = '(t.age_min IS NULL OR t.age_min <= 12)'; |
| 666 |
break; |
| 667 |
case 'senior-friendly': |
| 668 |
$ageParts[] = '(t.age_max IS NULL OR t.age_max >= 65)'; |
| 669 |
break; |
| 670 |
case 'adults-only': |
| 671 |
$ageParts[] = 't.age_min >= 18'; |
| 672 |
break; |
| 673 |
} |
| 674 |
} |
| 675 |
if ($ageParts !== []) { |
| 676 |
$wheres[] = '(' . implode(' OR ', $ageParts) . ')'; |
| 677 |
} |
| 678 |
} |
| 679 |
|
| 680 |
// Accommodation type |
| 681 |
if (!empty($filters['accommodation']) && is_array($filters['accommodation']) && $this->tripTableHasColumn('accommodation_type')) { |
| 682 |
$acc = array_values(array_filter(array_map('sanitize_text_field', $filters['accommodation']))); |
| 683 |
if ($acc !== []) { |
| 684 |
$placeholders = implode(',', array_fill(0, count($acc), '%s')); |
| 685 |
$wheres[] = "t.accommodation_type IN ({$placeholders})"; |
| 686 |
$params = array_merge($params, $acc); |
| 687 |
} |
| 688 |
} |
| 689 |
|
| 690 |
// Included services (loose match on JSON text) |
| 691 |
if (!empty($filters['included_services']) && is_array($filters['included_services'])) { |
| 692 |
foreach (array_unique($filters['included_services']) as $svc) { |
| 693 |
if (!is_string($svc) || $svc === '') { |
| 694 |
continue; |
| 695 |
} |
| 696 |
$like = '%' . $wpdb->esc_like($svc) . '%'; |
| 697 |
$wheres[] = 't.included_items LIKE %s'; |
| 698 |
$params[] = $like; |
| 699 |
} |
| 700 |
} |
| 701 |
|
| 702 |
// Dynamic attribute filters (only attributes allowed in public filters — admin setting "Show in Filters") |
| 703 |
if (!empty($filters['attribute_filters']) && is_array($filters['attribute_filters'])) { |
| 704 |
$allowedFilterAttrIds = (new AttributeRepository())->getFilterableAttributeIds(); |
| 705 |
foreach ($filters['attribute_filters'] as $attrId => $rawVal) { |
| 706 |
$attrId = (int) $attrId; |
| 707 |
if ($attrId <= 0) { |
| 708 |
continue; |
| 709 |
} |
| 710 |
if (!in_array($attrId, $allowedFilterAttrIds, true)) { |
| 711 |
continue; |
| 712 |
} |
| 713 |
if (is_array($rawVal) && (isset($rawVal['min']) || isset($rawVal['max']))) { |
| 714 |
if (isset($rawVal['min']) && is_numeric($rawVal['min'])) { |
| 715 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} ta_n WHERE ta_n.trip_id = t.id AND ta_n.classification_type = 'attribute' AND ta_n.classification_id = {$attrId} AND ta_n.is_active = 1 AND CAST(JSON_UNQUOTE(JSON_EXTRACT(ta_n.metadata, '$.value')) AS DECIMAL(14,4)) >= %f)"; |
| 716 |
$params[] = (float) $rawVal['min']; |
| 717 |
} |
| 718 |
if (isset($rawVal['max']) && is_numeric($rawVal['max'])) { |
| 719 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} ta_x WHERE ta_x.trip_id = t.id AND ta_x.classification_type = 'attribute' AND ta_x.classification_id = {$attrId} AND ta_x.is_active = 1 AND CAST(JSON_UNQUOTE(JSON_EXTRACT(ta_x.metadata, '$.value')) AS DECIMAL(14,4)) <= %f)"; |
| 720 |
$params[] = (float) $rawVal['max']; |
| 721 |
} |
| 722 |
} elseif (is_array($rawVal) && $rawVal !== []) { |
| 723 |
$vals = array_values(array_filter(array_map('sanitize_text_field', $rawVal))); |
| 724 |
if ($vals !== []) { |
| 725 |
$placeholders = implode(',', array_fill(0, count($vals), '%s')); |
| 726 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} ta_m WHERE ta_m.trip_id = t.id AND ta_m.classification_type = 'attribute' AND ta_m.classification_id = {$attrId} AND ta_m.is_active = 1 AND JSON_UNQUOTE(JSON_EXTRACT(ta_m.metadata, '$.value')) IN ({$placeholders}))"; |
| 727 |
$params = array_merge($params, $vals); |
| 728 |
} |
| 729 |
} else { |
| 730 |
$v = sanitize_text_field((string) $rawVal); |
| 731 |
if ($v !== '') { |
| 732 |
$wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} ta_s WHERE ta_s.trip_id = t.id AND ta_s.classification_type = 'attribute' AND ta_s.classification_id = {$attrId} AND ta_s.is_active = 1 AND JSON_UNQUOTE(JSON_EXTRACT(ta_s.metadata, '$.value')) = %s)"; |
| 733 |
$params[] = $v; |
| 734 |
} |
| 735 |
} |
| 736 |
} |
| 737 |
} |
| 738 |
|
| 739 |
// Price range filter. |
| 740 |
// |
| 741 |
// The previous version compared a single legacy "effective" |
| 742 |
// price (discounted → sale → original column) against the |
| 743 |
// range. That excluded traveler-based trips whose real |
| 744 |
// displayed price lives in the `price_types` JSON — so eg. a |
| 745 |
// trip costing €3755 (Adult category) never appeared in search |
| 746 |
// when the user set max=€3755, because its legacy |
| 747 |
// original_price was €0 / stale. |
| 748 |
// |
| 749 |
// New approach: match a trip if EITHER the legacy effective |
| 750 |
// price OR ANY per-category price in the JSON falls in the |
| 751 |
// requested range. Numeric values in the `price_types` JSON |
| 752 |
// are extracted with a regex on the raw text — works in all |
| 753 |
// MySQL 5.7+ builds without needing JSON_VALUE / JSON_TABLE |
| 754 |
// (which are inconsistent across MariaDB / older MySQL). |
| 755 |
$effPrice = $this->sqlTripEffectiveListPrice(); |
| 756 |
$priceMin = !empty($filters['price_min']) && $filters['price_min'] > 0 ? (float) $filters['price_min'] : null; |
| 757 |
$priceMax = !empty($filters['price_max']) && $filters['price_max'] > 0 ? (float) $filters['price_max'] : null; |
| 758 |
|
| 759 |
if ($priceMin !== null || $priceMax !== null) { |
| 760 |
$legacyCond = []; |
| 761 |
if ($priceMin !== null) { $legacyCond[] = "{$effPrice} >= %f"; $params[] = $priceMin; } |
| 762 |
if ($priceMax !== null) { $legacyCond[] = "{$effPrice} <= %f"; $params[] = $priceMax; } |
| 763 |
$legacyClause = implode(' AND ', $legacyCond); |
| 764 |
|
| 765 |
// For per-category pricing, find ANY price in `price_types` |
| 766 |
// JSON that falls in the requested range. This subquery |
| 767 |
// creates an ad-hoc number sequence (n=0..49 covers up to |
| 768 |
// 50 categories — far more than any real trip uses) and |
| 769 |
// uses JSON_EXTRACT to fetch each entry's effective price. |
| 770 |
$numbers = "(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19)"; |
| 771 |
// Each category's effective price = first non-null of |
| 772 |
// discounted_price → sale_price → original_price → price. |
| 773 |
$categoryEff = "COALESCE(" |
| 774 |
. "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].discounted_price'))) AS DECIMAL(10,2)), 0)," |
| 775 |
. "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].sale_price'))) AS DECIMAL(10,2)), 0)," |
| 776 |
. "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].original_price'))) AS DECIMAL(10,2)), 0)," |
| 777 |
. "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].price'))) AS DECIMAL(10,2)), 0)" |
| 778 |
. ")"; |
| 779 |
$catCond = []; |
| 780 |
if ($priceMin !== null) { $catCond[] = "{$categoryEff} >= %f"; $params[] = $priceMin; } |
| 781 |
if ($priceMax !== null) { $catCond[] = "{$categoryEff} <= %f"; $params[] = $priceMax; } |
| 782 |
$catClause = implode(' AND ', $catCond); |
| 783 |
|
| 784 |
// Guard the per-category branch against malformed JSON. |
| 785 |
// JSON_EXTRACT throws on invalid JSON, and `price_types` |
| 786 |
// can be NULL, '', '[]' or a stale string on legacy rows. |
| 787 |
// JSON_VALID() + IFNULL() lets us bail cleanly for those. |
| 788 |
$perCategoryExists = "(" |
| 789 |
. "t.price_types IS NOT NULL" |
| 790 |
. " AND t.price_types <> ''" |
| 791 |
. " AND JSON_VALID(t.price_types) = 1" |
| 792 |
. " AND EXISTS (" |
| 793 |
. "SELECT 1 FROM {$numbers} _n" |
| 794 |
. " WHERE JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, ']')) IS NOT NULL" |
| 795 |
. " AND {$catClause}" |
| 796 |
. ")" |
| 797 |
. ")"; |
| 798 |
|
| 799 |
// Match if EITHER condition passes — a single legacy-priced |
| 800 |
// trip OR a per-category trip with any matching tier. |
| 801 |
$wheres[] = "(({$legacyClause}) OR {$perCategoryExists})"; |
| 802 |
} |
| 803 |
|
| 804 |
// Duration filter |
| 805 |
if (!empty($filters['duration_min']) && $filters['duration_min'] > 0) { |
| 806 |
$wheres[] = "CAST(t.duration_days AS UNSIGNED) >= %d"; |
| 807 |
$params[] = $filters['duration_min']; |
| 808 |
} |
| 809 |
|
| 810 |
if (!empty($filters['duration_max']) && $filters['duration_max'] > 0) { |
| 811 |
$wheres[] = "CAST(t.duration_days AS UNSIGNED) <= %d"; |
| 812 |
$params[] = $filters['duration_max']; |
| 813 |
} |
| 814 |
|
| 815 |
// Rating filter |
| 816 |
if (!empty($filters['rating_min']) && $filters['rating_min'] > 0) { |
| 817 |
$having_clauses[] = "AVG(r.rating) >= %f"; |
| 818 |
$rating_params[] = $filters['rating_min']; |
| 819 |
} |
| 820 |
|
| 821 |
// Difficulty filter |
| 822 |
if (!empty($filters['difficulty']) && is_array($filters['difficulty'])) { |
| 823 |
$joins[] = "LEFT JOIN {$classificationsTable} dl ON dl.id = t.difficulty_level"; |
| 824 |
$difficulty_placeholders = implode(',', array_fill(0, count($filters['difficulty']), '%d')); |
| 825 |
$wheres[] = "dl.type = %s AND dl.id IN ({$difficulty_placeholders})"; |
| 826 |
$params[] = ClassificationTypes::DIFFICULTY; |
| 827 |
$params = array_merge($params, $filters['difficulty']); |
| 828 |
} |
| 829 |
|
| 830 |
// Build SQL components |
| 831 |
$join_sql = !empty($joins) ? implode(' ', $joins) : ''; |
| 832 |
$where_sql = 'WHERE ' . implode(' AND ', $wheres); |
| 833 |
$having_sql = !empty($having_clauses) ? ('HAVING ' . implode(' AND ', $having_clauses)) : ''; |
| 834 |
|
| 835 |
// Calculate pagination |
| 836 |
$offset = ($page - 1) * $perPage; |
| 837 |
|
| 838 |
// Count total results |
| 839 |
$count_params = array_merge($params, $rating_params); |
| 840 |
if (!empty($having_clauses)) { |
| 841 |
$count_sql = "SELECT COUNT(*) FROM ( |
| 842 |
SELECT t.id |
| 843 |
FROM {$trip_table} t |
| 844 |
{$join_sql} |
| 845 |
LEFT JOIN {$reviews_table} r ON r.trip_id = t.id AND r.status = 'approved' |
| 846 |
{$where_sql} |
| 847 |
GROUP BY t.id |
| 848 |
{$having_sql} |
| 849 |
) as filtered_trips"; |
| 850 |
} else { |
| 851 |
$count_sql = "SELECT COUNT(DISTINCT t.id) |
| 852 |
FROM {$trip_table} t |
| 853 |
{$join_sql} |
| 854 |
{$where_sql}"; |
| 855 |
$count_params = $params; |
| 856 |
} |
| 857 |
|
| 858 |
$prepared_count_query = empty($count_params) ? $count_sql : |
| 859 |
$wpdb->prepare($count_sql, ...$count_params); |
| 860 |
|
| 861 |
$total = (int) $wpdb->get_var($prepared_count_query); |
| 862 |
$total_pages = $total > 0 ? (int) ceil($total / $perPage) : 1; |
| 863 |
|
| 864 |
// Build ORDER BY clause |
| 865 |
$order_clause = $this->buildTripOrderClause($filters['sort'] ?? ''); |
| 866 |
|
| 867 |
// Check for difficulty table and build difficulty JOIN |
| 868 |
$difficulty_join = $this->buildDifficultyJoin(); |
| 869 |
$difficulty_select = $difficulty_join ? ', diff.name AS difficulty_name, diff.icon AS difficulty_icon' : ''; |
| 870 |
|
| 871 |
|
| 872 |
// Main query |
| 873 |
$query_sql = "SELECT t.*, |
| 874 |
AVG(r.rating) AS average_rating, |
| 875 |
COUNT(DISTINCT r.id) AS review_count, |
| 876 |
COUNT(DISTINCT b.id) AS booking_count{$difficulty_select} |
| 877 |
FROM {$trip_table} t |
| 878 |
{$join_sql} |
| 879 |
LEFT JOIN {$reviews_table} r ON r.trip_id = t.id AND r.status = 'approved' |
| 880 |
LEFT JOIN {$bookings_table} b ON b.trip_id = t.id AND b.status IN ('confirmed', 'completed', 'paid') |
| 881 |
{$difficulty_join} |
| 882 |
{$where_sql} |
| 883 |
GROUP BY t.id |
| 884 |
{$having_sql} |
| 885 |
{$order_clause} |
| 886 |
LIMIT %d OFFSET %d"; |
| 887 |
|
| 888 |
$main_query_params = array_merge($params, $rating_params, [$perPage, $offset]); |
| 889 |
$prepared_query = $wpdb->prepare($query_sql, ...$main_query_params); |
| 890 |
|
| 891 |
$trips = $wpdb->get_results($prepared_query) ?: []; |
| 892 |
|
| 893 |
// Process trips in batch for better performance (eliminates N+1 queries) |
| 894 |
if (!empty($trips)) { |
| 895 |
$this->batchEnrichTrips($trips); |
| 896 |
} |
| 897 |
|
| 898 |
return [ |
| 899 |
'trips' => $trips, |
| 900 |
'total' => $total, |
| 901 |
'pages' => $total_pages, |
| 902 |
'page' => $page, |
| 903 |
'per_page' => $perPage |
| 904 |
]; |
| 905 |
}, Cache::DURATION_QUERY_RESULT); // Cache for 10 minutes |
| 906 |
} |
| 907 |
|
| 908 |
/** |
| 909 |
* Build ORDER BY clause for trip-specific sorting |
| 910 |
*/ |
| 911 |
protected function buildTripOrderClause(string $sort): string |
| 912 |
{ |
| 913 |
$effPrice = $this->sqlTripEffectiveListPrice(); |
| 914 |
switch ($sort) { |
| 915 |
case 'most_popular': |
| 916 |
return "ORDER BY booking_count DESC, t.created_at DESC"; |
| 917 |
case 'price_low': |
| 918 |
return "ORDER BY {$effPrice} ASC"; |
| 919 |
case 'price_high': |
| 920 |
return "ORDER BY {$effPrice} DESC"; |
| 921 |
case 'rating_high': |
| 922 |
return "ORDER BY average_rating DESC"; |
| 923 |
case 'date_asc': |
| 924 |
return "ORDER BY t.created_at ASC"; |
| 925 |
case 'duration_short': |
| 926 |
return "ORDER BY CAST(t.duration_days AS UNSIGNED) ASC, CAST(t.duration_nights AS UNSIGNED) ASC"; |
| 927 |
case 'duration_long': |
| 928 |
return "ORDER BY CAST(t.duration_days AS UNSIGNED) DESC, CAST(t.duration_nights AS UNSIGNED) DESC"; |
| 929 |
default: |
| 930 |
return "ORDER BY t.created_at DESC"; |
| 931 |
} |
| 932 |
} |
| 933 |
|
| 934 |
/** |
| 935 |
* Build difficulty JOIN clause if table exists |
| 936 |
*/ |
| 937 |
protected function buildDifficultyJoin(): string |
| 938 |
{ |
| 939 |
global $wpdb; |
| 940 |
|
| 941 |
// Use ClassificationsTable for difficulty levels (type = 'difficulty') |
| 942 |
$difficulty_table = ClassificationsTable::getTableName(); |
| 943 |
|
| 944 |
// Use advanced caching system instead of simple array cache |
| 945 |
$tableExists = Cache::tableExists($difficulty_table, function() use ($wpdb, $difficulty_table) { |
| 946 |
return (bool) $wpdb->get_var("SHOW TABLES LIKE '{$difficulty_table}'"); |
| 947 |
}); |
| 948 |
|
| 949 |
if ($tableExists) { |
| 950 |
// Map yatra_trips.difficulty_level (bigint ID) to yatra_classifications table |
| 951 |
// The difficulty_level field contains the classification ID |
| 952 |
return sprintf( |
| 953 |
"LEFT JOIN {$difficulty_table} diff ON diff.id = t.difficulty_level AND diff.type = '%s'", |
| 954 |
ClassificationTypes::DIFFICULTY |
| 955 |
); |
| 956 |
} |
| 957 |
return ''; |
| 958 |
} |
| 959 |
|
| 960 |
/** |
| 961 |
* Batch enrich multiple trips to eliminate N+1 queries |
| 962 |
* |
| 963 |
* @param array $trips Array of trip objects |
| 964 |
*/ |
| 965 |
protected function batchEnrichTrips(array $trips): void |
| 966 |
{ |
| 967 |
if (empty($trips)) { |
| 968 |
return; |
| 969 |
} |
| 970 |
|
| 971 |
global $wpdb; |
| 972 |
$trip_ids = array_column($trips, 'id'); |
| 973 |
$trip_ids_placeholder = implode(',', array_fill(0, count($trip_ids), '%d')); |
| 974 |
|
| 975 |
// Batch load destinations |
| 976 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 977 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 978 |
$destinations_data = []; |
| 979 |
$destTableExists = Cache::tableExists($tripClassificationsTable, function() use ($wpdb, $tripClassificationsTable) { |
| 980 |
return (bool) $wpdb->get_var("SHOW TABLES LIKE '{$tripClassificationsTable}'"); |
| 981 |
}); |
| 982 |
|
| 983 |
if ($destTableExists) { |
| 984 |
$destinations_raw = $wpdb->get_results($wpdb->prepare( |
| 985 |
"SELECT tc.trip_id, c.* FROM {$classificationsTable} c |
| 986 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 987 |
WHERE tc.trip_id IN ({$trip_ids_placeholder}) AND c.type = %s AND c.status = 'publish' |
| 988 |
ORDER BY tc.trip_id, tc.sort_order ASC, c.name ASC", |
| 989 |
ClassificationTypes::DESTINATION, ...$trip_ids |
| 990 |
)); |
| 991 |
|
| 992 |
foreach ($destinations_raw as $dest) { |
| 993 |
$trip_id = $dest->trip_id; |
| 994 |
unset($dest->trip_id); |
| 995 |
$destinations_data[$trip_id][] = $dest; |
| 996 |
} |
| 997 |
} |
| 998 |
|
| 999 |
// Batch load activities |
| 1000 |
$activities_data = []; |
| 1001 |
$actTableExists = Cache::tableExists($tripClassificationsTable, function() use ($wpdb, $tripClassificationsTable) { |
| 1002 |
return (bool) $wpdb->get_var("SHOW TABLES LIKE '{$tripClassificationsTable}'"); |
| 1003 |
}); |
| 1004 |
|
| 1005 |
if ($actTableExists) { |
| 1006 |
$activities_raw = $wpdb->get_results($wpdb->prepare( |
| 1007 |
"SELECT tc.trip_id, c.* FROM {$classificationsTable} c |
| 1008 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 1009 |
WHERE tc.trip_id IN ({$trip_ids_placeholder}) AND c.type = %s AND c.status = 'publish' |
| 1010 |
ORDER BY tc.trip_id, tc.sort_order ASC, c.name ASC", |
| 1011 |
ClassificationTypes::ACTIVITY, ...$trip_ids |
| 1012 |
)); |
| 1013 |
|
| 1014 |
foreach ($activities_raw as $act) { |
| 1015 |
$trip_id = $act->trip_id; |
| 1016 |
unset($act->trip_id); |
| 1017 |
$activities_data[$trip_id][] = $act; |
| 1018 |
} |
| 1019 |
} |
| 1020 |
|
| 1021 |
// Batch load categories |
| 1022 |
// Using TripClassificationsTable for category relationships |
| 1023 |
$cat_rel_table = $tripClassificationsTable; |
| 1024 |
$categories_data = []; |
| 1025 |
$catTableExists = Cache::tableExists($cat_rel_table, function() use ($wpdb, $cat_rel_table) { |
| 1026 |
return (bool) $wpdb->get_var("SHOW TABLES LIKE '{$cat_rel_table}'"); |
| 1027 |
}); |
| 1028 |
|
| 1029 |
if ($catTableExists) { |
| 1030 |
$categories_raw = $wpdb->get_results($wpdb->prepare( |
| 1031 |
"SELECT tc.trip_id, c.* FROM {$classificationsTable} c |
| 1032 |
INNER JOIN {$cat_rel_table} tc ON c.id = tc.classification_id |
| 1033 |
WHERE tc.trip_id IN ({$trip_ids_placeholder}) |
| 1034 |
AND c.type = %s AND c.status = 'publish' |
| 1035 |
ORDER BY tc.trip_id, c.name ASC", |
| 1036 |
ClassificationTypes::CATEGORY, ...$trip_ids |
| 1037 |
)); |
| 1038 |
|
| 1039 |
foreach ($categories_raw as $cat) { |
| 1040 |
$trip_id = $cat->trip_id; |
| 1041 |
unset($cat->trip_id); |
| 1042 |
$categories_data[$trip_id][] = $cat; |
| 1043 |
} |
| 1044 |
} |
| 1045 |
|
| 1046 |
$price_types_by_trip = $this->batchLoadPriceTypesByTripIds($trip_ids); |
| 1047 |
|
| 1048 |
// Apply enriched data to each trip — use centralized TripPricingService |
| 1049 |
foreach ($trips as $trip) { |
| 1050 |
$trip->effective_price_min = \Yatra\Services\TripPricingService::getEffectivePrice($trip); |
| 1051 |
|
| 1052 |
// Set relationships |
| 1053 |
$trip->destinations = $destinations_data[$trip->id] ?? []; |
| 1054 |
$trip->activities = $activities_data[$trip->id] ?? []; |
| 1055 |
$trip->categories = $categories_data[$trip->id] ?? []; |
| 1056 |
$trip->price_types = $price_types_by_trip[$trip->id] ?? []; |
| 1057 |
} |
| 1058 |
} |
| 1059 |
|
| 1060 |
/** |
| 1061 |
* Enrich trip data with additional computed fields (legacy single-trip method) |
| 1062 |
*/ |
| 1063 |
protected function enrichTripData(\stdClass $trip): void |
| 1064 |
{ |
| 1065 |
global $wpdb; |
| 1066 |
|
| 1067 |
// Compute effective pricing via centralized TripPricingService |
| 1068 |
$trip->effective_price_min = \Yatra\Services\TripPricingService::getEffectivePrice($trip); |
| 1069 |
} |
| 1070 |
|
| 1071 |
/** |
| 1072 |
* Load trip relationships (destinations, activities, categories) |
| 1073 |
*/ |
| 1074 |
protected function loadTripRelationships(\stdClass $trip): void |
| 1075 |
{ |
| 1076 |
global $wpdb; |
| 1077 |
|
| 1078 |
// Use new Classification tables |
| 1079 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 1080 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 1081 |
|
| 1082 |
// Load destinations |
| 1083 |
$destinations = $wpdb->get_results($wpdb->prepare( |
| 1084 |
"SELECT c.* FROM {$classificationsTable} c |
| 1085 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 1086 |
WHERE tc.trip_id = %d AND c.type = %s AND c.status = 'publish' |
| 1087 |
ORDER BY tc.sort_order ASC, c.name ASC", |
| 1088 |
$trip->id, ClassificationTypes::DESTINATION |
| 1089 |
)); |
| 1090 |
$trip->destinations = $destinations ?: []; |
| 1091 |
|
| 1092 |
// Load activities |
| 1093 |
$activities = $wpdb->get_results($wpdb->prepare( |
| 1094 |
"SELECT c.* FROM {$classificationsTable} c |
| 1095 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 1096 |
WHERE tc.trip_id = %d AND c.type = %s AND c.status = 'publish' |
| 1097 |
ORDER BY tc.sort_order ASC, c.name ASC", |
| 1098 |
$trip->id, ClassificationTypes::ACTIVITY |
| 1099 |
)); |
| 1100 |
$trip->activities = $activities ?: []; |
| 1101 |
|
| 1102 |
// Load categories |
| 1103 |
$categories = $wpdb->get_results($wpdb->prepare( |
| 1104 |
"SELECT c.* FROM {$classificationsTable} c |
| 1105 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 1106 |
WHERE tc.trip_id = %d AND c.type = %s AND c.status = 'publish' |
| 1107 |
ORDER BY tc.sort_order ASC, c.name ASC", |
| 1108 |
$trip->id, ClassificationTypes::CATEGORY |
| 1109 |
)); |
| 1110 |
$trip->categories = $categories ?: []; |
| 1111 |
|
| 1112 |
// Load price types for traveler-based pricing (from trips table JSON) |
| 1113 |
$trip->price_types = $this->getPriceTypes((int) $trip->id); |
| 1114 |
} |
| 1115 |
|
| 1116 |
/** |
| 1117 |
* Find by slug |
| 1118 |
*/ |
| 1119 |
public function findBySlug(string $slug): ?\stdClass |
| 1120 |
{ |
| 1121 |
$table = esc_sql($this->table); |
| 1122 |
$query = "SELECT * FROM `{$table}` WHERE slug = %s"; |
| 1123 |
|
| 1124 |
if ($this->hasSoftDelete()) { |
| 1125 |
$query .= " AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')"; |
| 1126 |
} |
| 1127 |
|
| 1128 |
$result = $this->wpdb->get_row( |
| 1129 |
$this->wpdb->prepare($query, $slug) |
| 1130 |
); |
| 1131 |
|
| 1132 |
return $result ?: null; |
| 1133 |
} |
| 1134 |
|
| 1135 |
/** |
| 1136 |
* Find by ID with relationships |
| 1137 |
*/ |
| 1138 |
public function findWithRelations(int $id, bool $includeDeleted = false): ?\stdClass |
| 1139 |
{ |
| 1140 |
$trip = $this->find($id, $includeDeleted); |
| 1141 |
|
| 1142 |
if (!$trip) { |
| 1143 |
return null; |
| 1144 |
} |
| 1145 |
|
| 1146 |
// Load destinations |
| 1147 |
$trip->destinations = $this->getDestinations($id); |
| 1148 |
|
| 1149 |
// Load activities |
| 1150 |
$trip->activities = $this->getActivities($id); |
| 1151 |
|
| 1152 |
// Load trip categories |
| 1153 |
$trip->trip_category = $this->getTripCategories($id); |
| 1154 |
|
| 1155 |
// Load price types |
| 1156 |
$trip->price_types = $this->getPriceTypes($id); |
| 1157 |
|
| 1158 |
// Load gallery images |
| 1159 |
$trip->gallery_images = $this->getGalleryImages($id); |
| 1160 |
|
| 1161 |
// Load downloads |
| 1162 |
$trip->downloadable_items = $this->getDownloads($id); |
| 1163 |
|
| 1164 |
// Load highlights |
| 1165 |
$trip->highlights = $this->getHighlights($id); |
| 1166 |
|
| 1167 |
// Load landmarks |
| 1168 |
$trip->landmarks = $this->getLandmarks($id); |
| 1169 |
|
| 1170 |
// Load FAQs |
| 1171 |
$trip->faqs = $this->getFaqs($id); |
| 1172 |
|
| 1173 |
// Load availability dates |
| 1174 |
$trip->availability_dates = $this->getAvailabilityDates($id); |
| 1175 |
|
| 1176 |
// Load itinerary days with entries |
| 1177 |
$trip->itinerary_days = $this->getItineraryDays($id); |
| 1178 |
|
| 1179 |
do_action('yatra_trip_loaded_with_relations', $trip); |
| 1180 |
|
| 1181 |
return $trip; |
| 1182 |
} |
| 1183 |
|
| 1184 |
/** |
| 1185 |
* Get destinations for a trip |
| 1186 |
*/ |
| 1187 |
public function getDestinations(int $tripId): array |
| 1188 |
{ |
| 1189 |
global $wpdb; |
| 1190 |
|
| 1191 |
// Use new Classification tables |
| 1192 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 1193 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 1194 |
|
| 1195 |
return $wpdb->get_results( |
| 1196 |
$wpdb->prepare( |
| 1197 |
"SELECT tc.classification_id as id, tc.sort_order, tc.relationship_type, tc.is_featured, c.name, c.slug |
| 1198 |
FROM {$tripClassificationsTable} tc |
| 1199 |
LEFT JOIN {$classificationsTable} c ON c.id = tc.classification_id |
| 1200 |
WHERE tc.trip_id = %d AND c.type = %s |
| 1201 |
ORDER BY tc.sort_order ASC, tc.id ASC", |
| 1202 |
$tripId, ClassificationTypes::DESTINATION |
| 1203 |
) |
| 1204 |
) ?: []; |
| 1205 |
} |
| 1206 |
|
| 1207 |
/** |
| 1208 |
* Get activities for a trip |
| 1209 |
*/ |
| 1210 |
public function getActivities(int $tripId): array |
| 1211 |
{ |
| 1212 |
global $wpdb; |
| 1213 |
|
| 1214 |
// Use new Classification tables |
| 1215 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 1216 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 1217 |
|
| 1218 |
$results = $wpdb->get_results( |
| 1219 |
$wpdb->prepare( |
| 1220 |
"SELECT tc.*, c.name as activity_name, c.slug as activity_slug |
| 1221 |
FROM {$tripClassificationsTable} tc |
| 1222 |
LEFT JOIN {$classificationsTable} c ON c.id = tc.classification_id |
| 1223 |
WHERE tc.trip_id = %d AND tc.classification_type = %s |
| 1224 |
ORDER BY tc.sort_order ASC, tc.id ASC", |
| 1225 |
$tripId, ClassificationTypes::ACTIVITY |
| 1226 |
) |
| 1227 |
) ?: []; |
| 1228 |
|
| 1229 |
// Filter out relationships where the activity doesn't exist in Classifications table |
| 1230 |
$validResults = array_filter($results, function($result) { |
| 1231 |
return !empty($result->activity_name) && !empty($result->activity_slug); |
| 1232 |
}); |
| 1233 |
|
| 1234 |
return array_values($validResults); |
| 1235 |
} |
| 1236 |
|
| 1237 |
/** |
| 1238 |
* Get trip categories for a trip |
| 1239 |
*/ |
| 1240 |
public function getTripCategories(int $tripId): array |
| 1241 |
{ |
| 1242 |
global $wpdb; |
| 1243 |
|
| 1244 |
// Use TripClassificationsTable for trip-category relationships |
| 1245 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 1246 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 1247 |
|
| 1248 |
$sql = $wpdb->prepare( |
| 1249 |
"SELECT tc.*, c.name as category_name, c.slug as category_slug |
| 1250 |
FROM {$tripClassificationsTable} tc |
| 1251 |
LEFT JOIN {$classificationsTable} c ON c.id = tc.classification_id |
| 1252 |
WHERE tc.trip_id = %d AND tc.classification_type = %s |
| 1253 |
ORDER BY tc.sort_order ASC, tc.id ASC", |
| 1254 |
$tripId, ClassificationTypes::CATEGORY |
| 1255 |
); |
| 1256 |
|
| 1257 |
$results = $wpdb->get_results($sql) ?: []; |
| 1258 |
|
| 1259 |
// Filter out relationships where the category doesn't exist in Classifications table |
| 1260 |
$validResults = array_filter($results, function($result) { |
| 1261 |
return !empty($result->category_name) && !empty($result->category_slug); |
| 1262 |
}); |
| 1263 |
|
| 1264 |
return array_values($validResults); |
| 1265 |
} |
| 1266 |
|
| 1267 |
/** |
| 1268 |
* Normalize decoded price_types JSON (trips.price_types column). |
| 1269 |
* |
| 1270 |
* @param mixed $json Raw column value or already-decoded array |
| 1271 |
* @return array<int, array<string, mixed>> |
| 1272 |
*/ |
| 1273 |
protected function parsePriceTypesJson($json): array |
| 1274 |
{ |
| 1275 |
if ($json === null || $json === '') { |
| 1276 |
return []; |
| 1277 |
} |
| 1278 |
|
| 1279 |
$decoded = is_string($json) ? json_decode($json, true) : $json; |
| 1280 |
if (!is_array($decoded)) { |
| 1281 |
return []; |
| 1282 |
} |
| 1283 |
|
| 1284 |
return array_values(array_filter(array_map(function ($pt) { |
| 1285 |
if (!is_array($pt)) { |
| 1286 |
return null; |
| 1287 |
} |
| 1288 |
$normalized = [ |
| 1289 |
'category_id' => isset($pt['category_id']) ? (int) $pt['category_id'] : null, |
| 1290 |
'original_price' => isset($pt['original_price']) ? (float) $pt['original_price'] : null, |
| 1291 |
'discounted_price' => isset($pt['discounted_price']) ? (float) $pt['discounted_price'] : null, |
| 1292 |
'sale_price' => isset($pt['sale_price']) ? (float) $pt['sale_price'] : null, |
| 1293 |
'label' => $pt['label'] ?? ($pt['title'] ?? null), |
| 1294 |
'pricing_mode' => $pt['pricing_mode'] ?? 'per_person', |
| 1295 |
'is_default' => !empty($pt['is_default']), |
| 1296 |
]; |
| 1297 |
if (isset($pt['category_label'])) { |
| 1298 |
$normalized['category_label'] = $pt['category_label']; |
| 1299 |
} |
| 1300 |
if (isset($pt['description'])) { |
| 1301 |
$normalized['description'] = $pt['description']; |
| 1302 |
} |
| 1303 |
|
| 1304 |
return $normalized; |
| 1305 |
}, $decoded))); |
| 1306 |
} |
| 1307 |
|
| 1308 |
/** |
| 1309 |
* Batch-load price_types for many trips (single query). |
| 1310 |
* |
| 1311 |
* @param int[] $trip_ids |
| 1312 |
* @return array<int, array<int, array<string, mixed>>> |
| 1313 |
*/ |
| 1314 |
protected function batchLoadPriceTypesByTripIds(array $trip_ids): array |
| 1315 |
{ |
| 1316 |
$trip_ids = array_values(array_unique(array_map('intval', array_filter($trip_ids)))); |
| 1317 |
if ($trip_ids === []) { |
| 1318 |
return []; |
| 1319 |
} |
| 1320 |
|
| 1321 |
$table = esc_sql($this->table); |
| 1322 |
$placeholders = implode(',', array_fill(0, count($trip_ids), '%d')); |
| 1323 |
$sql = "SELECT id, price_types FROM `{$table}` WHERE id IN ({$placeholders})"; |
| 1324 |
$rows = $this->wpdb->get_results($this->wpdb->prepare($sql, ...$trip_ids)) ?: []; |
| 1325 |
|
| 1326 |
$out = []; |
| 1327 |
foreach ($rows as $row) { |
| 1328 |
$out[(int) $row->id] = $this->parsePriceTypesJson($row->price_types ?? null); |
| 1329 |
} |
| 1330 |
|
| 1331 |
return $out; |
| 1332 |
} |
| 1333 |
|
| 1334 |
/** |
| 1335 |
* Get price types for a trip |
| 1336 |
*/ |
| 1337 |
public function getPriceTypes(int $tripId): array |
| 1338 |
{ |
| 1339 |
$table = esc_sql($this->table); |
| 1340 |
$json = $this->wpdb->get_var( |
| 1341 |
$this->wpdb->prepare("SELECT price_types FROM `{$table}` WHERE id = %d", $tripId) |
| 1342 |
); |
| 1343 |
|
| 1344 |
return $this->parsePriceTypesJson($json); |
| 1345 |
} |
| 1346 |
|
| 1347 |
/** |
| 1348 |
* Get gallery images for a trip |
| 1349 |
*/ |
| 1350 |
public function getGalleryImages(int $tripId): array |
| 1351 |
{ |
| 1352 |
global $wpdb; |
| 1353 |
|
| 1354 |
// Use TripContentTable for gallery images |
| 1355 |
$tripContentTable = \Yatra\Database\Tables\TripContentTable::getTableName(); |
| 1356 |
|
| 1357 |
$rows = $wpdb->get_results( |
| 1358 |
$wpdb->prepare( |
| 1359 |
"SELECT * FROM {$tripContentTable} |
| 1360 |
WHERE trip_id = %d AND content_type = 'image' |
| 1361 |
ORDER BY sort_order ASC, id ASC", |
| 1362 |
$tripId |
| 1363 |
) |
| 1364 |
) ?: []; |
| 1365 |
|
| 1366 |
// Normalize to the shape the edit form expects |
| 1367 |
return array_map(function ($row) { |
| 1368 |
$metadata = []; |
| 1369 |
if (!empty($row->metadata)) { |
| 1370 |
$decoded = json_decode($row->metadata, true); |
| 1371 |
if (is_array($decoded)) { |
| 1372 |
$metadata = $decoded; |
| 1373 |
} |
| 1374 |
} |
| 1375 |
|
| 1376 |
$imageId = $metadata['image_id'] ?? ($row->image_id ?? null); |
| 1377 |
$altText = $metadata['alt_text'] ?? null; |
| 1378 |
$caption = $metadata['caption'] ?? null; |
| 1379 |
$dimensions = $metadata['dimensions'] ?? null; |
| 1380 |
|
| 1381 |
return (object) [ |
| 1382 |
'id' => $imageId ? (int) $imageId : 0, |
| 1383 |
'image_id' => $imageId ? (int) $imageId : 0, |
| 1384 |
'url' => $row->content_url ?? '', |
| 1385 |
'image_url' => $row->content_url ?? '', |
| 1386 |
'thumbnail_url' => $row->thumbnail_url ?? '', |
| 1387 |
'alt_text' => $altText ?? '', |
| 1388 |
'caption' => $caption ?? '', |
| 1389 |
'width' => is_array($dimensions) && isset($dimensions['width']) ? (int) $dimensions['width'] : null, |
| 1390 |
'height' => is_array($dimensions) && isset($dimensions['height']) ? (int) $dimensions['height'] : null, |
| 1391 |
'is_featured' => isset($row->is_featured) ? (bool) $row->is_featured : false, |
| 1392 |
'order' => isset($row->sort_order) ? (int) $row->sort_order : 0, |
| 1393 |
]; |
| 1394 |
}, $rows); |
| 1395 |
} |
| 1396 |
|
| 1397 |
/** |
| 1398 |
* Get highlights for a trip |
| 1399 |
*/ |
| 1400 |
public function getHighlights(int $tripId): array |
| 1401 |
{ |
| 1402 |
global $wpdb; |
| 1403 |
|
| 1404 |
// Use TripContentTable for highlights |
| 1405 |
$tripContentTable = \Yatra\Database\Tables\TripContentTable::getTableName(); |
| 1406 |
|
| 1407 |
$rows = $wpdb->get_results( |
| 1408 |
$wpdb->prepare( |
| 1409 |
"SELECT * FROM {$tripContentTable} |
| 1410 |
WHERE trip_id = %d AND content_type = 'highlight' |
| 1411 |
ORDER BY sort_order ASC, id ASC", |
| 1412 |
$tripId |
| 1413 |
) |
| 1414 |
) ?: []; |
| 1415 |
|
| 1416 |
// Normalize to UI shape |
| 1417 |
return array_map(function ($row) { |
| 1418 |
$metadata = []; |
| 1419 |
if (!empty($row->metadata)) { |
| 1420 |
$decoded = json_decode($row->metadata, true); |
| 1421 |
if (is_array($decoded)) { |
| 1422 |
$metadata = $decoded; |
| 1423 |
} |
| 1424 |
} |
| 1425 |
|
| 1426 |
$imageId = $metadata['image_id'] ?? ($row->image_id ?? null); |
| 1427 |
$icon = $metadata['icon'] ?? ($row->icon ?? null); |
| 1428 |
|
| 1429 |
return (object) [ |
| 1430 |
'text' => $row->title ?? '', |
| 1431 |
'description' => $row->description ?? '', |
| 1432 |
'image_id' => $imageId ? (int) $imageId : 0, |
| 1433 |
'icon' => $icon ?? '', |
| 1434 |
'is_featured' => isset($row->is_featured) ? (bool) $row->is_featured : false, |
| 1435 |
'order' => isset($row->sort_order) ? (int) $row->sort_order : 0, |
| 1436 |
]; |
| 1437 |
}, $rows); |
| 1438 |
} |
| 1439 |
|
| 1440 |
/** |
| 1441 |
* Get landmarks for a trip |
| 1442 |
*/ |
| 1443 |
public function getLandmarks(int $tripId): array |
| 1444 |
{ |
| 1445 |
global $wpdb; |
| 1446 |
|
| 1447 |
// Use TripContentTable for landmarks |
| 1448 |
$tripContentTable = \Yatra\Database\Tables\TripContentTable::getTableName(); |
| 1449 |
|
| 1450 |
$rows = $wpdb->get_results( |
| 1451 |
$wpdb->prepare( |
| 1452 |
"SELECT * FROM {$tripContentTable} |
| 1453 |
WHERE trip_id = %d AND content_type = 'landmark' |
| 1454 |
ORDER BY sort_order ASC, id ASC", |
| 1455 |
$tripId |
| 1456 |
) |
| 1457 |
) ?: []; |
| 1458 |
|
| 1459 |
// Convert to simple array of landmark texts (like SingleTripController) |
| 1460 |
$landmark_texts = []; |
| 1461 |
foreach ($rows as $landmark) { |
| 1462 |
if (!empty($landmark->title)) { |
| 1463 |
$landmark_texts[] = $landmark->title; |
| 1464 |
} elseif (!empty($landmark->description)) { |
| 1465 |
$landmark_texts[] = $landmark->description; |
| 1466 |
} |
| 1467 |
} |
| 1468 |
|
| 1469 |
return $landmark_texts; |
| 1470 |
} |
| 1471 |
|
| 1472 |
/** |
| 1473 |
* Get FAQs for a trip |
| 1474 |
*/ |
| 1475 |
public function getFaqs(int $tripId): array |
| 1476 |
{ |
| 1477 |
global $wpdb; |
| 1478 |
|
| 1479 |
// Use TripContentTable for FAQs |
| 1480 |
$tripContentTable = \Yatra\Database\Tables\TripContentTable::getTableName(); |
| 1481 |
|
| 1482 |
$rows = $wpdb->get_results( |
| 1483 |
$wpdb->prepare( |
| 1484 |
"SELECT * FROM {$tripContentTable} |
| 1485 |
WHERE trip_id = %d AND content_type = 'faq' |
| 1486 |
ORDER BY sort_order ASC, id ASC", |
| 1487 |
$tripId |
| 1488 |
) |
| 1489 |
) ?: []; |
| 1490 |
|
| 1491 |
// Normalize to UI shape |
| 1492 |
return array_map(function ($row) { |
| 1493 |
$metadata = []; |
| 1494 |
if (!empty($row->metadata)) { |
| 1495 |
$decoded = json_decode($row->metadata, true); |
| 1496 |
if (is_array($decoded)) { |
| 1497 |
$metadata = $decoded; |
| 1498 |
} |
| 1499 |
} |
| 1500 |
return (object) [ |
| 1501 |
'question' => $row->title ?? '', |
| 1502 |
'answer' => $row->description ?? '', |
| 1503 |
'category' => $metadata['category'] ?? '', |
| 1504 |
'is_featured' => isset($row->is_featured) ? (bool) $row->is_featured : false, |
| 1505 |
'order' => isset($row->sort_order) ? (int) $row->sort_order : 0, |
| 1506 |
]; |
| 1507 |
}, $rows); |
| 1508 |
} |
| 1509 |
|
| 1510 |
/** |
| 1511 |
* Get availability dates for a trip |
| 1512 |
*/ |
| 1513 |
public function getAvailabilityDates(int $tripId): array |
| 1514 |
{ |
| 1515 |
global $wpdb; |
| 1516 |
$table = TripAvailabilityDatesTable::getTableName(); |
| 1517 |
|
| 1518 |
return $wpdb->get_results( |
| 1519 |
$wpdb->prepare( |
| 1520 |
"SELECT * FROM `{$table}` |
| 1521 |
WHERE trip_id = %d |
| 1522 |
ORDER BY departure_date ASC", |
| 1523 |
$tripId |
| 1524 |
) |
| 1525 |
) ?: []; |
| 1526 |
} |
| 1527 |
|
| 1528 |
/** |
| 1529 |
* Get itinerary days with entries for a trip |
| 1530 |
*/ |
| 1531 |
public function getItineraryDays(int $tripId): array |
| 1532 |
{ |
| 1533 |
global $wpdb; |
| 1534 |
|
| 1535 |
// Use new table names for itinerary |
| 1536 |
$tableDays = \Yatra\Database\Tables\TripItineraryDaysTable::getTableName(); |
| 1537 |
$tableEntries = \Yatra\Database\Tables\TripItineraryDayEntryTable::getTableName(); |
| 1538 |
|
| 1539 |
// Check if tables exist, return empty array if they don't |
| 1540 |
$table_exists = $wpdb->get_var($wpdb->prepare( |
| 1541 |
"SELECT COUNT(*) FROM information_schema.tables |
| 1542 |
WHERE table_schema = %s AND table_name = %s", |
| 1543 |
DB_NAME, |
| 1544 |
$tableDays |
| 1545 |
)); |
| 1546 |
|
| 1547 |
if (!$table_exists) { |
| 1548 |
// Tables don't exist yet, return empty array |
| 1549 |
return []; |
| 1550 |
} |
| 1551 |
|
| 1552 |
// Get all days for this trip |
| 1553 |
$days = $wpdb->get_results( |
| 1554 |
$wpdb->prepare( |
| 1555 |
"SELECT * FROM `{$tableDays}` |
| 1556 |
WHERE trip_id = %d |
| 1557 |
ORDER BY `order` ASC, day_number ASC", |
| 1558 |
$tripId |
| 1559 |
) |
| 1560 |
) ?: []; |
| 1561 |
|
| 1562 |
// For each day, load its entries |
| 1563 |
foreach ($days as $day) { |
| 1564 |
$dayId = (int) $day->id; |
| 1565 |
|
| 1566 |
// Get entries for this day |
| 1567 |
$entries = $wpdb->get_results( |
| 1568 |
$wpdb->prepare( |
| 1569 |
"SELECT * FROM `{$tableEntries}` |
| 1570 |
WHERE day_id = %d |
| 1571 |
ORDER BY `order` ASC", |
| 1572 |
$dayId |
| 1573 |
) |
| 1574 |
) ?: []; |
| 1575 |
|
| 1576 |
// Process each entry |
| 1577 |
foreach ($entries as $entry) { |
| 1578 |
// Decode included/excluded items JSON stored directly on the entry |
| 1579 |
$entry->included_items = $this->decodeAmenityItems($entry->included_items ?? null); |
| 1580 |
$entry->excluded_items = $this->decodeAmenityItems($entry->excluded_items ?? null); |
| 1581 |
|
| 1582 |
// Images are stored in metadata in the new structure |
| 1583 |
$entry->images = []; |
| 1584 |
} |
| 1585 |
|
| 1586 |
// Attach entries to day |
| 1587 |
$day->entries = $entries; |
| 1588 |
} |
| 1589 |
|
| 1590 |
return $days; |
| 1591 |
} |
| 1592 |
|
| 1593 |
/** |
| 1594 |
* Save destinations for a trip |
| 1595 |
*/ |
| 1596 |
public function saveDestinations(int $tripId, array $destinations): void |
| 1597 |
{ |
| 1598 |
global $wpdb; |
| 1599 |
|
| 1600 |
$table = TripClassificationsTable::getTableName(); |
| 1601 |
$classificationsTable = ClassificationsTable::getTableName(); |
| 1602 |
|
| 1603 |
// Delete existing destination relations |
| 1604 |
$wpdb->delete( |
| 1605 |
$table, |
| 1606 |
[ |
| 1607 |
'trip_id' => $tripId, |
| 1608 |
'classification_type' => ClassificationTypes::DESTINATION, |
| 1609 |
], |
| 1610 |
['%d', '%s'] |
| 1611 |
); |
| 1612 |
|
| 1613 |
// Extract destination IDs from destination objects |
| 1614 |
$destinationIds = []; |
| 1615 |
foreach ($destinations as $destination) { |
| 1616 |
if (is_array($destination) && isset($destination['id'])) { |
| 1617 |
$destinationIds[] = (int) $destination['id']; |
| 1618 |
} elseif (is_object($destination) && isset($destination->id)) { |
| 1619 |
$destinationIds[] = (int) $destination->id; |
| 1620 |
} elseif (is_numeric($destination)) { |
| 1621 |
$destinationIds[] = (int) $destination; |
| 1622 |
} |
| 1623 |
} |
| 1624 |
|
| 1625 |
// Validate that destinations exist before saving (same as activities) |
| 1626 |
$validDestinationIds = []; |
| 1627 |
if (!empty($destinationIds)) { |
| 1628 |
$placeholders = implode(',', array_fill(0, count($destinationIds), '%d')); |
| 1629 |
$existingDestinations = $wpdb->get_col( |
| 1630 |
$wpdb->prepare( |
| 1631 |
"SELECT id FROM {$classificationsTable} |
| 1632 |
WHERE id IN ({$placeholders}) AND type = %s", |
| 1633 |
array_merge($destinationIds, [ClassificationTypes::DESTINATION]) |
| 1634 |
) |
| 1635 |
); |
| 1636 |
$validDestinationIds = array_map('intval', $existingDestinations); |
| 1637 |
} |
| 1638 |
|
| 1639 |
// Also clean up any existing invalid destination relationships for this trip |
| 1640 |
$deletedRows = $wpdb->query( |
| 1641 |
$wpdb->prepare( |
| 1642 |
"DELETE FROM {$table} |
| 1643 |
WHERE trip_id = %d AND classification_type = %s |
| 1644 |
AND classification_id NOT IN ( |
| 1645 |
SELECT id FROM {$classificationsTable} WHERE type = %s |
| 1646 |
)", |
| 1647 |
$tripId, ClassificationTypes::DESTINATION, ClassificationTypes::DESTINATION |
| 1648 |
) |
| 1649 |
); |
| 1650 |
|
| 1651 |
|
| 1652 |
// Insert new destination relations |
| 1653 |
if (!empty($validDestinationIds)) { |
| 1654 |
foreach ($validDestinationIds as $index => $destinationId) { |
| 1655 |
$wpdb->insert( |
| 1656 |
$table, |
| 1657 |
[ |
| 1658 |
'trip_id' => $tripId, |
| 1659 |
'classification_id' => $destinationId, |
| 1660 |
'classification_type' => ClassificationTypes::DESTINATION, |
| 1661 |
'relationship_type' => $index === 0 ? 'primary' : 'secondary', |
| 1662 |
'sort_order' => $index, |
| 1663 |
'is_featured' => $index === 0 ? 1 : 0, |
| 1664 |
], |
| 1665 |
['%d', '%d', '%s', '%s', '%d', '%d'] |
| 1666 |
); |
| 1667 |
} |
| 1668 |
} |
| 1669 |
} |
| 1670 |
|
| 1671 |
/** |
| 1672 |
* Save activities for a trip |
| 1673 |
*/ |
| 1674 |
public function saveActivities(int $tripId, array $activityIds): void |
| 1675 |
{ |
| 1676 |
global $wpdb; |
| 1677 |
|
| 1678 |
$table = TripClassificationsTable::getTableName(); |
| 1679 |
$classificationsTable = ClassificationsTable::getTableName(); |
| 1680 |
|
| 1681 |
// Validate that activities exist before saving |
| 1682 |
$validActivityIds = []; |
| 1683 |
if (!empty($activityIds)) { |
| 1684 |
$placeholders = implode(',', array_fill(0, count($activityIds), '%d')); |
| 1685 |
$existingActivities = $wpdb->get_col( |
| 1686 |
$wpdb->prepare( |
| 1687 |
"SELECT id FROM {$classificationsTable} |
| 1688 |
WHERE id IN ({$placeholders}) AND type = %s", |
| 1689 |
array_merge($activityIds, [ClassificationTypes::ACTIVITY]) |
| 1690 |
) |
| 1691 |
); |
| 1692 |
$validActivityIds = array_map('intval', $existingActivities); |
| 1693 |
} |
| 1694 |
|
| 1695 |
// Delete existing activity relations |
| 1696 |
$wpdb->delete( |
| 1697 |
$table, |
| 1698 |
[ |
| 1699 |
'trip_id' => $tripId, |
| 1700 |
'classification_type' => ClassificationTypes::ACTIVITY, |
| 1701 |
], |
| 1702 |
['%d', '%s'] |
| 1703 |
); |
| 1704 |
|
| 1705 |
// Insert new activity relations (only for valid activities) |
| 1706 |
if (!empty($validActivityIds)) { |
| 1707 |
foreach ($validActivityIds as $index => $activityId) { |
| 1708 |
$wpdb->insert( |
| 1709 |
$table, |
| 1710 |
[ |
| 1711 |
'trip_id' => $tripId, |
| 1712 |
'classification_id' => (int) $activityId, |
| 1713 |
'classification_type' => ClassificationTypes::ACTIVITY, |
| 1714 |
'relationship_type' => $index === 0 ? 'primary' : 'secondary', |
| 1715 |
'sort_order' => $index, |
| 1716 |
'is_featured' => $index === 0 ? 1 : 0, |
| 1717 |
], |
| 1718 |
['%d', '%d', '%s', '%s', '%d', '%d'] |
| 1719 |
); |
| 1720 |
} |
| 1721 |
} |
| 1722 |
} |
| 1723 |
|
| 1724 |
/** |
| 1725 |
* Save trip categories for a trip |
| 1726 |
*/ |
| 1727 |
public function saveTripCategories(int $tripId, array $categoryIds): void |
| 1728 |
{ |
| 1729 |
global $wpdb; |
| 1730 |
|
| 1731 |
$table = TripClassificationsTable::getTableName(); |
| 1732 |
|
| 1733 |
// Delete existing category relations |
| 1734 |
$wpdb->delete( |
| 1735 |
$table, |
| 1736 |
[ |
| 1737 |
'trip_id' => $tripId, |
| 1738 |
'classification_type' => ClassificationTypes::CATEGORY, |
| 1739 |
], |
| 1740 |
['%d', '%s'] |
| 1741 |
); |
| 1742 |
|
| 1743 |
// Insert new categories |
| 1744 |
if (!empty($categoryIds)) { |
| 1745 |
foreach ($categoryIds as $index => $categoryId) { |
| 1746 |
$wpdb->insert( |
| 1747 |
$table, |
| 1748 |
[ |
| 1749 |
'trip_id' => $tripId, |
| 1750 |
'classification_id' => (int) $categoryId, |
| 1751 |
'classification_type' => ClassificationTypes::CATEGORY, |
| 1752 |
'relationship_type' => $index === 0 ? 'primary' : 'secondary', |
| 1753 |
'sort_order' => $index, |
| 1754 |
'is_featured' => $index === 0 ? 1 : 0, |
| 1755 |
], |
| 1756 |
['%d', '%d', '%s', '%s', '%d', '%d'] |
| 1757 |
); |
| 1758 |
} |
| 1759 |
} |
| 1760 |
} |
| 1761 |
|
| 1762 |
/** |
| 1763 |
* Save price types for a trip |
| 1764 |
*/ |
| 1765 |
public function savePriceTypes(int $tripId, array $priceTypes): void |
| 1766 |
{ |
| 1767 |
if (empty($priceTypes)) { |
| 1768 |
// Explicitly clear stored per-category pricing so that deleting all |
| 1769 |
// rows (even the last one) persists. Previously this early-returned, |
| 1770 |
// leaving the old price_types JSON in place — the deletion silently |
| 1771 |
// reverted on reload. Only the JSON is always cleared; the derived |
| 1772 |
// scalar columns (original/discounted/sale) are reset solely for |
| 1773 |
// traveler-based trips, because for regular pricing those columns |
| 1774 |
// hold the actual price and must not be wiped. |
| 1775 |
$table = TripsTable::getTableName(); |
| 1776 |
$pricingType = $this->wpdb->get_var( |
| 1777 |
$this->wpdb->prepare("SELECT pricing_type FROM `{$table}` WHERE id = %d", $tripId) |
| 1778 |
); |
| 1779 |
|
| 1780 |
$data = ['price_types' => null]; |
| 1781 |
if ($pricingType === 'traveler_based') { |
| 1782 |
$data['original_price'] = null; |
| 1783 |
$data['discounted_price'] = null; |
| 1784 |
$data['sale_price'] = null; |
| 1785 |
} |
| 1786 |
|
| 1787 |
$this->wpdb->update($table, $data, ['id' => $tripId]); |
| 1788 |
return; |
| 1789 |
} |
| 1790 |
|
| 1791 |
// Ensure at most one default category is set (keep the first truthy one). |
| 1792 |
$defaultFound = false; |
| 1793 |
foreach ($priceTypes as &$pt) { |
| 1794 |
if (!is_array($pt)) { |
| 1795 |
continue; |
| 1796 |
} |
| 1797 |
$isDefault = !empty($pt['is_default']); |
| 1798 |
if ($isDefault && !$defaultFound) { |
| 1799 |
$defaultFound = true; |
| 1800 |
$pt['is_default'] = true; |
| 1801 |
} else { |
| 1802 |
$pt['is_default'] = false; |
| 1803 |
} |
| 1804 |
} |
| 1805 |
unset($pt); |
| 1806 |
|
| 1807 |
// Compute minimal pricing values from provided price types |
| 1808 |
$minOriginal = PHP_FLOAT_MAX; |
| 1809 |
$minDiscounted = PHP_FLOAT_MAX; |
| 1810 |
$minSale = PHP_FLOAT_MAX; |
| 1811 |
|
| 1812 |
foreach ($priceTypes as $priceType) { |
| 1813 |
$original = isset($priceType['original_price']) ? (float) $priceType['original_price'] : null; |
| 1814 |
$discounted = isset($priceType['discounted_price']) ? (float) $priceType['discounted_price'] : null; |
| 1815 |
$sale = isset($priceType['sale_price']) ? (float) $priceType['sale_price'] : null; |
| 1816 |
|
| 1817 |
if ($original !== null && $original > 0 && $original < $minOriginal) { |
| 1818 |
$minOriginal = $original; |
| 1819 |
} |
| 1820 |
if ($discounted !== null && $discounted > 0 && $discounted < $minDiscounted) { |
| 1821 |
$minDiscounted = $discounted; |
| 1822 |
} |
| 1823 |
if ($sale !== null && $sale > 0 && $sale < $minSale) { |
| 1824 |
$minSale = $sale; |
| 1825 |
} |
| 1826 |
} |
| 1827 |
|
| 1828 |
// Normalize infinity values to null |
| 1829 |
$minOriginal = ($minOriginal === PHP_FLOAT_MAX) ? null : $minOriginal; |
| 1830 |
$minDiscounted = ($minDiscounted === PHP_FLOAT_MAX) ? null : $minDiscounted; |
| 1831 |
$minSale = ($minSale === PHP_FLOAT_MAX) ? null : $minSale; |
| 1832 |
|
| 1833 |
// Determine final prices to store on trips table |
| 1834 |
$finalOriginal = $minOriginal; |
| 1835 |
$finalDiscounted = $minDiscounted ?? null; |
| 1836 |
$finalSale = $minSale ?? null; |
| 1837 |
|
| 1838 |
// If no discounted/sale but original exists, keep it; else leave unchanged |
| 1839 |
$data = []; |
| 1840 |
$format = []; |
| 1841 |
|
| 1842 |
// Persist full price_types JSON for reference (stored on trips table) |
| 1843 |
$data['price_types'] = wp_json_encode($priceTypes); |
| 1844 |
$format[] = '%s'; |
| 1845 |
|
| 1846 |
if ($finalOriginal !== null) { |
| 1847 |
$data['original_price'] = $finalOriginal; |
| 1848 |
$format[] = '%f'; |
| 1849 |
} |
| 1850 |
if ($finalDiscounted !== null) { |
| 1851 |
$data['discounted_price'] = $finalDiscounted; |
| 1852 |
$format[] = '%f'; |
| 1853 |
} |
| 1854 |
if ($finalSale !== null) { |
| 1855 |
$data['sale_price'] = $finalSale; |
| 1856 |
$format[] = '%f'; |
| 1857 |
} |
| 1858 |
|
| 1859 |
if (!empty($data)) { |
| 1860 |
$this->wpdb->update( |
| 1861 |
TripsTable::getTableName(), |
| 1862 |
$data, |
| 1863 |
['id' => $tripId], |
| 1864 |
$format, |
| 1865 |
['%d'] |
| 1866 |
); |
| 1867 |
} |
| 1868 |
} |
| 1869 |
|
| 1870 |
/** |
| 1871 |
* Save highlights for a trip |
| 1872 |
*/ |
| 1873 |
public function saveHighlights(int $tripId, array $highlights): void |
| 1874 |
{ |
| 1875 |
global $wpdb; |
| 1876 |
|
| 1877 |
$table = TripContentTable::getTableName(); |
| 1878 |
|
| 1879 |
// Delete existing highlights |
| 1880 |
$wpdb->delete( |
| 1881 |
$table, |
| 1882 |
[ |
| 1883 |
'trip_id' => $tripId, |
| 1884 |
'content_type' => 'highlight', |
| 1885 |
], |
| 1886 |
['%d', '%s'] |
| 1887 |
); |
| 1888 |
|
| 1889 |
if (!empty($highlights)) { |
| 1890 |
foreach ($highlights as $index => $highlight) { |
| 1891 |
$highlightText = is_string($highlight) ? $highlight : ($highlight['text'] ?? $highlight['highlight_text'] ?? ''); |
| 1892 |
if (empty($highlightText)) { |
| 1893 |
continue; |
| 1894 |
} |
| 1895 |
|
| 1896 |
$metadata = []; |
| 1897 |
if (is_array($highlight)) { |
| 1898 |
if (!empty($highlight['icon'])) { |
| 1899 |
$metadata['icon'] = $highlight['icon']; |
| 1900 |
} |
| 1901 |
if (!empty($highlight['image_id'])) { |
| 1902 |
$metadata['image_id'] = (int) $highlight['image_id']; |
| 1903 |
} |
| 1904 |
} |
| 1905 |
|
| 1906 |
$wpdb->insert( |
| 1907 |
$table, |
| 1908 |
[ |
| 1909 |
'trip_id' => $tripId, |
| 1910 |
'content_type' => 'highlight', |
| 1911 |
'title' => sanitize_text_field($highlightText), |
| 1912 |
'description' => is_array($highlight) && !empty($highlight['description']) ? wp_kses_post($highlight['description']) : null, |
| 1913 |
'metadata' => !empty($metadata) ? wp_json_encode($metadata) : null, |
| 1914 |
'sort_order' => $index, |
| 1915 |
'is_featured' => is_array($highlight) && isset($highlight['is_featured']) ? (int) $highlight['is_featured'] : 0, |
| 1916 |
], |
| 1917 |
['%d', '%s', '%s', '%s', '%s', '%d', '%d'] |
| 1918 |
); |
| 1919 |
} |
| 1920 |
} |
| 1921 |
} |
| 1922 |
|
| 1923 |
/** |
| 1924 |
* Save landmarks for a trip |
| 1925 |
*/ |
| 1926 |
public function saveLandmarks(int $tripId, array $landmarks): void |
| 1927 |
{ |
| 1928 |
global $wpdb; |
| 1929 |
|
| 1930 |
$table = TripContentTable::getTableName(); |
| 1931 |
|
| 1932 |
// Delete existing landmarks |
| 1933 |
$wpdb->delete( |
| 1934 |
$table, |
| 1935 |
[ |
| 1936 |
'trip_id' => $tripId, |
| 1937 |
'content_type' => 'landmark', |
| 1938 |
], |
| 1939 |
['%d', '%s'] |
| 1940 |
); |
| 1941 |
|
| 1942 |
if (!empty($landmarks)) { |
| 1943 |
foreach ($landmarks as $index => $landmark) { |
| 1944 |
$landmarkText = is_string($landmark) ? $landmark : ($landmark['text'] ?? $landmark['landmark_text'] ?? ''); |
| 1945 |
if (empty($landmarkText)) { |
| 1946 |
continue; |
| 1947 |
} |
| 1948 |
|
| 1949 |
$metadata = []; |
| 1950 |
if (is_array($landmark)) { |
| 1951 |
if (!empty($landmark['icon'])) { |
| 1952 |
$metadata['icon'] = $landmark['icon']; |
| 1953 |
} |
| 1954 |
if (!empty($landmark['image_id'])) { |
| 1955 |
$metadata['image_id'] = (int) $landmark['image_id']; |
| 1956 |
} |
| 1957 |
} |
| 1958 |
|
| 1959 |
$wpdb->insert( |
| 1960 |
$table, |
| 1961 |
[ |
| 1962 |
'trip_id' => $tripId, |
| 1963 |
'content_type' => 'landmark', |
| 1964 |
'title' => sanitize_text_field($landmarkText), |
| 1965 |
'description' => is_array($landmark) && !empty($landmark['description']) ? wp_kses_post($landmark['description']) : null, |
| 1966 |
'metadata' => !empty($metadata) ? wp_json_encode($metadata) : null, |
| 1967 |
'sort_order' => $index, |
| 1968 |
'is_featured' => is_array($landmark) && isset($landmark['is_featured']) ? (int) $landmark['is_featured'] : 0, |
| 1969 |
], |
| 1970 |
['%d', '%s', '%s', '%s', '%s', '%d', '%d'] |
| 1971 |
); |
| 1972 |
} |
| 1973 |
} |
| 1974 |
} |
| 1975 |
|
| 1976 |
/** |
| 1977 |
* Save gallery images for a trip |
| 1978 |
*/ |
| 1979 |
public function saveGalleryImages(int $tripId, array $galleryImages): void |
| 1980 |
{ |
| 1981 |
global $wpdb; |
| 1982 |
|
| 1983 |
$table = TripContentTable::getTableName(); |
| 1984 |
|
| 1985 |
// Delete existing gallery images |
| 1986 |
$wpdb->delete( |
| 1987 |
$table, |
| 1988 |
[ |
| 1989 |
'trip_id' => $tripId, |
| 1990 |
'content_type' => 'image', |
| 1991 |
], |
| 1992 |
['%d', '%s'] |
| 1993 |
); |
| 1994 |
|
| 1995 |
// Insert new |
| 1996 |
if (!empty($galleryImages)) { |
| 1997 |
foreach ($galleryImages as $index => $image) { |
| 1998 |
$imageUrl = is_string($image) ? $image : ($image['url'] ?? $image['image_url'] ?? ''); |
| 1999 |
if (empty($imageUrl)) { |
| 2000 |
continue; |
| 2001 |
} |
| 2002 |
|
| 2003 |
$metadata = []; |
| 2004 |
if (is_array($image)) { |
| 2005 |
if (!empty($image['alt_text'])) { |
| 2006 |
$metadata['alt_text'] = $image['alt_text']; |
| 2007 |
} |
| 2008 |
if (!empty($image['caption'])) { |
| 2009 |
$metadata['caption'] = $image['caption']; |
| 2010 |
} |
| 2011 |
} |
| 2012 |
|
| 2013 |
$wpdb->insert( |
| 2014 |
$table, |
| 2015 |
[ |
| 2016 |
'trip_id' => $tripId, |
| 2017 |
'content_type' => 'image', |
| 2018 |
'content_url' => esc_url_raw($imageUrl), |
| 2019 |
'file_path' => is_array($image) ? ($image['file_path'] ?? null) : null, |
| 2020 |
'metadata' => !empty($metadata) ? wp_json_encode($metadata) : null, |
| 2021 |
'thumbnail_url' => is_array($image) ? ($image['thumbnail_url'] ?? null) : null, |
| 2022 |
'sort_order' => $index, |
| 2023 |
'is_featured' => is_array($image) && isset($image['is_featured']) ? (int) $image['is_featured'] : 0, |
| 2024 |
], |
| 2025 |
['%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d'] |
| 2026 |
); |
| 2027 |
} |
| 2028 |
} |
| 2029 |
} |
| 2030 |
|
| 2031 |
/** |
| 2032 |
* Save FAQs for a trip |
| 2033 |
*/ |
| 2034 |
public function saveFaqs(int $tripId, array $faqs): void |
| 2035 |
{ |
| 2036 |
global $wpdb; |
| 2037 |
|
| 2038 |
$table = TripContentTable::getTableName(); |
| 2039 |
|
| 2040 |
// Delete existing FAQs |
| 2041 |
$wpdb->delete( |
| 2042 |
$table, |
| 2043 |
[ |
| 2044 |
'trip_id' => $tripId, |
| 2045 |
'content_type' => 'faq', |
| 2046 |
], |
| 2047 |
['%d', '%s'] |
| 2048 |
); |
| 2049 |
|
| 2050 |
// Insert new FAQs |
| 2051 |
if (!empty($faqs)) { |
| 2052 |
foreach ($faqs as $index => $faq) { |
| 2053 |
if (!is_array($faq) || empty($faq['question']) || empty($faq['answer'])) { |
| 2054 |
continue; |
| 2055 |
} |
| 2056 |
|
| 2057 |
$metadata = []; |
| 2058 |
if (!empty($faq['category'])) { |
| 2059 |
$metadata['category'] = sanitize_text_field($faq['category']); |
| 2060 |
} |
| 2061 |
|
| 2062 |
$wpdb->insert( |
| 2063 |
$table, |
| 2064 |
[ |
| 2065 |
'trip_id' => $tripId, |
| 2066 |
'content_type' => 'faq', |
| 2067 |
'title' => sanitize_text_field($faq['question']), |
| 2068 |
'description' => wp_kses_post($faq['answer']), |
| 2069 |
'metadata' => !empty($metadata) ? wp_json_encode($metadata) : null, |
| 2070 |
'sort_order' => $index, |
| 2071 |
'is_featured' => isset($faq['is_featured']) ? (int) $faq['is_featured'] : 0, |
| 2072 |
], |
| 2073 |
['%d', '%s', '%s', '%s', '%s', '%d', '%d'] |
| 2074 |
); |
| 2075 |
} |
| 2076 |
} |
| 2077 |
} |
| 2078 |
|
| 2079 |
/** |
| 2080 |
* Save entries for a specific day using upsert strategy |
| 2081 |
*/ |
| 2082 |
private function saveDayEntries(int $dayId, array $entries, array $existingEntries): void |
| 2083 |
{ |
| 2084 |
global $wpdb; |
| 2085 |
$tableEntries = TripItineraryDayEntryTable::getTableName(); |
| 2086 |
|
| 2087 |
// Create lookup map for existing entries |
| 2088 |
$existingEntryMap = []; |
| 2089 |
foreach ($existingEntries as $entry) { |
| 2090 |
$key = $entry->title . '|' . ($entry->order ?? 0); |
| 2091 |
$existingEntryMap[$key] = $entry; |
| 2092 |
} |
| 2093 |
|
| 2094 |
$processedEntryIds = []; |
| 2095 |
foreach ($entries as $entryIndex => $entry) { |
| 2096 |
if (!is_array($entry) || empty($entry['title'])) continue; |
| 2097 |
|
| 2098 |
$entryKey = $entry['title'] . '|' . $entryIndex; |
| 2099 |
$entryData = [ |
| 2100 |
'title' => sanitize_text_field($entry['title']), |
| 2101 |
'description' => isset($entry['description']) ? wp_kses_post($entry['description']) : null, |
| 2102 |
'item_type_id' => isset($entry['item_type_id']) ? (int) $entry['item_type_id'] : null, |
| 2103 |
'item_id' => isset($entry['item_id']) ? (int) $entry['item_id'] : null, |
| 2104 |
'item_type' => isset($entry['item_type']) ? sanitize_text_field($entry['item_type']) : null, |
| 2105 |
'item_name' => isset($entry['item_name']) ? sanitize_text_field($entry['item_name']) : null, |
| 2106 |
'item_icon' => isset($entry['item_icon']) ? sanitize_text_field($entry['item_icon']) : null, |
| 2107 |
'time' => isset($entry['time']) ? sanitize_text_field($entry['time']) : null, |
| 2108 |
'start_time' => isset($entry['start_time']) ? sanitize_text_field($entry['start_time']) : null, |
| 2109 |
'end_time' => isset($entry['end_time']) ? sanitize_text_field($entry['end_time']) : null, |
| 2110 |
'time_type' => isset($entry['time_type']) ? sanitize_text_field($entry['time_type']) : 'exact', |
| 2111 |
'location' => isset($entry['location']) ? sanitize_text_field($entry['location']) : null, |
| 2112 |
'duration' => isset($entry['duration']) ? sanitize_text_field($entry['duration']) : null, |
| 2113 |
'cost' => isset($entry['cost']) ? floatval($entry['cost']) : null, |
| 2114 |
'cost_per_person' => isset($entry['cost_per_person']) ? (int) $entry['cost_per_person'] : 0, |
| 2115 |
'notes' => isset($entry['notes']) ? wp_kses_post($entry['notes']) : null, |
| 2116 |
'included_items' => isset($entry['included_items']) ? wp_json_encode($entry['included_items']) : null, |
| 2117 |
'excluded_items' => isset($entry['excluded_items']) ? wp_json_encode($entry['excluded_items']) : null, |
| 2118 |
'gallery' => isset($entry['gallery']) ? wp_json_encode($entry['gallery']) : null, |
| 2119 |
'video_url' => isset($entry['video_url']) ? esc_url_raw($entry['video_url']) : null, |
| 2120 |
'status' => isset($entry['status']) ? sanitize_text_field($entry['status']) : 'publish', |
| 2121 |
'order' => $entryIndex, |
| 2122 |
'updated_at' => current_time('mysql'), |
| 2123 |
]; |
| 2124 |
|
| 2125 |
// Update existing entry or insert new |
| 2126 |
if (isset($existingEntryMap[$entryKey])) { |
| 2127 |
$existingEntry = $existingEntryMap[$entryKey]; |
| 2128 |
$wpdb->update($tableEntries, $entryData, ['id' => $existingEntry->id]); |
| 2129 |
$processedEntryIds[] = $existingEntry->id; |
| 2130 |
} else { |
| 2131 |
$entryData['day_id'] = $dayId; |
| 2132 |
$entryData['trip_id'] = $this->getTripIdByDayId($dayId); |
| 2133 |
$entryData['created_at'] = current_time('mysql'); |
| 2134 |
$wpdb->insert($tableEntries, $entryData); |
| 2135 |
$processedEntryIds[] = $wpdb->insert_id; |
| 2136 |
} |
| 2137 |
} |
| 2138 |
|
| 2139 |
// Delete entries that are no longer present |
| 2140 |
if (!empty($processedEntryIds)) { |
| 2141 |
$placeholders = implode(',', array_fill(0, count($processedEntryIds), '%d')); |
| 2142 |
$wpdb->query($wpdb->prepare( |
| 2143 |
"DELETE FROM {$tableEntries} WHERE day_id = %d AND id NOT IN ({$placeholders})", |
| 2144 |
$dayId, |
| 2145 |
...$processedEntryIds |
| 2146 |
)); |
| 2147 |
} else { |
| 2148 |
// If no entries provided, delete all entries for this day |
| 2149 |
$wpdb->delete($tableEntries, ['day_id' => $dayId], ['%d']); |
| 2150 |
} |
| 2151 |
} |
| 2152 |
|
| 2153 |
/** |
| 2154 |
* Get trip ID by day ID |
| 2155 |
*/ |
| 2156 |
private function getTripIdByDayId(int $dayId): int |
| 2157 |
{ |
| 2158 |
global $wpdb; |
| 2159 |
$tableDays = TripItineraryDaysTable::getTableName(); |
| 2160 |
return (int) $wpdb->get_var($wpdb->prepare( |
| 2161 |
"SELECT trip_id FROM {$tableDays} WHERE id = %d", |
| 2162 |
$dayId |
| 2163 |
)); |
| 2164 |
} |
| 2165 |
|
| 2166 |
/** |
| 2167 |
* Save availability dates for a trip. |
| 2168 |
* |
| 2169 |
* Previously a flat DELETE-THEN-INSERT: every row for the trip was nuked |
| 2170 |
* and the incoming list rewritten. That made the trip-edit save lossy — |
| 2171 |
* when the Trip form only knew about a subset of fields on each date |
| 2172 |
* (e.g. it was loaded once, then the operator changed the seats on a |
| 2173 |
* single date through the Availability tab, then re-saved the Trip with |
| 2174 |
* the form's stale date payload), every field absent from the incoming |
| 2175 |
* row was reset to its hard-coded default. That's why operator edits |
| 2176 |
* to `seats_total` silently reverted to 20 after another trip-save: |
| 2177 |
* |
| 2178 |
* 1. Operator opens Trip → form loads `availability_dates` with |
| 2179 |
* `seats_total = 20`. |
| 2180 |
* 2. Operator switches to Availability tab → edits Sept 5 to |
| 2181 |
* `seats_total = 14` via the dedicated specific-date endpoint |
| 2182 |
* (writes directly to the row, so the row is now 14). |
| 2183 |
* 3. Operator returns to the Trip form, edits something unrelated |
| 2184 |
* (title / description), clicks Save. The form re-posts the date |
| 2185 |
* list it loaded in step 1 — `seats_total = 20`. |
| 2186 |
* 4. saveAvailabilityDates DELETEs everything, INSERTs the |
| 2187 |
* form-supplied list → Sept 5 is back to 20 (or the literal `20` |
| 2188 |
* default if the form didn't include seats_total at all). |
| 2189 |
* |
| 2190 |
* Fix: snapshot the existing rows before the DELETE and, when the |
| 2191 |
* incoming row omits an editable field, fall back to the snapshot's |
| 2192 |
* value instead of the schema default. The literal `20` default is |
| 2193 |
* removed in favour of the trip's `max_travelers` (or 1 as a last |
| 2194 |
* resort) so new dates inserted by truly-new entries don't pretend the |
| 2195 |
* trip seats 20 people unless the trip actually says so. |
| 2196 |
* |
| 2197 |
* Match key for the snapshot: `departure_date` + `departure_time` (both |
| 2198 |
* normalised), which is the same identity the Availability UI uses. |
| 2199 |
*/ |
| 2200 |
public function saveAvailabilityDates(int $tripId, array $availabilityDates): void |
| 2201 |
{ |
| 2202 |
global $wpdb; |
| 2203 |
$table = TripAvailabilityDatesTable::getTableName(); |
| 2204 |
|
| 2205 |
// Snapshot existing rows by date + time so partial incoming payloads |
| 2206 |
// can be merged with persisted values instead of clobbering them. |
| 2207 |
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name from schema helper. |
| 2208 |
$existingRows = $wpdb->get_results($wpdb->prepare( |
| 2209 |
"SELECT * FROM {$table} WHERE trip_id = %d", |
| 2210 |
$tripId |
| 2211 |
)); |
| 2212 |
$snapshot = []; |
| 2213 |
if (is_array($existingRows)) { |
| 2214 |
foreach ($existingRows as $row) { |
| 2215 |
$key = self::availabilityRowKey( |
| 2216 |
(string) ($row->departure_date ?? ''), |
| 2217 |
(string) ($row->departure_time ?? '') |
| 2218 |
); |
| 2219 |
if ($key !== '') { |
| 2220 |
$snapshot[$key] = $row; |
| 2221 |
} |
| 2222 |
} |
| 2223 |
} |
| 2224 |
|
| 2225 |
// Fallback capacity when both the incoming row and the snapshot |
| 2226 |
// miss seats_total: read the trip's max_travelers once. The |
| 2227 |
// legacy literal `20` was a phantom default that punished sites |
| 2228 |
// whose actual trip capacity differs. |
| 2229 |
$tripFallbackSeats = 0; |
| 2230 |
$tripRow = $this->find($tripId); |
| 2231 |
if (is_object($tripRow) && isset($tripRow->max_travelers)) { |
| 2232 |
$tripFallbackSeats = (int) $tripRow->max_travelers; |
| 2233 |
} |
| 2234 |
if ($tripFallbackSeats <= 0) { |
| 2235 |
$tripFallbackSeats = 1; |
| 2236 |
} |
| 2237 |
|
| 2238 |
// Delete existing |
| 2239 |
$wpdb->delete($table, ['trip_id' => $tripId], ['%d']); |
| 2240 |
|
| 2241 |
// Insert new |
| 2242 |
if (!empty($availabilityDates)) { |
| 2243 |
foreach ($availabilityDates as $date) { |
| 2244 |
if (is_array($date) && !empty($date['departure_date'])) { |
| 2245 |
$key = self::availabilityRowKey( |
| 2246 |
(string) $date['departure_date'], |
| 2247 |
(string) ($date['departure_time'] ?? '') |
| 2248 |
); |
| 2249 |
$existing = $key !== '' && isset($snapshot[$key]) ? $snapshot[$key] : null; |
| 2250 |
|
| 2251 |
$seatsTotal = isset($date['seats_total']) |
| 2252 |
? (int) $date['seats_total'] |
| 2253 |
: ($existing ? (int) ($existing->seats_total ?? $tripFallbackSeats) : $tripFallbackSeats); |
| 2254 |
$seatsAvailable = isset($date['seats_available']) |
| 2255 |
? (int) $date['seats_available'] |
| 2256 |
: ($existing ? (int) ($existing->seats_available ?? $seatsTotal) : $seatsTotal); |
| 2257 |
|
| 2258 |
$arrivalDate = isset($date['arrival_date']) |
| 2259 |
? sanitize_text_field($date['arrival_date']) |
| 2260 |
: (isset($date['return_date']) |
| 2261 |
? sanitize_text_field($date['return_date']) |
| 2262 |
: ($existing ? $existing->arrival_date : null)); |
| 2263 |
$returnDate = isset($date['return_date']) |
| 2264 |
? sanitize_text_field($date['return_date']) |
| 2265 |
: ($existing ? $existing->return_date : null); |
| 2266 |
$arrivalTime = isset($date['arrival_time']) |
| 2267 |
? sanitize_text_field($date['arrival_time']) |
| 2268 |
: ($existing ? $existing->arrival_time : null); |
| 2269 |
|
| 2270 |
$originalPrice = isset($date['original_price']) |
| 2271 |
? (float) $date['original_price'] |
| 2272 |
: (isset($date['price_override']) |
| 2273 |
? (float) $date['price_override'] |
| 2274 |
: ($existing && $existing->original_price !== null ? (float) $existing->original_price : null)); |
| 2275 |
$discountedPrice = isset($date['discounted_price']) |
| 2276 |
? (float) $date['discounted_price'] |
| 2277 |
: ($existing && $existing->discounted_price !== null ? (float) $existing->discounted_price : null); |
| 2278 |
|
| 2279 |
$fromLocation = isset($date['from_location']) ? sanitize_text_field($date['from_location']) : ($existing ? $existing->from_location : null); |
| 2280 |
$toLocation = isset($date['to_location']) ? sanitize_text_field($date['to_location']) : ($existing ? $existing->to_location : null); |
| 2281 |
$fromLat = isset($date['from_latitude']) && is_numeric($date['from_latitude']) |
| 2282 |
? (string) $date['from_latitude'] |
| 2283 |
: ($existing ? $existing->from_latitude : null); |
| 2284 |
$fromLng = isset($date['from_longitude']) && is_numeric($date['from_longitude']) |
| 2285 |
? (string) $date['from_longitude'] |
| 2286 |
: ($existing ? $existing->from_longitude : null); |
| 2287 |
$toLat = isset($date['to_latitude']) && is_numeric($date['to_latitude']) |
| 2288 |
? (string) $date['to_latitude'] |
| 2289 |
: ($existing ? $existing->to_latitude : null); |
| 2290 |
$toLng = isset($date['to_longitude']) && is_numeric($date['to_longitude']) |
| 2291 |
? (string) $date['to_longitude'] |
| 2292 |
: ($existing ? $existing->to_longitude : null); |
| 2293 |
|
| 2294 |
if (isset($date['is_blackout'])) { |
| 2295 |
$status = $date['is_blackout'] ? 'blocked' : 'available'; |
| 2296 |
} elseif (isset($date['status'])) { |
| 2297 |
$status = sanitize_text_field($date['status']); |
| 2298 |
} elseif ($existing) { |
| 2299 |
$status = (string) ($existing->status ?? 'available'); |
| 2300 |
} else { |
| 2301 |
$status = 'available'; |
| 2302 |
} |
| 2303 |
|
| 2304 |
$insertData = [ |
| 2305 |
'trip_id' => $tripId, |
| 2306 |
'departure_date' => sanitize_text_field($date['departure_date']), |
| 2307 |
'arrival_date' => $arrivalDate, |
| 2308 |
'return_date' => $returnDate, |
| 2309 |
'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : ($existing ? $existing->departure_time : null), |
| 2310 |
'arrival_time' => $arrivalTime, |
| 2311 |
'seats_total' => $seatsTotal, |
| 2312 |
'seats_available' => $seatsAvailable, |
| 2313 |
'original_price' => $originalPrice, |
| 2314 |
'discounted_price' => $discountedPrice, |
| 2315 |
'from_location' => $fromLocation, |
| 2316 |
'to_location' => $toLocation, |
| 2317 |
'from_latitude' => $fromLat, |
| 2318 |
'from_longitude' => $fromLng, |
| 2319 |
'to_latitude' => $toLat, |
| 2320 |
'to_longitude' => $toLng, |
| 2321 |
'status' => $status, |
| 2322 |
]; |
| 2323 |
|
| 2324 |
$wpdb->insert( |
| 2325 |
$table, |
| 2326 |
$insertData, |
| 2327 |
['%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s'] |
| 2328 |
); |
| 2329 |
} |
| 2330 |
} |
| 2331 |
} |
| 2332 |
} |
| 2333 |
|
| 2334 |
/** |
| 2335 |
* Normalise a (date, time) pair to a single string key used to match |
| 2336 |
* incoming availability rows against the pre-delete snapshot. Time is |
| 2337 |
* left as-is for an exact comparison; empty time matches the "no |
| 2338 |
* specific time" row. |
| 2339 |
*/ |
| 2340 |
private static function availabilityRowKey(string $date, string $time): string |
| 2341 |
{ |
| 2342 |
$date = trim($date); |
| 2343 |
if ($date === '') { |
| 2344 |
return ''; |
| 2345 |
} |
| 2346 |
return $date . '|' . trim($time); |
| 2347 |
} |
| 2348 |
|
| 2349 |
/** |
| 2350 |
* Save attributes for a trip |
| 2351 |
*/ |
| 2352 |
public function saveAttributes(int $tripId, array $attributes): void |
| 2353 |
{ |
| 2354 |
$tripAttributeRepository = new \Yatra\Repositories\TripAttributeRepository(); |
| 2355 |
$tripAttributeRepository->saveTripAttributes($tripId, $attributes); |
| 2356 |
} |
| 2357 |
|
| 2358 |
/** |
| 2359 |
* Create trip with relationships |
| 2360 |
*/ |
| 2361 |
public function createWithRelations(array $data, array $relationships = []): int |
| 2362 |
{ |
| 2363 |
// Extract relationship data |
| 2364 |
$destinations = $relationships['destinations'] ?? []; |
| 2365 |
$activities = $relationships['activities'] ?? []; |
| 2366 |
$tripCategories = $relationships['trip_category'] ?? []; |
| 2367 |
$priceTypes = $relationships['price_types'] ?? []; |
| 2368 |
$highlights = $relationships['highlights'] ?? []; |
| 2369 |
$landmarks = $relationships['landmarks'] ?? []; |
| 2370 |
$galleryImages = $relationships['gallery_images'] ?? []; |
| 2371 |
$faqs = $relationships['faqs'] ?? []; |
| 2372 |
$downloadableItems = $relationships['downloadable_items'] ?? []; |
| 2373 |
$itineraryDays = $relationships['itinerary_days'] ?? []; |
| 2374 |
$availabilityDates = $relationships['availability_dates'] ?? []; |
| 2375 |
$attributes = $relationships['attributes'] ?? []; |
| 2376 |
|
| 2377 |
// Remove relationship data from main data (these should not be in the main table) |
| 2378 |
unset( |
| 2379 |
$data['destinations'], |
| 2380 |
$data['activities'], |
| 2381 |
$data['trip_category'], |
| 2382 |
$data['highlights'], |
| 2383 |
$data['landmarks'], |
| 2384 |
$data['gallery_images'], |
| 2385 |
$data['faqs'], |
| 2386 |
$data['downloadable_items'], |
| 2387 |
$data['itinerary_days'], |
| 2388 |
$data['availability_dates'], |
| 2389 |
$data['attributes'] |
| 2390 |
); |
| 2391 |
|
| 2392 |
// Create main trip record |
| 2393 |
$tripId = $this->create($data); |
| 2394 |
|
| 2395 |
// Update relationships if provided |
| 2396 |
if (!empty($destinations)) { |
| 2397 |
$this->saveDestinations($tripId, $destinations); |
| 2398 |
} |
| 2399 |
|
| 2400 |
if (!empty($activities)) { |
| 2401 |
$this->saveActivities($tripId, $activities); |
| 2402 |
} |
| 2403 |
|
| 2404 |
if (!empty($tripCategories)) { |
| 2405 |
$this->saveTripCategories($tripId, $tripCategories); |
| 2406 |
} |
| 2407 |
|
| 2408 |
if (!empty($priceTypes)) { |
| 2409 |
$this->savePriceTypes($tripId, $priceTypes); |
| 2410 |
} |
| 2411 |
|
| 2412 |
if (!empty($highlights)) { |
| 2413 |
$this->saveHighlights($tripId, $highlights); |
| 2414 |
} |
| 2415 |
|
| 2416 |
if (!empty($landmarks)) { |
| 2417 |
$this->saveLandmarks($tripId, $landmarks); |
| 2418 |
} |
| 2419 |
|
| 2420 |
if (!empty($galleryImages)) { |
| 2421 |
$this->saveGalleryImages($tripId, $galleryImages); |
| 2422 |
} |
| 2423 |
|
| 2424 |
if (!empty($faqs)) { |
| 2425 |
$this->saveFaqs($tripId, $faqs); |
| 2426 |
} |
| 2427 |
|
| 2428 |
// Always replace downloads (clear if empty array) |
| 2429 |
$downloadRepo = new TripDownloadRepository(); |
| 2430 |
$downloadRepo->replaceForTrip($tripId, is_array($downloadableItems) ? $downloadableItems : []); |
| 2431 |
|
| 2432 |
// ITINERARY SHOULD NEVER BE PROCESSED DURING TRIP CREATION |
| 2433 |
// Itinerary should be created separately through dedicated itinerary endpoints |
| 2434 |
// This ensures complete separation of concerns and prevents data loss |
| 2435 |
|
| 2436 |
if (!empty($availabilityDates)) { |
| 2437 |
$this->saveAvailabilityDates($tripId, $availabilityDates); |
| 2438 |
} |
| 2439 |
|
| 2440 |
if (!empty($attributes)) { |
| 2441 |
$this->saveAttributes($tripId, $attributes); |
| 2442 |
} |
| 2443 |
|
| 2444 |
// Full bust after junction/related tables are written (create() already invalidated listings/stats). |
| 2445 |
Cache::invalidateAfterTripWrite('update', $tripId); |
| 2446 |
|
| 2447 |
do_action('yatra_trip_created_with_relations', $tripId, $relationships, $data); |
| 2448 |
|
| 2449 |
return $tripId; |
| 2450 |
} |
| 2451 |
|
| 2452 |
/** |
| 2453 |
* Update trip with relationships |
| 2454 |
*/ |
| 2455 |
public function updateWithRelations(int $id, array $data, array $relationships = []): bool |
| 2456 |
{ |
| 2457 |
// Extract relationship data (excluding itinerary - handled separately) |
| 2458 |
$destinations = $relationships['destinations'] ?? null; |
| 2459 |
$activities = $relationships['activities'] ?? null; |
| 2460 |
$tripCategories = $relationships['trip_category'] ?? null; |
| 2461 |
$priceTypes = $relationships['price_types'] ?? null; |
| 2462 |
$highlights = $relationships['highlights'] ?? null; |
| 2463 |
$landmarks = $relationships['landmarks'] ?? null; |
| 2464 |
$galleryImages = $relationships['gallery_images'] ?? null; |
| 2465 |
$faqs = $relationships['faqs'] ?? null; |
| 2466 |
$downloadableItems = $relationships['downloadable_items'] ?? null; |
| 2467 |
// ITINERARY IS HANDLED SEPARATELY - NEVER PROCESSED HERE |
| 2468 |
$availabilityDates = $relationships['availability_dates'] ?? null; |
| 2469 |
$attributes = $relationships['attributes'] ?? null; |
| 2470 |
|
| 2471 |
// Remove relationship data from main data (excluding itinerary - handled separately) |
| 2472 |
unset( |
| 2473 |
$data['destinations'], |
| 2474 |
$data['activities'], |
| 2475 |
$data['trip_category'], |
| 2476 |
$data['price_types'], |
| 2477 |
$data['highlights'], |
| 2478 |
$data['landmarks'], |
| 2479 |
$data['gallery_images'], |
| 2480 |
$data['faqs'], |
| 2481 |
// ITINERARY IS HANDLED SEPARATELY - DO NOT UNSET |
| 2482 |
$data['availability_dates'], |
| 2483 |
$data['attributes'] |
| 2484 |
); |
| 2485 |
|
| 2486 |
// Update main trip record |
| 2487 |
$result = $this->update($id, $data); |
| 2488 |
|
| 2489 |
// Update relationships if provided |
| 2490 |
if ($destinations !== null) { |
| 2491 |
$this->saveDestinations($id, $destinations); |
| 2492 |
} |
| 2493 |
|
| 2494 |
if ($activities !== null) { |
| 2495 |
$this->saveActivities($id, $activities); |
| 2496 |
} |
| 2497 |
|
| 2498 |
if ($tripCategories !== null) { |
| 2499 |
$this->saveTripCategories($id, $tripCategories); |
| 2500 |
} |
| 2501 |
|
| 2502 |
if ($priceTypes !== null) { |
| 2503 |
$this->savePriceTypes($id, $priceTypes); |
| 2504 |
} |
| 2505 |
|
| 2506 |
if ($highlights !== null) { |
| 2507 |
$this->saveHighlights($id, $highlights); |
| 2508 |
} |
| 2509 |
|
| 2510 |
if ($landmarks !== null) { |
| 2511 |
$this->saveLandmarks($id, $landmarks); |
| 2512 |
} |
| 2513 |
|
| 2514 |
if ($galleryImages !== null) { |
| 2515 |
$this->saveGalleryImages($id, $galleryImages); |
| 2516 |
} |
| 2517 |
|
| 2518 |
if ($faqs !== null) { |
| 2519 |
$this->saveFaqs($id, $faqs); |
| 2520 |
} |
| 2521 |
|
| 2522 |
if (is_array($downloadableItems)) { |
| 2523 |
$downloadRepo = new TripDownloadRepository(); |
| 2524 |
$downloadRepo->replaceForTrip($id, $downloadableItems); |
| 2525 |
} |
| 2526 |
|
| 2527 |
// ITINERARY SHOULD NEVER BE PROCESSED DURING TRIP UPDATES |
| 2528 |
// Itinerary updates should be handled separately through dedicated endpoints |
| 2529 |
// This ensures complete separation of concerns and prevents data loss |
| 2530 |
|
| 2531 |
if ($availabilityDates !== null) { |
| 2532 |
$this->saveAvailabilityDates($id, $availabilityDates); |
| 2533 |
} |
| 2534 |
|
| 2535 |
if ($attributes !== null) { |
| 2536 |
$this->saveAttributes($id, $attributes); |
| 2537 |
} |
| 2538 |
|
| 2539 |
if ($result) { |
| 2540 |
// Ensure caches reflect relationship writes (update() runs before saves; this runs after). |
| 2541 |
Cache::invalidateAfterTripWrite('update', $id); |
| 2542 |
} |
| 2543 |
|
| 2544 |
do_action('yatra_trip_updated_with_relations', $id, $relationships, $data); |
| 2545 |
|
| 2546 |
return $result; |
| 2547 |
} |
| 2548 |
|
| 2549 |
/** |
| 2550 |
* Soft delete a trip |
| 2551 |
*/ |
| 2552 |
public function softDelete(int $id, int $userId): bool |
| 2553 |
{ |
| 2554 |
return $this->update($id, [ |
| 2555 |
'deleted_at' => current_time('mysql'), |
| 2556 |
'deleted_by' => $userId, |
| 2557 |
]); |
| 2558 |
} |
| 2559 |
|
| 2560 |
/** |
| 2561 |
* Restore a soft-deleted trip |
| 2562 |
*/ |
| 2563 |
public function restore(int $id): bool |
| 2564 |
{ |
| 2565 |
return $this->update($id, [ |
| 2566 |
'deleted_at' => null, |
| 2567 |
'deleted_by' => null, |
| 2568 |
]); |
| 2569 |
} |
| 2570 |
|
| 2571 |
/** |
| 2572 |
* Get active trips (not deleted, with specified statuses) |
| 2573 |
* |
| 2574 |
* @param array $args Query arguments |
| 2575 |
* @param array $statuses Array of statuses to filter by. Defaults to ['published'] |
| 2576 |
* @return array |
| 2577 |
*/ |
| 2578 |
public function getActive(array $args = [], array $statuses = ['publish']): array |
| 2579 |
{ |
| 2580 |
$args['where']['deleted_at'] = null; |
| 2581 |
|
| 2582 |
// If status is already set in where clause, respect it |
| 2583 |
if (!isset($args['where']['status'])) { |
| 2584 |
$args['where']['status'] = $statuses; |
| 2585 |
} |
| 2586 |
|
| 2587 |
return $this->all($args); |
| 2588 |
} |
| 2589 |
|
| 2590 |
/** |
| 2591 |
* Find trips within a price range, considering all pricing types |
| 2592 |
* |
| 2593 |
* @param float $min_price Minimum price |
| 2594 |
* @param float $max_price Maximum price |
| 2595 |
* @param array $args Additional query arguments |
| 2596 |
* @return array Array of trips |
| 2597 |
*/ |
| 2598 |
public function findByPriceRange(float $min_price = 0, float $max_price = 0, array $args = []): array |
| 2599 |
{ |
| 2600 |
global $wpdb; |
| 2601 |
|
| 2602 |
// DEBUG: Log method entry |
| 2603 |
if (defined('WP_DEBUG') && WP_DEBUG) { |
| 2604 |
} |
| 2605 |
|
| 2606 |
// Base query to get all active trips |
| 2607 |
$args['where']['deleted_at'] = null; |
| 2608 |
if (!isset($args['where']['status'])) { |
| 2609 |
$args['where']['status'] = ['publish']; |
| 2610 |
} |
| 2611 |
|
| 2612 |
// DEBUG: Log query args |
| 2613 |
if (defined('WP_DEBUG') && WP_DEBUG) { |
| 2614 |
} |
| 2615 |
|
| 2616 |
// Get all active trips first |
| 2617 |
$all_trips = $this->all($args); |
| 2618 |
|
| 2619 |
if (empty($all_trips)) { |
| 2620 |
return []; |
| 2621 |
} |
| 2622 |
|
| 2623 |
// Get trip IDs |
| 2624 |
$trip_ids = array_map(function($trip) { |
| 2625 |
return $trip->id; |
| 2626 |
}, $all_trips); |
| 2627 |
|
| 2628 |
// Get trip prices and filter by range (price types table removed) |
| 2629 |
$filtered_trips = []; |
| 2630 |
|
| 2631 |
// DEBUG: Log price filtering process |
| 2632 |
if (defined('WP_DEBUG') && WP_DEBUG) { |
| 2633 |
} |
| 2634 |
|
| 2635 |
foreach ($all_trips as $trip) { |
| 2636 |
$trip_id = $trip->id; |
| 2637 |
$trip_min_price = PHP_FLOAT_MAX; |
| 2638 |
|
| 2639 |
// Check trip's own price first |
| 2640 |
if (!empty($trip->sale_price) && $trip->sale_price > 0) { |
| 2641 |
$trip_min_price = min($trip_min_price, (float)$trip->sale_price); |
| 2642 |
} |
| 2643 |
if (!empty($trip->discounted_price) && $trip->discounted_price > 0) { |
| 2644 |
$trip_min_price = min($trip_min_price, (float)$trip->discounted_price); |
| 2645 |
} |
| 2646 |
if (!empty($trip->original_price) && $trip->original_price > 0) { |
| 2647 |
$trip_min_price = min($trip_min_price, (float)$trip->original_price); |
| 2648 |
} |
| 2649 |
|
| 2650 |
// If no valid price found, skip |
| 2651 |
if ($trip_min_price === PHP_FLOAT_MAX) { |
| 2652 |
continue; |
| 2653 |
} |
| 2654 |
|
| 2655 |
// Apply price range filter |
| 2656 |
$passes_filter = ($min_price === 0 || $trip_min_price >= $min_price) && |
| 2657 |
($max_price === 0 || $trip_min_price <= $max_price); |
| 2658 |
|
| 2659 |
// DEBUG: Log individual trip filtering |
| 2660 |
if (defined('WP_DEBUG') && WP_DEBUG) { |
| 2661 |
} |
| 2662 |
|
| 2663 |
if ($passes_filter) { |
| 2664 |
$trip->min_price = $trip_min_price; |
| 2665 |
$filtered_trips[] = $trip; |
| 2666 |
} |
| 2667 |
} |
| 2668 |
|
| 2669 |
return $filtered_trips; |
| 2670 |
} |
| 2671 |
|
| 2672 |
/** |
| 2673 |
* Build where clause (override to handle soft deletes) |
| 2674 |
*/ |
| 2675 |
protected function buildWhereClause(array $args): string |
| 2676 |
{ |
| 2677 |
$where = parent::buildWhereClause($args); |
| 2678 |
|
| 2679 |
// Add soft delete filter if not explicitly requested |
| 2680 |
if (!isset($args['include_deleted']) || !$args['include_deleted']) { |
| 2681 |
if ($where) { |
| 2682 |
$where .= ' AND (deleted_at IS NULL OR deleted_at = \'0000-00-00 00:00:00\')'; |
| 2683 |
} else { |
| 2684 |
$where = 'WHERE (deleted_at IS NULL OR deleted_at = \'0000-00-00 00:00:00\')'; |
| 2685 |
} |
| 2686 |
} |
| 2687 |
|
| 2688 |
return $where; |
| 2689 |
} |
| 2690 |
|
| 2691 |
/** |
| 2692 |
* Count trips by status |
| 2693 |
*/ |
| 2694 |
public function countByStatus(string $status): int |
| 2695 |
{ |
| 2696 |
$table = esc_sql($this->table); |
| 2697 |
$count = $this->wpdb->get_var( |
| 2698 |
$this->wpdb->prepare( |
| 2699 |
"SELECT COUNT(*) FROM `{$table}` |
| 2700 |
WHERE status = %s |
| 2701 |
AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')", |
| 2702 |
$status |
| 2703 |
) |
| 2704 |
); |
| 2705 |
|
| 2706 |
return (int) $count; |
| 2707 |
} |
| 2708 |
|
| 2709 |
/** |
| 2710 |
* Search trips by keyword |
| 2711 |
*/ |
| 2712 |
public function search(string $keyword, array $args = []): array |
| 2713 |
{ |
| 2714 |
$table = esc_sql($this->table); |
| 2715 |
$where = $this->buildWhereClause($args); |
| 2716 |
$order = $this->buildOrderClause($args); |
| 2717 |
$limit = $this->buildLimitClause($args); |
| 2718 |
|
| 2719 |
$searchTerm = '%' . $this->wpdb->esc_like($keyword) . '%'; |
| 2720 |
|
| 2721 |
// Build search condition |
| 2722 |
$searchCondition = "(title LIKE %s OR description LIKE %s OR short_description LIKE %s)"; |
| 2723 |
|
| 2724 |
// If we have a WHERE clause, add AND; otherwise start with WHERE |
| 2725 |
if (!empty($where)) { |
| 2726 |
$whereClause = "{$where} AND {$searchCondition}"; |
| 2727 |
} else { |
| 2728 |
$whereClause = "WHERE {$searchCondition}"; |
| 2729 |
} |
| 2730 |
|
| 2731 |
$query = $this->wpdb->prepare( |
| 2732 |
"SELECT * FROM `{$table}` {$whereClause} {$order} {$limit}", |
| 2733 |
$searchTerm, |
| 2734 |
$searchTerm, |
| 2735 |
$searchTerm |
| 2736 |
); |
| 2737 |
|
| 2738 |
return $this->wpdb->get_results($query) ?: []; |
| 2739 |
} |
| 2740 |
|
| 2741 |
/** |
| 2742 |
* Decode included/excluded items JSON column stored on itinerary entries |
| 2743 |
*/ |
| 2744 |
private function decodeAmenityItems($value): array |
| 2745 |
{ |
| 2746 |
if (empty($value)) { |
| 2747 |
return []; |
| 2748 |
} |
| 2749 |
|
| 2750 |
if (is_array($value)) { |
| 2751 |
return $value; |
| 2752 |
} |
| 2753 |
|
| 2754 |
if (is_string($value)) { |
| 2755 |
$decoded = json_decode($value, true); |
| 2756 |
return is_array($decoded) ? $decoded : []; |
| 2757 |
} |
| 2758 |
|
| 2759 |
return []; |
| 2760 |
} |
| 2761 |
|
| 2762 |
/** |
| 2763 |
* Human-readable label for one included_items JSON element (title, name, label, or plain string). |
| 2764 |
*/ |
| 2765 |
private function extractIncludedItemLabel($item): string |
| 2766 |
{ |
| 2767 |
if (is_string($item)) { |
| 2768 |
$t = sanitize_text_field($item); |
| 2769 |
|
| 2770 |
return $t; |
| 2771 |
} |
| 2772 |
if (is_object($item)) { |
| 2773 |
$item = (array) $item; |
| 2774 |
} |
| 2775 |
if (!is_array($item)) { |
| 2776 |
return ''; |
| 2777 |
} |
| 2778 |
foreach (['title', 'name', 'label', 'text'] as $k) { |
| 2779 |
if (!empty($item[$k]) && is_scalar($item[$k])) { |
| 2780 |
$t = sanitize_text_field((string) $item[$k]); |
| 2781 |
|
| 2782 |
return $t; |
| 2783 |
} |
| 2784 |
} |
| 2785 |
|
| 2786 |
return ''; |
| 2787 |
} |
| 2788 |
|
| 2789 |
/** |
| 2790 |
* Get trip title by ID |
| 2791 |
*/ |
| 2792 |
public function getTripTitle(int $tripId): string |
| 2793 |
{ |
| 2794 |
global $wpdb; |
| 2795 |
$trips_table = $this->getTableName(); |
| 2796 |
|
| 2797 |
return (string) $wpdb->get_var($wpdb->prepare( |
| 2798 |
"SELECT title FROM {$trips_table} WHERE id = %d", |
| 2799 |
$tripId |
| 2800 |
)) ?: ''; |
| 2801 |
} |
| 2802 |
|
| 2803 |
/** |
| 2804 |
* Count all trips |
| 2805 |
*/ |
| 2806 |
public function countAllTrips(): int |
| 2807 |
{ |
| 2808 |
global $wpdb; |
| 2809 |
$trips_table = $this->getTableName(); |
| 2810 |
|
| 2811 |
return (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$trips_table}`"); |
| 2812 |
} |
| 2813 |
|
| 2814 |
/** |
| 2815 |
* Get trip status counts |
| 2816 |
*/ |
| 2817 |
public function getTripStatusCounts(): array |
| 2818 |
{ |
| 2819 |
global $wpdb; |
| 2820 |
$trips_table = $this->getTableName(); |
| 2821 |
|
| 2822 |
return $wpdb->get_results("SELECT status, COUNT(*) as count FROM `{$trips_table}` GROUP BY status"); |
| 2823 |
} |
| 2824 |
|
| 2825 |
/** |
| 2826 |
* Get trip with destinations |
| 2827 |
*/ |
| 2828 |
public function getTripWithDestinations(int $tripId): ?\stdClass |
| 2829 |
{ |
| 2830 |
global $wpdb; |
| 2831 |
$trips_table = $this->getTableName(); |
| 2832 |
|
| 2833 |
// Use ClassificationsTable for destinations (type = 'destination') |
| 2834 |
$trip_destinations_table = TripClassificationsTable::getTableName(); |
| 2835 |
$destinations_table = ClassificationsTable::getTableName(); |
| 2836 |
|
| 2837 |
return $wpdb->get_row($wpdb->prepare( |
| 2838 |
"SELECT t.id, t.title, t.slug, t.status, t.pricing_type, t.original_price, t.sale_price, |
| 2839 |
td.destination_id, d.name as destination_name, d.slug as destination_slug |
| 2840 |
FROM {$trips_table} t |
| 2841 |
LEFT JOIN {$trip_destinations_table} td ON td.trip_id = t.id |
| 2842 |
LEFT JOIN {$destinations_table} d ON d.id = td.destination_id |
| 2843 |
WHERE t.id = %d", |
| 2844 |
$tripId |
| 2845 |
)); |
| 2846 |
} |
| 2847 |
|
| 2848 |
/** |
| 2849 |
* Get trip destinations |
| 2850 |
*/ |
| 2851 |
public function getTripDestinations(int $tripId): array |
| 2852 |
{ |
| 2853 |
global $wpdb; |
| 2854 |
|
| 2855 |
// Use new TripClassificationsTable for trip-destination relationships |
| 2856 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 2857 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 2858 |
|
| 2859 |
$results = $wpdb->get_results($wpdb->prepare( |
| 2860 |
"SELECT tc.trip_id, tc.classification_id, c.name, c.slug |
| 2861 |
FROM {$tripClassificationsTable} tc |
| 2862 |
LEFT JOIN {$classificationsTable} c ON c.id = tc.classification_id |
| 2863 |
WHERE tc.trip_id = %d AND tc.classification_type = %s", |
| 2864 |
$tripId, ClassificationTypes::DESTINATION |
| 2865 |
)); |
| 2866 |
|
| 2867 |
// Filter out destinations with missing classification data |
| 2868 |
return array_filter($results, function($destination) { |
| 2869 |
return !empty($destination->name) && !empty($destination->slug); |
| 2870 |
}); |
| 2871 |
} |
| 2872 |
|
| 2873 |
/** |
| 2874 |
* Get trip activities |
| 2875 |
*/ |
| 2876 |
public function getTripActivities(int $tripId): array |
| 2877 |
{ |
| 2878 |
global $wpdb; |
| 2879 |
|
| 2880 |
// Use new TripClassificationsTable for trip-activity relationships |
| 2881 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 2882 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 2883 |
|
| 2884 |
return $wpdb->get_results($wpdb->prepare( |
| 2885 |
"SELECT tc.trip_id, c.id, c.name, c.slug |
| 2886 |
FROM {$tripClassificationsTable} tc |
| 2887 |
INNER JOIN {$classificationsTable} c ON c.id = tc.classification_id |
| 2888 |
WHERE tc.trip_id = %d AND c.type = 'activity'", |
| 2889 |
$tripId |
| 2890 |
)); |
| 2891 |
} |
| 2892 |
|
| 2893 |
/** |
| 2894 |
* Get trip with availability |
| 2895 |
*/ |
| 2896 |
public function getTripWithAvailability(int $tripId): ?\stdClass |
| 2897 |
{ |
| 2898 |
global $wpdb; |
| 2899 |
$trips_table = $this->getTableName(); |
| 2900 |
|
| 2901 |
// Use TripAvailabilityDatesTable for availability data |
| 2902 |
$availability_table = \Yatra\Database\Tables\TripAvailabilityDatesTable::getTableName(); |
| 2903 |
|
| 2904 |
return $wpdb->get_row($wpdb->prepare( |
| 2905 |
"SELECT t.*, a.departure_date, a.seats_total, a.seats_reserved AS seats_booked, a.status as availability_status |
| 2906 |
FROM {$trips_table} t |
| 2907 |
LEFT JOIN {$availability_table} a ON a.trip_id = t.id |
| 2908 |
WHERE t.id = %d", |
| 2909 |
$tripId |
| 2910 |
)); |
| 2911 |
} |
| 2912 |
|
| 2913 |
/** |
| 2914 |
* Get price range statistics for published trips. |
| 2915 |
* |
| 2916 |
* IMPORTANT: the previous SQL-only implementation only considered |
| 2917 |
* the legacy `original_price` / `discounted_price` / `sale_price` |
| 2918 |
* columns. For trips using traveler-based pricing (per-category |
| 2919 |
* prices stored in the `price_types` JSON column), those legacy |
| 2920 |
* columns are often empty or stale — so the computed max was lower |
| 2921 |
* than the actually-displayed price, and the price-range slider |
| 2922 |
* cut off above the real maximum (eg. trip cost €3755 but slider |
| 2923 |
* stopped at €3731). Worse, the trip then couldn't be filtered |
| 2924 |
* into the results because the effective-price WHERE excluded it. |
| 2925 |
* |
| 2926 |
* Fix: walk the published trips once in PHP and let |
| 2927 |
* `TripPricingService` decide each trip's effective price using |
| 2928 |
* the same logic the listing/single-trip pages use to DISPLAY the |
| 2929 |
* price. We also track the per-trip MIN/MAX across categories so |
| 2930 |
* the slider bounds cover every traveler tier (not only the |
| 2931 |
* default / cheapest). |
| 2932 |
* |
| 2933 |
* @return object Object with min_price and max_price properties |
| 2934 |
*/ |
| 2935 |
public function getPriceRangeStats(): object |
| 2936 |
{ |
| 2937 |
$table = $this->getTableName(); |
| 2938 |
$rows = $this->wpdb->get_results( |
| 2939 |
"SELECT id, original_price, discounted_price, sale_price, price_types |
| 2940 |
FROM {$table} |
| 2941 |
WHERE status IN ('publish', 'published') |
| 2942 |
AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')" |
| 2943 |
) ?: []; |
| 2944 |
|
| 2945 |
$minPrice = null; |
| 2946 |
$maxPrice = null; |
| 2947 |
|
| 2948 |
foreach ($rows as $row) { |
| 2949 |
$tripPrices = $this->collectTripDisplayPrices($row); |
| 2950 |
foreach ($tripPrices as $price) { |
| 2951 |
if ($price <= 0) { |
| 2952 |
continue; |
| 2953 |
} |
| 2954 |
if ($minPrice === null || $price < $minPrice) { |
| 2955 |
$minPrice = $price; |
| 2956 |
} |
| 2957 |
if ($maxPrice === null || $price > $maxPrice) { |
| 2958 |
$maxPrice = $price; |
| 2959 |
} |
| 2960 |
} |
| 2961 |
} |
| 2962 |
|
| 2963 |
// Fold in per-date / per-rule / per-departure price overrides so the |
| 2964 |
// slider bounds span every price a customer can actually be charged, |
| 2965 |
// not just the trips-table base. See collectPriceOverridePoints(). |
| 2966 |
foreach ($this->collectPriceOverridePoints() as $price) { |
| 2967 |
if ($price <= 0) { |
| 2968 |
continue; |
| 2969 |
} |
| 2970 |
if ($minPrice === null || $price < $minPrice) { |
| 2971 |
$minPrice = $price; |
| 2972 |
} |
| 2973 |
if ($maxPrice === null || $price > $maxPrice) { |
| 2974 |
$maxPrice = $price; |
| 2975 |
} |
| 2976 |
} |
| 2977 |
|
| 2978 |
return (object) [ |
| 2979 |
'min_price' => $minPrice, |
| 2980 |
'max_price' => $maxPrice, |
| 2981 |
]; |
| 2982 |
} |
| 2983 |
|
| 2984 |
/** |
| 2985 |
* Collect every price a single trip might display to a user. |
| 2986 |
* |
| 2987 |
* Returns BOTH the legacy "regular" effective price AND every |
| 2988 |
* per-category effective price from `price_types`. Used by |
| 2989 |
* `getPriceRangeStats()` so the price-range slider's bounds cover |
| 2990 |
* the full range a customer could see — eg. for a traveler-based |
| 2991 |
* trip with Adult €3755 / Child €1500 / Infant €100, the array |
| 2992 |
* includes all three so the slider stretches from €100 to €3755. |
| 2993 |
* |
| 2994 |
* @param object $row Raw trip row (must include `price_types`, |
| 2995 |
* `original_price`, `discounted_price`, |
| 2996 |
* `sale_price`). |
| 2997 |
* @return float[] |
| 2998 |
*/ |
| 2999 |
protected function collectTripDisplayPrices(object $row): array |
| 3000 |
{ |
| 3001 |
$prices = []; |
| 3002 |
|
| 3003 |
// Legacy regular pricing. |
| 3004 |
$legacyPrice = \Yatra\Services\TripPricingService::resolveRegularCurrentPrice($row); |
| 3005 |
if ($legacyPrice > 0) { |
| 3006 |
$prices[] = $legacyPrice; |
| 3007 |
} |
| 3008 |
|
| 3009 |
// Per-category prices (traveler-based pricing). Decode whether |
| 3010 |
// `price_types` is stored as a JSON string (raw DB row) or an |
| 3011 |
// already-decoded array (in-memory trip object). |
| 3012 |
$priceTypes = $row->price_types ?? null; |
| 3013 |
if (is_string($priceTypes) && $priceTypes !== '') { |
| 3014 |
$decoded = json_decode($priceTypes, true); |
| 3015 |
if (is_array($decoded)) { |
| 3016 |
$priceTypes = $decoded; |
| 3017 |
} |
| 3018 |
} |
| 3019 |
if (is_array($priceTypes)) { |
| 3020 |
foreach ($priceTypes as $pt) { |
| 3021 |
$price = \Yatra\Services\TripPricingService::resolveCategoryEffectivePrice((array) $pt); |
| 3022 |
if ($price > 0) { |
| 3023 |
$prices[] = $price; |
| 3024 |
} |
| 3025 |
} |
| 3026 |
} |
| 3027 |
|
| 3028 |
return $prices; |
| 3029 |
} |
| 3030 |
|
| 3031 |
/** |
| 3032 |
* Effective price points that OVERRIDE a trip's base price for specific |
| 3033 |
* dates, recurring rules, or individual departures. |
| 3034 |
* |
| 3035 |
* The price a customer actually sees, filters on, and pays is not always |
| 3036 |
* the trips-table base price: it can be overridden per |
| 3037 |
* - specific availability date (yatra_trip_availability_dates) |
| 3038 |
* - recurring availability rule (yatra_trip_availability_rules) |
| 3039 |
* - individual departure (yatra_trip_departures) |
| 3040 |
* each carrying its own original/discounted price and per-category |
| 3041 |
* (traveler-based) pricing JSON. The price-range slider bounds must span |
| 3042 |
* these too — otherwise a trip whose highest bookable price lives only on |
| 3043 |
* an override (e.g. a peak-season date priced €3,055 while the base is |
| 3044 |
* lower) is unreachable on the filter even though customers can book it. |
| 3045 |
* |
| 3046 |
* Effective-price semantics mirror the customer-facing display exactly by |
| 3047 |
* reusing collectTripDisplayPrices(): the discounted/sale price when set, |
| 3048 |
* otherwise the original — the same amount shown and charged. Non-bookable |
| 3049 |
* rows (blocked / cancelled / past dates, inactive rules) are excluded so |
| 3050 |
* they can't push the slider max beyond any price a customer can reach. |
| 3051 |
* |
| 3052 |
* @return array<int,float> Effective override prices (each > 0). |
| 3053 |
*/ |
| 3054 |
protected function collectPriceOverridePoints(): array |
| 3055 |
{ |
| 3056 |
$prices = []; |
| 3057 |
$today = current_time('Y-m-d'); |
| 3058 |
|
| 3059 |
// Only overrides that belong to a genuinely available (published, not |
| 3060 |
// soft-deleted) trip may influence the bounds — otherwise a stray |
| 3061 |
// override on a draft/trashed trip leaks a phantom max that no |
| 3062 |
// customer-visible trip carries. Mirrors the trips-table filter the |
| 3063 |
// base bound uses. |
| 3064 |
$tripsTable = $this->getTableName(); |
| 3065 |
$publishedJoin = "INNER JOIN {$tripsTable} t ON t.id = o.trip_id" |
| 3066 |
. " AND t.status IN ('publish', 'published')" |
| 3067 |
. " AND (t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')"; |
| 3068 |
|
| 3069 |
// 1) Specific availability dates — same column shape as a trip row, so |
| 3070 |
// collectTripDisplayPrices() resolves them identically. |
| 3071 |
$datesTable = \Yatra\Database\Tables\TripAvailabilityDatesTable::getTableName(); |
| 3072 |
$dateRows = $this->wpdb->get_results($this->wpdb->prepare( |
| 3073 |
"SELECT o.original_price, o.discounted_price, NULL AS sale_price, o.price_types |
| 3074 |
FROM {$datesTable} o |
| 3075 |
{$publishedJoin} |
| 3076 |
WHERE COALESCE(o.is_blocked, 0) = 0 |
| 3077 |
AND o.status NOT IN ('cancelled', 'blocked', 'closed') |
| 3078 |
AND (o.departure_date IS NULL OR o.departure_date >= %s)", |
| 3079 |
$today |
| 3080 |
)) ?: []; |
| 3081 |
foreach ($dateRows as $row) { |
| 3082 |
foreach ($this->collectTripDisplayPrices($row) as $p) { |
| 3083 |
$prices[] = $p; |
| 3084 |
} |
| 3085 |
} |
| 3086 |
|
| 3087 |
// 2) Recurring rules (active only — mirrors the `status = 'active'` |
| 3088 |
// filter RecurringAvailabilityRepository uses when generating |
| 3089 |
// availability). `traveler_pricing` holds the per-category JSON; a |
| 3090 |
// fixed `price_override` is an absolute per-booking price. A |
| 3091 |
// percentage override is a relative adjustment to the trip base — |
| 3092 |
// already spanned by the base bound — so it adds no new absolute max. |
| 3093 |
$rulesTable = \Yatra\Database\Tables\TripAvailabilityRulesTable::getTableName(); |
| 3094 |
$ruleRows = $this->wpdb->get_results( |
| 3095 |
"SELECT o.original_price, o.sale_price, NULL AS discounted_price, |
| 3096 |
o.price_override, o.price_type, o.traveler_pricing AS price_types |
| 3097 |
FROM {$rulesTable} o |
| 3098 |
{$publishedJoin} |
| 3099 |
WHERE o.status = 'active'" |
| 3100 |
) ?: []; |
| 3101 |
foreach ($ruleRows as $row) { |
| 3102 |
foreach ($this->collectTripDisplayPrices($row) as $p) { |
| 3103 |
$prices[] = $p; |
| 3104 |
} |
| 3105 |
if ($row->price_override !== null && (string) $row->price_type !== 'percentage') { |
| 3106 |
$po = (float) $row->price_override; |
| 3107 |
if ($po > 0) { |
| 3108 |
$prices[] = $po; |
| 3109 |
} |
| 3110 |
} |
| 3111 |
} |
| 3112 |
|
| 3113 |
// 3) Departures — `price_override` is an absolute price; |
| 3114 |
// `price_by_traveler_type` is the per-category JSON. |
| 3115 |
$depTable = \Yatra\Database\Tables\DeparturesTable::getTableName(); |
| 3116 |
$depRows = $this->wpdb->get_results($this->wpdb->prepare( |
| 3117 |
"SELECT o.price_override AS original_price, NULL AS discounted_price, |
| 3118 |
NULL AS sale_price, o.price_by_traveler_type AS price_types |
| 3119 |
FROM {$depTable} o |
| 3120 |
{$publishedJoin} |
| 3121 |
WHERE o.status NOT IN ('cancelled') |
| 3122 |
AND (COALESCE(o.start_date, o.`date`) IS NULL OR COALESCE(o.start_date, o.`date`) >= %s)", |
| 3123 |
$today |
| 3124 |
)) ?: []; |
| 3125 |
foreach ($depRows as $row) { |
| 3126 |
foreach ($this->collectTripDisplayPrices($row) as $p) { |
| 3127 |
$prices[] = $p; |
| 3128 |
} |
| 3129 |
} |
| 3130 |
|
| 3131 |
return $prices; |
| 3132 |
} |
| 3133 |
|
| 3134 |
/** |
| 3135 |
* Count trips by difficulty level |
| 3136 |
* |
| 3137 |
* @param int $difficultyLevelId Difficulty level ID |
| 3138 |
* @return int Number of trips with this difficulty level |
| 3139 |
*/ |
| 3140 |
public function countByDifficultyLevel(int $difficultyLevelId): int |
| 3141 |
{ |
| 3142 |
$table = $this->getTableName(); |
| 3143 |
|
| 3144 |
// Use ClassificationsTable for difficulty levels (type = 'difficulty') |
| 3145 |
$difficultyTable = ClassificationsTable::getTableName(); |
| 3146 |
|
| 3147 |
return (int) $this->wpdb->get_var($this->wpdb->prepare( |
| 3148 |
"SELECT COUNT(*) FROM {$table} t |
| 3149 |
LEFT JOIN {$difficultyTable} dl ON (t.difficulty_level = dl.id OR t.difficulty_level = dl.slug OR t.difficulty_level = dl.name) |
| 3150 |
WHERE dl.id = %d AND t.status IN ('publish','published')", |
| 3151 |
$difficultyLevelId |
| 3152 |
)); |
| 3153 |
} |
| 3154 |
|
| 3155 |
/** |
| 3156 |
* Check if reviews table exists |
| 3157 |
* |
| 3158 |
* @return bool True if reviews table exists |
| 3159 |
*/ |
| 3160 |
public function reviewsTableExists(): bool |
| 3161 |
{ |
| 3162 |
// Use ReviewsTable for reviews |
| 3163 |
$reviewsTable = ReviewsTable::getTableName(); |
| 3164 |
return (bool) $this->wpdb->get_var( |
| 3165 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES |
| 3166 |
WHERE TABLE_SCHEMA = DATABASE() |
| 3167 |
AND TABLE_NAME = '{$reviewsTable}'" |
| 3168 |
); |
| 3169 |
} |
| 3170 |
|
| 3171 |
/** |
| 3172 |
* Count trips by minimum rating |
| 3173 |
* |
| 3174 |
* @param int $minRating Minimum rating |
| 3175 |
* @return int Number of trips with this rating or above |
| 3176 |
*/ |
| 3177 |
public function countByMinRating(int $minRating): int |
| 3178 |
{ |
| 3179 |
$table = $this->getTableName(); |
| 3180 |
|
| 3181 |
// Use ReviewsTable for reviews |
| 3182 |
$reviewsTable = ReviewsTable::getTableName(); |
| 3183 |
|
| 3184 |
return (int) $this->wpdb->get_var($this->wpdb->prepare( |
| 3185 |
"SELECT COUNT(DISTINCT t.id) |
| 3186 |
FROM {$reviewsTable} r |
| 3187 |
INNER JOIN {$table} t ON r.trip_id = t.id |
| 3188 |
WHERE r.rating >= %d AND t.status IN ('publish','published')", |
| 3189 |
$minRating |
| 3190 |
)); |
| 3191 |
} |
| 3192 |
|
| 3193 |
/** |
| 3194 |
* Count trips by category |
| 3195 |
* |
| 3196 |
* @param int $categoryId Category ID |
| 3197 |
* @return int Number of trips in this category |
| 3198 |
*/ |
| 3199 |
public function countByCategory(int $categoryId): int |
| 3200 |
{ |
| 3201 |
$table = $this->getTableName(); |
| 3202 |
|
| 3203 |
// Use TripClassificationsTable for trip-category relationships |
| 3204 |
$categoryTable = TripClassificationsTable::getTableName(); |
| 3205 |
|
| 3206 |
$c = ClassificationsTable::getTableName(); |
| 3207 |
|
| 3208 |
return (int) $this->wpdb->get_var($this->wpdb->prepare( |
| 3209 |
"SELECT COUNT(DISTINCT t.id) FROM {$table} t |
| 3210 |
INNER JOIN {$categoryTable} ttc ON t.id = ttc.trip_id AND ttc.is_active = 1 |
| 3211 |
INNER JOIN {$c} cls ON cls.id = ttc.classification_id AND cls.type = %s |
| 3212 |
WHERE ttc.classification_id = %d |
| 3213 |
AND t.status IN ('publish', 'published') |
| 3214 |
AND (t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')", |
| 3215 |
ClassificationTypes::CATEGORY, |
| 3216 |
$categoryId |
| 3217 |
)); |
| 3218 |
} |
| 3219 |
|
| 3220 |
/** |
| 3221 |
* Count trips by destination |
| 3222 |
* |
| 3223 |
* @param int $destinationId Destination ID |
| 3224 |
* @return int Number of trips to this destination |
| 3225 |
*/ |
| 3226 |
public function countByDestination(int $destinationId): int |
| 3227 |
{ |
| 3228 |
$table = $this->getTableName(); |
| 3229 |
$tc = TripClassificationsTable::getTableName(); |
| 3230 |
$c = ClassificationsTable::getTableName(); |
| 3231 |
|
| 3232 |
return (int) $this->wpdb->get_var($this->wpdb->prepare( |
| 3233 |
"SELECT COUNT(DISTINCT t.id) FROM {$table} t |
| 3234 |
INNER JOIN {$tc} ttc ON t.id = ttc.trip_id AND ttc.is_active = 1 |
| 3235 |
INNER JOIN {$c} cls ON cls.id = ttc.classification_id AND cls.type = %s |
| 3236 |
WHERE ttc.classification_id = %d |
| 3237 |
AND t.status IN ('publish', 'published') |
| 3238 |
AND (t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')", |
| 3239 |
ClassificationTypes::DESTINATION, |
| 3240 |
$destinationId |
| 3241 |
)); |
| 3242 |
} |
| 3243 |
|
| 3244 |
/** |
| 3245 |
* Count trips by activity |
| 3246 |
* |
| 3247 |
* @param int $activityId Activity ID |
| 3248 |
* @return int Number of trips with this activity |
| 3249 |
*/ |
| 3250 |
public function countByActivity(int $activityId): int |
| 3251 |
{ |
| 3252 |
$table = $this->getTableName(); |
| 3253 |
$tc = TripClassificationsTable::getTableName(); |
| 3254 |
$c = ClassificationsTable::getTableName(); |
| 3255 |
|
| 3256 |
return (int) $this->wpdb->get_var($this->wpdb->prepare( |
| 3257 |
"SELECT COUNT(DISTINCT t.id) FROM {$table} t |
| 3258 |
INNER JOIN {$tc} ttc ON t.id = ttc.trip_id AND ttc.is_active = 1 |
| 3259 |
INNER JOIN {$c} cls ON cls.id = ttc.classification_id AND cls.type = %s |
| 3260 |
WHERE ttc.classification_id = %d |
| 3261 |
AND t.status IN ('publish', 'published') |
| 3262 |
AND (t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')", |
| 3263 |
ClassificationTypes::ACTIVITY, |
| 3264 |
$activityId |
| 3265 |
)); |
| 3266 |
} |
| 3267 |
|
| 3268 |
/** |
| 3269 |
* Get popular trips for cache warming |
| 3270 |
* |
| 3271 |
* @param int $limit Number of trips to return |
| 3272 |
* @return array Array of popular trip IDs |
| 3273 |
*/ |
| 3274 |
public function getPopularTrips(int $limit = 20): array |
| 3275 |
{ |
| 3276 |
global $wpdb; |
| 3277 |
$tripsTable = $this->getTableName(); |
| 3278 |
|
| 3279 |
// Using hardcoded table name since there's no dedicated repository for this table |
| 3280 |
$bookingsTable = BookingsTable::getTableName(); |
| 3281 |
|
| 3282 |
return $wpdb->get_results(" |
| 3283 |
SELECT t.id |
| 3284 |
FROM {$tripsTable} t |
| 3285 |
LEFT JOIN {$bookingsTable} b ON b.trip_id = t.id |
| 3286 |
WHERE t.status = 'publish' |
| 3287 |
GROUP BY t.id |
| 3288 |
ORDER BY COUNT(b.id) DESC |
| 3289 |
LIMIT {$limit} |
| 3290 |
") ?: []; |
| 3291 |
} |
| 3292 |
|
| 3293 |
/** |
| 3294 |
* Get price statistics for filter sidebar |
| 3295 |
*/ |
| 3296 |
public function getPriceStats(): ?object |
| 3297 |
{ |
| 3298 |
$table = $this->getTableName(); |
| 3299 |
|
| 3300 |
// Bounds must cover EVERY price a customer can actually filter on. |
| 3301 |
// The search filter matches a trip if its legacy effective price OR |
| 3302 |
// ANY per-category price in `price_types` falls in range (see the |
| 3303 |
// price clause in the listing query), so the slider's min/max have to |
| 3304 |
// span the same set — otherwise a traveler-based trip whose highest |
| 3305 |
// tier (eg. Adult €3,055) lives only in `price_types` becomes |
| 3306 |
// unreachable when the column-only max stops short (eg. €3,045). |
| 3307 |
// |
| 3308 |
// Reuse `collectTripDisplayPrices()` — the single source of truth also |
| 3309 |
// used by `getPriceRangeStats()` and the listing/single-trip DISPLAY — |
| 3310 |
// so bound == display == filter for both regular and traveler-based |
| 3311 |
// pricing. |
| 3312 |
$rows = $this->wpdb->get_results( |
| 3313 |
"SELECT id, original_price, discounted_price, sale_price, price_types |
| 3314 |
FROM {$table} |
| 3315 |
WHERE status IN ('publish', 'published') |
| 3316 |
AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')" |
| 3317 |
) ?: []; |
| 3318 |
|
| 3319 |
$minPrice = null; |
| 3320 |
$maxPrice = null; |
| 3321 |
$sum = 0.0; |
| 3322 |
$count = 0; |
| 3323 |
|
| 3324 |
foreach ($rows as $row) { |
| 3325 |
foreach ($this->collectTripDisplayPrices($row) as $price) { |
| 3326 |
if ($price <= 0) { |
| 3327 |
continue; |
| 3328 |
} |
| 3329 |
if ($minPrice === null || $price < $minPrice) { |
| 3330 |
$minPrice = $price; |
| 3331 |
} |
| 3332 |
if ($maxPrice === null || $price > $maxPrice) { |
| 3333 |
$maxPrice = $price; |
| 3334 |
} |
| 3335 |
$sum += $price; |
| 3336 |
$count++; |
| 3337 |
} |
| 3338 |
} |
| 3339 |
|
| 3340 |
// Fold in per-date / per-rule / per-departure price overrides so the |
| 3341 |
// slider spans every price a customer can actually be charged (bounds |
| 3342 |
// only — the average stays a base-trip figure). See |
| 3343 |
// collectPriceOverridePoints(). |
| 3344 |
foreach ($this->collectPriceOverridePoints() as $price) { |
| 3345 |
if ($price <= 0) { |
| 3346 |
continue; |
| 3347 |
} |
| 3348 |
if ($minPrice === null || $price < $minPrice) { |
| 3349 |
$minPrice = $price; |
| 3350 |
} |
| 3351 |
if ($maxPrice === null || $price > $maxPrice) { |
| 3352 |
$maxPrice = $price; |
| 3353 |
} |
| 3354 |
} |
| 3355 |
|
| 3356 |
if ($minPrice === null || $maxPrice === null) { |
| 3357 |
return null; |
| 3358 |
} |
| 3359 |
|
| 3360 |
// Widen to whole units so the template's `(int)` cast on the bounds |
| 3361 |
// can never truncate a fractional boundary out of range (eg. a |
| 3362 |
// €3,055.50 max would otherwise int-cast to 3055 and exclude it). |
| 3363 |
return (object) [ |
| 3364 |
'min_price' => (float) floor($minPrice), |
| 3365 |
'max_price' => (float) ceil($maxPrice), |
| 3366 |
'avg_price' => $count > 0 ? (float) ($sum / $count) : 0.0, |
| 3367 |
]; |
| 3368 |
} |
| 3369 |
|
| 3370 |
/** |
| 3371 |
* Distinct accommodation_type values on published trips with counts. |
| 3372 |
* |
| 3373 |
* @return list<object{name: string, trip_count: int}> |
| 3374 |
*/ |
| 3375 |
public function getAccommodationTypes(): array |
| 3376 |
{ |
| 3377 |
if (!$this->tripTableHasColumn('accommodation_type')) { |
| 3378 |
return []; |
| 3379 |
} |
| 3380 |
$table = $this->getTableName(); |
| 3381 |
$rows = $this->wpdb->get_results( |
| 3382 |
"SELECT TRIM(accommodation_type) AS name, COUNT(*) AS trip_count |
| 3383 |
FROM {$table} |
| 3384 |
WHERE status IN ('publish', 'published') |
| 3385 |
AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00') |
| 3386 |
AND accommodation_type IS NOT NULL AND TRIM(accommodation_type) <> '' |
| 3387 |
GROUP BY TRIM(accommodation_type) |
| 3388 |
ORDER BY trip_count DESC, name ASC" |
| 3389 |
) ?: []; |
| 3390 |
|
| 3391 |
$out = []; |
| 3392 |
foreach ($rows as $r) { |
| 3393 |
$out[] = (object) [ |
| 3394 |
'name' => (string) $r->name, |
| 3395 |
'trip_count' => (int) $r->trip_count, |
| 3396 |
]; |
| 3397 |
} |
| 3398 |
|
| 3399 |
return $out; |
| 3400 |
} |
| 3401 |
|
| 3402 |
/** |
| 3403 |
* Included item titles from trip.included_items JSON, aggregated by trip count. |
| 3404 |
* |
| 3405 |
* @return list<object{service_name: string, trip_count: int}> |
| 3406 |
*/ |
| 3407 |
public function getIncludedServices(): array |
| 3408 |
{ |
| 3409 |
if (!$this->tripTableHasColumn('included_items')) { |
| 3410 |
return []; |
| 3411 |
} |
| 3412 |
$table = $this->getTableName(); |
| 3413 |
$jsons = $this->wpdb->get_col( |
| 3414 |
"SELECT included_items FROM {$table} |
| 3415 |
WHERE status IN ('publish', 'published') |
| 3416 |
AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00') |
| 3417 |
AND included_items IS NOT NULL |
| 3418 |
AND included_items <> '' |
| 3419 |
AND included_items <> '[]'" |
| 3420 |
) ?: []; |
| 3421 |
|
| 3422 |
$counts = []; |
| 3423 |
foreach ($jsons as $json) { |
| 3424 |
$decoded = json_decode((string) $json, true); |
| 3425 |
if (!is_array($decoded)) { |
| 3426 |
continue; |
| 3427 |
} |
| 3428 |
$list = $decoded; |
| 3429 |
if (isset($decoded['items']) && is_array($decoded['items'])) { |
| 3430 |
$list = $decoded['items']; |
| 3431 |
} |
| 3432 |
foreach ($list as $item) { |
| 3433 |
$title = $this->extractIncludedItemLabel($item); |
| 3434 |
if ($title === '') { |
| 3435 |
continue; |
| 3436 |
} |
| 3437 |
$counts[$title] = ($counts[$title] ?? 0) + 1; |
| 3438 |
} |
| 3439 |
} |
| 3440 |
arsort($counts, SORT_NUMERIC); |
| 3441 |
$out = []; |
| 3442 |
foreach ($counts as $name => $c) { |
| 3443 |
$out[] = (object) ['service_name' => $name, 'trip_count' => (int) $c]; |
| 3444 |
} |
| 3445 |
|
| 3446 |
return $out; |
| 3447 |
} |
| 3448 |
|
| 3449 |
/** |
| 3450 |
* Min/max duration_days among published trips (for search UI). Falls back to 1–30 when empty. |
| 3451 |
* |
| 3452 |
* @return array{min: int, max: int} |
| 3453 |
*/ |
| 3454 |
public function getDurationDaysBounds(): array |
| 3455 |
{ |
| 3456 |
if (!$this->tripTableHasColumn('duration_days')) { |
| 3457 |
return ['min' => 1, 'max' => 30]; |
| 3458 |
} |
| 3459 |
$table = $this->getTableName(); |
| 3460 |
$row = $this->wpdb->get_row( |
| 3461 |
"SELECT |
| 3462 |
MIN(NULLIF(CAST(duration_days AS UNSIGNED), 0)) AS min_days, |
| 3463 |
MAX(CAST(duration_days AS UNSIGNED)) AS max_days |
| 3464 |
FROM {$table} |
| 3465 |
WHERE status IN ('publish', 'published') |
| 3466 |
AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')" |
| 3467 |
); |
| 3468 |
$min = (int) ($row->min_days ?? 1); |
| 3469 |
$max = (int) ($row->max_days ?? 1); |
| 3470 |
if ($min < 1) { |
| 3471 |
$min = 1; |
| 3472 |
} |
| 3473 |
if ($max < $min) { |
| 3474 |
$max = $min; |
| 3475 |
} |
| 3476 |
// Sensible upper bound for dual slider UX |
| 3477 |
if ($max > 365) { |
| 3478 |
$max = 365; |
| 3479 |
} |
| 3480 |
|
| 3481 |
return ['min' => $min, 'max' => $max]; |
| 3482 |
} |
| 3483 |
|
| 3484 |
/** |
| 3485 |
* Get duration options (placeholder) |
| 3486 |
* |
| 3487 |
* @return array |
| 3488 |
*/ |
| 3489 |
public function getDurationOptions(): array |
| 3490 |
{ |
| 3491 |
return []; |
| 3492 |
} |
| 3493 |
|
| 3494 |
/** |
| 3495 |
* Get group size options (placeholder) |
| 3496 |
* |
| 3497 |
* @return array |
| 3498 |
*/ |
| 3499 |
public function getGroupSizeOptions(): array |
| 3500 |
{ |
| 3501 |
return []; |
| 3502 |
} |
| 3503 |
|
| 3504 |
/** |
| 3505 |
* Get physical grades (placeholder) |
| 3506 |
* |
| 3507 |
* @return array |
| 3508 |
*/ |
| 3509 |
public function getPhysicalGrades(): array |
| 3510 |
{ |
| 3511 |
return []; |
| 3512 |
} |
| 3513 |
|
| 3514 |
/** |
| 3515 |
* Get trip types |
| 3516 |
*/ |
| 3517 |
public function getTripTypes(): array |
| 3518 |
{ |
| 3519 |
return [ |
| 3520 |
(object) ['value' => 'single_day', 'label' => __('Single day', 'yatra')], |
| 3521 |
(object) ['value' => 'multi_day', 'label' => __('Multi-day', 'yatra')], |
| 3522 |
(object) ['value' => 'flexible', 'label' => __('Flexible', 'yatra')], |
| 3523 |
]; |
| 3524 |
} |
| 3525 |
|
| 3526 |
/** |
| 3527 |
* Count trips by trip type |
| 3528 |
*/ |
| 3529 |
public function countByTripType(string $tripType): int |
| 3530 |
{ |
| 3531 |
global $wpdb; |
| 3532 |
$table = $this->getTableName(); |
| 3533 |
|
| 3534 |
return (int) $wpdb->get_var($wpdb->prepare( |
| 3535 |
"SELECT COUNT(*) FROM {$table} |
| 3536 |
WHERE trip_type = %s AND status = 'publish'", |
| 3537 |
$tripType |
| 3538 |
)); |
| 3539 |
} |
| 3540 |
|
| 3541 |
/** |
| 3542 |
* Count trips with discounts |
| 3543 |
*/ |
| 3544 |
public function countByDiscount(): int |
| 3545 |
{ |
| 3546 |
$table = $this->getTableName(); |
| 3547 |
return (int) $this->wpdb->get_var( |
| 3548 |
"SELECT COUNT(*) FROM {$table} |
| 3549 |
WHERE status = 'publish' AND (discounted_price IS NOT NULL OR sale_price IS NOT NULL)" |
| 3550 |
); |
| 3551 |
} |
| 3552 |
|
| 3553 |
/** |
| 3554 |
* Count trips with early bird offers |
| 3555 |
*/ |
| 3556 |
public function countByEarlyBird(): int |
| 3557 |
{ |
| 3558 |
$table = $this->getTableName(); |
| 3559 |
|
| 3560 |
// Check if column exists |
| 3561 |
$column_exists = (int) $this->wpdb->get_var( |
| 3562 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 3563 |
WHERE TABLE_SCHEMA = DATABASE() |
| 3564 |
AND TABLE_NAME = '{$table}' |
| 3565 |
AND COLUMN_NAME = 'early_bird_discount_enabled'" |
| 3566 |
); |
| 3567 |
|
| 3568 |
if ($column_exists) { |
| 3569 |
return (int) $this->wpdb->get_var( |
| 3570 |
"SELECT COUNT(*) FROM {$table} |
| 3571 |
WHERE status = 'publish' AND early_bird_discount_enabled = 1" |
| 3572 |
); |
| 3573 |
} |
| 3574 |
|
| 3575 |
return 0; |
| 3576 |
} |
| 3577 |
|
| 3578 |
/** |
| 3579 |
* Count trips with last minute deals |
| 3580 |
*/ |
| 3581 |
public function countByLastMinute(): int |
| 3582 |
{ |
| 3583 |
$table = $this->getTableName(); |
| 3584 |
|
| 3585 |
// Check if column exists |
| 3586 |
$column_exists = (int) $this->wpdb->get_var( |
| 3587 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 3588 |
WHERE TABLE_SCHEMA = DATABASE() |
| 3589 |
AND TABLE_NAME = '{$table}' |
| 3590 |
AND COLUMN_NAME = 'last_minute_discount_enabled'" |
| 3591 |
); |
| 3592 |
|
| 3593 |
if ($column_exists) { |
| 3594 |
return (int) $this->wpdb->get_var( |
| 3595 |
"SELECT COUNT(*) FROM {$table} |
| 3596 |
WHERE status = 'publish' AND last_minute_discount_enabled = 1" |
| 3597 |
); |
| 3598 |
} |
| 3599 |
|
| 3600 |
return 0; |
| 3601 |
} |
| 3602 |
|
| 3603 |
/** |
| 3604 |
* Count trips with instant booking |
| 3605 |
*/ |
| 3606 |
public function countByInstantBooking(): int |
| 3607 |
{ |
| 3608 |
$table = $this->getTableName(); |
| 3609 |
|
| 3610 |
// Check if column exists |
| 3611 |
$column_exists = (int) $this->wpdb->get_var( |
| 3612 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 3613 |
WHERE TABLE_SCHEMA = DATABASE() |
| 3614 |
AND TABLE_NAME = '{$table}' |
| 3615 |
AND COLUMN_NAME = 'instant_booking'" |
| 3616 |
); |
| 3617 |
|
| 3618 |
if ($column_exists) { |
| 3619 |
return (int) $this->wpdb->get_var( |
| 3620 |
"SELECT COUNT(*) FROM {$table} |
| 3621 |
WHERE status = 'publish' AND instant_booking = 1" |
| 3622 |
); |
| 3623 |
} |
| 3624 |
|
| 3625 |
return 0; |
| 3626 |
} |
| 3627 |
|
| 3628 |
/** |
| 3629 |
* Count trips with flexible dates |
| 3630 |
*/ |
| 3631 |
public function countByFlexibleDates(): int |
| 3632 |
{ |
| 3633 |
$table = $this->getTableName(); |
| 3634 |
|
| 3635 |
// Check if column exists |
| 3636 |
$column_exists = (int) $this->wpdb->get_var( |
| 3637 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 3638 |
WHERE TABLE_SCHEMA = DATABASE() |
| 3639 |
AND TABLE_NAME = '{$table}' |
| 3640 |
AND COLUMN_NAME = 'flexible_dates'" |
| 3641 |
); |
| 3642 |
|
| 3643 |
if ($column_exists) { |
| 3644 |
return (int) $this->wpdb->get_var( |
| 3645 |
"SELECT COUNT(*) FROM {$table} |
| 3646 |
WHERE status = 'publish' AND flexible_dates = 1" |
| 3647 |
); |
| 3648 |
} |
| 3649 |
|
| 3650 |
return 0; |
| 3651 |
} |
| 3652 |
|
| 3653 |
/** |
| 3654 |
* Count trips requiring deposit |
| 3655 |
*/ |
| 3656 |
public function countByDepositRequired(): int |
| 3657 |
{ |
| 3658 |
$table = $this->getTableName(); |
| 3659 |
|
| 3660 |
// Check if column exists |
| 3661 |
$column_exists = (int) $this->wpdb->get_var( |
| 3662 |
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS |
| 3663 |
WHERE TABLE_SCHEMA = DATABASE() |
| 3664 |
AND TABLE_NAME = '{$table}' |
| 3665 |
AND COLUMN_NAME = 'deposit_required'" |
| 3666 |
); |
| 3667 |
|
| 3668 |
if ($column_exists) { |
| 3669 |
return (int) $this->wpdb->get_var( |
| 3670 |
"SELECT COUNT(*) FROM {$table} |
| 3671 |
WHERE status = 'publish' AND deposit_required = 1" |
| 3672 |
); |
| 3673 |
} |
| 3674 |
|
| 3675 |
return 0; |
| 3676 |
} |
| 3677 |
|
| 3678 |
/** |
| 3679 |
* Count family friendly trips |
| 3680 |
*/ |
| 3681 |
public function countByFamilyFriendly(): int |
| 3682 |
{ |
| 3683 |
$table = $this->getTableName(); |
| 3684 |
return (int) $this->wpdb->get_var( |
| 3685 |
"SELECT COUNT(*) FROM {$table} |
| 3686 |
WHERE status = 'publish' AND (age_min IS NULL OR age_min <= 5)" |
| 3687 |
); |
| 3688 |
} |
| 3689 |
|
| 3690 |
/** |
| 3691 |
* Count kids friendly trips |
| 3692 |
*/ |
| 3693 |
public function countByKidsFriendly(): int |
| 3694 |
{ |
| 3695 |
$table = $this->getTableName(); |
| 3696 |
return (int) $this->wpdb->get_var( |
| 3697 |
"SELECT COUNT(*) FROM {$table} |
| 3698 |
WHERE status = 'publish' AND (age_min IS NULL OR age_min <= 12)" |
| 3699 |
); |
| 3700 |
} |
| 3701 |
|
| 3702 |
/** |
| 3703 |
* Count senior friendly trips |
| 3704 |
*/ |
| 3705 |
public function countBySeniorFriendly(): int |
| 3706 |
{ |
| 3707 |
$table = $this->getTableName(); |
| 3708 |
return (int) $this->wpdb->get_var( |
| 3709 |
"SELECT COUNT(*) FROM {$table} |
| 3710 |
WHERE status = 'publish' AND (age_max IS NULL OR age_max >= 65)" |
| 3711 |
); |
| 3712 |
} |
| 3713 |
|
| 3714 |
/** |
| 3715 |
* Count adults only trips |
| 3716 |
*/ |
| 3717 |
public function countByAdultsOnly(): int |
| 3718 |
{ |
| 3719 |
$table = $this->getTableName(); |
| 3720 |
return (int) $this->wpdb->get_var( |
| 3721 |
"SELECT COUNT(*) FROM {$table} |
| 3722 |
WHERE status = 'publish' AND age_min >= 18" |
| 3723 |
); |
| 3724 |
} |
| 3725 |
|
| 3726 |
/** |
| 3727 |
* Get all destinations for search dropdown |
| 3728 |
* Returns destinations that have associated trips |
| 3729 |
* |
| 3730 |
* @return array Array of destination objects |
| 3731 |
*/ |
| 3732 |
public function getAllDestinationsForSearch(): array |
| 3733 |
{ |
| 3734 |
global $wpdb; |
| 3735 |
|
| 3736 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 3737 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 3738 |
|
| 3739 |
// Get destinations - try multiple status values |
| 3740 |
$destinations = $wpdb->get_results(" |
| 3741 |
SELECT DISTINCT c.* FROM {$classificationsTable} c |
| 3742 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 3743 |
WHERE c.type = 'destination' AND c.status IN ('publish', 'active', 'draft') |
| 3744 |
ORDER BY c.name ASC |
| 3745 |
"); |
| 3746 |
|
| 3747 |
// If no destinations found, try without status filter |
| 3748 |
if (empty($destinations)) { |
| 3749 |
$destinations = $wpdb->get_results(" |
| 3750 |
SELECT DISTINCT c.* FROM {$classificationsTable} c |
| 3751 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 3752 |
WHERE c.type = 'destination' |
| 3753 |
ORDER BY c.name ASC |
| 3754 |
"); |
| 3755 |
} |
| 3756 |
|
| 3757 |
return $destinations ?: []; |
| 3758 |
} |
| 3759 |
|
| 3760 |
/** |
| 3761 |
* Get all activities for search dropdown |
| 3762 |
* Returns activities that have associated trips |
| 3763 |
* |
| 3764 |
* @return array Array of activity objects |
| 3765 |
*/ |
| 3766 |
public function getAllActivitiesForSearch(): array |
| 3767 |
{ |
| 3768 |
global $wpdb; |
| 3769 |
|
| 3770 |
$tripClassificationsTable = \Yatra\Database\Tables\TripClassificationsTable::getTableName(); |
| 3771 |
$classificationsTable = \Yatra\Database\Tables\ClassificationsTable::getTableName(); |
| 3772 |
|
| 3773 |
// Get activities - try multiple status values |
| 3774 |
$activities = $wpdb->get_results(" |
| 3775 |
SELECT DISTINCT c.* FROM {$classificationsTable} c |
| 3776 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 3777 |
WHERE c.type = 'activity' AND c.status IN ('publish', 'active', 'draft') |
| 3778 |
ORDER BY c.name ASC |
| 3779 |
"); |
| 3780 |
|
| 3781 |
// If no activities found, try without status filter |
| 3782 |
if (empty($activities)) { |
| 3783 |
$activities = $wpdb->get_results(" |
| 3784 |
SELECT DISTINCT c.* FROM {$classificationsTable} c |
| 3785 |
INNER JOIN {$tripClassificationsTable} tc ON c.id = tc.classification_id |
| 3786 |
WHERE c.type = 'activity' |
| 3787 |
ORDER BY c.name ASC |
| 3788 |
"); |
| 3789 |
} |
| 3790 |
|
| 3791 |
return $activities ?: []; |
| 3792 |
} |
| 3793 |
} |
| 3794 |
|
