riskctrl-server-fixed/script/sql/person_info.sql

-- ----------------------------
-- 人员信息表
-- ----------------------------
DROP TABLE IF EXISTS person_info;
CREATE TABLE person_info (
    person_id         BIGINT        NOT NULL AUTO_INCREMENT COMMENT '人员ID',
    person_type       VARCHAR(20)   NOT NULL COMMENT '人员类型(字典: person_type)',
    person_name       VARCHAR(50)   NOT NULL COMMENT '姓名',
    id_card           VARCHAR(18)   NOT NULL COMMENT '身份证号',
    age               INT           DEFAULT NULL COMMENT '年龄',
    work_store_id     BIGINT        DEFAULT NULL COMMENT '工作门店ID',
    work_store_name   VARCHAR(100)  DEFAULT NULL COMMENT '工作门店名称',
    insurance_status  VARCHAR(1)    DEFAULT '0' COMMENT '保险状态(0未投保 1已投保)',
    insurance_expire_date DATE      DEFAULT NULL COMMENT '保险有效期',
    certificates      TEXT          DEFAULT NULL COMMENT '岗位证书(JSON数组)',
    status            VARCHAR(1)    DEFAULT '0' COMMENT '状态(0正常 1停用)',
    del_flag          VARCHAR(1)    DEFAULT '0' COMMENT '删除标志(0存在 1删除)',
    create_dept       BIGINT        DEFAULT NULL COMMENT '创建部门',
    create_by         BIGINT        DEFAULT NULL COMMENT '创建者',
    create_time       DATETIME      DEFAULT NULL COMMENT '创建时间',
    update_by         BIGINT        DEFAULT NULL COMMENT '更新者',
    update_time       DATETIME      DEFAULT NULL COMMENT '更新时间',
    remark            VARCHAR(500)  DEFAULT NULL COMMENT '备注',
    PRIMARY KEY (person_id),
    KEY idx_person_type (person_type),
    KEY idx_work_store_id (work_store_id),
    KEY idx_insurance_status (insurance_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员信息表';

-- 人员类型字典
INSERT INTO sys_dict_type (dict_id, tenant_id, dict_name, dict_type, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_id), 0) + 1, '000000', '人员类型', 'person_type', 1, NOW(), 1, NOW(), NULL
FROM sys_dict_type
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'person_type');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 1, '防损人员', '1', 'person_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'person_type' AND dict_value = '1');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 2, '施工人员', '2', 'person_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'person_type' AND dict_value = '2');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 3, '特岗人员', '3', 'person_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'person_type' AND dict_value = '3');

-- 岗位证书类型字典
INSERT INTO sys_dict_type (dict_id, tenant_id, dict_name, dict_type, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_id), 0) + 1, '000000', '岗位证书类型', 'certificate_type', 1, NOW(), 1, NOW(), NULL
FROM sys_dict_type
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'certificate_type');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 1, '特种作业操作证', '1', 'certificate_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'certificate_type' AND dict_value = '1');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 2, '安全生产管理人员证', '2', 'certificate_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'certificate_type' AND dict_value = '2');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 3, '电工证', '3', 'certificate_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'certificate_type' AND dict_value = '3');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 4, '焊工证', '4', 'certificate_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'certificate_type' AND dict_value = '4');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 5, '高处作业证', '5', 'certificate_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'certificate_type' AND dict_value = '5');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 6, '消防安全管理员证', '6', 'certificate_type', '', 'default', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'certificate_type' AND dict_value = '6');

-- 保险状态字典
INSERT INTO sys_dict_type (dict_id, tenant_id, dict_name, dict_type, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_id), 0) + 1, '000000', '保险状态', 'insurance_status', 1, NOW(), 1, NOW(), NULL
FROM sys_dict_type
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'insurance_status');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 1, '已投保', '1', 'insurance_status', '', 'success', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'insurance_status' AND dict_value = '1');

INSERT INTO sys_dict_data (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_by, create_time, update_by, update_time, remark)
SELECT IFNULL(MAX(dict_code), 0) + 1, 2, '未投保', '0', 'insurance_status', '', 'danger', 'N', 1, NOW(), 1, NOW(), NULL FROM sys_dict_data
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'insurance_status' AND dict_value = '0');