class User(BaseModel): __tablename__ = 'users' STATUS_ACTIVE = 'active' STATUS_BLOCKED = 'blocked' STATUSES = [STATUS_ACTIVE, STATUS_BLOCKED] ROLE_USER = '******' ROLE_ADMIN = 'admin' id = db.Column(db.Integer(), primary_key=True) username = db.Column(db.String(100), unique=True, nullable=False, index=True) password = db.Column(db.String(150)) status = db.Column(db.String(30), nullable=False, default=STATUS_ACTIVE, index=True) role = db.Column(db.String(30), nullable=False, default=ROLE_USER, index=True) count_recipes = db.Column(db.Integer(), default=0) count_likes = db.Column(db.Integer(), default=0) def set_password(self, password): self.password = sha256_crypt.hash(password) def check_password(self, password): return sha256_crypt.verify(password, self.password)
class Ticket(Base): __tablename__ = 'ticket' id = db.Column(db.Integer(), primary_key=True) date = db.Column(db.Date()) updated = db.Column(db.DateTime(timezone=True)) um = db.Column(db.String(10)) cost = db.Column(db.Float(scale=17, precision=3)) total = db.Column(db.Float(scale=17, precision=3)) total_tax_paid = db.Column(db.Float(scale=16, precision=2)) total_no_tax = total - total_tax_paid project_task_id = db.Column(db.Integer()) project_task_name = db.Column(db.String(200)) user_id = db.Column(db.Integer()) project_id = db.Column(db.Integer()) project_name = db.Column(db.String(200)) currency = db.Column(db.String(3)) city = db.Column(db.String(75)) quantity = db.Column(db.Float(scale=12, precision=2)) acct_date = db.Column(db.Date()) @staticmethod def get_my_tickets(user_email): query = '''SELECT DISTINCT t.id, t.date, t.updated, t.um, t.cost, t.total, t.total_tax_paid, t.project_task_id, pt.name AS project_task_name, t.user_id, t.project_id, p.name AS project_name, t.currency, t.city, t.quantity, t.acct_date FROM ticket t INNER JOIN envelope e ON t.envelope_id = e.id INNER JOIN project p ON e.project_id = p.id LEFT JOIN project_task pt ON t.project_task_id = t.id LEFT JOIN user u ON t.user_id=u.id WHERE u.email = :email OR p.id IN (SELECT DISTINCT p2.id FROM project p2 LEFT JOIN project_task pt2 ON pt2.project_id = p2.id LEFT JOIN project_task_assign pta ON pta.project_task_id = pt2.id LEFT JOIN booking b ON b.project_id = p2.id LEFT JOIN user u ON (p2.user_id = u.id OR pta.user_id = u.id OR b.user_id = u.id) WHERE u.email = :email AND p2.active="1");''' return Ticket.query.from_statement( text(query)).params(email=user_email).all() def __repr__(self): return '<Ticket %r>' % self.id
class QuoteModel(db.Model): __tablename__ = 'quote' __table_args__ = {'schema': cfg_db_schema} id = db.Column(db.Integer(), primary_key=True) body = db.Column(db.String(500), unique=True) active = db.Column(db.Integer(), default=1) created_at = db.Column(db.DateTime, default=datetime.utcnow) def __repr__(self): return "<Quote %r>" % (self.id)
class Booking(Base): __tablename__ = 'booking' id = db.Column(db.Integer(), primary_key=True) owner_id = db.Column(db.Integer()) user_id = db.Column(db.Integer()) project_id = db.Column(db.Integer()) project_name = db.Column(db.String(200)) startdate = db.Column(db.Date()) enddate = db.Column(db.Date()) percentage = db.Column(db.Float(scale=12, precision=2)) hours = db.Column(db.Float(scale=12, precision=2)) project_task_id = db.Column(db.Integer()) project_task_name = db.Column(db.String(200)) as_percentage = db.Column(db.String(1)) approval_status = db.Column(db.String(1)) @staticmethod def get_my_bookings(user_email): query = '''SELECT DISTINCT b.id, b.owner_id, b.user_id, b.project_id, p.name AS "project_name", b.startdate, b.enddate, b.percentage, b.hours, b.project_task_id, pt.name AS "project_task_name", b.as_percentage, b.approval_status FROM booking b INNER JOIN project_task pt ON b.project_task_id = pt.id INNER JOIN project p ON b.project_id = p.id LEFT JOIN user u ON b.user_id = u.id WHERE b.approval_status = "A" AND (u.email = :email OR p.id IN (SELECT DISTINCT p2.id FROM project p2 LEFT JOIN project_task pt2 ON pt2.project_id = p2.id LEFT JOIN project_task_assign pta ON pta.project_task_id = pt2.id LEFT JOIN booking b ON b.project_id = p2.id LEFT JOIN user u ON (p2.user_id = u.id OR pta.user_id = u.id OR b.user_id = u.id) WHERE u.email = :email AND p2.active="1"));''' return Booking.query.from_statement( text(query)).params(email=user_email).all() def __repr__(self): return '<Booking %r>' % self.id
class Daily(Base): # Multiple binds: http://flask-sqlalchemy.pocoo.org/2.1/binds/ __bind_key__ = 'dailies' __tablename__ = 'timesheets_vs_bookings_daily' id = db.Column(db.Integer(), primary_key=True) timesheets_id = db.Column(db.Integer()) bookings_daily_id = db.Column(db.Integer()) associate = db.Column(db.String(200)) practice = db.Column(db.String(200)) client_name = db.Column(db.String(200)) project_name = db.Column(db.String(200)) task_name = db.Column(db.String(200)) date = db.Column(db.Date()) week_of_booking = db.Column(db.Integer()) week_of_year_iso = db.Column(db.String(3)) booking_hours = db.Column(db.Float(scale=12, precision=8)) booking_fees = db.Column(db.Float(scale=12, precision=2)) timesheet_hours = db.Column(db.Float(scale=12, precision=2)) associate_currency = db.Column(db.String(3)) @staticmethod def get_dailies(project_name, task_name, associate): query = '''SELECT DISTINCT id, timesheets_id, bookings_daily_id, associate, practice, client_name, project_name, task_name, date, week_of_booking, week_of_year_iso, booking_hours booking_fees, timesheet_hours, associate_currency FROM timesheets_vs_bookings_daily WHERE project_name=:project_name AND task_name=:task_name AND associate=:associate ORDER BY date ASC''' return Daily.query.from_statement(text(query)).params( project_name=project_name, task_name=task_name, associate=associate).all() def __repr__(self): return '<Daily %r>' % self.id
class User(Base): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(200)) nickname = db.Column(db.String(50)) active = db.Column(db.String(1)) timezone = db.Column(db.String(6)) line_manager_id = db.Column(db.Integer()) department_id = db.Column(db.Integer()) def __repr__(self): return '<User %r>' % self.id
class UserRoles(db.Model): __tablename__ = 'user_roles' id = db.Column(db.Integer(), primary_key=True) user_id = db.Column(db.Integer(), db.ForeignKey('users.id', ondelete='CASCADE')) role_id = db.Column(db.Integer(), db.ForeignKey('roles.id', ondelete='CASCADE')) def __init__(self, user_id, role_id): self.user_id = user_id, self.role_id = role_id def __repr__(self): return f"user_id[{self.user_id}] to role_id[{self.user_role}]"
class TemplateGeneCard(db.Model): __tablename__ = 'template_gene_card' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(200)) # gene名称 transcript = db.Column(db.String(200)) # 转录本 gene_func = db.Column(db.String(2000)) # 基因功能 gene_cancer = db.Column(db.String(2000)) # 基因与肿瘤的关系
class SelectedRecipe(BaseModel): __tablename__ = 'selected_recipes' id = db.Column(db.Integer(), primary_key=True) recipe_id = db.Column(db.Integer, db.ForeignKey('recipes.id'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('tags.id'), nullable=False)
class Role(db.Model): __tablename__ = 'roles' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(50), unique=True) def __init__(self, name): self.name = name def __repr__(self): return self.name
class RoleModel(db.Model): __tablename__ = 'role' __table_args__ = {'schema': cfg_db_schema} id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(80), unique=True) description = db.Column(db.String(255)) permissions = db.relationship('PermissionModel', secondary=role_permission) def __repr__(self): return "<Role %r>" % (self.name)
class User(db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String()) email = db.Column(db.String(), unique=True) password = db.Column(db.String()) admin = db.Column(db.Boolean(), default=False) create_on = db.Column(db.DateTime(), default=datetime.utcnow()) update_on = db.Column(db.DateTime(), default=datetime.utcnow(), onupdate=datetime.utcnow())
class Role(db.Model, RoleMixin): """ Basic Role model """ __tablename__ = "roles" id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.Unicode(80), unique=True) description = db.Column(db.UnicodeText) def __repr__(self): return "{}: {}".format(self.name, self.description)
class Task(Base): __tablename__ = 'task' id = db.Column(db.Integer(), primary_key=True) project_id = db.Column(db.Integer()) project_name = db.Column(db.String(200)) project_task_id = db.Column(db.Integer()) project_task_name = db.Column(db.String(200)) user_id = db.Column(db.Integer()) date = db.Column(db.Date()) updated = db.Column(db.DateTime(timezone=True)) hour = db.Column(db.Float(scale=12, precision=2)) minute = db.Column(db.Float(scale=6, precision=0)) timesheet_id = db.Column(db.Integer()) cost_center_id = db.Column(db.Integer()) decimal_hours = hour + minute / 60.00 @staticmethod def get_my_tasks(user_email): query = '''SELECT DISTINCT t.id, t.project_id, p.name AS project_name, t.project_task_id, pt.name AS project_task_name, t.user_id, t.date, t.updated, t.hour, t.minute, t.timesheet_id, t.cost_center_id FROM task t INNER JOIN project_task pt ON t.project_task_id = pt.id INNER JOIN project p ON pt.project_id = p.id LEFT JOIN user u ON t.user_id = u.id WHERE u.email = :email OR p.id IN (SELECT DISTINCT p2.id FROM project p2 LEFT JOIN project_task pt2 ON pt2.project_id = p2.id LEFT JOIN project_task_assign pta ON pta.project_task_id = pt2.id LEFT JOIN booking b ON b.project_id = p2.id LEFT JOIN user u ON (p2.user_id = u.id OR pta.user_id = u.id OR b.user_id = u.id) WHERE u.email = :email AND p2.active="1");''' return Task.query.from_statement( text(query)).params(email=user_email).all() def __repr__(self): return '<Task %r>' % self.id
class TemplateConfig(db.Model): __tablename__ = 'template_config' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(50)) # 检测项目 seq_content = db.Column(db.String(2000)) # 检测内容 gene_detial = db.Column(db.String(2000)) # 结果详情 method = db.Column(db.String(2000)) # 检测方法和局限性 limit = db.Column(db.String(2000)) # 检测方法和局限性 gene_list = db.Column(db.String(5000)) # 基因列表 def to_dict(self): dict = { 'id': self.id, 'name': self.name, 'seq_content': self.seq_content, 'gene_detail': self.gene_detial, 'method': self.method, 'limit': self.limit, 'gene_list': self.gene_list } return dict
class Recipe(db.Model): __tablename__ = 'recipes' STATUS_ACTIVE = 'active' STATUS_BLOCKED = 'blocked' STATUSES = [STATUS_ACTIVE, STATUS_BLOCKED] TYPE_SALAD = 'salad' TYPE_FIRST_COURSE = 'first course' TYPE_SECOND_COURSE = 'second course' TYPE_SOUP = 'soup' TYPE_DESSERT = 'dessert' TYPE_DRINK = 'drink' TYPES = [ TYPE_SALAD, TYPE_FIRST_COURSE, TYPE_SECOND_COURSE, TYPE_SOUP, TYPE_DESSERT, TYPE_DRINK ] id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(250), nullable=False, index=True) description = db.Column(db.String(3000), nullable=False) created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) status = db.Column(db.String(30), nullable=False, default=STATUS_ACTIVE) type = db.Column(db.String(50), nullable=False, index=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) image_id = db.Column(db.Integer, db.ForeignKey('images.id')) count_likes = db.Column(db.Integer(), default=0) def __init__(self, **kw): super().__init__(**kw) self._recipe_steps = set() self._tags = set() self._user = None @property def recipe_steps(self): return self._recipe_steps @recipe_steps.setter def recipe_steps(self, recipe_steps): self._recipe_steps = recipe_steps def add_recipe_step(self, recipe_step): print(recipe_step) self._recipe_steps.add(recipe_step) @property def tags(self): return self._tags @tags.setter def tags(self, tags): self._tags = tags def add_tag(self, tag): self._tags.add(tag) @property def user(self): return self._user @user.setter def user(self, user): self._user = user
# coding=utf-8 # Created by OhBonsai at 2018/3/7 """User model""" from flask_bcrypt import (generate_password_hash, check_password_hash) from flask_login import UserMixin from app.models.patch import (TagMixin, StatusMixin) from app.models import db # User Group m2m db.relationship db.Table user_group = db.Table( 'user_group', db.metadata, db.Column('user_id', db.Integer(), db.ForeignKey('user.id')), db.Column('group_id', db.Integer(), db.ForeignKey('group.id')), db.PrimaryKeyConstraint('user_id', 'group_id')) class User(UserMixin, db.Model): """Implements the User model.""" username = db.Column(db.String(32), unique=True) password = db.Column(db.String(128)) chinese_name = db.Column(db.String(32)) email = db.Column(db.String(32)) is_admin = db.Column(db.Boolean, default=False) active = db.Column(db.Boolean(), default=True) my_groups = db.relationship('Group', backref='user', lazy='dynamic') groups = db.relationship('Group', secondary=user_group, backref=db.backref('users', lazy='dynamic'))
class Role(db.Model, RoleMixin): __tablename__ = 'roles' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(80), unique=True) description = db.Column(db.String(255))
from flask_login import LoginManager, current_user from flask_security import RoleMixin, UserMixin, SQLAlchemyUserDatastore, Security from sqlalchemy import event from app.load_app import app from app.models import db, BaseMixin # login_manager = LoginManager() # Define models roles_users = db.Table( 'roles_users', db.Column('user_id', db.Integer(), db.ForeignKey('users.id')), db.Column('role_id', db.Integer(), db.ForeignKey('roles.id'))) class Role(BaseMixin, RoleMixin, db.Model): name = db.Column(db.String(80), unique=True) description = db.Column(db.String(255)) event.listen(Role, 'before_insert', Role.before_insert) event.listen(Role, 'before_update', Role.before_update) class User(BaseMixin, UserMixin, db.Model): name = db.Column(db.String(255), unique=True) email = db.Column(db.String(255), unique=True) password = db.Column(db.String(255)) active = db.Column(db.Boolean()) confirmed_at = db.Column(db.DateTime()) roles = db.relationship('Role',