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 cancel_booking(db: directives.PeeweeSession,
secret: hug.types.text,
start_date_time: hug.types.text,
for_real: hug.types.smart_boolean = False):
with db.atomic():
sdt = datetime.fromisoformat(start_date_time).replace(tzinfo=None)
timeslot = TimeSlot.get(TimeSlot.start_date_time == sdt)
booking = Booking.select(Booking).join(
Appointment).where((Booking.secret == secret)
& (Appointment.time_slot == timeslot)).get()
if not for_real:
print(
f"This would delete the booking with id '{booking.id}' and secret '{booking.secret}'. Run with "
f"--for_real if you are sure.")
sys.exit(1)
else:
print(
f"Deleting the booking with id '{booking.id}' and secret '{booking.secret}'."
)
booking.appointment.booked = False
booking.appointment.save()
q = Booking.delete().where(Booking.id == booking.id)
q.execute()
print("Done.")
def get_bookings_created_at(db: directives.PeeweeSession, booked_at: hug.types.text):
"""
[--booked_at <ISO datetime string>] get all bookings made at specific day or time
Get bookings for a day with yyyy-mm-dd or one specific booking at yyyy-mm-ddThh:mm:ss.mmmmmm
"""
with db.atomic():
query = Booking.select(
Booking,
Appointment.time_slot.start_date_time.alias("start_date_time")
).join(Appointment).join(TimeSlot)
booked_start = datetime.fromisoformat(booked_at).replace(tzinfo=None)
if str(booked_start.date()) == booked_at:
# booked_at is yyyy-mm-dd
booked_end = booked_start.date() + timedelta(days=1)
bookings = query.where(
Booking.booked_at.between(booked_start, booked_end))
else:
# booked_at is yyyy-mm-ddThh:mm:ss.mmmmmm
bookings = query.where(Booking.booked_at == booked_start)
result = []
for booking in bookings.dicts().iterator():
del booking["appointment"]
result.append({**booking})
return result
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 get_coupon_state():
"""
get a list of all users and their bookings and remaining coupons
"""
ret = []
for user in User.select():
bookings = Booking.select().where(
user.user_name == Booking.booked_by)
ret.append({
"name": user.user_name,
"num_bookings": len(bookings),
"coupons": user.coupons
})
return ret
def get_users():
"""
SELECT u.user_name, u.coupons, COUNT(b.id)
FROM "user" u
JOIN booking b ON b.booked_by = u.user_name
GROUP BY u.user_name, u.coupons
"""
users = User.select().where(User.role != UserRoles.ANON).order_by(User.role.desc(), User.user_name)
return [{
"user_name": user.user_name,
"is_admin": user.role == UserRoles.ADMIN,
"total_bookings": len(Booking.select().where(
user.user_name == Booking.booked_by)),
"coupons": user.coupons
} for user in users]
def get_coupon_state():
"""
SELECT u.user_name, u.coupons, COUNT(b.id)
FROM "user" u
JOIN booking b ON b.booked_by = u.user_name
GROUP BY u.user_name, u.coupons
"""
ret = []
for user in User.select():
bookings = Booking.select().where(user.user_name == Booking.booked_by)
ret.append({
"name": user.user_name,
"num_bookings": len(bookings),
"coupons": user.coupons
})
return ret
def has_booking(db: directives.PeeweeSession, booking: hug.types.json):
"""
BOOKING_JSON; check if a booking exists for the booked person
"""
try:
return Booking.select(Booking).where(
(Booking.surname == booking["surname"])
& (Booking.first_name == booking["first_name"])
& (Booking.birthday == booking["birthday"])
& (Booking.phone == booking["phone"])
& (Booking.street == booking["street"])
& (Booking.street_number == booking["street_number"])
& (Booking.post_code == booking["post_code"])
& (Booking.city == booking["city"])
).count() > 0
except KeyError as e:
print(f"Key {e} is missing in booking.")
return None
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
def has_booked_by(db: directives.PeeweeSession, user: hug.types.text):
"""
USER_NAME; checks if there are bookings made by that user
"""
return Booking.select(Booking).where(Booking.booked_by == user).count() > 0
def has_booked_by(db: directives.PeeweeSession, user: hug.types.text):
"""
args: USER_NAME
"""
return Booking.select(Booking).where(Booking.booked_by == user).count() > 0