-- ===================================================================== -- 诚发五金 · 工单助手(App)后端配套脚本 -- 依据《附件一:技术方案-诚发五金(2026)》第(八)章「工单助手」 -- 内容: -- 1. after_sale_ticket 增加现场签到位(sign_in_time / sign_in_address) -- 2. 新建 after_sale_ticket_log 工单操作/进度记录表(进度跟踪、全生命周期追溯) -- 3. 补「工单助手」菜单与按钮权限(菜单 visible=0:仅 App 使用,不改变 Web 侧边栏) -- 4. 给销售/生产管理员/经理角色授权 -- 适用库:诚发五金本地库 CFWJ(单租户,脚本不含 tenant_id) -- 幂等:可重复执行 -- ===================================================================== SET NAMES utf8mb4; -- ==================== 1. after_sale_ticket 现场签到位 ==================== SET @exists_sign_in_time = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'after_sale_ticket' AND column_name = 'sign_in_time'); SET @sql = IF(@exists_sign_in_time = 0, 'ALTER TABLE `after_sale_ticket` ADD COLUMN `sign_in_time` datetime DEFAULT NULL COMMENT ''首次现场签到时间'' AFTER `repair_id`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists_sign_in_address = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'after_sale_ticket' AND column_name = 'sign_in_address'); SET @sql = IF(@exists_sign_in_address = 0, 'ALTER TABLE `after_sale_ticket` ADD COLUMN `sign_in_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''首次现场签到地址'' AFTER `sign_in_time`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ==================== 2. 工单操作/进度记录表 ==================== CREATE TABLE IF NOT EXISTS `after_sale_ticket_log` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录编号', `ticket_id` bigint NOT NULL COMMENT '工单编号,关联 after_sale_ticket.id', `action_type` tinyint NOT NULL COMMENT '操作类型: 1-上报 2-派单 3-签到 4-问题判定 5-转派 6-升级 7-处理记录 8-完结 9-回访 10-归档 11-取消', `content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作内容/说明', `operator_user_id` bigint DEFAULT NULL COMMENT '操作人用户编号', `longitude` decimal(12,8) DEFAULT NULL COMMENT '经度(现场签到时上报)', `latitude` decimal(12,8) DEFAULT NULL COMMENT '纬度(现场签到时上报)', `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '定位地址(现场签到时上报)', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`) USING BTREE, KEY `idx_ticket_id` (`ticket_id`) USING BTREE, KEY `idx_action_type` (`action_type`) USING BTREE, KEY `idx_create_time` (`create_time`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='售后工单操作/进度记录表'; -- ==================== 3. 工单助手菜单(App 使用,Web 侧不显示) ==================== START TRANSACTION; -- 3.1 一级目录 INSERT INTO `system_menu` (`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 '工单助手', '', 1, 20, 0, 'aftersales-helper', 'ep:headset', NULL, NULL, 0, b'0', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = 0 AND `name` = '工单助手'); SET @helper_id = (SELECT id FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = 0 AND `name` = '工单助手' ORDER BY id LIMIT 1); -- 3.2 工单受理页面 INSERT INTO `system_menu` (`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 '工单受理', '', 2, 1, @helper_id, 'ticket', 'ep:document', 'aftersales/ticket/index', NULL, 0, b'0', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = @helper_id AND `component` = 'aftersales/ticket/index'); SET @ticket_menu_id = (SELECT id FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = @helper_id AND `component` = 'aftersales/ticket/index' ORDER BY id LIMIT 1); -- 3.3 退货申请页面 INSERT INTO `system_menu` (`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 '退货申请', '', 2, 2, @helper_id, 'return', 'ep:refresh-left', 'aftersales/return/index', NULL, 0, b'0', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = @helper_id AND `component` = 'aftersales/return/index'); SET @return_menu_id = (SELECT id FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = @helper_id AND `component` = 'aftersales/return/index' ORDER BY id LIMIT 1); -- 3.4 问题库页面 INSERT INTO `system_menu` (`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 '问题库', '', 2, 3, @helper_id, 'problem', 'ep:notebook', 'aftersales/problem/index', NULL, 0, b'0', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = @helper_id AND `component` = 'aftersales/problem/index'); SET @problem_menu_id = (SELECT id FROM `system_menu` WHERE `deleted` = b'0' AND `parent_id` = @helper_id AND `component` = 'aftersales/problem/index' ORDER BY id LIMIT 1); -- 3.5 按钮权限(按 permission 幂等) INSERT INTO `system_menu` (`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 t.`name`, t.`permission`, 3, t.`sort`, t.`parent_id`, '', '', NULL, NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM ( SELECT '工单查询' AS `name`, 'aftersales:ticket:query' AS `permission`, 1 AS `sort`, @ticket_menu_id AS `parent_id` UNION ALL SELECT '工单上报', 'aftersales:ticket:create', 2, @ticket_menu_id UNION ALL SELECT '工单处理', 'aftersales:ticket:update', 3, @ticket_menu_id UNION ALL SELECT '工单删除', 'aftersales:ticket:delete', 4, @ticket_menu_id UNION ALL SELECT '工单回访', 'aftersales:visit:create', 5, @ticket_menu_id UNION ALL SELECT '退货查询', 'aftersales:return:query', 1, @return_menu_id UNION ALL SELECT '退货申请', 'aftersales:return:create', 2, @return_menu_id UNION ALL SELECT '退货审核', 'aftersales:return:update', 3, @return_menu_id UNION ALL SELECT '问题查询', 'aftersales:problem:query', 1, @problem_menu_id UNION ALL SELECT '问题新增', 'aftersales:problem:create', 2, @problem_menu_id UNION ALL SELECT '问题修改', 'aftersales:problem:update', 3, @problem_menu_id UNION ALL SELECT '问题删除', 'aftersales:problem:delete', 4, @problem_menu_id UNION ALL SELECT '问题发布', 'aftersales:problem:publish', 5, @problem_menu_id UNION ALL SELECT '问题停用', 'aftersales:problem:disable', 6, @problem_menu_id ) t WHERE NOT EXISTS (SELECT 1 FROM `system_menu` m WHERE m.`deleted` = b'0' AND m.`permission` = t.`permission`); -- 3.6 角色授权:销售、生产管理员、经理(超级管理员由代码直通,无需行) CREATE TEMPORARY TABLE IF NOT EXISTS `tmp_helper_role` (`role_id` bigint NOT NULL PRIMARY KEY); TRUNCATE TABLE `tmp_helper_role`; INSERT INTO `tmp_helper_role` (`role_id`) VALUES (165), (166), (168); INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) SELECT r.`role_id`, m.`id`, '1', NOW(), '1', NOW(), b'0' FROM `tmp_helper_role` r JOIN `system_menu` m ON m.`deleted` = b'0' AND ( m.`id` IN (@helper_id, @ticket_menu_id, @return_menu_id, @problem_menu_id) OR m.`permission` LIKE 'aftersales:%' ) WHERE NOT EXISTS ( SELECT 1 FROM `system_role_menu` rm WHERE rm.`role_id` = r.`role_id` AND rm.`menu_id` = m.`id` AND rm.`deleted` = b'0' ); DROP TEMPORARY TABLE IF EXISTS `tmp_helper_role`; COMMIT; -- ==================== 4. 验证 ==================== -- 1) 工单助手菜单树 SELECT id, name, permission, type, parent_id, visible, component FROM `system_menu` WHERE `deleted` = b'0' AND (name = '工单助手' OR parent_id = @helper_id OR permission LIKE 'aftersales:%') ORDER BY parent_id, type, sort; -- 2) 角色授权数量 SELECT rm.role_id, COUNT(*) AS menu_count FROM `system_role_menu` rm JOIN `system_menu` m ON m.id = rm.menu_id AND m.deleted = b'0' WHERE rm.deleted = b'0' AND (m.name = '工单助手' OR m.permission LIKE 'aftersales:%') GROUP BY rm.role_id; -- 3) 进度记录表 SELECT COUNT(*) AS log_count FROM `after_sale_ticket_log`;