-- =============================================================================
|
-- 玻思韬 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;
|
