-- =============================================
|
-- MES 接口日志页面数据库配置脚本
|
-- 创建日期: 2026-04-13
|
-- 说明: 创建性能索引、菜单记录、数据字典
|
-- =============================================
|
|
-- =============================================
|
-- Step 1: 创建性能索引
|
-- =============================================
|
|
-- 接口类型索引
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_MesApiLog_ApiType' AND object_id = OBJECT_ID('Dt_MesApiLog'))
|
BEGIN
|
CREATE INDEX IX_MesApiLog_ApiType ON Dt_MesApiLog(ApiType);
|
PRINT '索引 IX_MesApiLog_ApiType 已创建';
|
END
|
ELSE
|
PRINT '索引 IX_MesApiLog_ApiType 已存在';
|
|
-- 创建时间索引
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_MesApiLog_CreateDate' AND object_id = OBJECT_ID('Dt_MesApiLog'))
|
BEGIN
|
CREATE INDEX IX_MesApiLog_CreateDate ON Dt_MesApiLog(CreateDate DESC);
|
PRINT '索引 IX_MesApiLog_CreateDate 已创建';
|
END
|
ELSE
|
PRINT '索引 IX_MesApiLog_CreateDate 已存在';
|
|
-- 成功状态索引
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_MesApiLog_IsSuccess' AND object_id = OBJECT_ID('Dt_MesApiLog'))
|
BEGIN
|
CREATE INDEX IX_MesApiLog_IsSuccess ON Dt_MesApiLog(IsSuccess);
|
PRINT '索引 IX_MesApiLog_IsSuccess 已创建';
|
END
|
ELSE
|
PRINT '索引 IX_MesApiLog_IsSuccess 已存在';
|
|
-- 创建人索引
|
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_MesApiLog_Creator' AND object_id = OBJECT_ID('Dt_MesApiLog'))
|
BEGIN
|
CREATE INDEX IX_MesApiLog_Creator ON Dt_MesApiLog(Creator);
|
PRINT '索引 IX_MesApiLog_Creator 已创建';
|
END
|
ELSE
|
PRINT '索引 IX_MesApiLog_Creator 已存在';
|
|
PRINT '=====================================';
|
PRINT '索引创建完成';
|
PRINT '=====================================';
|
GO
|
|
-- =============================================
|
-- Step 2: 添加菜单记录
|
-- =============================================
|
|
-- 查询系统管理菜单的 ID
|
DECLARE @SystemMenuId INT;
|
SELECT TOP 1 @SystemMenuId = Id FROM Dt_Menu WHERE MenuName LIKE '%系统%' OR MenuName LIKE '%System%';
|
|
IF @SystemMenuId IS NULL
|
BEGIN
|
PRINT '警告: 未找到系统管理菜单，请手动确认菜单 ID';
|
END
|
ELSE
|
BEGIN
|
PRINT '系统管理菜单 ID: ' + CAST(@SystemMenuId AS VARCHAR(10));
|
|
-- 检查 MES 接口日志菜单是否已存在
|
IF NOT EXISTS (SELECT 1 FROM Dt_Menu WHERE Url = '/Mes_Log')
|
BEGIN
|
INSERT INTO Dt_Menu (ParentId, MenuName, Url, Component, Permission, Sort, Icon, CreateDate, Modifier)
|
VALUES (
|
@SystemMenuId,
|
'MES接口日志',
|
'/Mes_Log',
|
'views/system/Mes_Log',
|
'Mes_Log:view',
|
(SELECT ISNULL(MAX(Sort), 0) + 1 FROM Dt_Menu WHERE ParentId = @SystemMenuId),
|
'el-icon-document',
|
GETDATE(),
|
'System'
|
);
|
PRINT 'MES接口日志菜单已添加，ID: ' + CAST(SCOPE_IDENTITY() AS VARCHAR(10));
|
END
|
ELSE
|
BEGIN
|
PRINT 'MES接口日志菜单已存在';
|
END
|
END
|
GO
|
|
-- =============================================
|
-- Step 3: 添加数据字典记录
|
-- =============================================
|
|
-- 接口类型字典
|
DECLARE @DictId INT;
|
SELECT @DictId = Id FROM Dt_Dictionary WHERE DictKey = 'mesApiType';
|
|
IF @DictId IS NULL
|
BEGIN
|
INSERT INTO Dt_Dictionary (DictKey, DictValue, CreateDate, Modifier)
|
VALUES ('mesApiType', 'MES接口类型', GETDATE(), 'System');
|
SET @DictId = SCOPE_IDENTITY();
|
PRINT 'MES接口类型字典已创建，ID: ' + CAST(@DictId AS VARCHAR(10));
|
END
|
ELSE
|
BEGIN
|
PRINT 'MES接口类型字典已存在，ID: ' + CAST(@DictId AS VARCHAR(10));
|
END
|
|
-- 添加接口类型选项
|
IF NOT EXISTS (SELECT 1 FROM Dt_DictionaryList WHERE DictId = @DictId AND [Key] = 'BindContainer')
|
BEGIN
|
INSERT INTO Dt_DictionaryList (DictId, Value, [Key], DisplayOrder, CreateDate, Modifier)
|
VALUES (@DictId, '电芯绑定', 'BindContainer', 1, GETDATE(), 'System');
|
PRINT '接口类型选项已添加: BindContainer (电芯绑定)';
|
END
|
|
IF NOT EXISTS (SELECT 1 FROM Dt_DictionaryList WHERE DictId = @DictId AND [Key] = 'UnBindContainer')
|
BEGIN
|
INSERT INTO Dt_DictionaryList (DictId, Value, [Key], DisplayOrder, CreateDate, Modifier)
|
VALUES (@DictId, '电芯解绑', 'UnBindContainer', 2, GETDATE(), 'System');
|
PRINT '接口类型选项已添加: UnBindContainer (电芯解绑)';
|
END
|
|
IF NOT EXISTS (SELECT 1 FROM Dt_DictionaryList WHERE DictId = @DictId AND [Key] = 'ContainerNgReport')
|
BEGIN
|
INSERT INTO Dt_DictionaryList (DictId, Value, [Key], DisplayOrder, CreateDate, Modifier)
|
VALUES (@DictId, 'NG上报', 'ContainerNgReport', 3, GETDATE(), 'System');
|
PRINT '接口类型选项已添加: ContainerNgReport (NG上报)';
|
END
|
|
IF NOT EXISTS (SELECT 1 FROM Dt_DictionaryList WHERE DictId = @DictId AND [Key] = 'InboundInContainer')
|
BEGIN
|
INSERT INTO Dt_DictionaryList (DictId, Value, [Key], DisplayOrder, CreateDate, Modifier)
|
VALUES (@DictId, '托盘进站', 'InboundInContainer', 4, GETDATE(), 'System');
|
PRINT '接口类型选项已添加: InboundInContainer (托盘进站)';
|
END
|
|
IF NOT EXISTS (SELECT 1 FROM Dt_DictionaryList WHERE DictId = @DictId AND [Key] = 'OutboundInContainer')
|
BEGIN
|
INSERT INTO Dt_DictionaryList (DictId, Value, [Key], DisplayOrder, CreateDate, Modifier)
|
VALUES (@DictId, '托盘出站', 'OutboundInContainer', 5, GETDATE(), 'System');
|
PRINT '接口类型选项已添加: OutboundInContainer (托盘出站)';
|
END
|
|
-- 调用状态字典
|
DECLARE @StatusDictId INT;
|
SELECT @StatusDictId = Id FROM Dt_Dictionary WHERE DictKey = 'mesApiStatus';
|
|
IF @StatusDictId IS NULL
|
BEGIN
|
INSERT INTO Dt_Dictionary (DictKey, DictValue, CreateDate, Modifier)
|
VALUES ('mesApiStatus', 'MES接口状态', GETDATE(), 'System');
|
SET @StatusDictId = SCOPE_IDENTITY();
|
PRINT 'MES接口状态字典已创建，ID: ' + CAST(@StatusDictId AS VARCHAR(10));
|
END
|
ELSE
|
BEGIN
|
PRINT 'MES接口状态字典已存在，ID: ' + CAST(@StatusDictId AS VARCHAR(10));
|
END
|
|
-- 添加状态选项
|
IF NOT EXISTS (SELECT 1 FROM Dt_DictionaryList WHERE DictId = @StatusDictId AND [Key] = 'true')
|
BEGIN
|
INSERT INTO Dt_DictionaryList (DictId, Value, [Key], DisplayOrder, CreateDate, Modifier)
|
VALUES (@StatusDictId, '成功', 'true', 1, GETDATE(), 'System');
|
PRINT '状态选项已添加: true (成功)';
|
END
|
|
IF NOT EXISTS (SELECT 1 FROM Dt_DictionaryList WHERE DictId = @StatusDictId AND [Key] = 'false')
|
BEGIN
|
INSERT INTO Dt_DictionaryList (DictId, Value, [Key], DisplayOrder, CreateDate, Modifier)
|
VALUES (@StatusDictId, '失败', 'false', 2, GETDATE(), 'System');
|
PRINT '状态选项已添加: false (失败)';
|
END
|
|
PRINT '=====================================';
|
PRINT '数据字典配置完成';
|
PRINT '=====================================';
|
GO
|
|
-- =============================================
|
-- 执行完毕
|
-- =============================================
|
PRINT '=====================================';
|
PRINT 'MES 接口日志页面数据库配置执行完毕';
|
PRINT '=====================================';
|
