| @@ -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,16 +265,14 @@ | ||
| 150 | 265 | $query .= " LIMIT %d OFFSET %d"; |
| 151 | 266 | $params[] = $perPage; |
| 152 | 267 | $params[] = $offset; |
| 153 | 268 | } |
| 154 | - | |
| 155 | - // Debug output | |
| 156 | - $prepared_query = $this->wpdb->prepare($query, ...$params); | |
| 157 | - $results = $this->wpdb->get_results($prepared_query, ARRAY_A); | |
| 158 | - | |
| 159 | - if ($this->wpdb->last_error) { | |
| 160 | - } | |
| 161 | - | |
| 269 | + | |
| 270 | + $results = $this->wpdb->get_results( | |
| 271 | + $this->wpdb->prepare($query, ...$params), | |
| 272 | + ARRAY_A | |
| 273 | + ); | |
| 274 | + | |
| 162 | 275 | return array_map(function ($row) { |
| 163 | 276 | return Departure::fromArray($row); |
| 164 | 277 | }, $results ?: []); |
| 165 | 278 | } |
| @@ -164,27 +277,47 @@ | ||
| 164 | 277 | }, $results ?: []); |
| 165 | 278 | } |
| 166 | 279 | |
| 167 | 280 | /** |
| 168 | - * Find departures by trip ID | |
| 169 | - * | |
| 170 | - * @param int $tripId Trip ID | |
| 171 | - * @param array $filters Filters: status, date_from, date_to, source | |
| 172 | - * @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} | |
| 173 | 289 | */ |
| 174 | - public function findByTripId(int $tripId, array $filters = []): array | |
| 290 | + private function whereForTrip(int $tripId, array $filters): array | |
| 175 | 291 | { |
| 176 | - | |
| 177 | 292 | $table = esc_sql($this->table); |
| 178 | 293 | $where = ['trip_id = %d']; |
| 179 | 294 | $params = [$tripId]; |
| 180 | 295 | |
| 181 | - // 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'); | |
| 182 | 302 | if (!empty($filters['status']) && $filters['status'] !== 'all') { |
| 183 | - $where[] = 'status = %s'; | |
| 184 | - $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 | + } | |
| 185 | 312 | } |
| 186 | - | |
| 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 | + | |
| 187 | 320 | // Date range filter - check both start_date and date columns |
| 188 | 321 | $columns = $this->wpdb->get_col("DESCRIBE {$table}"); |
| 189 | 322 | $hasStartDate = in_array('start_date', $columns, true); |
| 190 | 323 | |
| @@ -222,35 +355,35 @@ | ||
| 222 | 355 | $where[] = 'source = %s'; |
| 223 | 356 | $params[] = $filters['source']; |
| 224 | 357 | } |
| 225 | 358 | |
| 226 | - // 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). | |
| 227 | 362 | if (isset($filters['include_past'])) { |
| 228 | - if (!$filters['include_past']) { | |
| 363 | + if (!$filters['include_past'] && !$statusIsPast) { | |
| 229 | 364 | $where[] = 'date >= CURDATE()'; |
| 230 | 365 | } |
| 231 | 366 | } |
| 232 | - | |
| 233 | - $query = "SELECT * FROM `{$table}` WHERE " . implode(' AND ', $where); | |
| 234 | - $query .= " ORDER BY date ASC, time ASC"; | |
| 235 | - | |
| 236 | - if (!empty($filters['per_page'])) { | |
| 237 | - $perPage = (int) $filters['per_page']; | |
| 238 | - $page = max(1, (int) ($filters['page'] ?? 1)); | |
| 239 | - $offset = ($page - 1) * $perPage; | |
| 240 | - $query .= " LIMIT %d OFFSET %d"; | |
| 241 | - $params[] = $perPage; | |
| 242 | - $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 | + )"; | |
| 243 | 383 | } |
| 244 | 384 | |
| 245 | - $results = $this->wpdb->get_results( | |
| 246 | - $this->wpdb->prepare($query, ...$params), | |
| 247 | - ARRAY_A | |
| 248 | - ); | |
| 249 | - | |
| 250 | - return array_map(function ($row) { | |
| 251 | - return Departure::fromArray($row); | |
| 252 | - }, $results ?: []); | |
| 385 | + return [$where, $params]; | |
| 253 | 386 | } |
| 254 | 387 | |
| 255 | 388 | /** |
| 256 | 389 | * Find past departures by trip ID |
| @@ -256,9 +389,12 @@ | ||
| 256 | 389 | * Find past departures by trip ID |
| 257 | 390 | */ |
| 258 | 391 | public function findPastByTripId(int $tripId, array $filters = []): array |
| 259 | 392 | { |
| 260 | - $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; | |
| 261 | 397 | $filters['include_past'] = true; |
| 262 | 398 | return $this->findByTripId($tripId, $filters); |
| 263 | 399 | } |
| 264 | 400 | |
| @@ -279,42 +415,28 @@ | ||
| 279 | 415 | return $this->findByTripIdAndStartDate($tripId, $date, $time); |
| 280 | 416 | } |
| 281 | 417 | |
| 282 | 418 | /** |
| 283 | - * 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(). | |
| 284 | 430 | */ |
| 285 | 431 | public function countByTripId(int $tripId, array $filters = []): int |
| 286 | 432 | { |
| 287 | 433 | $table = esc_sql($this->table); |
| 288 | - $where = ['trip_id = %d']; | |
| 289 | - $params = [$tripId]; | |
| 290 | - | |
| 291 | - if (!empty($filters['status']) && $filters['status'] !== 'all') { | |
| 292 | - $where[] = 'status = %s'; | |
| 293 | - $params[] = $filters['status']; | |
| 294 | - } | |
| 295 | - | |
| 296 | - if (!empty($filters['date_from'])) { | |
| 297 | - $where[] = 'date >= %s'; | |
| 298 | - $params[] = $filters['date_from']; | |
| 299 | - } | |
| 300 | - | |
| 301 | - if (!empty($filters['date_to'])) { | |
| 302 | - $where[] = 'date <= %s'; | |
| 303 | - $params[] = $filters['date_to']; | |
| 304 | - } | |
| 305 | - | |
| 306 | - if (!empty($filters['source']) && $filters['source'] !== 'all') { | |
| 307 | - $where[] = 'source = %s'; | |
| 308 | - $params[] = $filters['source']; | |
| 309 | - } | |
| 310 | - | |
| 311 | - if (isset($filters['include_past']) && !$filters['include_past']) { | |
| 312 | - $where[] = 'date >= CURDATE()'; | |
| 313 | - } | |
| 314 | - | |
| 434 | + [$where, $params] = $this->whereForTrip($tripId, $filters); | |
| 435 | + | |
| 315 | 436 | $query = "SELECT COUNT(*) FROM `{$table}` WHERE " . implode(' AND ', $where); |
| 316 | - | |
| 437 | + | |
| 438 | + // trip_id is always bound, so there is always a placeholder to prepare. | |
| 317 | 439 | return (int) $this->wpdb->get_var($this->wpdb->prepare($query, ...$params)); |
| 318 | 440 | } |
| 319 | 441 | |
| 320 | 442 | /** |
| @@ -484,30 +606,68 @@ | ||
| 484 | 606 | ); |
| 485 | 607 | } |
| 486 | 608 | |
| 487 | 609 | /** |
| 488 | - * Increment booked count | |
| 610 | + * Atomically increment booked count, refusing the write when it | |
| 611 | + * would exceed max_capacity. | |
| 612 | + * | |
| 613 | + * The capacity guard lives in the SQL WHERE clause — not in PHP — | |
| 614 | + * so concurrent writers can't both read "we have room" and then | |
| 615 | + * both succeed. Each writer's UPDATE either updates 1 row (the | |
| 616 | + * reservation succeeded; capacity was decremented atomically) or | |
| 617 | + * 0 rows (the seats were taken between read and write; the caller | |
| 618 | + * should treat this as "departure full"). | |
| 619 | + * | |
| 620 | + * `max_capacity = 0` or NULL means "unlimited" — the guard | |
| 621 | + * intentionally allows unlimited writes in that case. | |
| 622 | + * | |
| 623 | + * Returns true only when 1 row was actually updated. Previous | |
| 624 | + * behaviour returned true unconditionally, which created a | |
| 625 | + * check-then-act overbooking race in `DepartureService:: | |
| 626 | + * incrementBookedCount()`. | |
| 489 | 627 | */ |
| 490 | - public function incrementBookedCount(int $id, int $amount = 1): bool | |
| 628 | + public function incrementBookedCount(int $id, int $amount = 1, bool $force = false): bool | |
| 491 | 629 | { |
| 630 | + if ($amount <= 0 || $id <= 0) return false; | |
| 631 | + | |
| 492 | 632 | $table = esc_sql($this->table); |
| 493 | - | |
| 494 | - $this->wpdb->query($this->wpdb->prepare( | |
| 495 | - "UPDATE `{$table}` | |
| 496 | - SET booked_count = booked_count + %d, | |
| 497 | - updated_at = %s | |
| 498 | - WHERE id = %d", | |
| 499 | - $amount, | |
| 500 | - current_time('mysql'), | |
| 501 | - $id | |
| 502 | - )); | |
| 503 | - | |
| 504 | - // Recalculate status | |
| 633 | + | |
| 634 | + // The capacity guard (`booked_count + %d <= max_capacity`) is | |
| 635 | + // the right default for direct bookings — it stops the website | |
| 636 | + // checkout from overselling a seat that's no longer there. | |
| 637 | + // | |
| 638 | + // For external-channel bookings (Viator / GetYourGuide / any | |
| 639 | + // OTA webhook) the seat has ALREADY been sold on the OTA. We | |
| 640 | + // MUST record the booking locally even if our view of capacity | |
| 641 | + // says "no room left" — refusing to record would just hide the | |
| 642 | + // oversell from the operator and make reconciliation impossible. | |
| 643 | + // Callers that own that case pass `$force = true` and the | |
| 644 | + // capacity clause is dropped from the WHERE. | |
| 645 | + $sql = "UPDATE `{$table}` | |
| 646 | + SET booked_count = booked_count + %d, | |
| 647 | + updated_at = %s | |
| 648 | + WHERE id = %d"; | |
| 649 | + $args = [$amount, current_time('mysql'), $id]; | |
| 650 | + | |
| 651 | + if (!$force) { | |
| 652 | + $sql .= " | |
| 653 | + AND (max_capacity IS NULL | |
| 654 | + OR max_capacity = 0 | |
| 655 | + OR booked_count + %d <= max_capacity)"; | |
| 656 | + $args[] = $amount; | |
| 657 | + } | |
| 658 | + | |
| 659 | + $result = $this->wpdb->query($this->wpdb->prepare($sql, $args)); | |
| 660 | + | |
| 661 | + if ($result === false) return false; // SQL error | |
| 662 | + if ((int) $result === 0) return false; // capacity guard rejected the write (only possible when !$force) | |
| 663 | + | |
| 664 | + // Recalculate status only when the reservation actually landed. | |
| 505 | 665 | $departure = $this->findModel($id); |
| 506 | 666 | if ($departure) { |
| 507 | 667 | $this->update($id, ['status' => $departure->calculateStatus()]); |
| 508 | 668 | } |
| 509 | - | |
| 669 | + | |
| 510 | 670 | return true; |
| 511 | 671 | } |
| 512 | 672 | |
| 513 | 673 | /** |
| @@ -536,27 +696,28 @@ | ||
| 536 | 696 | return true; |
| 537 | 697 | } |
| 538 | 698 | |
| 539 | 699 | /** |
| 540 | - * Delete a departure | |
| 541 | - * Only allowed if source is recurring_generated and booked_count is 0 | |
| 700 | + * Delete a departure. | |
| 701 | + * | |
| 702 | + * The booking-level policy lives in DepartureService::delete(), which is the | |
| 703 | + * only caller — this performs the row removal itself. | |
| 542 | 704 | */ |
| 543 | 705 | public function delete(int $id): bool |
| 544 | 706 | { |
| 545 | 707 | $departure = $this->findModel($id); |
| 546 | - | |
| 708 | + | |
| 547 | 709 | if (!$departure) { |
| 548 | 710 | return false; |
| 549 | 711 | } |
| 550 | - | |
| 551 | - // Only allow deletion of recurring_generated departures with no bookings | |
| 552 | - if ($departure->source === 'recurring_generated' && $departure->booked_count === 0) { | |
| 553 | - $table = esc_sql($this->table); | |
| 554 | - return (bool) $this->wpdb->delete($table, ['id' => $id], ['%d']); | |
| 555 | - } | |
| 556 | - | |
| 557 | - // Manual departures or departures with bookings cannot be deleted | |
| 558 | - return false; | |
| 712 | + | |
| 713 | + // This previously required source === 'recurring_generated', a value the | |
| 714 | + // plugin never writes (departures are `booking_created` or `manual`, see | |
| 715 | + // Departure::$source), so the guard could never pass and every departure | |
| 716 | + // was undeletable. | |
| 717 | + $table = esc_sql($this->table); | |
| 718 | + | |
| 719 | + return (bool) $this->wpdb->delete($table, ['id' => $id], ['%d']); | |
| 559 | 720 | } |
| 560 | 721 | |
| 561 | 722 | /** |
| 562 | 723 | * Recalculate status for all departures (for cron job) |
| @@ -565,19 +726,28 @@ | ||
| 565 | 726 | { |
| 566 | 727 | $table = esc_sql($this->table); |
| 567 | 728 | $today = date('Y-m-d'); |
| 568 | 729 | |
| 569 | - // 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. | |
| 570 | 746 | $this->wpdb->query($this->wpdb->prepare( |
| 571 | - "UPDATE `{$table}` | |
| 747 | + "UPDATE `{$table}` | |
| 572 | 748 | SET status = 'past', updated_at = %s |
| 573 | - WHERE ( | |
| 574 | - (end_date IS NOT NULL AND end_date != '' AND end_date < %s) OR | |
| 575 | - (end_date IS NULL OR end_date = '') AND ( | |
| 576 | - (start_date IS NOT NULL AND start_date != '' AND start_date < %s) OR | |
| 577 | - (start_date IS NULL OR start_date = '') AND date < %s | |
| 578 | - ) | |
| 579 | - ) | |
| 749 | + WHERE {$pastCond} | |
| 580 | 750 | AND status != 'cancelled'", |
| 581 | 751 | current_time('mysql'), |
| 582 | 752 | $today, |
| 583 | 753 | $today, |
| @@ -582,41 +752,120 @@ | ||
| 582 | 752 | $today, |
| 583 | 753 | $today, |
| 584 | 754 | $today |
| 585 | 755 | )); |
| 586 | - | |
| 587 | - // Update full departures - check future dates only | |
| 756 | + | |
| 757 | + // Update full departures - future dates only. | |
| 588 | 758 | $this->wpdb->query( |
| 589 | - "UPDATE `{$table}` | |
| 759 | + "UPDATE `{$table}` | |
| 590 | 760 | SET status = 'full', updated_at = NOW() |
| 591 | - WHERE booked_count >= max_capacity | |
| 592 | - AND max_capacity > 0 | |
| 593 | - AND ( | |
| 594 | - (end_date IS NOT NULL AND end_date != '' AND end_date >= CURDATE()) OR | |
| 595 | - (end_date IS NULL OR end_date = '') AND ( | |
| 596 | - (start_date IS NOT NULL AND start_date != '' AND start_date >= CURDATE()) OR | |
| 597 | - (start_date IS NULL OR start_date = '') AND date >= CURDATE() | |
| 598 | - ) | |
| 599 | - ) | |
| 761 | + WHERE booked_count >= max_capacity | |
| 762 | + AND max_capacity > 0 | |
| 763 | + AND {$futureCond} | |
| 600 | 764 | AND status NOT IN ('cancelled', 'past')" |
| 601 | 765 | ); |
| 602 | - | |
| 603 | - // Update upcoming departures - check future dates only | |
| 766 | + | |
| 767 | + // Update upcoming departures - future dates only. | |
| 604 | 768 | $this->wpdb->query( |
| 605 | - "UPDATE `{$table}` | |
| 769 | + "UPDATE `{$table}` | |
| 606 | 770 | SET status = 'upcoming', updated_at = NOW() |
| 607 | - WHERE ( | |
| 608 | - (end_date IS NOT NULL AND end_date != '' AND end_date >= CURDATE()) OR | |
| 609 | - (end_date IS NULL OR end_date = '') AND ( | |
| 610 | - (start_date IS NOT NULL AND start_date != '' AND start_date >= CURDATE()) OR | |
| 611 | - (start_date IS NULL OR start_date = '') AND date >= CURDATE() | |
| 612 | - ) | |
| 613 | - ) | |
| 771 | + WHERE {$futureCond} | |
| 614 | 772 | AND booked_count < max_capacity |
| 615 | 773 | AND status NOT IN ('cancelled', 'past', 'full')" |
| 616 | 774 | ); |
| 617 | 775 | |
| 618 | 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; | |
| 619 | 868 | } |
| 620 | 869 | |
| 621 | 870 | /** |
| 622 | 871 | * Check if table supports soft delete |
