| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentBooking\App\Services; |
| 4 |
|
| 5 |
use FluentBooking\App\Models\Booking; |
| 6 |
use FluentBooking\Framework\Support\Arr; |
| 7 |
|
| 8 |
/** |
| 9 |
* Aggregate queries over bookings, and the one definition of "bookings this |
| 10 |
* user is allowed to count". |
| 11 |
* |
| 12 |
* That second job is why this class exists. The dashboard held two different |
| 13 |
* answers to the same question: the widget numbers scoped on the |
| 14 |
* `fcal_booking_hosts` pivot, and the graph beneath them scoped on the |
| 15 |
* `host_user_id` column — so a limited host could read a smaller number from |
| 16 |
* the graph than from the widget directly above it. `scoped()` is now the |
| 17 |
* single answer for both, and it is the union of calendar ownership and host |
| 18 |
* membership (`Booking::whereHostAccess()`). |
| 19 |
* |
| 20 |
* The schedules list is NOT on it. `SchedulesController::buildSchedulesQuery()` |
| 21 |
* and `addCountsForFirstPage()` still filter on `host_user_id` alone, so a host |
| 22 |
* who owns a calendar but is not the named host on its bookings sees fewer rows |
| 23 |
* there than the widgets above now count. Moving that screen onto `scoped()` |
| 24 |
* would change a shipped list's contents, so it is left as a deliberate, |
| 25 |
* recorded divergence rather than folded in here. |
| 26 |
* |
| 27 |
* Aggregation is dimension-and-metric based rather than free-form: callers pick |
| 28 |
* from a fixed set of group-by dimensions and metrics, both of which map to |
| 29 |
* literal SQL fragments held in this file. No caller-supplied string ever |
| 30 |
* reaches the query. |
| 31 |
* |
| 32 |
* @since 2.2.6 |
| 33 |
*/ |
| 34 |
class BookingReportService |
| 35 |
{ |
| 36 |
/** |
| 37 |
* A year plus a day, so "the last 12 months" and "this calendar year" |
| 38 |
* both fit without the caller having to think about it. |
| 39 |
*/ |
| 40 |
const MAX_RANGE_DAYS = 366; |
| 41 |
|
| 42 |
/** |
| 43 |
* Ceiling on returned groups. A report is a summary; a caller that needs |
| 44 |
* every row wants the bookings list, not this. |
| 45 |
*/ |
| 46 |
const MAX_GROUPS = 200; |
| 47 |
|
| 48 |
/** |
| 49 |
* Group-by dimension => [SQL expression, result key]. `%offset%` is |
| 50 |
* replaced with an integer offset in seconds; see shiftedColumn(). |
| 51 |
* |
| 52 |
* @return array |
| 53 |
*/ |
| 54 |
public static function dimensions() |
| 55 |
{ |
| 56 |
return [ |
| 57 |
'status' => ['expr' => 'status', 'label' => __('Status', 'fluent-booking')], |
| 58 |
'event' => ['expr' => 'event_id', 'label' => __('Event type', 'fluent-booking')], |
| 59 |
'event_type' => ['expr' => 'event_type', 'label' => __('Event kind', 'fluent-booking')], |
| 60 |
'host' => ['expr' => 'host_user_id', 'label' => __('Host', 'fluent-booking')], |
| 61 |
'calendar' => ['expr' => 'calendar_id', 'label' => __('Calendar', 'fluent-booking')], |
| 62 |
'source' => ['expr' => 'source', 'label' => __('Source', 'fluent-booking')], |
| 63 |
'country' => ['expr' => 'country', 'label' => __('Country', 'fluent-booking')], |
| 64 |
'day' => ['expr' => 'DATE(%shifted%)', 'label' => __('Day', 'fluent-booking')], |
| 65 |
'month' => ['expr' => 'DATE_FORMAT(%shifted%, \'%Y-%m\')', 'label' => __('Month', 'fluent-booking')], |
| 66 |
'weekday' => ['expr' => 'DAYOFWEEK(%shifted%)', 'label' => __('Weekday', 'fluent-booking')], |
| 67 |
'hour' => ['expr' => 'HOUR(%shifted%)', 'label' => __('Hour', 'fluent-booking')], |
| 68 |
]; |
| 69 |
} |
| 70 |
|
| 71 |
/** |
| 72 |
* @return array |
| 73 |
*/ |
| 74 |
public static function metrics() |
| 75 |
{ |
| 76 |
return ['count', 'distinct_attendees', 'total_minutes', 'no_show_rate', 'cancellation_rate']; |
| 77 |
} |
| 78 |
|
| 79 |
/** |
| 80 |
* Which timestamp column a date range and the time dimensions read. |
| 81 |
* |
| 82 |
* @return array |
| 83 |
*/ |
| 84 |
public static function dateFields() |
| 85 |
{ |
| 86 |
return ['start_time', 'created_at', 'end_time']; |
| 87 |
} |
| 88 |
|
| 89 |
/** |
| 90 |
* Bookings the given user is allowed to see, or all of them when they hold |
| 91 |
* read-all-bookings. The canonical scope — do not reimplement it. |
| 92 |
* |
| 93 |
* @param int|null $userId Defaults to the current user. |
| 94 |
* |
| 95 |
* @return \FluentBooking\Framework\Database\Orm\Builder |
| 96 |
*/ |
| 97 |
public static function scoped($userId = null) |
| 98 |
{ |
| 99 |
$query = Booking::query(); |
| 100 |
|
| 101 |
if (PermissionManager::userCanSeeAllBookings()) { |
| 102 |
return $query; |
| 103 |
} |
| 104 |
|
| 105 |
$userId = $userId === null ? get_current_user_id() : (int) $userId; |
| 106 |
|
| 107 |
return $query->whereHostAccess($userId); |
| 108 |
} |
| 109 |
|
| 110 |
/** |
| 111 |
* Run an aggregate query. |
| 112 |
* |
| 113 |
* @param array $args { |
| 114 |
* @type array $group_by Dimension keys, in order. At most 2. |
| 115 |
* @type array $metrics Metric keys. Defaults to ['count']. |
| 116 |
* @type string $date_field Which timestamp the range and the day/hour |
| 117 |
* dimensions read. Defaults to 'start_time'. |
| 118 |
* @type string $from Y-m-d, inclusive. |
| 119 |
* @type string $to Y-m-d, inclusive. |
| 120 |
* @type string $timezone IANA zone the day/weekday/hour buckets are |
| 121 |
* expressed in. Defaults to the site zone. |
| 122 |
* @type array $filters Optional equality filters: status[], |
| 123 |
* event_id, calendar_id, host_id, event_type, |
| 124 |
* source. |
| 125 |
* @type array $having ['metric' => …, 'op' => …, 'value' => …]. |
| 126 |
* @type string $order_by A metric key, or a dimension key. |
| 127 |
* @type string $order 'asc'|'desc'. |
| 128 |
* @type int $limit |
| 129 |
* } |
| 130 |
* |
| 131 |
* @return array|\WP_Error |
| 132 |
*/ |
| 133 |
public static function aggregate($args = []) |
| 134 |
{ |
| 135 |
$groupBy = array_values(array_filter((array) Arr::get($args, 'group_by', []))); |
| 136 |
$metrics = array_values(array_filter((array) Arr::get($args, 'metrics', []))); |
| 137 |
|
| 138 |
if (!$metrics) { |
| 139 |
$metrics = ['count']; |
| 140 |
} |
| 141 |
|
| 142 |
$dimensions = self::dimensions(); |
| 143 |
|
| 144 |
$unknownDims = array_diff($groupBy, array_keys($dimensions)); |
| 145 |
|
| 146 |
if ($unknownDims) { |
| 147 |
return new \WP_Error('invalid_group_by', sprintf( |
| 148 |
/* translators: %1$s: rejected dimension names, %2$s: accepted dimension names */ |
| 149 |
__('Unknown group_by: %1$s. Available: %2$s.', 'fluent-booking'), |
| 150 |
implode(', ', $unknownDims), |
| 151 |
implode(', ', array_keys($dimensions)) |
| 152 |
)); |
| 153 |
} |
| 154 |
|
| 155 |
$unknownMetrics = array_diff($metrics, self::metrics()); |
| 156 |
|
| 157 |
if ($unknownMetrics) { |
| 158 |
return new \WP_Error('invalid_metric', sprintf( |
| 159 |
/* translators: %1$s: rejected metric names, %2$s: accepted metric names */ |
| 160 |
__('Unknown metrics: %1$s. Available: %2$s.', 'fluent-booking'), |
| 161 |
implode(', ', $unknownMetrics), |
| 162 |
implode(', ', self::metrics()) |
| 163 |
)); |
| 164 |
} |
| 165 |
|
| 166 |
// Two dimensions already produce a cross-product; a third turns a |
| 167 |
// summary back into a row dump, which is what this tool exists to avoid. |
| 168 |
if (count($groupBy) > 2) { |
| 169 |
return new \WP_Error('too_many_dimensions', __('Group by at most two dimensions.', 'fluent-booking')); |
| 170 |
} |
| 171 |
|
| 172 |
$dateField = Arr::get($args, 'date_field', 'start_time'); |
| 173 |
|
| 174 |
if (!in_array($dateField, self::dateFields(), true)) { |
| 175 |
return new \WP_Error('invalid_date_field', sprintf( |
| 176 |
/* translators: %s: accepted date field names */ |
| 177 |
__('date_field must be one of: %s.', 'fluent-booking'), |
| 178 |
implode(', ', self::dateFields()) |
| 179 |
)); |
| 180 |
} |
| 181 |
|
| 182 |
$range = self::resolveRange(Arr::get($args, 'from'), Arr::get($args, 'to')); |
| 183 |
|
| 184 |
if (is_wp_error($range)) { |
| 185 |
return $range; |
| 186 |
} |
| 187 |
|
| 188 |
$timezone = Arr::get($args, 'timezone') ?: DateTimeHelper::getTimeZone(); |
| 189 |
$offset = self::offsetSeconds($timezone, $range['from']); |
| 190 |
|
| 191 |
$query = self::scoped(); |
| 192 |
|
| 193 |
// Both bounds describe a LOCAL window, so both are converted from local |
| 194 |
// to UTC — and each with its OWN offset, not the range's opening one. |
| 195 |
// Filtering on unshifted UTC while grouping on shifted local made the |
| 196 |
// first and last bucket of every report partial by the size of the |
| 197 |
// offset; using one offset for both bounds then reintroduces the same |
| 198 |
// error, an hour wide, on any range that crosses a DST change (a March |
| 199 |
// report for America/New_York would read its final day at -05:00 when |
| 200 |
// that day is actually -04:00, and swallow the first hour of April). |
| 201 |
$query->whereBetween($dateField, [ |
| 202 |
self::localToUtc($range['from'] . ' 00:00:00', $timezone), |
| 203 |
self::localToUtc($range['to'] . ' 23:59:59', $timezone), |
| 204 |
]); |
| 205 |
|
| 206 |
$applied = self::applyFilters($query, (array) Arr::get($args, 'filters', [])); |
| 207 |
|
| 208 |
if (is_wp_error($applied)) { |
| 209 |
return $applied; |
| 210 |
} |
| 211 |
|
| 212 |
$selects = []; |
| 213 |
$groups = []; |
| 214 |
|
| 215 |
foreach ($groupBy as $i => $key) { |
| 216 |
$expr = self::resolveExpression($dimensions[$key]['expr'], $dateField, $offset); |
| 217 |
$alias = 'dim_' . $i; |
| 218 |
$selects[] = $expr . ' as ' . $alias; |
| 219 |
$groups[] = $alias; |
| 220 |
} |
| 221 |
|
| 222 |
// COUNT(DISTINCT email) builds a distinct set over the whole range and |
| 223 |
// each SUM adds per-row work, so a count-only report selects neither. |
| 224 |
$orderMetric = (string) Arr::get($args, 'order_by', ''); |
| 225 |
|
| 226 |
// Always: resolveOrder falls back to it when no order is named. |
| 227 |
$selects[] = 'COUNT(*) as m_count'; |
| 228 |
|
| 229 |
if (in_array('distinct_attendees', $metrics, true) || $orderMetric === 'distinct_attendees') { |
| 230 |
$selects[] = 'COUNT(DISTINCT email) as m_distinct_attendees'; |
| 231 |
} |
| 232 |
|
| 233 |
if (in_array('total_minutes', $metrics, true) || $orderMetric === 'total_minutes') { |
| 234 |
$selects[] = 'SUM(slot_minutes) as m_total_minutes'; |
| 235 |
} |
| 236 |
|
| 237 |
if (in_array('no_show_rate', $metrics, true)) { |
| 238 |
$selects[] = "SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) as m_no_show"; |
| 239 |
} |
| 240 |
|
| 241 |
if (in_array('cancellation_rate', $metrics, true)) { |
| 242 |
$selects[] = "SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as m_cancelled"; |
| 243 |
} |
| 244 |
|
| 245 |
if (self::wantsRate($metrics)) { |
| 246 |
// Rate denominator. `reserved` rows are checkout placeholders for |
| 247 |
// payments that were never completed — counting them as bookings |
| 248 |
// deflates every rate by however many people abandoned a payment form. |
| 249 |
$selects[] = "SUM(CASE WHEN status != 'reserved' THEN 1 ELSE 0 END) as m_real"; |
| 250 |
} |
| 251 |
|
| 252 |
$query->selectRaw(implode(', ', $selects)); |
| 253 |
|
| 254 |
foreach ($groups as $group) { |
| 255 |
$query->groupBy($group); |
| 256 |
} |
| 257 |
|
| 258 |
$having = self::resolveHaving(Arr::get($args, 'having')); |
| 259 |
|
| 260 |
if (is_wp_error($having)) { |
| 261 |
return $having; |
| 262 |
} |
| 263 |
|
| 264 |
if ($having) { |
| 265 |
$query->havingRaw($having); |
| 266 |
} |
| 267 |
|
| 268 |
$query->orderByRaw(self::resolveOrder($groupBy, $metrics, $args)); |
| 269 |
|
| 270 |
$limit = (int) Arr::get($args, 'limit', 50); |
| 271 |
$limit = max(1, min($limit, self::MAX_GROUPS)); |
| 272 |
|
| 273 |
// Fetch one past the limit so the caller can be told the list was cut |
| 274 |
// rather than reading a truncated report as a complete one. |
| 275 |
$rows = $query->limit($limit + 1)->get(); |
| 276 |
|
| 277 |
$truncated = count($rows) > $limit; |
| 278 |
|
| 279 |
if ($truncated) { |
| 280 |
$rows = array_slice(is_array($rows) ? $rows : $rows->all(), 0, $limit); |
| 281 |
} |
| 282 |
|
| 283 |
return [ |
| 284 |
'rows' => self::formatRows($rows, $groupBy, $metrics), |
| 285 |
'group_by' => $groupBy, |
| 286 |
'metrics' => $metrics, |
| 287 |
'date_field' => $dateField, |
| 288 |
'range' => $range, |
| 289 |
'timezone' => $timezone, |
| 290 |
'truncated' => $truncated, |
| 291 |
'limit' => $limit, |
| 292 |
]; |
| 293 |
} |
| 294 |
|
| 295 |
/** |
| 296 |
* @return array |
| 297 |
*/ |
| 298 |
/** |
| 299 |
* @return bool |
| 300 |
*/ |
| 301 |
private static function wantsRate($metrics) |
| 302 |
{ |
| 303 |
return (bool) array_intersect($metrics, ['no_show_rate', 'cancellation_rate']); |
| 304 |
} |
| 305 |
|
| 306 |
private static function formatRows($rows, $groupBy, $metrics) |
| 307 |
{ |
| 308 |
$out = []; |
| 309 |
$wantsRate = self::wantsRate($metrics); |
| 310 |
|
| 311 |
foreach ($rows as $row) { |
| 312 |
$entry = []; |
| 313 |
|
| 314 |
foreach ($groupBy as $i => $key) { |
| 315 |
$value = $row->{'dim_' . $i}; |
| 316 |
|
| 317 |
// Never drop a null bucket silently. `country` is only |
| 318 |
// populated behind Cloudflare, so a report that omitted the |
| 319 |
// blanks would read as "everyone is in Germany". |
| 320 |
$entry[$key] = $value === null || $value === '' ? '(unknown)' : $value; |
| 321 |
} |
| 322 |
|
| 323 |
$count = (int) $row->m_count; |
| 324 |
$rateBase = $wantsRate ? (int) $row->m_real : 0; |
| 325 |
|
| 326 |
foreach ($metrics as $metric) { |
| 327 |
if ($metric === 'count') { |
| 328 |
$entry['count'] = $count; |
| 329 |
} elseif ($metric === 'distinct_attendees') { |
| 330 |
$entry['distinct_attendees'] = (int) $row->m_distinct_attendees; |
| 331 |
} elseif ($metric === 'total_minutes') { |
| 332 |
$entry['total_minutes'] = (int) $row->m_total_minutes; |
| 333 |
} elseif ($metric === 'no_show_rate') { |
| 334 |
$entry['no_show_rate'] = $rateBase ? round((int) $row->m_no_show / $rateBase, 4) : 0; |
| 335 |
} elseif ($metric === 'cancellation_rate') { |
| 336 |
$entry['cancellation_rate'] = $rateBase ? round((int) $row->m_cancelled / $rateBase, 4) : 0; |
| 337 |
} |
| 338 |
} |
| 339 |
|
| 340 |
$out[] = $entry; |
| 341 |
} |
| 342 |
|
| 343 |
return $out; |
| 344 |
} |
| 345 |
|
| 346 |
/** |
| 347 |
* Equality filters, each one whitelisted. |
| 348 |
* |
| 349 |
* @return true|\WP_Error |
| 350 |
*/ |
| 351 |
private static function applyFilters($query, $filters) |
| 352 |
{ |
| 353 |
$allowed = ['status', 'event_id', 'calendar_id', 'host_id', 'event_type', 'source']; |
| 354 |
|
| 355 |
$unknown = array_diff(array_keys($filters), $allowed); |
| 356 |
|
| 357 |
if ($unknown) { |
| 358 |
return new \WP_Error('invalid_filter', sprintf( |
| 359 |
/* translators: %1$s: rejected filter names, %2$s: accepted filter names */ |
| 360 |
__('Unknown filters: %1$s. Available: %2$s.', 'fluent-booking'), |
| 361 |
implode(', ', $unknown), |
| 362 |
implode(', ', $allowed) |
| 363 |
)); |
| 364 |
} |
| 365 |
|
| 366 |
if ($statuses = array_filter((array) Arr::get($filters, 'status', []))) { |
| 367 |
$query->whereIn('status', array_map('sanitize_text_field', $statuses)); |
| 368 |
} |
| 369 |
|
| 370 |
// array_key_exists, not truthiness: `host_id: 0` and `source: "0"` are |
| 371 |
// filters the caller asked for, and silently dropping them returns the |
| 372 |
// whole unfiltered set under a heading that says otherwise. |
| 373 |
foreach (['event_id' => 'event_id', 'calendar_id' => 'calendar_id', 'host_id' => 'host_user_id'] as $key => $column) { |
| 374 |
if (array_key_exists($key, $filters) && $filters[$key] !== null && $filters[$key] !== '') { |
| 375 |
$query->where($column, (int) $filters[$key]); |
| 376 |
} |
| 377 |
} |
| 378 |
|
| 379 |
foreach (['event_type', 'source'] as $key) { |
| 380 |
if (array_key_exists($key, $filters) && $filters[$key] !== null && $filters[$key] !== '') { |
| 381 |
$query->where($key, sanitize_text_field($filters[$key])); |
| 382 |
} |
| 383 |
} |
| 384 |
|
| 385 |
return true; |
| 386 |
} |
| 387 |
|
| 388 |
/** |
| 389 |
* @return string|\WP_Error '' when there is no having clause. |
| 390 |
*/ |
| 391 |
private static function resolveHaving($having) |
| 392 |
{ |
| 393 |
if (!$having || !is_array($having)) { |
| 394 |
return ''; |
| 395 |
} |
| 396 |
|
| 397 |
$columns = [ |
| 398 |
'count' => 'COUNT(*)', |
| 399 |
'distinct_attendees' => 'COUNT(DISTINCT email)', |
| 400 |
'total_minutes' => 'SUM(slot_minutes)', |
| 401 |
]; |
| 402 |
|
| 403 |
$metric = Arr::get($having, 'metric', 'count'); |
| 404 |
|
| 405 |
if (!isset($columns[$metric])) { |
| 406 |
return new \WP_Error('invalid_having', sprintf( |
| 407 |
/* translators: %s: accepted having metrics */ |
| 408 |
__('having.metric must be one of: %s.', 'fluent-booking'), |
| 409 |
implode(', ', array_keys($columns)) |
| 410 |
)); |
| 411 |
} |
| 412 |
|
| 413 |
$operators = ['>=' => '>=', '>' => '>', '<=' => '<=', '<' => '<', '=' => '=']; |
| 414 |
$op = Arr::get($having, 'op', '>='); |
| 415 |
|
| 416 |
if (!isset($operators[$op])) { |
| 417 |
return new \WP_Error('invalid_having', __('having.op must be one of: >=, >, <=, <, =.', 'fluent-booking')); |
| 418 |
} |
| 419 |
|
| 420 |
// Required, not defaulted to 0: that builds `COUNT(*) >= 0`, so a |
| 421 |
// wrong-shaped having returns everything as though it had filtered. |
| 422 |
if (!is_numeric(Arr::get($having, 'value'))) { |
| 423 |
return new \WP_Error('invalid_having', __('having.value is required and must be a number, e.g. {"metric":"count","op":">=","value":5}.', 'fluent-booking')); |
| 424 |
} |
| 425 |
|
| 426 |
// Every part of this string is a literal from the maps above except the |
| 427 |
// value, which is cast to an integer. |
| 428 |
return $columns[$metric] . ' ' . $operators[$op] . ' ' . (int) Arr::get($having, 'value', 0); |
| 429 |
} |
| 430 |
|
| 431 |
/** |
| 432 |
* @return string |
| 433 |
*/ |
| 434 |
private static function resolveOrder($groupBy, $metrics, $args) |
| 435 |
{ |
| 436 |
$columns = [ |
| 437 |
'count' => 'm_count', |
| 438 |
'distinct_attendees' => 'm_distinct_attendees', |
| 439 |
'total_minutes' => 'm_total_minutes', |
| 440 |
]; |
| 441 |
|
| 442 |
$requested = Arr::get($args, 'order_by', ''); |
| 443 |
$direction = strtolower(Arr::get($args, 'order', '')) === 'asc' ? 'ASC' : 'DESC'; |
| 444 |
|
| 445 |
if (isset($columns[$requested])) { |
| 446 |
return self::tieBreak($columns[$requested] . ' ' . $direction, $groupBy); |
| 447 |
} |
| 448 |
|
| 449 |
$dimensionIndex = array_search($requested, $groupBy, true); |
| 450 |
|
| 451 |
if ($dimensionIndex !== false) { |
| 452 |
return self::tieBreak('dim_' . (int) $dimensionIndex . ' ' . $direction, $groupBy); |
| 453 |
} |
| 454 |
|
| 455 |
// Time series read as a series; everything else reads as a ranking. |
| 456 |
$timeDimensions = ['day', 'month', 'weekday', 'hour']; |
| 457 |
|
| 458 |
if ($groupBy && in_array($groupBy[0], $timeDimensions, true)) { |
| 459 |
return self::tieBreak('dim_0 ASC', $groupBy); |
| 460 |
} |
| 461 |
|
| 462 |
return self::tieBreak('m_count DESC', $groupBy); |
| 463 |
} |
| 464 |
|
| 465 |
/** |
| 466 |
* Settle equal sort keys, so tied rows do not reorder with the plan. |
| 467 |
* |
| 468 |
* @return string |
| 469 |
*/ |
| 470 |
private static function tieBreak($order, $groupBy) |
| 471 |
{ |
| 472 |
$parts = [$order]; |
| 473 |
|
| 474 |
foreach (array_keys($groupBy) as $i) { |
| 475 |
$alias = 'dim_' . (int) $i; |
| 476 |
|
| 477 |
if (strpos($order, $alias . ' ') !== 0) { |
| 478 |
$parts[] = $alias . ' ASC'; |
| 479 |
} |
| 480 |
} |
| 481 |
|
| 482 |
return implode(', ', $parts); |
| 483 |
} |
| 484 |
|
| 485 |
/** |
| 486 |
* @return string |
| 487 |
*/ |
| 488 |
private static function resolveExpression($expr, $dateField, $offset) |
| 489 |
{ |
| 490 |
if (strpos($expr, '%shifted%') === false) { |
| 491 |
return $expr; |
| 492 |
} |
| 493 |
|
| 494 |
return str_replace('%shifted%', self::shiftedColumn($dateField, $offset), $expr); |
| 495 |
} |
| 496 |
|
| 497 |
/** |
| 498 |
* One local wall-clock instant expressed in UTC, using the offset in force |
| 499 |
* at that instant rather than a fixed one. |
| 500 |
* |
| 501 |
* The range bounds get this treatment individually because a range can cross |
| 502 |
* a daylight-saving change: applying the offset from the range's opening day |
| 503 |
* to its closing day reads a March 31st in America/New_York at -05:00 when |
| 504 |
* it is actually -04:00, and quietly pulls in the first hour of April. |
| 505 |
* |
| 506 |
* @param string $localDateTime 'Y-m-d H:i:s' |
| 507 |
* @param string $timezone |
| 508 |
* |
| 509 |
* @return string 'Y-m-d H:i:s' in UTC |
| 510 |
*/ |
| 511 |
private static function localToUtc($localDateTime, $timezone) |
| 512 |
{ |
| 513 |
try { |
| 514 |
$local = new \DateTime($localDateTime, new \DateTimeZone($timezone)); |
| 515 |
$local->setTimezone(new \DateTimeZone('UTC')); |
| 516 |
|
| 517 |
return $local->format('Y-m-d H:i:s'); |
| 518 |
} catch (\Exception $e) { |
| 519 |
return $localDateTime; |
| 520 |
} |
| 521 |
} |
| 522 |
|
| 523 |
/** |
| 524 |
* Times are stored in UTC. Grouping them by day or hour without shifting |
| 525 |
* would put an 11pm booking in Berlin on the wrong date — the exact class |
| 526 |
* of error this project keeps guarding against. CONVERT_TZ is not usable |
| 527 |
* because it needs MySQL's timezone tables loaded, which most hosts do not |
| 528 |
* do, so the offset is computed in PHP and applied as a fixed interval. |
| 529 |
* |
| 530 |
* @param string $dateField Whitelisted column name. |
| 531 |
* @param int $offset Seconds, already cast. |
| 532 |
* |
| 533 |
* @return string |
| 534 |
*/ |
| 535 |
private static function shiftedColumn($dateField, $offset) |
| 536 |
{ |
| 537 |
if (!$offset) { |
| 538 |
return $dateField; |
| 539 |
} |
| 540 |
|
| 541 |
return 'DATE_ADD(' . $dateField . ', INTERVAL ' . (int) $offset . ' SECOND)'; |
| 542 |
} |
| 543 |
|
| 544 |
/** |
| 545 |
* The zone's offset at the start of the range. A range that crosses a DST |
| 546 |
* boundary uses one offset throughout, so bookings on the far side can land |
| 547 |
* an hour out; the caller is told which offset was applied. |
| 548 |
* |
| 549 |
* @return int |
| 550 |
*/ |
| 551 |
public static function offsetSeconds($timezone, $onDate) |
| 552 |
{ |
| 553 |
try { |
| 554 |
$zone = new \DateTimeZone($timezone); |
| 555 |
$when = new \DateTime($onDate . ' 12:00:00', new \DateTimeZone('UTC')); |
| 556 |
|
| 557 |
return $zone->getOffset($when); |
| 558 |
} catch (\Exception $e) { |
| 559 |
return 0; |
| 560 |
} |
| 561 |
} |
| 562 |
|
| 563 |
/** |
| 564 |
* @return array|\WP_Error |
| 565 |
*/ |
| 566 |
public static function resolveRange($from, $to) |
| 567 |
{ |
| 568 |
$to = $to ? sanitize_text_field($to) : gmdate('Y-m-d'); |
| 569 |
$from = $from ? sanitize_text_field($from) : gmdate('Y-m-d', strtotime($to . ' -29 days')); |
| 570 |
|
| 571 |
foreach ([$from, $to] as $date) { |
| 572 |
// checkdate() as well as the shape: 2026-02-30 matches the pattern |
| 573 |
// and strtotime() then rolls it forward to March 2 silently. |
| 574 |
if (!preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $date, $parts) |
| 575 |
|| !checkdate((int) $parts[2], (int) $parts[3], (int) $parts[1])) { |
| 576 |
return new \WP_Error( |
| 577 |
'invalid_range', |
| 578 |
__('from and to must be real dates formatted Y-m-d.', 'fluent-booking'), |
| 579 |
['received' => $date] |
| 580 |
); |
| 581 |
} |
| 582 |
} |
| 583 |
|
| 584 |
if ($from > $to) { |
| 585 |
return new \WP_Error('invalid_range', __('from must not be later than to.', 'fluent-booking')); |
| 586 |
} |
| 587 |
|
| 588 |
$days = (int) floor((strtotime($to) - strtotime($from)) / DAY_IN_SECONDS) + 1; |
| 589 |
|
| 590 |
if ($days > self::MAX_RANGE_DAYS) { |
| 591 |
return new \WP_Error('range_too_large', sprintf( |
| 592 |
/* translators: %1$d: requested number of days, %2$d: maximum */ |
| 593 |
__('That range is %1$d days; the maximum is %2$d. Narrow it, or group by month.', 'fluent-booking'), |
| 594 |
$days, |
| 595 |
self::MAX_RANGE_DAYS |
| 596 |
)); |
| 597 |
} |
| 598 |
|
| 599 |
return ['from' => $from, 'to' => $to, 'days' => $days]; |
| 600 |
} |
| 601 |
} |
| 602 |
|
