-- ============================================================================= -- 玻思韬 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; -- --------------------------------------------------------------------------- -- 1. 基础设置 -- --------------------------------------------------------------------------- -- 培训地点。在线学习任务地点可空；占用按小时与未取消任务时间交叉校验。 CREATE TABLE IF NOT EXISTS tms_place ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), place_no varchar(50) NOT NULL, place_name varchar(200) NOT NULL, place_group varchar(50), enabled varchar(20) NOT NULL DEFAULT '1', remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_place PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_place IS '培训地点。在线学习任务地点可空；占用按小时与未取消任务时间交叉校验。'; COMMENT ON COLUMN tms_place.f_id IS '主键'; COMMENT ON COLUMN tms_place.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_place.place_no IS '地点编号，单据规则 A+YYYYMM+4，如 A2026090001'; COMMENT ON COLUMN tms_place.place_name IS '地点名称'; COMMENT ON COLUMN tms_place.place_group IS '组别/分区，字典：办公区/生产区/A区/B区'; COMMENT ON COLUMN tms_place.enabled IS '1启用 0停用'; COMMENT ON COLUMN tms_place.remark IS '备注'; COMMENT ON COLUMN tms_place.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_place.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_place.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_place.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_place.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_place.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_place.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_place_no ON tms_place (place_no) WHERE f_delete_mark = 0; CREATE INDEX IF NOT EXISTS idx_tms_place_group ON tms_place (place_group, enabled); -- 培训师档案，关联系统用户；重复录入按user_id识别；账号注销后自动废弃。 CREATE TABLE IF NOT EXISTS tms_trainer ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), archive_no varchar(50), user_id varchar(50) NOT NULL, trainer_name varchar(100), dept_id varchar(50), post_id varchar(50), school varchar(200), education varchar(50), major varchar(100), tech_title varchar(100), hire_date date, license varchar(200), work_years varchar(50), trainer_level varchar(50), biz_status varchar(20) NOT NULL DEFAULT 'valid', remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_trainer PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_trainer IS '培训师档案，关联系统用户；重复录入按user_id识别；账号注销后自动废弃。'; COMMENT ON COLUMN tms_trainer.f_id IS '主键'; COMMENT ON COLUMN tms_trainer.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_trainer.archive_no IS '档案编号 AR+YYYY+4，可与个人培训档案共用'; COMMENT ON COLUMN tms_trainer.user_id IS '系统用户ID'; COMMENT ON COLUMN tms_trainer.trainer_name IS '姓名（冗余，便于列表）'; COMMENT ON COLUMN tms_trainer.dept_id IS '部门'; COMMENT ON COLUMN tms_trainer.post_id IS '岗位'; COMMENT ON COLUMN tms_trainer.school IS '毕业院校'; COMMENT ON COLUMN tms_trainer.education IS '学历'; COMMENT ON COLUMN tms_trainer.major IS '专业'; COMMENT ON COLUMN tms_trainer.tech_title IS '技术职称'; COMMENT ON COLUMN tms_trainer.hire_date IS '入职时间'; COMMENT ON COLUMN tms_trainer.license IS '执业证书'; COMMENT ON COLUMN tms_trainer.work_years IS '工作年限'; COMMENT ON COLUMN tms_trainer.trainer_level IS '培训师级别'; COMMENT ON COLUMN tms_trainer.biz_status IS 'valid有效期内 / revoked已撤销'; COMMENT ON COLUMN tms_trainer.remark IS '备注'; COMMENT ON COLUMN tms_trainer.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_trainer.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_trainer.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_trainer.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_trainer.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_trainer.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_trainer.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_trainer_user ON tms_trainer (user_id) WHERE f_delete_mark = 0; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_trainer_no ON tms_trainer (archive_no) WHERE f_delete_mark = 0; CREATE INDEX IF NOT EXISTS idx_tms_trainer_status ON tms_trainer (biz_status); -- 培训师个人工作经历子表 CREATE TABLE IF NOT EXISTS tms_trainer_work ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, sort_no int, hire_date date, leave_date date, company_name varchar(200), post_name varchar(100), leave_reason varchar(200), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_trainer_work PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_trainer_work IS '培训师个人工作经历子表'; COMMENT ON COLUMN tms_trainer_work.f_id IS '主键'; COMMENT ON COLUMN tms_trainer_work.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_trainer_work.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_trainer_work.sort_no IS '序号'; COMMENT ON COLUMN tms_trainer_work.hire_date IS '入职时间'; COMMENT ON COLUMN tms_trainer_work.leave_date IS '离职时间'; COMMENT ON COLUMN tms_trainer_work.company_name IS '公司名称'; COMMENT ON COLUMN tms_trainer_work.post_name IS '任职岗位'; COMMENT ON COLUMN tms_trainer_work.leave_reason IS '离职原因'; COMMENT ON COLUMN tms_trainer_work.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_trainer_work.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_trainer_work.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_trainer_work.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_trainer_work.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_trainer_work_fid ON tms_trainer_work (f_foreign_id); -- 培训师个人培训经历子表 CREATE TABLE IF NOT EXISTS tms_trainer_exp ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, sort_no int, train_date date, org_name varchar(200), cert_name varchar(200), cert_file text, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_trainer_exp PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_trainer_exp IS '培训师个人培训经历子表'; COMMENT ON COLUMN tms_trainer_exp.f_id IS '主键'; COMMENT ON COLUMN tms_trainer_exp.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_trainer_exp.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_trainer_exp.sort_no IS '序号'; COMMENT ON COLUMN tms_trainer_exp.train_date IS '培训时间'; COMMENT ON COLUMN tms_trainer_exp.org_name IS '培训机构'; COMMENT ON COLUMN tms_trainer_exp.cert_name IS '培训证书'; COMMENT ON COLUMN tms_trainer_exp.cert_file IS '证书附件'; COMMENT ON COLUMN tms_trainer_exp.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_trainer_exp.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_trainer_exp.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_trainer_exp.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_trainer_exp.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_trainer_exp_fid ON tms_trainer_exp (f_foreign_id); -- 培训管理员跨部门授权。到期前一个月提醒管理员/培训师。 CREATE TABLE IF NOT EXISTS tms_admin_auth ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), admin_user_id varchar(50) NOT NULL, scope_dept_id text NOT NULL, include_child varchar(10) NOT NULL DEFAULT '1', registrar_id varchar(50), register_date date, expire_date date, biz_status varchar(20) NOT NULL DEFAULT 'valid', remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_admin_auth PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_admin_auth IS '培训管理员跨部门授权。到期前一个月提醒管理员/培训师。'; COMMENT ON COLUMN tms_admin_auth.f_id IS '主键'; COMMENT ON COLUMN tms_admin_auth.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_admin_auth.admin_user_id IS '培训管理员'; COMMENT ON COLUMN tms_admin_auth.scope_dept_id IS '管理范围，组织多选'; COMMENT ON COLUMN tms_admin_auth.include_child IS '是否包含下级部门 1是 0否'; COMMENT ON COLUMN tms_admin_auth.registrar_id IS '登记人'; COMMENT ON COLUMN tms_admin_auth.register_date IS '登记日期'; COMMENT ON COLUMN tms_admin_auth.expire_date IS '授权到期日，可空表示长期'; COMMENT ON COLUMN tms_admin_auth.biz_status IS 'valid有效 / revoked废弃'; COMMENT ON COLUMN tms_admin_auth.remark IS '备注'; COMMENT ON COLUMN tms_admin_auth.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_admin_auth.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_admin_auth.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_admin_auth.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_admin_auth.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_admin_auth.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_admin_auth.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_admin_auth_user ON tms_admin_auth (admin_user_id, biz_status); -- 培训方式配置（含签到规则，不仅是字典） CREATE TABLE IF NOT EXISTS tms_train_mode ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), mode_code varchar(50) NOT NULL, mode_name varchar(100) NOT NULL, sign_rule varchar(20) NOT NULL DEFAULT 'both', allow_guest_sign varchar(10) NOT NULL DEFAULT '0', same_day_required varchar(10) NOT NULL DEFAULT '0', place_required varchar(10) NOT NULL DEFAULT '1', sort_no int, enabled varchar(10) NOT NULL DEFAULT '1', remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_train_mode PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_train_mode IS '培训方式配置（含签到规则，不仅是字典）'; COMMENT ON COLUMN tms_train_mode.f_id IS '主键'; COMMENT ON COLUMN tms_train_mode.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_train_mode.mode_code IS '编码：onsite/practice/online'; COMMENT ON COLUMN tms_train_mode.mode_name IS '名称：集中授课/操作授课/在线学习'; COMMENT ON COLUMN tms_train_mode.sign_rule IS 'scan仅扫码 / both扫码+在线'; COMMENT ON COLUMN tms_train_mode.allow_guest_sign IS '允许名单外签到，集中授课=1'; COMMENT ON COLUMN tms_train_mode.same_day_required IS '开始结束必须同一天，集中/操作=1'; COMMENT ON COLUMN tms_train_mode.place_required IS '地点必填，在线学习=0'; COMMENT ON COLUMN tms_train_mode.sort_no IS '排序'; COMMENT ON COLUMN tms_train_mode.enabled IS '启用'; COMMENT ON COLUMN tms_train_mode.remark IS '备注'; COMMENT ON COLUMN tms_train_mode.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_train_mode.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_train_mode.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_train_mode.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_train_mode.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_train_mode.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_train_mode.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_train_mode_code ON tms_train_mode (mode_code) WHERE f_delete_mark = 0; -- 签到方式：APP扫码 / 微信扫码 / 在线签到 CREATE TABLE IF NOT EXISTS tms_sign_mode ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), mode_code varchar(50) NOT NULL, mode_name varchar(100) NOT NULL, tip_seconds int NOT NULL DEFAULT 30, match_roster varchar(10) NOT NULL DEFAULT '1', enabled varchar(10) NOT NULL DEFAULT '1', remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_sign_mode PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_sign_mode IS '签到方式：APP扫码 / 微信扫码 / 在线签到'; COMMENT ON COLUMN tms_sign_mode.f_id IS '主键'; COMMENT ON COLUMN tms_sign_mode.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_sign_mode.mode_code IS '编码：app_scan/wx_scan/online'; COMMENT ON COLUMN tms_sign_mode.mode_name IS '名称'; COMMENT ON COLUMN tms_sign_mode.tip_seconds IS '有培训要点时强制阅读秒数，IT可调'; COMMENT ON COLUMN tms_sign_mode.match_roster IS '是否必须匹配培训对象名单'; COMMENT ON COLUMN tms_sign_mode.enabled IS '启用'; COMMENT ON COLUMN tms_sign_mode.remark IS '备注'; COMMENT ON COLUMN tms_sign_mode.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_sign_mode.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_sign_mode.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_sign_mode.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_sign_mode.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_sign_mode.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_sign_mode.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_sign_mode_code ON tms_sign_mode (mode_code) WHERE f_delete_mark = 0; -- 培训效果评估方式：提问 / 现场操作 / 在线考试 / 无需考核 CREATE TABLE IF NOT EXISTS tms_eval_mode ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), mode_code varchar(50) NOT NULL, mode_name varchar(100) NOT NULL, need_paper varchar(10) NOT NULL DEFAULT '0', need_quiz varchar(10) NOT NULL DEFAULT '0', need_practice varchar(10) NOT NULL DEFAULT '0', enabled varchar(10) NOT NULL DEFAULT '1', remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_eval_mode PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_eval_mode IS '培训效果评估方式：提问 / 现场操作 / 在线考试 / 无需考核'; COMMENT ON COLUMN tms_eval_mode.f_id IS '主键'; COMMENT ON COLUMN tms_eval_mode.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_eval_mode.mode_code IS '编码：quiz/practice/exam/none'; COMMENT ON COLUMN tms_eval_mode.mode_name IS '名称'; COMMENT ON COLUMN tms_eval_mode.need_paper IS '是否需要试卷'; COMMENT ON COLUMN tms_eval_mode.need_quiz IS '是否需要提问预设'; COMMENT ON COLUMN tms_eval_mode.need_practice IS '是否需要实操评分'; COMMENT ON COLUMN tms_eval_mode.enabled IS '启用'; COMMENT ON COLUMN tms_eval_mode.remark IS '备注'; COMMENT ON COLUMN tms_eval_mode.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_eval_mode.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_eval_mode.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_eval_mode.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_eval_mode.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_eval_mode.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_eval_mode.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_eval_mode_code ON tms_eval_mode (mode_code) WHERE f_delete_mark = 0; -- 培训课程主数据，计划/任务可引用 CREATE TABLE IF NOT EXISTS tms_course ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), course_no varchar(50), course_name varchar(200) NOT NULL, category varchar(50), train_mode varchar(50), eval_mode varchar(50), paper_id varchar(50), hours numeric(8,1), enabled varchar(10) NOT NULL DEFAULT '1', remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_course PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_course IS '培训课程主数据，计划/任务可引用'; COMMENT ON COLUMN tms_course.f_id IS '主键'; COMMENT ON COLUMN tms_course.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_course.course_no IS '课程编号'; COMMENT ON COLUMN tms_course.course_name IS '课程名称'; COMMENT ON COLUMN tms_course.category IS '课程分类'; COMMENT ON COLUMN tms_course.train_mode IS '培训方式编码'; COMMENT ON COLUMN tms_course.eval_mode IS '考核方式编码'; COMMENT ON COLUMN tms_course.paper_id IS '关联试卷'; COMMENT ON COLUMN tms_course.hours IS '学时'; COMMENT ON COLUMN tms_course.enabled IS '启用'; COMMENT ON COLUMN tms_course.remark IS '备注'; COMMENT ON COLUMN tms_course.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_course.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_course.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_course.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_course.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_course.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_course.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_course_no ON tms_course (course_no) WHERE f_delete_mark = 0; -- 培训教材/企业知识库。可上传本地或引用DMS（需鉴权）。撤销后不可再引用。 CREATE TABLE IF NOT EXISTS tms_material ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), material_no varchar(50) NOT NULL, material_name varchar(500) NOT NULL, file_type varchar(50), apply_time date, applicant_id varchar(50), apply_dept_id varchar(50), file_json text, dms_file_id varchar(50), quote_count int NOT NULL DEFAULT 0, biz_status varchar(20) NOT NULL DEFAULT 'valid', remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_material PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_material IS '培训教材/企业知识库。可上传本地或引用DMS（需鉴权）。撤销后不可再引用。'; COMMENT ON COLUMN tms_material.f_id IS '主键'; COMMENT ON COLUMN tms_material.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_material.material_no IS '教材编号 TM+YYYYMM+4'; COMMENT ON COLUMN tms_material.material_name IS '教材名称'; COMMENT ON COLUMN tms_material.file_type IS 'Word/PPT/Excel/PDF/视频/音频'; COMMENT ON COLUMN tms_material.apply_time IS '申请时间'; COMMENT ON COLUMN tms_material.applicant_id IS '上传申请人'; COMMENT ON COLUMN tms_material.apply_dept_id IS '申请人部门'; COMMENT ON COLUMN tms_material.file_json IS '教材附件（JNPF附件JSON）'; COMMENT ON COLUMN tms_material.dms_file_id IS '引用的DMS文件ID'; COMMENT ON COLUMN tms_material.quote_count IS '被任务引用次数'; COMMENT ON COLUMN tms_material.biz_status IS 'valid有效 / revoked已撤销'; COMMENT ON COLUMN tms_material.remark IS '备注'; COMMENT ON COLUMN tms_material.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_material.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_material.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_material.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_material.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_material.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_material.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_material_no ON tms_material (material_no) WHERE f_delete_mark = 0; CREATE INDEX IF NOT EXISTS idx_tms_material_type ON tms_material (file_type, biz_status); -- --------------------------------------------------------------------------- -- 2. 题库试卷 -- --------------------------------------------------------------------------- -- 题库 CREATE TABLE IF NOT EXISTS tms_question_bank ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), bank_name varchar(200) NOT NULL, question_count int NOT NULL DEFAULT 0, biz_status varchar(20) NOT NULL DEFAULT 'normal', remark text, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_question_bank PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_question_bank IS '题库'; COMMENT ON COLUMN tms_question_bank.f_id IS '主键'; COMMENT ON COLUMN tms_question_bank.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_question_bank.bank_name IS '题库名称'; COMMENT ON COLUMN tms_question_bank.question_count IS '试题数量（维护时回写）'; COMMENT ON COLUMN tms_question_bank.biz_status IS 'normal正常 / invalid失效'; COMMENT ON COLUMN tms_question_bank.remark IS '备注'; COMMENT ON COLUMN tms_question_bank.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_question_bank.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_question_bank.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_question_bank.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_question_bank.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_question_bank.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_question_bank.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_qbank_status ON tms_question_bank (biz_status); -- 试题。修改不影响已完成考试快照。 CREATE TABLE IF NOT EXISTS tms_question ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), question_no varchar(50), bank_id varchar(50) NOT NULL, question_type varchar(20) NOT NULL, difficulty varchar(20), source_type varchar(50), stem text NOT NULL, analysis text, admin_user_id varchar(50), biz_status varchar(20) NOT NULL DEFAULT 'open', f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_question PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_question IS '试题。修改不影响已完成考试快照。'; COMMENT ON COLUMN tms_question.f_id IS '主键'; COMMENT ON COLUMN tms_question.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_question.question_no IS '试题编号，可流水'; COMMENT ON COLUMN tms_question.bank_id IS '所属题库 tms_question_bank.f_id'; COMMENT ON COLUMN tms_question.question_type IS 'single单选 / multi多选 / judge判断 / blank填空 / essay问答'; COMMENT ON COLUMN tms_question.difficulty IS 'easy/normal/hard，界面默认一般'; COMMENT ON COLUMN tms_question.source_type IS '试题来源，默认自命题'; COMMENT ON COLUMN tms_question.stem IS '题干，富文本'; COMMENT ON COLUMN tms_question.analysis IS '答案解析/备注，错题可显示'; COMMENT ON COLUMN tms_question.admin_user_id IS '管理员'; COMMENT ON COLUMN tms_question.biz_status IS 'open开放 / closed不开放 / invalid废弃'; COMMENT ON COLUMN tms_question.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_question.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_question.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_question.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_question.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_question.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_question.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_question_bank ON tms_question (bank_id, question_type, biz_status); -- 试题选项。单选单选中，多选可多选中，判断可做成A对B错。 CREATE TABLE IF NOT EXISTS tms_question_option ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, sort_no int, option_label varchar(10), option_content text, is_correct varchar(10) NOT NULL DEFAULT '0', f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_question_option PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_question_option IS '试题选项。单选单选中，多选可多选中，判断可做成A对B错。'; COMMENT ON COLUMN tms_question_option.f_id IS '主键'; COMMENT ON COLUMN tms_question_option.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_question_option.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_question_option.sort_no IS '序号'; COMMENT ON COLUMN tms_question_option.option_label IS 'A/B/C/D...'; COMMENT ON COLUMN tms_question_option.option_content IS '选项内容'; COMMENT ON COLUMN tms_question_option.is_correct IS '是否正确答案 1是 0否'; COMMENT ON COLUMN tms_question_option.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_question_option.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_question_option.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_question_option.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_question_option.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_qopt_fid ON tms_question_option (f_foreign_id); -- 试卷。失效试卷不得再用于新任务；改卷不影响已考记录。 CREATE TABLE IF NOT EXISTS tms_paper ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), paper_name varchar(300) NOT NULL, biz_status varchar(20) NOT NULL DEFAULT 'open', duration_min int NOT NULL DEFAULT 60, exam_start timestamp, exam_end timestamp, score_publish_time timestamp, pass_score numeric(8,1), total_score numeric(8,1), sort_mode varchar(20) NOT NULL DEFAULT 'paper', has_subjective varchar(10) NOT NULL DEFAULT '0', remark text, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_paper PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_paper IS '试卷。失效试卷不得再用于新任务；改卷不影响已考记录。'; COMMENT ON COLUMN tms_paper.f_id IS '主键'; COMMENT ON COLUMN tms_paper.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_paper.paper_name IS '试卷名称'; COMMENT ON COLUMN tms_paper.biz_status IS 'open开放 / closed不开放 / invalid失效'; COMMENT ON COLUMN tms_paper.duration_min IS '考试时长（分钟）'; COMMENT ON COLUMN tms_paper.exam_start IS '试卷级开考时间，可空，任务上再设'; COMMENT ON COLUMN tms_paper.exam_end IS '试卷级结考时间，可空'; COMMENT ON COLUMN tms_paper.score_publish_time IS '成绩公布时间，空=立即公布'; COMMENT ON COLUMN tms_paper.pass_score IS '合格分数'; COMMENT ON COLUMN tms_paper.total_score IS '试卷总分'; COMMENT ON COLUMN tms_paper.sort_mode IS 'paper试卷自身顺序 / random随机'; COMMENT ON COLUMN tms_paper.has_subjective IS '是否含主观题，组卷时回写'; COMMENT ON COLUMN tms_paper.remark IS '备注'; COMMENT ON COLUMN tms_paper.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_paper.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_paper.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_paper.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_paper.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_paper.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_paper.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_paper_status ON tms_paper (biz_status); -- 试卷章节，可按题型归类 CREATE TABLE IF NOT EXISTS tms_paper_section ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, section_name varchar(100), question_type varchar(20), sort_no int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_paper_section PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_paper_section IS '试卷章节，可按题型归类'; COMMENT ON COLUMN tms_paper_section.f_id IS '主键'; COMMENT ON COLUMN tms_paper_section.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_paper_section.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_paper_section.section_name IS '章节名，如 一、单选题'; COMMENT ON COLUMN tms_paper_section.question_type IS '默认题型'; COMMENT ON COLUMN tms_paper_section.sort_no IS '排序'; COMMENT ON COLUMN tms_paper_section.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_paper_section.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_paper_section.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_paper_section.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_paper_section.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_psec_fid ON tms_paper_section (f_foreign_id); -- 试卷组成试题（当前版本）。已考试卷走 tms_exam_item 快照。 CREATE TABLE IF NOT EXISTS tms_paper_question ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, section_id varchar(50), question_id varchar(50) NOT NULL, score numeric(8,1), sort_no int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_paper_question PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_paper_question IS '试卷组成试题（当前版本）。已考试卷走 tms_exam_item 快照。'; COMMENT ON COLUMN tms_paper_question.f_id IS '主键'; COMMENT ON COLUMN tms_paper_question.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_paper_question.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_paper_question.section_id IS '章节ID'; COMMENT ON COLUMN tms_paper_question.question_id IS '试题ID'; COMMENT ON COLUMN tms_paper_question.score IS '本题分值'; COMMENT ON COLUMN tms_paper_question.sort_no IS '自定义排序'; COMMENT ON COLUMN tms_paper_question.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_paper_question.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_paper_question.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_paper_question.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_paper_question.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_pq_fid ON tms_paper_question (f_foreign_id); CREATE INDEX IF NOT EXISTS idx_tms_pq_qid ON tms_paper_question (question_id); -- --------------------------------------------------------------------------- -- 3. 岗位/年度计划 -- --------------------------------------------------------------------------- -- 岗位培训目录。支持升版、废弃。审批通过后可整单复制。 CREATE TABLE IF NOT EXISTS tms_post_catalog ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), catalog_no varchar(50), post_id varchar(50) NOT NULL, post_name varchar(100), dept_id varchar(50), version_no int NOT NULL DEFAULT 1, biz_status varchar(20) NOT NULL DEFAULT 'valid', remark varchar(500), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_post_catalog PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_post_catalog IS '岗位培训目录。支持升版、废弃。审批通过后可整单复制。'; COMMENT ON COLUMN tms_post_catalog.f_id IS '主键'; COMMENT ON COLUMN tms_post_catalog.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_post_catalog.catalog_no IS '目录编号'; COMMENT ON COLUMN tms_post_catalog.post_id IS '岗位'; COMMENT ON COLUMN tms_post_catalog.post_name IS '岗位名称冗余'; COMMENT ON COLUMN tms_post_catalog.dept_id IS '所属部门'; COMMENT ON COLUMN tms_post_catalog.version_no IS '版本号，升版+1'; COMMENT ON COLUMN tms_post_catalog.biz_status IS 'valid有效 / revoked废弃'; COMMENT ON COLUMN tms_post_catalog.remark IS '备注'; COMMENT ON COLUMN tms_post_catalog.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_post_catalog.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_post_catalog.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_post_catalog.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_post_catalog.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_post_catalog.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_post_catalog.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_post_catalog.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_post_catalog.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_post_catalog.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_pcat_post ON tms_post_catalog (post_id, biz_status); -- 岗位培训目录内容行，支持回车多行、手动排序 CREATE TABLE IF NOT EXISTS tms_post_catalog_item ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, sort_no int, subject varchar(500), train_mode varchar(50), hours numeric(8,1), eval_mode varchar(50), material_id varchar(50), course_id varchar(50), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_post_catalog_item PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_post_catalog_item IS '岗位培训目录内容行，支持回车多行、手动排序'; COMMENT ON COLUMN tms_post_catalog_item.f_id IS '主键'; COMMENT ON COLUMN tms_post_catalog_item.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_post_catalog_item.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_post_catalog_item.sort_no IS '顺序'; COMMENT ON COLUMN tms_post_catalog_item.subject IS '培训内容'; COMMENT ON COLUMN tms_post_catalog_item.train_mode IS '培训方式'; COMMENT ON COLUMN tms_post_catalog_item.hours IS '课时'; COMMENT ON COLUMN tms_post_catalog_item.eval_mode IS '考核方式'; COMMENT ON COLUMN tms_post_catalog_item.material_id IS '教材'; COMMENT ON COLUMN tms_post_catalog_item.course_id IS '课程'; COMMENT ON COLUMN tms_post_catalog_item.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_post_catalog_item.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_post_catalog_item.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_post_catalog_item.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_post_catalog_item.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_pcati_fid ON tms_post_catalog_item (f_foreign_id); -- 岗位培训计划。由目录推送，可生成个人岗位培训和培训任务。 CREATE TABLE IF NOT EXISTS tms_post_plan ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), plan_no varchar(50) NOT NULL, post_id varchar(50), post_name varchar(100), dept_id varchar(50), plan_end_time date, post_desc text, trainees text, catalog_id varchar(50), publish_status varchar(20) NOT NULL DEFAULT 'draft', remark varchar(500), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_post_plan PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_post_plan IS '岗位培训计划。由目录推送，可生成个人岗位培训和培训任务。'; COMMENT ON COLUMN tms_post_plan.f_id IS '主键'; COMMENT ON COLUMN tms_post_plan.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_post_plan.plan_no IS '计划编号 JP+YYYYMM+4，如 JP2026090012'; COMMENT ON COLUMN tms_post_plan.post_id IS '岗位编码/ID'; COMMENT ON COLUMN tms_post_plan.post_name IS '岗位名称'; COMMENT ON COLUMN tms_post_plan.dept_id IS '所属部门'; COMMENT ON COLUMN tms_post_plan.plan_end_time IS '计划结束时间，带入个人岗位培训'; COMMENT ON COLUMN tms_post_plan.post_desc IS '岗位描述'; COMMENT ON COLUMN tms_post_plan.trainees IS '培训对象，用户多选'; COMMENT ON COLUMN tms_post_plan.catalog_id IS '来源目录'; COMMENT ON COLUMN tms_post_plan.publish_status IS 'draft未发布 / published已发布'; COMMENT ON COLUMN tms_post_plan.remark IS '备注'; COMMENT ON COLUMN tms_post_plan.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_post_plan.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_post_plan.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_post_plan.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_post_plan.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_post_plan.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_post_plan.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_post_plan.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_post_plan.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_post_plan.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_pplan_no ON tms_post_plan (plan_no) WHERE f_delete_mark = 0; CREATE INDEX IF NOT EXISTS idx_tms_pplan_post ON tms_post_plan (post_id, publish_status); -- 岗位培训计划明细 CREATE TABLE IF NOT EXISTS tms_post_plan_item ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, plan_time date, teacher_id varchar(50), subject varchar(500), train_mode varchar(50), hours numeric(8,1), eval_mode varchar(50), material_id varchar(50), task_id varchar(50), line_status varchar(20), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_post_plan_item PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_post_plan_item IS '岗位培训计划明细'; COMMENT ON COLUMN tms_post_plan_item.f_id IS '主键'; COMMENT ON COLUMN tms_post_plan_item.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_post_plan_item.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_post_plan_item.plan_time IS '培训计划时间'; COMMENT ON COLUMN tms_post_plan_item.teacher_id IS '培训师'; COMMENT ON COLUMN tms_post_plan_item.subject IS '培训内容'; COMMENT ON COLUMN tms_post_plan_item.train_mode IS '培训方式'; COMMENT ON COLUMN tms_post_plan_item.hours IS '培训课时'; COMMENT ON COLUMN tms_post_plan_item.eval_mode IS '考核方式'; COMMENT ON COLUMN tms_post_plan_item.material_id IS '教材'; COMMENT ON COLUMN tms_post_plan_item.task_id IS '生成的培训任务ID'; COMMENT ON COLUMN tms_post_plan_item.line_status IS 'pending待发布 / published已发布 / done已完成'; COMMENT ON COLUMN tms_post_plan_item.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_post_plan_item.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_post_plan_item.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_post_plan_item.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_post_plan_item.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_pplani_fid ON tms_post_plan_item (f_foreign_id); -- 个人岗位培训清单。转岗时子表标记已培训过则不生成任务。 CREATE TABLE IF NOT EXISTS tms_person_post ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), plan_id varchar(50), catalog_id varchar(50), user_id varchar(50) NOT NULL, post_id varchar(50), plan_end_time date, biz_status varchar(20) NOT NULL DEFAULT 'doing', person_remark varchar(500), audit_remark varchar(500), approve_remark varchar(500), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_person_post PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_person_post IS '个人岗位培训清单。转岗时子表标记已培训过则不生成任务。'; COMMENT ON COLUMN tms_person_post.f_id IS '主键'; COMMENT ON COLUMN tms_person_post.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_person_post.plan_id IS '岗位培训计划ID'; COMMENT ON COLUMN tms_person_post.catalog_id IS '目录ID'; COMMENT ON COLUMN tms_person_post.user_id IS '培训对象'; COMMENT ON COLUMN tms_person_post.post_id IS '岗位'; COMMENT ON COLUMN tms_person_post.plan_end_time IS '计划结束时间（由计划带出）'; COMMENT ON COLUMN tms_person_post.biz_status IS 'doing执行中 / done完成 / returned退回'; COMMENT ON COLUMN tms_person_post.person_remark IS '个人备注'; COMMENT ON COLUMN tms_person_post.audit_remark IS '审核备注'; COMMENT ON COLUMN tms_person_post.approve_remark IS '审批备注'; COMMENT ON COLUMN tms_person_post.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_person_post.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_person_post.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_person_post.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_person_post.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_person_post.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_person_post.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_person_post.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_person_post.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_person_post.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_ppost_user ON tms_person_post (user_id, biz_status); CREATE INDEX IF NOT EXISTS idx_tms_ppost_plan ON tms_person_post (plan_id); -- 个人岗位培训明细 CREATE TABLE IF NOT EXISTS tms_person_post_item ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, subject varchar(500), train_mode varchar(50), hours numeric(8,1), eval_mode varchar(50), already_trained varchar(10) NOT NULL DEFAULT '0', task_id varchar(50), line_status varchar(20), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_person_post_item PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_person_post_item IS '个人岗位培训明细'; COMMENT ON COLUMN tms_person_post_item.f_id IS '主键'; COMMENT ON COLUMN tms_person_post_item.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_person_post_item.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_person_post_item.subject IS '培训内容'; COMMENT ON COLUMN tms_person_post_item.train_mode IS '培训方式'; COMMENT ON COLUMN tms_person_post_item.hours IS '课时'; COMMENT ON COLUMN tms_person_post_item.eval_mode IS '考核方式'; COMMENT ON COLUMN tms_person_post_item.already_trained IS '转岗：是否已培训过，是则不生成任务'; COMMENT ON COLUMN tms_person_post_item.task_id IS '生成的培训任务'; COMMENT ON COLUMN tms_person_post_item.line_status IS '状态'; COMMENT ON COLUMN tms_person_post_item.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_person_post_item.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_person_post_item.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_person_post_item.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_person_post_item.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_pposti_fid ON tms_person_post_item (f_foreign_id); -- 年度培训计划（公司/部门）。办结后生成培训任务。流程：部门负责人→QA负责人。 CREATE TABLE IF NOT EXISTS tms_annual_plan ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), plan_year int NOT NULL, plan_no varchar(50) NOT NULL, plan_type varchar(20), author_id varchar(50), author_dept_id varchar(50), author_date date, version_no int NOT NULL DEFAULT 1, dept_leader_id varchar(50), qa_leader_id varchar(50), biz_status varchar(20) NOT NULL DEFAULT 'draft', remark text, f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_annual_plan PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_annual_plan IS '年度培训计划（公司/部门）。办结后生成培训任务。流程：部门负责人→QA负责人。'; COMMENT ON COLUMN tms_annual_plan.f_id IS '主键'; COMMENT ON COLUMN tms_annual_plan.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_annual_plan.plan_year IS '年份，制定时默认当前年+1'; COMMENT ON COLUMN tms_annual_plan.plan_no IS '计划编号，如 20260001'; COMMENT ON COLUMN tms_annual_plan.plan_type IS 'company公司级 / dept部门级'; COMMENT ON COLUMN tms_annual_plan.author_id IS '制定人'; COMMENT ON COLUMN tms_annual_plan.author_dept_id IS '制定部门'; COMMENT ON COLUMN tms_annual_plan.author_date IS '制定日期'; COMMENT ON COLUMN tms_annual_plan.version_no IS '版本'; COMMENT ON COLUMN tms_annual_plan.dept_leader_id IS '部门负责人（签核展示）'; COMMENT ON COLUMN tms_annual_plan.qa_leader_id IS 'QA部负责人'; COMMENT ON COLUMN tms_annual_plan.biz_status IS 'draft草稿 / approving审批中 / approved已批准'; COMMENT ON COLUMN tms_annual_plan.remark IS '备注'; COMMENT ON COLUMN tms_annual_plan.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_annual_plan.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_annual_plan.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_annual_plan.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_annual_plan.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_annual_plan.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_annual_plan.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_annual_plan.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_annual_plan.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_annual_plan.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_aplan_no ON tms_annual_plan (plan_no) WHERE f_delete_mark = 0; CREATE INDEX IF NOT EXISTS idx_tms_aplan_year ON tms_annual_plan (plan_year, plan_type, author_dept_id); -- 年度培训计划明细 CREATE TABLE IF NOT EXISTS tms_annual_plan_item ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, train_month varchar(6), subject varchar(500), train_mode varchar(50), trainees text, hours numeric(8,1), teacher_id varchar(50), eval_mode varchar(50), course_id varchar(50), line_status varchar(20) NOT NULL DEFAULT 'pending', task_id varchar(50), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_annual_plan_item PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_annual_plan_item IS '年度培训计划明细'; COMMENT ON COLUMN tms_annual_plan_item.f_id IS '主键'; COMMENT ON COLUMN tms_annual_plan_item.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_annual_plan_item.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_annual_plan_item.train_month IS '培训月份 YYYYMM，如 202609'; COMMENT ON COLUMN tms_annual_plan_item.subject IS '培训内容'; COMMENT ON COLUMN tms_annual_plan_item.train_mode IS '培训方式'; COMMENT ON COLUMN tms_annual_plan_item.trainees IS '培训对象'; COMMENT ON COLUMN tms_annual_plan_item.hours IS '培训课时'; COMMENT ON COLUMN tms_annual_plan_item.teacher_id IS '培训师'; COMMENT ON COLUMN tms_annual_plan_item.eval_mode IS '考核方式'; COMMENT ON COLUMN tms_annual_plan_item.course_id IS '培训课程'; COMMENT ON COLUMN tms_annual_plan_item.line_status IS 'pending待执行 / tasked已生成任务 / done已完成 / delayed已延期'; COMMENT ON COLUMN tms_annual_plan_item.task_id IS '生成的培训任务'; COMMENT ON COLUMN tms_annual_plan_item.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_annual_plan_item.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_annual_plan_item.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_annual_plan_item.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_annual_plan_item.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_aplani_fid ON tms_annual_plan_item (f_foreign_id); CREATE INDEX IF NOT EXISTS idx_tms_aplani_month ON tms_annual_plan_item (train_month); -- 年度培训计划延期申请 CREATE TABLE IF NOT EXISTS tms_annual_delay ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), plan_id varchar(50) NOT NULL, item_id varchar(50), old_month varchar(6), new_month varchar(6), reason text, biz_status varchar(20), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_annual_delay PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_annual_delay IS '年度培训计划延期申请'; COMMENT ON COLUMN tms_annual_delay.f_id IS '主键'; COMMENT ON COLUMN tms_annual_delay.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_annual_delay.plan_id IS '年度计划ID'; COMMENT ON COLUMN tms_annual_delay.item_id IS '计划明细ID'; COMMENT ON COLUMN tms_annual_delay.old_month IS '原月份'; COMMENT ON COLUMN tms_annual_delay.new_month IS '延期至月份'; COMMENT ON COLUMN tms_annual_delay.reason IS '延期原因'; COMMENT ON COLUMN tms_annual_delay.biz_status IS '审批状态'; COMMENT ON COLUMN tms_annual_delay.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_annual_delay.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_annual_delay.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_annual_delay.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_annual_delay.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_annual_delay.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_annual_delay.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_annual_delay.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_annual_delay.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_annual_delay.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_adelay_plan ON tms_annual_delay (plan_id); -- --------------------------------------------------------------------------- -- 4. 培训任务与记录 -- --------------------------------------------------------------------------- -- 培训任务（核心）。来源：岗位计划/年度计划/文件生效/临时/外出/再维护。 CREATE TABLE IF NOT EXISTS tms_task ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), task_no varchar(50) NOT NULL, category varchar(50), train_type varchar(50), source_type varchar(50), source_plan_no varchar(50), source_id varchar(50), source_item_id varchar(50), subject text, key_points text, material_id varchar(50), material_no varchar(50), place_id varchar(50), place_no varchar(50), place_name varchar(200), start_time timestamp, end_time timestamp, close_time timestamp, paper_id varchar(50), paper_name varchar(300), exam_start timestamp, exam_end timestamp, teacher_id text, train_mode varchar(50), eval_mode varchar(50), trainees text, task_kind varchar(20), prev_task_no varchar(50), publish_status varchar(20) NOT NULL DEFAULT 'draft', publish_time timestamp, retake_limit int NOT NULL DEFAULT 1, pass_score numeric(8,1), quiz_ratio int, sign_rate int, pass_rate int, unfinished_rate int, overdue_remind varchar(10) NOT NULL DEFAULT '0', invite_flag varchar(10) NOT NULL DEFAULT '0', dms_file_id varchar(50), remark varchar(500), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_task PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_task IS '培训任务（核心）。来源：岗位计划/年度计划/文件生效/临时/外出/再维护。'; COMMENT ON COLUMN tms_task.f_id IS '主键'; COMMENT ON COLUMN tms_task.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_task.task_no IS '任务编号 TT+YYYYMM+4，如 TT2026090618'; COMMENT ON COLUMN tms_task.category IS '培训分类：临时培训/岗位培训计划/年度培训计划/文件生效培训/外出培训/再维护'; COMMENT ON COLUMN tms_task.train_type IS '培训类型（列表字段，可与分类并存）'; COMMENT ON COLUMN tms_task.source_type IS '来源类型编码'; COMMENT ON COLUMN tms_task.source_plan_no IS '来源计划编号'; COMMENT ON COLUMN tms_task.source_id IS '来源主表ID'; COMMENT ON COLUMN tms_task.source_item_id IS '来源明细ID，归档回写'; COMMENT ON COLUMN tms_task.subject IS '培训主题'; COMMENT ON COLUMN tms_task.key_points IS '培训要点，签到前强制阅读'; COMMENT ON COLUMN tms_task.material_id IS '培训教材'; COMMENT ON COLUMN tms_task.material_no IS '教材编号冗余'; COMMENT ON COLUMN tms_task.place_id IS '培训地点'; COMMENT ON COLUMN tms_task.place_no IS '地点编号冗余'; COMMENT ON COLUMN tms_task.place_name IS '地点名称冗余'; COMMENT ON COLUMN tms_task.start_time IS '培训开始时间'; COMMENT ON COLUMN tms_task.end_time IS '培训结束时间'; COMMENT ON COLUMN tms_task.close_time IS '任务关闭时间，到达后记录进待归档'; COMMENT ON COLUMN tms_task.paper_id IS '试卷'; COMMENT ON COLUMN tms_task.paper_name IS '试卷名称冗余'; COMMENT ON COLUMN tms_task.exam_start IS '考试开始时间'; COMMENT ON COLUMN tms_task.exam_end IS '考试结束时间'; COMMENT ON COLUMN tms_task.teacher_id IS '培训师，可多选'; COMMENT ON COLUMN tms_task.train_mode IS '培训方式'; COMMENT ON COLUMN tms_task.eval_mode IS '培训效果评估方式/考核方式'; COMMENT ON COLUMN tms_task.trainees IS '培训对象，用户多选；发布时拆个人任务'; COMMENT ON COLUMN tms_task.task_kind IS '任务类型：new新增 / continue继续(再维护)'; COMMENT ON COLUMN tms_task.prev_task_no IS '上一次培训任务编号'; COMMENT ON COLUMN tms_task.publish_status IS 'draft未发布 / published已发布 / cancelled已取消 / done已完成'; COMMENT ON COLUMN tms_task.publish_time IS '发布时间'; COMMENT ON COLUMN tms_task.retake_limit IS '不合格可重考次数'; COMMENT ON COLUMN tms_task.pass_score IS '通过分数，空则用试卷'; COMMENT ON COLUMN tms_task.quiz_ratio IS '提问比例0-100，自动算应提问人数'; COMMENT ON COLUMN tms_task.sign_rate IS '签到率%，取整舍小数'; COMMENT ON COLUMN tms_task.pass_rate IS '合格率%，取整舍小数'; COMMENT ON COLUMN tms_task.unfinished_rate IS '未完成率%'; COMMENT ON COLUMN tms_task.overdue_remind IS '是否逾期提醒部门领导'; COMMENT ON COLUMN tms_task.invite_flag IS '是否发送培训邀请'; COMMENT ON COLUMN tms_task.dms_file_id IS '文件生效自动引入的附件'; COMMENT ON COLUMN tms_task.remark IS '备注'; COMMENT ON COLUMN tms_task.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_task.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_task.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_task.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_task.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_task.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_task.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_task.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_task.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_task.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_task_no ON tms_task (task_no) WHERE f_delete_mark = 0; CREATE INDEX IF NOT EXISTS idx_tms_task_status ON tms_task (publish_status, start_time); CREATE INDEX IF NOT EXISTS idx_tms_task_place ON tms_task (place_id, start_time, end_time); CREATE INDEX IF NOT EXISTS idx_tms_task_source ON tms_task (source_type, source_id); CREATE INDEX IF NOT EXISTS idx_tms_task_teacher ON tms_task (teacher_id); -- 培训任务教材/附件清单，发布后同步到培训记录 CREATE TABLE IF NOT EXISTS tms_task_file ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, file_no varchar(50), file_name varchar(300), material_id varchar(50), file_json text, can_download varchar(10) NOT NULL DEFAULT '0', f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_task_file PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_task_file IS '培训任务教材/附件清单，发布后同步到培训记录'; COMMENT ON COLUMN tms_task_file.f_id IS '主键'; COMMENT ON COLUMN tms_task_file.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_task_file.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_task_file.file_no IS '编号'; COMMENT ON COLUMN tms_task_file.file_name IS '文件名称'; COMMENT ON COLUMN tms_task_file.material_id IS '来源教材'; COMMENT ON COLUMN tms_task_file.file_json IS '附件'; COMMENT ON COLUMN tms_task_file.can_download IS '引用教材默认不可下载打印'; COMMENT ON COLUMN tms_task_file.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_task_file.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_task_file.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_task_file.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_task_file.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_tfile_fid ON tms_task_file (f_foreign_id); -- 培训任务提问预设问题和答案，签到后弹出 CREATE TABLE IF NOT EXISTS tms_task_quiz ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, sort_no int, question text, answer text, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_task_quiz PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_task_quiz IS '培训任务提问预设问题和答案，签到后弹出'; COMMENT ON COLUMN tms_task_quiz.f_id IS '主键'; COMMENT ON COLUMN tms_task_quiz.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_task_quiz.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_task_quiz.sort_no IS '序号'; COMMENT ON COLUMN tms_task_quiz.question IS '问题'; COMMENT ON COLUMN tms_task_quiz.answer IS '参考答案'; COMMENT ON COLUMN tms_task_quiz.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_task_quiz.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_task_quiz.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_task_quiz.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_task_quiz.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_tquiz_fid ON tms_task_quiz (f_foreign_id); -- 个人培训任务。发布时按人拆行；学员端自学/签到/考试入口。 CREATE TABLE IF NOT EXISTS tms_person_task ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), task_id varchar(50) NOT NULL, task_no varchar(50), user_id varchar(50) NOT NULL, dept_id varchar(50), hours numeric(8,1), biz_status varchar(20) NOT NULL DEFAULT 'todo', sign_time timestamp, sign_mode varchar(20), learn_seconds int, exam_score numeric(8,1), pass_flag varchar(20), guest_flag varchar(10) NOT NULL DEFAULT '0', f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_person_task PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_person_task IS '个人培训任务。发布时按人拆行；学员端自学/签到/考试入口。'; COMMENT ON COLUMN tms_person_task.f_id IS '主键'; COMMENT ON COLUMN tms_person_task.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_person_task.task_id IS '培训任务ID'; COMMENT ON COLUMN tms_person_task.task_no IS '任务编号冗余'; COMMENT ON COLUMN tms_person_task.user_id IS '培训对象'; COMMENT ON COLUMN tms_person_task.dept_id IS '当时部门快照'; COMMENT ON COLUMN tms_person_task.hours IS '学时'; COMMENT ON COLUMN tms_person_task.biz_status IS 'todo未完成 / signed已签到 / done已完成 / expired已过期 / cancelled已取消'; COMMENT ON COLUMN tms_person_task.sign_time IS '签到时间'; COMMENT ON COLUMN tms_person_task.sign_mode IS '签到方式'; COMMENT ON COLUMN tms_person_task.learn_seconds IS '在线学习时长秒'; COMMENT ON COLUMN tms_person_task.exam_score IS '考试成绩'; COMMENT ON COLUMN tms_person_task.pass_flag IS 'pass合格 / fail不合格 / na无需考核'; COMMENT ON COLUMN tms_person_task.guest_flag IS '是否名单外签到'; COMMENT ON COLUMN tms_person_task.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_person_task.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_person_task.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_person_task.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_person_task.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_person_task.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_person_task.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_ptask_user ON tms_person_task (user_id, biz_status); CREATE INDEX IF NOT EXISTS idx_tms_ptask_task ON tms_person_task (task_id); CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_ptask_task_user ON tms_person_task (task_id, user_id) WHERE f_delete_mark = 0; -- 培训记录。任务发布后生成；关闭时间到达进待归档。 CREATE TABLE IF NOT EXISTS tms_record ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), record_no varchar(50), task_id varchar(50) NOT NULL, task_no varchar(50), category varchar(50), subject text, teacher_id text, train_type varchar(50), train_mode varchar(50), eval_mode varchar(50), start_time timestamp, end_time timestamp, close_time timestamp, archive_status varchar(20) NOT NULL DEFAULT 'pending', archive_time timestamp, archiver_id varchar(50), sign_rate int, pass_rate int, lecture_file text, paper_sign_file text, print_tpl_id varchar(50), remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_record PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_record IS '培训记录。任务发布后生成；关闭时间到达进待归档。'; COMMENT ON COLUMN tms_record.f_id IS '主键'; COMMENT ON COLUMN tms_record.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_record.record_no IS '记录编号，默认同任务编号'; COMMENT ON COLUMN tms_record.task_id IS '培训任务ID'; COMMENT ON COLUMN tms_record.task_no IS '任务编号'; COMMENT ON COLUMN tms_record.category IS '培训分类'; COMMENT ON COLUMN tms_record.subject IS '培训主题'; COMMENT ON COLUMN tms_record.teacher_id IS '培训师'; COMMENT ON COLUMN tms_record.train_type IS '培训类型'; COMMENT ON COLUMN tms_record.train_mode IS '培训方式'; COMMENT ON COLUMN tms_record.eval_mode IS '考核方式'; COMMENT ON COLUMN tms_record.start_time IS '培训开始时间，套打模板按此索引版本'; COMMENT ON COLUMN tms_record.end_time IS '培训结束时间'; COMMENT ON COLUMN tms_record.close_time IS '关闭时间'; COMMENT ON COLUMN tms_record.archive_status IS 'pending待归档 / ready可归档 / archived已归档 / invalid无效'; COMMENT ON COLUMN tms_record.archive_time IS '归档时间'; COMMENT ON COLUMN tms_record.archiver_id IS '归档人'; COMMENT ON COLUMN tms_record.sign_rate IS '参与/签到率% 取整舍小数'; COMMENT ON COLUMN tms_record.pass_rate IS '合格率%'; COMMENT ON COLUMN tms_record.lecture_file IS '培训讲义附件'; COMMENT ON COLUMN tms_record.paper_sign_file IS '纸质签到表附件'; COMMENT ON COLUMN tms_record.print_tpl_id IS '打印模板版本'; COMMENT ON COLUMN tms_record.remark IS '备注'; COMMENT ON COLUMN tms_record.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_record.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_record.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_record.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_record.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_record.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_record.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_record_task ON tms_record (task_id) WHERE f_delete_mark = 0; CREATE INDEX IF NOT EXISTS idx_tms_record_arch ON tms_record (archive_status, start_time); -- 培训记录签到/结果明细（一人一行） CREATE TABLE IF NOT EXISTS tms_record_sign ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, user_id varchar(50) NOT NULL, user_name varchar(100), dept_id varchar(50), sign_time timestamp, sign_mode varchar(20), guest_flag varchar(10) NOT NULL DEFAULT '0', result_flag varchar(20), exam_score numeric(8,1), practice_score numeric(8,1), quiz_score numeric(8,1), exam_id varchar(50), judge_user_id varchar(50), judge_time timestamp, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_record_sign PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_record_sign IS '培训记录签到/结果明细（一人一行）'; COMMENT ON COLUMN tms_record_sign.f_id IS '主键'; COMMENT ON COLUMN tms_record_sign.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_record_sign.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_record_sign.user_id IS '培训对象'; COMMENT ON COLUMN tms_record_sign.user_name IS '姓名快照'; COMMENT ON COLUMN tms_record_sign.dept_id IS '部门快照'; COMMENT ON COLUMN tms_record_sign.sign_time IS '签到时间'; COMMENT ON COLUMN tms_record_sign.sign_mode IS '签到方式'; COMMENT ON COLUMN tms_record_sign.guest_flag IS '名单外签到'; COMMENT ON COLUMN tms_record_sign.result_flag IS 'pass合格 / fail不合格 / absent未参加 / na无需考核'; COMMENT ON COLUMN tms_record_sign.exam_score IS '考试成绩'; COMMENT ON COLUMN tms_record_sign.practice_score IS '实操成绩'; COMMENT ON COLUMN tms_record_sign.quiz_score IS '提问成绩'; COMMENT ON COLUMN tms_record_sign.exam_id IS '答卷ID'; COMMENT ON COLUMN tms_record_sign.judge_user_id IS '结果判定人（培训师）'; COMMENT ON COLUMN tms_record_sign.judge_time IS '判定时间'; COMMENT ON COLUMN tms_record_sign.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_record_sign.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_record_sign.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_record_sign.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_record_sign.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_rsign_fid ON tms_record_sign (f_foreign_id); CREATE INDEX IF NOT EXISTS idx_tms_rsign_user ON tms_record_sign (user_id); -- 培训记录变更：改结束时间、增加培训人员。培训师发起→部门领导（领导即培训师则跳过）。 CREATE TABLE IF NOT EXISTS tms_record_change ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), record_id varchar(50) NOT NULL, task_id varchar(50), change_type varchar(20), old_end_time timestamp, new_end_time timestamp, add_users text, reason text, biz_status varchar(20), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_record_change PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_record_change IS '培训记录变更：改结束时间、增加培训人员。培训师发起→部门领导（领导即培训师则跳过）。'; COMMENT ON COLUMN tms_record_change.f_id IS '主键'; COMMENT ON COLUMN tms_record_change.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_record_change.record_id IS '培训记录ID'; COMMENT ON COLUMN tms_record_change.task_id IS '任务ID'; COMMENT ON COLUMN tms_record_change.change_type IS 'end_time改结束时间 / add_user增加人员'; COMMENT ON COLUMN tms_record_change.old_end_time IS '原结束时间'; COMMENT ON COLUMN tms_record_change.new_end_time IS '新结束时间'; COMMENT ON COLUMN tms_record_change.add_users IS '新增人员'; COMMENT ON COLUMN tms_record_change.reason IS '变更原因'; COMMENT ON COLUMN tms_record_change.biz_status IS '审批状态'; COMMENT ON COLUMN tms_record_change.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_record_change.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_record_change.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_record_change.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_record_change.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_record_change.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_record_change.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_record_change.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_record_change.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_record_change.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_rchg_rec ON tms_record_change (record_id); -- 培训资料归档（电子/纸质/实操记录双结合） CREATE TABLE IF NOT EXISTS tms_archive_file ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), record_id varchar(50) NOT NULL, task_id varchar(50), archive_type varchar(20), file_json text, archive_time timestamp, archiver_id varchar(50), remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_archive_file PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_archive_file IS '培训资料归档（电子/纸质/实操记录双结合）'; COMMENT ON COLUMN tms_archive_file.f_id IS '主键'; COMMENT ON COLUMN tms_archive_file.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_archive_file.record_id IS '培训记录ID'; COMMENT ON COLUMN tms_archive_file.task_id IS '任务ID'; COMMENT ON COLUMN tms_archive_file.archive_type IS 'electronic电子 / paper纸质 / practice实操档案'; COMMENT ON COLUMN tms_archive_file.file_json IS '归档资料'; COMMENT ON COLUMN tms_archive_file.archive_time IS '归档时间'; COMMENT ON COLUMN tms_archive_file.archiver_id IS '归档人'; COMMENT ON COLUMN tms_archive_file.remark IS '备注'; COMMENT ON COLUMN tms_archive_file.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_archive_file.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_archive_file.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_archive_file.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_archive_file.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_archive_file.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_archive_file.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_afile_rec ON tms_archive_file (record_id); -- --------------------------------------------------------------------------- -- 5. 考试与考核 -- --------------------------------------------------------------------------- -- 在线考试答卷。中途退出可续考；提交后客观题自动评分，有主观题进阅卷。 CREATE TABLE IF NOT EXISTS tms_exam ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), task_id varchar(50) NOT NULL, person_task_id varchar(50), record_id varchar(50), paper_id varchar(50), paper_name varchar(300), user_id varchar(50) NOT NULL, attempt_no int NOT NULL DEFAULT 1, start_time timestamp, submit_time timestamp, duration_min int, objective_score numeric(8,1), subjective_score numeric(8,1), total_score numeric(8,1), pass_score numeric(8,1), pass_flag varchar(20), grade_status varchar(20) NOT NULL DEFAULT 'auto', grader_id varchar(50), grade_time timestamp, exam_status varchar(20) NOT NULL DEFAULT 'doing', f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_exam PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_exam IS '在线考试答卷。中途退出可续考；提交后客观题自动评分，有主观题进阅卷。'; COMMENT ON COLUMN tms_exam.f_id IS '主键'; COMMENT ON COLUMN tms_exam.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_exam.task_id IS '培训任务'; COMMENT ON COLUMN tms_exam.person_task_id IS '个人任务'; COMMENT ON COLUMN tms_exam.record_id IS '培训记录'; COMMENT ON COLUMN tms_exam.paper_id IS '当时试卷ID'; COMMENT ON COLUMN tms_exam.paper_name IS '试卷名称快照'; COMMENT ON COLUMN tms_exam.user_id IS '考生'; COMMENT ON COLUMN tms_exam.attempt_no IS '第几次考试'; COMMENT ON COLUMN tms_exam.start_time IS '开始作答'; COMMENT ON COLUMN tms_exam.submit_time IS '交卷时间'; COMMENT ON COLUMN tms_exam.duration_min IS '当时时长快照'; COMMENT ON COLUMN tms_exam.objective_score IS '客观题得分'; COMMENT ON COLUMN tms_exam.subjective_score IS '主观题得分'; COMMENT ON COLUMN tms_exam.total_score IS '总分'; COMMENT ON COLUMN tms_exam.pass_score IS '当时合格分快照'; COMMENT ON COLUMN tms_exam.pass_flag IS 'pass/fail'; COMMENT ON COLUMN tms_exam.grade_status IS 'auto无需阅卷 / pending待批改 / graded已批改'; COMMENT ON COLUMN tms_exam.grader_id IS '阅卷老师'; COMMENT ON COLUMN tms_exam.grade_time IS '阅卷时间'; COMMENT ON COLUMN tms_exam.exam_status IS 'doing考试中 / submitted已交卷 / cancelled已取消'; COMMENT ON COLUMN tms_exam.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_exam.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_exam.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_exam.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_exam.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_exam.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_exam.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_exam_task_user ON tms_exam (task_id, user_id, attempt_no); CREATE INDEX IF NOT EXISTS idx_tms_exam_grade ON tms_exam (grade_status); CREATE INDEX IF NOT EXISTS idx_tms_exam_user ON tms_exam (user_id); -- 答卷试题快照：题目/选项/答案/分数保留考试当时状态，后续改题改卷不影响。 CREATE TABLE IF NOT EXISTS tms_exam_item ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, question_id varchar(50), question_type varchar(20), stem text, options_json text, correct_answer text, user_answer text, score numeric(8,1), got_score numeric(8,1), is_subjective varchar(10) NOT NULL DEFAULT '0', analysis text, sort_no int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_exam_item PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_exam_item IS '答卷试题快照：题目/选项/答案/分数保留考试当时状态，后续改题改卷不影响。'; COMMENT ON COLUMN tms_exam_item.f_id IS '主键'; COMMENT ON COLUMN tms_exam_item.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_exam_item.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_exam_item.question_id IS '原试题ID'; COMMENT ON COLUMN tms_exam_item.question_type IS '题型快照'; COMMENT ON COLUMN tms_exam_item.stem IS '题干快照'; COMMENT ON COLUMN tms_exam_item.options_json IS '选项快照 JSON'; COMMENT ON COLUMN tms_exam_item.correct_answer IS '当时正确答案'; COMMENT ON COLUMN tms_exam_item.user_answer IS '考生作答'; COMMENT ON COLUMN tms_exam_item.score IS '本题满分'; COMMENT ON COLUMN tms_exam_item.got_score IS '得分'; COMMENT ON COLUMN tms_exam_item.is_subjective IS '是否主观题'; COMMENT ON COLUMN tms_exam_item.analysis IS '解析快照'; COMMENT ON COLUMN tms_exam_item.sort_no IS '试卷内顺序'; COMMENT ON COLUMN tms_exam_item.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_exam_item.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_exam_item.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_exam_item.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_exam_item.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_eitem_fid ON tms_exam_item (f_foreign_id); -- 实操考核主表，保存后回写培训记录 CREATE TABLE IF NOT EXISTS tms_practice ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), task_id varchar(50) NOT NULL, record_id varchar(50), teacher_id varchar(50), practice_time timestamp, remark varchar(500), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_practice PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_practice IS '实操考核主表，保存后回写培训记录'; COMMENT ON COLUMN tms_practice.f_id IS '主键'; COMMENT ON COLUMN tms_practice.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_practice.task_id IS '培训任务'; COMMENT ON COLUMN tms_practice.record_id IS '培训记录'; COMMENT ON COLUMN tms_practice.teacher_id IS '考核人'; COMMENT ON COLUMN tms_practice.practice_time IS '考核时间'; COMMENT ON COLUMN tms_practice.remark IS '备注'; COMMENT ON COLUMN tms_practice.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_practice.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_practice.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_practice.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_practice.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_practice.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_practice.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_prac_task ON tms_practice (task_id); -- 实操考核明细 CREATE TABLE IF NOT EXISTS tms_practice_item ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, user_id varchar(50) NOT NULL, content text, score numeric(8,1), result_flag varchar(20), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_practice_item PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_practice_item IS '实操考核明细'; COMMENT ON COLUMN tms_practice_item.f_id IS '主键'; COMMENT ON COLUMN tms_practice_item.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_practice_item.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_practice_item.user_id IS '培训对象'; COMMENT ON COLUMN tms_practice_item.content IS '考核内容'; COMMENT ON COLUMN tms_practice_item.score IS '分数'; COMMENT ON COLUMN tms_practice_item.result_flag IS '合格/不合格'; COMMENT ON COLUMN tms_practice_item.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_practice_item.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_practice_item.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_practice_item.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_practice_item.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_praci_fid ON tms_practice_item (f_foreign_id); -- 现场提问考核打分，保存后回写培训记录 CREATE TABLE IF NOT EXISTS tms_quiz_score ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), task_id varchar(50) NOT NULL, record_id varchar(50), user_id varchar(50) NOT NULL, question text, answer text, score numeric(8,1), teacher_id varchar(50), quiz_time timestamp, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_quiz_score PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_quiz_score IS '现场提问考核打分，保存后回写培训记录'; COMMENT ON COLUMN tms_quiz_score.f_id IS '主键'; COMMENT ON COLUMN tms_quiz_score.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_quiz_score.task_id IS '培训任务'; COMMENT ON COLUMN tms_quiz_score.record_id IS '培训记录'; COMMENT ON COLUMN tms_quiz_score.user_id IS '培训对象'; COMMENT ON COLUMN tms_quiz_score.question IS '提问内容'; COMMENT ON COLUMN tms_quiz_score.answer IS '回答'; COMMENT ON COLUMN tms_quiz_score.score IS '分数'; COMMENT ON COLUMN tms_quiz_score.teacher_id IS '提问人'; COMMENT ON COLUMN tms_quiz_score.quiz_time IS '时间'; COMMENT ON COLUMN tms_quiz_score.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_quiz_score.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_quiz_score.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_quiz_score.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_quiz_score.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_quiz_score.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_quiz_score.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_qscore_task ON tms_quiz_score (task_id); CREATE INDEX IF NOT EXISTS idx_tms_qscore_user ON tms_quiz_score (user_id); -- --------------------------------------------------------------------------- -- 6. 个人档案 -- --------------------------------------------------------------------------- -- 个人培训档案。入职自动建档；一人一档。 CREATE TABLE IF NOT EXISTS tms_person_file ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), archive_no varchar(50) NOT NULL, archive_date date, user_id varchar(50) NOT NULL, user_name varchar(100), dept_id varchar(50), hire_date date, leave_date date, main_post_id varchar(50), part_post_id varchar(50), biz_status varchar(20) NOT NULL DEFAULT 'valid', f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_person_file PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_person_file IS '个人培训档案。入职自动建档；一人一档。'; COMMENT ON COLUMN tms_person_file.f_id IS '主键'; COMMENT ON COLUMN tms_person_file.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_person_file.archive_no IS '档案编号 AR+YYYY+4'; COMMENT ON COLUMN tms_person_file.archive_date IS '建档时间'; COMMENT ON COLUMN tms_person_file.user_id IS '员工'; COMMENT ON COLUMN tms_person_file.user_name IS '姓名冗余'; COMMENT ON COLUMN tms_person_file.dept_id IS '所属部门'; COMMENT ON COLUMN tms_person_file.hire_date IS '入职时间'; COMMENT ON COLUMN tms_person_file.leave_date IS '离职时间'; COMMENT ON COLUMN tms_person_file.main_post_id IS '主要岗位'; COMMENT ON COLUMN tms_person_file.part_post_id IS '兼职岗位'; COMMENT ON COLUMN tms_person_file.biz_status IS 'valid在职 / left离职'; COMMENT ON COLUMN tms_person_file.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_person_file.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_person_file.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_person_file.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_person_file.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_person_file.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_person_file.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_pfile_user ON tms_person_file (user_id) WHERE f_delete_mark = 0; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_pfile_no ON tms_person_file (archive_no) WHERE f_delete_mark = 0; -- 个人培训档案-工作履历 CREATE TABLE IF NOT EXISTS tms_person_work ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, company_name varchar(200), post_name varchar(100), hire_date date, leave_date date, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, CONSTRAINT pk_tms_person_work PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_person_work IS '个人培训档案-工作履历'; COMMENT ON COLUMN tms_person_work.f_id IS '主键'; COMMENT ON COLUMN tms_person_work.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_person_work.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_person_work.company_name IS '公司名称'; COMMENT ON COLUMN tms_person_work.post_name IS '任职岗位'; COMMENT ON COLUMN tms_person_work.hire_date IS '入职时间'; COMMENT ON COLUMN tms_person_work.leave_date IS '离职时间'; COMMENT ON COLUMN tms_person_work.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_person_work.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_person_work.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_person_work.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_person_work.f_delete_mark IS '删除标记 0正常 1删除'; CREATE INDEX IF NOT EXISTS idx_tms_pwork_fid ON tms_person_work (f_foreign_id); -- 资质文件/获得证书。资质文件管理与档案子表共用此表。 CREATE TABLE IF NOT EXISTS tms_person_cert ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), f_foreign_id varchar(50) NOT NULL, archive_id varchar(50), user_id varchar(50), cert_no varchar(100), cert_name varchar(200), gain_date date, expire_date date, cert_file text, cert_status varchar(20), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_person_cert PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_person_cert IS '资质文件/获得证书。资质文件管理与档案子表共用此表。'; COMMENT ON COLUMN tms_person_cert.f_id IS '主键'; COMMENT ON COLUMN tms_person_cert.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_person_cert.f_foreign_id IS '主表f_id'; COMMENT ON COLUMN tms_person_cert.archive_id IS '个人档案ID，无外键字段时由主从写入f_foreign_id'; COMMENT ON COLUMN tms_person_cert.user_id IS '员工，列表模块用'; COMMENT ON COLUMN tms_person_cert.cert_no IS '证书编号'; COMMENT ON COLUMN tms_person_cert.cert_name IS '证书名称'; COMMENT ON COLUMN tms_person_cert.gain_date IS '获得时间'; COMMENT ON COLUMN tms_person_cert.expire_date IS '有效期至'; COMMENT ON COLUMN tms_person_cert.cert_file IS '证书附件'; COMMENT ON COLUMN tms_person_cert.cert_status IS 'valid有效 / near近效期 / invalid无效'; COMMENT ON COLUMN tms_person_cert.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_person_cert.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_person_cert.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_person_cert.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_person_cert.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_person_cert.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_person_cert.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_pcert_fid ON tms_person_cert (f_foreign_id); CREATE INDEX IF NOT EXISTS idx_tms_pcert_user ON tms_person_cert (user_id); -- 个人培训目录（归档快照）。记录归档后写入，满足审计。 CREATE TABLE IF NOT EXISTS tms_person_train ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), archive_id varchar(50) NOT NULL, user_id varchar(50) NOT NULL, record_id varchar(50), record_no varchar(50), category varchar(50), subject text, train_mode varchar(50), teacher_id text, eval_mode varchar(50), pass_flag varchar(20), start_time timestamp, end_time timestamp, hours numeric(8,1), f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_person_train PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_person_train IS '个人培训目录（归档快照）。记录归档后写入，满足审计。'; COMMENT ON COLUMN tms_person_train.f_id IS '主键'; COMMENT ON COLUMN tms_person_train.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_person_train.archive_id IS '个人档案ID'; COMMENT ON COLUMN tms_person_train.user_id IS '员工'; COMMENT ON COLUMN tms_person_train.record_id IS '培训记录ID'; COMMENT ON COLUMN tms_person_train.record_no IS '培训记录编号'; COMMENT ON COLUMN tms_person_train.category IS '培训类型/分类'; COMMENT ON COLUMN tms_person_train.subject IS '培训内容'; COMMENT ON COLUMN tms_person_train.train_mode IS '培训方式'; COMMENT ON COLUMN tms_person_train.teacher_id IS '培训师'; COMMENT ON COLUMN tms_person_train.eval_mode IS '考核方式'; COMMENT ON COLUMN tms_person_train.pass_flag IS '是否合格'; COMMENT ON COLUMN tms_person_train.start_time IS '培训开始时间'; COMMENT ON COLUMN tms_person_train.end_time IS '培训结束时间'; COMMENT ON COLUMN tms_person_train.hours IS '学时'; COMMENT ON COLUMN tms_person_train.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_person_train.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_person_train.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_person_train.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_person_train.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_person_train.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_person_train.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_ptrain_user ON tms_person_train (user_id); CREATE INDEX IF NOT EXISTS idx_tms_ptrain_arch ON tms_person_train (archive_id); CREATE INDEX IF NOT EXISTS idx_tms_ptrain_rec ON tms_person_train (record_id); -- --------------------------------------------------------------------------- -- 7. 其他流程与配置 -- --------------------------------------------------------------------------- -- 外出培训 CREATE TABLE IF NOT EXISTS tms_out_train ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), apply_no varchar(50), applicant_id varchar(50), apply_dept_id varchar(50), org_name varchar(200), subject varchar(500), start_time timestamp, end_time timestamp, hours numeric(8,1), file_json text, biz_status varchar(20), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_out_train PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_out_train IS '外出培训'; COMMENT ON COLUMN tms_out_train.f_id IS '主键'; COMMENT ON COLUMN tms_out_train.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_out_train.apply_no IS '申请单号'; COMMENT ON COLUMN tms_out_train.applicant_id IS '申请人'; COMMENT ON COLUMN tms_out_train.apply_dept_id IS '申请部门'; COMMENT ON COLUMN tms_out_train.org_name IS '外部机构'; COMMENT ON COLUMN tms_out_train.subject IS '培训主题'; COMMENT ON COLUMN tms_out_train.start_time IS '开始'; COMMENT ON COLUMN tms_out_train.end_time IS '结束'; COMMENT ON COLUMN tms_out_train.hours IS '学时'; COMMENT ON COLUMN tms_out_train.file_json IS '资料附件'; COMMENT ON COLUMN tms_out_train.biz_status IS '审批状态'; COMMENT ON COLUMN tms_out_train.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_out_train.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_out_train.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_out_train.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_out_train.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_out_train.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_out_train.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_out_train.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_out_train.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_out_train.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_out_no ON tms_out_train (apply_no) WHERE f_delete_mark = 0; -- 转岗人员考核鉴定 CREATE TABLE IF NOT EXISTS tms_transfer_eval ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), user_id varchar(50) NOT NULL, from_post_id varchar(50), to_post_id varchar(50), eval_result varchar(50), remark text, biz_status varchar(20), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_transfer_eval PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_transfer_eval IS '转岗人员考核鉴定'; COMMENT ON COLUMN tms_transfer_eval.f_id IS '主键'; COMMENT ON COLUMN tms_transfer_eval.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_transfer_eval.user_id IS '转岗人员'; COMMENT ON COLUMN tms_transfer_eval.from_post_id IS '原岗位'; COMMENT ON COLUMN tms_transfer_eval.to_post_id IS '新岗位'; COMMENT ON COLUMN tms_transfer_eval.eval_result IS '鉴定结论'; COMMENT ON COLUMN tms_transfer_eval.remark IS '说明'; COMMENT ON COLUMN tms_transfer_eval.biz_status IS '审批状态'; COMMENT ON COLUMN tms_transfer_eval.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_transfer_eval.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_transfer_eval.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_transfer_eval.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_transfer_eval.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_transfer_eval.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_transfer_eval.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_transfer_eval.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_transfer_eval.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_transfer_eval.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_teval_user ON tms_transfer_eval (user_id); -- 新员工培训效果考评 CREATE TABLE IF NOT EXISTS tms_newcomer_eval ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), user_id varchar(50) NOT NULL, eval_result varchar(50), remark text, biz_status varchar(20), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_newcomer_eval PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_newcomer_eval IS '新员工培训效果考评'; COMMENT ON COLUMN tms_newcomer_eval.f_id IS '主键'; COMMENT ON COLUMN tms_newcomer_eval.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_newcomer_eval.user_id IS '新员工'; COMMENT ON COLUMN tms_newcomer_eval.eval_result IS '考评结论'; COMMENT ON COLUMN tms_newcomer_eval.remark IS '说明'; COMMENT ON COLUMN tms_newcomer_eval.biz_status IS '审批状态'; COMMENT ON COLUMN tms_newcomer_eval.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_newcomer_eval.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_newcomer_eval.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_newcomer_eval.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_newcomer_eval.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_newcomer_eval.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_newcomer_eval.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_newcomer_eval.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_newcomer_eval.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_newcomer_eval.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_neval_user ON tms_newcomer_eval (user_id); -- 复岗员工培训效果考评 CREATE TABLE IF NOT EXISTS tms_return_eval ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), user_id varchar(50) NOT NULL, eval_result varchar(50), remark text, biz_status varchar(20), f_flow_id varchar(50), f_flow_task_id varchar(50), f_flow_state int, f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_return_eval PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_return_eval IS '复岗员工培训效果考评'; COMMENT ON COLUMN tms_return_eval.f_id IS '主键'; COMMENT ON COLUMN tms_return_eval.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_return_eval.user_id IS '复岗员工'; COMMENT ON COLUMN tms_return_eval.eval_result IS '考评结论'; COMMENT ON COLUMN tms_return_eval.remark IS '说明'; COMMENT ON COLUMN tms_return_eval.biz_status IS '审批状态'; COMMENT ON COLUMN tms_return_eval.f_flow_id IS '流程模板ID'; COMMENT ON COLUMN tms_return_eval.f_flow_task_id IS '流程任务ID'; COMMENT ON COLUMN tms_return_eval.f_flow_state IS '流程状态'; COMMENT ON COLUMN tms_return_eval.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_return_eval.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_return_eval.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_return_eval.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_return_eval.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_return_eval.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_return_eval.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_reval_user ON tms_return_eval (user_id); -- 培训记录套打模板版本。按培训开始时间匹配生效版本。 CREATE TABLE IF NOT EXISTS tms_print_template ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), tpl_code varchar(50) NOT NULL, version_no int NOT NULL, effective_from date NOT NULL, effective_to date, file_json text, enabled varchar(10) NOT NULL DEFAULT '1', f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_print_template PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_print_template IS '培训记录套打模板版本。按培训开始时间匹配生效版本。'; COMMENT ON COLUMN tms_print_template.f_id IS '主键'; COMMENT ON COLUMN tms_print_template.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_print_template.tpl_code IS '模板编码，如 TRAIN_RECORD'; COMMENT ON COLUMN tms_print_template.version_no IS '版本号'; COMMENT ON COLUMN tms_print_template.effective_from IS '生效起始（按培训开始时间索引）'; COMMENT ON COLUMN tms_print_template.effective_to IS '生效截止，空=当前'; COMMENT ON COLUMN tms_print_template.file_json IS '模板文件'; COMMENT ON COLUMN tms_print_template.enabled IS '启用'; COMMENT ON COLUMN tms_print_template.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_print_template.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_print_template.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_print_template.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_print_template.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_print_template.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_print_template.f_delete_time IS '删除时间'; CREATE INDEX IF NOT EXISTS idx_tms_ptpl_code ON tms_print_template (tpl_code, effective_from); -- 提醒规则：计划到期前一个月、授权到期、个人岗位培训时限等 CREATE TABLE IF NOT EXISTS tms_remind_rule ( f_id varchar(50) NOT NULL, f_tenant_id varchar(50), rule_code varchar(50) NOT NULL, rule_name varchar(100), biz_type varchar(50), days_before int NOT NULL DEFAULT 30, notify_roles varchar(200), enabled varchar(10) NOT NULL DEFAULT '1', f_creator_user_id varchar(50), f_creator_time timestamp, f_last_modify_user_id varchar(50), f_last_modify_time timestamp, f_delete_mark int NOT NULL DEFAULT 0, f_delete_user_id varchar(50), f_delete_time timestamp, CONSTRAINT pk_tms_remind_rule PRIMARY KEY (f_id) ); COMMENT ON TABLE tms_remind_rule IS '提醒规则：计划到期前一个月、授权到期、个人岗位培训时限等'; COMMENT ON COLUMN tms_remind_rule.f_id IS '主键'; COMMENT ON COLUMN tms_remind_rule.f_tenant_id IS '租户'; COMMENT ON COLUMN tms_remind_rule.rule_code IS '规则编码'; COMMENT ON COLUMN tms_remind_rule.rule_name IS '名称'; COMMENT ON COLUMN tms_remind_rule.biz_type IS 'annual_plan/admin_auth/person_post/task_overdue'; COMMENT ON COLUMN tms_remind_rule.days_before IS '提前天数'; COMMENT ON COLUMN tms_remind_rule.notify_roles IS '通知角色：部门培训管理员,培训师,部门领导'; COMMENT ON COLUMN tms_remind_rule.enabled IS '启用'; COMMENT ON COLUMN tms_remind_rule.f_creator_user_id IS '创建人'; COMMENT ON COLUMN tms_remind_rule.f_creator_time IS '创建时间'; COMMENT ON COLUMN tms_remind_rule.f_last_modify_user_id IS '修改人'; COMMENT ON COLUMN tms_remind_rule.f_last_modify_time IS '修改时间'; COMMENT ON COLUMN tms_remind_rule.f_delete_mark IS '删除标记 0正常 1删除'; COMMENT ON COLUMN tms_remind_rule.f_delete_user_id IS '删除人'; COMMENT ON COLUMN tms_remind_rule.f_delete_time IS '删除时间'; CREATE UNIQUE INDEX IF NOT EXISTS uk_tms_remind_code ON tms_remind_rule (rule_code) WHERE f_delete_mark = 0; -- ============================================================================= -- 预置数据（培训方式 / 签到方式 / 评估方式 / 提醒规则） -- ============================================================================= 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;