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, }
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} )
def user_id(self): return reference_col("users", nullable=False)
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}>" )