| @@ -96,19 +96,98 @@ | ||
| 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 | - // Status filter | |
| 166 | + // Status filter. 'past' is date-derived (see Departure::calculateStatus), | |
| 167 | + // NOT the stored status column — that column is only kept current by the | |
| 168 | + // daily cron, so filtering status = 'past' hid every departure that had | |
| 169 | + // taken place whenever the cron had not run. Match 'past' by date instead; | |
| 170 | + // all other statuses (upcoming/full/cancelled/trash) use the stored value. | |
| 171 | + $statusIsPast = (!empty($filters['status']) && $filters['status'] === 'past'); | |
| 106 | 172 | if (!empty($filters['status']) && $filters['status'] !== 'all') { |
| 107 | - $where[] = 'status = %s'; | |
| 108 | - $params[] = $filters['status']; | |
| 173 | + if ($statusIsPast) { | |
| 174 | + $where[] = "( | |
| 175 | + (end_date IS NOT NULL AND end_date < CURDATE()) | |
| 176 | + OR (end_date IS NULL AND start_date IS NOT NULL AND start_date < CURDATE()) | |
| 177 | + OR (end_date IS NULL AND start_date IS NULL AND date < CURDATE()) | |
| 178 | + )"; | |
| 179 | + } else { | |
| 180 | + $this->applyStatusClause((string) $filters['status'], $where, $params); | |
| 181 | + } | |
| 109 | 182 | } |
| 110 | - | |
| 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 | + | |
| 111 | 190 | // Date range filter - simple approach |
| 112 | 191 | if (isset($filters['date_from']) && is_string($filters['date_from']) && trim($filters['date_from']) !== '') { |
| 113 | 192 | $dateFrom = trim($filters['date_from']); |
| 114 | 193 | // Validate date format AND that it's a real date |
| @@ -132,18 +211,54 @@ | ||
| 132 | 211 | $where[] = 'source = %s'; |
| 133 | 212 | $params[] = $filters['source']; |
| 134 | 213 | } |
| 135 | 214 | |
| 136 | - // Past/upcoming filter | |
| 215 | + // Past/upcoming filter. Never exclude past dates when the caller is asking | |
| 216 | + // for the 'past' status — that combination is contradictory and returned | |
| 217 | + // nothing (the exact reason completed departures vanished from the Past tab). | |
| 137 | 218 | if (isset($filters['include_past'])) { |
| 138 | - if (!$filters['include_past']) { | |
| 219 | + if (!$filters['include_past'] && !$statusIsPast) { | |
| 139 | 220 | $where[] = 'date >= CURDATE()'; |
| 140 | 221 | } |
| 141 | 222 | } |
| 223 | + | |
| 224 | + // Past-only filter — for the "Past Departures" archive. Deliberately | |
| 225 | + // DATE-based (mirrors the end_date ?: start_date ?: date precedence used | |
| 226 | + // when marking departures past) rather than checking status = 'past': | |
| 227 | + // that stored status is only kept current by the daily cron, so relying | |
| 228 | + // on it made departures that had already taken place disappear entirely | |
| 229 | + // whenever the cron had not run. Date is the source of truth. | |
| 230 | + if (!empty($filters['past_only'])) { | |
| 231 | + // start_date / end_date are nullable DATE columns (never ''), so guard | |
| 232 | + // with IS NULL / IS NOT NULL — comparing a DATE column to '' errors | |
| 233 | + // under MySQL strict mode. | |
| 234 | + $where[] = "( | |
| 235 | + (end_date IS NOT NULL AND end_date < CURDATE()) | |
| 236 | + OR (end_date IS NULL AND start_date IS NOT NULL AND start_date < CURDATE()) | |
| 237 | + OR (end_date IS NULL AND start_date IS NULL AND date < CURDATE()) | |
| 238 | + )"; | |
| 239 | + } | |
| 142 | 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 | + | |
| 143 | 256 | $query = "SELECT * FROM `{$table}` WHERE " . implode(' AND ', $where); |
| 144 | - $query .= " ORDER BY date ASC, time ASC"; | |
| 145 | - | |
| 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 | + | |
| 146 | 261 | if (!empty($filters['per_page'])) { |
| 147 | 262 | $perPage = (int) $filters['per_page']; |
| 148 | 263 | $page = max(1, (int) ($filters['page'] ?? 1)); |
| 149 | 264 | $offset = ($page - 1) * $perPage; |
| @@ -150,21 +265,14 @@ | ||
| 150 | 265 | $query .= " LIMIT %d OFFSET %d"; |
| 151 | 266 | $params[] = $perPage; |
| 152 | 267 | $params[] = $offset; |
| 153 | 268 | } |
| 154 | - | |
| 155 | - // Every dynamic value in this query goes through $params, so with no | |
| 156 | - // filters applied the SQL carries no placeholders at all — and calling | |
| 157 | - // prepare() on a placeholder-free query is what WordPress warns about | |
| 158 | - // ("The query argument of wpdb::prepare() must have a placeholder"). | |
| 159 | - // Only prepare when there is something to bind. | |
| 160 | - $results = empty($params) | |
| 161 | - ? $this->wpdb->get_results($query, ARRAY_A) | |
| 162 | - : $this->wpdb->get_results($this->wpdb->prepare($query, ...$params), ARRAY_A); | |
| 163 | - | |
| 164 | - if ($this->wpdb->last_error) { | |
| 165 | - } | |
| 166 | - | |
| 269 | + | |
| 270 | + $results = $this->wpdb->get_results( | |
| 271 | + $this->wpdb->prepare($query, ...$params), | |
| 272 | + ARRAY_A | |
| 273 | + ); | |
| 274 | + | |
| 167 | 275 | return array_map(function ($row) { |
| 168 | 276 | return Departure::fromArray($row); |
| 169 | 277 | }, $results ?: []); |
| 170 | 278 | } |
| @@ -169,27 +277,47 @@ | ||
| 169 | 277 | }, $results ?: []); |
| 170 | 278 | } |
| 171 | 279 | |
| 172 | 280 | /** |
| 173 | - * Find departures by trip ID | |
| 174 | - * | |
| 175 | - * @param int $tripId Trip ID | |
| 176 | - * @param array $filters Filters: status, date_from, date_to, source | |
| 177 | - * @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} | |
| 178 | 289 | */ |
| 179 | - public function findByTripId(int $tripId, array $filters = []): array | |
| 290 | + private function whereForTrip(int $tripId, array $filters): array | |
| 180 | 291 | { |
| 181 | - | |
| 182 | 292 | $table = esc_sql($this->table); |
| 183 | 293 | $where = ['trip_id = %d']; |
| 184 | 294 | $params = [$tripId]; |
| 185 | 295 | |
| 186 | - // Status filter | |
| 296 | + // Status filter. 'past' is date-derived (see Departure::calculateStatus), | |
| 297 | + // NOT the stored status column — that column is only kept current by the | |
| 298 | + // daily cron, so filtering status = 'past' hid every departure that had | |
| 299 | + // taken place whenever the cron had not run. Match 'past' by date instead; | |
| 300 | + // all other statuses (upcoming/full/cancelled/trash) use the stored value. | |
| 301 | + $statusIsPast = (!empty($filters['status']) && $filters['status'] === 'past'); | |
| 187 | 302 | if (!empty($filters['status']) && $filters['status'] !== 'all') { |
| 188 | - $where[] = 'status = %s'; | |
| 189 | - $params[] = $filters['status']; | |
| 303 | + if ($statusIsPast) { | |
| 304 | + $where[] = "( | |
| 305 | + (end_date IS NOT NULL AND end_date < CURDATE()) | |
| 306 | + OR (end_date IS NULL AND start_date IS NOT NULL AND start_date < CURDATE()) | |
| 307 | + OR (end_date IS NULL AND start_date IS NULL AND date < CURDATE()) | |
| 308 | + )"; | |
| 309 | + } else { | |
| 310 | + $this->applyStatusClause((string) $filters['status'], $where, $params); | |
| 311 | + } | |
| 190 | 312 | } |
| 191 | - | |
| 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 | + | |
| 192 | 320 | // Date range filter - check both start_date and date columns |
| 193 | 321 | $columns = $this->wpdb->get_col("DESCRIBE {$table}"); |
| 194 | 322 | $hasStartDate = in_array('start_date', $columns, true); |
| 195 | 323 | |
| @@ -227,35 +355,35 @@ | ||
| 227 | 355 | $where[] = 'source = %s'; |
| 228 | 356 | $params[] = $filters['source']; |
| 229 | 357 | } |
| 230 | 358 | |
| 231 | - // Past/upcoming filter | |
| 359 | + // Past/upcoming filter. Never exclude past dates when the caller is asking | |
| 360 | + // for the 'past' status — that combination is contradictory and returned | |
| 361 | + // nothing (the exact reason completed departures vanished from the Past tab). | |
| 232 | 362 | if (isset($filters['include_past'])) { |
| 233 | - if (!$filters['include_past']) { | |
| 363 | + if (!$filters['include_past'] && !$statusIsPast) { | |
| 234 | 364 | $where[] = 'date >= CURDATE()'; |
| 235 | 365 | } |
| 236 | 366 | } |
| 237 | - | |
| 238 | - $query = "SELECT * FROM `{$table}` WHERE " . implode(' AND ', $where); | |
| 239 | - $query .= " ORDER BY date ASC, time ASC"; | |
| 240 | - | |
| 241 | - if (!empty($filters['per_page'])) { | |
| 242 | - $perPage = (int) $filters['per_page']; | |
| 243 | - $page = max(1, (int) ($filters['page'] ?? 1)); | |
| 244 | - $offset = ($page - 1) * $perPage; | |
| 245 | - $query .= " LIMIT %d OFFSET %d"; | |
| 246 | - $params[] = $perPage; | |
| 247 | - $params[] = $offset; | |
| 367 | + | |
| 368 | + // Past-only filter — for the "Past Departures" archive. Deliberately | |
| 369 | + // DATE-based (mirrors the end_date ?: start_date ?: date precedence used | |
| 370 | + // when marking departures past) rather than checking status = 'past': | |
| 371 | + // that stored status is only kept current by the daily cron, so relying | |
| 372 | + // on it made departures that had already taken place disappear entirely | |
| 373 | + // whenever the cron had not run. Date is the source of truth. | |
| 374 | + if (!empty($filters['past_only'])) { | |
| 375 | + // start_date / end_date are nullable DATE columns (never ''), so guard | |
| 376 | + // with IS NULL / IS NOT NULL — comparing a DATE column to '' errors | |
| 377 | + // under MySQL strict mode. | |
| 378 | + $where[] = "( | |
| 379 | + (end_date IS NOT NULL AND end_date < CURDATE()) | |
| 380 | + OR (end_date IS NULL AND start_date IS NOT NULL AND start_date < CURDATE()) | |
| 381 | + OR (end_date IS NULL AND start_date IS NULL AND date < CURDATE()) | |
| 382 | + )"; | |
| 248 | 383 | } |
| 249 | 384 | |
| 250 | - $results = $this->wpdb->get_results( | |
| 251 | - $this->wpdb->prepare($query, ...$params), | |
| 252 | - ARRAY_A | |
| 253 | - ); | |
| 254 | - | |
| 255 | - return array_map(function ($row) { | |
| 256 | - return Departure::fromArray($row); | |
| 257 | - }, $results ?: []); | |
| 385 | + return [$where, $params]; | |
| 258 | 386 | } |
| 259 | 387 | |
| 260 | 388 | /** |
| 261 | 389 | * Find past departures by trip ID |
| @@ -261,9 +389,12 @@ | ||
| 261 | 389 | * Find past departures by trip ID |
| 262 | 390 | */ |
| 263 | 391 | public function findPastByTripId(int $tripId, array $filters = []): array |
| 264 | 392 | { |
| 265 | - $filters['status'] = 'past'; | |
| 393 | + // Match by date, not the stored status column (see past_only in | |
| 394 | + // findByTripId) — a departure that has taken place must appear here even | |
| 395 | + // if the daily status cron has not yet marked it 'past'. | |
| 396 | + $filters['past_only'] = true; | |
| 266 | 397 | $filters['include_past'] = true; |
| 267 | 398 | return $this->findByTripId($tripId, $filters); |
| 268 | 399 | } |
| 269 | 400 | |
| @@ -284,42 +415,28 @@ | ||
| 284 | 415 | return $this->findByTripIdAndStartDate($tripId, $date, $time); |
| 285 | 416 | } |
| 286 | 417 | |
| 287 | 418 | /** |
| 288 | - * 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(). | |
| 289 | 430 | */ |
| 290 | 431 | public function countByTripId(int $tripId, array $filters = []): int |
| 291 | 432 | { |
| 292 | 433 | $table = esc_sql($this->table); |
| 293 | - $where = ['trip_id = %d']; | |
| 294 | - $params = [$tripId]; | |
| 295 | - | |
| 296 | - if (!empty($filters['status']) && $filters['status'] !== 'all') { | |
| 297 | - $where[] = 'status = %s'; | |
| 298 | - $params[] = $filters['status']; | |
| 299 | - } | |
| 300 | - | |
| 301 | - if (!empty($filters['date_from'])) { | |
| 302 | - $where[] = 'date >= %s'; | |
| 303 | - $params[] = $filters['date_from']; | |
| 304 | - } | |
| 305 | - | |
| 306 | - if (!empty($filters['date_to'])) { | |
| 307 | - $where[] = 'date <= %s'; | |
| 308 | - $params[] = $filters['date_to']; | |
| 309 | - } | |
| 310 | - | |
| 311 | - if (!empty($filters['source']) && $filters['source'] !== 'all') { | |
| 312 | - $where[] = 'source = %s'; | |
| 313 | - $params[] = $filters['source']; | |
| 314 | - } | |
| 315 | - | |
| 316 | - if (isset($filters['include_past']) && !$filters['include_past']) { | |
| 317 | - $where[] = 'date >= CURDATE()'; | |
| 318 | - } | |
| 319 | - | |
| 434 | + [$where, $params] = $this->whereForTrip($tripId, $filters); | |
| 435 | + | |
| 320 | 436 | $query = "SELECT COUNT(*) FROM `{$table}` WHERE " . implode(' AND ', $where); |
| 321 | - | |
| 437 | + | |
| 438 | + // trip_id is always bound, so there is always a placeholder to prepare. | |
| 322 | 439 | return (int) $this->wpdb->get_var($this->wpdb->prepare($query, ...$params)); |
| 323 | 440 | } |
| 324 | 441 | |
| 325 | 442 | /** |
| @@ -609,19 +726,28 @@ | ||
| 609 | 726 | { |
| 610 | 727 | $table = esc_sql($this->table); |
| 611 | 728 | $today = date('Y-m-d'); |
| 612 | 729 | |
| 613 | - // Update past departures - use end_date if available, otherwise start_date or date | |
| 730 | + // The effective date is end_date ?: start_date ?: date. start_date and | |
| 731 | + // end_date are nullable DATE columns (never ''), so guard with IS NULL / | |
| 732 | + // IS NOT NULL — comparing a DATE column to '' errors under MySQL strict | |
| 733 | + // mode, which previously made this whole recalculation fail silently. | |
| 734 | + $pastCond = "( | |
| 735 | + (end_date IS NOT NULL AND end_date < %s) OR | |
| 736 | + (end_date IS NULL AND start_date IS NOT NULL AND start_date < %s) OR | |
| 737 | + (end_date IS NULL AND start_date IS NULL AND date < %s) | |
| 738 | + )"; | |
| 739 | + $futureCond = "( | |
| 740 | + (end_date IS NOT NULL AND end_date >= CURDATE()) OR | |
| 741 | + (end_date IS NULL AND start_date IS NOT NULL AND start_date >= CURDATE()) OR | |
| 742 | + (end_date IS NULL AND start_date IS NULL AND date >= CURDATE()) | |
| 743 | + )"; | |
| 744 | + | |
| 745 | + // Update past departures. | |
| 614 | 746 | $this->wpdb->query($this->wpdb->prepare( |
| 615 | - "UPDATE `{$table}` | |
| 747 | + "UPDATE `{$table}` | |
| 616 | 748 | SET status = 'past', updated_at = %s |
| 617 | - WHERE ( | |
| 618 | - (end_date IS NOT NULL AND end_date != '' AND end_date < %s) OR | |
| 619 | - (end_date IS NULL OR end_date = '') AND ( | |
| 620 | - (start_date IS NOT NULL AND start_date != '' AND start_date < %s) OR | |
| 621 | - (start_date IS NULL OR start_date = '') AND date < %s | |
| 622 | - ) | |
| 623 | - ) | |
| 749 | + WHERE {$pastCond} | |
| 624 | 750 | AND status != 'cancelled'", |
| 625 | 751 | current_time('mysql'), |
| 626 | 752 | $today, |
| 627 | 753 | $today, |
| @@ -626,41 +752,120 @@ | ||
| 626 | 752 | $today, |
| 627 | 753 | $today, |
| 628 | 754 | $today |
| 629 | 755 | )); |
| 630 | - | |
| 631 | - // Update full departures - check future dates only | |
| 756 | + | |
| 757 | + // Update full departures - future dates only. | |
| 632 | 758 | $this->wpdb->query( |
| 633 | - "UPDATE `{$table}` | |
| 759 | + "UPDATE `{$table}` | |
| 634 | 760 | SET status = 'full', updated_at = NOW() |
| 635 | - WHERE booked_count >= max_capacity | |
| 636 | - AND max_capacity > 0 | |
| 637 | - AND ( | |
| 638 | - (end_date IS NOT NULL AND end_date != '' AND end_date >= CURDATE()) OR | |
| 639 | - (end_date IS NULL OR end_date = '') AND ( | |
| 640 | - (start_date IS NOT NULL AND start_date != '' AND start_date >= CURDATE()) OR | |
| 641 | - (start_date IS NULL OR start_date = '') AND date >= CURDATE() | |
| 642 | - ) | |
| 643 | - ) | |
| 761 | + WHERE booked_count >= max_capacity | |
| 762 | + AND max_capacity > 0 | |
| 763 | + AND {$futureCond} | |
| 644 | 764 | AND status NOT IN ('cancelled', 'past')" |
| 645 | 765 | ); |
| 646 | - | |
| 647 | - // Update upcoming departures - check future dates only | |
| 766 | + | |
| 767 | + // Update upcoming departures - future dates only. | |
| 648 | 768 | $this->wpdb->query( |
| 649 | - "UPDATE `{$table}` | |
| 769 | + "UPDATE `{$table}` | |
| 650 | 770 | SET status = 'upcoming', updated_at = NOW() |
| 651 | - WHERE ( | |
| 652 | - (end_date IS NOT NULL AND end_date != '' AND end_date >= CURDATE()) OR | |
| 653 | - (end_date IS NULL OR end_date = '') AND ( | |
| 654 | - (start_date IS NOT NULL AND start_date != '' AND start_date >= CURDATE()) OR | |
| 655 | - (start_date IS NULL OR start_date = '') AND date >= CURDATE() | |
| 656 | - ) | |
| 657 | - ) | |
| 771 | + WHERE {$futureCond} | |
| 658 | 772 | AND booked_count < max_capacity |
| 659 | 773 | AND status NOT IN ('cancelled', 'past', 'full')" |
| 660 | 774 | ); |
| 661 | 775 | |
| 662 | 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; | |
| 663 | 868 | } |
| 664 | 869 | |
| 665 | 870 | /** |
| 666 | 871 | * Check if table supports soft delete |
