← All changes | app/Repositories/TripRepository.php +575 -74 3.0.4 → 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	{
		@@ -481,9 +516,9 @@
481	516	$params[] = $filters['trip_type'];
482	517	}
483	518	}
484	519
485		- // Featured Priority (column on yatra_new_trips, indexed by idx_featured_priority).
	520	+ // Featured Priority (column on yatra_trips, indexed by idx_featured_priority).
486	521	// Admin form's Featured Priority dropdown is the single source of truth.
487	522	// Legacy `is_featured` shortcode/block flag is normalised upstream into featured_priority='featured'.
488	523	if (
489	524	!empty($filters['featured_priority'])
		@@ -735,18 +770,71 @@
735	770	}
736	771	}
737	772	}
738	773
739		- // Price range filter (effective list price, not original-only)
	774	+ // Price range filter.
	775	+ //
	776	+ // The previous version compared a single legacy "effective"
	777	+ // price (discounted → sale → original column) against the
	778	+ // range. That excluded traveler-based trips whose real
	779	+ // displayed price lives in the `price_types` JSON — so eg. a
	780	+ // trip costing €3755 (Adult category) never appeared in search
	781	+ // when the user set max=€3755, because its legacy
	782	+ // original_price was €0 / stale.
	783	+ //
	784	+ // New approach: match a trip if EITHER the legacy effective
	785	+ // price OR ANY per-category price in the JSON falls in the
	786	+ // requested range. Numeric values in the `price_types` JSON
	787	+ // are extracted with a regex on the raw text — works in all
	788	+ // MySQL 5.7+ builds without needing JSON_VALUE / JSON_TABLE
	789	+ // (which are inconsistent across MariaDB / older MySQL).
740	790	$effPrice = $this->sqlTripEffectiveListPrice();
741		- if (!empty($filters['price_min']) && $filters['price_min'] > 0) {
742		- $wheres[] = "{$effPrice} >= %f";
743		- $params[] = $filters['price_min'];
744		- }
	791	+ $priceMin = !empty($filters['price_min']) && $filters['price_min'] > 0 ? (float) $filters['price_min'] : null;
	792	+ $priceMax = !empty($filters['price_max']) && $filters['price_max'] > 0 ? (float) $filters['price_max'] : null;
745	793
746		- if (!empty($filters['price_max']) && $filters['price_max'] > 0) {
747		- $wheres[] = "{$effPrice} <= %f";
748		- $params[] = $filters['price_max'];
	794	+ if ($priceMin !== null || $priceMax !== null) {
	795	+ $legacyCond = [];
	796	+ if ($priceMin !== null) { $legacyCond[] = "{$effPrice} >= %f"; $params[] = $priceMin; }
	797	+ if ($priceMax !== null) { $legacyCond[] = "{$effPrice} <= %f"; $params[] = $priceMax; }
	798	+ $legacyClause = implode(' AND ', $legacyCond);
	799	+
	800	+ // For per-category pricing, find ANY price in `price_types`
	801	+ // JSON that falls in the requested range. This subquery
	802	+ // creates an ad-hoc number sequence (n=0..49 covers up to
	803	+ // 50 categories — far more than any real trip uses) and
	804	+ // uses JSON_EXTRACT to fetch each entry's effective price.
	805	+ $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)";
	806	+ // Each category's effective price = first non-null of
	807	+ // discounted_price → sale_price → original_price → price.
	808	+ $categoryEff = "COALESCE("
	809	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].discounted_price'))) AS DECIMAL(10,2)), 0),"
	810	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].sale_price'))) AS DECIMAL(10,2)), 0),"
	811	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].original_price'))) AS DECIMAL(10,2)), 0),"
	812	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].price'))) AS DECIMAL(10,2)), 0)"
	813	+ . ")";
	814	+ $catCond = [];
	815	+ if ($priceMin !== null) { $catCond[] = "{$categoryEff} >= %f"; $params[] = $priceMin; }
	816	+ if ($priceMax !== null) { $catCond[] = "{$categoryEff} <= %f"; $params[] = $priceMax; }
	817	+ $catClause = implode(' AND ', $catCond);
	818	+
	819	+ // Guard the per-category branch against malformed JSON.
	820	+ // JSON_EXTRACT throws on invalid JSON, and `price_types`
	821	+ // can be NULL, '', '[]' or a stale string on legacy rows.
	822	+ // JSON_VALID() + IFNULL() lets us bail cleanly for those.
	823	+ $perCategoryExists = "("
	824	+ . "t.price_types IS NOT NULL"
	825	+ . " AND t.price_types <> ''"
	826	+ . " AND JSON_VALID(t.price_types) = 1"
	827	+ . " AND EXISTS ("
	828	+ . "SELECT 1 FROM {$numbers} _n"
	829	+ . " WHERE JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, ']')) IS NOT NULL"
	830	+ . " AND {$catClause}"
	831	+ . ")"
	832	+ . ")";
	833	+
	834	+ // Match if EITHER condition passes — a single legacy-priced
	835	+ // trip OR a per-category trip with any matching tier.
	836	+ $wheres[] = "(({$legacyClause}) OR {$perCategoryExists})";
