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()]