class SRState(Base): sr_state_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) sr_code = db.Column(db.String(100), nullable=False) sr_state_desc = db.Column(db.String(1000), nullable=False) def __repr__(self): return '<SR State Code:(name={self.sr_code!r})>'.format(self=self) def __init__(self, **kwargs): super(SRState, self).__init__(**kwargs) @classmethod @cache.memoize(timeout=300) def get_state_by_name(cls, sr_code): state = SRState.query.filter_by(sr_code=sr_code).first() return state
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) 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 Counter(Base): counter_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) counter_name = db.Column(db.String(50), nullable=False) def __repr__(self): return self.counter_name def __init__(self, **kwargs): super(Counter, self).__init__(**kwargs)
class ExamType(Base): exam_type_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) exam_type_name = db.Column(db.String(50), nullable=False) exam_color = db.Column(db.String(10), nullable=False) number_of_hours = db.Column(db.Integer, nullable=False) method_type = db.Column(db.String(10), nullable=False) ita_ind = db.Column(db.Integer, nullable=False) group_exam_ind = db.Column(db.Integer, nullable=False) exam = db.relationship("Exam", lazy=False) def __repr__(self): return '<Exam Type Name: (name={self.exam_type_name!r})>'.format( self=self) def __init__(self, **kwargs): super(ExamType, self).__init__(**kwargs)
class PeriodState(Base): ps_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) ps_name = db.Column(db.String(100), nullable=False) ps_desc = db.Column(db.String(1000), nullable=False) ps_number = db.Column(db.Integer, nullable=False) def __repr__(self): return '<Period State Name:(name={self.ps_name!r})>'.format(self=self) def __init__(self, **kwargs): super(PeriodState, self).__init__(**kwargs) @classmethod @cache.memoize(timeout=300) def get_state_by_name(cls, period_name): state = PeriodState.query.filter_by(ps_name=period_name).first() return state
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 Channel(Base): channel_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) channel_name = db.Column(db.String(100), nullable=False) def __repr__(self): return '<Channel Name:(name={self.channel_name!r})>'.format(self=self) def __init__(self, **kwargs): super(Channel, self).__init__(**kwargs)
class Permission(Base): model = api.model('Permission', { 'permission_id': fields.Integer, 'permission_code': fields.String, 'permission_desc': fields.String }) permission_id = db.Column(db.Integer, primary_key=True, autoincrement=True) permission_code = db.Column(db.String(100)) permission_desc = db.Column(db.String(1000)) def __repr__(self, permission_code): return '<Permission Code: %r>' % self.permission_code def __init__(self, **kwargs): super(Permission, self).__init__(**kwargs) def json(self, permission_id, permission_code, permission_desc): return {"permission_id": self.permission_id, "permission_code": self.permission_code, "permission_desc": self.permission_desc}
class SmartBoard(Base): sb_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) sb_type = db.Column(db.String(45), nullable=False) def __repr__(self): return self.sb_type def __init__(self, **kwargs): super(SmartBoard, 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) 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 MetaData(Base): metadata_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) meta_text = db.Column(db.String(100), nullable=False) def __repr__(self): return '<Meta Data:(name={self.meta_text!r})>'.format(self=self) def __init__(self, **kwargs): super(MetaData, 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 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 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=False) 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 = db.Column(db.Integer, nullable=False) 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_ind = db.Column(db.Integer, nullable=False, default=0) exam_returned_tracking_number = db.Column(db.String(50), nullable=True) 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) 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 @cache.memoize(timeout=300) def find_by_username(cls, username): csr = CSR.query.filter(CSR.deleted.is_(None)).filter_by( username=username.split("idir/")[-1]).first() return csr def get_id(self): return str(self.csr_id)
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 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) services = db.relationship("Service", secondary='office_service') csrs = db.relationship('CSR') citizens = db.relationship('Citizen', backref='office_citizens') sb = db.relationship('SmartBoard') def __repr__(self): return self.office_name def __init__(self, **kwargs): super(Office, 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_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 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)) # 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 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 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)') 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): idir_id = username.split("idir/")[-1] key = (CSR.format_string % idir_id).lower() if cache.get(key): return cache.get(key) csr = CSR.query.filter( CSR.deleted.is_(None)).filter_by(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 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 PublicUser(Base): user_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False) username = db.Column(db.String(100), unique=True, index=True) last_name = db.Column(db.String(100)) display_name = db.Column(db.String(200)) email = db.Column(db.String(200)) telephone = db.Column(db.String(20)) send_reminders = db.Column(db.Boolean()) # format_string = 'public_user_%s' def __repr__(self): return '<Public User Name:(name={self.display_name!r})>'.format( self=self) def __init__(self, **kwargs): super(PublicUser, self).__init__(**kwargs) @classmethod def find_by_username(cls, username): """Find User records by username.""" print('>>>>>>', username) # key = PublicUser.format_string % username # print(key) # if cache.get(key): # print('>>>>From Cache>>>>>') # return cache.get(key) user = cls.query.filter_by(username=username).one_or_none() print('user ', user) # cache.set(key, user) return user @classmethod def find_by_user_id(cls, user_id): """Find User records by user_id.""" print('>>>>>>user_id', user_id) # key = PublicUser.format_string % username # print(key) # if cache.get(key): # print('>>>>From Cache>>>>>') # return cache.get(key) user = cls.query.get(user_id) print('user ', user) # cache.set(key, user) return user @classmethod def find_appointments_by_username(cls, username: str): """Find all appointments for the user.""" query = db.session.query(Appointment) \ .join(Citizen) \ .join(PublicUser) \ .filter(PublicUser.username == username, Citizen.user_id == PublicUser.user_id, Appointment.citizen_id == Citizen.citizen_id) \ .filter(Appointment.checked_in_time.is_(None)) return query.all() @classmethod def find_by_citizen_id(cls, citizen_id: int): """Find user by citizen_id.""" query = db.session.query(PublicUser) \ .join(Citizen) \ .filter(PublicUser.user_id == Citizen.user_id, Citizen.citizen_id == citizen_id) return query.one_or_none()
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
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)
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 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_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) 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') sb = db.relationship('SmartBoard') timezone = db.relationship('Timezone') exams = db.relationship("Exam") rooms = db.relationship('Room') def __repr__(self): return self.office_name def __init__(self, **kwargs): super(Office, self).__init__(**kwargs)