· 8 years ago · Jun 08, 2018, 01:16 PM
1public static void FillWorkplaceToProductData(
2 DataSet dset, DocProductListType listType, DocProductListParams param, DocProductFilterParams filter,
3 bool useFullTextIndex, bool addProductAssessmentData, DocProductClassificationParam classificationParam,
4 string languageCode, bool isTechnicalSpecificationFunctionalityVisible, int? commandTimeout = null)
5 {
6 StringBuilder sql = new StringBuilder(@"
7DECLARE @wpCompanyOrgElementId INT;
8DECLARE @wpIsSubworkplace BIT;
9SET @wpCompanyOrgElementId = (SELECT TOP 1 CompanyOrgElID FROM pi.Workplace WHERE ID = @rootWorkplaceId);
10SET @wpIsSubworkplace = (SELECT (CASE WHEN ParentId IS NOT NULL THEN 1 ELSE 0 END) AS IsSubworkplace FROM pi.Workplace WHERE ID = @rootWorkplaceId);
11
12IF OBJECT_ID('tempdb..#productIds') IS NOT NULL
13 DROP TABLE #productIds ;
14CREATE TABLE #productIds (ID INT PRIMARY KEY);
15
16DECLARE @workplaceIds as dbo.Array;
17DECLARE @countryIndex TINYINT = (
18 SELECT VegasCountryIndex FROM dbo.Country (NOLOCK) WHERE ID=@countryId
19);
20");
21
22 bool filterByDate = (param.StartDateFilter != DateTime.MinValue || param.EndDateFilter != DateTime.MinValue);
23
24 if (filterByDate)
25 {
26 SqlBuilders.WorkplaceToProduct.GetProductsAndWorkplacesFilteredByDate(param, sql);
27 }
28 else
29 {
30 SqlBuilders.WorkplaceToProduct.GetProductsAndWorkplaces(param, sql, listType);
31 }
32
33 if (listType == DocProductListType.Deviations && !param.ShowClosedTasks)
34 {
35 SqlBuilders.WorkplaceToProduct.ExtractPNCFeedback(sql);
36 }
37
38 if (filter.Enabled && useFullTextIndex && filter.HasSearchTerm)
39 {
40 SqlBuilders.WorkplaceToProduct.GetAllProductsFilteredByName(param, filter, sql);
41 }
42
43 if (filter.Enabled && useFullTextIndex && filter.HasArticleNumber)
44 {
45 SqlBuilders.WorkplaceToProduct.GetAllProductsFilteredByArticleNumber(param, filter, sql);
46 }
47
48 SqlBuilders.WorkplaceToProduct.ExtractProducts(param, filter, sql, listType);
49
50 sql.Append(@"
51DECLARE @allProductIds AS dbo.Array
52INSERT INTO @allProductIds
53SELECT DISTINCT ProductDistributorId FROM #synProductsCopy;
54");
55 SqlBuilders.WorkplaceToProduct.ExtractProductTypes(sql, param);
56 if (filter.Enabled)
57 {
58 SqlBuilders.WorkplaceToProduct.ExtractDocumentTypes(sql);
59 SqlBuilders.WorkplaceToProduct.FilterProducts(filter, sql, listType, useFullTextIndex);
60 SqlBuilders.WorkplaceToProduct.ExtractFilters(sql);
61 if (param.ShowDeliveredBy)
62 SqlBuilders.WorkplaceToProduct.ExtractDeliveredBy(sql);
63
64 }
65
66 if (param.ListSelectedProductTypesOnly && param.ProductTypes != null && param.ProductTypes.Count > 0)
67 {
68 sql.Append(@"
69UPDATE PR
70SET PR.ProductType = PT.TypeName
71FROM #synProductsCopy PR
72JOIN #synProductType PT ON PT.ProductDistributorId=PR.ProductDistributorId;");
73 }
74 else
75 {
76 sql.Append(@"
77IF OBJECT_ID('tempdb..#productsWithOnePT') IS NOT NULL
78 DROP TABLE #productsWithOnePT;
79CREATE TABLE #productsWithOnePT (ID INT PRIMARY KEY);
80INSERT #productsWithOnePT
81SELECT ProductDistributorId
82FROM #synProductType
83GROUP BY ProductDistributorId
84HAVING COUNT(*) = 1;
85
86IF OBJECT_ID('tempdb..#productsWithManyPT') IS NOT NULL
87 DROP TABLE #productsWithManyPT;
88CREATE TABLE #productsWithManyPT (ID INT PRIMARY KEY);
89INSERT #productsWithManyPT
90SELECT DISTINCT ProductDistributorId
91FROM #synProductType
92WHERE ProductDistributorId NOT IN (SELECT one.ID FROM #productsWithOnePT one);
93
94UPDATE PR
95SET PR.ProductType = pt.TypeName
96FROM #synProductsCopy PR
97JOIN #synProductType pt on pt.ProductDistributorId=PR.ProductDistributorId
98JOIN #productsWithOnePT one ON one.ID=pt.ProductDistributorId;
99
100UPDATE PR
101SET PR.ProductType =
102 STUFF(((
103 SELECT ', ' + pt.TypeName
104 FROM #synProductType pt
105 WHERE pt.ProductDistributorId=PR.ProductDistributorId
106 ORDER BY pt.TypeName
107 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(4000)')
108 ),1,2,'')
109FROM #synProductsCopy PR
110JOIN #productsWithManyPT many ON many.ID=PR.ProductDistributorId;
111");
112 }
113
114 sql.Append(@"
115DELETE @allProductIds
116WHERE ID NOT IN (SELECT ProductDistributorId FROM #synProductsCopy);
117/*PRODUCT TYPES*/
118");
119 //if (classificationParam.ShouldBeApplied)
120 //{
121 // SqlBuilders.WorkplaceToProduct.FilterProductsInClassification(classificationParam, sql);
122 //}
123
124 sql.Append(@"
125SELECT ID
126FROM @allProductIds;
127");
128 sql.Append(@"
129DECLARE @resultUnsorted TABLE (
130 [ProductDistributorID] [INT] NOT NULL,
131 [IsPSP] [TINYINT],
132 [Name1] [NVARCHAR](255),
133 [Name2] [NVARCHAR](255),");
134 if (!param.SkipProductIdentifiers)
135 sql.Append(@"
136 [Spn] [NVARCHAR](50),
137 [Gtin] [NVARCHAR](50),");
138 if (!param.SkipDateAdded)
139 sql.Append(@"
140 [DateAdded] [DATETIME],");
141 sql.Append(@"
142 [DocumentsCount] [INT],
143 [SupplierName] [NVARCHAR] (100),
144 [ProductType] [NVARCHAR](4000),
145 [ProductAssessmentStatus] [SMALLINT],
146 [ProductAssessmentStatus_Static] [SMALLINT],
147 [ProductAssessmentId] [INT],
148 [ProductAssessmentComment] [NVARCHAR](2000)");
149 if (listType == DocProductListType.Deviations)
150 {
151 sql.Append(@",
152 [DeliveredBy] [NVARCHAR](MAX),
153 [FbSendToWorkplaceId] [NVARCHAR](4000),
154 [TaskId] [INT],");
155 if (param.ShowClosedTasks)
156 sql.Append(@"
157 ClosedOn DATETIME,");
158 else
159 sql.Append(@"
160 SentOn NVARCHAR(4000),
161 SentBy NVARCHAR(150),
162 SentTo NVARCHAR(4000),
163 ExplicitRecipients NVARCHAR(4000),");
164 sql.Append(@"
165 RequestedBy NVARCHAR(300),
166 UNIQUE CLUSTERED ([ProductDistributorID], [TaskId])");
167 }
168 else if (param.ShowDeliveredBy)
169 sql.Append(@",
170 [DeliveredBy] [NVARCHAR](MAX),
171 PRIMARY KEY ([ProductDistributorID])");
172 else
173 sql.Append(@",
174 PRIMARY KEY ([ProductDistributorID])
175");
176 sql.Append(@");
177;WITH rows AS (
178 SELECT DISTINCT
179 pr.ProductDistributorId,
180 pr.Name1,
181 pr.Name2,");
182 if (!param.SkipProductIdentifiers)
183 sql.Append(@"
184 pr.Spn,
185 pr.Gtin,");
186 if (!param.SkipDateAdded)
187 sql.Append(@"
188 pr.DateAdded,");
189 sql.Append(@"
190 pr.SupplierName,
191 pr.ProductType,");
192 if (listType == DocProductListType.Deviations)
193 {
194 sql.Append(@"
195 STUFF(((
196 SELECT DISTINCT ', ' + WP.DisplayName
197 FROM #deliveredByData WP
198 WHERE WP.ProductDistributorID=pr.ProductDistributorId
199 ORDER BY ', ' + WP.DisplayName
200 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
201 ), 1, 2, '') [DeliveredBy],
202 STUFF(((
203 SELECT DISTINCT ', ' + CONVERT(NVARCHAR(20), WP.FbSendToWorkplaceId)
204 FROM #deliveredByData WP
205 WHERE WP.ProductDistributorID=pr.ProductDistributorId
206 ORDER BY ', ' + CONVERT(NVARCHAR(20), WP.FbSendToWorkplaceId)
207 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
208 ), 1, 2, '') [FbSendToWorkplaceId],
209 pr.TaskId,");
210 if (param.ShowClosedTasks)
211 sql.Append(@"
212 pr.ClosedOn,");
213 else
214 sql.Append(@"
215 STUFF(((
216 SELECT DISTINCT ', ' + CONVERT(NVARCHAR(20), PF.SentOn)
217 FROM @productsWithFeedbackSent PF
218 WHERE PF.ProductDistributorID=pr.ProductDistributorId
219 ORDER BY ', ' + CONVERT(NVARCHAR(20), PF.SentOn)
220 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
221 ), 1, 2, '') [SentOn],
222 STUFF(((
223 SELECT DISTINCT ', ' + CONVERT(NVARCHAR(101), PF.SentBy)
224 FROM @productsWithFeedbackSent PF
225 WHERE PF.ProductDistributorID=pr.ProductDistributorId
226 ORDER BY ', ' + CONVERT(NVARCHAR(101), PF.SentBy)
227 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
228 ), 1, 2, '') [SentBy],
229 STUFF(((
230 SELECT DISTINCT ', ' + CONVERT(NVARCHAR(160), PF.SentTo)
231 FROM @productsWithFeedbackSent PF
232 WHERE PF.ProductDistributorID=pr.ProductDistributorId
233 ORDER BY ', ' + CONVERT(NVARCHAR(160), PF.SentTo)
234 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
235 ), 1, 2, '') [SentTo],
236 STUFF(((
237 SELECT DISTINCT ', ' + CONVERT(NVARCHAR(MAX), PF.ExplicitRecipients)
238 FROM @productsWithFeedbackSent PF
239 WHERE PF.ProductDistributorID=pr.ProductDistributorId
240 ORDER BY ', ' + CONVERT(NVARCHAR(MAX), PF.ExplicitRecipients)
241 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
242 ), 1, 2, '') [ExplicitRecipients],");
243 sql.Append(@"
244 pr.RequestedBy,");
245 }
246 else if (param.ShowDeliveredBy)
247 sql.Append(@"
248 pr.DeliveredBy,");
249 if (addProductAssessmentData)
250 sql.Append(@"
251 pad.ProductAssessmentStatus, pad.ProductAssessmentStatus_Static, pad.ProductAssessmentId, pad.ProductAssessmentComment");
252 else
253 sql.Append(@"
254 NULL [ProductAssessmentStatus], NULL [ProductAssessmentStatus_Static], NULL [ProductAssessmentId], NULL [ProductAssessmentComment]");
255
256 sql.Append(@"
257 FROM #synProductsCopy pr");
258 if (addProductAssessmentData)
259 sql.Append(@"
260 JOIN pa.GetProductAssessmentData(@orgElementId, @languageId, @countryId, @userid, @allProductIds) pad ON pad.ProductDistributorID = pr.ProductDistributorID");
261 if (listType == DocProductListType.Deviations && !param.ShowClosedTasks)
262 {
263 sql.AppendFormat(@"
264 {0} @productsWithFeedbackSent fs ON fs.ProductDistributorID = pr.ProductDistributorId AND ISNULL(fs.SentToWorkplaceId,-1)=ISNULL(pr.FbSendToWorkplaceId,-1) COLLATE SQL_Latin1_General_CP1_CS_AS",
265 filter.FilterFeedback == FilterGeneral.Yes ? "JOIN" : "LEFT JOIN");
266
267 if (filter.FilterFeedback == FilterGeneral.No)
268 {
269 sql.Append(@"
270 WHERE fs.ProductDistributorID IS NULL");
271 }
272 }
273 sql.Append(@"
274)
275INSERT @resultUnsorted(
276 Name1, Name2, ProductDistributorID, " +
277 (!param.SkipProductIdentifiers ? "Spn, Gtin," : "") +
278 " SupplierName," +
279 (!param.SkipDateAdded ? " DateAdded," : "") +
280 " ProductType, ProductAssessmentStatus, ProductAssessmentStatus_Static, ProductAssessmentId, ProductAssessmentComment" +
281 (param.ShowDeliveredBy ? ", DeliveredBy" : "") +
282 ((listType == DocProductListType.Deviations) ? ", FbSendToWorkplaceId, TaskId, RequestedBy" + (param.ShowClosedTasks ? ", ClosedOn)" : ", SentOn, SentBy, SentTo, ExplicitRecipients)") : ")") + @"
283SELECT
284 r.Name1, r.Name2, r.ProductDistributorId, " +
285 (!param.SkipProductIdentifiers ? "r.Spn, r.Gtin," : "") +
286 " r.SupplierName," +
287 (!param.SkipDateAdded ? " r.DateAdded, " : "") +
288 " r.ProductType, r.ProductAssessmentStatus, r.ProductAssessmentStatus_Static, r.ProductAssessmentId, r.ProductAssessmentComment" +
289 (param.ShowDeliveredBy ? ", r.DeliveredBy" : "") +
290 ((listType == DocProductListType.Deviations) ? ", r.FbSendToWorkplaceId, r.TaskId, r.RequestedBy" + (param.ShowClosedTasks ? ", r.ClosedOn" : ", r.SentOn, r.SentBy, r.SentTo, r.ExplicitRecipients") : "") + @"
291FROM rows r;");
292
293 #region Stuff Products data added by mnore than one (sub)contractor.
294
295 sql.Append(@"
296DECLARE @result TABLE (
297 [ProductDistributorID] [INT] NOT NULL,
298 [IsPSP] [TINYINT],
299 [Name1] [NVARCHAR](255),
300 [Name2] [NVARCHAR](255),");
301 if (!param.SkipProductIdentifiers)
302 sql.Append(@"
303 [Spn] [NVARCHAR](50),
304 [Gtin] [NVARCHAR](50),");
305 if (!param.SkipDateAdded)
306 sql.Append(@"
307 [DateAdded] [DATETIME],");
308 sql.Append(@"
309 [RowNumber] [INT],
310 [TotalRows] [INT],
311 [DocumentsCount] [INT],
312 [SupplierName] [NVARCHAR] (100),
313 [ProductType] [NVARCHAR](4000),
314 [ProductAssessmentStatus] [SMALLINT],
315 [ProductAssessmentStatus_Static] [SMALLINT],
316 [ProductAssessmentId] [INT],
317 [ProductAssessmentComment] [NVARCHAR](2000)");
318 if (listType == DocProductListType.Deviations)
319 {
320 sql.Append(@",
321 [DeliveredBy] [NVARCHAR](MAX),
322 [FbSendToWorkplaceId] [NVARCHAR](4000),
323 [TaskId] [INT],");
324 if (param.ShowClosedTasks)
325 sql.Append(@"
326 ClosedOn DATETIME,");
327 else
328 sql.Append(@"
329 SentOn NVARCHAR(4000),
330 SentBy NVARCHAR(150),
331 SentTo NVARCHAR(4000),
332 ExplicitRecipients NVARCHAR(4000),");
333 sql.Append(@"
334 RequestedBy NVARCHAR(300),
335 UNIQUE CLUSTERED ([ProductDistributorID], [TaskId])");
336 }
337 else if (param.ShowDeliveredBy)
338 sql.Append(@",
339 [DeliveredBy] [NVARCHAR](MAX),
340 PRIMARY KEY ([ProductDistributorID])");
341 else
342 sql.Append(@",
343 PRIMARY KEY ([ProductDistributorID])
344");
345 sql.Append(@");
346;WITH rows AS (
347 SELECT
348 r.ProductDistributorId,
349 r.Name1,
350 r.Name2,");
351 if (!param.SkipProductIdentifiers)
352 sql.Append(@"
353 r.Spn,
354 r.Gtin,");
355 if (!param.SkipDateAdded)
356 sql.Append(@"
357 r.DateAdded,");
358 sql.Append(@"
359 r.SupplierName,
360 r.ProductType,");
361 if (listType == DocProductListType.Deviations)
362 {
363 sql.Append(@"
364 r.DeliveredBy,
365 r.FbSendToWorkplaceId,
366 r.TaskId,");
367 if (param.ShowClosedTasks)
368 sql.Append(@"
369 r.ClosedOn,");
370 else
371 sql.Append(@"
372 r.SentOn,
373 r.SentBy,
374 r.SentTo,
375 r.ExplicitRecipients,");
376 sql.Append(@"
377 r.RequestedBy,");
378 }
379 else if (param.ShowDeliveredBy)
380 sql.Append(@"
381 r.DeliveredBy,");
382 if (addProductAssessmentData)
383 sql.Append(@"
384 r.ProductAssessmentStatus, r.ProductAssessmentStatus_Static, r.ProductAssessmentId, r.ProductAssessmentComment,");
385 else
386 sql.Append(@"
387 NULL [ProductAssessmentStatus], NULL [ProductAssessmentStatus_Static], NULL [ProductAssessmentId], NULL [ProductAssessmentComment],");
388
389 #region RowNumber/InverseRowNumber
390 if (String.IsNullOrWhiteSpace(param.SortColumn) || param.SortColumn == "Name1" || (!addProductAssessmentData && param.SortColumn == "ProductAssessmentStatus"))
391 sql.Append(@"
392 ROW_NUMBER() OVER (ORDER BY r.Name1 $order$, r.ProductDistributorId $order$) [RowNumber],
393 ROW_NUMBER() OVER (ORDER BY r.Name1 $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
394 else if (param.SortColumn == "Spn")
395 sql.Append(@"
396 ROW_NUMBER() OVER (ORDER BY r.Spn $order$, r.ProductDistributorId $order$) [RowNumber],
397 ROW_NUMBER() OVER (ORDER BY r.Spn $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
398 else if (param.SortColumn == "Gtin")
399 sql.Append(@"
400 ROW_NUMBER() OVER (ORDER BY r.Gtin $order$, r.ProductDistributorId $order$) [RowNumber],
401 ROW_NUMBER() OVER (ORDER BY r.Gtin $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
402 else if (param.SortColumn == "Identifiers")
403 sql.Append(@"
404 ROW_NUMBER() OVER (ORDER BY r.Gtin $order$, r.Spn $order$, r.ProductDistributorId $order$) [RowNumber],
405 ROW_NUMBER() OVER (ORDER BY r.Gtin $inverseOrder$, r.Spn $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
406 else if (param.SortColumn == "DateAdded")
407 sql.Append(@"
408 ROW_NUMBER() OVER (ORDER BY r.DateAdded $order$, r.Name1 $order$, r.ProductDistributorId $order$) [RowNumber],
409 ROW_NUMBER() OVER (ORDER BY r.DateAdded $inverseOrder$, r.Name1 $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
410 else if (param.SortColumn == "SupplierName")
411 sql.Append(@"
412 ROW_NUMBER() OVER (ORDER BY r.SupplierName $order$, r.Name1 $order$, r.ProductDistributorId $order$) [RowNumber],
413 ROW_NUMBER() OVER (ORDER BY r.SupplierName $inverseOrder$, r.Name1 $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
414 else if (param.SortColumn == "ProductType")
415 sql.Append(@"
416 ROW_NUMBER() OVER (ORDER BY r.ProductType $order$, r.Name1 $order$, r.ProductDistributorId $order$) [RowNumber],
417 ROW_NUMBER() OVER (ORDER BY r.ProductType $inverseOrder$, r.Name1 $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
418 else if (param.SortColumn == "ProductAssessmentStatus")
419 sql.Append(@"
420 ROW_NUMBER() OVER (ORDER BY r.ProductAssessmentStatus $order$, r.Name1 $order$, r.ProductDistributorId $order$) [RowNumber],
421 ROW_NUMBER() OVER (ORDER BY r.ProductAssessmentStatus $inverseOrder$, r.Name1 $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
422 else if (param.SortColumn == "DeliveredBy")
423 sql.Append(@"
424 ROW_NUMBER() OVER (ORDER BY r.DeliveredBy $order$, r.Name1 $order$, r.ProductDistributorId $order$) [RowNumber],
425 ROW_NUMBER() OVER (ORDER BY r.DeliveredBy $inverseOrder$, r.Name1 $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
426 else if (param.SortColumn == "RequestedBy")
427 sql.Append(@"
428 ROW_NUMBER() OVER (ORDER BY r.RequestedBy $order$, r.Name1 ASC, r.DeliveredBy $order$, r.ProductDistributorId $order$) [RowNumber],
429 ROW_NUMBER() OVER (ORDER BY r.RequestedBy $inverseOrder$, r.Name1 DESC, r.DeliveredBy $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
430 else if (param.SortColumn == "SentOn")
431 sql.Append(@"
432 ROW_NUMBER() OVER (ORDER BY r.SentOn $order$, r.Name1 $order$, r.DeliveredBy $order$, r.ProductDistributorId $order$) [RowNumber],
433 ROW_NUMBER() OVER (ORDER BY r.SentOn $inverseOrder$, r.Name1 $inverseOrder$, r.DeliveredBy $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
434 else if (param.SortColumn == "ClosedOn")
435 sql.Append(@"
436 ROW_NUMBER() OVER (ORDER BY r.ClosedOn $order$, r.Name1 $order$, r.DeliveredBy $order$, r.ProductDistributorId $order$) [RowNumber],
437 ROW_NUMBER() OVER (ORDER BY r.ClosedOn $inverseOrder$, r.Name1 $inverseOrder$, r.DeliveredBy $inverseOrder$, r.ProductDistributorId $inverseOrder$) [InverseRowNumber]");
438 #endregion
439
440 sql.Append(@"
441 FROM @resultUnsorted r");
442
443 sql.Append(@"
444)
445INSERT @result(
446 Name1, Name2, ProductDistributorID, " +
447 (!param.SkipProductIdentifiers ? "Spn, Gtin," : "") +
448 " SupplierName," +
449 (!param.SkipDateAdded ? " DateAdded," : "") +
450 " RowNumber, TotalRows, ProductType, ProductAssessmentStatus, ProductAssessmentStatus_Static, ProductAssessmentId, ProductAssessmentComment" +
451 (param.ShowDeliveredBy ? ", DeliveredBy" : "") +
452 ((listType == DocProductListType.Deviations) ? ", FbSendToWorkplaceId, TaskId, RequestedBy" + (param.ShowClosedTasks ? ", ClosedOn)" : ", SentOn, SentBy, SentTo, ExplicitRecipients)") : ")") + @"
453SELECT
454 r.Name1, r.Name2, r.ProductDistributorId, " +
455 (!param.SkipProductIdentifiers ? "r.Spn, r.Gtin," : "") +
456 " r.SupplierName," +
457 (!param.SkipDateAdded ? " r.DateAdded, " : "") +
458 " r.RowNumber, r.InverseRowNumber + r.RowNumber - 1, r.ProductType, r.ProductAssessmentStatus, r.ProductAssessmentStatus_Static, r.ProductAssessmentId, r.ProductAssessmentComment" +
459 (param.ShowDeliveredBy ? ", r.DeliveredBy" : "") +
460 ((listType == DocProductListType.Deviations) ? ", r.FbSendToWorkplaceId, r.TaskId, r.RequestedBy" + (param.ShowClosedTasks ? ", r.ClosedOn" : ", r.SentOn, r.SentBy, r.SentTo, r.ExplicitRecipients") : "") + @"
461FROM rows r
462WHERE RowNumber BETWEEN ((@pageNumber - 1) * @pageSize + 1) AND (@pageNumber * @pageSize)
463ORDER BY RowNumber;");
464
465 #endregion
466
467 sql.Append(@"DECLARE @productIdsToBeShown dbo.Array
468INSERT INTO @productIdsToBeShown
469SELECT DISTINCT ProductDistributorID FROM @result;");
470
471 SqlBuilders.WorkplaceToProduct.SetPSPFlag(sql);
472 SqlBuilders.WorkplaceToProduct.SetDocumentCounts(sql, listType);
473
474 if (listType != DocProductListType.DistributeSDS)
475 {
476 sql.Append(@"
477DECLARE @targetFilterWorkplaceIds dbo.Array;
478INSERT @targetFilterWorkplaceIds SELECT
479@rootWorkplaceId;
480");
481 SqlBuilders.WorkplaceToProduct.ExtractProductComments(sql, param.IsCommnetsShown);
482
483 //Bug#40519:FR-PI40.1 Deviations sub-dashboard - missing harmonized standard icon
484 SqlBuilders.WorkplaceToProduct.ExtractProductFlags(sql);
485 if (listType != DocProductListType.Deviations)
486 {
487 // SqlBuilders.WorkplaceToProduct.ExtractProductFlags(sql);
488 SqlBuilders.WorkplaceToProduct.ExtractPNCDetails(sql);
489 SqlBuilders.WorkplaceToProduct.ExtractEcoRuleCompliance(sql);
490 SqlBuilders.WorkplaceToProduct.ExtractProductFilterCompliance(sql);
491 }
492 }
493
494 if (listType == DocProductListType.DistributeSDS)
495 {
496 sql.Append(@"
497SELECT * FROM @result
498WHERE DocumentsCount>0
499");
500 }
501 else
502 {
503 sql.Append(@"
504SELECT *,
505(SELECT COUNT(DISTINCT CommentId) FROM @comments c WHERE c.ProductDistributorId=r.ProductDistributorID) [CommentCount], ");
506 if (param.IsCommnetsShown)
507 {
508 sql.Append(@"
509STUFF(((SELECT DISTINCT [comm].CommentText + ' | ' FROM @comments [comm]
510 WHERE [comm].ProductDistributorId = r.ProductDistributorID
511 AND LEN([comm].CommentText) > 0
512 FOR XML PATH(''), TYPE).value('.','nvarchar(max)'))
513 ,1,0,'') [Comments], ");
514 }
515 sql.Append(@"
5161 [FeedbackEnabled]
517FROM @result r
518INNER JOIN @flags f ON f.ProductDistributorID=r.ProductDistributorID ");
519 //Bug#40519:FR-PI40.1 Deviations sub-dashboard - missing harmonized standard icon --INNER JOIN @flags f ON f.ProductDistributorID=r.ProductDistributorID
520 if (listType != DocProductListType.Deviations)
521 {
522 sql.Append(@"
523INNER JOIN @compliance c ON c.ProductDistributorId=r.ProductDistributorID
524LEFT JOIN @ecoCompliance c1 ON c1.ProductDistributorId=r.ProductDistributorID
525LEFT JOIN @productsWithFilterCriteria fc ON fc.ProductDistributorId = r.ProductDistributorID
526");
527 }
528 }
529
530 sql.Append(@"
531ORDER BY RowNumber;");
532
533 bool includeRulesNotRequiredByLegislation = param.ChemicalListFilters.HasFlag(WorkplaceChemicalList.Checklist_A20) || param.ChemicalListFilters.HasFlag(WorkplaceChemicalList.Checklist_A202016);
534
535 using (IHelper helper = MainDALC.GetHelper())
536 {
537 SqlCommand command = (SqlCommand)helper.SelectCommand;
538 command.CommandType = CommandType.Text;
539
540 AddParameter(command, "@rootWorkplaceId", SqlDbType.Int, -1, ParameterDirection.Input, param.WorkplaceId);
541 AddParameter(command, "@isWorkplaceImported", SqlDbType.Bit, -1, ParameterDirection.Input, param.IsWorkplaceImported);
542 AddParameter(command, "@orgElementId", SqlDbType.Int, -1, ParameterDirection.Input, param.OrgElementId);
543 AddParameter(command, "@includeArchivedWorkplaces", SqlDbType.Bit, -1, ParameterDirection.Input, param.DeviationType == DeviationType.BlackProducts ? true : param.IncludeArchivedWorkplaces);
544 AddParameter(command, "@includeRulesNotRequiredByLegislation", SqlDbType.Bit, -1, ParameterDirection.Input, includeRulesNotRequiredByLegislation);
545
546 AddParameter(command, "@pageNumber", SqlDbType.Int, -1, ParameterDirection.Input, param.PageIndex);
547 AddParameter(command, "@pageSize", SqlDbType.Int, -1, ParameterDirection.Input, param.PageSize);
548 AddParameter(command, "@countryId", SqlDbType.Int, -1, ParameterDirection.Input, (int)param.Country);
549 AddParameter(command, "@languageId", SqlDbType.Int, -1, ParameterDirection.Input, (int)param.Language);
550 AddParameter(command, "@feedbackMsgReasonId", SqlDbType.Int, -1, ParameterDirection.Input, (int)MessageReason.ProductNoncomplianceFeedback);
551 AddParameter(command, "@workplaceToProductEntityTypeId", SqlDbType.Int, -1, ParameterDirection.Input, (int)EntityType.WorkplaceToProduct);
552 AddParameter(command, "@ecoRuleId", SqlDbType.Int, -1, ParameterDirection.Input, (int)ProductRule.HasEcoRule);
553
554 AddParameter(command, "@deviationType", SqlDbType.Int, -1, ParameterDirection.Input, (int)param.DeviationType);
555 AddParameter(command, "@taskStatus", SqlDbType.Int, -1, ParameterDirection.Input, param.ShowClosedTasks ? 1 : 0);
556 AddParameter(command, "@objectTypeProduct", SqlDbType.Int, -1, ParameterDirection.Input, (int)EntityType.Product);
557 AddParameter(command, "@deviationEntityTypeId", SqlDbType.Int, -1, ParameterDirection.Input, (int)EntityType.Deviation);
558 AddParameter(command, "@deviationFilterKey", SqlDbType.NVarChar, -1, ParameterDirection.Input, String.IsNullOrWhiteSpace(param.SummaryOptionRule) ? (object)DBNull.Value : param.SummaryOptionRule);
559
560 if (addProductAssessmentData)
561 AddParameter(command, "@userid", SqlDbType.UniqueIdentifier, -1, ParameterDirection.Input, param.UserId);
562
563 if (useFullTextIndex && filter.HasSearchTerm)
564 {
565 string filterSearchTerm = filter.SearchTerm.Replace("\"", "");
566 AddParameter(command, "@searchTerm", SqlDbType.NVarChar, -1, ParameterDirection.Input, FormatValueForFullTextSearch(filterSearchTerm));
567 }
568 else
569 {
570 AddParameter(command, "@searchTerm", SqlDbType.NVarChar, -1, ParameterDirection.Input, "%" + filter.SearchTerm + "%");
571 }
572
573 if (param.StartDateFilter != DateTime.MinValue)
574 AddParameter(command, "@startDateFilter", SqlDbType.DateTime, -1, ParameterDirection.Input, param.StartDateFilter);
575 if (param.EndDateFilter != DateTime.MinValue)
576 AddParameter(command, "@endDateFilter", SqlDbType.DateTime, -1, ParameterDirection.Input, param.EndDateFilter);
577
578 command.CommandText = sql.ToString()
579 .Replace("$order$", param.SortDescending ? "DESC" : "ASC")
580 .Replace("$inverseOrder$", param.SortDescending ? "ASC" : "DESC")
581 .Replace("{DeviationWorkplaceFilter}", param.IsWorkplaceInvariantDeviation ? String.Empty : " AND dev.WorkplaceId=@rootWorkplaceId")
582 .Replace("{FilterByName}", String.Empty)
583 .Replace("{FilterByArticleNumber}", String.Empty);
584
585 if (commandTimeout.HasValue)
586 {
587 command.CommandTimeout = commandTimeout.Value;
588 }
589
590 helper.Fill(dset);
591 if (filter.Enabled)
592 {
593 dset.Tables[0].TableName = "Suppliers";
594 dset.Tables[1].TableName = "ProductTypes";
595 dset.Tables[2].TableName = "DocumentTypes";
596 if (param.ShowDeliveredBy)
597 dset.Tables[3].TableName = "DeliveredBy";
598 }
599 dset.Tables[dset.Tables.Count - 2].TableName = "AllProductDistributorIds";
600 dset.Tables[dset.Tables.Count - 1].TableName = "Products";
601 }
602
603 FillHasTechSpecificationAndHarmonizedStandards(dset, languageCode, isTechnicalSpecificationFunctionalityVisible, listType != DocProductListType.DistributeSDS);
604
605 if (classificationParam.ShouldBeApplied)
606 {
607 FilterByClassification(dset, classificationParam);
608 }
609 }
610
611internal static void GetProductsAndWorkplacesFilteredByDate(DocProductListParams param, StringBuilder sql)
612 {
613 string dateFilter = null;
614 if (param.StartDateFilter != DateTime.MinValue)
615 dateFilter = @" wtp.CreatedDate >= @startDateFilter ";
616 if (param.StartDateFilter != DateTime.MinValue && param.EndDateFilter != DateTime.MinValue)
617 dateFilter += @" AND ";
618 if (param.EndDateFilter != DateTime.MinValue)
619 dateFilter += @" wtp.CreatedDate < DATEADD(DAY, 1, @endDateFilter) ";
620
621 if (param.Ids != null)
622 {
623 sql.Append(@"DECLARE @filteredProductIds dbo.Array; ");
624 sql.Append(QueriesBase.MakeInsertInArrayClause(param.Ids.Distinct().ToList(), @"@filteredProductIds"));
625 sql.Append(@"
626INSERT #productIds
627SELECT DISTINCT wtp.ProductDistributorID
628FROM [pi].[WorkplaceToProduct] wtp (NOLOCK)
629WHERE wtp.ProductDistributorID in (SELECT i.ID FROM @filteredProductIds i)
630");
631 if (!String.IsNullOrWhiteSpace(dateFilter))
632 sql.Append(@"
633AND " + dateFilter);
634 }
635
636 sql.Append(@" DECLARE @filteredWorkplaceIds dbo.Array; ");
637 if (param.WorkplaceIds != null && param.WorkplaceIds.Count > 0)
638 {
639 sql.Append(QueriesBase.MakeInsertInArrayClause(param.WorkplaceIds.Distinct().ToList(), @"@filteredWorkplaceIds"));
640 sql.Append(@"
641INSERT @workplaceIds
642SELECT DISTINCT wtp.WorkplaceId
643FROM [pi].[WorkplaceToProduct] wtp (NOLOCK)
644WHERE wtp.WorkplaceId IN (SELECT i.ID FROM @filteredWorkplaceIds i)
645");
646 if (!String.IsNullOrWhiteSpace(dateFilter))
647 sql.Append(@"
648AND " + dateFilter);
649
650 if (param.Ids == null)
651 {
652 sql.Append(@"
653INSERT #productIds
654SELECT DISTINCT wtp.ProductDistributorID
655FROM [pi].[WorkplaceToProduct] wtp (NOLOCK)
656INNER JOIN @workplaceIds wp ON wp.ID=wtp.WorkplaceID
657");
658 if (!String.IsNullOrWhiteSpace(dateFilter))
659 sql.Append(@"
660WHERE " + dateFilter);
661 }
662 }
663 }
664
665 internal static void GetProductsAndWorkplaces(DocProductListParams param, StringBuilder sql, DocProductListType listType)
666 {
667 if (param.Ids != null)
668 sql.Append(QueriesBase.MakeInsertInArrayClause(param.Ids.Distinct().ToList(), @"#productIds"));
669 if (!String.IsNullOrWhiteSpace(param.ProductSourceQuery))
670 sql.AppendFormat(@"INSERT INTO #productIds {0}", param.ProductSourceQuery);
671
672 if (param.WorkplaceIds != null && param.WorkplaceIds.Count > 0)
673 {
674 if (param.DeviationType == DeviationType.BlackProducts)
675 {
676 sql.Append(@"
677;WITH TREE(Id, ParentId, RecursionLevel)
678AS
679(
680 /* root WP */
681 SELECT WP.ID [Id], NULL [ParentId], 0
682 FROM pi.WorkplaceNotDeleted WP (NOLOCK)
683 JOIN pi.OrgElement OE (NOLOCK) ON OE.ID = WP.OrgElementId
684 WHERE
685 WP.Id = @rootWorkplaceId AND OE.IsWholesaler = 0
686 --AND WP.IsArchived = 0
687
688 UNION ALL
689
690 /* subworkplaces */
691 SELECT child.ID [Id], parent.ID [ParentId], parent.RecursionLevel + 1
692 FROM TREE parent
693 INNER JOIN pi.WorkplaceNotDeleted child (NOLOCK) ON child.ParentId = parent.ID
694 WHERE
695 parent.ID <> @rootWorkplaceId
696 -- AND child.IsArchived = 0
697 AND (parent.RecursionLevel + 1 <= 20)
698
699 UNION ALL
700
701 /* imported project/company from WHOLESALERS */
702 SELECT child.ID [Id], parent.Id [ParentId], parent.RecursionLevel + 1
703 FROM TREE parent
704 INNER JOIN pi.ImportedWorkplace import (NOLOCK) ON import.ParentID = parent.ID
705 INNER JOIN pi.WorkplaceNotDeleted child (NOLOCK) ON child.ID = import.WorkplaceID
706 INNER JOIN pi.OrgElement importedCompany (NOLOCK) ON importedCompany.ID = child.OrgElementId
707 WHERE importedCompany.IsWholesaler = 1
708 AND import.Deleted = 0
709 --AND child.IsArchived = 0
710 AND (parent.RecursionLevel + 1 <= 20)
711
712 --UNION ALL
713
714 --/* OTHER imported project/company */
715 --SELECT child.ID [Id], parent.id [ParentId], CAST(importedCompany.Name AS NVARCHAR(300)) [DisplayAsName], child.ID [DisplayAsId], 1 [IsImportedWorkplace], child.ID, @emptyGuid, parent.RecursionLevel + 1
716 --FROM TREE parent
717 --INNER JOIN pi.ImportedWorkplace import (NOLOCK) ON import.ParentID = parent.ID
718 --INNER JOIN pi.WorkplaceNotDeleted child (NOLOCK) ON child.ID = import.WorkplaceID
719 --INNER JOIN pi.OrgElement importedCompany (NOLOCK) ON importedCompany.ID = child.OrgElementId
720 --WHERE importedCompany.IsWholesaler = 0
721 -- AND import.Deleted = 0
722 -- AND child.IsArchived = 0
723 -- AND (parent.RecursionLevel + 1 <= 20)
724)
725
726INSERT @workplaceIds
727SELECT DISTINCT t.id
728FROM TREE t
729
730");
731 }
732 else
733 {
734 sql.Append(QueriesBase.MakeInsertInArrayClause(param.WorkplaceIds.Distinct().ToList(), @"@workplaceIds"));
735 }
736
737 if (param.ShowDeliveredBy)
738 {
739 sql.Append(@"
740IF OBJECT_ID('tempdb..#deliveredByData') IS NOT NULL
741 DROP TABLE #deliveredByData;
742CREATE TABLE #deliveredByData (ProductDistributorID INT, ID INT, ParentID INT, IsImported BIT, IsArchived BIT, FbSendToWorkplaceId INT, DisplayName NVARCHAR(300), TaskId INT);
743
744DECLARE @MAX_RECURSION_LEVEL TINYINT = 20;
745;WITH deliveredBy AS (
746 SELECT DISTINCT
747 tree.ID, tree.ParentID, tree.IsImportedWorkplace [IsImported], wp.IsArchived, orgEl.Name
748 FROM pi.GetWorkplaceSubtree(@rootWorkplaceId, @includeArchivedWorkplaces, 1, 1) tree
749 JOIN @workplaceIds list ON list.ID=tree.ID
750 JOIN pi.Workplace wp (NOLOCK) ON wp.ID=list.ID
751 JOIN pi.OrgElement orgEl (NOLOCK) ON orgEl.ID=CASE WHEN tree.IsImportedWorkplace=1 THEN wp.OrgElementId ELSE wp.CompanyOrgElID END
752)
753,deliveredByWithViewFix AS (
754 SELECT
755 0 AS RecursionLevel,
756 ID, ParentID, IsImported, IsArchived,
757 ID [FbSendToWorkplaceId], Name [DisplayName]
758 FROM deliveredBy
759 WHERE ID = @rootWorkplaceId
760 UNION ALL
761 SELECT
762 (parent.RecursionLevel + 1) AS RecursionLevel,
763 child.ID, child.ParentID, child.IsImported, child.IsArchived,
764 CASE
765 WHEN child.IsImported=0 THEN child.ID
766 WHEN child.IsImported=1 AND parent.IsImported=0 THEN child.ID
767 WHEN child.IsImported=1 AND parent.IsImported=1 AND parent.ID<>@rootWorkplaceId THEN parent.FbSendToWorkplaceId
768 ELSE child.ID
769 END [DisplayId],
770 CASE
771 WHEN child.IsImported=0 THEN child.Name
772 WHEN child.IsImported=1 AND parent.IsImported=0 THEN child.Name
773 WHEN child.IsImported=1 AND parent.IsImported=1 AND parent.ID<>@rootWorkplaceId THEN parent.DisplayName
774 ELSE child.Name
775 END [DisplayName]
776 FROM deliveredBy child
777 JOIN deliveredByWithViewFix parent ON parent.ID=child.ParentID AND (parent.RecursionLevel + 1) <= @MAX_RECURSION_LEVEL
778)
779INSERT #deliveredByData
780SELECT DISTINCT wtp.ProductDistributorID, tree.ID, tree.ParentID, tree.IsImported, tree.IsArchived, tree.FbSendToWorkplaceId, tree.DisplayName, ");
781 sql.Append(listType == DocProductListType.Deviations ? "dev.ID" : "NULL");
782 sql.Append(@"
783FROM deliveredByWithViewFix tree
784JOIN pi.WorkplaceToProduct wtp (NOLOCK) ON wtp.WorkplaceId=tree.ID");
785
786 if (listType == DocProductListType.Deviations)
787 {
788 sql.Append(@"
789JOIN task.Deviation dev (NOLOCK) ON dev.ObjectId = wtp.ProductDistributorID {DeviationWorkplaceFilter}");
790
791 sql.AppendFormat(@" JOIN task.DeviationToWorkplace dw ON dev.ID = dw.TaskId AND dw.WorkplaceId = tree.Id ");
792
793 sql.Append(@"WHERE
794 dev.ObjectType = @objectTypeProduct AND dev.DeviationType = @deviationType AND dw.[Status] = @taskStatus
795 AND ISNULL(dev.FilterKey, '') = ISNULL(@deviationFilterKey COLLATE SQL_Latin1_General_CP1_CS_AS, '');
796");
797 }
798 sql.Append(@"
799CREATE INDEX IDX_deliveredByData_ProductDistributorId_
800ON #deliveredByData (ProductDistributorId);
801");
802 }
803 if (param.Ids == null || (param.Ids.Count < 1 && !param.ShowClosedTasks))
804 {
805 sql.Append(@"
806IF NOT EXISTS (SELECT NULL FROM #productIds)
807BEGIN
808 INSERT #productIds
809 SELECT DISTINCT wtp.ProductDistributorID
810 FROM [pi].[WorkplaceToProduct] wtp (NOLOCK)
811 INNER JOIN @workplaceIds wp ON wp.ID=wtp.WorkplaceID;
812END");
813 }
814
815 if (param.ShowClosedTasks)
816 {
817 sql.Append(@"
818IF NOT EXISTS (SELECT NULL FROM #productIds)
819BEGIN
820 INSERT #productIds
821 SELECT DISTINCT d.ProductDistributorID FROM #deliveredByData d;
822END");
823 }
824
825
826 }
827 }
828
829 internal static void ExtractPNCFeedback(StringBuilder sql)
830 {
831 sql.Append(@"
832/*PNC FEEDBACK*/
833DECLARE @productsWithFeedbackSent TABLE (
834 ProductDistributorID INT,
835 SentOn DATETIME,
836 SentBy NVARCHAR(150),
837 SentTo NVARCHAR(300),
838 SentToWorkplaceId NVARCHAR(4000),
839 ExplicitRecipients NVARCHAR(4000)
840);
841;WITH feedback AS (
842 SELECT DISTINCT
843 dev.ObjectID [ProductDistributorID],
844 im.CreatedDate [SentOn],
845 ISNULL(ud.FirstName, '') + ' ' + ISNULL(ud.LastName, '') [SentBy],
846 oe.Name [SentTo],
847 CONVERT(nvarchar(4000), im.SentToWorkplaceId) [SentToWorkplaceId],
848 im.ExplicitRecipients,
849 ROW_NUMBER() OVER (PARTITION BY dev.ObjectID, im.SentToWorkplaceId ORDER BY im.CreatedDate DESC) [ChronologicalOrder]
850 FROM task.Deviation dev (NOLOCK)
851 JOIN msg.InternalMessage im (NOLOCK) ON im.TaskId=dev.ID
852 JOIN usr.UserData ud (NOLOCK) ON ud.UserId=im.[From]
853 LEFT JOIN pi.Workplace wp (NOLOCK) ON wp.ID=im.SentToWorkplaceId
854 LEFT JOIN pi.OrgElement oe (NOLOCK) ON oe.ID=wp.CompanyOrgElID
855 WHERE ud.OrgElementId IN (SELECT oeScope.ID FROM pi.GetOrgElementTree(@orgElementId) oeScope)
856 AND dev.DeviationType = @deviationType AND dev.ObjectType = @objectTypeProduct
857 AND ISNULL(dev.FilterKey, '') = ISNULL(@deviationFilterKey, '') COLLATE SQL_Latin1_General_CP1_CS_AS
858 AND (im.SentToWorkplaceId IS NULL OR im.SentToWorkplaceId IN (
859 SELECT wpScope.FbSendToWorkplaceId FROM #deliveredByData wpScope
860-- WHERE wpScope.FbSendToWorkplaceId != @rootWorkplaceId
861 ))
862 {DeviationWorkplaceFilter}
863)
864INSERT @productsWithFeedbackSent SELECT DISTINCT
865 ProductDistributorID, SentOn, SentBy,
866 SentTo,
867 SentToWorkplaceId,
868 ExplicitRecipients
869FROM feedback f
870WHERE [ChronologicalOrder]=1;
871
872DELETE toDelete FROM @productsWithFeedbackSent toDelete
873WHERE EXISTS (
874 SELECT NULL FROM @productsWithFeedbackSent fb
875 WHERE fb.SentToWorkplaceId = toDelete.SentToWorkplaceId
876 AND fb.ProductDistributorID = toDelete.ProductDistributorID
877 AND toDelete.SentOn < fb.SentOn
878);
879/*PNC FEEDBACK*/
880");
881 }
882
883internal static void GetAllProductsFilteredByName(DocProductListParams param, DocProductFilterParams filter, StringBuilder sql)
884 {
885 if (filter.HasSearchTerm)
886 {
887 sql.AppendFormat(@"
888IF OBJECT_ID('tempdb..#allProductsFilteredByName') IS NOT NULL
889 DROP TABLE #allProductsFilteredByName;
890CREATE TABLE #allProductsFilteredByName (ID INT PRIMARY KEY);
891
892INSERT #allProductsFilteredByName
893SELECT DISTINCT DP.ProductDistributorId
894FROM #productIds pIds
895INNER JOIN [{0}].[dbo].[vDistributorParameter] DP (NOLOCK) ON pIds.ID = DP.ProductDistributorId
896INNER JOIN dbo.synProducts(CAST(@countryIndex AS CHAR(1))) pr ON pIds.ID = pr.ProductDistributorId
897WHERE CONTAINS ((DP.product_text1, DP.product_text2), @searchTerm)
898AND pr.DistributorParameterId = DP.DistributorParameterId; /*Use full-text index*/
899", filter.VegasDBName);
900 }
901 }
902
903internal static void GetAllProductsFilteredByArticleNumber(DocProductListParams param, DocProductFilterParams filter, StringBuilder sql)
904 {
905 if (filter.HasArticleNumber)
906 {
907 sql.AppendFormat(@"
908IF OBJECT_ID('tempdb..#allProductsFilteredArticleNumber') IS NOT NULL
909 DROP TABLE #allProductsFilteredArticleNumber;
910CREATE TABLE #allProductsFilteredArticleNumber (ID INT PRIMARY KEY);
911
912DECLARE @selectedProducts as dbo.Array;
913INSERT @selectedProducts
914SELECT ID FROM #productIds ;
915
916INSERT #allProductsFilteredArticleNumber
917SELECT DISTINCT DP.ProductDistributorId
918FROM pi.GetProductsByArticleNumber_v2('%{0}%',@selectedProducts) DP
919", filter.ArticleNumber);
920 }
921
922 if (filter.HasSearchTerm)
923 {
924 sql.AppendFormat(@"
925DELETE FROM #allProductsFilteredArticleNumber
926WHERE ID NOT IN (SELECT * FROM #allProductsFilteredByName)");
927 }
928 }
929
930internal static void ExtractProducts(DocProductListParams param, DocProductFilterParams filter, StringBuilder sql, DocProductListType listType)
931 {
932 sql.Append(@"
933IF OBJECT_ID('tempdb..#synProductsCopy') IS NOT NULL
934 DROP TABLE #synProductsCopy;
935CREATE TABLE #synProductsCopy (
936 ProductDistributorId INT NOT NULL,
937 Name1 NVARCHAR(255) NULL,
938 Name2 NVARCHAR(255) NULL,");
939 if (!param.SkipProductIdentifiers)
940 sql.Append(@"
941 Spn NVARCHAR(50) NULL,
942 Gtin NVARCHAR(50) NULL,");
943 if (!param.SkipDateAdded)
944 sql.Append(@"
945 DateAdded DATETIME NULL,");
946 if (listType == DocProductListType.Deviations)
947 {
948 sql.Append(@"
949 DeliveredBy NVARCHAR(4000),
950 RequestedBy NVARCHAR(300),
951 FbSendToWorkplaceId NVARCHAR(4000),
952 TaskId INT,");
953 if (param.ShowClosedTasks)
954 sql.Append(@"
955 ClosedOn DATETIME,");
956 }
957 else if (param.ShowDeliveredBy)
958 sql.Append(@"
959 DeliveredBy NVARCHAR(4000),");
960 sql.Append(@"
961 Supplier_levid INT,
962 SupplierName NVARCHAR(160) NULL,
963 ProductType NVARCHAR(4000),
964 ProductAssessmentStatus SMALLINT
965);
966
967INSERT #synProductsCopy (
968 ProductDistributorId,
969 Name1,
970 Name2,");
971 if (!param.SkipProductIdentifiers)
972 sql.Append(@"
973 Spn,
974 Gtin,");
975 if (!param.SkipDateAdded)
976 sql.Append(@"
977 DateAdded,");
978 if (listType == DocProductListType.Deviations)
979 {
980 sql.Append(@"
981 DeliveredBy,
982 RequestedBy,
983 FbSendToWorkplaceId,
984 TaskId,");
985 if (param.ShowClosedTasks)
986 sql.Append(@"
987 ClosedOn,");
988 }
989 else if (param.ShowDeliveredBy)
990 sql.Append(@"
991 DeliveredBy,");
992 sql.Append(@"
993 Supplier_levid,
994 SupplierName
995)
996SELECT DISTINCT
997 pr.ProductDistributorId,
998 pr.Name1,
999 pr.Name2,");
1000 if (!param.SkipProductIdentifiers)
1001 sql.Append(@"
1002 pr.Spn,
1003 pr.Gtin,");
1004 if (!param.SkipDateAdded)
1005 sql.Append(@"
1006 (
1007 SELECT MAX(WTP.CreatedDate)
1008 FROM pi.WorkplaceToProduct WTP (NOLOCK)
1009 INNER JOIN @workplaceIds WP ON WP.ID = WTP.WorkplaceId
1010 WHERE WTP.ProductDistributorID = pr.ProductDistributorId
1011 ) [DateAdded],");
1012 if (listType == DocProductListType.Deviations)
1013 {
1014 sql.Append(@"
1015 wp.DisplayName [DeliveredBy],
1016 (
1017 SELECT TOP (1) oe.Name
1018 FROM task.Deviation dev (NOLOCK)
1019 JOIN msg.InternalMessage im (NOLOCK) ON im.TaskId=dev.ID
1020 JOIN usr.UserData ud (NOLOCK) ON ud.UserID=im.[From]
1021 JOIN pi.OrgElement oe (NOLOCK) ON oe.ID=ud.OrgElementId
1022 WHERE im.SentToWorkplaceId=@rootWorkplaceId
1023 AND (@wpIsSubworkplace = 1 OR oe.ID <> @wpCompanyOrgElementId)
1024 AND dev.DeviationType = @deviationType
1025 AND dev.ObjectId = pr.ProductDistributorID AND dev.ObjectType = @objectTypeProduct
1026 AND ISNULL(dev.FilterKey, '') = ISNULL(@deviationFilterKey, '') COLLATE SQL_Latin1_General_CP1_CS_AS
1027 ORDER BY im.CreatedDate DESC
1028 ) [RequestedBy],
1029 CASE WHEN wp.IsImported=1 THEN wp.FbSendToWorkplaceId ELSE NULL END,
1030 wp.TaskId,");
1031 if (param.ShowClosedTasks)
1032 sql.Append(@"
1033 (SELECT TOP 1 dw.StatusDate
1034 FROM task.Deviation dev WITH (NOLOCK)
1035 INNER JOIN task.DeviationToWorkplace dw ON dw.TaskId = dev.ID
1036 WHERE dev.ObjectID = pr.ProductDistributorId AND dev.ObjectType = @objectTypeProduct
1037 AND dw.[Status] = 1
1038 AND dev.DeviationType=@deviationType AND ISNULL(dev.FilterKey, '') = ISNULL(@deviationFilterKey, '') COLLATE SQL_Latin1_General_CP1_CS_AS
1039 {DeviationWorkplaceFilter}
1040 ORDER BY dw.StatusDate DESC),");
1041 }
1042 else if (param.ShowDeliveredBy)
1043 sql.Append(@"
1044 STUFF(((
1045 SELECT DISTINCT ', ' + WP.DisplayName
1046 FROM #deliveredByData WP
1047 WHERE WP.ProductDistributorID=pr.ProductDistributorId
1048 ORDER BY ', ' + WP.DisplayName
1049 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
1050 ), 1, 2, '') [DeliveredBy],");
1051 sql.Append(@"
1052 pr.levid,
1053 pr.SupplierName
1054FROM dbo.synProducts" + (param.SkipProductIdentifiers ? "Short" : "") + @"(CAST(@countryIndex AS CHAR(1))) pr
1055INNER JOIN #productIds i ON i.ID=pr.ProductDistributorID
1056{FilterByName} {FilterByArticleNumber}");
1057
1058 if (listType == DocProductListType.Deviations || filter.HasSelectedWorkplaces)
1059 sql.AppendFormat(@"
1060{0} #deliveredByData wp ON wp.ProductDistributorID = pr.ProductDistributorId", param.ShowClosedTasks ? "LEFT JOIN" : "JOIN");
1061 if (filter.HasSelectedWorkplaces)
1062 sql.AppendFormat(@" WHERE wp.ID IN ({0})", QueriesBase.MakeInClause(filter.WorkplaceIds, false));
1063 sql.Append(@"
1064
1065CREATE INDEX IDX_synProductsCopy_ProductDistributorId_
1066ON #synProductsCopy (ProductDistributorId);
1067");
1068 }
1069
1070 internal static void ExtractProductTypes(StringBuilder sql, DocProductListParams param)
1071 {
1072 sql.Append(@"
1073/*PRODUCT TYPES*/
1074DECLARE @productIds dbo.Array;
1075INSERT @productIds SELECT ID FROM #productIds;
1076
1077IF OBJECT_ID('tempdb..#synProductType') IS NOT NULL
1078 DROP TABLE #synProductType;
1079CREATE TABLE #synProductType (
1080 ProductDistributorId INT NOT NULL,
1081 ProductTypeGuid VARCHAR(22) COLLATE SQL_Latin1_General_CP1_CS_AS,
1082 TypeName NVARCHAR(2000),
1083 RequiresAssessment BIT);
1084INSERT #synProductType
1085SELECT prodType.ProductDistributorId, prodType.ProductTypeGuid, prodType.TypeName, CASE WHEN config.ProductTypeGuid IS NULL THEN 0 ELSE 1 END
1086FROM [pi].[GetProductToTypeName](@productIds, @countryId, @languageId) prodType
1087LEFT JOIN pa.ProductTypeAssessmentConfiguration config WITH (NOLOCK) ON config.ProductTypeGuid = prodType.ProductTypeGuid AND config.OrgElementId = @orgElementId");
1088 if (param.ListSelectedProductTypesOnly && param.ProductTypes != null && param.ProductTypes.Count > 0)
1089 {
1090 sql.AppendFormat(@"
1091WHERE prodType.ProductTypeGuid IN ({0})", SqlQueries.MakeInClause(param.ProductTypes, true));
1092 }
1093 sql.Append(@";
1094
1095CREATE INDEX IDX_synProductType_ProductDistributorId_Inc_TypeName
1096ON #synProductType (ProductDistributorId) INCLUDE (TypeName);
1097");
1098 }
1099
1100 internal static void ExtractDocumentTypes(StringBuilder sql)
1101 {
1102 sql.Append(@"
1103/*DOC TYPES*/
1104IF OBJECT_ID('tempdb..#synDocumentsForProductsCopy') IS NOT NULL
1105 DROP TABLE #synDocumentsForProductsCopy;
1106CREATE TABLE #synDocumentsForProductsCopy (
1107 ProductDistributorId INT NOT NULL, [Type] NVARCHAR(2));
1108
1109DECLARE @filterSqlForPartitionedViewsInVegas NVARCHAR(4000) = N'
1110INSERT #synDocumentsForProductsCopy
1111SELECT DISTINCT pr.ProductDistributorID, DP.[Type]
1112FROM #synProductsCopy pr
1113INNER JOIN dbo.synDocumentsForProductsShort(' + CAST(@countryIndex AS CHAR(1)) + ') DP
1114 ON DP.ProductDistributorID=pr.ProductDistributorID
1115WHERE DP.[Type] IS NOT NULL AND DP.IsExpired=0;
1116INSERT #synDocumentsForProductsCopy
1117SELECT DISTINCT productsSyn.ProductDistributorId, spsDokumenter.Doktype
1118FROM [dbo].[synProducts](' + CAST(@countryIndex AS CHAR(1)) + ') productsSyn
1119INNER JOIN [dbo].[synSPSDokumenter] spsDokumenter
1120 ON productsSyn.ProductDistributorId = spsDokumenter.ProductDistributorId
1121WHERE spsDokumenter.DokType IS NOT NULL AND productsSyn.Expired=0;
1122';
1123EXEC sp_executesql @filterSqlForPartitionedViewsInVegas;
1124/*DOC TYPES*/
1125");
1126 }
1127
1128internal static void FilterProducts(DocProductFilterParams filter, StringBuilder sql, DocProductListType listType, bool useFullTextIndex)
1129 {
1130 sql.Append(@"
1131/*PRODUCTS FILTRATION*/");
1132
1133 string filterByName = String.Empty;
1134 string filterByArticleNumber = String.Empty;
1135 if (filter.Enabled && filter.HasSearchTerm)
1136 {
1137 if (useFullTextIndex)
1138 filterByName = @"
1139INNER JOIN #allProductsFilteredByName flt ON flt.ID=pr.ProductDistributorID ";
1140 else
1141 {
1142 filterByName = String.Format(@"
1143INNER JOIN [{0}].[dbo].[ProductDistributor] pd (NOLOCK)
1144 ON pd.ProductDistributorId = pr.ProductDistributorID
1145INNER JOIN [{0}].[dbo].[vDistributorParameter] DP (NOLOCK)
1146 ON DP.ProductDistributorId=pd.ProductDistributorID
1147 AND DP.distributor_levid=pd.supplier_levid
1148 AND DP.product_text1 LIKE @searchTerm
1149", filter.VegasDBName);
1150 }
1151 }
1152
1153 if (filter.Enabled && filter.HasArticleNumber)
1154 {
1155 filterByName = @"
1156INNER JOIN #allProductsFilteredArticleNumber flt ON flt.ID=pr.ProductDistributorID ";
1157 }
1158
1159 sql.Replace("{FilterByName}", filterByName);
1160 sql.Replace("{FilterByArticleNumber}", filterByArticleNumber);
1161
1162
1163
1164 if (filter.HasSelectedDocumentTypes || filter.HasSelectedSuppliers)
1165 {
1166 sql.Append(@"
1167DELETE pr FROM #synProductsCopy pr
1168WHERE ");
1169 }
1170 if (filter.HasSelectedDocumentTypes)
1171 {
1172 sql.Append(@"
1173pr.ProductDistributorID NOT IN (
1174 SELECT DP.ProductDistributorID
1175 FROM #synDocumentsForProductsCopy DP
1176 WHERE DP.Type IN (");
1177 sql.Append(QueriesBase.MakeInClause(filter.DocumentTypes, true));
1178 sql.Append(@"
1179 )
1180)");
1181 }
1182 if (filter.HasSelectedDocumentTypes && filter.HasSelectedSuppliers)
1183 sql.Append(@"
1184OR");
1185 if (filter.HasSelectedSuppliers)
1186 {
1187 sql.Append(@"
1188pr.Supplier_levid NOT IN (");
1189 sql.Append(QueriesBase.MakeInClause(filter.SupplierLevIDs, false));
1190 sql.Append(@"
1191)");
1192 }
1193
1194 if (filter.HasSelectedProductTypes)
1195 {
1196 sql.Append(@"
1197DELETE PR FROM #synProductsCopy PR
1198WHERE PR.ProductDistributorId NOT IN (
1199 SELECT pt.ProductDistributorId FROM #synProductType pt
1200 WHERE pt.ProductTypeGuid IN (");
1201 sql.Append(QueriesBase.MakeInClause(filter.ProductTypes, true));
1202 sql.Append(@"
1203 )
1204);");
1205 }
1206 //filter on delivered by is at ExtractProducts
1207 if (filter.HasSelectedFavourites)
1208 {
1209 string incl = filter.FilterFavourites == FilterGeneral.Yes ? "NOT" : String.Empty;
1210 sql.AppendFormat("DELETE PR FROM #synProductsCopy PR WHERE PR.ProductDistributorId {0} IN ({1});",
1211 incl,
1212 QueriesBase.MakeInClause(filter.FavouritesList, false));
1213 }
1214 if (filter.HasSelectedComment)
1215 {
1216 switch (filter.FilterComments)
1217 {
1218 case FilterGeneral.Yes:
1219 sql.Append(@"
1220DELETE PR FROM #synProductsCopy PR
1221WHERE PR.ProductDistributorId NOT IN
1222(SELECT ProductDistributorId FROM [pi].[GetProductCommentIds](@allProductIds, @rootWorkplaceId, @orgElementId)
1223");
1224 if (filter.CommentCount > 0)
1225 sql.AppendFormat("GROUP BY ProductDistributorId HAVING COUNT(DISTINCT CommentId)={0});", filter.CommentCount);
1226 else
1227 sql.Append(");");
1228 break;
1229 case FilterGeneral.No:
1230 sql.Append(@"
1231DELETE PR FROM #synProductsCopy PR
1232WHERE PR.ProductDistributorId IN
1233(SELECT ProductDistributorId FROM [pi].[GetProductCommentIds](@allProductIds, @rootWorkplaceId, @orgElementId));
1234");
1235 break;
1236 }
1237 }
1238 sql.Append(@"
1239/*PRODUCTS FILTRATION*/
1240");
1241 }
1242
1243internal static void ExtractFilters(StringBuilder sql)
1244 {
1245 sql.Append(@"
1246/*FILTER FILLS*/
1247SELECT DISTINCT Supplier_levid, SupplierName FROM #synProductsCopy ORDER BY SupplierName;
1248
1249SELECT DISTINCT ProductTypeGuid, TypeName FROM #synProductType PT INNER JOIN #synProductsCopy PR ON PT.ProductDistributorId=PR.ProductDistributorId ORDER BY TypeName;
1250
1251SELECT DISTINCT DP.[Type] FROM #synDocumentsForProductsCopy DP INNER JOIN #synProductsCopy PT ON PT.ProductDistributorId=DP.ProductDistributorId;
1252/*FILTER FILLS*/
1253");
1254 }
1255
1256internal static void ExtractDeliveredBy(StringBuilder sql)
1257 {
1258 sql.Append(@"
1259SELECT DISTINCT
1260 DisplayName, IsImported, MIN(CAST(IsArchived as INT)) [IsArchived],
1261 STUFF((
1262 (SELECT DISTINCT ', ' + CAST(WP.ID as VARCHAR)
1263 FROM #deliveredByData WP (NOLOCK)
1264 WHERE WP.DisplayName = res.DisplayName
1265 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
1266 ),1, 2,'') [IDs]
1267FROM #deliveredByData res
1268WHERE NOT EXISTS (SELECT NULL FROM #productIds) OR res.ProductDistributorID IN (SELECT i.ID FROM #productIds i)
1269GROUP BY DisplayName, IsImported
1270ORDER BY DisplayName;
1271/*FILTER FILLS*/
1272");
1273 }
1274
1275 internal static void SetPSPFlag(StringBuilder sql)
1276 {
1277 sql.AppendFormat(@"
1278UPDATE @result SET
1279res.IsPSP = (
1280 CASE WHEN EXISTS (SELECT 1 FROM dbo.synSpecificProducts sp
1281 WHERE sp.ProductDistributorId = res.ProductDistributorId) then 1 ELSE 0 END
1282)
1283FROM @result res;
1284 ");
1285 }
1286
1287 internal static void SetDocumentCounts(StringBuilder sql, DocProductListType listType)
1288 {
1289 sql.AppendFormat(@"
1290/*DOCs COUNT*/
1291UPDATE res SET
1292res.DocumentsCount = (
1293 SELECT COUNT(DP.KortID)
1294 FROM dbo.synDocumentsForProducts(@countryIndex) DP
1295 WHERE DP.ProductDistributorID=res.ProductDistributorID
1296 AND (DP.IsExpired = 0 OR EXISTS(SELECT NULL FROM sds.SDS sds where sds.MSDSId = DP.msdsid and sds.SDSStatusId = 3)))
1297FROM @result res WHERE IsPSP = 0;
1298
1299UPDATE res SET
1300res.DocumentsCount = (
1301 SELECT COUNT(DocumentId) FROM dbo.synSPSDokumenter spsD
1302 WHERE spsD.countryIndex = @countryIndex
1303 AND spsD.ProductDistributorId = res.ProductDistributorID
1304 AND spsd.DeletedOn IS NULL
1305 {1})
1306FROM @result res WHERE IsPSP = 1;
1307/*DOCs COUNT*/", listType == DocProductListType.DistributeSDS ? "AND DP.Path LIKE '%.pdf'" : String.Empty,
1308 listType == DocProductListType.DistributeSDS ? "AND spsD.Path LIKE '%.pdf'" : String.Empty);
1309 }
1310
1311internal static void ExtractProductComments(StringBuilder sql, bool addCommentText = false)
1312 {
1313 sql.Append(@"
1314/*product COMMENTS*/
1315");
1316 // bool addCommentText = (listType == DocProductListType.ExportProductListToExcel);
1317 string qwrComments = string.Format(@"
1318DECLARE @comments TABLE (
1319 ProductDistributorId INT,
1320 CommentId INT,
1321 WorkplaceSpecific BIT
1322 {0});
1323INSERT @comments(
1324 ProductDistributorId,
1325 CommentId,
1326 WorkplaceSpecific
1327 {1})
1328SELECT
1329 i.ProductDistributorId,
1330 i.CommentId,
1331 i.WorkplaceSpecific
1332 {2}
1333FROM pi.GetProductCommentIds(@productIdsToBeShown, @rootWorkplaceId, @orgElementId) AS i
1334 {3} ; ", addCommentText ? @" , CommentText nvarchar(2000) " : String.Empty
1335 , addCommentText ? @" , CommentText " : String.Empty
1336 , addCommentText ? @" , CASE WHEN c.WorkplaceId IS NOT NULL AND c.InWorkPlace=1 THEN c.[Comment] ELSE NULL END as CommentText " : String.Empty
1337 , addCommentText ? @" INNER JOIN pi.ProductComments AS c (NOLOCK) ON i.CommentId = c.ID " : String.Empty);
1338 sql.AppendFormat(@"{0}
1339/*product COMMENTS*/
1340 ", qwrComments);
1341 }
1342
1343internal static void ExtractProductFlags(StringBuilder sql)
1344 {
1345 sql.Append(@"
1346/*PRODUCT FLAGS*/
1347DECLARE @flags TABLE (
1348 ProductDistributorId INT NOT NULL PRIMARY KEY,
1349 HasDoP BIT NULL,
1350 HasAssemblyInstruction BIT NULL,
1351 Breeam BIT NULL,
1352 Epd BIT NULL,
1353 Sintef BIT NULL,
1354 Svanen BIT NULL,
1355 HasSDS BIT NULL,
1356 EcoLabel BIT NULL,
1357 HasTechSpecification BIT NULL,
1358 IsNonCompliantWithRules BIT NOT NULL,
1359 HarmonizedStandards NVARCHAR(4000) NULL,
1360 IsBlackProduct BIT NULL);
1361INSERT @flags
1362SELECT
1363 ProductDistributorId,
1364 HasDoP,
1365 HasAssemblyInstruction,
1366 Breeam,
1367 Epd,
1368 Sintef,
1369 Svanen,
1370 HasSDS,
1371 EcoLabel,
1372 NULL AS HasTechSpecification,
1373 IsNonCompliantWithRules,
1374 '' AS HarmonizedStandards,
1375 IsBlackProduct
1376FROM pi.GetProductsFlags_fn(@productIdsToBeShown, @countryId, @ecoRuleId);
1377/*PRODUCT FLAGS*/
1378");
1379 }
1380
1381internal static void ExtractPNCDetails(StringBuilder sql)
1382 {
1383 var breeam2016ChemicalListId = 25;
1384 var breeam2012hea9Id = 4;
1385 var breeam2012a20Id = 5;
1386 var breeam2016hea02Id = 13;
1387 var breeam2016a20Id = 14;
1388
1389 sql.Append(string.Format(@"
1390/*PNC DETAILS*/
1391DECLARE @breeam2016ChemicalListId INT = {0};
1392DECLARE @isBreeam2016 BIT =
1393 CASE
1394 WHEN @breeam2016ChemicalListId = ANY (SELECT DISTINCT clf.ChemicalListId FROM [pi].[ChemicalListFilter] AS clf WHERE clf.WorkplaceId = @rootWorkplaceId) THEN 1
1395 ELSE 0
1396 END
1397DECLARE @compliance TABLE(
1398 ProductDistributorId INT NOT NULL PRIMARY KEY,
1399 NonCompliantWith VARCHAR(20) NULL)
1400INSERT @compliance (ProductDistributorId, NonCompliantWith)
1401SELECT wp.ID,
1402 STUFF(((
1403 SELECT ',' + cast(ID AS VARCHAR(5))
1404 FROM pi.GetProductComplianceDetails(@countryId,@productIdsToBeShown,@includeRulesNotRequiredByLegislation) c
1405 WHERE c.ProductDistributorID=wp.ID AND ID <> CASE WHEN @isBreeam2016 = 1 THEN {1} ELSE {2} END AND ID <> CASE WHEN @isBreeam2016 = 1 THEN {3} ELSE {4} END
1406 FOR XML PATH(''), TYPE).value('.', 'VARCHAR(20)')
1407 ),1,1,'') AS NonCompliantWith
1408FROM @productIdsToBeShown wp;
1409/*PNC DETAILS*/
1410", breeam2016ChemicalListId, breeam2012hea9Id, breeam2016hea02Id, breeam2012a20Id, breeam2016a20Id));
1411 }
1412
1413internal static void ExtractEcoRuleCompliance(StringBuilder sql)
1414 {
1415 sql.Append(@"
1416/*ECO RULE Compliance*/
1417DECLARE @ecoCompliance TABLE(
1418 ProductDistributorId INT NOT NULL PRIMARY KEY,
1419 NonCompliantWithEco VARCHAR(300) NULL)
1420INSERT @ecoCompliance SELECT
1421 wp.ID,
1422 STUFF(((
1423 SELECT ',' + EcoRuleID
1424 FROM pi.GetProductEcoComplianceDetails(@countryId, @productIdsToBeShown, @targetFilterWorkplaceIds) c
1425 WHERE c.ProductDistributorID=wp.ID
1426 FOR XML PATH(''), TYPE).value('.', 'VARCHAR(300)')
1427 ), 1, 1, '') AS NonCompliantWithEco
1428FROM @productIdsToBeShown wp;
1429/*ECO RULE Compliance*/
1430");
1431 }
1432
1433internal static void ExtractProductFilterCompliance(StringBuilder sql)
1434 {
1435 sql.Append(@"
1436/*Product Filter Compliance*/
1437DECLARE @productsWithFilterCriteria TABLE (
1438 ProductDistributorId INT NOT NULL PRIMARY KEY, ProductFilterComplianceStateID INT);
1439INSERT @productsWithFilterCriteria SELECT
1440 c.ProductDistributorID, c.StateID
1441FROM pi.GetProductsNonCompliantToProductTypeFilterDetails(@countryId, @productIdsToBeShown, @targetFilterWorkplaceIds) c;
1442/*Product Filter Compliance*/
1443");
1444 }