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 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 Permission(Base):
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(30), unique=True)
roles = db.relationship('Roles',
secondary=roles_permissions,
back_populates='permissions')
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 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 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 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 Roles(Base):
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(30), unique=True)
permissions = db.relationship('Permission',
secondary=roles_permissions,
back_populates='roles') #在对方那叫做roles
users = db.relationship('User', back_populates='role')
Administrator = 3
Moderator = 2
User = 1
@staticmethod
def init_role():
roles_permissions_map = {
'User': ['LOOK', 'PUBSIGNIN'],
'Moderator': ['LOOK', 'PUBSIGNIN', 'UPLOAD', 'DELSELF'],
'Administrator':
['LOOK', 'PUBSIGNIN', 'UPLOAD', 'CREATEOTHER', 'DELOTHER']
}
for role_name in roles_permissions_map:
# print(role_name)
# print(str(roles_permissions_map[role_name]))
role = Roles.query.filter_by(name=role_name).first()
# role = None
if role is None:
role = Roles()
role.name = role_name
db.session.add(role)
role.permissions = []
for permission_name in roles_permissions_map[role_name]:
permission = Permission.query.filter_by(
name=permission_name).first()
if permission is None:
permission = Permission()
permission.name = permission_name
db.session.add(permission)
# print(permission)
role.permissions.append(permission)
db.session.commit()
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 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 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 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()]
