# -*- coding: utf-8 -*- from pathlib import Path import re from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter sql = Path(r"D:/qiansheng/demo/flowApi/docs/tms/tms_schema_pg.sql").read_text(encoding="utf-8") tables = [] current = None block = None create_map = {} for line in sql.splitlines(): m = re.match(r"CREATE TABLE IF NOT EXISTS (\w+)", line) if m: current = {"name": m.group(1), "comment": "", "cols": []} tables.append(current) block = [line] continue if block is not None: block.append(line) if line.startswith(");"): cols = [] for ln in block[1:]: if "CONSTRAINT" in ln or ln.strip().startswith(");") or not ln.strip(): continue ln2 = ln.strip().rstrip(",") parts = ln2.split() if len(parts) >= 2: cols.append((parts[0], " ".join(parts[1:]))) create_map[current["name"]] = cols block = None continue if current is None: continue m = re.match(r"COMMENT ON TABLE (\w+) IS '(.+)';", line) if m and current["name"] == m.group(1): current["comment"] = m.group(2) continue m = re.match(r"COMMENT ON COLUMN (\w+)\.(\w+) IS '(.+)';", line) if m and current["name"] == m.group(1): current["cols"].append((m.group(2), m.group(3))) FORMS = { "tms_place": ("培训地点", "A+YYYYMM+4"), "tms_trainer": ("培训师档案", "AR+YYYY+4"), "tms_trainer_work": ("培训师-工作经历", ""), "tms_trainer_exp": ("培训师-培训经历", ""), "tms_admin_auth": ("培训管理员授权", ""), "tms_train_mode": ("培训方式", ""), "tms_sign_mode": ("签到方式", ""), "tms_eval_mode": ("培训效果评估方式", ""), "tms_course": ("培训课程", "KC+YYYYMM+4"), "tms_material": ("培训教材", "TM+YYYYMM+4"), "tms_question_bank": ("题库", ""), "tms_question": ("试题", ""), "tms_question_option": ("试题选项", ""), "tms_paper": ("试卷", ""), "tms_paper_section": ("试卷章节", ""), "tms_paper_question": ("试卷试题", ""), "tms_post_catalog": ("岗位培训目录", ""), "tms_post_catalog_item": ("岗位目录明细", ""), "tms_post_plan": ("岗位培训计划", "JP+YYYYMM+4"), "tms_post_plan_item": ("岗位计划明细", ""), "tms_person_post": ("个人岗位培训", ""), "tms_person_post_item": ("个人岗位培训明细", ""), "tms_annual_plan": ("年度培训计划", "YYYY+4位流水"), "tms_annual_plan_item": ("年度计划明细", ""), "tms_annual_delay": ("年度计划延期", ""), "tms_task": ("培训任务", "TT+YYYYMM+4"), "tms_task_file": ("任务教材附件", ""), "tms_task_quiz": ("任务提问预设", ""), "tms_person_task": ("个人培训任务", ""), "tms_record": ("培训记录", "默认同任务编号"), "tms_record_sign": ("签到/结果明细", ""), "tms_record_change": ("培训记录变更", ""), "tms_archive_file": ("培训资料归档", ""), "tms_exam": ("在线考试答卷", ""), "tms_exam_item": ("答卷试题快照", ""), "tms_practice": ("实操考核", ""), "tms_practice_item": ("实操考核明细", ""), "tms_quiz_score": ("现场提问考核", ""), "tms_person_file": ("个人培训档案", "AR+YYYY+4"), "tms_person_work": ("档案-工作履历", ""), "tms_person_cert": ("资质文件/证书", ""), "tms_person_train": ("档案-培训目录快照", ""), "tms_out_train": ("外出培训", "WC+YYYYMM+4"), "tms_transfer_eval": ("转岗考核鉴定", ""), "tms_newcomer_eval": ("新员工培训效果考评", ""), "tms_return_eval": ("复岗培训效果考评", ""), "tms_print_template": ("套打模板版本", ""), "tms_remind_rule": ("提醒规则", ""), } CTRL = { "user_id": "用户", "teacher_id": "用户(可多选)", "trainees": "用户多选", "admin_user_id": "用户", "applicant_id": "用户", "author_id": "用户", "registrar_id": "用户", "grader_id": "用户", "archiver_id": "用户", "dept_id": "组织", "apply_dept_id": "组织", "author_dept_id": "组织", "scope_dept_id": "组织多选", "post_id": "岗位", "main_post_id": "岗位", "part_post_id": "岗位", "file_json": "附件", "cert_file": "附件", "lecture_file": "附件", "paper_sign_file": "附件", "stem": "富文本", "key_points": "多行", "subject": "多行", "start_time": "日期时间", "end_time": "日期时间", "close_time": "日期时间", "exam_start": "日期时间", "exam_end": "日期时间", "hire_date": "日期", "enabled": "下拉/开关", "biz_status": "下拉-字典", "publish_status": "下拉-字典", "train_mode": "下拉/关联培训方式", "eval_mode": "下拉/关联评估方式", "place_id": "关联表单-培训地点", "material_id": "关联表单-培训教材", "paper_id": "关联表单-试卷", "bank_id": "关联表单-题库", "course_id": "关联表单-课程", "task_id": "关联表单-培训任务", "record_id": "关联表单-培训记录", } def suggest_ctrl(name, typ): if name in CTRL: return CTRL[name] if name.startswith("f_"): return "系统字段" if "timestamp" in typ: return "日期时间" if typ.startswith("date"): return "日期" if "numeric" in typ or typ.startswith("int"): return "数字" if name.endswith("_id"): return "关联/用户/组织" if "text" in typ: return "多行/附件/JSON" return "单行/下拉" wb = Workbook() ws = wb.active ws.title = "表清单" ws.append(["序号", "表名", "中文名", "表说明", "主从", "流程", "单据规则"]) for i, t in enumerate(tables, 1): child = any(c[0] == "f_foreign_id" for c in t["cols"]) flow = any(c[0] == "f_flow_id" for c in t["cols"]) form_name, rule = FORMS.get(t["name"], (t["comment"][:20], "")) ws.append([i, t["name"], form_name, t["comment"], "子表" if child else "主表", "是" if flow else "否", rule]) ws2 = wb.create_sheet("字段明细") ws2.append(["表名", "中文名", "字段名", "类型", "说明", "JNPF建议控件"]) for t in tables: comments = {n: c for n, c in t["cols"]} form_name = FORMS.get(t["name"], (t["comment"][:20], ""))[0] for n, typ in create_map.get(t["name"], []): ws2.append([t["name"], form_name, n, typ, comments.get(n, ""), suggest_ctrl(n, typ)]) ws3 = wb.create_sheet("字典与单据规则") ws3.append(["类型", "名称", "选项或规则", "用途"]) for row in [ ("字典", "任务发布状态", "draft未发布 / published已发布 / cancelled已取消 / done已完成", "tms_task.publish_status"), ("字典", "培训分类", "临时培训 / 岗位培训计划 / 年度培训计划 / 文件生效培训 / 外出培训 / 再维护", "tms_task.category"), ("字典", "任务类型", "new新增 / continue继续", "tms_task.task_kind"), ("字典", "归档状态", "pending待归档 / ready可归档 / archived已归档 / invalid无效", "tms_record.archive_status"), ("字典", "个人任务状态", "todo未完成 / signed已签到 / done已完成 / expired已过期 / cancelled已取消", "tms_person_task.biz_status"), ("字典", "是否合格", "pass合格 / fail不合格 / absent未参加 / na无需考核", "签到结果/考试"), ("字典", "题型", "single单选 / multi多选 / judge判断 / blank填空 / essay问答", "试题"), ("字典", "试题试卷状态", "open开放 / closed不开放 / invalid废弃", "题库试卷"), ("字典", "难度", "easy简单 / normal一般 / hard困难", "试题"), ("字典", "阅卷状态", "auto无需阅卷 / pending待批改 / graded已批改", "tms_exam.grade_status"), ("字典", "有效撤销", "valid有效期内 / revoked已撤销", "培训师/授权/教材"), ("字典", "计划行状态", "pending待执行 / tasked已生成任务 / done已完成 / delayed已延期", "年度计划明细"), ("字典", "教材类型", "Word / PPT / Excel / PDF / 视频 / 音频", "教材"), ("单据规则", "地点编号", "A + YYYYMM + 4位流水", "tms_place.place_no 例 A2026090001"), ("单据规则", "档案编号", "AR + YYYY + 4位流水", "培训师/个人档案 例 AR20260028"), ("单据规则", "教材编号", "TM + YYYYMM + 4位流水", "tms_material 例 TM2026090001"), ("单据规则", "岗位计划编号", "JP + YYYYMM + 4位流水", "tms_post_plan 例 JP2026090012"), ("单据规则", "年度计划编号", "年份4位 + 4位流水", "tms_annual_plan 例 20260001"), ("单据规则", "培训任务编号", "TT + YYYYMM + 4位流水", "tms_task 例 TT2026090618"), ]: ws3.append(list(row)) header_fill = PatternFill("solid", fgColor="1F4E79") header_font = Font(color="FFFFFF", bold=True) thin = Border(*(Side(style="thin", color="D0D0D0"),) * 4) for sheet in wb.worksheets: for cell in sheet[1]: cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal="center") sheet.freeze_panes = "A2" sheet.auto_filter.ref = sheet.dimensions widths = {} for row in sheet.iter_rows(min_row=1, max_row=min(sheet.max_row, 80)): for c in row: c.border = thin c.alignment = Alignment(wrap_text=True, vertical="center") widths[c.column] = max(widths.get(c.column, 10), min(len(str(c.value or "")) + 4, 42)) for col, w in widths.items(): sheet.column_dimensions[get_column_letter(col)].width = w xlsx = Path(r"D:/qiansheng/demo/flowApi/docs/tms/玻思韬TMS-JNPF数据表字段清单.xlsx") wb.save(xlsx) print("tables", len(tables), "xlsx", xlsx.stat().st_size)