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)