# 数据库
    DELETE from t_sys_rule where rule_id = '3c66aef141bc47d48e4214f08a057b72';

    CREATE INDEX idx_job_code ON t_job_statistics(CODE);
    CREATE INDEX idx_job_status ON t_job_statistics(status);
    CREATE INDEX idx_job_dhzy_zykssj ON t_job_statistics(DHZY_ZYKSSJ);
    CREATE INDEX idx_job_dhzy_zyjssj ON t_job_statistics(DHZY_ZYJSSJ);
    
    CREATE INDEX idx_org_org_code ON t_sys_org(org_code);
    CREATE INDEX idx_org_org_id ON t_sys_org(org_id);
    CREATE INDEX idx_org_parent_id ON t_sys_org(parent_id);
    CREATE INDEX idx_org_tree_level ON t_sys_org(tree_level);
    
    
    
    -- 为 t_safe_risk_source 表的 Inherent_level_id 字段添加索引
    CREATE INDEX idx_inherent_level_id ON t_safe_risk_source (Inherent_level_id);
    
    -- 为 t_safe_risk_source 表的 real_level_id 字段添加索引
    CREATE INDEX idx_real_level_id ON t_safe_risk_source (real_level_id);
    
    -- 为 t_safe_risk_source 表的 org_id 字段添加索引
    CREATE INDEX idx_org_id ON t_safe_risk_source (org_id);
    
    -- 为 t_safe_risk_source 表的 status 字段添加索引
    CREATE INDEX idx_status ON t_safe_risk_source (status);
    
    -- 为 t_sys_dict_data 表的 dict_data_id 字段添加索引
    CREATE INDEX idx_dict_data_id ON t_sys_dict_data (dict_data_id); 

    CREATE OR REPLACE VIEW act_id_group AS
    SELECT
    NULL AS REV_,
    CONCAT(r.tree_level, '-', r.role_id) AS ID_,
    CASE
    WHEN r.tree_level = 1 THEN CONCAT('总公司', '-', r.role_name)
    WHEN r.tree_level = 2 THEN CONCAT('区域公司', '-', r.role_name)
    WHEN r.tree_level = 3 THEN CONCAT('粮库公司', '-', r.role_name)
    ELSE r.role_name
    END AS NAME_,
    r.tree_level AS TYPE_
    FROM
    t_sys_role r
    WHERE
    r.status = 0 AND r.tree_level IS NOT NULL
    ORDER BY
    r.tree_level;   

    CREATE OR REPLACE VIEW act_id_user AS
    SELECT
    su.user_id AS ID_,
    NULL AS REV_,
    su.user_name AS FIRST_,
    NULL AS LAST_,
    su.email AS EMAIL_,
    su.login_pwd AS PWD_,
    NULL AS PICTURE_ID_
    FROM
    t_sys_user su;

    CREATE OR REPLACE VIEW act_id_membership AS
    SELECT
    u.user_id AS USER_ID_,
    CONCAT(u.org_id, '-', ur.role_id) AS GROUP_ID_
    FROM
    t_sys_user_role ur
    JOIN
    t_sys_user u
    ON
    ur.user_id = u.user_id
    WHERE
    u.status = 0;





  



# 模型需要重新部署



# 验证数据库完成性 

1 查询表数量 309  
2 视图 
3 流程相关的表 数量   79 
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'zlmy'  
 AND TABLE_NAME LIKE 'act%';  



SELECT  TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'zlmy'     AND TABLE_NAME LIKE 'act%' order by TABLE_NAME  ;


SELECT TABLE_NAME AS view_name  FROM information_schema.views  WHERE TABLE_SCHEMA = 'zlmy';
+-------------------+
| view_name         |
+-------------------+
| act_id_group      |
| act_id_membership |
| act_id_user       |
+-------------------+






# 下次上线(标记于20250710)
ALTER TABLE public.t_accident_report ALTER COLUMN report_unit TYPE varchar(1000) USING report_unit::varchar;
ALTER TABLE public.t_accident_report ALTER COLUMN attachment TYPE varchar(1000) USING attachment::varchar;




# 北良按部门指定流程图
update t_sys_config set status = '1' where config_id in('1','2');

