class OfficeSchema(ma.SQLAlchemySchema):
class Meta:
model = Office
include_relationships = True
load_instance = True
jit = toastedmarshmallow.Jit
# exclude = ('citizens', 'csrs', 'deleted', 'exams', 'rooms', 'services',)
office_id = fields.Int()
office_name = fields.Str()
office_number = fields.Int()
sb_id = fields.Int()
deleted = fields.DateTime()
exams_enabled_ind = fields.Int()
appointments_enabled_ind = fields.Int()
max_person_appointment_per_day = fields.Int()
telephone = fields.Str()
appointments_days_limit = fields.Int()
appointment_duration = fields.Int()
sb = fields.Nested(SmartBoardSchema())
counters = fields.Nested(CounterSchema(), many=True)
quick_list = fields.Nested(ServiceSchema(), many=True)
back_office_list = fields.Nested(ServiceSchema(), many=True)
timezone = fields.Nested(TimezoneSchema())
timeslots = fields.Nested(TimeslotSchema(), many=True)
latitude = fields.Float()
longitude = fields.Float()
office_appointment_message = fields.Str()
civic_address = fields.Str()
online_status = fields.Str()
class OfficeSchema(ma.ModelSchema):
class Meta:
model = Office
jit = toastedmarshmallow.Jit
exclude = ('citizens', 'csrs', 'deleted', 'exams', 'rooms', 'services',)
office_id = fields.Int()
office_name = fields.Str()
office_number = fields.Int()
sb_id = fields.Int()
deleted = fields.DateTime()
exams_enabled_ind = fields.Int()
appointments_enabled_ind = fields.Int()
sb = fields.Nested(SmartBoardSchema())
counters = fields.Nested(CounterSchema(), many=True)
quick_list = fields.Nested(ServiceSchema(), many=True)
back_office_list = fields.Nested(ServiceSchema(), many=True)
timezone = fields.Nested(TimezoneSchema())
timeslots = fields.Nested(TimeslotSchema(), many=True)
latitude = fields.Float()
longitude = fields.Float()
office_appointment_message = fields.Str()
civic_address = fields.Str()
online_status = fields.Str()
class OfficeSchema(BaseSchema):
class Meta(BaseSchema.Meta):
model = Office
include_relationships = True
# exclude = ('citizens', 'csrs', 'deleted', 'exams', 'rooms', 'services',)
office_id = fields.Int()
office_name = fields.Str()
office_number = fields.Int()
sb_id = fields.Int()
deleted = fields.DateTime()
exams_enabled_ind = fields.Int()
appointments_enabled_ind = fields.Int()
max_person_appointment_per_day = fields.Int()
telephone = fields.Str()
appointments_days_limit = fields.Int()
appointment_duration = fields.Int()
sb = fields.Nested(SmartBoardSchema())
counters = fields.Nested(CounterSchema(), many=True)
quick_list = fields.Nested(ServiceSchema(), many=True)
back_office_list = fields.Nested(ServiceSchema(), many=True)
timezone = fields.Nested(TimezoneSchema())
timeslots = fields.Nested(TimeslotSchema(), many=True)
latitude = fields.Float()
longitude = fields.Float()
office_appointment_message = fields.Str()
civic_address = fields.Str()
online_status = fields.Str()
external_map_link = fields.Str()
# for walk-in notifications
check_in_notification = fields.Int()
check_in_reminder_msg = fields.Str()
automatic_reminder_at = fields.Int()
# for Digital Signage
currently_waiting = fields.Int()
digital_signage_message = fields.Int()
digital_signage_message_1 = fields.Str()
digital_signage_message_2 = fields.Str()
digital_signage_message_3 = fields.Str()
show_currently_waiting_bottom = fields.Int()
class ExamList(Resource):
exam_schema = ExamSchema(many=True)
office_schema = OfficeSchema(many=True)
timezone_schema = TimezoneSchema(many=True)
@oidc.accept_token(require_token=True)
@has_any_role(roles=[Role.internal_user.value])
def get(self):
try:
csr = CSR.find_by_username(g.oidc_token_info['username'])
is_designate = csr.finance_designate
start_param = request.args.get("start_date")
end_param = request.args.get("end_date")
exam_type = request.args.get("exam_type")
validate_params(start_param, end_param)
try:
start_date = datetime.strptime(request.args['start_date'],
"%Y-%m-%d")
end_date = datetime.strptime(request.args['end_date'],
"%Y-%m-%d")
except ValueError as err:
print(err)
return {"message", "Unable to return date time string"}, 422
# Code for UTC time.
csr_office = Office.query.filter(
Office.office_id == csr.office_id).first()
csr_timezone = Timezone.query.filter(
Timezone.timezone_id == csr_office.timezone_id).first()
csr_timename = csr_timezone.timezone_name
timezone = pytz.timezone(csr_timename)
start_local = timezone.localize(start_date)
end_date += timedelta(days=1)
end_local = timezone.localize(end_date)
exams = Exam.query.join(Booking, Exam.booking_id == Booking.booking_id) \
.filter(Booking.start_time >= start_local) \
.filter(Booking.start_time < end_local) \
.join(Room, Booking.room_id == Room.room_id, isouter=True) \
.join(Office, Booking.office_id == Office.office_id) \
.join(ExamType, Exam.exam_type_id == ExamType.exam_type_id)
if exam_type == 'all_bookings':
non_exams = Booking.query.join(Exam, Booking.booking_id == Exam.booking_id, isouter=True) \
.filter(Booking.start_time >= start_local) \
.filter(Booking.start_time < end_local) \
.filter(Exam.booking_id.is_(None)) \
.join(Room, Booking.room_id == Room.room_id, isouter=True) \
.join(Office, Booking.office_id == Office.office_id) \
if not is_designate:
exams = exams.filter(Booking.office_id == csr.office_id)
if exam_type == 'all_bookings':
non_exams = non_exams.filter(
Booking.office_id == csr.office_id)
if exam_type == 'ita':
exams = exams.filter(ExamType.ita_ind == 1)
elif exam_type == 'all_non_ita':
exams = exams.filter(ExamType.ita_ind == 0)
dest = io.StringIO()
out = csv.writer(dest)
out.writerow([
'Office Name', 'Exam Type', 'Exam ID', 'Exam Name',
'Examinee Name', 'Event ID', 'Room Name',
'Invigilator Name(s)', 'Shadow Invigilator Name',
'SBC Invigilator', 'Start Time', 'End Time', 'Booking ID',
'Booking Name', 'Number Of Students', 'Exam Received',
'Exam Written', 'Exam Returned', 'Notes', 'Collect Fees'
])
keys = [
"office_name", "exam_type_name", "exam_id", "exam_name",
"examinee_name", "event_id", "room_name", "invigilator_names",
"shadow_invigilator_id", "sbc_staff_invigilated", "start_time",
"end_time", "booking_id", "booking_name", "number_of_students",
"exam_received_date", "exam_written_ind", "exam_returned_date",
"notes", "fees"
]
exam_keys = [
"exam_id",
"exam_name",
"examinee_name",
"event_id",
"number_of_students",
"notes",
]
booking_keys = [
"start_time", "end_time", "booking_id", "booking_name"
]
non_exam_keys = [
"exam_name",
"notes",
]
for exam in exams:
row = []
if exam.booking.shadow_invigilator_id:
shadow_invigilator_id = exam.booking.shadow_invigilator_id
else:
shadow_invigilator_id = None
try:
for key in keys:
if key == "room_name":
write_room(row, exam)
elif key == "invigilator_names":
write_invigilator(row, exam)
elif key == "shadow_invigilator_id":
write_shadow_invigilator(row,
shadow_invigilator_id)
elif key == "sbc_staff_invigilated":
write_sbc(row, exam)
elif key == "exam_received_date":
write_exam_received(row, exam)
elif key == "exam_written_ind":
write_exam_written(row, exam)
elif key == "exam_returned_date":
write_exam_returned(row, exam)
elif key == "office_name":
row.append(getattr(exam.office, key))
elif key == "exam_type_name":
row.append(getattr(exam.exam_type, key))
elif key in booking_keys:
value = getattr(exam.booking, key)
if isinstance(value, datetime):
row.append('="' +
localize_time(value, timezone) +
'"')
else:
row.append(value)
elif key in exam_keys:
row.append(getattr(exam, key))
elif key == "fees":
row.append("")
out.writerow(row)
except AttributeError as error:
logging.error(error, exc_info=True)
return {
"message": "Issue writing row to CSV ",
"key": key
}, 500
if exam_type == 'all_bookings':
for non_exam in non_exams:
row = []
try:
for key in keys:
if key == "room_name":
write_booking_room(row, non_exam)
elif key == "invigilator_names":
row.append("")
elif key == "shadow_invigilator_id":
row.append("")
elif key == "sbc_staff_invigilated":
row.append("")
elif key == "exam_received_date":
row.append("")
elif key == "exam_written_ind":
row.append("")
elif key == "exam_returned_date":
row.append("")
elif key == "office_name":
row.append(getattr(non_exam.office, key))
elif key == "exam_type_name":
row.append("Non Exam Booking")
elif key in booking_keys:
value = getattr(non_exam, key)
if isinstance(value, datetime):
row.append('="' +
localize_time(value, timezone) +
'"')
else:
row.append(value)
elif key in non_exam_keys:
which_non_exam_key(non_exam, row, key)
elif key == "exam_id":
row.append("")
elif key == "exam_name":
row.append("")
elif key == "examinee_name":
row.append("")
elif key == "event_id":
row.append("")
elif key == "fees":
which_non_exam_key(non_exam, row, key)
elif key == "number_of_students":
row.append("")
elif key == "exam_received_ind":
row.append("")
out.writerow(row)
except AttributeError as error:
logging.error(error, exc_info=True)
return {
"message": "Issue writing row to CSV ",
"key": key
}, 500
output = make_response(dest.getvalue())
output.headers[
"Content-Disposition"] = "attachment; filename=export.csv"
output.headers["Content-type"] = "text/csv"
return output
except exc.SQLAlchemyError as error:
logging.error(error, exc_info=True)
return {"message": "api is down"}, 500