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
| CREATE PROCEDURE [dbo].[sto_rptListeClientNonRenouveller]
(
@idFranchise int, --OBL identification de la franchise
@anneeCourante int, --OBL année de contrat du traitement
@ordreTri nvarchar(10), --telephone, nom, secteur
@contraAnnulerInclu bit --1=oui, 0=non, null=nonInclus Si on inclu les contras annuler
)
AS
CREATE TABLE #tbClientNonRenouveller(
TEL_RES nvarchar(8) NOT NULL,
NOM nvarchar(50) NOT NULL,
PRENOM nvarchar(50) NOT NULL,
NO_CIVIC nvarchar(10) NOT NULL,
RUE nvarchar(50) NOT NULL,
VILLE nvarchar(50) NOT NULL,
CODE_POST nvarchar(7) NOT NULL,
NO_SECT nvarchar(10) NOT NULL,
SOLDE float NOT NULL,
lstAnneeCont nvarchar(50) NOT NULL,
nbContrat int NOT NULL
)
IF @contraAnnulerInclu =0 OR @contraAnnulerInclu IS NULL
DECLARE cListeClient CURSOR FOR
SELECT CLIENT.ID_CLIENT, CLIENT.TEL_RES, CLIENT.NOM, CLIENT.PRENOM, CLIENT.NO_CIVIC,
CLIENT.RUE, CLIENT.VILLE, CLIENT.CODE_POST, CLIENT.NO_SECT, CLIENT.SOLDE
FROM CLIENT INNER JOIN
CONTRAT ON CLIENT.ID_CLIENT = CONTRAT.ID_CLIENT AND
CLIENT.NO_FRANCH = CONTRAT.NO_FRANCH
WHERE CONTRAT.NO_FRANCH =@idFranchise
AND CONTRAT.ANNEE_CONTRAT =@anneeCourante-1
AND CONTRAT.ID_CONTRAT NOT IN(
/*Liste de client annee courante*/
SELECT CONTRAT.ID_CONTRAT
FROM CLIENT INNER JOIN
CONTRAT ON CLIENT.ID_CLIENT = CONTRAT.ID_CLIENT AND
CLIENT.NO_FRANCH = CONTRAT.NO_FRANCH
WHERE CONTRAT.NO_FRANCH =@idFranchise
AND CONTRAT.ANNEE_CONTRAT =@anneeCourante)
/*Contra annuler non inclu*/
AND CONTRAT.ID_CONTRAT NOT IN(
SELECT CA.ID_CONTRAT
FROM CONTRAT_ANN AS CA --TODO Ajouter critere DATE pour filtrer & ameliorer la performance
WHERE CA.NO_FRANCH =@idFranchise)
/*Contra annuler non inclu*/
GROUP BY CLIENT.ID_CLIENT, CLIENT.TEL_RES, CLIENT.NOM, CLIENT.PRENOM, CLIENT.NO_CIVIC,
CLIENT.RUE, CLIENT.VILLE, CLIENT.CODE_POST, CLIENT.NO_SECT, CLIENT.SOLDE
ELSE
/*copier coller sans la section "Contra annuler non inclu",
ca serait bien de reussir a placer ca dans une variable et
que le curseur puisse utiliser cette variable*/
DECLARE cListeClient CURSOR FOR
SELECT CLIENT.ID_CLIENT, CLIENT.TEL_RES, CLIENT.NOM, CLIENT.PRENOM, CLIENT.NO_CIVIC,
CLIENT.RUE, CLIENT.VILLE, CLIENT.CODE_POST, CLIENT.NO_SECT, CLIENT.SOLDE
FROM CLIENT INNER JOIN
CONTRAT ON CLIENT.ID_CLIENT = CONTRAT.ID_CLIENT AND
CLIENT.NO_FRANCH = CONTRAT.NO_FRANCH
WHERE CONTRAT.NO_FRANCH =@idFranchise
AND CONTRAT.ANNEE_CONTRAT =@anneeCourante-1
AND CONTRAT.ID_CONTRAT NOT IN(
/*Liste de client annee courante*/
SELECT CONTRAT.ID_CONTRAT
FROM CLIENT INNER JOIN
CONTRAT ON CLIENT.ID_CLIENT = CONTRAT.ID_CLIENT AND
CLIENT.NO_FRANCH = CONTRAT.NO_FRANCH
WHERE CONTRAT.NO_FRANCH =@idFranchise
AND CONTRAT.ANNEE_CONTRAT =@anneeCourante)
GROUP BY CLIENT.ID_CLIENT, CLIENT.TEL_RES, CLIENT.NOM, CLIENT.PRENOM, CLIENT.NO_CIVIC,
CLIENT.RUE, CLIENT.VILLE, CLIENT.CODE_POST, CLIENT.NO_SECT, CLIENT.SOLDE
DECLARE @ID_CLIENT int
DECLARE @TEL_RES nvarchar(8)
DECLARE @NOM nvarchar(50)
DECLARE @PRENOM nvarchar(50)
DECLARE @NO_CIVIC nvarchar(10)
DECLARE @RUE nvarchar(50)
DECLARE @VILLE nvarchar(50)
DECLARE @CODE_POST nvarchar(7)
DECLARE @NO_SECT nvarchar(10)
DECLARE @SOLDE float
DECLARE @lstAnneeCont nvarchar(50)
DECLARE @nbContrat int
BEGIN
OPEN cListeClient
FETCH NEXT FROM cListeClient INTO @ID_CLIENT, @TEL_RES, @NOM, @PRENOM,
@NO_CIVIC, @RUE, @VILLE, @CODE_POST, @NO_SECT, @SOLDE
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @nbContrat=0
SET @lstAnneeCont=''
/*Raison #1 curseur, compter le nombre de contra qu'un client possede*/
SET @nbContrat=(
SELECT COUNT(CONTRAT.ID_CONTRAT)
FROM CONTRAT
WHERE CONTRAT.ID_CLIENT =@ID_CLIENT
AND CONTRAT.NO_FRANCH =@idFranchise
)
/*Raison #2 curseur, Fonction qui renvoie les annees des contras d'un client*/
SET @lstAnneeCont =dbo.func_ListAnneeContrat(@ID_CLIENT)
/*Recupere nos 2 informations traités et le contenu du curseur,
on place tout ca dans une table qu'on fait afficher a la fin*/
INSERT INTO #tbClientNonRenouveller
SELECT @TEL_RES, @NOM, @PRENOM, @NO_CIVIC, @RUE, @VILLE,
@CODE_POST, @NO_SECT, @SOLDE, @lstAnneeCont, @nbContrat
FETCH NEXT FROM cListeClient INTO @ID_CLIENT, @TEL_RES, @NOM, @PRENOM,
@NO_CIVIC, @RUE, @VILLE, @CODE_POST, @NO_SECT, @SOLDE
END
CLOSE cListeClient
DEALLOCATE cListeClient
SELECT * FROM #tbClientNonRenouveller
DROP TABLE #tbClientNonRenouveller
END |
