207 lines
9.9 KiB
SQL
207 lines
9.9 KiB
SQL
-- 尔印独立打印终端、终端一对一绑定及持久化打印任务（迁移自 hq-platform 20260904_add_er_yin_printer.sql，仅保留 DDL）。
|
|
-- 字典（hard_type 类型12、hard_print_type、hard_print_status）与按钮权限请按 db/20260908_权限菜单字典待添加.txt 手动添加。
|
|
-- 每个结构变更独立判断，可在兼容数据库自动提交部分DDL后安全重跑整个脚本。
|
|
|
|
ALTER TABLE `pjcloudx_hardware`.`hard_invoice`
|
|
MODIFY COLUMN `hard_id` bigint(20) NULL COMMENT '来源业务或核销终端id',
|
|
MODIFY COLUMN `pic` varchar(512) NULL COMMENT '后端生成的打印PNG地址';
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'printer_hard_id') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `printer_hard_id` bigint(20) NULL COMMENT ''目标尔印打印终端id'' AFTER `hard_id`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'print_type') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `print_type` char(2) NOT NULL DEFAULT ''1'' COMMENT ''打印类型，1发票、2兑换券'' AFTER `order_id`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'print_no') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `print_no` int(11) NOT NULL DEFAULT 1 COMMENT ''同订单同类型打印序号'' AFTER `print_type`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'retry_version') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `retry_version` int(11) NOT NULL DEFAULT 0 COMMENT ''人工失败重试版本'' AFTER `print_no`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'attempt_count') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `attempt_count` int(11) NOT NULL DEFAULT 0 COMMENT ''终端USB累计尝试次数'' AFTER `retry_version`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'failure_stage') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `failure_stage` varchar(32) NULL COMMENT ''BINDING、IMAGE_GENERATION、MQTT_DISPATCH或TERMINAL_PRINT'' AFTER `status`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'last_error') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `last_error` varchar(2000) NULL COMMENT ''最近失败摘要'' AFTER `failure_stage`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'printed_at') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `printed_at` datetime NULL COMMENT ''终端确认完整USB数据和切纸命令写入成功时间'' AFTER `last_error`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`columns`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `column_name` = 'reprint_reason') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD COLUMN `reprint_reason` varchar(500) NULL COMMENT ''人工重打或再次核销原因'' AFTER `printed_at`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
-- 唯一索引尚未建立才回填历史发票，避免迁移完成后重跑时覆盖新产生的兑换券任务。
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`statistics`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `index_name` = 'uk_hard_invoice_order_type_no') = 0,
|
|
'UPDATE `pjcloudx_hardware`.`hard_invoice` invoice
|
|
JOIN (
|
|
SELECT ranked.`id`, ranked.`print_no`
|
|
FROM (
|
|
SELECT `id`, ROW_NUMBER() OVER (
|
|
PARTITION BY `tenant_id`, `order_id`
|
|
ORDER BY `create_time`, `id`
|
|
) AS `print_no`
|
|
FROM `pjcloudx_hardware`.`hard_invoice`
|
|
) ranked
|
|
) ranked_rows ON ranked_rows.`id` = invoice.`id`
|
|
SET invoice.`print_type` = ''1'',
|
|
invoice.`print_no` = ranked_rows.`print_no`,
|
|
invoice.`retry_version` = 0,
|
|
invoice.`attempt_count` = 0',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`statistics`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `index_name` = 'uk_hard_invoice_order_type_no') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD UNIQUE KEY `uk_hard_invoice_order_type_no` (`tenant_id`, `order_id`, `print_type`, `print_no`)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`statistics`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `index_name` = 'idx_hard_invoice_printer_status') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD KEY `idx_hard_invoice_printer_status` (`tenant_id`, `printer_hard_id`, `status`, `update_time`, `del_flag`)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
SET @er_yin_ddl = IF(
|
|
(SELECT COUNT(*) FROM `information_schema`.`statistics`
|
|
WHERE `table_schema` = 'pjcloudx_hardware' AND `table_name` = 'hard_invoice'
|
|
AND `index_name` = 'idx_hard_invoice_source_status') = 0,
|
|
'ALTER TABLE `pjcloudx_hardware`.`hard_invoice` ADD KEY `idx_hard_invoice_source_status` (`tenant_id`, `hard_id`, `status`, `del_flag`)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE er_yin_stmt FROM @er_yin_ddl;
|
|
EXECUTE er_yin_stmt;
|
|
DEALLOCATE PREPARE er_yin_stmt;
|
|
|
|
CREATE TABLE IF NOT EXISTS `pjcloudx_hardware`.`hard_printer_binding`
|
|
(
|
|
`id` bigint(20) NOT NULL COMMENT '绑定关系主键',
|
|
`source_hard_id` bigint(20) NOT NULL COMMENT 'SUPERMARKET或CANTEEN来源业务终端id',
|
|
`printer_hard_id` bigint(20) NOT NULL COMMENT 'EY_PRINT目标打印终端id',
|
|
`merchant_id` bigint(20) NOT NULL COMMENT '双方共同所属商户id快照',
|
|
`dept_id` bigint(20) NOT NULL COMMENT '双方共同所属商户部门id快照',
|
|
`create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
|
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
|
|
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
|
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_hard_printer_binding_source` (`tenant_id`, `source_hard_id`),
|
|
UNIQUE KEY `uk_hard_printer_binding_printer` (`tenant_id`, `printer_hard_id`),
|
|
KEY `idx_hard_printer_binding_merchant` (`tenant_id`, `merchant_id`)
|
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci
|
|
COMMENT = '业务终端与尔印打印终端一对一绑定';
|
|
|
|
CREATE TABLE IF NOT EXISTS `pjcloudx_hardware`.`hard_printer_config`
|
|
(
|
|
`id` bigint(20) NOT NULL COMMENT '配置主键',
|
|
`printer_hard_id` bigint(20) NOT NULL COMMENT 'EY_PRINT打印终端id',
|
|
`printer_model` varchar(128) DEFAULT NULL COMMENT '打印机型号',
|
|
`usb_vendor_id` int(11) DEFAULT NULL COMMENT 'USB Vendor ID十进制值',
|
|
`usb_product_id` int(11) DEFAULT NULL COMMENT 'USB Product ID十进制值',
|
|
`paper_width` int(11) NOT NULL DEFAULT 80 COMMENT '纸宽，单位mm，首期固定80',
|
|
`auto_cut` char(1) NOT NULL DEFAULT '1' COMMENT '自动切刀开关，1开启、0关闭',
|
|
`image_mode` varchar(16) NOT NULL DEFAULT 'GS_V0' COMMENT '图片指令模式，GS_V0或ESC_STAR',
|
|
`create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
|
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
|
|
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
|
`tenant_id` bigint(20) NOT NULL COMMENT '租户ID',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_hard_printer_config_printer` (`tenant_id`, `printer_hard_id`)
|
|
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci
|
|
COMMENT = '尔印打印终端USB与ESC/POS配置';
|
|
|
|
SET @er_yin_ddl = NULL;
|