Epicor多语言翻译相关
获取翻译存储过程：
C# 全选
exec Ice.TranslateByHashIDList
@LangNameID=N'sch',
@CompanyID=N'3040',
@TenantID=N'',
@ProgramID=N'Ice.Core.Getcodedesclist',
@List=N'897017848',
@LoadCustomStrings=012.0.100.7版本中，LoadCustomStrings参数会传入1导致这个存储过程执行有4秒。
而在该版本工单列表查询后会遍历每行数据并执行这个存储过程。从而造成非英文状态下，查询会非常非常慢
解决办法：
- 升级新版本。
- 修改存储过程
SQL 全选
ALTER PROCEDURE [Ice].[TranslateByHashIDList]
@LangNameID nvarchar(8) = 'enu',
@CompanyID nvarchar(8) = '',
@TenantID nvarchar(36) = '',
@ProgramID nvarchar(100) = NULL,
@NomenclatureID uniqueidentifier = NULL,
@List nvarchar(max),
@LoadCustomStrings bit = 1
AS
BEGIN
-- set @List = '0'
-- exec Ice.TranslateByHashIDList @LangNameID=N'sch',@CompanyID=N'3040',@TenantID=N'',@ProgramID=N'Ice.Core.Getcodedesclist',@List=N'897017848',@LoadCustomStrings=1
-- exec Ice.TranslateByHashIDList @LangNameID=N'ens',@CompanyID=N'3040',@TenantID=N'',@ProgramID=N'Ice.Core.Getcodedesclist',@List=N'897017848',@LoadCustomStrings=1
-- exec Ice.TranslateByHashIDList @LangNameID=N'sch',@CompanyID=N'3040',@TenantID=N'',@ProgramID=N'Ice.Core.Getcodedesclist',@List=N'-2019069696',@LoadCustomStrings=1
-- exec Ice.TranslateByHashIDList @LangNameID=N'sch',@CompanyID=N'3040',@TenantID=N'',@ProgramID=N'Ice.Core.Getcodedesclist',@List=N'1598272341',@LoadCustomStrings=1
DECLARE @StartTime DATETIME = GETDATE();
print 'start time:'+CONVERT(VARCHAR(12), GETDATE(), 114)
SET NOCOUNT ON
DECLARE @LangID nvarchar(8)
DECLARE @ParentLevel int = 0
DECLARE @TempList AS TABLE
(
HashID int
)
INSERT INTO @TempList(HashID)
SELECT value FROM STRING_SPLIT(@List, ',')
CREATE TABLE #TransTable
(
Company nvarchar(8) NOT NULL,
OrgTextID int NOT NULL,
OrgHash int NOT NULL,
OrgText nvarchar(max) NOT NULL,
TransText nvarchar(max) NOT NULL,
Translated int NOT NULL,
Parent int NOT NULL,
ProgFlag int NOT NULL,
NomenclatureFlag int NOT NULL,
OrigOrgTextID int NOT NULL,
SystemFlag bit NOT NULL
)
INSERT INTO #TransTable
SELECT DISTINCT a.Company, a.OrgTextID, a.OrgHash, a.OrgText, '', 0, 0, 0, 0, 0, a.SystemFlag
FROM Ice.LangOrg a, @TempList b
WHERE a.OrgHash = b.HashID AND (a.Company = @CompanyID OR a.Company = '')
IF OBJECT_ID('tempdb..#LangTran') IS NOT NULL drop table #LangTran
CREATE TABLE #LangTran
(
[Company] [nvarchar](8) NOT NULL,
[LangNameID] [nvarchar](8) NOT NULL,
[OrgTextID] [int] NOT NULL,
[ProgramID] [nvarchar](100) NOT NULL,
[TransText] [nvarchar](max) NOT NULL,
[CustomTrans] [bit] NOT NULL,
[SortTransText] [nvarchar](50) NOT NULL,
[SystemFlag] [bit] NOT NULL,
[OrigOrgTextID] [int] NOT NULL,
[NomenclatureID] [uniqueidentifier] NULL
)
print 'before while :'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
SET @LangID = @LangNameID
WHILE (NOT @LangID IS NULL)
BEGIN
IF (NOT @ProgramID IS NULL)
BEGIN
IF (NOT @NomenclatureID IS NULL)
BEGIN
IF (@LoadCustomStrings = 1)
BEGIN
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
OrigOrgTextID = t.OrigOrgTextID,
Translated = 1,
ProgFlag = 1,
Parent = @ParentLevel,
NomenclatureFlag = 1,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrigOrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = @CompanyID
AND t.LangNameID = @LangID
AND t.ProgramID = @ProgramID
AND t.NomenclatureID = @NomenclatureID
print 'update 01:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
END
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
Translated = 1,
ProgFlag = 1,
Parent = @ParentLevel,
NomenclatureFlag = 1,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = @CompanyID
AND t.LangNameID = @LangID
AND t.ProgramID = @ProgramID
AND t.NomenclatureID = @NomenclatureID
AND t.OrigOrgTextID = 0
print 'update 02:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
END
IF (@LoadCustomStrings = 1)
BEGIN
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
OrigOrgTextID = t.OrigOrgTextID,
Translated = 1,
ProgFlag = 1,
Parent = @ParentLevel,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrigOrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = @CompanyID
AND t.LangNameID = @LangID
AND t.ProgramID = @ProgramID
AND t.NomenclatureID IS NULL
print 'update 03:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
OrigOrgTextID = t.OrigOrgTextID,
Translated = 1,
ProgFlag = 1,
Parent = @ParentLevel,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrigOrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = ''
AND t.LangNameID = @LangID
AND t.ProgramID = @ProgramID
AND t.NomenclatureID IS NULL
print 'update 04:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
END
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
Translated = 1,
ProgFlag = 1,
Parent = @ParentLevel,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = @CompanyID
AND t.LangNameID = @LangID
AND t.ProgramID = @ProgramID
AND t.NomenclatureID IS NULL
AND t.OrigOrgTextID = 0
print 'update 05:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
Translated = 1,
ProgFlag = 1,
Parent = @ParentLevel,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = ''
AND t.LangNameID = @LangID
AND t.ProgramID = @ProgramID
AND t.NomenclatureID IS NULL
AND t.OrigOrgTextID = 0
print 'update 06:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
END
IF (NOT @NomenclatureID IS NULL)
BEGIN
IF (@LoadCustomStrings = 1)
BEGIN
UPDATE #TransTable
SET TransText = t.TransText,
Translated = 1,
ProgFlag = 1,
Parent = @ParentLevel,
NomenclatureFlag = 1,
OrigOrgTextID = t.OrigOrgTextID,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrigOrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = @CompanyID
AND t.LangNameID = @LangID
AND t.ProgramID = ''
AND t.NomenclatureID = @NomenclatureID
print 'update 07:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
END
UPDATE #TransTable
SET TransText = t.TransText,
Translated = 1,
Parent = @ParentLevel,
NomenclatureFlag = 1,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = @CompanyID
AND t.LangNameID = @LangID
AND t.ProgramID = ''
AND t.NomenclatureID = @NomenclatureID
AND t.OrigOrgTextID = 0
print 'update 08:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
END
IF (@LoadCustomStrings = 1)
BEGIN
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
OrigOrgTextID = t.OrigOrgTextID,
Translated = 1,
Parent = @ParentLevel,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrigOrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = @CompanyID
AND t.LangNameID = @LangID
AND t.ProgramID = ''
AND t.NomenclatureID IS NULL
print 'update 09:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
--UPDATE #TransTable
--SET Company = t.Company,
-- OrgTextID = t.OrgTextID,
-- TransText = t.TransText,
-- OrigOrgTextID = t.OrigOrgTextID,
-- Translated = 1,
-- Parent = @ParentLevel,
-- SystemFlag = t.SystemFlag
--FROM #TransTable m
--JOIN Ice.LangTran t
--ON t.OrigOrgTextID = m.OrgTextID
--WHERE m.Translated = 0
--AND t.Company = ''
--AND t.LangNameID = @LangID
--AND t.ProgramID = ''
--AND t.NomenclatureID IS NULL
-- select * from ice.ZDataField where DBTableName='JobHead' and DBFieldName='ProcessMode'
TRUNCATE table #LangTran
insert into #LangTran(Company,LangNameID,OrgTextID,ProgramID,TransText,CustomTrans,SortTransText,SystemFlag,OrigOrgTextID,NomenclatureID)
select t.Company,t.LangNameID,t.OrgTextID,t.ProgramID,t.TransText,t.CustomTrans,t.SortTransText,t.SystemFlag,t.OrigOrgTextID,t.NomenclatureID
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrigOrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = ''
AND t.LangNameID = @LangID
AND t.ProgramID = ''
AND t.NomenclatureID IS NULL
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
OrigOrgTextID = t.OrigOrgTextID,
Translated = 1,
Parent = @ParentLevel,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN #LangTran t
ON t.OrigOrgTextID = m.OrgTextID
print 'update 10:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
END
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
Translated = 1,
Parent = @ParentLevel,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = @CompanyID
AND t.LangNameID = @LangID
AND t.ProgramID = ''
AND t.NomenclatureID IS NULL
AND t.OrigOrgTextID = 0
print 'update 11:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
UPDATE #TransTable
SET Company = t.Company,
OrgTextID = t.OrgTextID,
TransText = t.TransText,
Translated = 1,
Parent = @ParentLevel,
SystemFlag = t.SystemFlag
FROM #TransTable m
JOIN Ice.LangTran t
ON t.OrgTextID = m.OrgTextID
WHERE m.Translated = 0
AND t.Company = ''
AND t.LangNameID = @LangID
AND t.ProgramID = ''
AND t.NomenclatureID IS NULL
AND t.OrigOrgTextID = 0
print 'update 12:'+CONVERT(VARCHAR(12), GETDATE(), 114)
print 'total time:'+ CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) as nvarchar(10))
set @StartTime=GETDATE()
IF (NOT EXISTS(SELECT 1 FROM #TransTable WHERE Translated=0)) BREAK
IF (@ParentLevel = 32) BREAK
SET @ParentLevel = @ParentLevel + 1
SET @LangID = (SELECT TOP 1 ParentLangID
FROM Ice.SysCompany AS c
INNER JOIN Ice.LangName AS l
ON (l.LangNameID = @LangNameID AND l.Company = @CompanyID AND l.CompanyVisibility = 0)
OR (l.LangNameID = @LangNameID AND l.CompanyVisibility = 10)
AND ParentLangID <> @LangID
WHERE c.Company = @CompanyID OR c.TenantID = @TenantID
ORDER BY l.[CompanyVisibility] ASC);
END
SELECT
Company,
OrgTextID as TextID,
OrgHash as HashCode,
OrgText as OriginalText,
TransText as TranslatedText,
Parent as ParentLevel,
Translated as HasTranslation,
ProgFlag as ProgSpecific,
NomenclatureFlag as NomenclatureSpecific,
OrigOrgTextID as OriginalTextID,
SystemFlag
FROM #TransTable
DROP TABLE #TransTable
END
版权声明：本文为YES开发框架网发布内容,转载请附上原文出处连接
epicor 张国生
