← All changes | app/Repositories/BookingRepository.php +134 -49 3.0.4 → 3.0.16 View file →

		@@ -4,8 +4,9 @@
4	4
5	5	namespace Yatra\Repositories;
6	6
7	7	use Yatra\Constants\ClassificationTypes;
	8	+use Yatra\Database\Tables\BookingDeparturesTable;
8	9	use Yatra\Database\Tables\BookingsTable;
9	10	use Yatra\Database\Tables\ClassificationsTable;
10	11	use Yatra\Database\Tables\ReviewsTable;
11	12	use Yatra\Database\Tables\TripClassificationsTable;
		@@ -21,50 +22,28 @@
21	22	* @package Yatra\Repositories
22	23	*/
23	24	class BookingRepository extends BaseRepository
24	25	{
25		- private ?string $resolvedBookingsTable = null;
26		-
27		- private function getResolvedBookingsTable(): string
28		- {
29		- if ($this->resolvedBookingsTable !== null) {
30		- return $this->resolvedBookingsTable;
31		- }
32		- $candidates = [
33		- $this->wpdb->prefix . 'yatra_new_bookings',
34		- $this->wpdb->prefix . 'yatra_bookings',
35		- ];
36		- foreach ($candidates as $candidate) {
37		- $pattern = $this->wpdb->esc_like($candidate);
38		- $exists = $this->wpdb->get_var($this->wpdb->prepare('SHOW TABLES LIKE %s', $pattern));
39		- if ($exists === $candidate) {
40		- $this->resolvedBookingsTable = $candidate;
41		- if (defined('WP_DEBUG') && WP_DEBUG) {
42		- }
43		- return $candidate;
44		- }
45		- }
46		- // Fallback to default
47		- $this->resolvedBookingsTable = BookingsTable::getTableName();
48		- if (defined('WP_DEBUG') && WP_DEBUG) {
49		- }
50		- return $this->resolvedBookingsTable;
51		- }
52		-
53	26	/**
54		- * Get full table name with prefix
	27	+ * Get full table name with prefix. Single source of truth =
	28	+ * {@see BookingsTable::getTableName()}. A previous "try both names"
	29	+ * resolver existed to bridge the 3.0.4 → 3.0.5 rename (when the
	30	+ * physical table could be either `wp_yatra_new_bookings` or
	31	+ * `wp_yatra_bookings`); now that
	32	+ * {@see \Yatra\Upgrades\Versions\Upgrade_3_0_5} guarantees the
	33	+ * canonical name, the probe is no longer needed.
55	34	*/
56	35	protected function getTableName(): string
57	36	{
58		- return $this->getResolvedBookingsTable();
	37	+ return BookingsTable::getTableName();
59	38	}
60	39
61	40	/**
62		- * Public accessor for resolved bookings table (e.g. joins from other repositories).
	41	+ * Public accessor for the bookings table name (e.g. joins from other repositories).
63	42	*/
64	43	public function getBookingsTableName(): string
65	44	{
66		- return $this->getResolvedBookingsTable();
	45	+ return BookingsTable::getTableName();
67	46	}
68	47
69	48	/**
70	49	* Get trips table name
		@@ -143,10 +122,32 @@
143	122	$count_query = $this->wpdb->prepare($count_query, ...$where_values);
144	123	}
145	124	$total = (int)$this->wpdb->get_var($count_query);
146	125
	126	+ // Resolve the sort column from a strict whitelist. ORDER BY cannot be
	127	+ // parameterised, so the column MUST come from this known-safe map and the
	128	+ // direction is constrained to ASC/DESC — user input never touches the SQL.
	129	+ $sortColumns = [
	130	+ 'booking_number' => 'b.id',
	131	+ 'reference' => 'b.reference',
	132	+ 'customer' => 'b.contact_first_name',
	133	+ 'trip' => 't.title',
	134	+ 'travelers' => 'b.travelers_count',
	135	+ 'booking_date' => 'b.created_at',
	136	+ 'created_at' => 'b.created_at',
	137	+ 'travel_date' => 'b.travel_date',
	138	+ 'amount' => 'b.total_amount',
	139	+ 'total_amount' => 'b.total_amount',
	140	+ 'payment_status' => 'b.payment_status',
	141	+ 'booking_status' => 'b.status',
	142	+ 'status' => 'b.status',
	143	+ ];
	144	+ $orderColumn = $sortColumns[(string) ($filters['orderby'] ?? '')] ?? 'b.created_at';
	145	+ $orderDir = strtoupper((string) ($filters['order'] ?? '')) === 'ASC' ? 'ASC' : 'DESC';
	146	+ $order_sql = $orderColumn . ' ' . $orderDir . ', b.id DESC';
	147	+
147	148	// Get bookings with trip info and customer info
148		- $query = "SELECT
	149	+ $query = "SELECT
149	150	b.*,
150	151	t.title as trip_title,
151	152	t.slug as trip_slug,
152	153	t.featured_image,
		@@ -156,9 +157,9 @@
156	157	FROM {$table} b
157	158	LEFT JOIN {$trips_table} t ON b.trip_id = t.id
158	159	LEFT JOIN {$customers_table} c ON c.id = b.customer_id
159	160	WHERE {$where_sql}
160		- ORDER BY b.created_at DESC
	161	+ ORDER BY {$order_sql}
161	162	LIMIT %d OFFSET %d";
162	163
163	164	$query_values = array_merge($where_values, [$per_page, $offset]);
164	165	$bookings = $this->wpdb->get_results($this->wpdb->prepare($query, ...$query_values));
		@@ -201,9 +202,9 @@
201	202	* @return object|null
202	203	*/
203	204	public function findByReference(string $reference): ?object
204	205	{
205		- $table = $this->getResolvedBookingsTable();
	206	+ $table = $this->getTableName();
206	207
207	208	$query = $this->wpdb->prepare(
208	209	"SELECT * FROM {$table} WHERE reference = %s",
209	210	sanitize_text_field($reference)
		@@ -246,9 +247,9 @@
246	247	* @return object|null
247	248	*/
248	249	public function findByReferenceWithTrip(string $reference): ?object
249	250	{
250		- $table = $this->getResolvedBookingsTable();
	251	+ $table = $this->getTableName();
251	252
252	253	// Use TripRepository for trips table
253	254	$tripRepository = new \Yatra\Repositories\TripRepository();
254	255	$tripsTable = $tripRepository->getTableName();
		@@ -263,8 +264,14 @@
263	264	t.duration_days, t.duration_nights, t.difficulty_level,
264	265	t.starting_location, t.ending_location"
265	266	];
266	267
	268	+ // Hour-based day tours (3.0.14+ column) — guarded so an install whose
	269	+ // upgrade ALTER has not run yet keeps rendering the confirmation page.
	270	+ if ($tripRepository->hasTripColumn('duration_hours')) {
	271	+ $selectParts[] = 't.duration_hours';
	272	+ }
	273	+
267	274	$joins[] = "LEFT JOIN {$tripClassificationTable} tc ON tc.trip_id = t.id";
268	275	$joins[] = "LEFT JOIN {$classificationTable} cls ON cls.id = tc.classification_id";
269	276	$selectParts[] = "GROUP_CONCAT(DISTINCT cls.name ORDER BY tc.`sort_order` SEPARATOR ',') as trip_classifications";
270	277
		@@ -746,13 +753,24 @@
746	753	// Use TripRepository for trips table
747	754	$tripRepository = new \Yatra\Repositories\TripRepository();
748	755	$tripsTable = $tripRepository->getTableName();
749	756
	757	+ // Confirmed bookings, plus PENDING bookings that have paid something
	758	+ // (a deposit). Under the Auto-Confirm `online` mode a deposit booking
	759	+ // legitimately stays pending until the balance is paid; those customers
	760	+ // are real travellers and must still get the pre-trip reminder — which
	761	+ // already carries the "outstanding balance, please pay before travel"
	762	+ // block for exactly this case. Unpaid pending bookings stay excluded.
	763	+ //
	764	+ // No `t.currency`: the trips table has never had that column, so the
	765	+ // previous SELECT threw "Unknown column" — get_results() then returned
	766	+ // nothing and the reminder cron silently sent zero emails. The booking's
	767	+ // own currency arrives via b.* (and is no longer clobbered by the join).
750	768	return $this->wpdb->get_results($this->wpdb->prepare(
751		- "SELECT b.*, t.title as trip_title, t.currency
	769	+ "SELECT b.*, t.title as trip_title
752	770	FROM {$table} b
753	771	LEFT JOIN {$tripsTable} t ON b.trip_id = t.id
754		- WHERE b.status = 'confirmed'
	772	+ WHERE (b.status = 'confirmed' OR (b.status = 'pending' AND b.amount_paid > 0))
755	773	AND b.travel_date = %s
756	774	AND b.reminder_sent = 0",
757	775	$travelDate
758	776	));
		@@ -758,8 +776,52 @@
758	776	));
759	777	}
760	778
761	779	/**
	780	+ * Find IDs of confirmed bookings whose tour has already taken place, so the
	781	+ * daily cron can transition them to 'completed' (which fires the
	782	+ * booking.completed notification / Email Automation sequence).
	783	+ *
	784	+ * "Tour has taken place" = its effective end date is strictly before today.
	785	+ * The effective date prefers end_date (multi-day itineraries), then
	786	+ * start_date, then travel_date (which is NOT NULL). NULLIF guards against
	787	+ * any legacy zero-dates so they fall through to the next non-empty date.
	788	+ *
	789	+ * The $floor lower bound (feature-activation date) ensures we never
	790	+ * retroactively complete — and email the customers of — tours that ended
	791	+ * before this automation existed. Only 'confirmed' bookings are eligible:
	792	+ * pending/on_hold/waitlist never travelled, and cancelled/refunded/completed
	793	+ * are terminal.
	794	+ *
	795	+ * @param string $today Today (WP-local, 'Y-m-d') — exclusive upper bound.
	796	+ * @param string $floor Activation floor ('Y-m-d') — inclusive lower bound.
	797	+ * @param int $limit Max rows per run (drains any backlog over days).
	798	+ * @return int[]
	799	+ */
	800	+ public function getConfirmedBookingIdsPastTour(string $today, string $floor, int $limit = 500): array
	801	+ {
	802	+ $table = $this->getTableName();
	803	+
	804	+ $effectiveDate = "COALESCE(NULLIF(b.end_date, '0000-00-00'), "
	805	+ . "NULLIF(b.start_date, '0000-00-00'), b.travel_date)";
	806	+
	807	+ $ids = $this->wpdb->get_col($this->wpdb->prepare(
	808	+ "SELECT b.id
	809	+ FROM {$table} b
	810	+ WHERE b.status = 'confirmed'
	811	+ AND {$effectiveDate} < %s
	812	+ AND {$effectiveDate} >= %s
	813	+ ORDER BY b.id ASC
	814	+ LIMIT %d",
	815	+ $today,
	816	+ $floor,
	817	+ $limit
	818	+ ));
	819	+
	820	+ return array_map('intval', (array) $ids);
	821	+ }
	822	+
	823	+ /**
762	824	* Mark booking reminder as sent
763	825	*
764	826	* @param int $bookingId Booking ID
765	827	* @return bool
		@@ -787,20 +849,44 @@
787	849	*
788	850	* @param string $expiryThreshold Datetime threshold
789	851	* @return array
790	852	*/
791		- public function getExpiredPendingBookings(string $expiryThreshold): array
	853	+ /**
	854	+ * Unpaid bookings past the expiry threshold.
	855	+ *
	856	+ * @param string $expiryThreshold Bookings created before this are expired.
	857	+ * @param string $createdSince Activation floor: when set, bookings created
	858	+ * before it are never expired. Keeps a site
	859	+ * that switches the feature on from
	860	+ * retroactively cancelling (and emailing about)
	861	+ * its historical pending bookings.
	862	+ * @param int $limit Batch size. The sweep emails each customer,
	863	+ * so an unbounded run could try to send
	864	+ * hundreds of emails in one cron request and
	865	+ * time out half-way. It runs hourly, so a
	866	+ * backlog simply drains over the next runs.
	867	+ * @return array<int, object>
	868	+ */
	869	+ public function getExpiredPendingBookings(string $expiryThreshold, string $createdSince = '', int $limit = 200): array
