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