213 lines
8.9 KiB
Python
213 lines
8.9 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
生成月度课时快照（MonthlySnapshot）
|
||
基于现有数据，为每个账户生成当前月份的快照
|
||
"""
|
||
import os
|
||
import sys
|
||
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
|
||
|
||
os.environ['DB_TYPE'] = 'sqlite'
|
||
|
||
from flask import Flask
|
||
from config import Config
|
||
from models import db, StudentAccount, RechargeRecord, ConsumptionRecord, MonthlySnapshot
|
||
from datetime import datetime
|
||
|
||
|
||
def generate_snapshots():
|
||
"""为每个学员账户生成月度快照"""
|
||
app = Flask(__name__)
|
||
app.config.from_object(Config)
|
||
db.init_app(app)
|
||
|
||
with app.app_context():
|
||
# 清空现有快照
|
||
MonthlySnapshot.query.delete()
|
||
|
||
accounts = StudentAccount.query.all()
|
||
print(f"开始为 {len(accounts)} 个账户生成快照...")
|
||
|
||
# 获取所有充值记录并按账户分组
|
||
recharges_by_account = {}
|
||
all_recharges = RechargeRecord.query.all()
|
||
for r in all_recharges:
|
||
key = (r.student_id, r.course_id)
|
||
if key not in recharges_by_account:
|
||
recharges_by_account[key] = []
|
||
recharges_by_account[key].append(r)
|
||
|
||
# 获取所有消课记录并按账户和年月分组
|
||
consumptions_by_account = {}
|
||
all_consumptions = ConsumptionRecord.query.all()
|
||
for c in all_consumptions:
|
||
key = (c.student_id, c.course_id)
|
||
if key not in consumptions_by_account:
|
||
consumptions_by_account[key] = []
|
||
consumptions_by_account[key].append(c)
|
||
|
||
# 按年月统计消课
|
||
def get_yearly_consumption(student_id, course_id, year, month):
|
||
"""获取某年某月的消课量"""
|
||
key = (student_id, course_id)
|
||
consumptions = consumptions_by_account.get(key, [])
|
||
total = 0.0
|
||
for c in consumptions:
|
||
if c.consume_date and c.consume_date.year == year and c.consume_date.month == month:
|
||
total += float(c.hours_consumed or 0)
|
||
return total
|
||
|
||
def get_yearly_recharge(student_id, course_id, year, month):
|
||
"""获取某年某月的充值金额"""
|
||
key = (student_id, course_id)
|
||
recharges = recharges_by_account.get(key, [])
|
||
total_amount = 0.0
|
||
total_lessons = 0.0
|
||
total_gift = 0.0
|
||
for r in recharges:
|
||
if r.created_at and r.created_at.year == year and r.created_at.month == month:
|
||
total_amount += float(r.amount or 0)
|
||
total_lessons += float(r.normal_hours or 0)
|
||
total_gift += float(r.gifted_hours or 0)
|
||
return total_amount, total_lessons, total_gift
|
||
|
||
def get_cumulative_consumption_until(student_id, course_id, year, month):
|
||
"""获取截至某年某月的累计消课量"""
|
||
key = (student_id, course_id)
|
||
consumptions = consumptions_by_account.get(key, [])
|
||
total = 0.0
|
||
for c in consumptions:
|
||
if c.consume_date:
|
||
# 计算到指定月份为止
|
||
if (c.consume_date.year < year) or \
|
||
(c.consume_date.year == year and c.consume_date.month <= month):
|
||
total += float(c.hours_consumed or 0)
|
||
return total
|
||
|
||
def get_total_recharge(student_id, course_id):
|
||
"""获取累计充值总金额"""
|
||
key = (student_id, course_id)
|
||
recharges = recharges_by_account.get(key, [])
|
||
return sum(float(r.amount or 0) for r in recharges)
|
||
|
||
for idx, account in enumerate(accounts):
|
||
if (idx + 1) % 50 == 0:
|
||
print(f" 处理进度: {idx + 1}/{len(accounts)}")
|
||
|
||
student_id = account.student_id
|
||
course_id = account.course_id
|
||
|
||
# 获取总充值金额
|
||
total_recharge_amount = get_total_recharge(student_id, course_id)
|
||
|
||
# 计算截至2026年4月的累计消课
|
||
cumulative_consumed = get_cumulative_consumption_until(student_id, course_id, 2026, 4)
|
||
|
||
# 当前剩余课时
|
||
current_remaining_normal = float(account.normal_hours or 0)
|
||
current_remaining_gift = float(account.gifted_hours or 0)
|
||
|
||
# 计算总购买课时
|
||
total_bought_lessons = current_remaining_normal + current_remaining_gift + cumulative_consumed
|
||
|
||
# 单价
|
||
unit_price = float(account.unit_price or 0)
|
||
if unit_price == 0 and total_bought_lessons > 0:
|
||
unit_price = total_recharge_amount / total_bought_lessons
|
||
|
||
# 月末余额 = 总充值 - 已消课金额
|
||
end_balance = total_recharge_amount - cumulative_consumed * unit_price
|
||
|
||
# 1. 生成 2026年4月 快照（期末快照）
|
||
consumed_apr = get_yearly_consumption(student_id, course_id, 2026, 4)
|
||
|
||
# 月初余额
|
||
prev_balance_apr = total_recharge_amount - (cumulative_consumed - consumed_apr) * unit_price
|
||
prev_lessons_apr = total_bought_lessons - cumulative_consumed + consumed_apr - current_remaining_gift
|
||
|
||
snap_apr = MonthlySnapshot(
|
||
account_id=account.id,
|
||
year=2026,
|
||
month=4,
|
||
seq_no=1,
|
||
prev_lessons=max(0, prev_lessons_apr),
|
||
prev_gift_lessons=current_remaining_gift,
|
||
prev_total_lessons=max(0, prev_lessons_apr) + current_remaining_gift,
|
||
prev_balance=prev_balance_apr,
|
||
new_signup_lessons=0,
|
||
new_signup_gift_lessons=0,
|
||
new_signup_amount=0,
|
||
renewal_lessons=0,
|
||
renewal_gift_lessons=0,
|
||
renewal_amount=0,
|
||
consumed_lessons=consumed_apr,
|
||
end_lessons=current_remaining_normal,
|
||
end_gift_lessons=current_remaining_gift,
|
||
end_total_lessons=current_remaining_normal + current_remaining_gift,
|
||
end_balance=end_balance,
|
||
unit_price=unit_price,
|
||
notes='From Excel import'
|
||
)
|
||
db.session.add(snap_apr)
|
||
|
||
# 2. 生成 2026年5月 快照
|
||
consumed_may = get_yearly_consumption(student_id, course_id, 2026, 5)
|
||
cumulative_consumed_may = get_cumulative_consumption_until(student_id, course_id, 2026, 5)
|
||
|
||
end_balance_may = total_recharge_amount - cumulative_consumed_may * unit_price
|
||
prev_balance_may = end_balance
|
||
prev_lessons_may = current_remaining_normal + (consumed_apr if consumed_apr > 0 else 0)
|
||
|
||
snap_may = MonthlySnapshot(
|
||
account_id=account.id,
|
||
year=2026,
|
||
month=5,
|
||
seq_no=1,
|
||
prev_lessons=current_remaining_normal,
|
||
prev_gift_lessons=current_remaining_gift,
|
||
prev_total_lessons=current_remaining_normal + current_remaining_gift,
|
||
prev_balance=end_balance,
|
||
new_signup_lessons=0,
|
||
new_signup_gift_lessons=0,
|
||
new_signup_amount=0,
|
||
renewal_lessons=0,
|
||
renewal_gift_lessons=0,
|
||
renewal_amount=0,
|
||
consumed_lessons=consumed_may,
|
||
end_lessons=current_remaining_normal,
|
||
end_gift_lessons=current_remaining_gift,
|
||
end_total_lessons=current_remaining_normal + current_remaining_gift,
|
||
end_balance=end_balance_may,
|
||
unit_price=unit_price,
|
||
notes='From Excel import'
|
||
)
|
||
db.session.add(snap_may)
|
||
|
||
db.session.commit()
|
||
print(f"完成! 共生成 {MonthlySnapshot.query.count()} 条月度快照")
|
||
|
||
# 显示统计
|
||
print("\n=== 按月份统计 ===")
|
||
from sqlalchemy import func
|
||
stats = db.session.query(
|
||
MonthlySnapshot.year,
|
||
MonthlySnapshot.month,
|
||
func.count(MonthlySnapshot.id)
|
||
).group_by(MonthlySnapshot.year, MonthlySnapshot.month).order_by(
|
||
MonthlySnapshot.year.desc(), MonthlySnapshot.month.desc()
|
||
).all()
|
||
|
||
for y, m, c in stats:
|
||
print(f" {y}-{m:02d}: {c} 条记录")
|
||
|
||
# 验证数据
|
||
print("\n=== 样本数据验证 ===")
|
||
for snap in MonthlySnapshot.query.filter_by(year=2026, month=4).limit(5).all():
|
||
print(f" Account {snap.account_id}: {snap.year}-{snap.month:02d}")
|
||
print(f" 月末正课: {snap.end_lessons}, 赠课: {snap.end_gift_lessons}, 总课时: {snap.end_total_lessons}")
|
||
print(f" 月末余额: {snap.end_balance:.2f}, 单价: {snap.unit_price:.4f}")
|
||
|
||
|
||
if __name__ == '__main__':
|
||
generate_snapshots()
|