soc/project_data/sql/1.0.14/soc/translate.sql

-- 已完成的人员数据迁移
START TRANSACTION; -- 开启事务：确保迁移要么全成功，要么全回滚（安全兜底）

-- 2025-11-6 : web端下发的人员并入到历史库中
INSERT INTO core_introduce_letter_out_in_history_user (id,
                                                       org_id,
                                                       user_name,
                                                       company_name,
                                                       id_type,
                                                       id_card,
                                                       img_file,
                                                       out_in_request_id,
                                                       create_by,
                                                       create_time,
                                                       update_by,
                                                       update_time)
select lu.id,
       le.org_id,
       lu.user_name,
       lu.company_name,
       lu.id_type,
       lu.id_card,
       lu.img_file,
       le.id,
       lu.create_by,
       lu.create_time,
       lu.create_by,
       lu.create_time
from (select *,
             ROW_NUMBER() over (
                 partition by user_name,company_name,id_card,id_type
                 order by create_time desc
                 ) as rn
      from core_introduce_letter_user) lu
         inner join core_introduce_letter le on lu.letter_id = le.id

where lu.rn = 1
  and lu.id in (select a.id
                from core_introduce_letter_user a
                         inner join core_introduce_letter b on a.letter_id = b.id
                where b.type = 1
                  and b.deleted = 0 );

INSERT INTO core_introduce_letter_out_in_history_user (id, -- 复用源表reu.id（新表无数据，无冲突）
                                                       org_id, -- 接待机构ID（来自core_out_in_record）
                                                       user_name, -- 人员姓名
                                                       company_name, -- 所属公司
                                                       id_type, -- 证件类型
                                                       id_card, -- 证件号码
                                                       img_file, -- 图片文件路径
                                                       out_in_request_id, -- 关联的出入请求ID（来自core_out_in_record）
                                                       create_by, -- 创建人
                                                       create_time, -- 创建时间
                                                       update_by, -- 修改人（默认用创建人填充）
                                                       update_time -- 修改时间（默认用创建时间填充）
)
select reu.id,
       rec.org_id,
       reu.user_name,
       reu.company_name,
       reu.id_type,
       reu.id_card,
       reu.img_file,
       rec.out_in_request_id,
       reu.create_by,
       reu.create_time,
       reu.create_by,  -- update_by 用 create_by 填充
       reu.create_time -- update_time 用 create_time 填充
-- 去重
FROM (
         SELECT *,
                ROW_NUMBER() OVER (
                    PARTITION BY user_name, company_name, id_card, id_type
                    ORDER BY create_time DESC
                    ) as rn
         FROM core_introduce_letter_out_in_request_user
     ) reu
         INNER JOIN core_out_in_record rec ON rec.out_in_request_user_id = reu.id
WHERE reu.rn = 1
  AND reu.id IN (
    SELECT a.out_in_request_user_id
    FROM core_out_in_record a
             INNER JOIN core_introduce_letter_out_in_request b ON a.out_in_request_id = b.id
    WHERE (a.status = 1 AND a.pdf_url IS NOT NULL)
       OR (a.status = 1 AND a.departure_time IS NOT NULL)
);
-- 确认数据无误后，提交事务（若发现数据不对，执行 ROLLBACK; 回滚）
COMMIT;

-- 修改介绍信出入类型  将 电子、纸质 统一改为 普通 , 由于迁移 下发人员到历史库时需要 介绍信出入类型，故此处修改
update  core_introduce_letter set type =2 where type=1;


