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 FConfig(Base, SurrogatePK): __tablename__ = 'f_config' id = db.Column(db.Integer, primary_key=True) server_name = db.Column(db.String(100)) is_register = db.Column(db.Integer) default_reg_role = db.Column(ChoiceType(ROLES), default=NORMAL) backup_http = db.Column(db.String(20), default='http:') backup_ip = db.Column(db.String(100)) backup_port = db.Column(db.Integer) backup_path = db.Column(db.String(200)) backup_base_path = db.Column(db.String(500)) update_time = db.Column(db.DATETIME) create_time = db.Column(db.DATETIME, default=datetime.now()) def __init__(self, server_name, is_register, **kwargs): db.Model.__init__(self, server_name=server_name, is_register=is_register, **kwargs) def __repr__(self): return f""" config register: {self.is_register}\n default reg role: {self.default_reg_role}\n backup ip: {self.backup_ip}:{self.backup_port} """ def set_backup_base_path(self): if not os.path.exists(self.backup_base_path): try: os.mkdir(self.backup_base_path) except: raise IOError(f'mkdir {self.backup_base_path} failed')
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 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 Sourcecode2(db.Model): __tablename__ = 'TBL_SOURCECODE2' """ CONSTRAINT PK_SOURCECODE2 PRIMARY KEY (SOURCECODE2, OWNERCODE) """ sourcecode2 = db.Column(db.String(50), primary_key=True, nullable=False, index=True) # ! Important serialnumber = db.Column(db.String(50), index=True) category = db.Column(db.String(50), index=True) # ! Important type = db.Column(db.String(50), index=True) # ! Important description = db.Column(db.String(255), index=True) notes = db.Column(db.String(2500)) excludefromdropdown = db.Column(db.SmallInteger, index=True) # ! Important Yes(-1)/No(0, Null)/Error(other) archive = db.Column(db.SmallInteger, index=True) # ! Important Yes(-1)/No(0, Null)/Error(other) created = db.Column(db.DateTime, index=True) # Audit Information createdby = db.Column(db.String(50), index=True) # Audit Information modified = db.Column(db.DateTime, index=True) # Audit Information modifiedby = db.Column(db.String(50), index=True) # Audit Information prefix = db.Column(db.String(50), index=True) lowermanualreceiptno = db.Column(db.Integer, index=True) uppermanualreceiptno = db.Column(db.Integer, index=True) ownercode = db.Column(db.Integer, primary_key=True, nullable=False, default=0, index=True) target = db.Column(db.Float) # (mssql:money) """ Relationships """ def __repr__(self): return "<SS2 {}".format(self.sourcecode2)
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 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)
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 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 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 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 PointHistoryModel(db.Model, BaseMixin): __tablename__ = 'point_history' id: int = db.Column(db.Integer, primary_key=True) student_id: str = db.Column( db.String(20), 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).order_by( PointHistoryModel.point_date.desc()).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 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 PointStatusModel(db.Model, BaseMixin): __tablename__ = 'point_status' student_id: str = db.Column(db.String(20), 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 User(UserMixin, db.Model): __tablename__ = 'user' uid = db.Column(db.Integer, primary_key=True, autoincrement=True) user_name = db.Column(db.String(64), nullable=False, unique=True) password_hash = db.Column(db.String(256), nullable=False) is_teacher = db.Column(db.Boolean(), nullable=False, server_default='0') avatar = db.Column(db.String(256), nullable=False, server_default='default.jpg') item_per_page = db.Column(db.Integer, nullable=False, server_default='20') def __repr__(self): return self.name @property def id(self): return self.uid @property def password(self): raise AttributeError('Password is unreadble') @password.setter def password(self, password): self.password_hash = generate_password_hash(password, method='pbkdf2:sha256') @property def position(self): return 'Teacher' if self.is_teacher else 'Student' @position.setter def position(self, pos): if type(pos) == bool: self.is_teacher = pos else: self.is_teacher = pos == 'Teacher' @property def avatar_path(self): return self.avatar if os.path.exists( os.path.join(current_app.config['UPLOADED_PHOTOS_DEST'], self.avatar)) else 'default.jpg' def verify_password(self, password): return check_password_hash(self.password_hash, password)
class UnsignedStudentModel(db.Model, BaseMixin): __tablename__ = 'unsigned_student' uuid: str = db.Column(db.String(6), primary_key=True) name: str = db.Column(db.String(20)) number: int = db.Column(db.Integer) email: str = db.Column(db.String(50), nullable=True) 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 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 PledgeInstalmentsClosed(db.Model): __tablename__ = 'TBL_PLEDGEINSTALMENTS_CLOSED' """ CONSTRAINT PK_PLEDGEINSTALMENTS_CLOSED PRIMARY KEY (PLEDGEID, INSTALMENTID) """ pledgeid = db.Column(db.String(150), primary_key=True, nullable=False, index=True) # ! Important instalmentid = db.Column(db.Float, primary_key=True, nullable=False, index=True) # ! Important instalment = db.Column(db.Float, index=True) # mssql: money ! Important datedue = db.Column(db.DateTime, index=True) amountpaid = db.Column(db.Float, index=True) # mssql: money ! Important campaignyear = db.Column(db.Integer, index=True) comments = db.Column(db.String(3000)) invoicenth = db.Column(db.Numeric(18), index=True) """ Relationships """ def __repr__(self): return "<PledgeC {} Ins {} ${} Due {}>".format( self.pledgeid, self.instalmentid, self.instalment, self.datedue)
class User(db.Model): __tablename__ = "user" id = db.Column(db.String(32), primary_key=True) # 手机号 phone = db.Column(db.String(32), nullable=False, unique=True, index=True) # 用户名 name = db.Column(db.String(32), nullable=False, unique=True, index=True) # 哈希密码 hash_password = db.Column(db.String(32)) # 创建时间 ctime = db.Column(db.BigInteger, nullable=False) # 修改时间 utime = db.Column(db.BigInteger, nullable=False)
class PledgeHeaderAttribute(db.Model): __tablename__ = 'TBL_PLEDGEHEADERATTRIBUTE' """ CONSTRAINT PK_PLEDGEHEADERATTRIBUTE PRIMARY KEY (PLEDGEID) """ pledgeid = db.Column(db.String(150), primary_key=True, nullable=False, index=True) # Redundant destinationcode = db.Column(db.String(50), index=True) destinationcode2 = db.Column(db.String(50), index=True) paymentratio = db.Column(db.Float, index=True) # mssql: money # !! Key Information notes = db.Column(db.Text) # mssql: varchar(max) auditnotes = db.Column(db.String(8000)) # CC Info creditcardmasked = db.Column(db.String(100)) """ Relationships """ def __repr__(self): return "<Pledge Attributes {} >".format(self.pledgeid)
class PledgeDestination(db.Model): __tablename__ = 'TBL_PLEDGEDESTINATION' # Primary Key pledgeid = db.Column(db.String(150), primary_key=True, nullable=False, index=True) autoid = db.Column(db.Numeric(18), primary_key=True, nullable=False, index=True) # Source Codes sourcecode = db.Column(db.String(50), index=True) sourcecode2 = db.Column(db.String(50), index=True) # Destination Codes destinationcode = db.Column(db.String(50), index=True) destinationcode2 = db.Column(db.String(50), index=True) # Other campaignyear = db.Column(db.Integer, index=True) paymentratio = db.Column(db.Float, index=True) # mssql: money """ Relationships """ def __repr__(self): return "<Pledge S/D {} ({}/{}) ({}/{})>".format( self.pledgeid, self.sourcecode, self.sourcecode2, self.destinationcode, self.destinationcode2)
class PledgeInstalmentsActive(db.Model): __tablename__ = 'TBL_PLEDGEINSTALMENTS_ACTIVE' """ CONSTRAINT PK_PLEDGEINSTALMENTS_ACTIVE PRIMARY KEY (PLEDGEID, INSTALMENTID) """ pledgeid = db.Column(db.String(150), primary_key=True, nullable=False, index=True) # ! Important instalmentid = db.Column(db.Float, primary_key=True, nullable=False, 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 ! Important onhold = db.Column(db.SmallInteger, index=True) # Yes(-1)/No(0, Null)/Error(other) campaignyear = db.Column(db.Integer, index=True) comments = db.Column(db.String(3000)) invoicenth = db.Column(db.Numeric(18), index=True) """ Relationships """ def __repr__(self): return "<PledgeA {} Ins {} ${} Due {}>".format( self.pledgeid, self.instalmentid, self.instalment, self.datedue)
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 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 FacilityReportModel(db.Model, BaseMixin): __tablename__ = 'facility_report' id: int = db.Column(db.Integer, primary_key=True) student_id: str = db.Column( db.String(20), db.ForeignKey('student.id', ondelete='CASCADE')) room: int = db.Column(db.Integer) content: str = db.Column(db.String(100)) 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 PledgeInstalmentsWrittenOff(db.Model): __tablename__ = 'TBL_PLEDGEINSTALMENTS_WRITTENOFF' """ CONSTRAINT PK_PLEDGEINSTALMENTS_WRITTENOFF PRIMARY KEY (PLEDGEID, INSTALMENTID) """ pledgeid = db.Column(db.String(150), primary_key=True, nullable=False, index=True) # ! Important instalmentid = db.Column(db.Float, primary_key=True, nullable=False, 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 originalinstalment = db.Column(db.Float, index=True) # mssql: money campaignyear = db.Column(db.Integer, index=True) comments = db.Column(db.String(3000)) invoicenth = db.Column(db.Numeric(18), index=True) writtendown = db.Column(db.DateTime) # ! Important writtendownby = db.Column(db.String(50)) # ! Important writtendownreason = db.Column(db.String(100)) # ! Important """ Relationships """ def __repr__(self): return "<PledgeR {} Ins {} ${} Due {}>".format( self.pledgeid, self.instalmentid, self.instalment, self.datedue)
class BackupHistory(Base, SurrogatePK): STAT = ( ('success', '备份成功'), ('failed', '备份失败'), ) __tablename__ = 'f_backup_history' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer) backup_stat = db.Column(ChoiceType(STAT), default=STAT[0][0]) backup_id = db.Column(db.Integer) path = db.Column(db.String(500)) show_name = db.Column(db.String(500)) file_name = db.Column(db.String(300)) failed_msg = db.Column(db.String(1000)) create_time = db.Column(db.DATETIME, default=datetime.now()) def __init__(self, user_id, backup_stat, backup_id, **kwargs): db.Model.__init__(self, user_id=user_id, backup_stat=backup_stat, backup_id=backup_id, **kwargs)
class DiskFile(Base, SurrogatePK): __tablename__ = 'f_file' id = db.Column(db.INTEGER, primary_key=True) show_name = db.Column(db.String(500)) file_name = db.Column(db.String(500)) file_size = db.Column(db.INTEGER) group_path = db.Column(db.String(1000)) group_id = db.Column(db.INTEGER) user_id = db.Column(db.INTEGER) is_trash = db.Column(db.INTEGER) is_share = db.Column(db.INTEGER) 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, show_name, group_path, group_id, user_id, **kwargs): db.Model.__init__(self, show_name=show_name, group_path=group_path, group_id=group_id, user_id=user_id, **kwargs) self.set_filename(show_name) def set_filename(self, show_name): f, ex = os.path.splitext(show_name) new_filename = uuid.uuid3(uuid.NAMESPACE_DNS, show_name).hex + ex self.file_name = new_filename
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