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) |
