class Reply(Base):
id = Column(Integer, autoincrement=True, primary_key=True)
content = Column(String(300), nullable=False)
suggestionId = Column(Integer, db.ForeignKey('suggestions.id'))
suggestion = db.relationship('Suggestions')
replyPersonId = Column(Integer, db.ForeignKey('user.userid'))
replyPerson = db.relationship('User', back_populates='myReplySuggestions')
class image_deid(Mixin, db.Model):
"""image_deid table
"""
__bind_key__ = "image_exams_db"
__tablename__ = "image_deid"
image_id = db.Column(db.INT, unique=True, primary_key=True)
pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id"))
image_procedure_id = db.Column(
db.INT, db.ForeignKey("image_procedure.image_procedure_id"))
exam_id = db.Column(db.INT, db.ForeignKey("exam_deid.exam_id"))
image_num = db.Column(db.INT)
image_type = db.Column(db.VARCHAR, nullable=False)
image_laterality = db.Column(db.VARCHAR, nullable=False)
device_id = db.Column(db.INT)
def __repr__(self):
return "<image_deid {!r}, pt_id {!r}>".format(self.image_id,
self.pt_id)
@staticmethod
def get_pt_id_by_image_procedure_type(ipt: list) -> list:
"""Filter pt_id by image_procedure_type
:param ipt <list<str>> list of image_procedure_type
:returns <list<int>> pt_id
"""
# Initialise query
qry = (image_deid.query.with_entities(
image_deid.pt_id).distinct().join(image_procedure))
# construct list of AND queries
and_query = [
image_procedure.image_procedure == img_proc_type
for img_proc_type in ipt
]
# do the query, get distinct pt_id
qry = qry.filter(db.and_(*and_query))
return [v.pt_id for v in qry.all()]
@staticmethod
def get_image_procedure_from_pt_id(pt_id: int) -> list:
qry = (image_deid.query.join(image_procedure).with_entities(
image_procedure.image_procedure).filter(
image_deid.pt_id == pt_id).distinct())
return [v[0] for v in qry.all()]
class medication_deid(Mixin, db.Model):
"""medication_deid table
"""
__tablename__ = "medication_deid"
medication_id = db.Column(db.INT, unique=True, primary_key=True)
pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id"))
generic_name = db.Column(db.VARCHAR)
therapeutic_class = db.Column(db.VARCHAR)
order_placed_dt = db.Column(db.DateTime)
order_end_dt = db.Column(db.DateTime)
usage_directions = db.Column(db.VARCHAR)
order_class = db.Column(db.VARCHAR)
strength = db.Column(db.VARCHAR)
form = db.Column(db.VARCHAR)
number_of_doses = db.Column(db.INT)
dose_unit = db.Column(db.VARCHAR)
frequency = db.Column(db.VARCHAR)
medication_administration_deid = db.relationship(
"medication_administration_deid",
backref="medication_deid",
lazy="dynamic")
def __repr__(self):
return "<medication_deid {!r}, pt_id {!r}".format(
self.medication_id, self.pt_id)
@staticmethod
def get_pt_id_by_generic_name(mgn: list) -> list:
"""Get pt_id by medication_generic_name
:param mgn <list<str>> list of medication_generic_name
:returns <list<int>> list of pt_id
"""
# Initialise query
qry = medication_deid.query.with_entities(
medication_deid.pt_id).distinct()
# do query
qry = qry.filter(medication_deid.generic_name.in_(mgn))
return [v.pt_id for v in qry.all()]
@staticmethod
def get_pt_id_by_therapeutic_class(mtc: list) -> list:
"""Get pt_id by medication_generic_name
:param mgn <list<str>> list of medication_generic_name
:returns <list<int>> list of pt_id
"""
# Initialise query
qry = medication_deid.query.with_entities(
medication_deid.pt_id).distinct()
# do query
qry = qry.filter(medication_deid.therapeutic_class.in_(mtc))
return [v.pt_id for v in qry.all()]
class diagnosis_deid(Mixin, db.Model):
"""diagnosis_deid table
"""
__tablename__ = "diagnosis_deid"
diagnosis_id = db.Column(db.INT, unique=True, primary_key=True)
pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id"))
diagnosis_code = db.Column(db.VARCHAR, nullable=False)
diagnosis_code_set = db.Column(db.VARCHAR)
diagnosis_start_dt = db.Column(db.DateTime)
diagnosis_end_dt = db.Column(db.DateTime)
diagnosis_name = db.Column(
db.VARCHAR
) ## This isn't in the sqldbm.com model but is in the sample data
def __repr__(self):
return "<diagnosis_deid {!r}, pt_id {!r}>".format(
self.diagnosis_id, self.pt_id)
@staticmethod
def get_pt_id_by_diagnosis_names(diagnosis_names: list) -> list:
"""Get pt_id by diagnosis_name
Currently uses OR logic -- consider AND in the future
:param diagnosis_names <list<str>> list of diagnosis_name
:returns <list<int>> list of pt_id
"""
qry = diagnosis_deid.query.with_entities(
diagnosis_deid.pt_id).distinct()
qry = qry.filter(diagnosis_deid.diagnosis_name.in_(diagnosis_names))
return [v.pt_id for v in qry.all()]
class User(UserMixin,Base):
userid=Column(Integer,primary_key=True,autoincrement=True)#主键
username=Column(String(20))
userPhone=Column(String(20),index=True)
password_hash=Column(String(128))
wxopenId=Column(String(100),index=True,nullable=True)
roleId=Column(Integer,db.ForeignKey('roles.id'))
role=db.relationship('Roles',back_populates='users')
pubCount=Column(Integer,default=0)
signInCount=Column(Integer,default=0)
pubSignIns=db.relationship('SignInOrder',back_populates='pubPerson')
myPubSuggestions=db.relationship('Suggestions',back_populates='pubPerson')
myReplySuggestions=db.relationship('Reply',back_populates='replyPerson')
posts=db.relationship('Post',back_populates='pubPerson')
admin=Column(Boolean,default=False)
canPub = db.Column(db.Boolean, default=False, nullable=False)#可以发布签到
canEdit = db.Column(db.Boolean, default=False, nullable=False)#可以发布文章
@property
def password(self):
return self.password_hash
# 装饰器装饰后，可以像调用属性一样调用password
@password.setter
def password(self, raw):
self.password_hash = generate_password_hash(raw) # 密码加密
# 装饰器装饰后，可以像设置属性一样调用password
def validate_password(self,password):
return check_password_hash(self.password,password)
def increase_pubCount(self):
self.pubCount+=1
def increase_signInCount(self):
self.signInCount+=1
def get_id(self):
#覆盖父类的id
try:
return text_type(self.userid)
except AttributeError:
raise NotImplementedError('`get_id`error')
def can(self,permission_name):
#判断是否有权限
permission=Permission.query.filter_by(name=permission_name).first()
return permission is not None and self.role is not None and \
permission in self.role.permissions
class Post(Base):
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(60))
body = Column(Text)
time = Column(DateTime, default=datetime.now)
pubPersonId = Column(Integer, db.ForeignKey('user.userid'))
pubPerson = db.relationship('User', back_populates='posts')
status = Column(SmallInteger, default=1)
class UserStockQuantity(BaseModel):
__tablename__ = "user_stock_quantities"
id = db.Column(db.Unicode(), primary_key=True)
user_id = db.Column(db.ForeignKey("users.id", ondelete="CASCADE"),
nullable=False)
stock_id = db.Column(db.ForeignKey("stocks.id", ondelete="CASCADE"),
nullable=False)
quantity = db.Column(db.Integer, nullable=False, default=0)
@classmethod
def get_or_create(cls, uid=None, **kwargs):
user_id, stock_id = kwargs.get('user_id'), kwargs.get('stock_id')
user_quantity_stock = cls.query.filter(
cls.user_id == user_id, cls.stock_id == stock_id).one_or_none()
if user_quantity_stock:
return user_quantity_stock
return super().get_or_create(uid=uid, **kwargs)
class Order(BaseModel):
__tablename__ = "orders"
id = db.Column(db.Unicode(), primary_key=True)
user_id = db.Column(
db.Unicode(),
db.ForeignKey("users.id", ondelete="CASCADE"),
)
stock_id = db.Column(
db.Unicode(),
db.ForeignKey("stocks.id", ondelete="CASCADE"),
)
quantity = db.Column(db.Integer(), nullable=False)
is_buy = db.Column(db.Boolean, default=False)
stock = db.relationship("Stock", backref="orders")
user = db.relationship("User", backref="orders")
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
@classmethod
def create_buy_order(cls, **kwargs):
user_stock_quantity = UserStockQuantity.get_or_create(
user_id=kwargs.get('user_id'), stock_id=kwargs.get('stock_id'))
user_stock_quantity.quantity += kwargs.get("quantity")
instance = cls.create(**{**kwargs, "is_buy": True})
Stock.add_stock_quantity(kwargs.get('stock_id'),
-kwargs.get("quantity"))
user_stock_quantity.save()
return instance
@classmethod
def create_sell_order(cls, **kwargs):
user_stock_quantity = UserStockQuantity.get_or_create(
user_id=kwargs.get('user_id'), stock_id=kwargs.get('stock_id'))
user_stock_quantity.quantity -= kwargs.get("quantity")
instance = cls.create(**{**kwargs, "is_buy": False})
Stock.add_stock_quantity(kwargs.get('stock_id'),
kwargs.get("quantity"))
user_stock_quantity.save()
return instance
class medication_administration_deid(Mixin, db.Model):
"""medication_administration_deid table
"""
__tablename__ = "medication_administration_deid"
medication_administration_id = db.Column(db.INT,
unique=True,
primary_key=True)
medication_id = db.Column(db.INT,
db.ForeignKey("medication_deid.medication_id"))
pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id"))
generic_name = db.Column(db.VARCHAR)
therapeutic_class = db.Column(db.VARCHAR)
order_placed_dt = db.Column(db.DateTime)
order_end_dt = db.Column(db.DateTime)
scheduled_administration_dt = db.Column(db.DateTime)
administration_dt = db.Column(db.DateTime)
discontinue_order_dt = db.Column(db.DateTime)
action_name = db.Column(db.VARCHAR)
def __repr__(self):
return "<medication_administration_deid {!r}, pt_id {!r}".format(
self.medication_administration_id, self.pt_id)
class SignInPeople(Base):
id = Column(Integer, autoincrement=True, primary_key=True)
signInOrderId = Column(Integer, db.ForeignKey('sign_in_order.id'))
signInOrder = db.relationship('SignInOrder', back_populates='signInPerson')
userId = Column(Integer, index=True, nullable=False)
username = Column(String(20))
userPhone = Column(String(20), index=True)
signInTime = Column(Time)
# Time datetime.time 时间
def generate_signInTime(self):
return datetime.time(datetime.now()) #17:51:30.854656修改
class Suggestions(Base):
id = Column(Integer, autoincrement=True, primary_key=True)
title = Column(String(50), nullable=False)
content = Column(String(300), nullable=False)
sugPubPersonId = Column(Integer, db.ForeignKey('user.userid'))
pubPerson = db.relationship('User', back_populates='myPubSuggestions')
sugStatus = Column(SmallInteger, default=1)
reply = db.relationship('Reply', uselist=False)
IGNORE = 0
NOREPLY = 1
HAVEREPLY = 2
class visit_movement_deid(Mixin, db.Model):
"""visit_movement_deid table
"""
__tablename__ = "visit_movement_deid"
visit_movement_id = db.Column(db.INT, unique=True, primary_key=True)
pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id"))
department_external_name = db.Column(db.VARCHAR)
department_name = db.Column(db.VARCHAR, nullable=False)
event_start_dt = db.Column(db.DateTime)
event_end_dt = db.Column(db.DateTime)
event_type_name = db.Column(db.VARCHAR)
event_subtype_name = db.Column(db.VARCHAR)
patient_class_name = db.Column(db.VARCHAR)
def __repr__(self):
return "<visit_movement_deid {!r}, pt_id {!r}>".format(
self.visit_movement_id, self.pt_id)
class SignInOrder(Base):
id = Column(Integer, autoincrement=True, primary_key=True)
pubPersonId = Column(Integer, db.ForeignKey('user.userid'))
pubPerson = db.relationship('User', back_populates='pubSignIns')
pubTime = Column(DateTime)
endTime = Column(DateTime)
# DateTime datetime.datetime 日期和时间
needtoSignIn = Column(Integer)
haveSignIn = Column(Integer, default=0)
signInStatus = Column(SmallInteger, default=1)
qrcodeUrl = Column(String(100)) #本地的相对路径
signInPerson = db.relationship('SignInPeople',
back_populates='signInOrder') #一对多的一侧
def generate_pubTime(self):
return datetime.now() # 可能有误类型错误,这里的应该有闭包不影响
class lab_value_deid(Mixin, db.Model):
"""lab_value_deid table
"""
__tablename__ = "lab_value_deid"
lab_value_id = db.Column(db.INT, unique=True, primary_key=True)
pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id"))
name = db.Column(db.VARCHAR, nullable=False)
loinc_code = db.Column(db.VARCHAR)
value = db.Column(db.VARCHAR)
reference_high = db.Column(db.VARCHAR)
reference_low = db.Column(db.VARCHAR)
reference_normal = db.Column(db.VARCHAR)
reference_unit = db.Column(db.VARCHAR)
result_category = db.Column(db.VARCHAR)
order_dt = db.Column(db.DateTime)
result_dt = db.Column(db.DateTime)
value_numeric = db.Column(db.DECIMAL(18, 0))
def __repr__(self):
return "<lab_value_deid {!r}, pt_id {!r}".format(
self.lab_value_id, self.pt_id)
class exam_deid(Mixin, db.Model):
"""exam_deid table
"""
__bind_key__ = "image_exams_db"
__tablename__ = "exam_deid"
exam_id = db.Column(db.INT, unique=True, primary_key=True)
pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id"))
exam_date = db.Column(db.DateTime)
image_deid = db.relationship("image_deid", backref="exam_deid", lazy="dynamic")
def __repr__(self):
return "<exam_deid {!r}, pt_id {!r}>".format(self.exam_id, self.pt_id)
@staticmethod
def get_distinct_pt_ids():
"""Get all pt_ids available
Using this table to make sure all the patients
returned indeed have relevant data entries
"""
qry = exam_deid.query.with_entities(exam_deid.pt_id).distinct()
return [v.pt_id for v in qry.all()]
from api.models.base import db
roles_permissions = db.Table(
'roles_permissions',
db.Column('role_id', db.Integer, db.ForeignKey('roles.id')),
db.Column('permission_id', db.Integer, db.ForeignKey('permission.id')))
class smart_data_deid(Mixin, db.Model):
"""smart_data_deid table
"""
__tablename__ = "smart_data_deid"
smart_data_id = db.Column(db.INT, unique=True, primary_key=True)
pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id"))
element_name = db.Column(db.VARCHAR, nullable=False)
smrtdta_elem_value = db.Column(db.VARCHAR)
value_dt = db.Column(db.DateTime)
def __repr__(self):
return "<smart_data_deid {!r}, pt_id {!r}>".format(
self.smart_data_id, self.pt_id)
@staticmethod
def get_pt_id_by_vision(data: dict) -> set:
""" Take the json data from filters
and filter for the pt_ids with vision
params:
data: <dict> input filter json
returns:
pt_ids: <set>
"""
pt_ids = []
for field in ("left_vision", "right_vision"):
if field in data:
less_than = _parse_vision(data.get(field).get("less"))
more_than = _parse_vision(data.get(field).get("more"))
pt_ids.extend(
_filter_vis_pres_range(
KEYWORDS[field],
(more_than, less_than),
KEYWORDS["vision_value_regex"],
vision=True,
))
return set(pt_ids)
@staticmethod
def get_pt_id_by_pressure(data: dict) -> set:
""" Take the json data from filters
and filter for the pt_ids with pressure
params:
data: <dict> input filter json
returns:
pt_ids: <set>
"""
pt_ids = []
for field in ("left_pressure", "right_pressure"):
if field in data:
less_than = data.get(field).get("less")
more_than = data.get(field).get("more")
pt_ids.extend(
_filter_vis_pres_range(
KEYWORDS[field],
(more_than, less_than),
KEYWORDS["pressure_value_regex"],
vision=False,
))
return set(pt_ids)
@staticmethod
def get_data_for_pt_id(pt_id, pressure=False, vision=False):
if not pressure ^ vision:
raise ValueError(
"get_data_for_pt_id: set either pressure or vision to True")
if pressure:
kws = KEYWORDS["pressure"]
elif vision:
kws = KEYWORDS["vision"]
qry = (smart_data_deid.query.with_entities(
smart_data_deid.element_name,
smart_data_deid.smrtdta_elem_value,
smart_data_deid.smart_data_id,
smart_data_deid.value_dt,
).filter(
db.and_(
smart_data_deid.pt_id == pt_id,
smart_data_deid.element_name.ilike(kws),
)).order_by(smart_data_deid.value_dt.desc()))
res = qry.all()
return res
