def get(self, challenge_slug=None, user_id=None): from maproulette.models import HistoricalMetrics as HM start = None end = None from dateutil import parser as dateparser from datetime import datetime parser = reqparse.RequestParser() parser.add_argument('start', type=str, help='start datetime yyyymmddhhmm') parser.add_argument('end', type=str, help='end datetime yyyymmddhhmm') args = parser.parse_args() stats_query = db.session.query( HM.timestamp, HM.status, func.sum(HM.count)) if challenge_slug is not None: stats_query = stats_query.filter(HM.challenge_slug == challenge_slug) if user_id is not None: stats_query = stats_query.filter(HM.user_id == user_id) stats_query = stats_query.group_by( HM.timestamp, HM.status).order_by( HM.status) # time slicing filters if args['start'] is not None: start = dateparser.parse(args['start']) if args['end'] is None: end = datetime.utcnow() else: end = dateparser.parse(args['end']) stats_query = stats_query.filter( Action.timestamp.between(start, end)) app.logger.debug(stats_query) return as_stats_dict( stats_query.all(), order=[1, 0, 2], start=start, end=end)
def get(self, challenge_slug=None, user_id=None): from maproulette.models import HistoricalMetrics as HM start = None end = None from dateutil import parser as dateparser from datetime import datetime parser = reqparse.RequestParser() parser.add_argument('start', type=str, help='start datetime yyyymmddhhmm') parser.add_argument('end', type=str, help='end datetime yyyymmddhhmm') args = parser.parse_args() stats_query = db.session.query( HM.timestamp, HM.status, func.sum(HM.count)) if challenge_slug is not None: stats_query = stats_query.filter(HM.challenge_slug == challenge_slug) if user_id is not None: stats_query = stats_query.filter(HM.user_id == user_id) stats_query = stats_query.group_by( HM.timestamp, HM.status).order_by( HM.status) # time slicing filters if args['start'] is not None: start = dateparser.parse(args['start']) if args['end'] is None: end = datetime.utcnow() else: end = dateparser.parse(args['end']) stats_query = stats_query.filter( Action.timestamp.between(start, end)) return as_stats_dict( stats_query.all(), order=[1, 0, 2], start=start, end=end)
def get(self, challenge_slug): start = None end = None from dateutil import parser as dateparser from datetime import datetime parser = reqparse.RequestParser() parser.add_argument('start', type=str, help='start datetime yyyymmddhhmm') parser.add_argument('end', type=str, help='end datetime yyyymmddhhmm') args = parser.parse_args() query = db.session.query( func.date_trunc('day', Action.timestamp).label('day'), Action.status, func.count(Action.id)).join(Task).filter_by( challenge_slug=challenge_slug).group_by( 'day', Action.status) # time slicing filters if args['start'] is not None: start = dateparser.parse(args['start']) if args['end'] is None: end = datetime.utcnow() else: end = dateparser.parse(args['end']) query = query.filter( Action.timestamp.between(start, end)) return as_stats_dict( query.all(), order=[1, 0, 2], start=start, end=end)
def get(self, challenge_slug=None, user_id=None): from dateutil import parser as dateparser from datetime import datetime from maproulette.models import AggregateMetrics start = None end = None parser = reqparse.RequestParser() parser.add_argument('start', type=str, help='start datetime yyyymmddhhmm') parser.add_argument('end', type=str, help='end datetime yyyymmddhhmm') args = parser.parse_args() breakdown = False select_fields = [ AggregateMetrics.status, func.sum(AggregateMetrics.count)] group_fields = [ AggregateMetrics.status] if request.path.endswith('/users'): select_fields.insert(0, AggregateMetrics.user_name) group_fields.insert(0, AggregateMetrics.user_name) breakdown = True elif request.path.endswith('/challenges'): select_fields.insert(0, AggregateMetrics.challenge_slug) group_fields.insert(0, AggregateMetrics.challenge_slug) breakdown = True stats_query = db.session.query( *select_fields).group_by( *group_fields) # stats for a specific challenge if challenge_slug is not None: stats_query = stats_query.filter_by( challenge_slug=challenge_slug) # stats for a specific user if user_id is not None: stats_query = stats_query.filter_by( user_id=user_id) # time slicing filters if args['start'] is not None: start = dateparser.parse(args['start']) if args['end'] is None: end = datetime.utcnow() else: end = dateparser.parse(args['end']) stats_query = stats_query.filter( AggregateMetrics.timestamp.between(start, end)) if breakdown: # if this is a breakdown by a secondary variable, the # query will have returned three columns and we need to # build a nested dictionary. return as_stats_dict(stats_query.all(), start=start, end=end) else: return dict(stats_query.all())
def get(self, challenge_slug=None, user_id=None): from dateutil import parser as dateparser from datetime import datetime start = None end = None parser = reqparse.RequestParser() parser.add_argument('start', type=str, help='start datetime yyyymmddhhmm') parser.add_argument('end', type=str, help='end datetime yyyymmddhhmm') args = parser.parse_args() breakdown = None # base CTE and query # the base CTE gets the set of latest actions for any task latest_cte = db.session.query( Action.id, Action.task_id, Action.timestamp, Action.user_id, Action.status, Task.challenge_slug, User.display_name).join( Task).outerjoin(User).distinct( Action.task_id).order_by( Action.task_id, Action.id.desc()).cte(name='latest') # the base query gets a count on the base CTE grouped by status, # optionally broken down by users or challenges if request.path.endswith('/users'): breakdown = 'users' stats_query = db.session.query( latest_cte.c.display_name, latest_cte.c.status, func.count(latest_cte.c.id)).group_by( latest_cte.c.status, latest_cte.c.display_name) elif request.path.endswith('/challenges'): breakdown = 'challenges' stats_query = db.session.query( latest_cte.c.challenge_slug, latest_cte.c.status, func.count(latest_cte.c.id)).group_by( latest_cte.c.status, latest_cte.c.challenge_slug) else: stats_query = db.session.query( latest_cte.c.status, func.count(latest_cte.c.id)).group_by( latest_cte.c.status) # stats for a specific challenge if challenge_slug is not None: stats_query = stats_query.filter( latest_cte.c.challenge_slug == challenge_slug) # stats for a specific user if user_id is not None: stats_query = stats_query.filter( latest_cte.c.user_id == user_id) # time slicing filters if args['start'] is not None: start = dateparser.parse(args['start']) if args['end'] is None: end = datetime.utcnow() else: end = dateparser.parse(args['end']) stats_query = stats_query.filter( latest_cte.c.timestamp.between(start, end)) if breakdown is not None: # if this is a breakdown by a secondary variable, the # query will have returned three columns and we need to # build a nested dictionary. return as_stats_dict(stats_query.all(), start=start, end=end) else: return dict(stats_query.all())