def get_mapped_projects(user_id: int, preferred_locale: str) -> UserMappedProjectsDTO:
""" Get all projects a user has mapped on """
sql = '''select p.id, p.status, p.default_locale, count(t.mapped_by), count(t.validated_by), st_asgeojson(p.centroid),
st_asgeojson(p.geometry)
from projects p,
tasks t
where p.id in (select unnest(projects_mapped) from users where id = {0})
and p.id = t.project_id
and (t.mapped_by = {0} or t.mapped_by is null)
and (t.validated_by = {0} or t.validated_by is null)
GROUP BY p.id, p.status, p.centroid, p.geometry'''.format(user_id)
results = db.engine.execute(sql)
if results.rowcount == 0:
raise NotFound()
mapped_projects_dto = UserMappedProjectsDTO()
for row in results:
mapped_project = MappedProject()
mapped_project.project_id = row[0]
mapped_project.status = ProjectStatus(row[1]).name
mapped_project.tasks_mapped = row[3]
mapped_project.tasks_validated = row[4]
mapped_project.centroid = geojson.loads(row[5])
mapped_project.aoi = geojson.loads(row[6])
project_info = ProjectInfo.get_dto_for_locale(row[0], preferred_locale, row[2])
mapped_project.name = project_info.name
mapped_projects_dto.mapped_projects.append(mapped_project)
return mapped_projects_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 update(self, project_dto: ProjectDTO):
""" Updates project from DTO """
self.status = ProjectStatus[project_dto.project_status].value
self.priority = ProjectPriority[project_dto.project_priority].value
self.default_locale = project_dto.default_locale
self.enforce_mapper_level = project_dto.enforce_mapper_level
self.enforce_validator_role = project_dto.enforce_validator_role
self.private = project_dto.private
self.mapper_level = MappingLevel[project_dto.mapper_level.upper()].value
self.entities_to_map = project_dto.entities_to_map
self.changeset_comment = project_dto.changeset_comment
self.due_date = project_dto.due_date
self.imagery = project_dto.imagery
self.josm_preset = project_dto.josm_preset
self.last_updated = timestamp()
self.license_id = project_dto.license_id
if project_dto.organisation_tag:
org_tag = Tags.upsert_organistion_tag(project_dto.organisation_tag)
self.organisation_tag = org_tag
else:
self.organisation_tag = None # Set to none, for cases where a tag could have been removed
if project_dto.campaign_tag:
camp_tag = Tags.upsert_campaign_tag(project_dto.campaign_tag)
self.campaign_tag = camp_tag
else:
self.campaign_tag = None # Set to none, for cases where a tag could have been removed
# Cast MappingType strings to int array
type_array = []
for mapping_type in project_dto.mapping_types:
type_array.append(MappingTypes[mapping_type].value)
self.mapping_types = type_array
# Add list of allowed users, meaning the project can only be mapped by users in this list
if hasattr(project_dto, 'allowed_users'):
self.allowed_users = [] # Clear existing relationships then re-insert
for user in project_dto.allowed_users:
self.allowed_users.append(user)
# Set Project Info for all returned locales
for dto in project_dto.project_info_locales:
project_info = self.project_info.filter_by(locale=dto.locale).one_or_none()
if project_info is None:
new_info = ProjectInfo.create_from_dto(dto) # Can't find info so must be new locale
self.project_info.append(new_info)
else:
project_info.update_from_dto(dto)
self.priority_areas = [] # Always clear Priority Area prior to updating
if project_dto.priority_areas:
for priority_area in project_dto.priority_areas:
pa = PriorityArea.from_dict(priority_area)
self.priority_areas.append(pa)
db.session.commit()
def as_dto_for_mapping(self, locale: str) -> Optional[ProjectDTO]:
""" Creates a Project DTO suitable for transmitting to mapper users """
project, project_dto = self._get_project_and_base_dto(self.id)
project_dto.tasks = Task.get_tasks_as_geojson_feature_collection(self.id)
project_dto.project_info = ProjectInfo.get_dto_for_locale(self.id, locale, project.default_locale)
return project_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 as_dto_for_admin(self, project_id):
""" Creates a Project DTO suitable for transmitting to project admins """
project, project_dto = self._get_project_and_base_dto()
if project is None:
return None
project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales(project_id)
return project_dto
def create_draft_project(self, draft_project_dto: DraftProjectDTO):
"""
Creates a draft project
:param draft_project_dto: DTO containing draft project details
:param aoi: Area of Interest for the project (eg boundary of project)
"""
self.project_info.append(ProjectInfo.create_from_name(draft_project_dto.project_name))
self.status = ProjectStatus.DRAFT.value
self.author_id = draft_project_dto.user_id
self.last_updated = timestamp()
def get_mapped_projects(user_id: int,
preferred_locale: str) -> UserMappedProjectsDTO:
""" Get all projects a user has mapped on """
# This query looks scary, but we're really just creating an outer join between the query that gets the
# counts of all mapped tasks and the query that gets counts of all validated tasks. This is necessary to
# handle cases where users have only validated tasks on a project, or only mapped on a project.
sql = '''SELECT p.id,
p.status,
p.default_locale,
c.mapped,
c.validated,
st_asgeojson(p.centroid)
FROM projects p,
(SELECT coalesce(v.project_id, m.project_id) project_id,
coalesce(v.validated, 0) validated,
coalesce(m.mapped, 0) mapped
FROM (SELECT t.project_id,
count (t.validated_by) validated
FROM tasks t
WHERE t.project_id IN (SELECT unnest(projects_mapped) FROM users WHERE id = {0})
AND t.validated_by = {0}
GROUP BY t.project_id, t.validated_by) v
FULL OUTER JOIN
(SELECT t.project_id,
count(t.mapped_by) mapped
FROM tasks t
WHERE t.project_id IN (SELECT unnest(projects_mapped) FROM users WHERE id = {0})
AND t.mapped_by = {0}
GROUP BY t.project_id, t.mapped_by) m
ON v.project_id = m.project_id) c
WHERE p.id = c.project_id ORDER BY p.id DESC'''.format(
user_id)
results = db.engine.execute(sql)
if results.rowcount == 0:
raise NotFound()
mapped_projects_dto = UserMappedProjectsDTO()
for row in results:
mapped_project = MappedProject()
mapped_project.project_id = row[0]
mapped_project.status = ProjectStatus(row[1]).name
mapped_project.tasks_mapped = row[3]
mapped_project.tasks_validated = row[4]
mapped_project.centroid = geojson.loads(row[5])
project_info = ProjectInfo.get_dto_for_locale(
row[0], preferred_locale, row[2])
mapped_project.name = project_info.name
mapped_projects_dto.mapped_projects.append(mapped_project)
return mapped_projects_dto
def create_draft_project(self, draft_project_dto: DraftProjectDTO, aoi: AreaOfInterest):
"""
Creates a draft project
:param draft_project_dto: DTO containing draft project details
:param aoi: Area of Interest for the project (eg boundary of project)
"""
self.project_info.append(ProjectInfo.create_from_name(draft_project_dto.project_name))
self.area_of_interest = aoi
self.status = ProjectStatus.DRAFT.value
self.author_id = draft_project_dto.user_id
self.last_updated = timestamp()
self.changeset_comment = current_app.config['DEFAULT_CHANGESET_COMMENT']
def get_project_summary(project, preferred_locale) -> ProjectSummary:
""" Create Project Summary model for postgis project object"""
pm_project = ProjectSummary()
pm_project.project_id = project.id
pm_project.campaign_tag = project.campaign_tag
pm_project.created = project.created
pm_project.last_updated = project.last_updated
pm_project.aoi_centroid = geojson.loads(project.geojson)
pm_project.percent_mapped = round((project.tasks_mapped / (project.total_tasks - project.tasks_bad_imagery)) * 100, 0)
pm_project.percent_validated = round(((project.tasks_validated + project.tasks_bad_imagery) / project.total_tasks) * 100, 0)
project_info = ProjectInfo.get_dto_for_locale(project.id, preferred_locale, project.default_locale)
pm_project.name = project_info.name
return pm_project
def get_user_invalidated_tasks(as_validator,
username: str,
preferred_locale: str,
closed=None,
project_id=None,
page=1,
page_size=10,
sort_by="updated_date",
sort_direction="desc") -> InvalidatedTasks:
""" Get invalidated tasks either mapped or invalidated by the user """
user = UserService.get_user_by_username(username)
query = TaskInvalidationHistory.query.filter_by(invalidator_id=user.id) if as_validator else \
TaskInvalidationHistory.query.filter_by(mapper_id=user.id)
if closed is not None:
query = query.filter_by(is_closed=closed)
if project_id is not None:
query = query.filter_by(project_id=project_id)
results = query.order_by(sort_by + " " + sort_direction).paginate(
page, page_size, True)
project_names = {}
invalidated_tasks_dto = InvalidatedTasks()
for entry in results.items:
dto = InvalidatedTask()
dto.task_id = entry.task_id
dto.project_id = entry.project_id
dto.history_id = entry.invalidation_history_id
dto.closed = entry.is_closed
dto.updated_date = entry.updated_date
if not dto.project_id in project_names:
project_names[dto.project_id] = ProjectInfo.get_dto_for_locale(
dto.project_id, preferred_locale).name
dto.project_name = project_names[dto.project_id]
invalidated_tasks_dto.invalidated_tasks.append(dto)
invalidated_tasks_dto.pagination = Pagination(results)
return invalidated_tasks_dto
def get_project_title(self, preferred_locale):
project_info = ProjectInfo.get_dto_for_locale(self.id,
preferred_locale,
self.default_locale)
return project_info.name
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
def update(self, project_dto: ProjectDTO):
""" Updates project from DTO """
self.status = ProjectStatus[project_dto.project_status].value
self.priority = ProjectPriority[project_dto.project_priority].value
self.default_locale = project_dto.default_locale
self.enforce_mapper_level = project_dto.enforce_mapper_level
self.enforce_validator_role = project_dto.enforce_validator_role
self.enforce_random_task_selection = project_dto.enforce_random_task_selection
self.allow_non_beginners = project_dto.allow_non_beginners
self.private = project_dto.private
self.mapper_level = MappingLevel[
project_dto.mapper_level.upper()].value
self.entities_to_map = project_dto.entities_to_map
self.changeset_comment = project_dto.changeset_comment
self.due_date = project_dto.due_date
self.imagery = project_dto.imagery
self.josm_preset = project_dto.josm_preset
self.id_presets = project_dto.id_presets
self.last_updated = timestamp()
self.license_id = project_dto.license_id
if project_dto.osmcha_filter_id:
# Support simple extraction of OSMCha filter id from OSMCha URL
match = re.search('aoi=([\w-]+)', project_dto.osmcha_filter_id)
self.osmcha_filter_id = match.group(
1) if match else project_dto.osmcha_filter_id
else:
self.osmcha_filter_id = None
if project_dto.organisation_tag:
org_tag = Tags.upsert_organistion_tag(project_dto.organisation_tag)
self.organisation_tag = org_tag
else:
self.organisation_tag = None # Set to none, for cases where a tag could have been removed
if project_dto.campaign_tag:
camp_tag = Tags.upsert_campaign_tag(project_dto.campaign_tag)
self.campaign_tag = camp_tag
else:
self.campaign_tag = None # Set to none, for cases where a tag could have been removed
# Cast MappingType strings to int array
type_array = []
for mapping_type in project_dto.mapping_types:
type_array.append(MappingTypes[mapping_type].value)
self.mapping_types = type_array
# Cast Editor strings to int array
mapping_editors_array = []
for mapping_editor in project_dto.mapping_editors:
mapping_editors_array.append(Editors[mapping_editor].value)
self.mapping_editors = mapping_editors_array
validation_editors_array = []
for validation_editor in project_dto.validation_editors:
validation_editors_array.append(Editors[validation_editor].value)
self.validation_editors = validation_editors_array
# Add list of allowed users, meaning the project can only be mapped by users in this list
if hasattr(project_dto, 'allowed_users'):
self.allowed_users = [
] # Clear existing relationships then re-insert
for user in project_dto.allowed_users:
self.allowed_users.append(user)
# Set Project Info for all returned locales
for dto in project_dto.project_info_locales:
project_info = self.project_info.filter_by(
locale=dto.locale).one_or_none()
if project_info is None:
new_info = ProjectInfo.create_from_dto(
dto) # Can't find info so must be new locale
self.project_info.append(new_info)
else:
project_info.update_from_dto(dto)
self.priority_areas = [
] # Always clear Priority Area prior to updating
if project_dto.priority_areas:
for priority_area in project_dto.priority_areas:
pa = PriorityArea.from_dict(priority_area)
self.priority_areas.append(pa)
if project_dto.custom_editor:
if not self.custom_editor:
new_editor = CustomEditor.create_from_dto(
self.id, project_dto.custom_editor)
self.custom_editor = new_editor
else:
self.custom_editor.update_editor(project_dto.custom_editor)
else:
if self.custom_editor:
self.custom_editor.delete()
db.session.commit()