INSERT INTO "public"."t_sys_config" ("config_id", "config_name", "config_key", "config_value", "config_type", "status", "create_by", "create_date", "update_by", "update_date", "remarks") VALUES ('3', '北良公司-公司级机构ids', 'sys.beiliang.orgs', '74ba35d937a3403ca59e37d238a0d93f,dafc181b7b314f8fb26c277afcbfeafc,bf729a8807ae4804baf5b719889a1b10,640323dc46334c8d8467a6d1889f90b1,8be46ac128e24c658ebc890f9da7818a
,71ccfa9b528c43eba3c374990472c5f4,703c7a0dbbe84d768bcb175905d3140d,a072abc298464c32a66761acbbf851be', 'N', '0', NULL, '2024-12-24 15:29:54', NULL, '2024-12-24 15:29:54', NULL);
INSERT INTO "public"."t_sys_config" ("config_id", "config_name", "config_key", "config_value", "config_type", "status", "create_by", "create_date", "update_by", "update_date", "remarks") VALUES ('4', '北良公司-部门级机构ids', 'sys.beiliang.depts', 'bc8047cac1214b558ed9465a92cf3f27,51555a47ff6a42c0a75814fee3e60676,97180fe2554b4f2a9ec6d1cf213b7f67,ef4e50ad1da64b9ba0e9c894faca4487,b66916fcfad44412a17aa3f641cd2ad5
,b828ca4431854e699195888466057955,e5a463b08c9d4e54bd47ed416941380c,7ba23b0151cb49578cd3abd5ceea72ac,d4185815c13544c3950416d77cd6d65c,164b5cf61efe4a269911e2097eb997f9
,49d90fa9ddac4b94928afc126f72161c,b5e5a052640f4a5b82b0ce7ef0f31bb0,b82dc615aecb4b2dae28d82ce8eb4693,bed4208a45254ff8b072e3737cdbe85f,c4396dd90e964a208a9992186632ff59
,5c7b5e799ae6482bae0ca375e64210de,6f7549e8402643beb79e4f726722ed27,6ceddfaddf20442ca861f58ecb139012,311ee5aebc884803a0d0e1353db805cd,1335dbc3f81b4919bf59014fede4819d', 'N', '0', NULL, '2024-12-24 15:29:54', NULL, '2024-12-24 15:29:54', NULL);





# 20251106上线需求



ALTER TABLE t_contractor_person ADD COLUMN contract_period varchar(200) NULL; -- 合同期限
COMMENT ON COLUMN public.t_contractor_person.contract_period IS '合同期限';





-- public.t_contractor_access_log definition

-- Drop table

-- DROP TABLE public.t_contractor_access_log;

CREATE TABLE public.t_contractor_access_log (
id varchar(64) NOT NULL, -- 主键ID
contractor_allow_id varchar(1000) NULL, -- 准入管理对象ID
person_name varchar(50) NULL, -- 人员姓名
gender varchar(255) NULL, -- 性别
id_card_no varchar(20) NULL, -- 身份证号
age int4 NULL, -- 年龄
job_nature_id varchar(64) NULL, -- 岗位性质id（数据字典id）
job_nature varchar(50) NULL, -- 岗位性质
job_title_id varchar(64) NULL, -- 岗位名称id（数据字典id）
job_title varchar(50) NULL, -- 岗位名称
tel varchar(20) NULL, -- 联系电话
status varchar(1) NULL DEFAULT '0'::varchar, -- 0 正常 1 删除 2 停用
org_id text NULL, -- 所属企业id
tools_name varchar(50) NULL, -- 工器具名称
tools_type_id varchar(64) NULL, -- 工器具类别id（数据字典id）
tools_type varchar(50) NULL, -- 工器具类别
tools_model varchar(100) NULL, -- 工器具型号
tools_num int4 NULL, -- 工器具数量
tools_purpose varchar(200) NULL, -- 工器具用途
tools_is_pass bpchar(1) NULL, -- 工器具是否合格 0 不合格 1 合格
material_name varchar(100) NULL, -- 物料名称
material_type_id varchar(64) NULL, -- 物料类别id（数据字典id）
material_type varchar(50) NULL, -- 物料类别
risk_id varchar(64) NULL, -- 危险性id（数据字典id）
risk varchar(100) NULL, -- 危险性
material_model varchar(100) NULL, -- 物料型号
material_num int4 NULL, -- 物料数量
material_purpose varchar(200) NULL, -- 物料用途
equipment_name varchar(100) NULL, -- 防护用品名称
type_id varchar(64) NULL, -- 防护用品类别id（数据字典id）
equipment_type varchar(50) NULL, -- 防护用品类别
is_general bpchar(1) NULL, -- 常规用品 0否 1是
equipment_model varchar(100) NULL, -- 防护用品型号
equipment_num int4 NULL, -- 防护用品数量
equipment_purpose varchar(200) NULL, -- 防护用品用途
batch varchar(50) NULL, -- 批次
remarks varchar(255) NULL, -- 备注
create_by varchar(64) NULL, -- 创建者
create_date sys."timestamp" NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
update_by varchar(64) NULL, -- 更新者
update_date sys."timestamp" NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 更新时间
"type" varchar(50) NULL -- 修改项(1:人员 2:工器具 3:防护用品数量 4:防护用品数量)
);
COMMENT ON TABLE public.t_contractor_access_log IS '承包商准入管理访问日志';

-- Column comments

COMMENT ON COLUMN public.t_contractor_access_log.id IS '主键ID';
COMMENT ON COLUMN public.t_contractor_access_log.contractor_allow_id IS '准入管理对象ID';
COMMENT ON COLUMN public.t_contractor_access_log.person_name IS '人员姓名';
COMMENT ON COLUMN public.t_contractor_access_log.gender IS '性别';
COMMENT ON COLUMN public.t_contractor_access_log.id_card_no IS '身份证号';
COMMENT ON COLUMN public.t_contractor_access_log.age IS '年龄';
COMMENT ON COLUMN public.t_contractor_access_log.job_nature_id IS '岗位性质id（数据字典id）';
COMMENT ON COLUMN public.t_contractor_access_log.job_nature IS '岗位性质';
COMMENT ON COLUMN public.t_contractor_access_log.job_title_id IS '岗位名称id（数据字典id）';
COMMENT ON COLUMN public.t_contractor_access_log.job_title IS '岗位名称';
COMMENT ON COLUMN public.t_contractor_access_log.tel IS '联系电话';
COMMENT ON COLUMN public.t_contractor_access_log.status IS '0 正常 1 删除 2 停用';
COMMENT ON COLUMN public.t_contractor_access_log.org_id IS '所属企业id';
COMMENT ON COLUMN public.t_contractor_access_log.tools_name IS '工器具名称';
COMMENT ON COLUMN public.t_contractor_access_log.tools_type_id IS '工器具类别id（数据字典id）';
COMMENT ON COLUMN public.t_contractor_access_log.tools_type IS '工器具类别';
COMMENT ON COLUMN public.t_contractor_access_log.tools_model IS '工器具型号';
COMMENT ON COLUMN public.t_contractor_access_log.tools_num IS '工器具数量';
COMMENT ON COLUMN public.t_contractor_access_log.tools_purpose IS '工器具用途';
COMMENT ON COLUMN public.t_contractor_access_log.tools_is_pass IS '工器具是否合格 0 不合格 1 合格';
COMMENT ON COLUMN public.t_contractor_access_log.material_name IS '物料名称';
COMMENT ON COLUMN public.t_contractor_access_log.material_type_id IS '物料类别id（数据字典id）';
COMMENT ON COLUMN public.t_contractor_access_log.material_type IS '物料类别';
COMMENT ON COLUMN public.t_contractor_access_log.risk_id IS '危险性id（数据字典id）';
COMMENT ON COLUMN public.t_contractor_access_log.risk IS '危险性';
COMMENT ON COLUMN public.t_contractor_access_log.material_model IS '物料型号';
COMMENT ON COLUMN public.t_contractor_access_log.material_num IS '物料数量';
COMMENT ON COLUMN public.t_contractor_access_log.material_purpose IS '物料用途';
COMMENT ON COLUMN public.t_contractor_access_log.equipment_name IS '防护用品名称';
COMMENT ON COLUMN public.t_contractor_access_log.type_id IS '防护用品类别id（数据字典id）';
COMMENT ON COLUMN public.t_contractor_access_log.equipment_type IS '防护用品类别';
COMMENT ON COLUMN public.t_contractor_access_log.is_general IS '常规用品 0否 1是';
COMMENT ON COLUMN public.t_contractor_access_log.equipment_model IS '防护用品型号';
COMMENT ON COLUMN public.t_contractor_access_log.equipment_num IS '防护用品数量';
COMMENT ON COLUMN public.t_contractor_access_log.equipment_purpose IS '防护用品用途';
COMMENT ON COLUMN public.t_contractor_access_log.batch IS '批次';
COMMENT ON COLUMN public.t_contractor_access_log.remarks IS '备注';
COMMENT ON COLUMN public.t_contractor_access_log.create_by IS '创建者';
COMMENT ON COLUMN public.t_contractor_access_log.create_date IS '创建时间';
COMMENT ON COLUMN public.t_contractor_access_log.update_by IS '更新者';
COMMENT ON COLUMN public.t_contractor_access_log.update_date IS '更新时间';
COMMENT ON COLUMN public.t_contractor_access_log."type" IS '修改项(1:人员 2:工器具 3:防护用品数量 4:防护用品数量)';


-- public.t_contractor_access_log foreign keys



-- public.t_contractor_access_log_approval definition

-- Drop table

-- DROP TABLE public.t_contractor_access_log_approval;

CREATE TABLE public.t_contractor_access_log_approval (
id varchar(64) NOT NULL, -- 主键ID
access_log_id varchar(64) NULL, -- 承包商准入管理访问日志ID
"name" varchar(50) NULL, -- 审核人姓名
approval_content varchar(500) NULL, -- 审核人内容
approval_status varchar(50) NULL, -- 审批状态(1:通过 2:进行中 3:驳回)
status varchar(1) NULL DEFAULT '0'::varchar, -- 0 正常 1 删除 2 停用
remarks varchar(255) NULL,
create_by varchar(64) NULL, -- 创建者
create_date sys."timestamp" NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
update_by varchar(64) NULL, -- 更新者
update_date sys."timestamp" NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 更新时间
"type" varchar(50) NULL, -- 修改项(1:人员 2:工器具 3:防护用品数量 4:防护用品数量)
contractor_allow_id varchar(64) NULL, -- 准入管理对象ID
name_by varchar(500) NULL
);
COMMENT ON TABLE public.t_contractor_access_log_approval IS '承包商准入管理访问日志审批表';

-- Column comments

COMMENT ON COLUMN public.t_contractor_access_log_approval.id IS '主键ID';
COMMENT ON COLUMN public.t_contractor_access_log_approval.access_log_id IS '承包商准入管理访问日志ID';
COMMENT ON COLUMN public.t_contractor_access_log_approval."name" IS '审核人姓名';
COMMENT ON COLUMN public.t_contractor_access_log_approval.approval_content IS '审核人内容';
COMMENT ON COLUMN public.t_contractor_access_log_approval.approval_status IS '审批状态(1:通过 2:进行中 3:驳回)';
COMMENT ON COLUMN public.t_contractor_access_log_approval.status IS '0 正常 1 删除 2 停用';
COMMENT ON COLUMN public.t_contractor_access_log_approval.create_by IS '创建者';
COMMENT ON COLUMN public.t_contractor_access_log_approval.create_date IS '创建时间';
COMMENT ON COLUMN public.t_contractor_access_log_approval.update_by IS '更新者';
COMMENT ON COLUMN public.t_contractor_access_log_approval.update_date IS '更新时间';
COMMENT ON COLUMN public.t_contractor_access_log_approval."type" IS '修改项(1:人员 2:工器具 3:防护用品数量 4:防护用品数量)';
COMMENT ON COLUMN public.t_contractor_access_log_approval.contractor_allow_id IS '准入管理对象ID';


-- public.t_contractor_access_log_approval foreign keys