class Race(db.Model):
__tablename__ = 'race'
id = db.Column(db.Integer, primary_key=True)
course_id = db.Column(db.Integer, db.ForeignKey('course.id'))
course = db.relationship('Course')
date = db.Column(db.Date, nullable=False)
# results = db.relationship('Result',
# backref="race",
# order_by='Result.time'
# )
results = db.relationship('Result')
comment = db.Column(db.Text)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
def __repr__(self):
return "<Race(id:{}, {}, date {:%Y-%m-%d})>".format(
self.id, self.course, self.date)
def ranking():
query = db.session.query(
Member.show_name,
func.count(TrainingParticipant.training_id).label('cnt'), Member.sex,
Member.id).join(TrainingParticipant,
TrainingParticipant.member_id == Member.id).join(
Training,
TrainingParticipant.training_id == Training.id)
year_list = request.args.getlist('year_list')
app.logger.info(request.form)
begin = request.args.get("begin") or datetime.datetime(1990, 1, 1)
end = request.args.get("end") or datetime.datetime.today()
query = query.filter(Training.date >= begin) if begin else query
query = query.filter(Training.date <= end)
query = query.group_by(TrainingParticipant.member_id)
items = []
if year_list:
query = query.filter(Member.year.in_(year_list))
items = [{
'rank': i + 1,
'show_name': d[0],
't_cnt': d[1],
'sex': d[2],
'id': d[3]
} for i, d in enumerate(query.order_by(db.text('cnt DESC')).all())]
year_list = map(lambda x: int(x), year_list)
return render_template('ranking.html',
items=items,
begin=begin,
end=end,
year_list=year_list,
years=range(current_school_year, 1990, -1))
class Training(db.Model):
__tablename__ = 'training'
id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.Date, nullable=False)
type = db.Column(db.String(30), nullable=False)
weather = db.Column(db.String(30), nullable=False)
title = db.Column(db.String(100), nullable=False)
comment = db.Column(db.Text)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
participants = db.relationship(
'Member',
secondary=TrainingParticipant.__tablename__,
order_by='Member.year, Member.family_kana, Member.first_kana')
def __repr__(self):
return "<Training(id:{}, {:%Y-%m-%d}, place:{}, title:'{}')>"\
.format(self.id, self.date, self.place, self.title)
class Result(db.Model):
__tablename__ = 'result'
id = db.Column(db.Integer, primary_key=True)
race_id = db.Column(db.Integer, db.ForeignKey('race.id'))
race = db.relationship('Race')
time = db.Column(db.Integer)
distance = db.Column(db.Float)
comment = db.Column(db.Text)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
participants = db.relationship(
'Member',
secondary=RaceParticipant.__tablename__,
order_by='Member.year, Member.family_kana, Member.first_kana',
backref='results')
def __repr__(self):
return "<Result(id:{}, race_id:{}, time:{}, distance:{}, participants:{})>".\
format(self.id, self.race_id, self.time,
self.distance, self.participants)
class Course(db.Model):
__tablename__ = 'course'
id = db.Column(db.Integer, primary_key=True)
competition_id = db.Column(db.Integer, db.ForeignKey('competition.id'))
competition = db.relationship('Competition')
type = db.Column(db.String(30))
show_name = db.Column(db.String(30))
time = db.Column(db.Integer)
distance = db.Column(db.Float)
elevation = db.Column(db.Integer)
comment = db.Column(db.Text)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
races = db.relationship("Race")
def __repr__(self):
return "<Course(id:{}, 大会:{}, 距離:{}km)>".format(
self.id, self.competition.name, self.distance)
class Restaurant(db.Model):
__tablename__ = 'restaurant'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60), nullable=False)
place = db.Column(db.String(20))
score = db.Column(db.Float, server_default=db.text('0'))
comment = db.Column(db.Text)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
def __repr__(self):
return "<Restaurant(id:{}, name:{}, plase:{})>".\
format(self.id, self.name, self.place)
class After(db.Model):
__tablename__ = 'after'
id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.Date, nullable=False)
after_stage = db.Column(db.Integer,
nullable=False,
server_default=db.text('1'))
restaurant_id = db.Column(db.Integer,
db.ForeignKey('restaurant.id'),
nullable=False)
title = db.Column(db.String(128), nullable=False)
comment = db.Column(db.Text)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
restaurant = db.relationship('Restaurant')
participants = db.relationship(
'Member',
secondary=AfterParticipant.__tablename__,
order_by='Member.year, Member.family_kana, Member.first_kana')
def __repr__(self):
# return "<After(id:{}, {:%Y-%m-%d}, title:'{}')>".format(self.id,
# self.date, self.title)
tmp = 'id:{}'.format(self.id)
if self.date:
tmp += ', {:%Y-%m-%d}'.format(self.date)
tmp += ', {}次会'.format(self.after_stage)
tmp += ', {}'.format(self.title)
return "<After({})>".format(tmp)
def ranking():
q1 = db.session.query(Member.show_name, func.count(TrainingParticipant.training_id).label('cnt'), Member.sex).\
join(TrainingParticipant, TrainingParticipant.member_id == Member.id).\
group_by(TrainingParticipant.member_id)
year_list = request.args.getlist('year_list')
app.logger.info(year_list)
if year_list:
q2 = q1.\
filter(Member.year.in_(year_list))
else:
q2 = q1
items = [{'rank': i+1, 'show_name': d[0], 't_cnt': d[1], 'sex': d[2]}
for i, d in enumerate(q2.order_by(db.text('cnt DESC')).all())
]
return render_template('ranking.html', items=items, years=range(current_school_year, 1990, -1))
class AfterParticipant(db.Model):
__tablename__ = 'after_participant'
member_id = db.Column(db.Integer,
db.ForeignKey('member.id'),
primary_key=True)
after_id = db.Column(db.Integer,
db.ForeignKey('after.id'),
primary_key=True)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
def __repr__(self):
return "<AfterParticipant(after_id:{}, member_id:{})>".\
format(self.after_id, self.member_id)
class Competition(db.Model):
__tablename__ = 'competition'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60))
kana = db.Column(db.String(60))
show_name = db.Column(db.String(30))
place = db.Column(db.String(30))
comment = db.Column(db.Text)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
courses = db.relationship("Course")
def __repr__(self):
return "<Competition(id:{}, name:{}, place:{}, comment:'{}')>".format(
self.id, self.name, self.place, self.comment)
class Member(db.Model):
__tablename__ = 'member'
id = db.Column(db.Integer, primary_key=True)
family_name = db.Column(db.String(30), nullable=False)
first_name = db.Column(db.String(30), nullable=False)
show_name = db.Column(db.String(30), nullable=False)
# kana = db.Column(db.String(60), nullable=False)
family_kana = db.Column(db.String(30), nullable=True)
first_kana = db.Column(db.String(30), nullable=True)
year = db.Column(db.Integer, nullable=False)
sex = db.Column(db.Integer, nullable=False)
visible = db.Column(db.Boolean, nullable=False)
created_at = db.Column(db.DateTime,
nullable=False,
server_default=current_timestamp())
updated_at = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
# results = db.relationship('Result') # created by backref
def __repr__(self):
fields = {}
fields['id'] = self.id
fields['family_name'] = self.family_name
fields['first_name'] = self.first_name
fields['show_name'] = self.show_name
fields['year'] = self.year
fields['sex'] = self.sex
fields['visible'] = self.visible
return "<Member('{id}','{family_name}', '{first_name}', '{show_name}', {year}, {sex}, {visible})>".format(
**fields)
def member_individual(member_id):
m = Member.query.get(member_id)
if m is None:
return abort(404)
m.results.sort(key=lambda x: x.race.date, reverse=False)
raw_results = list(
filter(lambda x: x.race.course.distance in [42.195, 21.0975],
m.results))
results1 = []
results2 = []
races1 = []
races2 = []
for r in raw_results:
if r.distance == 42.195:
results1 += [{
'x': "{:%Y/%m/%d}".format(r.race.date),
'y': r.time // 1000
}]
races1 += [r.race.course.competition.name]
else:
results2 += [{
'x': "{:%Y/%m/%d}".format(r.race.date),
'y': r.time // 1000
}]
races2 += [r.race.course.competition.name]
trainings = db.session.query(TrainingParticipant.member_id, Training.date).\
filter(TrainingParticipant.member_id == member_id).\
join(Training, Training.id == TrainingParticipant.training_id).\
order_by(Training.date).all()
afters = db.session.query(AfterParticipant.member_id, After.date).\
filter(AfterParticipant.member_id == member_id).\
join(After, After.id == AfterParticipant.after_id).\
order_by(After.date)
afterdays = afters.distinct(After.date).all()
afters = afters.all()
first_training = trainings[0].date.strftime(
'%Y/%m/%d') if len(trainings) > 0 else '未参加'
first_after = afters[0].date.strftime(
'%Y/%m/%d') if len(afters) > 0 else '未参加'
count_trainings = len(trainings)
count_afters = len(afters)
count_afterdays = len(afterdays)
def summary(li, name):
for i in range(len(li)):
li[i] = {'year': li[i][1].year, 'month': li[i][1].month}
if li[i]['month'] < 4:
li[i]['year'] -= 1
li[i]['month'] += 12
x = []
for key, group in groupby(li, key=lambda x: x['year']):
y = []
for year in group:
y.append(year['month'])
x.append({
'year': key,
name + '_sum': len(y),
name + '_first_half': len([i for i in y if i < 10]),
name + '_second_half': len([i for i in y if i >= 10])
})
return x
participations = []
for key, group in groupby(
sorted(summary(trainings, "trainings") +
summary(afters, "afters") + summary(afterdays, "afterdays"),
key=lambda x: x['year']),
key=lambda x: x['year']):
y = []
for year in group:
y.append(year)
for i in range(len(y) - 1):
y[0].update(y[i + 1])
participations.append(y[0])
restaurants = db.session.query(AfterParticipant.member_id, Restaurant.name, Restaurant.id, func.count(After.id).label('cnt')).\
filter(AfterParticipant.member_id == member_id).\
join(After, After.id == AfterParticipant.after_id).\
join(Restaurant, Restaurant.id == After.restaurant_id).\
group_by(Restaurant.id).order_by(db.text('cnt DESC')).all()
return render_template('member_individual.html',
member=m,
results1=str(results1),
results2=str(results2),
races1=str(races1),
races2=str(races2),
first_training=first_training,
first_after=first_after,
count_trainings=count_trainings,
count_afters=count_afters,
count_afterdays=count_afterdays,
participations=participations,
restaurants=restaurants)
