def get_mapped_tasks_by_user(project_id: int):
""" Gets all mapped tasks for supplied project grouped by user"""
# Raw SQL is easier to understand that SQL alchemy here :)
sql = """select u.username, u.mapping_level, count(distinct(t.id)), json_agg(distinct(t.id)),
max(th.action_date) last_seen, u.date_registered, u.last_validation_date
from tasks t,
task_history th,
users u
where t.project_id = th.project_id
and t.id = th.task_id
and t.mapped_by = u.id
and t.project_id = {0}
and t.task_status = 2
and th.action_text = 'MAPPED'
group by u.username, u.mapping_level, u.date_registered, u.last_validation_date""".format(project_id)
results = db.engine.execute(sql)
if results.rowcount == 0:
raise NotFound()
mapped_tasks_dto = MappedTasks()
for row in results:
user_mapped = MappedTasksByUser()
user_mapped.username = row[0]
user_mapped.mapping_level = MappingLevel(row[1]).name
user_mapped.mapped_task_count = row[2]
user_mapped.tasks_mapped = row[3]
user_mapped.last_seen = row[4]
user_mapped.date_registered = row[5]
user_mapped.last_validation_date = row[6]
mapped_tasks_dto.mapped_tasks.append(user_mapped)
return mapped_tasks_dto
def get_project_summary(self, preferred_locale) -> ProjectSummary:
""" Create Project Summary model for postgis project object"""
summary = ProjectSummary()
summary.project_id = self.id
summary.campaign_tag = self.campaign_tag
summary.created = self.created
summary.last_updated = self.last_updated
summary.mapper_level = MappingLevel(self.mapper_level).name
summary.organisation_tag = self.organisation_tag
summary.status = ProjectStatus(self.status).name
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
summary.aoi_centroid = geojson.loads(centroid_geojson)
summary.percent_mapped = int(
(self.tasks_mapped /
(self.total_tasks - self.tasks_bad_imagery)) * 100)
summary.percent_validated = int(
((self.tasks_validated + self.tasks_bad_imagery) /
self.total_tasks) * 100)
project_info = ProjectInfo.get_dto_for_locale(self.id,
preferred_locale,
self.default_locale)
summary.name = project_info.name
summary.short_description = project_info.short_description
return summary
def get_all_users(query: UserSearchQuery) -> UserSearchDTO:
""" Search and filter all users """
# Base query that applies to all searches
base = db.session.query(User.id, User.username, User.mapping_level,
User.role)
# Add filter to query as required
if query.mapping_level:
base = base.filter(User.mapping_level == MappingLevel[
query.mapping_level.upper()].value)
if query.username:
base = base.filter(
User.username.ilike(query.username.lower() + '%'))
if query.role:
base = base.filter(User.role == UserRole[query.role.upper()].value)
results = base.order_by(User.username).paginate(query.page, 20, True)
dto = UserSearchDTO()
for result in results.items:
listed_user = ListedUser()
listed_user.id = result.id
listed_user.mapping_level = MappingLevel(result.mapping_level).name
listed_user.username = result.username
listed_user.role = UserRole(result.role).name
dto.users.append(listed_user)
dto.pagination = Pagination(results)
return dto
def get_project_summary(self, preferred_locale) -> ProjectSummary:
""" Create Project Summary model for postgis project object"""
summary = ProjectSummary()
summary.project_id = self.id
priority = self.priority
if priority == 0:
summary.priority = 'URGENT'
elif priority == 1:
summary.priority = 'HIGH'
elif priority == 2:
summary.priority = 'MEDIUM'
else:
summary.priority = 'LOW'
summary.author = User().get_by_id(self.author_id).username
polygon = to_shape(self.geometry)
polygon_aea = transform(
partial(
pyproj.transform,
pyproj.Proj(init='EPSG:4326'),
pyproj.Proj(
proj='aea',
lat1=polygon.bounds[1],
lat2=polygon.bounds[3])),
polygon)
area = polygon_aea.area/1000000
summary.area = area
summary.campaign_tag = self.campaign_tag
summary.changeset_comment = self.changeset_comment
summary.created = self.created
summary.last_updated = self.last_updated
summary.due_date = self.due_date
summary.mapper_level = MappingLevel(self.mapper_level).name
summary.mapper_level_enforced = self.enforce_mapper_level
summary.validator_level_enforced = self.enforce_validator_role
summary.organisation_tag = self.organisation_tag
summary.status = ProjectStatus(self.status).name
summary.entities_to_map = self.entities_to_map
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
summary.aoi_centroid = geojson.loads(centroid_geojson)
summary.percent_mapped = Project.calculate_tasks_percent('mapped', self.total_tasks,
self.tasks_mapped, self.tasks_validated,
self.tasks_bad_imagery)
summary.percent_validated = Project.calculate_tasks_percent('validated', self.total_tasks,
self.tasks_mapped, self.tasks_validated,
self.tasks_bad_imagery)
summary.percent_bad_imagery = Project.calculate_tasks_percent('bad_imagery', self.total_tasks,
self.tasks_mapped, self.tasks_validated,
self.tasks_bad_imagery)
project_info = ProjectInfo.get_dto_for_locale(self.id, preferred_locale, self.default_locale)
summary.name = project_info.name
summary.short_description = project_info.short_description
return summary
def search_projects(
search_dto: ProjectSearchDTO) -> ProjectSearchResultsDTO:
""" Searches all projects for matches to the criteria provided by the user """
all_results, paginated_results = ProjectSearchService._filter_projects(
search_dto)
if paginated_results.total == 0:
raise NotFound()
features = []
for project in all_results:
# This loop creates a geojson feature collection so you can see all active projects on the map
properties = {
"projectId": project.id,
"priority": ProjectPriority(project.priority).name
}
centroid = project.centroid
feature = geojson.Feature(geometry=geojson.loads(project.centroid),
properties=properties)
features.append(feature)
feature_collection = geojson.FeatureCollection(features)
dto = ProjectSearchResultsDTO()
dto.map_results = feature_collection
for project in paginated_results.items:
# This loop loads the paginated text results
# TODO would be nice to get this for an array rather than individually would be more efficient
project_info_dto = ProjectInfo.get_dto_for_locale(
project.id, search_dto.preferred_locale,
project.default_locale)
list_dto = ListSearchResultDTO()
list_dto.project_id = project.id
list_dto.locale = project_info_dto.locale
list_dto.name = project_info_dto.name
list_dto.priority = ProjectPriority(project.priority).name
list_dto.mapper_level = MappingLevel(project.mapper_level).name
list_dto.short_description = project_info_dto.short_description
list_dto.organisation_tag = project.organisation_tag
list_dto.campaign_tag = project.campaign_tag
list_dto.percent_mapped = Project.calculate_tasks_percent(
'mapped', project.total_tasks, project.tasks_mapped,
project.tasks_validated, project.tasks_bad_imagery)
list_dto.percent_validated = Project.calculate_tasks_percent(
'validated', project.total_tasks, project.tasks_mapped,
project.tasks_validated, project.tasks_bad_imagery)
list_dto.status = ProjectStatus(project.status).name
list_dto.active_mappers = Project.get_active_mappers(project.id)
dto.results.append(list_dto)
dto.pagination = Pagination(paginated_results)
return dto
def as_dto(self, logged_in_username: str) -> UserDTO:
""" Create DTO object from user in scope """
user_dto = UserDTO()
user_dto.id = self.id
user_dto.username = self.username
user_dto.role = UserRole(self.role).name
user_dto.mapping_level = MappingLevel(self.mapping_level).name
user_dto.is_expert = self.is_expert or False
user_dto.date_registered = str(self.date_registered)
try:
user_dto.projects_mapped = len(self.projects_mapped)
# Handle users that haven't touched a project yet.
except:
user_dto.projects_mapped = 0
user_dto.tasks_mapped = self.tasks_mapped
user_dto.tasks_validated = self.tasks_validated
user_dto.tasks_invalidated = self.tasks_invalidated
user_dto.twitter_id = self.twitter_id
user_dto.linkedin_id = self.linkedin_id
user_dto.facebook_id = self.facebook_id
user_dto.validation_message = self.validation_message
user_dto.total_time_spent = 0
user_dto.time_spent_mapping = 0
user_dto.time_spent_validating = 0
sql = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE (action='LOCKED_FOR_VALIDATION' or action='AUTO_UNLOCKED_FOR_VALIDATION')
and user_id = :user_id;"""
total_validation_time = db.engine.execute(text(sql), user_id=self.id)
for row in total_validation_time:
total_validation_time = row[0]
if total_validation_time:
total_validation_seconds = total_validation_time.total_seconds(
)
user_dto.time_spent_validating = total_validation_seconds
user_dto.total_time_spent += user_dto.time_spent_validating
sql = """SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE (action='LOCKED_FOR_MAPPING' or action='AUTO_UNLOCKED_FOR_MAPPING')
and user_id = :user_id;"""
total_mapping_time = db.engine.execute(text(sql), user_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()
user_dto.time_spent_mapping = total_mapping_seconds
user_dto.total_time_spent += user_dto.time_spent_mapping
if self.username == logged_in_username:
# Only return email address when logged in user is looking at their own profile
user_dto.email_address = self.email_address
user_dto.is_email_verified = self.is_email_verified
return user_dto
def _get_project_and_base_dto(self):
""" Populates a project DTO with properties common to all roles """
base_dto = ProjectDTO()
base_dto.project_id = self.id
base_dto.project_status = ProjectStatus(self.status).name
base_dto.default_locale = self.default_locale
base_dto.project_priority = ProjectPriority(self.priority).name
base_dto.area_of_interest = self.get_aoi_geometry_as_geojson()
base_dto.enforce_mapper_level = self.enforce_mapper_level
base_dto.enforce_validator_role = self.enforce_validator_role
base_dto.private = self.private
base_dto.mapper_level = MappingLevel(self.mapper_level).name
base_dto.entities_to_map = self.entities_to_map
base_dto.changeset_comment = self.changeset_comment
base_dto.due_date = self.due_date
base_dto.imagery = self.imagery
base_dto.josm_preset = self.josm_preset
base_dto.campaign_tag = self.campaign_tag
base_dto.organisation_tag = self.organisation_tag
base_dto.license_id = self.license_id
base_dto.created = self.created
base_dto.last_updated = self.last_updated
base_dto.id_custom_imagery = self.id_custom_imagery
base_dto.id_custom_presets = self.id_custom_presets
base_dto.id_min_editable_zoom = self.id_min_editable_zoom
base_dto.author = User().get_by_id(self.author_id).username
base_dto.active_mappers = Project.get_active_mappers(self.id)
base_dto.task_creation_mode = TaskCreationMode(
self.task_creation_mode).name
if self.private:
# If project is private it should have a list of allowed users
allowed_usernames = []
for user in self.allowed_users:
allowed_usernames.append(user.username)
base_dto.allowed_usernames = allowed_usernames
if self.mapping_types:
mapping_types = []
for mapping_type in self.mapping_types:
mapping_types.append(MappingTypes(mapping_type).name)
base_dto.mapping_types = mapping_types
if self.priority_areas:
geojson_areas = []
for priority_area in self.priority_areas:
geojson_areas.append(priority_area.get_as_geojson())
base_dto.priority_areas = geojson_areas
return self, base_dto
def _get_project_and_base_dto(self, project_id):
""" Populates a project DTO with properties common to all roles """
project = Project.get(project_id)
if project is None:
return None, None
aoi = project.area_of_interest
base_dto = ProjectDTO()
base_dto.project_id = project_id
base_dto.project_status = ProjectStatus(project.status).name
base_dto.default_locale = project.default_locale
base_dto.project_priority = ProjectPriority(project.priority).name
base_dto.area_of_interest = aoi.get_aoi_geometry_as_geojson()
base_dto.enforce_mapper_level = project.enforce_mapper_level
base_dto.enforce_validator_role = project.enforce_validator_role
base_dto.private = project.private
base_dto.mapper_level = MappingLevel(project.mapper_level).name
base_dto.entities_to_map = project.entities_to_map
base_dto.changeset_comment = project.changeset_comment
base_dto.due_date = project.due_date
base_dto.imagery = project.imagery
base_dto.josm_preset = project.josm_preset
base_dto.campaign_tag = project.campaign_tag
base_dto.organisation_tag = project.organisation_tag
base_dto.license_id = project.license_id
base_dto.last_updated = project.last_updated
base_dto.author = User().get_by_id(project.author_id).username
if project.private:
# If project is private it should have a list of allowed users
allowed_usernames = []
for user in project.allowed_users:
allowed_usernames.append(user.username)
base_dto.allowed_usernames = allowed_usernames
if project.mapping_types:
mapping_types = []
for mapping_type in project.mapping_types:
mapping_types.append(MappingTypes(mapping_type).name)
base_dto.mapping_types = mapping_types
if project.priority_areas:
geojson_areas = []
for priority_area in project.priority_areas:
geojson_areas.append(priority_area.get_as_geojson())
base_dto.priority_areas = geojson_areas
return project, base_dto
def as_dto(self, logged_in_username: str) -> UserDTO:
""" Create DTO object from user in scope """
user_dto = UserDTO()
user_dto.username = self.username
user_dto.role = UserRole(self.role).name
user_dto.mapping_level = MappingLevel(self.mapping_level).name
user_dto.tasks_mapped = self.tasks_mapped
user_dto.tasks_validated = self.tasks_validated
user_dto.twitter_id = self.twitter_id
user_dto.linkedin_id = self.linkedin_id
user_dto.facebook_id = self.facebook_id
if self.username == logged_in_username:
# Only return email address when logged in user is looking at their own profile
user_dto.email_address = self.email_address
user_dto.is_email_verified = self.is_email_verified
return user_dto
def search_projects(
search_dto: ProjectSearchDTO) -> ProjectSearchResultsDTO:
""" Searches all projects for matches to the criteria provided by the user """
filtered_projects = ProjectSearchService._filter_projects(search_dto)
if filtered_projects.total == 0:
raise NotFound()
dto = ProjectSearchResultsDTO()
for project in filtered_projects.items:
# TODO would be nice to get this for an array rather than individually would be more efficient
project_info_dto = ProjectInfo.get_dto_for_locale(
project.id, search_dto.preferred_locale,
project.default_locale)
result_dto = ProjectSearchResultDTO()
result_dto.project_id = project.id
result_dto.locale = project_info_dto.locale
result_dto.name = project_info_dto.name
result_dto.priority = ProjectPriority(project.priority).name
result_dto.mapper_level = MappingLevel(project.mapper_level).name
result_dto.short_description = project_info_dto.short_description
result_dto.aoi_centroid = geojson.loads(project.centroid)
result_dto.organisation_tag = project.organisation_tag
result_dto.campaign_tag = project.campaign_tag
result_dto.percent_mapped = round(
(project.tasks_mapped /
(project.total_tasks - project.tasks_bad_imagery)) * 100, 0)
result_dto.percent_validated = round(
((project.tasks_validated + project.tasks_bad_imagery) /
project.total_tasks) * 100, 0)
dto.results.append(result_dto)
dto.pagination = Pagination(filtered_projects)
return dto
def get_project_summary(self, preferred_locale) -> ProjectSummary:
""" Create Project Summary model for postgis project object"""
summary = ProjectSummary()
summary.project_id = self.id
priority = self.priority
if priority == 0:
summary.priority = 'URGENT'
elif priority == 1:
summary.priority = 'HIGH'
elif priority == 2:
summary.priority = 'MEDIUM'
else:
summary.priority = 'LOW'
summary.author = User().get_by_id(self.author_id).username
polygon = to_shape(self.geometry)
polygon_aea = transform(
partial(
pyproj.transform,
pyproj.Proj(init='EPSG:4326'),
pyproj.Proj(
proj='aea',
lat1=polygon.bounds[1],
lat2=polygon.bounds[3])),
polygon)
area = polygon_aea.area/1000000
summary.area = area
summary.campaign_tag = self.campaign_tag
summary.changeset_comment = self.changeset_comment
summary.created = self.created
summary.last_updated = self.last_updated
summary.due_date = self.due_date
summary.mapper_level = MappingLevel(self.mapper_level).name
summary.mapper_level_enforced = self.enforce_mapper_level
summary.validator_level_enforced = self.enforce_validator_role
summary.organisation_tag = self.organisation_tag
summary.status = ProjectStatus(self.status).name
summary.total_mappers = db.session.query(User).filter(User.projects_mapped.any(self.id)).count()
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()
summary.total_tasks = self.total_tasks
summary.total_comments = db.session.query(ProjectChat).filter(ProjectChat.project_id == self.id).count()
summary.entities_to_map = self.entities_to_map
centroid_geojson = db.session.scalar(self.centroid.ST_AsGeoJSON())
summary.aoi_centroid = geojson.loads(centroid_geojson)
summary.percent_mapped = int(((self.tasks_mapped + self.tasks_bad_imagery) / self.total_tasks) * 100)
summary.percent_validated = int((self.tasks_validated / self.total_tasks) * 100)
summary.percent_bad_imagery = int((self.tasks_bad_imagery / self.total_tasks) * 100)
project_info = ProjectInfo.get_dto_for_locale(self.id, preferred_locale, self.default_locale)
summary.name = project_info.name
summary.short_description = project_info.short_description
summary.total_time_spent = 0
summary.total_mapping_time = 0
summary.total_validation_time = 0
summary.average_mapping_time = 0
summary.average_validation_time = 0
sql = '''SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE action='LOCKED_FOR_MAPPING'and project_id = {0};'''.format(self.id)
total_mapping_time = db.engine.execute(sql)
for row in total_mapping_time:
total_mapping_time = row[0]
if total_mapping_time:
total_mapping_seconds = total_mapping_time.total_seconds()
summary.total_mapping_time = total_mapping_seconds
summary.total_time_spent += summary.total_mapping_time
if unique_mappers:
average_mapping_time = total_mapping_seconds/unique_mappers
summary.average_mapping_time = average_mapping_time
sql = '''SELECT SUM(TO_TIMESTAMP(action_text, 'HH24:MI:SS')::TIME) FROM task_history
WHERE action='LOCKED_FOR_VALIDATION' and project_id = {0};'''.format(self.id)
total_validation_time = db.engine.execute(sql)
for row in total_validation_time:
total_validation_time = row[0]
if total_validation_time:
total_validation_seconds = total_validation_time.total_seconds()
summary.total_validation_time = total_validation_seconds
summary.total_time_spent += summary.total_validation_time
if unique_validators:
average_validation_time = total_validation_seconds/unique_validators
summary.average_validation_time = average_validation_time
return summary