625 lines
28 KiB
Python
625 lines
28 KiB
Python
"""学生课程管理系统 - SQLAlchemy 数据模型"""
|
||
from datetime import datetime, date
|
||
from flask_sqlalchemy import SQLAlchemy
|
||
from werkzeug.security import generate_password_hash, check_password_hash
|
||
|
||
db = SQLAlchemy()
|
||
|
||
|
||
class Model(db.Model):
|
||
"""ORM 基类：表名/列名加反引号，兼容 Doris 保留字（roles、classes、date 等）"""
|
||
__abstract__ = True
|
||
__table_args__ = {'quote': True}
|
||
|
||
|
||
class Role(Model):
|
||
"""角色表"""
|
||
__tablename__ = 'roles'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
name = db.Column(db.String(50), unique=True, nullable=False)
|
||
description = db.Column(db.String(200))
|
||
permissions = db.Column(db.Text)
|
||
remark = db.Column(db.Text)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'name': self.name, 'description': self.description,
|
||
'permissions': self.permissions, 'remark': self.remark,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class User(Model):
|
||
"""用户表"""
|
||
__tablename__ = 'users'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
username = db.Column(db.String(50), unique=True, nullable=False)
|
||
password = db.Column(db.String(200), nullable=False)
|
||
real_name = db.Column(db.String(50), nullable=False)
|
||
role_id = db.Column(db.Integer, db.ForeignKey('roles.id'), nullable=False)
|
||
phone = db.Column(db.String(20))
|
||
email = db.Column(db.String(100))
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
remark = db.Column(db.String(200))
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
|
||
|
||
role = db.relationship('Role', backref='users')
|
||
|
||
def set_password(self, pwd):
|
||
self.password = generate_password_hash(pwd)
|
||
|
||
def check_password(self, pwd):
|
||
return check_password_hash(self.password, pwd)
|
||
|
||
@property
|
||
def is_authenticated(self):
|
||
return True
|
||
|
||
def has_permission(self, perm):
|
||
"""检查用户是否拥有指定权限，超级管理员角色拥有所有权限"""
|
||
if not self.role:
|
||
return False
|
||
if self.role.name == '超级管理员':
|
||
return True
|
||
perms_str = self.role.permissions or ''
|
||
if perms_str == 'all':
|
||
return True
|
||
return perm in perms_str.split(',')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'username': self.username, 'real_name': self.real_name,
|
||
'role_id': self.role_id, 'phone': self.phone, 'email': self.email,
|
||
'status': self.status, 'created_at': str(self.created_at)}
|
||
|
||
|
||
class Teacher(Model):
|
||
"""老师表"""
|
||
__tablename__ = 'teachers'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
|
||
name = db.Column(db.String(50), nullable=False)
|
||
phone = db.Column(db.String(20))
|
||
specialty = db.Column(db.String(200))
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
remark = db.Column(db.Text)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
user = db.relationship('User', backref='teacher_profile')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'user_id': self.user_id, 'name': self.name,
|
||
'phone': self.phone, 'specialty': self.specialty,
|
||
'status': self.status, 'remark': self.remark,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class Student(Model):
|
||
"""学员表"""
|
||
__tablename__ = 'students'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
name = db.Column(db.String(50), nullable=False)
|
||
display_name = db.Column(db.String(50))
|
||
nickname = db.Column(db.String(50))
|
||
gender = db.Column(db.String(10))
|
||
phone = db.Column(db.String(20))
|
||
parent_phone = db.Column(db.String(20))
|
||
birthday = db.Column(db.Date)
|
||
address = db.Column(db.String(200))
|
||
remark = db.Column(db.Text)
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
source = db.Column(db.String(50), default='新学员')
|
||
referrer_id = db.Column(db.Integer)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'name': self.name,
|
||
'display_name': self.display_name, 'nickname': self.nickname,
|
||
'gender': self.gender,
|
||
'phone': self.phone, 'parent_phone': self.parent_phone,
|
||
'birthday': str(self.birthday) if self.birthday else None,
|
||
'address': self.address, 'remark': self.remark,
|
||
'status': self.status, 'source': self.source,
|
||
'referrer_id': self.referrer_id,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class Course(Model):
|
||
"""课程表"""
|
||
__tablename__ = 'courses'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
name = db.Column(db.String(100), nullable=False)
|
||
course_code = db.Column(db.String(20))
|
||
type = db.Column(db.String(50), default='一对一')
|
||
level = db.Column(db.String(50))
|
||
description = db.Column(db.Text)
|
||
price_per_hour = db.Column(db.Numeric(10, 2), default=0)
|
||
total_hours = db.Column(db.Integer, default=0)
|
||
material_fee = db.Column(db.Numeric(10, 2), default=0)
|
||
remark = db.Column(db.Text)
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'name': self.name, 'type': self.type,
|
||
'level': self.level, 'description': self.description,
|
||
'price_per_hour': float(self.price_per_hour) if self.price_per_hour else 0,
|
||
'total_hours': self.total_hours,
|
||
'material_fee': float(self.material_fee or 0),
|
||
'remark': self.remark,
|
||
'status': self.status, 'created_at': str(self.created_at)}
|
||
|
||
|
||
class Class_(Model):
|
||
"""班级表"""
|
||
__tablename__ = 'classes'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
name = db.Column(db.String(100), nullable=False)
|
||
course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), nullable=False)
|
||
teacher_id = db.Column(db.Integer, db.ForeignKey('teachers.id'), nullable=False)
|
||
start_date = db.Column(db.Date)
|
||
end_date = db.Column(db.Date)
|
||
schedule = db.Column(db.String(200))
|
||
max_students = db.Column(db.Integer, default=20)
|
||
current_students = db.Column(db.Integer, default=0)
|
||
remark = db.Column(db.Text)
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
course = db.relationship('Course', backref='classes')
|
||
teacher = db.relationship('Teacher', backref='classes')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'name': self.name, 'course_id': self.course_id,
|
||
'teacher_id': self.teacher_id,
|
||
'start_date': str(self.start_date) if self.start_date else None,
|
||
'end_date': str(self.end_date) if self.end_date else None,
|
||
'schedule': self.schedule, 'max_students': self.max_students,
|
||
'current_students': self.current_students,
|
||
'remark': self.remark,
|
||
'status': self.status, 'created_at': str(self.created_at)}
|
||
|
||
|
||
class ClassStudent(Model):
|
||
"""班级学员关联表"""
|
||
__tablename__ = 'class_students'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
class_id = db.Column(db.Integer, db.ForeignKey('classes.id'), nullable=False)
|
||
student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
|
||
join_date = db.Column(db.Date, nullable=False)
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
class_ = db.relationship('Class_', backref='student_relations')
|
||
student = db.relationship('Student', backref='class_relations')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'class_id': self.class_id, 'student_id': self.student_id,
|
||
'join_date': str(self.join_date), 'status': self.status}
|
||
|
||
|
||
class StudentAccount(Model):
|
||
"""学员课时账户表"""
|
||
__tablename__ = 'student_accounts'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
|
||
course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), nullable=False)
|
||
from_class_id = db.Column(db.Integer, db.ForeignKey('classes.id'))
|
||
from_teacher_id = db.Column(db.Integer, db.ForeignKey('teachers.id'))
|
||
normal_hours = db.Column(db.Numeric(10, 2), default=0)
|
||
gifted_hours = db.Column(db.Numeric(10, 2), default=0)
|
||
consumed_normal = db.Column(db.Numeric(10, 2), default=0)
|
||
consumed_gifted = db.Column(db.Numeric(10, 2), default=0)
|
||
normal_validity = db.Column(db.String(20), default='permanent')
|
||
normal_expiry_date = db.Column(db.Date)
|
||
gifted_validity = db.Column(db.String(20), default='permanent')
|
||
gifted_expiry_date = db.Column(db.Date)
|
||
is_stopped = db.Column(db.SmallInteger, default=0)
|
||
stop_end_date = db.Column(db.Date)
|
||
cumulative_amount = db.Column(db.Numeric(10, 2), default=0)
|
||
cumulative_start_date = db.Column(db.Date)
|
||
unit_price = db.Column(db.Numeric(10, 4), default=0)
|
||
original_price_per_lesson = db.Column(db.Numeric(10, 4))
|
||
account_status = db.Column(db.String(20), default='active')
|
||
version = db.Column(db.Integer, default=0)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
|
||
|
||
student = db.relationship('Student', backref='accounts')
|
||
course = db.relationship('Course', backref='accounts')
|
||
from_class = db.relationship('Class_', foreign_keys=[from_class_id])
|
||
from_teacher = db.relationship('Teacher', foreign_keys=[from_teacher_id])
|
||
monthly_snapshots = db.relationship('MonthlySnapshot', backref='account', lazy='dynamic')
|
||
class_records = db.relationship('ClassRecord', backref='account', lazy='dynamic')
|
||
|
||
__table_args__ = (
|
||
db.UniqueConstraint('student_id', 'course_id'),
|
||
{'quote': True},
|
||
)
|
||
|
||
@property
|
||
def total_hours(self):
|
||
return float(self.normal_hours or 0) + float(self.gifted_hours or 0)
|
||
|
||
@property
|
||
def total_consumed(self):
|
||
return float(self.consumed_normal or 0) + float(self.consumed_gifted or 0)
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'student_id': self.student_id, 'course_id': self.course_id,
|
||
'normal_hours': float(self.normal_hours or 0),
|
||
'gifted_hours': float(self.gifted_hours or 0),
|
||
'consumed_normal': float(self.consumed_normal or 0),
|
||
'consumed_gifted': float(self.consumed_gifted or 0),
|
||
'total_hours': self.total_hours,
|
||
'is_stopped': self.is_stopped,
|
||
'cumulative_amount': float(self.cumulative_amount or 0)}
|
||
|
||
|
||
class RechargeActivity(Model):
|
||
"""充值优惠活动表"""
|
||
__tablename__ = 'recharge_activities'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
name = db.Column(db.String(100), nullable=False)
|
||
trigger_amount = db.Column(db.Numeric(10, 2), nullable=False)
|
||
gifted_hours = db.Column(db.Numeric(10, 2), default=0)
|
||
gift_type = db.Column(db.String(50), default='课时')
|
||
is_cumulative = db.Column(db.SmallInteger, default=0)
|
||
cumulative_window = db.Column(db.Integer, default=0)
|
||
cumulative_clear = db.Column(db.SmallInteger, default=0)
|
||
allow_multi_trigger = db.Column(db.SmallInteger, default=0)
|
||
is_time_limited = db.Column(db.SmallInteger, default=0)
|
||
start_date = db.Column(db.Date)
|
||
end_date = db.Column(db.Date)
|
||
target_audience = db.Column(db.String(50), default='all')
|
||
can_stack = db.Column(db.SmallInteger, default=1)
|
||
gift_cap = db.Column(db.Numeric(10, 2), default=0)
|
||
group_discount = db.Column(db.SmallInteger, default=0)
|
||
group_min_people = db.Column(db.Integer, default=0)
|
||
group_extra_hours = db.Column(db.Numeric(10, 2), default=0)
|
||
referral_reward = db.Column(db.SmallInteger, default=0)
|
||
referral_reward_hours = db.Column(db.Numeric(10, 2), default=0)
|
||
reward_timing = db.Column(db.String(50), default='immediate')
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'name': self.name,
|
||
'trigger_amount': float(self.trigger_amount or 0),
|
||
'gifted_hours': float(self.gifted_hours or 0),
|
||
'gift_type': self.gift_type,
|
||
'is_cumulative': self.is_cumulative,
|
||
'cumulative_window': self.cumulative_window,
|
||
'allow_multi_trigger': self.allow_multi_trigger,
|
||
'is_time_limited': self.is_time_limited,
|
||
'start_date': str(self.start_date) if self.start_date else None,
|
||
'end_date': str(self.end_date) if self.end_date else None,
|
||
'target_audience': self.target_audience,
|
||
'can_stack': self.can_stack,
|
||
'gift_cap': float(self.gift_cap or 0),
|
||
'group_discount': self.group_discount,
|
||
'referral_reward': self.referral_reward,
|
||
'reward_timing': self.reward_timing,
|
||
'status': self.status}
|
||
|
||
|
||
class RechargeRecord(Model):
|
||
"""充值记录表"""
|
||
__tablename__ = 'recharge_records'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
|
||
course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), nullable=False)
|
||
activity_id = db.Column(db.Integer, db.ForeignKey('recharge_activities.id'))
|
||
amount = db.Column(db.Numeric(10, 2), nullable=False)
|
||
normal_hours = db.Column(db.Numeric(10, 2), nullable=False)
|
||
gifted_hours = db.Column(db.Numeric(10, 2), default=0)
|
||
payment_method = db.Column(db.String(50))
|
||
operator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
|
||
remark = db.Column(db.Text)
|
||
gift_clawed_back = db.Column(db.SmallInteger, default=0)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
student = db.relationship('Student', backref='recharge_records')
|
||
course = db.relationship('Course', backref='recharge_records')
|
||
activity = db.relationship('RechargeActivity', backref='records')
|
||
operator = db.relationship('User', backref='recharge_operations')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'student_id': self.student_id, 'course_id': self.course_id,
|
||
'activity_id': self.activity_id,
|
||
'amount': float(self.amount or 0),
|
||
'normal_hours': float(self.normal_hours or 0),
|
||
'gifted_hours': float(self.gifted_hours or 0),
|
||
'payment_method': self.payment_method,
|
||
'operator_id': self.operator_id,
|
||
'remark': self.remark,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class ConsumptionRecord(Model):
|
||
"""消课记录表"""
|
||
__tablename__ = 'consumption_records'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
|
||
class_id = db.Column(db.Integer, db.ForeignKey('classes.id'))
|
||
course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), nullable=False)
|
||
hours_consumed = db.Column(db.Numeric(10, 2), nullable=False)
|
||
consume_type = db.Column(db.String(20), nullable=False)
|
||
normal_consumed = db.Column(db.Numeric(10, 2), default=0)
|
||
gifted_consumed = db.Column(db.Numeric(10, 2), default=0)
|
||
operator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
|
||
consume_date = db.Column(db.Date, nullable=False)
|
||
is_makeup = db.Column(db.SmallInteger, default=0)
|
||
is_trial = db.Column(db.SmallInteger, default=0)
|
||
unit_price_at_consume = db.Column(db.Numeric(10, 4))
|
||
remark = db.Column(db.Text)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
student = db.relationship('Student', backref='consumption_records')
|
||
class_ = db.relationship('Class_', backref='consumption_records')
|
||
course = db.relationship('Course', backref='consumption_records')
|
||
operator = db.relationship('User', backref='consumption_operations')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'student_id': self.student_id, 'class_id': self.class_id,
|
||
'course_id': self.course_id,
|
||
'hours_consumed': float(self.hours_consumed or 0),
|
||
'consume_type': self.consume_type,
|
||
'normal_consumed': float(self.normal_consumed or 0),
|
||
'gifted_consumed': float(self.gifted_consumed or 0),
|
||
'consume_date': str(self.consume_date),
|
||
'is_makeup': self.is_makeup,
|
||
'is_trial': self.is_trial,
|
||
'remark': self.remark,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class RefundRecord(Model):
|
||
"""退费记录表"""
|
||
__tablename__ = 'refund_records'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
|
||
course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), nullable=False)
|
||
refund_amount = db.Column(db.Numeric(10, 2), nullable=False)
|
||
remaining_normal_hours = db.Column(db.Numeric(10, 2))
|
||
remaining_gifted_hours = db.Column(db.Numeric(10, 2))
|
||
deduct_gifted_hours = db.Column(db.Numeric(10, 2), default=0)
|
||
deduct_gifted_amount = db.Column(db.Numeric(12, 2), default=0)
|
||
promo_clawback_hours = db.Column(db.Numeric(10, 2), default=0)
|
||
consumed_hours_value = db.Column(db.Numeric(10, 2), default=0)
|
||
material_fee_per_quarter = db.Column(db.Numeric(10, 2), default=250)
|
||
quarters = db.Column(db.Integer, default=0)
|
||
total_deduct = db.Column(db.Numeric(10, 2), default=0)
|
||
reason = db.Column(db.Text)
|
||
operator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
student = db.relationship('Student', backref='refund_records')
|
||
course = db.relationship('Course', backref='refund_records')
|
||
operator = db.relationship('User', backref='refund_operations')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'student_id': self.student_id, 'course_id': self.course_id,
|
||
'refund_amount': float(self.refund_amount or 0),
|
||
'remaining_normal_hours': float(self.remaining_normal_hours or 0),
|
||
'remaining_gifted_hours': float(self.remaining_gifted_hours or 0),
|
||
'deduct_gifted_hours': float(self.deduct_gifted_hours or 0),
|
||
'consumed_hours_value': float(self.consumed_hours_value or 0),
|
||
'material_fee_per_quarter': float(self.material_fee_per_quarter or 0),
|
||
'quarters': self.quarters,
|
||
'total_deduct': float(self.total_deduct or 0),
|
||
'reason': self.reason,
|
||
'status': self.status,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class TransferRecord(Model):
|
||
"""转课记录表"""
|
||
__tablename__ = 'transfer_records'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
from_student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
|
||
to_student_id = db.Column(db.Integer, db.ForeignKey('students.id'))
|
||
from_course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), nullable=False)
|
||
to_course_id = db.Column(db.Integer, db.ForeignKey('courses.id'))
|
||
from_class_id = db.Column(db.Integer, db.ForeignKey('classes.id'))
|
||
to_class_id = db.Column(db.Integer, db.ForeignKey('classes.id'))
|
||
from_teacher_id = db.Column(db.Integer, db.ForeignKey('teachers.id'))
|
||
to_teacher_id = db.Column(db.Integer, db.ForeignKey('teachers.id'))
|
||
transfer_type = db.Column(db.String(20), nullable=False)
|
||
# 转课类型：class_transfer(班级转课) / gift(转赠)
|
||
transfer_class_type = db.Column(db.String(20), default='class_transfer')
|
||
# 是否变更单价：no_change(无单价变更) / price_changed(有单价变更)
|
||
price_change_type = db.Column(db.String(20), default='no_change')
|
||
transfer_hours = db.Column(db.Numeric(10, 2), nullable=False)
|
||
original_unit_price = db.Column(db.Numeric(10, 4))
|
||
new_unit_price = db.Column(db.Numeric(10, 4))
|
||
transfer_amount = db.Column(db.Numeric(10, 2))
|
||
price_change_reason = db.Column(db.Text)
|
||
effective_date = db.Column(db.Date)
|
||
operator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
|
||
remark = db.Column(db.Text)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
from_student = db.relationship('Student', foreign_keys=[from_student_id], backref='transfers_out')
|
||
to_student = db.relationship('Student', foreign_keys=[to_student_id], backref='transfers_in')
|
||
from_course = db.relationship('Course', foreign_keys=[from_course_id])
|
||
to_course = db.relationship('Course', foreign_keys=[to_course_id])
|
||
from_class = db.relationship('Class_', foreign_keys=[from_class_id])
|
||
to_class = db.relationship('Class_', foreign_keys=[to_class_id])
|
||
from_teacher = db.relationship('Teacher', foreign_keys=[from_teacher_id])
|
||
to_teacher = db.relationship('Teacher', foreign_keys=[to_teacher_id])
|
||
operator = db.relationship('User', backref='transfer_operations')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'from_student_id': self.from_student_id,
|
||
'to_student_id': self.to_student_id,
|
||
'from_course_id': self.from_course_id, 'to_course_id': self.to_course_id,
|
||
'from_class_id': self.from_class_id, 'to_class_id': self.to_class_id,
|
||
'from_teacher_id': self.from_teacher_id, 'to_teacher_id': self.to_teacher_id,
|
||
'transfer_type': self.transfer_type,
|
||
'transfer_class_type': self.transfer_class_type,
|
||
'price_change_type': self.price_change_type,
|
||
'transfer_hours': float(self.transfer_hours or 0),
|
||
'original_unit_price': float(self.original_unit_price or 0),
|
||
'new_unit_price': float(self.new_unit_price or 0),
|
||
'transfer_amount': float(self.transfer_amount or 0),
|
||
'price_change_reason': self.price_change_reason,
|
||
'effective_date': str(self.effective_date) if self.effective_date else None,
|
||
'remark': self.remark,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class StopRecord(Model):
|
||
"""停课记录表"""
|
||
__tablename__ = 'stop_records'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
|
||
course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), nullable=False)
|
||
start_date = db.Column(db.Date, nullable=False)
|
||
end_date = db.Column(db.Date, nullable=False)
|
||
reason = db.Column(db.Text)
|
||
operator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
|
||
status = db.Column(db.SmallInteger, default=1)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
student = db.relationship('Student', backref='stop_records')
|
||
course = db.relationship('Course', backref='stop_records')
|
||
operator = db.relationship('User', backref='stop_operations')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'student_id': self.student_id, 'course_id': self.course_id,
|
||
'start_date': str(self.start_date), 'end_date': str(self.end_date),
|
||
'reason': self.reason, 'status': self.status,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class AttendanceRecord(Model):
|
||
"""考勤记录表"""
|
||
__tablename__ = 'attendance_records'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
student_id = db.Column(db.Integer, db.ForeignKey('students.id'), nullable=False)
|
||
class_id = db.Column(db.Integer, db.ForeignKey('classes.id'), nullable=False)
|
||
date = db.Column(db.Date, nullable=False)
|
||
status = db.Column(db.String(20), nullable=False)
|
||
remark = db.Column(db.Text)
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
student = db.relationship('Student', backref='attendance_records')
|
||
class_ = db.relationship('Class_', backref='attendance_records')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'student_id': self.student_id, 'class_id': self.class_id,
|
||
'date': str(self.date), 'status': self.status, 'remark': self.remark}
|
||
|
||
|
||
class OperationLog(Model):
|
||
"""操作日志表"""
|
||
__tablename__ = 'operation_logs'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
|
||
operation_type = db.Column(db.String(50), nullable=False)
|
||
operation_detail = db.Column(db.Text)
|
||
ip_address = db.Column(db.String(50))
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
user = db.relationship('User', backref='operation_logs')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'user_id': self.user_id,
|
||
'operation_type': self.operation_type,
|
||
'operation_detail': self.operation_detail,
|
||
'ip_address': self.ip_address,
|
||
'created_at': str(self.created_at)}
|
||
|
||
|
||
class Schedule(Model):
|
||
"""课程安排表"""
|
||
__tablename__ = 'schedules'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
class_id = db.Column(db.Integer, db.ForeignKey('classes.id'), nullable=False)
|
||
date = db.Column(db.Date, nullable=False)
|
||
start_time = db.Column(db.String(10), nullable=False)
|
||
end_time = db.Column(db.String(10), nullable=False)
|
||
teacher_id = db.Column(db.Integer, db.ForeignKey('teachers.id'), nullable=False)
|
||
topic = db.Column(db.String(200))
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
class_ = db.relationship('Class_', backref='schedules')
|
||
teacher = db.relationship('Teacher', backref='schedules')
|
||
|
||
def to_dict(self):
|
||
return {'id': self.id, 'class_id': self.class_id,
|
||
'date': str(self.date), 'start_time': self.start_time,
|
||
'end_time': self.end_time, 'teacher_id': self.teacher_id,
|
||
'topic': self.topic}
|
||
|
||
|
||
# ========== 课时核对表（Excel 流水账）==========
|
||
|
||
COURSE_CODES = ('scratch', 'python', 'c++', 'wedo')
|
||
|
||
|
||
class MonthlySnapshot(Model):
|
||
"""月度课时快照：每个学员课程账户每月一条"""
|
||
__tablename__ = 'monthly_snapshots'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
account_id = db.Column(db.Integer, db.ForeignKey('student_accounts.id'), nullable=False)
|
||
year = db.Column(db.SmallInteger, nullable=False)
|
||
month = db.Column(db.SmallInteger, nullable=False)
|
||
seq_no = db.Column(db.Integer)
|
||
prev_lessons = db.Column(db.Numeric(10, 2))
|
||
prev_gift_lessons = db.Column(db.Numeric(10, 2))
|
||
prev_total_lessons = db.Column(db.Numeric(10, 2))
|
||
prev_balance = db.Column(db.Numeric(12, 2))
|
||
new_signup_amount = db.Column(db.Numeric(12, 2))
|
||
new_signup_lessons = db.Column(db.Numeric(10, 2))
|
||
new_signup_gift_lessons = db.Column(db.Numeric(10, 2))
|
||
renewal_amount = db.Column(db.Numeric(12, 2))
|
||
renewal_lessons = db.Column(db.Numeric(10, 2))
|
||
renewal_gift_lessons = db.Column(db.Numeric(10, 2))
|
||
consumed_lessons = db.Column(db.Numeric(10, 2))
|
||
consumed_amount = db.Column(db.Numeric(12, 2))
|
||
refund_lessons = db.Column(db.Numeric(10, 2))
|
||
account_fee = db.Column(db.Numeric(12, 2))
|
||
refund_gift_lessons = db.Column(db.Numeric(10, 2))
|
||
refund_amount = db.Column(db.Numeric(12, 2))
|
||
end_lessons = db.Column(db.Numeric(10, 2))
|
||
end_gift_lessons = db.Column(db.Numeric(10, 2))
|
||
end_total_lessons = db.Column(db.Numeric(10, 2))
|
||
end_balance = db.Column(db.Numeric(12, 2))
|
||
unit_price = db.Column(db.Numeric(10, 4))
|
||
notes = db.Column(db.Text)
|
||
source_file = db.Column(db.String(200))
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
__table_args__ = (
|
||
db.UniqueConstraint('account_id', 'year', 'month', name='uq_snapshot_account_month'),
|
||
{'quote': True},
|
||
)
|
||
|
||
@property
|
||
def period_label(self):
|
||
return f'{self.year}-{self.month:02d}'
|
||
|
||
|
||
class ClassRecord(Model):
|
||
"""上课记录：从 Excel「上课情况」列解析"""
|
||
__tablename__ = 'class_records'
|
||
id = db.Column(db.Integer, primary_key=True)
|
||
account_id = db.Column(db.Integer, db.ForeignKey('student_accounts.id'), nullable=False)
|
||
year = db.Column(db.SmallInteger, nullable=False)
|
||
month = db.Column(db.SmallInteger, nullable=False)
|
||
class_date = db.Column(db.Date, nullable=False)
|
||
lessons_consumed = db.Column(db.Numeric(10, 2), default=2)
|
||
raw_text = db.Column(db.String(50))
|
||
created_at = db.Column(db.DateTime, default=datetime.now)
|
||
|
||
__table_args__ = (
|
||
db.UniqueConstraint(
|
||
'account_id', 'year', 'month', 'class_date', 'lessons_consumed',
|
||
name='uq_class_record_day',
|
||
),
|
||
{'quote': True},
|
||
)
|