749	837	}
750	838
751	839	// Duration filter
752	840	if (!empty($filters['duration_min']) && $filters['duration_min'] > 0) {
		@@ -757,9 +845,29 @@
757	845	if (!empty($filters['duration_max']) && $filters['duration_max'] > 0) {
758	846	$wheres[] = "CAST(t.duration_days AS UNSIGNED) <= %d";
759	847	$params[] = $filters['duration_max'];
760	848	}
761		-
	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	+
762	870	// Rating filter
763	871	if (!empty($filters['rating_min']) && $filters['rating_min'] > 0) {
764	872	$having_clauses[] = "AVG(r.rating) >= %f";
765	873	$rating_params[] = $filters['rating_min'];
		@@ -1711,8 +1819,28 @@
1711	1819	*/
1712	1820	public function savePriceTypes(int $tripId, array $priceTypes): void
1713	1821	{
1714	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]);
1715	1843	return;
1716	1844	}
1717	1845
1718	1846	// Ensure at most one default category is set (keep the first truthy one).
		@@ -2090,45 +2218,165 @@
2090	2218	));
2091	2219	}
2092	2220
2093	2221	/**
2094		- * Save availability dates for a trip
	2222	+ * Save availability dates for a trip.
	2223	+ *
	2224	+ * Previously a flat DELETE-THEN-INSERT: every row for the trip was nuked
	2225	+ * and the incoming list rewritten. That made the trip-edit save lossy —
	2226	+ * when the Trip form only knew about a subset of fields on each date
	2227	+ * (e.g. it was loaded once, then the operator changed the seats on a
	2228	+ * single date through the Availability tab, then re-saved the Trip with
	2229	+ * the form's stale date payload), every field absent from the incoming
	2230	+ * row was reset to its hard-coded default. That's why operator edits
	2231	+ * to `seats_total` silently reverted to 20 after another trip-save:
	2232	+ *
	2233	+ * 1. Operator opens Trip → form loads `availability_dates` with
	2234	+ * `seats_total = 20`.
	2235	+ * 2. Operator switches to Availability tab → edits Sept 5 to
	2236	+ * `seats_total = 14` via the dedicated specific-date endpoint
	2237	+ * (writes directly to the row, so the row is now 14).
	2238	+ * 3. Operator returns to the Trip form, edits something unrelated
	2239	+ * (title / description), clicks Save. The form re-posts the date
	2240	+ * list it loaded in step 1 — `seats_total = 20`.
	2241	+ * 4. saveAvailabilityDates DELETEs everything, INSERTs the
	2242	+ * form-supplied list → Sept 5 is back to 20 (or the literal `20`
	2243	+ * default if the form didn't include seats_total at all).
	2244	+ *
	2245	+ * Fix: snapshot the existing rows before the DELETE and, when the
	2246	+ * incoming row omits an editable field, fall back to the snapshot's
	2247	+ * value instead of the schema default. The literal `20` default is
	2248	+ * removed in favour of the trip's `max_travelers` (or 1 as a last
	2249	+ * resort) so new dates inserted by truly-new entries don't pretend the
	2250	+ * trip seats 20 people unless the trip actually says so.
	2251	+ *
	2252	+ * Match key for the snapshot: `departure_date` + `departure_time` (both
	2253	+ * normalised), which is the same identity the Availability UI uses.
2095	2254	*/
2096	2255	public function saveAvailabilityDates(int $tripId, array $availabilityDates): void
2097	2256	{
2098	2257	global $wpdb;
2099	2258	$table = TripAvailabilityDatesTable::getTableName();
2100		-
	2259	+
	2260	+ // Snapshot existing rows by date + time so partial incoming payloads
	2261	+ // can be merged with persisted values instead of clobbering them.
	2262	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name from schema helper.
	2263	+ $existingRows = $wpdb->get_results($wpdb->prepare(
	2264	+ "SELECT * FROM {$table} WHERE trip_id = %d",
	2265	+ $tripId
	2266	+ ));
	2267	+ $snapshot = [];
	2268	+ if (is_array($existingRows)) {
	2269	+ foreach ($existingRows as $row) {
	2270	+ $key = self::availabilityRowKey(
	2271	+ (string) ($row->departure_date ?? ''),
	2272	+ (string) ($row->departure_time ?? '')
	2273	+ );
	2274	+ if ($key !== '') {
	2275	+ $snapshot[$key] = $row;
	2276	+ }
	2277	+ }
	2278	+ }
	2279	+
	2280	+ // Fallback capacity when both the incoming row and the snapshot
	2281	+ // miss seats_total: read the trip's max_travelers once. The
	2282	+ // legacy literal `20` was a phantom default that punished sites
	2283	+ // whose actual trip capacity differs.
	2284	+ $tripFallbackSeats = 0;
	2285	+ $tripRow = $this->find($tripId);
	2286	+ if (is_object($tripRow) && isset($tripRow->max_travelers)) {
	2287	+ $tripFallbackSeats = (int) $tripRow->max_travelers;
	2288	+ }
	2289	+ if ($tripFallbackSeats <= 0) {
	2290	+ $tripFallbackSeats = 1;
	2291	+ }
	2292	+
2101	2293	// Delete existing
2102	2294	$wpdb->delete($table, ['trip_id' => $tripId], ['%d']);
2103		-
	2295	+
2104	2296	// Insert new
2105	2297	if (!empty($availabilityDates)) {
2106	2298	foreach ($availabilityDates as $date) {
2107	2299	if (is_array($date) && !empty($date['departure_date'])) {
2108		- $seatsTotal = isset($date['seats_total']) ? (int) $date['seats_total'] : 20;
2109		- $seatsAvailable = isset($date['seats_available']) ? (int) $date['seats_available'] : $seatsTotal;
2110		-
	2300	+ $key = self::availabilityRowKey(
	2301	+ (string) $date['departure_date'],
	2302	+ (string) ($date['departure_time'] ?? '')
	2303	+ );
	2304	+ $existing = $key !== '' && isset($snapshot[$key]) ? $snapshot[$key] : null;
	2305	+
	2306	+ $seatsTotal = isset($date['seats_total'])
	2307	+ ? (int) $date['seats_total']
	2308	+ : ($existing ? (int) ($existing->seats_total ?? $tripFallbackSeats) : $tripFallbackSeats);
	2309	+ $seatsAvailable = isset($date['seats_available'])
	2310	+ ? (int) $date['seats_available']
	2311	+ : ($existing ? (int) ($existing->seats_available ?? $seatsTotal) : $seatsTotal);
	2312	+
	2313	+ $arrivalDate = isset($date['arrival_date'])
	2314	+ ? sanitize_text_field($date['arrival_date'])
	2315	+ : (isset($date['return_date'])
	2316	+ ? sanitize_text_field($date['return_date'])
	2317	+ : ($existing ? $existing->arrival_date : null));
	2318	+ $returnDate = isset($date['return_date'])
	2319	+ ? sanitize_text_field($date['return_date'])
	2320	+ : ($existing ? $existing->return_date : null);
	2321	+ $arrivalTime = isset($date['arrival_time'])
	2322	+ ? sanitize_text_field($date['arrival_time'])
	2323	+ : ($existing ? $existing->arrival_time : null);
	2324	+
	2325	+ $originalPrice = isset($date['original_price'])
	2326	+ ? (float) $date['original_price']
	2327	+ : (isset($date['price_override'])
	2328	+ ? (float) $date['price_override']
	2329	+ : ($existing && $existing->original_price !== null ? (float) $existing->original_price : null));
	2330	+ $discountedPrice = isset($date['discounted_price'])
	2331	+ ? (float) $date['discounted_price']
	2332	+ : ($existing && $existing->discounted_price !== null ? (float) $existing->discounted_price : null);
	2333	+
	2334	+ $fromLocation = isset($date['from_location']) ? sanitize_text_field($date['from_location']) : ($existing ? $existing->from_location : null);
	2335	+ $toLocation = isset($date['to_location']) ? sanitize_text_field($date['to_location']) : ($existing ? $existing->to_location : null);
	2336	+ $fromLat = isset($date['from_latitude']) && is_numeric($date['from_latitude'])
	2337	+ ? (string) $date['from_latitude']
	2338	+ : ($existing ? $existing->from_latitude : null);
	2339	+ $fromLng = isset($date['from_longitude']) && is_numeric($date['from_longitude'])
	2340	+ ? (string) $date['from_longitude']
	2341	+ : ($existing ? $existing->from_longitude : null);
	2342	+ $toLat = isset($date['to_latitude']) && is_numeric($date['to_latitude'])
	2343	+ ? (string) $date['to_latitude']
	2344	+ : ($existing ? $existing->to_latitude : null);
	2345	+ $toLng = isset($date['to_longitude']) && is_numeric($date['to_longitude'])
	2346	+ ? (string) $date['to_longitude']
	2347	+ : ($existing ? $existing->to_longitude : null);
	2348	+
	2349	+ if (isset($date['is_blackout'])) {
	2350	+ $status = $date['is_blackout'] ? 'blocked' : 'available';
	2351	+ } elseif (isset($date['status'])) {
	2352	+ $status = sanitize_text_field($date['status']);
	2353	+ } elseif ($existing) {
	2354	+ $status = (string) ($existing->status ?? 'available');
	2355	+ } else {
	2356	+ $status = 'available';
	2357	+ }
	2358	+
2111	2359	$insertData = [
2112	2360	'trip_id' => $tripId,
2113	2361	'departure_date' => sanitize_text_field($date['departure_date']),
2114		- 'arrival_date' => isset($date['arrival_date']) ? sanitize_text_field($date['arrival_date']) : ($date['return_date'] ?? null),
2115		- 'return_date' => isset($date['return_date']) ? sanitize_text_field($date['return_date']) : null,
2116		- 'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : null,
2117		- 'arrival_time' => isset($date['arrival_time']) ? sanitize_text_field($date['arrival_time']) : null,
	2362	+ 'arrival_date' => $arrivalDate,
	2363	+ 'return_date' => $returnDate,
	2364	+ 'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : ($existing ? $existing->departure_time : null),
	2365	+ 'arrival_time' => $arrivalTime,
2118	2366	'seats_total' => $seatsTotal,
2119	2367	'seats_available' => $seatsAvailable,
2120		- 'original_price' => isset($date['original_price']) ? (float) $date['original_price'] : (isset($date['price_override']) ? (float) $date['price_override'] : null),
2121		- 'discounted_price' => isset($date['discounted_price']) ? (float) $date['discounted_price'] : null,
2122		- 'from_location' => isset($date['from_location']) ? sanitize_text_field($date['from_location']) : null,
2123		- 'to_location' => isset($date['to_location']) ? sanitize_text_field($date['to_location']) : null,
2124		- 'from_latitude' => isset($date['from_latitude']) && is_numeric($date['from_latitude']) ? (string) $date['from_latitude'] : null,
2125		- 'from_longitude' => isset($date['from_longitude']) && is_numeric($date['from_longitude']) ? (string) $date['from_longitude'] : null,
2126		- 'to_latitude' => isset($date['to_latitude']) && is_numeric($date['to_latitude']) ? (string) $date['to_latitude'] : null,
2127		- 'to_longitude' => isset($date['to_longitude']) && is_numeric($date['to_longitude']) ? (string) $date['to_longitude'] : null,
2128		- 'status' => isset($date['is_blackout']) && $date['is_blackout'] ? 'blocked' : (isset($date['status']) ? sanitize_text_field($date['status']) : 'available'),
	2368	+ 'original_price' => $originalPrice,
	2369	+ 'discounted_price' => $discountedPrice,
	2370	+ 'from_location' => $fromLocation,
	2371	+ 'to_location' => $toLocation,
	2372	+ 'from_latitude' => $fromLat,
	2373	+ 'from_longitude' => $fromLng,
	2374	+ 'to_latitude' => $toLat,
	2375	+ 'to_longitude' => $toLng,
	2376	+ 'status' => $status,
2129	2377	];
2130		-
	2378	+
2131	2379	$wpdb->insert(
2132	2380	$table,
2133	2381	$insertData,
2134	2382	['%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
		@@ -2138,8 +2386,23 @@
2138	2386	}
2139	2387	}
2140	2388
2141	2389	/**
	2390	+ * Normalise a (date, time) pair to a single string key used to match
	2391	+ * incoming availability rows against the pre-delete snapshot. Time is
	2392	+ * left as-is for an exact comparison; empty time matches the "no
	2393	+ * specific time" row.
	2394	+ */
	2395	+ private static function availabilityRowKey(string $date, string $time): string
	2396	+ {
	2397	+ $date = trim($date);
	2398	+ if ($date === '') {
	2399	+ return '';
	2400	+ }
	2401	+ return $date . '|' . trim($time);
	2402	+ }
	2403	+
	2404	+ /**
2142	2405	* Save attributes for a trip
2143	2406	*/
2144	2407	public function saveAttributes(int $tripId, array $attributes): void
2145	2408	{
		@@ -2702,34 +2965,229 @@
2702	2965	));
2703	2966	}
2704	2967
2705	2968	/**
2706		- * Get price range statistics for published trips
2707		- *
	2969	+ * Get price range statistics for published trips.
	2970	+ *
	2971	+ * IMPORTANT: the previous SQL-only implementation only considered
	2972	+ * the legacy `original_price` / `discounted_price` / `sale_price`
	2973	+ * columns. For trips using traveler-based pricing (per-category
	2974	+ * prices stored in the `price_types` JSON column), those legacy
	2975	+ * columns are often empty or stale — so the computed max was lower
	2976	+ * than the actually-displayed price, and the price-range slider
	2977	+ * cut off above the real maximum (eg. trip cost €3755 but slider
	2978	+ * stopped at €3731). Worse, the trip then couldn't be filtered
	2979	+ * into the results because the effective-price WHERE excluded it.
	2980	+ *
	2981	+ * Fix: walk the published trips once in PHP and let
	2982	+ * `TripPricingService` decide each trip's effective price using
	2983	+ * the same logic the listing/single-trip pages use to DISPLAY the
	2984	+ * price. We also track the per-trip MIN/MAX across categories so
	2985	+ * the slider bounds cover every traveler tier (not only the
	2986	+ * default / cheapest).
	2987	+ *
2708	2988	* @return object Object with min_price and max_price properties
2709	2989	*/
2710	2990	public function getPriceRangeStats(): object
2711	2991	{
2712	2992	$table = $this->getTableName();
2713		- return $this->wpdb->get_row(
2714		- "SELECT
2715		- MIN(sub.eff_price) as min_price,
2716		- MAX(sub.eff_price) as max_price
2717		- FROM (
2718		- SELECT (CASE
2719		- WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2))
2720		- WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2))
2721		- ELSE CAST(original_price AS DECIMAL(10,2))
2722		- END) AS eff_price
2723		- FROM {$table}
2724		- WHERE status IN ('publish', 'published')
2725		- AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')
2726		- ) sub
2727		- WHERE sub.eff_price > 0"
2728		- );
	2993	+ $rows = $this->wpdb->get_results(
	2994	+ "SELECT id, original_price, discounted_price, sale_price, price_types
	2995	+ FROM {$table}
	2996	+ WHERE status IN ('publish', 'published')
	2997	+ AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')"
	2998	+ ) ?: [];
	2999	+
	3000	+ $minPrice = null;
	3001	+ $maxPrice = null;
	3002	+
	3003	+ foreach ($rows as $row) {
	3004	+ $tripPrices = $this->collectTripDisplayPrices($row);
	3005	+ foreach ($tripPrices as $price) {
	3006	+ if ($price <= 0) {
	3007	+ continue;
	3008	+ }
	3009	+ if ($minPrice === null || $price < $minPrice) {
	3010	+ $minPrice = $price;
	3011	+ }
	3012	+ if ($maxPrice === null || $price > $maxPrice) {
	3013	+ $maxPrice = $price;
	3014	+ }
	3015	+ }
	3016	+ }
	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	+
	3033	+ return (object) [
	3034	+ 'min_price' => $minPrice,
	3035	+ 'max_price' => $maxPrice,
	3036	+ ];
