242 lines
15 KiB
SQL
242 lines
15 KiB
SQL
-- ============================================================
|
||
-- 进销存（erp）—— 菜单配置（yz_system_menu）
|
||
-- 幂等脚本：按 path 判重，重复执行不会产生重复菜单
|
||
-- 执行：mysql -h <host> -P <port> -u <user> -p <db> --default-character-set=utf8mb4 < erp_menus.sql
|
||
-- ============================================================
|
||
-- 目标结构（「大类」= 模块根下一层目录，会作为移动端功能页的分组标题；
|
||
-- 「小类」= 叶子菜单，渲染为功能卡片）：
|
||
-- 工作台 -> 工作台（复用已有仪表盘页面 /apps/erp/dashboard）
|
||
-- 基础资料 -> 商品 / 供应商 / 客户 / 仓库
|
||
-- 采购管理 -> 采购订单 / 采购入库 / 采购退货
|
||
-- 销售管理 -> 销售订单 / 销售出库 / 销售退货
|
||
-- 库存管理 -> 入库 / 出库 / 调拨 / 盘点 / 库存查询 / 库存预警
|
||
-- 财务管理 -> 应收 / 应付 / 收付款
|
||
-- 报表中心 -> 报表中心
|
||
-- 系统管理 -> 系统管理 / 账套管理（账套列表）/ 基础设置
|
||
--
|
||
-- 字段说明：
|
||
-- type 1=目录（大类），2=页面（小类）
|
||
-- views '[2]' 租户端：Web 租户后台与移动端共用同一份菜单
|
||
-- platforms 留 NULL：展示端继承 erp 模块配置
|
||
-- component_path 由「3.1 页面文件登记」统一写入；未登记的菜单 Web 端为空白页，
|
||
-- 移动端落到「功能开发中」占位页
|
||
-- icon 完整 Font Awesome 类名；移动端必须落在 uniapp/utils/fa-icons.js 白名单内才显示
|
||
|
||
SET @erp_id := (
|
||
SELECT id FROM yz_system_menu
|
||
WHERE path = '/apps/erp' AND delete_time IS NULL
|
||
LIMIT 1
|
||
);
|
||
|
||
-- ---------- 1. 八个大类（目录） ----------
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT @erp_id, '工作台', '/apps/erp/workbench', NULL, 'fa-solid fa-gauge', 1, 1, 1, '[2]', 1, 'system', '进销存大类：工作台'
|
||
FROM DUAL
|
||
WHERE @erp_id IS NOT NULL
|
||
AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/workbench' AND delete_time IS NULL) x);
|
||
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT @erp_id, '基础资料', '/apps/erp/basic', NULL, 'fa-solid fa-database', 2, 1, 1, '[2]', 1, 'system', '进销存大类：基础资料'
|
||
FROM DUAL
|
||
WHERE @erp_id IS NOT NULL
|
||
AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/basic' AND delete_time IS NULL) x);
|
||
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT @erp_id, '采购管理', '/apps/erp/purchase', NULL, 'fa-solid fa-cart-shopping', 3, 1, 1, '[2]', 1, 'system', '进销存大类：采购管理'
|
||
FROM DUAL
|
||
WHERE @erp_id IS NOT NULL
|
||
AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/purchase' AND delete_time IS NULL) x);
|
||
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT @erp_id, '销售管理', '/apps/erp/sales', NULL, 'fa-solid fa-tags', 4, 1, 1, '[2]', 1, 'system', '进销存大类：销售管理'
|
||
FROM DUAL
|
||
WHERE @erp_id IS NOT NULL
|
||
AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/sales' AND delete_time IS NULL) x);
|
||
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT @erp_id, '库存管理', '/apps/erp/stock', NULL, 'fa-solid fa-warehouse', 5, 1, 1, '[2]', 1, 'system', '进销存大类：库存管理'
|
||
FROM DUAL
|
||
WHERE @erp_id IS NOT NULL
|
||
AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/stock' AND delete_time IS NULL) x);
|
||
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT @erp_id, '财务管理', '/apps/erp/finance', NULL, 'fa-solid fa-coins', 6, 1, 1, '[2]', 1, 'system', '进销存大类：财务管理'
|
||
FROM DUAL
|
||
WHERE @erp_id IS NOT NULL
|
||
AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/finance' AND delete_time IS NULL) x);
|
||
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT @erp_id, '报表中心', '/apps/erp/report', NULL, 'fa-solid fa-chart-column', 7, 1, 1, '[2]', 1, 'system', '进销存大类：报表中心'
|
||
FROM DUAL
|
||
WHERE @erp_id IS NOT NULL
|
||
AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/report' AND delete_time IS NULL) x);
|
||
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT @erp_id, '系统管理', '/apps/erp/system', NULL, 'fa-solid fa-gears', 8, 1, 1, '[2]', 1, 'system', '进销存大类：系统管理'
|
||
FROM DUAL
|
||
WHERE @erp_id IS NOT NULL
|
||
AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/system' AND delete_time IS NULL) x);
|
||
|
||
-- 取回八个大类的ID，供小类挂载
|
||
SET @erp_workbench := (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/workbench' AND delete_time IS NULL LIMIT 1);
|
||
SET @erp_basic := (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/basic' AND delete_time IS NULL LIMIT 1);
|
||
SET @erp_system := (SELECT id FROM yz_system_menu WHERE path = '/apps/erp/system' AND delete_time IS NULL LIMIT 1);
|
||
|
||
-- ---------- 2. 已有菜单迁入新树（避免同一功能出现两份） ----------
|
||
-- 仪表盘 -> 工作台大类，并更名为「工作台」；路由与页面文件不变
|
||
UPDATE yz_system_menu
|
||
SET pid = @erp_workbench, title = '工作台', sort = 1, update_time = NOW()
|
||
WHERE delete_time IS NULL AND path = '/apps/erp/dashboard' AND @erp_workbench IS NOT NULL;
|
||
|
||
-- 供应商列表 / 客户列表 -> 基础资料大类，去掉「列表」后缀；路由与页面文件不变
|
||
UPDATE yz_system_menu
|
||
SET pid = @erp_basic, title = '供应商', sort = 2, update_time = NOW()
|
||
WHERE delete_time IS NULL AND path = '/apps/erp/suppliers' AND @erp_basic IS NOT NULL;
|
||
|
||
UPDATE yz_system_menu
|
||
SET pid = @erp_basic, title = '客户', sort = 3, update_time = NOW()
|
||
WHERE delete_time IS NULL AND path = '/apps/erp/customers' AND @erp_basic IS NOT NULL;
|
||
|
||
-- 账套管理 / 基础设置 -> 系统管理大类
|
||
UPDATE yz_system_menu
|
||
SET pid = @erp_system, sort = 2, update_time = NOW()
|
||
WHERE delete_time IS NULL AND pid = @erp_id AND title = '账套管理' AND @erp_system IS NOT NULL;
|
||
|
||
UPDATE yz_system_menu
|
||
SET pid = @erp_system, sort = 3, update_time = NOW()
|
||
WHERE delete_time IS NULL AND pid = @erp_id AND title = '基础设置' AND @erp_system IS NOT NULL;
|
||
|
||
-- ---------- 3. 新增小类（页面） ----------
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_erp_leaf;
|
||
CREATE TEMPORARY TABLE tmp_erp_leaf (
|
||
parent_path VARCHAR(200) NOT NULL,
|
||
title VARCHAR(50) NOT NULL,
|
||
path VARCHAR(200) NOT NULL,
|
||
icon VARCHAR(100) NOT NULL,
|
||
sort INT NOT NULL,
|
||
remark VARCHAR(500) NOT NULL,
|
||
PRIMARY KEY (path)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
|
||
|
||
INSERT INTO tmp_erp_leaf (parent_path, title, path, icon, sort, remark) VALUES
|
||
('/apps/erp/basic', '商品', '/apps/erp/basic/product', 'fa-solid fa-box', 1, '商品档案'),
|
||
('/apps/erp/basic', '仓库', '/apps/erp/basic/warehouse', 'fa-solid fa-boxes-packing', 4, '仓库档案'),
|
||
('/apps/erp/purchase', '采购订单', '/apps/erp/purchase/order', 'fa-solid fa-file-invoice', 1, '采购订单'),
|
||
('/apps/erp/purchase', '采购入库', '/apps/erp/purchase/inbound', 'fa-solid fa-dolly', 2, '采购入库单'),
|
||
('/apps/erp/purchase', '采购退货', '/apps/erp/purchase/return', 'fa-solid fa-rotate-left', 3, '采购退货单'),
|
||
('/apps/erp/sales', '销售订单', '/apps/erp/sales/order', 'fa-solid fa-file-signature', 1, '销售订单'),
|
||
('/apps/erp/sales', '销售出库', '/apps/erp/sales/outbound', 'fa-solid fa-truck-fast', 2, '销售出库单'),
|
||
('/apps/erp/sales', '销售退货', '/apps/erp/sales/return', 'fa-solid fa-arrow-right-arrow-left',3, '销售退货单'),
|
||
('/apps/erp/stock', '入库', '/apps/erp/stock/inbound', 'fa-solid fa-box-open', 1, '其他入库'),
|
||
('/apps/erp/stock', '出库', '/apps/erp/stock/outbound', 'fa-solid fa-truck', 2, '其他出库'),
|
||
('/apps/erp/stock', '调拨', '/apps/erp/stock/transfer', 'fa-solid fa-right-left', 3, '仓库调拨'),
|
||
('/apps/erp/stock', '盘点', '/apps/erp/stock/taking', 'fa-solid fa-clipboard-check', 4, '库存盘点'),
|
||
('/apps/erp/stock', '库存查询', '/apps/erp/stock/query', 'fa-solid fa-magnifying-glass', 5, '库存查询'),
|
||
('/apps/erp/stock', '库存预警', '/apps/erp/stock/warning', 'fa-solid fa-triangle-exclamation', 6, '缺货预警与智能补货'),
|
||
('/apps/erp/finance', '应收', '/apps/erp/finance/receivable', 'fa-solid fa-file-invoice-dollar', 1, '应收账款'),
|
||
('/apps/erp/finance', '应付', '/apps/erp/finance/payable', 'fa-solid fa-money-check-dollar', 2, '应付账款'),
|
||
('/apps/erp/finance', '收付款', '/apps/erp/finance/payment', 'fa-solid fa-cash-register', 3, '收付款单'),
|
||
('/apps/erp/report', '报表中心', '/apps/erp/report/index', 'fa-solid fa-chart-line', 1, '进销存报表'),
|
||
('/apps/erp/system', '系统管理', '/apps/erp/system/index', 'fa-solid fa-sliders', 1, '进销存系统设置');
|
||
|
||
INSERT INTO yz_system_menu
|
||
(pid, title, path, component_path, icon, sort, status, is_visible, views, type, creater, remark)
|
||
SELECT p.id, t.title, t.path, NULL, t.icon, t.sort, 1, 1, '[2]', 2, 'system', t.remark
|
||
FROM tmp_erp_leaf t
|
||
JOIN yz_system_menu p ON p.path = t.parent_path AND p.delete_time IS NULL
|
||
LEFT JOIN yz_system_menu e ON e.path = t.path AND e.delete_time IS NULL
|
||
WHERE e.id IS NULL;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_erp_leaf;
|
||
|
||
-- ---------- 3.1 页面文件登记 ----------
|
||
-- 前端 pathResolver 先按 component_path 精确匹配，命中不到才按文件名模糊兜底，
|
||
-- 模糊兜底会把菜单渲染成另一个同名 index.vue，所以这里一律写从 src/views 起的完整路径。
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_erp_page;
|
||
CREATE TEMPORARY TABLE tmp_erp_page (
|
||
path VARCHAR(200) NOT NULL,
|
||
component_path VARCHAR(255) NOT NULL,
|
||
PRIMARY KEY (path)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
|
||
|
||
INSERT INTO tmp_erp_page (path, component_path) VALUES
|
||
('/apps/erp/basic/product', '/apps/erp/basic/product.vue'),
|
||
('/apps/erp/basic/warehouse', '/apps/erp/basic/warehouse.vue'),
|
||
('/apps/erp/purchase/order', '/apps/erp/purchase/order.vue'),
|
||
('/apps/erp/purchase/inbound', '/apps/erp/purchase/inbound.vue'),
|
||
('/apps/erp/purchase/return', '/apps/erp/purchase/return.vue'),
|
||
('/apps/erp/sales/order', '/apps/erp/sales/order.vue'),
|
||
('/apps/erp/sales/outbound', '/apps/erp/sales/outbound.vue'),
|
||
('/apps/erp/sales/return', '/apps/erp/sales/return.vue'),
|
||
('/apps/erp/stock/inbound', '/apps/erp/stock/inbound.vue'),
|
||
('/apps/erp/stock/outbound', '/apps/erp/stock/outbound.vue'),
|
||
('/apps/erp/stock/transfer', '/apps/erp/stock/transfer.vue'),
|
||
('/apps/erp/stock/taking', '/apps/erp/stock/taking.vue'),
|
||
('/apps/erp/stock/query', '/apps/erp/stock/query.vue'),
|
||
('/apps/erp/stock/warning', '/apps/erp/stock/warning.vue'),
|
||
('/apps/erp/finance/receivable', '/apps/erp/finance/receivable.vue'),
|
||
('/apps/erp/finance/payable', '/apps/erp/finance/payable.vue'),
|
||
('/apps/erp/finance/payment', '/apps/erp/finance/payment.vue'),
|
||
('/apps/erp/report/index', '/apps/erp/report/index.vue'),
|
||
('/apps/erp/system/index', '/apps/erp/system/index.vue'),
|
||
('/apps/erp/ProgramManagement', '/apps/erp/ProgramManagement/index.vue');
|
||
|
||
UPDATE yz_system_menu m
|
||
JOIN tmp_erp_page t ON t.path = m.path
|
||
SET m.component_path = t.component_path, m.update_time = NOW()
|
||
WHERE m.delete_time IS NULL AND (m.component_path IS NULL OR m.component_path <> t.component_path);
|
||
|
||
-- 基础设置 历史上挂成目录(type=1)又没有子菜单，登记页面后按叶子菜单处理
|
||
UPDATE yz_system_menu SET type = 2, update_time = NOW()
|
||
WHERE delete_time IS NULL AND path = '/apps/erp/ProgramManagement' AND type = 1;
|
||
|
||
-- 菜单行还没建（上一节未生效）时这里会列出来，执行后应重新跑一次本脚本
|
||
SELECT t.path AS 未登记菜单 FROM tmp_erp_page t
|
||
LEFT JOIN yz_system_menu m ON m.path = t.path AND m.delete_time IS NULL
|
||
WHERE m.id IS NULL;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_erp_page;
|
||
|
||
-- 补图标：迁移过来的菜单原本没有图标，移动端会退化成兜底图标，这里只填空值、不覆盖已有配置
|
||
UPDATE yz_system_menu SET icon = 'fa-solid fa-store', update_time = NOW()
|
||
WHERE delete_time IS NULL AND path = '/apps/erp/suppliers' AND (icon IS NULL OR icon = '');
|
||
|
||
UPDATE yz_system_menu SET icon = 'fa-solid fa-address-book', update_time = NOW()
|
||
WHERE delete_time IS NULL AND path = '/apps/erp/customers' AND (icon IS NULL OR icon = '');
|
||
|
||
UPDATE yz_system_menu SET icon = 'fa-solid fa-book-bookmark', update_time = NOW()
|
||
WHERE delete_time IS NULL AND pid = @erp_system AND title = '账套管理' AND (icon IS NULL OR icon = '');
|
||
|
||
-- ---------- 4. 清理被搬空的两级旧目录（供应商管理 / 客户管理） ----------
|
||
-- 子菜单已迁走（供应商列表 / 客户列表），留着会变成空分组。
|
||
-- 先取出「已无子菜单」的旧目录ID，再按ID软删除：MySQL 不允许在 UPDATE 的子查询里读同一张表。
|
||
SET @erp_empty_dir := NULL;
|
||
|
||
SELECT GROUP_CONCAT(d.id) INTO @erp_empty_dir
|
||
FROM yz_system_menu d
|
||
LEFT JOIN yz_system_menu c ON c.pid = d.id AND c.delete_time IS NULL
|
||
WHERE d.pid = @erp_id
|
||
AND d.delete_time IS NULL
|
||
AND d.title IN ('供应商管理', '客户管理')
|
||
AND d.path IN ('/apps/erp/supplier', '/apps/erp/customer')
|
||
AND c.id IS NULL;
|
||
|
||
UPDATE yz_system_menu
|
||
SET delete_time = NOW(), status = 0
|
||
WHERE delete_time IS NULL AND FIND_IN_SET(id, IFNULL(@erp_empty_dir, ''));
|
||
|
||
-- ---------- 5. 结果校验（大类 + 其下小类） ----------
|
||
SELECT d.sort AS 大类序号, d.title AS 大类, IFNULL(m.title, '(无)') AS 小类, m.sort AS 小类序号,
|
||
m.path AS 路径, m.icon AS 图标, m.component_path AS 页面文件
|
||
FROM yz_system_menu d
|
||
LEFT JOIN yz_system_menu m ON m.pid = d.id AND m.delete_time IS NULL
|
||
WHERE d.pid = @erp_id AND d.delete_time IS NULL
|
||
ORDER BY d.sort, m.sort, m.id; |