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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
|
USE [mabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[mg_getPassagers]
(
@comm_numeroCommande VARCHAR(30),
@comm_dateDebutResa INT,
@comm_dateFinResa INT,
@comm_dateDebutDepart INT,
@comm_dateFinDepart INT,
@comm_client INT,
@comm_typeCommande INT,
@comm_statutresa INT,
@comm_statutpaiement INT,
@comm_statuttraitement INT,
@comm_statutfacturation BIT,
@prest_typePrestation INT,
@prest_statutresa INT,
@prest_fournisseur INT,
@paysDestination INT,
@codeAnalytique VARCHAR(30) = '',
@idApporteurAffaire INT = NULL ,
@prest_supplement INT = 0,
@idGroupProduit INT = 0,
@user INT = 0,
@prest_dateDebutPrest INT = NULL,
@prest_dateFinPrest INT = NULL,
@idCategoriePrestation INT = 0,
@codeProduit VARCHAR(100) = '',
@listeProduits VARCHAR(400)='',
@listeOffres VARCHAR(400)='',
@listeTarifsAdultes VARCHAR(400)='',
@listeTarifsEnfants VARCHAR(400)=''
)
AS
BEGIN
DECLARE @psgListTmp TABLE (id INT, civilite VARCHAR(10), libCivilite VARCHAR(100), commentaire VARCHAR(2000),datenaissance INT,groupe VARCHAR(100), nom VARCHAR(30), prenom VARCHAR(30), typePax INT
, numeroPiece VARCHAR(30), telephone VARCHAR(50), dateFinValidite INT, idCommande INT, nomProduit VARCHAR(400), numeroCommande VARCHAR(30), referenceExterne VARCHAR(30)
, dateDepart VARCHAR(30),dateDepartInt INT, statutConfirmation INT, categorie VARCHAR(200), dateCreation DATETIME, codeAnalytique VARCHAR(30), raisonSociale VARCHAR(100)
, apporteurAffaire VARCHAR(100), statutTraitement varchar(100), activite VARCHAR(400), typeLogement VARCHAR(400)
, nomAgentClient VARCHAR(100), codeApporteurAffaire VARCHAR(100)
, codeProduit VARCHAR(100)
, prixParPersonne NUMERIC(18,3)
, offreExiste BIT, tarifAdulteExiste BIT, tarifEnfantExiste BIT)
DECLARE @psgListCategorie TABLE (idCategorie INT, typeProduit INT, nomCategorie VARCHAR(400), idPassager INT, activite VARCHAR(100), typeLogement VARCHAR(400))
DECLARE @tmpValeur VARCHAR(30)
DECLARE @cmdTmpOffre TABLE(idCommande INT)
DECLARE @cmdTarifAdulte TABLE(idCommande INT)
DECLARE @cmdTarifEnfant TABLE(idCommande INT)
DECLARE @countTableOffre BIT, @countTableAdulte BIT, @countTableEnfant BIT
SET @countTableOffre = 0
SET @countTableAdulte = 0
SET @countTableEnfant = 0
DECLARE @cmdTmp TABLE (id INT, numero VARCHAR(50), client INT, idApporteurAffaire INT, statuTraitement INT, nomProduit VARCHAR(200), referenceExterne VARCHAR(100), dateDepart INT, dateCreation DATETIME, codeAnalytique VARCHAR(50), nomAgentClient VARCHAR(100), codeApporteurAffaire VARCHAR(100), codeProduit VARCHAR(100))
INSERT INTO @cmdTmp (id, numero, client, idApporteurAffaire, statuTraitement, nomProduit, referenceExterne, dateDepart, dateCreation, codeAnalytique, nomAgentClient, codeApporteurAffaire, codeProduit)
SELECT cmde.id, cmde.numero, cmde.client, cmde.idApporteurAffaire, cmde.statuTraitement, cmde.nomProduit, cmde.referenceExterne, cmde.dateDepart, cmde.dateCreation, cmde.codeAnalytique, cmde.nomAgentClient, cmde.codeApporteurAffaire, cmde.codeProduit
FROM commande cmde WITH(NOLOCK)
WHERE
(@comm_numeroCommande = '' OR ( @comm_numeroCommande <> '' AND cmde.numero LIKE '%' + LTRIM(RTRIM(@comm_numeroCommande)) + '%'))
AND (@comm_dateFinResa IS NULL OR (cmde.dateCreation < DATEADD("D", 1, dbo.inttodate(@comm_dateFinResa)) AND @comm_dateFinResa IS NOT NULL))
AND (@comm_dateDebutResa IS NULL OR (cmde.dateCreation >= dbo.inttodate(@comm_dateDebutResa) AND @comm_dateDebutResa IS NOT NULL))
AND (@comm_dateFinDepart IS NULL OR (cmde.dateDepart <= @comm_dateFinDepart AND @comm_dateFinDepart IS NOT NULL))
AND (@comm_dateDebutDepart IS NULL OR (cmde.dateDepart >= @comm_dateDebutDepart AND @comm_dateDebutDepart IS NOT NULL))
AND (@comm_typeCommande = 0 OR (@comm_typeCommande <> 0 AND cmde.type = @comm_typeCommande))
AND (@comm_statutresa = 0 OR (@comm_statutresa <> 0 AND cmde.statuReservation = @comm_statutresa))
AND (@comm_statutpaiement = 0 OR (@comm_statutpaiement <> 0 AND cmde.statuPayement = @comm_statutpaiement))
AND (@comm_statuttraitement = 0 OR (@comm_statuttraitement <> 0 AND cmde.statuTraitement = @comm_statuttraitement))
AND (@comm_statutfacturation IS NULL OR (statuFacturation = @comm_statutfacturation AND @comm_statutfacturation IS NOT NULL))
AND (@paysDestination IS NULL OR (cmde.idpaysDestination = @paysDestination AND @paysDestination IS NOT NULL))
AND (@codeAnalytique = '' OR ( @codeAnalytique <> '' AND cmde.codeAnalytique LIKE '%' +LTRIM(RTRIM(@codeAnalytique))+ '%' ))
AND (COALESCE(@codeProduit, '') = '' OR ( @codeProduit <> '' AND cmde.codeProduit LIKE '%' +LTRIM(RTRIM(@codeProduit))+ '%' ))
AND (@idApporteurAffaire is NULL OR (@idApporteurAffaire is not null AND cmde.idApporteurAffaire = @idApporteurAffaire))
AND (COALESCE(@user, 0) = 0 OR cmde.[USER] = @user)
INSERT INTO @psgListTmp (id, civilite, libCivilite, commentaire,datenaissance,groupe, nom, prenom, typePax, numeroPiece, telephone, dateFinValidite, idCommande, nomProduit, numeroCommande, referenceExterne, dateDepart,dateDepartInt, statutConfirmation, dateCreation, codeAnalytique, raisonSociale, apporteurAffaire, statutTraitement, nomAgentClient, codeApporteurAffaire, codeProduit)
SELECT DISTINCT p.id AS id
,p.civilite AS civilite
,civ.valeur AS libCivilite
,p.commentaire AS commentaire
,p.dateNaissance AS datenaissance
,p.groupePassager AS groupe
,p.nom AS nom
,p.prenom AS prenom
,p.typePax AS typePax
,p.numeroPieceIdentite numeroPiece
,p.telephone
,COALESCE(p.dateFinValidite,0)
,commandeprestation.idCommande
,cmd.nomProduit AS nomProduit
,cmd.numero AS numeroCommande
,cmd.referenceExterne AS referenceExterne
,( CONVERT (VARCHAR(30) ,dbo.intToDate(cmd.dateDepart), 103)) AS dateDepart
,cmd.dateDepart AS dateDepartInt
,commandeprestation.statutConfirmation
,cmd.dateCreation AS dateCreation
,cmd.codeAnalytique AS codeAnalytique
,client.raisonSociale AS raisonSociale
, Coalesce (apporteurAffaire.raisonSociale,'') AS apporteurAffaire
,ValTraitement.valeur
, cmd.nomAgentClient
, cmd.codeApporteurAffaire
, cmd.codeProduit
FROM @cmdTmp cmd INNER JOIN commandePassager p WITH(NOLOCK) ON (cmd.id = p.idCommande)
LEFT JOIN client WITH(NOLOCK) ON client.id = cmd.client
LEFT JOIN client AS apporteurAffaire WITH(NOLOCK) ON apporteurAffaire.id = cmd.idApporteurAffaire
LEFT JOIN commandeprestation WITH(NOLOCK) ON commandeprestation.idCommande = cmd.id
LEFT JOIN produit WITH(NOLOCK) ON produit.id =commandePrestation.idProduit
LEFT JOIN fournisseur WITH(NOLOCK) ON fournisseur.id =produit.idFournisseur
LEFT JOIN produitPaysDestination pPD WITH(NOLOCK) ON pPD.idProduit=commandePrestation.idProduit
LEFT JOIN masterParametreValeur ValTraitement WITH(NOLOCK) ON cmd.statuTraitement=ValTraitement.id
LEFT JOIN masterParametreValeur civ WITH(NOLOCK) ON p.civilite = civ.id
WHERE
(
(@prest_typePrestation = 0)
OR
(commandeprestation.typeProduit = @prest_typePrestation AND @prest_typePrestation <> 0)
)
AND (COALESCE(@prest_supplement, 0) = 0 OR (@prest_supplement <> 0 AND commandePrestation.idSupplement = @prest_supplement))
AND (@comm_client = 0 OR (client.id = @comm_client AND @comm_client > 0) OR (@comm_client = -1 AND client.typeClient=12))
AND (@prest_statutresa = 0 OR (commandeprestation.statutConfirmation = @prest_statutresa AND @prest_statutresa <> 0))
AND (@prest_fournisseur = 0 OR (fournisseur.id = @prest_fournisseur AND @prest_fournisseur <> 0))
AND (COALESCE(@idGroupProduit,0) = 0 OR (@idGroupProduit <> 0 AND produit.groupe = @idGroupProduit))
AND (@prest_dateDebutPrest IS NULL
OR (@prest_dateDebutPrest IS NOT NULL AND @prest_dateDebutPrest <= commandeprestation.[date])
)
AND (@prest_dateFinPrest IS NULL
OR (@prest_dateFinPrest IS NOT NULL AND @prest_dateFinPrest >= commandeprestation.[date])
)
AND (COALESCE(@idCategoriePrestation, 0) = 0 OR commandeprestation.categorie = @idCategoriePrestation)
AND (
COALESCE(@listeProduits, '') = ''
OR
(
CommandePrestation.idProduit IN (SELECT number FROM dbo.varcharToList(@listeProduits))
)
)
ORDER BY dateDepartInt
INSERT INTO @psgListCategorie
SELECT commandePrestation.categorie, commandePrestation.typeProduit, NULL, tableTmp.id, NULL,NULL
FROM @psgListTmp tableTmp
INNER JOIN commandePassager WITH(NOLOCK) ON commandePassager.id = tableTmp.id AND commandePassager.idCommande = tableTmp.idCommande
INNER JOIN commandePresationConsommee WITH(NOLOCK) ON commandePresationConsommee.idPax = commandePassager.id
INNER JOIN commandePrestation WITH(NOLOCK) ON commandePrestation.id = commandePresationConsommee.idPrestation
WHERE
COALESCE(commandePrestation.categorie,0) <> 0
AND commandePrestation.typeProduit IN (29,30)
UPDATE tmpCategorie
SET tmpCategorie.nomCategorie = typeLogement.nom,
tmpCategorie.typeLogement = typeLogement.nom
FROM @psgListCategorie tmpCategorie
INNER JOIN typeLogement ON typeLogement.id = tmpCategorie.idCategorie
WHERE tmpCategorie.typeProduit = 29
UPDATE tmpCategorie
SET tmpCategorie.nomCategorie = categorie.nom,
tmpCategorie.activite = categorie.nom
FROM @psgListCategorie tmpCategorie
INNER JOIN categorie ON categorie.id = tmpCategorie.idCategorie
WHERE tmpCategorie.typeProduit = 30
UPDATE tableTmp
SET tableTmp.categorie = (SELECT STUFF((SELECT DISTINCT ' , ' + nomCategorie
FROM @psgListCategorie
WHERE idPassager = tab.idPassager
FOR XML PATH ('')),1,2,'') AS categorie
FROM @psgListCategorie tab
WHERE tab.idPassager = tableTmp.id
GROUP BY idPassager
)
FROM @psgListTmp tableTmp
UPDATE tableTmp
SET tableTmp.typeLogement = (SELECT STUFF((SELECT DISTINCT ' , ' + typeLogement
FROM @psgListCategorie
WHERE idPassager = tab.idPassager
FOR XML PATH ('')),1,2,'') AS categorie
FROM @psgListCategorie tab
WHERE tab.idPassager = tableTmp.id
GROUP BY idPassager
)
FROM @psgListTmp tableTmp
UPDATE tableTmp
SET tableTmp.activite = (SELECT STUFF((SELECT DISTINCT ' , ' + activite
FROM @psgListCategorie
WHERE idPassager = tab.idPassager
FOR XML PATH ('')),1,2,'') AS categorie
FROM @psgListCategorie tab
WHERE tab.idPassager = tableTmp.id
GROUP BY idPassager
)
FROM @psgListTmp tableTmp
UPDATE tableTmp
SET tableTmp.prixParPersonne = tarif.prixParPersonne
FROM @psgListTmp tableTmp
CROSS APPLY (
SELECT SUM(commandeTarifVente.prixUnitaire) AS prixParPersonne
FROM commandeTarifVente
WHERE commandeTarifVente.identifiantCommande = tableTmp.idCommande
AND
(
(
commandeTarifVente.codeTarif <> 29
AND
(
(tableTmp.typePax = 64 AND commandeTarifVente.codeTarif = 30)
OR
(tableTmp.typePax = 65 AND commandeTarifVente.codeTarif = 79)
OR
(tableTmp.typePax = 66 AND commandeTarifVente.codeTarif = 80)
)
)
OR
(
commandeTarifVente.codeTarif = 29
)
)
) AS tarif
-- Offre
IF(COALESCE(@listeOffres, '') <> '')
BEGIN
SET @countTableOffre = 1
DECLARE curseur_liste CURSOR FOR
SELECT (CASE WHEN number = 10 THEN 'E7' WHEN number = 15 THEN 'E6' WHEN number = 20 THEN 'E5' WHEN number = 0 THEN 'PT' END ) FROM dbo.varcharToList(@listeOffres)
OPEN curseur_liste FETCH curseur_liste INTO @tmpValeur
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@tmpValeur = 'PT')
BEGIN
INSERT INTO @cmdTmpOffre
SELECT DISTINCT t.idCommande
FROM @psgListTmp t
WHERE COALESCE(t.codeProduit, '') NOT LIKE '%E5%'
AND COALESCE(t.codeProduit, '') NOT LIKE '%E6%'
AND COALESCE(t.codeProduit, '') NOT LIKE '%E7%'
END
ELSE
BEGIN
INSERT INTO @cmdTmpOffre
SELECT DISTINCT t.idCommande
FROM @psgListTmp t
WHERE COALESCE(t.codeProduit, '') LIKE CONCAT('%', @tmpValeur,'%')
END
FETCH curseur_liste INTO @tmpValeur
END
CLOSE curseur_liste
DEALLOCATE curseur_liste
/*
DELETE FROM t1
FROM @psgListTmp t1
WHERE t1.idcommande NOT IN (SELECT idCommande FROM @cmdTmpOffre)
DELETE FROM @cmdTmpOffre
*/
END
-- Tarif Adulte
IF(COALESCE(@listeTarifsAdultes, '') <> '')
BEGIN
SET @countTableAdulte = 1
DECLARE @Current VARCHAR(30)
SET @Current = NULL
WHILE (LEN(@listeTarifsAdultes) > 0)
BEGIN
IF (Charindex(',', @listeTarifsAdultes) > 0)
BEGIN
SET @Current = LEFT(@listeTarifsAdultes, Charindex(',', @listeTarifsAdultes) - 1)
SET @listeTarifsAdultes = RIGHT(@listeTarifsAdultes, LEN(@listeTarifsAdultes) - Charindex(',', @listeTarifsAdultes))
END
ELSE
BEGIN
SET @Current = @listeTarifsAdultes
SET @listeTarifsAdultes = NULL
END
INSERT INTO @cmdTarifAdulte
SELECT DISTINCT t.idCommande
FROM @psgListTmp t INNER JOIN commandeTarifVente WITH(NOLOCK) ON t.idCommande = commandeTarifVente.identifiantCommande
WHERE commandeTarifVente.prixUnitaire = CONVERT(NUMERIC(18,3), @Current)
AND commandeTarifVente.codeTarif = 30
SET @Current = NULL
END
END
-- Tarif Enfant
IF(COALESCE(@listeTarifsEnfants, '') <> '')
BEGIN
SET @countTableEnfant = 1
SET @Current = NULL
WHILE (LEN(@listeTarifsEnfants) > 0)
BEGIN
IF (Charindex(',', @listeTarifsEnfants) > 0)
BEGIN
SET @Current = LEFT(@listeTarifsEnfants, Charindex(',', @listeTarifsEnfants) - 1)
SET @listeTarifsEnfants = RIGHT(@listeTarifsEnfants, LEN(@listeTarifsEnfants) - Charindex(',', @listeTarifsEnfants))
END
ELSE
BEGIN
SET @Current = @listeTarifsEnfants
SET @listeTarifsEnfants = NULL
END
INSERT INTO @cmdTarifEnfant
SELECT DISTINCT t.idCommande
FROM @psgListTmp t INNER JOIN commandeTarifVente WITH(NOLOCK) ON t.idCommande = commandeTarifVente.identifiantCommande
WHERE commandeTarifVente.prixUnitaire = CONVERT(NUMERIC(18,3), @Current)
AND commandeTarifVente.codeTarif = 79
SET @Current = NULL
END
END
--SELECT @countTableOffre, @countTableAdulte, @countTableEnfant
UPDATE t1
SET t1.offreExiste = 1
FROM @psgListTmp t1
INNER JOIN @cmdTmpOffre t2 ON t1.idCommande = t2.idCommande
UPDATE t1
SET t1.tarifAdulteExiste = 1
FROM @psgListTmp t1
INNER JOIN @cmdTarifAdulte t2 ON t1.idCommande = t2.idCommande
UPDATE t1
SET t1.tarifEnfantExiste = 1
FROM @psgListTmp t1
INNER JOIN @cmdTarifEnfant t2 ON t1.idCommande = t2.idCommande
SELECT id
, civilite
, libCivilite
, commentaire
, datenaissance
, groupe
, nom
, prenom
, typePax
, numeroPiece
, telephone
, dateFinValidite
, idCommande
, nomProduit
, numeroCommande
, referenceExterne
, dateDepart
, dateDepartInt
, statutConfirmation
, dateCreation
, codeAnalytique
, raisonSociale
, apporteurAffaire
, categorie
, statutTraitement
, typeLogement
, activite
, nomAgentClient
, codeProduit
, codeApporteurAffaire
, prixParPersonne
FROM @psgListTmp t1
WHERE
(COALESCE(@countTableOffre, 0) = 0 AND COALESCE(@countTableAdulte, 0) = 0 AND COALESCE(@countTableEnfant, 0) = 0)
OR
(COALESCE(@countTableOffre, 0) = 0 AND COALESCE(@countTableAdulte, 0) = 0 AND COALESCE(@countTableEnfant, 0) = 1 AND COALESCE(t1.tarifEnfantExiste, 0) = 1 )
OR
(COALESCE(@countTableOffre, 0) = 0 AND COALESCE(@countTableAdulte, 0) = 1 AND COALESCE(@countTableEnfant, 0) = 0 AND COALESCE(t1.tarifAdulteExiste, 0) = 1)
OR
(COALESCE(@countTableOffre, 0) = 0 AND COALESCE(@countTableAdulte, 0) = 1 AND COALESCE(@countTableEnfant, 0) = 1 AND COALESCE(t1.tarifAdulteExiste, 0) = 1 AND COALESCE(t1.tarifEnfantExiste, 0) = 1 )
OR
(COALESCE(@countTableOffre, 0) = 1 AND COALESCE(@countTableAdulte, 0) = 0 AND COALESCE(@countTableEnfant, 0) = 0 AND COALESCE(t1.offreExiste, 0) = 1)
OR
(COALESCE(@countTableOffre, 0) = 1 AND COALESCE(@countTableAdulte, 0) = 0 AND COALESCE(@countTableEnfant, 0) = 1 AND COALESCE(t1.offreExiste, 0) = 1 AND COALESCE(t1.tarifEnfantExiste, 0) = 1)
OR
(COALESCE(@countTableOffre, 0) = 1 AND COALESCE(@countTableAdulte, 0) = 1 AND COALESCE(@countTableEnfant, 0) = 0 AND COALESCE(t1.offreExiste, 0) = 1 AND COALESCE(t1.tarifAdulteExiste, 0) = 1)
OR
(COALESCE(@countTableOffre, 0) = 1 AND COALESCE(@countTableAdulte, 0) = 1 AND COALESCE(@countTableEnfant, 0) = 1 AND COALESCE(t1.offreExiste, 0) = 1 AND COALESCE(t1.tarifAdulteExiste, 0) = 1 AND COALESCE(t1.tarifEnfantExiste, 0) = 1)
ORDER BY dateDepartInt
END |
Optimisation d'un Procédure Stockée
Répondre avec citation
Partager