class TrainingAlgorithm(db.Model):
__tablename__ = "training_algorithm"
training_algorithm_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(200))
scope = db.Column(db.Enum(Scope))
ml_engine = db.Column(db.Enum(ModelMlEngine))
author = db.Column(db.String(50))
creation_timestamp = db.Column(db.DateTime, default=datetime.now)
external = db.Column(db.Boolean, default=False)
file_name = db.Column(db.String(100))
output_file_name = db.Column(db.String(100))
class UserToken(db.Model):
__tablename__ = 'user_token'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
user_id = db.Column(db.Integer, index=True)
token_type = db.Column(db.Enum(('access', 'refresh')), nullable=False)
token = db.Column(db.String(130))
created_at = db.Column(db.DateTime, default=datetime.utcnow)
class Role(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Enum(RoleEnum), nullable=False)
@staticmethod
def schema():
return RoleSchema
class Workshop(db.Model):
__tablename__ = "workshop"
id = db.Column(db.Integer, primary_key=True)
workshop_name = db.Column(db.String(64))
workshop_category = db.Column(
db.Enum(
"Academy", "DSS", "Corporate", "Weekend", "Others", name="workshop_category"
),
nullable=False,
)
workshop_instructor = db.Column(
db.Integer, db.ForeignKey("employee.id"), nullable=False
)
workshop_start = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
workshop_hours = db.Column(db.Integer, nullable=False)
workshop_venue = db.Column(db.String(64), nullable=False)
class_size = db.Column(db.Integer, nullable=False)
responses = db.relationship("Response", backref="workshop", lazy="dynamic")
def __repr__(self):
past = arrow.get(self.workshop_start).humanize()
# return '{} on {}'.format(self.workshop_name, self.workshop_start)
return "{}, {}".format(self.workshop_name, past)
def printtime(self):
past = arrow.get(self.workshop_start).humanize()
return past
class Patient(db.Model):
patientId = db.Column(db.String(50), primary_key=True)
patientName = db.Column(db.String(50))
patientSex = db.Column(db.Enum(SexEnum), nullable=False)
isPregnant = db.Column(db.Boolean)
gestationalAgeUnit = db.Column(db.String(50))
gestationalTimestamp = db.Column(db.BigInteger)
medicalHistory = db.Column(db.Text)
drugHistory = db.Column(db.Text)
zone = db.Column(db.String(20))
dob = db.Column(db.Date)
isExactDob = db.Column(db.Boolean)
villageNumber = db.Column(db.String(50))
householdNumber = db.Column(db.String(50))
created = db.Column(db.BigInteger, nullable=False, default=get_current_time)
lastEdited = db.Column(
db.BigInteger,
nullable=False,
default=get_current_time,
onupdate=get_current_time,
)
def as_dict(self):
return {c.name: str(getattr(self, c.name)) for c in self.__table__.columns}
@staticmethod
def schema():
return PatientSchema
class Patient(db.Model):
patientId = db.Column(db.String(50), primary_key=True)
patientName = db.Column(db.String(50))
patientAge = db.Column(db.Integer)
patientSex = db.Column(db.Enum(SexEnum), nullable=False)
isPregnant = db.Column(db.Boolean)
gestationalAgeUnit = db.Column(db.String(50))
gestationalAgeValue = db.Column(db.String(20))
medicalHistory = db.Column(db.Text)
drugHistory = db.Column(db.Text)
zone = db.Column(db.String(20))
dob = db.Column(db.BigInteger)
villageNumber = db.Column(db.String(50))
# FOREIGN KEYS
# villageNumber = db.Column(db.String(50), db.ForeignKey('village.villageNumber'))
# RELATIONSHIPS
# village = db.relationship('Village', backref=db.backref('patients', lazy=True))
def as_dict(self):
return {
c.name: str(getattr(self, c.name))
for c in self.__table__.columns
}
class Product(db.Model):
__tablename__ = 'product'
id = db.Column(u'id', db.INTEGER(), primary_key=True, nullable=False)
vendor_id = db.Column(db.Integer,
db.ForeignKey("vendor.id"),
nullable=False)
created_at = db.Column(u'created_at', db.DATE(), nullable=False)
title = db.Column(u'title', db.VARCHAR(length=128), nullable=False)
listing_type = db.Column(u'listing_type',
db.Enum(u'saas', u'ami'),
default=u'saas',
nullable=False)
price = db.Column(u'price', db.INTEGER(), nullable=False)
def __init__(self, **kwargs):
for key, value in kwargs.items():
setattr(self, key, value)
self.created_at = datetime.utcnow()
def __repr__(self):
return "<Product('%d', '%s')>" % (self.id, self.title)
def __iter__(self) -> Generator:
yield ('id', self.id)
yield ('title', self.title)
yield ('listingType', self.listing_type)
yield ('price', self.price)
orders = db.relation('Order', primaryjoin="Order.product_id==Product.id")
class PArticle(db.Model):
__tablename__ = 'particle'
id = db.Column('id', db.INTEGER, primary_key=True)
article_id = db.Column('article_id', db.ForeignKey(Article.id))
article = db.relationship(Article)
name = db.Column('name', db.VARCHAR(20), nullable=False)
text = db.Column('text', db.TEXT(2000), nullable=False)
status = db.Column('status', db.Enum(StatusEnum), nullable=False)
class OfficeModel(db.Model):
__tablename__ = 'office'
officeId = db.Column(db.Integer, primary_key=True, autoincrement=True)
officeType = db.Column(db.Enum(OfficeTypeEnum))
electionId = db.Column(db.Integer, db.ForeignKey("election.electionId"))
parentOfficeId = db.Column(db.Integer, db.ForeignKey("office.officeId"))
election = relationship("ElectionModel", foreign_keys=[electionId])
electorates = relationship("ElectionModel", foreign_keys=[electionId])
class Dataset(db.Model):
__tablename__ = "dataset"
dataset_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(200))
service_type = db.Column(db.Enum(ServiceType))
validity_expiration_timestamp = db.Column(db.DateTime)
author = db.Column(db.String(50))
creation_timestamp = db.Column(db.DateTime, default=datetime.now)
file_name = db.Column(db.String(100))
external = db.Column(db.Boolean, default=False)
class Classroom(db.Model):
__tablename__ = "classroom"
id = db.Column(db.Integer, primary_key=True)
student_name = db.Column(db.String(256), nullable=False)
student_id = db.Column(db.Integer(), nullable=False)
classcode = db.Column(db.String(256), nullable=False)
classname = db.Column(db.String(256), nullable=False)
date = db.Column(db.Date(), nullable=False)
presence = db.Column(db.Enum(PresenceEnum),
nullable=False,
default=PresenceEnum.unknown)
class HealthFacility(db.Model):
__tablename__ = 'healthfacility'
# To Do: should probably have a unique id as primary key here, in addition to facility name
healthFacilityName = db.Column(db.String(50), primary_key=True)
facilityType = db.Column(db.Enum(facilityTypeEnum))
# Best practice would be to add column for area code + column for rest of number.
# However, all of our facilites are in Uganda so area code does not change.
# May want to change in the future if system if used in multiple countries
healthFacilityPhoneNumber = db.Column(db.String(50))
location = db.Column(db.String(50))
about = db.Column(db.Text)
class Users(db.Model):
__tablename__ = "users"
id = db.Column(db.Integer,primary_key=True)
first_name = db.Column(db.String(255),nullable=True)
last_name = db.Column(db.String(255),nullable = True)
phone = db.Column(db.String(255),nullable = True)
email = db.Column(db.String(120), unique = True, nullable = False)
password = db.Column(db.String(255))
address = db.Column(db.Text(),nullable = True)
gender = db.Column(db.Enum('M','F','O'),nullable=True)
role_id = db.Column(db.Integer, db.ForeignKey('roles.id'), nullable = True)
status = db.Column(db.Boolean, default = True)
created_at = db.Column(db.DateTime, default = datetime.datetime.utcnow())
updated_at = db.Column(db.DateTime)
class Category(db.Model):
__tablename__ = "category"
"""
Represents category
field id: Primary key
type id: Integer
"""
id = db.Column(db.Integer, primary_key=True)
categoryTitle = db.Column(db.Enum('Hats', 'Shirts', 'Pants', 'Shoes'),
nullable=False,
unique=True)
status = db.Column(db.Boolean, default=True)
createdAt = db.Column(db.DateTime, default=datetime.datetime.utcnow())
updatedAt = db.Column(db.DateTime, onupdate=datetime.datetime.utcnow())
class DatasetCollector(db.Model):
__tablename__ = "dataset_collector"
collector_id = db.Column(
db.Integer,
primary_key=True) # primary keys are required by SQLAlchemy
kafka_topic = db.Column(db.String(300), unique=True)
kafka_server = db.Column(db.String(300))
nsd_id = db.Column(db.String(200))
status = db.Column(db.Enum(CollectorStatus))
creation_timestamp = db.Column(db.DateTime, default=datetime.now)
termination_timestamp = db.Column(db.DateTime)
latest_update = db.Column(db.DateTime,
default=datetime.now,
onupdate=datetime.now)
metric_query_id = db.Column(db.String(50))
il_query_id = db.Column(db.String(50))
class Modulelog(db.Model):
"""
The Modulelog table
"""
__tablename__ = 'modulelog'
__table_args__ = {'schema': DB_SCHEMA}
id = db.Column(db.Integer, primary_key=True)
job_id = db.Column(UUID(as_uuid=True), default=uuid4, index=True)
app_name = db.Column(db.String, nullable=False)
state = db.Column(db.Enum("STARTED",
"FINISHED",
"ERROR",
name="job_states"),
nullable=False)
timestamp = db.Column(db.String,
default=get_timestamp(),
onupdate=get_timestamp())
class FollowUp(db.Model):
__tablename__ = 'followup'
id = db.Column(db.Integer, primary_key=True)
followupInstructions = db.Column(db.Text)
diagnosis = db.Column(db.Text)
treatment = db.Column(db.Text)
dateAssessed = db.Column(db.BigInteger, nullable=False)
healthcareWorkerId = db.Column(db.ForeignKey(User.id), nullable=False)
specialInvestigations = db.Column(db.Text)
medicationPrescribed = db.Column(
db.Text) # those medication names can get pretty long ...
followupNeeded = db.Column(db.Boolean)
# reading = db.relationship('Reading', backref=db.backref('referral', lazy=True, uselist=False))
healthcareWorker = db.relationship(User,
backref=db.backref('followups',
lazy=True))
followupFrequencyValue = db.Column(db.Float)
followupFrequencyUnit = db.Column(db.Enum(frequencyUnitEnum))
dateFollowupNeededTill = db.Column(db.String(50))
class Event(db.Model):
__tablename__ = 'Event'
event_id = db.Column(UUID(as_uuid=True),
default=uuid.uuid4,
primary_key=True)
creator_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('User.user_id'),
nullable=False)
organization_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('Organization.organization_id'),
nullable=False)
event_name = db.Column(db.String(250), nullable=False)
start_date = db.Column(db.DateTime,
default=datetime.utcnow,
nullable=False)
end_date = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
theme = db.Column(db.String(250), nullable=False)
perks = db.Column(db.String(250), nullable=False)
categories = db.Column(db.String(250), nullable=False)
info = db.Column(db.String(2500), nullable=False)
phase = db.Column(db.Enum(EventPhase), nullable=False)
contact_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('Contact.contact_id'),
nullable=False)
image_url = db.Column(db.String(255))
creator = db.relationship('User', lazy=True)
organization = db.relationship('Organization', lazy=True)
contact = db.relationship('Contact', lazy=True)
'''def __init__(self, creator_id, organization_id, event_name, start_date, end_date, theme, perks, categories, info, phase, contact_id):
self.creator_id = creator_id
self.organization_id = organization_id
self.event_name = event_name
self.start_date = start_date
self.end_date = end_date
self.theme = theme
self.perks = perks
self.categories = categories
self.info = info
self.phase = phase
self.contact_id = contact_id'''
'''
class Npc(db.Model):
__tablename__="npc"
pknpc = db.Column(db.Integer, primary_key=True, nullable=False)
name = db.Column(db.String, nullable=False)
type = db.Column(db.Enum('hero', 'unit'), nullable=False)
class Reading(db.Model):
readingId = db.Column(db.String(50), primary_key=True)
bpSystolic = db.Column(db.Integer)
bpDiastolic = db.Column(db.Integer)
heartRateBPM = db.Column(db.Integer)
respiratoryRate = db.Column(db.Integer)
oxygenSaturation = db.Column(db.Integer)
temperature = db.Column(db.Integer)
symptoms = db.Column(db.Text)
trafficLightStatus = db.Column(db.Enum(TrafficLightEnum))
dateTimeTaken = db.Column(db.BigInteger)
dateRecheckVitalsNeeded = db.Column(db.BigInteger)
retestOfPreviousReadingIds = db.Column(db.String(100))
isFlaggedForFollowup = db.Column(db.Boolean)
# FOREIGN KEYS
userId = db.Column(
db.Integer, db.ForeignKey("user.id", ondelete="SET NULL"), nullable=True
)
patientId = db.Column(
db.String(50), db.ForeignKey("patient.patientId"), nullable=False
)
# RELATIONSHIPS
patient = db.relationship("Patient", backref=db.backref("readings", lazy=True))
def get_traffic_light(self):
red_systolic = 160
red_diastolic = 110
yellow_systolic = 140
yellow_diastolic = 90
shock_high = 1.7
shock_medium = 0.9
if (
self.bpSystolic is None
or self.bpDiastolic is None
or self.heartRateBPM is None
):
return TrafficLightEnum.NONE.name
shock_index = self.heartRateBPM / self.bpSystolic
is_bp_very_high = (self.bpSystolic >= red_systolic) or (
self.bpDiastolic >= red_diastolic
)
is_bp_high = (self.bpSystolic >= yellow_systolic) or (
self.bpDiastolic >= yellow_diastolic
)
is_severe_shock = shock_index >= shock_high
is_shock = shock_index >= shock_medium
if is_severe_shock:
traffic_light = TrafficLightEnum.RED_DOWN.name
elif is_bp_very_high:
traffic_light = TrafficLightEnum.RED_UP.name
elif is_shock:
traffic_light = TrafficLightEnum.YELLOW_DOWN.name
elif is_bp_high:
traffic_light = TrafficLightEnum.YELLOW_UP.name
else:
traffic_light = TrafficLightEnum.GREEN.name
return traffic_light
@staticmethod
def schema():
return ReadingSchema
class ElectorateModel(db.Model):
__tablename__ = 'electorate'
electorateId = db.Column(db.Integer, primary_key=True, autoincrement=True)
electorateType = db.Column(db.Enum(ElectorateTypeEnum))
electionId = db.Column(db.Integer, db.ForeignKey("election.electionId"))
parentElectorateId = db.Column(db.Integer, db.ForeignKey("electorate.electorateId"))
class Model(db.Model):
__tablename__ = "model"
model_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(200))
status = db.Column(db.Enum(ModelStatus), default=ModelStatus.not_trained)
external = db.Column(db.Boolean, default=False)
validity_expiration_timestamp = db.Column(db.DateTime)
training_timestamp = db.Column(db.DateTime)
_author = db.Column(db.String(50))
creation_timestamp = db.Column(db.DateTime, default=datetime.now)
accuracy = db.Column(db.Float)
latest_update = db.Column(db.DateTime,
default=datetime.now,
onupdate=datetime.now)
dataset_id = db.Column(db.Integer, db.ForeignKey('dataset.dataset_id'))
dataset = db.relationship('Dataset')
training_algorithm_id = db.Column(
db.Integer, db.ForeignKey('training_algorithm.training_algorithm_id'))
training_algorithm = db.relationship('TrainingAlgorithm')
trained_model_file_name = db.Column(db.String(100))
inf_class_file_name = db.Column(db.String(100))
training_algorithm_file_name = db.Column(db.String(100))
dataset_file_name = db.Column(db.String(100))
_scope = db.Column(db.Enum(Scope))
_service_type = db.Column(db.Enum(ServiceType))
nsd_id = db.Column(db.String(200))
# scope = association_proxy('training_algorithm', 'scope')
# service_type = association_proxy('dataset', 'service_type')
_ml_engine = db.Column(db.Enum(ModelMlEngine))
inference_class_id = db.Column(
db.Integer, db.ForeignKey('inference_class.inference_class_id'))
inference_class = db.relationship('InferenceClass')
@hybrid_property
def validity(self):
if self.training_timestamp and self.validity_expiration_timestamp:
if self.training_timestamp < self.validity_expiration_timestamp:
return True
else:
return False
@hybrid_property
def model_file_url(self):
if self.status == ModelStatus.trained:
return url_for('get_model_file', model_id=self.model_id)
@hybrid_property
def download_file_name(self):
return str(self.model_id) + "_trained_model.zip"
@hybrid_property
def scope(self):
if self._scope: return self._scope
else: return self.training_algorithm.scope
@scope.setter
def scope(self, scope_value):
self._scope = scope_value
@scope.expression
def scope(cls):
return case([
(cls._scope == None, TrainingAlgorithm.scope),
],
else_=cls._scope)
# return TrainingAlgorithm.scope
@hybrid_property
def service_type(self):
if self._service_type: return self._service_type
else: return self.dataset.service_type
@service_type.setter
def service_type(self, service_type_value):
self._service_type = service_type_value
@service_type.expression
def service_type(cls):
return case([
(cls._service_type == None, Dataset.service_type),
],
else_=cls._service_type)
@hybrid_property
def ml_engine(self):
if self._ml_engine: return self._ml_engine
else: return self.training_algorithm.ml_engine
@ml_engine.setter
def ml_engine(self, ml_engine_value):
self._ml_engine = ml_engine_value
@ml_engine.expression
def ml_engine(cls):
return case([
(cls._ml_engine == None, TrainingAlgorithm.ml_engine),
],
else_=cls._ml_engine)
@hybrid_property
def author(self):
if not self._author:
if self.dataset.author == self.training_algorithm.author:
return self.dataset.author
elif self.dataset.author and not self.training_algorithm.author:
return self.dataset.author
elif self.training_algorithm.author and not self.dataset.author:
return self.dataset.author
else:
return self.training_algorithm.author + " & " + self.dataset.author
else:
return self._author
@author.setter
def author(self, author_value):
self._author = author_value
@author.expression
def author(cls):
return case([
(and_(cls._author == None, Dataset.author != None,
TrainingAlgorithm.author != None,
Dataset.author != TrainingAlgorithm.author),
TrainingAlgorithm.author + " & " + Dataset.author),
(and_(cls._author == None, Dataset.author
== TrainingAlgorithm.author), TrainingAlgorithm.author),
(and_(cls._author == None, Dataset.author == None,
TrainingAlgorithm.author != None), TrainingAlgorithm.author),
(and_(cls._author == None, Dataset.author != None,
TrainingAlgorithm.author == None), Dataset.author),
],
else_=cls._author)
class Role(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Enum(RoleEnum), nullable=False)
class Reading(db.Model):
readingId = db.Column(db.String(50), primary_key=True)
bpSystolic = db.Column(db.Integer)
bpDiastolic = db.Column(db.Integer)
heartRateBPM = db.Column(db.Integer)
symptoms = db.Column(db.Text)
trafficLightStatus = db.Column(db.Enum(TrafficLightEnum))
# date ex: 2019-09-25T19:00:16.683-07:00[America/Vancouver]
dateLastSaved = db.Column(db.BigInteger)
dateTimeTaken = db.Column(db.BigInteger)
dateUploadedToServer = db.Column(db.BigInteger)
dateRecheckVitalsNeeded = db.Column(db.BigInteger)
gpsLocationOfReading = db.Column(db.String(50))
retestOfPreviousReadingIds = db.Column(db.String(100))
isFlaggedForFollowup = db.Column(db.Boolean)
appVersion = db.Column(db.String(50))
deviceInfo = db.Column(db.String(50))
totalOcrSeconds = db.Column(db.Float)
manuallyChangeOcrResults = db.Column(db.Integer)
temporaryFlags = db.Column(db.Integer)
userHasSelectedNoSymptoms = db.Column(db.Boolean)
# change this to enum (currently cumbersome because currently system saves data straight from json, values look like 'g ++' and we cannot have enums with that name)
# so need some sort of way to map it over manually when saving data
urineTest = db.Column(db.String(50))
# FOREIGN KEYS
userId = db.Column(db.Integer,
db.ForeignKey('user.id', ondelete='SET NULL'),
nullable=True)
# @hybrid_property
def getTrafficLight(self):
RED_SYSTOLIC = 160
RED_DIASTOLIC = 110
YELLOW_SYSTOLIC = 140
YELLOW_DIASTOLIC = 90
SHOCK_HIGH = 1.7
SHOCK_MEDIUM = 0.9
if self.bpSystolic == None or self.bpDiastolic == None or self.heartRateBPM == None:
return TrafficLightEnum.NONE.name
shockIndex = self.heartRateBPM / self.bpSystolic
isBpVeryHigh = (self.bpSystolic >= RED_SYSTOLIC) or (self.bpDiastolic
>= RED_DIASTOLIC)
isBpHigh = (self.bpSystolic >= YELLOW_SYSTOLIC) or (self.bpDiastolic >=
YELLOW_DIASTOLIC)
isSevereShock = (shockIndex >= SHOCK_HIGH)
isShock = (shockIndex >= SHOCK_MEDIUM)
if isSevereShock:
trafficLight = TrafficLightEnum.RED_DOWN.name
elif isBpVeryHigh:
trafficLight = TrafficLightEnum.RED_UP.name
elif isShock:
trafficLight = TrafficLightEnum.YELLOW_DOWN.name
elif isBpHigh:
trafficLight = TrafficLightEnum.YELLOW_UP.name
else:
trafficLight = TrafficLightEnum.GREEN.name
return trafficLight
def __init__(self,
userId,
patientId,
readingId,
bpSystolic,
bpDiastolic,
heartRateBPM,
symptoms,
trafficLightStatus=None,
dateLastSaved=None,
dateTimeTaken=None,
dateUploadedToServer=None,
dateRecheckVitalsNeeded=None,
gpsLocationOfReading=None,
retestOfPreviousReadingIds=None,
isFlaggedForFollowup=None,
appVersion=None,
deviceInfo=None,
totalOcrSeconds=None,
manuallyChangeOcrResults=None,
temporaryFlags=None,
userHasSelectedNoSymptoms=None,
urineTest=None):
self.userId = userId
self.patientId = patientId
self.readingId = readingId
self.bpSystolic = bpSystolic
self.bpDiastolic = bpDiastolic
self.heartRateBPM = heartRateBPM
self.symptoms = symptoms
self.trafficLightStatus = self.getTrafficLight()
self.dateTimeTaken = dateTimeTaken
self.dateLastSaved = dateLastSaved
self.dateUploadedToServer = dateUploadedToServer
self.dateRecheckVitalsNeeded = dateRecheckVitalsNeeded
self.gpsLocationOfReading = gpsLocationOfReading
self.retestOfPreviousReadingIds = retestOfPreviousReadingIds
self.isFlaggedForFollowup = isFlaggedForFollowup
self.appVersion = appVersion
self.deviceInfo = deviceInfo
self.totalOcrSeconds = totalOcrSeconds
self.manuallyChangeOcrResults = manuallyChangeOcrResults
self.temporaryFlags = temporaryFlags
self.userHasSelectedNoSymptoms = userHasSelectedNoSymptoms
self.urineTest = urineTest
# FOREIGN KEYS
patientId = db.Column(db.String(50),
db.ForeignKey('patient.patientId'),
nullable=False)
# RELATIONSHIPS
patient = db.relationship('Patient',
backref=db.backref('readings', lazy=True))
urineTests = db.relationship('urineTest',
backref=db.backref('reading', lazy=True))
