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