792	870	{
793	871	$table = $this->getTableName();
794	872
795		- return $this->wpdb->get_results($this->wpdb->prepare(
796		- "SELECT id, reference, contact_email, contact_first_name, contact_last_name, trip_id
797		- FROM {$table}
798		- WHERE status = 'pending'
799		- AND payment_status = 'pending'
800		- AND created_at < %s",
801		- $expiryThreshold
802		- ));
	873	+ $sql = "SELECT id, reference, contact_email, contact_first_name, contact_last_name, trip_id
	874	+ FROM {$table}
	875	+ WHERE status = 'pending'
	876	+ AND payment_status = 'pending'
	877	+ AND created_at < %s";
	878	+ $params = [$expiryThreshold];
	879	+
	880	+ if ($createdSince !== '') {
	881	+ $sql .= ' AND created_at >= %s';
	882	+ $params[] = $createdSince;
	883	+ }
	884	+
	885	+ $sql .= ' ORDER BY created_at ASC LIMIT %d';
	886	+ $params[] = max(1, $limit);
	887	+
	888	+ return $this->wpdb->get_results($this->wpdb->prepare($sql, $params));
803	889	}
804	890
805	891	/**
806	892	* Expire a booking
		@@ -1160,10 +1246,9 @@
1160	1246	public function findByDepartureId(int $departureId): array
1161	1247	{
1162	1248	$table = $this->getTableName();
1163	1249
1164		- // Using hardcoded table name since there's no dedicated repository for this table
1165		- $relationTable = $this->wpdb->prefix . 'yatra_booking_departures';
	1250	+ $relationTable = BookingDeparturesTable::getTableName();
1166	1251
1167	1252	$bookings = $this->wpdb->get_results($this->wpdb->prepare(
1168	1253	"SELECT b.* FROM {$table} b
1169	1254	INNER JOIN {$relationTable} bd ON b.id = bd.booking_id