class Review(SurrogatePK, Model):
"""Review of a teacher"""
__tablename__ = "reviews"
teacher_id = reference_col("teachers", nullable=False)
teacher = relationship("Teacher", backref=backref("reviews", lazy="dynamic"))
student_id = reference_col("students", nullable=False)
student = relationship("Student", backref=backref("reviews", lazy="dynamic"))
content = Column(db.Text, nullable=True)
price_rating = Column(db.Float, nullable=False)
availability_rating = Column(db.Float, nullable=False)
content_rating = Column(db.Float, nullable=False)
created_at = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow)
def __init__(self, **kwargs):
"""Create instance."""
db.Model.__init__(self, **kwargs)
def to_dict(self):
return {
"id": self.id,
"teacher_id": self.teacher_id,
"student_id": self.student_id,
"content": self.content,
"price_rating": self.price_rating,
"availability_rating": self.availability_rating,
"content_rating": self.content_rating,
"created_at": self.created_at,
}
class Report(SurrogatePK, Model):
"""A test for a user"""
__tablename__ = "reports"
uuid = Column(db.String, default="", nullable=True)
report_type = Column(ChoiceType(ReportType, impl=db.Integer()), nullable=False)
since = Column(db.DateTime, nullable=True)
until = Column(db.DateTime, nullable=True)
teacher_id = reference_col("teachers", nullable=False)
teacher = relationship("Teacher", backref=backref("reports", lazy="dynamic"))
car_id = reference_col("cars", nullable=True)
car = relationship("Car", backref=backref("reports", lazy="dynamic"))
created_at = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow)
DATES_REQUIRED = ["lessons", "kilometers"] # list of types where dates are required
def __init__(self, **kwargs):
"""Create instance."""
self.uuid = str(uuid.uuid4())
db.Model.__init__(self, **kwargs)
def to_dict(self) -> dict:
return {
"uuid": self.uuid,
"report_type": self.report_type.name,
"created_at": self.created_at,
}
class Kilometer(SurrogatePK, Model):
"""daily report of distances in km"""
__tablename__ = "kilometers"
start_of_day = Column(db.Float, nullable=False)
end_of_day = Column(db.Float, nullable=False)
personal = Column(db.Float, default=0, nullable=False)
date = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow)
teacher_id = reference_col("teachers", nullable=False)
teacher = relationship("Teacher",
backref=backref("kilometers", lazy="dynamic"))
car_id = reference_col("cars", nullable=False)
car = relationship("Car", backref=backref("kilometers", lazy="dynamic"))
def __init__(self, **kwargs):
"""Create instance."""
db.Model.__init__(self, **kwargs)
@hybrid_property
def total_work_km(self) -> float:
return float(self.end_of_day) - float(self.start_of_day) - float(
self.personal)
def to_dict(self) -> dict:
return {
"date": self.date,
"car": self.car.to_dict(),
"total_work_km": self.total_work_km,
"start_of_day": self.start_of_day,
"end_of_day": self.end_of_day,
"personal": self.personal,
}
class Car(SurrogatePK, Model):
"""Teacher's car"""
__tablename__ = "cars"
name = Column(db.String, nullable=True)
type = Column(
ChoiceType(CarType, impl=db.Integer()), default=CarType.manual, nullable=False
)
number = Column(db.String, nullable=False)
teacher_id = reference_col("teachers", nullable=False)
teacher = relationship(
"Teacher",
backref=backref("cars", lazy="dynamic", order_by="Car.created_at.asc()"),
)
created_at = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow)
color = Column(db.String, nullable=True)
def __init__(self, **kwargs):
"""Create instance."""
db.Model.__init__(self, **kwargs)
def to_dict(self) -> dict:
return {
"id": self.id,
"name": self.name,
"type": self.type.name,
"number": self.number,
"color": self.color,
"created_at": self.created_at,
}
def user(self):
return relationship(
"User",
backref=backref(self.__tablename__[:-1],
uselist=False), # slice the `s` from table name
uselist=False,
foreign_keys=[self.user_id],
)
class WorkDay(SurrogatePK, Model):
"""A Work day"""
__tablename__ = "work_days"
teacher_id = reference_col("teachers", nullable=False)
teacher = relationship("Teacher", backref=backref("work_days", lazy="dynamic"))
day = Column(ChoiceType(Day, impl=db.Integer()), nullable=True)
from_hour = Column(db.Integer, nullable=False)
from_minutes = Column(db.Integer, nullable=False, default=0)
to_hour = Column(db.Integer, nullable=False)
to_minutes = Column(db.Integer, nullable=False, default=0)
on_date = Column(db.Date, nullable=True)
car_id = reference_col("cars", nullable=True)
car = relationship("Car", backref=backref("work_days", lazy="dynamic"))
ALLOWED_FILTERS = ["day", "on_date"]
default_sort_column = "day"
def __init__(self, **kwargs):
"""Create instance."""
db.Model.__init__(self, **kwargs)
if not self.car:
self.car = self.teacher.cars.first()
def to_dict(self):
return {
"id": self.id,
"day": self.day.value if self.day else None,
"from_hour": self.from_hour,
"from_minutes": self.from_minutes,
"to_hour": self.to_hour,
"car": self.car.to_dict(),
"to_minutes": self.to_minutes,
"on_date": self.on_date,
}
def __repr__(self):
return (
f"<WorkDay day={self.day}"
f"from={self.from_hour}:{self.from_minutes}"
f", to={self.to_hour}:{self.to_minutes}"
f", on_date={self.on_date}>"
)
class Place(SurrogatePK, Model):
"""A dropoff/meetup place"""
__tablename__ = "places"
student_id = reference_col("students", nullable=False)
student = relationship("Student",
backref=backref("places", lazy="dynamic"))
description = Column(db.String, nullable=False)
google_id = Column(db.String, nullable=True)
used_as = Column(ChoiceType(PlaceType, impl=db.Integer()),
default=1,
nullable=False)
times_used = Column(db.Integer, default=1)
@classmethod
def create_or_find(cls, place_dict: Optional[Dict], used_as: PlaceType,
student: "Student") -> Optional["Place"]:
try:
description = place_dict["description"]
except (KeyError, TypeError):
return None
if not description:
return None
try:
ret = cls.query.filter(
and_(
cls.description == description,
cls.used_as == used_as.value,
cls.student == student,
)).one()
ret.update(times_used=ret.times_used + 1)
except NoResultFound:
ret = cls.create(
student=student,
description=description,
google_id=place_dict.get("google_id"),
used_as=used_as.value,
)
return ret
def __init__(self, **kwargs):
"""Create instance."""
db.Model.__init__(self, **kwargs)
def to_dict(self):
return {
"description": self.description,
"google_id": self.google_id,
"used_as": self.used_as.name,
"times_used": self.times_used,
}
class Payment(SurrogatePK, Model):
"""Payment from student to teacher"""
__tablename__ = "payments"
teacher_id = reference_col("teachers", nullable=False)
teacher = relationship("Teacher", backref=backref("payments", lazy="dynamic"))
student_id = reference_col("students", nullable=True)
student = relationship("Student", backref=backref("payments", lazy="dynamic"))
amount = Column(db.Integer, nullable=False)
created_at = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow)
pdf_link = Column(db.String(300), nullable=True)
crn = Column(db.Integer, nullable=True)
payment_type = Column(
ChoiceType(PaymentType, impl=db.Integer()), nullable=False, server_default="1"
)
details = Column(db.String(240), nullable=True)
ALLOWED_FILTERS = ["student_id", "amount", "created_at"]
default_sort_method = "desc"
def __init__(self, **kwargs):
"""Create instance."""
db.Model.__init__(self, **kwargs)
def to_dict(self) -> dict:
return {
"id": self.id,
"student": self.student.user.to_dict(), # student contains teacher
"amount": self.amount,
"pdf_link": self.pdf_link,
"crn": self.crn,
"payment_type": self.payment_type.name,
"created_at": self.created_at,
}
def __repr__(self):
return f"<Payment created_at={self.created_at}, teacher={self.teacher}, student={self.student}>"
class OAuth(SurrogatePK, Model):
"""oauh provider"""
__tablename__ = "oauth_providers"
provider_user_id = Column(db.String(256), unique=True)
user_id = reference_col("users", nullable=False)
user = relationship(User)
provider = Column(ChoiceType(Provider, impl=db.Integer()), nullable=False)
token = Column(JSONType, nullable=False, default=dt.datetime.utcnow)
created_at = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
def __init__(self, **kwargs):
"""Create instance."""
db.Model.__init__(self, **kwargs)
class LessonTopic(SurrogatePK, Model):
"""lesson-topics association"""
__tablename__ = "lesson_topics"
topic_id = reference_col("topics", nullable=False)
topic = relationship("Topic")
lesson_id = Column(db.Integer, db.ForeignKey("appointments.id"), nullable=False)
is_finished = Column(db.Boolean, nullable=False, default=False)
created_at = Column(db.DateTime, nullable=False, default=datetime.utcnow)
def __init__(self, **kwargs):
"""Create instance."""
db.Model.__init__(self, **kwargs)
def to_dict(self):
return dict(
**Topic.get_by_id(self.topic_id).to_dict(),
**{"lesson_id": self.lesson_id, "is_finished": self.is_finished}
)
class Student(SurrogatePK, LessonCreator):
"""A student of the app."""
__tablename__ = "students"
teacher_id = reference_col("teachers", nullable=False)
teacher = relationship("Teacher",
backref=backref("students", lazy="dynamic"))
is_approved = Column(db.Boolean, default=False, nullable=False)
is_active = Column(db.Boolean, default=True, nullable=False)
creator_id = reference_col("users", nullable=False)
creator = relationship("User", foreign_keys=[creator_id])
created_at = Column(db.DateTime, nullable=False, default=datetime.utcnow)
number_of_old_lessons = Column(db.Float, nullable=False, default=0)
theory = Column(db.Boolean, nullable=False, default=False)
doctor_check = Column(db.Boolean, nullable=False, default=False)
eyes_check = Column(db.Boolean, nullable=False, default=False)
green_form = Column(db.String(240), nullable=True)
id_number = Column(db.String, nullable=True)
price = Column(db.Integer, nullable=True)
car_id = reference_col("cars", nullable=True)
car = relationship("Car", backref=backref("students", lazy="dynamic"))
ALLOWED_FILTERS = [
"is_active",
"is_approved",
"theory",
"doctor_check",
"eyes_check",
"green_form",
]
def __init__(self, **kwargs):
"""Create instance."""
if current_user and not kwargs.get(
"creator") and current_user.is_authenticated:
self.creator = current_user
db.Model.__init__(self, **kwargs)
if not self.price:
self.price = self.teacher.price
if not self.car:
self.car = self.teacher.cars.first()
def _lesson_topics(self, is_finished: bool):
lesson_ids = [lesson.id for lesson in self.lessons]
return LessonTopic.query.filter(
and_(
LessonTopic.lesson_id.in_(lesson_ids),
LessonTopic.is_finished == is_finished,
)).order_by(LessonTopic.created_at.desc())
def _topics_in_progress(self, lesson_topics: BaseQuery) -> Set[Topic]:
"""loop through given lesson topics, check for rows
that do not have is_finished in other rows -
these are the in progress topics.
"""
topics = (lt.topic for lt in lesson_topics.all())
in_progress_topics = itertools.filterfalse(
lambda topic: (LessonTopic.query.filter_by(topic_id=topic.id).
filter_by(is_finished=True).first()),
topics,
)
return set(list(in_progress_topics))
def topics(self, is_finished: bool) -> Set[Topic]:
"""get topics for student. if status is finished,
get all finished lesson_topics. if in progress, get lesson_topics
that do not have finished status - get latest row of each one.
return topic of lesson_topic"""
lesson_topics = self._lesson_topics(is_finished)
if is_finished:
"""if we check for is_finished,
there should be one row with is_finished=True for each topic"""
return {lt.topic for lt in lesson_topics.all()}
return self._topics_in_progress(lesson_topics)
@hybrid_property
def common_meetup(self) -> Place:
return (self.places.filter_by(used_as=PlaceType.meetup.value).order_by(
Place.times_used.desc()).first())
@hybrid_property
def common_dropoff(self) -> Place:
return (self.places.filter_by(
used_as=PlaceType.dropoff.value).order_by(
Place.times_used.desc()).first())
@hybrid_property
def lessons_done(self) -> int:
"""return the number of a new lesson:
num of latest lesson+1"""
latest_lesson = (self.lessons.filter(
Appointment.approved_lessons_filter(
Appointment.date < datetime.utcnow())).order_by(
Appointment.date.desc()).limit(1).one_or_none())
starting_count = self.number_of_old_lessons
if not latest_lesson:
return starting_count
return latest_lesson.lesson_number
@lessons_done.expression
def lessons_done(cls):
q = select([
cast(func.sum(Appointment.duration), db.Float) /
(func.count(Appointment.student_id) * Teacher.lesson_duration)
]).where(
Appointment.approved_lessons_filter(
Appointment.date < datetime.utcnow(),
Appointment.student_id == cls.id))
j = Student.__table__.join(Teacher.__table__)
q = q.select_from(j).label("lessons_done")
return q + cls.number_of_old_lessons
@hybrid_property
def balance(self):
"""calculate sum of payments minus
number of lessons taken * price"""
return self.total_paid - self.total_lessons_price
@balance.expression
def balance(cls):
return cls.total_paid - cls.total_lessons_price
@hybrid_property
def total_lessons_price(self):
return (sum(lesson.price for lesson in self.lessons.filter(
Appointment.approved_lessons_filter(
Appointment.date < datetime.utcnow())).all()) +
self.price * self.number_of_old_lessons)
@total_lessons_price.expression
def total_lessons_price(cls):
q = (select([coalesce(func.sum(Appointment.price), 0)]).where(
Appointment.approved_lessons_filter(
Appointment.date < datetime.utcnow(),
Appointment.student_id == cls.id,
)).label("total_lessons_price"))
return q + cls.number_of_old_lessons * cls.price
@hybrid_property
def total_paid(self):
return sum([payment.amount for payment in self.payments])
@total_paid.expression
def total_paid(cls):
q = (select([coalesce(
func.sum(Payment.amount),
0)]).where(Payment.student_id == cls.id).label("total_paid"))
return q
def to_dict(self, with_user=True):
green_form = ""
if self.green_form:
try:
green_form = cloudinary_url(self.green_form)[0]
except Exception:
pass
if with_user:
return self.user.to_dict() # returns user dict with student info
return {
"student_id": self.id,
"my_teacher": self.teacher.to_dict(),
"balance": self.balance,
"lessons_done": self.lessons_done,
"is_approved": self.is_approved,
"is_active": self.is_active,
"theory": self.theory,
"eyes_check": self.eyes_check,
"doctor_check": self.doctor_check,
"number_of_old_lessons": self.number_of_old_lessons,
"green_form": green_form,
"price": self.price,
"id_number": self.id_number,
"car": self.car.to_dict(),
}
def __repr__(self):
return (f"<Student id={self.id}, balance={self.balance}"
f", total_lessons_price={self.total_lessons_price}"
f", lessons_done={self.lessons_done}, teacher={self.teacher}"
f", total_paid={self.total_paid}>")
class Appointment(SurrogatePK, Model):
"""A driving lesson/test/exam"""
__tablename__ = "appointments"
query_class = QueryWithSoftDelete
teacher_id = reference_col("teachers", nullable=False)
teacher = relationship("Teacher",
backref=backref("appointments", lazy="dynamic"))
student_id = reference_col("students", nullable=True)
student = relationship("Student",
backref=backref("appointments", lazy="dynamic"))
topics = relationship("LessonTopic", lazy="dynamic")
duration = Column(db.Integer, nullable=False)
date = Column(db.DateTime, nullable=False)
created_at = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
meetup_place_id = reference_col("places", nullable=True)
meetup_place = relationship("Place", foreign_keys=[meetup_place_id])
dropoff_place_id = reference_col("places", nullable=True)
dropoff_place = relationship("Place", foreign_keys=[dropoff_place_id])
is_approved = Column(db.Boolean, nullable=False, default=True)
comments = Column(db.Text, nullable=True)
deleted = Column(db.Boolean, nullable=False, default=False)
creator_id = reference_col("users", nullable=False)
creator = relationship("User")
price = Column(db.Integer, nullable=True)
type = Column(
ChoiceType(AppointmentType, impl=db.Integer()),
default=AppointmentType.LESSON.value,
nullable=False,
)
ALLOWED_FILTERS = [
"deleted",
"is_approved",
"date",
"student_id",
"created_at",
"creator_id",
]
default_sort_column = "date"
def __init__(self, **kwargs):
"""Create instance."""
if current_user and not kwargs.get(
"creator") and current_user.is_authenticated:
self.creator = current_user
db.Model.__init__(self, **kwargs)
if not self.price:
if self.student:
self.price = self.student.price * self.lesson_length
else:
self.price = self.teacher.price * self.lesson_length
def update_only_changed_fields(self, **kwargs):
args = {k: v for k, v in kwargs.items() if v or isinstance(v, bool)}
self.update(**args)
@staticmethod
def approved_filter(*args):
return and_(Appointment.is_approved == True,
Appointment.deleted == False, *args)
@staticmethod
def approved_lessons_filter(*args):
return Appointment.approved_filter(
Appointment.type == AppointmentType.LESSON.value, *args)
@staticmethod
def appointments_between(start_date, end_date):
appointment_end_date = addinterval(Appointment.date,
Appointment.duration)
start_date = start_date.replace(second=0, microsecond=0)
end_date = end_date.replace(second=0, microsecond=0)
query = Appointment.approved_filter(
or_(
and_(start_date <= Appointment.date,
Appointment.date < end_date),
and_(start_date < appointment_end_date,
appointment_end_date <= end_date),
))
return query
@hybrid_property
def lesson_length(self) -> float:
return self.duration / self.teacher.lesson_duration
@hybrid_property
def lesson_number(self) -> float:
lessons = Appointment.query.filter(
self.approved_lessons_filter(
Appointment.date < self.date,
Appointment.student == self.student)).all()
return (sum(lesson.lesson_length for lesson in lessons) +
self.student.number_of_old_lessons + self.lesson_length)
def to_dict(self):
return {
"id":
self.id,
"student":
self.student.user.to_dict() if self.student else None,
"date":
self.date,
"meetup_place":
self.meetup_place.description if self.meetup_place else None,
"dropoff_place":
self.dropoff_place.description if self.dropoff_place else None,
"is_approved":
self.is_approved,
"comments":
self.comments,
"lesson_number":
self.lesson_number,
"created_at":
self.created_at,
"duration":
self.duration,
"price":
self.price,
"creator_id":
self.creator_id,
"type":
self.type.name.lower(),
}
def __repr__(self):
return (
f"<Appointment date={self.date}, created_at={self.created_at},"
f"student={self.student}, teacher={self.teacher}"
f",approved={self.is_approved}, number={self.lesson_number}, duration={self.duration}>"
)
