| @@ -122,10 +122,32 @@ | ||
| 122 | 122 | $count_query = $this->wpdb->prepare($count_query, ...$where_values); |
| 123 | 123 | } |
| 124 | 124 | $total = (int)$this->wpdb->get_var($count_query); |
| 125 | 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 | + | |
| 126 | 148 | // Get bookings with trip info and customer info |
| 127 | - $query = "SELECT | |
| 149 | + $query = "SELECT | |
| 128 | 150 | b.*, |
| 129 | 151 | t.title as trip_title, |
| 130 | 152 | t.slug as trip_slug, |
| 131 | 153 | t.featured_image, |
| @@ -135,9 +157,9 @@ | ||
| 135 | 157 | FROM {$table} b |
| 136 | 158 | LEFT JOIN {$trips_table} t ON b.trip_id = t.id |
| 137 | 159 | LEFT JOIN {$customers_table} c ON c.id = b.customer_id |
| 138 | 160 | WHERE {$where_sql} |
| 139 | - ORDER BY b.created_at DESC | |
| 161 | + ORDER BY {$order_sql} | |
| 140 | 162 | LIMIT %d OFFSET %d"; |
| 141 | 163 | |
| 142 | 164 | $query_values = array_merge($where_values, [$per_page, $offset]); |
| 143 | 165 | $bookings = $this->wpdb->get_results($this->wpdb->prepare($query, ...$query_values)); |
| @@ -242,8 +264,14 @@ | ||
| 242 | 264 | t.duration_days, t.duration_nights, t.difficulty_level, |
| 243 | 265 | t.starting_location, t.ending_location" |
| 244 | 266 | ]; |
| 245 | 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 | + | |
| 246 | 274 | $joins[] = "LEFT JOIN {$tripClassificationTable} tc ON tc.trip_id = t.id"; |
| 247 | 275 | $joins[] = "LEFT JOIN {$classificationTable} cls ON cls.id = tc.classification_id"; |
| 248 | 276 | $selectParts[] = "GROUP_CONCAT(DISTINCT cls.name ORDER BY tc.`sort_order` SEPARATOR ',') as trip_classifications"; |
| 249 | 277 | |
| @@ -725,13 +753,24 @@ | ||
| 725 | 753 | // Use TripRepository for trips table |
| 726 | 754 | $tripRepository = new \Yatra\Repositories\TripRepository(); |
| 727 | 755 | $tripsTable = $tripRepository->getTableName(); |
| 728 | 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). | |
| 729 | 768 | return $this->wpdb->get_results($this->wpdb->prepare( |
| 730 | - "SELECT b.*, t.title as trip_title, t.currency | |
| 769 | + "SELECT b.*, t.title as trip_title | |
| 731 | 770 | FROM {$table} b |
| 732 | 771 | LEFT JOIN {$tripsTable} t ON b.trip_id = t.id |
| 733 | - WHERE b.status = 'confirmed' | |
| 772 | + WHERE (b.status = 'confirmed' OR (b.status = 'pending' AND b.amount_paid > 0)) | |
| 734 | 773 | AND b.travel_date = %s |
| 735 | 774 | AND b.reminder_sent = 0", |
| 736 | 775 | $travelDate |
| 737 | 776 | )); |
| @@ -737,8 +776,52 @@ | ||
| 737 | 776 | )); |
| 738 | 777 | } |
| 739 | 778 | |
| 740 | 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 | + /** | |
| 741 | 824 | * Mark booking reminder as sent |
| 742 | 825 | * |
| 743 | 826 | * @param int $bookingId Booking ID |
| 744 | 827 | * @return bool |
| @@ -766,20 +849,44 @@ | ||
| 766 | 849 | * |
| 767 | 850 | * @param string $expiryThreshold Datetime threshold |
| 768 | 851 | * @return array |
| 769 | 852 | */ |
| 770 | - 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 | |
| 771 | 870 | { |
| 772 | 871 | $table = $this->getTableName(); |
| 773 | 872 | |
| 774 | - return $this->wpdb->get_results($this->wpdb->prepare( | |
| 775 | - "SELECT id, reference, contact_email, contact_first_name, contact_last_name, trip_id | |
| 776 | - FROM {$table} | |
| 777 | - WHERE status = 'pending' | |
| 778 | - AND payment_status = 'pending' | |
| 779 | - AND created_at < %s", | |
| 780 | - $expiryThreshold | |
| 781 | - )); | |
| 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)); | |
| 782 | 889 | } |
| 783 | 890 | |
| 784 | 891 | /** |
| 785 | 892 | * Expire a booking |
