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 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 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 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 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 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 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'
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 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 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)
def reference_col(tablename,
nullable=False,
pk_name="id",
foreign_key_kwargs=None,
column_kwargs=None):
"""Column that adds primary key foreign key reference.
Usage: ::
category_id = reference_col('category')
category = relationship('Category', backref='categories')
"""
foreign_key_kwargs = foreign_key_kwargs or {}
column_kwargs = column_kwargs or {}
return Column(
db.ForeignKey(f"{tablename}.{pk_name}", **foreign_key_kwargs),
nullable=nullable,
**column_kwargs,
)
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 Problem(db.Model):
__tablename__ = 'problem'
pid = db.Column(db.Integer(), primary_key = True, autoincrement = True)
testset_id = db.Column(db.Integer, db.ForeignKey('testset.testset_id'))
level = db.Column(db.Integer, nullable = False)
title = db.Column(db.String(64), nullable = False)
description = db.Column(db.Text(), nullable = False)
visible = db.Column(db.Boolean, nullable = False, server_default = "1")
testset = db.relationship('TestSet', backref = 'problem')
def get_user_sub(self):
return self.submissions.filter_by(uid = current_user.uid).order_by(Submission.score.desc()).first()
@property
def ac_rate(self):
subs_count = self.submissions.filter(Submission.result != None)
total = subs_count.count()
ac = 0
for sub in subs_count.all():
ac = ac + (1 if sub.score != None and sub.score == sub.full_score else 0)
return ('%.2f%%' % (ac / total)) if total else '0.00%'
class CompanyName(BaseModel, BaseMixin):
__tablename__ = 'company_name'
id = db.Column(db.Integer, primary_key=True)
company_id = db.Column(db.Integer,
db.ForeignKey('company.id', ondelete='CASCADE'))
name = db.Column(db.String(63))
language = db.Column(db.String(32))
def __init__(self, company_id: int, name: str, language: str):
self.company_id = company_id
self.name = name
self.language = language
@staticmethod
def get_companies_by_similar_name(
name: str) -> Optional[List[CompanyName], None]:
if not name:
return None
search = f'%{name}%'
companies = CompanyName.query.filter(
CompanyName.name.like(search)).all()
return companies
class ExtensionApplyModel(db.Model, BaseMixin):
__tablename__ = 'extension_apply'
student_id = db.Column(db.String(20),
db.ForeignKey('student.id', ondelete='CASCADE'),
primary_key=True)
time = db.Column(db.Integer, primary_key=True)
class_ = db.Column(db.Integer)
seat = db.Column(db.Integer)
def __init__(self, student_id, time, class_, seat):
self.student_id = student_id
self.time = time
self.class_ = class_
self.seat = seat
@staticmethod
def get_extension_apply(student_id: str,
time: int) -> 'ExtensionApplyModel':
return ExtensionApplyModel.query.filter_by(student_id=student_id,
time=time).first()
@staticmethod
def get_extension_apply_by_seat(class_: int, seat: int,
time: int) -> 'ExtensionApplyModel':
return ExtensionApplyModel.query.filter_by(class_=class_,
seat=seat,
time=time).first()
@staticmethod
def get_extension_apply_status(student_id: str,
time: int) -> Union[None, dict]:
extension = ExtensionApplyModel.get_extension_apply(student_id, time)
if extension is None:
return None
return {'classNum': extension.class_, 'seatNum': extension.seat}
@staticmethod
def get_extension_apply_by_class_num(class_: int, time: int) -> dict:
extension_list = ExtensionApplyModel.query.filter_by(class_=class_,
time=time).all()
extension_dict = {
apply.seat: StudentModel.get_student_by_id(apply.student_id).name
for apply in extension_list
}
return extension_dict
@staticmethod
def post_extension_apply(student_id: str, time: int, class_: int,
seat: int):
extension = ExtensionApplyModel.get_extension_apply_by_seat(
class_, seat, time)
if extension is not None:
raise ResetContentException()
extension = ExtensionApplyModel.get_extension_apply(student_id, time)
if extension is not None:
extension.delete()
ExtensionApplyModel(student_id, time, class_, seat).save()
@staticmethod
def delete_extension_apply(student_id: str, time: int):
extension = ExtensionApplyModel.get_extension_apply(student_id, time)
if extension is None:
raise NoContentException()
extension.delete()
@staticmethod
def get_extension_map(class_num: int, time: int) -> dict:
apply_list = ExtensionApplyModel.get_extension_apply_by_class_num(
class_num, time)
seat_count = 1
chart = get_map_chart(class_num)
for i, row in enumerate(chart):
for j, seat in enumerate(row):
if seat:
if seat == -1:
chart[i][j] = seat
continue
apply = apply_list.get(seat_count, False)
if apply:
chart[i][j] = apply
else:
chart[i][j] = seat_count
seat_count += 1
return {'map': chart}
@db.validates('time')
def validate_time(self, key, time):
self.assert_validation(time in (11, 12))
return time
@db.validates('class_')
def validate_class_(self, key, class_):
self.assert_validation(1 <= class_ <= 11)
return class_
@db.validates('seat')
def validate_seat(self, key, seat):
self.assert_validation(1 <= seat <= seat_count[self.class_])
return seat
class MusicApplyModel(db.Model, BaseMixin):
__tablename__ = 'music_apply'
id: int = db.Column(db.Integer, primary_key=True)
# (0 = 월요일, 1 = 화요일, 2 = 수요일, 3 = 목요일, 4 = 금요일)
day: int = db.Column(db.Integer)
student_id: str = db.Column(
db.String, db.ForeignKey('student.id', ondelete='CASCADE'))
singer: str = db.Column(db.String)
song_name: str = db.Column(db.String)
apply_date: datetime = db.Column(db.DateTime)
def __init__(self, day: int, student_id: str, singer: str, song_name: str):
self.day = day
self.student_id = student_id
self.singer = singer
self.song_name = song_name
self.apply_date = self.kst_now()
@staticmethod
def get_music_apply_by_id(student_id: str):
return MusicApplyModel.query.filter_by(student_id=student_id).first()
@staticmethod
def get_music_apply() -> List['MusicApplyModel']:
return MusicApplyModel.query.order_by(
MusicApplyModel.day, MusicApplyModel.apply_date).all()
@staticmethod
def get_music_apply_status() -> dict:
status = {week: [] for week in weekday}
music_apply = MusicApplyModel.get_music_apply()
if not music_apply:
raise NoContentException()
if music_apply is not None:
for apply in music_apply:
student = StudentModel.get_student_by_id(apply.student_id)
status[weekday[apply.day]].append({
'id':
apply.id,
'musicName':
apply.song_name,
'singer':
apply.singer,
'studentId':
apply.student_id,
'applyDate':
str(apply.apply_date),
'studentName':
student.name
})
return status
@staticmethod
def post_music_apply(day: int, student_id: str, singer: str,
song_name: str):
if len(MusicApplyModel.query.filter_by(day=day).all()) >= 3:
raise ResetContentException()
already_exist_apply = MusicApplyModel.get_music_apply_by_id(student_id)
if already_exist_apply is not None:
raise AlreadyApplyMusic()
MusicApplyModel(day, student_id, singer, song_name).save()
@staticmethod
def delete_music_apply(student_id: str, apply_id: int):
apply: 'MusicApplyModel' = MusicApplyModel.query.filter_by(
id=apply_id).first()
if apply is None:
raise NoContentException()
if apply.student_id != student_id:
raise ForbiddenException()
apply.delete()
@db.validates('day')
def validate_day(self, key, day):
self.assert_validation(day in week)
return day
from .user import User
from .problem import Problem
from .submission import Submission
from .usergroup import UserGroup
from .task import Task
from .tag import Tag
from .test import Test
from .testset import TestSet
from .announcement import Announcement
from app.extension import db
from sqlalchemy import DateTime, Numeric
UserInGroup = db.Table(
'user_in_group',
db.Column('uid', db.Integer, db.ForeignKey('user.uid'), primary_key = True),
db.Column('gid', db.Integer, db.ForeignKey('user_group.gid'), primary_key = True)
)
TagOfProblem = db.Table(
'tag_of_problem',
db.Column('tag_id', db.Integer, db.ForeignKey('tag.tag_id'), primary_key = True),
db.Column('pid', db.Integer, db.ForeignKey('problem.pid'), primary_key = True)
)
TestInTestSet = db.Table(
'test_in_testset',
db.Column('testset_id', db.Integer, db.ForeignKey('testset.testset_id'), primary_key = True),
db.Column('test_id', db.Integer, db.ForeignKey('test.test_id'), primary_key = True)
)
ProblemInTask = db.Table(
class GoingOutApplyModel(db.Model, BaseMixin):
__tablename__ = 'goingout'
id: int = db.Column(db.Integer, primary_key=True)
student_id: str = db.Column(
db.String, db.ForeignKey('student.id', ondelete='CASCADE'))
go_out_date: datetime = db.Column(db.DateTime)
return_date: datetime = db.Column(db.DateTime)
reason: str = db.Column(db.String)
# 0: 외출 전. 1: 외출 중, 2: 복귀 완료
goingout_status: int = db.Column(db.Integer, default=0)
def __init__(self, student_id: str, go_out_date: datetime,
return_date: datetime, reason: str):
self.student_id = student_id
self.go_out_date = go_out_date
self.return_date = return_date
self.reason = reason
@staticmethod
def check_going_out_apply_time(now: datetime, go_out_date: datetime,
return_date: datetime):
if (now.weekday() in {4, 5}) and (now.hour >= 22 and now.minute >= 30):
if now.date() + timedelta(days=1) == go_out_date.date():
raise ApplyTimeException
if go_out_date.date() != return_date.date():
raise ApplyTimeException
difference_time = go_out_date - now
if not difference_time <= timedelta(days=7):
raise ApplyTimeException
@staticmethod
def get_going_out_apply(student_id: str) -> dict:
applies: List['GoingOutApplyModel'] = GoingOutApplyModel.query.filter(
GoingOutApplyModel.student_id == student_id,
GoingOutApplyModel.return_date > datetime.now()).all()
if not applies:
raise NoContentException
going_out_data = {'workday': [], 'saturday': [], 'sunday': []}
for apply in applies:
days_of_week = apply.go_out_date.weekday()
date = datetime_to_str(apply.go_out_date, apply.return_date)
apply = {
'date': date,
'id': apply.id,
'reason': apply.reason,
'goingoutStatus':
going_out_status_message[apply.goingout_status]
}
if days_of_week <= 4:
going_out_data['workday'].append(apply)
elif days_of_week == 5:
going_out_data['saturday'].append(apply)
else:
going_out_data['sunday'].append(apply)
return going_out_data
@staticmethod
def post_going_out_apply(student_id: str, date: str, reason: str):
now = GoingOutApplyModel.kst_now()
date_dict = str_to_datetime(date)
go_out_date: datetime = date_dict.get('go_out_date')
return_date: datetime = date_dict.get('return_date')
GoingOutApplyModel.check_going_out_apply_time(now, go_out_date,
return_date)
GoingOutApplyModel(student_id, go_out_date, return_date, reason).save()
@staticmethod
def patch_going_out_apply(apply_id: int, student_id: str, date: str,
reason: str):
now = GoingOutApplyModel.kst_now()
apply: 'GoingOutApplyModel' = GoingOutApplyModel.query.filter_by(
id=apply_id, student_id=student_id).first()
date_dict = str_to_datetime(date)
go_out_date = date_dict.get('go_out_date')
return_date = date_dict.get('return_date')
GoingOutApplyModel.check_going_out_apply_time(now, go_out_date,
return_date)
apply.go_out_date = go_out_date
apply.return_date = return_date
apply.reason = reason
db.session.commit()
@staticmethod
def delete_going_out_apply(apply_id: int, student_id: str):
apply: 'GoingOutApplyModel' = GoingOutApplyModel.query.filter_by(
id=apply_id, student_id=student_id).first()
if apply is None:
raise NoContentException()
apply.delete()
from app.extension import db
from app.models.ManageAccess import ManageAccess
from app.common import AdminException
import time
manager_role_access = db.Table(
'manager_role_access',
db.Column('manage_access_id', db.Integer, db.ForeignKey('manage_access.id'), primary_key=True),
db.Column('manage_role_id', db.Integer, db.ForeignKey('manage_role.id'), primary_key=True)
)
class ManageRole(db.Model):
__tablename__ = 'manage_role'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
role_name = db.Column(db.String(40), index=True, nullable=False)
create_time = db.Column(db.Integer, index=True, default=0)
update_time = db.Column(db.Integer, index=True, default=0)
access = db.relationship('ManageAccess', secondary=manager_role_access, backref=db.backref('roles', lazy='dynamic'),
lazy='dynamic')
def get_all_access(self, role_id=0):
role_access_ids = []
if role_id:
role = self.query.get(role_id)
access = role.access.all()
print(access)
for access_item in access:
role_access_ids.append(access_item.id)
class TokenModel(db.Model, BaseMixin):
__tablename__ = 'token'
owner: str = db.Column(db.String(20),
db.ForeignKey('student.id', ondelete='CASCADE'))
refresh_token: str = db.Column(db.String(500), primary_key=True)
user_agent: str = db.Column(db.String(200), primary_key=True)
def __init__(self, owner: str, refresh_token: str, user_agent: str):
self.owner: str = owner
self.refresh_token: str = refresh_token
self.user_agent: str = user_agent
@classmethod
def find_refresh_token(cls, owner: str, user_agent: str):
return TokenModel.query.filter_by(owner=owner,
user_agent=user_agent).first()
@classmethod
def _verify_refresh_token(cls, refresh_token, user_agent):
token = TokenModel.query.filter_by(owner=get_jwt_identity(),
refresh_token=refresh_token,
user_agent=user_agent).first()
if not token:
raise ForbiddenException()
return token
@classmethod
def create_new_token(cls, owner: str, user_agent: str) -> dict:
new_access_token = create_access_token(owner)
new_refresh_token = create_refresh_token(owner)
exist_token = cls.find_refresh_token(owner, user_agent)
if exist_token:
exist_token.delete()
TokenModel(owner, new_refresh_token, user_agent).save()
return {
'accessToken': new_access_token,
'refreshToken': new_refresh_token
}
@classmethod
def create_refresh_token(cls, refresh_token, user_agent):
new_token = {}
token = cls._verify_refresh_token(refresh_token, user_agent)
token_data = decode_token(refresh_token)
new_access_token = create_access_token(token_data.get('sub'))
new_token['accessToken'] = new_access_token
# refresh token 의 유효 기간이 3일 미만일 경우 refresh_token 또한 재발급.
if (token_data.get('exp') - time()) < 259200:
new_refresh_token = create_refresh_token(token_data.get('sub'))
token.refresh_token = new_refresh_token
new_token['refreshToken'] = new_refresh_token
db.session.commit()
return new_token
from app.extension import db
from werkzeug.security import generate_password_hash, check_password_hash
from app.models.ManageRole import ManageRole
from app.common import AdminException
import time
manager_user_role = db.Table(
'manager_user_role',
db.Column('manage_user_id',
db.Integer,
db.ForeignKey('manage_user.id'),
primary_key=True),
db.Column('manage_role_id',
db.Integer,
db.ForeignKey('manage_role.id'),
primary_key=True))
class ManageUser(db.Model):
__tablename__ = 'manage_user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(30), index=True, nullable=False)
password = db.Column(db.String(200), nullable=False)
status = db.Column(db.SmallInteger, nullable=False, default=1)
is_super = db.Column(db.SmallInteger, nullable=True, default=0)
create_time = db.Column(db.Integer, index=True, default=0)
update_time = db.Column(db.Integer, index=True, default=0)
roles = db.relationship('ManageRole',
secondary=manager_user_role,
backref=db.backref('manages', lazy='dynamic'),
