class SubmissionVersion(BaseModel): __tablename__ = 'submission_version' CHANNEL_CHOICES = ( ('SMS', _('SMS')), ('WEB', _('Web')), ('API', _('API')), ('ODK', _('ODK')), ) id = db.Column(db.Integer, primary_key=True) submission_id = db.Column(db.Integer, db.ForeignKey('submission.id', ondelete='CASCADE'), nullable=False) data = db.Column(JSONB) submission = db.relationship('Submission', backref=db.backref('versions', cascade='all, delete', passive_deletes=True)) timestamp = db.Column(db.DateTime, default=current_timestamp) channel = db.Column(ChoiceType(CHANNEL_CHOICES)) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) deployment = db.relationship('Deployment', backref=db.backref('submission_versions', cascade='all, delete', passive_deletes=True)) identity = db.Column(db.String, default='unknown', nullable=False) def changes(self): from apollo import models added = [] changed = [] deleted = [] attr_current = set(self.data.keys()) prev_changeset = models.SubmissionVersion.query.filter( models.SubmissionVersion.submission == self.submission, # noqa models.SubmissionVersion.timestamp < self.timestamp # noqa ).order_by(sa.desc(models.SubmissionVersion.timestamp)).first() if prev_changeset: attr_previous = set(prev_changeset.data.keys()) added = attr_current.difference(attr_previous) deleted = attr_previous.difference(attr_current) changed = { attr for attr in attr_current.intersection(attr_previous) if self.data[attr] != prev_changeset.data[attr] } else: added = set(self.data.keys()) return {'added': added, 'deleted': deleted, 'changed': changed}
class Message(BaseModel): DIRECTIONS = ( ('IN', _('INCOMING')), ('OUT', _('OUTGOING')), ) MESSAGE_TYPES = ( ('SMS', _('SMS')), ('API', _('API')), ('ODK', _('ODK')), ) __tablename__ = 'message' id = db.Column(db.Integer, primary_key=True) direction = db.Column(ChoiceType(DIRECTIONS), nullable=False) recipient = db.Column(db.String) sender = db.Column(db.String) text = db.Column(db.String) message_type = db.Column(ChoiceType(MESSAGE_TYPES), default=MESSAGE_TYPES[0][0]) received = db.Column(db.DateTime, default=datetime.utcnow, index=True) delivered = db.Column(db.DateTime) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) event_id = db.Column(db.Integer, db.ForeignKey('event.id', ondelete='CASCADE'), nullable=False) submission_id = db.Column( db.Integer, db.ForeignKey('submission.id', ondelete='CASCADE')) participant_id = db.Column( db.Integer, db.ForeignKey('participant.id', ondelete='CASCADE')) # this is set only for reply SMS messages. originating_message_id = db.Column( db.Integer, db.ForeignKey('message.id', ondelete='SET NULL')) deployment = db.relationship('Deployment', backref=db.backref('messages', cascade='all, delete', passive_deletes=True)) event = db.relationship('Event', backref=db.backref('messages', cascade='all, delete', passive_deletes=True)) submission = db.relationship('Submission', backref=db.backref('messages', passive_deletes=True)) participant = db.relationship('Participant', backref=db.backref('messages', passive_deletes=True)) originating_message = db.relationship('Message', uselist=False) __table_args__ = (db.Index('ix_text_tsv', sa.func.to_tsvector('english', text), postgresql_using='gin'), )
class User(BaseModel, UserMixin): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) email = db.Column(db.String, nullable=False) username = db.Column(db.String, nullable=False) password = db.Column(db.String, nullable=False) last_name = db.Column(db.String) first_name = db.Column(db.String) active = db.Column(db.Boolean, default=True) locale = db.Column(db.String) confirmed_at = db.Column(db.DateTime) current_login_at = db.Column(db.DateTime) last_login_at = db.Column(db.DateTime) current_login_ip = db.Column(db.String) last_login_ip = db.Column(db.String) login_count = db.Column(db.Integer) deployment = db.relationship('Deployment', backref=db.backref('users', cascade='all, delete', passive_deletes=True)) roles = db.relationship('Role', backref='users', secondary=roles_users) permissions = db.relationship('Permission', backref='users', secondary=users_permissions) def is_admin(self): role = Role.query.join(Role.users).filter( Role.deployment == self.deployment, Role.name == 'admin', Role.users.contains(self)).first() return bool(role)
class ParticipantDataField(Resource): __mapper_args__ = {'polymorphic_identity': 'participant_data_field'} __tablename__ = 'participant_data_field' id = db.Column(db.Integer, primary_key=True) participant_set_id = db.Column(db.Integer, db.ForeignKey('participant_set.id', ondelete='CASCADE'), nullable=False) name = db.Column(db.String, nullable=False) label = db.Column(db.String, nullable=False) visible_in_lists = db.Column(db.Boolean, default=False) resource_id = db.Column( db.Integer, db.ForeignKey('resource.resource_id', ondelete='CASCADE')) participant_set = db.relationship('ParticipantSet', backref=db.backref( 'extra_fields', cascade='all, delete-orphan', passive_deletes=True)) def __str__(self): return str( _('ParticipantDataField - %(name)s in %(participant_set)s', name=self.name, participant_set=self.participant_set.name))
class LocationTypePath(db.Model): __tablename__ = 'location_type_path' __table_args__ = (db.Index('location_type_paths_ancestor_idx', 'ancestor_id'), db.Index('location_type_paths_descendant_idx', 'descendant_id')) location_set_id = db.Column(db.Integer, db.ForeignKey('location_set.id', ondelete='CASCADE'), nullable=False) ancestor_id = db.Column(db.Integer, db.ForeignKey('location_type.id', ondelete='CASCADE'), primary_key=True) descendant_id = db.Column(db.Integer, db.ForeignKey('location_type.id', ondelete='CASCADE'), primary_key=True) depth = db.Column(db.Integer) location_set = db.relationship('LocationSet', backref=db.backref('location_type_paths', cascade='all, delete', passive_deletes=True))
class UserUpload(BaseModel): __tablename__ = 'user_upload' id = db.Column(db.Integer, primary_key=True) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'), nullable=False) created = db.Column(db.DateTime, default=current_timestamp) upload_filename = db.Column(db.String) deployment = db.relationship('Deployment', backref=db.backref('user_uploads', cascade='all, delete', passive_deletes=True)) user = db.relationship('User', backref=db.backref('uploads', cascade='all, delete', passive_deletes=True))
class ContactHistory(BaseModel): id = db.Column(db.Integer, nullable=False, primary_key=True) participant_id = db.Column(db.Integer, db.ForeignKey('participant.id', ondelete='CASCADE'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'), nullable=False) description = db.Column(db.String) created = db.Column(db.DateTime, nullable=False, default=utils.current_timestamp) participant = db.relationship('Participant', backref=db.backref('contact_history', cascade='all, delete', passive_deletes=True)) user = db.relationship('User', backref=db.backref('contact_history', cascade='all, delete', passive_deletes=True))
class ParticipantGroup(BaseModel): __tablename__ = 'participant_group' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) group_type_id = db.Column(db.Integer, db.ForeignKey('participant_group_type.id', ondelete='CASCADE'), nullable=False) participant_set_id = db.Column(db.Integer, db.ForeignKey('participant_set.id', ondelete='CASCADE'), nullable=False) group_type = db.relationship('ParticipantGroupType', backref=db.backref('participant_groups', cascade='all, delete')) participant_set = db.relationship('ParticipantSet', backref=db.backref( 'participant_groups', cascade='all, delete')) def __str__(self): return self.name or ''
class SubmissionComment(BaseModel): __tablename__ = 'submission_comment' id = db.Column(db.Integer, primary_key=True) submission_id = db.Column(db.Integer, db.ForeignKey('submission.id', ondelete='CASCADE'), nullable=False) submission = db.relationship('Submission', backref=db.backref('comments', cascade='all, delete', passive_deletes=True)) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) user = db.relationship('User', backref='submission_comments') comment = db.Column(db.String) submit_date = db.Column(db.DateTime, default=current_timestamp) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) deployment = db.relationship('Deployment', backref=db.backref('submission_comments', cascade='all, delete', passive_deletes=True))
class Sample(BaseModel): __tablename__ = 'sample' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) location_set_id = db.Column(db.Integer, db.ForeignKey('location_set.id', ondelete='CASCADE'), nullable=False) location_set = db.relationship('LocationSet', backref=db.backref('samples', cascade='all, delete', lazy='dynamic', passive_deletes=True))
class Permission(BaseModel): __tablename__ = 'permission' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) description = db.Column(db.String) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) deployment = db.relationship('Deployment', backref=db.backref('permissions', cascade='all, delete')) def __str__(self): return self.description if self.description else self.name
class Role(BaseModel, RoleMixin): __tablename__ = 'role' __table_args__ = (db.UniqueConstraint('deployment_id', 'name'), ) id = db.Column(db.Integer, primary_key=True) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) name = db.Column(db.String) description = db.Column(db.String) deployment = db.relationship('Deployment', backref=db.backref('roles', cascade='all, delete', passive_deletes=True)) permissions = db.relationship('Permission', backref='roles', secondary=roles_permissions) def __str__(self): return self.name or '' def get_by_name(self, name): return Role.query.filter_by(name=name).one_or_none()
class Submission(BaseModel): SUBMISSION_TYPES = ( ('O', _('Observer submission')), ('M', _('Master submission')), ) QUARANTINE_STATUSES = ( ('', _('None')), ('A', _('All')), ('R', _('Results')), ) INCIDENT_STATUSES = ((None, _('Unmarked')), ('citizen', _('Citizen Report')), ('confirmed', _('Confirmed')), ('rejected', _('Rejected'))) VERIFICATION_STATUSES = (('', _('Unconfirmed')), ('4', _('Confirmed'))) VERIFICATION_OPTIONS = {'VERIFIED': '4', 'REJECTED': '5'} __table_args__ = (db.Index('submission_data_idx', 'data', postgresql_using='gin'), ) __tablename__ = 'submission' id = db.Column(db.Integer, primary_key=True) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) event_id = db.Column(db.Integer, db.ForeignKey('event.id', ondelete='CASCADE'), nullable=False) form_id = db.Column(db.Integer, db.ForeignKey('form.id', ondelete='CASCADE'), nullable=False) participant_id = db.Column( db.Integer, db.ForeignKey('participant.id', ondelete='CASCADE')) location_id = db.Column(db.Integer, db.ForeignKey('location.id', ondelete='CASCADE'), nullable=False) data = db.Column(JSONB, default={}) extra_data = db.Column(JSONB) submission_type = db.Column(ChoiceType(SUBMISSION_TYPES), index=True) serial_no = db.Column(db.String, index=True) created = db.Column(db.DateTime, default=current_timestamp) updated = db.Column(db.DateTime, onupdate=current_timestamp, index=True) participant_updated = db.Column(db.DateTime, index=True) sender_verified = db.Column(db.Boolean, default=True) quarantine_status = db.Column(ChoiceType(QUARANTINE_STATUSES), default='') verification_status = db.Column(ChoiceType(VERIFICATION_STATUSES), default=VERIFICATION_STATUSES[0][0]) incident_description = db.Column(db.String) incident_status = db.Column(ChoiceType(INCIDENT_STATUSES)) overridden_fields = db.Column(ARRAY(db.String), default=[]) last_phone_number = db.Column(db.String) deployment = db.relationship('Deployment', backref=db.backref('submissions', cascade='all, delete', passive_deletes=True)) event = db.relationship('Event', backref=db.backref('submissions', cascade='all, delete', passive_deletes=True)) form = db.relationship('Form', backref=db.backref('submissions', cascade='all, delete', passive_deletes=True)) location = db.relationship('Location', backref=db.backref('submissions', cascade='all, delete', passive_deletes=True)) participant = db.relationship('Participant', backref=db.backref('submissions', cascade='all, delete', passive_deletes=True)) conflicts = db.Column(JSONB) unreachable = db.Column(db.Boolean, default=False, nullable=False) geom = db.Column(Geometry('POINT', srid=4326)) verified_fields = db.Column(JSONB, default=[]) @classmethod def init_submissions(cls, event, form, role, location_type, task=None): from apollo.participants.models import Participant if form.form_type != 'CHECKLIST': return location_set_id = location_type.location_set_id participant_set_id = role.participant_set_id if location_set_id != event.location_set_id: return if participant_set_id != event.participant_set_id: return deployment_id = event.deployment_id participants = Participant.query.filter_by(role_id=role.id) total_records = participants.count() processed_records = 0 error_records = 0 warning_records = 0 error_log = [] for participant in participants: if not participant.location_id: error_records += 1 error_log.append({ 'label': 'ERROR', 'message': gettext('Participant ID %(part_id)s has no location', part_id=participant.participant_id) }) continue if location_type.id == participant.location.location_type.id: location = participant.location else: try: location = next(a for a in participant.location.ancestors() if a.location_type.id == location_type.id) if not location: error_records = total_records - processed_records if task: task.update_task_info( total_records=total_records, processed_records=processed_records, error_records=error_records, warning_records=warning_records, error_log=error_log) return except StopIteration: error_records = total_records - processed_records if task: task.update_task_info( total_records=total_records, processed_records=processed_records, error_records=error_records, warning_records=warning_records, error_log=error_log) return obs_submission = cls.query.filter_by(form_id=form.id, participant_id=participant.id, location_id=location.id, deployment_id=deployment_id, event_id=event.id, submission_type='O').first() if not obs_submission: obs_submission = cls(form_id=form.id, participant_id=participant.id, location_id=location.id, deployment_id=deployment_id, event_id=event.id, submission_type='O', data={}) obs_submission.save() master_submission = cls.query.filter_by( form_id=form.id, participant_id=None, location_id=location.id, deployment_id=deployment_id, event_id=event.id, submission_type='M').first() if not master_submission: master_submission = cls(form_id=form.id, participant_id=None, location_id=location.id, deployment_id=deployment_id, event_id=event.id, submission_type='M', data={}) master_submission.save() processed_records += 1 if task: task.update_task_info(total_records=total_records, processed_records=processed_records, error_records=error_records, warning_records=warning_records, error_log=error_log) def update_related(self, data): ''' Given a dict used to update the submission, update the master with the data not in conflict, and update all related submissions with the conflict data ''' if self.form.form_type == 'INCIDENT': return if self.form.untrack_data_conflicts: return combined_data = self.data combined_data.update(data) if self.quarantine_status == 'A': conflict_tags = [] subset = {} elif self.quarantine_status == 'R': conflict_tags = self.compute_conflict_tags( set(combined_data.keys()).difference(set(self.form.vote_tags))) subset = { k: v for k, v in combined_data.items() if k not in conflict_tags and k not in self.form.vote_tags } else: conflict_tags = self.compute_conflict_tags(combined_data.keys()) subset = { k: v for k, v in combined_data.items() if k not in conflict_tags } subset_keys = set(subset.keys()) master = self.master siblings = self.siblings self.conflicts = trim_conflicts(self, conflict_tags, combined_data.keys()) master.conflicts = trim_conflicts(master, conflict_tags, combined_data.keys()) for sibling in siblings: sibling_data_keys = set(sibling.data.keys()) if sibling.quarantine_status == 'A': conflict_tags = [] elif sibling.quarantine_status == 'R': conflict_tags = sibling.compute_conflict_tags( set(combined_data.keys()).difference( set(self.form.vote_tags))) subset.update({ k: sibling.data[k] for k in sibling_data_keys.difference(subset_keys) if k not in conflict_tags and k not in self.form.vote_tags }) else: if self.quarantine_status == 'R': conflict_tags = sibling.compute_conflict_tags( set(combined_data.keys()).difference( set(self.form.vote_tags))) else: conflict_tags = sibling.compute_conflict_tags( combined_data.keys()) subset.update({ k: sibling.data[k] for k in sibling_data_keys.difference(subset_keys) if k not in conflict_tags }) sibling.conflicts = trim_conflicts(sibling, conflict_tags, combined_data.keys()) for key in master.overridden_fields: if key in master.data: subset[key] = master.data[key] else: subset.pop(key, None) master.data = subset master.participant_updated = self.participant_updated db.session.begin(nested=True) db.session.add_all([self, master]) db.session.add_all(siblings) db.session.commit() def update_master_offline_status(self): siblings = self.siblings master_offline_status = self.master.unreachable if siblings: if (all([s.unreachable for s in self.siblings]) and self.unreachable): self.master.unreachable = True else: self.master.unreachable = False else: self.master.unreachable = self.unreachable # if the offline status changed in any way if master_offline_status != self.master.unreachable: db.session.add(self.master) def compute_conflict_tags(self, tags=None): # don't compute if the 'track conflicts' flag is not set # on the form if self.form.untrack_data_conflicts: return [] # or if the form is an incident form if self.form.form_type == 'INCIDENT': return [] # check only a subset of the tags tags_to_check = set(self.form.tags) - set(self.overridden_fields) if tags: tags_to_check = tags_to_check.intersection(set(tags)) tags_to_check_non_votes = tags_to_check.difference( set(self.form.vote_tags)) # conflict query any_conflict_query = [ sa.func.bool_or( sa.and_( Submission.data.has_key(tag), # noqa sa.not_(Submission.data.contains({tag: self.data[tag] })))).label(tag) for tag in tags_to_check if tag in self.data ] non_vote_conflict_query = [ sa.func.bool_or( sa.and_( Submission.data.has_key(tag), # noqa sa.not_(Submission.data.contains({tag: self.data[tag] })))).label(tag) for tag in tags_to_check_non_votes if tag in self.data ] if not any_conflict_query: return [] siblings_no_quarantine = self.__siblings().filter( Submission.quarantine_status == '') siblings_with_results_quarantine = self.__siblings().filter( Submission.quarantine_status == 'R') results_conflict = siblings_no_quarantine.with_entities( *any_conflict_query) results_vote_conflict = siblings_with_results_quarantine.with_entities( *non_vote_conflict_query) any_conflict = { k for result in results_conflict for k, v in result._asdict().items() if v } vote_conflict = { k for result in results_vote_conflict for k, v in result._asdict().items() if v } return any_conflict.union(vote_conflict) def get_incident_status_display(self): d = dict(self.INCIDENT_STATUSES) return d.get(self.incident_status, _('Unmarked')) def _compute_completion(self, group_tags): are_empty_values = [ # TODO: check the empty values self.data.get(tag) not in (None, '', []) for tag in group_tags ] if self.data else [] if are_empty_values and all(are_empty_values): return 'Complete' elif any(are_empty_values): return 'Partial' else: return 'Missing' def completion(self, group_name): group_tags = self.form.get_group_tags(group_name) if (self.form.form_type == 'INCIDENT' or self.form.untrack_data_conflicts): return self._compute_completion(group_tags) else: conflict_tags = set(group_tags).intersection( set(self.conflicts or [])) if conflict_tags: return 'Conflict' return self._compute_completion(group_tags) def __siblings(self): '''Returns siblings as a SQLA query object''' return Submission.query.filter( Submission.deployment_id == self.deployment_id, Submission.event_id == self.event_id, Submission.form_id == self.form_id, Submission.serial_no == self.serial_no, Submission.location_id == self.location_id, Submission.submission_type == 'O', Submission.id != self.id) def __master(self): return Submission.query.filter( Submission.deployment_id == self.deployment_id, Submission.event_id == self.event_id, Submission.form_id == self.form_id, Submission.serial_no == self.serial_no, Submission.location_id == self.location_id, Submission.submission_type == 'M').first() @property def siblings(self): '''Returns siblings as POPOs''' if not hasattr(self, '_siblings'): self._siblings = self.__siblings().all() return self._siblings @property def master(self): if self.submission_type == 'M': return self if self.form.form_type == 'INCIDENT': self._master = None if not hasattr(self, '_master'): self._master = Submission.query.filter( Submission.deployment_id == self.deployment_id, Submission.event_id == self.event_id, Submission.form_id == self.form_id, Submission.location_id == self.location_id, Submission.submission_type == 'M').first() return self._master
class Participant(BaseModel): GENDER = ( ('', _('Unspecified')), ('F', _('Female')), ('M', _('Male')), ) __tablename__ = 'participant' id = db.Column(db.Integer, primary_key=True) full_name_translations = db.Column(JSONB) first_name_translations = db.Column(JSONB) other_names_translations = db.Column(JSONB) last_name_translations = db.Column(JSONB) participant_id = db.Column(db.String) role_id = db.Column( db.Integer, db.ForeignKey('participant_role.id', ondelete='SET NULL')) partner_id = db.Column( db.Integer, db.ForeignKey('participant_partner.id', ondelete='SET NULL')) supervisor_id = db.Column( db.Integer, db.ForeignKey('participant.id', ondelete='SET NULL')) gender = db.Column(sqlalchemy_utils.ChoiceType(GENDER)) email = db.Column(db.String) location_id = db.Column(db.Integer, db.ForeignKey('location.id', ondelete='CASCADE')) participant_set_id = db.Column(db.Integer, db.ForeignKey('participant_set.id', ondelete='CASCADE'), nullable=False) message_count = db.Column(db.Integer, default=0) accurate_message_count = db.Column(db.Integer, default=0) completion_rating = db.Column(db.Float, default=1) device_id = db.Column(db.String) password = db.Column(db.String) extra_data = db.Column(JSONB) full_name = translation_hybrid(full_name_translations) first_name = translation_hybrid(first_name_translations) other_names = translation_hybrid(other_names_translations) last_name = translation_hybrid(last_name_translations) location = db.relationship('Location', backref='participants') participant_set = db.relationship('ParticipantSet', backref=db.backref('participants', cascade='all, delete', lazy='dynamic', passive_deletes=True)) groups = db.relationship('ParticipantGroup', secondary=groups_participants, backref='participants') role = db.relationship('ParticipantRole', backref='participants') partner = db.relationship('ParticipantPartner', backref='participants') phone_contacts = db.relationship('PhoneContact', backref=db.backref('participants', cascade='all, delete')) supervisor = db.relationship('Participant', remote_side=id) def __str__(self): return self.name or '' @property def primary_phone(self): if not self.id: return None p_phone = PhoneContact.query.filter_by( participant_id=self.id, verified=True).order_by(PhoneContact.updated.desc()).first() return p_phone.number if p_phone else None @property def other_phones(self): if not self.id: return None phone_primary = PhoneContact.query.filter_by( participant_id=self.id, verified=True).order_by(PhoneContact.updated.desc()).first() if phone_primary: other_phones = PhoneContact.query.filter( PhoneContact.participant_id == self.id, # noqa PhoneContact.id != phone_primary.id, # noqa PhoneContact.verified == True # noqa ).order_by(PhoneContact.updated.desc()).with_entities( PhoneContact.number).all() return list(chain(*other_phones)) return [] @property def phones(self): if not self.id: return None if not hasattr(self, '_phones'): phones = PhoneContact.query.filter_by( participant_id=self.id).order_by(PhoneContact.updated).all() self._phones = phones return self._phones @property def gender_display(self): if not self.gender: return Participant.GENDER[0][1] d = dict(Participant.GENDER) return d.get(self.gender, Participant.GENDER[0][1]) @property def last_contacted(self): contact = ContactHistory.query.filter( ContactHistory.participant == self).order_by( ContactHistory.created.desc()).first() if contact: return contact.created else: return None @property def name(self): if self.full_name: return self.full_name names = [self.first_name, self.other_names, self.last_name] names = [n for n in names if n] return ' '.join(names)
def deployment(self): return db.relationship('Deployment', backref=db.backref('resources', cascade='all, delete', passive_deletes=True))
class ParticipantSet(BaseModel): __tablename__ = 'participant_set' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) slug = db.Column(db.String) location_set_id = db.Column(db.Integer, db.ForeignKey('location_set.id', ondelete='CASCADE'), nullable=False) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) deployment = db.relationship('Deployment', backref=db.backref('participant_sets', cascade='all, delete', passive_deletes=True)) location_set = db.relationship('LocationSet', backref=db.backref('participant_sets', cascade='all, delete', passive_deletes=True)) gender_hidden = db.Column(db.Boolean, default=False) role_hidden = db.Column(db.Boolean, default=False) partner_hidden = db.Column(db.Boolean, default=False) def __str__(self): return self.name or '' def get_import_fields(self): fields = { 'role': _('Role'), 'sample': _('Sample'), 'id': _('Participant ID'), 'supervisor': _('Supervisor ID'), 'phone': _('Phone'), 'partner': _('Partner'), 'location': _('Location code'), 'group': _('Group'), 'gender': _('Gender'), 'email': _('Email'), 'password': _('Password') } for locale, language in self.deployment.languages.items(): fields[f'full_name_{locale}'] = _('Full Name (%(language)s)', language=language) for locale, language in self.deployment.languages.items(): fields[f'first_name_{locale}'] = _('First Name (%(language)s)', language=language) for locale, language in self.deployment.languages.items(): fields[f'other_names_{locale}'] = _('Other Names (%(language)s)', language=language) for locale, language in self.deployment.languages.items(): fields[f'last_name_{locale}'] = _('Last Name (%(language)s)', language=language) extra_fields = ParticipantDataField.query.filter_by( participant_set_id=self.id).all() for ex_field in extra_fields: fields[ex_field.id] = ex_field.label return fields
class Location(BaseModel): __tablename__ = 'location' __table_args__ = (db.UniqueConstraint('location_set_id', 'code'), ) id = db.Column(db.Integer, primary_key=True) name_translations = db.Column(JSONB) code = db.Column(db.String, index=True, nullable=False) registered_voters = db.Column(db.Integer, default=0) location_set_id = db.Column(db.Integer, db.ForeignKey('location_set.id', ondelete='CASCADE'), nullable=False) location_type_id = db.Column(db.Integer, db.ForeignKey('location_type.id', ondelete='CASCADE'), nullable=False) geom = db.Column(Geometry('POINT', srid=4326)) extra_data = db.Column(JSONB) name = translation_hybrid(name_translations) location_set = db.relationship('LocationSet', backref=db.backref('locations', cascade='all, delete', lazy='dynamic')) location_type = db.relationship('LocationType', backref=db.backref('locations', cascade='all, delete', passive_deletes=True)) samples = db.relationship('Sample', backref='locations', secondary=samples_locations) ancestor_paths = db.relationship( 'LocationPath', order_by='desc(LocationPath.depth)', primaryjoin='Location.id == LocationPath.descendant_id', backref='descendant_location') descendant_paths = db.relationship( 'LocationPath', order_by='LocationPath.depth', primaryjoin='Location.id == LocationPath.ancestor_id', backref='ancestor_location') def ancestors(self): return [ p.ancestor_location for p in self.ancestor_paths if p.depth != 0 ] def parents(self): return [ p.ancestor_location for p in self.ancestor_paths if p.depth == 1 ] def descendants(self): return [ p.descendant_location for p in self.descendant_paths if p.depth != 0 ] def children(self): return [ p.descendant_location for p in self.descendant_paths if p.depth == 1 ] @classmethod def root(cls, location_set_id): anc = aliased(LocationPath) q = LocationPath.query.with_entities( LocationPath.descendant_id).filter_by( depth=0, location_set_id=location_set_id).outerjoin( anc, and_(anc.descendant_id == LocationPath.descendant_id, anc.ancestor_id != LocationPath.ancestor_id)).filter( anc.ancestor_id == None) # noqa return cls.query.filter( cls.id.in_(q), cls.location_set_id == location_set_id).first() def make_path(self): self._cached_path = getattr(self, '_cached_path', None) if not self._cached_path: data = { ans.location_type.name: ans.name for ans in self.ancestors() } data.update({self.location_type.name: self.name}) self._cached_path = data return self._cached_path def __repr__(self): return self.name
class LocationType(BaseModel): __tablename__ = 'location_type' id = db.Column(db.Integer, primary_key=True) name_translations = db.Column(JSONB) is_administrative = db.Column(db.Boolean, default=False) is_political = db.Column(db.Boolean, default=False) has_registered_voters = db.Column(db.Boolean, default=False) slug = db.Column(db.String) location_set_id = db.Column(db.Integer, db.ForeignKey('location_set.id', ondelete='CASCADE'), nullable=False) has_coordinates = db.Column(db.Boolean, default=False, server_default=false()) name = translation_hybrid(name_translations) location_set = db.relationship('LocationSet', backref=db.backref('location_types', cascade='all, delete', lazy='dynamic', passive_deletes=True)) ancestor_paths = db.relationship( 'LocationTypePath', order_by='desc(LocationTypePath.depth)', primaryjoin='LocationType.id == LocationTypePath.descendant_id', backref='descendant_location_type') descendant_paths = db.relationship( 'LocationTypePath', order_by='LocationTypePath.depth', primaryjoin='LocationType.id == LocationTypePath.ancestor_id', backref='ancestor_location_type') def __str__(self): return self.name or '' def ancestors(self): return [ p.ancestor_location_type for p in self.ancestor_paths if p.depth != 0 ] def descendants(self): return [ p.descendant_location_type for p in self.descendant_paths if p.depth != 0 ] def children(self): return [ p.descendant_location_type for p in self.descendant_paths if p.depth == 1 ] @classmethod def root(cls, location_set_id): anc = aliased(LocationTypePath) q = LocationTypePath.query.with_entities( LocationTypePath.descendant_id).filter_by( depth=0, location_set_id=location_set_id).outerjoin( anc, and_(anc.descendant_id == LocationTypePath.descendant_id, anc.ancestor_id != LocationTypePath.ancestor_id)).filter( anc.ancestor_id == None) # noqa return cls.query.filter( cls.id.in_(q), cls.location_set_id == location_set_id).first()
class LocationSet(BaseModel): __tablename__ = 'location_set' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) slug = db.Column(db.String) deployment_id = db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False) deployment = db.relationship('Deployment', backref=db.backref('location_sets', cascade='all, delete', passive_deletes=True)) def __str__(self): return self.name or '' def make_admin_divisions_graph(self): edges = LocationTypePath.query.filter( LocationTypePath.location_set_id == self.id, LocationTypePath.depth == 1).with_entities( LocationTypePath.ancestor_id, LocationTypePath.descendant_id).all() di_graph = nx.DiGraph() di_graph.add_edges_from(edges) sorted_nodes = list(nx.topological_sort(di_graph)) sorted_edges = list(di_graph.edges(sorted_nodes)) location_types = [ LocationType.query.filter(LocationType.location_set_id == self.id, LocationType.id == _id).one() for _id in sorted_nodes ] nodes = [{ 'id': lt.id, 'name': lt.name, 'nameTranslations': {locale: tr for locale, tr in lt.name_translations.items()} if lt.name_translations else {}, 'has_registered_voters': lt.has_registered_voters, 'has_coordinates': lt.has_coordinates, 'is_administrative': lt.is_administrative, 'is_political': lt.is_political, } for lt in location_types] graph = {'nodes': nodes, 'edges': sorted_edges} return graph def get_import_fields(self): fields = {} extra_fields = LocationDataField.query.filter_by( location_set_id=self.id).all() location_types = LocationTypePath.query.filter_by( location_set=self).join( LocationType, LocationType.id == LocationTypePath.ancestor_id).with_entities( LocationType).group_by( LocationTypePath.ancestor_id, LocationType.id).order_by( func.count(LocationTypePath.ancestor_id).desc(), LocationType.name).all() for lt in location_types: lt_data = {} for locale, language in self.deployment.languages.items(): lt_data[f'{lt.id}_name_{locale}'] = _( '%(location_type)s Name (%(language)s)', location_type=lt.name, language=language) lt_data['{}_code'.format(lt.id)] = _('%(location_type)s Code', location_type=lt.name) if lt.has_coordinates: lt_data['{}_lat'.format(lt.id)] = _( '%(location_type)s Latitude', location_type=lt.name) lt_data['{}_lon'.format(lt.id)] = _( '%(location_type)s Longitude', location_type=lt.name) for ex_field in extra_fields: lt_data['{}:{}'.format(lt.id, ex_field.id)] = _( '%(location_type)s %(field_label)s', location_type=lt.name, field_label=ex_field.label) if lt.has_registered_voters: lt_data['{}_rv'.format(lt.id)] = \ _('%(location_type)s registered voters', location_type=lt.name) fields.update(lt_data) return fields @property def events(self): return { event for participant_set in self.participant_sets for event in participant_set.events }