| @@ -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 |
| @@ -1011,12 +1097,74 @@ | ||
| 1011 | 1097 | * @return list<string> |
| 1012 | 1098 | */ |
| 1013 | 1099 | public static function getCapacityConsumingBookingStatuses(): array |
| 1014 | 1100 | { |
| 1015 | - return ['pending', 'confirmed', 'processing', 'completed', 'on_hold']; | |
| 1101 | + $default = ['pending', 'confirmed', 'processing', 'completed', 'on_hold']; | |
| 1102 | + /** @var list<string> $default */ | |
| 1103 | + $filtered = apply_filters('yatra_capacity_consuming_booking_statuses', $default); | |
| 1104 | + return is_array($filtered) && $filtered !== [] ? array_values(array_unique(array_map('strval', $filtered))) : $default; | |
| 1016 | 1105 | } |
| 1017 | 1106 | |
| 1018 | 1107 | /** |
| 1108 | + * Count booked travelers for a virtual (rule-generated) slot. | |
| 1109 | + * | |
| 1110 | + * Rule-generated dates do not have a numeric availability_id, so capacity must be | |
| 1111 | + * computed from bookings by (trip_id, travel_date, departure_time) and the same | |
| 1112 | + * capacity-consuming statuses used for manual availability rows. | |
| 1113 | + */ | |
| 1114 | + public function countActiveSeatsForSlot(int $tripId, string $travelDate, ?string $departureTime = null): int | |
| 1115 | + { | |
| 1116 | + if ($tripId <= 0 || $travelDate === '') { | |
| 1117 | + return 0; | |
| 1118 | + } | |
| 1119 | + | |
| 1120 | + $bookingsTable = esc_sql($this->getTableName()); | |
| 1121 | + $statuses = self::getCapacityConsumingBookingStatuses(); | |
| 1122 | + $stPh = implode(',', array_fill(0, count($statuses), '%s')); | |
| 1123 | + | |
| 1124 | + // Use booking_departures when we need time-slot precision. | |
| 1125 | + $relationTable = esc_sql(\Yatra\Database\Tables\BookingDeparturesTable::getTableName()); | |
| 1126 | + | |
| 1127 | + if ($departureTime !== null && $departureTime !== '') { | |
| 1128 | + // Normalize to match TIME storage in MySQL (HH:MM:SS). | |
| 1129 | + $ts = strtotime($departureTime); | |
| 1130 | + if ($ts !== false) { | |
| 1131 | + $departureTime = date('H:i:s', $ts); | |
| 1132 | + } | |
| 1133 | + $params = array_merge([$tripId, $travelDate, $departureTime], $statuses); | |
| 1134 | + | |
| 1135 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 1136 | + $count = (int) $this->wpdb->get_var($this->wpdb->prepare( | |
| 1137 | + "SELECT COALESCE(SUM(b.travelers_count), 0) | |
| 1138 | + FROM `{$bookingsTable}` b | |
| 1139 | + INNER JOIN `{$relationTable}` bd ON b.id = bd.booking_id | |
| 1140 | + WHERE b.trip_id = %d | |
| 1141 | + AND b.travel_date = %s | |
| 1142 | + AND bd.travel_date = %s | |
| 1143 | + AND bd.departure_time = %s | |
| 1144 | + AND b.status IN ({$stPh})", | |
| 1145 | + array_merge([$tripId, $travelDate, $travelDate, $departureTime], $statuses) | |
| 1146 | + )); | |
| 1147 | + /** @var int $count */ | |
| 1148 | + $count = (int) apply_filters('yatra_virtual_availability_reserved_seats', $count, $tripId, $travelDate, $departureTime, $statuses); | |
| 1149 | + return max(0, $count); | |
| 1150 | + } | |
| 1151 | + | |
| 1152 | + // No time-slot filter: sum all bookings for the trip/date. | |
| 1153 | + // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared | |
| 1154 | + $count = (int) $this->wpdb->get_var($this->wpdb->prepare( | |
| 1155 | + "SELECT COALESCE(SUM(travelers_count), 0) | |
| 1156 | + FROM `{$bookingsTable}` | |
| 1157 | + WHERE trip_id = %d | |
| 1158 | + AND travel_date = %s | |
| 1159 | + AND status IN ({$stPh})", | |
| 1160 | + array_merge([$tripId, $travelDate], $statuses) | |
| 1161 | + )); | |
| 1162 | + $count = (int) apply_filters('yatra_virtual_availability_reserved_seats', $count, $tripId, $travelDate, null, $statuses); | |
| 1163 | + return max(0, $count); | |
| 1164 | + } | |
| 1165 | + | |
| 1166 | + /** | |
| 1019 | 1167 | * Count booked travelers by availability ID |
| 1020 | 1168 | * |
| 1021 | 1169 | * @param int $availabilityId Availability ID |
| 1022 | 1170 | * @return int Number of booked travelers |
| @@ -1098,10 +1246,9 @@ | ||
| 1098 | 1246 | public function findByDepartureId(int $departureId): array |
| 1099 | 1247 | { |
| 1100 | 1248 | $table = $this->getTableName(); |
| 1101 | 1249 | |
| 1102 | - // Using hardcoded table name since there's no dedicated repository for this table | |
| 1103 | - $relationTable = $this->wpdb->prefix . 'yatra_booking_departures'; | |
| 1250 | + $relationTable = BookingDeparturesTable::getTableName(); | |
| 1104 | 1251 | |
| 1105 | 1252 | $bookings = $this->wpdb->get_results($this->wpdb->prepare( |
| 1106 | 1253 | "SELECT b.* FROM {$table} b |
| 1107 | 1254 | INNER JOIN {$relationTable} bd ON b.id = bd.booking_id |
