class Image(db.Model):
__tablename__ = 'images'
id = db.Column(db.Integer, primary_key=True)
src = db.Column(db.Text)
story_id = db.Column(db.Integer, db.ForeignKey('stories.id'), index=True)
story = db.relationship('Story',
backref=backref('images', cascade='all,delete'))
class Equipment(db.Model):
__tablename__ = 'equipment'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer,
db.ForeignKey('users.id', ondelete='CASCADE'),
index=True)
user = db.relationship('User', backref='equipment')
type_id = db.Column(db.Integer,
db.ForeignKey('equipment_types.id'),
index=True)
type = db.relationship('EquipmentType',
backref=backref('equipment', cascade='all,delete'))
name = db.Column(db.String(128), nullable=False)
avatar = db.Column(db.String(128))
about = db.Column(db.Text)
@classmethod
def get_all(cls, id):
return cls.query.filter(cls.user_id == id).all()
@classmethod
def get_first(cls, id):
return cls.query.filter(cls.user_id == id).order_by(cls.id).first()
@classmethod
def get_by_id(cls, id):
return cls.query.get(id)
class User(UserMixin, db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(64), default='Имя')
second_name = db.Column(db.String(64), default='Фамилия')
email = db.Column(db.String(64), index=True, unique=True)
password_hash = db.Column(db.String(128))
about_me = db.Column(db.Text)
avatar = db.Column(db.String(128),
default=os.path.join(Config.IMAGE_PATH,
'default-avatar.png'))
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password_hash, password)
class EquipmentType(db.Model):
__tablename__ = 'equipment_types'
id = db.Column(db.Integer, primary_key=True)
type_name = db.Column(db.String(32), unique=True)
class Story(db.Model):
__tablename__ = 'stories'
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.Text)
stats_id = db.Column(db.Integer, db.ForeignKey('stats.id'), index=True)
class Stats(db.Model):
__tablename__ = 'stats'
id = db.Column(db.Integer, primary_key=True)
equipment_id = db.Column(db.Integer,
db.ForeignKey('equipment.id'),
index=True)
equipment = db.relationship('Equipment',
backref=backref('stats', cascade='all,delete'))
date = db.Column(db.Date, nullable=False)
distance = db.Column(db.BigInteger)
time = db.Column(db.BigInteger)
total_time = db.Column(db.BigInteger)
max_speed = db.Column(db.BigInteger)
steps = db.Column(db.BigInteger)
avg_cadence = db.Column(db.SmallInteger)
max_cadence = db.Column(db.SmallInteger)
avg_heart_rate = db.Column(db.SmallInteger)
max_heart_rate = db.Column(db.SmallInteger)
max_temperature = db.Column(db.Float)
min_temperature = db.Column(db.Float)
start_altitude = db.Column(db.SmallInteger)
total_up_altitude = db.Column(db.SmallInteger)
total_down_altitude = db.Column(db.SmallInteger)
min_altitude = db.Column(db.SmallInteger)
max_altitude = db.Column(db.SmallInteger)
story_id = db.Column(db.Integer, db.ForeignKey('stories.id'), index=True)
story = db.relationship('Story',
uselist=False,
cascade='all,delete',
backref='stats',
foreign_keys='Story.stats_id')
@classmethod
def get_by_id(cls, id):
return cls.query.get(id)
@classmethod
def filter_by_date_and_equipment(cls, function, id, start_date, end_date):
query = db.session.query(db.func.coalesce(
function, 0)).filter(cls.equipment_id == id).filter(
start_date <= cls.date).filter(cls.date <= end_date).scalar()
return query
@classmethod
def filter_by_date(cls, id, start_date, end_date):
query = cls.query.filter(cls.equipment_id == id).filter(
start_date <= cls.date).filter(cls.date <= end_date).order_by(
cls.date).all()
return query
@classmethod
def get_story_and_images(cls, id, start_date, end_date):
if start_date != end_date:
return None
query = cls.query.filter(cls.equipment_id == id).filter(
cls.date == start_date).first()
if query is None:
return {'story': None, 'main_image': None}
if query.story is None:
return {'story': None, 'main_image': None, 'id': query.id}
if not query.story.images:
return {
'story': query.story.text,
'main_image': None,
'id': query.id
}
main_image, *rest_images = [image.src for image in query.story.images]
return {
'story': query.story.text if query.story.text != '' else None,
'main_image': main_image,
'rest_images': rest_images,
'id': query.id
}
@classmethod
def get_statistics(
cls,
id,
start_date,
end_date,
):
total_distance = cls.filter_by_date_and_equipment(
db.func.sum(cls.distance), id, start_date, end_date)
total_time = cls.filter_by_date_and_equipment(db.func.sum(cls.time),
id, start_date, end_date)
statistics = {
'Тренировок':
cls.query.filter(cls.equipment_id == id).filter(
start_date <= cls.date).filter(cls.date <= end_date).count(),
'Дистанция':
total_distance / Config.METERS_PER_KILOMETER,
'Время':
convert_time_to_user_view(total_time),
'Общее время':
convert_time_to_user_view(
cls.filter_by_date_and_equipment(db.func.sum(cls.total_time),
id, start_date, end_date)),
'Всего шагов':
cls.filter_by_date_and_equipment(db.func.sum(cls.steps), id,
start_date, end_date),
'Общий подъем':
cls.filter_by_date_and_equipment(
db.func.sum(cls.total_up_altitude), id, start_date, end_date),
'Общий спуск':
cls.filter_by_date_and_equipment(
db.func.sum(cls.total_down_altitude), id, start_date,
end_date),
'Макс. скорость':
cls.filter_by_date_and_equipment(db.func.max(cls.max_speed), id,
start_date, end_date) /
Config.METERS_PER_KILOMETER,
'Макс. каденс':
cls.filter_by_date_and_equipment(db.func.max(cls.max_cadence), id,
start_date, end_date),
'Макс. пульс':
cls.filter_by_date_and_equipment(db.func.max(cls.max_heart_rate),
id, start_date, end_date),
'Макс. температура':
cls.filter_by_date_and_equipment(db.func.max(cls.max_temperature),
id, start_date, end_date),
'Макс. высота':
cls.filter_by_date_and_equipment(db.func.max(cls.max_altitude), id,
start_date, end_date),
'Каденс':
cls.filter_by_date_and_equipment(
db.func.sum(cls.avg_cadence * cls.time) / db.func.sum(
case([(cls.avg_cadence.isnot(None), Stats.time),
(cls.avg_cadence.is_(None), 0)])), id, start_date,
end_date),
'Средний пульс':
cls.filter_by_date_and_equipment(
db.func.sum(cls.avg_heart_rate * cls.time) / db.func.sum(
case([(cls.avg_heart_rate.isnot(None), Stats.time),
(cls.avg_heart_rate.is_(None), 0)])), id, start_date,
end_date),
'Скорость':
round(
total_distance / Config.METERS_PER_KILOMETER / total_time *
Config.SECONDS_PER_MINUTE *
Config.MINUTES_PER_HOUR, 2) if total_time != 0 else 0,
'Мин. температура':
cls.filter_by_date_and_equipment(db.func.min(cls.min_temperature),
id, start_date, end_date),
'Мин. высота':
cls.filter_by_date_and_equipment(db.func.min(cls.min_altitude), id,
start_date, end_date),
}