class User(db.Model):
"""User model for storing login credentials
"""
__bind_key__ = "users"
__table_name__ = "users"
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), unique=True, index=True)
password_hash = db.Column(db.String(128))
def __repr__(self):
return "<User {!r}>".format(self.username)
def generate_auth_token(self, expiration=60 * 60):
s = Serializer(app.config["SECRET_KEY"], expires_in=expiration)
return s.dumps({"id": self.id})
@staticmethod
def verify_auth_token(token):
s = Serializer(app.config["SECRET_KEY"])
try:
data = s.loads(token)
except SignatureExpired:
return None
except BadSignature:
return None
user = User.query.get(data["id"])
return user
def hash_password(self, password):
self.password_hash = pwd_context.hash(password)
def verify_password(self, password):
return pwd_context.verify(password, self.password_hash)
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 Stock(BaseModel):
__tablename__ = "stocks"
id = db.Column(db.Unicode(), primary_key=True)
brand = db.Column(db.Unicode(), nullable=False, unique=True)
quantity = db.Column(db.Integer, nullable=False, default = 30)
price = db.Column(db.Float, nullable=False, default = 100)
@classmethod
def get_stock_by_brand(cls, brand):
return cls.query.filter(cls.brand == brand).one_or_none()
@classmethod
def get_stocks_infos_by_user_id(cls, user_id):
stocks_infos = cls.session.query(Stock, UserStockQuantity)\
.join(UserStockQuantity, cls.id == UserStockQuantity.stock_id)\
.filter(UserStockQuantity.user_id == user_id).all()
return [
{
'brand':stock.brand,
'user_id': user_id,
'user_quantity': ust_quantity.quantity,
'stock_id': stock.id
}
for stock, ust_quantity in stocks_infos
]
@classmethod
def get_stock_infos_by_user_id(cls, brand, user_id):
stock = Stock.get_stock_by_brand(brand)
stock_infos, ust_quantity = cls.session.query(Stock, UserStockQuantity)\
.join(UserStockQuantity, cls.id == UserStockQuantity.stock_id)\
.filter(UserStockQuantity.user_id == user_id, UserStockQuantity.stock_id == stock.id ).one_or_none()
if stock_infos:
return {
'brand':stock_infos.brand,
'user_id': user_id,
'user_quantity': ust_quantity.quantity,
'stock_id': stock_infos.id
}
@classmethod
def add_stock_quantity(cls, stock_id, quantity):
stock = cls.get_by_id(stock_id)
stock.quantity += quantity
stock.save()
@classmethod
def update_stock_prices(cls):
for stock in cls.all():
t = (random() - 0.5) / 4
stock.price = round(stock.price * (1 + t), 2)
stock.save()
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 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 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 User(BaseModel):
__tablename__ = "users"
id = db.Column(db.Unicode(), primary_key=True)
name = db.Column(db.Unicode(), nullable=False)
mail = db.Column(db.Unicode(), nullable=False)
password = db.Column(db.Unicode(), nullable=False)
funds = db.Column(db.Float, nullable=False, default=100000)
def buy_stock(self, quantity, brand):
stock = Stock.query.filter(Stock.brand == brand).one_or_none()
if not stock or quantity < 0 or (stock.quantity < quantity or
quantity * stock.price > self.funds):
return False
else:
Order.create_buy_order(quantity=quantity,
user_id=self.id,
stock_id=stock.id)
self.funds -= stock.price * quantity
return True
def get_stock_quantity(self, stock_id):
user_stock_quantity = UserStockQuantity.get_or_create(
user_id=self.id, stock_id=stock_id)
return user_stock_quantity.quantity
def sell_stock(self, quantity, brand):
stock = Stock.query.filter(Stock.brand == brand).one_or_none()
stock_infos = Stock.get_stock_infos_by_user_id(brand, self.id)
if not stock_infos or (quantity > stock_infos.get('user_quantity')):
return False
else:
Order.create_sell_order(quantity=quantity,
user_id=self.id,
stock_id=stock.id)
self.funds += stock.price * quantity
return True
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 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()]
class Person(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(100), unique=True)
password = db.Column(db.String(100))
name = db.Column(db.String(1000))
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 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 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
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 pt_deid(Mixin, db.Model):
"""pt_deid table
"""
__tablename__ = "pt_deid"
pt_id = db.Column(db.INT, unique=True, primary_key=True)
dob = db.Column(db.DateTime, nullable=False)
over_90 = db.Column(db.SMALLINT)
race_1 = db.Column(db.VARCHAR)
diagnosis_deid = db.relationship(
"diagnosis_deid", backref="pt_deid", lazy="dynamic"
)
exam_deid = db.relationship("exam_deid", backref="pt_deid", lazy="dynamic")
image_deid = db.relationship("image_deid", backref="pt_deid", lazy="dynamic")
lab_value_deid = db.relationship(
"lab_value_deid", backref="pt_deid", lazy="dynamic"
)
medication_administration_deid = db.relationship(
"medication_administration_deid", backref="pt_deid", lazy="dynamic"
)
medication_deid = db.relationship(
"medication_deid", backref="pt_deid", lazy="dynamic"
)
smart_data_deid = db.relationship(
"smart_data_deid", backref="pt_deid", lazy="dynamic"
)
visit_movement_deid = db.relationship(
"visit_movement_deid", backref="pt_deid", lazy="dynamic"
)
def __repr__(self):
return "<pt_deid {!r}>".format(self.pt_id)
@staticmethod
def get_all_pt_ids():
"""Get all pt_id available
"""
qry = pt_deid.query.with_entities(pt_deid.pt_id).distinct()
return [v.pt_id for v in qry.all()]
@staticmethod
def get_pt_id_by_age_or_race_1(
race_1: list = None, younger_than: datetime = None, older_than: datetime = None,
) -> list:
"""Filter pt_id by age and/or race_1
:param race_1 <list<str>>
:param younger_than <DateTime> earliest DoB
:param older_than <DateTime> latest DoB
:returns <list<int>> pt_id
"""
qry = pt_deid.query.with_entities(pt_deid.pt_id).distinct()
if younger_than != None:
qry = qry.filter(pt_deid.dob > younger_than)
if older_than != None:
qry = qry.filter(pt_deid.dob < older_than)
if race_1:
qry = qry.filter(pt_deid.race_1.in_(race_1))
return [v.pt_id 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()]