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
45
46
47
48
49
50
51
52
| -- =============================================
| -- 售后工单：客户/联系人/物料/批次 改为文本存储 + 兜底补齐工单按钮权限（可重复执行）
| -- =============================================
| SET @c1 := (SELECT COUNT(*) FROM information_schema.COLUMNS
| WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'after_sale_ticket' AND COLUMN_NAME = 'customer_name');
| SET @ddl1 := IF(@c1 = 0,
| 'ALTER TABLE `after_sale_ticket` ADD COLUMN `customer_name` varchar(255) NULL COMMENT ''客户名称(文本)'' AFTER `customer_id`',
| 'SELECT 1');
| PREPARE s1 FROM @ddl1; EXECUTE s1; DEALLOCATE PREPARE s1;
|
| SET @c2 := (SELECT COUNT(*) FROM information_schema.COLUMNS
| WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'after_sale_ticket' AND COLUMN_NAME = 'contact_name');
| SET @ddl2 := IF(@c2 = 0,
| 'ALTER TABLE `after_sale_ticket` ADD COLUMN `contact_name` varchar(255) NULL COMMENT ''联系人(文本)'' AFTER `contact_id`',
| 'SELECT 1');
| PREPARE s2 FROM @ddl2; EXECUTE s2; DEALLOCATE PREPARE s2;
|
| -- 客户/联系人改为文本后，原外键列允许为空
| ALTER TABLE `after_sale_ticket` MODIFY COLUMN `customer_id` bigint NULL COMMENT '客户编号(可选)';
| ALTER TABLE `after_sale_ticket` MODIFY COLUMN `contact_id` bigint NULL COMMENT '联系人编号(可选)';
|
| -- 物料/批次改为文本后，明细原外键列允许为空
| ALTER TABLE `after_sale_ticket_item` MODIFY COLUMN `sale_order_item_id` bigint NULL COMMENT '销售订单行(可选)';
| ALTER TABLE `after_sale_ticket_item` MODIFY COLUMN `item_id` bigint NULL COMMENT 'MDM物料编号(可选)';
| ALTER TABLE `after_sale_ticket_item` MODIFY COLUMN `batch_id` bigint NULL COMMENT '批次编号(可选)';
|
| -- 兜底：工单按钮权限（仅当该权限不存在时才插入，避免与已有菜单重复/撞 id）
| INSERT INTO `system_menu`
| (`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
| SELECT 602700,'售后工单新增','aftersales:ticket:create',3,1,602588,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:ticket:create' AND `deleted`=b'0');
| INSERT INTO `system_menu`
| (`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
| SELECT 602701,'售后工单编辑','aftersales:ticket:update',3,2,602588,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:ticket:update' AND `deleted`=b'0');
| INSERT INTO `system_menu`
| (`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
| SELECT 602702,'售后工单删除','aftersales:ticket:delete',3,3,602588,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:ticket:delete' AND `deleted`=b'0');
| INSERT INTO `system_menu`
| (`id`,`name`,`permission`,`type`,`sort`,`parent_id`,`path`,`icon`,`component`,`component_name`,`status`,`visible`,`keep_alive`,`always_show`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
| SELECT 602703,'售后工单查询','aftersales:ticket:query',3,4,602588,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:ticket:query' AND `deleted`=b'0');
|
| -- 兜底按钮 授权给超管
| INSERT INTO `system_role_menu` (`role_id`,`menu_id`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
| SELECT 1, m.menu_id, '1', NOW(), '1', NOW(), b'0'
| FROM (SELECT 602700 AS menu_id UNION ALL SELECT 602701 UNION ALL SELECT 602702 UNION ALL SELECT 602703) m
| WHERE EXISTS (SELECT 1 FROM `system_menu` sm WHERE sm.`id`=m.menu_id)
| AND NOT EXISTS (SELECT 1 FROM `system_role_menu` rm WHERE rm.`role_id`=1 AND rm.`menu_id`=m.menu_id AND rm.`deleted`=b'0');
|
| SELECT '售后工单文本字段初始化完成' AS result;
|
|
