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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
| # -*- coding: utf-8 -*-
| from pathlib import Path
|
| SYS = [
| ("f_id", "varchar(50)", "NOT NULL", "主键"),
| ("f_tenant_id", "varchar(50)", None, "租户"),
| ]
| SYS_AUDIT = [
| ("f_creator_user_id", "varchar(50)", None, "创建人"),
| ("f_creator_time", "timestamp", None, "创建时间"),
| ("f_last_modify_user_id", "varchar(50)", None, "修改人"),
| ("f_last_modify_time", "timestamp", None, "修改时间"),
| ("f_delete_mark", "int", "NOT NULL DEFAULT 0", "删除标记 0正常 1删除"),
| ("f_delete_user_id", "varchar(50)", None, "删除人"),
| ("f_delete_time", "timestamp", None, "删除时间"),
| ]
| SYS_FLOW = [
| ("f_flow_id", "varchar(50)", None, "流程模板ID"),
| ("f_flow_task_id", "varchar(50)", None, "流程任务ID"),
| ("f_flow_state", "int", None, "流程状态"),
| ]
| CHILD_FK = ("f_foreign_id", "varchar(50)", "NOT NULL", "主表f_id")
|
| def col(name, typ, extra=None, cmt=""):
| return (name, typ, extra, cmt)
|
| tables = []
|
| def add(name, comment, cols, indexes=None, child=False, flow=False, delete_user=True):
| fields = list(SYS)
| if child:
| fields.append(CHILD_FK)
| fields.extend(cols)
| if flow:
| fields.extend(SYS_FLOW)
| audit = list(SYS_AUDIT)
| if not delete_user:
| audit = [x for x in audit if x[0] not in ("f_delete_user_id", "f_delete_time")]
| fields.extend(audit)
| tables.append({
| "name": name,
| "comment": comment,
| "cols": fields,
| "indexes": indexes or [],
| "child": child,
| })
|
| # ===== 1 基础设置 =====
| add("tms_place", "培训地点。在线学习任务地点可空；占用按小时与未取消任务时间交叉校验。", [
| col("place_no", "varchar(50)", "NOT NULL", "地点编号，单据规则 A+YYYYMM+4，如 A2026090001"),
| col("place_name", "varchar(200)", "NOT NULL", "地点名称"),
| col("place_group", "varchar(50)", None, "组别/分区，字典：办公区/生产区/A区/B区"),
| col("enabled", "varchar(20)", "NOT NULL DEFAULT '1'", "1启用 0停用"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[
| ("uk", "uk_tms_place_no", ["place_no"], True),
| ("idx", "idx_tms_place_group", ["place_group", "enabled"], False),
| ])
|
| add("tms_trainer", "培训师档案，关联系统用户；重复录入按user_id识别；账号注销后自动废弃。", [
| col("archive_no", "varchar(50)", None, "档案编号 AR+YYYY+4，可与个人培训档案共用"),
| col("user_id", "varchar(50)", "NOT NULL", "系统用户ID"),
| col("trainer_name", "varchar(100)", None, "姓名（冗余，便于列表）"),
| col("dept_id", "varchar(50)", None, "部门"),
| col("post_id", "varchar(50)", None, "岗位"),
| col("school", "varchar(200)", None, "毕业院校"),
| col("education", "varchar(50)", None, "学历"),
| col("major", "varchar(100)", None, "专业"),
| col("tech_title", "varchar(100)", None, "技术职称"),
| col("hire_date", "date", None, "入职时间"),
| col("license", "varchar(200)", None, "执业证书"),
| col("work_years", "varchar(50)", None, "工作年限"),
| col("trainer_level", "varchar(50)", None, "培训师级别"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'valid'", "valid有效期内 / revoked已撤销"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[
| ("uk", "uk_tms_trainer_user", ["user_id"], True),
| ("uk", "uk_tms_trainer_no", ["archive_no"], True),
| ("idx", "idx_tms_trainer_status", ["biz_status"], False),
| ])
|
| add("tms_trainer_work", "培训师个人工作经历子表", [
| col("sort_no", "int", None, "序号"),
| col("hire_date", "date", None, "入职时间"),
| col("leave_date", "date", None, "离职时间"),
| col("company_name", "varchar(200)", None, "公司名称"),
| col("post_name", "varchar(100)", None, "任职岗位"),
| col("leave_reason", "varchar(200)", None, "离职原因"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_trainer_work_fid", ["f_foreign_id"], False)])
|
| add("tms_trainer_exp", "培训师个人培训经历子表", [
| col("sort_no", "int", None, "序号"),
| col("train_date", "date", None, "培训时间"),
| col("org_name", "varchar(200)", None, "培训机构"),
| col("cert_name", "varchar(200)", None, "培训证书"),
| col("cert_file", "text", None, "证书附件"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_trainer_exp_fid", ["f_foreign_id"], False)])
|
| add("tms_admin_auth", "培训管理员跨部门授权。到期前一个月提醒管理员/培训师。", [
| col("admin_user_id", "varchar(50)", "NOT NULL", "培训管理员"),
| col("scope_dept_id", "text", "NOT NULL", "管理范围，组织多选"),
| col("include_child", "varchar(10)", "NOT NULL DEFAULT '1'", "是否包含下级部门 1是 0否"),
| col("registrar_id", "varchar(50)", None, "登记人"),
| col("register_date", "date", None, "登记日期"),
| col("expire_date", "date", None, "授权到期日，可空表示长期"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'valid'", "valid有效 / revoked废弃"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[("idx", "idx_tms_admin_auth_user", ["admin_user_id", "biz_status"], False)])
|
| add("tms_train_mode", "培训方式配置（含签到规则，不仅是字典）", [
| col("mode_code", "varchar(50)", "NOT NULL", "编码：onsite/practice/online"),
| col("mode_name", "varchar(100)", "NOT NULL", "名称：集中授课/操作授课/在线学习"),
| col("sign_rule", "varchar(20)", "NOT NULL DEFAULT 'both'", "scan仅扫码 / both扫码+在线"),
| col("allow_guest_sign", "varchar(10)", "NOT NULL DEFAULT '0'", "允许名单外签到，集中授课=1"),
| col("same_day_required", "varchar(10)", "NOT NULL DEFAULT '0'", "开始结束必须同一天，集中/操作=1"),
| col("place_required", "varchar(10)", "NOT NULL DEFAULT '1'", "地点必填，在线学习=0"),
| col("sort_no", "int", None, "排序"),
| col("enabled", "varchar(10)", "NOT NULL DEFAULT '1'", "启用"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[("uk", "uk_tms_train_mode_code", ["mode_code"], True)])
|
| add("tms_sign_mode", "签到方式：APP扫码 / 微信扫码 / 在线签到", [
| col("mode_code", "varchar(50)", "NOT NULL", "编码：app_scan/wx_scan/online"),
| col("mode_name", "varchar(100)", "NOT NULL", "名称"),
| col("tip_seconds", "int", "NOT NULL DEFAULT 30", "有培训要点时强制阅读秒数，IT可调"),
| col("match_roster", "varchar(10)", "NOT NULL DEFAULT '1'", "是否必须匹配培训对象名单"),
| col("enabled", "varchar(10)", "NOT NULL DEFAULT '1'", "启用"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[("uk", "uk_tms_sign_mode_code", ["mode_code"], True)])
|
| add("tms_eval_mode", "培训效果评估方式：提问 / 现场操作 / 在线考试 / 无需考核", [
| col("mode_code", "varchar(50)", "NOT NULL", "编码：quiz/practice/exam/none"),
| col("mode_name", "varchar(100)", "NOT NULL", "名称"),
| col("need_paper", "varchar(10)", "NOT NULL DEFAULT '0'", "是否需要试卷"),
| col("need_quiz", "varchar(10)", "NOT NULL DEFAULT '0'", "是否需要提问预设"),
| col("need_practice", "varchar(10)", "NOT NULL DEFAULT '0'", "是否需要实操评分"),
| col("enabled", "varchar(10)", "NOT NULL DEFAULT '1'", "启用"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[("uk", "uk_tms_eval_mode_code", ["mode_code"], True)])
|
| add("tms_course", "培训课程主数据，计划/任务可引用", [
| col("course_no", "varchar(50)", None, "课程编号"),
| col("course_name", "varchar(200)", "NOT NULL", "课程名称"),
| col("category", "varchar(50)", None, "课程分类"),
| col("train_mode", "varchar(50)", None, "培训方式编码"),
| col("eval_mode", "varchar(50)", None, "考核方式编码"),
| col("paper_id", "varchar(50)", None, "关联试卷"),
| col("hours", "numeric(8,1)", None, "学时"),
| col("enabled", "varchar(10)", "NOT NULL DEFAULT '1'", "启用"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[("uk", "uk_tms_course_no", ["course_no"], True)])
|
| add("tms_material", "培训教材/企业知识库。可上传本地或引用DMS（需鉴权）。撤销后不可再引用。", [
| col("material_no", "varchar(50)", "NOT NULL", "教材编号 TM+YYYYMM+4"),
| col("material_name", "varchar(500)", "NOT NULL", "教材名称"),
| col("file_type", "varchar(50)", None, "Word/PPT/Excel/PDF/视频/音频"),
| col("apply_time", "date", None, "申请时间"),
| col("applicant_id", "varchar(50)", None, "上传申请人"),
| col("apply_dept_id", "varchar(50)", None, "申请人部门"),
| col("file_json", "text", None, "教材附件（JNPF附件JSON）"),
| col("dms_file_id", "varchar(50)", None, "引用的DMS文件ID"),
| col("quote_count", "int", "NOT NULL DEFAULT 0", "被任务引用次数"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'valid'", "valid有效 / revoked已撤销"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[
| ("uk", "uk_tms_material_no", ["material_no"], True),
| ("idx", "idx_tms_material_type", ["file_type", "biz_status"], False),
| ])
|
| # ===== 2 考试引擎 =====
| add("tms_question_bank", "题库", [
| col("bank_name", "varchar(200)", "NOT NULL", "题库名称"),
| col("question_count", "int", "NOT NULL DEFAULT 0", "试题数量（维护时回写）"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'normal'", "normal正常 / invalid失效"),
| col("remark", "text", None, "备注"),
| ], indexes=[("idx", "idx_tms_qbank_status", ["biz_status"], False)])
|
| add("tms_question", "试题。修改不影响已完成考试快照。", [
| col("question_no", "varchar(50)", None, "试题编号，可流水"),
| col("bank_id", "varchar(50)", "NOT NULL", "所属题库 tms_question_bank.f_id"),
| col("question_type", "varchar(20)", "NOT NULL", "single单选 / multi多选 / judge判断 / blank填空 / essay问答"),
| col("difficulty", "varchar(20)", None, "easy/normal/hard，界面默认一般"),
| col("source_type", "varchar(50)", None, "试题来源，默认自命题"),
| col("stem", "text", "NOT NULL", "题干，富文本"),
| col("analysis", "text", None, "答案解析/备注，错题可显示"),
| col("admin_user_id", "varchar(50)", None, "管理员"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'open'", "open开放 / closed不开放 / invalid废弃"),
| ], indexes=[
| ("idx", "idx_tms_question_bank", ["bank_id", "question_type", "biz_status"], False),
| ])
|
| add("tms_question_option", "试题选项。单选单选中，多选可多选中，判断可做成A对B错。", [
| col("sort_no", "int", None, "序号"),
| col("option_label", "varchar(10)", None, "A/B/C/D..."),
| col("option_content", "text", None, "选项内容"),
| col("is_correct", "varchar(10)", "NOT NULL DEFAULT '0'", "是否正确答案 1是 0否"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_qopt_fid", ["f_foreign_id"], False)])
|
| add("tms_paper", "试卷。失效试卷不得再用于新任务；改卷不影响已考记录。", [
| col("paper_name", "varchar(300)", "NOT NULL", "试卷名称"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'open'", "open开放 / closed不开放 / invalid失效"),
| col("duration_min", "int", "NOT NULL DEFAULT 60", "考试时长（分钟）"),
| col("exam_start", "timestamp", None, "试卷级开考时间，可空，任务上再设"),
| col("exam_end", "timestamp", None, "试卷级结考时间，可空"),
| col("score_publish_time", "timestamp", None, "成绩公布时间，空=立即公布"),
| col("pass_score", "numeric(8,1)", None, "合格分数"),
| col("total_score", "numeric(8,1)", None, "试卷总分"),
| col("sort_mode", "varchar(20)", "NOT NULL DEFAULT 'paper'", "paper试卷自身顺序 / random随机"),
| col("has_subjective", "varchar(10)", "NOT NULL DEFAULT '0'", "是否含主观题，组卷时回写"),
| col("remark", "text", None, "备注"),
| ], indexes=[("idx", "idx_tms_paper_status", ["biz_status"], False)])
|
| add("tms_paper_section", "试卷章节，可按题型归类", [
| col("section_name", "varchar(100)", None, "章节名，如 一、单选题"),
| col("question_type", "varchar(20)", None, "默认题型"),
| col("sort_no", "int", None, "排序"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_psec_fid", ["f_foreign_id"], False)])
|
| add("tms_paper_question", "试卷组成试题（当前版本）。已考试卷走 tms_exam_item 快照。", [
| col("section_id", "varchar(50)", None, "章节ID"),
| col("question_id", "varchar(50)", "NOT NULL", "试题ID"),
| col("score", "numeric(8,1)", None, "本题分值"),
| col("sort_no", "int", None, "自定义排序"),
| ], child=True, delete_user=False, indexes=[
| ("idx", "idx_tms_pq_fid", ["f_foreign_id"], False),
| ("idx", "idx_tms_pq_qid", ["question_id"], False),
| ])
|
| # ===== 3 岗位/年度计划 =====
| add("tms_post_catalog", "岗位培训目录。支持升版、废弃。审批通过后可整单复制。", [
| col("catalog_no", "varchar(50)", None, "目录编号"),
| col("post_id", "varchar(50)", "NOT NULL", "岗位"),
| col("post_name", "varchar(100)", None, "岗位名称冗余"),
| col("dept_id", "varchar(50)", None, "所属部门"),
| col("version_no", "int", "NOT NULL DEFAULT 1", "版本号，升版+1"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'valid'", "valid有效 / revoked废弃"),
| col("remark", "varchar(500)", None, "备注"),
| ], flow=True, indexes=[
| ("idx", "idx_tms_pcat_post", ["post_id", "biz_status"], False),
| ])
|
| add("tms_post_catalog_item", "岗位培训目录内容行，支持回车多行、手动排序", [
| col("sort_no", "int", None, "顺序"),
| col("subject", "varchar(500)", None, "培训内容"),
| col("train_mode", "varchar(50)", None, "培训方式"),
| col("hours", "numeric(8,1)", None, "课时"),
| col("eval_mode", "varchar(50)", None, "考核方式"),
| col("material_id", "varchar(50)", None, "教材"),
| col("course_id", "varchar(50)", None, "课程"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_pcati_fid", ["f_foreign_id"], False)])
|
| add("tms_post_plan", "岗位培训计划。由目录推送，可生成个人岗位培训和培训任务。", [
| col("plan_no", "varchar(50)", "NOT NULL", "计划编号 JP+YYYYMM+4，如 JP2026090012"),
| col("post_id", "varchar(50)", None, "岗位编码/ID"),
| col("post_name", "varchar(100)", None, "岗位名称"),
| col("dept_id", "varchar(50)", None, "所属部门"),
| col("plan_end_time", "date", None, "计划结束时间，带入个人岗位培训"),
| col("post_desc", "text", None, "岗位描述"),
| col("trainees", "text", None, "培训对象，用户多选"),
| col("catalog_id", "varchar(50)", None, "来源目录"),
| col("publish_status", "varchar(20)", "NOT NULL DEFAULT 'draft'", "draft未发布 / published已发布"),
| col("remark", "varchar(500)", None, "备注"),
| ], flow=True, indexes=[
| ("uk", "uk_tms_pplan_no", ["plan_no"], True),
| ("idx", "idx_tms_pplan_post", ["post_id", "publish_status"], False),
| ])
|
| add("tms_post_plan_item", "岗位培训计划明细", [
| col("plan_time", "date", None, "培训计划时间"),
| col("teacher_id", "varchar(50)", None, "培训师"),
| col("subject", "varchar(500)", None, "培训内容"),
| col("train_mode", "varchar(50)", None, "培训方式"),
| col("hours", "numeric(8,1)", None, "培训课时"),
| col("eval_mode", "varchar(50)", None, "考核方式"),
| col("material_id", "varchar(50)", None, "教材"),
| col("task_id", "varchar(50)", None, "生成的培训任务ID"),
| col("line_status", "varchar(20)", None, "pending待发布 / published已发布 / done已完成"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_pplani_fid", ["f_foreign_id"], False)])
|
| add("tms_person_post", "个人岗位培训清单。转岗时子表标记已培训过则不生成任务。", [
| col("plan_id", "varchar(50)", None, "岗位培训计划ID"),
| col("catalog_id", "varchar(50)", None, "目录ID"),
| col("user_id", "varchar(50)", "NOT NULL", "培训对象"),
| col("post_id", "varchar(50)", None, "岗位"),
| col("plan_end_time", "date", None, "计划结束时间（由计划带出）"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'doing'", "doing执行中 / done完成 / returned退回"),
| col("person_remark", "varchar(500)", None, "个人备注"),
| col("audit_remark", "varchar(500)", None, "审核备注"),
| col("approve_remark", "varchar(500)", None, "审批备注"),
| ], flow=True, indexes=[
| ("idx", "idx_tms_ppost_user", ["user_id", "biz_status"], False),
| ("idx", "idx_tms_ppost_plan", ["plan_id"], False),
| ])
|
| add("tms_person_post_item", "个人岗位培训明细", [
| col("subject", "varchar(500)", None, "培训内容"),
| col("train_mode", "varchar(50)", None, "培训方式"),
| col("hours", "numeric(8,1)", None, "课时"),
| col("eval_mode", "varchar(50)", None, "考核方式"),
| col("already_trained", "varchar(10)", "NOT NULL DEFAULT '0'", "转岗：是否已培训过，是则不生成任务"),
| col("task_id", "varchar(50)", None, "生成的培训任务"),
| col("line_status", "varchar(20)", None, "状态"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_pposti_fid", ["f_foreign_id"], False)])
|
| add("tms_annual_plan", "年度培训计划（公司/部门）。办结后生成培训任务。流程：部门负责人→QA负责人。", [
| col("plan_year", "int", "NOT NULL", "年份，制定时默认当前年+1"),
| col("plan_no", "varchar(50)", "NOT NULL", "计划编号，如 20260001"),
| col("plan_type", "varchar(20)", None, "company公司级 / dept部门级"),
| col("author_id", "varchar(50)", None, "制定人"),
| col("author_dept_id", "varchar(50)", None, "制定部门"),
| col("author_date", "date", None, "制定日期"),
| col("version_no", "int", "NOT NULL DEFAULT 1", "版本"),
| col("dept_leader_id", "varchar(50)", None, "部门负责人（签核展示）"),
| col("qa_leader_id", "varchar(50)", None, "QA部负责人"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'draft'", "draft草稿 / approving审批中 / approved已批准"),
| col("remark", "text", None, "备注"),
| ], flow=True, indexes=[
| ("uk", "uk_tms_aplan_no", ["plan_no"], True),
| ("idx", "idx_tms_aplan_year", ["plan_year", "plan_type", "author_dept_id"], False),
| ])
|
| add("tms_annual_plan_item", "年度培训计划明细", [
| col("train_month", "varchar(6)", None, "培训月份 YYYYMM，如 202609"),
| col("subject", "varchar(500)", None, "培训内容"),
| col("train_mode", "varchar(50)", None, "培训方式"),
| col("trainees", "text", None, "培训对象"),
| col("hours", "numeric(8,1)", None, "培训课时"),
| col("teacher_id", "varchar(50)", None, "培训师"),
| col("eval_mode", "varchar(50)", None, "考核方式"),
| col("course_id", "varchar(50)", None, "培训课程"),
| col("line_status", "varchar(20)", "NOT NULL DEFAULT 'pending'", "pending待执行 / tasked已生成任务 / done已完成 / delayed已延期"),
| col("task_id", "varchar(50)", None, "生成的培训任务"),
| ], child=True, delete_user=False, indexes=[
| ("idx", "idx_tms_aplani_fid", ["f_foreign_id"], False),
| ("idx", "idx_tms_aplani_month", ["train_month"], False),
| ])
|
| add("tms_annual_delay", "年度培训计划延期申请", [
| col("plan_id", "varchar(50)", "NOT NULL", "年度计划ID"),
| col("item_id", "varchar(50)", None, "计划明细ID"),
| col("old_month", "varchar(6)", None, "原月份"),
| col("new_month", "varchar(6)", None, "延期至月份"),
| col("reason", "text", None, "延期原因"),
| col("biz_status", "varchar(20)", None, "审批状态"),
| ], flow=True, indexes=[("idx", "idx_tms_adelay_plan", ["plan_id"], False)])
|
| # ===== 4 培训任务 / 记录 =====
| add("tms_task", "培训任务（核心）。来源：岗位计划/年度计划/文件生效/临时/外出/再维护。", [
| col("task_no", "varchar(50)", "NOT NULL", "任务编号 TT+YYYYMM+4，如 TT2026090618"),
| col("category", "varchar(50)", None, "培训分类：临时培训/岗位培训计划/年度培训计划/文件生效培训/外出培训/再维护"),
| col("train_type", "varchar(50)", None, "培训类型（列表字段，可与分类并存）"),
| col("source_type", "varchar(50)", None, "来源类型编码"),
| col("source_plan_no", "varchar(50)", None, "来源计划编号"),
| col("source_id", "varchar(50)", None, "来源主表ID"),
| col("source_item_id", "varchar(50)", None, "来源明细ID，归档回写"),
| col("subject", "text", None, "培训主题"),
| col("key_points", "text", None, "培训要点，签到前强制阅读"),
| col("material_id", "varchar(50)", None, "培训教材"),
| col("material_no", "varchar(50)", None, "教材编号冗余"),
| col("place_id", "varchar(50)", None, "培训地点"),
| col("place_no", "varchar(50)", None, "地点编号冗余"),
| col("place_name", "varchar(200)", None, "地点名称冗余"),
| col("start_time", "timestamp", None, "培训开始时间"),
| col("end_time", "timestamp", None, "培训结束时间"),
| col("close_time", "timestamp", None, "任务关闭时间，到达后记录进待归档"),
| col("paper_id", "varchar(50)", None, "试卷"),
| col("paper_name", "varchar(300)", None, "试卷名称冗余"),
| col("exam_start", "timestamp", None, "考试开始时间"),
| col("exam_end", "timestamp", None, "考试结束时间"),
| col("teacher_id", "text", None, "培训师，可多选"),
| col("train_mode", "varchar(50)", None, "培训方式"),
| col("eval_mode", "varchar(50)", None, "培训效果评估方式/考核方式"),
| col("trainees", "text", None, "培训对象，用户多选；发布时拆个人任务"),
| col("task_kind", "varchar(20)", None, "任务类型：new新增 / continue继续(再维护)"),
| col("prev_task_no", "varchar(50)", None, "上一次培训任务编号"),
| col("publish_status", "varchar(20)", "NOT NULL DEFAULT 'draft'", "draft未发布 / published已发布 / cancelled已取消 / done已完成"),
| col("publish_time", "timestamp", None, "发布时间"),
| col("retake_limit", "int", "NOT NULL DEFAULT 1", "不合格可重考次数"),
| col("pass_score", "numeric(8,1)", None, "通过分数，空则用试卷"),
| col("quiz_ratio", "int", None, "提问比例0-100，自动算应提问人数"),
| col("sign_rate", "int", None, "签到率%，取整舍小数"),
| col("pass_rate", "int", None, "合格率%，取整舍小数"),
| col("unfinished_rate", "int", None, "未完成率%"),
| col("overdue_remind", "varchar(10)", "NOT NULL DEFAULT '0'", "是否逾期提醒部门领导"),
| col("invite_flag", "varchar(10)", "NOT NULL DEFAULT '0'", "是否发送培训邀请"),
| col("dms_file_id", "varchar(50)", None, "文件生效自动引入的附件"),
| col("remark", "varchar(500)", None, "备注"),
| ], flow=True, indexes=[
| ("uk", "uk_tms_task_no", ["task_no"], True),
| ("idx", "idx_tms_task_status", ["publish_status", "start_time"], False),
| ("idx", "idx_tms_task_place", ["place_id", "start_time", "end_time"], False),
| ("idx", "idx_tms_task_source", ["source_type", "source_id"], False),
| ("idx", "idx_tms_task_teacher", ["teacher_id"], False),
| ])
|
| add("tms_task_file", "培训任务教材/附件清单，发布后同步到培训记录", [
| col("file_no", "varchar(50)", None, "编号"),
| col("file_name", "varchar(300)", None, "文件名称"),
| col("material_id", "varchar(50)", None, "来源教材"),
| col("file_json", "text", None, "附件"),
| col("can_download", "varchar(10)", "NOT NULL DEFAULT '0'", "引用教材默认不可下载打印"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_tfile_fid", ["f_foreign_id"], False)])
|
| add("tms_task_quiz", "培训任务提问预设问题和答案，签到后弹出", [
| col("sort_no", "int", None, "序号"),
| col("question", "text", None, "问题"),
| col("answer", "text", None, "参考答案"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_tquiz_fid", ["f_foreign_id"], False)])
|
| add("tms_person_task", "个人培训任务。发布时按人拆行；学员端自学/签到/考试入口。", [
| col("task_id", "varchar(50)", "NOT NULL", "培训任务ID"),
| col("task_no", "varchar(50)", None, "任务编号冗余"),
| col("user_id", "varchar(50)", "NOT NULL", "培训对象"),
| col("dept_id", "varchar(50)", None, "当时部门快照"),
| col("hours", "numeric(8,1)", None, "学时"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'todo'", "todo未完成 / signed已签到 / done已完成 / expired已过期 / cancelled已取消"),
| col("sign_time", "timestamp", None, "签到时间"),
| col("sign_mode", "varchar(20)", None, "签到方式"),
| col("learn_seconds", "int", None, "在线学习时长秒"),
| col("exam_score", "numeric(8,1)", None, "考试成绩"),
| col("pass_flag", "varchar(20)", None, "pass合格 / fail不合格 / na无需考核"),
| col("guest_flag", "varchar(10)", "NOT NULL DEFAULT '0'", "是否名单外签到"),
| ], indexes=[
| ("idx", "idx_tms_ptask_user", ["user_id", "biz_status"], False),
| ("idx", "idx_tms_ptask_task", ["task_id"], False),
| ("uk", "uk_tms_ptask_task_user", ["task_id", "user_id"], True),
| ])
|
| add("tms_record", "培训记录。任务发布后生成；关闭时间到达进待归档。", [
| col("record_no", "varchar(50)", None, "记录编号，默认同任务编号"),
| col("task_id", "varchar(50)", "NOT NULL", "培训任务ID"),
| col("task_no", "varchar(50)", None, "任务编号"),
| col("category", "varchar(50)", None, "培训分类"),
| col("subject", "text", None, "培训主题"),
| col("teacher_id", "text", None, "培训师"),
| col("train_type", "varchar(50)", None, "培训类型"),
| col("train_mode", "varchar(50)", None, "培训方式"),
| col("eval_mode", "varchar(50)", None, "考核方式"),
| col("start_time", "timestamp", None, "培训开始时间，套打模板按此索引版本"),
| col("end_time", "timestamp", None, "培训结束时间"),
| col("close_time", "timestamp", None, "关闭时间"),
| col("archive_status", "varchar(20)", "NOT NULL DEFAULT 'pending'", "pending待归档 / ready可归档 / archived已归档 / invalid无效"),
| col("archive_time", "timestamp", None, "归档时间"),
| col("archiver_id", "varchar(50)", None, "归档人"),
| col("sign_rate", "int", None, "参与/签到率% 取整舍小数"),
| col("pass_rate", "int", None, "合格率%"),
| col("lecture_file", "text", None, "培训讲义附件"),
| col("paper_sign_file", "text", None, "纸质签到表附件"),
| col("print_tpl_id", "varchar(50)", None, "打印模板版本"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[
| ("uk", "uk_tms_record_task", ["task_id"], True),
| ("idx", "idx_tms_record_arch", ["archive_status", "start_time"], False),
| ])
|
| add("tms_record_sign", "培训记录签到/结果明细（一人一行）", [
| col("user_id", "varchar(50)", "NOT NULL", "培训对象"),
| col("user_name", "varchar(100)", None, "姓名快照"),
| col("dept_id", "varchar(50)", None, "部门快照"),
| col("sign_time", "timestamp", None, "签到时间"),
| col("sign_mode", "varchar(20)", None, "签到方式"),
| col("guest_flag", "varchar(10)", "NOT NULL DEFAULT '0'", "名单外签到"),
| col("result_flag", "varchar(20)", None, "pass合格 / fail不合格 / absent未参加 / na无需考核"),
| col("exam_score", "numeric(8,1)", None, "考试成绩"),
| col("practice_score", "numeric(8,1)", None, "实操成绩"),
| col("quiz_score", "numeric(8,1)", None, "提问成绩"),
| col("exam_id", "varchar(50)", None, "答卷ID"),
| col("judge_user_id", "varchar(50)", None, "结果判定人（培训师）"),
| col("judge_time", "timestamp", None, "判定时间"),
| ], child=True, delete_user=False, indexes=[
| ("idx", "idx_tms_rsign_fid", ["f_foreign_id"], False),
| ("idx", "idx_tms_rsign_user", ["user_id"], False),
| ])
|
| add("tms_record_change", "培训记录变更：改结束时间、增加培训人员。培训师发起→部门领导（领导即培训师则跳过）。", [
| col("record_id", "varchar(50)", "NOT NULL", "培训记录ID"),
| col("task_id", "varchar(50)", None, "任务ID"),
| col("change_type", "varchar(20)", None, "end_time改结束时间 / add_user增加人员"),
| col("old_end_time", "timestamp", None, "原结束时间"),
| col("new_end_time", "timestamp", None, "新结束时间"),
| col("add_users", "text", None, "新增人员"),
| col("reason", "text", None, "变更原因"),
| col("biz_status", "varchar(20)", None, "审批状态"),
| ], flow=True, indexes=[("idx", "idx_tms_rchg_rec", ["record_id"], False)])
|
| add("tms_archive_file", "培训资料归档（电子/纸质/实操记录双结合）", [
| col("record_id", "varchar(50)", "NOT NULL", "培训记录ID"),
| col("task_id", "varchar(50)", None, "任务ID"),
| col("archive_type", "varchar(20)", None, "electronic电子 / paper纸质 / practice实操档案"),
| col("file_json", "text", None, "归档资料"),
| col("archive_time", "timestamp", None, "归档时间"),
| col("archiver_id", "varchar(50)", None, "归档人"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[("idx", "idx_tms_afile_rec", ["record_id"], False)])
|
| # ===== 5 考试执行 =====
| add("tms_exam", "在线考试答卷。中途退出可续考；提交后客观题自动评分，有主观题进阅卷。", [
| col("task_id", "varchar(50)", "NOT NULL", "培训任务"),
| col("person_task_id", "varchar(50)", None, "个人任务"),
| col("record_id", "varchar(50)", None, "培训记录"),
| col("paper_id", "varchar(50)", None, "当时试卷ID"),
| col("paper_name", "varchar(300)", None, "试卷名称快照"),
| col("user_id", "varchar(50)", "NOT NULL", "考生"),
| col("attempt_no", "int", "NOT NULL DEFAULT 1", "第几次考试"),
| col("start_time", "timestamp", None, "开始作答"),
| col("submit_time", "timestamp", None, "交卷时间"),
| col("duration_min", "int", None, "当时时长快照"),
| col("objective_score", "numeric(8,1)", None, "客观题得分"),
| col("subjective_score", "numeric(8,1)", None, "主观题得分"),
| col("total_score", "numeric(8,1)", None, "总分"),
| col("pass_score", "numeric(8,1)", None, "当时合格分快照"),
| col("pass_flag", "varchar(20)", None, "pass/fail"),
| col("grade_status", "varchar(20)", "NOT NULL DEFAULT 'auto'", "auto无需阅卷 / pending待批改 / graded已批改"),
| col("grader_id", "varchar(50)", None, "阅卷老师"),
| col("grade_time", "timestamp", None, "阅卷时间"),
| col("exam_status", "varchar(20)", "NOT NULL DEFAULT 'doing'", "doing考试中 / submitted已交卷 / cancelled已取消"),
| ], indexes=[
| ("idx", "idx_tms_exam_task_user", ["task_id", "user_id", "attempt_no"], False),
| ("idx", "idx_tms_exam_grade", ["grade_status"], False),
| ("idx", "idx_tms_exam_user", ["user_id"], False),
| ])
|
| add("tms_exam_item", "答卷试题快照：题目/选项/答案/分数保留考试当时状态，后续改题改卷不影响。", [
| col("question_id", "varchar(50)", None, "原试题ID"),
| col("question_type", "varchar(20)", None, "题型快照"),
| col("stem", "text", None, "题干快照"),
| col("options_json", "text", None, "选项快照 JSON"),
| col("correct_answer", "text", None, "当时正确答案"),
| col("user_answer", "text", None, "考生作答"),
| col("score", "numeric(8,1)", None, "本题满分"),
| col("got_score", "numeric(8,1)", None, "得分"),
| col("is_subjective", "varchar(10)", "NOT NULL DEFAULT '0'", "是否主观题"),
| col("analysis", "text", None, "解析快照"),
| col("sort_no", "int", None, "试卷内顺序"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_eitem_fid", ["f_foreign_id"], False)])
|
| add("tms_practice", "实操考核主表，保存后回写培训记录", [
| col("task_id", "varchar(50)", "NOT NULL", "培训任务"),
| col("record_id", "varchar(50)", None, "培训记录"),
| col("teacher_id", "varchar(50)", None, "考核人"),
| col("practice_time", "timestamp", None, "考核时间"),
| col("remark", "varchar(500)", None, "备注"),
| ], indexes=[("idx", "idx_tms_prac_task", ["task_id"], False)])
|
| add("tms_practice_item", "实操考核明细", [
| col("user_id", "varchar(50)", "NOT NULL", "培训对象"),
| col("content", "text", None, "考核内容"),
| col("score", "numeric(8,1)", None, "分数"),
| col("result_flag", "varchar(20)", None, "合格/不合格"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_praci_fid", ["f_foreign_id"], False)])
|
| add("tms_quiz_score", "现场提问考核打分，保存后回写培训记录", [
| col("task_id", "varchar(50)", "NOT NULL", "培训任务"),
| col("record_id", "varchar(50)", None, "培训记录"),
| col("user_id", "varchar(50)", "NOT NULL", "培训对象"),
| col("question", "text", None, "提问内容"),
| col("answer", "text", None, "回答"),
| col("score", "numeric(8,1)", None, "分数"),
| col("teacher_id", "varchar(50)", None, "提问人"),
| col("quiz_time", "timestamp", None, "时间"),
| ], indexes=[
| ("idx", "idx_tms_qscore_task", ["task_id"], False),
| ("idx", "idx_tms_qscore_user", ["user_id"], False),
| ])
|
| # ===== 6 档案 =====
| add("tms_person_file", "个人培训档案。入职自动建档；一人一档。", [
| col("archive_no", "varchar(50)", "NOT NULL", "档案编号 AR+YYYY+4"),
| col("archive_date", "date", None, "建档时间"),
| col("user_id", "varchar(50)", "NOT NULL", "员工"),
| col("user_name", "varchar(100)", None, "姓名冗余"),
| col("dept_id", "varchar(50)", None, "所属部门"),
| col("hire_date", "date", None, "入职时间"),
| col("leave_date", "date", None, "离职时间"),
| col("main_post_id", "varchar(50)", None, "主要岗位"),
| col("part_post_id", "varchar(50)", None, "兼职岗位"),
| col("biz_status", "varchar(20)", "NOT NULL DEFAULT 'valid'", "valid在职 / left离职"),
| ], indexes=[
| ("uk", "uk_tms_pfile_user", ["user_id"], True),
| ("uk", "uk_tms_pfile_no", ["archive_no"], True),
| ])
|
| add("tms_person_work", "个人培训档案-工作履历", [
| col("company_name", "varchar(200)", None, "公司名称"),
| col("post_name", "varchar(100)", None, "任职岗位"),
| col("hire_date", "date", None, "入职时间"),
| col("leave_date", "date", None, "离职时间"),
| ], child=True, delete_user=False, indexes=[("idx", "idx_tms_pwork_fid", ["f_foreign_id"], False)])
|
| add("tms_person_cert", "资质文件/获得证书。资质文件管理与档案子表共用此表。", [
| col("archive_id", "varchar(50)", None, "个人档案ID，无外键字段时由主从写入f_foreign_id"),
| col("user_id", "varchar(50)", None, "员工，列表模块用"),
| col("cert_no", "varchar(100)", None, "证书编号"),
| col("cert_name", "varchar(200)", None, "证书名称"),
| col("gain_date", "date", None, "获得时间"),
| col("expire_date", "date", None, "有效期至"),
| col("cert_file", "text", None, "证书附件"),
| col("cert_status", "varchar(20)", None, "valid有效 / near近效期 / invalid无效"),
| ], child=True, indexes=[
| ("idx", "idx_tms_pcert_fid", ["f_foreign_id"], False),
| ("idx", "idx_tms_pcert_user", ["user_id"], False),
| ])
|
| add("tms_person_train", "个人培训目录（归档快照）。记录归档后写入，满足审计。", [
| col("archive_id", "varchar(50)", "NOT NULL", "个人档案ID"),
| col("user_id", "varchar(50)", "NOT NULL", "员工"),
| col("record_id", "varchar(50)", None, "培训记录ID"),
| col("record_no", "varchar(50)", None, "培训记录编号"),
| col("category", "varchar(50)", None, "培训类型/分类"),
| col("subject", "text", None, "培训内容"),
| col("train_mode", "varchar(50)", None, "培训方式"),
| col("teacher_id", "text", None, "培训师"),
| col("eval_mode", "varchar(50)", None, "考核方式"),
| col("pass_flag", "varchar(20)", None, "是否合格"),
| col("start_time", "timestamp", None, "培训开始时间"),
| col("end_time", "timestamp", None, "培训结束时间"),
| col("hours", "numeric(8,1)", None, "学时"),
| ], indexes=[
| ("idx", "idx_tms_ptrain_user", ["user_id"], False),
| ("idx", "idx_tms_ptrain_arch", ["archive_id"], False),
| ("idx", "idx_tms_ptrain_rec", ["record_id"], False),
| ])
|
| # ===== 7 其他流程 =====
| add("tms_out_train", "外出培训", [
| col("apply_no", "varchar(50)", None, "申请单号"),
| col("applicant_id", "varchar(50)", None, "申请人"),
| col("apply_dept_id", "varchar(50)", None, "申请部门"),
| col("org_name", "varchar(200)", None, "外部机构"),
| col("subject", "varchar(500)", None, "培训主题"),
| col("start_time", "timestamp", None, "开始"),
| col("end_time", "timestamp", None, "结束"),
| col("hours", "numeric(8,1)", None, "学时"),
| col("file_json", "text", None, "资料附件"),
| col("biz_status", "varchar(20)", None, "审批状态"),
| ], flow=True, indexes=[("uk", "uk_tms_out_no", ["apply_no"], True)])
|
| add("tms_transfer_eval", "转岗人员考核鉴定", [
| col("user_id", "varchar(50)", "NOT NULL", "转岗人员"),
| col("from_post_id", "varchar(50)", None, "原岗位"),
| col("to_post_id", "varchar(50)", None, "新岗位"),
| col("eval_result", "varchar(50)", None, "鉴定结论"),
| col("remark", "text", None, "说明"),
| col("biz_status", "varchar(20)", None, "审批状态"),
| ], flow=True, indexes=[("idx", "idx_tms_teval_user", ["user_id"], False)])
|
| add("tms_newcomer_eval", "新员工培训效果考评", [
| col("user_id", "varchar(50)", "NOT NULL", "新员工"),
| col("eval_result", "varchar(50)", None, "考评结论"),
| col("remark", "text", None, "说明"),
| col("biz_status", "varchar(20)", None, "审批状态"),
| ], flow=True, indexes=[("idx", "idx_tms_neval_user", ["user_id"], False)])
|
| add("tms_return_eval", "复岗员工培训效果考评", [
| col("user_id", "varchar(50)", "NOT NULL", "复岗员工"),
| col("eval_result", "varchar(50)", None, "考评结论"),
| col("remark", "text", None, "说明"),
| col("biz_status", "varchar(20)", None, "审批状态"),
| ], flow=True, indexes=[("idx", "idx_tms_reval_user", ["user_id"], False)])
|
| add("tms_print_template", "培训记录套打模板版本。按培训开始时间匹配生效版本。", [
| col("tpl_code", "varchar(50)", "NOT NULL", "模板编码，如 TRAIN_RECORD"),
| col("version_no", "int", "NOT NULL", "版本号"),
| col("effective_from", "date", "NOT NULL", "生效起始（按培训开始时间索引）"),
| col("effective_to", "date", None, "生效截止，空=当前"),
| col("file_json", "text", None, "模板文件"),
| col("enabled", "varchar(10)", "NOT NULL DEFAULT '1'", "启用"),
| ], indexes=[("idx", "idx_tms_ptpl_code", ["tpl_code", "effective_from"], False)])
|
| add("tms_remind_rule", "提醒规则：计划到期前一个月、授权到期、个人岗位培训时限等", [
| col("rule_code", "varchar(50)", "NOT NULL", "规则编码"),
| col("rule_name", "varchar(100)", None, "名称"),
| col("biz_type", "varchar(50)", None, "annual_plan/admin_auth/person_post/task_overdue"),
| col("days_before", "int", "NOT NULL DEFAULT 30", "提前天数"),
| col("notify_roles", "varchar(200)", None, "通知角色：部门培训管理员,培训师,部门领导"),
| col("enabled", "varchar(10)", "NOT NULL DEFAULT '1'", "启用"),
| ], indexes=[("uk", "uk_tms_remind_code", ["rule_code"], True)])
|
|
| HEADER = '''-- =============================================================================
| -- 玻思韬 TMS 培训管理系统 · PostgreSQL 18 建表脚本
| -- 适用：JNPF 6.x（字段风格对齐现有 lims_/dms_ 表）
| -- 来源：玻思韬TMS功能设计书 V1 + 老飞企界面字段
| --
| -- 使用
| -- 1) 推荐：JNPF「数据建模」按表清单建表，再绑表单（系统字段会自动带）
| -- 2) 或先执行本脚本，再到数据建模「同步数据表」
| --
| -- 约定
| -- f_id 主键 varchar(50)；子表 f_foreign_id = 主表 f_id
| -- 流程表单含 f_flow_id / f_flow_task_id / f_flow_state
| -- 用户/组织/岗位存 JNPF ID；多选 text；附件 text(JSON)
| -- 业务状态存编码，选项用 JNPF 字典
| -- =============================================================================
|
| BEGIN;
| '''
|
| FOOTER = '''
| -- =============================================================================
| -- 预置数据（培训方式 / 签到方式 / 评估方式 / 提醒规则）
| -- =============================================================================
| INSERT INTO tms_train_mode (f_id, mode_code, mode_name, sign_rule, allow_guest_sign, same_day_required, place_required, sort_no, enabled, f_delete_mark)
| VALUES
| ('tms_mode_onsite', 'onsite', '集中授课', 'scan', '1', '1', '1', 1, '1', 0),
| ('tms_mode_practice', 'practice', '操作授课', 'scan', '0', '1', '1', 2, '1', 0),
| ('tms_mode_online', 'online', '在线学习', 'both', '0', '0', '0', 3, '1', 0)
| ON CONFLICT (f_id) DO NOTHING;
|
| INSERT INTO tms_sign_mode (f_id, mode_code, mode_name, tip_seconds, match_roster, enabled, f_delete_mark)
| VALUES
| ('tms_sign_app', 'app_scan', 'APP扫码', 30, '1', '1', 0),
| ('tms_sign_wx', 'wx_scan', '微信扫码', 30, '1', '1', 0),
| ('tms_sign_online', 'online', '在线签到', 30, '1', '1', 0)
| ON CONFLICT (f_id) DO NOTHING;
|
| INSERT INTO tms_eval_mode (f_id, mode_code, mode_name, need_paper, need_quiz, need_practice, enabled, f_delete_mark)
| VALUES
| ('tms_eval_quiz', 'quiz', '提问', '0', '1', '0', '1', 0),
| ('tms_eval_practice', 'practice', '现场操作', '0', '0', '1', '1', 0),
| ('tms_eval_exam', 'exam', '在线考试', '1', '0', '0', '1', 0),
| ('tms_eval_none', 'none', '无需考核', '0', '0', '0', '1', 0)
| ON CONFLICT (f_id) DO NOTHING;
|
| INSERT INTO tms_remind_rule (f_id, rule_code, rule_name, biz_type, days_before, notify_roles, enabled, f_delete_mark)
| VALUES
| ('tms_rm_annual', 'annual_due', '年度计划到期前一个月', 'annual_plan', 30, '部门培训管理员,培训师', '1', 0),
| ('tms_rm_auth', 'auth_due', '管理员授权到期前一个月', 'admin_auth', 30, '培训管理员,培训师', '1', 0),
| ('tms_rm_overdue','task_overdue','培训逾期提醒部门领导', 'task_overdue', 0, '部门领导', '1', 0)
| ON CONFLICT (f_id) DO NOTHING;
|
| COMMIT;
| '''
|
| def sql_type_line(c):
| name, typ, extra, _ = c
| extra_s = f" {extra}" if extra else ""
| return f" {name:<24} {typ}{extra_s}"
|
| def emit_table(t):
| lines = []
| lines.append(f"-- {t['comment']}")
| lines.append(f"CREATE TABLE IF NOT EXISTS {t['name']} (")
| col_lines = []
| for c in t["cols"]:
| col_lines.append(sql_type_line(c))
| col_lines.append(f" CONSTRAINT pk_{t['name']} PRIMARY KEY (f_id)")
| lines.append(",\n".join(col_lines))
| lines.append(");")
| lines.append(f"COMMENT ON TABLE {t['name']} IS '{t['comment'].replace(chr(39), chr(39)*2)}';")
| for c in t["cols"]:
| if c[3]:
| cmt = c[3].replace("'", "''")
| lines.append(f"COMMENT ON COLUMN {t['name']}.{c[0]} IS '{cmt}';")
| for kind, iname, cols, uniqueish in t["indexes"]:
| colsql = ", ".join(cols)
| if kind == "uk":
| # unique among not-deleted; nullable columns allowed multiple NULL in PG unique, extra WHERE keeps it simple
| lines.append(f"CREATE UNIQUE INDEX IF NOT EXISTS {iname} ON {t['name']} ({colsql}) WHERE f_delete_mark = 0;")
| else:
| lines.append(f"CREATE INDEX IF NOT EXISTS {iname} ON {t['name']} ({colsql});")
| lines.append("")
| return "\n".join(lines)
|
| out = [HEADER]
| groups = [
| ("1. 基础设置", ["tms_place","tms_trainer","tms_trainer_work","tms_trainer_exp","tms_admin_auth","tms_train_mode","tms_sign_mode","tms_eval_mode","tms_course","tms_material"]),
| ("2. 题库试卷", ["tms_question_bank","tms_question","tms_question_option","tms_paper","tms_paper_section","tms_paper_question"]),
| ("3. 岗位/年度计划", ["tms_post_catalog","tms_post_catalog_item","tms_post_plan","tms_post_plan_item","tms_person_post","tms_person_post_item","tms_annual_plan","tms_annual_plan_item","tms_annual_delay"]),
| ("4. 培训任务与记录", ["tms_task","tms_task_file","tms_task_quiz","tms_person_task","tms_record","tms_record_sign","tms_record_change","tms_archive_file"]),
| ("5. 考试与考核", ["tms_exam","tms_exam_item","tms_practice","tms_practice_item","tms_quiz_score"]),
| ("6. 个人档案", ["tms_person_file","tms_person_work","tms_person_cert","tms_person_train"]),
| ("7. 其他流程与配置", ["tms_out_train","tms_transfer_eval","tms_newcomer_eval","tms_return_eval","tms_print_template","tms_remind_rule"]),
| ]
| by_name = {t["name"]: t for t in tables}
| for title, names in groups:
| out.append(f"-- ---------------------------------------------------------------------------")
| out.append(f"-- {title}")
| out.append(f"-- ---------------------------------------------------------------------------")
| for n in names:
| out.append(emit_table(by_name[n]))
| out.append(FOOTER)
|
| sql_path = Path(r"D:/qiansheng/demo/flowApi/docs/tms/tms_schema_pg.sql")
| sql_path.write_text("\n".join(out), encoding="utf-8")
| print("tables", len(tables), "bytes", sql_path.stat().st_size)
|
|
