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 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)
number_of_minutes = db.Column(db.Integer, nullable=True, default=0)
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)
pesticide_exam_ind = db.Column(db.Integer, nullable=False)
deleted = db.Column(db.DateTime, nullable=True)
# TODO changed lazy=false to lazy=raise
exam = db.relationship("Exam", lazy='raise')
# TODO changed lazy4-false to no lazy option
#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 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):
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 Timezone(Base):
timezone_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
timezone_name = db.Column(db.String(256), nullable=False)
def __repr__(self):
return self.timezone_name
def __init__(self, **kwargs):
super(Timezone, self).__init__(**kwargs)
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 '<Smartboard Type:(name={self.sb_type!r})>'.format(self=self)
def __init__(self, **kwargs):
super(SmartBoard, self).__init__(**kwargs)
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 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 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 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)
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')
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 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 CSRState(Base):
csr_state_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False)
csr_state_name = db.Column(db.String(50), nullable=False)
csr_state_desc = db.Column(db.String(1000), nullable=False)
csrs = db.relationship('CSR', backref='csr_state', lazy=False)
def __repr__(self):
return self.csr_state_name
def __init__(self, **kwargs):
super(CSRState, self).__init__(**kwargs)
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 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 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 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 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 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)
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 CitizenState(Base):
cs_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
cs_state_name = db.Column(db.String(100), nullable=False)
cs_state_desc = db.Column(db.String(1000), nullable=False)
def __repr__(self):
return '<Citizen State Name:(name={self.cs_state_name!r})>'.format(
self=self)
def __init__(self, **kwargs):
super(CitizenState, 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)
class Permission(Base):
permission_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
permission_code = db.Column(db.String(100), nullable=False)
permission_desc = db.Column(db.String(1000), nullable=False)
def __repr__(self, permission_code):
return '<Permission Code:(name={self.permission_code!r})>'.format(
self=self)
def __init__(self, **kwargs):
super(Permission, 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_email_reminders = db.Column(db.Boolean())
send_sms_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."""
user = cls.query.filter_by(username=username).one_or_none()
# cache.set(key, user)
return user
@classmethod
def find_by_user_id(cls, user_id):
"""Find User records by user_id."""
user = cls.query.get(user_id)
# cache.set(key, user)
return user
@classmethod
def find_appointments_by_username(cls, username: str):
"""Find all appointments for the user."""
today = datetime.now(timezone('UTC'))
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)) \
.filter(Appointment.start_time > today )
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 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 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 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 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 TimeSlot(Base):
time_slot_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False)
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)
offices = db.relationship('Office', secondary='office_timeslot')
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 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 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 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()
