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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
| -- =============================================
| -- 售后：问题库 + 回访（可重复执行）
| -- 含：after_sale_ticket 加 6 列、after_sale_problem / after_sale_visit 建表、问题库菜单（空闲 id 602710-602716，已存在则跳过）
| -- =============================================
|
| -- 1. after_sale_ticket 加回访/沉淀相关列
| SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
| WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'after_sale_ticket' AND COLUMN_NAME = 'visit_status');
| SET @ddl := IF(@c = 0,
| 'ALTER TABLE `after_sale_ticket`
| ADD COLUMN `visit_status` tinyint NULL COMMENT ''回访状态: 0-待回访 1-已回访通过'' AFTER `closed_time`,
| ADD COLUMN `last_visit_time` datetime NULL COMMENT ''最近一次回访时间'' AFTER `visit_status`,
| ADD COLUMN `last_visit_satisfied` bit(1) NULL COMMENT ''最近一次回访是否满意'' AFTER `last_visit_time`,
| ADD COLUMN `last_visit_recurred` bit(1) NULL COMMENT ''最近一次回访问题是否复发'' AFTER `last_visit_satisfied`,
| ADD COLUMN `problem_id` bigint NULL COMMENT ''沉淀问题编号'' AFTER `last_visit_recurred`,
| ADD COLUMN `problem_no` varchar(64) NULL COMMENT ''沉淀问题单号'' AFTER `problem_id`',
| 'SELECT 1');
| PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
|
| -- 2. 问题库表
| CREATE TABLE IF NOT EXISTS `after_sale_problem` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '问题编号',
| `no` varchar(64) DEFAULT NULL COMMENT '问题单号 AP+yyyyMMdd+6位',
| `title` varchar(255) DEFAULT NULL COMMENT '问题标题',
| `source_ticket_id` bigint DEFAULT NULL COMMENT '来源工单编号',
| `source_ticket_no` varchar(64) DEFAULT NULL COMMENT '来源工单单号',
| `customer_id` bigint DEFAULT NULL COMMENT '客户编号',
| `item_id` bigint DEFAULT NULL COMMENT '物料编号',
| `item_code` varchar(64) DEFAULT NULL COMMENT '物料编码',
| `item_name` varchar(255) DEFAULT NULL COMMENT '物料名称',
| `batch_id` bigint DEFAULT NULL COMMENT '生产批次编号',
| `batch_code` varchar(64) DEFAULT NULL COMMENT '生产批次号',
| `issue_type` tinyint DEFAULT NULL COMMENT '问题类型',
| `issue_category` varchar(64) DEFAULT NULL COMMENT '问题分类',
| `severity_level` tinyint DEFAULT NULL COMMENT '严重程度',
| `description` varchar(1000) DEFAULT NULL COMMENT '问题描述',
| `root_cause_category` tinyint DEFAULT NULL COMMENT '根因分类',
| `root_cause` text COMMENT '根因分析',
| `solution` text COMMENT '解决方案',
| `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态',
| `owner_user_id` bigint DEFAULT NULL COMMENT '沉淀人用户编号',
| `publish_time` datetime DEFAULT NULL COMMENT '发布时间',
| `remark` varchar(500) DEFAULT NULL COMMENT '备注',
| `creator` varchar(64) DEFAULT '' COMMENT '创建者',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `updater` varchar(64) DEFAULT '' COMMENT '更新者',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
| PRIMARY KEY (`id`), KEY `idx_source_ticket` (`source_ticket_id`), KEY `idx_item` (`item_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后问题库表';
|
| -- 3. 回访记录表
| CREATE TABLE IF NOT EXISTS `after_sale_visit` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '回访记录编号',
| `ticket_id` bigint DEFAULT NULL COMMENT '工单编号',
| `visit_type` tinyint DEFAULT NULL COMMENT '回访方式: 0-电话 1-上门 2-远程 3-其他',
| `visitor_user_id` bigint DEFAULT NULL COMMENT '回访人用户编号',
| `content` varchar(1000) DEFAULT NULL COMMENT '回访内容',
| `is_satisfied` bit(1) DEFAULT NULL COMMENT '是否满意',
| `is_recurred` bit(1) DEFAULT NULL COMMENT '问题是否复发',
| `visit_time` datetime DEFAULT NULL COMMENT '回访时间',
| `remark` varchar(500) DEFAULT NULL COMMENT '备注',
| `creator` varchar(64) DEFAULT '' COMMENT '创建者',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `updater` varchar(64) DEFAULT '' COMMENT '更新者',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
| PRIMARY KEY (`id`), KEY `idx_ticket` (`ticket_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后回访记录表';
|
| -- 4. 问题库菜单 —— 仅当系统中【尚无】问题库菜单(按权限 aftersales:problem:query 判断)时才插入，避免与已有菜单重复
| 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 * FROM (
| SELECT 602710 AS id,'问题库' AS name,'' AS permission,2 AS type,3 AS sort,602587 AS parent_id,'problem' AS path,'ep:collection' AS icon,'/aftersales/problem/index' AS component,'AfterSaleProblem' AS component_name,0 AS status,b'1' AS visible,b'1' AS keep_alive,b'1' AS always_show,'1' AS creator,NOW() AS create_time,'1' AS updater,NOW() AS update_time,b'0' AS deleted
| UNION ALL SELECT 602711,'问题库查询','aftersales:problem:query',3,1,602710,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| UNION ALL SELECT 602712,'问题库创建','aftersales:problem:create',3,2,602710,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| UNION ALL SELECT 602713,'问题库更新','aftersales:problem:update',3,3,602710,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| UNION ALL SELECT 602714,'问题库删除','aftersales:problem:delete',3,4,602710,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| UNION ALL SELECT 602715,'问题库发布','aftersales:problem:publish',3,5,602710,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| UNION ALL SELECT 602716,'问题库停用','aftersales:problem:disable',3,6,602710,'','','',NULL,0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| ) t
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `permission`='aftersales:problem: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 602710 AS menu_id UNION ALL SELECT 602711 UNION ALL SELECT 602712 UNION ALL SELECT 602713 UNION ALL SELECT 602714 UNION ALL SELECT 602715 UNION ALL SELECT 602716) 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;
|
|
