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 ProductImage(db.Model):
"""Represents an ordered image for a particular variety of a product."""
__tablename__ = 'product_images'
__table_args__ = (db.UniqueConstraint('variety_id',
'order',
name='unique order indices',
deferrable=True,
initially='DEFERRED'), )
id = db.Column(db.Integer, primary_key=True)
variety_id = db.Column(db.Integer,
db.ForeignKey('varieties.id', ondelete='CASCADE'),
nullable=False)
variety = db.relationship('Variety',
uselist=False,
back_populates='images')
image_id = db.Column(db.Integer,
db.ForeignKey('static_files.id', ondelete='CASCADE'),
nullable=False)
image = db.relationship('StaticFile',
back_populates='product_image',
uselist=False)
order = db.Column(db.Integer,
db.CheckConstraint('"order" >= 0',
name='non-negative order'),
nullable=False)
class Variety(db.Model):
"""Represents various types of one product."""
__tablename__ = 'varieties'
__table_args__ = (
# Warning: this index requires a manually written migration.
# In upgrade() use:
# op.create_index('unique varieties', 'varieties',
# ['product_id',
# sa.text("coalesce(color, '')"),
# sa.text("coalesce(size, '')")],
# unique=True)
#
# In downgrade() use:
# op.drop_index('unique varieties', 'varieties')
db.Index('unique varieties',
'product_id',
db.text("coalesce(color, '')"),
db.text("coalesce(size, '')"),
unique=True), )
id = db.Column(db.Integer, primary_key=True)
product_id = db.Column(db.Integer,
db.ForeignKey('products.id', ondelete='CASCADE'),
nullable=False)
product = db.relationship('Product', back_populates='varieties')
size = db.Column(db.String(3),
db.ForeignKey('sizes.value', ondelete='CASCADE'),
nullable=True)
color = db.Column(db.String(6),
db.ForeignKey('colors.value', ondelete='CASCADE'),
nullable=True)
images = db.relationship('ProductImage',
cascade='all, delete-orphan',
passive_deletes=True,
back_populates='variety')
stock_changes = db.relationship('StockChange',
cascade='all, delete-orphan',
passive_deletes=True,
back_populates='variety')
@property
def amount(self):
"""Return the amount of items of this variety, computed
from the StockChange instances."""
return db.session.query(db.func.sum(StockChange.amount)).filter(
StockChange.variety_id == self.id,
StockChange.status != StockChangeStatus.rejected).scalar() or 0
@property
def purchases(self):
"""Return the amount of purchases of this variety, computed
from the StockChange instances."""
# pylint: disable=invalid-unary-operand-type
return -(db.session.query(db.func.sum(StockChange.amount)).join(
StockChange.account).filter(
StockChange.variety_id == self.id,
StockChange.status != StockChangeStatus.rejected,
StockChange.amount < 0, ~Account.is_admin).scalar() or 0)
class ProjectFile(db.Model):
"""Represents the files that can only be accessed by volunteers and moderators
of a certain project.
WARNING: this class is currently not used."""
__tablename__ = 'project_files'
project_id = db.Column(db.Integer,
db.ForeignKey('projects.id', ondelete='CASCADE'),
primary_key=True)
file_id = db.Column(db.Integer,
db.ForeignKey('static_files.id', ondelete='CASCADE'),
primary_key=True)
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 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 Transaction(db.Model):
"""Represents a change in the innopoints balance for a certain user."""
__tablename__ = 'transactions'
__table_args__ = (
db.CheckConstraint('(stock_change_id IS NULL) OR (feedback_id IS NULL)',
name='not(feedback and stock_change)'),
)
id = db.Column(db.Integer, primary_key=True)
account_email = db.Column(db.String(128),
db.ForeignKey('accounts.email', ondelete='CASCADE'),
nullable=False)
account = db.relationship('Account', back_populates='transactions')
change = db.Column(db.Integer, nullable=False)
stock_change_id = db.Column(db.Integer,
db.ForeignKey('stock_changes.id', ondelete='SET NULL'),
nullable=True)
stock_change = db.relationship('StockChange',
back_populates='transaction')
feedback_id = db.Column(db.Integer,
db.ForeignKey('feedback.application_id', ondelete='SET NULL'),
nullable=True)
feedback = db.relationship('Feedback',
back_populates='transaction')
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 StaticFile(db.Model):
"""Represents the user-uploaded static files."""
__tablename__ = 'static_files'
id = db.Column(db.Integer, primary_key=True)
mimetype = db.Column(db.String(255), nullable=False)
owner_email = db.Column(db.String(128),
db.ForeignKey('accounts.email',
ondelete='CASCADE'),
nullable=False)
owner = db.relationship('Account', back_populates='static_files')
product_image = db.relationship('ProductImage',
uselist=False,
cascade='all, delete-orphan',
passive_deletes=True,
back_populates='image')
project_file = db.relationship('ProjectFile',
uselist=False,
cascade='all, delete-orphan',
passive_deletes=True)
cover_for = db.relationship('Project', back_populates='image')
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')
"""The many-to-many relationship between Feedback and Competence."""
from innopoints.extensions import db
feedback_competence = db.Table(
'feedback_competence',
db.Column('feedback_id', db.Integer,
db.ForeignKey('feedback.application_id', ondelete='CASCADE'),
primary_key=True),
db.Column('competence_id', db.Integer,
db.ForeignKey('competences.id', ondelete='CASCADE'),
primary_key=True)
)
"""The many-to-many relationship between Activity and Competence."""
from innopoints.extensions import db
activity_competence = db.Table(
'activity_competence',
db.Column('activity_id', db.Integer,
db.ForeignKey('activities.id', ondelete='CASCADE'),
primary_key=True),
db.Column('competence_id', db.Integer,
db.ForeignKey('competences.id', ondelete='CASCADE'),
primary_key=True)
)
"""The many-to-many relationship between Project and its moderators – Account."""
from innopoints.extensions import db
project_moderation = db.Table(
'project_moderation',
db.Column('project_id', db.Integer,
db.ForeignKey('projects.id', ondelete='CASCADE'),
primary_key=True),
db.Column('account_email', db.String(128),
db.ForeignKey('accounts.email', ondelete='CASCADE'),
primary_key=True)
)
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))
"""The many-to-many relationship between Project and Tag."""
from innopoints.extensions import db
project_tags = db.Table(
'project_tags',
db.Column('project_id',
db.Integer,
db.ForeignKey('projects.id', ondelete='CASCADE'),
primary_key=True),
db.Column('tag_id',
db.Integer,
db.ForeignKey('tags.id', ondelete='CASCADE'),
primary_key=True))