def _get_last_year_statistics():
query = db.session.query(func.count('*').label('flights'),
func.sum(Flight.olc_classic_distance).label('distance'),
func.sum(Flight.duration).label('duration')) \
.filter(Flight.pilot == g.user) \
.filter(Flight.date_local > (date.today() - timedelta(days=365))) \
.filter(Flight.is_rankable()) \
.first()
last_year_statistics = dict(flights=0,
distance=0,
duration=timedelta(0),
speed=0)
if query and query.flights > 0:
duration_seconds = query.duration.days * 24 * 3600 + query.duration.seconds
if duration_seconds > 0:
last_year_statistics['speed'] = float(
query.distance) / duration_seconds
last_year_statistics['flights'] = query.flights
last_year_statistics['distance'] = query.distance
last_year_statistics['duration'] = query.duration
last_year_statistics[
'average_distance'] = query.distance / query.flights
last_year_statistics[
'average_duration'] = query.duration / query.flights
return last_year_statistics
def _distance_flight(user, distance, schema):
flight = (Flight.query().filter(Flight.pilot == user).filter(
Flight.olc_classic_distance >= distance).order_by(
Flight.landing_time).filter(Flight.is_rankable()).first())
if flight:
return schema.dump(flight).data
def _get_distance_flight(distance):
return Flight.query() \
.filter(Flight.pilot == g.user) \
.filter(Flight.olc_classic_distance >= distance) \
.order_by(Flight.landing_time) \
.filter(Flight.is_rankable()) \
.first()
def _list():
query = (Notification.query(
recipient_id=request.user_id).join("event").options(
contains_eager("event")).options(
subqueryload("event.actor")).outerjoin(Event.flight).options(
contains_eager("event.flight")).filter(
or_(Event.flight == None,
Flight.is_rankable())).order_by(Event.time.desc()))
query = _filter_query(query, request.args)
page = request.args.get("page", type=int, default=1)
per_page = request.args.get("per_page", type=int, default=50)
query = query.limit(per_page)
query = query.offset((page - 1) * per_page)
def get_event(notification):
event = notification.event
event.unread = notification.time_read is None
return event
events = list(
convert_event(get_event(notification)) for notification in query)
return jsonify(events=events)
def _get_last_year_statistics():
query = db.session.query(func.count('*').label('flights'),
func.sum(Flight.olc_classic_distance).label('distance'),
func.sum(Flight.duration).label('duration')) \
.filter(Flight.pilot == g.user) \
.filter(Flight.date_local > (date.today() - timedelta(days=365))) \
.filter(Flight.is_rankable()) \
.first()
last_year_statistics = dict(flights=0,
distance=0,
duration=timedelta(0),
speed=0)
if query and query.flights > 0:
duration_seconds = query.duration.days * 24 * 3600 + query.duration.seconds
if duration_seconds > 0:
last_year_statistics['speed'] = float(query.distance) / duration_seconds
last_year_statistics['flights'] = query.flights
last_year_statistics['distance'] = query.distance
last_year_statistics['duration'] = query.duration
last_year_statistics['average_distance'] = query.distance / query.flights
last_year_statistics['average_duration'] = query.duration / query.flights
return last_year_statistics
def index():
query = Event.query() \
.options(subqueryload('actor')) \
.options(subqueryload('user')) \
.options(subqueryload('club')) \
.outerjoin(Event.flight) \
.options(contains_eager(Event.flight)) \
.filter(or_(Event.flight == None, Flight.is_rankable())) \
.order_by(Event.time.desc())
query = _filter_query(query, request.args)
page = request.args.get('page', type=int, default=1)
per_page = request.args.get('per_page', type=int, default=50)
events = query.limit(per_page).offset((page - 1) * per_page).all()
events_count = len(events)
if request.args.get('grouped', True, type=str_to_bool):
events = group_events(events)
template_vars = dict(events=events, types=Event.Type)
if page > 1:
template_vars['prev_page'] = page - 1
if events_count == per_page:
template_vars['next_page'] = page + 1
return render_template('timeline/list.jinja', **template_vars)
def _get_distance_flight(distance):
return Flight.query() \
.filter(Flight.pilot == g.user) \
.filter(Flight.olc_classic_distance >= distance) \
.order_by(Flight.landing_time) \
.filter(Flight.is_rankable()) \
.first()
def _list():
query = Notification.query(recipient_id=request.user_id) \
.join('event') \
.options(contains_eager('event')) \
.options(subqueryload('event.actor')) \
.outerjoin(Event.flight) \
.options(contains_eager('event.flight')) \
.filter(or_(Event.flight == None, Flight.is_rankable())) \
.order_by(Event.time.desc())
query = _filter_query(query, request.args)
page = request.args.get('page', type=int, default=1)
per_page = request.args.get('per_page', type=int, default=50)
query = query.limit(per_page)
query = query.offset((page - 1) * per_page)
def get_event(notification):
event = notification.event
event.unread = (notification.time_read is None)
return event
events = map(get_event, query)
return jsonify(events=(map(convert_event, events)))
def _get_result(model, flight_field, year=None):
subq = db.session \
.query(getattr(Flight, flight_field),
func.count('*').label('count'),
func.sum(Flight.index_score).label('total')) \
.group_by(getattr(Flight, flight_field)) \
.outerjoin(Flight.model) \
.filter(Flight.is_rankable())
if isinstance(year, int):
year_start = date(year, 1, 1)
year_end = date(year, 12, 31)
subq = subq.filter(Flight.date_local >= year_start) \
.filter(Flight.date_local <= year_end)
subq = subq.subquery()
result = db.session \
.query(model, subq.c.count, subq.c.total,
over(func.rank(), order_by=desc('total')).label('rank')) \
.join((subq, getattr(subq.c, flight_field) == model.id))
if model == User:
result = result.outerjoin(model.club)
result = result.options(eagerload(model.club))
return result
def _get_takeoff_locations(user):
locations = Location.get_clustered_locations(
Flight.takeoff_location_wkt,
filter=and_(Flight.pilot == user, Flight.is_rankable()),
)
return [loc.to_lonlat() for loc in locations]
def _list():
query = (
Notification.query(recipient_id=request.user_id)
.join("event")
.options(contains_eager("event"))
.options(subqueryload("event.actor"))
.outerjoin(Event.flight)
.options(contains_eager("event.flight"))
.filter(or_(Event.flight == None, Flight.is_rankable()))
.order_by(Event.time.desc())
)
query = _filter_query(query, request.args)
page = request.args.get("page", type=int, default=1)
per_page = request.args.get("per_page", type=int, default=50)
query = query.limit(per_page)
query = query.offset((page - 1) * per_page)
def get_event(notification):
event = notification.event
event.unread = notification.time_read is None
return event
events = list(convert_event(get_event(notification)) for notification in query)
return jsonify(events=events)
def list():
query = Notification.query(recipient_id=request.user_id) \
.join('event') \
.options(contains_eager('event')) \
.options(subqueryload('event.actor')) \
.outerjoin(Event.flight) \
.options(contains_eager('event.flight')) \
.filter(or_(Event.flight == None, Flight.is_rankable())) \
.order_by(Event.time.desc())
query = _filter_query(query, request.args)
page = request.args.get('page', type=int, default=1)
per_page = request.args.get('per_page', type=int, default=50)
query = query.limit(per_page)
query = query.offset((page - 1) * per_page)
def get_event(notification):
event = notification.event
event.unread = (notification.time_read is None)
return event
events = map(get_event, query)
return jsonify(events=(map(convert_event, events)))
def index():
query = Event.query() \
.options(subqueryload('actor')) \
.options(subqueryload('user')) \
.options(subqueryload('club')) \
.outerjoin(Event.flight) \
.options(contains_eager(Event.flight)) \
.filter(or_(Event.flight == None, Flight.is_rankable())) \
.order_by(Event.time.desc())
query = _filter_query(query, request.args)
page = request.args.get('page', type=int, default=1)
per_page = request.args.get('per_page', type=int, default=50)
query = query.limit(per_page)
query = query.offset((page - 1) * per_page)
events = query.all()
events_count = len(events)
if request.args.get('grouped', True, type=str_to_bool):
events = group_events(events)
template_vars = dict(events=events, types=Event.Type)
if page > 1:
template_vars['prev_page'] = page - 1
if events_count == per_page:
template_vars['next_page'] = page + 1
return render_template('timeline/list.jinja', **template_vars)
def _get_result(model, flight_field, year=None):
subq = (
db.session.query(
getattr(Flight, flight_field),
func.count("*").label("count"),
func.sum(Flight.index_score).label("total"),
)
.group_by(getattr(Flight, flight_field))
.outerjoin(Flight.model)
.filter(Flight.is_rankable())
)
if isinstance(year, int):
year_start = date(year, 1, 1)
year_end = date(year, 12, 31)
subq = subq.filter(Flight.date_local >= year_start).filter(
Flight.date_local <= year_end
)
subq = subq.subquery()
result = db.session.query(
model,
subq.c.count,
subq.c.total,
over(func.rank(), order_by=desc("total")).label("rank"),
).join((subq, getattr(subq.c, flight_field) == model.id))
if model == User:
result = result.outerjoin(model.club)
result = result.options(eagerload(model.club))
return result
def _largest_flight(user, schema):
flight = user.get_largest_flights() \
.filter(Flight.is_rankable()) \
.first()
if flight:
return schema.dump(flight).data
def index(page=None, id=None):
if 'application/json' not in request.headers.get('Accept', ''):
return render_template('ember-page.jinja', active_page='statistics')
name = None
query = db.session.query(Flight.year.label('year'),
func.count('*').label('flights'),
func.count(distinct(Flight.pilot_id)).label('pilots'),
func.sum(Flight.olc_classic_distance).label('distance'),
func.sum(Flight.duration).label('duration'))
pilots_query = db.session.query(func.count(distinct(Flight.pilot_id)))
if page == 'pilot':
pilot = get_requested_record(User, id)
name = unicode(pilot)
query = query.filter(Flight.pilot_id == pilot.id)
elif page == 'club':
club = get_requested_record(Club, id)
name = unicode(club)
query = query.filter(Flight.club_id == club.id)
pilots_query = pilots_query.filter(Flight.club_id == club.id)
elif page == 'airport':
airport = get_requested_record(Airport, id)
name = unicode(airport)
query = query.filter(Flight.takeoff_airport_id == airport.id)
pilots_query = pilots_query.filter(Flight.takeoff_airport_id == airport.id)
elif page is not None:
abort(404)
query = query.filter(Flight.is_rankable())
query = query.group_by(Flight.year).order_by(Flight.year.desc())
if page == 'pilot':
sum_pilots = 0
else:
sum_pilots = pilots_query.scalar()
list = []
for row in query:
row.average_distance = row.distance / row.flights
row.average_duration = row.duration / row.flights
list.append({
'year': row.year,
'flights': row.flights,
'distance': row.distance,
'duration': row.duration.total_seconds(),
'pilots': row.pilots,
'average_distance': row.distance / row.flights,
'average_duration': row.duration.total_seconds() / row.flights,
})
return jsonify(name=name, years=list, sumPilots=sum_pilots)
def index(page=None, id=None):
name = None
query = db.session.query(
Flight.year.label("year"),
func.count("*").label("flights"),
func.count(distinct(Flight.pilot_id)).label("pilots"),
func.sum(Flight.olc_classic_distance).label("distance"),
func.sum(Flight.duration).label("duration"),
)
pilots_query = db.session.query(func.count(distinct(Flight.pilot_id)))
if page == "pilot":
pilot = get_requested_record(User, id)
name = pilot.name
query = query.filter(Flight.pilot_id == pilot.id)
elif page == "club":
club = get_requested_record(Club, id)
name = club.name
query = query.filter(Flight.club_id == club.id)
pilots_query = pilots_query.filter(Flight.club_id == club.id)
elif page == "airport":
airport = get_requested_record(Airport, id)
name = airport.name
query = query.filter(Flight.takeoff_airport_id == airport.id)
pilots_query = pilots_query.filter(Flight.takeoff_airport_id == airport.id)
elif page is not None:
abort(404)
query = query.filter(Flight.is_rankable())
query = query.group_by(Flight.year).order_by(Flight.year.desc())
if page == "pilot":
sum_pilots = 0
else:
sum_pilots = pilots_query.scalar()
list = []
for row in query:
list.append(
{
"year": row.year,
"flights": row.flights,
"distance": row.distance,
"duration": row.duration.total_seconds(),
"pilots": row.pilots,
"average_distance": row.distance / row.flights,
"average_duration": row.duration.total_seconds() / row.flights,
}
)
return jsonify(name=name, years=list, sumPilots=sum_pilots)
def _quick_stats():
result = db.session.query(func.count('*').label('flights'),
func.sum(Flight.olc_classic_distance).label('distance'),
func.sum(Flight.duration).label('duration')) \
.filter(Flight.pilot == g.user) \
.filter(Flight.date_local > (date.today() - timedelta(days=365))) \
.filter(Flight.is_rankable()) \
.one()
return QuickStatsSchema().dump(result).data
def _quick_stats(user):
result = db.session.query(func.count('*').label('flights'),
func.sum(Flight.olc_classic_distance).label('distance'),
func.sum(Flight.duration).label('duration')) \
.filter(Flight.pilot == user) \
.filter(Flight.date_local > (date.today() - timedelta(days=365))) \
.filter(Flight.is_rankable()) \
.one()
return QuickStatsSchema().dump(result).data
def _distance_flight(user, distance, schema):
flight = Flight.query() \
.filter(Flight.pilot == user) \
.filter(Flight.olc_classic_distance >= distance) \
.order_by(Flight.landing_time) \
.filter(Flight.is_rankable()) \
.first()
if flight:
return schema.dump(flight).data
def _quick_stats(user):
result = (db.session.query(
func.count("*").label("flights"),
func.sum(Flight.olc_classic_distance).label("distance"),
func.sum(Flight.duration).label("duration"),
).filter(Flight.pilot == user).filter(
Flight.date_local > (date.today() - timedelta(days=365))).filter(
Flight.is_rankable()).one())
return QuickStatsSchema().dump(result).data
def _quick_stats(user):
result = (
db.session.query(
func.count("*").label("flights"),
func.sum(Flight.olc_classic_distance).label("distance"),
func.sum(Flight.duration).label("duration"),
)
.filter(Flight.pilot == user)
.filter(Flight.date_local > (date.today() - timedelta(days=365)))
.filter(Flight.is_rankable())
.one()
)
return QuickStatsSchema().dump(result).data
def _list():
query = (Event.query().options(subqueryload("user")).options(
subqueryload("club")).outerjoin(Event.flight).options(
contains_eager(Event.flight)).filter(
or_(Event.flight == None,
Flight.is_rankable())).order_by(Event.time.desc()))
query = _filter_query(query, request.args)
page = request.args.get("page", type=int, default=1)
per_page = request.args.get("per_page", type=int, default=50)
events = query.limit(per_page).offset((page - 1) * per_page).all()
return jsonify(events=([convert_event(event) for event in events]))
def _list():
query = Event.query() \
.options(subqueryload('actor')) \
.options(subqueryload('user')) \
.options(subqueryload('club')) \
.outerjoin(Event.flight) \
.options(contains_eager(Event.flight)) \
.filter(or_(Event.flight == None, Flight.is_rankable())) \
.order_by(Event.time.desc())
query = _filter_query(query, request.args)
page = request.args.get('page', type=int, default=1)
per_page = request.args.get('per_page', type=int, default=50)
events = query.limit(per_page).offset((page - 1) * per_page).all()
return jsonify(events=(map(convert_event, events)))
def _get_distance_flights():
distance_flights = []
largest_flight = g.user.get_largest_flights() \
.filter(Flight.is_rankable()) \
.first()
if largest_flight:
distance_flights.append(
[largest_flight.olc_classic_distance, largest_flight])
for distance in [50000, 100000, 300000, 500000, 700000, 1000000]:
distance_flight = _get_distance_flight(distance)
if distance_flight is not None:
distance_flights.append([distance, distance_flight])
distance_flights.sort()
return distance_flights
def _get_distance_flights():
distance_flights = []
largest_flight = g.user.get_largest_flights() \
.filter(Flight.is_rankable()) \
.first()
if largest_flight:
distance_flights.append([largest_flight.olc_classic_distance,
largest_flight])
for distance in [50000, 100000, 300000, 500000, 700000, 1000000]:
distance_flight = _get_distance_flight(distance)
if distance_flight is not None:
distance_flights.append([distance, distance_flight])
distance_flights.sort()
return distance_flights
def _list():
query = (
Event.query()
.options(subqueryload("user"))
.options(subqueryload("club"))
.outerjoin(Event.flight)
.options(contains_eager(Event.flight))
.filter(or_(Event.flight == None, Flight.is_rankable()))
.order_by(Event.time.desc())
)
query = _filter_query(query, request.args)
page = request.args.get("page", type=int, default=1)
per_page = request.args.get("per_page", type=int, default=50)
events = query.limit(per_page).offset((page - 1) * per_page).all()
return jsonify(events=([convert_event(event) for event in events]))
def index():
if 'application/json' not in request.headers.get('Accept', ''):
return render_template('ember-page.jinja')
query = Event.query() \
.options(subqueryload('actor')) \
.options(subqueryload('user')) \
.options(subqueryload('club')) \
.outerjoin(Event.flight) \
.options(contains_eager(Event.flight)) \
.filter(or_(Event.flight == None, Flight.is_rankable())) \
.order_by(Event.time.desc())
query = _filter_query(query, request.args)
page = request.args.get('page', type=int, default=1)
per_page = request.args.get('per_page', type=int, default=50)
events = query.limit(per_page).offset((page - 1) * per_page).all()
return jsonify(events=(map(convert_event, events)))
def flights_js():
flights = Flight.query() \
.filter(Flight.is_rankable())
# Filter by user
user_id = request.values.get('user', type=int)
if user_id:
user = User.get(user_id)
if not user:
abort(404)
flights = flights.filter(
or_(Flight.pilot == user, Flight.co_pilot == user))
# Filter by club
club_id = request.values.get('club', type=int)
if club_id:
club = Club.get(club_id)
if not club:
abort(404)
flights = flights.filter(Flight.club == club)
# Order by date and distance
flights = flights.order_by(Flight.date_local.desc(),
Flight.olc_classic_distance)
# Limit number of flights
limit = request.values.get('limit', type=int, default=5)
if not 0 < limit <= 30:
raise BadRequest('The `limit` parameter must be between 1 and 30.')
flights = flights.limit(limit)
# Produce JS response
callback = request.values.get('callback', 'onFlightsLoaded')
return wrap(callback,
render_template('widgets/flights.jinja', flights=flights))
def flights_js():
flights = Flight.query() \
.filter(Flight.is_rankable())
# Filter by user
user_id = request.values.get('user', type=int)
if user_id:
user = User.get(user_id)
if not user:
abort(404)
flights = flights.filter(or_(
Flight.pilot == user,
Flight.co_pilot == user
))
# Filter by club
club_id = request.values.get('club', type=int)
if club_id:
club = Club.get(club_id)
if not club:
abort(404)
flights = flights.filter(Flight.club == club)
# Order by date and distance
flights = flights.order_by(Flight.date_local.desc(), Flight.olc_classic_distance)
# Limit number of flights
limit = request.values.get('limit', type=int, default=5)
if not 0 < limit <= 30:
raise BadRequest('The `limit` parameter must be between 1 and 30.')
flights = flights.limit(limit)
# Produce JS response
callback = request.values.get('callback', 'onFlightsLoaded')
return wrap(callback, render_template('widgets/flights.jinja', flights=flights))
def _get_takeoff_locations():
return Location.get_clustered_locations(Flight.takeoff_location_wkt,
filter=and_(
Flight.pilot == g.user,
Flight.is_rankable()))
def index(page=None, id=None):
club = None
pilot = None
airport = None
query = db.session.query(
Flight.year.label('year'),
func.count('*').label('flights'),
func.count(distinct(Flight.pilot_id)).label('pilots'),
func.sum(Flight.olc_classic_distance).label('distance'),
func.sum(Flight.duration).label('duration'))
pilots_query = db.session.query(func.count(distinct(Flight.pilot_id)))
if page == 'pilot':
pilot = get_requested_record(User, id)
query = query.filter(Flight.pilot_id == pilot.id)
elif page == 'club':
club = get_requested_record(Club, id)
query = query.filter(Flight.club_id == club.id)
pilots_query = pilots_query.filter(Flight.club_id == club.id)
elif page == 'airport':
airport = get_requested_record(Airport, id)
query = query.filter(Flight.takeoff_airport_id == airport.id)
pilots_query = pilots_query.filter(
Flight.takeoff_airport_id == airport.id)
elif page is not None:
abort(404)
query = query.filter(Flight.is_rankable())
query = query.group_by(Flight.year).order_by(Flight.year.desc())
max_flights = 1
max_pilots = 1
max_distance = 1
max_duration = 1
sum_flights = 0
sum_distance = 0
sum_duration = 0
if page == 'pilot':
sum_pilots = 0
else:
sum_pilots = pilots_query.scalar()
list = []
for row in query:
row.average_distance = row.distance / row.flights
row.average_duration = row.duration / row.flights
list.append(row)
max_flights = max(max_flights, row.flights)
max_pilots = max(max_pilots, row.pilots)
max_distance = max(max_distance, row.distance)
max_duration = max(max_duration, row.duration.total_seconds())
sum_flights = sum_flights + row.flights
sum_distance = sum_distance + row.distance
sum_duration = sum_duration + row.duration.total_seconds()
return render_template('statistics/years.jinja',
years=list,
max_flights=max_flights,
max_pilots=max_pilots,
max_distance=max_distance,
max_duration=max_duration,
sum_flights=sum_flights,
sum_distance=sum_distance,
sum_duration=sum_duration,
sum_pilots=sum_pilots,
airport=airport,
pilot=pilot,
club=club)
def index(page=None, id=None):
club = None
pilot = None
airport = None
query = db.session.query(Flight.year.label('year'),
func.count('*').label('flights'),
func.count(distinct(Flight.pilot_id)).label('pilots'),
func.sum(Flight.olc_classic_distance).label('distance'),
func.sum(Flight.duration).label('duration'))
pilots_query = db.session.query(func.count(distinct(Flight.pilot_id)))
if page == 'pilot':
pilot = get_requested_record(User, id)
query = query.filter(Flight.pilot_id == pilot.id)
elif page == 'club':
club = get_requested_record(Club, id)
query = query.filter(Flight.club_id == club.id)
pilots_query = pilots_query.filter(Flight.club_id == club.id)
elif page == 'airport':
airport = get_requested_record(Airport, id)
query = query.filter(Flight.takeoff_airport_id == airport.id)
pilots_query = pilots_query.filter(Flight.takeoff_airport_id == airport.id)
elif page is not None:
abort(404)
query = query.filter(Flight.is_rankable())
query = query.group_by(Flight.year).order_by(Flight.year.desc())
max_flights = 1
max_pilots = 1
max_distance = 1
max_duration = 1
sum_flights = 0
sum_distance = 0
sum_duration = 0
if page == 'pilot':
sum_pilots = 0
else:
sum_pilots = pilots_query.scalar()
list = []
for row in query:
row.average_distance = row.distance / row.flights
row.average_duration = row.duration / row.flights
list.append(row)
max_flights = max(max_flights, row.flights)
max_pilots = max(max_pilots, row.pilots)
max_distance = max(max_distance, row.distance)
max_duration = max(max_duration, row.duration.total_seconds())
sum_flights = sum_flights + row.flights
sum_distance = sum_distance + row.distance
sum_duration = sum_duration + row.duration.total_seconds()
return render_template('statistics/years.jinja',
years=list,
max_flights=max_flights,
max_pilots=max_pilots,
max_distance=max_distance,
max_duration=max_duration,
sum_flights=sum_flights,
sum_distance=sum_distance,
sum_duration=sum_duration,
sum_pilots=sum_pilots,
airport=airport,
pilot=pilot,
club=club)
def _get_takeoff_locations():
return Location.get_clustered_locations(
Flight.takeoff_location_wkt,
filter=and_(Flight.pilot == g.user, Flight.is_rankable()))
def _largest_flight(user, schema):
flight = user.get_largest_flights().filter(Flight.is_rankable()).first()
if flight:
return schema.dump(flight).data