0001-api-forbid-overlapping-events-booking-64383.patch
chrono/agendas/models.py | ||
---|---|---|
36 | 36 |
from django.core.exceptions import FieldDoesNotExist, ValidationError |
37 | 37 |
from django.core.validators import MaxValueValidator, MinValueValidator |
38 | 38 |
from django.db import connection, models, transaction |
39 |
from django.db.models import Count, Exists, F, Func, Max, OuterRef, Prefetch, Q
|
|
40 |
from django.db.models.functions import Cast, Coalesce, ExtractWeek |
|
39 |
from django.db.models import Count, Exists, ExpressionWrapper, F, Func, Max, OuterRef, Prefetch, Q, Value
|
|
40 |
from django.db.models.functions import Cast, Coalesce, Concat, ExtractWeek
|
|
41 | 41 |
from django.template import Context, Template, TemplateSyntaxError, VariableDoesNotExist, engines |
42 | 42 |
from django.urls import reverse |
43 | 43 |
from django.utils import functional |
... | ... | |
55 | 55 | |
56 | 56 |
from chrono.interval import Interval, IntervalSet |
57 | 57 |
from chrono.utils.date import get_weekday_index |
58 |
from chrono.utils.db import SumCardinality |
|
58 |
from chrono.utils.db import ArraySubquery, SumCardinality
|
|
59 | 59 |
from chrono.utils.publik_urls import translate_from_publik_url |
60 | 60 |
from chrono.utils.requests_wrapper import requests as requests_wrapper |
61 | 61 | |
... | ... | |
1563 | 1563 |
) |
1564 | 1564 |
return qs |
1565 | 1565 | |
1566 |
@staticmethod |
|
1567 |
def annotate_queryset_with_overlaps(qs): |
|
1568 |
qs = qs.annotate( |
|
1569 |
computed_end_datetime=ExpressionWrapper( |
|
1570 |
F('start_datetime') + datetime.timedelta(minutes=1) * F('duration'), |
|
1571 |
output_field=models.DateTimeField(), |
|
1572 |
), |
|
1573 |
computed_slug=Concat('agenda__slug', Value('@'), 'slug'), |
|
1574 |
) |
|
1575 | ||
1576 |
overlapping_events = qs.filter( |
|
1577 |
start_datetime__lt=OuterRef('computed_end_datetime'), |
|
1578 |
computed_end_datetime__gt=OuterRef('start_datetime'), |
|
1579 |
).exclude(pk=OuterRef('pk')) |
|
1580 | ||
1581 |
return qs.annotate( |
|
1582 |
overlaps=ArraySubquery( |
|
1583 |
overlapping_events.values('computed_slug'), |
|
1584 |
output_field=ArrayField(models.CharField()), |
|
1585 |
), |
|
1586 |
has_overlap=Exists(overlapping_events), |
|
1587 |
) |
|
1588 | ||
1566 | 1589 |
@property |
1567 | 1590 |
def remaining_places(self): |
1568 | 1591 |
return max(0, self.places - self.booked_places) |
chrono/api/views.py | ||
---|---|---|
590 | 590 |
details['status'] = 'cancelled' |
591 | 591 |
else: |
592 | 592 |
details['status'] = 'free' |
593 |
if hasattr(event, 'overlaps'): |
|
594 |
details['overlaps'] = event.overlaps |
|
593 | 595 | |
594 | 596 |
return details |
595 | 597 | |
... | ... | |
920 | 922 |
show_out_of_minimal_delay=show_past_events, |
921 | 923 |
) |
922 | 924 |
entries = Event.annotate_queryset_for_user(entries, user_external_id, with_status=with_status) |
925 |
entries = Event.annotate_queryset_with_overlaps(entries) |
|
923 | 926 |
if show_only_subscribed: |
924 | 927 |
entries = entries.filter( |
925 | 928 |
agenda__subscriptions__user_external_id=user_external_id, |
... | ... | |
1840 | 1843 | |
1841 | 1844 |
events = self.get_events(request, payload) |
1842 | 1845 | |
1846 |
overlapping_events = Event.annotate_queryset_with_overlaps(events).filter(has_overlap=True) |
|
1847 |
if overlapping_events: |
|
1848 |
raise APIError( |
|
1849 |
N_('Some events occur at the same time: %s'), |
|
1850 |
', '.join(sorted(str(x) for x in overlapping_events)), |
|
1851 |
) |
|
1852 | ||
1843 | 1853 |
already_booked_events = self.get_already_booked_events(user_external_id) |
1844 | 1854 |
already_booked_events = already_booked_events.filter(start_datetime__gt=now()) |
1845 | 1855 |
if start_datetime: |
... | ... | |
1871 | 1881 |
booking__user_external_id=user_external_id, |
1872 | 1882 |
booking__cancellation_datetime__isnull=False, |
1873 | 1883 |
) |
1884 | ||
1874 | 1885 |
# book only events without active booking for the user |
1875 | 1886 |
events = events.exclude( |
1876 | 1887 |
pk__in=Booking.objects.filter( |
chrono/utils/db.py | ||
---|---|---|
15 | 15 |
# along with this program. If not, see <http://www.gnu.org/licenses/>. |
16 | 16 | |
17 | 17 |
from django.db.migrations.operations.base import Operation |
18 |
from django.db.models import Aggregate |
|
18 |
from django.db.models import Aggregate, Subquery
|
|
19 | 19 | |
20 | 20 | |
21 | 21 |
class SumCardinality(Aggregate): |
22 | 22 |
template = 'SUM(CARDINALITY(%(expressions)s))' |
23 | 23 | |
24 | 24 | |
25 |
class ArraySubquery(Subquery): |
|
26 |
''' |
|
27 |
Available in Django 4.0 |
|
28 |
https://docs.djangoproject.com/en/4.0/ref/contrib/postgres/expressions/#arraysubquery-expressions |
|
29 |
''' |
|
30 | ||
31 |
template = 'ARRAY(%(subquery)s)' |
|
32 | ||
33 | ||
25 | 34 |
class EnsureJsonbType(Operation): |
26 | 35 | |
27 | 36 |
reversible = True |
tests/api/datetimes/test_events_multiple_agendas.py | ||
---|---|---|
1330 | 1330 |
status=400, |
1331 | 1331 |
) |
1332 | 1332 |
assert 'required' in resp.json['errors']['user_external_id'][0] |
1333 | ||
1334 | ||
1335 |
@pytest.mark.freeze_time('2021-09-06 12:00') |
|
1336 |
def test_datetimes_multiple_agendas_overlapping_events(app): |
|
1337 |
agenda = Agenda.objects.create(label='Foo bar', kind='events') |
|
1338 |
Event.objects.create( |
|
1339 |
label='Event', |
|
1340 |
start_datetime=now() + datetime.timedelta(days=5), |
|
1341 |
duration=120, |
|
1342 |
places=5, |
|
1343 |
agenda=agenda, |
|
1344 |
) |
|
1345 |
Event.objects.create( |
|
1346 |
label='Event containing all events', |
|
1347 |
start_datetime=now() + datetime.timedelta(days=4, hours=23), |
|
1348 |
duration=440, |
|
1349 |
places=5, |
|
1350 |
agenda=agenda, |
|
1351 |
) |
|
1352 |
second_agenda = Agenda.objects.create(label='Foo bar 2', kind='events') |
|
1353 |
Event.objects.create( |
|
1354 |
label='Event', |
|
1355 |
start_datetime=now() + datetime.timedelta(days=5, hours=1), |
|
1356 |
duration=120, |
|
1357 |
places=5, |
|
1358 |
agenda=second_agenda, |
|
1359 |
) |
|
1360 |
Event.objects.create( |
|
1361 |
label='Event', |
|
1362 |
start_datetime=now() + datetime.timedelta(days=5, hours=2), |
|
1363 |
duration=120, |
|
1364 |
places=5, |
|
1365 |
agenda=second_agenda, |
|
1366 |
) |
|
1367 |
Event.objects.create( |
|
1368 |
label='Event no duration', |
|
1369 |
start_datetime=now() + datetime.timedelta(days=5, hours=1), |
|
1370 |
places=5, |
|
1371 |
agenda=second_agenda, |
|
1372 |
) |
|
1373 | ||
1374 |
resp = app.get('/api/agendas/datetimes/', params={'agendas': 'foo-bar,foo-bar-2'}) |
|
1375 |
assert [(x['id'], x['overlaps']) for x in resp.json['data']] == [ |
|
1376 |
('foo-bar@event-containing-all-events', ['foo-bar@event', 'foo-bar-2@event', 'foo-bar-2@event-1']), |
|
1377 |
('foo-bar@event', ['foo-bar@event-containing-all-events', 'foo-bar-2@event']), |
|
1378 |
('foo-bar-2@event', ['foo-bar@event-containing-all-events', 'foo-bar@event', 'foo-bar-2@event-1']), |
|
1379 |
('foo-bar-2@event-no-duration', []), |
|
1380 |
('foo-bar-2@event-1', ['foo-bar@event-containing-all-events', 'foo-bar-2@event']), |
|
1381 |
] |
tests/api/fillslot/test_events.py | ||
---|---|---|
33 | 33 |
params = {'user_external_id': 'user_id', 'slots': 'event,event-2'} |
34 | 34 |
with CaptureQueriesContext(connection) as ctx: |
35 | 35 |
resp = app.post_json(fillslots_url, params=params) |
36 |
assert len(ctx.captured_queries) == 11
|
|
36 |
assert len(ctx.captured_queries) == 12
|
|
37 | 37 |
assert resp.json['booking_count'] == 2 |
38 | 38 |
assert len(resp.json['booked_events']) == 2 |
39 | 39 |
assert resp.json['booked_events'][0]['id'] == 'event' |
... | ... | |
402 | 402 |
assert resp.json['cancelled_booking_count'] == 0 |
403 | 403 |
assert event.booking_set.filter(cancellation_datetime__isnull=True).count() == 1 |
404 | 404 |
assert second_event.booking_set.filter(cancellation_datetime__isnull=True).count() == 0 |
405 | ||
406 | ||
407 |
@pytest.mark.freeze_time('2021-09-06 12:00') |
|
408 |
def test_api_events_fillslots_overlapping_events(app, user, freezer): |
|
409 |
agenda = Agenda.objects.create(label='Foo bar', kind='events') |
|
410 |
first_event = Event.objects.create( |
|
411 |
label='Event', |
|
412 |
start_datetime=now() + datetime.timedelta(days=5), |
|
413 |
duration=120, |
|
414 |
places=5, |
|
415 |
agenda=agenda, |
|
416 |
) |
|
417 |
second_event = Event.objects.create( |
|
418 |
label='Event 2', |
|
419 |
start_datetime=now() + datetime.timedelta(days=5, hours=1), |
|
420 |
duration=120, |
|
421 |
places=5, |
|
422 |
agenda=agenda, |
|
423 |
) |
|
424 |
Event.objects.create( |
|
425 |
label='Event no duration', |
|
426 |
start_datetime=now() + datetime.timedelta(days=5, hours=1), |
|
427 |
places=5, |
|
428 |
agenda=agenda, |
|
429 |
) |
|
430 | ||
431 |
app.authorization = ('Basic', ('john.doe', 'password')) |
|
432 |
fillslots_url = '/api/agenda/foo-bar/events/fillslots/' |
|
433 |
resp = app.post_json(fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event'}) |
|
434 |
assert resp.json['booking_count'] == 1 |
|
435 | ||
436 |
# booking the same event is still allowed |
|
437 |
resp = app.post_json(fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event'}) |
|
438 |
assert resp.json['err'] == 0 |
|
439 |
assert resp.json['booking_count'] == 0 |
|
440 | ||
441 |
# changing booking to second event is allowed |
|
442 |
resp = app.post_json(fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event-2'}) |
|
443 |
assert resp.json['err'] == 0 |
|
444 |
assert resp.json['booking_count'] == 1 |
|
445 |
assert resp.json['cancelled_booking_count'] == 1 |
|
446 | ||
447 |
# booking overlapping events is allowed if one has no duration |
|
448 |
resp = app.post_json( |
|
449 |
fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event,event-no-duration'} |
|
450 |
) |
|
451 |
assert resp.json['err'] == 0 |
|
452 |
assert resp.json['booking_count'] == 2 |
|
453 |
assert resp.json['cancelled_booking_count'] == 1 |
|
454 | ||
455 |
# booking overlapping events with durations is forbidden |
|
456 |
resp = app.post_json(fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event,event-2'}) |
|
457 |
assert resp.json['err'] == 1 |
|
458 |
assert resp.json['err_desc'] == 'Some events occur at the same time: Event, Event 2' |
|
459 | ||
460 |
# still overlaps but start before |
|
461 |
second_event.start_datetime -= datetime.timedelta(hours=2) |
|
462 |
second_event.save() |
|
463 | ||
464 |
resp = app.post_json(fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event,event-2'}) |
|
465 |
assert resp.json['err'] == 1 |
|
466 |
assert resp.json['err_desc'] == 'Some events occur at the same time: Event, Event 2' |
|
467 | ||
468 |
# still overlaps but contains first event |
|
469 |
second_event.start_datetime = first_event.start_datetime - datetime.timedelta(minutes=10) |
|
470 |
second_event.save() |
|
471 |
second_event.duration = first_event.duration + 10 |
|
472 |
second_event.save() |
|
473 | ||
474 |
resp = app.post_json(fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event,event-2'}) |
|
475 |
assert resp.json['err'] == 1 |
|
476 |
assert resp.json['err_desc'] == 'Some events occur at the same time: Event, Event 2' |
|
477 | ||
478 |
# still overlaps but contained by first event |
|
479 |
second_event.start_datetime = first_event.start_datetime + datetime.timedelta(minutes=10) |
|
480 |
second_event.save() |
|
481 |
second_event.duration = first_event.duration - 10 |
|
482 |
second_event.save() |
|
483 | ||
484 |
resp = app.post_json(fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event,event-2'}) |
|
485 |
assert resp.json['err'] == 1 |
|
486 |
assert resp.json['err_desc'] == 'Some events occur at the same time: Event, Event 2' |
|
487 | ||
488 |
# no more overlap |
|
489 |
second_event.start_datetime -= datetime.timedelta(hours=5) |
|
490 |
second_event.save() |
|
491 | ||
492 |
resp = app.post_json(fillslots_url, params={'user_external_id': 'user_id', 'slots': 'event,event-2'}) |
|
493 |
assert resp.json['booking_count'] == 1 |
tests/api/fillslot/test_events_multiple_agendas.py | ||
---|---|---|
163 | 163 |
params = {'user_external_id': 'user_id', 'slots': event_slugs} |
164 | 164 |
with CaptureQueriesContext(connection) as ctx: |
165 | 165 |
resp = app.post_json('/api/agendas/events/fillslots/?agendas=%s' % agenda_slugs, params=params) |
166 |
assert len(ctx.captured_queries) == 16
|
|
166 |
assert len(ctx.captured_queries) == 17
|
|
167 | 167 |
assert resp.json['booking_count'] == 2 |
168 | 168 |
assert len(resp.json['booked_events']) == 2 |
169 | 169 |
assert resp.json['booked_events'][0]['id'] == 'first-agenda@event' |
... | ... | |
648 | 648 |
) |
649 | 649 |
assert resp.json['err'] == 1 |
650 | 650 |
assert resp.json['err_desc'] == 'Some events are outside guardian custody: first-agenda@event-thursday' |
651 | ||
652 | ||
653 |
@pytest.mark.freeze_time('2021-09-06 12:00') |
|
654 |
def test_api_events_fillslots_multiple_agendas_overlapping_events(app, user, freezer): |
|
655 |
agenda = Agenda.objects.create(label='Foo bar', kind='events') |
|
656 |
Event.objects.create( |
|
657 |
label='Event', |
|
658 |
start_datetime=now() + datetime.timedelta(days=5), |
|
659 |
duration=120, |
|
660 |
places=5, |
|
661 |
agenda=agenda, |
|
662 |
) |
|
663 |
second_agenda = Agenda.objects.create(label='Foo bar 2', kind='events') |
|
664 |
Event.objects.create( |
|
665 |
label='Event 2', |
|
666 |
start_datetime=now() + datetime.timedelta(days=5, hours=1), |
|
667 |
duration=120, |
|
668 |
places=5, |
|
669 |
agenda=second_agenda, |
|
670 |
) |
|
671 | ||
672 |
app.authorization = ('Basic', ('john.doe', 'password')) |
|
673 |
fillslots_url = '/api/agendas/events/fillslots/?agendas=%s' |
|
674 |
resp = app.post_json( |
|
675 |
fillslots_url % ','.join((agenda.slug, second_agenda.slug)), |
|
676 |
params={'user_external_id': 'user_id', 'slots': 'foo-bar@event,foo-bar-2@event-2'}, |
|
677 |
) |
|
678 |
assert resp.json['err'] == 1 |
|
679 |
assert resp.json['err_desc'] == 'Some events occur at the same time: Event, Event 2' |
|
680 | ||
681 |
# events can be booked separately |
|
682 |
resp = app.post_json( |
|
683 |
fillslots_url % agenda.slug, params={'user_external_id': 'user_id', 'slots': 'foo-bar@event'} |
|
684 |
) |
|
685 |
assert resp.json['booking_count'] == 1 |
|
686 | ||
687 |
resp = app.post_json( |
|
688 |
fillslots_url % second_agenda.slug, |
|
689 |
params={'user_external_id': 'user_id', 'slots': 'foo-bar-2@event-2'}, |
|
690 |
) |
|
691 |
assert resp.json['booking_count'] == 1 |
|
651 |
- |