80 lines
3.7 KiB
SQL
80 lines
3.7 KiB
SQL
-- 课时核对表 Excel 流水账 — 扩展字段 + 月度快照 + 上课记录
|
||
-- 与现有 students / courses / student_accounts 集成，非独立库
|
||
|
||
-- ---------- students 扩展 ----------
|
||
ALTER TABLE `students`
|
||
ADD COLUMN `display_name` VARCHAR(50) NULL COMMENT '括号外正式姓名' AFTER `name`,
|
||
ADD COLUMN `nickname` VARCHAR(50) NULL COMMENT '括号内小名' AFTER `display_name`;
|
||
|
||
-- ---------- courses 扩展 ----------
|
||
ALTER TABLE `courses`
|
||
ADD COLUMN `course_code` VARCHAR(20) NULL COMMENT 'scratch/python/c++/wedo' AFTER `name`,
|
||
ADD UNIQUE INDEX `uk_courses_code` (`course_code`);
|
||
|
||
-- ---------- student_accounts（学员课程账户）扩展 ----------
|
||
ALTER TABLE `student_accounts`
|
||
ADD COLUMN `unit_price` DECIMAL(10,4) DEFAULT 0 COMMENT '当前单价' AFTER `cumulative_start_date`,
|
||
ADD COLUMN `account_status` VARCHAR(20) DEFAULT 'active' COMMENT 'active/graduated' AFTER `unit_price`;
|
||
|
||
-- ---------- monthly_snapshots（月度快照，核心表）----------
|
||
CREATE TABLE IF NOT EXISTS `monthly_snapshots` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`account_id` INT NOT NULL,
|
||
`year` SMALLINT NOT NULL,
|
||
`month` SMALLINT NOT NULL,
|
||
`seq_no` INT NULL,
|
||
`prev_lessons` DECIMAL(10,2) NULL,
|
||
`prev_gift_lessons` DECIMAL(10,2) NULL,
|
||
`prev_total_lessons` DECIMAL(10,2) NULL,
|
||
`prev_balance` DECIMAL(12,2) NULL,
|
||
`new_signup_amount` DECIMAL(12,2) NULL,
|
||
`new_signup_lessons` DECIMAL(10,2) NULL,
|
||
`new_signup_gift_lessons` DECIMAL(10,2) NULL,
|
||
`renewal_amount` DECIMAL(12,2) NULL,
|
||
`renewal_lessons` DECIMAL(10,2) NULL,
|
||
`renewal_gift_lessons` DECIMAL(10,2) NULL,
|
||
`consumed_lessons` DECIMAL(10,2) NULL,
|
||
`consumed_amount` DECIMAL(12,2) NULL,
|
||
`refund_lessons` DECIMAL(10,2) NULL,
|
||
`account_fee` DECIMAL(12,2) NULL,
|
||
`refund_gift_lessons` DECIMAL(10,2) NULL,
|
||
`refund_amount` DECIMAL(12,2) NULL,
|
||
`end_lessons` DECIMAL(10,2) NULL,
|
||
`end_gift_lessons` DECIMAL(10,2) NULL,
|
||
`end_total_lessons` DECIMAL(10,2) NULL,
|
||
`end_balance` DECIMAL(12,2) NULL,
|
||
`unit_price` DECIMAL(10,4) NULL,
|
||
`notes` TEXT NULL,
|
||
`source_file` VARCHAR(200) NULL,
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_snapshot_account_month` (`account_id`, `year`, `month`),
|
||
KEY `idx_snapshot_period` (`year`, `month`),
|
||
KEY `idx_snapshot_account` (`account_id`),
|
||
CONSTRAINT `fk_snapshot_account` FOREIGN KEY (`account_id`) REFERENCES `student_accounts` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课时核对表月度快照';
|
||
|
||
-- ---------- class_records（上课记录）----------
|
||
CREATE TABLE IF NOT EXISTS `class_records` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`account_id` INT NOT NULL,
|
||
`year` SMALLINT NOT NULL,
|
||
`month` SMALLINT NOT NULL,
|
||
`class_date` DATE NOT NULL,
|
||
`lessons_consumed` DECIMAL(10,2) DEFAULT 2,
|
||
`raw_text` VARCHAR(50) NULL,
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uq_class_record_day` (`account_id`, `year`, `month`, `class_date`, `lessons_consumed`),
|
||
KEY `idx_class_record_account` (`account_id`),
|
||
KEY `idx_class_record_date` (`class_date`),
|
||
CONSTRAINT `fk_class_record_account` FOREIGN KEY (`account_id`) REFERENCES `student_accounts` (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Excel上课情况解析记录';
|
||
|
||
-- 初始化课程类型（可按机构调整显示名）
|
||
INSERT IGNORE INTO `courses` (`name`, `course_code`, `level`, `type`, `status`) VALUES
|
||
('Scratch', 'scratch', 'scratch', '编程', 1),
|
||
('Python', 'python', 'python', '编程', 1),
|
||
('C++', 'c++', 'c++', '编程', 1),
|
||
('WeDo', 'wedo', 'wedo', '编程', 1);
|