1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
|
declare @key VARCHAR(64)
declare @Separator nvarchar(1)
declare @LocalisationKeyId int
declare @DateOfAction datetime
declare @LanguageName nvarchar(2)
set @key = 'Button.Click.Save';
set @Separator = '.';
set @DateOfAction = getdate();
set @LanguageName = 'de';
DROP TABLE #T_LocalisationTemp ;
DROP TABLE #T_KeyTemp ;
--Put in a temporary table the name found in this key
WITH CTE (Deb, Fin,level) AS
(
SELECT 1 Deb, CHARINDEX( @Separator, @key + @Separator) Fin ,0
UNION ALL
SELECT Fin + 1, CHARINDEX( @Separator, @key + @Separator, Fin + 1) ,level+1
FROM CTE
WHERE CHARINDEX( @Separator, @key + @Separator, Fin + 1 ) > 0
)
SELECT SUBSTRING(@key, Deb , Fin - Deb) as GroupKeyName,level
into #T_KeyTemp
FROM CTE ;
Select top 1 * from #T_KeyTemp
order by level Desc;
--Put in a temporary table the groupid that may fit the name of the localisation goup found in this key
Select LocalisationGroupId,LocalisationGroupParentGroupId
INTO #T_LocalisationTemp
from T_LocalisationGroup_LGR L
where L.LocalisationGroupName in
(
select GroupKeyName from #T_KeyTemp
);
--Apply recursivity to determines the possible son of the first name found in the key
WITH tree (groupid, parentgroupid,level) AS
(
Select LocalisationGroupId,LocalisationGroupParentGroupId,0 from T_LocalisationGroup_LGR
where LocalisationGroupName= substring(@key,0,patindex( '%'+@Separator+'%',@key))
Union ALL
Select LocalisationGroupId,LocalisationGroupParentGroupId,level+1 FROM #T_LocalisationTemp L
INNER JOIN tree t
ON t.groupid = L.LocalisationGroupParentGroupId
)
-- select the key that I am looking for
Select @LocalisationKeyId=LocalisationKeyId from T_LocalisationKey_LKE
where LocalisationKeyParentGroupId=
(-- select the final GroupId
Select top 1 groupid from tree
order by level desc)
and LocalisationKeyName in
(
Select top 1 GroupKeyName from #T_KeyTemp
order by level Desc
)
Select * from V_LocalisationItem
where LocalisationItemKeyId= @LocalisationKeyId
and LocalisationItemLanguage=@LanguageName
and LocalisationItemStartDate<=isnull(@DateOfAction,LocalisationItemStartDate)
and LocalisationItemEndDate>=isnull(@DateOfAction,LocalisationItemStartDate) |
Partager