class PointStatusModel(db.Model, BaseMixin):
__tablename__ = 'point_status'
student_id: str = db.Column(db.String, db.ForeignKey('student.id', ondelete='CASCADE'), primary_key=True)
good_point: int = db.Column(db.Integer, default=0)
bad_point: int = db.Column(db.Integer, default=0)
penalty_level: int = db.Column(db.Integer, default=0)
penalty_status: bool = db.Column(db.Boolean, default=False)
def __init__(self, student_id: str):
self.student_id = student_id
@staticmethod
def get_point_status(student_id: str):
point_status: PointStatusModel = PointStatusModel.query.filter_by(student_id=student_id).first()
if point_status is None:
point_status = PointStatusModel(student_id).save()
return {
'badPoint': point_status.bad_point,
'goodPoint': point_status.good_point,
'penaltyLevel': point_status.penalty_level,
'penaltyStatus': point_status.penalty_status,
'advice': get_advice(point_status.bad_point)
}
class NoticeModel(db.Model, BaseMixin):
__tablename__ = 'notice'
id: int = db.Column(db.Integer, primary_key=True)
post_date: datetime = db.Column(db.DateTime)
title: str = db.Column(db.String(50))
content: str = db.Column(db.String(2000))
def __init__(self, title: str, content: str):
self.post_date: datetime = self.kst_now()
self.title: str = title
self.content: str = content
@staticmethod
def get_notice_list() -> dict:
return {
'noticeList': [
{
'id': notice.id,
'postDate': str(notice.post_date),
'title': notice.title
} for notice in NoticeModel.query.order_by(desc(NoticeModel.post_date)).all()
]
}
@staticmethod
def get_notice(notice_id) -> dict:
notice: NoticeModel = NoticeModel.query.filter_by(id=notice_id).first()
if notice is None:
raise NoContentException()
return {
'content': notice.content,
'title': notice.title,
'postDate': str(notice.post_date)
}
class RuleModel(db.Model, BaseMixin):
__tablename__ = 'rule'
id: int = db.Column(db.Integer, primary_key=True)
post_date: datetime = db.Column(db.DateTime)
title: str = db.Column(db.String(50))
content: str = db.Column(db.String(200))
def __init__(self, title: str, content: str):
self.post_date: datetime = BaseMixin.kst_now()
self.title: str = title
self.content: str = content
@staticmethod
def get_rule_list() -> dict:
return {
'ruleList': [
{
'id': rule.id,
'postDate': str(rule.post_date),
'title': rule.title
} for rule in RuleModel.query.all()
]
}
@staticmethod
def get_rule(rule_id) -> dict:
rule: RuleModel = RuleModel.query.filter_by(id=rule_id).first()
if rule is None:
raise NoContentException()
return {
'content': rule.content,
'title': rule.title,
'postDate': str(rule.post_date)
}
class Task(db.Model):
__tablename__ = 'task'
task_id = db.Column(db.Integer(), primary_key = True, autoincrement = True)
task_name = db.Column(db.String(64), nullable = False)
description = db.Column(db.Text, nullable = False, default = '')
deadline = db.Column(db.DateTime(), nullable = False)
problems = db.relationship(
'Problem',
secondary = 'problem_in_task',
backref = db.backref('tasks', lazy = 'dynamic'),
lazy = 'dynamic'
)
groups = db.relationship(
'UserGroup',
secondary = 'task_for_usergroup',
backref = db.backref('tasks', lazy = 'dynamic'),
lazy = 'dynamic'
)
@property
def available(self):
return self.deadline > datetime.now()
class PointHistoryModel(db.Model, BaseMixin):
__tablename__ = 'point_history'
id: int = db.Column(db.Integer, primary_key=True)
student_id: str = db.Column(
db.String, db.ForeignKey('student.id', ondelete='CASCADE'))
point_id: int = db.Column(
db.Integer, db.ForeignKey('point_item.id', ondelete='CASCADE'))
point_date: date = db.Column(db.Date)
def __init__(self, student_id: str, point_id: int):
self.student_id: str = student_id
self.point_id: int = point_id
self.point_date: date = date.today()
@staticmethod
def get_point_history(student_id: str):
histories: List[
'PointHistoryModel'] = PointHistoryModel.query.filter_by(
student_id=student_id).all()
result = {'point_history': []}
for history in histories:
temp = PointItemModel.get_point_item(history.point_id)
temp['date'] = str(history.point_date)
result['point_history'].append(temp)
return result
class Role(db.Model):
"""
角色表
"""
__tablename__ = "Role"
role_id = db.Column(
"role_id",
INTEGER,
primary_key=True,
nullable=False,
autoincrement=True,
doc="value is 1 means it is admin which has all permissions")
role_name = db.Column("role_name", VARCHAR(255), nullable=False)
create_time = db.Column("create_time",
TIMESTAMP,
nullable=False,
server_default=func.now)
status = db.Column("status", INTEGER(), default=1) # 角色状态, 1.正常 0.停用
def __init__(self, role_name):
self.role_name = role_name
@classmethod
def insert(cls, role_name):
one = cls(role_name)
db.session.add(one)
db.session.commit()
class User_role(db.Model):
"""
用户角色关联表
"""
__tablename__ = 'User_Role'
RUN = 1
STOP = 0
ERROR = -1
id = db.Column("id",
INTEGER(),
primary_key=True,
nullable=False,
autoincrement=True) # 分组id
user_id = db.Column("user_id",
INTEGER(),
db.ForeignKey("User.uid"),
nullable=False) # 用户id
role_id = db.Column("role_id",
INTEGER(),
db.ForeignKey("Role.id"),
nullable=False) # 分组id
status = db.Column('status', INTEGER(), nullable=False, default=1) # 分组状态
def __init__(self, user_id, role_id):
self.User_id = user_id
self.role_id = role_id
@classmethod
def insert(cls, user_id, role_id):
one = cls(user_id, role_id)
db.session.add(one)
db.session.commit()
class Role_Module(db.Model):
"""
角色模块关联表
"""
__tablename__ = "Role_Module"
id = db.Column("id",
INTEGER(),
primary_key=True,
nullable=False,
autoincrement=True) # 角色与模块关联id
role_id = db.Column("role_id",
INTEGER(),
db.ForeignKey("Role.id"),
nullable=False) # 角色id, 外键, 关联Role表id
module_id = db.Column("module_id",
INTEGER(),
db.ForeignKey("Module.id"),
nullable=False) # 模块id, 外键, 关联Module表id
status = db.Column("status", INTEGER(), default=1) # 关联状态, 1.正常 0.停用
def __init__(self, role_id, module_id):
self.role_id = role_id
self.module_id = module_id
@classmethod
def insert(cls, role_id, module_id):
one = cls(role_id, module_id)
db.session.add(one)
db.session.commit()
class MealModel(db.Model, BaseMixin):
__tablename__ = 'meal'
date: Date = db.Column(db.Date, primary_key=True)
type: int = db.Column(db.Integer,
primary_key=True) # type (0: 아침, 1: 점심, 2: 저녁)
meal: str = db.Column(db.String(300)) # ||로 구분하여 입력
def __init__(self, date: Date, type: int, meal: str):
self.date = date
self.type = type
self.meal = meal
@staticmethod
def get_meal(date: Date) -> dict:
meal_list: List['MealModel'] = MealModel.query.filter_by(
date=date).order_by('type')
return {
str(date): {
type_list[meal.type]: meal.meal.split('||')
for meal in meal_list
}
}
@staticmethod
def save_meal(date: Date, type: int, meal: str):
meal_data: MealModel = MealModel.query.filter_by(date=date).first()
if meal_data:
meal_data.delete()
MealModel(date, type, meal).save()
@db.validates('type')
def validate_type(self, key, type):
self.assert_validation(type in [0, 1, 2])
return type
class StayApplyModel(db.Model, BaseMixin):
__tablename__ = 'stay_apply'
student_id: str = db.Column(db.String(20),
db.ForeignKey('student.id',
ondelete='CASCADE'),
primary_key=True)
value: int = db.Column(db.Integer)
def __init__(self, student_id: str, value: int):
self.student_id = student_id
self.value = value
@staticmethod
def get_stay_apply_status(student_id: str) -> dict:
stay: 'StayApplyModel' = StayApplyModel.query.filter_by(
student_id=student_id).first()
if stay is None:
stay = StayApplyModel(student_id, 4).save()
return {'value': stay.value}
@staticmethod
def post_stay_apply(student_id: str, value: int):
stay = StayApplyModel.query.filter(
StayApplyModel.student_id == student_id).first()
if stay:
stay.delete()
StayApplyModel(student_id, value).save()
@db.validates('value')
def validate_value(self, key, value):
self.assert_validation(1 <= value <= 4)
return value
class Motto(BasicModel):
__tablename__ = 'motto'
content = db.Column(db.Text, unique=True, nullable=False)
tag = db.Column(db.String(64), unique=True, nullable=False, default='all')
source = db.Column(db.String(64), nullable=True, index=True)
def __repr__(self):
return '<Motto %r>' % self.id
class UserGroup(db.Model):
__tablename__ = 'user_group'
gid = db.Column(db.Integer, primary_key=True, autoincrement=True)
group_name = db.Column(db.String(64), nullable=False)
description = db.Column(db.Text, nullable=False, default='')
users = db.relationship('User',
secondary='user_in_group',
backref=db.backref('groups', lazy='dynamic'),
lazy='dynamic')
class PledgeHeaderRelationship(db.Model):
__tablename__ = 'TBL_PLEDGEHEADERRELATIONSHIP'
# Primary Key
pledgeid = db.Column(db.String(150), primary_key=True, nullable=False)
serialnumber = db.Column(db.String(50), primary_key=True, nullable=False)
# Designation : PRIMARY / SOFT
designation = db.Column(db.String(10), index=True)
""" Relationships """
def __repr__(self):
return "<Pledge {},{} ({})>".format(
self.pledgeid, self.serialnumber, self.designation)
class Announcement(db.Model):
__tablename__ = 'announcement'
aid = db.Column(db.Integer(), primary_key=True, autoincrement=True)
uid = db.Column(db.Integer, db.ForeignKey('user.uid'))
title = db.Column(db.String(64), nullable=False)
description = db.Column(db.Text(), nullable=False)
publish_time = db.Column(db.DateTime(),
nullable=False,
default=datetime.now)
user = db.relationship('User', backref='announcements')
class Tag(BaseModel, BaseMixin):
__tablename__ = 'tag'
id = db.Column(db.Integer, primary_key=True)
tag_group_id = db.Column(db.Integer,
db.ForeignKey('tag_group.id', ondelete='CASCADE'))
name = db.Column(db.String(255), nullable=False)
language = db.Column(db.String(255), nullable=False)
def __init__(self, tag_group, name, language):
self.tag_group = tag_group
self.name = name
self.language = language
class UnsignedStudentModel(db.Model, BaseMixin):
__tablename__ = 'unsigned_student'
uuid: str = db.Column(db.String, primary_key=True)
name: str = db.Column(db.String)
number: int = db.Column(db.Integer)
email: str = db.Column(db.String)
def __init__(self, name: str, number: int, email: str, uuid=None):
self.uuid: str = UnsignedStudentModel.generate_uuid(
email) if uuid is None else uuid
self.name: str = name
self.number: int = number
self.email: str = email
@staticmethod
def get_unsigned_student(uuid: str) -> 'UnsignedStudentModel':
unsigned_student = UnsignedStudentModel.query.filter_by(
uuid=uuid).first()
if unsigned_student is None:
raise NoContentException()
return unsigned_student
@staticmethod
def generate_uuid(key: str) -> str:
while True:
sha = hashlib.sha1()
sha.update(key.encode())
key = sha.hexdigest[:5]
if not UnsignedStudentModel.query.filter_by(uuid=key).first():
return key
@db.validates('number')
def validate_number(self, key, number):
grade = number // 1000
class_ = number // 100 % 10
number_ = number % 100
self.assert_validation(grade in (1, 2, 3))
self.assert_validation(class_ in (1, 2, 3, 4))
self.assert_validation(number_ in range(1, 21))
return number
@db.validates('email')
def validate_email(self, key, email):
self.assert_validation(
re.match(r"^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-][email protected]$", email)
is not None)
return email
class FindPWModel(db.Model, BaseMixin):
__tablename__ = 'find_pw'
id = db.Column(db.String(32), primary_key=True, default=str(uuid4()))
student = db.Column(db.String(20), db.ForeignKey('student.id', ondelete='CASCADE'))
date = db.Column(db.DateTime, default=BaseMixin.kst_now())
def __init__(self, student):
self.student = student
self.save()
@staticmethod
def check_uuid(uuid: str):
find_pw = FindPWModel.query.filter_by(id=uuid).first()
StudentModel.reset_pw(find_pw.student)
class Stage(db.Model):
__tablename__ = 'TBL_STAGE'
""" CONSTRAINT PK_STAGE
PRIMARY KEY (STAGEID)
"""
stageid = db.Column(db.Float, primary_key=True, nullable=False, index=True)
stage = db.Column(db.String(50), index=True)
stagealias = db.Column(db.String(50))
description = db.Column(db.String(50))
history = db.Column(db.SmallInteger, nullable=False, default=0) # (mssql:bit) Yes(-1)/No(0, Null)/Error(other)
""" Relationships """
def __repr__(self):
return "<Stage {} {} history {}>".format(
self.stageid, self.stage, self.history)
class PledgeInstalmentsNextActive(db.Model):
__tablename__ = 'TBL_PLEDGEINSTALMENTS_NEXTACTIVE'
""" CONSTRAINT PK_PLEDGEINSTALMENTS_NEXTACTIVE
PRIMARY KEY (PLEDGEID)
"""
pledgeid = db.Column(db.String(150), primary_key=True, nullable=False, index=True)
instalmentid = db.Column(db.Float, index=True) # ! Important
instalment = db.Column(db.Float, index=True) # mssql: money ! Important
datedue = db.Column(db.DateTime, index=True) # ! Important
amountpaid = db.Column(db.Float, index=True) # mssql: money
""" Relationships """
def __repr__(self):
return "<PledgeN {} Ins {} ${} Due {}>".format(
self.pledgeid, self.instalmentid, self.instalment, self.datedue)
class TestSet(db.Model):
__tablename__ = 'testset'
testset_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
full_score = db.Column(db.DECIMAL(6, 2),
nullable=False,
server_default='0')
tests = db.relationship('Test',
secondary='test_in_testset',
backref=db.backref('testsets', lazy='dynamic'),
lazy='dynamic')
submissions = db.relationship('Submission',
backref='testset',
lazy='dynamic')
class SurrogatePK(object):
"""A mixin that adds a surrogate integer 'primary key' column named
``id`` to any declarative-mapped class.
"""
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
@classmethod
def get_by_id(cls, id):
try:
id = int(id)
except:
raise TypeError('id must int')
if id <= 0:
raise ValueError('ID must not be negative or zero!')
if any((isinstance(id, basestring)
and id.isdigit(), isinstance(id, (int, float))), ):
return cls.query.get(int(id))
return None
@classmethod
def query_one(cls, **kwargs):
if kwargs:
return cls.query.filter_by(**kwargs).first()
return None
class DiskFolder(Base, SurrogatePK):
__tablename__ = 'f_folder'
id = db.Column(db.INTEGER, primary_key=True)
folder_name = db.Column(db.String(500))
folder_path = db.Column(db.String(1000))
group_id = db.Column(db.INTEGER)
user_id = db.Column(db.INTEGER)
is_trash = db.Column(db.INTEGER, default=0)
is_share = db.Column(db.INTEGER, default=0)
is_user_group = db.Column(db.INTEGER)
create_time = db.Column(db.DATETIME, default=datetime.now())
update_time = db.Column(db.DATETIME)
def __init__(self, folder_name, folder_path, group_id, user_id, **kwargs):
db.Model.__init__(self, folder_name=folder_name, folder_path=folder_path, group_id=group_id, user_id=user_id,
**kwargs)
class CompanyTagMapping(BaseModel, BaseMixin):
__tablename__ = 'company_tag_mapping'
id = db.Column(db.Integer, primary_key=True)
company_id = db.Column(db.Integer,
db.ForeignKey('company.id', ondelete='CASCADE'))
tag_group_id = db.Column(db.Integer,
db.ForeignKey('tag_group.id', ondelete='CASCADE'))
def __init__(self, company_id: int, tag_group_id: int):
self.company_id = company_id
self.tag_group_id = tag_group_id
@staticmethod
def get_company_tag_mapping(company_id: int, tag_group_id: int):
return CompanyTagMapping.query.filter_by(
company_id=company_id, tag_group_id=tag_group_id).first()
class Tag(db.Model):
__tablename__ = 'tag'
tag_id = db.Column(db.Integer(), primary_key=True, autoincrement=True)
tag_name = db.Column(db.String(64), nullable=False, unique=True)
problems = db.relationship('Problem',
secondary='tag_of_problem',
backref=db.backref('tags', lazy='dynamic'),
lazy='dynamic')
def __repr__(self):
return self.tag_name
@property
def id(self):
return self.tag_id
def ReferenceCol(tablename, nullable=False, pk_name='id', **kwargs):
"""Column that adds primary key foreign key reference.
Usage: ::
category_id = ReferenceCol('category')
category = relationship('Category', backref='categories')
"""
return db.Column(db.ForeignKey("{0}.{1}".format(tablename, pk_name)),
nullable=nullable,
**kwargs) # pragma: no cover
class Submission(db.Model):
__tablename__ = 'submission'
sid = db.Column(db.Integer(), primary_key=True, autoincrement=True)
pid = db.Column(db.Integer(), db.ForeignKey('problem.pid'))
uid = db.Column(db.Integer(), db.ForeignKey('user.uid'))
testset_id = db.Column(db.Integer, db.ForeignKey('testset.testset_id'))
result = db.Column(db.Text, nullable=True)
score = db.Column(db.DECIMAL(6, 2), nullable=True)
code = db.Column(db.Text, nullable=False)
is_solution = db.Column(db.Boolean(), nullable=False, server_default='0')
submit_time = db.Column(db.DateTime(),
nullable=False,
default=datetime.now)
problem = db.relationship('Problem',
backref=db.backref('submissions',
lazy='dynamic'))
user = db.relationship('User',
backref=db.backref('submissions', lazy='dynamic'))
tasks = db.relationship('Task',
secondary='submission_in_task',
backref=db.backref('submissions', lazy='dynamic'),
lazy='dynamic')
@property
def full_score(self):
return self.testset.full_score if self.testset else None
@property
def status(self):
if not current_user.is_teacher:
for task in self.tasks.all():
if task.available:
return 'hidden'
if self.result is None:
return 'pending'
if self.result == 'running':
return 'running'
if self.result == 'system_error':
return 'system_error'
if self.full_score == self.score:
return 'accepted'
if self.full_score > self.score:
return 'wrong_answer'
return 'other'
class BasicModel(db.Model):
__abstract__ = True
id = db.Column(db.String(64), primary_key=True, unique=True, nullable=True)
create_time = db.Column(db.DateTime, default=datetime.utcnow)
def init_id(self):
self.id = str(uuid.uuid4().hex)
def add(self):
db.session.add(self)
db.session.commit()
def update(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
class FacilityReportModel(db.Model, BaseMixin):
__tablename__ = 'facility_report'
id: int = db.Column(db.Integer, primary_key=True)
student_id: str = db.Column(
db.String, db.ForeignKey('student.id', ondelete='CASCADE'))
room: int = db.Column(db.Integer)
content: str = db.Column(db.String)
def __init__(self, student_id: str, room: int, content: str):
self.student_id: str = student_id
self.room: int = room
self.content: str = content
@staticmethod
def post_facility_report(student_id: str, room: int, content: str):
FacilityReportModel(student_id, room, content).save()
@db.validates('room')
def validate_room(self, key, room):
self.assert_validation(201 <= room <= 518)
return room
class Backup(Base, SurrogatePK):
__tablename__ = 'f_backup'
id = db.Column(db.Integer, primary_key=True)
show_name = db.Column(db.String(500))
file_name = db.Column(db.String(500))
path = db.Column(db.String(600))
file_size = db.Column(db.INTEGER)
user_id = db.Column(db.INTEGER)
is_trash = db.Column(db.INTEGER, default=0)
is_share = db.Column(db.INTEGER)
create_time = db.Column(db.DATETIME, default=datetime.now())
def __init__(self, show_name, file_name, file_size, **kwargs):
db.Model.__init__(self,
show_name=show_name,
file_name=file_name,
file_size=file_size,
**kwargs)
class PointItemModel(db.Model, BaseMixin):
__tablename__ = 'point_item'
id = db.Column(db.Integer, primary_key=True)
reason: str = db.Column(db.String)
point: int = db.Column(db.Integer)
type: bool = db.Column(db.Boolean)
def __init__(self, reason: str, point: int, type: bool):
self.reason: str = reason
self.point: int = point
self.type: bool = type
@staticmethod
def get_point_item(id: int):
point_item: PointItemModel = PointItemModel.query.filter_by(id=id).first()
return {
'point': point_item.point,
'pointType': point_item.type,
'reason': point_item.reason
}
