← All changes | app/Repositories/TripRepository.php +263 -27 3.0.5 → 3.0.16 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	{
		@@ -810,9 +845,29 @@
810	845	if (!empty($filters['duration_max']) && $filters['duration_max'] > 0) {
811	846	$wheres[] = "CAST(t.duration_days AS UNSIGNED) <= %d";
812	847	$params[] = $filters['duration_max'];
813	848	}
814		-
	849	+
	850	+ // Availability date filter ("available from this date onward"): keep only
	851	+ // trips that have a departure starting ON OR AFTER the selected date, so
	852	+ // a customer who picks a date sees every trip they could still travel on
	853	+ // from then. A departure matches when its effective start date (the
	854	+ // explicit start_date, else the single `date`) is >= the chosen day and
	855	+ // it has not been cancelled — the date bound alone excludes past dates
	856	+ // when the picker's min=today is respected, so no reliance on a possibly
	857	+ // stale 'past' status. Clause + param are appended together so the WHERE
	858	+ // ordering stays in sync with $params (see the count/main queries).
	859	+ if (!empty($filters['available_date'])) {
	860	+ $departures_table = DeparturesTable::getTableName();
	861	+ $wheres[] = "EXISTS (
	862	+ SELECT 1 FROM {$departures_table} yd
	863	+ WHERE yd.trip_id = t.id
	864	+ AND COALESCE(NULLIF(yd.start_date, '0000-00-00'), yd.date) >= %s
	865	+ AND yd.status <> 'cancelled'
	866	+ )";
	867	+ $params[] = $filters['available_date'];
	868	+ }
	869	+
815	870	// Rating filter
816	871	if (!empty($filters['rating_min']) && $filters['rating_min'] > 0) {
817	872	$having_clauses[] = "AVG(r.rating) >= %f";
818	873	$rating_params[] = $filters['rating_min'];
		@@ -1764,8 +1819,28 @@
1764	1819	*/
1765	1820	public function savePriceTypes(int $tripId, array $priceTypes): void
1766	1821	{
1767	1822	if (empty($priceTypes)) {
	1823	+ // Explicitly clear stored per-category pricing so that deleting all
	1824	+ // rows (even the last one) persists. Previously this early-returned,
	1825	+ // leaving the old price_types JSON in place — the deletion silently
	1826	+ // reverted on reload. Only the JSON is always cleared; the derived
	1827	+ // scalar columns (original/discounted/sale) are reset solely for
	1828	+ // traveler-based trips, because for regular pricing those columns
	1829	+ // hold the actual price and must not be wiped.
	1830	+ $table = TripsTable::getTableName();
	1831	+ $pricingType = $this->wpdb->get_var(
	1832	+ $this->wpdb->prepare("SELECT pricing_type FROM `{$table}` WHERE id = %d", $tripId)
	1833	+ );
	1834	+
	1835	+ $data = ['price_types' => null];
	1836	+ if ($pricingType === 'traveler_based') {
	1837	+ $data['original_price'] = null;
	1838	+ $data['discounted_price'] = null;
	1839	+ $data['sale_price'] = null;
	1840	+ }
	1841	+
	1842	+ $this->wpdb->update($table, $data, ['id' => $tripId]);
1768	1843	return;
1769	1844	}
1770	1845
1771	1846	// Ensure at most one default category is set (keep the first truthy one).
		@@ -2939,8 +3014,23 @@
2939	3014	}
2940	3015	}
2941	3016	}
2942	3017
	3018	+ // Fold in per-date / per-rule / per-departure price overrides so the
	3019	+ // slider bounds span every price a customer can actually be charged,
	3020	+ // not just the trips-table base. See collectPriceOverridePoints().
	3021	+ foreach ($this->collectPriceOverridePoints() as $price) {
	3022	+ if ($price <= 0) {
	3023	+ continue;
	3024	+ }
	3025	+ if ($minPrice === null || $price < $minPrice) {
	3026	+ $minPrice = $price;
	3027	+ }
	3028	+ if ($maxPrice === null || $price > $maxPrice) {
	3029	+ $maxPrice = $price;
	3030	+ }
	3031	+ }
	3032	+
2943	3033	return (object) [
2944	3034	'min_price' => $minPrice,
2945	3035	'max_price' => $maxPrice,
2946	3036	];
		@@ -2993,8 +3083,111 @@
