def get_all_active_offices(cls):
"""Return all active offices."""
from app.schemas.theq import OfficeSchema
active_offices = cache.get(Office.offices_cache_key)
if not active_offices:
office_schema = OfficeSchema(many=True)
active_offices = office_schema.dump(
Office.query.filter(Office.deleted.is_(None)))
cache.set(Office.offices_cache_key, active_offices)
return active_offices
class SmartBoradQDetails(Resource):
citizen_schema = CitizenSchema()
office_schema = OfficeSchema()
walkinObj = WalkinDetail()
processObj = SendLineReminderWalkin()
@api_call_with_retry
def get(self, id):
try:
# get office details from url id
office = Office.query.filter_by(office_number=id).first()
if not office:
return {'message': 'office_number could not be found.'}, 400
res_list = []
if (office.currently_waiting == 1):
# result= all citizen in q
result = self.walkinObj.get_all_citizen_in_q(office=office)
# process result
booked_check_app, walkin_app = self.processObj.process_all_citizen_in_q(
result)
# sorting-maintaing the order group
res_list = tuple(booked_check_app + walkin_app)
return {'citizen_in_q': res_list}, 200
return {}
except exc.SQLAlchemyError as e:
print(e)
return {'message': 'API is down'}, 500
class BookingSchema(ma.ModelSchema):
class Meta:
model = Booking
jit = toastedmarshmallow.Jit
booking_id = fields.Int(dump_only=True)
booking_name = fields.Str()
end_time = fields.DateTime()
fees = fields.Str()
room_id = fields.Int()
start_time = fields.DateTime()
invigilator_id = fields.Int(allow_none=True)
office_id = fields.Int()
sbc_staff_invigilated = fields.Int()
booking_contact_information = fields.Str()
invigilator = fields.Nested(
InvigilatorSchema(only=('invigilator_id', 'invigilator_name',
'invigilator_notes')))
room = fields.Nested(RoomSchema(exclude=(
"booking",
"office",
)))
office = fields.Nested(
OfficeSchema(only=('appointments_enabled_ind', 'exams_enabled_ind',
'office_id', 'office_name', 'office_number',
'timezone')))
class ExamSchema(ma.ModelSchema):
class Meta:
model = Exam
jit = toastedmarshmallow.Jit
booking_id = fields.Int()
deleted_date = fields.Str()
event_id = fields.Str()
exam_id = fields.Int(dump_only=True)
exam_method = fields.Str()
exam_name = fields.Str()
exam_received = fields.Int()
exam_received_date = fields.DateTime(allow_none=True)
exam_type_id = fields.Int()
examinee_name = fields.Str()
expiry_date = fields.DateTime()
notes = fields.Str(allow_none=True)
number_of_students = fields.Int()
office_id = fields.Int()
session_number = fields.Int()
exam_returned_date = fields.Str(allow_none=True)
exam_returned_tracking_number = fields.String(allow_none=True)
exam_written_ind = fields.Int()
offsite_location = fields.String()
booking = fields.Nested(BookingSchema())
exam_type = fields.Nested(ExamTypeSchema())
office = fields.Nested(OfficeSchema(exclude=("csrs", )))
class AppointmentSchema(ma.SQLAlchemySchema):
class Meta:
model = Appointment
include_relationships = True
load_instance = True
jit = toastedmarshmallow.Jit
appointment_id = fields.Int(dump_only=True)
office_id = fields.Int()
service_id = fields.Int(allow_none=True)
citizen_id = fields.Int()
start_time = fields.DateTime()
end_time = fields.DateTime()
checked_in_time = fields.DateTime()
comments = fields.String(allow_none=True)
citizen_name = fields.String()
contact_information = fields.String(allow_none=True)
blackout_flag = fields.String(allow_none=True)
recurring_uuid = fields.String(allow_none=True)
online_flag = fields.Boolean(allow_none=True)
is_draft = fields.Boolean(allow_none=True)
# office = fields.Int(attribute="office_id")
# service = fields.Int(attribute="service_id")
office = fields.Nested(OfficeSchema())
service = fields.Nested(ServiceSchema())
class CSRSchema(ma.SQLAlchemySchema):
class Meta:
model = CSR
include_relationships = True
load_instance = True
jit = toastedmarshmallow.Jit
# exclude = ('periods',)
csr_id = fields.Int()
username = fields.Str()
office_id = fields.Int()
role_id = fields.Int()
receptionist_ind = fields.Int()
deleted = fields.DateTime()
csr_state_id = fields.Int()
counter_id = fields.Int()
counter = fields.Int(attribute="counter_id")
csr_state = fields.Nested(CSRStateSchema(exclude=('csrs', )))
office = fields.Nested(OfficeSchema())
role = fields.Nested(RoleSchema(exclude=('roles', )))
office_manager = fields.Int()
pesticide_designate = fields.Int()
qt_xn_csr_ind = fields.Int()
finance_designate = fields.Int()
ita2_designate = fields.Int()
class CSRSchema(BaseSchema):
class Meta(BaseSchema.Meta):
model = CSR
include_relationships = True
csr_id = fields.Int()
username = fields.Str()
office_id = fields.Int()
role_id = fields.Int()
receptionist_ind = fields.Int()
deleted = fields.DateTime()
csr_state_id = fields.Int()
counter_id = fields.Int()
csr_state = fields.Nested(CSRStateSchema())
office = fields.Nested(OfficeSchema())
role = fields.Nested(RoleSchema())
office_manager = fields.Int()
pesticide_designate = fields.Int()
qt_xn_csr_ind = fields.Int()
finance_designate = fields.Int()
ita2_designate = fields.Int()
@post_dump(pass_many=True)
def add_counter_id(self, data, many, **kwargs):
if not many:
data['counter'] = data['counter_id']
else:
for csr in data:
csr['counter'] = csr['counter_id']
return data
class SmartBoradQDetails(Resource):
citizen_schema = CitizenSchema()
office_schema = OfficeSchema()
walkinObj = WalkinDetail()
processObj = SendLineReminderWalkin()
@api_call_with_retry
def get(self, id):
try:
# get office details from url id
office = Office.query.filter_by(office_number=id).first()
if not office:
return {'message': 'office_number could not be found.'}, 400
booked_not_checkin = []
if (office.currently_waiting == 1):
# office time zone
local_timezone = self.walkinObj.get_my_office_timezone(
office=office)
# get all app from agenda panel
result_in_book = self.walkinObj.get_all_app_from_agenda_panel(
office=office)
# processing agenda panel appointmnets:
booked_not_checkin = self.walkinObj.process_agenda_panel(
result_in_book, local_timezone)
return {'booked_not_checkin': booked_not_checkin}, 200
return {}
except exc.SQLAlchemyError as e:
print(e)
return {'message': 'API is down'}, 500
class BookingSchema(ma.SQLAlchemySchema):
class Meta:
model = Booking
include_relationships = True
load_instance = True
jit = toastedmarshmallow.Jit
booking_id = fields.Int(dump_only=True)
booking_name = fields.Str()
end_time = fields.DateTime()
fees = fields.Str()
room_id = fields.Int()
start_time = fields.DateTime()
shadow_invigilator_id = fields.Int(allow_none=True)
office_id = fields.Int()
sbc_staff_invigilated = fields.Int()
booking_contact_information = fields.Str()
blackout_flag = fields.Str(allow_none=True)
blackout_notes = fields.Str(allow_none=True)
recurring_uuid = fields.Str(allow_none=True)
room = fields.Nested(RoomSchema(exclude=(
"booking",
"office",
)))
office = fields.Nested(
OfficeSchema(only=('appointments_enabled_ind', 'exams_enabled_ind',
'office_id', 'office_name', 'office_number',
'timezone')))
# NOTE: The reason for the exclude, rather than just a single include, is because
# an include with a single field didn't seem to work. When I added a second field, it worked.
# I only want a single field, so had to use an exclude instead. ?????
invigilators = fields.Nested(InvigilatorSchema(
exclude=('contact_name', 'contact_email', 'contract_number',
'contract_expiry_date', 'invigilator_name',
'invigilator_notes', 'shadow_count', 'shadow_flag',
'contact_phone', 'deleted', 'office')),
many=True)
def update_invigilators(self, data):
invigilator_data = data.get('invigilators')
invigilator_list = []
for invigilator in invigilator_data:
id = invigilator.get('invigilator_id')
invigilator_list.append(id)
data['invigilators'] = invigilator_list
return data
@post_dump(pass_many=True)
def fix_invigilators(self, data, many, **kwargs):
if not many:
data = self.update_invigilators(data)
else:
for booking in data:
booking = self.update_invigilators(booking)
return data
class RoomSchema(ma.ModelSchema):
class Meta:
model = Room
exclude = ("booking", )
jit = toastedmarshmallow.Jit
capacity = fields.Int()
color = fields.Str()
room_id = fields.Int(dump_only=True)
room_name = fields.Str()
office = fields.Nested(OfficeSchema())
class RoomSchema(BaseSchema):
class Meta(BaseSchema.Meta):
model = Room
include_relationships = True
#exclude = ("booking",)
capacity = fields.Int()
color = fields.Str()
room_id = fields.Int(dump_only=True)
room_name = fields.Str()
deleted = fields.Str()
office = fields.Nested(OfficeSchema())
class ExamSchema(ma.SQLAlchemySchema):
class Meta:
model = Exam
include_relationships = True
load_instance = True
jit = toastedmarshmallow.Jit
booking_id = fields.Int(allow_none=True)
deleted_date = fields.Str(allow_none=True)
event_id = fields.Str(allow_none=True)
exam_destroyed_date = fields.Str(allow_none=True)
exam_id = fields.Int(dump_only=True)
exam_method = fields.Str()
exam_name = fields.Str()
exam_received = fields.Int()
exam_received_date = fields.DateTime(allow_none=True)
exam_type_id = fields.Int()
examinee_name = fields.Str(allow_none=True)
examinee_phone = fields.Str(allow_none=True)
examinee_email = fields.Str(allow_none=True)
expiry_date = fields.DateTime(allow_none=True)
notes = fields.Str(allow_none=True)
number_of_students = fields.Int(allow_none=True)
office_id = fields.Int()
invigilator_id = fields.Int(allow_none=True)
session_number = fields.Int(allow_none=True)
exam_returned_ind = fields.Int()
exam_returned_date = fields.Str(allow_none=True)
exam_returned_tracking_number = fields.Str(allow_none=True)
exam_written_ind = fields.Int()
upload_received_ind = fields.Int(allow_none=True)
offsite_location = fields.Str(allow_none=True)
sbc_managed_ind = fields.Int(allow_none=True)
receipt = fields.Str(allow_none=True)
receipt_number = fields.Str()
fees = fields.Str()
payee_ind = fields.Int(allow_none=True)
receipt_sent_ind = fields.Int(allow_none=True)
payee_name = fields.Str(allow_none=True)
payee_email = fields.Str(allow_none=True)
payee_phone = fields.Str(allow_none=True)
bcmp_job_id = fields.Str(allow_none=True)
is_pesticide = fields.Int(allow_none=True)
candidates_list = fields.Nested(CandidateSchema)
booking = fields.Nested(BookingSchema())
exam_type = fields.Nested(ExamTypeSchema())
invigilator = fields.Nested(InvigilatorSchema())
office = fields.Nested(OfficeSchema(only=('appointments_enabled_ind', 'exams_enabled_ind', 'office_id',
'office_name', 'office_number', 'timezone')))
class SmartBoradQMenu(Resource):
office_schema = OfficeSchema()
def get(self, id):
try:
# get office details from url id
office = Office.query.filter_by(office_number=id).first()
if not office:
return {'message': 'office_number could not be found.'}, 400
else:
return {'office': self.office_schema.dump(office)}, 200
except exc.SQLAlchemyError as e:
print(e)
return {'message': 'API is down'}, 500
class InvigilatorSchema(ma.ModelSchema):
class Meta:
model = Invigilator
exclude = ("invigilators", )
jit = toastedmarshmallow.Jit
contact_phone = fields.Str()
contact_email = fields.Str()
contract_number = fields.Str()
contract_expiry_date = fields.Str()
invigilator_id = fields.Int(dump_only=True)
invigilator_name = fields.Str()
invigilator_notes = fields.Str()
office = fields.Nested(OfficeSchema())
class RoomSchema(ma.SQLAlchemySchema):
class Meta:
model = Room
include_relationships = True
load_instance = True
jit = toastedmarshmallow.Jit
#exclude = ("booking",)
capacity = fields.Int()
color = fields.Str()
room_id = fields.Int(dump_only=True)
room_name = fields.Str()
deleted = fields.Str()
office = fields.Nested(OfficeSchema())
class OfficeList(Resource):
office_schema = OfficeSchema(many=True)
@oidc.accept_token(require_token=False)
def get(self):
try:
offices = Office.query.filter(Office.deleted.is_(None))
result = self.office_schema.dump(offices)
return {'offices': result.data, 'errors': result.errors}
except exc.SQLAlchemyError as e:
print(e)
return {'message': 'API is down'}, 500
class CSRSchema(ma.ModelSchema):
class Meta:
model = CSR
jit = toastedmarshmallow.Jit
exclude = ('periods', )
csr_id = fields.Int()
username = fields.Str()
office_id = fields.Int()
role_id = fields.Int()
qt_xn_csr_ind = fields.Int()
receptionist_ind = fields.Int()
deleted = fields.DateTime()
csr_state_id = fields.Int()
csr_state = fields.Nested(CSRStateSchema(exclude=('csrs', )))
office = fields.Nested(OfficeSchema())
role = fields.Nested(RoleSchema(exclude=('roles', )))
class InvigilatorSchema(BaseSchema):
class Meta(BaseSchema.Meta):
model = Invigilator
#exclude = ("invigilators",)
include_relationships = True
contact_phone = fields.Str()
contact_email = fields.Str()
contract_number = fields.Str()
contract_expiry_date = fields.Str()
invigilator_id = fields.Int(dump_only=True)
invigilator_name = fields.Str()
invigilator_notes = fields.Str()
shadow_count = fields.Int()
shadow_flag = fields.Str()
office_id = fields.Int()
deleted = fields.Str()
office = fields.Nested(OfficeSchema())
class BookingSchema(ma.ModelSchema):
class Meta:
model = Booking
jit = toastedmarshmallow.Jit
booking_id = fields.Int(dump_only=True)
booking_name = fields.Str()
end_time = fields.DateTime()
fees = fields.Str()
room_id = fields.Int()
start_time = fields.DateTime()
invigilator_id = fields.Int()
office_id = fields.Int()
invigilator = fields.Nested(InvigilatorSchema())
room = fields.Nested(RoomSchema(exclude=(
"booking",
"office",
)))
office = fields.Nested(OfficeSchema())
class OfficeList(Resource):
office_schema = OfficeSchema(many=True)
@oidc.accept_token(require_token=True)
def get(self):
try:
csr = CSR.find_by_username(g.oidc_token_info['username'])
# if csr.role.role_code != "LIAISON":
# return {'message': 'You do not have permission to view this end-point'}, 403
offices = Office.query.filter(Office.deleted.is_(None))
result = self.office_schema.dump(offices)
return {'offices': result.data, 'errors': result.errors}
except exc.SQLAlchemyError as e:
print(e)
return {'message': 'API is down'}, 500
class CSRSchema(ma.ModelSchema):
class Meta:
model = CSR
jit = toastedmarshmallow.Jit
exclude = ('periods', )
csr_id = fields.Int()
username = fields.Str()
office_id = fields.Int()
role_id = fields.Int()
receptionist_ind = fields.Int()
deleted = fields.DateTime()
csr_state_id = fields.Int()
counter_id = fields.Int()
csr_state = fields.Nested(CSRStateSchema(exclude=('csrs', )))
office = fields.Nested(OfficeSchema())
role = fields.Nested(RoleSchema(exclude=('roles', )))
ita_designate = fields.Int()
pesticide_designate = fields.Int()
finance_designate = fields.Int()
liaison_designate = fields.Int()
class InvigilatorSchema(ma.SQLAlchemySchema):
class Meta:
model = Invigilator
#exclude = ("invigilators",)
include_relationships = True
load_instance = True
jit = toastedmarshmallow.Jit
contact_phone = fields.Str()
contact_email = fields.Str()
contract_number = fields.Str()
contract_expiry_date = fields.Str()
invigilator_id = fields.Int(dump_only=True)
invigilator_name = fields.Str()
invigilator_notes = fields.Str()
shadow_count = fields.Int()
shadow_flag = fields.Str()
office_id = fields.Int()
deleted = fields.Str()
office = fields.Nested(OfficeSchema())
class AppointmentSchema(BaseSchema):
class Meta(BaseSchema.Meta):
model = Appointment
include_relationships = True
appointment_id = fields.Int(dump_only=True)
office_id = fields.Int()
service_id = fields.Int(allow_none=True)
citizen_id = fields.Int()
start_time = fields.DateTime()
end_time = fields.DateTime()
checked_in_time = fields.DateTime()
comments = fields.String(allow_none=True)
citizen_name = fields.String()
contact_information = fields.String(allow_none=True)
blackout_flag = fields.String(allow_none=True)
recurring_uuid = fields.String(allow_none=True)
online_flag = fields.Boolean(allow_none=True)
is_draft = fields.Boolean(allow_none=True)
stat_flag = fields.Boolean(allow_none=True)
office = fields.Nested(
OfficeSchema(exclude=('sb', 'counters', 'quick_list',
'back_office_list', 'timeslots')))
service = fields.Nested(ServiceSchema())
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
def get(self):
if request.args.get('office_id'):
office_id = int(request.args.get('office_id'))
csr = CSR.find_by_username(g.jwt_oidc_token_info['username'])
if csr.role.role_code == "GA":
if csr.office_id != office_id:
return {
'message': 'This is not your office, cannot refresh.'
}, 403
elif csr.role.role_code != "SUPPORT":
return {
'message':
'You do not have permission to view this end-point'
}, 403
def top_reqs(is_back_office=True):
# Get top requests for the office, and set the lists based on those.
results = ServiceReq.query.options(
noload('*'),
joinedload('service')).join(Citizen).join(Service).filter(
Citizen.office_id == office_id, ).filter(
Service.deleted.is_(None))
if is_back_office:
results = results.filter(
Service.display_dashboard_ind == 0, )
else:
results = results.filter(
Service.display_dashboard_ind == 1, )
results = results.order_by(ServiceReq.sr_id.desc()).limit(100)
print("start *****************************")
print(results.statement)
print("end *****************************")
# Some fancy dicts to collect the top 5 services in a list.
counts = {}
services = {}
byname = {}
for result in results:
service_ct = counts.get(result.service_id, 0)
counts[result.service_id] = service_ct + 1
services[result.service_id] = result
byname[result.service.service_name] = counts[
result.service_id]
counts = list(counts.items())
counts.sort(key=lambda x: x[1]) # sort by quantity.
counts = counts[-5:]
print("Results of refresh call for office {} : {}".format(
office_id, byname))
service_ids = [c[0] for c in counts]
print("List chosen: {}".format([
r.service.service_name for r in services.values()
if r.service_id in service_ids
]))
return [
r.service for r in services.values()
if r.service_id in service_ids
]
quick_list = top_reqs(is_back_office=False)
back_office_list = top_reqs(is_back_office=True)
office = Office.query.get(office_id)
office.quick_list = quick_list
office.back_office_list = back_office_list
db.session.commit()
return OfficeSchema().dump(office)
else:
return {'message': 'no office specified'}, 400
class BookingSchema(BaseSchema):
class Meta:
model = Booking
include_relationships = True
load_instance = True
unknown = EXCLUDE
booking_id = fields.Int(dump_only=True)
booking_name = fields.Str()
end_time = fields.DateTime()
fees = fields.Str()
room_id = fields.Int()
start_time = fields.DateTime()
shadow_invigilator_id = fields.Int(allow_none=True)
office_id = fields.Int()
sbc_staff_invigilated = fields.Int()
booking_contact_information = fields.Str()
blackout_flag = fields.Str(allow_none=True)
blackout_notes = fields.Str(allow_none=True)
recurring_uuid = fields.Str(allow_none=True)
stat_flag = fields.Boolean(allow_none=True)
room = fields.Nested(RoomSchema(exclude=("office",)))
office = fields.Nested(OfficeSchema(only=('appointments_enabled_ind', 'exams_enabled_ind', 'office_id',
'office_name', 'office_number', 'timezone')))
# NOTE: The reason for the exclude, rather than just a single include, is because
# an include with a single field didn't seem to work. When I added a second field, it worked.
# I only want a single field, so had to use an exclude instead. ?????
invigilators = fields.Nested(InvigilatorSchema(exclude=( 'contact_email', 'contract_number',
'contract_expiry_date', 'invigilator_name',
'invigilator_notes', 'shadow_count', 'shadow_flag',
'contact_phone', 'deleted', 'office'
)), many=True)
def update_invigilators(self, data):
invigilator_data = data.get('invigilators')
invigilator_list = []
# NOTE: The not none test put in by Chris to fix an error
# PUT /bookings/recurring/uuid call
if invigilator_data is not None:
for invigilator in invigilator_data:
identifier = invigilator.get('invigilator_id')
invigilator_list.append(identifier)
data['invigilators'] = invigilator_list
return data
@post_dump(pass_many=True)
def fix_invigilators(self, data, many, **kwargs):
if not many:
data = self.update_invigilators(data)
else:
for booking in data:
booking = self.update_invigilators(booking)
return data
@pre_load
def convert_bool_to_int(self, in_data, **kwargs):
if type(in_data) == dict and 'sbc_staff_invigilated' in in_data and type(
in_data['sbc_staff_invigilated']) == bool:
in_data['sbc_staff_invigilated'] = 1 if in_data['sbc_staff_invigilated'] else 0
return in_data
class SendLineReminderWalkin(Resource):
citizen_schema = CitizenSchema()
office_schema = OfficeSchema()
walkinObj = WalkinDetail()
@jwt.has_one_of_roles([Role.internal_user.value])
@api_call_with_retry
def post(self):
try:
result = []
json_data = request.get_json()
previous_citizen_id = json_data.get('previous_citizen_id', False)
if previous_citizen_id:
previous_citizen = Citizen.query.filter_by(
citizen_id=previous_citizen_id).first()
# get nth line
nth_line = self.get_nth_line(previous_citizen)
# get all in Q + Agenda panel
res_list = []
# result= all citizen in q
result = self.walkinObj.get_all_citizen_in_q(
citizen=previous_citizen)
# process result
# am_on_true= means get all citizen in Q
booked_check_app, walkin_app = self.process_all_citizen_in_q(
result)
# sorting-maintaing the order group
res_list = tuple(booked_check_app + walkin_app)
# get the nth object in checkedin and walkin list
# bool checks for both False and 0
nth_app = False
if nth_line and len(res_list) >= int(nth_line) and (
int(nth_line) > 0):
nth_app = res_list[int(nth_line) - 1]
if nth_app['citizen_id']:
citizen = Citizen.query.filter_by(
citizen_id=nth_app['citizen_id']).first()
if (not (citizen.automatic_reminder_flag)
or (citizen.automatic_reminder_flag == 0)):
office_obj = Office.find_by_id(citizen.office_id)
if citizen.notification_phone:
citizen = self.send_sms_reminder(
citizen, office_obj)
citizen.automatic_reminder_flag = 1
if citizen.notification_email:
citizen = self.send_email_reminder(
citizen, office_obj)
citizen.automatic_reminder_flag = 1
db.session.add(citizen)
db.session.commit()
result = self.citizen_schema.dump(previous_citizen)
return {
'citizen': result,
'errors': self.citizen_schema.validate(previous_citizen)
}, 200
except ValidationError as err:
return {'message': err.messages}, 422
def get_nth_line(self, citizen):
my_office = Office.query.filter_by(office_id=citizen.office_id).first()
my_office_data = self.office_schema.dump(my_office)
nth_line = False
if my_office_data:
nth_line = my_office_data.get('automatic_reminder_at', False)
return nth_line
def process_all_citizen_in_q(self, result):
booked_check_app = []
walkin_app = []
for each in result:
data_dict = {}
if bool(each.get('service_reqs', False)):
for i in each['service_reqs']:
served_period = sorted(i['periods'],
key=lambda x: x['period_id'],
reverse=True)[0]
if served_period and (not (served_period['time_end']) and
(served_period['ps']['ps_name']
in ('Waiting', 'Invited'))):
not_booked_flag = False
data_dict = {}
data_dict['citizen_id'] = each.get('citizen_id', False)
data_dict['service_name'] = i['service']['parent'][
'service_name']
if (each.get('citizen_comments', '')):
if '|||' in each['citizen_comments']:
data_dict['flag'] = 'booked_app'
booked_check_app.append(data_dict)
data_dict = {}
break
else:
not_booked_flag = True
else:
not_booked_flag = True
if not_booked_flag and each.get(
'cs', False) and each['cs'].get(
'cs_state_name', '') == 'Active':
data_dict['flag'] = 'walkin_app'
data_dict['created_at'] = each.get(
'created_at', '')
walkin_app.append(data_dict)
data_dict = {}
break
return booked_check_app, walkin_app
def send_sms_reminder(self, citizen, office_obj):
if (citizen.notification_phone):
sms_sent = False
validate_check = True
# code/function call to send sms notification,
if citizen.reminder_flag and (citizen.reminder_flag == 2):
validate_check = False
if validate_check:
sms_sent = send_walkin_reminder_sms(
citizen, office_obj,
request.headers['Authorization'].replace('Bearer ', ''))
if (sms_sent):
flag_value = 1
if citizen.reminder_flag == 1:
flag_value = 2
citizen.reminder_flag = flag_value
citizen.notification_sent_time = datetime.utcnow()
return citizen
def send_email_reminder(self, citizen, office_obj):
if (citizen.notification_email):
# code/function call to send first email notification,
email_sent = False
validate_check = True
if citizen.reminder_flag and (citizen.reminder_flag == 2):
validate_check = False
if validate_check:
email_sent = get_walkin_reminder_email_contents(
citizen, office_obj)
if email_sent:
send_email(
request.headers['Authorization'].replace(
'Bearer ', ''), *email_sent)
flag_value = 1
if citizen.reminder_flag == 1:
flag_value = 2
citizen.reminder_flag = flag_value
citizen.notification_sent_time = datetime.utcnow()
return citizen
class WalkinDetail(Resource):
citizen_schema = CitizenSchema()
citizens_schema = CitizenSchema(many=True)
appointment_schema = AppointmentSchema(many=True)
office_schema = OfficeSchema()
def get(self, id):
try:
citizen = Citizen.query.filter_by(walkin_unique_id=id).join(CitizenState)\
.filter(CitizenState.cs_state_name == 'Active')\
.order_by(Citizen.citizen_id.desc()).first()
if citizen:
res_list = []
# office time zone
local_timezone = self.get_my_office_timezone(citizen=citizen)
# am i on hold
am_on_hold = self.am_i_on_hold(citizen)
show_estimate = application.config.get(
'SHOW_ESTIMATE_TIME_WALKIN', False)
# result= all citizen in q
result = self.get_all_citizen_in_q(citizen=citizen)
# process result
booked_check_app, walkin_app = self.process_all_citizen_in_q(
result, citizen, am_on_hold, local_timezone)
# get all app from agenda panel
result_in_book = self.get_all_app_from_agenda_panel(
citizen=citizen)
# processing agenda panel appointmnets:
booked_not_checkin = self.process_agenda_panel(
result_in_book, local_timezone)
# sorting-maintaing the order group
# serving people dont want see
res_list = tuple(booked_check_app + booked_not_checkin +
walkin_app)
return {
'citizen': res_list,
'show_estimate': show_estimate
}, 200
return {}
except exc.SQLAlchemyError as e:
print(e)
return {'message': 'API is down'}, 500
def get_my_office_timezone(self, citizen=False, office=False):
office_id = False
local_timezone = False
if citizen:
office_id = citizen.office_id
if office:
office_id = office.office_id
if office_id:
my_office = Office.query.filter_by(office_id=office_id).first()
my_office_data = self.office_schema.dump(my_office)
if my_office_data:
my_time_zone = my_office_data['timezone']['timezone_name']
local_timezone = pytz.timezone(my_time_zone)
return local_timezone
def am_i_on_hold(self, citizen):
my_result = self.citizen_schema.dump(citizen)
am_on_hold = False
citizen_service_reqs = my_result.get('service_reqs', [])
for j in citizen_service_reqs:
my_served_period = sorted(j['periods'],
key=lambda x: x['period_id'],
reverse=True)[0]
if my_served_period and (my_served_period['ps']['ps_name']
== 'On hold'):
am_on_hold = True
return am_on_hold
def get_all_citizen_in_q(self, citizen=False, office=False):
office_id = False
result = []
if citizen:
office_id = citizen.office_id
if office:
office_id = office.office_id
if office_id:
all_citizen_in_q = Citizen.query.filter_by(office_id=office_id) \
.join(CitizenState)\
.filter(CitizenState.cs_state_name == 'Active')\
.order_by(Citizen.priority) \
.join(Citizen.service_reqs).all()
result = self.citizens_schema.dump(all_citizen_in_q)
return result
def process_all_citizen_in_q(self, result, citizen, am_on_hold,
local_timezone):
booked_check_app = []
walkin_app = []
for each in result:
data_dict = {}
if bool(each.get('service_reqs', False)):
for i in each['service_reqs']:
served_period = sorted(i['periods'],
key=lambda x: x['period_id'],
reverse=True)[0]
if served_period and (not (served_period['time_end']) and
(served_period['ps']['ps_name']
in ('Waiting', 'Invited'))):
not_booked_flag = False
data_dict = {}
data_dict['ticket_number'] = each.get(
'ticket_number', '')
data_dict['walkin_unique_id'] = each.get(
'walkin_unique_id', '')
if (each.get('citizen_comments', '')):
if '|||' in each['citizen_comments']:
data_dict['flag'] = 'booked_app'
booked_check_app.append(data_dict)
data_dict = {}
break
else:
not_booked_flag = True
else:
not_booked_flag = True
if (not_booked_flag
and each.get('cs', False)) and each['cs'].get(
'cs_state_name', '') == 'Active':
each_time_obj = datetime.strptime(
each['start_time'], '%Y-%m-%dT%H:%M:%SZ')
# start
local_datetime_start = each_time_obj.replace(
tzinfo=pytz.utc).astimezone(local_timezone)
#end
local_datetime_end = citizen.start_time.replace(
tzinfo=pytz.utc).astimezone(local_timezone)
if am_on_hold or local_datetime_start <= local_datetime_end:
data_dict['flag'] = 'walkin_app'
walkin_app.append(data_dict)
data_dict = {}
break
return booked_check_app, walkin_app
def get_all_app_from_agenda_panel(self, citizen=False, office=False):
office_id = False
result_in_book = []
if citizen:
office_id = citizen.office_id
if office:
office_id = office.office_id
if office_id:
past_hour = datetime.utcnow() - timedelta(minutes=15)
future_hour = datetime.utcnow() + timedelta(minutes=15)
local_past = pytz.utc.localize(past_hour)
local_future = pytz.utc.localize(future_hour)
# getting agenda panel app
appointments = Appointment.query.filter_by(office_id=office_id)\
.filter(Appointment.start_time <= local_future)\
.filter(Appointment.start_time >= local_past)\
.filter(Appointment.checked_in_time == None)\
.order_by(Appointment.start_time)\
.all()
result_in_book = self.appointment_schema.dump(appointments)
return result_in_book
def process_agenda_panel(self, result_in_book, local_timezone):
booked_not_checkin = []
for app in result_in_book:
if not (app.get('is_draft', True)) and (app.get(
'blackout_flag',
'N') == 'N') and not (app.get('stat_flag', True)):
data_dict = {}
data_dict['flag'] = 'agenda_panel'
data_dict['start_time'] = app.get('start_time', '')
if data_dict['start_time'] and local_timezone:
if (len(data_dict['start_time']) >=
3) and ':' in data_dict['start_time'][-3]:
data_dict['start_time'] = '{}{}'.format(
data_dict['start_time'][:-3],
data_dict['start_time'][-2:])
utc_datetime = datetime.strptime(data_dict['start_time'],
'%Y-%m-%dT%H:%M:%S%z')
local_datetime = utc_datetime.replace(tzinfo=pytz.utc)
local_datetime = local_datetime.astimezone(local_timezone)
data_dict['start_time'] = local_datetime.strftime(
"%m/%d/%Y, %H:%M:%S")
booked_not_checkin.append(data_dict)
return booked_not_checkin
