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',
