Beispiel #1
0
    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
Beispiel #2
0
 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)
Beispiel #3
0
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
Beispiel #4
0
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]
Beispiel #5
0
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
Beispiel #6
0
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]
Beispiel #7
0
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
Beispiel #8
0
    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
Beispiel #11
0
    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
Beispiel #12
0
 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')
Beispiel #13
0
 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')
Beispiel #14
0
 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)
Beispiel #15
0
	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')
Beispiel #16
0
	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')