← All changes | app/Repositories/TripRepository.php +294 -39 3.0.8 → 3.0.17 View file →

		@@ -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
		@@ -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	{
		@@ -656,20 +691,21 @@
656	691	foreach (array_unique($filters['age_suitability']) as $age) {
657	692	if (!is_string($age)) {
658	693	continue;
659	694	}
	695	+ $ageLimits = self::ageSuitabilityThresholds();
660	696	switch ($age) {
661	697	case 'family-friendly':
662		- $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'] . ')';
663	699	break;
664	700	case 'kids-friendly':
665		- $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'] . ')';
666	702	break;
667	703	case 'senior-friendly':
668		- $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'] . ')';
669	705	break;
670	706	case 'adults-only':
671		- $ageParts[] = 't.age_min >= 18';
	707	+ $ageParts[] = 't.age_min >= ' . (int) $ageLimits['adults_min'];
672	708	break;
673	709	}
674	710	}
675	711	if ($ageParts !== []) {
		@@ -810,9 +846,29 @@
810	846	if (!empty($filters['duration_max']) && $filters['duration_max'] > 0) {
811	847	$wheres[] = "CAST(t.duration_days AS UNSIGNED) <= %d";
812	848	$params[] = $filters['duration_max'];
813	849	}
814		-
	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	+
815	871	// Rating filter
816	872	if (!empty($filters['rating_min']) && $filters['rating_min'] > 0) {
817	873	$having_clauses[] = "AVG(r.rating) >= %f";
818	874	$rating_params[] = $filters['rating_min'];
		@@ -2959,8 +3015,23 @@
2959	3015	}
2960	3016	}
2961	3017	}
2962	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	+
2963	3034	return (object) [
2964	3035	'min_price' => $minPrice,
2965	3036	'max_price' => $maxPrice,
2966	3037	];
		@@ -3013,8 +3084,111 @@
3013	3084	return $prices;
3014	3085	}
3015	3086
3016	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	+ /**
3017	3191	* Count trips by difficulty level
3018	3192	*
3019	3193	* @param int $difficultyLevelId Difficulty level ID
3020	3194	* @return int Number of trips with this difficulty level
		@@ -3176,35 +3350,78 @@
3176	3350	* Get price statistics for filter sidebar
3177	3351	*/
3178	3352	public function getPriceStats(): ?object
3179	3353	{
3180		- global $wpdb;
3181		-
3182	3354	$table = $this->getTableName();
3183		-
3184		- $result = $wpdb->get_row("
3185		- SELECT
3186		- MIN(sub.eff_price) as min_price,
3187		- MAX(sub.eff_price) as max_price,
3188		- AVG(sub.eff_price) as avg_price
3189		- FROM (
3190		- SELECT (CASE
3191		- WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2))
3192		- WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2))
3193		- ELSE CAST(original_price AS DECIMAL(10,2))
3194		- END) AS eff_price
3195		- FROM {$table}
3196		- WHERE status IN ('publish', 'published')
3197		- AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')
3198		- ) sub
3199		- WHERE sub.eff_price > 0
3200		- ");
3201		-
3202		- return $result ? (object) [
3203		- 'min_price' => (float) $result->min_price,
3204		- 'max_price' => (float) $result->max_price,
3205		- 'avg_price' => (float) $result->avg_price
3206		- ] : 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	+ ];
3207	3424	}
3208	3425
3209	3426	/**
3210	3427	* Distinct accommodation_type values on published trips with counts.
		@@ -3514,16 +3731,48 @@
3514	3731	return 0;
3515	3732	}
3516	3733
3517	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	+ /**
3518	3765	* Count family friendly trips
3519	3766	*/
3520	3767	public function countByFamilyFriendly(): int
3521	3768	{
3522	3769	$table = $this->getTableName();
	3770	+ $limit = (int) self::ageSuitabilityThresholds()['family_max'];
	3771	+
3523	3772	return (int) $this->wpdb->get_var(
3524		- "SELECT COUNT(*) FROM {$table}
3525		- 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})"
3526	3775	);
3527	3776	}
3528	3777
3529	3778	/**
		@@ -3531,11 +3780,13 @@
3531	3780	*/
3532	3781	public function countByKidsFriendly(): int
3533	3782	{
3534	3783	$table = $this->getTableName();
	3784	+ $limit = (int) self::ageSuitabilityThresholds()['kids_max'];
	3785	+
3535	3786	return (int) $this->wpdb->get_var(
3536		- "SELECT COUNT(*) FROM {$table}
3537		- 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})"
3538	3789	);
3539	3790	}
3540	3791
3541	3792	/**
		@@ -3543,11 +3794,13 @@
3543	3794	*/
3544	3795	public function countBySeniorFriendly(): int
3545	3796	{
3546	3797	$table = $this->getTableName();
	3798	+ $limit = (int) self::ageSuitabilityThresholds()['senior_min'];
	3799	+
3547	3800	return (int) $this->wpdb->get_var(
3548		- "SELECT COUNT(*) FROM {$table}
3549		- 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})"
3550	3803	);
3551	3804	}
3552	3805
3553	3806	/**
		@@ -3555,11 +3808,13 @@
3555	3808	*/
3556	3809	public function countByAdultsOnly(): int
3557	3810	{
3558	3811	$table = $this->getTableName();
	3812	+ $limit = (int) self::ageSuitabilityThresholds()['adults_min'];
	3813	+
3559	3814	return (int) $this->wpdb->get_var(
3560		- "SELECT COUNT(*) FROM {$table}
3561		- WHERE status = 'publish' AND age_min >= 18"
	3815	+ "SELECT COUNT(*) FROM {$table}
	3816	+ WHERE status = 'publish' AND age_min >= {$limit}"
3562	3817	);
3563	3818	}
3564	3819
3565	3820	/**