class Species(db.Model): __tablename__ = 'species' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(120), unique=True, nullable=False) homeworld = db.Column(db.String(120), nullable=False) quadrant = db.Column(db.String(120), nullable=False) warp_capable = db.Column(db.Boolean, nullable=False) sightings = db.Column(db.Integer, nullable=False) extinct = db.Column(db.Boolean, nullable=True) extra_galactic = db.Column(db.Boolean, nullable=True) humanoid = db.Column(db.Boolean, nullable=True) reptilian = db.Column(db.Boolean, nullable=True) non_corporeal = db.Column(db.Boolean, nullable=True) shape_shifting = db.Column(db.Boolean, nullable=True) spaceborne = db.Column(db.Boolean, nullable=True) telepathic = db.Column(db.Boolean, nullable=True) trans_dimentional = db.Column(db.Boolean, nullable=True) alternate_reality = db.Column(db.Boolean, nullable=True) def __repr__(self): return '<Species {}>'.format(self.name) def _serializable_keys(self): return [ 'id', 'name', 'homeworld', 'quadrant', 'warp_capable', 'sightings', 'extinct', 'extra_galactic', 'humanoid', 'reptilian', 'non_corporeal', 'shape_shifting', 'spaceborne', 'telepathic', 'trans_dimentional', 'alternate_reality' ] def serialize(self): return {key: self.__dict__[key] for key in self._serializable_keys()}
class SensorsGroup(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(140)) description = db.Column(db.String(1000))
class History(db.Model): id = db.Column(db.Integer, primary_key=True) sensor = db.Column(db.Integer, db.ForeignKey('sensor.id')) time = db.Column(db.DateTime, index=True, default=datetime.utcnow) value = db.Column(db.String(140))
class Compound(db.Model): id = db.Column(db.Integer, unique=True, primary_key=True) # Identifiers gsk_compound_num = db.Column(db.String(30), unique=True, nullable=True) qtx_compound_num = db.Column(db.String(20), unique=True, nullable=True) pubchem_compound_id = db.Column(db.String(30), unique=True, nullable=True) cas_num = db.Column(db.String(30), unique=True, nullable=True) # Properties for biologists drug_class = db.Column(db.String(120), nullable=True) analog_info = db.Column(db.String(120), nullable=True) screening_info = db.Column(db.String(120), nullable=True) # Properties for chemists molecular_weight = db.Column(db.Float, nullable=True) compound_class = db.Column(db.String(120), nullable=True) multimer_class = db.Column(db.String(30), nullable=True) dimerization_position = db.Column(db.String(30), nullable=True) p2_group = db.Column(db.String(20), nullable=True) p3_group = db.Column(db.String(20), nullable=True) p3_group_radical = db.Column(db.String(20), nullable=True) structure_reference_code = db.Column(db.String(30), nullable=True) smiles_string = db.Column(db.Text, nullable=True) # Other comments = db.Column(db.Text, nullable=True) date_added = db.Column(db.DateTime, nullable=True) # Relationships batches = db.relationship('Batch', backref='Compound', lazy=True) vials = db.relationship('Vial', backref='Compound', lazy=True) def get_compound_represenation(self): d = { 'GSK Compound #': self.gsk_compound_num, 'QTX Compound #': self.qtx_compound_num, 'CAS #': self.cas_num, 'PubChem ID': self.pubchem_compound_id } return [(k, v) for (k, v) in d.items() if v is not None] def __repr__(self): return f'<Compound({self.get_compound_represenation()})>'
class Vial(db.Model): id = db.Column(db.Integer, primary_key=True) qura_log_num = db.Column(db.Integer, autoincrement=True, nullable=False) compound_id = db.Column(db.Integer, db.ForeignKey('compound.id'), nullable=False) batch_id = db.Column(db.Integer, db.ForeignKey('batch.id'), nullable=False) # Vial Properties date_entered = db.Column(db.DateTime, default=datetime.now(), nullable=False) barcode = db.Column(db.String(50), nullable=True) owner_initials = db.Column(db.String(10), nullable=False) weighed_by = db.Column(db.String(50), nullable=True) qura_project_code = db.Column(db.String(30), nullable=True) gsk_project_code = db.Column(db.String(20), nullable=True) is_empty = db.Column(db.Boolean) # Material Properties concentration = db.Column(db.Float, nullable=True) concentration_units = db.relationship('Unit', lazy=True) weight = db.Column(db.Float, nullable=True) weight_units = db.relationship('Unit', lazy=True) vehicle = db.Column(db.String(50), nullable=True, default='DMSO') vehicle_volume = db.Column(db.Float, nullable=True) vehicle_volume_units = db.relationship('Unit', lazy=True) is_solid = db.Column(db.Boolean) # Other comment = db.Column(db.Text, nullable=True) # Relationships parent_vial_id = db.Column(db.Integer, db.ForeignKey('vial.id')) parent_vial = db.relationship('Vial', backref='vial_parent_vial', uselist=False, remote_side=[id], lazy=True) # Not critical but exists in original Excel log location = db.Column(db.String(50), nullable=True) balance = db.Column(db.String(50), nullable=True) gsk_log_num = db.Column(db.String(20), nullable=True) def __repr__(self): return f'<Vial({self.qura_log_num})>'
ST_SetSRID, ST_GeomFromGeoJSON, timestamp, ST_Centroid, NotFound, ST_X, ST_Y, ) from backend.services.grid.grid_service import GridService from backend.models.postgis.interests import Interest, project_interests # Secondary table defining many-to-many join for projects that were favorited by users. project_favorites = db.Table( "project_favorites", db.metadata, db.Column("project_id", db.Integer, db.ForeignKey("projects.id")), db.Column("user_id", db.BigInteger, db.ForeignKey("users.id")), ) # Secondary table defining many-to-many join for private projects that only defined users can map on project_allowed_users = db.Table( "project_allowed_users", db.metadata, db.Column("project_id", db.Integer, db.ForeignKey("projects.id")), db.Column("user_id", db.BigInteger, db.ForeignKey("users.id")), ) class ProjectTeams(db.Model): __tablename__ = "project_teams" team_id = db.Column(db.Integer,
class BaseMixin(object): id = db.Column(db.Integer, primary_key=True) def __repr__(self): return "<{} {!r}>".format(type(self).__name__, self.id)
class Image(db.Model): def __init__(self, data): self.id = uuid.uuid1() self.data = re.sub('^data:image/.+;base64,', '', data).decode('base64') id = db.Column(UUIDType(binary=False), primary_key=True) data = db.Column(db.LargeBinary, nullable=False)
class Performer(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) services = db.relationship('Service', secondary=performer_service, back_populates='performers', lazy='dynamic') phone = db.Column(db.String(80), nullable=True) photo_id = db.Column(UUIDType(binary=False), db.ForeignKey( 'image.id', onupdate="CASCADE", ondelete="SET NULL" ), nullable=True) description = db.Column(db.String(256)) business_id = db.Column(db.Integer, db.ForeignKey( 'business.id', onupdate="CASCADE", ondelete="CASCADE" ), nullable=False) work_beg = db.Column(db.Interval, nullable=False, default=timedelta(hours=9)) work_end = db.Column(db.Interval, nullable=False, default=timedelta(hours=18)) lunch_beg = db.Column(db.Interval, nullable=False, default=timedelta(hours=12)) lunch_end = db.Column(db.Interval, nullable=False, default=timedelta(hours=13)) non_working_days = db.Column( db.ARRAY(db.Integer), nullable=False, default=[]) business = db.relationship("Business", back_populates='performers') appointments = db.relationship('Appointment', back_populates='performer') def to_obj(self): return { 'id': self.id, 'name': self.name, 'phone': self.phone, 'photo': self.photo_id, 'description': self.description, 'services': list(map(lambda x: x.id, self.services)) } def get_working_hours(self): if not self.work_beg: return timedelta(hours=9), timedelta(hours=18) return self.work_beg, self.work_end def get_lunch_hours(self): if not self.lunch_beg: return timedelta(hours=12), timedelta(hours=13) return self.lunch_beg, self.lunch_end @staticmethod def get(performer_id): return db.session.query(Performer).get(performer_id)
class ConfigModel(db.Model): """ConfigModel keeps track of the trade configuration set in /trades""" __tablename__ = "Config" id = db.Column(db.Integer, primary_key=True) updated = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) bot = db.relationship("BotModel", back_populates="config") symbols = db.Column(MutableList.as_mutable(PickleType), default=[]) timeframe = db.Column(db.Text, default="1h") candle_interval = db.Column(db.Integer, default=100) wallet_amount = db.Column(db.Float, default=99) below_average = db.Column(db.Float, default=5) profit_margin = db.Column(db.Float, default=35) profit_as = db.Column(db.Text, default="USDT") spot = db.Column(db.Boolean, default=True) sandbox = db.Column(db.Boolean, default=True) # Futures expected_leverage = db.Column(db.Float, default=5) volume_timeframe = db.Column(db.Text, default="5m") total_volume = db.Column(db.Integer, default=30) margin_type = db.Column(db.Text, default="ISOLATED") in_green = db.Column(db.Float, default=0.2) in_red = db.Column(db.Float, default=2) take_profit = db.Column(db.Float, default=2) allow_multiple_orders = db.Column(db.Boolean, default=False) use_average = db.Column(db.Boolean, default=False) def update_data(self, data: dict): """"Just throw in a json object, each key that can be mapped will be updated" Args: data (dict): The data to update with """ for key, value in data.items(): try: getattr(self, key) setattr(self, key, value) except AttributeError: pass db.session.commit() def to_dict(self, blacklist: list = []): """Transforms a row object into a dictionary object Args: blacklist ([list]): [Columns you don't want to include in the dict] """ return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs if c.key not in blacklist } def remove_sandbox(self): from backend.models.orders import OrdersModel sandbox_items = OrdersModel.query.filter( OrdersModel.sandbox == True).delete() db.session.commit()
'name': self.name, 'address': self.address, 'phone': self.phone, 'performers': list(map(lambda x: x.to_obj(), self.performers)), 'services': list(map(lambda x: x.to_obj(), self.services)) } @staticmethod def get(user_id): return db.session.query(Business).filter(Business.user_id == user_id).first() performer_service = db.Table('performer_service', db.Column('performer_id', db.Integer, db.ForeignKey( 'performer.id', onupdate="CASCADE", ondelete="CASCADE" ), primary_key=True), db.Column('service_id', db.Integer, db.ForeignKey( 'service.id', onupdate="CASCADE", ondelete="CASCADE" ), primary_key=True) ) class Performer(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) services = db.relationship('Service', secondary=performer_service, back_populates='performers', lazy='dynamic')
class Team(db.Model): """ Describes a team """ __tablename__ = "teams" # Columns id = db.Column(db.Integer, primary_key=True) organisation_id = db.Column( db.Integer, db.ForeignKey("organisations.id", name="fk_organisations"), nullable=False, ) name = db.Column(db.String(512), nullable=False) logo = db.Column(db.String) # URL of a logo description = db.Column(db.String) invite_only = db.Column(db.Boolean, default=False, nullable=False) visibility = db.Column(db.Integer, default=TeamVisibility.PUBLIC.value, nullable=False) organisation = db.relationship(Organisation, backref="teams") def create(self): """ Creates and saves the current model to the DB """ db.session.add(self) db.session.commit() @classmethod def create_from_dto(cls, new_team_dto: NewTeamDTO): """ Creates a new team from a dto """ new_team = cls() new_team.name = new_team_dto.name new_team.description = new_team_dto.description new_team.invite_only = new_team_dto.invite_only new_team.visibility = TeamVisibility[new_team_dto.visibility].value org = Organisation.get(new_team_dto.organisation_id) new_team.organisation = org # Create team member with creator as a manager new_member = TeamMembers() new_member.team = new_team new_member.user_id = new_team_dto.creator new_member.function = TeamMemberFunctions.MANAGER.value new_member.active = True new_team.members.append(new_member) new_team.create() return new_team def update(self, team_dto: TeamDTO): """ Updates Team from DTO """ if team_dto.organisation: self.organisation = Organisation().get_organisation_by_name( team_dto.organisation) for attr, value in team_dto.items(): if attr == "visibility" and value is not None: value = TeamVisibility[team_dto.visibility].value if attr in ("members", "organisation"): continue try: is_field_nullable = self.__table__.columns[attr].nullable if is_field_nullable and value is not None: setattr(self, attr, value) elif value is not None: setattr(self, attr, value) except KeyError: continue if team_dto.members != self._get_team_members() and team_dto.members: for member in self.members: db.session.delete(member) for member in team_dto.members: user = User.get_by_username(member["userName"]) if user is None: raise NotFound("User not found") new_team_member = TeamMembers() new_team_member.team = self new_team_member.member = user new_team_member.function = TeamMemberFunctions[ member["function"]].value db.session.commit() def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() def can_be_deleted(self) -> bool: """ A Team can be deleted if it doesn't have any projects """ return len(self.projects) == 0 def get(team_id: int): """ Gets specified team by id :param team_id: team ID in scope :return: Team if found otherwise None """ return Team.query.get(team_id) def get_team_by_name(team_name: str): """ Gets specified team by name :param team_name: team name in scope :return: Team if found otherwise None """ return Team.query.filter_by(name=team_name).one_or_none() def as_dto(self): """ Returns a dto for the team """ team_dto = TeamDTO() team_dto.team_id = self.id team_dto.description = self.description team_dto.invite_only = self.invite_only team_dto.members = self._get_team_members() team_dto.name = self.name team_dto.organisation = self.organisation.name team_dto.organisation_id = self.organisation.id team_dto.logo = self.organisation.logo team_dto.visibility = TeamVisibility(self.visibility).name return team_dto def as_dto_inside_org(self): """ Returns a dto for the team """ team_dto = OrganisationTeamsDTO() team_dto.team_id = self.id team_dto.name = self.name team_dto.description = self.description team_dto.invite_only = self.invite_only team_dto.members = self._get_team_members() team_dto.visibility = TeamVisibility(self.visibility).name return team_dto def as_dto_team_member(self, member) -> TeamMembersDTO: """ Returns a dto for the team member""" member_dto = TeamMembersDTO() user = User.get_by_id(member.user_id) member_function = TeamMemberFunctions(member.function).name member_dto.username = user.username member_dto.function = member_function member_dto.picture_url = user.picture_url member_dto.active = member.active return member_dto def as_dto_team_project(self, project) -> TeamProjectDTO: """ Returns a dto for the team project """ project_team_dto = TeamProjectDTO() project_team_dto.project_name = project.name project_team_dto.project_id = project.project_id project_team_dto.role = TeamRoles(project.role).name return project_team_dto def _get_team_members(self): """ Helper to get JSON serialized members """ members = [] for mem in self.members: members.append({ "username": mem.member.username, "pictureUrl": mem.member.picture_url, "function": TeamMemberFunctions(mem.function).name, "active": mem.active, }) return members def get_team_managers(self): return TeamMembers.query.filter_by( team_id=self.id, function=TeamMemberFunctions.MANAGER.value, active=True).all()
class User(db.Model, UserMixin): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(20), unique=True, nullable=False) first_name = db.Column(db.String(20)) last_name = db.Column(db.String(20)) gender = db.Column(db.String(20), nullable=False) birth_date = db.Column(db.Date()) email = db.Column(db.String(120), unique=True, nullable=False) password = db.Column(db.String(60), nullable=False) image_file = db.Column(db.String(20), nullable=False, default='default.jpg') travels = db.relationship('Travel', backref='traveler', lazy='dynamic', cascade='all, delete-orphan') followed = db.relationship('Follow', foreign_keys=[Follow.follower_id], backref=db.backref('follower', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan') followers = db.relationship('Follow', foreign_keys=[Follow.followed_id], backref=db.backref('followed', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan') subcribed_posts = db.relationship('Subscriptions', backref='subcribed_posts', lazy='dynamic', cascade='all, delete-orphan') notifications = db.relationship('Notifications', backref='notifications', lazy='dynamic', cascade='all, delete-orphan') def __repr__(self): return f"User('{self.username}', '{self.email}', '{self.image_file}')" def update(self, data): for attr in data: setattr(self, attr, data[attr]) db.session.commit() def follow(self, user): if not self.is_following(user): f = Follow(follower=self, followed=user) db.session.add(f) db.session.commit() def unfollow(self, user): f = self.followed.filter_by(followed_id=user.id).first() if f: db.session.delete(f) db.session.commit() def is_following(self, user): if user.id is None: return False return self.followed.filter_by(followed_id=user.id).first() is not None def is_followed_by(self, user): if user.id is None: return False return self.followers.filter_by( follower_id=user.id).first() is not None # def get_followers(self): # User.query.join(User.followers).filter_by(followed_id=self.id).all() def is_subscribed(self, post): if post.id is None: return False return self.subcribed_posts.filter_by( post_id=post.id).first() is not None def subscribe(self, post): if not self.is_subscribed(post): f = Subscriptions(user_id=self.id, post_id=post.id) db.session.add(f) db.session.commit() def unsubscribe(self, post): found_post = self.subcribed_posts.filter_by(post_id=post.id).first() if found_post: db.session.delete(found_post) db.session.commit() ''' def show_notification(self, notification): found_notification = self.notifications.filter_by( notification_id = notification.notification_id).first() if found_notification.showed: Notifications.update().where( notification_id == found_notification.notification_id).values(showed=True) ''' def delete_notification(self, notification): found_notification = self.notifications.filter_by( notification_id=notification.notification_id).first() if found_notification: db.session.delete(found_notification) db.session.commit()
class Travel(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.Text, nullable=False) date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) start_date = db.Column(db.Date, nullable=False) end_date = db.Column(db.Date, nullable=False) country = db.Column(db.Text, nullable=False) city = db.Column(db.Text, nullable=False) latitude = db.Column(db.Float, nullable=False) longitude = db.Column(db.Float, nullable=False) content = db.Column(db.Text, nullable=False) comment = db.Column(db.Text, nullable=False) image_file = db.Column(db.Text, nullable=False, default="default_post.jpeg") subscribers = db.relationship('Subscriptions', backref='subscribers', lazy='dynamic', cascade='all, delete-orphan') def update(self, data): for attr in data: setattr(self, attr, data[attr]) db.session.commit() for sub in self.subscribers.all(): sub.send_notification() def _repr_(self): return f"Travel('{self.start_date}', '{self.end_date}', '{self.content}')"
class Organisation(db.Model): """ Describes an Organisation """ __tablename__ = "organisations" # Columns id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(512), nullable=False, unique=True) logo = db.Column(db.String) # URL of a logo url = db.Column(db.String) managers = db.relationship( User, secondary=organisation_managers, backref=db.backref("organisations", lazy="joined"), ) campaign = db.relationship( Campaign, secondary=campaign_organisations, backref="organisation" ) def create(self): """ Creates and saves the current model to the DB """ db.session.add(self) db.session.commit() @classmethod def create_from_dto(cls, new_organisation_dto: NewOrganisationDTO): """ Creates a new organisation from a DTO """ new_org = cls() new_org.name = new_organisation_dto.name new_org.logo = new_organisation_dto.logo new_org.url = new_organisation_dto.url for manager in new_organisation_dto.managers: user = User.get_by_username(manager) if user is None: raise NotFound(f"User {manager} Not Found") new_org.managers.append(user) new_org.create() return new_org def update(self, organisation_dto: OrganisationDTO): """ Updates Organisation from DTO """ for attr, value in organisation_dto.items(): if attr == "managers": continue try: is_field_nullable = self.__table__.columns[attr].nullable if is_field_nullable and value is not None: setattr(self, attr, value) elif value is not None: setattr(self, attr, value) except KeyError: continue if organisation_dto.managers: self.managers = [] # Need to handle this in the loop so we can take care of NotFound users for manager in organisation_dto.managers: new_manager = User.get_by_username(manager) if new_manager is None: raise NotFound(f"User {manager} Not Found") self.managers.append(new_manager) db.session.commit() def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() def can_be_deleted(self) -> bool: """ An Organisation can be deleted if it doesn't have any projects or teams """ return len(self.projects) == 0 and len(self.teams) == 0 @staticmethod def get(organisation_id: int): """ Gets specified organisation by id :param organisation_id: organisation ID in scope :return: Organisation if found otherwise None """ return Organisation.query.get(organisation_id) @staticmethod def get_organisation_by_name(organisation_name: str): """ Get organisation by name :param organisation_name: name of organisation :return: Organisation if found else None """ return Organisation.query.filter_by(name=organisation_name).first() @staticmethod def get_organisation_name_by_id(organisation_id: int): """ Get organisation name by id :param organisation_id: :return: Organisation name """ return Organisation.query.get(organisation_id).name @staticmethod def get_all_organisations(): """ Gets all organisations""" return Organisation.query.order_by(Organisation.name).all() @staticmethod def get_organisations_managed_by_user(user_id: int): """ Gets organisations a user can manage """ query_results = ( Organisation.query.join(organisation_managers) .filter( (organisation_managers.c.organisation_id == Organisation.id) & (organisation_managers.c.user_id == user_id) ) .order_by(Organisation.name) .all() ) return query_results def as_dto(self): """ Returns a dto for an organisation """ organisation_dto = OrganisationDTO() organisation_dto.organisation_id = self.id organisation_dto.name = self.name organisation_dto.logo = self.logo organisation_dto.url = self.url organisation_dto.managers = [] for manager in self.managers: org_manager_dto = OrganisationManagerDTO() org_manager_dto.username = manager.username org_manager_dto.picture_url = manager.picture_url organisation_dto.managers.append(org_manager_dto) return organisation_dto
class MappingIssueCategory(db.Model): """ Represents a category of task mapping issues identified during validaton """ __tablename__ = "mapping_issue_categories" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False, unique=True) description = db.Column(db.String, nullable=True) archived = db.Column(db.Boolean, default=False, nullable=False) def __init__(self, name): self.name = name @staticmethod def get_by_id(category_id: int): """ Get category by id """ return MappingIssueCategory.query.get(category_id) @classmethod def create_from_dto(cls, dto: MappingIssueCategoryDTO) -> int: """ Creates a new MappingIssueCategory class from dto """ new_category = cls(dto.name) new_category.description = dto.description db.session.add(new_category) db.session.commit() return new_category.id def update_category(self, dto: MappingIssueCategoryDTO): """ Update existing category """ self.name = dto.name self.description = dto.description if dto.archived is not None: self.archived = dto.archived db.session.commit() def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() @staticmethod def get_all_categories(include_archived): category_query = MappingIssueCategory.query.order_by(MappingIssueCategory.name) if not include_archived: category_query = category_query.filter_by(archived=False) results = category_query.all() if len(results) == 0: raise NotFound() dto = MappingIssueCategoriesDTO() for result in results: category = MappingIssueCategoryDTO() category.category_id = result.id category.name = result.name category.description = result.description category.archived = result.archived dto.categories.append(category) return dto def as_dto(self) -> MappingIssueCategoryDTO: """ Convert the category to its DTO representation """ dto = MappingIssueCategoryDTO() dto.category_id = self.id dto.name = self.name dto.description = self.description dto.archived = self.archived return dto
import geojson import json from backend import db from geoalchemy2 import Geometry from backend.models.postgis.utils import InvalidGeoJson, ST_SetSRID, ST_GeomFromGeoJSON # Priority areas aren't shared, however, this arch was taken from TM2 to ease data migration project_priority_areas = db.Table( "project_priority_areas", db.metadata, db.Column("project_id", db.Integer, db.ForeignKey("projects.id")), db.Column("priority_area_id", db.Integer, db.ForeignKey("priority_areas.id")), ) class PriorityArea(db.Model): """ Describes an individual priority area """ __tablename__ = "priority_areas" id = db.Column(db.Integer, primary_key=True) geometry = db.Column(Geometry("POLYGON", srid=4326)) @classmethod def from_dict(cls, area_poly: dict): """ Create a new Priority Area from dictionary """ pa_geojson = geojson.loads(json.dumps(area_poly)) if type(pa_geojson) is not geojson.Polygon: raise InvalidGeoJson("Priority Areas must be supplied as Polygons")
class ProjectChat(db.Model): """ Contains all project info localized into supported languages """ __tablename__ = "project_chat" id = db.Column(db.BigInteger, primary_key=True) project_id = db.Column( db.Integer, db.ForeignKey("projects.id"), index=True, nullable=False ) user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False) time_stamp = db.Column(db.DateTime, nullable=False, default=timestamp) message = db.Column(db.String, nullable=False) # Relationships posted_by = db.relationship(User, foreign_keys=[user_id]) @classmethod def create_from_dto(cls, dto: ChatMessageDTO): """ Creates a new ProjectInfo class from dto, used from project edit """ current_app.logger.debug("Create chat message from DTO") new_message = cls() new_message.project_id = dto.project_id new_message.user_id = dto.user_id # Use bleach to remove any potential mischief allowed_tags = [ "a", "b", "blockquote", "br", "code", "em", "h1", "h2", "h3", "img", "i", "li", "ol", "p", "pre", "strong", "ul", ] allowed_atrributes = {"a": ["href", "rel"], "img": ["src", "alt"]} clean_message = bleach.clean( markdown(dto.message, output_format="html"), tags=allowed_tags, attributes=allowed_atrributes, ) clean_message = bleach.linkify(clean_message) new_message.message = clean_message db.session.add(new_message) return new_message @staticmethod def get_messages(project_id: int, page: int, per_page: int = 20) -> ProjectChatDTO: """ Get all messages on the project """ project_messages = ( ProjectChat.query.filter_by(project_id=project_id) .order_by(ProjectChat.time_stamp.desc()) .paginate(page, per_page, True) ) dto = ProjectChatDTO() if project_messages.total == 0: return dto for message in project_messages.items: chat_dto = ChatMessageDTO() chat_dto.message = message.message chat_dto.username = message.posted_by.username chat_dto.picture_url = message.posted_by.picture_url chat_dto.timestamp = message.time_stamp dto.chat.append(chat_dto) dto.pagination = Pagination(project_messages) return dto
class Project(db.Model): """ Describes a HOT Mapping Project """ __tablename__ = "projects" # Columns id = db.Column(db.Integer, primary_key=True) status = db.Column(db.Integer, default=ProjectStatus.DRAFT.value, nullable=False) created = db.Column(db.DateTime, default=timestamp, nullable=False) priority = db.Column(db.Integer, default=ProjectPriority.MEDIUM.value) default_locale = db.Column( db.String(10), default="en" ) # The locale that is returned if requested locale not available author_id = db.Column(db.BigInteger, db.ForeignKey("users.id", name="fk_users"), nullable=False) mapper_level = db.Column( db.Integer, default=2, nullable=False, index=True) # Mapper level project is suitable for mapping_permission = db.Column(db.Integer, default=MappingPermission.ANY.value) validation_permission = db.Column( db.Integer, default=ValidationPermission.ANY.value ) # Means only users with validator role can validate enforce_random_task_selection = db.Column( db.Boolean, default=False ) # Force users to edit at random to avoid mapping "easy" tasks private = db.Column(db.Boolean, default=False) # Only allowed users can validate featured = db.Column( db.Boolean, default=False) # Only PMs can set a project as featured entities_to_map = db.Column(db.String) changeset_comment = db.Column(db.String) osmcha_filter_id = db.Column( db.String) # Optional custom filter id for filtering on OSMCha due_date = db.Column(db.DateTime) imagery = db.Column(db.String) josm_preset = db.Column(db.String) id_presets = db.Column(ARRAY(db.String)) last_updated = db.Column(db.DateTime, default=timestamp) license_id = db.Column(db.Integer, db.ForeignKey("licenses.id", name="fk_licenses")) geometry = db.Column(Geometry("MULTIPOLYGON", srid=4326)) centroid = db.Column(Geometry("POINT", srid=4326)) country = db.Column(ARRAY(db.String), default=[]) task_creation_mode = db.Column(db.Integer, default=TaskCreationMode.GRID.value, nullable=False) organisation_id = db.Column( db.Integer, db.ForeignKey("organisations.id", name="fk_organisations"), index=True, ) # Tags mapping_types = db.Column(ARRAY(db.Integer), index=True) # Editors mapping_editors = db.Column( ARRAY(db.Integer), default=[ Editors.ID.value, Editors.JOSM.value, Editors.POTLATCH_2.value, Editors.FIELD_PAPERS.value, Editors.CUSTOM.value, ], index=True, nullable=False, ) validation_editors = db.Column( ARRAY(db.Integer), default=[ Editors.ID.value, Editors.JOSM.value, Editors.POTLATCH_2.value, Editors.FIELD_PAPERS.value, Editors.CUSTOM.value, ], index=True, nullable=False, ) # Stats total_tasks = db.Column(db.Integer, nullable=False) tasks_mapped = db.Column(db.Integer, default=0, nullable=False) tasks_validated = db.Column(db.Integer, default=0, nullable=False) tasks_bad_imagery = db.Column(db.Integer, default=0, nullable=False) # Mapped Objects tasks = db.relationship(Task, backref="projects", cascade="all, delete, delete-orphan", lazy="dynamic") project_info = db.relationship(ProjectInfo, lazy="dynamic", cascade="all") project_chat = db.relationship(ProjectChat, lazy="dynamic", cascade="all") author = db.relationship(User) allowed_users = db.relationship(User, secondary=project_allowed_users) priority_areas = db.relationship( PriorityArea, secondary=project_priority_areas, cascade="all, delete-orphan", single_parent=True, ) custom_editor = db.relationship(CustomEditor, uselist=False) favorited = db.relationship(User, secondary=project_favorites, backref="favorites") organisation = db.relationship(Organisation, backref="projects") campaign = db.relationship(Campaign, secondary=campaign_projects, backref="projects") interests = db.relationship(Interest, secondary=project_interests, backref="projects") 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 set_project_aoi(self, draft_project_dto: DraftProjectDTO): """ Sets the AOI for the supplied project """ aoi_geojson = geojson.loads( json.dumps(draft_project_dto.area_of_interest)) aoi_geometry = GridService.merge_to_multi_polygon(aoi_geojson, dissolve=True) valid_geojson = geojson.dumps(aoi_geometry) self.geometry = ST_SetSRID(ST_GeomFromGeoJSON(valid_geojson), 4326) self.centroid = ST_Centroid(self.geometry) def set_default_changeset_comment(self): """ Sets the default changeset comment""" default_comment = current_app.config["DEFAULT_CHANGESET_COMMENT"] self.changeset_comment = ( f"{default_comment}-{self.id} {self.changeset_comment}" if self.changeset_comment is not None else f"{default_comment}-{self.id}") self.save() def set_country_info(self): """ Sets the default country based on centroid""" lat, lng = (db.session.query( cast(ST_Y(Project.centroid), sqlalchemy.String), cast(ST_X(Project.centroid), sqlalchemy.String), ).filter(Project.id == self.id).one()) url = "https://nominatim.openstreetmap.org/reverse?format=jsonv2&lat={0}&lon={1}".format( lat, lng) country_info = requests.get(url) country_info_json = country_info.content.decode("utf8").replace( "'", '"') # Load the JSON to a Python list & dump it back out as formatted JSON data = json.loads(country_info_json) if data["address"].get("country") is not None: self.country = [data["address"]["country"]] else: self.country = [data["address"]["county"]] self.save() def create(self): """ Creates and saves the current model to the DB """ db.session.add(self) db.session.commit() def save(self): """ Save changes to db""" db.session.commit() @staticmethod def clone(project_id: int, author_id: int): """ Clone project """ cloned_project = Project.get(project_id) # Remove clone from session so we can reinsert it as a new object db.session.expunge(cloned_project) make_transient(cloned_project) # Re-initialise counters and meta-data cloned_project.total_tasks = 0 cloned_project.tasks_mapped = 0 cloned_project.tasks_validated = 0 cloned_project.tasks_bad_imagery = 0 cloned_project.last_updated = timestamp() cloned_project.created = timestamp() cloned_project.author_id = author_id cloned_project.status = ProjectStatus.DRAFT.value cloned_project.id = None # Reset ID so we get a new ID when inserted cloned_project.geometry = None cloned_project.centroid = None db.session.add(cloned_project) db.session.commit() # Now add the project info, we have to do it in a two stage commit because we need to know the new project id original_project = Project.get(project_id) for info in original_project.project_info: db.session.expunge(info) make_transient( info ) # Must remove the object from the session or it will be updated rather than inserted info.id = None info.project_id_str = str(cloned_project.id) cloned_project.project_info.append(info) # Now add allowed users now we know new project id, if there are any for user in original_project.allowed_users: cloned_project.allowed_users.append(user) # Add other project metadata cloned_project.priority = original_project.priority cloned_project.default_locale = original_project.default_locale cloned_project.mapper_level = original_project.mapper_level cloned_project.mapping_permission = original_project.mapping_permission cloned_project.validation_permission = original_project.validation_permission cloned_project.enforce_random_task_selection = ( original_project.enforce_random_task_selection) cloned_project.private = original_project.private cloned_project.entities_to_map = original_project.entities_to_map cloned_project.due_date = original_project.due_date cloned_project.imagery = original_project.imagery cloned_project.josm_preset = original_project.josm_preset cloned_project.license_id = original_project.license_id cloned_project.mapping_types = original_project.mapping_types # We try to remove the changeset comment referencing the old project. This # assumes the default changeset comment has not changed between the old # project and the cloned. This is a best effort basis. default_comment = current_app.config["DEFAULT_CHANGESET_COMMENT"] changeset_comments = [] if original_project.changeset_comment is not None: changeset_comments = original_project.changeset_comment.split(" ") if f"{default_comment}-{original_project.id}" in changeset_comments: changeset_comments.remove( f"{default_comment}-{original_project.id}") cloned_project.changeset_comment = " ".join(changeset_comments) db.session.add(cloned_project) db.session.commit() return cloned_project @staticmethod def get(project_id: int): """ Gets specified project :param project_id: project ID in scope :return: Project if found otherwise None """ return Project.query.get(project_id) 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_random_task_selection = project_dto.enforce_random_task_selection 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(r"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: org = Organisation.get(project_dto.organisation) if org is None: raise NotFound("Organisation does not exist") self.organisation = org # 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 self.country = project_dto.country_tag # 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) # Update teams and projects relationship. self.teams = [] if hasattr(project_dto, "project_teams") and project_dto.project_teams: for team_dto in project_dto.project_teams: team = Team.get(team_dto.team_id) if team is None: raise NotFound(f"Team not found") role = TeamRoles[team_dto.role].value ProjectTeams(project=self, team=team, role=role) # 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() self.campaign = [ Campaign.query.get(c.id) for c in project_dto.campaigns ] if project_dto.mapping_permission: self.mapping_permission = MappingPermission[ project_dto.mapping_permission.upper()].value if project_dto.validation_permission: self.validation_permission = ValidationPermission[ project_dto.validation_permission.upper()].value # Update Interests. self.interests = [] if project_dto.interests: self.interests = [ Interest.query.get(i.id) for i in project_dto.interests ] db.session.commit() def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() def is_favorited(self, user_id: int) -> bool: user = User.query.get(user_id) if user not in self.favorited: return False return True def favorite(self, user_id: int): user = User.query.get(user_id) self.favorited.append(user) db.session.commit() def unfavorite(self, user_id: int): user = User.query.get(user_id) if user not in self.favorited: raise ValueError("Project not been favorited by user") self.favorited.remove(user) db.session.commit() def set_as_featured(self): if self.featured is True: raise ValueError("Project is already featured") self.featured = True db.session.commit() def unset_as_featured(self): if self.featured is False: raise ValueError("Project is not featured") self.featured = False db.session.commit() def can_be_deleted(self) -> bool: """ Projects can be deleted if they have no mapped work """ task_count = self.tasks.filter( Task.task_status != TaskStatus.READY.value).count() if task_count == 0: return True else: return False @staticmethod def get_projects_for_admin(admin_id: int, preferred_locale: str, search_dto: ProjectSearchDTO) -> PMDashboardDTO: """ Get projects for admin """ query = Project.query.filter(Project.author_id == admin_id) # Do Filtering Here if search_dto.order_by: if search_dto.order_by_type == "DESC": query = query.order_by(desc(search_dto.order_by)) else: query = query.order_by(search_dto.order_by) admins_projects = query.all() if admins_projects is None: raise NotFound("No projects found for admin") admin_projects_dto = PMDashboardDTO() for project in admins_projects: pm_project = project.get_project_summary(preferred_locale) project_status = ProjectStatus(project.status) if project_status == ProjectStatus.DRAFT: admin_projects_dto.draft_projects.append(pm_project) elif project_status == ProjectStatus.PUBLISHED: admin_projects_dto.active_projects.append(pm_project) elif project_status == ProjectStatus.ARCHIVED: admin_projects_dto.archived_projects.append(pm_project) else: current_app.logger.error( f"Unexpected state project {project.id}") return admin_projects_dto 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 query = """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 and project_id = :project_id;""" total_mapping_time = db.engine.execute(text(query), user_id=user_id, 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 = """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 and project_id = :project_id;""" total_validation_time = db.engine.execute(text(query), user_id=user_id, project_id=self.id) 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 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 query = """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 project_id = :project_id;""" total_mapping_time = db.engine.execute(text(query), 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 = """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 project_id = :project_id;""" total_validation_time = db.engine.execute(text(query), project_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( ) 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 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", lat_1=polygon.bounds[1], lat_2=polygon.bounds[3]), ), polygon, ) area = polygon_aea.area / 1000000 summary.area = area summary.country_tag = self.country summary.changeset_comment = self.changeset_comment summary.due_date = self.due_date summary.created = self.created summary.last_updated = self.last_updated summary.osmcha_filter_id = self.osmcha_filter_id summary.mapper_level = MappingLevel(self.mapper_level).name summary.mapping_permission = MappingPermission( self.mapping_permission).name summary.validation_permission = ValidationPermission( self.validation_permission).name summary.random_task_selection_enforced = self.enforce_random_task_selection summary.private = self.private summary.status = ProjectStatus(self.status).name summary.entities_to_map = self.entities_to_map summary.imagery = self.imagery if self.organisation_id: summary.organisation_name = self.organisation.name summary.organisation_logo = self.organisation.logo if self.campaign: summary.campaigns = [i.as_dto() for i in self.campaign] # Cast MappingType values to related string array mapping_types_array = [] if self.mapping_types: for mapping_type in self.mapping_types: mapping_types_array.append(MappingTypes(mapping_type).name) summary.mapping_types = mapping_types_array if self.mapping_editors: mapping_editors = [] for mapping_editor in self.mapping_editors: mapping_editors.append(Editors(mapping_editor).name) summary.mapping_editors = mapping_editors if self.validation_editors: validation_editors = [] for validation_editor in self.validation_editors: validation_editors.append(Editors(validation_editor).name) summary.validation_editors = validation_editors if self.custom_editor: summary.custom_editor = self.custom_editor.as_dto() # If project is private, fetch list of allowed users if self.private: allowed_users = [] for user in self.allowed_users: allowed_users.append(user.username) summary.allowed_users = allowed_users 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, ) summary.project_teams = [ ProjectTeamDTO( dict( team_id=t.team.id, team_name=t.team.name, role=TeamRoles(t.role).name, )) for t in self.teams ] project_info = ProjectInfo.get_dto_for_locale(self.id, preferred_locale, self.default_locale) summary.project_info = project_info return summary 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 @staticmethod def get_project_total_contributions(project_id: int) -> int: project_contributors_count = (TaskHistory.query.with_entities( TaskHistory.user_id).filter( TaskHistory.project_id == project_id, TaskHistory.action != "COMMENT").distinct( TaskHistory.user_id).count()) return project_contributors_count def get_aoi_geometry_as_geojson(self): """ Helper which returns the AOI geometry as a geojson object """ aoi_geojson = db.engine.execute(self.geometry.ST_AsGeoJSON()).scalar() return geojson.loads(aoi_geojson) def get_project_teams(self): """ Helper to return teams with members so we can handle permissions """ project_teams = [] for t in self.teams: project_teams.append({ "name": t.team.name, "role": t.role, "members": [m.member.username for m in t.team.members], }) return project_teams @staticmethod @cached(active_mappers_cache) def get_active_mappers(project_id) -> int: """ Get count of Locked tasks as a proxy for users who are currently active on the project """ return (Task.query.filter( Task.task_status.in_(( TaskStatus.LOCKED_FOR_MAPPING.value, TaskStatus.LOCKED_FOR_VALIDATION.value, ))).filter(Task.project_id == project_id).distinct( Task.locked_by).count()) 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.aoi_bbox = shape(base_dto.area_of_interest).bounds base_dto.mapping_permission = MappingPermission( self.mapping_permission).name base_dto.validation_permission = ValidationPermission( self.validation_permission).name base_dto.enforce_random_task_selection = self.enforce_random_task_selection 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.osmcha_filter_id = self.osmcha_filter_id base_dto.due_date = self.due_date base_dto.imagery = self.imagery base_dto.josm_preset = self.josm_preset base_dto.id_presets = self.id_presets base_dto.country_tag = self.country base_dto.organisation_id = self.organisation_id base_dto.license_id = self.license_id base_dto.created = self.created base_dto.last_updated = self.last_updated 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 base_dto.percent_mapped = Project.calculate_tasks_percent( "mapped", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) base_dto.percent_validated = Project.calculate_tasks_percent( "validated", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) base_dto.percent_bad_imagery = Project.calculate_tasks_percent( "bad_imagery", self.total_tasks, self.tasks_mapped, self.tasks_validated, self.tasks_bad_imagery, ) base_dto.project_teams = [ ProjectTeamDTO( dict( team_id=t.team.id, team_name=t.team.name, role=TeamRoles(t.role).name, )) for t in self.teams ] if self.custom_editor: base_dto.custom_editor = self.custom_editor.as_dto() 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.campaign: base_dto.campaigns = [i.as_dto() for i in self.campaign] if self.mapping_editors: mapping_editors = [] for mapping_editor in self.mapping_editors: mapping_editors.append(Editors(mapping_editor).name) base_dto.mapping_editors = mapping_editors if self.validation_editors: validation_editors = [] for validation_editor in self.validation_editors: validation_editors.append(Editors(validation_editor).name) base_dto.validation_editors = validation_editors 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 base_dto.interests = [ InterestDTO(dict(id=i.id, name=i.name)) for i in self.interests ] return self, base_dto def as_dto_for_mapping(self, authenticated_user_id: int = None, locale: str = "en", abbrev: bool = True) -> Optional[ProjectDTO]: """ Creates a Project DTO suitable for transmitting to mapper users """ # Check for project visibility settings is_allowed_user = True if self.private: is_allowed_user = False if authenticated_user_id: user = User().get_by_id(authenticated_user_id) if (UserRole(user.role) == UserRole.ADMIN or authenticated_user_id == self.author_id): is_allowed_user = True for user in self.allowed_users: if user.id == authenticated_user_id: is_allowed_user = True break if is_allowed_user: project, project_dto = self._get_project_and_base_dto() if abbrev is False: project_dto.tasks = Task.get_tasks_as_geojson_feature_collection( self.id, None) else: project_dto.tasks = Task.get_tasks_as_geojson_feature_collection_no_geom( self.id) project_dto.project_info = ProjectInfo.get_dto_for_locale( self.id, locale, project.default_locale) if project.organisation_id: project_dto.organisation = project.organisation.id project_dto.organisation_name = project.organisation.name project_dto.organisation_logo = project.organisation.logo project_dto.project_info_locales = ProjectInfo.get_dto_for_all_locales( self.id) return project_dto def tasks_as_geojson(self, task_ids_str: str, order_by=None, order_by_type="ASC", status=None): """ Creates a geojson of all areas """ project_tasks = Task.get_tasks_as_geojson_feature_collection( self.id, task_ids_str, order_by, order_by_type, status) return project_tasks @staticmethod def get_all_countries(): query = (db.session.query( func.unnest(Project.country).label("country")).distinct().order_by( "country")) tags_dto = TagsDTO() tags_dto.tags = [r[0] for r in query] return tags_dto @staticmethod def get_all_organisations_tag(preferred_locale="en"): query = (db.session.query( Project.id, Project.organisation_tag, Project.private, Project.status).join(ProjectInfo).filter( ProjectInfo.locale.in_( [preferred_locale, "en"])).filter(Project.private is not True).filter( Project.organisation_tag.isnot(None)).filter( Project.organisation_tag != "")) query = query.distinct(Project.organisation_tag) query = query.order_by(Project.organisation_tag) tags_dto = TagsDTO() tags_dto.tags = [r[1] for r in query] return tags_dto @staticmethod def get_all_campaign_tag(preferred_locale="en"): query = (db.session.query( Project.id, Project.campaign_tag, Project.private, Project.status).join(ProjectInfo).filter( ProjectInfo.locale.in_( [preferred_locale, "en"])).filter(Project.private is not True).filter( Project.campaign_tag.isnot(None)).filter( Project.campaign_tag != "")) query = query.distinct(Project.campaign_tag) query = query.order_by(Project.campaign_tag) tags_dto = TagsDTO() tags_dto.tags = [r[1] for r in query] return tags_dto @staticmethod def calculate_tasks_percent(target, total_tasks, tasks_mapped, tasks_validated, tasks_bad_imagery): """ Calculates percentages of contributions """ if target == "mapped": return int((tasks_mapped + tasks_validated) / (total_tasks - tasks_bad_imagery) * 100) elif target == "validated": return int(tasks_validated / (total_tasks - tasks_bad_imagery) * 100) elif target == "bad_imagery": return int((tasks_bad_imagery / total_tasks) * 100) 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_or_update_interests(self, interests_ids): self.interests = [] objs = [Interest.get_by_id(i) for i in interests_ids] self.interests.extend(objs) db.session.commit()
class ProjectInfo(db.Model): """ Contains all project info localized into supported languages """ __tablename__ = "project_info" project_id = db.Column(db.Integer, db.ForeignKey("projects.id"), primary_key=True) locale = db.Column(db.String(10), primary_key=True) name = db.Column(db.String(512)) short_description = db.Column(db.String) description = db.Column(db.String) instructions = db.Column(db.String) project_id_str = db.Column(db.String) text_searchable = db.Column( TSVECTOR ) # This contains searchable text and is populated by a DB Trigger per_task_instructions = db.Column(db.String) __table_args__ = ( db.Index("idx_project_info_composite", "locale", "project_id"), db.Index("textsearch_idx", "text_searchable"), {}, ) @classmethod def create_from_name(cls, name: str): """ Creates a new ProjectInfo class from name, used when creating draft projects """ new_info = cls() new_info.locale = "en" # Draft project default to english, PMs can change this prior to publication new_info.name = name return new_info @classmethod def create_from_dto(cls, dto: ProjectInfoDTO): """ Creates a new ProjectInfo class from dto, used from project edit """ new_info = cls() new_info.update_from_dto(dto) return new_info def update_from_dto(self, dto: ProjectInfoDTO): """ Updates existing ProjectInfo from supplied DTO """ self.locale = dto.locale self.name = dto.name self.project_id_str = str(self.project_id) # Allows project_id to be searched # Note project info not bleached on basis that admins are trusted users and shouldn't be doing anything bad self.short_description = dto.short_description self.description = dto.description self.instructions = dto.instructions self.per_task_instructions = dto.per_task_instructions @staticmethod def get_dto_for_locale(project_id, locale, default_locale="en") -> ProjectInfoDTO: """ Gets the projectInfoDTO for the project for the requested locale. If not found, then the default locale is used :param project_id: ProjectID in scope :param locale: locale requested by user :param default_locale: default locale of project :raises: ValueError if no info found for Default Locale """ project_info = ProjectInfo.query.filter_by( project_id=project_id, locale=locale ).one_or_none() if project_info is None: # If project is none, get default locale and don't worry about empty translations project_info = ProjectInfo.query.filter_by( project_id=project_id, locale=default_locale ).one_or_none() return project_info.get_dto() if locale == default_locale: # If locale == default_locale don't need to worry about empty translations return project_info.get_dto() default_locale = ProjectInfo.query.filter_by( project_id=project_id, locale=default_locale ).one_or_none() if default_locale is None: error_message = f"BAD DATA: no info for project {project_id}, locale: {locale}, default {default_locale}" current_app.logger.critical(error_message) raise ValueError(error_message) # Pass thru default_locale in case of partial translation return project_info.get_dto(default_locale) def get_dto(self, default_locale=ProjectInfoDTO()) -> ProjectInfoDTO: """ Get DTO for current ProjectInfo :param default_locale: The default locale string for any empty fields """ project_info_dto = ProjectInfoDTO() project_info_dto.locale = self.locale project_info_dto.name = self.name if self.name else default_locale.name project_info_dto.description = ( self.description if self.description else default_locale.description ) project_info_dto.short_description = ( self.short_description if self.short_description else default_locale.short_description ) project_info_dto.instructions = ( self.instructions if self.instructions else default_locale.instructions ) project_info_dto.per_task_instructions = ( self.per_task_instructions if self.per_task_instructions else default_locale.per_task_instructions ) return project_info_dto @staticmethod def get_dto_for_all_locales(project_id) -> List[ProjectInfoDTO]: locales = ProjectInfo.query.filter_by(project_id=project_id).all() project_info_dtos = [] for locale in locales: project_info_dto = locale.get_dto() project_info_dtos.append(project_info_dto) return project_info_dtos
class Unit(db.Model): id = db.Column(db.Integer, primary_key=True) unit = db.Column(db.String(20), nullable=False) classification = db.Column(db.String(20), nullable=False) multiplier = db.Column(db.Integer, nullable=False)
class Keyword(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(127), nullable=False)
class Batch(db.Model): id = db.Column(db.Integer, primary_key=True) compound_id = db.Column(db.Integer, db.ForeignKey('compound.id'), nullable=False) # Batch Identifiers lnb_reference = db.Column(db.String(30), nullable=True) source_reference = db.Column(db.String(50), nullable=True) source_barcode = db.Column(db.String(50), nullable=True) lot_num = db.Column(db.String(50), nullable=True) # Source Information source_name = db.Column(db.String(120), nullable=True) # Synthesis Information chemist = db.Column(db.String(50), nullable=True) date_synthesized = db.Column(db.DateTime, nullable=True) # External Synthesis Information shipment_num = db.Column(db.String(20), nullable=True) # Order Information date_ordered = db.Column(db.DateTime, nullable=True) ordered_by = db.Column(db.String(50), nullable=True) catalog_number = db.Column(db.String(100), nullable=True) date_received = db.Column(db.DateTime, nullable=True) # Relationships vials = db.relationship('Vial', backref='batch', lazy=True) def get_batch_represenation(self): d = { 'LNB Reference': self.lnb_reference, 'Source Reference': self.source_reference, 'Lot #': self.lot_num, 'Source': self.source_name, 'Catalog #': self.catalog_number } return [(k, v) for (k, v) in d.items() if v is not None] def __repr__(self): return f'<Compound({self.get_batch_represenation()})>'
class SearchKeywordRel(db.Model): id = db.Column(db.Integer, primary_key=True) ss_id = db.Column(db.Integer, db.ForeignKey('search_stream.id'), nullable=False) kw_id = db.Column(db.Integer, db.ForeignKey('keyword.id'), nullable=False)
class User(db.Model): """ Describes the history associated with a task """ __tablename__ = "users" id = db.Column(db.BigInteger, primary_key=True, index=True) username = db.Column(db.String, unique=True) role = db.Column(db.Integer, default=0, nullable=False) mapping_level = db.Column(db.Integer, default=1, nullable=False) tasks_mapped = db.Column(db.Integer, default=0, nullable=False) tasks_validated = db.Column(db.Integer, default=0, nullable=False) tasks_invalidated = db.Column(db.Integer, default=0, nullable=False) projects_mapped = db.Column(db.ARRAY(db.Integer)) email_address = db.Column(db.String) is_email_verified = db.Column(db.Boolean, default=False) is_expert = db.Column(db.Boolean, default=False) twitter_id = db.Column(db.String) facebook_id = db.Column(db.String) linkedin_id = db.Column(db.String) slack_id = db.Column(db.String) skype_id = db.Column(db.String) irc_id = db.Column(db.String) name = db.Column(db.String) city = db.Column(db.String) country = db.Column(db.String) picture_url = db.Column(db.String) gender = db.Column(db.Integer) self_description_gender = db.Column(db.String) default_editor = db.Column(db.String, default="ID", nullable=False) mentions_notifications = db.Column(db.Boolean, default=True, nullable=False) comments_notifications = db.Column(db.Boolean, default=False, nullable=False) projects_notifications = db.Column(db.Boolean, default=True, nullable=False) tasks_notifications = db.Column(db.Boolean, default=True, nullable=False) teams_notifications = db.Column(db.Boolean, default=True, nullable=False) date_registered = db.Column(db.DateTime, default=timestamp) # Represents the date the user last had one of their tasks validated last_validation_date = db.Column(db.DateTime, default=timestamp) # Relationships accepted_licenses = db.relationship("License", secondary=user_licenses_table) interests = db.relationship(Interest, secondary=user_interests, backref="users") @property def missing_maps_profile_url(self): return f"http://www.missingmaps.org/users/#/{self.username}" @property def osm_profile_url(self): return f"{current_app.config['OSM_SERVER_URL']}/user/{self.username}" def create(self): """ Creates and saves the current model to the DB """ db.session.add(self) db.session.commit() def save(self): db.session.commit() @staticmethod def get_by_id(user_id: int): """ Return the user for the specified id, or None if not found """ return User.query.get(user_id) @staticmethod def get_by_username(username: str): """ Return the user for the specified username, or None if not found """ return User.query.filter_by(username=username).one_or_none() def update_username(self, username: str): """ Update the username """ self.username = username db.session.commit() def update_picture_url(self, picture_url: str): """ Update the profile picture """ self.picture_url = picture_url db.session.commit() def update(self, user_dto: UserDTO): """ Update the user details """ for attr, value in user_dto.items(): if attr == "gender" and value is not None: value = UserGender[value].value try: is_field_nullable = self.__table__.columns[attr].nullable if is_field_nullable and value is not None: setattr(self, attr, value) elif value is not None: setattr(self, attr, value) except KeyError: continue if user_dto.gender != UserGender.SELF_DESCRIBE.name: self.self_description_gender = None db.session.commit() def set_email_verified_status(self, is_verified: bool): """ Updates email verfied flag on successfully verified emails""" self.is_email_verified = is_verified db.session.commit() def set_is_expert(self, is_expert: bool): """ Enables or disables expert mode on the user""" self.is_expert = is_expert db.session.commit() @staticmethod 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, User.picture_url) # Add filter to query as required if query.mapping_level: mapping_levels = query.mapping_level.split(",") mapping_level_array = [ MappingLevel[mapping_level].value for mapping_level in mapping_levels ] base = base.filter(User.mapping_level.in_(mapping_level_array)) if query.username: base = base.filter( User.username.ilike(("%" + query.username + "%"))) if query.role: roles = query.role.split(",") role_array = [UserRole[role].value for role in roles] base = base.filter(User.role.in_(role_array)) 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.picture_url = result.picture_url listed_user.role = UserRole(result.role).name dto.users.append(listed_user) dto.pagination = Pagination(results) return dto @staticmethod def get_all_users_not_paginated(): """ Get all users in DB""" return db.session.query(User.id).all() @staticmethod def filter_users(user_filter: str, project_id: int, page: int) -> UserFilterDTO: """Finds users that matches first characters, for auto-complete. Users who have participated (mapped or validated) in the project, if given, will be returned ahead of those who have not. """ # Note that the projects_mapped column includes both mapped and validated projects. query = (db.session.query( User.username, User.projects_mapped.any(project_id).label("participant")).filter( User.username.ilike(user_filter.lower() + "%")).order_by( desc("participant").nullslast(), User.username)) results = query.paginate(page, 20, True) if results.total == 0: raise NotFound() dto = UserFilterDTO() for result in results.items: dto.usernames.append(result.username) if project_id is not None: participant = ProjectParticipantUser() participant.username = result.username participant.project_id = project_id participant.is_participant = bool(result.participant) dto.users.append(participant) dto.pagination = Pagination(results) return dto @staticmethod def upsert_mapped_projects(user_id: int, project_id: int): """ Adds projects to mapped_projects if it doesn't exist """ query = User.query.filter_by(id=user_id) result = query.filter( User.projects_mapped.op("@>")("{}".format("{" + str(project_id) + "}"))).count() if result > 0: return # User has previously mapped this project so return user = query.one_or_none() # Fix for new mappers. if user.projects_mapped is None: user.projects_mapped = [] user.projects_mapped.append(project_id) db.session.commit() @staticmethod def get_mapped_projects(user_id: int, preferred_locale: str) -> UserMappedProjectsDTO: """ Get all projects a user has mapped on """ from backend.models.postgis.task import Task from backend.models.postgis.project import Project query = db.session.query(func.unnest( User.projects_mapped)).filter_by(id=user_id) query_validated = (db.session.query( Task.project_id.label("project_id"), func.count(Task.validated_by).label("validated"), ).filter(Task.project_id.in_(query)).filter_by( validated_by=user_id).group_by(Task.project_id, Task.validated_by).subquery()) query_mapped = (db.session.query( Task.project_id.label("project_id"), func.count(Task.mapped_by).label("mapped"), ).filter(Task.project_id.in_(query)).filter_by( mapped_by=user_id).group_by(Task.project_id, Task.mapped_by).subquery()) query_union = (db.session.query( func.coalesce(query_validated.c.project_id, query_mapped.c.project_id).label("project_id"), func.coalesce(query_validated.c.validated, 0).label("validated"), func.coalesce(query_mapped.c.mapped, 0).label("mapped"), ).join( query_mapped, query_validated.c.project_id == query_mapped.c.project_id, full=True, ).subquery()) results = (db.session.query( Project.id, Project.status, Project.default_locale, query_union.c.mapped, query_union.c.validated, functions.ST_AsGeoJSON(Project.centroid), ).filter(Project.id == query_union.c.project_id).order_by( desc(Project.id)).all()) 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 set_user_role(self, role: UserRole): """ Sets the supplied role on the user """ self.role = role.value db.session.commit() def set_mapping_level(self, level: MappingLevel): """ Sets the supplied level on the user """ self.mapping_level = level.value db.session.commit() def accept_license_terms(self, license_id: int): """ Associate the user in scope with the supplied license """ image_license = License.get_by_id(license_id) self.accepted_licenses.append(image_license) db.session.commit() def has_user_accepted_licence(self, license_id: int): """ Test to see if the user has accepted the terms of the specified license""" image_license = License.get_by_id(license_id) if image_license in self.accepted_licenses: return True return False def delete(self): """ Delete the user in scope from DB """ db.session.delete(self) db.session.commit() 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.projects_mapped = (len(self.projects_mapped) if self.projects_mapped else None) user_dto.is_expert = self.is_expert or False user_dto.date_registered = self.date_registered user_dto.twitter_id = self.twitter_id user_dto.linkedin_id = self.linkedin_id user_dto.facebook_id = self.facebook_id user_dto.skype_id = self.skype_id user_dto.slack_id = self.slack_id user_dto.irc_id = self.irc_id user_dto.city = self.city user_dto.country = self.country user_dto.name = self.name user_dto.picture_url = self.picture_url user_dto.osm_profile = self.osm_profile_url user_dto.missing_maps_profile = self.missing_maps_profile_url user_dto.default_editor = self.default_editor user_dto.mentions_notifications = self.mentions_notifications user_dto.projects_notifications = self.projects_notifications user_dto.comments_notifications = self.comments_notifications user_dto.tasks_notifications = self.tasks_notifications user_dto.teams_notifications = self.teams_notifications gender = None if self.gender is not None: gender = UserGender(self.gender).name user_dto.gender = gender user_dto.self_description_gender = self.self_description_gender 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 create_or_update_interests(self, interests_ids): self.interests = [] objs = [Interest.get_by_id(i) for i in interests_ids] self.interests.extend(objs) db.session.commit()
class Search(db.Model): id = db.Column(db.Integer, primary_key=True) q = db.Column(db.String(1023), nullable=False, default=True) center = db.Column(db.String(127), nullable=True) description = db.Column(db.Text(16383), nullable=True) description_508 = db.Column(db.Text(16383), nullable=True) location = db.Column(db.String(127), nullable=True) media_type = db.Column(db.String(127), nullable=True) nasa_id = db.Column(db.String(127), nullable=True) photographer = db.Column(db.String(127), nullable=True) secondary_creator = db.Column(db.String(127), nullable=True) title = db.Column(db.String(1023), nullable=True) year_start = db.Column(db.Integer, nullable=True) year_end = db.Column(db.Integer, nullable=True) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True) timestamp = db.Column(db.DateTime, nullable=False, default=datetime.now())
class NNSensorGroup(db.Model): id = db.Column(db.Integer, primary_key=True) sensor = db.Column(db.Integer) group = db.Column(db.Integer)
OrganisationDTO, NewOrganisationDTO, OrganisationManagerDTO, ) from backend.models.postgis.user import User from backend.models.postgis.campaign import Campaign, campaign_organisations from backend.models.postgis.utils import NotFound # Secondary table defining many-to-many relationship between organisations and managers organisation_managers = db.Table( "organisation_managers", db.metadata, db.Column( "organisation_id", db.Integer, db.ForeignKey("organisations.id"), nullable=False ), db.Column("user_id", db.BigInteger, db.ForeignKey("users.id"), nullable=False), db.UniqueConstraint("organisation_id", "user_id", name="organisation_user_key"), ) class InvalidRoleException(Exception): pass class Organisation(db.Model): """ Describes an Organisation """ __tablename__ = "organisations"
class Users(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(20)) email = db.Column(db.String(50), unique=True) password = db.Column(db.Text()) name = db.Column(db.String(250)) birth_date = db.Column(db.Date()) gender = db.Column(db.String(1)) address = db.Column(db.String(250)) push_notification_token = db.Column(db.Text(), nullable=True) type = db.Column(db.Integer, db.ForeignKey('user_type.id')) program_id = db.Column(db.Integer, db.ForeignKey('program.id'), nullable=True) image_path = db.Column(db.Text(), nullable=True) def set_fields(self, fields): self.username = fields.get('username') self.email = fields.get('email') self.name = fields.get('name') self.gender = fields.get('gender') self.address = fields.get('address') self.birth_date = datetime.datetime.strptime(fields.get('birth_date'), "%m-%d-%Y").date() if fields.get('birth_date') else None self.program_id = fields.get('program_id') self.type = self.type if self.type else fields.get('type') def set_password(self, password): self.password = generate_password_hash(password) def check_password(self, password): return check_password_hash(self.password, password) def save_image(self, file): file_name, _format = str(file.filename).rsplit('.', 1) user_name, domain = str(self.email).split('@', maxsplit=1) if not _format in settings.ALLOWED_EXTENSIONS: _format = 'jpg' files = {'image_file': file} headers = { "enctype": "multipart/form-data" } r = requests.post('http://eliakimdjango.pythonanywhere.com/save_profile_image', files={'file': (user_name + str(random.randint(1000, 10000)) + '.' + _format, file, headers, {'Expires': '0'})}, data={'old_file_path': self.image_path}) # r = requests.post('http://127.0.0.1:2000/save_profile_image', # files={'file': (self.username + str(random.randint(1000, 10000)) + '.' + _format, file, # headers, {'Expires': '0'})}, # data={'old_file_path': self.image_path}) if r.status_code==200: self.image_path = r.json()['result'] return True
class Task(db.Model): """ Describes an individual mapping Task """ __tablename__ = "tasks" # Table has composite PK on (id and project_id) id = db.Column(db.Integer, primary_key=True) project_id = db.Column( db.Integer, db.ForeignKey("projects.id"), index=True, primary_key=True ) x = db.Column(db.Integer) y = db.Column(db.Integer) zoom = db.Column(db.Integer) extra_properties = db.Column(db.Unicode) # Tasks need to be split differently if created from an arbitrary grid or were clipped to the edge of the AOI is_square = db.Column(db.Boolean, default=True) geometry = db.Column(Geometry("MULTIPOLYGON", srid=4326)) task_status = db.Column(db.Integer, default=TaskStatus.READY.value) locked_by = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_users_locked"), index=True ) mapped_by = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_users_mapper"), index=True ) validated_by = db.Column( db.BigInteger, db.ForeignKey("users.id", name="fk_users_validator"), index=True ) # Mapped objects task_history = db.relationship(TaskHistory, cascade="all") task_annotations = db.relationship(TaskAnnotation, cascade="all") lock_holder = db.relationship(User, foreign_keys=[locked_by]) mapper = db.relationship(User, foreign_keys=[mapped_by]) def create(self): """ Creates and saves the current model to the DB """ db.session.add(self) db.session.commit() def update(self): """ Updates the DB with the current state of the Task """ db.session.commit() def delete(self): """ Deletes the current model from the DB """ db.session.delete(self) db.session.commit() @classmethod def from_geojson_feature(cls, task_id, task_feature): """ Constructs and validates a task from a GeoJson feature object :param task_id: Unique ID for the task :param task_feature: A geoJSON feature object :raises InvalidGeoJson, InvalidData """ if type(task_feature) is not geojson.Feature: raise InvalidGeoJson("Task: Invalid GeoJson should be a feature") task_geometry = task_feature.geometry if type(task_geometry) is not geojson.MultiPolygon: raise InvalidGeoJson("Task: Geometry must be a MultiPolygon") is_valid_geojson = geojson.is_valid(task_geometry) if is_valid_geojson["valid"] == "no": raise InvalidGeoJson( f"Task: Invalid MultiPolygon - {is_valid_geojson['message']}" ) task = cls() try: task.x = task_feature.properties["x"] task.y = task_feature.properties["y"] task.zoom = task_feature.properties["zoom"] task.is_square = task_feature.properties["isSquare"] except KeyError as e: raise InvalidData(f"Task: Expected property not found: {str(e)}") if "extra_properties" in task_feature.properties: task.extra_properties = json.dumps( task_feature.properties["extra_properties"] ) task.id = task_id task_geojson = geojson.dumps(task_geometry) task.geometry = ST_SetSRID(ST_GeomFromGeoJSON(task_geojson), 4326) return task @staticmethod def get(task_id: int, project_id: int): """ Gets specified task :param task_id: task ID in scope :param project_id: project ID in scope :return: Task if found otherwise None """ # LIKELY PROBLEM AREA return Task.query.filter_by(id=task_id, project_id=project_id).one_or_none() @staticmethod def get_tasks(project_id: int, task_ids: List[int]): """ Get all tasks that match supplied list """ return Task.query.filter( Task.project_id == project_id, Task.id.in_(task_ids) ).all() @staticmethod def get_all_tasks(project_id: int): """ Get all tasks for a given project """ return Task.query.filter(Task.project_id == project_id).all() @staticmethod def auto_unlock_delta(): return parse_duration(current_app.config["TASK_AUTOUNLOCK_AFTER"]) @staticmethod def auto_unlock_tasks(project_id: int): """Unlock all tasks locked for longer than the auto-unlock delta""" expiry_delta = Task.auto_unlock_delta() lock_duration = (datetime.datetime.min + expiry_delta).time().isoformat() expiry_date = datetime.datetime.utcnow() - expiry_delta old_locks_query = """SELECT t.id FROM tasks t, task_history th WHERE t.id = th.task_id AND t.project_id = th.project_id AND t.task_status IN (1,3) AND th.action IN ( 'LOCKED_FOR_VALIDATION','LOCKED_FOR_MAPPING' ) AND th.action_text IS NULL AND t.project_id = :project_id AND th.action_date <= :expiry_date """ old_tasks = db.engine.execute( text(old_locks_query), project_id=project_id, expiry_date=str(expiry_date) ) if old_tasks.rowcount == 0: # no tasks older than the delta found, return without further processing return for old_task in old_tasks: task = Task.get(old_task[0], project_id) task.auto_unlock_expired_tasks(expiry_date, lock_duration) def auto_unlock_expired_tasks(self, expiry_date, lock_duration): """Unlock all tasks locked before expiry date. Clears task lock if needed""" TaskHistory.update_expired_and_locked_actions( self.project_id, self.id, expiry_date, lock_duration ) last_action = TaskHistory.get_last_locked_or_auto_unlocked_action( self.project_id, self.id ) if last_action.action in [ "AUTO_UNLOCKED_FOR_MAPPING", "AUTO_UNLOCKED_FOR_VALIDATION", ]: self.clear_lock() def is_mappable(self): """ Determines if task in scope is in suitable state for mapping """ if TaskStatus(self.task_status) not in [ TaskStatus.READY, TaskStatus.INVALIDATED, ]: return False return True def set_task_history( self, action, user_id, comment=None, new_state=None, mapping_issues=None ): """ Sets the task history for the action that the user has just performed :param task: Task in scope :param user_id: ID of user performing the action :param action: Action the user has performed :param comment: Comment user has added :param new_state: New state of the task :param mapping_issues: Identified issues leading to invalidation """ history = TaskHistory(self.id, self.project_id, user_id) if action in [TaskAction.LOCKED_FOR_MAPPING, TaskAction.LOCKED_FOR_VALIDATION]: history.set_task_locked_action(action) elif action == TaskAction.COMMENT: history.set_comment_action(comment) elif action == TaskAction.STATE_CHANGE: history.set_state_change_action(new_state) elif action in [ TaskAction.AUTO_UNLOCKED_FOR_MAPPING, TaskAction.AUTO_UNLOCKED_FOR_VALIDATION, ]: history.set_auto_unlock_action(action) if mapping_issues is not None: history.task_mapping_issues = mapping_issues self.task_history.append(history) return history def lock_task_for_mapping(self, user_id: int): self.set_task_history(TaskAction.LOCKED_FOR_MAPPING, user_id) self.task_status = TaskStatus.LOCKED_FOR_MAPPING.value self.locked_by = user_id self.update() def lock_task_for_validating(self, user_id: int): self.set_task_history(TaskAction.LOCKED_FOR_VALIDATION, user_id) self.task_status = TaskStatus.LOCKED_FOR_VALIDATION.value self.locked_by = user_id self.update() def reset_task(self, user_id: int): if TaskStatus(self.task_status) in [ TaskStatus.LOCKED_FOR_MAPPING, TaskStatus.LOCKED_FOR_VALIDATION, ]: self.record_auto_unlock() self.set_task_history(TaskAction.STATE_CHANGE, user_id, None, TaskStatus.READY) self.mapped_by = None self.validated_by = None self.locked_by = None self.task_status = TaskStatus.READY.value self.update() def clear_task_lock(self): """ Unlocks task in scope in the database. Clears the lock as though it never happened. No history of the unlock is recorded. :return: """ # clear the lock action for the task in the task history last_action = TaskHistory.get_last_locked_action(self.project_id, self.id) last_action.delete() # Set locked_by to null and status to last status on task self.clear_lock() def record_auto_unlock(self, lock_duration): locked_user = self.locked_by last_action = TaskHistory.get_last_locked_action(self.project_id, self.id) next_action = ( TaskAction.AUTO_UNLOCKED_FOR_MAPPING if last_action.action == "LOCKED_FOR_MAPPING" else TaskAction.AUTO_UNLOCKED_FOR_VALIDATION ) self.clear_task_lock() # Add AUTO_UNLOCKED action in the task history auto_unlocked = self.set_task_history(action=next_action, user_id=locked_user) auto_unlocked.action_text = lock_duration self.update() def unlock_task( self, user_id, new_state=None, comment=None, undo=False, issues=None ): """ Unlock task and ensure duration task locked is saved in History """ if comment: self.set_task_history( action=TaskAction.COMMENT, comment=comment, user_id=user_id, mapping_issues=issues, ) history = self.set_task_history( action=TaskAction.STATE_CHANGE, new_state=new_state, user_id=user_id, mapping_issues=issues, ) if ( new_state in [TaskStatus.MAPPED, TaskStatus.BADIMAGERY] and TaskStatus(self.task_status) != TaskStatus.LOCKED_FOR_VALIDATION ): # Don't set mapped if state being set back to mapped after validation self.mapped_by = user_id elif new_state == TaskStatus.VALIDATED: TaskInvalidationHistory.record_validation( self.project_id, self.id, user_id, history ) self.validated_by = user_id elif new_state == TaskStatus.INVALIDATED: TaskInvalidationHistory.record_invalidation( self.project_id, self.id, user_id, history ) self.mapped_by = None self.validated_by = None if not undo: # Using a slightly evil side effect of Actions and Statuses having the same name here :) TaskHistory.update_task_locked_with_duration( self.id, self.project_id, TaskStatus(self.task_status), user_id ) self.task_status = new_state.value self.locked_by = None self.update() def reset_lock(self, user_id, comment=None): """ Removes a current lock from a task, resets to last status and updates history with duration of lock """ if comment: self.set_task_history( action=TaskAction.COMMENT, comment=comment, user_id=user_id ) # Using a slightly evil side effect of Actions and Statuses having the same name here :) TaskHistory.update_task_locked_with_duration( self.id, self.project_id, TaskStatus(self.task_status), user_id ) self.clear_lock() def clear_lock(self): """ Resets to last status and removes current lock from a task """ self.task_status = TaskHistory.get_last_status(self.project_id, self.id).value self.locked_by = None self.update() @staticmethod def get_tasks_as_geojson_feature_collection( project_id, task_ids_str: str = None, order_by: str = None, order_by_type: str = "ASC", status: int = None, ): """ Creates a geoJson.FeatureCollection object for tasks related to the supplied project ID :param project_id: Owning project ID :order_by: sorting option: available values update_date and building_area_diff :status: task status id to filter by :return: geojson.FeatureCollection """ # subquery = ( # db.session.query(func.max(TaskHistory.action_date)) # .filter( # Task.id == TaskHistory.task_id, # Task.project_id == TaskHistory.project_id, # ) # .correlate(Task) # .group_by(Task.id) # .label("update_date") # ) query = db.session.query( Task.id, Task.x, Task.y, Task.zoom, Task.is_square, Task.task_status, Task.geometry.ST_AsGeoJSON().label("geojson"), Task.locked_by, # subquery, ) filters = [Task.project_id == project_id] if task_ids_str: tasks_filters = [] task_ids = map(int, task_ids_str.split(",")) tasks = Task.get_tasks(project_id, task_ids) if not tasks or len(tasks) == 0: raise NotFound() else: for task in tasks: tasks_filters.append(task.id) filters = [Task.project_id == project_id, Task.id.in_(tasks_filters)] else: tasks = Task.get_all_tasks(project_id) if not tasks or len(tasks) == 0: raise NotFound() if status: filters.append(Task.task_status == status) if order_by == "effort_prediction": query = query.outerjoin(TaskAnnotation).filter(*filters) if order_by_type == "DESC": query = query.order_by( desc( cast( cast(TaskAnnotation.properties["building_area_diff"], Text), Float, ) ) ) else: query = query.order_by( cast( cast(TaskAnnotation.properties["building_area_diff"], Text), Float, ) ) # elif order_by == "last_updated": # if order_by_type == "DESC": # query = query.filter(*filters).order_by(desc("update_date")) # else: # query = query.filter(*filters).order_by("update_date") else: query = query.filter(*filters) project_tasks = query.all() tasks_features = [] for task in project_tasks: task_geometry = geojson.loads(task.geojson) task_properties = dict( taskId=task.id, taskX=task.x, taskY=task.y, taskZoom=task.zoom, taskIsSquare=task.is_square, taskStatus=TaskStatus(task.task_status).name, lockedBy=task.locked_by, ) feature = geojson.Feature( geometry=task_geometry, properties=task_properties ) tasks_features.append(feature) return geojson.FeatureCollection(tasks_features) @staticmethod def get_tasks_as_geojson_feature_collection_no_geom(project_id): """ Creates a geoJson.FeatureCollection object for all tasks related to the supplied project ID without geometry :param project_id: Owning project ID :return: geojson.FeatureCollection """ project_tasks = ( db.session.query( Task.id, Task.x, Task.y, Task.zoom, Task.is_square, Task.task_status ) .filter(Task.project_id == project_id) .all() ) tasks_features = [] for task in project_tasks: task_properties = dict( taskId=task.id, taskX=task.x, taskY=task.y, taskZoom=task.zoom, taskIsSquare=task.is_square, taskStatus=TaskStatus(task.task_status).name, ) feature = geojson.Feature(properties=task_properties) tasks_features.append(feature) return geojson.FeatureCollection(tasks_features) @staticmethod 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 = :project_id and t.task_status = 2 and th.action_text = 'MAPPED' group by u.username, u.mapping_level, u.date_registered, u.last_validation_date""" results = db.engine.execute(text(sql), project_id=project_id) 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 @staticmethod def get_max_task_id_for_project(project_id: int): """Gets the nights task id currently in use on a project""" sql = """select max(id) from tasks where project_id = :project_id GROUP BY project_id""" result = db.engine.execute(text(sql), project_id=project_id) if result.rowcount == 0: raise NotFound() for row in result: return row[0] def as_dto( self, task_history: List[TaskHistoryDTO] = [], last_updated: datetime.datetime = None, ): """Just converts to a TaskDTO""" task_dto = TaskDTO() task_dto.task_id = self.id task_dto.project_id = self.project_id task_dto.task_status = TaskStatus(self.task_status).name task_dto.lock_holder = self.lock_holder.username if self.lock_holder else None task_dto.task_history = task_history if last_updated: task_dto.last_updated = last_updated task_dto.auto_unlock_seconds = Task.auto_unlock_delta().total_seconds() return task_dto def as_dto_with_instructions(self, preferred_locale: str = "en") -> TaskDTO: """Get dto with any task instructions""" task_history = [] for action in reversed(self.task_history): history = TaskHistoryDTO() history.history_id = action.id history.action = action.action history.action_text = action.action_text history.action_date = action.action_date history.action_by = ( action.actioned_by.username if action.actioned_by else None ) history.picture_url = ( action.actioned_by.picture_url if action.actioned_by else None ) if action.task_mapping_issues: history.issues = [ issue.as_dto() for issue in action.task_mapping_issues ] task_history.append(history) last_updated = None if len(task_history) > 0: last_updated = task_history[0].action_date task_dto = self.as_dto(task_history, last_updated=last_updated) per_task_instructions = self.get_per_task_instructions(preferred_locale) # If we don't have instructions in preferred locale try again for default locale task_dto.per_task_instructions = ( per_task_instructions if per_task_instructions else self.get_per_task_instructions(self.projects.default_locale) ) annotations = self.get_per_task_annotations() task_dto.task_annotations = annotations if annotations else [] return task_dto def get_per_task_annotations(self): result = [ta.get_dto() for ta in self.task_annotations] return result def get_per_task_instructions(self, search_locale: str) -> str: """ Gets any per task instructions attached to the project """ project_info = self.projects.project_info.all() for info in project_info: if info.locale == search_locale: return self.format_per_task_instructions(info.per_task_instructions) def format_per_task_instructions(self, instructions) -> str: """ Format instructions by looking for X, Y, Z tokens and replacing them with the task values """ if not instructions: return "" # No instructions so return empty string properties = {} if self.x: properties["x"] = str(self.x) if self.y: properties["y"] = str(self.y) if self.zoom: properties["z"] = str(self.zoom) if self.extra_properties: properties.update(json.loads(self.extra_properties)) try: instructions = instructions.format(**properties) except KeyError: pass return instructions def copy_task_history(self) -> list: copies = [] for entry in self.task_history: db.session.expunge(entry) make_transient(entry) entry.id = None entry.task_id = None db.session.add(entry) copies.append(entry) return copies def get_locked_tasks_for_user(user_id: int): """ Gets tasks on project owned by specified user id""" tasks = Task.query.filter_by(locked_by=user_id) tasks_dto = LockedTasksForUser() tasks_dto.locked_tasks = [] for task in tasks: tasks_dto.locked_tasks.append(task.id) tasks_dto.project = task.project_id tasks_dto.task_status = TaskStatus(task.task_status).name return tasks_dto def get_locked_tasks_details_for_user(user_id: int): """ Gets tasks on project owned by specified user id""" tasks = Task.query.filter_by(locked_by=user_id) locked_tasks = [] for task in tasks: locked_tasks.append(task) return locked_tasks