| @@ -18,8 +18,9 @@ | ||
| 18 | 18 | use Yatra\Utils\Cache; |
| 19 | 19 | use Yatra\Utils\QueryCache; |
| 20 | 20 | use Yatra\Database\Tables\TripAvailabilityDatesTable; |
| 21 | 21 | use Yatra\Database\Tables\TripAvailabilityRulesTable; |
| 22 | +use Yatra\Database\Tables\DeparturesTable; | |
| 22 | 23 | use Yatra\Constants\ClassificationTypes; |
| 23 | 24 | |
| 24 | 25 | /** |
| 25 | 26 | * Trip Repository |
| @@ -270,8 +271,9 @@ | ||
| 270 | 271 | * Create trip row; {@see afterWrite} handles cache; fires action `yatra_trip_created` once per insert. |
| 271 | 272 | */ |
| 272 | 273 | public function create(array $data): int |
| 273 | 274 | { |
| 275 | + $data = $this->dropUnsupportedDurationHours($data); | |
| 274 | 276 | $id = parent::create($data); |
| 275 | 277 | do_action('yatra_trip_created', $id); |
| 276 | 278 | |
| 277 | 279 | return $id; |
| @@ -277,12 +279,32 @@ | ||
| 277 | 279 | return $id; |
| 278 | 280 | } |
| 279 | 281 | |
| 280 | 282 | /** |
| 283 | + * Defensive: if the `duration_hours` column is somehow missing (a failed or | |
| 284 | + * pending column-add migration on a restrictive host), drop it from the | |
| 285 | + * write set so a normal trip save still succeeds instead of erroring on an | |
| 286 | + * unknown column. The hour-based feature simply stays off until the column | |
| 287 | + * exists — it never breaks saving. | |
| 288 | + * | |
| 289 | + * @param array<string, mixed> $data | |
| 290 | + * @return array<string, mixed> | |
| 291 | + */ | |
| 292 | + protected function dropUnsupportedDurationHours(array $data): array | |
| 293 | + { | |
| 294 | + if (array_key_exists('duration_hours', $data) && !$this->tripTableHasColumn('duration_hours')) { | |
| 295 | + unset($data['duration_hours']); | |
| 296 | + } | |
| 297 | + | |
| 298 | + return $data; | |
| 299 | + } | |
| 300 | + | |
| 301 | + /** | |
| 281 | 302 | * Override update method to provide proper field formats |
| 282 | 303 | */ |
| 283 | 304 | public function update(int $id, array $data): bool |
| 284 | 305 | { |
| 306 | + $data = $this->dropUnsupportedDurationHours($data); | |
| 285 | 307 | $data = $this->sanitizeData($data); |
| 286 | 308 | $data['updated_at'] = current_time('mysql'); |
| 287 | 309 | |
| 288 | 310 | // Build format array based on field types |
| @@ -293,9 +315,9 @@ | ||
| 293 | 315 | } elseif (in_array($key, ['created_at', 'updated_at'], true)) { |
| 294 | 316 | $formats[] = '%s'; |
| 295 | 317 | } elseif (in_array($key, ['original_price', 'discounted_price', 'sale_price', 'deposit_amount', 'deposit_percentage', 'avg_rating', 'revenue_total', 'conversion_rate'], true)) { |
| 296 | 318 | $formats[] = '%f'; |
| 297 | - } elseif (in_array($key, ['transportation_included', 'is_featured', 'seasonal_auto_enable'], true)) { | |
| 319 | + } elseif (in_array($key, ['transportation_included', 'is_featured', 'seasonal_auto_enable', 'has_default_time_slots'], true)) { | |
| 298 | 320 | $formats[] = '%d'; // boolean as integer |
| 299 | 321 | } else { |
| 300 | 322 | $formats[] = '%s'; // default to string |
| 301 | 323 | } |
| @@ -400,8 +422,21 @@ | ||
| 400 | 422 | return self::$tripColumnExistsCache[$column]; |
| 401 | 423 | } |
| 402 | 424 | |
| 403 | 425 | /** |
| 426 | + * Public, cached column-existence check for the trips table. | |
| 427 | + * | |
| 428 | + * Lets raw SELECTs outside this repository (booking confirmation join, | |
| 429 | + * similar-trips query) add columns introduced by later upgrades — e.g. | |
| 430 | + * `duration_hours` — without failing on an install whose ALTER has not | |
| 431 | + * run yet (see InstallerService::maybeAddTripDurationHoursColumn()). | |
| 432 | + */ | |
| 433 | + public function hasTripColumn(string $column): bool | |
| 434 | + { | |
| 435 | + return $this->tripTableHasColumn($column); | |
| 436 | + } | |
| 437 | + | |
| 438 | + /** | |
| 404 | 439 | * SQL expression for trip "current" list price (matches TripPricingService::resolveRegularCurrentPrice). |
| 405 | 440 | */ |
| 406 | 441 | protected function sqlTripEffectiveListPrice(): string |
| 407 | 442 | { |
| @@ -481,8 +516,21 @@ | ||
| 481 | 516 | $params[] = $filters['trip_type']; |
| 482 | 517 | } |
| 483 | 518 | } |
| 484 | 519 | |
| 520 | + // Featured Priority (column on yatra_trips, indexed by idx_featured_priority). | |
| 521 | + // Admin form's Featured Priority dropdown is the single source of truth. | |
| 522 | + // Legacy `is_featured` shortcode/block flag is normalised upstream into featured_priority='featured'. | |
| 523 | + if ( | |
| 524 | + !empty($filters['featured_priority']) | |
| 525 | + && is_string($filters['featured_priority']) | |
| 526 | + && in_array($filters['featured_priority'], ['featured', 'new', 'limited'], true) | |
| 527 | + && $this->tripTableHasColumn('featured_priority') | |
| 528 | + ) { | |
| 529 | + $wheres[] = 't.featured_priority = %s'; | |
| 530 | + $params[] = $filters['featured_priority']; | |
| 531 | + } | |
| 532 | + | |
| 485 | 533 | // Destination: checkbox classification IDs (OR) or single slug |
| 486 | 534 | if (!empty($filters['destination_ids']) && is_array($filters['destination_ids'])) { |
| 487 | 535 | $ids = array_values(array_filter(array_map('intval', $filters['destination_ids']), static fn (int $id): bool => $id > 0)); |
| 488 | 536 | if ($ids !== []) { |
| @@ -492,13 +540,23 @@ | ||
| 492 | 540 | $params[] = ClassificationTypes::DESTINATION; |
| 493 | 541 | $params = array_merge($params, $ids); |
| 494 | 542 | } |
| 495 | 543 | } elseif (!empty($filters['destination'])) { |
| 496 | - $joins[] = "LEFT JOIN {$tripClassificationsTable} tcd ON tcd.trip_id = t.id"; | |
| 497 | - $joins[] = "LEFT JOIN {$classificationsTable} dest ON dest.id = tcd.classification_id"; | |
| 498 | - $wheres[] = 'dest.type = %s AND dest.slug = %s'; | |
| 499 | - $params[] = ClassificationTypes::DESTINATION; | |
| 500 | - $params[] = $filters['destination']; | |
| 544 | + if (is_array($filters['destination'])) { | |
| 545 | + $slugs = array_values(array_filter(array_map('sanitize_title', $filters['destination']))); | |
| 546 | + if ($slugs !== []) { | |
| 547 | + $placeholders = implode(',', array_fill(0, count($slugs), '%s')); | |
| 548 | + $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}))"; | |
| 549 | + $params[] = ClassificationTypes::DESTINATION; | |
| 550 | + $params = array_merge($params, $slugs); | |
| 551 | + } | |
| 552 | + } elseif (is_string($filters['destination']) && $filters['destination'] !== '') { | |
| 553 | + $joins[] = "LEFT JOIN {$tripClassificationsTable} tcd ON tcd.trip_id = t.id"; | |
| 554 | + $joins[] = "LEFT JOIN {$classificationsTable} dest ON dest.id = tcd.classification_id"; | |
| 555 | + $wheres[] = 'dest.type = %s AND dest.slug = %s'; | |
| 556 | + $params[] = ClassificationTypes::DESTINATION; | |
| 557 | + $params[] = $filters['destination']; | |
| 558 | + } | |
| 501 | 559 | } |
| 502 | 560 | |
| 503 | 561 | // Activity: IDs or slug |
| 504 | 562 | if (!empty($filters['activity_ids']) && is_array($filters['activity_ids'])) { |
| @@ -509,13 +567,23 @@ | ||
| 509 | 567 | $params[] = ClassificationTypes::ACTIVITY; |
| 510 | 568 | $params = array_merge($params, $ids); |
| 511 | 569 | } |
| 512 | 570 | } elseif (!empty($filters['activity'])) { |
| 513 | - $joins[] = "LEFT JOIN {$tripClassificationsTable} tca ON tca.trip_id = t.id"; | |
| 514 | - $joins[] = "LEFT JOIN {$classificationsTable} act ON act.id = tca.classification_id"; | |
| 515 | - $wheres[] = 'act.type = %s AND act.slug = %s'; | |
| 516 | - $params[] = ClassificationTypes::ACTIVITY; | |
| 517 | - $params[] = $filters['activity']; | |
| 571 | + if (is_array($filters['activity'])) { | |
| 572 | + $slugs = array_values(array_filter(array_map('sanitize_title', $filters['activity']))); | |
| 573 | + if ($slugs !== []) { | |
| 574 | + $placeholders = implode(',', array_fill(0, count($slugs), '%s')); | |
| 575 | + $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}))"; | |
| 576 | + $params[] = ClassificationTypes::ACTIVITY; | |
| 577 | + $params = array_merge($params, $slugs); | |
| 578 | + } | |
| 579 | + } elseif (is_string($filters['activity']) && $filters['activity'] !== '') { | |
| 580 | + $joins[] = "LEFT JOIN {$tripClassificationsTable} tca ON tca.trip_id = t.id"; | |
| 581 | + $joins[] = "LEFT JOIN {$classificationsTable} act ON act.id = tca.classification_id"; | |
| 582 | + $wheres[] = 'act.type = %s AND act.slug = %s'; | |
| 583 | + $params[] = ClassificationTypes::ACTIVITY; | |
| 584 | + $params[] = $filters['activity']; | |
| 585 | + } | |
| 518 | 586 | } |
| 519 | 587 | |
| 520 | 588 | // Category: IDs or slug |
| 521 | 589 | if (!empty($filters['category_ids']) && is_array($filters['category_ids'])) { |
| @@ -526,13 +594,23 @@ | ||
| 526 | 594 | $params[] = ClassificationTypes::CATEGORY; |
| 527 | 595 | $params = array_merge($params, $ids); |
| 528 | 596 | } |
| 529 | 597 | } elseif (!empty($filters['trip_category'])) { |
| 530 | - $joins[] = "LEFT JOIN {$tripClassificationsTable} tcc ON tcc.trip_id = t.id"; | |
| 531 | - $joins[] = "LEFT JOIN {$classificationsTable} cat ON cat.id = tcc.classification_id"; | |
| 532 | - $wheres[] = 'cat.type = %s AND cat.slug = %s'; | |
| 533 | - $params[] = ClassificationTypes::CATEGORY; | |
| 534 | - $params[] = $filters['trip_category']; | |
| 598 | + if (is_array($filters['trip_category'])) { | |
| 599 | + $slugs = array_values(array_filter(array_map('sanitize_title', $filters['trip_category']))); | |
| 600 | + if ($slugs !== []) { | |
| 601 | + $placeholders = implode(',', array_fill(0, count($slugs), '%s')); | |
| 602 | + $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}))"; | |
| 603 | + $params[] = ClassificationTypes::CATEGORY; | |
| 604 | + $params = array_merge($params, $slugs); | |
| 605 | + } | |
| 606 | + } elseif (is_string($filters['trip_category']) && $filters['trip_category'] !== '') { | |
| 607 | + $joins[] = "LEFT JOIN {$tripClassificationsTable} tcc ON tcc.trip_id = t.id"; | |
| 608 | + $joins[] = "LEFT JOIN {$classificationsTable} cat ON cat.id = tcc.classification_id"; | |
| 609 | + $wheres[] = 'cat.type = %s AND cat.slug = %s'; | |
| 610 | + $params[] = ClassificationTypes::CATEGORY; | |
| 611 | + $params[] = $filters['trip_category']; | |
| 612 | + } | |
| 535 | 613 | } |
| 536 | 614 | |
| 537 | 615 | // Special offers (OR within group) |
| 538 | 616 | if (!empty($filters['special_offers']) && is_array($filters['special_offers'])) { |
| @@ -613,20 +691,21 @@ | ||
| 613 | 691 | foreach (array_unique($filters['age_suitability']) as $age) { |
| 614 | 692 | if (!is_string($age)) { |
| 615 | 693 | continue; |
| 616 | 694 | } |
| 695 | + $ageLimits = self::ageSuitabilityThresholds(); | |
| 617 | 696 | switch ($age) { |
| 618 | 697 | case 'family-friendly': |
| 619 | - $ageParts[] = '(t.age_min IS NULL OR t.age_min <= 5)'; | |
| 698 | + $ageParts[] = '(t.age_min IS NULL OR t.age_min <= ' . (int) $ageLimits['family_max'] . ')'; | |
| 620 | 699 | break; |
| 621 | 700 | case 'kids-friendly': |
| 622 | - $ageParts[] = '(t.age_min IS NULL OR t.age_min <= 12)'; | |
| 701 | + $ageParts[] = '(t.age_min IS NULL OR t.age_min <= ' . (int) $ageLimits['kids_max'] . ')'; | |
| 623 | 702 | break; |
| 624 | 703 | case 'senior-friendly': |
| 625 | - $ageParts[] = '(t.age_max IS NULL OR t.age_max >= 65)'; | |
| 704 | + $ageParts[] = '(t.age_max IS NULL OR t.age_max >= ' . (int) $ageLimits['senior_min'] . ')'; | |
| 626 | 705 | break; |
| 627 | 706 | case 'adults-only': |
| 628 | - $ageParts[] = 't.age_min >= 18'; | |
| 707 | + $ageParts[] = 't.age_min >= ' . (int) $ageLimits['adults_min']; | |
| 629 | 708 | break; |
| 630 | 709 | } |
| 631 | 710 | } |
| 632 | 711 | if ($ageParts !== []) { |
| @@ -692,18 +771,71 @@ | ||
| 692 | 771 | } |
| 693 | 772 | } |
| 694 | 773 | } |
| 695 | 774 | |
| 696 | - // Price range filter (effective list price, not original-only) | |
| 775 | + // Price range filter. | |
| 776 | + // | |
| 777 | + // The previous version compared a single legacy "effective" | |
| 778 | + // price (discounted → sale → original column) against the | |
| 779 | + // range. That excluded traveler-based trips whose real | |
| 780 | + // displayed price lives in the `price_types` JSON — so eg. a | |
| 781 | + // trip costing €3755 (Adult category) never appeared in search | |
| 782 | + // when the user set max=€3755, because its legacy | |
| 783 | + // original_price was €0 / stale. | |
| 784 | + // | |
| 785 | + // New approach: match a trip if EITHER the legacy effective | |
| 786 | + // price OR ANY per-category price in the JSON falls in the | |
| 787 | + // requested range. Numeric values in the `price_types` JSON | |
| 788 | + // are extracted with a regex on the raw text — works in all | |
| 789 | + // MySQL 5.7+ builds without needing JSON_VALUE / JSON_TABLE | |
| 790 | + // (which are inconsistent across MariaDB / older MySQL). | |
| 697 | 791 | $effPrice = $this->sqlTripEffectiveListPrice(); |
| 698 | - if (!empty($filters['price_min']) && $filters['price_min'] > 0) { | |
| 699 | - $wheres[] = "{$effPrice} >= %f"; | |
| 700 | - $params[] = $filters['price_min']; | |
| 701 | - } | |
| 792 | + $priceMin = !empty($filters['price_min']) && $filters['price_min'] > 0 ? (float) $filters['price_min'] : null; | |
| 793 | + $priceMax = !empty($filters['price_max']) && $filters['price_max'] > 0 ? (float) $filters['price_max'] : null; | |
| 702 | 794 | |
| 703 | - if (!empty($filters['price_max']) && $filters['price_max'] > 0) { | |
| 704 | - $wheres[] = "{$effPrice} <= %f"; | |
| 705 | - $params[] = $filters['price_max']; | |
| 795 | + if ($priceMin !== null || $priceMax !== null) { | |
| 796 | + $legacyCond = []; | |
| 797 | + if ($priceMin !== null) { $legacyCond[] = "{$effPrice} >= %f"; $params[] = $priceMin; } | |
| 798 | + if ($priceMax !== null) { $legacyCond[] = "{$effPrice} <= %f"; $params[] = $priceMax; } | |
| 799 | + $legacyClause = implode(' AND ', $legacyCond); | |
| 800 | + | |
| 801 | + // For per-category pricing, find ANY price in `price_types` | |
| 802 | + // JSON that falls in the requested range. This subquery | |
| 803 | + // creates an ad-hoc number sequence (n=0..49 covers up to | |
| 804 | + // 50 categories — far more than any real trip uses) and | |
| 805 | + // uses JSON_EXTRACT to fetch each entry's effective price. | |
| 806 | + $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)"; | |
| 807 | + // Each category's effective price = first non-null of | |
| 808 | + // discounted_price → sale_price → original_price → price. | |
| 809 | + $categoryEff = "COALESCE(" | |
| 810 | + . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].discounted_price'))) AS DECIMAL(10,2)), 0)," | |
| 811 | + . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].sale_price'))) AS DECIMAL(10,2)), 0)," | |
| 812 | + . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].original_price'))) AS DECIMAL(10,2)), 0)," | |
| 813 | + . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].price'))) AS DECIMAL(10,2)), 0)" | |
| 814 | + . ")"; | |
| 815 | + $catCond = []; | |
| 816 | + if ($priceMin !== null) { $catCond[] = "{$categoryEff} >= %f"; $params[] = $priceMin; } | |
| 817 | + if ($priceMax !== null) { $catCond[] = "{$categoryEff} <= %f"; $params[] = $priceMax; } | |
| 818 | + $catClause = implode(' AND ', $catCond); | |
| 819 | + | |
| 820 | + // Guard the per-category branch against malformed JSON. | |
| 821 | + // JSON_EXTRACT throws on invalid JSON, and `price_types` | |
| 822 | + // can be NULL, '', '[]' or a stale string on legacy rows. | |
| 823 | + // JSON_VALID() + IFNULL() lets us bail cleanly for those. | |
| 824 | + $perCategoryExists = "(" | |
| 825 | + . "t.price_types IS NOT NULL" | |
| 826 | + . " AND t.price_types <> ''" | |
| 827 | + . " AND JSON_VALID(t.price_types) = 1" | |
| 828 | + . " AND EXISTS (" | |
| 829 | + . "SELECT 1 FROM {$numbers} _n" | |
| 830 | + . " WHERE JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, ']')) IS NOT NULL" | |
| 831 | + . " AND {$catClause}" | |
| 832 | + . ")" | |
| 833 | + . ")"; | |
| 834 | + | |
| 835 | + // Match if EITHER condition passes — a single legacy-priced | |
| 836 | + // trip OR a per-category trip with any matching tier. | |
| 837 | + $wheres[] = "(({$legacyClause}) OR {$perCategoryExists})"; | |
| 706 | 838 | } |
| 707 | 839 | |
| 708 | 840 | // Duration filter |
| 709 | 841 | if (!empty($filters['duration_min']) && $filters['duration_min'] > 0) { |
| @@ -714,9 +846,29 @@ | ||
| 714 | 846 | if (!empty($filters['duration_max']) && $filters['duration_max'] > 0) { |
| 715 | 847 | $wheres[] = "CAST(t.duration_days AS UNSIGNED) <= %d"; |
| 716 | 848 | $params[] = $filters['duration_max']; |
| 717 | 849 | } |
| 718 | - | |
| 850 | + | |
| 851 | + // Availability date filter ("available from this date onward"): keep only | |
| 852 | + // trips that have a departure starting ON OR AFTER the selected date, so | |
| 853 | + // a customer who picks a date sees every trip they could still travel on | |
| 854 | + // from then. A departure matches when its effective start date (the | |
| 855 | + // explicit start_date, else the single `date`) is >= the chosen day and | |
| 856 | + // it has not been cancelled — the date bound alone excludes past dates | |
| 857 | + // when the picker's min=today is respected, so no reliance on a possibly | |
| 858 | + // stale 'past' status. Clause + param are appended together so the WHERE | |
| 859 | + // ordering stays in sync with $params (see the count/main queries). | |
| 860 | + if (!empty($filters['available_date'])) { | |
| 861 | + $departures_table = DeparturesTable::getTableName(); | |
| 862 | + $wheres[] = "EXISTS ( | |
| 863 | + SELECT 1 FROM {$departures_table} yd | |
| 864 | + WHERE yd.trip_id = t.id | |
| 865 | + AND COALESCE(NULLIF(yd.start_date, '0000-00-00'), yd.date) >= %s | |
| 866 | + AND yd.status <> 'cancelled' | |
| 867 | + )"; | |
| 868 | + $params[] = $filters['available_date']; | |
| 869 | + } | |
| 870 | + | |
| 719 | 871 | // Rating filter |
| 720 | 872 | if (!empty($filters['rating_min']) && $filters['rating_min'] > 0) { |
| 721 | 873 | $having_clauses[] = "AVG(r.rating) >= %f"; |
| 722 | 874 | $rating_params[] = $filters['rating_min']; |
| @@ -823,8 +975,10 @@ | ||
| 823 | 975 | case 'price_high': |
| 824 | 976 | return "ORDER BY {$effPrice} DESC"; |
| 825 | 977 | case 'rating_high': |
| 826 | 978 | return "ORDER BY average_rating DESC"; |
| 979 | + case 'date_asc': | |
| 980 | + return "ORDER BY t.created_at ASC"; | |
| 827 | 981 | case 'duration_short': |
| 828 | 982 | return "ORDER BY CAST(t.duration_days AS UNSIGNED) ASC, CAST(t.duration_nights AS UNSIGNED) ASC"; |
| 829 | 983 | case 'duration_long': |
| 830 | 984 | return "ORDER BY CAST(t.duration_days AS UNSIGNED) DESC, CAST(t.duration_nights AS UNSIGNED) DESC"; |
| @@ -1666,8 +1820,28 @@ | ||
| 1666 | 1820 | */ |
| 1667 | 1821 | public function savePriceTypes(int $tripId, array $priceTypes): void |
| 1668 | 1822 | { |
| 1669 | 1823 | if (empty($priceTypes)) { |
| 1824 | + // Explicitly clear stored per-category pricing so that deleting all | |
| 1825 | + // rows (even the last one) persists. Previously this early-returned, | |
| 1826 | + // leaving the old price_types JSON in place — the deletion silently | |
| 1827 | + // reverted on reload. Only the JSON is always cleared; the derived | |
| 1828 | + // scalar columns (original/discounted/sale) are reset solely for | |
| 1829 | + // traveler-based trips, because for regular pricing those columns | |
| 1830 | + // hold the actual price and must not be wiped. | |
| 1831 | + $table = TripsTable::getTableName(); | |
| 1832 | + $pricingType = $this->wpdb->get_var( | |
| 1833 | + $this->wpdb->prepare("SELECT pricing_type FROM `{$table}` WHERE id = %d", $tripId) | |
| 1834 | + ); | |
| 1835 | + | |
| 1836 | + $data = ['price_types' => null]; | |
| 1837 | + if ($pricingType === 'traveler_based') { | |
| 1838 | + $data['original_price'] = null; | |
| 1839 | + $data['discounted_price'] = null; | |
| 1840 | + $data['sale_price'] = null; | |
| 1841 | + } | |
| 1842 | + | |
| 1843 | + $this->wpdb->update($table, $data, ['id' => $tripId]); | |
| 1670 | 1844 | return; |
| 1671 | 1845 | } |
| 1672 | 1846 | |
| 1673 | 1847 | // Ensure at most one default category is set (keep the first truthy one). |
| @@ -2045,45 +2219,165 @@ | ||
| 2045 | 2219 | )); |
| 2046 | 2220 | } |
| 2047 | 2221 | |
| 2048 | 2222 | /** |
| 2049 | - * Save availability dates for a trip | |
| 2223 | + * Save availability dates for a trip. | |
| 2224 | + * | |
| 2225 | + * Previously a flat DELETE-THEN-INSERT: every row for the trip was nuked | |
| 2226 | + * and the incoming list rewritten. That made the trip-edit save lossy — | |
| 2227 | + * when the Trip form only knew about a subset of fields on each date | |
| 2228 | + * (e.g. it was loaded once, then the operator changed the seats on a | |
| 2229 | + * single date through the Availability tab, then re-saved the Trip with | |
| 2230 | + * the form's stale date payload), every field absent from the incoming | |
| 2231 | + * row was reset to its hard-coded default. That's why operator edits | |
| 2232 | + * to `seats_total` silently reverted to 20 after another trip-save: | |
| 2233 | + * | |
| 2234 | + * 1. Operator opens Trip → form loads `availability_dates` with | |
| 2235 | + * `seats_total = 20`. | |
| 2236 | + * 2. Operator switches to Availability tab → edits Sept 5 to | |
| 2237 | + * `seats_total = 14` via the dedicated specific-date endpoint | |
| 2238 | + * (writes directly to the row, so the row is now 14). | |
| 2239 | + * 3. Operator returns to the Trip form, edits something unrelated | |
| 2240 | + * (title / description), clicks Save. The form re-posts the date | |
| 2241 | + * list it loaded in step 1 — `seats_total = 20`. | |
| 2242 | + * 4. saveAvailabilityDates DELETEs everything, INSERTs the | |
| 2243 | + * form-supplied list → Sept 5 is back to 20 (or the literal `20` | |
| 2244 | + * default if the form didn't include seats_total at all). | |
| 2245 | + * | |
| 2246 | + * Fix: snapshot the existing rows before the DELETE and, when the | |
| 2247 | + * incoming row omits an editable field, fall back to the snapshot's | |
| 2248 | + * value instead of the schema default. The literal `20` default is | |
| 2249 | + * removed in favour of the trip's `max_travelers` (or 1 as a last | |
| 2250 | + * resort) so new dates inserted by truly-new entries don't pretend the | |
| 2251 | + * trip seats 20 people unless the trip actually says so. | |
| 2252 | + * | |
| 2253 | + * Match key for the snapshot: `departure_date` + `departure_time` (both | |
| 2254 | + * normalised), which is the same identity the Availability UI uses. | |
| 2050 | 2255 | */ |
| 2051 | 2256 | public function saveAvailabilityDates(int $tripId, array $availabilityDates): void |
| 2052 | 2257 | { |
| 2053 | 2258 | global $wpdb; |
| 2054 | 2259 | $table = TripAvailabilityDatesTable::getTableName(); |
| 2055 | - | |
| 2260 | + | |
| 2261 | + // Snapshot existing rows by date + time so partial incoming payloads | |
| 2262 | + // can be merged with persisted values instead of clobbering them. | |
| 2263 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name from schema helper. | |
| 2264 | + $existingRows = $wpdb->get_results($wpdb->prepare( | |
| 2265 | + "SELECT * FROM {$table} WHERE trip_id = %d", | |
| 2266 | + $tripId | |
| 2267 | + )); | |
| 2268 | + $snapshot = []; | |
| 2269 | + if (is_array($existingRows)) { | |
| 2270 | + foreach ($existingRows as $row) { | |
| 2271 | + $key = self::availabilityRowKey( | |
| 2272 | + (string) ($row->departure_date ?? ''), | |
| 2273 | + (string) ($row->departure_time ?? '') | |
| 2274 | + ); | |
| 2275 | + if ($key !== '') { | |
| 2276 | + $snapshot[$key] = $row; | |
| 2277 | + } | |
| 2278 | + } | |
| 2279 | + } | |
| 2280 | + | |
| 2281 | + // Fallback capacity when both the incoming row and the snapshot | |
| 2282 | + // miss seats_total: read the trip's max_travelers once. The | |
| 2283 | + // legacy literal `20` was a phantom default that punished sites | |
| 2284 | + // whose actual trip capacity differs. | |
| 2285 | + $tripFallbackSeats = 0; | |
| 2286 | + $tripRow = $this->find($tripId); | |
| 2287 | + if (is_object($tripRow) && isset($tripRow->max_travelers)) { | |
| 2288 | + $tripFallbackSeats = (int) $tripRow->max_travelers; | |
| 2289 | + } | |
| 2290 | + if ($tripFallbackSeats <= 0) { | |
| 2291 | + $tripFallbackSeats = 1; | |
| 2292 | + } | |
| 2293 | + | |
| 2056 | 2294 | // Delete existing |
| 2057 | 2295 | $wpdb->delete($table, ['trip_id' => $tripId], ['%d']); |
| 2058 | - | |
| 2296 | + | |
| 2059 | 2297 | // Insert new |
| 2060 | 2298 | if (!empty($availabilityDates)) { |
| 2061 | 2299 | foreach ($availabilityDates as $date) { |
| 2062 | 2300 | if (is_array($date) && !empty($date['departure_date'])) { |
| 2063 | - $seatsTotal = isset($date['seats_total']) ? (int) $date['seats_total'] : 20; | |
| 2064 | - $seatsAvailable = isset($date['seats_available']) ? (int) $date['seats_available'] : $seatsTotal; | |
| 2065 | - | |
| 2301 | + $key = self::availabilityRowKey( | |
| 2302 | + (string) $date['departure_date'], | |
| 2303 | + (string) ($date['departure_time'] ?? '') | |
| 2304 | + ); | |
| 2305 | + $existing = $key !== '' && isset($snapshot[$key]) ? $snapshot[$key] : null; | |
| 2306 | + | |
| 2307 | + $seatsTotal = isset($date['seats_total']) | |
| 2308 | + ? (int) $date['seats_total'] | |
| 2309 | + : ($existing ? (int) ($existing->seats_total ?? $tripFallbackSeats) : $tripFallbackSeats); | |
| 2310 | + $seatsAvailable = isset($date['seats_available']) | |
| 2311 | + ? (int) $date['seats_available'] | |
| 2312 | + : ($existing ? (int) ($existing->seats_available ?? $seatsTotal) : $seatsTotal); | |
| 2313 | + | |
| 2314 | + $arrivalDate = isset($date['arrival_date']) | |
| 2315 | + ? sanitize_text_field($date['arrival_date']) | |
| 2316 | + : (isset($date['return_date']) | |
| 2317 | + ? sanitize_text_field($date['return_date']) | |
| 2318 | + : ($existing ? $existing->arrival_date : null)); | |
| 2319 | + $returnDate = isset($date['return_date']) | |
| 2320 | + ? sanitize_text_field($date['return_date']) | |
| 2321 | + : ($existing ? $existing->return_date : null); | |
| 2322 | + $arrivalTime = isset($date['arrival_time']) | |
| 2323 | + ? sanitize_text_field($date['arrival_time']) | |
| 2324 | + : ($existing ? $existing->arrival_time : null); | |
| 2325 | + | |
| 2326 | + $originalPrice = isset($date['original_price']) | |
| 2327 | + ? (float) $date['original_price'] | |
| 2328 | + : (isset($date['price_override']) | |
| 2329 | + ? (float) $date['price_override'] | |
| 2330 | + : ($existing && $existing->original_price !== null ? (float) $existing->original_price : null)); | |
| 2331 | + $discountedPrice = isset($date['discounted_price']) | |
| 2332 | + ? (float) $date['discounted_price'] | |
| 2333 | + : ($existing && $existing->discounted_price !== null ? (float) $existing->discounted_price : null); | |
| 2334 | + | |
| 2335 | + $fromLocation = isset($date['from_location']) ? sanitize_text_field($date['from_location']) : ($existing ? $existing->from_location : null); | |
| 2336 | + $toLocation = isset($date['to_location']) ? sanitize_text_field($date['to_location']) : ($existing ? $existing->to_location : null); | |
| 2337 | + $fromLat = isset($date['from_latitude']) && is_numeric($date['from_latitude']) | |
| 2338 | + ? (string) $date['from_latitude'] | |
| 2339 | + : ($existing ? $existing->from_latitude : null); | |
| 2340 | + $fromLng = isset($date['from_longitude']) && is_numeric($date['from_longitude']) | |
| 2341 | + ? (string) $date['from_longitude'] | |
| 2342 | + : ($existing ? $existing->from_longitude : null); | |
| 2343 | + $toLat = isset($date['to_latitude']) && is_numeric($date['to_latitude']) | |
| 2344 | + ? (string) $date['to_latitude'] | |
| 2345 | + : ($existing ? $existing->to_latitude : null); | |
| 2346 | + $toLng = isset($date['to_longitude']) && is_numeric($date['to_longitude']) | |
| 2347 | + ? (string) $date['to_longitude'] | |
| 2348 | + : ($existing ? $existing->to_longitude : null); | |
| 2349 | + | |
| 2350 | + if (isset($date['is_blackout'])) { | |
| 2351 | + $status = $date['is_blackout'] ? 'blocked' : 'available'; | |
| 2352 | + } elseif (isset($date['status'])) { | |
| 2353 | + $status = sanitize_text_field($date['status']); | |
| 2354 | + } elseif ($existing) { | |
| 2355 | + $status = (string) ($existing->status ?? 'available'); | |
| 2356 | + } else { | |
| 2357 | + $status = 'available'; | |
| 2358 | + } | |
| 2359 | + | |
| 2066 | 2360 | $insertData = [ |
| 2067 | 2361 | 'trip_id' => $tripId, |
| 2068 | 2362 | 'departure_date' => sanitize_text_field($date['departure_date']), |
| 2069 | - 'arrival_date' => isset($date['arrival_date']) ? sanitize_text_field($date['arrival_date']) : ($date['return_date'] ?? null), | |
| 2070 | - 'return_date' => isset($date['return_date']) ? sanitize_text_field($date['return_date']) : null, | |
| 2071 | - 'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : null, | |
| 2072 | - 'arrival_time' => isset($date['arrival_time']) ? sanitize_text_field($date['arrival_time']) : null, | |
| 2363 | + 'arrival_date' => $arrivalDate, | |
| 2364 | + 'return_date' => $returnDate, | |
| 2365 | + 'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : ($existing ? $existing->departure_time : null), | |
| 2366 | + 'arrival_time' => $arrivalTime, | |
| 2073 | 2367 | 'seats_total' => $seatsTotal, |
| 2074 | 2368 | 'seats_available' => $seatsAvailable, |
| 2075 | - 'original_price' => isset($date['original_price']) ? (float) $date['original_price'] : (isset($date['price_override']) ? (float) $date['price_override'] : null), | |
| 2076 | - 'discounted_price' => isset($date['discounted_price']) ? (float) $date['discounted_price'] : null, | |
| 2077 | - 'from_location' => isset($date['from_location']) ? sanitize_text_field($date['from_location']) : null, | |
| 2078 | - 'to_location' => isset($date['to_location']) ? sanitize_text_field($date['to_location']) : null, | |
| 2079 | - 'from_latitude' => isset($date['from_latitude']) && is_numeric($date['from_latitude']) ? (string) $date['from_latitude'] : null, | |
| 2080 | - 'from_longitude' => isset($date['from_longitude']) && is_numeric($date['from_longitude']) ? (string) $date['from_longitude'] : null, | |
| 2081 | - 'to_latitude' => isset($date['to_latitude']) && is_numeric($date['to_latitude']) ? (string) $date['to_latitude'] : null, | |
| 2082 | - 'to_longitude' => isset($date['to_longitude']) && is_numeric($date['to_longitude']) ? (string) $date['to_longitude'] : null, | |
| 2083 | - 'status' => isset($date['is_blackout']) && $date['is_blackout'] ? 'blocked' : (isset($date['status']) ? sanitize_text_field($date['status']) : 'available'), | |
| 2369 | + 'original_price' => $originalPrice, | |
| 2370 | + 'discounted_price' => $discountedPrice, | |
| 2371 | + 'from_location' => $fromLocation, | |
| 2372 | + 'to_location' => $toLocation, | |
| 2373 | + 'from_latitude' => $fromLat, | |
| 2374 | + 'from_longitude' => $fromLng, | |
| 2375 | + 'to_latitude' => $toLat, | |
| 2376 | + 'to_longitude' => $toLng, | |
| 2377 | + 'status' => $status, | |
| 2084 | 2378 | ]; |
| 2085 | - | |
| 2379 | + | |
| 2086 | 2380 | $wpdb->insert( |
| 2087 | 2381 | $table, |
| 2088 | 2382 | $insertData, |
| 2089 | 2383 | ['%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s'] |
| @@ -2093,8 +2387,23 @@ | ||
| 2093 | 2387 | } |
| 2094 | 2388 | } |
| 2095 | 2389 | |
| 2096 | 2390 | /** |
| 2391 | + * Normalise a (date, time) pair to a single string key used to match | |
| 2392 | + * incoming availability rows against the pre-delete snapshot. Time is | |
| 2393 | + * left as-is for an exact comparison; empty time matches the "no | |
| 2394 | + * specific time" row. | |
| 2395 | + */ | |
| 2396 | + private static function availabilityRowKey(string $date, string $time): string | |
| 2397 | + { | |
| 2398 | + $date = trim($date); | |
| 2399 | + if ($date === '') { | |
| 2400 | + return ''; | |
| 2401 | + } | |
| 2402 | + return $date . '|' . trim($time); | |
| 2403 | + } | |
| 2404 | + | |
| 2405 | + /** | |
| 2097 | 2406 | * Save attributes for a trip |
| 2098 | 2407 | */ |
| 2099 | 2408 | public function saveAttributes(int $tripId, array $attributes): void |
| 2100 | 2409 | { |
| @@ -2657,34 +2966,229 @@ | ||
| 2657 | 2966 | )); |
| 2658 | 2967 | } |
| 2659 | 2968 | |
| 2660 | 2969 | /** |
| 2661 | - * Get price range statistics for published trips | |
| 2662 | - * | |
| 2970 | + * Get price range statistics for published trips. | |
| 2971 | + * | |
| 2972 | + * IMPORTANT: the previous SQL-only implementation only considered | |
| 2973 | + * the legacy `original_price` / `discounted_price` / `sale_price` | |
| 2974 | + * columns. For trips using traveler-based pricing (per-category | |
| 2975 | + * prices stored in the `price_types` JSON column), those legacy | |
| 2976 | + * columns are often empty or stale — so the computed max was lower | |
| 2977 | + * than the actually-displayed price, and the price-range slider | |
| 2978 | + * cut off above the real maximum (eg. trip cost €3755 but slider | |
| 2979 | + * stopped at €3731). Worse, the trip then couldn't be filtered | |
| 2980 | + * into the results because the effective-price WHERE excluded it. | |
| 2981 | + * | |
| 2982 | + * Fix: walk the published trips once in PHP and let | |
| 2983 | + * `TripPricingService` decide each trip's effective price using | |
| 2984 | + * the same logic the listing/single-trip pages use to DISPLAY the | |
| 2985 | + * price. We also track the per-trip MIN/MAX across categories so | |
| 2986 | + * the slider bounds cover every traveler tier (not only the | |
| 2987 | + * default / cheapest). | |
| 2988 | + * | |
| 2663 | 2989 | * @return object Object with min_price and max_price properties |
| 2664 | 2990 | */ |
| 2665 | 2991 | public function getPriceRangeStats(): object |
| 2666 | 2992 | { |
| 2667 | 2993 | $table = $this->getTableName(); |
| 2668 | - return $this->wpdb->get_row( | |
| 2669 | - "SELECT | |
| 2670 | - MIN(sub.eff_price) as min_price, | |
| 2671 | - MAX(sub.eff_price) as max_price | |
| 2672 | - FROM ( | |
| 2673 | - SELECT (CASE | |
| 2674 | - WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2)) | |
| 2675 | - WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2)) | |
| 2676 | - ELSE CAST(original_price AS DECIMAL(10,2)) | |
| 2677 | - END) AS eff_price | |
| 2678 | - FROM {$table} | |
| 2679 | - WHERE status IN ('publish', 'published') | |
| 2680 | - AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00') | |
| 2681 | - ) sub | |
| 2682 | - WHERE sub.eff_price > 0" | |
| 2683 | - ); | |
| 2994 | + $rows = $this->wpdb->get_results( | |
| 2995 | + "SELECT id, original_price, discounted_price, sale_price, price_types | |
| 2996 | + FROM {$table} | |
| 2997 | + WHERE status IN ('publish', 'published') | |
| 2998 | + AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')" | |
| 2999 | + ) ?: []; | |
| 3000 | + | |
| 3001 | + $minPrice = null; | |
| 3002 | + $maxPrice = null; | |
| 3003 | + | |
| 3004 | + foreach ($rows as $row) { | |
| 3005 | + $tripPrices = $this->collectTripDisplayPrices($row); | |
| 3006 | + foreach ($tripPrices as $price) { | |
| 3007 | + if ($price <= 0) { | |
| 3008 | + continue; | |
| 3009 | + } | |
| 3010 | + if ($minPrice === null || $price < $minPrice) { | |
| 3011 | + $minPrice = $price; | |
| 3012 | + } | |
| 3013 | + if ($maxPrice === null || $price > $maxPrice) { | |
| 3014 | + $maxPrice = $price; | |
| 3015 | + } | |
| 3016 | + } | |
| 3017 | + } | |
| 3018 | + | |
| 3019 | + // Fold in per-date / per-rule / per-departure price overrides so the | |
| 3020 | + // slider bounds span every price a customer can actually be charged, | |
| 3021 | + // not just the trips-table base. See collectPriceOverridePoints(). | |
| 3022 | + foreach ($this->collectPriceOverridePoints() as $price) { | |
| 3023 | + if ($price <= 0) { | |
| 3024 | + continue; | |
| 3025 | + } | |
| 3026 | + if ($minPrice === null || $price < $minPrice) { | |
| 3027 | + $minPrice = $price; | |
| 3028 | + } | |
| 3029 | + if ($maxPrice === null || $price > $maxPrice) { | |
| 3030 | + $maxPrice = $price; | |
| 3031 | + } | |
| 3032 | + } | |
| 3033 | + | |
| 3034 | + return (object) [ | |
| 3035 | + 'min_price' => $minPrice, | |
| 3036 | + 'max_price' => $maxPrice, | |
| 3037 | + ]; | |
| 2684 | 3038 | } |
| 2685 | 3039 | |
| 2686 | 3040 | /** |
| 3041 | + * Collect every price a single trip might display to a user. | |
| 3042 | + * | |
| 3043 | + * Returns BOTH the legacy "regular" effective price AND every | |
| 3044 | + * per-category effective price from `price_types`. Used by | |
| 3045 | + * `getPriceRangeStats()` so the price-range slider's bounds cover | |
| 3046 | + * the full range a customer could see — eg. for a traveler-based | |
| 3047 | + * trip with Adult €3755 / Child €1500 / Infant €100, the array | |
| 3048 | + * includes all three so the slider stretches from €100 to €3755. | |
| 3049 | + * | |
| 3050 | + * @param object $row Raw trip row (must include `price_types`, | |
| 3051 | + * `original_price`, `discounted_price`, | |
| 3052 | + * `sale_price`). | |
| 3053 | + * @return float[] | |
| 3054 | + */ | |
| 3055 | + protected function collectTripDisplayPrices(object $row): array | |
| 3056 | + { | |
| 3057 | + $prices = []; | |
| 3058 | + | |
| 3059 | + // Legacy regular pricing. | |
| 3060 | + $legacyPrice = \Yatra\Services\TripPricingService::resolveRegularCurrentPrice($row); | |
| 3061 | + if ($legacyPrice > 0) { | |
| 3062 | + $prices[] = $legacyPrice; | |
| 3063 | + } | |
| 3064 | + | |
| 3065 | + // Per-category prices (traveler-based pricing). Decode whether | |
| 3066 | + // `price_types` is stored as a JSON string (raw DB row) or an | |
| 3067 | + // already-decoded array (in-memory trip object). | |
| 3068 | + $priceTypes = $row->price_types ?? null; | |
| 3069 | + if (is_string($priceTypes) && $priceTypes !== '') { | |
| 3070 | + $decoded = json_decode($priceTypes, true); | |
| 3071 | + if (is_array($decoded)) { | |
| 3072 | + $priceTypes = $decoded; | |
| 3073 | + } | |
| 3074 | + } | |
| 3075 | + if (is_array($priceTypes)) { | |
| 3076 | + foreach ($priceTypes as $pt) { | |
| 3077 | + $price = \Yatra\Services\TripPricingService::resolveCategoryEffectivePrice((array) $pt); | |
| 3078 | + if ($price > 0) { | |
| 3079 | + $prices[] = $price; | |
| 3080 | + } | |
| 3081 | + } | |
| 3082 | + } | |
| 3083 | + | |
| 3084 | + return $prices; | |
| 3085 | + } | |
| 3086 | + | |
| 3087 | + /** | |
| 3088 | + * Effective price points that OVERRIDE a trip's base price for specific | |
| 3089 | + * dates, recurring rules, or individual departures. | |
| 3090 | + * | |
| 3091 | + * The price a customer actually sees, filters on, and pays is not always | |
| 3092 | + * the trips-table base price: it can be overridden per | |
| 3093 | + * - specific availability date (yatra_trip_availability_dates) | |
| 3094 | + * - recurring availability rule (yatra_trip_availability_rules) | |
| 3095 | + * - individual departure (yatra_trip_departures) | |
| 3096 | + * each carrying its own original/discounted price and per-category | |
| 3097 | + * (traveler-based) pricing JSON. The price-range slider bounds must span | |
| 3098 | + * these too — otherwise a trip whose highest bookable price lives only on | |
| 3099 | + * an override (e.g. a peak-season date priced €3,055 while the base is | |
| 3100 | + * lower) is unreachable on the filter even though customers can book it. | |
| 3101 | + * | |
| 3102 | + * Effective-price semantics mirror the customer-facing display exactly by | |
| 3103 | + * reusing collectTripDisplayPrices(): the discounted/sale price when set, | |
| 3104 | + * otherwise the original — the same amount shown and charged. Non-bookable | |
| 3105 | + * rows (blocked / cancelled / past dates, inactive rules) are excluded so | |
| 3106 | + * they can't push the slider max beyond any price a customer can reach. | |
| 3107 | + * | |
| 3108 | + * @return array<int,float> Effective override prices (each > 0). | |
| 3109 | + */ | |
| 3110 | + protected function collectPriceOverridePoints(): array | |
| 3111 | + { | |
| 3112 | + $prices = []; | |
| 3113 | + $today = current_time('Y-m-d'); | |
| 3114 | + | |
| 3115 | + // Only overrides that belong to a genuinely available (published, not | |
| 3116 | + // soft-deleted) trip may influence the bounds — otherwise a stray | |
| 3117 | + // override on a draft/trashed trip leaks a phantom max that no | |
| 3118 | + // customer-visible trip carries. Mirrors the trips-table filter the | |
| 3119 | + // base bound uses. | |
| 3120 | + $tripsTable = $this->getTableName(); | |
| 3121 | + $publishedJoin = "INNER JOIN {$tripsTable} t ON t.id = o.trip_id" | |
| 3122 | + . " AND t.status IN ('publish', 'published')" | |
| 3123 | + . " AND (t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')"; | |
| 3124 | + | |
| 3125 | + // 1) Specific availability dates — same column shape as a trip row, so | |
| 3126 | + // collectTripDisplayPrices() resolves them identically. | |
| 3127 | + $datesTable = \Yatra\Database\Tables\TripAvailabilityDatesTable::getTableName(); | |
| 3128 | + $dateRows = $this->wpdb->get_results($this->wpdb->prepare( | |
| 3129 | + "SELECT o.original_price, o.discounted_price, NULL AS sale_price, o.price_types | |
| 3130 | + FROM {$datesTable} o | |
| 3131 | + {$publishedJoin} | |
| 3132 | + WHERE COALESCE(o.is_blocked, 0) = 0 | |
| 3133 | + AND o.status NOT IN ('cancelled', 'blocked', 'closed') | |
| 3134 | + AND (o.departure_date IS NULL OR o.departure_date >= %s)", | |
| 3135 | + $today | |
| 3136 | + )) ?: []; | |
| 3137 | + foreach ($dateRows as $row) { | |
| 3138 | + foreach ($this->collectTripDisplayPrices($row) as $p) { | |
| 3139 | + $prices[] = $p; | |
| 3140 | + } | |
| 3141 | + } | |
| 3142 | + | |
| 3143 | + // 2) Recurring rules (active only — mirrors the `status = 'active'` | |
| 3144 | + // filter RecurringAvailabilityRepository uses when generating | |
| 3145 | + // availability). `traveler_pricing` holds the per-category JSON; a | |
| 3146 | + // fixed `price_override` is an absolute per-booking price. A | |
| 3147 | + // percentage override is a relative adjustment to the trip base — | |
| 3148 | + // already spanned by the base bound — so it adds no new absolute max. | |
| 3149 | + $rulesTable = \Yatra\Database\Tables\TripAvailabilityRulesTable::getTableName(); | |
| 3150 | + $ruleRows = $this->wpdb->get_results( | |
| 3151 | + "SELECT o.original_price, o.sale_price, NULL AS discounted_price, | |
| 3152 | + o.price_override, o.price_type, o.traveler_pricing AS price_types | |
| 3153 | + FROM {$rulesTable} o | |
| 3154 | + {$publishedJoin} | |
| 3155 | + WHERE o.status = 'active'" | |
| 3156 | + ) ?: []; | |
| 3157 | + foreach ($ruleRows as $row) { | |
| 3158 | + foreach ($this->collectTripDisplayPrices($row) as $p) { | |
| 3159 | + $prices[] = $p; | |
| 3160 | + } | |
| 3161 | + if ($row->price_override !== null && (string) $row->price_type !== 'percentage') { | |
| 3162 | + $po = (float) $row->price_override; | |
| 3163 | + if ($po > 0) { | |
| 3164 | + $prices[] = $po; | |
| 3165 | + } | |
| 3166 | + } | |
| 3167 | + } | |
| 3168 | + | |
| 3169 | + // 3) Departures — `price_override` is an absolute price; | |
| 3170 | + // `price_by_traveler_type` is the per-category JSON. | |
| 3171 | + $depTable = \Yatra\Database\Tables\DeparturesTable::getTableName(); | |
| 3172 | + $depRows = $this->wpdb->get_results($this->wpdb->prepare( | |
| 3173 | + "SELECT o.price_override AS original_price, NULL AS discounted_price, | |
| 3174 | + NULL AS sale_price, o.price_by_traveler_type AS price_types | |
| 3175 | + FROM {$depTable} o | |
| 3176 | + {$publishedJoin} | |
| 3177 | + WHERE o.status NOT IN ('cancelled') | |
| 3178 | + AND (COALESCE(o.start_date, o.`date`) IS NULL OR COALESCE(o.start_date, o.`date`) >= %s)", | |
| 3179 | + $today | |
| 3180 | + )) ?: []; | |
| 3181 | + foreach ($depRows as $row) { | |
| 3182 | + foreach ($this->collectTripDisplayPrices($row) as $p) { | |
| 3183 | + $prices[] = $p; | |
| 3184 | + } | |
| 3185 | + } | |
| 3186 | + | |
| 3187 | + return $prices; | |
| 3188 | + } | |
| 3189 | + | |
| 3190 | + /** | |
| 2687 | 3191 | * Count trips by difficulty level |
| 2688 | 3192 | * |
| 2689 | 3193 | * @param int $difficultyLevelId Difficulty level ID |
| 2690 | 3194 | * @return int Number of trips with this difficulty level |
| @@ -2846,35 +3350,78 @@ | ||
| 2846 | 3350 | * Get price statistics for filter sidebar |
| 2847 | 3351 | */ |
| 2848 | 3352 | public function getPriceStats(): ?object |
| 2849 | 3353 | { |
| 2850 | - global $wpdb; | |
| 2851 | - | |
| 2852 | 3354 | $table = $this->getTableName(); |
| 2853 | - | |
| 2854 | - $result = $wpdb->get_row(" | |
| 2855 | - SELECT | |
| 2856 | - MIN(sub.eff_price) as min_price, | |
| 2857 | - MAX(sub.eff_price) as max_price, | |
| 2858 | - AVG(sub.eff_price) as avg_price | |
| 2859 | - FROM ( | |
| 2860 | - SELECT (CASE | |
| 2861 | - WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2)) | |
| 2862 | - WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2)) | |
| 2863 | - ELSE CAST(original_price AS DECIMAL(10,2)) | |
| 2864 | - END) AS eff_price | |
| 2865 | - FROM {$table} | |
| 2866 | - WHERE status IN ('publish', 'published') | |
| 2867 | - AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00') | |
| 2868 | - ) sub | |
| 2869 | - WHERE sub.eff_price > 0 | |
| 2870 | - "); | |
| 2871 | - | |
| 2872 | - return $result ? (object) [ | |
| 2873 | - 'min_price' => (float) $result->min_price, | |
| 2874 | - 'max_price' => (float) $result->max_price, | |
| 2875 | - 'avg_price' => (float) $result->avg_price | |
| 2876 | - ] : null; | |
| 3355 | + | |
| 3356 | + // Bounds must cover EVERY price a customer can actually filter on. | |
| 3357 | + // The search filter matches a trip if its legacy effective price OR | |
| 3358 | + // ANY per-category price in `price_types` falls in range (see the | |
| 3359 | + // price clause in the listing query), so the slider's min/max have to | |
| 3360 | + // span the same set — otherwise a traveler-based trip whose highest | |
| 3361 | + // tier (eg. Adult €3,055) lives only in `price_types` becomes | |
| 3362 | + // unreachable when the column-only max stops short (eg. €3,045). | |
| 3363 | + // | |
| 3364 | + // Reuse `collectTripDisplayPrices()` — the single source of truth also | |
| 3365 | + // used by `getPriceRangeStats()` and the listing/single-trip DISPLAY — | |
| 3366 | + // so bound == display == filter for both regular and traveler-based | |
| 3367 | + // pricing. | |
| 3368 | + $rows = $this->wpdb->get_results( | |
| 3369 | + "SELECT id, original_price, discounted_price, sale_price, price_types | |
| 3370 | + FROM {$table} | |
| 3371 | + WHERE status IN ('publish', 'published') | |
| 3372 | + AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')" | |
| 3373 | + ) ?: []; | |
| 3374 | + | |
| 3375 | + $minPrice = null; | |
| 3376 | + $maxPrice = null; | |
| 3377 | + $sum = 0.0; | |
| 3378 | + $count = 0; | |
| 3379 | + | |
| 3380 | + foreach ($rows as $row) { | |
| 3381 | + foreach ($this->collectTripDisplayPrices($row) as $price) { | |
| 3382 | + if ($price <= 0) { | |
| 3383 | + continue; | |
| 3384 | + } | |
| 3385 | + if ($minPrice === null || $price < $minPrice) { | |
| 3386 | + $minPrice = $price; | |
| 3387 | + } | |
| 3388 | + if ($maxPrice === null || $price > $maxPrice) { | |
| 3389 | + $maxPrice = $price; | |
| 3390 | + } | |
| 3391 | + $sum += $price; | |
| 3392 | + $count++; | |
| 3393 | + } | |
| 3394 | + } | |
| 3395 | + | |
| 3396 | + // Fold in per-date / per-rule / per-departure price overrides so the | |
| 3397 | + // slider spans every price a customer can actually be charged (bounds | |
| 3398 | + // only — the average stays a base-trip figure). See | |
| 3399 | + // collectPriceOverridePoints(). | |
| 3400 | + foreach ($this->collectPriceOverridePoints() as $price) { | |
| 3401 | + if ($price <= 0) { | |
| 3402 | + continue; | |
| 3403 | + } | |
| 3404 | + if ($minPrice === null || $price < $minPrice) { | |
| 3405 | + $minPrice = $price; | |
| 3406 | + } | |
| 3407 | + if ($maxPrice === null || $price > $maxPrice) { | |
| 3408 | + $maxPrice = $price; | |
| 3409 | + } | |
| 3410 | + } | |
| 3411 | + | |
| 3412 | + if ($minPrice === null || $maxPrice === null) { | |
| 3413 | + return null; | |
| 3414 | + } | |
| 3415 | + | |
| 3416 | + // Widen to whole units so the template's `(int)` cast on the bounds | |
| 3417 | + // can never truncate a fractional boundary out of range (eg. a | |
| 3418 | + // €3,055.50 max would otherwise int-cast to 3055 and exclude it). | |
| 3419 | + return (object) [ | |
| 3420 | + 'min_price' => (float) floor($minPrice), | |
| 3421 | + 'max_price' => (float) ceil($maxPrice), | |
| 3422 | + 'avg_price' => $count > 0 ? (float) ($sum / $count) : 0.0, | |
| 3423 | + ]; | |
| 2877 | 3424 | } |
| 2878 | 3425 | |
| 2879 | 3426 | /** |
| 2880 | 3427 | * Distinct accommodation_type values on published trips with counts. |
| @@ -3184,16 +3731,48 @@ | ||
| 3184 | 3731 | return 0; |
| 3185 | 3732 | } |
| 3186 | 3733 | |
| 3187 | 3734 | /** |
| 3735 | + * Age thresholds behind the suitability filters. | |
| 3736 | + * | |
| 3737 | + * These were repeated as literals in six places — the filter query and the | |
| 3738 | + * count query for each band — so an operator whose "kids" means under 18 | |
| 3739 | + * rather than under 12 had no way to say so, and changing it meant editing | |
| 3740 | + * the same number in six spots and hoping none were missed. | |
| 3741 | + * | |
| 3742 | + * @return array{family_max:int, kids_max:int, senior_min:int, adults_min:int} | |
| 3743 | + */ | |
| 3744 | + public static function ageSuitabilityThresholds(): array | |
| 3745 | + { | |
| 3746 | + $defaults = [ | |
| 3747 | + 'family_max' => 5, | |
| 3748 | + 'kids_max' => 12, | |
| 3749 | + 'senior_min' => 65, | |
| 3750 | + 'adults_min' => 18, | |
| 3751 | + ]; | |
| 3752 | + | |
| 3753 | + $filtered = (array) apply_filters('yatra_age_suitability_thresholds', $defaults); | |
| 3754 | + | |
| 3755 | + foreach ($defaults as $key => $fallback) { | |
| 3756 | + $filtered[$key] = isset($filtered[$key]) && is_numeric($filtered[$key]) | |
| 3757 | + ? (int) $filtered[$key] | |
| 3758 | + : $fallback; | |
| 3759 | + } | |
| 3760 | + | |
| 3761 | + return $filtered; | |
| 3762 | + } | |
| 3763 | + | |
| 3764 | + /** | |
| 3188 | 3765 | * Count family friendly trips |
| 3189 | 3766 | */ |
| 3190 | 3767 | public function countByFamilyFriendly(): int |
| 3191 | 3768 | { |
| 3192 | 3769 | $table = $this->getTableName(); |
| 3770 | + $limit = (int) self::ageSuitabilityThresholds()['family_max']; | |
| 3771 | + | |
| 3193 | 3772 | return (int) $this->wpdb->get_var( |
| 3194 | - "SELECT COUNT(*) FROM {$table} | |
| 3195 | - WHERE status = 'publish' AND (age_min IS NULL OR age_min <= 5)" | |
| 3773 | + "SELECT COUNT(*) FROM {$table} | |
| 3774 | + WHERE status = 'publish' AND (age_min IS NULL OR age_min <= {$limit})" | |
| 3196 | 3775 | ); |
| 3197 | 3776 | } |
| 3198 | 3777 | |
| 3199 | 3778 | /** |
| @@ -3201,11 +3780,13 @@ | ||
| 3201 | 3780 | */ |
| 3202 | 3781 | public function countByKidsFriendly(): int |
| 3203 | 3782 | { |
| 3204 | 3783 | $table = $this->getTableName(); |
| 3784 | + $limit = (int) self::ageSuitabilityThresholds()['kids_max']; | |
| 3785 | + | |
| 3205 | 3786 | return (int) $this->wpdb->get_var( |
| 3206 | - "SELECT COUNT(*) FROM {$table} | |
| 3207 | - WHERE status = 'publish' AND (age_min IS NULL OR age_min <= 12)" | |
| 3787 | + "SELECT COUNT(*) FROM {$table} | |
| 3788 | + WHERE status = 'publish' AND (age_min IS NULL OR age_min <= {$limit})" | |
| 3208 | 3789 | ); |
| 3209 | 3790 | } |
| 3210 | 3791 | |
| 3211 | 3792 | /** |
| @@ -3213,11 +3794,13 @@ | ||
| 3213 | 3794 | */ |
| 3214 | 3795 | public function countBySeniorFriendly(): int |
| 3215 | 3796 | { |
| 3216 | 3797 | $table = $this->getTableName(); |
| 3798 | + $limit = (int) self::ageSuitabilityThresholds()['senior_min']; | |
| 3799 | + | |
| 3217 | 3800 | return (int) $this->wpdb->get_var( |
| 3218 | - "SELECT COUNT(*) FROM {$table} | |
| 3219 | - WHERE status = 'publish' AND (age_max IS NULL OR age_max >= 65)" | |
| 3801 | + "SELECT COUNT(*) FROM {$table} | |
| 3802 | + WHERE status = 'publish' AND (age_max IS NULL OR age_max >= {$limit})" | |
| 3220 | 3803 | ); |
| 3221 | 3804 | } |
| 3222 | 3805 | |
| 3223 | 3806 | /** |
| @@ -3225,11 +3808,13 @@ | ||
| 3225 | 3808 | */ |
| 3226 | 3809 | public function countByAdultsOnly(): int |
| 3227 | 3810 | { |
| 3228 | 3811 | $table = $this->getTableName(); |
| 3812 | + $limit = (int) self::ageSuitabilityThresholds()['adults_min']; | |
| 3813 | + | |
| 3229 | 3814 | return (int) $this->wpdb->get_var( |
| 3230 | - "SELECT COUNT(*) FROM {$table} | |
| 3231 | - WHERE status = 'publish' AND age_min >= 18" | |
| 3815 | + "SELECT COUNT(*) FROM {$table} | |
| 3816 | + WHERE status = 'publish' AND age_min >= {$limit}" | |
| 3232 | 3817 | ); |
| 3233 | 3818 | } |
| 3234 | 3819 | |
| 3235 | 3820 | /** |
