342 lines
12 KiB
Transact-SQL
342 lines
12 KiB
Transact-SQL
-- 查询统计菜单与库存异常字典补丁
|
||
-- 目标库：FTC25009-WMS
|
||
|
||
SET NOCOUNT ON;
|
||
|
||
IF EXISTS (SELECT 1 FROM dbo.sys_dict_data WHERE dict_type = N'bas_stock_stock_status' AND dict_value = N'3')
|
||
BEGIN
|
||
UPDATE dbo.sys_dict_data
|
||
SET dict_label = N'异常',
|
||
dict_sort = 2,
|
||
list_class = N'danger',
|
||
status = N'0',
|
||
update_by = N'admin',
|
||
update_time = GETDATE()
|
||
WHERE dict_type = N'bas_stock_stock_status'
|
||
AND dict_value = N'3';
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO dbo.sys_dict_data (dict_sort, dict_label, dict_value, dict_type, list_class, is_default, status, create_by, create_time)
|
||
VALUES (2, N'异常', N'3', N'bas_stock_stock_status', N'danger', N'N', N'0', N'admin', GETDATE());
|
||
END
|
||
|
||
DECLARE @parentMenuId bigint = 2007;
|
||
DECLARE @menuIdIsIdentity bit = COLUMNPROPERTY(OBJECT_ID(N'dbo.sys_menu'), 'menu_id', 'IsIdentity');
|
||
DECLARE @menuId bigint;
|
||
DECLARE @baseMenuId bigint;
|
||
|
||
IF @menuIdIsIdentity = 1
|
||
SET IDENTITY_INSERT dbo.sys_menu ON;
|
||
|
||
-- 库存明细查询
|
||
SET @baseMenuId = 2200;
|
||
SELECT TOP 1 @menuId = menu_id
|
||
FROM dbo.sys_menu
|
||
WHERE menu_type = N'C'
|
||
AND (path = N'stockDetail' OR component = N'search/stockDetail/index')
|
||
ORDER BY menu_id;
|
||
IF @menuId IS NULL
|
||
BEGIN
|
||
SET @menuId = @baseMenuId;
|
||
WHILE EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
SET @menuId = @menuId + 1;
|
||
END
|
||
IF NOT EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
BEGIN
|
||
INSERT INTO dbo.sys_menu (
|
||
menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon,
|
||
create_by, create_time, update_by, update_time, remark
|
||
)
|
||
VALUES (
|
||
@menuId, N'库存明细查询', @parentMenuId, 1, N'stockDetail', N'search/stockDetail/index', NULL, N'',
|
||
1, 0, N'C', N'0', N'0', N'bas:stock:list', N'document',
|
||
N'admin', GETDATE(), N'', NULL, N'查询统计-库存明细查询'
|
||
);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
UPDATE dbo.sys_menu
|
||
SET menu_name = N'库存明细查询',
|
||
parent_id = @parentMenuId,
|
||
order_num = 1,
|
||
path = N'stockDetail',
|
||
component = N'search/stockDetail/index',
|
||
perms = N'bas:stock:list',
|
||
icon = N'document',
|
||
visible = N'0',
|
||
status = N'0',
|
||
update_by = N'admin',
|
||
update_time = GETDATE()
|
||
WHERE menu_id = @menuId;
|
||
END
|
||
|
||
-- 入库单据历史查询
|
||
SET @menuId = NULL;
|
||
SET @baseMenuId = 2201;
|
||
SELECT TOP 1 @menuId = menu_id
|
||
FROM dbo.sys_menu
|
||
WHERE menu_type = N'C'
|
||
AND (path = N'inStockinHistory' OR component = N'search/inStockinHistory/index')
|
||
ORDER BY menu_id;
|
||
IF @menuId IS NULL
|
||
BEGIN
|
||
SET @menuId = @baseMenuId;
|
||
WHILE EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
SET @menuId = @menuId + 1;
|
||
END
|
||
IF NOT EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
BEGIN
|
||
INSERT INTO dbo.sys_menu (
|
||
menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon,
|
||
create_by, create_time, update_by, update_time, remark
|
||
)
|
||
VALUES (
|
||
@menuId, N'入库单据历史查询', @parentMenuId, 2, N'inStockinHistory', N'search/inStockinHistory/index', NULL, N'',
|
||
1, 0, N'C', N'0', N'0', N'in:stockin:list', N'list',
|
||
N'admin', GETDATE(), N'', NULL, N'查询统计-入库单据历史查询'
|
||
);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
UPDATE dbo.sys_menu
|
||
SET menu_name = N'入库单据历史查询',
|
||
parent_id = @parentMenuId,
|
||
order_num = 2,
|
||
path = N'inStockinHistory',
|
||
component = N'search/inStockinHistory/index',
|
||
perms = N'in:stockin:list',
|
||
icon = N'list',
|
||
visible = N'0',
|
||
status = N'0',
|
||
update_by = N'admin',
|
||
update_time = GETDATE()
|
||
WHERE menu_id = @menuId;
|
||
END
|
||
|
||
-- 出库单历史查询
|
||
SET @menuId = NULL;
|
||
SET @baseMenuId = 2202;
|
||
SELECT TOP 1 @menuId = menu_id
|
||
FROM dbo.sys_menu
|
||
WHERE menu_type = N'C'
|
||
AND (path = N'outStockoutHistory' OR component = N'search/outStockoutHistory/index')
|
||
ORDER BY menu_id;
|
||
IF @menuId IS NULL
|
||
BEGIN
|
||
SET @menuId = @baseMenuId;
|
||
WHILE EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
SET @menuId = @menuId + 1;
|
||
END
|
||
IF NOT EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
BEGIN
|
||
INSERT INTO dbo.sys_menu (
|
||
menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon,
|
||
create_by, create_time, update_by, update_time, remark
|
||
)
|
||
VALUES (
|
||
@menuId, N'出库单历史查询', @parentMenuId, 3, N'outStockoutHistory', N'search/outStockoutHistory/index', NULL, N'',
|
||
1, 0, N'C', N'0', N'0', N'out:stockout:list', N'document-copy',
|
||
N'admin', GETDATE(), N'', NULL, N'查询统计-出库单历史查询'
|
||
);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
UPDATE dbo.sys_menu
|
||
SET menu_name = N'出库单历史查询',
|
||
parent_id = @parentMenuId,
|
||
order_num = 3,
|
||
path = N'outStockoutHistory',
|
||
component = N'search/outStockoutHistory/index',
|
||
perms = N'out:stockout:list',
|
||
icon = N'document-copy',
|
||
visible = N'0',
|
||
status = N'0',
|
||
update_by = N'admin',
|
||
update_time = GETDATE()
|
||
WHERE menu_id = @menuId;
|
||
END
|
||
|
||
-- 入库流水查询
|
||
SET @menuId = NULL;
|
||
SET @baseMenuId = 2203;
|
||
SELECT TOP 1 @menuId = menu_id
|
||
FROM dbo.sys_menu
|
||
WHERE menu_type = N'C'
|
||
AND (path = N'inJobFlow' OR component = N'search/inJobFlow/index')
|
||
ORDER BY menu_id;
|
||
IF @menuId IS NULL
|
||
BEGIN
|
||
SET @menuId = @baseMenuId;
|
||
WHILE EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
SET @menuId = @menuId + 1;
|
||
END
|
||
IF NOT EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
BEGIN
|
||
INSERT INTO dbo.sys_menu (
|
||
menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon,
|
||
create_by, create_time, update_by, update_time, remark
|
||
)
|
||
VALUES (
|
||
@menuId, N'入库流水查询', @parentMenuId, 4, N'inJobFlow', N'search/inJobFlow/index', NULL, N'',
|
||
1, 0, N'C', N'0', N'0', N'in:job:list', N'receiving',
|
||
N'admin', GETDATE(), N'', NULL, N'查询统计-入库流水查询'
|
||
);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
UPDATE dbo.sys_menu
|
||
SET menu_name = N'入库流水查询',
|
||
parent_id = @parentMenuId,
|
||
order_num = 4,
|
||
path = N'inJobFlow',
|
||
component = N'search/inJobFlow/index',
|
||
perms = N'in:job:list',
|
||
icon = N'receiving',
|
||
visible = N'0',
|
||
status = N'0',
|
||
update_by = N'admin',
|
||
update_time = GETDATE()
|
||
WHERE menu_id = @menuId;
|
||
END
|
||
|
||
-- 出库流水查询
|
||
SET @menuId = NULL;
|
||
SET @baseMenuId = 2204;
|
||
SELECT TOP 1 @menuId = menu_id
|
||
FROM dbo.sys_menu
|
||
WHERE menu_type = N'C'
|
||
AND (path = N'outJobFlow' OR component = N'search/outJobFlow/index')
|
||
ORDER BY menu_id;
|
||
IF @menuId IS NULL
|
||
BEGIN
|
||
SET @menuId = @baseMenuId;
|
||
WHILE EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
SET @menuId = @menuId + 1;
|
||
END
|
||
IF NOT EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @menuId)
|
||
BEGIN
|
||
INSERT INTO dbo.sys_menu (
|
||
menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon,
|
||
create_by, create_time, update_by, update_time, remark
|
||
)
|
||
VALUES (
|
||
@menuId, N'出库流水查询', @parentMenuId, 5, N'outJobFlow', N'search/outJobFlow/index', NULL, N'',
|
||
1, 0, N'C', N'0', N'0', N'out:job:list', N'release',
|
||
N'admin', GETDATE(), N'', NULL, N'查询统计-出库流水查询'
|
||
);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
UPDATE dbo.sys_menu
|
||
SET menu_name = N'出库流水查询',
|
||
parent_id = @parentMenuId,
|
||
order_num = 5,
|
||
path = N'outJobFlow',
|
||
component = N'search/outJobFlow/index',
|
||
perms = N'out:job:list',
|
||
icon = N'release',
|
||
visible = N'0',
|
||
status = N'0',
|
||
update_by = N'admin',
|
||
update_time = GETDATE()
|
||
WHERE menu_id = @menuId;
|
||
END
|
||
|
||
DECLARE @exportButton table(
|
||
menu_name nvarchar(50),
|
||
parent_path nvarchar(100),
|
||
parent_component nvarchar(200),
|
||
perms nvarchar(100)
|
||
);
|
||
DECLARE @buttonMenuId bigint;
|
||
DECLARE @buttonMenuName nvarchar(50);
|
||
DECLARE @buttonParentPath nvarchar(100);
|
||
DECLARE @buttonParentComponent nvarchar(200);
|
||
DECLARE @buttonPerms nvarchar(100);
|
||
|
||
INSERT INTO @exportButton(menu_name, parent_path, parent_component, perms)
|
||
VALUES
|
||
(N'库存明细查询导出', N'stockDetail', N'search/stockDetail/index', N'bas:stock:export'),
|
||
(N'入库单据历史查询导出', N'inStockinHistory', N'search/inStockinHistory/index', N'in:stockin:export'),
|
||
(N'出库单历史查询导出', N'outStockoutHistory', N'search/outStockoutHistory/index', N'out:stockout:export'),
|
||
(N'入库流水查询导出', N'inJobFlow', N'search/inJobFlow/index', N'in:job:export'),
|
||
(N'出库流水查询导出', N'outJobFlow', N'search/outJobFlow/index', N'out:job:export');
|
||
|
||
DECLARE export_button_cursor CURSOR LOCAL FAST_FORWARD FOR
|
||
SELECT menu_name, parent_path, parent_component, perms
|
||
FROM @exportButton;
|
||
|
||
OPEN export_button_cursor;
|
||
FETCH NEXT FROM export_button_cursor INTO @buttonMenuName, @buttonParentPath, @buttonParentComponent, @buttonPerms;
|
||
WHILE @@FETCH_STATUS = 0
|
||
BEGIN
|
||
SET @menuId = NULL;
|
||
SELECT TOP 1 @menuId = menu_id
|
||
FROM dbo.sys_menu
|
||
WHERE menu_type = N'C'
|
||
AND (path = @buttonParentPath OR component = @buttonParentComponent)
|
||
ORDER BY menu_id;
|
||
|
||
IF @menuId IS NOT NULL
|
||
BEGIN
|
||
SET @buttonMenuId = NULL;
|
||
SELECT TOP 1 @buttonMenuId = menu_id
|
||
FROM dbo.sys_menu
|
||
WHERE parent_id = @menuId
|
||
AND menu_type = N'F'
|
||
AND perms = @buttonPerms
|
||
ORDER BY menu_id;
|
||
|
||
IF @buttonMenuId IS NULL
|
||
BEGIN
|
||
SET @buttonMenuId = @menuId + 100;
|
||
WHILE EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @buttonMenuId)
|
||
SET @buttonMenuId = @buttonMenuId + 1;
|
||
END
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM dbo.sys_menu WHERE menu_id = @buttonMenuId)
|
||
BEGIN
|
||
INSERT INTO dbo.sys_menu (
|
||
menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon,
|
||
create_by, create_time, update_by, update_time, remark
|
||
)
|
||
VALUES (
|
||
@buttonMenuId, @buttonMenuName, @menuId, 1, N'#', N'', NULL, N'',
|
||
1, 0, N'F', N'0', N'0', @buttonPerms, N'#',
|
||
N'admin', GETDATE(), N'', NULL, N''
|
||
);
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
UPDATE dbo.sys_menu
|
||
SET menu_name = @buttonMenuName,
|
||
parent_id = @menuId,
|
||
order_num = 1,
|
||
path = N'#',
|
||
component = N'',
|
||
perms = @buttonPerms,
|
||
icon = N'#',
|
||
visible = N'0',
|
||
status = N'0',
|
||
update_by = N'admin',
|
||
update_time = GETDATE()
|
||
WHERE menu_id = @buttonMenuId;
|
||
END
|
||
END
|
||
|
||
FETCH NEXT FROM export_button_cursor INTO @buttonMenuName, @buttonParentPath, @buttonParentComponent, @buttonPerms;
|
||
END
|
||
CLOSE export_button_cursor;
|
||
DEALLOCATE export_button_cursor;
|
||
|
||
IF @menuIdIsIdentity = 1
|
||
SET IDENTITY_INSERT dbo.sys_menu OFF;
|
||
GO
|