all-project/用户信息同步统一身份认证/gen_student_export_excel.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""根据 hzdyjs_xg.sql 模型生成「学生可提取第三方信息」Excel 清单（单表版）"""

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

OUTPUT = "学生信息第三方提取清单.xlsx"

HEADER_FILL = PatternFill("solid", fgColor="4472C4")
HEADER_FONT = Font(bold=True, color="FFFFFF", size=11)
THIN = Side(style="thin")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
WRAP = Alignment(wrap_text=True, vertical="center")
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)

# (数据表, 字段名, 中文名称, 数据类型, 说明)
FIELDS = [
    # ── xg_student_msg 学生信息主表 ──
    ("xg_student_msg", "student_no", "学号", "varchar(32)", "学号，唯一标识"),
    ("xg_student_msg", "name", "姓名", "varchar(50)", ""),
    ("xg_student_msg", "name_english", "英文姓名", "varchar(100)", ""),
    ("xg_student_msg", "former_name", "曾用名", "varchar(50)", ""),
    ("xg_student_msg", "gender", "性别", "char(1)", "0-男，1-女"),
    ("xg_student_msg", "birth_date", "出生日期", "date", ""),
    ("xg_student_msg", "nationality", "民族", "varchar(20)", ""),
    ("xg_student_msg", "native_place", "籍贯", "varchar(20)", ""),
    ("xg_student_msg", "political_status", "政治面貌", "varchar(20)", ""),
    ("xg_student_msg", "id_card", "身份证号", "varchar(18)", ""),
    ("xg_student_msg", "photo_url", "照片URL", "varchar(500)", ""),
    ("xg_student_msg", "phone", "手机号码", "varchar(20)", ""),
    ("xg_student_msg", "email", "电子邮箱", "varchar(100)", ""),
    ("xg_student_msg", "qq", "QQ号码", "varchar(20)", ""),
    ("xg_student_msg", "wechat", "微信号", "varchar(50)", ""),
    ("xg_student_msg", "postal_code", "邮政编码", "varchar(10)", ""),
    ("xg_student_msg", "home_address", "家庭地址", "varchar(1000)", ""),
    ("xg_student_msg", "current_address", "现住址", "varchar(1000)", ""),
    ("xg_student_msg", "hukou_location", "户籍所在地", "varchar(1000)", ""),
    ("xg_student_msg", "student_status", "学籍状态", "char(1)", "1-在籍，2-休学，3-复学，4-退学，5-毕业，6-不在籍"),
    ("xg_student_msg", "enrollment_year", "入学年份", "int(4)", ""),
    ("xg_student_msg", "enrollment_date", "入学日期", "date", ""),
    ("xg_student_msg", "admission_category", "录取类别", "varchar(20)", "统招、自主招生、专项计划等"),
    ("xg_student_msg", "school_system", "学制（年）", "int(2)", ""),
    ("xg_student_msg", "academic_level", "学历层次", "varchar(20)", ""),
    ("xg_student_msg", "training_type", "培养方式", "varchar(20)", "全日制、在职、函授、夜大等"),
    ("xg_student_msg", "major_id", "专业ID", "varchar(100)", ""),
    ("xg_student_msg", "major_name", "专业名称", "varchar(100)", ""),
    ("xg_student_msg", "class_id", "班级ID", "varchar(100)", ""),
    ("xg_student_msg", "class_name", "班级名称", "varchar(100)", ""),
    ("xg_student_msg", "grade", "年级", "varchar(10)", ""),
    ("xg_student_msg", "college_id", "学院ID", "varchar(100)", ""),
    ("xg_student_msg", "college_name", "学院名称", "varchar(100)", ""),
    ("xg_student_msg", "counselor_name", "辅导员姓名", "varchar(50)", ""),
    ("xg_student_msg", "class_teacher_name", "班主任姓名", "varchar(50)", ""),
    ("xg_student_msg", "is_boarder", "是否住校", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "education_level", "文化程度", "varchar(20)", ""),
    ("xg_student_msg", "household_register_type", "户口类型", "varchar(20)", "农业、非农业"),
    ("xg_student_msg", "religion", "宗教信仰", "varchar(200)", ""),
    ("xg_student_msg", "source_region", "生源地区", "varchar(100)", "省市县"),
    ("xg_student_msg", "graduate_school", "毕业院校", "varchar(100)", "升学前毕业院校"),
    ("xg_student_msg", "marital_status", "婚姻状况", "varchar(10)", "1未婚、2已婚、3离异、4丧偶"),
    ("xg_student_msg", "emergency_contact_name", "紧急联系人姓名", "varchar(50)", ""),
    ("xg_student_msg", "emergency_contact_phone", "紧急联系人电话", "varchar(20)", ""),
    ("xg_student_msg", "emergency_contact_relation", "紧急联系人关系", "varchar(100)", ""),
    ("xg_student_msg", "is_poor_student", "是否贫困生", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "is_minority", "是否少数民族", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "is_disabled", "是否残疾", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "is_orphan", "是否孤儿", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "is_left_behind_child", "是否留守儿童", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "is_single_child", "是否独生子女", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "is_low_income", "是否低保", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "has_loan", "是否有助学贷款", "tinyint(1)", "0-否，1-是"),
    ("xg_student_msg", "loan_amount", "助学贷款金额", "decimal(10,2)", ""),
    ("xg_student_msg", "father_name", "父亲姓名", "varchar(50)", ""),
    ("xg_student_msg", "father_phone", "父亲电话", "varchar(20)", ""),
    ("xg_student_msg", "father_work_unit", "父亲工作单位", "varchar(1000)", ""),
    ("xg_student_msg", "mother_name", "母亲姓名", "varchar(50)", ""),
    ("xg_student_msg", "mother_phone", "母亲电话", "varchar(20)", ""),
    ("xg_student_msg", "mother_work_unit", "母亲工作单位", "varchar(1000)", ""),
    ("xg_student_msg", "guardian_name", "监护人姓名", "varchar(50)", ""),
    ("xg_student_msg", "guardian_phone", "监护人电话", "varchar(20)", ""),
    ("xg_student_msg", "guardian_relation", "监护人关系", "varchar(20)", ""),
    ("xg_student_msg", "family_income", "家庭年收入", "decimal(12,2)", ""),
    ("xg_student_msg", "per_capita_income", "人均收入", "decimal(12,2)", ""),
    ("xg_student_msg", "income_source", "收入来源", "varchar(100)", ""),
    ("xg_student_msg", "family_economic_status", "家庭经济状况", "varchar(20)", "富裕、一般、困难、特困"),
    ("xg_student_msg", "family_members", "家庭成员数量", "tinyint(2)", ""),
    ("xg_student_msg", "height", "身高（cm）", "decimal(10,2)", ""),
    ("xg_student_msg", "weight", "体重（kg）", "decimal(10,2)", ""),
    ("xg_student_msg", "blood_type", "血型", "varchar(10)", ""),
    ("xg_student_msg", "health_status", "健康状况", "varchar(1000)", ""),
    ("xg_student_msg", "special_condition", "特殊体质或疾病", "varchar(1000)", ""),
    ("xg_student_msg", "medical_history", "病史", "varchar(1000)", ""),
    ("xg_student_msg", "allergies", "过敏史", "varchar(1000)", ""),
    ("xg_student_msg", "disability_type", "残疾类型", "varchar(50)", "1视力、2听力、3智力、9其他"),
    ("xg_student_msg", "disability_level", "残疾等级", "varchar(20)", ""),
    ("xg_student_msg", "bank_card_no", "银行卡号", "varchar(30)", ""),
    ("xg_student_msg", "bank_name", "开户银行", "varchar(100)", ""),
    ("xg_student_msg", "bank_branch", "开户支行", "varchar(100)", ""),
    ("xg_student_msg", "card_holder_name", "持卡人姓名", "varchar(50)", ""),
    ("xg_student_msg", "specialties", "特长爱好", "text", ""),
    ("xg_student_msg", "awards", "获奖情况", "text", ""),
    ("xg_student_msg", "violations", "违纪记录", "text", ""),
    ("xg_student_msg", "social_activities", "社会活动", "text", ""),
    ("xg_student_msg", "dormitory_building", "宿舍楼栋", "varchar(100)", ""),
    ("xg_student_msg", "dormitory_room", "宿舍房间号", "varchar(100)", ""),
    # ── xg_class_msg 班级信息表 ──
    ("xg_class_msg", "class_code", "班级代码", "varchar(32)", ""),
    ("xg_class_msg", "class_name", "班级名称", "varchar(100)", ""),
    ("xg_class_msg", "college_name", "学院名称", "varchar(100)", ""),
    ("xg_class_msg", "major_name", "专业名称", "varchar(100)", ""),
    ("xg_class_msg", "grade", "年级", "varchar(10)", ""),
    ("xg_class_msg", "enrollment_year", "入学年份", "int(4)", ""),
    ("xg_class_msg", "graduation_year", "毕业年份", "int(4)", ""),
    ("xg_class_msg", "school_system", "学制", "int(11)", ""),
    ("xg_class_msg", "training_type", "培养方式", "varchar(20)", ""),
    ("xg_class_msg", "degree_type", "学位类型", "varchar(20)", ""),
    ("xg_class_msg", "counselor_name", "辅导员姓名", "varchar(50)", ""),
    ("xg_class_msg", "class_teacher_name", "班主任姓名", "varchar(50)", ""),
    ("xg_class_msg", "class_status", "班级状态", "char(1)", "1-正常，2-暂停，3-毕业，4-解散"),
    # ── xg_dorm_bed_student 宿舍分配表 ──
    ("xg_dorm_bed_student", "student_id", "学生ID", "bigint(20)", "关联 xg_student_msg.id"),
    ("xg_dorm_bed_student", "bed_id", "床位ID", "bigint(20)", ""),
    ("xg_dorm_bed_student", "room_id", "房间ID", "bigint(20)", ""),
    ("xg_dorm_bed_student", "building_id", "宿舍楼ID", "bigint(20)", ""),
    ("xg_dorm_bed_student", "campus_id", "校区ID", "bigint(20)", ""),
    ("xg_dorm_bed_student", "status", "状态", "char(1)", "0-正常，1-已退宿"),
    ("xg_dorm_bed_student", "assign_time", "分配时间", "datetime", ""),
    # ── xg_student_leave 请假记录表 ──
    ("xg_student_leave", "student_no", "学号", "varchar(20)", ""),
    ("xg_student_leave", "student_name", "学生姓名", "varchar(50)", ""),
    ("xg_student_leave", "class_name", "班级名称", "varchar(100)", ""),
    ("xg_student_leave", "college_name", "学院名称", "varchar(100)", ""),
    ("xg_student_leave", "leave_type_name", "请假类型", "varchar(50)", ""),
    ("xg_student_leave", "start_time", "开始时间", "datetime", ""),
    ("xg_student_leave", "end_time", "结束时间", "datetime", ""),
    ("xg_student_leave", "leave_duration", "请假时长（天）", "decimal(5,2)", ""),
    ("xg_student_leave", "is_leave_school", "是否离校", "char(1)", "0-否，1-是"),
    ("xg_student_leave", "leave_reason", "请假原因", "text", ""),
    ("xg_student_leave", "approval_status", "审批状态", "char(100)", ""),
    # ── xg_student_status_change 学籍异动表 ──
    ("xg_student_status_change", "student_no", "学号", "varchar(100)", ""),
    ("xg_student_status_change", "change_type", "异动类型", "varchar(20)", "TUI_XUE退学、XIU_XUE休学、FU_XUE复学、BI_YE毕业"),
    ("xg_student_status_change", "apply_date", "申请日期", "datetime", ""),
    ("xg_student_status_change", "effective_date", "生效日期", "datetime", ""),
    ("xg_student_status_change", "reason", "异动原因", "varchar(1000)", ""),
    # ── xg_student_class_change 换班记录表 ──
    ("xg_student_class_change", "student_no", "学号", "varchar(32)", ""),
    ("xg_student_class_change", "student_name", "学生姓名", "varchar(50)", ""),
    ("xg_student_class_change", "original_class_name", "原班级名称", "varchar(100)", ""),
    ("xg_student_class_change", "new_class_name", "新班级名称", "varchar(100)", ""),
    ("xg_student_class_change", "change_time", "换班时间", "datetime", ""),
    ("xg_student_class_change", "change_reason", "换班原因", "varchar(500)", ""),
    # ── jcbt_work_study_application 勤工助学申请表 ──
    ("jcbt_work_study_application", "student_number", "学号", "varchar(20)", ""),
    ("jcbt_work_study_application", "student_name", "学生姓名", "varchar(50)", ""),
    ("jcbt_work_study_application", "college_name", "学院名称", "varchar(100)", ""),
    ("jcbt_work_study_application", "major", "专业", "varchar(100)", ""),
    ("jcbt_work_study_application", "grade", "年级", "varchar(10)", ""),
    ("jcbt_work_study_application", "class_name", "班级", "varchar(50)", ""),
    ("jcbt_work_study_application", "phone", "联系电话", "varchar(20)", ""),
    ("jcbt_work_study_application", "id_card", "身份证号", "varchar(18)", ""),
    ("jcbt_work_study_application", "position_name", "岗位名称", "varchar(100)", ""),
    ("jcbt_work_study_application", "status", "申请状态", "varchar(20)", ""),
]


def main():
    wb = Workbook()
    ws = wb.active
    ws.title = "学生可提取信息"

    headers = ["序号", "数据表", "字段名", "中文名称", "数据类型", "说明"]
    ws.append(headers)
    for cell in ws[1]:
        cell.fill = HEADER_FILL
        cell.font = HEADER_FONT
        cell.alignment = CENTER
        cell.border = BORDER

    for i, row in enumerate(FIELDS, 1):
        ws.append([i, *row])
        for cell in ws[ws.max_row]:
            cell.alignment = WRAP
            cell.border = BORDER

    for col in ws.columns:
        letter = get_column_letter(col[0].column)
        width = max(len(str(c.value or "")) for c in col)
        ws.column_dimensions[letter].width = min(max(width + 2, 8), 40)
    ws.column_dimensions["A"].width = 6
    ws.column_dimensions["B"].width = 28
    ws.column_dimensions["F"].width = 40

    ws.freeze_panes = "A2"
    wb.save(OUTPUT)
    print(f"已生成: {OUTPUT}（共 {len(FIELDS)} 条）")


if __name__ == "__main__":
    main()