# -*- 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)