2729	3037	}
2730	3038
2731	3039	/**
	3040	+ * Collect every price a single trip might display to a user.
	3041	+ *
	3042	+ * Returns BOTH the legacy "regular" effective price AND every
	3043	+ * per-category effective price from `price_types`. Used by
	3044	+ * `getPriceRangeStats()` so the price-range slider's bounds cover
	3045	+ * the full range a customer could see — eg. for a traveler-based
	3046	+ * trip with Adult €3755 / Child €1500 / Infant €100, the array
	3047	+ * includes all three so the slider stretches from €100 to €3755.
	3048	+ *
	3049	+ * @param object $row Raw trip row (must include `price_types`,
	3050	+ * `original_price`, `discounted_price`,
	3051	+ * `sale_price`).
	3052	+ * @return float[]
	3053	+ */
	3054	+ protected function collectTripDisplayPrices(object $row): array
	3055	+ {
	3056	+ $prices = [];
	3057	+
	3058	+ // Legacy regular pricing.
	3059	+ $legacyPrice = \Yatra\Services\TripPricingService::resolveRegularCurrentPrice($row);
	3060	+ if ($legacyPrice > 0) {
	3061	+ $prices[] = $legacyPrice;
	3062	+ }
	3063	+
	3064	+ // Per-category prices (traveler-based pricing). Decode whether
	3065	+ // `price_types` is stored as a JSON string (raw DB row) or an
	3066	+ // already-decoded array (in-memory trip object).
	3067	+ $priceTypes = $row->price_types ?? null;
	3068	+ if (is_string($priceTypes) && $priceTypes !== '') {
	3069	+ $decoded = json_decode($priceTypes, true);
	3070	+ if (is_array($decoded)) {
	3071	+ $priceTypes = $decoded;
	3072	+ }
	3073	+ }
	3074	+ if (is_array($priceTypes)) {
	3075	+ foreach ($priceTypes as $pt) {
	3076	+ $price = \Yatra\Services\TripPricingService::resolveCategoryEffectivePrice((array) $pt);
	3077	+ if ($price > 0) {
	3078	+ $prices[] = $price;
	3079	+ }
	3080	+ }
	3081	+ }
	3082	+
	3083	+ return $prices;
	3084	+ }
	3085	+
	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	+ /**
2732	3190	* Count trips by difficulty level
2733	3191	*
2734	3192	* @param int $difficultyLevelId Difficulty level ID
2735	3193	* @return int Number of trips with this difficulty level
		@@ -2891,35 +3349,78 @@
2891	3349	* Get price statistics for filter sidebar
2892	3350	*/
2893	3351	public function getPriceStats(): ?object
2894	3352	{
2895		- global $wpdb;
2896		-
2897	3353	$table = $this->getTableName();
2898		-
2899		- $result = $wpdb->get_row("
2900		- SELECT
2901		- MIN(sub.eff_price) as min_price,
2902		- MAX(sub.eff_price) as max_price,
2903		- AVG(sub.eff_price) as avg_price
2904		- FROM (
2905		- SELECT (CASE
2906		- WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2))
2907		- WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2))
2908		- ELSE CAST(original_price AS DECIMAL(10,2))
2909		- END) AS eff_price
2910		- FROM {$table}
2911		- WHERE status IN ('publish', 'published')
2912		- AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')
2913		- ) sub
2914		- WHERE sub.eff_price > 0
2915		- ");
2916		-
2917		- return $result ? (object) [
2918		- 'min_price' => (float) $result->min_price,
2919		- 'max_price' => (float) $result->max_price,
2920		- 'avg_price' => (float) $result->avg_price
2921		- ] : 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	+ ];
2922	3423	}
2923	3424
2924	3425	/**
2925	3426	* Distinct accommodation_type values on published trips with counts.