1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
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)
|
|
