· 7 years ago · Oct 05, 2018, 07:36 AM
1--758645
2
3 --@wpIds, @countryId, 1, @summaryContentTypeId
4--@wpIds, 758645
5DECLARE @countryId int = 2;
6DECLARE @updateSpecificWorkplace BIT = 1
7DECLARE @summaryContentTypeId INT = 2
8
9
10
11DECLARE @selectedProductTypes dbo.ArrayIFCGuid;
12DECLARE @wpIds dbo.Array;
13INSERT @wpIds SELECT 758645;
14--EXEC idx.UpdateDashboardSummary @wpIds, @countryId, @updateSpecificWorkplace, 2;
15
16---------------
17
18 --@wpIds dbo.Array READONLY,
19 --@countryId INT,
20 --@updateSpecificWorkplace BIT = 1,
21 --@summaryContentTypeId INT = NULL
22
23 DECLARE @workplaceIdsTemp dbo.Array;
24 INSERT INTO @workplaceIdsTemp SELECT * FROM @wpIds;
25 DECLARE @countryIdTemp TINYINT = @countryId;
26 DECLARE @updateSpecificWorkplaceTemp BIT = @updateSpecificWorkplace;
27 DECLARE @summaryContentTypeIdTemp TINYINT = @summaryContentTypeId;
28
29 DECLARE @currentDate DATETIME2(2) = GETDATE();
30 INSERT idx.DashboardSummary (WorkplaceId, UpdateDate, IndexDate)
31 SELECT ID, @currentDate, CASE WHEN @updateSpecificWorkplaceTemp = 1 THEN NULL ELSE @currentDate END
32 FROM @workplaceIdsTemp wIds
33 WHERE wIds.ID NOT IN (SELECT D.WorkplaceId FROM idx.DashboardSummary D (NOLOCK));
34
35 DECLARE @emptyProductTypeList dbo.ArrayIFCGuid;
36
37 --select * from @workplaceIdsTemp;
38 --select @summaryContentTypeIdTemp;
39 --select * from @emptyProductTypeList;
40 --select @countryIdTemp;
41 --select @updateSpecificWorkplace;
42
43 --EXEC pi.FillWorkplaceDashboardSummary @workplaceIdsTemp, 0, 1, 1, 1, 0, @summaryContentTypeIdTemp, @emptyProductTypeList, @countryIdTemp, 0, 1, @updateSpecificWorkplace;
44
45
46 -------------------
47
48
49 -- prevent parameters sniffing
50 --DECLARE @workplaceIdsTemp dbo.Array;
51 --INSERT INTO @workplaceIdsTemp SELECT * FROM @workplaceIds;
52 DECLARE @includeArchivedWorkplacesTemp BIT = 0;
53 DECLARE @includeChildWorkplacesTemp BIT = 1;
54 DECLARE @includeImportedWorkplacesTemp BIT = 1;
55 DECLARE @includeImportedWholesalerWorkplacesTemp BIT = 1;
56 DECLARE @archivedSDSsTemp BIT = 0;
57 --DECLARE @summaryContentTypeIdTemp TINYINT = @summaryContentTypeId;
58 DECLARE @selectedProductTypesTemp dbo.ArrayIFCGuid;
59 INSERT INTO @selectedProductTypesTemp SELECT * FROM @emptyProductTypeList;
60 --DECLARE @countryIdTemp TINYINT = @countryId;
61 DECLARE @loadDataFromDbTemp BIT = 0;
62 DECLARE @updateDataTemp BIT = 1;
63 --DECLARE @updateSpecificWorkplaceTemp BIT = @updateSpecificWorkplace;
64
65 IF @loadDataFromDbTemp = 0
66 BEGIN
67
68 IF (OBJECT_ID('tempdb..#WorkplaceDashboardSummary') IS NOT NULL)
69 DROP TABLE #WorkplaceDashboardSummary;
70
71 CREATE TABLE #WorkplaceDashboardSummary (
72 WorkplaceId INT NOT NULL,
73 Name NVARCHAR(300) NULL,
74 IsArchived BIT NULL,
75 AddressStreet NVARCHAR(1000) NULL,
76 AddressCity NVARCHAR(100) NULL,
77 AddressPostCode NVARCHAR(50) NULL,
78 ProjectNumbers NVARCHAR(2000) NULL,
79 CompanyOrgElId INT NOT NULL,
80 WorkplaceType TINYINT NOT NULL,
81 Products INT,
82 ProductsInNonCompliance INT,
83 ProductsInNonComplianceDetails XML,
84 ProductsWithFilterCriteria INT,
85 ProductsWithWorkplaceComments INT,
86 SDSs INT,
87 SDSsWithFilterCriteria INT,
88 SDSsHazardousWaste INT,
89 SDSsWithWorkplaceComments INT,
90 SDSsWithWorkplaceInstructions INT,
91 SDSsWithWorkplaceRiskAssessments INT,
92 SDSsWithWorkplaceRiskAssessmentsOrComments INT,
93 Documents INT,
94 InvitedCompanies INT,
95 Departments INT,
96 Lists INT,
97 Orders INT,
98 SuggestedUwp INT,
99 Deviations INT,
100 Subprojects INT, -- Collaborate subprojects count
101 PartialDeviations INT -- Temp Collaborate deviations count
102 );
103
104 DECLARE @vegasCountryIndex INT = (
105 SELECT VegasCountryIndex FROM dbo.Country (NOLOCK) WHERE Id = @countryIdTemp
106 );
107
108 DECLARE @isVisibleForDataRequirements BIT = (
109 SELECT CASE WHEN EXISTS (SELECT NULL FROM dbo.FunctionalityVisibleByCountry('DataRequirements') WHERE IsVisible = 1 AND CountryId = @countryIdTemp) THEN 1 ELSE 0 END
110 );
111
112 DECLARE @isVisibleForProductsMissingName BIT = (
113 SELECT CASE WHEN EXISTS (SELECT NULL FROM dbo.FunctionalityVisibleByCountry('ProductsMissingName') WHERE IsVisible = 1 AND CountryId = @countryIdTemp) THEN 1 ELSE 0 END
114 );
115
116 DECLARE @isVisibleForProductNonComplianceDashboardEcoRules BIT = (
117 SELECT CASE WHEN EXISTS (SELECT NULL FROM dbo.FunctionalityVisibleByCountry('ProductNonComplianceDashboardEcoRules') WHERE IsVisible = 1) THEN 1 ELSE 0 END
118 );
119
120 DECLARE @isVisibleForProductsWithFilterCriteria BIT = (
121 SELECT CASE WHEN EXISTS (SELECT NULL FROM dbo.FunctionalityVisibleByCountry('ProductsWithFilterCriteria') WHERE IsVisible = 1) THEN 1 ELSE 0 END
122 );
123
124 DECLARE @deviationProductWithFilterCriteria TINYINT = 7,
125 @deviationProductWithEcoRule TINYINT = 11,
126 @deviationProductMissingDOP TINYINT = 3,
127 @deviationProductMissingSDS TINYINT = 4,
128 @deviationProductMissingA20 TINYINT = 5,
129 @deviationProductMissingHEA9 TINYINT = 6,
130 @deviationProductMissingRequiredData TINYINT = 12,
131 @deviationProductInBreach TINYINT = 13,
132 @deviationProductMissingName TINYINT = 14,
133 @deviationProductIsBlack TINYINT = 15,
134 @deviationRequestedProductType TINYINT = 16,
135 @deviationProductMissingHEA02 TINYINT = 17,
136 @deviationProductMissingA202016 TINYINT = 18,
137 @objectTypeProduct TINYINT = 25,
138 @objectTypeProductType TINYINT = 36,
139 @deviationInvitedWithoutDelivery TINYINT = 1,
140 @objectTypeWorkplace TINYINT = 2,
141 @taskStatusOpen TINYINT = 0,
142 @taskStatusClosed TINYINT = 1,
143 @nonCompliantProductComplianceStatus TINYINT = 0,
144 @partlyProductComplianceStatus TINYINT = 1,
145 @ProductComplianceRuleHasMissingRequiredData TINYINT = 7,
146 @ProductComplianceRuleHasInBreachWithRequirements TINYINT = 8,
147 @ProductComplianceRuleHasMissingName TINYINT = 9;
148
149 DECLARE @productRuleRequestedProductType TINYINT;
150 SET @productRuleRequestedProductType = (SELECT TOP 1 [ID] FROM [pi].[ProductRuleConfiguration] WHERE DeviationType = @deviationRequestedProductType);
151
152 DECLARE @productRuleProductIsBlack TINYINT;
153 SET @productRuleProductIsBlack = (SELECT TOP 1 [ID] FROM [pi].[ProductRuleConfiguration] WHERE DeviationType = @deviationProductIsBlack);
154
155 DECLARE @productRuleProductMissingRequiredData TINYINT;
156 SET @productRuleProductMissingRequiredData = (SELECT TOP 1 [ID] FROM [pi].[ProductRuleConfiguration] WHERE DeviationType = @deviationProductMissingRequiredData);
157
158 DECLARE @productRuleProductInBreachWithRequirements TINYINT;
159 SET @productRuleProductInBreachWithRequirements = (SELECT TOP 1 [ID] FROM [pi].[ProductRuleConfiguration] WHERE DeviationType = @deviationProductInBreach);
160
161 IF (OBJECT_ID('tempdb..#deviationObjectsTemp_FWDS') IS NOT NULL)
162 DROP TABLE #deviationObjectsTemp_FWDS;
163 CREATE TABLE #deviationObjectsTemp_FWDS (
164 WorkplaceId INT, ObjectType TINYINT, ObjectId INT, DeviationType TINYINT, FilterKey NVARCHAR (445) COLLATE SQL_Latin1_General_CP1_CS_AS, ToBeClosed BIT
165 --UNIQUE (WorkplaceId, ObjectType, ObjectId, DeviationType, FilterKey)
166 );
167 --CREATE INDEX IDX_deviationObjectsTemp ON #deviationObjectsTemp_FWDS(WorkplaceId, ObjectType, ObjectId, DeviationType, FilterKey);
168
169 IF (OBJECT_ID('tempdb..#deviationObjects_FWDS') IS NOT NULL)
170 DROP TABLE #deviationObjects_FWDS;
171 CREATE TABLE #deviationObjects_FWDS (
172 WorkplaceId INT, ObjectType TINYINT, ObjectId INT, DeviationType TINYINT, FilterKey NVARCHAR (445) COLLATE SQL_Latin1_General_CP1_CS_AS, ToBeClosed BIT,
173 UNIQUE (WorkplaceId, ObjectType, ObjectId, DeviationType, FilterKey)
174 );
175
176 IF (OBJECT_ID('tempdb..#subtreesTemp_FWDS') IS NOT NULL)
177 DROP TABLE #subtreesTemp_FWDS;
178 CREATE TABLE #subtreesTemp_FWDS (
179 WorkplaceId INT, ChildWorkplaceId INT, ParentId INT, IsImported BIT, HasChecklistA20 BIT, HasChecklistA202016 BIT,
180 --UNIQUE (WorkplaceId, ChildWorkplaceId, ParentId, IsImported)
181 );
182 --CREATE INDEX IDX_subtreesTemp ON #subtreesTemp_FWDS(WorkplaceId, ChildWorkplaceId, ParentId, IsImported);
183
184 IF (OBJECT_ID('tempdb..#subtrees_FWDS') IS NOT NULL)
185 DROP TABLE #subtrees_FWDS;
186 CREATE TABLE #subtrees_FWDS (
187 WorkplaceId INT, ChildWorkplaceId INT, ParentId INT, IsImported BIT, HasChecklistA20 BIT, HasChecklistA202016 BIT
188 UNIQUE (WorkplaceId, ChildWorkplaceId, ParentId, IsImported)
189 );
190
191 IF (OBJECT_ID('tempdb..#subtreesForBlackProducts') IS NOT NULL)
192 DROP TABLE #subtreesForBlackProducts;
193 CREATE TABLE #subtreesForBlackProducts (
194 WorkplaceId INT, RootWorkplaceId INT,
195 --UNIQUE (WorkplaceId, ChildWorkplaceId, ParentId, IsImported)
196 );
197
198 DECLARE @wp2sds dbo.ArrayIntPair;
199
200 IF (OBJECT_ID('tempdb..#filterComplianceTemp_FWDS') IS NOT NULL)
201 DROP TABLE #filterComplianceTemp_FWDS
202 CREATE TABLE #filterComplianceTemp_FWDS (
203 WorkplaceID INT, CountryId INT, SDSLatestVersionId INT, IsHazardousWaste BIT, NonCompliantWithFilters BIT, IsCompliantWithLists BIT,
204 --UNIQUE(WorkplaceID, SDSLatestVersionId)
205 );
206 --CREATE INDEX IDX_filterComplianceTemp ON #filterComplianceTemp_FWDS(WorkplaceId, SDSLatestVersionId);
207
208 IF (OBJECT_ID('tempdb..#filterCompliance_FWDS') IS NOT NULL)
209 DROP TABLE #filterCompliance_FWDS
210 CREATE TABLE #filterCompliance_FWDS (
211 WorkplaceID INT, CountryId INT, SDSLatestVersionId INT, IsHazardousWaste BIT, NonCompliantWithFilters BIT, IsCompliantWithLists BIT,
212 UNIQUE(WorkplaceID, SDSLatestVersionId)
213 );
214
215 IF (OBJECT_ID('tempdb..#workplaceProdNDocTemp_FWDS') IS NOT NULL)
216 DROP TABLE #workplaceProdNDocTemp_FWDS;
217 CREATE TABLE #workplaceProdNDocTemp_FWDS (
218 WorkplaceId INT, ObjectID INT, IsProduct BIT, HasChecklistA20 BIT, HasChecklistA202016 BIT
219 --UNIQUE (IsProduct, ObjectID, WorkplaceId)
220 );
221 --CREATE INDEX IDX_workplaceProdNDocTemp ON #workplaceProdNDocTemp_FWDS(WorkplaceId, ObjectID, IsProduct);
222
223 IF (OBJECT_ID('tempdb..#workplaceProdNDoc_FWDS') IS NOT NULL)
224 DROP TABLE #workplaceProdNDoc_FWDS;
225 CREATE TABLE #workplaceProdNDoc_FWDS (
226 WorkplaceId INT, ObjectID INT, IsProduct BIT, HasChecklistA20 BIT, HasChecklistA202016 BIT
227 UNIQUE (IsProduct, ObjectID, WorkplaceId)
228 );
229
230 IF (OBJECT_ID('tempdb..#workplaceAttributesTemp_FWDS') IS NOT NULL)
231 DROP TABLE #workplaceAttributesTemp_FWDS;
232 CREATE TABLE #workplaceAttributesTemp_FWDS (ObjectId INT, AttributeType TINYINT);
233 --CREATE INDEX IDX_workplaceAttributesTemp ON #workplaceAttributesTemp_FWDS(ObjectId, AttributeType);
234
235 IF (OBJECT_ID('tempdb..#workplaceAttributes_FWDS') IS NOT NULL)
236 DROP TABLE #workplaceAttributes_FWDS;
237 CREATE TABLE #workplaceAttributes_FWDS (ObjectId INT, AttributeType TINYINT, UNIQUE (ObjectId, AttributeType));
238
239 IF (OBJECT_ID('tempdb..#workplaceProductsInNonComplianceTemp_FWDS') IS NOT NULL)
240 DROP TABLE #workplaceProductsInNonComplianceTemp_FWDS;
241 CREATE TABLE #workplaceProductsInNonComplianceTemp_FWDS (
242 WorkplaceId INT, ProductDistributorID INT, ProductRuleConfigurationId INT, EcoRuleId VARCHAR(22) COLLATE SQL_Latin1_General_CP1_CS_AS
243 --UNIQUE (WorkplaceId, ProductDistributorID, ProductRuleConfigurationId, EcoRuleId)
244 );
245 --CREATE INDEX IDX_workplaceProductsInNonComplianceTemp ON #workplaceProductsInNonComplianceTemp_FWDS(WorkplaceId, ProductDistributorID, ProductRuleConfigurationId, EcoRuleId);
246
247 IF (OBJECT_ID('tempdb..#workplaceProductsInNonCompliance_FWDS') IS NOT NULL)
248 DROP TABLE #workplaceProductsInNonCompliance_FWDS;
249 CREATE TABLE #workplaceProductsInNonCompliance_FWDS (
250 WorkplaceId INT, ProductDistributorID INT, ProductRuleConfigurationId INT, EcoRuleId VARCHAR(22) COLLATE SQL_Latin1_General_CP1_CS_AS,
251 UNIQUE (WorkplaceId, ProductDistributorID, ProductRuleConfigurationId, EcoRuleId)
252 );
253
254 IF (OBJECT_ID('tempdb..#workplaceProductsTypesTemp_FWDS') IS NOT NULL)
255 DROP TABLE #workplaceProductsTypesTemp_FWDS;
256 CREATE TABLE #workplaceProductsTypesTemp_FWDS (
257 ProductDistributorID INT, ProductTypeGuid VARCHAR(22) COLLATE SQL_Latin1_General_CP1_CS_AS,
258 --UNIQUE (ProductDistributorID, ProductTypeGuid)
259 );
260 --CREATE INDEX IDX_workplaceProductsTypesTemp ON #workplaceProductsTypesTemp_FWDS(ProductDistributorID, ProductTypeGuid);
261
262 IF (OBJECT_ID('tempdb..#workplaceProductsTypes_FWDS') IS NOT NULL)
263 DROP TABLE #workplaceProductsTypes_FWDS;
264 CREATE TABLE #workplaceProductsTypes_FWDS (
265 ProductDistributorID INT, ProductTypeGuid VARCHAR(22) COLLATE SQL_Latin1_General_CP1_CS_AS,
266 UNIQUE (ProductDistributorID, ProductTypeGuid)
267 );
268
269 IF (OBJECT_ID('tempdb..#workplaceProdFilterTemp_FWDS') IS NOT NULL)
270 DROP TABLE #workplaceProdFilterTemp_FWDS;
271 CREATE TABLE #workplaceProdFilterTemp_FWDS (
272 WorkplaceId INT, FilterID INT, ProductDistributorId INT, StateID INT);
273 --CREATE INDEX IDX_workplaceProdFilterTemp ON #workplaceProdFilterTemp_FWDS(WorkplaceId, FilterID, ProductDistributorId, StateID);
274
275 IF (OBJECT_ID('tempdb..#workplaceProdFilter_FWDS') IS NOT NULL)
276 DROP TABLE #workplaceProdFilter_FWDS;
277 CREATE TABLE #workplaceProdFilter_FWDS (
278 WorkplaceId INT, FilterID INT, ProductDistributorId INT, StateID INT,
279 UNIQUE (WorkplaceId, FilterID, ProductDistributorId, StateID)
280 )
281
282 INSERT #WorkplaceDashboardSummary (
283 WorkplaceId, WorkplaceType, CompanyOrgElId, Name, IsArchived,
284 AddressStreet, AddressCity, AddressPostCode,
285 ProjectNumbers)
286 SELECT
287 WP.ID, WP.Type, WP.CompanyOrgElID, WP.Name, WP.IsArchived,
288 WP.Address, WP.City, WP.PostCode,
289 STUFF(((
290 SELECT ', ' + Name
291 FROM pi.WorkplaceID wid (NOLOCK)
292 WHERE wid.WorkplaceId=list.ID AND wid.Name > ''
293 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(2000)')
294 ),1,2,'') [ProjectNumbers]
295 FROM @workplaceIdsTemp list
296 JOIN pi.Workplace WP (NOLOCK) ON WP.ID=list.ID;
297
298 DECLARE -- public enum SummaryContentType
299 @dashboard TINYINT = 0, -- WorkplaceDashboard = 0,
300 @deviations TINYINT = 1, -- Deviations = 1,
301 @products TINYINT = 2, -- Products = 2,
302 @chemicals TINYINT = 3; -- Chemicals = 3
303
304 INSERT #subtreesTemp_FWDS SELECT
305 list.ID, tree.ID, tree.ParentID, tree.IsImported, pi.HasChemicalChecklist(list.ID, default), pi.HasChemicalChecklist(list.ID, 25) -- Check for BREEAM 2016 chemical list
306 FROM @workplaceIdsTemp list
307 INNER JOIN pi.Workplace w (NOLOCK) ON w.ID=list.ID
308 CROSS APPLY pi.GetWorkplaceDashboardScope(w.ID, w.IsArchived|@includeArchivedWorkplacesTemp, @includeChildWorkplacesTemp, @includeImportedWorkplacesTemp, @includeImportedWholesalerWorkplacesTemp, 1) tree
309 OPTION (OPTIMIZE FOR UNKNOWN);
310
311 INSERT #subtrees_FWDS SELECT DISTINCT * FROM #subtreesTemp_FWDS;
312
313 IF @summaryContentTypeIdTemp IS NULL OR @summaryContentTypeIdTemp=@dashboard OR @summaryContentTypeIdTemp=@chemicals BEGIN
314 INSERT @wp2sds (
315 ID1, ID2)
316 SELECT DISTINCT
317 list.WorkplaceId, wts.SDSId
318 FROM pi.WorkplaceToSDS wts (NOLOCK)
319 INNER JOIN #subtrees_FWDS list ON list.ChildWorkplaceId=wts.WorkplaceId
320 WHERE (@archivedSDSsTemp=0 AND wts.ArchivedDate IS NULL)
321 OR (@archivedSDSsTemp=1 AND wts.ArchivedDate IS NOT NULL)
322 OPTION (OPTIMIZE FOR UNKNOWN);
323
324 /* Get Filter Compliance */
325 INSERT #filterCompliance_FWDS
326 EXEC idx.SDSsFilterCompliance @wp2sds;
327
328 UPDATE DS
329 SET DS.SDSs = (SELECT COUNT(DISTINCT f.SDSLatestVersionId) FROM #filterCompliance_FWDS f WHERE f.WorkplaceID=DS.WorkplaceID),
330 DS.SDSsWithFilterCriteria = (SELECT COUNT(DISTINCT f.SDSLatestVersionId) FROM #filterCompliance_FWDS f WHERE f.WorkplaceID=DS.WorkplaceID AND f.NonCompliantWithFilters = 1),
331 DS.SDSsHazardousWaste = (SELECT COUNT(DISTINCT f.SDSLatestVersionId) FROM #filterCompliance_FWDS f WHERE f.WorkplaceID=DS.WorkplaceID AND f.IsHazardousWaste = 1),
332 DS.Lists = (SELECT COUNT(DISTINCT f.SDSLatestVersionId) FROM #filterCompliance_FWDS f WHERE f.WorkplaceID=DS.WorkplaceID AND f.IsCompliantWithLists = 0)
333 FROM #WorkplaceDashboardSummary DS
334 OPTION (OPTIMIZE FOR UNKNOWN);
335 END
336
337 /*products are necessary on all summary contents*/
338 IF EXISTS (SELECT NULL FROM @selectedProductTypesTemp) BEGIN
339 INSERT #workplaceProductsTypesTemp_FWDS
340 SELECT
341 wtp.ProductDistributorID, ptp.ProductTypeGuid
342 FROM pi.WorkplaceToProduct wtp (NOLOCK)
343 INNER JOIN dbo.synProducts(CAST(@vegasCountryIndex AS CHAR(1))) pr ON pr.ProductDistributorId = wtp.ProductDistributorID
344 INNER JOIN #subtrees_FWDS list ON list.ChildWorkplaceId = wtp.WorkplaceId
345
346 INNER JOIN lex.synCxcProductType2Product ptp (NOLOCK) ON ptp.ProductDistributorID = wtp.ProductDistributorID
347 WHERE ptp.ProductTypeGuid IN (SELECT flt.IFCGuid FROM @selectedProductTypesTemp flt)
348 AND pr.CountryIndex = @vegasCountryIndex
349 AND ptp.CountryId = @vegasCountryIndex
350 OPTION (OPTIMIZE FOR UNKNOWN);
351
352 INSERT #workplaceProdNDocTemp_FWDS SELECT
353 list.WorkplaceId, wtp.ProductDistributorID, 1, list.HasChecklistA20, list.HasChecklistA202016
354 FROM pi.WorkplaceToProduct wtp (NOLOCK)
355 INNER JOIN #subtrees_FWDS list ON list.ChildWorkplaceId = wtp.WorkplaceId
356 INNER JOIN #workplaceProductsTypesTemp_FWDS ptp (NOLOCK) ON ptp.ProductDistributorId = wtp.ProductDistributorID
357 OPTION (OPTIMIZE FOR UNKNOWN);
358 END
359 ELSE BEGIN
360 INSERT #workplaceProdNDocTemp_FWDS SELECT
361 list.WorkplaceId, wtp.ProductDistributorID, 1, list.HasChecklistA20, list.HasChecklistA202016
362 FROM pi.WorkplaceToProduct wtp (NOLOCK)
363 INNER JOIN dbo.synProducts(CAST(@vegasCountryIndex AS CHAR(1))) pr ON pr.ProductDistributorId = wtp.ProductDistributorID
364 INNER JOIN #subtrees_FWDS list ON list.ChildWorkplaceId = wtp.WorkplaceId
365 WHERE pr.CountryIndex = @vegasCountryIndex
366
367 OPTION (OPTIMIZE FOR UNKNOWN);
368
369 INSERT #workplaceProductsTypesTemp_FWDS SELECT
370 list.ObjectID, ptp.ProductTypeGuid
371 FROM #workplaceProdNDocTemp_FWDS list
372 INNER JOIN lex.synCxcProductType2Product ptp (NOLOCK) ON ptp.ProductDistributorID = list.ObjectID
373 WHERE list.IsProduct=1 AND ptp.CountryId = @vegasCountryIndex;
374 END
375
376 IF @summaryContentTypeIdTemp IS NULL OR @summaryContentTypeIdTemp=@products OR @summaryContentTypeIdTemp=@chemicals BEGIN
377 /*AttributeType: 1=RA, 2=CO, 3=IFU, 4=ProdCO*/
378 DECLARE @workplaceId INT, @orgElementId INT, @list dbo.Array;
379 SELECT @workplaceId = MIN(ID) FROM @workplaceIdsTemp;
380 WHILE @workplaceId IS NOT NULL BEGIN
381 SELECT @orgElementId = CompanyOrgElId FROM pi.Workplace (NOLOCK) WHERE ID=@workplaceId;
382 IF @summaryContentTypeIdTemp IS NULL OR @summaryContentTypeIdTemp=@chemicals BEGIN
383 DELETE FROM @list
384 INSERT @list SELECT ID2 FROM @wp2sds WHERE ID1 = @workplaceId;
385
386 TRUNCATE TABLE #workplaceAttributes_FWDS;
387 TRUNCATE TABLE #workplaceAttributesTemp_FWDS;
388 INSERT #workplaceAttributesTemp_FWDS (
389 ObjectId, AttributeType)
390 SELECT
391 SDSId, 1
392 FROM pi.GetRiskAssessmentIds(@list, @workplaceId, @orgElementId)
393 WHERE WorkplaceSpecific = 1
394 UNION ALL SELECT
395 SDSId, 2
396 FROM pi.GetCommentsIds(@list, @workplaceId, @orgElementId)
397 WHERE WorkplaceSpecific = 1
398 UNION ALL SELECT
399 SDSId, 3
400 FROM pi.GetInstructionsIds(@list, @workplaceId, @orgElementId)
401 WHERE WorkplaceSpecific = 1
402 OPTION (OPTIMIZE FOR UNKNOWN);
403
404 INSERT #workplaceAttributes_FWDS SELECT DISTINCT * FROM #workplaceAttributesTemp_FWDS;
405
406 UPDATE #WorkplaceDashboardSummary
407 SET SDSsWithWorkplaceRiskAssessments = (SELECT COUNT(DISTINCT ObjectId) FROM #workplaceAttributes_FWDS WHERE AttributeType = 1),
408 SDSsWithWorkplaceComments = (SELECT COUNT(DISTINCT ObjectId) FROM #workplaceAttributes_FWDS WHERE AttributeType = 2),
409 SDSsWithWorkplaceInstructions = (SELECT COUNT(DISTINCT ObjectId) FROM #workplaceAttributes_FWDS WHERE AttributeType = 3),
410 SDSsWithWorkplaceRiskAssessmentsOrComments = (SELECT COUNT(DISTINCT ObjectId) FROM #workplaceAttributes_FWDS WHERE AttributeType IN (1,2))
411 WHERE WorkplaceId = @workplaceId
412 OPTION (OPTIMIZE FOR UNKNOWN);
413 END
414
415 IF @summaryContentTypeIdTemp IS NULL OR @summaryContentTypeIdTemp=@products BEGIN
416 DELETE FROM @list;
417 INSERT @list SELECT DISTINCT ObjectId FROM #workplaceProdNDocTemp_FWDS WHERE IsProduct=1 AND WorkplaceId=@workplaceId;
418
419 TRUNCATE TABLE #workplaceAttributes_FWDS;
420 TRUNCATE TABLE #workplaceAttributesTemp_FWDS;
421 INSERT #workplaceAttributesTemp_FWDS (
422 ObjectId, AttributeType)
423 SELECT
424 ProductDistributorId, 4
425 FROM pi.GetProductCommentIds(@list, @workplaceId, @orgElementId)
426 WHERE WorkplaceSpecific = 1
427 OPTION (OPTIMIZE FOR UNKNOWN);
428
429 INSERT #workplaceAttributes_FWDS SELECT DISTINCT * FROM #workplaceAttributesTemp_FWDS;
430
431 UPDATE #WorkplaceDashboardSummary
432 SET ProductsWithWorkplaceComments = (SELECT COUNT(DISTINCT ObjectId) FROM #workplaceAttributes_FWDS WHERE AttributeType = 4)
433 WHERE WorkplaceId = @workplaceId
434 OPTION (OPTIMIZE FOR UNKNOWN);
435 END
436
437 SELECT @workplaceId = (SELECT MIN(ID) FROM @workplaceIdsTemp WHERE ID > @workplaceId);
438 END--WHILE
439 END--IF
440
441 IF @summaryContentTypeIdTemp IS NULL OR @summaryContentTypeIdTemp=@dashboard BEGIN
442 INSERT #workplaceProdNDocTemp_FWDS SELECT
443 list.WorkplaceId, wtd.kortid, 0, list.HasChecklistA20, list.HasChecklistA202016
444 FROM pi.WorkplaceToDocument wtd (NOLOCK)
445 INNER JOIN #subtrees_FWDS list ON list.ChildWorkplaceId = wtd.WorkplaceId
446 INNER JOIN dbo.synDocuments dox ON dox.kortid=wtd.kortid --just to filter out documents that are deleted
447 OPTION (OPTIMIZE FOR UNKNOWN);
448
449 UPDATE DS
450 SET DS.Documents = (SELECT COUNT(DISTINCT data.ObjectID) FROM #workplaceProdNDocTemp_FWDS data WHERE data.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceID) AND data.IsProduct=0)
451 FROM #WorkplaceDashboardSummary DS
452 OPTION (OPTIMIZE FOR UNKNOWN);
453 END
454
455 INSERT #workplaceProdNDoc_FWDS SELECT DISTINCT * FROM #workplaceProdNDocTemp_FWDS;
456 INSERT #workplaceProductsTypes_FWDS SELECT DISTINCT * FROM #workplaceProductsTypesTemp_FWDS;
457
458 ---------------------------------------------- START Get WP hierarchy for Black Products deviation ---------------------------------------------
459
460 DECLARE @blackProductsWorkplaceIdsTemp TABLE (WorkplaceId INT, RootWorkplaceId INT);
461 DECLARE @blackProductsWorkplaceIds TABLE (WorkplaceId INT);
462
463 WITH TREE(WorkplaceId, ParentId, RootWorkplaceId, RecursionLevel)
464 AS
465 (
466 /* root WP */
467 SELECT WP.ID [WorkplaceId], NULL [ParentId], WP.ID [RootWorkplaceId], 0
468 FROM @workplaceIdsTemp t
469 INNER JOIN pi.WorkplaceNotDeleted WP (NOLOCK) ON t.ID = WP.ID
470 INNER JOIN pi.OrgElement OE (NOLOCK) ON OE.ID = WP.OrgElementId
471 WHERE
472 --WP.IsArchived = 0 AND
473 OE.IsWholesaler = 0
474
475 UNION ALL
476
477 /* subworkplaces */
478 SELECT child.ID [WorkplaceId], parent.WorkplaceId [ParentId], parent.RootWorkplaceId, parent.RecursionLevel + 1
479 FROM TREE parent
480 INNER JOIN pi.WorkplaceNotDeleted child (NOLOCK) ON child.ParentId = parent.WorkplaceId
481 WHERE
482 NOT EXISTS (SELECT ID FROM @workplaceIdsTemp WHERE ID = parent.WorkplaceId)
483 --AND child.IsArchived = 0
484 AND (parent.RecursionLevel + 1 <= 20)
485
486 UNION ALL
487
488 /* imported project/company from WHOLESALERS */
489 SELECT child.ID [WorkplaceId], parent.WorkplaceId [ParentId], parent.RootWorkplaceId, parent.RecursionLevel + 1
490 FROM TREE parent
491 INNER JOIN pi.ImportedWorkplace import (NOLOCK) ON import.ParentID = parent.WorkplaceId
492 INNER JOIN pi.WorkplaceNotDeleted child (NOLOCK) ON child.ID = import.WorkplaceID
493 INNER JOIN pi.OrgElement importedCompany (NOLOCK) ON importedCompany.ID = child.OrgElementId
494 WHERE importedCompany.IsWholesaler = 1
495 AND import.Deleted = 0
496 --AND child.IsArchived = 0
497 AND (parent.RecursionLevel + 1 <= 20)
498 )
499
500 INSERT #subtreesForBlackProducts
501 SELECT tree.WorkplaceId, tree.RootWorkplaceId
502 FROM TREE tree
503
504 INSERT @blackProductsWorkplaceIds
505 SELECT DISTINCT tree.WorkplaceId
506 FROM #subtreesForBlackProducts tree
507
508 ----------------------------------------------- END Get WP hierarchy for Black Products deviation --------------------------------------------
509
510 INSERT #workplaceProductsInNonComplianceTemp_FWDS SELECT
511 dw.WorkplaceId, d.ObjectID, prc.Id, NULL
512 FROM task.Deviation d
513 INNER JOIN task.DeviationToWorkplace dw ON dw.TaskId = d.ID
514 INNER JOIN pi.ProductRuleConfiguration prc ON prc.DeviationType = d.DeviationType
515 INNER JOIN pi.WorkplaceToProduct wtp ON wtp.WorkplaceId = dw.WorkplaceId AND wtp.ProductDistributorID = d.ObjectId
516 --INNER JOIN #workplaceProdNDoc_FWDS wpd ON wpd.ObjectID = wtp.ProductDistributorID
517 INNER JOIN dbo.synProducts(CAST(@vegasCountryIndex AS CHAR(1))) pr ON pr.ProductDistributorId = wtp.ProductDistributorID
518 INNER JOIN @blackProductsWorkplaceIds bpw ON bpw.Workplaceid = wtp.WorkplaceId
519 WHERE d.ObjectType = @objectTypeProduct
520 AND dw.[Status] = @taskStatusOpen
521 --AND wpd.IsProduct = 1
522 AND prc.DeviationType = @deviationProductIsBlack;
523
524 -- Requested product types without delivery
525
526 INSERT #workplaceProductsInNonComplianceTemp_FWDS SELECT
527 DISTINCT dw.WorkplaceId, d.ObjectID, prc.Id, NULL
528 FROM task.Deviation d
529 INNER JOIN task.DeviationToWorkplace dw ON dw.TaskId = d.ID
530 INNER JOIN #subtrees_FWDS scope ON scope.ChildWorkplaceId = dw.WorkplaceId
531 INNER JOIN pi.ProductRuleConfiguration prc ON prc.DeviationType = d.DeviationType
532 WHERE d.ObjectType = @objectTypeProductType
533 AND dw.[Status] = @taskStatusOpen
534 AND prc.DeviationType = @deviationRequestedProductType
535 AND (scope.ParentId IS NULL OR scope.ParentId IN (SELECT scope2.ChildWorkplaceId FROM #subtrees_FWDS scope2 WHERE scope2.ParentId IS NULL))
536
537 -- Requested product types without delivery
538
539 INSERT #workplaceProductsInNonComplianceTemp_FWDS SELECT
540 dw.WorkplaceId, d.ObjectID, prc.Id, NULL
541 FROM task.Deviation d
542 INNER JOIN task.DeviationToWorkplace dw ON dw.TaskId = d.ID
543 INNER JOIN pi.ProductRuleConfiguration prc ON prc.DeviationType = d.DeviationType
544 INNER JOIN pi.WorkplaceToProduct wtp ON wtp.WorkplaceId = dw.WorkplaceId AND wtp.ProductDistributorID = d.ObjectId
545 INNER JOIN #workplaceProdNDoc_FWDS wpd ON wpd.ObjectID = wtp.ProductDistributorID
546 INNER JOIN dbo.synProducts(CAST(@vegasCountryIndex AS CHAR(1))) pr ON pr.ProductDistributorId = wtp.ProductDistributorID
547 INNER JOIN #subtrees_FWDS sw ON sw.ChildWorkplaceid = wtp.WorkplaceId
548 WHERE d.ObjectType = @objectTypeProduct
549 AND dw.[Status] = @taskStatusOpen
550 AND wpd.IsProduct = 1
551 AND prc.Id NOT IN (
552 SELECT ID
553 FROM pi.ProductRuleConfiguration
554 WHERE DeviationType IN (@deviationProductMissingA20, @deviationProductMissingHEA9, @deviationProductMissingA202016, @deviationProductMissingHEA02, @deviationProductIsBlack, @deviationRequestedProductType, @deviationProductInBreach)
555 );
556
557 -- BREEAM 2012
558 INSERT #workplaceProductsInNonComplianceTemp_FWDS SELECT
559 dw.WorkplaceId, d.ObjectID, prc.Id, NULL
560 FROM task.Deviation d
561 INNER JOIN task.DeviationToWorkplace dw ON dw.TaskId = d.ID
562 INNER JOIN pi.ProductRuleConfiguration prc ON prc.DeviationType = d.DeviationType
563 INNER JOIN pi.WorkplaceToProduct wtp ON wtp.WorkplaceId = dw.WorkplaceId AND wtp.ProductDistributorID = d.ObjectId
564 INNER JOIN #workplaceProdNDoc_FWDS wpd ON wpd.ObjectID = wtp.ProductDistributorID
565 INNER JOIN dbo.synProducts(CAST(@vegasCountryIndex AS CHAR(1))) pr ON pr.ProductDistributorId = wtp.ProductDistributorID
566 INNER JOIN #subtrees_FWDS sw ON sw.ChildWorkplaceid = wtp.WorkplaceId
567
568 WHERE d.ObjectType = @objectTypeProduct
569 AND dw.[Status] = @taskStatusOpen
570 AND wpd.IsProduct = 1
571 AND wpd.HasChecklistA20 = 1
572 AND prc.Id IN (
573 SELECT ID
574 FROM pi.ProductRuleConfiguration
575 WHERE DeviationType IN (@deviationProductMissingA20, @deviationProductMissingHEA9)
576 );
577
578 -- BREEAM 2016
579 INSERT #workplaceProductsInNonComplianceTemp_FWDS SELECT
580 dw.WorkplaceId, d.ObjectID, prc.Id, NULL
581 FROM task.Deviation d
582 INNER JOIN task.DeviationToWorkplace dw ON dw.TaskId = d.ID
583 INNER JOIN pi.ProductRuleConfiguration prc ON prc.DeviationType = d.DeviationType
584 INNER JOIN pi.WorkplaceToProduct wtp ON wtp.WorkplaceId = dw.WorkplaceId AND wtp.ProductDistributorID = d.ObjectId
585 INNER JOIN #workplaceProdNDoc_FWDS wpd ON wpd.ObjectID = wtp.ProductDistributorID
586 INNER JOIN dbo.synProducts(CAST(@vegasCountryIndex AS CHAR(1))) pr ON pr.ProductDistributorId = wtp.ProductDistributorID
587 INNER JOIN #subtrees_FWDS sw ON sw.ChildWorkplaceid = wtp.WorkplaceId
588
589 WHERE d.ObjectType = @objectTypeProduct
590 AND dw.[Status] = @taskStatusOpen
591 AND wpd.IsProduct = 1
592 AND wpd.HasChecklistA202016 = 1
593 AND prc.Id IN (
594 SELECT ID
595 FROM pi.ProductRuleConfiguration
596 WHERE DeviationType IN (@deviationProductMissingA202016, @deviationProductMissingHEA02)
597 );
598
599 IF @isVisibleForProductNonComplianceDashboardEcoRules = 1
600
601 BEGIN
602 INSERT #workplaceProductsInNonComplianceTemp_FWDS SELECT
603 list.WorkplaceId, list.ObjectID, NULL, ER.PropertyGuid
604 FROM #workplaceProdNDoc_FWDS list
605 INNER JOIN #workplaceProductsTypes_FWDS ptp ON ptp.ProductDistributorID=list.ObjectID
606 INNER JOIN lex.synProductsNonCompliantToEcoRule ER (NOLOCK)
607 ON ER.ProductDistributorID = list.ObjectID
608 INNER JOIN pi.GetProductTypeFilters(@workplaceIdsTemp) PF
609 ON ER.ProductTypeFilterID = PF.ProductTypeFilterID
610 AND PF.WorkplaceId = list.WorkplaceId
611 WHERE list.IsProduct = 1
612 AND ER.VegasCountryIndex=@vegasCountryIndex
613 OPTION (OPTIMIZE FOR UNKNOWN);
614
615 INSERT #deviationObjectsTemp_FWDS SELECT
616 pnc.WorkplaceId, @objectTypeProduct, pnc.ProductDistributorID, @deviationProductWithEcoRule, pnc.EcoRuleId, 0
617 FROM #workplaceProductsInNonComplianceTemp_FWDS pnc
618 WHERE pnc.EcoRuleId IS NOT NULL;
619
620 INSERT #deviationObjectsTemp_FWDS SELECT
621 scope.WorkplaceId, @objectTypeProduct, scope.ObjectID, @deviationProductWithEcoRule, dev.FilterKey, 1
622 FROM task.Deviation dev
623 JOIN task.DeviationToWorkplace dw ON dw.TaskId = dev.ID
624 JOIN #workplaceProdNDoc_FWDS scope
625 ON scope.ObjectID=dev.ObjectID AND scope.IsProduct = 1
626 AND scope.WorkplaceId=dw.WorkplaceId
627 WHERE dw.Status = @taskStatusOpen
628 AND dev.ObjectType = @objectTypeProduct
629 AND dev.DeviationType = @deviationProductWithEcoRule
630 AND dev.FilterKey IS NOT NULL
631 AND NOT EXISTS (
632 SELECT NULL
633 FROM pi.GetProductTypeFilters(@workplaceIdsTemp) PF
634 JOIN lex.synProductsNonCompliantToEcoRule ER ON PF.ProductTypeFilterID=ER.ProductTypeFilterID
635 WHERE ER.VegasCountryIndex=@vegasCountryIndex
636 AND ER.ProductDistributorID=scope.ObjectID
637 AND PF.WorkplaceId=scope.WorkplaceId
638 AND ER.PropertyGuid=dev.FilterKey
639 );
640 END
641
642 IF @isVisibleForProductsWithFilterCriteria = 1
643 AND (@summaryContentTypeIdTemp IS NULL OR @summaryContentTypeIdTemp=@dashboard OR @summaryContentTypeIdTemp=@products)
644 BEGIN
645 INSERT INTO #workplaceProdFilterTemp_FWDS(WorkplaceId, ProductDistributorId)
646 SELECT list.WorkplaceId, list.ObjectID
647 FROM #workplaceProdNDoc_FWDS list
648 INNER JOIN #workplaceProductsTypes_FWDS ptp (NOLOCK) ON ptp.ProductDistributorID=list.ObjectID
649 INNER JOIN lex.synProductsNonCompliantToProductTypeFilter ER (NOLOCK)
650 ON ER.ProductDistributorID=list.ObjectID
651 INNER JOIN pi.GetProductTypeFilters(@workplaceIdsTemp) PF
652 ON ER.ProductTypeFilterID = PF.ProductTypeFilterID
653 AND PF.WorkplaceId = list.WorkplaceId
654 WHERE list.IsProduct=1 AND ER.StateID=0
655 AND ER.VegasCountryIndex=@vegasCountryIndex
656 OPTION (OPTIMIZE FOR UNKNOWN);
657
658 INSERT #workplaceProdFilter_FWDS SELECT DISTINCT * FROM #workplaceProdFilterTemp_FWDS;
659
660 UPDATE DS
661 SET DS.ProductsWithFilterCriteria = (SELECT COUNT(ProductDistributorId) FROM #workplaceProdFilter_FWDS WPF WHERE WPF.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceID))
662 FROM #WorkplaceDashboardSummary DS
663 OPTION (OPTIMIZE FOR UNKNOWN);
664 END
665
666 INSERT #workplaceProductsInNonCompliance_FWDS SELECT DISTINCT * FROM #workplaceProductsInNonComplianceTemp_FWDS;
667
668 UPDATE DS
669 SET DS.Products = (SELECT COUNT(DISTINCT data.ObjectID) FROM #workplaceProdNDoc_FWDS data WHERE data.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceID) AND data.IsProduct=1),
670 DS.ProductsInNonCompliance = (SELECT COUNT(DISTINCT pnc.ProductDistributorID) FROM #workplaceProductsInNonCompliance_FWDS pnc WHERE pnc.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceID)),
671 DS.ProductsInNonComplianceDetails =
672 '<ProductsInNonComplianceDetails>' +
673 ISNULL(cast((
674 select
675 pnc.ProductRuleConfigurationId as "@ID",
676 count(distinct pnc.ProductDistributorID) as [text()]
677 from #workplaceProductsInNonCompliance_FWDS pnc
678 where pnc.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceID) AND pnc.ProductRuleConfigurationId IS NOT NULL
679 AND pnc.ProductRuleConfigurationId <> @productRuleRequestedProductType
680 AND pnc.ProductRuleConfigurationID <> @productRuleProductIsBlack
681 AND pnc.ProductRuleConfigurationID <> @productRuleProductInBreachWithRequirements
682 group by pnc.ProductRuleConfigurationId
683 for xml path('ProductComplianceRule')
684 ) as nvarchar(1000)), '')
685 +
686 ISNULL(cast((
687 select
688 pnc.ProductRuleConfigurationId as "@ID",
689 count(distinct pnc.ProductDistributorID) as [text()]
690 from #workplaceProductsInNonCompliance_FWDS pnc
691 where pnc.ProductRuleConfigurationID = @productRuleProductIsBlack -- for black products use separate hierarchy scope
692 AND pnc.WorkplaceId IN (SELECT WorkplaceId FROM #subtreesForBlackProducts WHERE RootWorkplaceId = DS.WorkplaceID)
693 group by pnc.ProductRuleConfigurationId
694 for xml path('ProductComplianceRule')
695 ) as nvarchar(1000)), '')
696 +
697 ISNULL(cast((
698 select
699 pnc.ProductRuleConfigurationId as "@ID",
700 count(pnc.ProductDistributorID) as [text()]
701 from #workplaceProductsInNonCompliance_FWDS pnc
702 where pnc.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceID) AND pnc.ProductRuleConfigurationId IS NOT NULL
703 AND pnc.ProductRuleConfigurationId = @productRuleRequestedProductType
704 group by pnc.ProductRuleConfigurationId
705 for xml path('ProductComplianceRule')
706 ) as nvarchar(1000)), '')
707 +
708 ISNULL(cast((
709 select
710 pnc.EcoRuleId as "@ID",
711 count(distinct pnc.ProductDistributorID) as [text()]
712 from #workplaceProductsInNonCompliance_FWDS pnc
713 where pnc.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceID) AND pnc.EcoRuleId IS NOT NULL
714 group by pnc.EcoRuleId
715 for xml path('EcoRule')
716 ) as nvarchar(1000)), '') +
717 '</ProductsInNonComplianceDetails>',
718 DS.InvitedCompanies = (
719 SELECT COUNT(DISTINCT w.ID)
720 FROM pi.ImportedWorkplace iw (NOLOCK)
721 JOIN pi.WorkplaceNotDeleted w (NOLOCK) ON w.ID=iw.WorkplaceID
722 WHERE iw.Deleted=0 AND iw.ParentId=DS.WorkplaceId
723 AND (DS.IsArchived|@includeArchivedWorkplacesTemp=1 OR w.IsArchived=0)),
724 DS.Departments = (
725 SELECT COUNT(DISTINCT w.ID)
726 FROM pi.WorkplaceNotDeleted w (NOLOCK)
727 WHERE w.ParentId=DS.WorkplaceId
728 AND (DS.IsArchived|@includeArchivedWorkplacesTemp=1 OR w.IsArchived=0)),
729 DS.SuggestedUwp = 0, --this is calculated synchronously by the summary itself
730 DS.Subprojects = (SELECT COUNT(DISTINCT w.ID) FROM pi.WorkplaceNotDeleted w (NOLOCK) WHERE w.ParentId = DS.WorkplaceId)
731 FROM #WorkplaceDashboardSummary DS
732 OPTION (OPTIMIZE FOR UNKNOWN);
733
734 IF @summaryContentTypeIdTemp IS NULL OR @summaryContentTypeIdTemp=@dashboard OR @summaryContentTypeIdTemp=@deviations BEGIN
735 UPDATE DS
736 SET Deviations = ( SELECT count(1) FROM
737 (
738 (SELECT NULL WorkplaceId, t.ProductdistributorId, t.ProductRuleConfigurationId
739 FROM (
740 SELECT DISTINCT pnc.ProductdistributorId, pnc.ProductRuleConfigurationId
741 FROM #workplaceProductsInNonCompliance_FWDS pnc
742 WHERE pnc.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceId)
743 AND pnc.ProductRuleConfigurationId <> @productRuleRequestedProductType AND pnc.ProductRuleConfigurationId <> @productRuleProductIsBlack
744 ) AS t)
745
746 UNION
747
748 (SELECT NULL WorkplaceId, t.ProductdistributorId, t.ProductRuleConfigurationId
749 FROM (
750 SELECT DISTINCT pnc.ProductdistributorId, pnc.ProductRuleConfigurationId
751 FROM #workplaceProductsInNonCompliance_FWDS pnc
752 WHERE pnc.WorkplaceId IN (SELECT WorkplaceId FROM #subtreesForBlackProducts WHERE RootWorkplaceId = DS.WorkplaceId)
753 AND pnc.ProductRuleConfigurationId = @productRuleProductIsBlack
754 ) AS t)
755
756 UNION
757
758 (SELECT t.WorkplaceId, t.ProductdistributorId, t.ProductRuleConfigurationId
759 FROM (
760 SELECT DISTINCT pnc.WorkplaceId, pnc.ProductdistributorId, pnc.ProductRuleConfigurationId
761 FROM #workplaceProductsInNonCompliance_FWDS pnc
762 WHERE pnc.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceId)
763 AND pnc.ProductRuleConfigurationId = @productRuleRequestedProductType
764 ) AS t)
765 ) AS total
766 ),
767 PartialDeviations = ( SELECT count(1) FROM
768 (
769 (SELECT t.WorkplaceId, t.ProductdistributorId, t.ProductRuleConfigurationId
770 FROM (
771 SELECT DISTINCT pnc.WorkplaceId, pnc.ProductdistributorId, pnc.ProductRuleConfigurationId
772 FROM #workplaceProductsInNonCompliance_FWDS pnc
773 WHERE pnc.WorkplaceId IN (SELECT ChildWorkplaceId FROM #subtrees_FWDS WHERE WorkplaceId = DS.WorkplaceId)
774 AND (pnc.ProductRuleConfigurationId = @productRuleRequestedProductType
775 OR pnc.ProductRuleConfigurationId = @productRuleProductMissingRequiredData)
776 ) AS t)
777 ) AS total
778 )
779 FROM #WorkplaceDashboardSummary DS
780 OPTION (OPTIMIZE FOR UNKNOWN);
781 END
782
783 IF @updateDataTemp = 1
784 BEGIN
785 DECLARE @currentDate1 DATETIME2(2) = GETDATE();
786 UPDATE idx
787 SET idx.Products = ISNULL(summary.Products, idx.Products),
788 idx.SDSs = ISNULL(summary.SDSs, idx.SDSs),
789 idx.Documents = ISNULL(summary.Documents, idx.Documents),
790 idx.ProductsInNonCompliance = ISNULL(summary.ProductsInNonCompliance, idx.ProductsInNonCompliance),
791 idx.ProductsInNonComplianceDetails = ISNULL(summary.ProductsInNonComplianceDetails, idx.ProductsInNonComplianceDetails),
792 idx.SDSsWithFilterCriteria = ISNULL(summary.SDSsWithFilterCriteria, idx.SDSsWithFilterCriteria),
793 idx.SDSsHazardousWaste = ISNULL(summary.SDSsHazardousWaste, idx.SDSsHazardousWaste),
794 idx.SDSsWithWorkplaceComments = ISNULL(summary.SDSsWithWorkplaceComments, idx.SDSsWithWorkplaceComments),
795 idx.SDSsWithWorkplaceInstructions = ISNULL(summary.SDSsWithWorkplaceInstructions, idx.SDSsWithWorkplaceInstructions),
796 idx.SDSsWithWorkplaceRiskAssessments = ISNULL(summary.SDSsWithWorkplaceRiskAssessments, idx.SDSsWithWorkplaceRiskAssessments),
797 idx.SDSsWithWorkplaceRiskAssessmentsOrComments = ISNULL(summary.SDSsWithWorkplaceRiskAssessmentsOrComments, idx.SDSsWithWorkplaceRiskAssessmentsOrComments),
798 idx.InvitedCompanies = ISNULL(summary.InvitedCompanies, idx.InvitedCompanies),
799 idx.Departments = ISNULL(summary.Departments, idx.Departments),
800 idx.Lists = ISNULL(summary.Lists, idx.Lists),
801 idx.Orders = ISNULL(summary.Orders, idx.Orders),
802 idx.ProductsWithFilterCriteria = ISNULL(summary.ProductsWithFilterCriteria, idx.ProductsWithFilterCriteria),
803 idx.SuggestedUwp = ISNULL(summary.SuggestedUwp, idx.SuggestedUwp),
804 idx.ProductsWithWorkplaceComments = ISNULL(summary.ProductsWithWorkplaceComments, idx.ProductsWithWorkplaceComments),
805 idx.Deviations = ISNULL(summary.Deviations, idx.Deviations),
806 idx.Subprojects = ISNULL(summary.Subprojects, idx.Subprojects),
807 idx.PartialDeviations = ISNULL(summary.PartialDeviations, idx.PartialDeviations),
808 idx.IsDirty = 0,
809 idx.IndexDate = CASE WHEN @updateSpecificWorkplaceTemp = 1 THEN idx.IndexDate ELSE @currentDate1 END,
810 idx.UpdateDate = @currentDate1,
811 idx.LastUpdateContentTypeId = @summaryContentTypeId
812 FROM idx.DashboardSummary idx
813 JOIN #WorkplaceDashboardSummary summary ON summary.WorkplaceId=idx.WorkplaceId;
814 END
815
816 END
817
818 IF @loadDataFromDbTemp = 1 OR (@loadDataFromDbTemp = 0 AND @updateDataTemp = 1)
819 BEGIN
820
821 SELECT
822 DS.WorkplaceId,
823 WP.Name,
824 WP.IsArchived,
825 WP.Address [AddressStreet],
826 WP.City [AddressCity],
827 WP.PostCode [AddressPostCode],
828 STUFF(((
829 SELECT ', ' + Name
830 FROM pi.WorkplaceID wid (NOLOCK)
831 WHERE wid.WorkplaceId=DS.WorkplaceId AND wid.Name > ''
832 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(2000)')
833 ),1,2,'') [ProjectNumbers],
834 WP.CompanyOrgElId,
835 WP.Type [WorkplaceType],
836 DS.Products,
837 DS.ProductsInNonCompliance,
838 DS.ProductsInNonComplianceDetails,
839 DS.ProductsWithFilterCriteria,
840 DS.ProductsWithWorkplaceComments,
841 DS.SDSs,
842 DS.SDSsWithFilterCriteria,
843 DS.SDSsHazardousWaste,
844 DS.SDSsWithWorkplaceComments,
845 DS.SDSsWithWorkplaceInstructions,
846 DS.SDSsWithWorkplaceRiskAssessments,
847 DS.SDSsWithWorkplaceRiskAssessmentsOrComments,
848 DS.Documents,
849 DS.InvitedCompanies,
850 DS.Departments,
851 DS.Lists,
852 DS.Orders,
853 DS.SuggestedUwp,
854 DS.Deviations
855 FROM idx.DashboardSummary DS (NOLOCK)
856 JOIN pi.Workplace WP (NOLOCK) ON WP.ID=DS.WorkplaceId
857 WHERE DS.WorkplaceId IN (SELECT ID FROM @workplaceIdsTemp);
858 END
859 ELSE
860 BEGIN
861 SELECT * FROM #WorkplaceDashboardSummary;
862 END
863
864 RETURN;