| @@ -96,10 +96,71 @@ | ||
| 96 | 96 | * @return array Array of Departure models |
| 97 | 97 | */ |
| 98 | 98 | public function findAll(array $filters = []): array |
| 99 | 99 | { |
| 100 | + $table = esc_sql($this->table); | |
| 101 | + [$where, $params] = $this->whereForAll($filters); | |
| 100 | 102 | |
| 103 | + $query = "SELECT * FROM `{$table}` WHERE " . implode(' AND ', $where); | |
| 104 | + // id as a final tiebreaker: rows sharing a date + time would otherwise | |
| 105 | + // have no stable order, so a paginated list could repeat or skip them | |
| 106 | + // across pages. | |
| 107 | + $query .= " ORDER BY date ASC, time ASC, id ASC"; | |
| 108 | + | |
| 109 | + if (!empty($filters['per_page'])) { | |
| 110 | + $perPage = (int) $filters['per_page']; | |
| 111 | + $page = max(1, (int) ($filters['page'] ?? 1)); | |
| 112 | + $offset = ($page - 1) * $perPage; | |
| 113 | + $query .= " LIMIT %d OFFSET %d"; | |
| 114 | + $params[] = $perPage; | |
| 115 | + $params[] = $offset; | |
| 116 | + } | |
| 117 | + | |
| 118 | + // Every dynamic value in this query goes through $params, so with no | |
| 119 | + // filters applied the SQL carries no placeholders at all — and calling | |
| 120 | + // prepare() on a placeholder-free query is what WordPress warns about | |
| 121 | + // ("The query argument of wpdb::prepare() must have a placeholder"). | |
| 122 | + // Only prepare when there is something to bind. | |
| 123 | + $results = empty($params) | |
| 124 | + ? $this->wpdb->get_results($query, ARRAY_A) | |
| 125 | + : $this->wpdb->get_results($this->wpdb->prepare($query, ...$params), ARRAY_A); | |
| 126 | + | |
| 127 | + return array_map(function ($row) { | |
| 128 | + return Departure::fromArray($row); | |
| 129 | + }, $results ?: []); | |
| 130 | + } | |
| 131 | + | |
| 132 | + /** | |
| 133 | + * Count departures across all trips matching the SAME filters as findAll() | |
| 134 | + * (page / per_page are ignored). This is the true total behind a paginated | |
| 135 | + * list — sharing whereForAll() means the count can never drift from the | |
| 136 | + * rows findAll() returns. | |
| 137 | + * | |
| 138 | + * @param array $filters Same filters as findAll(). | |
| 139 | + */ | |
| 140 | + public function countAll(array $filters = []): int | |
| 141 | + { | |
| 101 | 142 | $table = esc_sql($this->table); |
| 143 | + [$where, $params] = $this->whereForAll($filters); | |
| 144 | + | |
| 145 | + $query = "SELECT COUNT(*) FROM `{$table}` WHERE " . implode(' AND ', $where); | |
| 146 | + | |
| 147 | + // Same prepare guard as findAll(): no placeholders when nothing is bound. | |
| 148 | + return (int) (empty($params) | |
| 149 | + ? $this->wpdb->get_var($query) | |
| 150 | + : $this->wpdb->get_var($this->wpdb->prepare($query, ...$params))); | |
| 151 | + } | |
| 152 | + | |
| 153 | + /** | |
| 154 | + * WHERE fragments + prepare params shared by findAll() and countAll(), so | |
| 155 | + * the list and its total are always built from identical conditions. | |
| 156 | + * | |
| 157 | + * @param array $filters Filters: status, availability, date_from, date_to, | |
| 158 | + * source, include_past, past_only. | |
| 159 | + * @return array{0: string[], 1: array} | |
| 160 | + */ | |
| 161 | + private function whereForAll(array $filters): array | |
| 162 | + { | |
| 102 | 163 | $where = ['1=1']; // Always true for base condition |
| 103 | 164 | $params = []; |
| 104 | 165 | |
| 105 | 166 | // Status filter. 'past' is date-derived (see Departure::calculateStatus), |
| @@ -115,13 +176,18 @@ | ||
| 115 | 176 | OR (end_date IS NULL AND start_date IS NOT NULL AND start_date < CURDATE()) |
| 116 | 177 | OR (end_date IS NULL AND start_date IS NULL AND date < CURDATE()) |
| 117 | 178 | )"; |
| 118 | 179 | } else { |
| 119 | - $where[] = 'status = %s'; | |
| 120 | - $params[] = $filters['status']; | |
| 180 | + $this->applyStatusClause((string) $filters['status'], $where, $params); | |
| 121 | 181 | } |
| 122 | 182 | } |
| 123 | - | |
| 183 | + | |
| 184 | + // Independent capacity filter (see applyAvailabilityClause). | |
| 185 | + $this->applyAvailabilityClause($filters, $where); | |
| 186 | + | |
| 187 | + // Free-text search on date / notes (see applySearchClause). | |
| 188 | + $this->applySearchClause($filters, $where, $params); | |
| 189 | + | |
| 124 | 190 | // Date range filter - simple approach |
| 125 | 191 | if (isset($filters['date_from']) && is_string($filters['date_from']) && trim($filters['date_from']) !== '') { |
| 126 | 192 | $dateFrom = trim($filters['date_from']); |
| 127 | 193 | // Validate date format AND that it's a real date |
| @@ -171,11 +237,28 @@ | ||
| 171 | 237 | OR (end_date IS NULL AND start_date IS NULL AND date < CURDATE()) |
| 172 | 238 | )"; |
| 173 | 239 | } |
| 174 | 240 | |
| 241 | + return [$where, $params]; | |
| 242 | + } | |
| 243 | + | |
| 244 | + /** | |
| 245 | + * Find departures by trip ID | |
| 246 | + * | |
| 247 | + * @param int $tripId Trip ID | |
| 248 | + * @param array $filters Filters: status, date_from, date_to, source | |
| 249 | + * @return array Array of Departure models | |
| 250 | + */ | |
| 251 | + public function findByTripId(int $tripId, array $filters = []): array | |
| 252 | + { | |
| 253 | + $table = esc_sql($this->table); | |
| 254 | + [$where, $params] = $this->whereForTrip($tripId, $filters); | |
| 255 | + | |
| 175 | 256 | $query = "SELECT * FROM `{$table}` WHERE " . implode(' AND ', $where); |
| 176 | - $query .= " ORDER BY date ASC, time ASC"; | |
| 177 | - | |
| 257 | + // id as a final tiebreaker so pagination over rows sharing a date + time | |
| 258 | + // is stable (see findAll()). | |
| 259 | + $query .= " ORDER BY date ASC, time ASC, id ASC"; | |
| 260 | + | |
| 178 | 261 | if (!empty($filters['per_page'])) { |
| 179 | 262 | $perPage = (int) $filters['per_page']; |
| 180 | 263 | $page = max(1, (int) ($filters['page'] ?? 1)); |
| 181 | 264 | $offset = ($page - 1) * $perPage; |
| @@ -182,21 +265,14 @@ | ||
| 182 | 265 | $query .= " LIMIT %d OFFSET %d"; |
| 183 | 266 | $params[] = $perPage; |
| 184 | 267 | $params[] = $offset; |
| 185 | 268 | } |
| 186 | - | |
| 187 | - // Every dynamic value in this query goes through $params, so with no | |
| 188 | - // filters applied the SQL carries no placeholders at all — and calling | |
| 189 | - // prepare() on a placeholder-free query is what WordPress warns about | |
| 190 | - // ("The query argument of wpdb::prepare() must have a placeholder"). | |
| 191 | - // Only prepare when there is something to bind. | |
| 192 | - $results = empty($params) | |
| 193 | - ? $this->wpdb->get_results($query, ARRAY_A) | |
| 194 | - : $this->wpdb->get_results($this->wpdb->prepare($query, ...$params), ARRAY_A); | |
| 195 | - | |
| 196 | - if ($this->wpdb->last_error) { | |
| 197 | - } | |
| 198 | - | |
| 269 | + | |
| 270 | + $results = $this->wpdb->get_results( | |
| 271 | + $this->wpdb->prepare($query, ...$params), | |
| 272 | + ARRAY_A | |
| 273 | + ); | |
| 274 | + | |
| 199 | 275 | return array_map(function ($row) { |
| 200 | 276 | return Departure::fromArray($row); |
| 201 | 277 | }, $results ?: []); |
| 202 | 278 | } |
| @@ -201,17 +277,19 @@ | ||
| 201 | 277 | }, $results ?: []); |
| 202 | 278 | } |
| 203 | 279 | |
| 204 | 280 | /** |
| 205 | - * Find departures by trip ID | |
| 206 | - * | |
| 207 | - * @param int $tripId Trip ID | |
| 208 | - * @param array $filters Filters: status, date_from, date_to, source | |
| 209 | - * @return array Array of Departure models | |
| 281 | + * WHERE fragments + prepare params shared by findByTripId() and | |
| 282 | + * countByTripId(), so a trip's list and its total are always built from | |
| 283 | + * identical conditions. trip_id is always the first bound param. | |
| 284 | + * | |
| 285 | + * @param int $tripId Trip ID. | |
| 286 | + * @param array $filters Filters: status, availability, date_from, date_to, | |
| 287 | + * source, include_past, past_only. | |
| 288 | + * @return array{0: string[], 1: array} | |
| 210 | 289 | */ |
| 211 | - public function findByTripId(int $tripId, array $filters = []): array | |
| 290 | + private function whereForTrip(int $tripId, array $filters): array | |
| 212 | 291 | { |
| 213 | - | |
| 214 | 292 | $table = esc_sql($this->table); |
| 215 | 293 | $where = ['trip_id = %d']; |
| 216 | 294 | $params = [$tripId]; |
| 217 | 295 | |
| @@ -228,13 +306,18 @@ | ||
| 228 | 306 | OR (end_date IS NULL AND start_date IS NOT NULL AND start_date < CURDATE()) |
| 229 | 307 | OR (end_date IS NULL AND start_date IS NULL AND date < CURDATE()) |
| 230 | 308 | )"; |
| 231 | 309 | } else { |
| 232 | - $where[] = 'status = %s'; | |
| 233 | - $params[] = $filters['status']; | |
| 310 | + $this->applyStatusClause((string) $filters['status'], $where, $params); | |
| 234 | 311 | } |
| 235 | 312 | } |
| 236 | - | |
| 313 | + | |
| 314 | + // Independent capacity filter (see applyAvailabilityClause). | |
| 315 | + $this->applyAvailabilityClause($filters, $where); | |
| 316 | + | |
| 317 | + // Free-text search on date / notes (see applySearchClause). | |
| 318 | + $this->applySearchClause($filters, $where, $params); | |
| 319 | + | |
| 237 | 320 | // Date range filter - check both start_date and date columns |
| 238 | 321 | $columns = $this->wpdb->get_col("DESCRIBE {$table}"); |
| 239 | 322 | $hasStartDate = in_array('start_date', $columns, true); |
| 240 | 323 | |
| @@ -298,28 +381,9 @@ | ||
| 298 | 381 | OR (end_date IS NULL AND start_date IS NULL AND date < CURDATE()) |
| 299 | 382 | )"; |
| 300 | 383 | } |
| 301 | 384 | |
| 302 | - $query = "SELECT * FROM `{$table}` WHERE " . implode(' AND ', $where); | |
| 303 | - $query .= " ORDER BY date ASC, time ASC"; | |
| 304 | - | |
| 305 | - if (!empty($filters['per_page'])) { | |
| 306 | - $perPage = (int) $filters['per_page']; | |
| 307 | - $page = max(1, (int) ($filters['page'] ?? 1)); | |
| 308 | - $offset = ($page - 1) * $perPage; | |
| 309 | - $query .= " LIMIT %d OFFSET %d"; | |
| 310 | - $params[] = $perPage; | |
| 311 | - $params[] = $offset; | |
| 312 | - } | |
| 313 | - | |
| 314 | - $results = $this->wpdb->get_results( | |
| 315 | - $this->wpdb->prepare($query, ...$params), | |
| 316 | - ARRAY_A | |
| 317 | - ); | |
| 318 | - | |
| 319 | - return array_map(function ($row) { | |
| 320 | - return Departure::fromArray($row); | |
| 321 | - }, $results ?: []); | |
| 385 | + return [$where, $params]; | |
| 322 | 386 | } |
| 323 | 387 | |
| 324 | 388 | /** |
| 325 | 389 | * Find past departures by trip ID |
| @@ -351,42 +415,28 @@ | ||
| 351 | 415 | return $this->findByTripIdAndStartDate($tripId, $date, $time); |
| 352 | 416 | } |
| 353 | 417 | |
| 354 | 418 | /** |
| 355 | - * Count departures by trip ID | |
| 419 | + * Count departures for one trip matching the SAME filters as findByTripId() | |
| 420 | + * (page / per_page are ignored) — the true total behind a paginated list. | |
| 421 | + * | |
| 422 | + * Previously this kept its own, simpler WHERE (literal status = 'past' | |
| 423 | + * instead of the date-derived match, plain `date` columns instead of the | |
| 424 | + * start_date-aware range, no past_only), so it could disagree with the | |
| 425 | + * rows findByTripId() returned. Sharing whereForTrip() makes that | |
| 426 | + * impossible. | |
| 427 | + * | |
| 428 | + * @param int $tripId Trip ID. | |
| 429 | + * @param array $filters Same filters as findByTripId(). | |
| 356 | 430 | */ |
| 357 | 431 | public function countByTripId(int $tripId, array $filters = []): int |
| 358 | 432 | { |
| 359 | 433 | $table = esc_sql($this->table); |
| 360 | - $where = ['trip_id = %d']; | |
| 361 | - $params = [$tripId]; | |
| 362 | - | |
| 363 | - if (!empty($filters['status']) && $filters['status'] !== 'all') { | |
| 364 | - $where[] = 'status = %s'; | |
| 365 | - $params[] = $filters['status']; | |
| 366 | - } | |
| 367 | - | |
| 368 | - if (!empty($filters['date_from'])) { | |
| 369 | - $where[] = 'date >= %s'; | |
| 370 | - $params[] = $filters['date_from']; | |
| 371 | - } | |
| 372 | - | |
| 373 | - if (!empty($filters['date_to'])) { | |
| 374 | - $where[] = 'date <= %s'; | |
| 375 | - $params[] = $filters['date_to']; | |
| 376 | - } | |
| 377 | - | |
| 378 | - if (!empty($filters['source']) && $filters['source'] !== 'all') { | |
| 379 | - $where[] = 'source = %s'; | |
| 380 | - $params[] = $filters['source']; | |
| 381 | - } | |
| 382 | - | |
| 383 | - if (isset($filters['include_past']) && !$filters['include_past']) { | |
| 384 | - $where[] = 'date >= CURDATE()'; | |
| 385 | - } | |
| 386 | - | |
| 434 | + [$where, $params] = $this->whereForTrip($tripId, $filters); | |
| 435 | + | |
| 387 | 436 | $query = "SELECT COUNT(*) FROM `{$table}` WHERE " . implode(' AND ', $where); |
| 388 | - | |
| 437 | + | |
| 438 | + // trip_id is always bound, so there is always a placeholder to prepare. | |
| 389 | 439 | return (int) $this->wpdb->get_var($this->wpdb->prepare($query, ...$params)); |
| 390 | 440 | } |
| 391 | 441 | |
| 392 | 442 | /** |
| @@ -723,8 +773,99 @@ | ||
| 723 | 773 | AND status NOT IN ('cancelled', 'past', 'full')" |
| 724 | 774 | ); |
| 725 | 775 | |
| 726 | 776 | return $this->wpdb->rows_affected; |
| 777 | + } | |
| 778 | + | |
| 779 | + /** | |
| 780 | + * Add the stored-status clause for a list filter. | |
| 781 | + * | |
| 782 | + * 'upcoming' is INCLUSIVE of 'full': a departure at capacity is still a | |
| 783 | + * future departure. The status column conflates lifecycle with capacity | |
| 784 | + * (the cron overwrites 'upcoming' with 'full'), which made the Upcoming | |
| 785 | + * tab silently drop full departures. Capacity is its own dimension — | |
| 786 | + * filter it with the `availability` filter instead. 'full' remains | |
| 787 | + * matchable on its own so existing API consumers are unaffected. | |
| 788 | + * | |
| 789 | + * @param string $status Requested status (never 'all' / 'past' here). | |
| 790 | + * @param array $where WHERE fragments (by reference). | |
| 791 | + * @param array $params Prepare params (by reference). | |
| 792 | + */ | |
| 793 | + private function applyStatusClause(string $status, array &$where, array &$params): void | |
| 794 | + { | |
| 795 | + if ($status === 'upcoming') { | |
| 796 | + // Fixed literals — nothing user-supplied, so no placeholder needed. | |
| 797 | + $where[] = "status IN ('upcoming', 'full')"; | |
| 798 | + return; | |
| 799 | + } | |
| 800 | + | |
| 801 | + $where[] = 'status = %s'; | |
| 802 | + $params[] = $status; | |
| 803 | + } | |
| 804 | + | |
| 805 | + /** | |
| 806 | + * Independent capacity filter, derived from booked_count / max_capacity — | |
| 807 | + * the source of truth — rather than the stored status, which the daily | |
| 808 | + * cron can leave stale. max_capacity <= 0 means unlimited (never full). | |
| 809 | + * | |
| 810 | + * available — has room (unbooked or partially booked) | |
| 811 | + * partial — some bookings, but not full | |
| 812 | + * full — at or over capacity | |
| 813 | + * | |
| 814 | + * Absent or unrecognised values add no clause, so existing callers and | |
| 815 | + * API consumers see no change (additive / backward compatible). | |
| 816 | + * | |
| 817 | + * @param array $filters Raw filters. | |
| 818 | + * @param array $where WHERE fragments (by reference). | |
| 819 | + */ | |
| 820 | + private function applyAvailabilityClause(array $filters, array &$where): void | |
| 821 | + { | |
| 822 | + $availability = isset($filters['availability']) ? (string) $filters['availability'] : ''; | |
| 823 | + | |
| 824 | + // Every branch is a fixed literal; the value only selects a branch. | |
| 825 | + switch ($availability) { | |
| 826 | + case 'available': | |
| 827 | + $where[] = '(max_capacity <= 0 OR booked_count < max_capacity)'; | |
| 828 | + break; | |
| 829 | + case 'partial': | |
| 830 | + $where[] = '(booked_count > 0 AND (max_capacity <= 0 OR booked_count < max_capacity))'; | |
| 831 | + break; | |
| 832 | + case 'full': | |
| 833 | + $where[] = '(max_capacity > 0 AND booked_count >= max_capacity)'; | |
| 834 | + break; | |
| 835 | + } | |
| 836 | + } | |
| 837 | + | |
| 838 | + /** | |
| 839 | + * Free-text search for the admin list ("Search by date or notes"): matches | |
| 840 | + * the departure date (start — `date` is kept in sync with start_date), the | |
| 841 | + * end date, or the notes. The term is bound through esc_like() and a %s | |
| 842 | + * placeholder, so `%` / `_` / quotes in it are literal and nothing is ever | |
| 843 | + * interpolated into SQL. A blank / whitespace-only term adds no clause. | |
| 844 | + * | |
| 845 | + * Lives in the shared WHERE builders, so a search narrows the rows, the | |
| 846 | + * pagination total and the tab counts identically. | |
| 847 | + * | |
| 848 | + * @param array $filters Raw filters. | |
| 849 | + * @param array $where WHERE fragments (by reference). | |
| 850 | + * @param array $params Prepare params (by reference). | |
| 851 | + */ | |
| 852 | + private function applySearchClause(array $filters, array &$where, array &$params): void | |
| 853 | + { | |
| 854 | + $term = isset($filters['search']) ? trim((string) $filters['search']) : ''; | |
| 855 | + if ($term === '') { | |
| 856 | + return; | |
| 857 | + } | |
| 858 | + | |
| 859 | + $like = '%' . $this->wpdb->esc_like($term) . '%'; | |
| 860 | + | |
| 861 | + // DATE columns are cast explicitly so this is a plain string LIKE under | |
| 862 | + // every SQL mode (no implicit DATE/string coercion, which strict modes | |
| 863 | + // reject for some comparisons). NULL end_date / notes simply don't match. | |
| 864 | + $where[] = '(CAST(date AS CHAR) LIKE %s OR CAST(end_date AS CHAR) LIKE %s OR notes LIKE %s)'; | |
| 865 | + $params[] = $like; | |
| 866 | + $params[] = $like; | |
| 867 | + $params[] = $like; | |
| 727 | 868 | } |
| 728 | 869 | |
| 729 | 870 | /** |
| 730 | 871 | * Check if table supports soft delete |
