From 1c0304091d5a7dd05859167f5e417cf932c67028 Mon Sep 17 00:00:00 2001
From: 刘光辉 <347230014@qq.com>
Date: 星期四, 17 九月 2026 11:19:17 +0800
Subject: [PATCH] 字段信息基础班
---
docs/tms/gen_xlsx.py | 222 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 222 insertions(+), 0 deletions(-)
diff --git a/docs/tms/gen_xlsx.py b/docs/tms/gen_xlsx.py
new file mode 100644
index 0000000..f5c7565
--- /dev/null
+++ b/docs/tms/gen_xlsx.py
@@ -0,0 +1,222 @@
+# -*- 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)
--
Gitblit v1.8.0