2993	3083	return $prices;
2994	3084	}
2995	3085
2996	3086	/**
	3087	+ * Effective price points that OVERRIDE a trip's base price for specific
	3088	+ * dates, recurring rules, or individual departures.
	3089	+ *
	3090	+ * The price a customer actually sees, filters on, and pays is not always
	3091	+ * the trips-table base price: it can be overridden per
	3092	+ * - specific availability date (yatra_trip_availability_dates)
	3093	+ * - recurring availability rule (yatra_trip_availability_rules)
	3094	+ * - individual departure (yatra_trip_departures)
	3095	+ * each carrying its own original/discounted price and per-category
	3096	+ * (traveler-based) pricing JSON. The price-range slider bounds must span
	3097	+ * these too — otherwise a trip whose highest bookable price lives only on
	3098	+ * an override (e.g. a peak-season date priced €3,055 while the base is
	3099	+ * lower) is unreachable on the filter even though customers can book it.
	3100	+ *
	3101	+ * Effective-price semantics mirror the customer-facing display exactly by
	3102	+ * reusing collectTripDisplayPrices(): the discounted/sale price when set,
	3103	+ * otherwise the original — the same amount shown and charged. Non-bookable
	3104	+ * rows (blocked / cancelled / past dates, inactive rules) are excluded so
	3105	+ * they can't push the slider max beyond any price a customer can reach.
	3106	+ *
	3107	+ * @return array<int,float> Effective override prices (each > 0).
	3108	+ */
	3109	+ protected function collectPriceOverridePoints(): array
	3110	+ {
	3111	+ $prices = [];
	3112	+ $today = current_time('Y-m-d');
	3113	+
	3114	+ // Only overrides that belong to a genuinely available (published, not
	3115	+ // soft-deleted) trip may influence the bounds — otherwise a stray
	3116	+ // override on a draft/trashed trip leaks a phantom max that no
	3117	+ // customer-visible trip carries. Mirrors the trips-table filter the
	3118	+ // base bound uses.
	3119	+ $tripsTable = $this->getTableName();
	3120	+ $publishedJoin = "INNER JOIN {$tripsTable} t ON t.id = o.trip_id"
	3121	+ . " AND t.status IN ('publish', 'published')"
	3122	+ . " AND (t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')";
	3123	+
	3124	+ // 1) Specific availability dates — same column shape as a trip row, so
	3125	+ // collectTripDisplayPrices() resolves them identically.
	3126	+ $datesTable = \Yatra\Database\Tables\TripAvailabilityDatesTable::getTableName();
	3127	+ $dateRows = $this->wpdb->get_results($this->wpdb->prepare(
	3128	+ "SELECT o.original_price, o.discounted_price, NULL AS sale_price, o.price_types
	3129	+ FROM {$datesTable} o
	3130	+ {$publishedJoin}
	3131	+ WHERE COALESCE(o.is_blocked, 0) = 0
	3132	+ AND o.status NOT IN ('cancelled', 'blocked', 'closed')
	3133	+ AND (o.departure_date IS NULL OR o.departure_date >= %s)",
	3134	+ $today
	3135	+ )) ?: [];
	3136	+ foreach ($dateRows as $row) {
	3137	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3138	+ $prices[] = $p;
	3139	+ }
	3140	+ }
	3141	+
	3142	+ // 2) Recurring rules (active only — mirrors the `status = 'active'`
	3143	+ // filter RecurringAvailabilityRepository uses when generating
	3144	+ // availability). `traveler_pricing` holds the per-category JSON; a
	3145	+ // fixed `price_override` is an absolute per-booking price. A
	3146	+ // percentage override is a relative adjustment to the trip base —
	3147	+ // already spanned by the base bound — so it adds no new absolute max.
	3148	+ $rulesTable = \Yatra\Database\Tables\TripAvailabilityRulesTable::getTableName();
	3149	+ $ruleRows = $this->wpdb->get_results(
	3150	+ "SELECT o.original_price, o.sale_price, NULL AS discounted_price,
	3151	+ o.price_override, o.price_type, o.traveler_pricing AS price_types
	3152	+ FROM {$rulesTable} o
	3153	+ {$publishedJoin}
	3154	+ WHERE o.status = 'active'"
	3155	+ ) ?: [];
	3156	+ foreach ($ruleRows as $row) {
	3157	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3158	+ $prices[] = $p;
	3159	+ }
	3160	+ if ($row->price_override !== null && (string) $row->price_type !== 'percentage') {
	3161	+ $po = (float) $row->price_override;
	3162	+ if ($po > 0) {
	3163	+ $prices[] = $po;
	3164	+ }
	3165	+ }
	3166	+ }
	3167	+
	3168	+ // 3) Departures — `price_override` is an absolute price;
	3169	+ // `price_by_traveler_type` is the per-category JSON.
	3170	+ $depTable = \Yatra\Database\Tables\DeparturesTable::getTableName();
	3171	+ $depRows = $this->wpdb->get_results($this->wpdb->prepare(
	3172	+ "SELECT o.price_override AS original_price, NULL AS discounted_price,
	3173	+ NULL AS sale_price, o.price_by_traveler_type AS price_types
	3174	+ FROM {$depTable} o
	3175	+ {$publishedJoin}
	3176	+ WHERE o.status NOT IN ('cancelled')
	3177	+ AND (COALESCE(o.start_date, o.`date`) IS NULL OR COALESCE(o.start_date, o.`date`) >= %s)",
	3178	+ $today
	3179	+ )) ?: [];
	3180	+ foreach ($depRows as $row) {
	3181	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3182	+ $prices[] = $p;
	3183	+ }
	3184	+ }
	3185	+
	3186	+ return $prices;
	3187	+ }
	3188	+
	3189	+ /**
2997	3190	* Count trips by difficulty level
2998	3191	*
2999	3192	* @param int $difficultyLevelId Difficulty level ID
3000	3193	* @return int Number of trips with this difficulty level
		@@ -3156,35 +3349,78 @@
3156	3349	* Get price statistics for filter sidebar
3157	3350	*/
3158	3351	public function getPriceStats(): ?object
3159	3352	{
3160		- global $wpdb;
3161		-
3162	3353	$table = $this->getTableName();
3163		-
3164		- $result = $wpdb->get_row("
3165		- SELECT
3166		- MIN(sub.eff_price) as min_price,
3167		- MAX(sub.eff_price) as max_price,
3168		- AVG(sub.eff_price) as avg_price
3169		- FROM (
3170		- SELECT (CASE
3171		- WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2))
3172		- WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2))
3173		- ELSE CAST(original_price AS DECIMAL(10,2))
3174		- END) AS eff_price
3175		- FROM {$table}
3176		- WHERE status IN ('publish', 'published')
3177		- AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')
3178		- ) sub
3179		- WHERE sub.eff_price > 0
3180		- ");
3181		-
3182		- return $result ? (object) [
3183		- 'min_price' => (float) $result->min_price,
3184		- 'max_price' => (float) $result->max_price,
3185		- 'avg_price' => (float) $result->avg_price
3186		- ] : null;
	3354	+
	3355	+ // Bounds must cover EVERY price a customer can actually filter on.
	3356	+ // The search filter matches a trip if its legacy effective price OR
	3357	+ // ANY per-category price in `price_types` falls in range (see the
	3358	+ // price clause in the listing query), so the slider's min/max have to
	3359	+ // span the same set — otherwise a traveler-based trip whose highest
	3360	+ // tier (eg. Adult €3,055) lives only in `price_types` becomes
	3361	+ // unreachable when the column-only max stops short (eg. €3,045).
	3362	+ //
	3363	+ // Reuse `collectTripDisplayPrices()` — the single source of truth also
	3364	+ // used by `getPriceRangeStats()` and the listing/single-trip DISPLAY —
	3365	+ // so bound == display == filter for both regular and traveler-based
	3366	+ // pricing.
	3367	+ $rows = $this->wpdb->get_results(
	3368	+ "SELECT id, original_price, discounted_price, sale_price, price_types
	3369	+ FROM {$table}
	3370	+ WHERE status IN ('publish', 'published')
	3371	+ AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')"
	3372	+ ) ?: [];
	3373	+
	3374	+ $minPrice = null;
	3375	+ $maxPrice = null;
	3376	+ $sum = 0.0;
	3377	+ $count = 0;
	3378	+
	3379	+ foreach ($rows as $row) {
	3380	+ foreach ($this->collectTripDisplayPrices($row) as $price) {
	3381	+ if ($price <= 0) {
	3382	+ continue;
	3383	+ }
	3384	+ if ($minPrice === null || $price < $minPrice) {
	3385	+ $minPrice = $price;
	3386	+ }
	3387	+ if ($maxPrice === null || $price > $maxPrice) {
	3388	+ $maxPrice = $price;
	3389	+ }
	3390	+ $sum += $price;
	3391	+ $count++;
	3392	+ }
	3393	+ }
	3394	+
	3395	+ // Fold in per-date / per-rule / per-departure price overrides so the
	3396	+ // slider spans every price a customer can actually be charged (bounds
	3397	+ // only — the average stays a base-trip figure). See
	3398	+ // collectPriceOverridePoints().
	3399	+ foreach ($this->collectPriceOverridePoints() as $price) {
	3400	+ if ($price <= 0) {
	3401	+ continue;
	3402	+ }
	3403	+ if ($minPrice === null || $price < $minPrice) {
	3404	+ $minPrice = $price;
	3405	+ }
	3406	+ if ($maxPrice === null || $price > $maxPrice) {
	3407	+ $maxPrice = $price;
	3408	+ }
	3409	+ }
	3410	+
	3411	+ if ($minPrice === null || $maxPrice === null) {
	3412	+ return null;
	3413	+ }
	3414	+
	3415	+ // Widen to whole units so the template's `(int)` cast on the bounds
	3416	+ // can never truncate a fractional boundary out of range (eg. a
	3417	+ // €3,055.50 max would otherwise int-cast to 3055 and exclude it).
	3418	+ return (object) [
	3419	+ 'min_price' => (float) floor($minPrice),
	3420	+ 'max_price' => (float) ceil($maxPrice),
	3421	+ 'avg_price' => $count > 0 ? (float) ($sum / $count) : 0.0,
	3422	+ ];
3187	3423	}
3188	3424
3189	3425	/**
3190	3426	* Distinct accommodation_type values on published trips with counts.