1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
| -- =============================================
| -- 售后工单明细增强 SQL 初始化脚本（可重复执行）
| -- 需求：售后退货/工单每条明细独立售后单号，每条选部门、原因类型并填具体原因
| -- 包含：after_sale_ticket_item 加字段 + aftersale_reason_type 字典
| -- =============================================
|
| -- =============================================
| -- 1. 工单明细表新增字段（存在性判断，保证可重复执行）
| -- =============================================
| SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
| WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'after_sale_ticket_item' AND COLUMN_NAME = 'item_no');
| SET @ddl := IF(@col_exists = 0,
| 'ALTER TABLE `after_sale_ticket_item`
| ADD COLUMN `item_no` varchar(64) NULL COMMENT ''明细售后单号（工单号-序号）'' AFTER `ticket_id`,
| ADD COLUMN `dept_id` bigint NULL COMMENT ''部门ID，关联 system_dept.id'' AFTER `need_return`,
| ADD COLUMN `reason_type` tinyint NULL COMMENT ''原因类型，字典 aftersale_reason_type'' AFTER `dept_id`,
| ADD COLUMN `reason` varchar(500) NULL COMMENT ''具体原因'' AFTER `reason_type`',
| 'SELECT 1');
| PREPARE stmt FROM @ddl;
| EXECUTE stmt;
| DEALLOCATE PREPARE stmt;
|
| -- =============================================
| -- 2. 数据字典：售后原因类型 aftersale_reason_type
| -- （取值可由业务在「系统管理 - 字典管理」中自行维护）
| -- =============================================
| INSERT INTO `system_dict_type` (`id`, `name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1065307, '售后原因类型', 'aftersale_reason_type', 0, '售后工单明细原因分类', '', NOW(), '', NOW(), b'0')
| ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `type` = VALUES(`type`), `status` = VALUES(`status`), `deleted` = b'0';
|
| INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1065352, 1, '外观缺陷', '1', 'aftersale_reason_type', 0, 'primary', NULL, '', NOW(), '', NOW(), b'0'),
| (1065353, 2, '功能故障', '2', 'aftersale_reason_type', 0, 'warning', NULL, '', NOW(), '', NOW(), b'0'),
| (1065354, 3, '尺寸偏差', '3', 'aftersale_reason_type', 0, 'info', NULL, '', NOW(), '', NOW(), b'0'),
| (1065355, 4, '包装破损', '4', 'aftersale_reason_type', 0, 'warning', NULL, '', NOW(), '', NOW(), b'0'),
| (1065356, 5, '物流损坏', '5', 'aftersale_reason_type', 0, 'danger', NULL, '', NOW(), '', NOW(), b'0'),
| (1065357, 6, '缺件少发', '6', 'aftersale_reason_type', 0, 'danger', NULL, '', NOW(), '', NOW(), b'0'),
| (1065358, 7, '规格型号不符', '7', 'aftersale_reason_type', 0, 'info', NULL, '', NOW(), '', NOW(), b'0'),
| (1065359, 8, '使用不当', '8', 'aftersale_reason_type', 0, 'default', NULL, '', NOW(), '', NOW(), b'0'),
| (1065360, 9, '其他', '9', 'aftersale_reason_type', 0, 'default', NULL, '', NOW(), '', NOW(), b'0')
| ON DUPLICATE KEY UPDATE `sort` = VALUES(`sort`), `label` = VALUES(`label`), `value` = VALUES(`value`),
| `dict_type` = VALUES(`dict_type`), `status` = VALUES(`status`), `color_type` = VALUES(`color_type`), `deleted` = b'0';
|
| SELECT '售后工单明细增强初始化完成' AS result;
|
|
