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'), )
