class Meal(db.Model, TimeStampMixin, SerializableModelMixin):
id = db.Column(db.Integer, primary_key=True)
day = db.Column(db.Enum('mon', 'tue', 'wed', 'thu', 'fri'), nullable=False)
time = db.Column(db.Enum('morning', 'lunch', 'dinner'), nullable=False)
category = db.Column(db.Enum('noodle', 'salad', 'takeout', 'korean',
'western'),
nullable=False)
class UserModel(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), nullable=False)
account_type = db.Column(db.Enum(AccoutTypeEnum), default=AccoutTypeEnum.free)
def to_json(self):
return {'id': self.id,
'name': self.username,
'acc_type': self.account_type.name}
@staticmethod
def get_user_info_by_id(user_id):
"""
Method to get user info (id, name, account type, money) using join
:param user_id:
:return (UserModel, UserBillsModel)
"""
info = db.session.query(UserModel, UserBillsModel).\
join(UserBillsModel).\
filter(UserBillsModel.user_id == user_id)
return info.first()
def __repr__(self):
return '<User: {} with id: {}>'.format(self.username, self.id)
class User(UserMixin, db.Model):
'''
User Model
- NOTE: one column has an array of strings
which some databases might not supported
(i.e Sqlite)
'''
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(15), index=True, nullable=False)
last_name = db.Column(db.String(15), index=True, nullable=False)
email = db.Column(db.String(120), index=True, unique=True, nullable=False)
password_hash = db.Column(db.String(128), nullable=False)
dob = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
gender = db.Column(db.Enum(Gender), nullable=False)
location = db.Column(db.String())
jobs = db.Column(ARRAY(db.String))
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password_hash, password)
def __repr__(self):
return f'User(name={self.first_name} {self.last_name},' \
f'email={self.email},' \
f'dob={self.dob},' \
f'gender={self.gender},' \
f'location={self.location})'
class User(db.Model, RoleFreeUserMixin):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(255))
password = db.Column(db.String(255))
active = db.Column(db.Boolean(), default=False)
confirmed_at = db.Column(db.DateTime())
user_type = db.Column(db.Enum(TypeOfUser, name="type_of_user_types"), nullable=False)
capabilities = db.Column(MutableList.as_mutable(ARRAY(db.String)), default=[])
last_login_at = db.Column(db.DateTime())
current_login_at = db.Column(db.DateTime())
last_login_ip = db.Column(db.String(16))
current_login_ip = db.Column(db.String(16))
login_count = db.Column(db.Integer)
failed_login_count = db.Column(db.Integer, default=0)
# relationships
measures = db.relationship(
"Measure",
lazy="subquery",
secondary="user_measure",
primaryjoin="User.id == user_measure.columns.user_id",
secondaryjoin="Measure.id == user_measure.columns.measure_id",
back_populates="shared_with",
)
__table_args__ = (UniqueConstraint(email, name="uq_users_email"),)
def user_name(self):
return self.email.split("@")[0]
def is_departmental_user(self):
return self.user_type == TypeOfUser.DEPT_USER
def is_rdu_user(self):
return self.user_type == TypeOfUser.RDU_USER
def is_admin_user(self):
return self.user_type == TypeOfUser.ADMIN_USER
def can(self, capability):
return capability in self.capabilities
def can_access_measure(self, measure):
if self.user_type != TypeOfUser.DEPT_USER:
return True
else:
return self in measure.shared_with
# DEPRECATED: use `can_access_measure` method instead.
def can_access(self, measure_slug):
if self.user_type != TypeOfUser.DEPT_USER:
return True
else:
if any(measure.slug == measure_slug for measure in self.measures):
return True
else:
return False
class User(db.Model):
id = db.Column(db.Integer, primary_key = True)
email = db.Column(db.String(60))
username = db.Column(db.String(45))
gender = db.Column(db.Enum('F', 'M'))
password = db.Column(db.String(100))
phone = db.Column(db.String(15))
birthday = db.Column(db.Date)
class Menu(db.Model, TimeStampMixin, SerializableModelMixin):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
price = db.Column(db.String(100))
like_count = db.Column(db.Integer, default=0)
category = db.Column(
db.Enum('rice', 'soup', 'porridge', 'maindish', 'sidedish', 'bread',
'drink', 'fruit', 'noodle', 'salad', "dressing", 'tea'))
class Student(User):
""" Student Model for storing information about students """
__tablename__ = "student"
group_id = db.Column(db.Integer, db.ForeignKey("group.id"), nullable=False)
entrance_year = db.Column(db.Integer, nullable=True, default=None)
education_degree = db.Column(db.Enum(EducationDegree),
nullable=False,
default=EducationDegree.nan)
education_form = db.Column(db.Enum(EducationForm),
nullable=False,
default=EducationForm.nan)
education_basis = db.Column(db.Enum(EducationBasis),
nullable=False,
default=EducationBasis.nan)
def __repr__(self):
return "Student " + super.__repr__()
class User(db.Model, TimeStampMixin, SerializableModelMixin):
__exclude_column_names__ = ('password', )
# 기본정보
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(120), unique=True)
password = db.Column(db.String(200), nullable=False)
nickname = db.Column(db.String(30), nullable=False)
is_admin = db.Column(db.Boolean, default=False)
is_deleted = db.Column(db.Boolean, default=False)
# is_activated = db.Column(db.Boolean, default=True)
# 추가정보
gender = db.Column(db.Enum('male', 'female'))
birth = db.Column(db.Date)
def __repr__(self):
return '<User %r>' % self.id
def get_token(self):
return jwt_encode({
"email": self.email,
"user_id": self.id,
"is_admin": self.is_admin
})
@classmethod
def password_process(cls, request_body):
"""
image, password p
:param request_body:
:return:
"""
# if 'image' in request_body:
# image_string_based64_encoded = request_body.pop('image')
# image = Image.add(image_string_based64_encoded, folder="image")
# request_body['profile_serving_url'] = image.serving_url
if 'password' in request_body:
if request_body['password'] is None:
request_body['password'] = password_encode(
request_body.get('newPassword'))
print request_body
if 'newPassword' in request_body:
request_body.pop('newPassword', None)
if 'newPasswordCheck' in request_body:
request_body.pop('newPasswordCheck', None)
return request_body
def update_data(self, **kwargs):
# pre operation before
kwargs = User.password_process(kwargs)
# check constraint
return super(User, self).update_data(**kwargs)
class Status(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Enum('not delayed', 'delayed'),
nullable=False,
default='not delayed')
line_id = db.Column(db.Integer, db.ForeignKey('line.id'))
line = db.relationship("Line", back_populates="statuses")
create_time = db.Column(db.DateTime, default=datetime.now)
update_time = db.Column(db.DateTime,
default=datetime.now,
onupdate=datetime.now)
class User(db.Model):
__tablename__ = 'user'
Roles = ['user', 'admin', 'superuser']
id = db.Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True, primary_key=True)
username = db.Column(db.String())
role = db.Column(db.Enum(*Roles, name='user_role'), default='user')
email = db.Column(db.String())
gpg_key = db.Column(db.String())
created_at = db.Column(db.TIMESTAMP, default=datetime.datetime.now)
updated_at = db.Column(db.TIMESTAMP, default=datetime.datetime.now)
def __repr__(self):
return f'<id {self.id}>'
class Build(db.Model):
__tablename__ = "build"
__table_args__ = (PrimaryKeyConstraint("id",
"created_at",
name="build_pkey"), {})
id = db.Column(UUID, nullable=False)
created_at = db.Column(db.DateTime,
default=datetime.utcnow,
nullable=False)
status = db.Column(db.Enum(BuildStatus, name="build_status"),
default=BuildStatus.PENDING,
nullable=False)
succeeded_at = db.Column(db.DateTime, nullable=True)
failure_reason = db.Column(db.String, nullable=True)
failed_at = db.Column(db.DateTime, nullable=True)
class Request(db.Model):
__tablename__ = 'request'
RequestStatus = ['pending', 'approved', 'declined']
id = db.Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True, primary_key=True)
user_id = db.Column(UUID(), db.ForeignKey(User.id), nullable=False)
reference_id = db.Column(db.Integer, default=get_request_ref_id, unique=True)
project_name = db.Column(db.String())
project_desc = db.Column(db.String())
status = db.Column(db.Enum(*RequestStatus, name='request_status'), default='pending')
updated_by = db.Column(db.String())
created_at = db.Column(db.TIMESTAMP, default=datetime.datetime.now)
updated_at = db.Column(db.TIMESTAMP, default=datetime.datetime.now)
def __repr__(self):
return f'<id {self.id}>'
class Server(db.Model):
id = db.Column(db.Integer, primary_key=True)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp())
name = db.Column(db.String(144), nullable=False)
description = db.Column(db.String(500), nullable=True)
status = db.Column(db.Enum(Status), default=Status.Offline)
creator_id = db.Column(db.Integer, db.ForeignKey('account.id'))
creator = db.relationship("User", back_populates="ownServers")
game_accounts = db.relationship(
"GameAccountServer", back_populates="server")
def __init__(self, name, status, creator, description=""):
self.name = name
self.status = status
self.creator_id = creator.id
self.description = description
class Exercise(db.Model):
__tablename__ = 'exercises'
id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.String())
duration = db.Column(db.String())
category = db.Column(db.Enum(SomaticCategory))
def __init__(self, url, duration, category):
self.url = url
self.duration = duration
self.category = category
def __repr__(self):
return f"<Exercise {self.id}>"
def delete(self):
db.session.delete(self)
db.session.commit()
class Users(UserMixin, db.Model):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(8), nullable=True, unique=True)
email = db.Column(db.String(50), nullable=True, unique=True)
phone = db.Column(db.Integer, nullable=True, unique=True)
pwd = db.Column(db.String(100), nullable=False)
gender = db.Column(db.Enum('male', 'female'), nullable=True)
age = db.Column(db.Integer, nullable=False, server_default='18')
regesiter_date = db.Column(db.DateTime,
nullable=False,
server_default=func.now())
#注册时是否验证标志
confirmed = db.Column(db.Boolean, default=False)
#hash加密
def set_password(self, password):
self.pwd = generate_password_hash(password)
#检查密码是否与hash值匹配
def check_password(self, password):
return check_password_hash(self.pwd, password)
#生成验证时token
def generate_confirmation_token(self, expiration=3600):
s = Serializer(current_app.config['SECRET_KEY'], expiration)
return s.dumps({'confirm': self.id})
#验证通过
def confirm(self, token):
s = Serializer(current_app.config['SECRET_KEY'])
try:
data = s.loads(token)
except:
return False
if data.get('confirm') != self.id:
return False
self.confirmed = True
db.session.add(self)
return True
class User(UserMixin, db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(), unique=True, nullable=False)
password = db.Column(db.String(), nullable=False)
created_date = db.Column(db.DateTime(), default=datetime.datetime.utcnow)
user_type = db.Column(db.Enum(UserTypeModel), default=UserTypeModel.FREE)
#Child
habits = relationship("Habit",
back_populates="user",
cascade="all, delete, delete-orphan")
def __init__(self, email, password):
self.password = password
self.email = email
def __repr__(self):
return self.email
class WHMCSaddons(db.Model):
''' Table inside WHMCS Database (the billing system)
Contains a list of family memberships.
'''
def __init__(self, hostingid=None, status=None, nextduedate=None):
self.hostingid = hostingid
self.status = status
self.nextduedate = nextduedate
__tablename__ = u'tblhostingaddons'
__table_args__ = {'schema': 'dms-whmcs'}
id = db.Column(db.Integer, primary_key=True)
hostingid = db.Column(db.Integer, db.ForeignKey('dms-whmcs.tblhosting.id'),
nullable=False, index=True)
status = db.Column(db.Enum(u'Pending', u'Active', u'Suspended',
u'Terminated', u'Cancelled', u'Fraud'),
nullable=False, index=True,
server_default=db.text("'Pending'"))
nextduedate = db.Column(db.Date)
class Session(Base):
__tablename__ = "sessions"
id = db.Column(db.Integer, primary_key=True)
start = db.Column(db.DateTime, nullable=False)
end = db.Column(db.DateTime, nullable=False)
comments = db.Column(db.String, default="")
status = db.Column(db.Enum(StatusSessionEnum),
default=StatusSessionEnum.PENDING,
server_default=StatusSessionEnum.PENDING.name,
nullable=False)
patient_id = db.Column(db.Integer,
db.ForeignKey('patients.id'),
nullable=False)
patient = db.relationship('Patient',
backref=db.backref('sessions', lazy=True))
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
user = db.relationship('User', backref=db.backref('sessions', lazy=True))
def __init__(self, start, end, patient_id, user_id):
# datetime.strptime('2021-03-07 03:22', "%Y-%m-%d %H:%M")
self.start = datetime.strptime(start, "%Y-%m-%d %H:%M")
self.end = datetime.strptime(end, "%Y-%m-%d %H:%M")
self.patient_id = patient_id
self.user_id = user_id
def valid(self):
if self.start >= self.end: return False
try:
sessions = Session.query.filter_by(user_id=self.user_id)
if sessions.count() == 0: return True
sessions = sessions.filter(Session.id != self.id)
sessions = sessions.filter(
((Session.start >= self.start) & (Session.start <= self.end))
| ((Session.end >= self.start) & (Session.end <= self.end)))
return sessions.count() < 1
except:
return False
class WHMCSproducts(db.Model):
''' Table inside WHMCS Database (the billing system)
This table contains a list of all regular memberships, but not all family members.
'''
def __init__(self, userid=None, nextduedate=None, domainstatus=None):
self.userid = userid
self.nextduedate = nextduedate
self.domainstatus = domainstatus
__tablename__ = u'tblhosting'
__table_args__ = {'schema': 'dms-whmcs'}
id = db.Column(db.Integer, primary_key=True, index=True)
userid = db.Column(db.Integer, db.ForeignKey('dms-whmcs.tblclients.id'),
nullable=False, index=True)
nextduedate = db.Column(db.Date)
domainstatus = db.Column(db.Enum(u'Pending', u'Active', u'Suspended',
u'Terminated', u'Cancelled', u'Fraud'),
nullable=False, index=True)
addons = db.relationship('WHMCSaddons', backref='tblhosting')
class Organisation(db.Model):
id = db.Column(db.String(255), primary_key=True)
name = db.Column(db.String(255), nullable=False)
other_names = db.Column(ARRAY(db.String), default=[])
abbreviations = db.Column(ARRAY(db.String), default=[])
organisation_type = db.Column(db.Enum(TypeOfOrganisation, name="type_of_organisation_types"), nullable=False)
pages = relationship("Page", back_populates="department_source", foreign_keys=[Page.department_source_id])
@classmethod
def select_options_by_type(cls):
organisations_by_type = []
for org_type in TypeOfOrganisation:
orgs = cls.query.filter_by(organisation_type=org_type).all()
organisations_by_type.append((org_type, orgs))
return organisations_by_type
def abbreviations_data(self):
return "|".join(self.abbreviations)
def other_names_data(self):
return "|".join(self.other_names)
class Machine(db.Model):
__tablename__ = "machine"
id = db.Column(db.Integer, primary_key=True)
repair_logs = db.relationship("RepairLog", back_populates="machine")
type = db.Column(db.Enum(MachineType), nullable=False)
description = db.Column(db.String(32), nullable=False)
number = db.Column(db.Integer, nullable=False)
model = db.Column(db.String(32), nullable=False)
serial = db.Column(db.String(64))
active = db.Column(db.Boolean, nullable=False)
__mapper_args__ = {
"polymorphic_identity": "machine",
"polymorphic_on": type
}
def __init__(self, active=True, **kwargs):
super().__init__(active=active, **kwargs)
def __repr__(self):
return "{}:{}:{}:{}:{}:{}".format(self.id, self.type.value,
self.number, self.model, self.serial,
self.active)
class User(db.Model, RoleFreeUserMixin):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(255), unique=True)
password = db.Column(db.String(255))
active = db.Column(db.Boolean(), default=False)
confirmed_at = db.Column(db.DateTime())
user_type = db.Column(db.Enum(TypeOfUser, name="type_of_user_types"),
nullable=False)
capabilities = db.Column(MutableList.as_mutable(ARRAY(db.String)),
default=[])
def user_name(self):
return self.email.split("@")[0]
def is_departmental_user(self):
return self.user_type == TypeOfUser.DEPT_USER
def is_rdu_user(self):
return self.user_type == TypeOfUser.RDU_USER
def is_admin_user(self):
return self.user_type == TypeOfUser.ADMIN_USER
def can(self, capability):
return capability in self.capabilities
def can_access(self, page_id):
if self.user_type != TypeOfUser.DEPT_USER:
return True
else:
for page in self.pages:
if page.guid == page_id or page.uri == page_id:
return True
else:
return False
class Item(db.Model):
''' An item that can be bought or borrowed
Some methods require the request-context.
TODO: Proper context validation with exceptions et al.'''
id = db.Column(db.String(), primary_key=True)
name = db.Column(db.Unicode(), unique=True)
description = db.Column(db.UnicodeText)
count = db.Column(db.Integer, default=1)
# The corresponding image is saved saved under uploads/<entity.id>.jpg
tax_base_int = db.Column(db.Float, default=0)
tax_base_edu = db.Column(db.Float, default=0)
tax_base_ext = db.Column(db.Float, default=0)
tax_int = db.Column(db.Float)
tax_edu = db.Column(db.Float)
tax_ext = db.Column(db.Float)
tax_period = db.Column(db.Enum('week', 'day'), default='week')
price_buy = db.Column(db.Float)
category = db.Column(db.Enum(*CATEGORIES))
related = db.relationship(
'Item',
secondary=related,
primaryjoin=id == related.c.item_id,
secondaryjoin=id == related.c.related_item_id,
)
def __repr__(self):
return u"<Item %s>" % (self.id)
def in_stock(self, start, end=None):
''' Returns the amount of this item in stock during the selected time-
span.
'''
if not end:
end = start
affected_tas = Transaction.query.filter(
db.and_(
Transaction.date_end >= start,
Transaction.date_start <= end,
)).all()
out = 0
for ta in affected_tas:
if not self.id in ta.lend:
continue
out += ta.lend[self.id].amount
return self.count - out
@property
def buying(self):
ta = g.ta
if not self.id in ta.buy:
return 0
return ta.buy[self.id].amount
@property
def lending(self):
ta = g.ta
if not self.id in ta.lend:
return 0
return ta.lend[self.id].amount
@property
def available(self):
return self.count - self.lending
@property
def lendable(self):
return self.tax() is not None
@property
def buyable(self):
return self.price_buy is not None
def tax_base(self, transaction=None):
ta = transaction or g.ta
return getattr(self, 'tax_base_' + ta.group)
def tax(self, transaction=None):
ta = transaction or g.ta
return getattr(self, 'tax_' + ta.group)
def tax_ta(self, transaction=None):
ta = transaction or g.ta
from math import ceil
if self.tax_period == 'week':
periods = int(ceil(ta.days / 7.0))
else:
periods = ta.days
if self.tax_base(ta) is None or self.tax(ta) is None:
return -1.0
return self.tax_base(ta) + periods * self.tax(ta)
class Store(db.Model, TimeStampMixin, SerializableModelMixin):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
category = db.Column(db.Enum('korean', 'western', 'soup', 'pork', 'beef',
'chinese', 'noodle'),
nullable=False)
class User(db.Model):
__tablename__ = 'user'
Host = db.Column(db.String(60, 'utf8_bin'),
primary_key=True,
nullable=False,
server_default=db.FetchedValue())
User = db.Column(db.String(32, 'utf8_bin'),
primary_key=True,
nullable=False,
server_default=db.FetchedValue())
Select_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Insert_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Update_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Delete_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Create_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Drop_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Reload_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Shutdown_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Process_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
File_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Grant_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
References_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Index_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Alter_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Show_db_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Super_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Create_tmp_table_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Lock_tables_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Execute_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Repl_slave_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Repl_client_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Create_view_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Show_view_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Create_routine_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Alter_routine_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Create_user_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Event_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Trigger_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
Create_tablespace_priv = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
ssl_type = db.Column(db.Enum('', 'ANY', 'X509', 'SPECIFIED'),
nullable=False,
server_default=db.FetchedValue())
ssl_cipher = db.Column(db.LargeBinary, nullable=False)
x509_issuer = db.Column(db.LargeBinary, nullable=False)
x509_subject = db.Column(db.LargeBinary, nullable=False)
max_questions = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
max_updates = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
max_connections = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
max_user_connections = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue())
plugin = db.Column(db.String(64, 'utf8_bin'),
nullable=False,
server_default=db.FetchedValue())
authentication_string = db.Column(db.Text(collation='utf8_bin'))
password_expired = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
password_last_changed = db.Column(db.DateTime)
password_lifetime = db.Column(db.SmallInteger)
account_locked = db.Column(db.Enum('N', 'Y'),
nullable=False,
server_default=db.FetchedValue())
class Page(db.Model):
"""
The Page model holds data about all pages in the page hierarchy of the website:
Homepage (root) -> Topics -> Subtopics -> Measure pages (leaves)
Most of our Pages are measure pages, and many of the fields in this model are only relevant to measure pages.
Home, topic and subtopic pages define the structure of the site through parent-child relationships.
A measure page can have multiple versions in different states (e.g. versions 1.0 and 1.1 published, 2.0 in draft).
Each version of a measure page is one record in the Page model, so we have a compound key consisting of `guid`
coupled with `version`.
"""
def __eq__(self, other):
return self.guid == other.guid and self.version == other.version
def __hash__(self):
return hash((self.guid, self.version))
def __lt__(self, other):
if self.major() < other.major():
return True
elif self.major() == other.major() and self.minor() < other.minor():
return True
else:
return False
# PAGE ORGANISATION, LIFECYCLE AND METADATA
# =========================================
guid = db.Column(db.String(255), nullable=False) # identifier for a measure (but not a page)
version = db.Column(db.String(), nullable=False) # combined with guid forms primary key for page table
internal_reference = db.Column(db.String()) # optional internal reference number for measures
latest = db.Column(db.Boolean, default=True) # True if the current row is the latest version of a measure
# (latest created, not latest published, so could be a new draft)
uri = db.Column(db.String(255)) # slug to be used in URLs for the page
review_token = db.Column(db.String()) # used for review page URLs
description = db.Column(db.Text) # TOPIC PAGES ONLY: a sentence below topic heading on homepage
additional_description = db.Column(db.TEXT) # TOPIC PAGES ONLY: short paragraph displayed on topic page itself
page_type = db.Column(db.String(255)) # one of measure, homepage, subtopic, topic
position = db.Column(db.Integer, default=0) # ordering for MEASURE and SUBTOPIC pages
# status for measure pages is one of APPROVED, DRAFT, DEPARTMENT_REVIEW, INTERNAL_REVIEW, REJECTED, UNPUBLISHED
# but it's free text in the DB and for other page types we have NULL or "draft" ¯\_(ツ)_/¯
status = db.Column(db.String(255))
created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False) # timestamp when page created
created_by = db.Column(db.String(255)) # email address of user who created the page
updated_at = db.Column(db.DateTime) # timestamp when page updated
last_updated_by = db.Column(db.String(255)) # email address of user who made the most recent update
# Only MEASURE PAGES are published. All other pages have published=False (or sometimes NULL)
published = db.Column(db.BOOLEAN, default=False) # set to True when a page version is published
publication_date = db.Column(db.Date) # date set automatically by CMS when a page version is published
published_by = db.Column(db.String(255)) # email address of user who published the page
unpublished_by = db.Column(db.String(255)) # email address of user who unpublished the page
# parent_guid defines the hierarchy between pages of the site
# TOPIC pages have "homepage" as parent_guid
# SUBTOPIC pages have "topic_xxx" as parent_guid
# MEASURE pages have "subtopic_xxx" as parent_guid
# The homepage and test area topic page have no parent_guid
parent_guid = db.Column(db.String(255))
parent_version = db.Column(db.String()) # version number of the parent page, as guid+version is PK
parent = relation("Page", remote_side=[guid, version], backref=backref("children", order_by="Page.position"))
__table_args__ = (
PrimaryKeyConstraint("guid", "version", name="page_guid_version_pk"),
ForeignKeyConstraint([parent_guid, parent_version], ["page.guid", "page.version"]),
UniqueConstraint("guid", "version", name="uix_page_guid_version"),
Index("ix_page_type_uri", page_type, uri),
{},
)
db_version_id = db.Column(db.Integer, nullable=False) # used to detect and prevent stale updates
__mapper_args__ = {"version_id_col": db_version_id}
# Uploads and dimensions belonging to this measure can be discovered through these relationships
uploads = db.relationship("Upload", backref="page", lazy="dynamic", cascade="all,delete")
dimensions = db.relationship(
"Dimension", backref="page", lazy="dynamic", order_by="Dimension.position", cascade="all,delete"
)
# MEASURE-PAGE DATA
# =================
title = db.Column(db.String(255)) # <h1> on measure page
summary = db.Column(db.TEXT) # "The main facts and figures show that..." bullets at top of measure page
need_to_know = db.Column(db.TEXT) # "Things you need to know" on a measure page
measure_summary = db.Column(db.TEXT) # "What the data measures" on measure page
ethnicity_definition_summary = db.Column(db.TEXT) # "The ethnicies used in this data" on a measure page
# Measure metadata
# ----------------
area_covered = db.Column(ArrayOfEnum(db.Enum(UKCountry, name="uk_country_types")), default=[]) # public metadata
time_covered = db.Column(db.String(255)) # public metadata
external_edit_summary = db.Column(db.TEXT) # notes on new version, displayed on public measure page
internal_edit_summary = db.Column(db.TEXT) # internal notes on new version, not displayed on public measure page
# lowest_level_of_geography is not displayed on the public site but is used for geographic dashboard
lowest_level_of_geography_id = db.Column(
db.String(255), ForeignKey("lowest_level_of_geography.name"), nullable=True
)
lowest_level_of_geography = relationship("LowestLevelOfGeography", back_populates="pages")
# Contact details for measure - not displayed on public site!
contact_name = db.Column(db.TEXT) # name of "Contact 1"
contact_phone = db.Column(db.TEXT) # phone of "Contact 1"
contact_email = db.Column(db.TEXT) # email address of "Contact 1"
contact_2_name = db.Column(db.TEXT) # name of "Contact 2"
contact_2_phone = db.Column(db.TEXT) # phone of "Contact 2"
contact_2_email = db.Column(db.TEXT) # email address of "Contact 2"
# Departmental users can only access measure pages that have been shared with them, as defined by this relationship
shared_with = db.relationship(
"User",
lazy="subquery",
secondary=user_page,
primaryjoin="Page.guid == user_page.columns.page_id",
secondaryjoin="User.id == user_page.columns.user_id",
backref=db.backref("pages", lazy=True),
)
# Methodology section
# -------------------
methodology = db.Column(db.TEXT) # "Methodology"
suppression_and_disclosure = db.Column(db.TEXT) # "Suppression rules and disclosure control"
estimation = db.Column(db.TEXT) # "Rounding"
related_publications = db.Column(db.TEXT) # "Related publications"
qmi_url = db.Column(db.TEXT) # "Quality and methodology information"
further_technical_information = db.Column(db.TEXT) # "Further technical information"
# Primary Source
# --------------
# TODO: rename these to be consistent with secondary sources.
source_text = db.Column(db.TEXT) # "Source" link text for primary data source_url
source_url = db.Column(db.TEXT) # "Source" URL for the primary data source
# "Type of data" in primary Data sources section; zero or more of (ADMINISTRATIVE, SURVEY)
type_of_data = db.Column(ArrayOfEnum(db.Enum(TypeOfData, name="type_of_data_types")), default=[])
# "Type of statistic" in primary Data sources section
type_of_statistic_id = db.Column(db.Integer, ForeignKey("type_of_statistic.id"))
type_of_statistic_description = relationship("TypeOfStatistic", foreign_keys=[type_of_statistic_id])
# "Publisher" in primary Data sources section
department_source_id = db.Column(db.String(255), ForeignKey("organisation.id"), nullable=True)
department_source = relationship("Organisation", foreign_keys=[department_source_id], back_populates="pages")
published_date = db.Column(db.String(255)) # "Date first published" for primary source (not currently shown)
note_on_corrections_or_updates = db.Column(db.TEXT) # "Note on corrections or updates" for primary source
# "Publication frequency" in primary Data sources section
frequency_id = db.Column(db.Integer, ForeignKey("frequency_of_release.id"))
frequency_of_release = relationship("FrequencyOfRelease", foreign_keys=[frequency_id])
frequency_other = db.Column(db.String(255)) # free text for when "Other" is chosen for frequency_of_release
data_source_purpose = db.Column(db.TEXT) # "Purpose of data source" in primary Data sources section
# Secondary Source
# ----------------
secondary_source_1_title = db.Column(db.TEXT) # "Source" link text for secondary data source_url
secondary_source_1_url = db.Column(db.TEXT) # "Source" URL for the secondary data source
# "Type of data" in secondary Data sources section; zero or more of (ADMINISTRATIVE, SURVEY)
secondary_source_1_type_of_data = db.Column(ArrayOfEnum(db.Enum(TypeOfData, name="type_of_data_types")), default=[])
# "Type of statistic" in secondary Data sources section
secondary_source_1_type_of_statistic_id = db.Column(db.Integer, ForeignKey("type_of_statistic.id"))
secondary_source_1_type_of_statistic_description = relationship(
"TypeOfStatistic", foreign_keys=[secondary_source_1_type_of_statistic_id]
) # noqa
# "Publisher" in secondary Data sources section
secondary_source_1_publisher_id = db.Column(
db.String(255), ForeignKey("organisation.id", name="organisation_secondary_source_1_fkey"), nullable=True
)
secondary_source_1_publisher = relationship("Organisation", foreign_keys=[secondary_source_1_publisher_id])
secondary_source_1_date = db.Column(db.TEXT) # "Date first published" for secondary source (not currently shown)
secondary_source_1_note_on_corrections_or_updates = db.Column(db.TEXT) # "Note on corrections or updates" 2ndary
# "Publication frequency" in secondary Data sources section
secondary_source_1_frequency_id = db.Column(
db.Integer, ForeignKey("frequency_of_release.id", name="frequency_secondary_source_1_fkey")
)
secondary_source_1_frequency_of_release = relationship(
"FrequencyOfRelease", foreign_keys=[secondary_source_1_frequency_id]
)
secondary_source_1_frequency_other = db.Column(db.String(255)) # free text for when "Other" is chosen for frequency
secondary_source_1_data_source_purpose = db.Column(db.TEXT) # "Purpose of data source" in secondary Data sources
# Returns an array of measures which have been published, and which
# were either first version (1.0) or the first version of an update
# eg (2.0, 3.0, 4.0) but not a minor update (1.1 or 2.1).
@classmethod
def published_major_versions(cls):
return cls.query.filter(
cls.publication_date.isnot(None), cls.version.endswith(".0"), cls.page_type == "measure"
)
# Returns an array of measures which have been published, and which
# were the first version (1.0)
@classmethod
def published_first_versions(cls):
return cls.query.filter(cls.publication_date.isnot(None), cls.version == "1.0", cls.page_type == "measure")
# Returns an array of published subsequent (major) updates at their initial
# release (eg 2.0, 3.0, 4.0 and so on...)
@classmethod
def published_updates_first_versions(cls):
return cls.query.filter(
cls.publication_date.isnot(None),
cls.page_type == "measure",
cls.version.endswith(".0"),
not_(cls.version == "1.0"),
)
def get_dimension(self, guid):
try:
dimension = Dimension.query.filter_by(guid=guid, page=self).one()
return dimension
except NoResultFound as e:
raise DimensionNotFoundException
def get_upload(self, guid):
try:
upload = Upload.query.filter_by(guid=guid).one()
return upload
except NoResultFound as e:
raise UploadNotFoundException
def publish_status(self, numerical=False):
current_status = self.status.upper()
if numerical:
return publish_status[current_status]
else:
return current_status
def available_actions(self):
if self.parent.parent.guid == "topic_testingspace":
return ["UPDATE"]
if self.status == "DRAFT":
return ["APPROVE", "UPDATE"]
if self.status == "INTERNAL_REVIEW":
return ["APPROVE", "REJECT"]
if self.status == "DEPARTMENT_REVIEW":
return ["APPROVE", "REJECT"]
if self.status == "APPROVED":
return ["UNPUBLISH"]
if self.status in ["REJECTED", "UNPUBLISHED"]:
return ["RETURN_TO_DRAFT"]
else:
return []
def next_state(self):
num_status = self.publish_status(numerical=True)
if num_status == 0:
# You can only get out of rejected state by saving
message = 'Page "{}" is rejected.'.format(self.title)
raise CannotPublishRejected(message)
elif num_status <= 3:
new_status = publish_status.inv[num_status + 1]
self.status = new_status
return 'Sent page "{}" to {}'.format(self.title, new_status)
else:
message = 'Page "{}" is already approved'.format(self.title)
raise AlreadyApproved(message)
def reject(self):
if self.status == "APPROVED":
message = 'Page "{}" cannot be rejected in state {}'.format(self.title, self.status)
raise RejectionImpossible(message)
rejected_state = "REJECTED"
message = 'Sent page "{}" to {}'.format(self.title, rejected_state)
self.status = rejected_state
return message
def unpublish(self):
unpublish_state = publish_status.inv[5]
message = 'Request to un-publish page "{}" - page will be removed from site'.format(self.title)
self.status = unpublish_state
return message
def not_editable(self):
if self.publish_status(numerical=True) == 5:
return False
else:
return self.publish_status(numerical=True) >= 2
def eligible_for_build(self):
return self.status == "APPROVED"
def major(self):
return int(self.version.split(".")[0])
def minor(self):
return int(self.version.split(".")[1])
def next_minor_version(self):
return "%s.%s" % (self.major(), self.minor() + 1)
def next_major_version(self):
return "%s.0" % str(self.major() + 1)
def next_version_number_by_type(self, version_type):
if version_type == "copy":
return "1.0"
if version_type == "minor":
return self.next_minor_version()
return self.next_major_version()
def latest_version(self):
versions = self.get_versions()
versions.sort(reverse=True)
return versions[0] if versions else self
def number_of_versions(self):
return len(self.get_versions())
def has_minor_update(self):
return len(self.minor_updates()) > 0
def has_major_update(self):
return len(self.major_updates()) > 0
def is_minor_version(self):
return self.minor() != 0
def is_major_version(self):
return not self.is_minor_version()
def get_versions(self, include_self=True):
if include_self:
return self.query.filter(Page.guid == self.guid).all()
else:
return self.query.filter(Page.guid == self.guid, Page.version != self.version).all()
def get_previous_version(self):
versions = self.get_versions(include_self=False)
versions.sort(reverse=True)
return versions[0] if versions else None
def has_no_later_published_versions(self):
updates = self.minor_updates() + self.major_updates()
published = [page for page in updates if page.status == "APPROVED"]
return len(published) == 0
@property
def is_published_measure_or_parent_of(self):
if self.page_type == "measure":
return self.published
return any(child.is_published_measure_or_parent_of for child in self.children)
def minor_updates(self):
versions = Page.query.filter(Page.guid == self.guid, Page.version != self.version)
return [page for page in versions if page.major() == self.major() and page.minor() > self.minor()]
def major_updates(self):
versions = Page.query.filter(Page.guid == self.guid, Page.version != self.version)
return [page for page in versions if page.major() > self.major()]
def format_area_covered(self):
if self.area_covered is None:
return ""
if len(self.area_covered) == 0:
return ""
if len(self.area_covered) == 1:
return self.area_covered[0].value
else:
last = self.area_covered[-1]
first = self.area_covered[:-1]
comma_separated = ", ".join([item.value for item in first])
return "%s and %s" % (comma_separated, last.value)
def to_dict(self, with_dimensions=False):
page_dict = {
"guid": self.guid,
"title": self.title,
"measure_summary": self.measure_summary,
"summary": self.summary,
"area_covered": self.area_covered,
"lowest_level_of_geography": self.lowest_level_of_geography,
"time_covered": self.time_covered,
"need_to_know": self.need_to_know,
"ethnicity_definition_summary": self.ethnicity_definition_summary,
"source_text": self.source_text,
"source_url": self.source_url,
"department_source": self.department_source,
"published_date": self.published_date,
"frequency": self.frequency_of_release.description if self.frequency_of_release else None,
"related_publications": self.related_publications,
"contact_name": self.contact_name,
"contact_phone": self.contact_phone,
"contact_email": self.contact_email,
"data_source_purpose": self.data_source_purpose,
"methodology": self.methodology,
"type_of_data": [t.name for t in self.type_of_data] if self.type_of_data else None,
"suppression_and_disclosure": self.suppression_and_disclosure,
"estimation": self.estimation,
"type_of_statistic": self.type_of_statistic_description.external
if self.type_of_statistic_description
else None,
"qmi_url": self.qmi_url,
"further_technical_information": self.further_technical_information,
}
if with_dimensions:
page_dict["dimensions"] = []
for dimension in self.dimensions:
page_dict["dimensions"].append(dimension.to_dict())
return page_dict
def review_token_expires_in(self, config):
try:
token_age = get_token_age(self.review_token, config)
max_token_age_days = config.get("PREVIEW_TOKEN_MAX_AGE_DAYS")
expiry = token_age + timedelta(days=max_token_age_days)
days_from_now = expiry.date() - datetime.today().date()
return days_from_now.days
except Exception:
return 0
class Transaction(db.Model):
''' Whenever an item is lent or bought, a transaction entity is created '''
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Unicode())
email = db.Column(db.Unicode())
tel = db.Column(db.Unicode())
group = db.Column(db.Enum(*GROUPS))
payment = db.Column(db.Unicode())
delivery = db.Column(db.Unicode()) # Address
remarks = db.Column(db.UnicodeText())
progress = db.Column(db.Enum(*PROGRESS), default='new')
buy = db.relationship(
'Buy',
backref='transaction',
collection_class=attribute_mapped_collection('item_id'),
cascade="save-update, merge, delete, delete-orphan")
lend = db.relationship(
'Lend',
backref='transaction',
collection_class=attribute_mapped_collection('item_id'),
cascade="save-update, merge, delete, delete-orphan")
date_start = db.Column(db.Date)
date_end = db.Column(db.Date)
date = db.Column(db.DateTime)
@property
def days(self):
if not self.date_end or not self.date_start:
return 0
return (self.date_end - self.date_start).days
@property
def weeks(self):
from math import ceil
return int(ceil(self.days / 7.0))
@property
def n_in_cart(self):
lend = [item.amount for item in self.lend.values()]
buy = [item.amount for item in self.buy.values()]
return sum(lend) + sum(buy)
@property
def cost(self):
lends = [il.cost(self) for il in self.lend.values()]
buys = [il.cost() for il in self.buy.values()]
return sum(lends) + sum(buys)
def __init__(self):
self.group = 'int'
self.date = datetime.datetime.utcnow()
def __repr__(self):
if self.id is not None:
return u"<Transaction %u>" % (self.id)
return u"<Transaction (no ID)>"
from application import db
genders = db.Enum('男', '女', ' ')
course_categories = db.Enum('学科基础', '专业必修', '专业选修', '跨学科专业选修',
' ')
departments = db.Enum('哲', '文', '历史', '国', '艺术', '外国语', '新闻', '农业与农村发展',
'社会与人口', '公共管理', '信息资源管理', '经济', '应用经济', '财政金融', '统计',
'商', '劳动人事', '中法', '法', '马克思主义', '国际关系', '环境', '信息', '理',
'数学', '高瓴人工智能', ' ')
class Teacher(db.Model):
__tablename__ = '教师'
id = db.Column('教职工号', db.Integer, primary_key=True)
name = db.Column('姓名', db.String(10), nullable=False)
gender = db.Column('性别', genders, nullable=False)
phone_number = db.Column('电话', db.String(11), unique=True, nullable=False)
email = db.Column('邮箱', db.String(30), unique=True, nullable=False)
password = db.Column('密码', db.String(20), nullable=False)
title = db.Column('职称', db.String(10), nullable=False)
department = db.Column('所属学院', departments, nullable=False)
def __repr__(self):
return f'<教师 {self.id}>'
class Course(db.Model):
__tablename__ = '课程'
id = db.Column('课程号', db.Integer, primary_key=True)