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