/
Cofoundry.CustomEntity_ReOrder.sql
81 lines (71 loc) · 2.35 KB
/
Cofoundry.CustomEntity_ReOrder.sql
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
71
72
73
74
75
76
77
78
79
80
81
create procedure Cofoundry.CustomEntity_ReOrder
(
@CustomEntityDefinitionCode char(6),
@CustomEntityIds varchar(max),
@LocaleId int,
@UpdatedIds varchar(max) output
)
as
begin
-- Need to first convert the ids and set the ordering
declare @AllOrderedIds table
(
CustomEntityId int not null,
Ordering int identity (1, 1) not null
)
-- can't do any modifications here because we dont want to affect the natural ordering
insert into @AllOrderedIds (CustomEntityId)
select i.number
from (select number from Cofoundry.IntListToTbl(@CustomEntityIds)) i
-- Create entity ordering
declare @OrderedEntities table
(
CustomEntityId int not null,
Ordering int identity (1, 1) not null,
OriginalOrdering int null
)
insert into @OrderedEntities (CustomEntityId, OriginalOrdering)
select c.CustomEntityId, min(c.Ordering)
from @AllOrderedIds i
inner join Cofoundry.CustomEntity c on c.CustomEntityId = i.CustomEntityId
and c.CustomEntityDefinitionCode = @CustomEntityDefinitionCode
and (@LocaleId is null or c.LocaleId = @LocaleId)
group by c.CustomEntityId
order by min(i.Ordering)
-- Find out which entities are having thier ordering rmeoved
declare @OrderingRemovedEntities table
(
CustomEntityId int not null
)
insert into @OrderingRemovedEntities
select c.CustomEntityId
from Cofoundry.CustomEntity c
left outer join @OrderedEntities oe on c.CustomEntityId = oe.CustomEntityId
where CustomEntityDefinitionCode = @CustomEntityDefinitionCode
and oe.CustomEntityId is null
and c.Ordering is not null
and (@LocaleId is null or c.LocaleId = @LocaleId)
-- Update the ordering records
update Cofoundry.CustomEntity
set Ordering = oe.Ordering
from Cofoundry.CustomEntity c
inner join @OrderedEntities oe on c.CustomEntityId = oe.CustomEntityId
where IsNull(oe.Ordering, -1) <> IsNull(oe.OriginalOrdering, -1)
update Cofoundry.CustomEntity
set Ordering = null
from Cofoundry.CustomEntity c
inner join @OrderingRemovedEntities ore on c.CustomEntityId = ore.CustomEntityId
-- Return a list of modified entity ids
select @UpdatedIds = (
select cast(convert(varchar(10), CustomEntityId) + ',' AS varchar(max))
from (
select CustomEntityId
from @OrderedEntities
where IsNull(Ordering, -1) <> IsNull(OriginalOrdering, -1)
union
select CustomEntityId
from @OrderingRemovedEntities
) x
for xml path ('')
)
end