class Product(db.Model):
"""Product describes an item in the InnoStore that a user may purchase."""
__tablename__ = 'products'
__table_args__ = __table_args__ = (
db.UniqueConstraint('name', 'type',
name='unique product'),
)
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), nullable=False)
type = db.Column(db.String(128), nullable=True)
description = db.Column(db.String(1024), nullable=False)
varieties = db.relationship('Variety',
cascade='all, delete-orphan',
passive_deletes=True,
back_populates='product')
price = db.Column(db.Integer,
db.CheckConstraint('price >= 0', name='non-negative price'),
nullable=False)
addition_time = db.Column(db.DateTime(timezone=True), nullable=False, default=tz_aware_now)
def __str__(self):
"""Human-readable representation of a product."""
if self.type is None:
return self.name
return f"'{self.name}' {self.type}"
class Application(db.Model):
"""Represents a volunteering application."""
__tablename__ = 'applications'
__table_args__ = (
db.UniqueConstraint('applicant_email', 'activity_id',
name='only one application'),
)
id = db.Column(db.Integer, primary_key=True)
applicant_email = db.Column(db.String(128),
db.ForeignKey('accounts.email', ondelete='CASCADE'),
nullable=False)
applicant = db.relationship('Account', back_populates='applications')
activity_id = db.Column(db.Integer,
db.ForeignKey('activities.id', ondelete='CASCADE'),
nullable=False)
activity = db.relationship('Activity',
uselist=False,
single_parent=True,
back_populates='applications')
comment = db.Column(db.String(1024), nullable=True)
application_time = db.Column(db.DateTime(timezone=True), nullable=False, default=tz_aware_now)
telegram_username = db.Column(db.String(32), nullable=True)
status = db.Column(db.Enum(ApplicationStatus),
nullable=False,
default=ApplicationStatus.pending)
actual_hours = db.Column(db.Integer, nullable=False)
reports = db.relationship('VolunteeringReport',
cascade='all, delete-orphan',
back_populates='application')
feedback = db.relationship('Feedback',
uselist=False,
cascade='all, delete-orphan',
passive_deletes=True,
back_populates='application')
class Project(db.Model):
"""Represents a project for volunteering."""
__tablename__ = 'projects'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), nullable=True)
image_id = db.Column(db.Integer,
db.ForeignKey('static_files.id'),
nullable=True)
image = db.relationship('StaticFile', back_populates='cover_for')
creation_time = db.Column(db.DateTime(timezone=True),
nullable=False,
default=tz_aware_now)
activities = db.relationship('Activity',
cascade='all, delete-orphan',
passive_deletes=True,
back_populates='project')
moderators = db.relationship('Account',
secondary='project_moderation',
back_populates='moderated_projects')
creator_email = db.Column(db.String(128),
db.ForeignKey('accounts.email',
ondelete='CASCADE'),
nullable=False)
creator = db.relationship('Account', back_populates='created_projects')
admin_feedback = db.Column(db.String(1024), nullable=True)
review_status = db.Column(db.Enum(ReviewStatus), nullable=True)
lifetime_stage = db.Column(db.Enum(LifetimeStage),
nullable=False,
default=LifetimeStage.draft)
tags = db.relationship('Tag', secondary='project_tags')
@property
def start_date(self):
"""Returns the project start date as the earliest start_time of its activities."""
return db.session.query(db.func.min(Activity.start_date), ).filter(
Activity.project_id == self.id, ).scalar()
@property
def end_date(self):
"""Returns the project end date as the earliest start_time of its activities."""
return db.session.query(db.func.max(Activity.end_date), ).filter(
Activity.project_id == self.id, ).scalar()
@property
def image_url(self):
"""Return an image URL constructed from the ID."""
if self.image_id is None:
return None
return f'/file/{self.image_id}'
class Notification(db.Model):
"""Represents a notification about a certain event."""
__tablename__ = 'notifications'
id = db.Column(db.Integer, primary_key=True)
recipient_email = db.Column(db.String(128),
db.ForeignKey('accounts.email',
ondelete='CASCADE'),
nullable=False)
recipient = db.relationship('Account', back_populates='notifications')
is_read = db.Column(db.Boolean, nullable=False, default=False)
payload = db.Column(JSONB, nullable=True)
timestamp = db.Column(db.DateTime(timezone=True),
nullable=False,
default=tz_aware_now)
type = db.Column(db.Enum(NotificationType), nullable=False)
class VolunteeringReport(db.Model):
"""Represents a moderator's report about a certain occurence of work
done by a volunteer."""
__tablename__ = 'reports'
__table_args__ = (db.PrimaryKeyConstraint('application_id',
'reporter_email'), )
application_id = db.Column(
db.Integer, db.ForeignKey('applications.id', ondelete='CASCADE'))
application = db.relationship('Application', back_populates='reports')
reporter_email = db.Column(db.String(128),
db.ForeignKey('accounts.email',
ondelete='CASCADE'),
nullable=False)
reporter = db.relationship('Account', back_populates='reports')
time = db.Column(db.DateTime(timezone=True),
nullable=False,
default=tz_aware_now)
rating = db.Column(db.Integer,
db.CheckConstraint('rating <= 5 AND rating >= 1'),
nullable=False)
content = db.Column(db.String(1024), nullable=True)
class StockChange(db.Model):
"""Represents the change in the amount of variety available."""
__tablename__ = 'stock_changes'
id = db.Column(db.Integer, primary_key=True)
amount = db.Column(db.Integer, nullable=False)
time = db.Column(db.DateTime(timezone=True),
nullable=False,
default=tz_aware_now)
status = db.Column(db.Enum(StockChangeStatus), nullable=False)
account_email = db.Column(db.String(128),
db.ForeignKey('accounts.email',
ondelete='CASCADE'),
nullable=False)
account = db.relationship('Account', back_populates='stock_changes')
variety_id = db.Column(db.Integer,
db.ForeignKey('varieties.id', ondelete='CASCADE'),
nullable=False)
variety = db.relationship('Variety', back_populates='stock_changes')
transaction = db.relationship('Transaction',
uselist=False,
single_parent=True,
back_populates='stock_change')
class Feedback(db.Model):
"""Represents a volunteer's feedback on an activity."""
__tablename__ = 'feedback'
application_id = db.Column(db.Integer,
db.ForeignKey('applications.id',
ondelete='CASCADE'),
unique=True,
primary_key=True)
application = db.relationship('Application',
back_populates='feedback',
uselist=False,
single_parent=True)
competences = db.relationship('Competence',
secondary='feedback_competence')
time = db.Column(db.DateTime(timezone=True),
nullable=False,
default=tz_aware_now)
answers = db.Column(db.ARRAY(db.String(1024)), nullable=False)
transaction = db.relationship('Transaction',
uselist=False,
single_parent=True,
back_populates='feedback')
class Activity(db.Model):
"""Represents a volunteering activity in the project."""
__tablename__ = 'activities'
__table_args__ = (
db.CheckConstraint('working_hours == NULL OR working_hours >= 0',
name='working hours are non-negative'),
db.CheckConstraint('people_required == NULL OR people_required >= 0',
name='people required are unset or non-negative'),
db.CheckConstraint(
'draft OR working_hours != NULL',
name='working hours are not nullable for non-drafts'),
db.CheckConstraint(
f'draft OR (fixed_reward AND working_hours = 1) '
f'OR (NOT fixed_reward AND reward_rate = {IPTS_PER_HOUR})',
name='reward policy'),
)
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), nullable=True)
description = db.Column(db.String(1024), nullable=True)
start_date = db.Column(db.DateTime(timezone=True), nullable=True)
end_date = db.Column(db.DateTime(timezone=True), nullable=True)
project_id = db.Column(db.Integer,
db.ForeignKey('projects.id', ondelete='CASCADE'),
nullable=False)
project = db.relationship('Project', back_populates='activities')
working_hours = db.Column(db.Integer, nullable=True, default=1)
reward_rate = db.Column(db.Integer, nullable=False, default=IPTS_PER_HOUR)
fixed_reward = db.Column(db.Boolean, nullable=False, default=False)
people_required = db.Column(db.Integer, nullable=True)
telegram_required = db.Column(db.Boolean, nullable=False, default=False)
competences = db.relationship('Competence',
secondary='activity_competence')
application_deadline = db.Column(db.DateTime(timezone=True), nullable=True)
feedback_questions = db.Column(db.ARRAY(db.String(1024)),
nullable=False,
default=DEFAULT_QUESTIONS)
internal = db.Column(db.Boolean, nullable=False, default=False)
draft = db.Column(db.Boolean, nullable=False, default=True)
applications = db.relationship('Application',
cascade='all, delete-orphan',
passive_deletes=True,
back_populates='activity')
@property
def dates(self):
"""Return the activity dates as a single JSON object."""
return {
'start': self.start_date.isoformat(),
'end': self.end_date.isoformat()
}
@property
def accepted_applications(self):
"""Return the amount of accepted applications."""
return Application.query.filter_by(
activity_id=self.id, status=ApplicationStatus.approved).count()
@property
def vacant_spots(self):
"""Return the amount of vacant spots for the activity."""
if self.people_required is None:
return -1
return self.people_required - self.accepted_applications
def has_application_from(self, user):
"""Return whether the given user has applied for this activity."""
application = Application.query.filter_by(applicant=user,
activity_id=self.id)
return db.session.query(application.exists()).scalar()
@property
def is_complete(self):
"""Return whether the all the required fields for an activity have been filled out."""
return (self.name is not None and not self.name.isspace()
and self.start_date is not None and self.end_date is not None
and self.start_date <= self.end_date
and self.working_hours is not None
and self.reward_rate is not None
and len(self.competences) in range(1, 4))