1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
| CREATE PROCEDURE Select_scans_product
@product_barcode_product nvarchar(50) = NULL
, @product_variety_name_product nvarchar(100) = NULL
, @product_range_name_product nvarchar(100) = NULL
, @product_comment_product nvarchar(500) = NULL
, @product_is_visible_product bit = NULL
, @creation_origin_product nvarchar(50) = NULL
, @is_non_alimentaire_product bit = NULL
, @view_1_quantite int = NULL
, @view_1_quantite_order nvarchar = NULL
, @product_barcode_product_order nvarchar = NULL
, @product_calories_product real = NULL
, @product_glucides_product real = NULL
, @product_lipides_product real = NULL
, @product_protides_product real = NULL
, @product_ingredient_product varchar(max) = NULL
, @perimetre_user nvarchar = NULL
, @creation_date_product_order datetime = NULL
, @quantite_scans_order nvarchar = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @var_perimetre_user nvarchar
, @sql nvarchar(max)
, @params nvarchar(max)
, @session_id uniqueidentifier
SELECT @var_perimetre_user = @perimetre_user
, @session_id = NEWSEQUENTIALID()
IF @var_perimetre_user IS NOT NULL
BEGIN
INSERT INTO dbo.temp_valeurs
(
session_id
, valeur
)
SELECT @session_id
, val
FROM dbo.split(@var_perimetre_user, ',')
END
SET @sql = N'
SELECT P.pk_product
, P.barcode_product
, P.variety_name_product
, P.range_name_product
, P.creation_date_product
, P.comment_product
, P.is_visible_product
, P.image_product
, P.ingredients_product
, P.calories_product
, P.glucides_product
, P.lipides_product
, P.protides_product
, P.is_non_alimentaire_product
, Family_NameFr
, Family_NameUs
, SubFam_NameFr
, SubFam_NameUs
, creation_origin_product
, V.quantite
, VQS.quantite_scans
FROM dbo.product AS P
LEFT JOIN (
SELECT barcode_product
, COUNT(*) AS quantite
FROM dbo.product
WHERE COALESCE (is_deleted_product, 0) = 0
GROUP BY barcode_product
) AS V
ON P.barcode_product = V.barcode_product
LEFT JOIN dbo.view_quantite_scans AS VQS
ON P.barcode_product = VQS.barcode_product
LEFT JOIN dbo.XTC_tSubFamily AS SF
ON P.fk_sub_family_product = SF.SubFamilyId
LEFT JOIN dbo.XTC_tFamily AS F
ON SF.SubFam_FamilyId = F.FamilyId
WHERE COALESCE(P.is_deleted_product,0) = 0'
SELECT @sql = @sql
+ CASE WHEN @product_barcode_product IS NULL THEN '' ELSE ' AND P.barcode_product LIKE @product_barcode_product' END
+ CASE WHEN @product_variety_name_product IS NULL THEN '' ELSE ' AND (LOWER(P.variety_name_product) LIKE @product_variety_name_product' END
+ CASE WHEN @product_range_name_product IS NULL THEN '' ELSE ' AND LOWER(P.range_name_product) LIKE @product_range_name_product)' END
+ CASE WHEN @product_comment_product IS NULL THEN '' ELSE ' AND LOWER(P.comment_product) LIKE @product_comment_product' END
+ CASE WHEN @product_is_visible_product IS NULL THEN '' ELSE ' AND P.is_visible_product = @product_is_visible_product' END
+ CASE WHEN @creation_origin_product IS NULL THEN '' ELSE ' AND creation_origin_product = @creation_origin_product' END
+ CASE WHEN @is_non_alimentaire_product IS NULL THEN '' ELSE ' AND COALESCE(is_non_alimentaire_product,0) = @is_non_alimentaire_product' END
+ CASE WHEN @product_calories_product IS NULL THEN '' ELSE ' AND P.calories_product = @product_calories_product' END
+ CASE WHEN @product_calories_product IS NULL THEN '' ELSE ' AND P.calories_product = @product_calories_product' END
+ CASE WHEN @product_glucides_product IS NULL THEN '' ELSE ' AND P.glucides_product = @product_glucides_product' END
+ CASE WHEN @product_lipides_product IS NULL THEN '' ELSE ' AND P.lipides_product = @product_lipides_product' END
+ CASE WHEN @product_protides_product IS NULL THEN '' ELSE ' AND P.protides_product = @product_protides_product' END
+ CASE WHEN @product_ingredient_product IS NULL THEN '' ELSE ' AND P.ingredients_product LIKE @product_ingredient_product' END
+ CASE WHEN @var_perimetre_user IS NULL THEN '' ELSE ' OR uneColonne IN (SELECT val FROM dbo.temp_valeurs WHERE session_id = @session_id)' END
+ ' ORDER BY '
+ CASE
WHEN @view_1_quantite_order IS NOT NULL THEN 'V.quantite'
WHEN @product_barcode_product_order IS NOT NULL THEN 'P.barcode_product'
WHEN @creation_date_product_order IS NOT NULL THEN 'creation_date_product'
WHEN @quantite_scans_order IS NOT NULL THEN 'VQS.quantite_scans'
END + 'DESC'
SELECT @params = '@product_barcode_product nvarchar(50)'
+ ', @product_variety_name_product nvarchar(100)'
+ ', @product_range_name_product nvarchar(100)'
+ ', @product_comment_product nvarchar(500)'
+ ', @product_is_visible_product bit'
+ ', @creation_origin_product nvarchar(50)'
+ ', @is_non_alimentaire_product bit'
+ ', @product_calories_product real'
+ ', @product_glucides_product real'
+ ', @product_lipides_product real'
+ ', @product_protides_product real'
+ ', @product_ingredient_product varchar(max)'
+ ', @session_id uniqueidentifier'
EXEC sp_executesql
@sql
, @params
, @product_barcode_product = @product_barcode_product
, @product_range_name_product = @product_range_name_product
, @product_comment_product = @product_comment_product
, @product_is_visible_product = @product_is_visible_product
, @creation_origin_product = @creation_origin_product
, @is_non_alimentaire_product = @is_non_alimentaire_product
, @product_calories_product = @product_calories_product
, @product_glucides_product = @product_glucides_product
, @product_lipides_product = @product_lipides_product
, @product_protides_product = @product_protides_product
, @session_id = @session_id
IF @var_perimetre_user IS NOT NULL
BEGIN
DELETE FROM dbo.temp_valeurs
WHERE session_id = @session_id
END
END
GO |
