· 9 years ago · Jan 10, 2017, 07:28 AM
1USE [CXC]
2GO
3/****** Object: StoredProcedure [pi].[spGetWorkplaceChildren] Script Date: 10.01.2017 09:25:22 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- =============================================
10-- Author: Ivaylo Kirilov
11-- Create date: 29.12.2016
12-- =============================================
13ALTER PROCEDURE [pi].[spGetWorkplaceChildren]
14 @orgElementId INT,
15 @userId uniqueidentifier,
16 @parentElementId INT,
17 @parentElementTypeId INT,
18 @parentElementOrgElIdInTree INT,
19 @includeArchived BIT,
20 @includeImported BIT,
21 @includeOrgElements BIT,
22 @includeChildCompanies BIT,
23 @filterWorkplaceIds dbo.Array READONLY,
24 @workplaceFilter INT,
25 @returnDataForParent BIT,
26 @hasCompanyWriteCred BIT
27AS
28BEGIN
29
30 IF @parentElementTypeId IS NULL SET @parentElementTypeId=2;
31
32 DECLARE @userTree TABLE (ID INT, ElementType TINYINT, PRIMARY KEY(ID, ElementType));
33 IF @orgelementID IS NOT NULL AND @hasCompanyWriteCred=0 BEGIN
34 INSERT @userTree SELECT DISTINCT ID, ElementType
35
36 FROM pi.GetFullWorkplaceSubtree_ProjectAdmin(@userId, @includeArchived, @includeOrgElements, @includeImported, @includeChildCompanies);
37 END
38
39 DECLARE @fullTree TABLE (
40 ID INT,
41 OrgElementID INT,
42 ElementType INT,
43 Name NVARCHAR(300),
44 ParentId INT,
45 IsImportedWorkplace BIT,
46 Archived BIT,
47 HasNonArchivedChildren BIT,
48 HasChildren BIT,
49 IsChildOfImportedWP BIT,
50 HasDelivery BIT);
51 INSERT INTO @fullTree(ID, Name, OrgElementID, ParentId, ElementType, Archived, HasChildren, HasNonArchivedChildren, IsImportedWorkplace, IsChildOfImportedWP)
52 EXEC pi.spGetFullWorkplaceSubtree_Company @orgelementID, @includeArchived, @includeOrgElements, @includeImported, @includeChildCompanies;
53
54 DECLARE @wpInvitedAndFromInvite dbo.Array;
55 INSERT @wpInvitedAndFromInvite
56 SELECT DISTINCT ID FROM @fullTree FW
57 WHERE IsImportedWorkplace=1 OR (FW.ElementType=2 AND EXISTS (SELECT NULL FROM pi.ImportedWorkplace iw (NOLOCK) WHERE iw.WorkplaceID=FW.ID AND iw.Deleted = 0));
58
59 DECLARE @wpIdsToCheckForDelivery TABLE (RootWpId INT, ChildWorkplaceID INT, PRIMARY KEY (RootWpId, ChildWorkplaceID));
60 INSERT @wpIdsToCheckForDelivery SELECT DISTINCT trees.RootWpId, trees.ID
61
62 FROM pi.GetWorkplacesSubtrees2(@wpInvitedAndFromInvite, @includeArchived, 1) trees
63 LEFT JOIN pi.Workplace iw (NOLOCK) ON iw.ID=trees.ID AND trees.IsImported=1
64 LEFT JOIN pi.OrgElement oeWh (NOLOCK) ON oeWh.ID=iw.CompanyOrgElID AND oeWh.IsWholesaler=1
65 WHERE trees.IsImported=0 OR oeWh.ID IS NOT NULL;
66
67 --- START @workplacesWithDelivery
68 DECLARE @workplacesWithDelivery dbo.Array;
69 INSERT @workplacesWithDelivery
70
71 SELECT list.RootWpId
72 FROM @wpIdsToCheckForDelivery list
73 JOIN pi.WorkplaceToDocument w2d (NOLOCK) ON list.ChildWorkplaceID=w2d.WorkplaceId
74
75 UNION
76
77 SELECT list.RootWpId
78 FROM @wpIdsToCheckForDelivery list
79 JOIN pi.WorkplaceToProduct w2p (NOLOCK) ON list.ChildWorkplaceID=w2p.WorkplaceId
80
81 UNION
82
83 SELECT list.RootWpId
84 FROM @wpIdsToCheckForDelivery list
85 JOIN pi.WorkplaceToSDS w2s (NOLOCK) ON list.ChildWorkplaceID=w2s.WorkplaceId;
86 --- END @workplacesWithDelivery
87
88 UPDATE @fullTree
89 SET HasDelivery=CASE WHEN delivering.ID IS NOT NULL THEN 1 ELSE 0 END
90 FROM @fullTree WP
91 JOIN @wpInvitedAndFromInvite deliveryScope ON deliveryScope.ID=WP.ID AND WP.ElementType=2
92 LEFT JOIN @workplacesWithDelivery delivering ON delivering.ID=WP.ID AND WP.ElementType=2;
93
94 IF @workplaceFilter=1 OR EXISTS (SELECT NULL FROM @filterWorkplaceIds)
95 BEGIN
96
97 DECLARE @MAX_RECURSION_LEVEL TINYINT = 20;
98
99 DECLARE @FilteredHierarchy TABLE (ID INT, ElementType INT, OrgElementId INT, ParentId INT, PRIMARY KEY(ID, ElementType, OrgElementId, ParentId));
100
101 WITH WP_CTE AS (
102 SELECT 0 AS RecursionLevel, WP.ID, WP.ParentId, WP.OrgElementId
103
104 FROM @fullTree WP
105 WHERE (@workplaceFilter=1 AND HasDelivery=0)
106 OR (EXISTS (SELECT NULL FROM @filterWorkplaceIds) AND WP.ElementType=2 AND WP.ID IN (SELECT ID FROM @filterWorkplaceIds))
107
108 UNION ALL
109
110 SELECT (WP_Child.RecursionLevel + 1) AS RecursionLevel, WP_Parent.ID, WP_Parent.ParentId, WP_Parent.OrgElementId
111 FROM @fullTree WP_Parent
112 JOIN WP_CTE WP_Child ON (WP_Parent.ElementType=2 AND WP_Child.ParentId=WP_Parent.ID AND WP_Child.OrgElementId=WP_Parent.OrgElementId)
113 WHERE (WP_Child.RecursionLevel + 1) <= @MAX_RECURSION_LEVEL
114 )
115
116 INSERT @FilteredHierarchy
117 SELECT DISTINCT ID, 2, OrgElementId, ISNULL(ParentId, -1) FROM WP_CTE;
118
119 WITH OE_CTE AS (
120 SELECT OE.ID, OE.ParentId
121 FROM @fullTree OE
122 JOIN @FilteredHierarchy fh ON fh.OrgElementId=OE.ID
123 WHERE OE.ElementType=1 AND fh.ParentId=-1
124
125 UNION ALL
126
127 SELECT OE_Parent.ID, OE_Parent.ParentId
128 FROM @fullTree OE_Parent
129 JOIN OE_CTE OE_Child ON (OE_Parent.ElementType=1 AND OE_Child.ParentId=OE_Parent.ID)
130 )
131
132 INSERT @FilteredHierarchy
133 SELECT DISTINCT ID, 1, ID, ISNULL(ParentId, -1) FROM OE_CTE;
134
135 DELETE @fullTree
136 FROM @fullTree tree
137 WHERE NOT EXISTS (SELECT NULL FROM @FilteredHierarchy fh WHERE fh.ID=tree.ID AND fh.ElementType=tree.ElementType);
138
139 UPDATE tree
140 SET tree.HasNonArchivedChildren =
141 CASE WHEN tree.ElementType=1 THEN 1 WHEN EXISTS (SELECT NULL FROM @fullTree t WHERE tree.ID=t.ParentID) THEN 1 ELSE 0 END
142
143 FROM @fullTree tree;
144 END
145
146 IF (OBJECT_ID('tempdb..#result') IS NOT NULL)
147 DROP TABLE #result
148 CREATE TABLE #result
149 (
150 ID INT NOT NULL ,
151 OrgElementId INT NULL,
152 Name NVARCHAR(300) NULL,
153 WorkplaceNumbers NVARCHAR(4000) NULL,
154 ImporterWorkplaceNumbers NVARCHAR(4000) NULL,
155 ParentId INT NULL,
156 Address NVARCHAR(MAX) NULL,--see orgelements address properties
157 Archived BIT NOT NULL,
158 HasChildren BIT NOT NULL,
159 Imported BIT NOT NULL,
160 IsFromImport BIT NULL,
161 HasExposedEmployees BIT NOT NULL,
162 ElementType INT NOT NULL,
163 AddedToDashboard BIT NOT NULL,
164 StartDate DATETIME NULL,
165 HasDelivery BIT NULL,
166 IsChildOfImportedWP BIT NOT NULL,
167 IsAttachedToUser BIT NOT NULL,
168 CrmID UNIQUEIDENTIFIER NULL
169 )
170
171 INSERT #result (
172 [ID], [OrgElementId], [Name], [ParentId],
173 [Address],
174 [ElementType],
175 [Archived],
176 [HasChildren],
177 [Imported],
178 [HasExposedEmployees],
179 [AddedToDashboard],
180 [StartDate],
181 [IsFromImport],
182 [IsChildOfImportedWP],
183 [IsAttachedToUser],
184 [HasDelivery],
185 [CrmID])
186 SELECT
187 FW.ID, FW.OrgElementID, FW.Name, FW.ParentId,
188 CASE WHEN FW.ElementType = 1
189 THEN CAST(OE.VisitingStreetAddress + ', <br>' + OE.VisitingPostalCode + ' ' + OE.VisitingCity AS NVARCHAR(MAX))
190 ELSE WP.Address + ', <br>' + WP.PostCode + ' ' + WP.City
191 END [Address],
192 FW.ElementType,
193 FW.Archived,
194 CASE WHEN @includeArchived=0
195 THEN FW.HasNonArchivedChildren
196 ELSE FW.HasChildren
197 END [HasChildren],
198 FW.IsImportedWorkplace,
199 0 [HasExposedEmployees],
200 CASE WHEN D.WorkplaceId IS NULL THEN 0 ELSE 1 END [AddedToDashboard],
201 WP.StartDate,
202 CASE
203 WHEN FW.IsImportedWorkplace=0 AND EXISTS
204 (SELECT NULL FROM @wpInvitedAndFromInvite fromImport WHERE fromImport.ID=FW.ID)
205 THEN 1 ELSE 0
206 END [IsFromImport],
207 FW.IsChildOfImportedWP,
208 CASE
209 WHEN @orgelementID IS NULL THEN 1
210 WHEN userTree.ID IS NOT NULL THEN 1
211 ELSE 0
212 END [IsAttachedToUser],
213 FW.HasDelivery,
214 OE.CrmID
215 FROM @fullTree FW
216 LEFT JOIN pi.Workplace WP (NOLOCK) ON FW.ID = WP.ID AND FW.ElementType=2
217 LEFT JOIN pi.OrgElement OE (NOLOCK) ON OE.ID = FW.ID AND FW.ElementType=1
218 LEFT JOIN pi.Dashboard D (NOLOCK) ON D.UserId=@userId AND D.WorkplaceId=FW.ID AND FW.ElementType=2
219 LEFT JOIN @userTree userTree ON userTree.ID=FW.ID AND userTree.ElementType=FW.ElementType
220 WHERE ( -- all workplaces for project admin
221 (@parentElementId IS NULL AND FW.ElementType=2 AND FW.ParentId=-1)
222 OR
223 (@parentElementId IS NULL AND FW.ElementType=1 AND FW.ParentId IS NULL)
224 OR
225 -- children of a workplace
226 (@parentElementTypeId=2 AND FW.ElementType=2 AND FW.ParentId=@parentElementId AND (@parentElementOrgElIdInTree IS NULL OR FW.OrgElementId=@parentElementOrgElIdInTree))
227 OR
228 -- children of an orgelement
229 (@parentElementTypeId=1 AND FW.ElementType=1 AND FW.ParentId=@parentElementId)
230 OR
231 (@parentElementTypeId=1 AND FW.ElementType=2 AND (FW.ParentId IS NULL OR FW.ParentId=-1) AND FW.OrgElementId=@parentElementId)
232 )
233 OR (@returnDataForParent=1 AND FW.ID=@parentElementId AND (
234 (@parentElementTypeId=1 AND FW.ElementType=1)
235 OR
236 (@parentElementTypeId=2 AND FW.ElementType=2 AND FW.OrgElementId=@parentElementOrgElIdInTree)
237 )
238 );
239
240 IF (@parentElementId IS NULL) BEGIN
241 UPDATE #result
242 SET ParentId = (SELECT OrgElementId FROM usr.UserData (NOLOCK) WHERE UserId=@userId)
243 WHERE ElementType=1 AND ParentId IS NULL;
244 END
245
246 DECLARE @wpIds dbo.Array;
247 INSERT @wpIds SELECT DISTINCT ID FROM #result;
248
249 CREATE INDEX IDX_Result_ID ON #result(ID)
250
251 UPDATE res
252 SET WorkplaceNumbers =
253 STUFF((
254 (SELECT ', ' + Name
255 FROM pi.WorkplaceID wid (NOLOCK)
256 WHERE wid.WorkplaceId=res.ID AND wid.Name > ''
257 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(1500)')
258 ), 1,2,''),
259 ImporterWorkplaceNumbers =
260 STUFF((
261 (SELECT ', ' + wid1.Name
262 FROM pi.ImportedWorkplace iw (NOLOCK) INNER JOIN
263 pi.WorkplaceID wid1 (NOLOCK) ON iw.Deleted=0 AND wid1.WorkplaceId=iw.ParentID INNER JOIN
264 pi.WorkplaceNotDeleted wp (NOLOCK) ON wp.ID=wid1.WorkplaceId
265 WHERE iw.WorkplaceID = res.ID AND wid1.Name > ''
266 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(1500)')
267 ), 1,2,'')--,
268 --HasExposedEmployees = eeStatus.HasExposedEmployess
269 FROM #result res
270 --INNER JOIN pi.WorkplaceExposedEmployees(@wpIds, 1) eeStatus ON eeStatus.WorkplaceId=res.ID -- -> this is the slowest part
271 WHERE res.ElementType = 2;
272
273 -- below is the sql from pi.WorkplaceExposedEmployees(@wpIds, 1)
274 -- adding indexes in both @result and @expEmployees tables speeds the query
275 DECLARE @expEmployees TABLE (
276 WorkplaceId INT NOT NULL PRIMARY KEY,
277 HasExposedEmployees BIT NOT NULL
278 )
279
280 INSERT @expEmployees SELECT DISTINCT list.ID, 1
281
282 FROM @wpIds list
283 INNER JOIN pi.EmployeeToWorkplace er (NOLOCK) ON er.WorkplaceID=list.ID;
284
285 INSERT @expEmployees SELECT DISTINCT list.RootWpId, 1
286
287 FROM pi.GetWorkplacesSubtrees2(@wpIds, 0, 0) list
288 INNER JOIN pi.EmployeeToWorkplace er (NOLOCK) ON er.WorkplaceID=list.ID
289 WHERE list.RootWpId NOT IN (SELECT r.WorkplaceId FROM @expEmployees r);
290
291 INSERT @expEmployees SELECT DISTINCT list.ID, 0
292
293 FROM @wpIds list
294 WHERE list.ID NOT IN (SELECT r.WorkplaceId FROM @expEmployees r);
295
296 UPDATE res
297 SET HasExposedEmployees = eeStatus.HasExposedEmployees
298 FROM #result res
299 JOIN @expEmployees eeStatus ON eeStatus.WorkplaceId = res.ID
300
301 SELECT * FROM #result
302END