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 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 ParticipantGroupType(BaseModel): __tablename__ = 'participant_group_type' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) participant_set_id = db.Column(db.Integer, db.ForeignKey('participant_set.id', ondelete='CASCADE'), nullable=False) participant_set = db.relationship('ParticipantSet', backref=db.backref( 'participant_group_types', cascade='all, delete')) def __str__(self): return self.name or ''
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 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 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 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 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 PhoneContact(BaseModel): __tablename__ = 'phone_contact' id = db.Column(db.Integer, nullable=False, primary_key=True) participant_id = db.Column(db.Integer, db.ForeignKey('participant.id', ondelete='CASCADE'), nullable=False) number = db.Column(db.String, nullable=False) created = db.Column(db.DateTime, nullable=False, default=utils.current_timestamp) updated = db.Column(db.DateTime, nullable=False, default=utils.current_timestamp, onupdate=utils.current_timestamp) verified = db.Column(db.Boolean, default=False) participant = db.relationship('Participant')
class Event(Resource): __mapper_args__ = {'polymorphic_identity': 'event'} __tablename__ = 'event' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) start = db.Column(db.DateTime(timezone=True), default=_default_event_start, nullable=False) end = db.Column(db.DateTime(timezone=True), default=_default_event_end, nullable=False) resource_id = db.Column( db.Integer, db.ForeignKey('resource.resource_id', ondelete='CASCADE')) location_set_id = db.Column( db.Integer, db.ForeignKey('location_set.id', ondelete='SET NULL')) participant_set_id = db.Column( db.Integer, db.ForeignKey('participant_set.id', ondelete='SET NULL')) location_set = db.relationship('LocationSet', backref='events') participant_set = db.relationship('ParticipantSet', backref='events') def __str__(self): return gettext('Event - %(name)s', name=self.name)
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 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)
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
def deployment_id(self): return db.Column(db.Integer, db.ForeignKey('deployment.id', ondelete='CASCADE'), nullable=False)
# -*- coding: utf-8 -*- from flask_security import RoleMixin, UserMixin from apollo.core import db from apollo.dal.models import BaseModel from apollo.utils import current_timestamp roles_users = db.Table( 'roles_users', db.Column('user_id', db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'), primary_key=True), db.Column('role_id', db.Integer, db.ForeignKey('role.id', ondelete='CASCADE'), primary_key=True)) roles_permissions = db.Table( 'roles_permissions', db.Column('role_id', db.Integer, db.ForeignKey('role.id', ondelete='CASCADE'), primary_key=True), db.Column('permission_id', db.Integer, db.ForeignKey('permission.id', ondelete='CASCADE'), primary_key=True)) users_permissions = db.Table( 'users_permissions',
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 }
return fields @property def events(self): return { event for participant_set in self.participant_sets for event in participant_set.events } samples_locations = db.Table( 'samples_locations', db.Column('sample_id', db.Integer, db.ForeignKey('sample.id', ondelete='CASCADE'), primary_key=True), db.Column('location_id', db.Integer, db.ForeignKey('location.id', ondelete='CASCADE'), primary_key=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'),
def resource_id(self): return db.Column(db.Integer, autoincrement=True, nullable=False, primary_key=True)
def uuid(self): return db.Column(UUID(as_uuid=True), default=uuid4, nullable=False)
) logger = logging.getLogger(__name__) gt_constraint_regex = re.compile(r'(?:.*\.\s*\>={0,1}\s*)(\d+)') lt_constraint_regex = re.compile(r'(?:.*\.\s*\<={0,1}\s*)(\d+)') def _make_version_identifer(): return datetime.utcnow().strftime('%Y%m%d%H%M%S%f') events_forms = db.Table( 'events_forms', db.Column( 'event_id', db.Integer, db.ForeignKey('event.id', ondelete='CASCADE'), primary_key=True), db.Column( 'form_id', db.Integer, db.ForeignKey('form.id', ondelete='CASCADE'), primary_key=True) ) class Form(Resource): FORM_TYPES = ( ('CHECKLIST', _('Checklist Form')), ('INCIDENT', _('Incident Form')), ('SURVEY', _('Survey Form')) ) __mapper_args__ = {'polymorphic_identity': 'form'}
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
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)) groups_participants = db.Table( 'participant_groups_participants', db.Column('group_id', db.Integer, db.ForeignKey('participant_group.id', ondelete='CASCADE'), nullable=False), db.Column('participant_id', db.Integer, db.ForeignKey('participant.id', ondelete='CASCADE'), nullable=False)) class ParticipantRole(BaseModel): __tablename__ = 'participant_role' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) participant_set_id = db.Column(db.Integer, db.ForeignKey('participant_set.id', ondelete='CASCADE'),
class Form(Resource): FORM_TYPES = ( ('CHECKLIST', _('Checklist Form')), ('INCIDENT', _('Incident Form')), ('SURVEY', _('Survey Form')) ) __mapper_args__ = {'polymorphic_identity': 'form'} __tablename__ = 'form' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) prefix = db.Column(db.String, nullable=False) form_type = db.Column(ChoiceType(FORM_TYPES), nullable=False) require_exclamation = db.Column(db.Boolean, default=True) untrack_data_conflicts = db.Column(db.Boolean, default=False, nullable=False) # noqa data = db.Column(JSONB) version_identifier = db.Column( db.String, default=_make_version_identifer, onupdate=_make_version_identifer) resource_id = db.Column( db.Integer, db.ForeignKey('resource.resource_id', ondelete='CASCADE')) quality_checks = db.Column(NestedMutableJson) party_mappings = db.Column(JSONB) calculate_moe = db.Column(db.Boolean) accredited_voters_tag = db.Column(db.String) quality_checks_enabled = db.Column(db.Boolean, default=False) invalid_votes_tag = db.Column(db.String) registered_voters_tag = db.Column(db.String) blank_votes_tag = db.Column(db.String) vote_shares = db.Column(JSONB) show_moment = db.Column(db.Boolean, default=False) show_map = db.Column(db.Boolean, default=False) show_progress = db.Column(db.Boolean, default=False) events = db.relationship('Event', backref='forms', secondary=events_forms) def __str__(self): return gettext('Form - %(name)s', name=self.name) def _populate_field_cache(self): if self.data: self._field_cache = { f['tag']: f for g in self.data.get('groups', []) for f in g.get('fields', []) } else: self._field_cache = {} def _populate_group_cache(self): if self.data: self._group_cache = { g['name']: g for g in self.data.get('groups', []) } else: self._group_cache = {} def get_form_type_display(self): d = dict(Form.FORM_TYPES) return d[self.form_type] @property def tags(self): if not hasattr(self, '_field_cache'): self._populate_field_cache() return sorted(self._field_cache.keys()) @property def vote_tags(self): if not hasattr(self, '_field_cache'): self._populate_field_cache() return sorted([ key for key in self._field_cache.keys() if self._field_cache[key]['analysis_type'] == 'RESULT']) def get_field_by_tag(self, tag): if not hasattr(self, '_field_cache'): self._populate_field_cache() return self._field_cache.get(tag) def get_group_tags(self, group_name): if not hasattr(self, '_group_cache'): self._populate_group_cache() grp = self._group_cache.get(group_name) tags = [] if grp and grp.get('fields'): for f in grp.get('fields'): if f and f.get('tag'): tags.append(f.get('tag')) return tags def to_xml(self): root = HTML_E.html() head = HTML_E.head(HTML_E.title(self.name)) data = E.data(id=self.uuid.hex) model = E.model(E.instance(data)) body = HTML_E.body() model.append(E.bind(nodeset='/data/form_id', readonly='true()')) model.append(E.bind(nodeset='/data/version_id', readonly='true()')) form_id = etree.Element('form_id') form_id.text = str(self.id) data.append(form_id) data.append(E.device_id()) data.append(E.subscriber_id()) data.append(E.phone_number()) data.attrib['{{{}}}version'.format(NSMAP['orx'])] = \ self.version_identifier device_id_bind = E.bind(nodeset='/data/device_id') device_id_bind.attrib['{{{}}}preload'.format(NSMAP['jr'])] = \ 'property' device_id_bind.attrib['{{{}}}preloadParams'.format(NSMAP['jr'])] = \ 'deviceid' subscriber_id_bind = E.bind(nodeset='/data/subscriber_id') subscriber_id_bind.attrib['{{{}}}preload'.format(NSMAP['jr'])] = 'property' # noqa subscriber_id_bind.attrib['{{{}}}preloadParams'.format(NSMAP['jr'])] = 'subscriberid' # noqa phone_number_bind = E.bind(nodeset='/data/phone_number') phone_number_bind.attrib['{{{}}}preload'.format(NSMAP['jr'])] = 'property' # noqa phone_number_bind.attrib['{{{}}}preloadParams'.format(NSMAP['jr'])] = 'phonenumber' # noqa model.append(device_id_bind) model.append(subscriber_id_bind) model.append(phone_number_bind) if self.form_type == 'SURVEY': description = gettext('Form Serial Number') path = '/data/form_serial' data.append(etree.Element('form_serial')) model.append(E.bind(nodeset=path, type='string')) grp_element = E.group(E.label(description)) field_element = E.input(E.label(description), ref='form_serial') grp_element.append(field_element) body.append(grp_element) if self.data: for group in self.data.get('groups'): grp_element = E.group(E.label(group['name'])) for field in group['fields']: data.append(etree.Element(field['tag'])) path = '/data/{}'.format(field['tag']) field_type = field.get('type') if field_type == 'boolean': field_element = E.select1( E.label(field['description']), E.item(E.label('True'), E.value('1')), E.item(E.label('False'), E.value('0')), ref=field['tag'] ) model.append(E.bind(nodeset=path, type='select1')) elif field_type == 'location': field_element = E.input( E.label(field['description']), ref=field['tag']) model.append(E.bind(nodeset=path, type='geopoint')) elif field_type in ('comment', 'string'): field_element = E.input( E.label(field['description']), ref=field['tag'] ) model.append(E.bind(nodeset=path, type='string')) elif field_type == 'integer': field_element = E.input( E.label(field['description']), ref=field['tag'] ) model.append(E.bind( nodeset=path, type='integer', constraint='. >= {} and . <= {}'.format( field.get('min', 0), field.get('max', 9999) ))) elif field_type in ('select', 'multiselect', 'category'): sorted_options = sorted(field.get('options').items(), key=itemgetter(1)) if field_type == 'select' or field_type == 'category': element_factory = E.select1 model.append(E.bind(nodeset=path, type='select1')) else: element_factory = E.select model.append(E.bind(nodeset=path, type='select')) field_element = element_factory( E.label(field['description']), ref=field['tag'] ) for key, value in sorted_options: field_element.append( E.item(E.label(key), E.value(str(value))) ) else: continue grp_element.append(field_element) body.append(grp_element) # hard coding a location question here until the form builder # gets updated. please remove once the form builder supports # locations description = gettext('Location') path = '/data/location' data.append(etree.Element('location')) model.append(E.bind(nodeset=path, type='geopoint')) grp_element = E.group(E.label(description)) field_element = E.input(E.label(description), ref='location') grp_element.append(field_element) body.append(grp_element) head.append(model) root.append(head) root.append(body) return root def odk_hash(self): xform_data = etree.tostring( self.to_xml(), encoding='UTF-8', xml_declaration=True) hash_engine = hashlib.md5() hash_engine.update(xform_data) return f'md5: {hash_engine.hexdigest()}'
class Deployment(BaseModel): __tablename__ = 'deployment' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) hostnames = db.Column(ARRAY(db.String), nullable=False) allow_observer_submission_edit = db.Column(db.Boolean, default=True) logo = db.Column(db.String) include_rejected_in_votes = db.Column(db.Boolean, default=False) is_initialized = db.Column(db.Boolean, default=False) dashboard_full_locations = db.Column(db.Boolean, default=True) enable_partial_response_for_messages = db.Column(db.Boolean, default=True) primary_locale = db.Column(db.String) other_locales = db.Column(ARRAY(db.String)) @classmethod def find_by_hostname(cls, hostname): return cls.query.filter(cls.hostnames.any(hostname)).first() def __repr__(self): return str(self) def __str__(self): return self.name or '' @property def locale_codes(self): locales = [self.primary_locale] if self.primary_locale else ['en'] if self.other_locales: locales.extend(self.other_locales) return locales @property def languages(self): return { locale: name for locale, name in LANGUAGES.items() if locale in self.locale_codes }
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
def resource_type(self): return db.Column(db.String, nullable=False)
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 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'), )