class Appointment(Base):
appointment_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
office_id = db.Column(db.Integer,
db.ForeignKey("office.office_id"),
nullable=False)
service_id = db.Column(db.Integer,
db.ForeignKey("service.service_id"),
nullable=False)
start_time = db.Column(UtcDateTime, nullable=False)
end_time = db.Column(UtcDateTime, nullable=False)
checked_in_time = db.Column(UtcDateTime, nullable=True)
comments = db.Column(db.String(255), nullable=True)
citizen_name = db.Column(db.String(255), nullable=False)
contact_information = db.Column(db.String(255), nullable=True)
office = db.relationship("Office")
service = db.relationship("Service")
def __repr__(self):
return '<Appointment ID: (name={self.appointment_id!r})>'.format(
self=self)
def __init__(self, **kwargs):
super(Appointment, self).__init__(**kwargs)
class Period(Base):
period_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
sr_id = db.Column(db.Integer,
db.ForeignKey('servicereq.sr_id'),
nullable=False)
csr_id = db.Column(db.Integer, db.ForeignKey('csr.csr_id'), nullable=False)
reception_csr_ind = db.Column(db.Integer, nullable=False)
ps_id = db.Column(db.Integer,
db.ForeignKey('periodstate.ps_id'),
nullable=False)
time_start = db.Column(db.DateTime, nullable=False)
time_end = db.Column(db.DateTime, nullable=True)
csr = db.relationship("CSR", lazy='joined')
ps = db.relationship("PeriodState", lazy='joined')
sr = db.relationship("ServiceReq", lazy='joined')
def __repr__(self):
return '<Period id:(name={self.period_id!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Period, self).__init__(**kwargs)
@classmethod
def delete_periods(cls, period_ids: list):
"""Delete all periods with ids in the list provided."""
delete_qry = Period.__table__.delete().where(
Period.period_id.in_(period_ids))
db.session.execute(delete_qry)
db.session.commit()
class Booking(Base):
booking_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
room_id = db.Column(db.Integer,
db.ForeignKey("room.room_id"),
nullable=True)
invigilator_id = db.Column(db.Integer,
db.ForeignKey("invigilator.invigilator_id"),
nullable=True)
office_id = db.Column(db.Integer,
db.ForeignKey("office.office_id"),
nullable=False)
start_time = db.Column(UtcDateTime, nullable=False)
end_time = db.Column(UtcDateTime, nullable=False)
fees = db.Column(db.String(5), nullable=True)
booking_name = db.Column(db.String(150), nullable=True)
sbc_staff_invigilated = db.Column(db.Integer, default=0)
room = db.relationship("Room")
invigilator = db.relationship("Invigilator")
office = db.relationship("Office")
def __repr__(self):
return '<Booking Name: (name={self.booking_name!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Booking, self).__init__(**kwargs)
class Period(Base):
period_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
sr_id = db.Column(db.Integer,
db.ForeignKey('servicereq.sr_id'),
nullable=False)
csr_id = db.Column(db.Integer, db.ForeignKey('csr.csr_id'), nullable=False)
reception_csr_ind = db.Column(db.Integer, nullable=False)
ps_id = db.Column(db.Integer,
db.ForeignKey('periodstate.ps_id'),
nullable=False)
time_start = db.Column(db.DateTime, nullable=False)
time_end = db.Column(db.DateTime, nullable=True)
csr = db.relationship("CSR", lazy='joined')
ps = db.relationship("PeriodState", lazy='joined')
sr = db.relationship("ServiceReq", lazy='joined')
def __repr__(self):
return '<Period id:(name={self.period_id!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Period, self).__init__(**kwargs)
class Office(Base):
office_service = db.Table(
'office_service',
db.Column('office_id',
db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"),
primary_key=True),
db.Column('service_id',
db.Integer,
db.ForeignKey('service.service_id', ondelete="CASCADE"),
primary_key=True))
office_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
office_name = db.Column(db.String(100))
office_number = db.Column(db.Integer)
sb_id = db.Column(db.Integer, db.ForeignKey('smartboard.sb_id'))
deleted = db.Column(db.DateTime, nullable=True)
exams_enabled_ind = db.Column(db.Integer, nullable=False)
services = db.relationship("Service", secondary='office_service')
csrs = db.relationship('CSR')
citizens = db.relationship('Citizen', backref='office_citizens')
sb = db.relationship('SmartBoard')
exams = db.relationship("Exam")
rooms = db.relationship('Room')
def __repr__(self):
return self.office_name
def __init__(self, **kwargs):
super(Office, self).__init__(**kwargs)
class Role(Base):
role_permission = db.Table(
'role_permission',
db.Column('role_id',
db.Integer,
db.ForeignKey('role.role_id'),
primary_key=True,
nullable=False),
db.Column('permission_id',
db.Integer,
db.ForeignKey('permission.permission_id'),
primary_key=True,
nullable=False))
role_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
role_code = db.Column(db.String(100))
role_desc = db.Column(db.String(1000))
roles = db.relationship('CSR', lazy=False)
def __repr__(self):
return self.role_code
def __init__(self, **kwargs):
super(Role, self).__init__(**kwargs)
class CSR(Base):
csr_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
username = db.Column(db.String(150), nullable=False)
office_id = db.Column(db.Integer,
db.ForeignKey('office.office_id'),
nullable=False)
role_id = db.Column(db.Integer,
db.ForeignKey('role.role_id'),
nullable=False)
qt_xn_csr_ind = db.Column(db.Integer, nullable=False)
receptionist_ind = db.Column(db.Integer, nullable=False)
deleted = db.Column(db.DateTime, nullable=True)
csr_state_id = db.Column(db.Integer,
db.ForeignKey('csrstate.csr_state_id'),
nullable=False)
periods = db.relationship(
"Period",
primaryjoin="and_(CSR.csr_id==Period.csr_id,Period.time_end.is_(None))",
lazy='joined',
order_by='desc(Period.time_start)')
def __repr__(self):
return '<CSR Username:(name={self.username!r})>'.format(self=self)
def __init__(self, **kwargs):
super(CSR, self).__init__(**kwargs)
class Exam(Base):
exam_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
booking_id = db.Column(db.Integer, db.ForeignKey("booking.booking_id", ondelete="set null"), nullable=True)
exam_type_id = db.Column(db.Integer, db.ForeignKey("examtype.exam_type_id"), nullable=False)
office_id = db.Column(db.Integer, db.ForeignKey("office.office_id"), nullable=False)
event_id = db.Column(db.String(25), nullable=True)
exam_name = db.Column(db.String(50), nullable=False)
examinee_name = db.Column(db.String(50), nullable=True)
expiry_date = db.Column(db.DateTime, nullable=True)
notes = db.Column(db.String(400), nullable=True)
exam_received_date = db.Column(db.DateTime, nullable=True)
session_number = db.Column(db.Integer, nullable=True)
number_of_students = db.Column(db.Integer, nullable=True)
exam_method = db.Column(db.String(15), nullable=False)
deleted_date = db.Column(db.String(50), nullable=True)
exam_returned_date = db.Column(db.DateTime, nullable=True)
exam_returned_tracking_number = db.Column(db.String(255), nullable=True)
exam_written_ind = db.Column(db.Integer, nullable=False, default=0)
offsite_location = db.Column(db.String(50), nullable=True)
booking = db.relationship("Booking")
exam_type = db.relationship("ExamType")
office = db.relationship("Office")
def __repr__(self):
return '<Exam Name: (name={self.exam_name!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Exam, self).__init__(**kwargs)
class CSR(Base):
csr_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
username = db.Column(db.String(150), nullable=False, unique=True)
office_id = db.Column(db.Integer, db.ForeignKey('office.office_id'), nullable=False)
role_id = db.Column(db.Integer, db.ForeignKey('role.role_id'), nullable=False)
qt_xn_csr_ind = db.Column(db.Integer, nullable=False)
receptionist_ind = db.Column(db.Integer, nullable=False)
deleted = db.Column(db.DateTime, nullable=True)
csr_state_id = db.Column(db.Integer, db.ForeignKey('csrstate.csr_state_id'), nullable=False)
role = db.relationship("Role", lazy='joined')
office = db.relationship("Office", lazy='joined')
periods = db.relationship("Period", primaryjoin="and_(CSR.csr_id==Period.csr_id,Period.time_end.is_(None))",
order_by='desc(Period.time_start)')
def __repr__(self):
return self.username
def __init__(self, **kwargs):
super(CSR, self).__init__(**kwargs)
@classmethod
def find_by_username(cls, username):
key = 'csr_detail_%s' % username
if cache.get(key):
return cache.get(key)
csr = CSR.query.filter(CSR.deleted.is_(None)).filter_by(username=username.split("idir/")[-1]).first()
cache.set(key, csr)
return csr
def get_id(self):
return str(self.csr_id)
class Exam(Base):
exam_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
booking_id = db.Column(db.Integer,
db.ForeignKey("booking.booking_id",
ondelete="set null"),
nullable=True)
exam_type_id = db.Column(db.Integer,
db.ForeignKey("examtype.exam_type_id"),
nullable=True)
office_id = db.Column(db.Integer,
db.ForeignKey("office.office_id"),
nullable=False)
invigilator_id = db.Column(db.Integer,
db.ForeignKey("invigilator.invigilator_id"),
nullable=True)
event_id = db.Column(db.String(25), nullable=True)
exam_name = db.Column(db.String(50), nullable=False)
examinee_name = db.Column(db.String(50), nullable=True)
examinee_email = db.Column(db.String(400), nullable=True)
examinee_phone = db.Column(db.String(400), nullable=True)
expiry_date = db.Column(db.DateTime, nullable=True)
notes = db.Column(db.String(400), nullable=True)
exam_received_date = db.Column(db.DateTime, nullable=True)
session_number = db.Column(db.Integer, nullable=True)
number_of_students = db.Column(db.Integer, nullable=True)
exam_method = db.Column(db.String(15), nullable=False)
deleted_date = db.Column(db.String(50), nullable=True)
exam_returned_date = db.Column(db.DateTime, nullable=True)
exam_returned_tracking_number = db.Column(db.String(255), nullable=True)
exam_written_ind = db.Column(db.Integer, nullable=False, default=0)
offsite_location = db.Column(db.String(50), nullable=True)
bcmp_job_id = db.Column(db.String(100), nullable=True)
exam_destroyed_date = db.Column(db.String(50), nullable=True)
upload_received_ind = db.Column(db.Integer, nullable=True, default=0)
sbc_managed_ind = db.Column(db.Integer, nullable=True, default=0)
receipt = db.Column(db.String(50), nullable=True)
payee_ind = db.Column(db.Integer, nullable=True, default=0)
receipt_sent_ind = db.Column(db.Integer, nullable=True, default=0)
payee_email = db.Column(db.String(50), nullable=True)
payee_name = db.Column(db.String(50), nullable=True)
payee_phone = db.Column(db.String(50), nullable=True)
candidates_list = db.Column(db.JSON, nullable=True)
is_pesticide = db.Column(db.Integer, nullable=True, default=0)
booking = db.relationship("Booking")
exam_type = db.relationship("ExamType")
office = db.relationship("Office")
invigilator = db.relationship("Invigilator")
def __repr__(self):
return '<Exam Name: (name={self.exam_name!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Exam, self).__init__(**kwargs)
class Citizen(Base):
citizen_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
office_id = db.Column(db.Integer,
db.ForeignKey('office.office_id'),
nullable=False)
counter_id = db.Column(db.Integer,
db.ForeignKey('counter.counter_id'),
nullable=True)
ticket_number = db.Column(db.String(50), nullable=True)
citizen_name = db.Column(db.String(150), nullable=True)
citizen_comments = db.Column(db.String(1000), nullable=True)
qt_xn_citizen_ind = db.Column(db.Integer, default=0, nullable=False)
cs_id = db.Column(db.Integer,
db.ForeignKey('citizenstate.cs_id'),
nullable=False)
start_time = db.Column(db.DateTime, nullable=False)
accurate_time_ind = db.Column(db.Integer, nullable=False, default=1)
priority = db.Column(db.Integer, nullable=False, default=2)
service_reqs = db.relationship('ServiceReq',
lazy='joined',
order_by='ServiceReq.sr_id')
cs = db.relationship('CitizenState', lazy='joined')
office = db.relationship('Office', lazy='joined')
counter = db.relationship('Counter', lazy='joined')
def __repr__(self):
return '<Citizen Name:(name={self.citizen_name!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Citizen, self).__init__(**kwargs)
def get_active_service_request(self):
for sr in self.service_reqs:
if sr.sr_state.sr_code != 'Complete':
return sr
return None
def get_service_start_time(self):
time_end = self.start_time
# If a service request already exists, then the start time for the next
# service should be the end time of the previous service request
for s in self.service_reqs:
sorted_periods = sorted(s.periods, key=lambda p: p.time_start)
if len(sorted_periods) > 0 and sorted_periods[
-1].time_end is not None and sorted_periods[
-1].time_end > time_end:
time_end = sorted_periods[-1].time_end
return time_end
class Invigilator(Base):
invigilator_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
office_id = db.Column(db.Integer,
db.ForeignKey("office.office_id"),
nullable=False)
invigilator_name = db.Column(db.String(50), nullable=False)
invigilator_notes = db.Column(db.String(400), nullable=True)
contact_phone = db.Column(db.String(15), nullable=True)
contact_email = db.Column(db.String(50), nullable=True)
contract_number = db.Column(db.String(50), nullable=False)
contract_expiry_date = db.Column(db.String(50), nullable=False)
bookings = db.relationship("Booking")
office = db.relationship("Office", lazy="joined")
def __repr__(self):
return '<Invigilator Name: (name={self.invigilator_name!r})>'.format(
self=self)
def __init__(self, **kwargs):
super(Invigilator, self).__init__(**kwargs)
class Service(Base):
service_metadata = db.Table(
'service_metadata',
db.Column('service_id',
db.Integer,
db.ForeignKey('service.service_id'),
primary_key=True,
nullable=False),
db.Column('metadata_id',
db.Integer,
db.ForeignKey('metadata.metadata_id'),
primary_key=True,
nullable=False))
service_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
service_code = db.Column(db.String(50), nullable=False)
service_name = db.Column(db.String(500), nullable=False)
service_desc = db.Column(db.String(2000), nullable=False)
parent_id = db.Column(db.Integer,
db.ForeignKey('service.service_id'),
nullable=True)
deleted = db.Column(db.DateTime, nullable=True)
prefix = db.Column(db.String(10), nullable=False)
display_dashboard_ind = db.Column(db.Integer, nullable=False)
actual_service_ind = db.Column(db.Integer, nullable=False)
external_service_name = db.Column(db.String(100), nullable=True)
online_link = db.Column(db.String(200), nullable=True)
online_availability = db.Column(Enum(Availability))
timeslot_duration = db.Column(db.Integer, nullable=True)
is_dlkt = db.Column(Enum(YesNo))
email_paragraph = db.Column(db.String(2000), nullable=True)
css_colour = db.Column(db.String(50), nullable=True)
offices = db.relationship("Office", secondary='office_service')
parent = db.relationship("Service", remote_side=[service_id])
def __repr__(self):
return self.service_name
def __init__(self, **kwargs):
super(Service, self).__init__(**kwargs)
class Booking(Base):
booking_invigilators = db.Table(
'booking_invigilators',
db.Column('booking_id',
db.Integer,
db.ForeignKey('booking.booking_id', ondelete='CASCADE'),
primary_key=True),
db.Column('invigilator_id',
db.Integer,
db.ForeignKey('invigilator.invigilator_id',
ondelete='CASCADE'),
primary_key=True))
booking_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
room_id = db.Column(db.Integer,
db.ForeignKey("room.room_id"),
nullable=True)
shadow_invigilator_id = db.Column(db.Integer, nullable=True)
office_id = db.Column(db.Integer,
db.ForeignKey("office.office_id"),
nullable=False)
start_time = db.Column(UtcDateTime, nullable=False)
end_time = db.Column(UtcDateTime, nullable=False)
fees = db.Column(db.String(5), nullable=True)
booking_name = db.Column(db.String(150), nullable=True)
sbc_staff_invigilated = db.Column(db.Integer, default=0)
booking_contact_information = db.Column(db.String(256), nullable=True)
blackout_flag = db.Column(db.String(1), default='N', nullable=False)
blackout_notes = db.Column(db.String(255), nullable=True)
recurring_uuid = db.Column(db.String(255), nullable=True)
room = db.relationship("Room")
invigilators = db.relationship("Invigilator",
secondary=booking_invigilators)
office = db.relationship("Office")
def __repr__(self):
return '<Booking Name: (name={self.booking_name!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Booking, self).__init__(**kwargs)
class Service(Base):
service_metadata = db.Table('service_metadata',
db.Column('service_id', db.Integer, db.ForeignKey('service.service_id'), primary_key=True, nullable=False),
db.Column('metadata_id', db.Integer,db.ForeignKey('metadata.metadata_id'), primary_key=True, nullable=False))
service_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
service_code = db.Column(db.String(50), nullable=False)
service_name = db.Column(db.String(500), nullable=False)
service_desc = db.Column(db.String(2000), nullable=False)
parent_id = db.Column(db.Integer, db.ForeignKey('service.service_id'), nullable=True)
deleted = db.Column(db.DateTime, nullable=True)
prefix = db.Column(db.String(10), nullable=False)
display_dashboard_ind = db.Column(db.Integer, nullable=False)
actual_service_ind = db.Column(db.Integer, nullable=False)
parent = db.relationship("Service", remote_side=[service_id])
def __repr__(self):
return '<Service Name:(name={self.service_name!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Service, self).__init__(**kwargs)
class Room(Base):
room_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
office_id = db.Column(db.Integer, db.ForeignKey("office.office_id"), nullable=False)
room_name = db.Column(db.String(50), nullable=False)
capacity = db.Column(db.Integer, nullable=False)
color = db.Column(db.String(25), nullable=False)
booking = db.relationship("Booking")
office = db.relationship("Office", lazy='joined')
def __repr__(self):
return '<Room Name: (name={self.room_name!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Room, self).__init__(**kwargs)
class TimeSlot(Base):
time_slot_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
office_id = db.Column(db.Integer,
db.ForeignKey('office.office_id'),
nullable=True)
start_time = db.Column(db.Time, nullable=False)
end_time = db.Column(db.Time, nullable=False)
day_of_week = db.Column(postgresql.ARRAY(String), nullable=False)
no_of_slots = db.Column(db.Integer, nullable=False)
office = db.relationship("Office", lazy='joined')
format_string = 'time_slot_%s'
def __repr__(self):
return '<Timselot :(start_time={self.start_time!r}, end_time={self.end_time!r}, day_of_week={self.day_of_week!r})>'.format(
self=self)
def __init__(self, **kwargs):
super(TimeSlot, self).__init__(**kwargs)
class Appointment(Base):
appointment_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
office_id = db.Column(db.Integer,
db.ForeignKey("office.office_id"),
nullable=False)
service_id = db.Column(db.Integer,
db.ForeignKey("service.service_id"),
nullable=True)
citizen_id = db.Column(db.Integer,
db.ForeignKey("citizen.citizen_id"),
nullable=True)
start_time = db.Column(UtcDateTime, nullable=False)
end_time = db.Column(UtcDateTime, nullable=False)
checked_in_time = db.Column(UtcDateTime, nullable=True)
comments = db.Column(db.String(255), nullable=True)
citizen_name = db.Column(db.String(255), nullable=False)
contact_information = db.Column(db.String(255), nullable=True)
blackout_flag = db.Column(db.String(1), default='N', nullable=False)
recurring_uuid = db.Column(db.String(255), nullable=True)
online_flag = db.Column(db.Boolean(), nullable=True, default=False)
office = db.relationship("Office")
service = db.relationship("Service")
def __repr__(self):
return '<Appointment ID: (name={self.appointment_id!r})>'.format(
self=self)
def __init__(self, **kwargs):
super(Appointment, self).__init__(**kwargs)
@classmethod
def find_appointment_availability(cls, office_id: int, timezone: str,
first_date: datetime,
last_date: datetime):
"""Find appointment availability for dates in a month"""
query = db.session.query(Appointment).filter(
func.date_trunc(
'day',
func.timezone(timezone, Appointment.start_time)).between(
func.date_trunc('day', func.timezone(timezone,
first_date)),
func.date_trunc('day', func.timezone(timezone,
last_date))))
query = query.filter(Appointment.office_id == office_id)
query = query.order_by(Appointment.start_time.asc())
return query.all()
@classmethod
def find_next_day_appointments(cls):
"""Find next day appointments."""
from app.models.theq import Office, PublicUser, Citizen, Timezone
tomorrow = datetime.now() + timedelta(days=1)
tomorrow = tomorrow.astimezone(tz.tzlocal())
query = db.session.query(Appointment, Office, Timezone, PublicUser). \
join(Citizen, Citizen.citizen_id == Appointment.citizen_id). \
join(Office, Office.office_id == Appointment.office_id). \
join(Timezone, Timezone.timezone_id == Office.timezone_id). \
outerjoin(PublicUser, PublicUser.user_id == Citizen.user_id). \
filter(func.date_trunc('day',
func.timezone(Timezone.timezone_name,Appointment.start_time)) ==
func.date_trunc('day', tomorrow))
return query.all()
@classmethod
def get_appointment_conflicts(cls,
office_id: int,
start_time: str,
end_time: str,
appointment_id=None):
"""Find appointment availability for dates in a month"""
from app.models.theq import Office, PublicUser, Citizen, Timezone
start_datetime = parse(start_time)
end_datetime = parse(end_time)
start_time_1 = start_datetime
end_time_1 = end_datetime - timedelta(minutes=1)
start_time_2 = start_datetime + timedelta(minutes=1)
end_time_2 = end_datetime
query = db.session.query(Appointment, Office, Timezone, PublicUser). \
join(Office, Office.office_id == Appointment.office_id). \
join(Timezone, Timezone.timezone_id == Office.timezone_id). \
join(Citizen, Citizen.citizen_id == Appointment.citizen_id). \
outerjoin(PublicUser, PublicUser.user_id == Citizen.user_id). \
filter(or_(Appointment.start_time.between(start_time_1, end_time_1), Appointment.end_time.between( start_time_2, end_time_2)))
query = query.filter(Appointment.office_id == office_id)
if appointment_id:
query = query.filter(Appointment.appointment_id != appointment_id)
return query.all()
@classmethod
def find_by_username_and_office_id(cls,
office_id: int,
user_name: str,
start_time,
timezone,
appointment_id=None):
"""Find apponintment for the user at an office for a date."""
from app.models.theq import PublicUser, Citizen
start_datetime = parse(start_time)
query = db.session.query(Appointment). \
join(Citizen). \
join(PublicUser). \
filter(Appointment.citizen_id == Citizen.citizen_id). \
filter(Citizen.user_id == PublicUser.user_id). \
filter(func.date_trunc('day', func.timezone(timezone, Appointment.start_time)) == (func.date_trunc('day', func.timezone(timezone, start_datetime)))). \
filter(Appointment.office_id == office_id). \
filter(PublicUser.username == user_name). \
filter(Appointment.checked_in_time.is_(None))
if appointment_id:
query = query.filter(Appointment.appointment_id != appointment_id)
return query.all()
@classmethod
def delete_appointments(cls, appointment_ids: list):
"""Delete all appointments with ids in the list provided."""
delete_qry = Appointment.__table__.delete().where(
Appointment.appointment_id.in_(appointment_ids))
db.session.execute(delete_qry)
db.session.commit()
class Citizen(Base):
citizen_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
office_id = db.Column(db.Integer,
db.ForeignKey('office.office_id'),
nullable=False)
counter_id = db.Column(db.Integer,
db.ForeignKey('counter.counter_id'),
nullable=True)
user_id = db.Column(db.Integer,
db.ForeignKey('publicuser.user_id'),
nullable=True)
ticket_number = db.Column(db.String(50), nullable=True)
citizen_name = db.Column(db.String(150), nullable=True)
citizen_comments = db.Column(db.String(1000), nullable=True)
qt_xn_citizen_ind = db.Column(db.Integer, default=0, nullable=False)
cs_id = db.Column(db.Integer,
db.ForeignKey('citizenstate.cs_id'),
nullable=False)
start_time = db.Column(db.DateTime, nullable=False)
accurate_time_ind = db.Column(db.Integer, nullable=False, default=1)
priority = db.Column(db.Integer, nullable=False, default=2)
service_reqs = db.relationship('ServiceReq',
lazy='joined',
order_by='ServiceReq.sr_id')
cs = db.relationship('CitizenState', lazy='joined')
office = db.relationship('Office', lazy='joined')
counter = db.relationship('Counter', lazy='joined')
user = db.relationship('PublicUser', lazy='joined')
# for walk-in notification
notification_phone = db.Column(db.String(100), nullable=True)
notification_email = db.Column(db.String(100), nullable=True)
notification_sent_time = db.Column(db.DateTime, nullable=True)
reminder_flag = db.Column(db.Integer, nullable=True)
walkin_unique_id = db.Column(db.String(500), nullable=True)
automatic_reminder_flag = db.Column(db.Integer, nullable=True)
# digital signage
created_at = db.Column(UtcDateTime, nullable=True, default=utcnow())
def __repr__(self):
return '<Citizen Name:(name={self.citizen_name!r})>'.format(self=self)
def __init__(self, **kwargs):
super(Citizen, self).__init__(**kwargs)
def get_active_service_request(self):
for sr in self.service_reqs:
if sr.sr_state.sr_code != 'Complete':
return sr
return None
def get_service_start_time(self):
time_end = self.start_time
# If a service request already exists, then the start time for the next
# service should be the end time of the previous service request
for s in self.service_reqs:
sorted_periods = sorted(s.periods, key=lambda p: p.time_start)
if len(sorted_periods) > 0 and sorted_periods[
-1].time_end is not None and sorted_periods[
-1].time_end > time_end:
time_end = sorted_periods[-1].time_end
return time_end
@classmethod
def find_citizen_by_user_id(cls, user_id, office_id):
"""Find citizen record by user id."""
return cls.query.filter(Citizen.user_id == user_id).filter(
Citizen.office_id == office_id).one_or_none()
@classmethod
def find_citizen_by_id(cls, citizen_id):
"""Find citizen record by user id."""
return cls.query.get(citizen_id)
class Office(Base):
office_service = db.Table(
'office_service',
db.Column('office_id',
db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"),
primary_key=True),
db.Column('service_id',
db.Integer,
db.ForeignKey('service.service_id', ondelete="CASCADE"),
primary_key=True))
office_quick_list = db.Table(
'office_quick_list',
db.Column('office_id',
db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"),
primary_key=True),
db.Column('service_id',
db.Integer,
db.ForeignKey('service.service_id', ondelete="CASCADE"),
primary_key=True))
office_back_office_list = db.Table(
'office_back_office_list',
db.Column('office_id',
db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"),
primary_key=True),
db.Column('service_id',
db.Integer,
db.ForeignKey('service.service_id', ondelete="CASCADE"),
primary_key=True))
office_counter = db.Table(
'office_counter',
db.Column('office_id',
db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"),
primary_key=True),
db.Column('counter_id',
db.Integer,
db.ForeignKey('counter.counter_id', ondelete="CASCADE"),
primary_key=True))
office_timeslot = db.Table(
'office_timeslot',
db.Column('office_id',
db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"),
primary_key=True),
db.Column('time_slot_id',
db.Integer,
db.ForeignKey('timeslot.time_slot_id', ondelete="CASCADE"),
primary_key=True))
office_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
office_name = db.Column(db.String(100))
office_number = db.Column(db.Integer)
sb_id = db.Column(db.Integer, db.ForeignKey('smartboard.sb_id'))
deleted = db.Column(db.DateTime, nullable=True)
exams_enabled_ind = db.Column(db.Integer, nullable=False)
appointments_enabled_ind = db.Column(db.Integer, nullable=False, default=0)
timezone_id = db.Column(db.Integer,
db.ForeignKey('timezone.timezone_id'),
nullable=True)
latitude = db.Column(db.Float)
longitude = db.Column(db.Float)
office_appointment_message = db.Column(db.String(1000))
appointments_days_limit = db.Column(db.Integer, default=30)
appointment_duration = db.Column(db.Integer, default=30)
max_person_appointment_per_day = db.Column(db.Integer, default=1)
civic_address = db.Column(db.String(200))
telephone = db.Column(db.String(20))
online_status = db.Column(Enum(Status))
number_of_dlkt = db.Column(db.Integer, nullable=True)
office_email_paragraph = db.Column(db.String(2000), nullable=True)
external_map_link = db.Column(db.String(500), nullable=True)
soonest_appointment = db.Column(db.Integer, default=0)
counters = db.relationship("Counter", secondary='office_counter')
services = db.relationship("Service", secondary='office_service')
quick_list = db.relationship("Service", secondary='office_quick_list')
back_office_list = db.relationship("Service",
secondary='office_back_office_list')
csrs = db.relationship('CSR')
citizens = db.relationship('Citizen', backref='office_citizens')
timeslots = db.relationship('TimeSlot')
sb = db.relationship('SmartBoard')
timezone = db.relationship('Timezone')
exams = db.relationship("Exam")
rooms = db.relationship('Room')
# for walk-in notifications
check_in_notification = db.Column(db.Integer)
check_in_reminder_msg = db.Column(db.Text)
automatic_reminder_at = db.Column(db.Integer)
# for Digital Signage
currently_waiting = db.Column(db.Integer)
digital_signage_message = db.Column(db.Integer)
digital_signage_message_1 = db.Column(db.Text)
digital_signage_message_2 = db.Column(db.Text)
digital_signage_message_3 = db.Column(db.Text)
show_currently_waiting_bottom = db.Column(db.Integer)
format_string = 'office_%s'
offices_cache_key: str = 'active_offices'
def __repr__(self):
return self.office_name
def __init__(self, **kwargs):
super(Office, self).__init__(**kwargs)
@classmethod
def find_by_id(cls, office_id: int):
"""Return a Office by office_id."""
key = Office.format_string % office_id
office = cache.get(key)
if not office:
office = cls.query.get(office_id)
office.timeslots
office.timezone
return office
@classmethod
def build_cache(cls):
"""Build cache."""
try:
all_offices = cls.query.all()
for office in all_offices:
key = Office.format_string % office.office_id
office.timeslots
office.timezone
cache.set(key, office)
except Exception:
print('Error on building cache')
@classmethod
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)).order_by(
Office.office_name))
cache.set(Office.offices_cache_key, active_offices)
return active_offices
@classmethod
def clear_offices_cache(cls):
"""Clear active offices cache."""
cache.delete(Office.offices_cache_key)
class CSR(Base):
csr_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
username = db.Column(db.String(150), nullable=False, unique=True)
office_id = db.Column(db.Integer,
db.ForeignKey('office.office_id'),
nullable=False)
counter_id = db.Column(db.Integer,
db.ForeignKey('counter.counter_id'),
nullable=True)
role_id = db.Column(db.Integer,
db.ForeignKey('role.role_id'),
nullable=False)
qt_xn_csr_ind = db.Column(db.Integer, nullable=False,
default=0) # deprecated
receptionist_ind = db.Column(db.Integer, nullable=False)
deleted = db.Column(db.DateTime, nullable=True)
csr_state_id = db.Column(db.Integer,
db.ForeignKey('csrstate.csr_state_id'),
nullable=False)
ita_designate = db.Column(db.Integer, default=0, nullable=False)
pesticide_designate = db.Column(db.Integer, default=0, nullable=False)
finance_designate = db.Column(db.Integer, default=0, nullable=False)
liaison_designate = db.Column(db.Integer, default=0, nullable=False)
role = db.relationship("Role", lazy='joined')
office = db.relationship("Office", lazy='joined')
counter = db.relationship("Counter", lazy='joined')
periods = db.relationship(
"Period",
primaryjoin="and_(CSR.csr_id==Period.csr_id,Period.time_end.is_(None))",
order_by='desc(Period.time_start)')
format_string = 'csr_detail_%s'
def __repr__(self):
return self.username
def __init__(self, **kwargs):
super(CSR, self).__init__(**kwargs)
@classmethod
def find_by_username(cls, username):
# Possible keycloak->TheQ id values are user@idir->user, idir/user->user or user@bceid->user@bceid
idir_id = username.split("idir/")[-1].lower()
if "@idir" in username:
idir_id = username.split("@idir")[0].lower()
key = CSR.format_string % idir_id
if cache.get(key):
return cache.get(key)
csr = CSR.query.filter(
CSR.deleted.is_(None)).filter(CSR.username == idir_id).first()
cache.set(key, csr)
return csr
@classmethod
def find_by_userid(cls, userid):
csr = CSR.query.filter(
CSR.deleted.is_(None)).filter_by(csr_id=userid).first()
key = (CSR.format_string % csr.username).lower()
if cache.get(key):
return cache.get(key)
cache.set(key, csr)
return csr
@classmethod
def delete_user_cache(cls, username):
idir_id = username.split("idir/")[-1]
key = (CSR.format_string % idir_id).lower()
cache.delete(key)
@classmethod
def update_user_cache(cls, userid):
csr_db = CSR.query.filter_by(csr_id=userid).first()
key = (CSR.format_string % csr_db.username).lower()
cache.set(key, csr_db)
def get_id(self):
return str(self.csr_id)
class Appointment(Base):
__versioned__ = {'exclude': []}
appointment_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
office_id = db.Column(db.Integer,
db.ForeignKey("office.office_id"),
nullable=False)
service_id = db.Column(db.Integer,
db.ForeignKey("service.service_id"),
nullable=True)
citizen_id = db.Column(db.Integer,
db.ForeignKey("citizen.citizen_id"),
nullable=True)
start_time = db.Column(UtcDateTime, nullable=False)
end_time = db.Column(UtcDateTime, nullable=False)
checked_in_time = db.Column(UtcDateTime, nullable=True)
comments = db.Column(db.String(255), nullable=True)
citizen_name = db.Column(db.String(255), nullable=False)
contact_information = db.Column(db.String(255), nullable=True)
blackout_flag = db.Column(db.String(1), default='N', nullable=False)
recurring_uuid = db.Column(db.String(255), nullable=True)
online_flag = db.Column(db.Boolean(), nullable=True, default=False)
is_draft = db.Column(db.Boolean(), nullable=True, default=False)
created_at = db.Column(UtcDateTime, nullable=True, default=utcnow())
stat_flag = db.Column(db.Boolean, default=False, nullable=False)
updated_at = db.Column(UtcDateTime, onupdate=utcnow(), default=None)
office = db.relationship("Office")
service = db.relationship("Service")
def __repr__(self):
return '<Appointment ID: (name={self.appointment_id!r})>'.format(
self=self)
def __init__(self, **kwargs):
super(Appointment, self).__init__(**kwargs)
@declared_attr
def updated_by(cls): # pylint:disable=no-self-argument, # noqa: N805
"""Return updated by."""
return db.Column('updated_by',
db.String(),
nullable=True,
onupdate=cls._get_user_name)
@staticmethod
def _get_user_name(**kwargs):
"""Return current user display name."""
_name: str = None
if g and 'jwt_oidc_token_info' in g:
_name = g.jwt_oidc_token_info.get('display_name')
return _name
@classmethod
def find_appointment_availability(cls, office_id: int, timezone: str,
first_date: datetime,
last_date: datetime):
"""Find appointment availability for dates in a month"""
query = db.session.query(Appointment).filter(
func.date_trunc(
'day',
func.timezone(timezone, Appointment.start_time)).between(
func.date_trunc('day', func.timezone(timezone,
first_date)),
func.date_trunc('day', func.timezone(timezone,
last_date))))
query = query.filter(Appointment.office_id == office_id)
query = query.order_by(Appointment.start_time.asc())
return query.all()
@classmethod
def find_next_day_appointments(cls):
"""Find next day appointments."""
from app.models.theq import Office, PublicUser, Citizen, Timezone
tomorrow = current_pacific_time() + timedelta(days=1)
query = db.session.query(Appointment, Office, Timezone, PublicUser). \
join(Citizen, Citizen.citizen_id == Appointment.citizen_id). \
join(Office, Office.office_id == Appointment.office_id). \
join(Timezone, Timezone.timezone_id == Office.timezone_id). \
outerjoin(PublicUser, PublicUser.user_id == Citizen.user_id). \
filter(func.date_trunc('day',
func.timezone(Timezone.timezone_name, Appointment.start_time)) ==
tomorrow.strftime("%Y-%m-%d 00:00:00"))
return query.all()
@classmethod
def get_appointment_conflicts(cls,
office_id: int,
start_time: str,
end_time: str,
appointment_id=None):
"""Find appointment availability for dates in a month"""
from app.models.theq import Office, PublicUser, Citizen, Timezone
start_datetime = parse(start_time)
end_datetime = parse(end_time)
start_time_1 = start_datetime
end_time_1 = end_datetime - timedelta(minutes=1)
start_time_2 = start_datetime + timedelta(minutes=1)
end_time_2 = end_datetime
query = db.session.query(Appointment, Office, Timezone, PublicUser). \
join(Office, Office.office_id == Appointment.office_id). \
join(Timezone, Timezone.timezone_id == Office.timezone_id). \
join(Citizen, Citizen.citizen_id == Appointment.citizen_id). \
outerjoin(PublicUser, PublicUser.user_id == Citizen.user_id). \
filter(or_(Appointment.start_time.between(start_time_1, end_time_1), Appointment.end_time.between( start_time_2, end_time_2)))
query = query.filter(Appointment.office_id == office_id)
if appointment_id:
query = query.filter(Appointment.appointment_id != appointment_id)
return query.all()
@classmethod
def find_by_username_and_office_id(cls,
office_id: int,
user_name: str,
start_time,
timezone,
appointment_id=None):
"""Find apponintment for the user at an office for a date."""
from app.models.theq import PublicUser, Citizen
start_datetime = parse(start_time)
query = db.session.query(Appointment). \
join(Citizen). \
join(PublicUser). \
filter(Appointment.citizen_id == Citizen.citizen_id). \
filter(Citizen.user_id == PublicUser.user_id). \
filter(func.date_trunc('day', func.timezone(timezone, Appointment.start_time)) == (func.date_trunc('day', func.timezone(timezone, start_datetime)))). \
filter(Appointment.office_id == office_id). \
filter(PublicUser.username == user_name). \
filter(Appointment.checked_in_time.is_(None))
if appointment_id:
query = query.filter(Appointment.appointment_id != appointment_id)
return query.all()
@classmethod
def delete_appointments(cls, appointment_ids: list):
"""Delete all appointments with ids in the list provided."""
delete_qry = Appointment.__table__.delete().where(
Appointment.appointment_id.in_(appointment_ids))
db.session.execute(delete_qry)
db.session.commit()
@classmethod
def find_expired_drafts(cls):
"""Find all is_draft appointments created over expiration cutoff ago."""
EXPIRATION_CUTOFF = timedelta(minutes=15)
expiry_limit = datetime.utcnow().replace(
tzinfo=timezone.utc) - EXPIRATION_CUTOFF
query = db.session.query(Appointment). \
filter(Appointment.is_draft.is_(True)). \
filter(Appointment.created_at < expiry_limit)
return query.all()
@classmethod
def delete_draft(cls, draft_appointment_ids):
"""Deletes a draft appointment by id."""
delete_qry = Appointment.__table__.delete().where(
and_(Appointment.appointment_id.in_(draft_appointment_ids),
Appointment.is_draft.is_(True)))
db.session.execute(delete_qry)
db.session.commit()
@classmethod
def delete_expired_drafts(cls):
"""Deletes all expired drafts."""
drafts = Appointment.find_expired_drafts()
draft_ids = [appointment.appointment_id for appointment in drafts]
Appointment.delete_appointments(draft_ids)
return draft_ids
class ServiceReq(Base):
sr_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
citizen_id = db.Column(db.Integer,
db.ForeignKey('citizen.citizen_id'),
nullable=False)
quantity = db.Column(db.Integer, default=1, nullable=False)
channel_id = db.Column(db.Integer,
db.ForeignKey('channel.channel_id'),
nullable=False)
service_id = db.Column(db.Integer,
db.ForeignKey('service.service_id'),
nullable=False)
sr_state_id = db.Column(db.Integer,
db.ForeignKey('srstate.sr_state_id'),
nullable=False)
channel = db.relationship('Channel')
periods = db.relationship('Period',
backref=db.backref("request_periods",
lazy=False),
lazy='joined',
order_by='Period.period_id')
sr_state = db.relationship('SRState', lazy='joined')
citizen = db.relationship('Citizen')
service = db.relationship('Service', lazy='joined')
def __init__(self, **kwargs):
super(ServiceReq, self).__init__(**kwargs)
def get_active_period(self):
sorted_periods = sorted(self.periods, key=lambda p: p.period_id)
return sorted_periods[-1]
def invite(self, csr, snowplow_event="use_period"):
active_period = self.get_active_period()
if active_period.ps.ps_name in ["Invited", "Being Served", "On Hold"]:
raise TypeError(
"You cannot invite a citizen that has already been invited")
# Calculate what Snowplow event to call.
if (snowplow_event == "use_period"):
if (active_period.ps.ps_name == "Waiting"):
snowplow_event = "invitefromlist"
else:
snowplow_event = "invitefromhold"
active_period.time_end = datetime.now()
# db.session.add(active_period)
period_state_invite = PeriodState.query.filter_by(
ps_name="Invited").first()
new_period = Period(sr_id=self.sr_id,
csr_id=csr.csr_id,
reception_csr_ind=csr.receptionist_ind,
ps_id=period_state_invite.ps_id,
time_start=datetime.now())
self.periods.append(new_period)
SnowPlow.snowplow_event(self.citizen_id, csr, snowplow_event)
def add_to_queue(self, csr, snowplow_event):
active_period = self.get_active_period()
active_period.time_end = datetime.now()
#db.session.add(active_period)
period_state_waiting = PeriodState.query.filter_by(
ps_name="Waiting").first()
new_period = Period(sr_id=self.sr_id,
csr_id=csr.csr_id,
reception_csr_ind=csr.receptionist_ind,
ps_id=period_state_waiting.ps_id,
time_start=datetime.now())
self.periods.append(new_period)
SnowPlow.snowplow_event(self.citizen_id, csr, snowplow_event)
def begin_service(self, csr, snowplow_event):
active_period = self.get_active_period()
if active_period.ps.ps_name in ["Being Served"]:
raise TypeError(
"You cannot begin serving a citizen that is already being served"
)
active_period.time_end = datetime.now()
# db.session.add(active_period)
period_state_being_served = PeriodState.query.filter_by(
ps_name="Being Served").first()
new_period = Period(sr_id=self.sr_id,
csr_id=csr.csr_id,
reception_csr_ind=csr.receptionist_ind,
ps_id=period_state_being_served.ps_id,
time_start=datetime.now())
self.periods.append(new_period)
# Calculate number of active periods, for Snowplow call.
period_count = len(self.periods)
SnowPlow.snowplow_event(self.citizen_id,
csr,
snowplow_event,
period_count=period_count)
def place_on_hold(self, csr):
active_period = self.get_active_period()
active_period.time_end = datetime.now()
# db.session.add(active_period)
period_state_on_hold = PeriodState.query.filter_by(
ps_name="On hold").first()
new_period = Period(sr_id=self.sr_id,
csr_id=csr.csr_id,
reception_csr_ind=csr.receptionist_ind,
ps_id=period_state_on_hold.ps_id,
time_start=datetime.now())
self.periods.append(new_period)
SnowPlow.snowplow_event(self.citizen_id, csr, "hold")
def finish_service(self, csr, clear_comments=True):
active_period = self.get_active_period()
active_period.time_end = datetime.now()
if clear_comments:
self.citizen.citizen_comments = None
class Office(Base):
office_service = db.Table(
'office_service',
db.Column('office_id', db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"), primary_key=True),
db.Column('service_id', db.Integer,
db.ForeignKey('service.service_id', ondelete="CASCADE"), primary_key=True))
office_quick_list = db.Table(
'office_quick_list',
db.Column('office_id', db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"), primary_key=True),
db.Column('service_id', db.Integer,
db.ForeignKey('service.service_id', ondelete="CASCADE"), primary_key=True))
office_back_office_list = db.Table(
'office_back_office_list',
db.Column('office_id', db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"), primary_key=True),
db.Column('service_id', db.Integer,
db.ForeignKey('service.service_id', ondelete="CASCADE"), primary_key=True))
office_counter= db.Table(
'office_counter',
db.Column('office_id', db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"), primary_key=True),
db.Column('counter_id', db.Integer,
db.ForeignKey('counter.counter_id', ondelete="CASCADE"), primary_key=True))
office_timeslot = db.Table(
'office_timeslot',
db.Column('office_id', db.Integer,
db.ForeignKey('office.office_id', ondelete="CASCADE"), primary_key=True),
db.Column('time_slot_id', db.Integer,
db.ForeignKey('timeslot.time_slot_id', ondelete="CASCADE"), primary_key=True))
office_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
office_name = db.Column(db.String(100))
office_number = db.Column(db.Integer)
sb_id = db.Column(db.Integer, db.ForeignKey('smartboard.sb_id'))
deleted = db.Column(db.DateTime, nullable=True)
exams_enabled_ind = db.Column(db.Integer, nullable=False)
appointments_enabled_ind = db.Column(db.Integer, nullable=False, default=0)
timezone_id = db.Column(db.Integer, db.ForeignKey('timezone.timezone_id'), nullable=True)
latitude = db.Column(db.Float)
longitude = db.Column(db.Float)
office_appointment_message = db.Column(db.String(1000))
appointments_days_limit = db.Column(db.Integer, default=30)
appointment_duration = db.Column(db.Integer, default=30)
max_person_appointment_per_day = db.Column(db.Integer, default=1)
civic_address = db.Column(db.String(200))
telephone = db.Column(db.String(20))
# disable_online_appointment = db.Column(db.Boolean, default=False)
online_status = db.Column(Enum(Status))
counters = db.relationship("Counter", secondary='office_counter')
services = db.relationship("Service", secondary='office_service')
quick_list = db.relationship("Service", secondary='office_quick_list')
back_office_list = db.relationship("Service", secondary='office_back_office_list')
csrs = db.relationship('CSR')
citizens = db.relationship('Citizen', backref='office_citizens')
timeslots = db.relationship('TimeSlot', secondary='office_timeslot')
sb = db.relationship('SmartBoard')
timezone = db.relationship('Timezone')
exams = db.relationship("Exam")
rooms = db.relationship('Room')
format_string = 'office_%s'
def __repr__(self):
return self.office_name
def __init__(self, **kwargs):
super(Office, self).__init__(**kwargs)
@classmethod
def find_by_id(cls, office_id: int):
"""Return a Office by office_id."""
key = Office.format_string % office_id
office = cache.get(key)
if not office:
office = cls.query.get(office_id)
office.timeslots
office.timezone
#TODO cache.set(key, office)
# print(office.timeslots)
return office
@classmethod
def build_cache(cls):
"""Build cache."""
try:
all_offices = cls.query.all()
for office in all_offices:
key = Office.format_string % office.office_id
office.timeslots
office.timezone
cache.set(key, office)
except Exception as e:
print('Error on building cache')
class ServiceReq(Base):
sr_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
citizen_id = db.Column(db.Integer,
db.ForeignKey('citizen.citizen_id'),
nullable=False)
quantity = db.Column(db.Integer, default=1, nullable=False)
channel_id = db.Column(db.Integer,
db.ForeignKey('channel.channel_id'),
nullable=False)
service_id = db.Column(db.Integer,
db.ForeignKey('service.service_id'),
nullable=False)
sr_state_id = db.Column(db.Integer,
db.ForeignKey('srstate.sr_state_id'),
nullable=False)
sr_number = db.Column(db.Integer, default=1, nullable=False)
channel = db.relationship('Channel')
periods = db.relationship('Period',
backref=db.backref("request_periods",
lazy=False),
lazy='joined',
order_by='Period.period_id')
sr_state = db.relationship('SRState', lazy='joined')
citizen = db.relationship('Citizen')
service = db.relationship('Service', lazy='joined')
def __init__(self, **kwargs):
super(ServiceReq, self).__init__(**kwargs)
def get_active_period(self):
sorted_periods = sorted(self.periods, key=lambda p: p.period_id)
return sorted_periods[-1]
def invite(self, csr, invite_type, sr_count=1):
active_period = self.get_active_period()
if active_period.ps.ps_name in ["Invited", "Being Served", "On hold"]:
raise TypeError(
"You cannot invite a citizen that has already been invited")
# If a generic invite type, event is either invitecitizen or returninvite.
if invite_type == "generic":
# If only one SR, one period, an invitecitizen call, from First Time in Line state.
if sr_count == 1 and len(self.periods) == 2:
snowplow_event = "invitecitizen"
# Otherwise from the Back in Line state.
else:
snowplow_event = "returninvite"
# A specific invite type. Event is invitefromlist, returnfromlist or invitefromhold
else:
# If only one SR, one period, an invitefromlist call, from First Time in Line state.
if sr_count == 1 and len(self.periods) == 2:
snowplow_event = "invitefromlist"
# Either from back in line or hold state.
else:
if active_period.ps.ps_name == "Waiting":
snowplow_event = "returnfromlist"
else:
snowplow_event = "invitefromhold"
active_period.time_end = datetime.utcnow()
# db.session.add(active_period)
period_state_invite = PeriodState.get_state_by_name("Invited")
new_period = Period(sr_id=self.sr_id,
csr_id=csr.csr_id,
reception_csr_ind=csr.receptionist_ind,
ps_id=period_state_invite.ps_id,
time_start=datetime.utcnow())
self.periods.append(new_period)
SnowPlow.snowplow_event(self.citizen_id,
csr,
snowplow_event,
current_sr_number=self.sr_number)
def add_to_queue(self, csr, snowplow_event):
active_period = self.get_active_period()
active_period.time_end = datetime.utcnow()
#db.session.add(active_period)
period_state_waiting = PeriodState.get_state_by_name("Waiting")
new_period = Period(sr_id=self.sr_id,
csr_id=csr.csr_id,
reception_csr_ind=csr.receptionist_ind,
ps_id=period_state_waiting.ps_id,
time_start=datetime.utcnow())
self.periods.append(new_period)
SnowPlow.snowplow_event(self.citizen_id,
csr,
snowplow_event,
current_sr_number=self.sr_number)
def remove_from_queue(self):
service_req_ids = [int(x.period_id) for x in self.periods]
Period.delete_periods(service_req_ids)
def begin_service(self, csr, snowplow_event):
active_period = self.get_active_period()
if active_period.ps.ps_name in ["Being Served"]:
raise TypeError(
"You cannot begin serving a citizen that is already being served"
)
active_period.time_end = datetime.utcnow()
# db.session.add(active_period)
period_state_being_served = PeriodState.get_state_by_name(
"Being Served")
new_period = Period(sr_id=self.sr_id,
csr_id=csr.csr_id,
reception_csr_ind=csr.receptionist_ind,
ps_id=period_state_being_served.ps_id,
time_start=datetime.utcnow())
self.periods.append(new_period)
# Calculate number of active periods, for Snowplow call.
period_count = len(self.periods)
SnowPlow.snowplow_event(self.citizen_id,
csr,
snowplow_event,
period_count=period_count,
current_sr_number=self.sr_number)
def place_on_hold(self, csr):
active_period = self.get_active_period()
active_period.time_end = datetime.utcnow()
# db.session.add(active_period)
period_state_on_hold = PeriodState.get_state_by_name("On hold")
new_period = Period(sr_id=self.sr_id,
csr_id=csr.csr_id,
reception_csr_ind=csr.receptionist_ind,
ps_id=period_state_on_hold.ps_id,
time_start=datetime.utcnow())
self.periods.append(new_period)
SnowPlow.snowplow_event(self.citizen_id,
csr,
"hold",
current_sr_number=self.sr_number)
def finish_service(self, csr, clear_comments=True):
active_period = self.get_active_period()
active_period.time_end = datetime.utcnow()
if clear_comments:
self.citizen.citizen_comments = None