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
|
ALTER PROCEDURE [dbo].[P_I_DF_POND_GENERIC_BETA]
-- Add the parameters for the stored procedure here
@SCHEMA VARCHAR(128) = 'dbo', -- nom du schema de la table (si NULL => dbo).
@TABLE varchar(128), -- nom de la table
@KEYCOL VARCHAR(130), -- nom de la colonne clef dans la table
@KEYCOL_ID INT, -- valeur de l'ID de la colonne clef dans la table
@CAL_TYPE_ID INT, -- valeur de l'ID du type de calendrier
@DD DATE, -- Date de début
@DUREE INT, -- Durée
@ETAT INT OUTPUT -- ETAT de retour d'insertion -- 0 = FALSE -- 1 = TRUE
AS
SET NOCOUNT ON;
-- vérification des paramètres
IF @TABLE IS NULL
BEGIN
RAISERROR('Nom de table omis (paramètre @TABLE). Insertion dans la procédure générique : dbo.P_I_DF_POND_GENERIC . ', 16, 1)
RETURN;
END
IF @KEYCOL IS NULL
BEGIN
RAISERROR('Nom de la colonne clef omis (paramètre @KEYCOL). Insertion dans la procédure générique : dbo.P_I_DF_POND_GENERIC . ', 16, 1)
RETURN;
END
IF @KEYCOL_ID IS NULL
BEGIN
RAISERROR(' ID de la colonne clef omis (paramètre @KEYCOL_ID). Insertion dans la procédure générique : dbo.P_I_DF_POND_GENERIC . ', 16, 1)
RETURN;
END
IF @CAL_TYPE_ID IS NULL
BEGIN
RAISERROR('Ce type de calendrier n''existe pas (@CAL_TYPE_ID).', 16, 1)
RETURN;
END
IF ((@DD IS NULL AND @DUREE IS NULL))
BEGIN
RAISERROR('Des variables n''ont pas été renseignées ou Elles sont incohérentes pour le système. (@DD, @DUREE).', 16, 1)
RETURN;
END
IF NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @SCHEMA
AND TABLE_NAME = @TABLE)
BEGIN
RAISERROR('Table inconnue (paramètres @SCHEMA et @TABLE, valeurs %s.%s). Insertion dans la procédure générique : dbo.P_I_DF_POND_GENERIC . ', 16, 1, @SCHEMA, @TABLE)
RETURN;
END
IF NOT EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCHEMA
AND TABLE_NAME = @TABLE
AND COLUMN_NAME = @KEYCOL)
BEGIN
RAISERROR('Colonne clef primaire inconnue (paramètre @KEYCOL, valeurs %s). Insertion dans la procédure générique : dbo.P_I_DF_POND_GENERIC . ', 16, 1, @KEYCOL)
RETURN;
END
DECLARE @SQL varchar(max), @VERIF_POND FLOAT, @DF DATE, @T VARCHAR(257),
@DUREE_POND FLOAT,@JOUR_ID INT,@JOUR varchar(32),@ERROR varchar(max);
SET @T = @SCHEMA +'.' + @TABLE;
SET @ETAT = 0;
-- démarrage transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
------------------------------------------------------------------------------------------------------------
-- Vérifie si la somme des prondérations de la date maximum d'un type de -------
-- calendrier est supérieurs à zéro. Sinon problème de boucle infinie et -----------
-- incohérence vis à vis des règles métiers --------------------------------------------------
------------------------------------------------------------------------------------------------------------
SET @VERIF_POND=(SELECT (T_CAL_P.LUNDI+T_CAL_P.MARDI+T_CAL_P.MERCREDI+T_CAL_P.JEUDI+T_CAL_P.VENDREDI+T_CAL_P.SAMEDI+T_CAL_P.DIMANCHE)
FROM T_CAL
INNER JOIN T_CAL_P ON T_CAL.CAL_P_ID=T_CAL_P.CAL_P_ID
WHERE CAL_ID=(SELECT CAL_ID FROM T_CAL WHERE CAL_TYPE_ID=@CAL_TYPE_ID
AND DATE_START=(SELECT MAX(DATE_START) FROM T_CAL WHERE CAL_TYPE_ID=@CAL_TYPE_ID)))
-- Obliger de retourner une ligne grace à la contrainte UNIQUE_CAL_TYPE_ID_DATE_START
IF @@ERROR <> 0 GOTO LBL_ERROR;
IF (@VERIF_POND <= 0 OR @VERIF_POND IS NULL)
BEGIN
RAISERROR('Problème dans les pondérations du calendrier', 16, 1)
RETURN;
END
-- Ma date de fin est décalée à la date de début moins un jour
SET @DF = DATEADD("dd",-1,@DD);
-- je récupère le jour de la semaine
SET @JOUR_ID = DATEPART(weekday,@DD);
SET @DUREE_POND=0;
WHILE @DUREE > 0
BEGIN
SET @JOUR=(SELECT COL_NAME(OBJECT_ID('dbo.T_CAL_P'),@JOUR_ID));
-- Ma date de fin est décalée de 1 jour
SET @DF = DATEADD("dd",1,@DF);
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- je récupère la date la plus proche de la date de début de ma tâche(TASK.DATE_START) et je prend la ponduration de chaque jour de la semaine avec la boucle
-- j'ai pas réussi à faire autrement... on ne peut pas à ce jour passer une variable (@JOUR) directement...
-- En terme de performance ça aurait été une erreur de le faire avec des curseurs !
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF @JOUR ='LUNDI'
BEGIN
SET @DUREE_POND=(SELECT T_CAL_P.LUNDI
FROM T_CAL INNER JOIN T_CAL_P ON T_CAL.CAL_P_ID=T_CAL_P.CAL_P_ID
WHERE CAL_ID=(SELECT CAL_ID
FROM T_CAL
WHERE DATE_START<=(SELECT MAX(DATE_START)
FROM T_CAL
WHERE DATE_START<=@DD AND CAL_TYPE_ID=1) AND CAL_TYPE_ID=@CAL_TYPE_ID))
END
ELSE IF @JOUR ='MARDI'
BEGIN
SET @DUREE_POND=(SELECT T_CAL_P.MARDI
FROM T_CAL INNER JOIN T_CAL_P ON T_CAL.CAL_P_ID=T_CAL_P.CAL_P_ID
WHERE CAL_ID=(SELECT CAL_ID
FROM T_CAL
WHERE DATE_START<=(SELECT MAX(DATE_START)
FROM T_CAL
WHERE DATE_START<=@DD AND CAL_TYPE_ID=1) AND CAL_TYPE_ID=@CAL_TYPE_ID))
END
ELSE IF @JOUR ='MERCREDI'
BEGIN
SET @DUREE_POND=(SELECT T_CAL_P.MERCREDI
FROM T_CAL INNER JOIN T_CAL_P ON T_CAL.CAL_P_ID=T_CAL_P.CAL_P_ID
WHERE CAL_ID=(SELECT CAL_ID
FROM T_CAL
WHERE DATE_START<=(SELECT MAX(DATE_START)
FROM T_CAL
WHERE DATE_START<=@DD AND CAL_TYPE_ID=1) AND CAL_TYPE_ID=@CAL_TYPE_ID))
END
ELSE IF @JOUR ='JEUDI'
BEGIN
SET @DUREE_POND=(SELECT T_CAL_P.JEUDI
FROM T_CAL INNER JOIN T_CAL_P ON T_CAL.CAL_P_ID=T_CAL_P.CAL_P_ID
WHERE CAL_ID=(SELECT CAL_ID
FROM T_CAL
WHERE DATE_START<=(SELECT MAX(DATE_START)
FROM T_CAL
WHERE DATE_START<=@DD AND CAL_TYPE_ID=1) AND CAL_TYPE_ID=@CAL_TYPE_ID))
END
ELSE IF @JOUR ='VENDREDI'
BEGIN
SET @DUREE_POND=(SELECT T_CAL_P.VENDREDI
FROM T_CAL INNER JOIN T_CAL_P ON T_CAL.CAL_P_ID=T_CAL_P.CAL_P_ID
WHERE CAL_ID=(SELECT CAL_ID
FROM T_CAL
WHERE DATE_START<=(SELECT MAX(DATE_START)
FROM T_CAL
WHERE DATE_START<=@DD AND CAL_TYPE_ID=1) AND CAL_TYPE_ID=@CAL_TYPE_ID))
END
ELSE IF @JOUR ='SAMEDI'
BEGIN
SET @DUREE_POND=(SELECT T_CAL_P.SAMEDI
FROM T_CAL INNER JOIN T_CAL_P ON T_CAL.CAL_P_ID=T_CAL_P.CAL_P_ID
WHERE CAL_ID=(SELECT CAL_ID
FROM T_CAL
WHERE DATE_START<=(SELECT MAX(DATE_START)
FROM T_CAL
WHERE DATE_START<=@DD AND CAL_TYPE_ID=1) AND CAL_TYPE_ID=@CAL_TYPE_ID))
END
ELSE IF @JOUR ='DIMANCHE'
BEGIN
SET @DUREE_POND=(SELECT T_CAL_P.DIMANCHE
FROM T_CAL INNER JOIN T_CAL_P ON T_CAL.CAL_P_ID=T_CAL_P.CAL_P_ID
WHERE CAL_ID=(SELECT CAL_ID
FROM T_CAL
WHERE DATE_START<=(SELECT MAX(DATE_START)
FROM T_CAL
WHERE DATE_START<=@DD AND CAL_TYPE_ID=1) AND CAL_TYPE_ID=@CAL_TYPE_ID))
END
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
-- ON TEST S'IL Y A UNE ERREUR LORS DE l'EXECUTION D'UNE REQUETE -------------
----------------------------------------------------------------------------------------------------------
IF @@ERROR <> 0 GOTO LBL_ERROR;
IF (@DUREE_POND IS NULL) GOTO LBL_ERROR_COUNT;
----------------------------------------------------------------------------------------------------------
-- on décrémente la durée en fonction de la pondération
SET @DUREE -= @DUREE_POND;
-- si on est à dimanche
IF @JOUR_ID = 7
BEGIN
-- on commence à lundi
SET @JOUR_ID = 1
END
ELSE
BEGIN
-- on passe au jour suivant
SET @JOUR_ID+= 1
END
END -- FIN DU WHILE
-------------------------------------------
-- UPDATE -----------------------------
-------------------------------------------
SET @SQL='SET NOCOUNT ON; UPDATE '+@T+' SET DATE_FINISH='''+CAST(@DF AS varchar(32))+''' WHERE '+@KEYCOL+'='+CAST(@KEYCOL_ID AS varchar(max))+';'
-------------------------------------------
EXECUTE(@SQL);
-------------------------------------------
IF @@ERROR <> 0 GOTO LBL_ERROR;
-- succès
LBL_OK:
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GOTO LBL_RESUME;
-- erreur SQL
LBL_ERROR:
IF @@TRANCOUNT > 1
COMMIT TRANSACTION;
ELSE
IF @@TRANCOUNT = 1
ROLLBACK TRANSACTION;
RAISERROR('Erreur SQL N°%d dans la procédure : P_I_DF_POND_GENERIC .', 16, 1, @ERROR);
-- erreur aucune insertion
LBL_ERROR_COUNT:
IF @@TRANCOUNT > 1
COMMIT TRANSACTION;
ELSE
IF @@TRANCOUNT = 1
ROLLBACK TRANSACTION;
RAISERROR('Erreur fonctionnelle : P_I_DF_POND_GENERIC . Aucune ligne insérée', 16, 1);
--------------------------------------------------------------------------
-- sortie de transaction
LBL_RESUME:
SET @ETAT = 1;
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
------------------------------------------------------------------------- |
23/05/2011, 14h17
[SQL 2k8] Cherche conseils sur l'évolution d'une base pour la gestion du temps
00
Nicolas Souquet
) voir fichier joint
