def get_project_user_stats(self, user_id: int) -> ProjectUserStatsDTO:
"""Compute project specific stats for a given user"""
stats_dto = ProjectUserStatsDTO()
stats_dto.time_spent_mapping = 0
stats_dto.time_spent_validating = 0
stats_dto.total_time_spent = 0
total_mapping_time = (
db.session.query(
func.sum(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)
)
)
.filter(
or_(
TaskHistory.action == "LOCKED_FOR_MAPPING",
TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING",
)
)
.filter(TaskHistory.user_id == user_id)
.filter(TaskHistory.project_id == self.id)
)
for time in total_mapping_time:
total_mapping_time = time[0]
if total_mapping_time:
stats_dto.time_spent_mapping = total_mapping_time.total_seconds()
stats_dto.total_time_spent += stats_dto.time_spent_mapping
query = (
TaskHistory.query.with_entities(
func.date_trunc("minute", TaskHistory.action_date).label("trn"),
func.max(TaskHistory.action_text).label("tm"),
)
.filter(TaskHistory.user_id == user_id)
.filter(TaskHistory.project_id == self.id)
.filter(TaskHistory.action == "LOCKED_FOR_VALIDATION")
.group_by("trn")
.subquery()
)
total_validation_time = db.session.query(
func.sum(cast(func.to_timestamp(query.c.tm, "HH24:MI:SS"), Time))
).all()
for time in total_validation_time:
total_validation_time = time[0]
if total_validation_time:
stats_dto.time_spent_validating = total_validation_time.total_seconds()
stats_dto.total_time_spent += stats_dto.time_spent_validating
return stats_dto
def round_time(time_expr, ceil=False):
round_func = func.ceil if ceil else func.trunc
step_epoch = func.extract('epoch', literal_column('arg_step'))
return cast(
func.to_timestamp(
round_func(func.extract('epoch', time_expr) / step_epoch) *
step_epoch), timestamptz)
def sqltracks(user):
''' Query to get track data from a give user '''
ast = session.query(func.to_char(
func.to_timestamp(Tracks.timestamp), 'HH24 DD-MM-YYYY').label('t')
).filter(Tracks.username==user).subquery('ast')
query = session.query(ast.c.t, func.count(ast.c.t).label('count')
).group_by(ast.c.t).order_by(ast.c.t)
return query
def extract_visits(places_location, label_id, restrict_to, attribute="label"):
if len(restrict_to) == 0:
return []
visits_10min = get_table("visits_10min", metadata)
# q = visits_10min.select([visits_10min.c.time_start]).where(extract('hour', visits_10min.c.time_start) > 10)
# connection.execute(q)
visits_with_places = places_location.join(
visits_10min,
onclause=and_(
places_location.c.userid == visits_10min.c.userid, places_location.c.placeid == visits_10min.c.placeid
),
)
if attribute == "label":
query = (
select([visits_with_places.c.places_location_latitude, visits_with_places.c.places_location_longitude])
.where(visits_with_places.c.places_location_place_label_int == label_id)
.where(visits_with_places.c.places_location_id.in_(restrict_to))
)
elif attribute == "time":
query = (
select([places_location.c.latitude, places_location.c.longitude])
.where(
and_(
extract("hour", func.to_timestamp(visits_10min.c.time_start)) >= (2 * label_id),
extract("hour", func.to_timestamp(visits_10min.c.time_end)) <= (2 * label_id + 1),
)
)
.where(places_location.c.id.in_(restrict_to))
.select_from(visits_with_places)
)
else:
demographics = get_table("demographics", metadata)
joined_t = demographics.join(
visits_with_places, visits_with_places.c.places_location_userid == demographics.c.userid
)
query = (
select([visits_with_places.c.places_location_latitude, visits_with_places.c.places_location_longitude])
.where(demographics.c[attribute] == label_id)
.where(places_location.c.id.in_(restrict_to))
.select_from(joined_t)
)
results = connection.execute(query).fetchall()
# print len(results)
return [(float(r[0]), float(r[1])) for r in results]
def sqltracks(user):
''' Query to get track data from a give user '''
ast = session.query(
func.to_char(func.to_timestamp(Tracks.timestamp),
'HH24 DD-MM-YYYY').label('t')).filter(
Tracks.username == user).subquery('ast')
query = session.query(ast.c.t,
func.count(ast.c.t).label('count')).group_by(
ast.c.t).order_by(ast.c.t)
return query
def extract_visits(places_location, label_id, restrict_to, attribute='label'):
if len(restrict_to) == 0:
return []
visits_10min = get_table("visits_10min", metadata)
#q = visits_10min.select([visits_10min.c.time_start]).where(extract('hour', visits_10min.c.time_start) > 10)
#connection.execute(q)
visits_with_places = places_location.join(
visits_10min,
onclause=and_(places_location.c.userid == visits_10min.c.userid,
places_location.c.placeid == visits_10min.c.placeid))
if attribute == 'label':
query = select([
visits_with_places.c.places_location_latitude,
visits_with_places.c.places_location_longitude
]).where(visits_with_places.c.places_location_place_label_int ==
label_id).where(
visits_with_places.c.places_location_id.in_(restrict_to))
elif attribute == 'time':
query = select([
places_location.c.latitude, places_location.c.longitude
]).where(
and_(
extract('hour', func.to_timestamp(
visits_10min.c.time_start)) >= (2 * label_id),
extract('hour', func.to_timestamp(visits_10min.c.time_end)) <=
(2 * label_id + 1))).where(
places_location.c.id.in_(restrict_to)).select_from(
visits_with_places)
else:
demographics = get_table('demographics', metadata)
joined_t = demographics.join(
visits_with_places, visits_with_places.c.places_location_userid ==
demographics.c.userid)
query = select([
visits_with_places.c.places_location_latitude,
visits_with_places.c.places_location_longitude
]).where(demographics.c[attribute] == label_id).where(
places_location.c.id.in_(restrict_to)).select_from(joined_t)
results = connection.execute(query).fetchall()
#print len(results)
return [(float(r[0]), float(r[1])) for r in results]
def sensord(id, start, end, aft_name):
functions = aft[aft_name]
table = inspect(DataTable).mapped_table
fields = list()
for agg_func in functions:
agg_func_name = str(agg_func()).replace('()', '')
fields.append(func.cast(agg_func(DataTable.value), types.Integer).label(agg_func_name))
per_seconds = (end - start).total_seconds() / 100
ots = func.to_timestamp(func.round(func.extract('epoch', DataTable.timestamp) / per_seconds) * per_seconds).label('timestamp')
if id == 0:
qry = g.session.query(ots, *fields) \
.filter(DataTable.probe == 1) # TODO: get probe 1
else:
qry = g.session.query(ots, *fields) \
.filter(DataTable.probe == id)
qry = qry.filter(table.c.timestamp >= start, table.c.timestamp <= end) \
.group_by(ots) \
.order_by(ots)
return qry
def getAssociation(self, server_url, handle=None):
query_args = {'server_url': server_url}
if handle is not None:
query_args['handle'] = handle
query = self.session.query(Association)
expires = func.to_timestamp(Association.issued + Association.lifetime)
query = query.filter(func.current_timestamp() < expires)
query = query.filter_by(**query_args)
query = query.order_by(Association.issued.desc())
association = query.first()
if association is None:
self.logger.debug('Association not found: %s', query_args)
return association
openid_association = OpenIDAssociation(
association.handle,
association.secret,
association.issued,
association.lifetime,
association.assoc_type,
)
self.logger.debug('Returning association: %s', openid_association)
return openid_association
def get_project_stats(self) -> ProjectStatsDTO:
""" Create Project Stats model for postgis project object"""
project_stats = ProjectStatsDTO()
project_stats.project_id = self.id
project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id"
project_area_result = db.engine.execute(text(project_area_sql),
id=self.id)
project_stats.area = project_area_result.fetchone()["area"]
project_stats.total_mappers = (db.session.query(User).filter(
User.projects_mapped.any(self.id)).count())
project_stats.total_tasks = self.total_tasks
project_stats.total_comments = (db.session.query(ProjectChat).filter(
ProjectChat.project_id == self.id).count())
project_stats.percent_mapped = Project.calculate_tasks_percent(
"mapped",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
project_stats.percent_validated = Project.calculate_tasks_percent(
"validated",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
project_stats.percent_bad_imagery = Project.calculate_tasks_percent(
"bad_imagery",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
project_stats.aoi_centroid = geojson.loads(centroid_geojson)
project_stats.total_time_spent = 0
project_stats.total_mapping_time = 0
project_stats.total_validation_time = 0
project_stats.average_mapping_time = 0
project_stats.average_validation_time = 0
total_mapping_time, total_mapping_tasks = (db.session.query(
func.sum(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"),
Time)),
func.count(TaskHistory.action),
).filter(
or_(
TaskHistory.action == "LOCKED_FOR_MAPPING",
TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING",
)).filter(TaskHistory.project_id == self.id).one())
if total_mapping_tasks > 0:
total_mapping_time = total_mapping_time.total_seconds()
project_stats.total_mapping_time = total_mapping_time
project_stats.average_mapping_time = (total_mapping_time /
total_mapping_tasks)
project_stats.total_time_spent += total_mapping_time
total_validation_time, total_validation_tasks = (db.session.query(
func.sum(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"),
Time)),
func.count(TaskHistory.action),
).filter(
or_(
TaskHistory.action == "LOCKED_FOR_VALIDATION",
TaskHistory.action == "AUTO_UNLOCKED_FOR_VALIDATION",
)).filter(TaskHistory.project_id == self.id).one())
if total_validation_tasks > 0:
total_validation_time = total_validation_time.total_seconds()
project_stats.total_validation_time = total_validation_time
project_stats.average_validation_time = (total_validation_time /
total_validation_tasks)
project_stats.total_time_spent += total_validation_time
actions = []
if project_stats.average_mapping_time <= 0:
actions.append(TaskStatus.LOCKED_FOR_MAPPING.name)
if project_stats.average_validation_time <= 0:
actions.append(TaskStatus.LOCKED_FOR_VALIDATION.name)
zoom_levels = []
# Check that averages are non-zero.
if len(actions) != 0:
zoom_levels = (Task.query.with_entities(
Task.zoom.distinct()).filter(Task.project_id == self.id).all())
zoom_levels = [z[0] for z in zoom_levels]
# Validate project has arbitrary tasks.
is_square = True
if None in zoom_levels:
is_square = False
sq = (TaskHistory.query.with_entities(
Task.zoom,
TaskHistory.action,
(cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"),
Time)).label("ts"),
).filter(Task.is_square == is_square).filter(
TaskHistory.project_id == Task.project_id).filter(
TaskHistory.task_id == Task.id).filter(
TaskHistory.action.in_(actions)))
if is_square is True:
sq = sq.filter(Task.zoom.in_(zoom_levels))
sq = sq.subquery()
nz = (db.session.query(sq.c.zoom, sq.c.action, sq.c.ts).filter(
sq.c.ts > datetime.time(0)).limit(10000).subquery())
if project_stats.average_mapping_time <= 0:
mapped_avg = (db.session.query(nz.c.zoom, (func.avg(
nz.c.ts)).label("avg")).filter(
nz.c.action ==
TaskStatus.LOCKED_FOR_MAPPING.name).group_by(
nz.c.zoom).all())
mapping_time = sum([t.avg.total_seconds()
for t in mapped_avg]) / len(mapped_avg)
project_stats.average_mapping_time = mapping_time
if project_stats.average_validation_time <= 0:
val_avg = (db.session.query(nz.c.zoom, (func.avg(
nz.c.ts)).label("avg")).filter(
nz.c.action ==
TaskStatus.LOCKED_FOR_VALIDATION.name).group_by(
nz.c.zoom).all())
validation_time = sum([t.avg.total_seconds()
for t in val_avg]) / len(val_avg)
project_stats.average_validation_time = validation_time
time_to_finish_mapping = (
self.total_tasks -
(self.tasks_mapped + self.tasks_bad_imagery +
self.tasks_validated)) * project_stats.average_mapping_time
project_stats.time_to_finish_mapping = time_to_finish_mapping
project_stats.time_to_finish_validating = (
self.total_tasks - (self.tasks_validated + self.tasks_bad_imagery)
) * project_stats.average_validation_time + time_to_finish_mapping
return project_stats
def get_detailed_stats(username: str):
user = UserService.get_user_by_username(username)
stats_dto = UserStatsDTO()
actions = [
TaskStatus.VALIDATED.name,
TaskStatus.INVALIDATED.name,
TaskStatus.MAPPED.name,
]
actions_table = (db.session.query(
literal(TaskStatus.VALIDATED.name).label("action_text")).union(
db.session.query(
literal(TaskStatus.INVALIDATED.name).label("action_text")),
db.session.query(
literal(TaskStatus.MAPPED.name).label("action_text")),
).subquery().alias("actions_table"))
# Get only rows with the given actions.
filtered_actions = (TaskHistory.query.with_entities(
TaskHistory.user_id,
TaskHistory.project_id,
TaskHistory.task_id,
TaskHistory.action_text,
).filter(TaskHistory.action_text.in_(actions)).subquery().alias(
"filtered_actions"))
user_tasks = (db.session.query(filtered_actions).filter(
filtered_actions.c.user_id == user.id).subquery().alias(
"user_tasks"))
others_tasks = (db.session.query(filtered_actions).filter(
filtered_actions.c.user_id != user.id
).filter(filtered_actions.c.task_id == user_tasks.c.task_id).filter(
filtered_actions.c.project_id == user_tasks.c.project_id).filter(
filtered_actions.c.action_text != TaskStatus.MAPPED.name).
subquery().alias("others_tasks"))
user_stats = (db.session.query(
actions_table.c.action_text,
func.count(user_tasks.c.action_text)).outerjoin(
user_tasks, actions_table.c.action_text ==
user_tasks.c.action_text).group_by(
actions_table.c.action_text))
others_stats = (db.session.query(
func.concat(actions_table.c.action_text, "_BY_OTHERS"),
func.count(others_tasks.c.action_text),
).outerjoin(others_tasks, actions_table.c.action_text ==
others_tasks.c.action_text).group_by(
actions_table.c.action_text))
res = user_stats.union(others_stats).all()
results = {key: value for key, value in res}
projects_mapped = UserService.get_projects_mapped(user.id)
stats_dto.tasks_mapped = results["MAPPED"]
stats_dto.tasks_validated = results["VALIDATED"]
stats_dto.tasks_invalidated = results["INVALIDATED"]
stats_dto.tasks_validated_by_others = results["VALIDATED_BY_OTHERS"]
stats_dto.tasks_invalidated_by_others = results[
"INVALIDATED_BY_OTHERS"]
stats_dto.projects_mapped = len(projects_mapped)
stats_dto.countries_contributed = UserService.get_countries_contributed(
user.id)
stats_dto.contributions_by_day = UserService.get_contributions_by_day(
user.id)
stats_dto.total_time_spent = 0
stats_dto.time_spent_mapping = 0
stats_dto.time_spent_validating = 0
query = (TaskHistory.query.with_entities(
func.date_trunc("minute", TaskHistory.action_date).label("trn"),
func.max(TaskHistory.action_text).label("tm"),
).filter(TaskHistory.user_id == user.id).filter(
TaskHistory.action == "LOCKED_FOR_VALIDATION").group_by(
"trn").subquery())
total_validation_time = db.session.query(
func.sum(cast(func.to_timestamp(query.c.tm, "HH24:MI:SS"),
Time))).scalar()
if total_validation_time:
stats_dto.time_spent_validating = total_validation_time.total_seconds(
)
stats_dto.total_time_spent += stats_dto.time_spent_validating
total_mapping_time = (db.session.query(
func.sum(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"),
Time))).filter(
or_(
TaskHistory.action ==
TaskAction.LOCKED_FOR_MAPPING.name,
TaskHistory.action ==
TaskAction.AUTO_UNLOCKED_FOR_MAPPING.name,
)).filter(TaskHistory.user_id == user.id).scalar())
if total_mapping_time:
stats_dto.time_spent_mapping = total_mapping_time.total_seconds()
stats_dto.total_time_spent += stats_dto.time_spent_mapping
stats_dto.contributions_interest = UserService.get_interests_stats(
user.id)
return stats_dto
def get_project_stats(self) -> ProjectStatsDTO:
""" Create Project Stats model for postgis project object"""
project_stats = ProjectStatsDTO()
project_stats.project_id = self.id
project_area_sql = "select ST_Area(geometry, true)/1000000 as area from public.projects where id = :id"
project_area_result = db.engine.execute(text(project_area_sql), id=self.id)
project_stats.area = project_area_result.fetchone()["area"]
project_stats.total_mappers = (
db.session.query(User).filter(User.projects_mapped.any(self.id)).count()
)
project_stats.total_tasks = self.total_tasks
project_stats.total_comments = (
db.session.query(ProjectChat)
.filter(ProjectChat.project_id == self.id)
.count()
)
project_stats.percent_mapped = Project.calculate_tasks_percent(
"mapped",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
project_stats.percent_validated = Project.calculate_tasks_percent(
"validated",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
project_stats.percent_bad_imagery = Project.calculate_tasks_percent(
"bad_imagery",
self.total_tasks,
self.tasks_mapped,
self.tasks_validated,
self.tasks_bad_imagery,
)
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
project_stats.aoi_centroid = geojson.loads(centroid_geojson)
unique_mappers = (
TaskHistory.query.filter(
TaskHistory.action == "LOCKED_FOR_MAPPING",
TaskHistory.project_id == self.id,
)
.distinct(TaskHistory.user_id)
.count()
)
unique_validators = (
TaskHistory.query.filter(
TaskHistory.action == "LOCKED_FOR_VALIDATION",
TaskHistory.project_id == self.id,
)
.distinct(TaskHistory.user_id)
.count()
)
project_stats.total_time_spent = 0
project_stats.total_mapping_time = 0
project_stats.total_validation_time = 0
project_stats.average_mapping_time = 0
project_stats.average_validation_time = 0
total_mapping_time = (
db.session.query(
func.sum(
cast(func.to_timestamp(TaskHistory.action_text, "HH24:MI:SS"), Time)
)
)
.filter(
or_(
TaskHistory.action == "LOCKED_FOR_MAPPING",
TaskHistory.action == "AUTO_UNLOCKED_FOR_MAPPING",
)
)
.filter(TaskHistory.project_id == self.id)
)
for row in total_mapping_time:
total_mapping_time = row[0]
if total_mapping_time:
total_mapping_seconds = total_mapping_time.total_seconds()
project_stats.total_mapping_time = total_mapping_seconds
project_stats.total_time_spent += project_stats.total_mapping_time
if unique_mappers:
average_mapping_time = total_mapping_seconds / unique_mappers
project_stats.average_mapping_time = average_mapping_time
query = (
TaskHistory.query.with_entities(
func.date_trunc("minute", TaskHistory.action_date).label("trn"),
func.max(TaskHistory.action_text).label("tm"),
)
.filter(TaskHistory.project_id == self.id)
.filter(TaskHistory.action == "LOCKED_FOR_VALIDATION")
.group_by("trn")
.subquery()
)
total_validation_time = db.session.query(
func.sum(cast(func.to_timestamp(query.c.tm, "HH24:MI:SS"), Time))
).all()
for row in total_validation_time:
total_validation_time = row[0]
if total_validation_time:
total_validation_seconds = total_validation_time.total_seconds()
project_stats.total_validation_time = total_validation_seconds
project_stats.total_time_spent += project_stats.total_validation_time
if unique_validators:
average_validation_time = (
total_validation_seconds / unique_validators
)
project_stats.average_validation_time = average_validation_time
return project_stats
def date_time_to_timestamp(self, t):
if self.engine == "mysql":
return t
if self.engine == "postgresql":
return func.to_timestamp(t, 'YYYY-MM-DD HH24:MI')
def date_compare(self, t):
if self.engine == "mysql":
return func.date_format(t, '%Y-%m-%d')
if self.engine == "postgresql":
return func.to_timestamp(func.to_char(t, 'YYYY-MM-DD'),
'YYYY-MM-DD')
def round_time(time_expr, ceil=False):
round_func = func.ceil if ceil else func.trunc
step_epoch = func.extract('epoch', literal_column('arg_step'))
return cast(func.to_timestamp(round_func(func.extract('epoch', time_expr) / step_epoch) * step_epoch), timestamptz)
def date_time_to_timestamp(self, t): if self.engine == "mysql": return t if self.engine == "postgresql": return func.to_timestamp(t, 'YYYY-MM-DD HH24:MI')
def date_compare(self, t): if self.engine == "mysql": return func.date_format(t, '%Y-%m-%d') if self.engine == "postgresql": return func.to_timestamp(func.to_char(t, 'YYYY-MM-DD'), 'YYYY-MM-DD')