def test_create_appointments(testing_db):
assert len(Booking.select()) == 0
assert len(TimeSlot.select()) == 0
assert len(Appointment.select()) == 0
NUM_SLOTS = 5
NUM_APPOINTMENTS = 3
create_kwargs = {
'day': 20,
'month': 4,
'year': 2020,
'start_hour': 16,
'start_min': 20,
'num_slots': NUM_SLOTS,
'num_appointment_per_slot': NUM_APPOINTMENTS,
'slot_duration_min': 10
}
hug.test.cli('create_appointments', module='main', **create_kwargs)
assert len(Booking.select()) == 0
assert len(TimeSlot.select()) == NUM_SLOTS
assert len(Appointment.select()) == NUM_APPOINTMENTS * NUM_SLOTS
sdt = datetime(2020, 4, 20, 16, 20, tzinfo=None)
for i in range(NUM_SLOTS):
ts = TimeSlot.get(TimeSlot.start_date_time == sdt +
timedelta(minutes=10 * i))
assert Appointment.select().where(
Appointment.time_slot == ts).count() == NUM_APPOINTMENTS
def delete_timeslots(db: directives.PeeweeSession,
year: hug.types.number,
month: hug.types.number,
day: hug.types.number,
start_hour: hug.types.number,
start_min: hug.types.number,
num_slots: hug.types.number,
for_real: hug.types.boolean = False):
with db.atomic():
dto = datetime(year, month, day, start_hour, start_min, tzinfo=None)
tomorrow = datetime(year, month, day, tzinfo=None) + timedelta(days=1)
ts = TimeSlot.select().where((TimeSlot.start_date_time >= dto) & (
TimeSlot.start_date_time < tomorrow)).order_by(
TimeSlot.start_date_time).limit(num_slots)
if not for_real:
log.info(
f"I would delete the following time slots - run with --for_real if these are correct"
)
else:
log.info(f"Deleting the following time slots")
tsids_to_delete = []
for t in ts:
tsids_to_delete.append(t.id)
log.info(f"ID: {t.id} - {t.start_date_time}")
if not tsids_to_delete:
log.error("No matching timeslots found! Exiting.")
sys.exit(1)
apts = Appointment.select().where(
Appointment.time_slot.in_(tsids_to_delete))
log.info(
f"this {'will' if for_real else 'would'} affect the following appointments"
)
apts_to_delete = []
for apt in apts:
apts_to_delete.append(apt)
log.info(
f"ID: {apt.id} - {apt.time_slot.start_date_time}: {'booked!' if apt.booked else 'free'}"
)
if all(not apt.booked for apt in apts_to_delete):
log.info(
f"none of these appointments are booked, so I {'will' if for_real else 'would'} delete them"
)
if for_real:
aq = Appointment.delete().where(
Appointment.id.in_([a.id for a in apts_to_delete]))
tq = TimeSlot.delete().where(TimeSlot.id.in_(tsids_to_delete))
aq.execute()
tq.execute()
log.info("Done!")
else:
log.error(
f"Some of these appointments are already booked, {'will' if for_real else 'would'} not delete!"
)
def booked(db: PeeweeSession, user: hug.directives.user, start_date: hug.types.text,
end_date: hug.types.text):
user_name = user.user_name
with db.atomic():
try:
user_role = user.role
start_day_object = date.fromisoformat(start_date)
end_day_object = date.fromisoformat(end_date)
if user_role != UserRoles.ADMIN:
start_day_object = date.fromisoformat('2021-01-01') #FIXME: hack to show users all their bookings
end_day_object = date.fromisoformat('2022-12-31') #FIXME: hack to show users all their bookings
bookings = []
for timeslot in TimeSlot.select().where((TimeSlot.start_date_time >= start_day_object) &
(TimeSlot.start_date_time < end_day_object + timedelta(days=1))) \
.order_by(TimeSlot.start_date_time.desc()):
for appointment in Appointment.select().where(
(Appointment.time_slot == timeslot) & (Appointment.booked == True)):
try:
booking = Booking.get(
Booking.appointment == appointment)
if user_role != UserRoles.ADMIN:
booking = Booking.select().where((Booking.appointment == appointment) &
(Booking.booked_by == user_name)).get()
bookings.append({'start_date_time': timeslot.start_date_time, 'first_name': booking.first_name,
'surname': booking.surname, 'phone': booking.phone, 'office': booking.office,
'secret': booking.secret, 'booked_by': booking.booked_by,
'booked_at': booking.booked_at, 'booking_id': booking.get_id()})
except DoesNotExist as e:
pass
return bookings
except DoesNotExist as e:
raise hug.HTTPGone
except ValueError as e:
raise hug.HTTPBadRequest
def list_for_day(db: PeeweeSession, user: hug.directives.user,
date_of_day: hug.types.text = None):
if not date_of_day:
date_of_day = (date.today() + timedelta(days=1)).isoformat()
user_name = user.user_name
with db.atomic():
try:
user_role = user.role
requested_day_object = date.fromisoformat(date_of_day)
result = io.StringIO()
writer = csv.DictWriter(result,
fieldnames=['start_date_time', 'first_name', 'surname', 'phone', 'office', 'secret',
'booked_by'])
writer.writeheader()
for timeslot in TimeSlot.select().where(
(TimeSlot.start_date_time > requested_day_object - timedelta(days=1)) &
(TimeSlot.start_date_time < requested_day_object + timedelta(days=1))):
for appointment in Appointment.select().where(
(Appointment.time_slot == timeslot) & (Appointment.booked == True)):
try:
booking = Booking.get(Booking.appointment == appointment)
if user_role != UserRoles.ADMIN:
booking = Booking.select().where((Booking.appointment == appointment) &
(Booking.booked_by == user_name)).get()
writer.writerow({'start_date_time': timeslot.start_date_time, 'first_name': booking.first_name,
'surname': booking.surname, 'phone': booking.phone, 'office': booking.office,
'secret': booking.secret, 'booked_by': booking.booked_by})
except DoesNotExist as e:
pass
return result.getvalue().encode('utf8')
except DoesNotExist as e:
raise hug.HTTPGone
except ValueError as e:
raise hug.HTTPBadRequest
def create_appointments(db: directives.PeeweeSession,
day: hug.types.number,
month: hug.types.number,
year: hug.types.number = 2020,
start_hour: hug.types.number = 8,
start_min: hug.types.number = 30,
num_slots: hug.types.number = 13,
num_appointment_per_slot: hug.types.number = 8,
slot_duration_min: hug.types.number = 30):
with db.atomic():
for i in range(num_slots):
ts = TimeSlot.create(start_date_time=datetime(
year, month, day, start_hour, start_min, tzinfo=None) +
timedelta(minutes=i * slot_duration_min),
length_min=slot_duration_min)
for _ in range(num_appointment_per_slot):
Appointment.create(booked=False, time_slot=ts)
ts.save()
def delete_claim_token(db: PeeweeSession, claim_token: hug.types.text):
with db.atomic():
try:
appointment = Appointment.get((Appointment.booked == False) & (
Appointment.claim_token == claim_token))
appointment.claim_token = None
appointment.claimed_at = None
appointment.save()
except DoesNotExist as e:
pass
except ValueError as e:
pass
def book_appointment(db: PeeweeSession, body: hug.types.json, user: hug.directives.user):
with db.atomic():
try:
assert user.coupons > 0
if all(key in body for key in ('claim_token', 'start_date_time', 'first_name', 'name', 'phone', 'office')):
claim_token = body['claim_token']
start_date_time = body['start_date_time']
start_date_time_object = datetime.fromisoformat(start_date_time)
now = datetime.now(tz=config.Settings.tz).replace(tzinfo=None)
if start_date_time_object < now:
raise ValueError("Can't claim an appointment in the past")
time_slot = TimeSlot.get(TimeSlot.start_date_time == start_date_time_object)
appointment = Appointment.get(
(Appointment.time_slot == time_slot) &
(Appointment.booked == False) &
(Appointment.claim_token == claim_token)
)
appointment.booked = True
appointment.claim_token = None
appointment.claimed_at = None
appointment.save()
success = False
with db.atomic():
while not success:
secret = get_secret_token(6)
try:
SlotCode.create(date=time_slot.start_date_time.date(), secret=secret)
success = True
except IntegrityError as e: # in the offchance that we had a collision with secret codes, retry
pass
booking = Booking.create(appointment=appointment, first_name=body['first_name'], surname=body['name'],
phone=body['phone'], office=body['office'], secret=secret,
booked_by=user.user_name)
booking.save()
user.coupons -= 1
user.save()
return {
"secret": booking.secret,
"time_slot": time_slot.start_date_time,
"slot_length_min": time_slot.length_min
}
else:
raise ValueError("Missing parameter")
except DoesNotExist as e:
raise hug.HTTPGone
except ValueError as e:
raise hug.HTTPBadRequest
except AssertionError as e:
raise hug.HTTPBadRequest
def test_get_coupon_state(testing_db):
def get_admin_and_user_data() -> Tuple[Dict, Dict]:
response = hug.test.cli('get_coupon_state',
module='main',
collect_output=True)
out = parse_csv(response)
a: Dict = next(a for a in out if a['name'] == ADMIN)
u: Dict = next(a for a in out if a['name'] == USER)
assert len(out) == 2
return a, u
admin_1, user_1 = get_admin_and_user_data()
assert int(admin_1['num_bookings']) == 0
assert int(user_1['num_bookings']) == 0
assert int(admin_1['coupons']) == 10
assert int(user_1['coupons']) == 10
User.update({
User.coupons: User.coupons + 11
}).where(User.user_name == USER).execute()
admin_2, user_2 = get_admin_and_user_data()
assert int(admin_2['num_bookings']) == 0
assert int(user_2['num_bookings']) == 0
assert int(admin_2['coupons']) == 10
assert int(user_2['coupons']) == 21
Booking.create(
surname="Mustermann",
first_name="Marianne",
phone="0123456789",
office="MusterOffice",
secret="SECRET",
booked_by=USER,
booked_at=datetime.now(),
appointment=(Appointment.create(booked=True,
time_slot=(TimeSlot.create(
start_date_time=datetime.now(),
length_min=15)))),
)
User.update({
User.coupons: User.coupons - 1
}).where(User.user_name == USER).execute()
admin_3, user_3 = get_admin_and_user_data()
assert int(admin_3['num_bookings']) == 0
assert int(user_3['num_bookings']) == 1
assert int(admin_3['coupons']) == 10
assert int(user_3['coupons']) == 20
def create_appointments(
db: directives.PeeweeSession,
day: hug.types.number,
month: hug.types.number,
year: hug.types.number = date.today().year,
start_hour: hug.types.number = 8,
start_min: hug.types.number = 30,
num_slots: hug.types.number = 13,
num_appointment_per_slot: hug.types.number = 8,
slot_duration_min: hug.types.number = 30
):
"""
[--day] <number> [--month] <number> [--year <number=date.today().year>] [--start_hour <number=8>] [--start_min <number=30>] [--num_slots <number=13>] [--num_appointment_per_slot <number=8>] [--slot_duration_min <number=30>]
creates timeslots and their corresponsing appointments
"""
with db.atomic():
for i in range(num_slots):
ts = TimeSlot.create(
start_date_time=datetime(year, month, day, start_hour, start_min, tzinfo=None) + timedelta(
minutes=i * slot_duration_min),
length_min=slot_duration_min)
for _ in range(num_appointment_per_slot):
Appointment.create(booked=False, time_slot=ts)
ts.save()
def claim_appointment(db: PeeweeSession, start_date_time: hug.types.text,
user: hug.directives.user):
"""
UPDATE appointment app
SET claim_token = 'claimed'
WHERE app.id
IN (
SELECT a.id FROM appointment a
JOIN timeslot t on a.time_slot_id = t.id
WHERE t.start_date_time = '2020-03-25 08:30:00.000000'
AND a.claim_token isnull
AND NOT a.booked
LIMIT 1
)
RETURNING *
"""
with db.atomic():
try:
if user.role != UserRoles.ANON:
assert user.coupons > 0
start_date_time_object = datetime.fromisoformat(start_date_time)
now = datetime.now(tz=config.Settings.tz).replace(tzinfo=None)
if start_date_time_object < now:
raise ValueError("Can't claim an appointment in the past")
time_slot = TimeSlot.get(
TimeSlot.start_date_time == start_date_time_object)
appointment = Appointment.select() \
.where(
(Appointment.time_slot == time_slot) &
(Appointment.booked == False) &
(Appointment.claim_token.is_null() | (Appointment.claimed_at +
timedelta(
minutes=config.Settings.claim_timeout_min) < now))
) \
.order_by(Appointment.claim_token.desc()) \
.get()
appointment.claim_token = get_random_string(32)
appointment.claimed_at = now
appointment.save()
return appointment.claim_token
except DoesNotExist as e:
raise hug.HTTPGone
except ValueError as e:
raise hug.HTTPBadRequest
except AssertionError as e:
raise hug.HTTPBadRequest
def test_get_coupon_state(testing_db):
admin_1, user_1 = _get_admin_and_user_data()
assert int(admin_1['num_bookings']) == 0
assert int(user_1['num_bookings']) == 0
assert int(admin_1['coupons']) == 10
assert int(user_1['coupons']) == 10
User.update({User.coupons: User.coupons + 11}).where(User.user_name == USER).execute()
admin_2, user_2 = _get_admin_and_user_data()
assert int(admin_2['num_bookings']) == 0
assert int(user_2['num_bookings']) == 0
assert int(admin_2['coupons']) == 10
assert int(user_2['coupons']) == 21
Booking.create(
surname="Mustermann",
first_name="Marianne",
phone="0123456789",
office="MusterOffice",
secret="SECRET",
booked_by=USER,
booked_at=datetime.now(),
appointment=(
Appointment.create(
booked=True,
time_slot=(
TimeSlot.create(
start_date_time=datetime.now(),
length_min=15)
)
)
),
)
User.update({User.coupons: User.coupons - 1}).where(User.user_name == USER).execute()
admin_3, user_3 = _get_admin_and_user_data()
assert int(admin_3['num_bookings']) == 0
assert int(user_3['num_bookings']) == 1
assert int(admin_3['coupons']) == 10
assert int(user_3['coupons']) == 20
def test_cancel_booking(testing_db):
now = datetime.now()
duration = 15
def get_booking_data(secret):
return {
"surname": "Mustermann",
"first_name": "Marianne",
"phone": "0123456789",
"office": "MusterOffice",
"secret": secret,
"booked_by": USER,
"booked_at": now
}
slot1 = TimeSlot.create(start_date_time=now, length_min=duration)
slot2 = TimeSlot.create(start_date_time=now + timedelta(minutes=duration),
length_min=duration)
appointment1 = Appointment.create(booked=False, time_slot=slot1)
appointment2 = Appointment.create(booked=False, time_slot=slot1)
appointment3 = Appointment.create(booked=False, time_slot=slot2)
appointment4 = Appointment.create(booked=False, time_slot=slot2)
appointment5 = Appointment.create(booked=True, time_slot=slot1)
appointment6 = Appointment.create(booked=True, time_slot=slot1)
appointment7 = Appointment.create(booked=True, time_slot=slot2)
appointment8 = Appointment.create(booked=True, time_slot=slot2)
booking1 = Booking.create(**get_booking_data("SECRET1"), appointment=appointment5)
booking2 = Booking.create(**get_booking_data("SECRET2"), appointment=appointment6)
booking3 = Booking.create(**get_booking_data("SECRET3"), appointment=appointment7)
booking4 = Booking.create(**get_booking_data("SECRET4"), appointment=appointment8)
not_cancel_booking = booking1
cancel_booking = booking3
fail_cancel_args = {
"secret": not_cancel_booking.secret,
"start_date_time": cancel_booking.appointment.time_slot.start_date_time,
}
assert Booking.select().count() == 4
assert TimeSlot.select().count() == 2
assert Appointment.select().count() == 8
assert Appointment.get_by_id(cancel_booking.appointment.id).booked == True
hug.test.cli('cancel_booking', module='main', **fail_cancel_args)
assert Booking.select().count() == 4
assert TimeSlot.select().count() == 2
assert Appointment.select().count() == 8
assert Appointment.get_by_id(cancel_booking.appointment.id).booked == True
hug.test.cli('cancel_booking', module='main', **fail_cancel_args, for_real=True)
assert Booking.select().count() == 4
assert TimeSlot.select().count() == 2
assert Appointment.select().count() == 8
assert Appointment.get_by_id(cancel_booking.appointment.id).booked == True
success_cancel_args = {
"secret": cancel_booking.secret,
"start_date_time": cancel_booking.appointment.time_slot.start_date_time,
}
hug.test.cli('cancel_booking', module='main', **success_cancel_args, for_real=True)
assert Booking.select().count() == 3
assert TimeSlot.select().count() == 2
assert Appointment.select().count() == 8
assert Appointment.get_by_id(cancel_booking.appointment.id).booked == False
def test_delete_timeslots(testing_db):
# this test assumes that create_apointments and cancel_booking both work. They are under test also.
# first, lets create some timeslots
NUM_SLOTS = 5
NUM_APPOINTMENTS = 3
create_kwargs = {
'day': 20,
'month': 4,
'year': 2020,
'start_hour': 16,
'start_min': 20,
'num_slots': NUM_SLOTS,
'num_appointment_per_slot': NUM_APPOINTMENTS,
'slot_duration_min': 10
}
hug.test.cli('create_appointments', module='main', **create_kwargs)
assert len(Booking.select()) == 0
assert len(TimeSlot.select()) == NUM_SLOTS
assert len(Appointment.select()) == NUM_APPOINTMENTS * NUM_SLOTS
# now, lets create two bookings, one of them in a to-be-deleted timeslot
booking_data = {
'surname': "Mustermann",
'first_name': "Marianne",
'phone': "0123456789",
'office': "MusterOffice",
'booked_by': USER,
'booked_at': datetime.now()
}
sdt1 = datetime(2020, 4, 20, 16, 20, tzinfo=None)
sdt2 = datetime(2020, 4, 20, 16, 40, tzinfo=None)
a1 = Appointment.get(Appointment.time_slot == TimeSlot.get(TimeSlot.start_date_time == sdt1))
a2 = Appointment.get(Appointment.time_slot == TimeSlot.get(TimeSlot.start_date_time == sdt2))
a1.booked = True
a1.save()
a2.booked = True
a2.save()
Booking.create(**booking_data, secret="secret1", appointment=a1)
Booking.create(**booking_data, secret="secret2", appointment=a2)
# with a booking in a timeslot, we should not delete
delete_kwargs = {
'year': 2020,
'month': 4,
'day': 20,
'start_hour': 16,
'start_min': 30,
'num_slots': 2,
'for_real': True
}
hug.test.cli('delete_timeslots', module='main', **delete_kwargs)
assert len(Booking.select()) == 2
assert len(TimeSlot.select()) == NUM_SLOTS
assert len(Appointment.select()) == NUM_APPOINTMENTS * NUM_SLOTS
# so let's cancel the booking that conflicts
hug.test.cli('cancel_booking', module='main', secret='secret2', start_date_time='2020-04-20T16:40', for_real=True)
assert len(Booking.select()) == 1
assert len(TimeSlot.select()) == NUM_SLOTS
assert len(Appointment.select()) == NUM_APPOINTMENTS * NUM_SLOTS
# and now let's retry the deletion
hug.test.cli('delete_timeslots', module='main', **delete_kwargs)
assert len(Booking.select()) == 1
assert len(TimeSlot.select()) == 3
assert len(Appointment.select()) == 9
for i in [0, 3, 4]:
ts = TimeSlot.get(TimeSlot.start_date_time == sdt1 + timedelta(minutes=10 * i))
assert Appointment.select().where(Appointment.time_slot == ts).count() == NUM_APPOINTMENTS
def list_for_day(db: PeeweeSession, user: hug.directives.user,
start_date: hug.types.text, end_date: hug.types.text):
user_name = user.user_name
with db.atomic():
try:
user_role = user.role
start_day_object = date.fromisoformat(start_date)
end_day_object = date.fromisoformat(end_date)
result = io.BytesIO()
workbook = xlsxwriter.Workbook(result)
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
date_format = workbook.add_format({'num_format': 'dd.mm.yyyy'})
time_format = workbook.add_format({'num_format': 'hh:mm'})
worksheet.set_column('A:A', 15)
worksheet.set_column('B:B', 8)
worksheet.set_column('C:C', 18)
worksheet.set_column('D:D', 15)
worksheet.set_column('E:E', 18)
worksheet.set_column('F:F', 15)
worksheet.set_column('G:G', 15)
worksheet.set_column('H:H', 15)
worksheet.set_column('I:I', 15)
worksheet.set_column('J:J', 15)
worksheet.set_column('K:K', 15)
worksheet.set_column('L:L', 15)
worksheet.set_column('M:M', 15)
worksheet.set_column('N:N', 15)
worksheet.set_column('O:O', 15)
worksheet.write('A1', 'Termin', bold)
worksheet.write('B1', 'Uhrzeit', bold)
worksheet.write('C1', 'Vorname', bold)
worksheet.write('D1', 'Nachname', bold)
worksheet.write('E1', 'Telefon', bold)
worksheet.write('F1', 'Straße', bold)
worksheet.write('G1', 'Hausnummer', bold)
worksheet.write('H1', 'PLZ', bold)
worksheet.write('I1', 'Stadt', bold)
worksheet.write('J1', 'Geburtdatum', bold)
worksheet.write('K1', 'Risikokategorie 1', bold)
worksheet.write('L1', 'Berechtigungscode', bold)
worksheet.write('M1', 'Behörde', bold)
worksheet.write('N1', 'Gebucht von', bold)
worksheet.write('O1', 'Gebucht am', bold)
row = 1
col = 0
for timeslot in TimeSlot.select().where(
(TimeSlot.start_date_time >= start_day_object)
& (TimeSlot.start_date_time < end_day_object +
timedelta(days=1))).order_by(
TimeSlot.start_date_time.desc()):
for appointment in Appointment.select().where(
(Appointment.time_slot == timeslot)
& (Appointment.booked == True)):
try:
booking = Booking.get(
Booking.appointment == appointment)
if user_role != UserRoles.ADMIN:
booking = Booking.select().where(
(Booking.appointment == appointment)
& (Booking.booked_by == user_name)).get()
worksheet.write_datetime(row, col,
timeslot.start_date_time,
date_format)
worksheet.write_datetime(row, col + 1,
timeslot.start_date_time,
time_format)
worksheet.write_string(row, col + 2,
booking.first_name)
worksheet.write_string(row, col + 3, booking.surname)
worksheet.write_string(row, col + 4, booking.phone)
worksheet.write_string(
row, col + 5, booking.street
if booking.street is not None else "")
worksheet.write_string(
row, col + 6, booking.street_number
if booking.street_number is not None else "")
worksheet.write_string(
row, col + 7, booking.post_code
if booking.post_code is not None else "")
worksheet.write_string(
row, col + 8,
booking.city if booking.city is not None else "")
if booking.birthday is None:
worksheet.write_string(row, col + 9, "")
else:
worksheet.write_datetime(row, col + 9,
booking.birthday,
date_format)
worksheet.write_string(
row, col + 10, booking.reason
if booking.reason is not None else "")
worksheet.write_string(row, col + 11, booking.secret)
worksheet.write_string(row, col + 12, booking.office)
worksheet.write_string(row, col + 13,
booking.booked_by)
worksheet.write_datetime(row, col + 14,
booking.booked_at,
date_format)
row += 1
except DoesNotExist as e:
pass
workbook.close()
result.flush()
return result.getvalue()
except DoesNotExist as e:
raise hug.HTTPGone
except ValueError as e:
raise hug.HTTPBadRequest
