-- 发票同步定时任务配置
|
-- 用于从旧系统同步发票信息到新系统
|
|
-- 1. 创建发票同步日志表
|
CREATE TABLE IF NOT EXISTS `sys_invoice_sync_log` (
|
`log_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '同步日志ID',
|
`sync_type` VARCHAR(50) NOT NULL COMMENT '同步类型(invoice_info-发票信息,invoice_status-发票状态)',
|
`sync_start_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '同步开始时间',
|
`sync_end_time` DATETIME DEFAULT NULL COMMENT '同步结束时间',
|
`records_processed` INT(11) DEFAULT 0 COMMENT '处理记录数',
|
`records_success` INT(11) DEFAULT 0 COMMENT '成功记录数',
|
`records_failed` INT(11) DEFAULT 0 COMMENT '失败记录数',
|
`error_message` TEXT DEFAULT NULL COMMENT '错误信息',
|
`status` TINYINT(1) DEFAULT 0 COMMENT '状态(0-处理中,1-成功,2-失败)',
|
PRIMARY KEY (`log_id`),
|
INDEX `idx_sync_type` (`sync_type`),
|
INDEX `idx_sync_start_time` (`sync_start_time`)
|
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='发票同步日志表';
|
|
-- 2. 插入发票同步定时任务配置
|
DELETE FROM sys_job WHERE job_name = 'InvoiceSyncJob';
|
INSERT INTO sys_job (
|
job_name,
|
job_group,
|
invoke_target,
|
cron_expression,
|
misfire_policy,
|
concurrent,
|
status,
|
create_by,
|
create_time
|
) VALUES (
|
'InvoiceSyncJob',
|
'SYSTEM',
|
'sysInvoiceTask.syncInvoiceFromLegacySystem',
|
'0 0/30 * * * ?', -- 每30分钟执行一次
|
'3',
|
'1',
|
'0',
|
'admin',
|
NOW()
|
);
|
|
-- 3. 同步发票信息的存储过程
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS sync_invoice_from_legacy$$
|
|
CREATE PROCEDURE sync_invoice_from_legacy()
|
BEGIN
|
DECLARE v_start_time DATETIME DEFAULT NOW();
|
DECLARE v_error_msg TEXT DEFAULT '';
|
DECLARE v_processed_count INT DEFAULT 0;
|
DECLARE v_success_count INT DEFAULT 0;
|
DECLARE v_failed_count INT DEFAULT 0;
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
BEGIN
|
GET DIAGNOSTICS CONDITION 1
|
v_error_msg = MESSAGE_TEXT;
|
ROLLBACK;
|
END;
|
|
START TRANSACTION;
|
|
-- 记录开始同步
|
INSERT INTO sys_invoice_sync_log (sync_type, sync_start_time, status)
|
VALUES ('invoice_info', v_start_time, 0);
|
|
SET @log_id = LAST_INSERT_ID();
|
|
-- 同步新的发票记录
|
INSERT INTO sys_invoice (
|
legacy_invoice_id,
|
legacy_service_order_id,
|
invoice_type,
|
invoice_name,
|
invoice_money,
|
invoice_remarks,
|
company_address,
|
company_bank,
|
company_bank_no,
|
zip_code,
|
mail_address,
|
contact_name,
|
contact_phone,
|
contact_email,
|
status,
|
invoice_no,
|
invoice_url,
|
apply_time,
|
audit_time,
|
audit_remarks,
|
sync_status
|
)
|
SELECT
|
i.InvoiceID as legacy_invoice_id,
|
i.ServiceOrderIDPK as legacy_service_order_id,
|
CASE
|
WHEN i.InvoiceType = 1 THEN 1 -- 个人发票
|
WHEN i.InvoiceType = 2 THEN 2 -- 企业发票
|
ELSE 1 -- 默认个人发票
|
END as invoice_type,
|
i.InvoiceName as invoice_name,
|
CAST(i.InvoiceMoney AS DECIMAL(10,2)) as invoice_money,
|
i.InvoiceMakeout as invoice_remarks,
|
i.InvoiceCompanyAdd as company_address,
|
i.InvoiceCompanyBank as company_bank,
|
i.InvoiceCompanyBankNo as company_bank_no,
|
i.InvoiceZipCode as zip_code,
|
i.Invoice_strAdd as mail_address,
|
i.Invoice_strName as contact_name,
|
i.Invoice_strPhone as contact_phone,
|
i.Invoice_strEmail as contact_email,
|
CASE
|
WHEN i.AuditStatus = 1 THEN 1 -- 已通过
|
WHEN i.AuditStatus = 2 THEN 2 -- 已驳回
|
ELSE 0 -- 待审核
|
END as status,
|
i.InvoiceNo as invoice_no,
|
COALESCE(i.InvoiceURL, i.EleCloud_PDF) as invoice_url,
|
i.ApplicationTime as apply_time,
|
i.AuditTime as audit_time,
|
i.AuditMakeout as audit_remarks,
|
1 as sync_status -- 标记为已同步
|
FROM InvoiceData i
|
WHERE i.InvoiceID NOT IN (
|
SELECT legacy_invoice_id
|
FROM sys_invoice
|
WHERE legacy_invoice_id IS NOT NULL
|
);
|
|
SET v_processed_count = ROW_COUNT();
|
SET v_success_count = v_processed_count;
|
|
-- 更新同步日志
|
UPDATE sys_invoice_sync_log
|
SET
|
sync_end_time = NOW(),
|
records_processed = v_processed_count,
|
records_success = v_success_count,
|
records_failed = v_failed_count,
|
error_message = v_error_msg,
|
status = IF(v_error_msg = '', 1, 2)
|
WHERE log_id = @log_id;
|
|
COMMIT;
|
END$$
|
|
DELIMITER ;
|
|
-- 4. 同步发票状态的存储过程
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS sync_invoice_status_from_legacy$$
|
|
CREATE PROCEDURE sync_invoice_status_from_legacy()
|
BEGIN
|
DECLARE v_start_time DATETIME DEFAULT NOW();
|
DECLARE v_error_msg TEXT DEFAULT '';
|
DECLARE v_processed_count INT DEFAULT 0;
|
DECLARE v_success_count INT DEFAULT 0;
|
DECLARE v_failed_count INT DEFAULT 0;
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
BEGIN
|
GET DIAGNOSTICS CONDITION 1
|
v_error_msg = MESSAGE_TEXT;
|
ROLLBACK;
|
END;
|
|
START TRANSACTION;
|
|
-- 记录开始同步
|
INSERT INTO sys_invoice_sync_log (sync_type, sync_start_time, status)
|
VALUES ('invoice_status', v_start_time, 0);
|
|
SET @log_id = LAST_INSERT_ID();
|
|
-- 更新现有发票的状态信息
|
UPDATE sys_invoice si
|
INNER JOIN InvoiceData i ON si.legacy_invoice_id = i.InvoiceID
|
SET
|
si.status = CASE
|
WHEN i.AuditStatus = 1 THEN 1 -- 已通过
|
WHEN i.AuditStatus = 2 THEN 2 -- 已驳回
|
ELSE 0 -- 待审核
|
END,
|
si.invoice_no = COALESCE(si.invoice_no, i.InvoiceNo),
|
si.invoice_url = COALESCE(si.invoice_url, i.InvoiceURL, i.EleCloud_PDF),
|
si.audit_time = i.AuditTime,
|
si.audit_remarks = i.AuditMakeout,
|
si.sync_status = 1
|
WHERE si.legacy_invoice_id IS NOT NULL
|
AND (
|
si.status != CASE
|
WHEN i.AuditStatus = 1 THEN 1
|
WHEN i.AuditStatus = 2 THEN 2
|
ELSE 0
|
END
|
OR si.invoice_no IS NULL
|
OR si.invoice_url IS NULL
|
);
|
|
SET v_processed_count = ROW_COUNT();
|
SET v_success_count = v_processed_count;
|
|
-- 更新同步日志
|
UPDATE sys_invoice_sync_log
|
SET
|
sync_end_time = NOW(),
|
records_processed = v_processed_count,
|
records_success = v_success_count,
|
records_failed = v_failed_count,
|
error_message = v_error_msg,
|
status = IF(v_error_msg = '', 1, 2)
|
WHERE log_id = @log_id;
|
|
COMMIT;
|
END$$
|
|
DELIMITER ;
|
|
-- 5. 查询发票同步日志的视图
|
CREATE OR REPLACE VIEW v_invoice_sync_log AS
|
SELECT
|
l.log_id,
|
l.sync_type,
|
l.sync_start_time,
|
l.sync_end_time,
|
l.records_processed,
|
l.records_success,
|
l.records_failed,
|
l.error_message,
|
l.status,
|
CASE l.status
|
WHEN 0 THEN '处理中'
|
WHEN 1 THEN '成功'
|
WHEN 2 THEN '失败'
|
ELSE '未知'
|
END as status_name,
|
TIMESTAMPDIFF(SECOND, l.sync_start_time, l.sync_end_time) as duration_seconds
|
FROM sys_invoice_sync_log l
|
ORDER BY l.sync_start_time DESC;
|
|
-- 6. 手动执行发票信息同步
|
-- CALL sync_invoice_from_legacy();
|
|
-- 7. 手动执行发票状态同步
|
-- CALL sync_invoice_status_from_legacy();
|