DROP TABLE if exists `core_introduce_letter_out_in_request_translate`;
CREATE TABLE `core_introduce_letter_out_in_request_translate` (
    `id` bigint NOT NULL,
    `letter_id` bigint DEFAULT NULL COMMENT '介绍信id',
    `approve_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '审核人',
    `approve_user_id` bigint DEFAULT NULL COMMENT '审核人Id',
    `approve_time` datetime DEFAULT NULL COMMENT '审核时间',
    `approve_status` int DEFAULT NULL COMMENT '审核状态:0待审批1通过2不通过3逾期',
    `approve_remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
    `approve_sign_img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '审批人签名',
    `org_id` bigint DEFAULT NULL COMMENT '接待机构',
    `letter_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '介绍信名称',
    `letter_file` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '介绍信附件',
    `letter_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '介绍信编号',
    `letter_description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '介绍信备注',
    `letter_reasons` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '出入事由',
    `letter_type` int DEFAULT NULL COMMENT '类型： 2 普通出入，3 紧急出入',
    `deleted` int DEFAULT '0' COMMENT '0正常1删除',
    `start_time` datetime DEFAULT NULL COMMENT '有效开始时间，开具时间',
    `end_time` datetime DEFAULT NULL COMMENT '有效结束时间',
    `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `effective_days` int DEFAULT NULL COMMENT '有效天数',
    `status` int DEFAULT NULL COMMENT '出入状态 ：0待审批 1 待登记 2 已完成 3 已拒绝 4 已过期 5已补登',
    `accompanying_person` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '陪同人员',
    `check_sign` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '核对人签名',
    `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注信息',
    `remark_image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注照片',
    `pdf_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '登记簿url',
    PRIMARY KEY (`id`),
    KEY `index_letter_id` (`letter_id`) USING BTREE,
    KEY `index_org_id` (`org_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='出入申请表';

-- request表数据迁移
DELIMITER ??
DROP PROCEDURE IF EXISTS MigrateDataWithCursorTimeBatchOptimized ??
CREATE PROCEDURE MigrateDataWithCursorTimeBatchOptimized()
BEGIN
    -- 变量声明（包含所有字段）
    DECLARE done INT DEFAULT FALSE;

    -- record表字段
    DECLARE v_record_id BIGINT;
    DECLARE v_old_request_id BIGINT;
    DECLARE v_user_id BIGINT;
    DECLARE v_arrival_time DATETIME;
    DECLARE v_departure_time DATETIME;
    DECLARE v_record_status INT;
    DECLARE v_pdf_url VARCHAR(255);
    DECLARE v_accompanying_person VARCHAR(255);
    DECLARE v_submit_sign VARCHAR(255);
    DECLARE v_remark VARCHAR(255);
    DECLARE v_check_image VARCHAR(255);

    -- request表字段
    DECLARE v_req_letter_id BIGINT;
    DECLARE v_req_approve_user VARCHAR(255);
    DECLARE v_req_approve_user_id BIGINT;
    DECLARE v_req_approve_time DATETIME;
    DECLARE v_req_approve_status INT;
    DECLARE v_req_approve_remark VARCHAR(255);
    DECLARE v_req_approve_sign_img VARCHAR(255);
    DECLARE v_req_org_id BIGINT;
    DECLARE v_req_letter_name VARCHAR(255);
    DECLARE v_req_letter_file TEXT;
    DECLARE v_req_letter_no VARCHAR(20);
    DECLARE v_req_letter_description VARCHAR(500);
    DECLARE v_req_letter_reasons VARCHAR(100);
    DECLARE v_req_letter_type INT;
    DECLARE v_req_deleted INT;
    DECLARE v_req_start_time DATETIME;
    DECLARE v_req_end_time DATETIME;
    DECLARE v_req_create_by VARCHAR(50);
    DECLARE v_req_create_time DATETIME;
    DECLARE v_req_update_by VARCHAR(50);
    DECLARE v_req_update_time DATETIME;
    DECLARE v_req_effective_days INT;

    -- 其他变量
    DECLARE v_final_deleted INT; -- 最终要插入的deleted值
    DECLARE v_new_request_id BIGINT;
    declare v_calculated_new_id bigint;
    DECLARE v_start_time DATETIME;
    DECLARE v_end_time DATETIME;
    DECLARE v_current_time DATETIME;
    DECLARE v_next_time DATETIME;
    DECLARE v_batch_count INT DEFAULT 0;
    DECLARE v_total_processed INT DEFAULT 0;
    DECLARE v_batch_start_time DATETIME;
    DECLARE v_batch_duration INT;

    -- 创建临时表用于批量更新
DROP TABLE if exists `temp_record_updates`;
CREATE TEMPORARY TABLE temp_record_updates (
                                                   record_id BIGINT PRIMARY KEY,
                                                   new_request_id BIGINT
    );
DROP TABLE if exists `temp_user_updates`;
CREATE TEMPORARY TABLE temp_user_updates (
                                                 user_id BIGINT PRIMARY KEY,
                                                 new_request_id bigint,
                                                 arrival_time DATETIME,
                                                 departure_time DATETIME
    );
    -- 添加全局计数器
    SET @global_row_number = 0;
    -- 获取时间范围和最大ID
SELECT MIN(create_time), MAX(create_time) INTO v_start_time, v_end_time
FROM core_out_in_record;

SET @max_request_id = (SELECT COALESCE(MAX(id), 0) FROM core_introduce_letter_out_in_request);

    SET v_current_time = v_start_time;

    -- 按月分批处理
    WHILE v_current_time <= v_end_time DO

            SET v_next_time = DATE_ADD(v_current_time, INTERVAL 1 MONTH);
            SET v_batch_count = v_batch_count + 1;
            SET v_batch_start_time = NOW();

            #         SELECT CONCAT('处理批次 ', v_batch_count, ': ', DATE_FORMAT(v_current_time, '%Y-%m')) as 批次开始;

-- 开始事务
START TRANSACTION;

BEGIN
                -- 优化后的游标：一次性JOIN获取所有数据
                DECLARE cur_records CURSOR FOR
SELECT a.id,
       a.out_in_request_id,
       a.out_in_request_user_id,
       a.arrival_time,
       a.departure_time,
       a.status,
       a.pdf_url,
       a.accompanying_person,
       a.submit_sign,
       a.remark,
       a.check_image,
       b.letter_id,
       b.approve_user,
       b.approve_user_id,
       b.approve_time,
       b.approve_status,
       b.approve_remark,
       b.approve_sign_img,
       b.org_id,
       b.letter_name,
       b.letter_file,
       b.letter_no,
       b.letter_description,
       b.letter_reasons,
       b.letter_type,
       b.deleted,
       b.start_time,
       b.end_time,
       b.create_by,
       b.create_time,
       b.update_by,
       b.update_time,
       b.effective_days
           #                        (@max_request_id + ROW_NUMBER() over (order by a.id)) as calculated_new_id
FROM core_out_in_record a
         INNER JOIN core_introduce_letter_out_in_request b ON a.out_in_request_id = b.id
WHERE a.create_time >= v_current_time
  AND a.create_time < v_next_time
ORDER BY a.id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_records;
read_loop: LOOP
                    FETCH cur_records INTO
                        v_record_id, v_old_request_id, v_user_id, v_arrival_time,
                        v_departure_time, v_record_status, v_pdf_url,
                        v_accompanying_person, v_submit_sign, v_remark, v_check_image,
                        v_req_letter_id, v_req_approve_user, v_req_approve_user_id, v_req_approve_time,
                        v_req_approve_status, v_req_approve_remark, v_req_approve_sign_img, v_req_org_id,
                        v_req_letter_name, v_req_letter_file, v_req_letter_no, v_req_letter_description,
                        v_req_letter_reasons, v_req_letter_type, v_req_deleted, v_req_start_time, v_req_end_time,
                        v_req_create_by, v_req_create_time, v_req_update_by, v_req_update_time, v_req_effective_days;

                    IF done THEN LEAVE read_loop; END IF;

                    -- 生成新ID并插入
#                 SET v_new_request_id = v_new_request_id + 1;
                    SET @global_row_number = @global_row_number + 1;
                    SET v_calculated_new_id = @max_request_id + @global_row_number;

                    -- 根据状态设置deleted字段
                    SET v_final_deleted = CASE
                        -- 已过期-1 : 过去当天申请，但是没有审批，过去的当天作废 → 已删除
                                              WHEN v_req_approve_status = 3 AND v_record_status = 3 THEN 1
                        -- 已过期-2 ： 过去当天审批通过了，但是没有填写结果 → 已删除
                                              WHEN v_req_approve_status = 1 AND v_record_status = 3 AND v_arrival_time IS NULL THEN 1
                        -- 已过期-3 : 审批通过，当天记录没有闭环 → 已删除
                                              WHEN v_req_approve_status = 1 AND v_record_status = 3 AND v_arrival_time IS NOT NULL THEN 1
                        -- 待登记-3 : 当天提交，当天审批通过的记录 → 已删除
                                              WHEN v_req_approve_status = 1 AND v_record_status = 1 AND v_arrival_time IS NULL AND v_departure_time IS NULL THEN 1
                        -- 待登记-1 : 历史待登记 : 已经完成进入时间填写的申请记录 → 已删除
                                              WHEN v_req_approve_status = 1 AND v_record_status = 1 AND v_arrival_time IS NOT NULL AND v_departure_time IS NULL THEN 1
                        #                         -- 待登记-2 : 离开时间和PDF是否有生成 为已完成等价条件 → 已删除
#                                               WHEN v_req_approve_status = 1 AND v_record_status = 1 AND v_arrival_time IS NOT NULL AND v_pdf_url IS NULL THEN 1
                        -- 其他状态使用原表的deleted值
                                              ELSE v_req_deleted
END;

                    -- 插入新request记录（使用游标中获取的字段，无需重复查询）
INSERT INTO core_introduce_letter_out_in_request_translate (id, letter_id, approve_user, approve_user_id,
                                                            approve_time, approve_status, approve_remark, approve_sign_img,
                                                            org_id, letter_name, letter_file, letter_no, letter_description,
                                                            letter_reasons, letter_type, deleted, start_time, end_time,
                                                            create_by, create_time, update_by, update_time, effective_days,
                                                            status, accompanying_person, check_sign, remark, remark_image,pdf_url)
VALUES (v_calculated_new_id, v_req_letter_id, v_req_approve_user, v_req_approve_user_id, v_req_approve_time,
        v_req_approve_status, v_req_approve_remark, v_req_approve_sign_img, v_req_org_id,
        v_req_letter_name, v_req_letter_file, v_req_letter_no, v_req_letter_description,
        v_req_letter_reasons, v_req_letter_type, v_final_deleted, v_req_start_time, v_req_end_time,
        v_req_create_by, v_req_create_time, v_req_update_by, v_req_update_time, v_req_effective_days,
           -- 状态映射
        case
            -- 已拒绝
            WHEN v_req_approve_status = 2 AND v_record_status = 2 THEN 3
            -- 待审批
            WHEN v_req_approve_status = 0 AND v_record_status = 0 THEN 0


            -- 已过期-1 : 过去当天申请，但是没有审批，过去的当天作废 → 待审批
            WHEN v_req_approve_status = 3 AND v_record_status = 3 THEN 0
            -- 已过期-2 ： 过去当天审批通过了， 但是没有填写结果，有可能已经发生事实，没有走流程，也有可能是人没来的过期，迁移到代办-待登记后 自行选择 删除 还是 补全 已过期2-> 已过期
            WHEN v_req_approve_status = 1 AND v_record_status = 3 AND v_arrival_time IS NULL THEN 4
            -- 已过期-3 : 造成原因 : 这是以前为改版前，审批通过，当天记录如果没有闭环的话就把它弄过期，目前来看没有，已过期3 -> 已补登你撤回了一条消息重新编辑剑雄15:52WHEN v_req_approve_status = 1 AND v_record_status = 3 AND v_arrival_time IS NOT NULL THEN 5


            -- 待登记-3 : 造成原因 : 当天提交，当天审批通过的记录 处理方式 : 上线前的当天记录如果没有完成离开时间的填写或者进入时间的填写，则转移到 已过期，代办处理 待登记-3 -> 待登记
            when v_req_approve_status = 1 and v_record_status = 1 and v_arrival_time is null and v_departure_time is null then 1
            -- 待登记-1 : 造成原因 : 历史待登记 : 已经完成进入时间填写的申请记录，记录的存活时间是无限期 待登记-1 -> 待登记
            WHEN v_req_approve_status = 1 AND v_record_status = 1 AND v_arrival_time IS NOT NULL AND v_departure_time IS NULL THEN 1
            -- 待登记-2 : 造成原因 : 历史待登记 : 错误 已闭环的流程 离开时间为空的话，PDF也应该未空
            when v_req_approve_status = 1 and v_record_status = 1 AND v_arrival_time IS NOT NULL and v_pdf_url is not null and v_departure_time is  null then 5


            -- 已完成2: #流程已闭环但是没有生成登记簿
            when v_req_approve_status = 1 and v_record_status = 1 and v_pdf_url is null and v_departure_time is not null then 5
            -- 已完成3 : 错误 已经闭环的流程应该也要形成PDF
            when v_req_approve_status = 1 and v_record_status = 1 AND v_arrival_time IS NOT NULL and v_pdf_url is null and v_departure_time is not null then 5
            -- 已完成1: 审批通过，有生成PDF 或者 有填写离开时间
            WHEN (v_record_status = 1 and v_pdf_url is not null) or (v_record_status = 1 and v_departure_time is not null) THEN 2


            ELSE 5
            END,
        v_accompanying_person,  -- 直接使用游标变量
        v_submit_sign,          -- 直接使用游标变量
        v_remark,               -- 直接使用游标变量
        v_check_image,          -- 直接使用游标变量
        v_pdf_url);             -- 直接使用游标变量


-- 收集更新数据到临时表（而不是立即更新）
INSERT INTO temp_record_updates (record_id, new_request_id)
VALUES (v_record_id, v_calculated_new_id)
    ON DUPLICATE KEY UPDATE new_request_id = VALUES(new_request_id);

INSERT INTO temp_user_updates (user_id, new_request_id, arrival_time, departure_time)
VALUES (v_user_id, v_calculated_new_id, v_arrival_time, v_departure_time)
    ON DUPLICATE KEY UPDATE new_request_id = VALUES(new_request_id),
    arrival_time   = VALUES(arrival_time),
                         departure_time = VALUES(departure_time);

SET v_total_processed = v_total_processed + 1;

END LOOP;

CLOSE cur_records;
SET done = FALSE;

END;
            -- 批量更新record表
UPDATE core_out_in_record rec
    INNER JOIN temp_record_updates tmp ON rec.id = tmp.record_id
    SET rec.out_in_request_id = tmp.new_request_id;

-- 批量更新user表
UPDATE core_introduce_letter_out_in_request_user user_tbl
    INNER JOIN temp_user_updates tmp ON user_tbl.id = tmp.user_id
    SET user_tbl.arrival_time = tmp.arrival_time,
        user_tbl.departure_time = tmp.departure_time,
        user_tbl.out_in_request_id = tmp.new_request_id;


-- 提交事务
COMMIT;

-- 清空临时表
TRUNCATE TABLE temp_record_updates;
TRUNCATE TABLE temp_user_updates;

-- 输出进度
SET v_batch_duration = TIMESTAMPDIFF(SECOND, v_batch_start_time, NOW());
#         SELECT CONCAT('批次 ', v_batch_count, ' 完成: 耗时 ', v_batch_duration, ' 秒') as 批次完成;

SET v_current_time = v_next_time;

END WHILE;

    -- 清理和输出结果
    DROP TEMPORARY TABLE temp_record_updates;
    DROP TEMPORARY TABLE temp_user_updates;

#     SELECT CONCAT('迁移完成！共处理 ', v_total_processed, ' 条记录') as 执行结果;

END ??
DELIMITER ;

call MigrateDataWithCursorTimeBatchOptimized();
rename table core_introduce_letter_out_in_request to core_introduce_letter_out_in_request_20251120;
rename table core_introduce_letter_out_in_request_translate to core_introduce_letter_out_in_request;

-- 2025-11-5 18:42:50 旧表的介绍信类型 电子(1) 并入到 普通(2)
update core_introduce_letter_out_in_request set letter_type = 2 where letter_type = 1;