class Time(db.Model):
__tablename__ = 'time'
id = db.Column(db.Integer(), primary_key=True)
time_day = db.Column(db.Integer())
time_start = db.Column(db.Time())
time_end = db.Column(db.Time())
# tutor = db.relationship('Tutor', uselist=False)
tutor_id = db.Column(db.Integer(),
db.ForeignKey('tutor.id', ondelete='CASCADE'))
class AvailableTime(db.Model):
__tablename__ = "available_time"
id = db.Column(db.Integer, primary_key=True)
artist_id = db.Column(db.Integer, db.ForeignKey("artist.id"), nullable=False)
# represents datetime.date.isoweekday: Monday = 1, Sunday = 7
day_of_week = db.Column(db.Integer(), nullable=False)
from_time = db.Column(db.Time(), nullable=False)
to_time = db.Column(db.Time())
class HappyHour(db.Model):
"""
Create Happy Hour table
"""
__tablename__ = 'happy_hours'
id = db.Column(db.Integer, primary_key=True)
restaurant_id = db.Column(db.Integer, db.ForeignKey('restaurants.id'))
restaurant = db.relationship('Restaurant')
day = db.Column(db.String(10))
start_time = db.Column(db.Time())
end_time = db.Column(db.Time())
class Ride(db.Model):
__tablename__ = 'ride'
ride_no = db.Column('ride_no', db.Integer(), primary_key=True)
origin = db.Column('origin', db.String(100))
destination = db.Column('destination', db.String(100))
driver_netid = db.Column('driver_netid', db.String(7),
db.ForeignKey('driver.netid'))
date = db.Column('date', db.Date())
earliest_time = db.Column('earliest_time', db.Time())
latest_time = db.Column('latest_time', db.Time())
seats_available = db.Column('seats_available', db.Integer())
gas_price = db.Column('gas_price', db.Integer())
comments = db.Column('comments', db.String(500))
class BeforeOrderLines(db.Model):
__tablename__ = 'BeforeOrderLines'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
Date = db.Column(db.Date())
Time = db.Column(db.Time())
RefLine = db.Column(db.Integer, unique=True, autoincrement=True)
RefProduct = db.Column(db.String(10))
Text = db.Column(db.String(50))
Price = db.Column(db.Float)
def TotalPrice():
a=BeforeOrderLines.query.filter_by().all()
mysum=0
for x in range(0,len(a)):
mysum=mysum+a[x].Price
return mysum
def AddOrderTotalLine(p_LineText = 'pizza reine 12€00',p_LinePrice = 12):#ajouter une ligne de commande
Item = OrderLines( LineText = p_LineText,LinePrice = p_LinePrice)
db.session.add(Item)
db.session.commit()
def AddBeforeOrderLine(p_LineText = 'pizza reine 12€00',p_LinePrice = 12):#ajouter une ligne de commande
Item = BeforeOrderLines( LineText = p_LineText,LinePrice = p_LinePrice)
db.session.add(Item)
db.session.commit()
#supprimer une ligne de commande
def DelOrderLine(p_LineId):
OrderLines.query.filter_by(id=p_LineId).delete()
db.session.commit()
def ShowAllLines():
return (OrderLines.query.filter_by().all())
class Subscription(db.Model):
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.String(20), unique=False, nullable=False)
email = db.Column(db.String(120), unique=False, nullable=False)
time = db.Column(db.Time(), unique=False, nullable=False)
last_send = db.Column(db.Date())
class Day(db.Model):
__tablename__ = 'Day'
locality_id = db.Column(db.Integer, db.ForeignKey('Locality.id'), primary_key=True, nullable=False)
date = db.Column(db.Date(), primary_key=True, nullable=False) # "2021-1-15"
temperature_max = db.Column(db.Integer) # 7,
temperature_min = db.Column(db.Integer) # -1,
icon = db.Column(db.String(10)) # "6",
text = db.Column(db.String(80)) # "Mostly cloudy",
humidity = db.Column(db.Integer) # 89,
wind = db.Column(db.Integer) # 4,
wind_direction = db.Column(db.String(30)) # "Northwest",
icon_wind = db.Column(db.String(10)) # "NO",
sunrise = db.Column(db.Time()) # "8:37",
sunset = db.Column(db.Time()) # "18:10",
moonrise = db.Column(db.Time()) # "10:28",
moonset = db.Column(db.Time()) # "20:43",
moon_phases_icon = db.Column(db.String(10)) # "2"
locality = db.relationship("Locality", backref="day_forecast")
class Kehadiran(db.Model):
__tablename__ = 'tb_kehadiran'
id_kehadiran = db.Column(db.Integer, primary_key=True)
waktu_hadir = db.Column(db.Time(timezone=False))
id_pegawai = db.Column(db.Integer)
kehadiran = db.Column(db.Enum(KehadiranEnum))
validasi_manager = db.Column(db.Boolean,
default=False,
server_default="false")
def __repr__(self):
return 'Id: {}, name: {}'.format(self.id_pegawai, self.nama_pegawai)
class Schedule(db.Model):
id = db.Column(db.Integer, primary_key=True)
timestamp = db.Column(db.Time(), index=True, nullable=False)
actuator_id = db.Column(db.Integer,
db.ForeignKey('actuator.id'),
nullable=False)
def __init__(self, timestamp: datetime.time, actuator_id: int):
self.timestamp = timestamp
self.actuator_id = actuator_id
def __repr__(self):
return '<Schedule {}>'.format(self.id, self.timestamp,
self.actuator_id)
class Hour(db.Model):
__tablename__ = 'Hour'
locality_id = db.Column(db.Integer,
db.ForeignKey('Locality.id'),
primary_key=True,
nullable=False)
date = db.Column(db.Date(), primary_key=True) # "2021-1-15"
hour_data = db.Column(db.Time(), primary_key=True) # "13:00",
temperature = db.Column(db.Integer) # -1,
icon = db.Column(db.String(10)) # "6",
text = db.Column(db.String(80)) # "Mostly cloudy",
humidity = db.Column(db.Integer) # 89,
wind = db.Column(db.Integer) # 4,
wind_direction = db.Column(db.String(30)) # "Northwest",
icon_wind = db.Column(db.String(10)) # "NO",
pressure = db.Column(db.Integer) # 1016,
locality = db.relationship("Locality", backref="hour_forecast")
class Event(db.Model):
__tablename__ = 'events'
#__table_args__ = (
# db.UniqueConstraint('name', 'place', 'date_start', name='unique_event'),
#)
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), nullable=False, unique=True)
place = db.Column(db.String(255), nullable=False, unique=False)
date_start = db.Column(db.DateTime(), default=datetime.utcnow)
time_start = db.Column(db.Time(), nullable=False)
date_end = db.Column(db.DateTime(), default=datetime.utcnow)
notes = db.Column(db.String(1000), nullable=True, unique=False, default='')
created_on = db.Column(db.DateTime(), default=datetime.utcnow)
updated_on = db.Column(db.DateTime(),
default=datetime.utcnow,
onupdate=datetime.utcnow)
def __repr__(self):
return "<{}:{}:{}>".format(self.id, self.date_start, self.name)
class Video(db.Model):
__tablename__ = 'videos'
id = db.Column(db.Integer, unique=True)
vid = db.Column(db.String(225), primary_key=True, unique=True)
vsize = db.Column(db.String(225)) # 视频大小
vtime = db.Column(db.Time()) # 视频时长
vurl = db.Column(db.String(225)) # 视频的url
def __init__(self, vid, vsize, vtime, vurl):
self.vid = vid
self.vsize = vsize
self.vtime = vtime
self.vurl = vurl
db.create_all()
def __repr__(self):
return "<video id '{}'>".format(self.vid)
class Order(db.Model):
"""
Model that maps 'order' table from the database
"""
__tablename__ = 'order'
order_id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'))
address_id = db.Column(db.Integer, db.ForeignKey('address.address_id'))
cleaner_id = db.Column(db.Integer, db.ForeignKey('cleaner.cleaner_id'))
date = db.Column(db.Date(), unique=False)
start_time = db.Column(db.Time(), unique=False)
end_time = db.Column(db.Time(), unique=False)
rooms = db.Column(db.Integer(), unique=False)
special_rooms = db.Column(db.String(500), unique=False)
extra_services = db.Column(db.String(500), unique=False)
transaction = db.Column(db.String(500), unique=False)
reference = db.Column(db.String(500), unique=True)
price = db.Column(db.Float(), unique=False)
def __init__(self,
user_id=None,
address_id=None,
cleaner_id=None,
date=None,
start_time=None,
end_time=None,
rooms=None,
special_rooms=None,
extra_services=None,
transaction=None,
reference=None,
price=None):
self.user_id = user_id
self.address_id = address_id
self.cleaner_id = cleaner_id
self.date = date
self.start_time = start_time
self.end_time = end_time
self.rooms = rooms
self.special_rooms = special_rooms
self.extra_services = extra_services
self.transaction = transaction
self.reference = reference
self.price = price
def __repr__(self):
return '<Order %r>' % self.order_id
def persist(self):
try:
db.session.add(self)
db.session.commit()
self.user_id
self.address_id
self.cleaner_id
except IntegrityError:
return False
return True
@staticmethod
def get_all(filters):
query = db.session.query(Order)
if 'from' in filters:
query = query.filter(Order.date >= filters['from'])
if 'to' in filters:
query = query.filter(Order.date <= filters['to'])
if 'payed' in filters:
if filters['payed'].lower() == 'true':
query = query.filter(
Order.transaction != None) # Equity operator is necessary
elif filters['payed'].lower() == 'false':
query = query.filter(
Order.transaction == None) # Equity operator is necessary
return query.all()
@staticmethod
def get_by_reference(reference):
return Order.query.filter_by(reference=reference).first()
@staticmethod
def get_all_by_user(user_id):
return Order.query.filter_by(user_id=user_id).all()
@staticmethod
def get_all_by_cleaner(cleaner_id):
return Order.query.filter_by(cleaner_id=cleaner_id).all()
@staticmethod
def get_all_by_address(address_id):
return Order.query.filter_by(address_id=address_id).all()
@staticmethod
def get_by_id(order_id):
return Order.query.filter_by(order_id=order_id).first()
@staticmethod
def delete_by_id(order_id):
order = Order.query.filter_by(order_id=order_id).first()
if order is None:
return False
db.session.delete(order)
db.session.commit()
return True
class EventModel(db.Model):
"""
Event Model
"""
# table name
__tablename__ = 'events'
# composite primary key: (title, venue, date)
title = db.Column(db.String(),
unique=False,
nullable=False,
primary_key=True)
venue = db.Column(db.String(),
unique=False,
nullable=False,
primary_key=True)
url = db.Column(db.String(), unique=False, nullable=True)
date = db.Column(db.Date, unique=False, nullable=False, primary_key=True)
# time will always be in pacific time because this is for SF venues.
# Will update if expanding to other regions
time = db.Column(db.Time(), unique=False, nullable=False)
# precision(x) scale(y) xxxx.yy
# No sitatuion where a ticket price should exceed $9999.99
price = db.Column(db.Decimal(precision=4, scale=2),
unique=False,
nullable=False)
def __init__(self, title, venue, url, date, time, price):
self.title = title
self.venue = venue
self.url = url
self.date = date
self.time = time
self.price = price
def save(self):
db.session.add(self)
db.session.commit(self)
def delete(self):
db.session.delete(self)
db.session.commit()
@staticmethod
def get_all_events():
return EventModel.query.all()
@staticmethod
def get_venue_events(venue):
return EventModel.query.filter(self.venue == venue).all()
def __repr__(self):
return '<id {title, venue, url, date, time, price}>'.format(
title=self.title,
venue=self.venue,
url=self.url,
date=self.date,
time=self.time,
price=self.price)
class WorksAt(db.Model):
"""
Represents the relationship between the Centre and User table.
The two foreign key entries link to the primary keys of their respective tables. (email and centre name).
"""
__tablename__ = "Works_At"
# Columns
id = db.Column(db.Integer, primary_key=True) # Unique identifier
place = db.Column(db.String(128), db.ForeignKey('Centres.name')) # Links to centre
provider = db.Column(db.String(128), db.ForeignKey('Users.email')) # Links to provider
hours_start = db.Column(db.Time()) # Starting hours for provider
hours_end = db.Column(db.Time()) # Ending hours for provider
# Relationships
user = db.relationship(User, backref=db.backref("Works_At", cascade="all, delete-orphan"))
centre = db.relationship(Centre, backref=db.backref("Works_At", cascade="all, delete-orphan"))
def __repr__(self):
"""
Specifies the interpreter representation of the object.
:return: A string representation of the WorksAt instance.
"""
return '<WorksAt {}, {}>'.format(self.provider, self.place)
@staticmethod
def are_valid_hours(start_time, end_time, centre_name, provider_email, patient_email):
"""
Determines whether the appointment time is valid and within a providers working hours.
:param start_time: Start time for the appointment.
:param centre_name: Name of the centre the provider is working at.
:param provider_email: The provider whose working hours we're querying
:return: None, if validation is successful. Otherwise a string 'result' with the outcome of the validation:
'Clash' - appointment already exists within provided start_time.
'Past' - appointment is in the past.
'Hours' - appointment exists outside of the providers working hours.
"""
result = ''
# Fetch the working hours of the provider for our specific centre
wa = WorksAt.query.filter_by(place=centre_name, provider=provider_email).first()
hours_start = wa.hours_start
hours_end = wa.hours_end
# Ensure appointment is not in the past
if start_time < datetime.now():
result = 'Past'
logger.warn("Appointment is in the past, not adding.", 'red')
# Ensure appointment is within providers working hours
elif start_time.time() < hours_start or end_time.time() > hours_end:
result = 'Hours'
logger.warn("Appointment falls outside of providers working hours, not adding.")
# Ensure there aren't any appointments already booked in that time period.
elif Appointment.query.filter(Appointment.start_time >= start_time, Appointment.end_time <= end_time,\
Appointment.provider_email == provider_email).all()\
or Appointment.query.filter(Appointment.start_time >= start_time, Appointment.end_time <= end_time,\
Appointment.patient_email == patient_email).all():
# There's already an appointment booked with this provider during this time, return an error.
result = 'Clash'
logger.warn("Appointment clashed with existing appointment, not adding.")
return result
class Shift(db.Model):
__tablename__ = 'shifts'
day = db.Column(db.Date(), primary_key=True)
staff_id = db.Column(db.String(), primary_key=True)
start_time = db.Column(db.Time(), nullable=False)
end_time = db.Column(db.Time(), nullable=False)
class Schedule(db.Model):
"""
Model that maps 'schedule' table from the database
"""
__tablename__ = 'schedule'
schedule_id = db.Column(db.Integer, primary_key=True)
cleaner_id = db.Column(db.Integer, db.ForeignKey('cleaner.cleaner_id'))
year = db.Column(db.String(4), unique=False)
week = db.Column(db.String(2), unique=False)
day_of_week = db.Column(db.String(1), unique=False)
start_time = db.Column(db.Time(), unique=False)
end_time = db.Column(db.Time(), unique=False)
def __init__(self,
cleaner_id=None,
year=None,
week=None,
day_of_week=None,
start_time=None,
end_time=None):
self.cleaner_id = cleaner_id
self.year = year
self.week = week
self.day_of_week = day_of_week
self.start_time = start_time
self.end_time = end_time
def __repr__(self):
return '<Schedule %r>' % self.schedule_id
def persist(self):
db.session.add(self)
db.session.commit()
self.cleaner_id
@staticmethod
def get_all(filter_dict):
query = db.session.query(Schedule)
for attr, value in filter_dict.items():
query = query.filter(
getattr(Schedule, attr).like("%%%s%%" % value))
return query.all()
@staticmethod
def get_all_by_cleaner(cleaner_id, filter_dict):
query = db.session.query(Schedule)
for attr, value in filter_dict.items():
query = query.filter(
getattr(Schedule, attr).like("%%%s%%" % value))
return query.filter_by(cleaner_id=cleaner_id).all()
@staticmethod
def get_by_id(schedule_id):
return Schedule.query.filter_by(schedule_id=schedule_id).first()
@staticmethod
def delete_by_id(schedule_id):
schedule = Schedule.query.filter_by(schedule_id=schedule_id).first()
if schedule is None:
return False
db.session.delete(schedule)
db.session.commit()
return True
