· 6 years ago · Jul 22, 2019, 02:32 AM
1CREATE DEFINER = 'clientem_gmc'@'%'
2PROCEDURE clientem_clientemelhorcompra.CalcPointsClients(IN IdPrograma INT, IN IdCliente INT)
3begin
4 -- Variaveis dos parâmetros do programa.
5 DECLARE ParamValidity INT;
6 DECLARE ParamRatio INT;
7 DECLARE ParamPercent DOUBLE(8,2);
8 DECLARE FirstIncrement INT;
9
10 DECLARE SecondIncrement INT;
11 DECLARE QtdDischarges INT;
12 DECLARE DateDischarge DATETIME;
13 DECLARE DateDischargeSub DATETIME;
14 DECLARE QtdReleasesOnPeriod INT;
15 DECLARE QtdReleasesNotUsedOnPeriod INT;
16 DECLARE ReleaseLastedOnPeriod INT;
17 DECLARE CodDischarge INT;
18 DECLARE AmountDischarge DECIMAL(10,2);
19 DECLARE ReleaseOnPeriod DECIMAL(10,2);
20 DECLARE CodRelease INT;
21 DECLARE DateRelease DATETIME;
22 DECLARE TmpCodRelease INT;
23 DECLARE TmpValueRelease DECIMAL(10,2);
24 DECLARE TmpDateRelease DATETIME;
25 DECLARE ThirdIncrement INT;
26 DECLARE TmpQtdReleases INT;
27 DECLARE RestRelease DECIMAL(10,2);
28 DECLARE RestDischarge DECIMAL(10,2);
29 DECLARE VerifyTTPoints INT;
30 DECLARE VerifyTTReleases INT;
31 DECLARE QtdCMCPoints INT;
32 DECLARE CMCPointsRelease DECIMAL(10,2);
33 DECLARE DataAtual DATETIME;
34 DECLARE DataPeriodoValidade DATETIME;
35
36 -- Set Variaveis dos parâmetros do sistema.
37 SET ParamValidity = (
38 SELECT P.validity
39 FROM tb_program
40 JOIN tb_parameter AS P ON P.id = tb_program.parameter_id
41 WHERE tb_program.id = IdPrograma
42 );
43 SET ParamRatio = (
44 SELECT P.ratio
45 FROM tb_program
46 JOIN tb_parameter AS P ON P.id = tb_program.parameter_id
47 WHERE tb_program.id = IdPrograma
48 );
49
50 DROP TABLE IF EXISTS rsp_points;
51 CREATE TEMPORARY TABLE rsp_points (
52 cd_release INT,
53 cd_discharge INT,
54 dt_release DATETIME,
55 dt_discharge DATETIME,
56 amount_release DECIMAL(10,2),
57 amount_discharge DECIMAL(10,2),
58 rest_release DECIMAL(10,2),
59 rest_discharge DECIMAL(10,2)
60 );
61
62 SET QtdDischarges = (
63 SELECT COUNT(created_at)
64 FROM tb_discharge
65 WHERE tb_discharge.program_id = IdPrograma
66 AND tb_discharge.client_id = IdCliente
67 AND tb_discharge.deleted_at IS NULL
68 );
69
70
71 DROP TABLE IF EXISTS tt_releases;
72 CREATE TEMPORARY TABLE tt_releases (
73 id int AUTO_INCREMENT PRIMARY KEY,
74 id_release INT,
75 type_release varchar(20),
76 dt_release DATETIME,
77 value_release DECIMAL(10,2),
78 used_by int null
79 );
80
81 DROP TABLE IF EXISTS tt_releases_check;
82 CREATE TEMPORARY TABLE tt_releases_check (
83 id int,
84 id_release INT,
85 type_release varchar(20),
86 dt_release DATETIME,
87 value_release DECIMAL(10,2),
88 used_by int null
89 );
90
91
92 -- Define o valor do Increment como Zero para iniciar o While.
93 SET FirstIncrement = 0;
94
95 WHILE FirstIncrement < QtdDischarges DO
96 SET CodDischarge = (
97 SELECT tb_discharge.id
98 FROM tb_discharge
99 WHERE tb_discharge.program_id = IdPrograma
100 AND tb_discharge.client_id = IdCliente
101 AND tb_discharge.deleted_at IS NULL
102 ORDER BY tb_discharge.created_at ASC
103 LIMIT 1
104 OFFSET FirstIncrement
105 );
106
107 SET DateDischarge = (
108 SELECT tb_discharge.created_at
109 FROM tb_discharge
110 WHERE tb_discharge.id = CodDischarge
111 );
112
113 SET AmountDischarge = (
114 SELECT ROUND(((tb_discharge.value / percent) * ParamRatio) * tb_discharge.quantity)
115 FROM tb_discharge
116 WHERE tb_discharge.deleted_at IS NULL
117 AND tb_discharge.id = CodDischarge
118 );
119
120 SET DateDischargeSub = DATE_SUB(DateDischarge, INTERVAL ParamValidity DAY);
121
122 DROP TABLE IF EXISTS tt_releases_check;
123 CREATE TEMPORARY TABLE tt_releases_check (
124 id int,
125 id_release INT,
126 type_release varchar(20),
127 dt_release DATETIME,
128 value_release DECIMAL(10,2),
129 used_by int null
130 );
131
132
133 INSERT INTO tt_releases_check (id, id_release, type_release, dt_release, value_release, used_by)
134 SELECT * FROM tt_releases tr;
135
136 INSERT INTO tt_releases(id, id_release, type_release, dt_release, value_release, used_by)
137 SELECT NULL, id, "MANUAL", created_at, ROUND(value * ParamRatio) as ttlrelease, null
138 FROM tb_release
139 WHERE tb_release.program_id = IdPrograma
140 AND tb_release.client_id = IdCliente
141 AND tb_release.deleted_at IS NULL
142 AND tb_release.created_at BETWEEN DateDischargeSub AND DateDischarge
143 AND tb_release.id NOT IN (SELECT id_release FROM tt_releases_check WHERE type_release = "MANUAL");
144
145
146 TRUNCATE TABLE tt_releases_check;
147
148 INSERT INTO tt_releases_check (id, id_release, type_release, dt_release, value_release, used_by)
149 SELECT * FROM tt_releases tr;
150
151 INSERT INTO tt_releases(id, id_release, type_release, dt_release, value_release, used_by)
152 SELECT NULL, tri.id, "INTEGRA", tri.date_release, ROUND(tri.value * ParamRatio) as ttlrelease, null
153 FROM tb_release_integration tri
154 JOIN tb_user tu ON tu.id = IdCliente
155 WHERE tri.program_id = IdPrograma
156 AND tri.client_id = IdCliente
157 AND tri.deleted_at IS NULL
158 AND tri.canceled = 'N'
159 AND tri.value > 0
160 AND tri.date_release >= tu.created_at
161 AND tri.date_release BETWEEN DateDischargeSub AND DateDischarge
162 AND tri.id NOT IN (SELECT id_release FROM tt_releases_check WHERE type_release = "INTEGRA");
163
164
165 SET QtdReleasesNotUsedOnPeriod = (
166 SELECT IFNULL(COUNT(dt_release), 0)
167 FROM tt_releases
168 WHERE used_by IS NULL
169 );
170
171 SET ReleaseLastedOnPeriod = (
172 SELECT IFNULL(COUNT(dt_release), 0)
173 FROM rsp_points
174 WHERE rest_release > 0
175 AND dt_release BETWEEN DateDischargeSub AND DateDischarge
176 );
177
178 SET QtdReleasesOnPeriod = QtdReleasesNotUsedOnPeriod + ReleaseLastedOnPeriod;
179
180 SET SecondIncrement = 0;
181
182 WHILE SecondIncrement < QtdReleasesOnPeriod DO
183 IF (FirstIncrement > 0) THEN
184 SET VerifyTTReleases = (
185 SELECT cd_release
186 FROM rsp_points
187 WHERE rest_release > 0
188 );
189
190 IF (VerifyTTReleases IS NOT NULL) THEN
191 SET CodRelease = VerifyTTReleases;
192 ELSE
193 SET CodRelease = (
194 SELECT tt_releases.id
195 FROM tt_releases
196 WHERE tt_releases.used_by IS NULL
197 AND tt_releases.dt_release BETWEEN DateDischargeSub AND DateDischarge
198 ORDER BY tt_releases.dt_release LIMIT 1
199 );
200 END IF;
201 ELSE
202 SET CodRelease = (
203 SELECT tt_releases.id
204 FROM tt_releases
205 WHERE tt_releases.used_by IS NULL
206 ORDER BY tt_releases.dt_release ASC LIMIT 1
207 );
208 END IF;
209
210 IF (VerifyTTReleases IS NOT NULL) THEN
211 SET ReleaseOnPeriod = (
212 SELECT rest_release
213 FROM rsp_points
214 WHERE rsp_points.cd_release = CodRelease
215 );
216
217 SET DateRelease = (
218 SELECT dt_release
219 FROM rsp_points
220 WHERE rsp_points.cd_release = CodRelease
221 );
222 ELSE
223 SET ReleaseOnPeriod = (
224 SELECT tt_releases.value_release
225 FROM tt_releases
226 WHERE tt_releases.id = CodRelease
227 );
228
229 SET DateRelease = (
230 SELECT tt_releases.dt_release
231 FROM tt_releases
232 WHERE tt_releases.id = CodRelease
233 );
234 END IF;
235
236 IF (RestDischarge IS NOT NULL) THEN
237 IF (RestDischarge > ReleaseOnPeriod) THEN
238 SET RestRelease = 0;
239 SET RestDischarge = RestDischarge - ReleaseOnPeriod;
240 ELSE
241 SET RestRelease = ReleaseOnPeriod - RestDischarge;
242 SET RestDischarge = 0;
243 END IF;
244 ELSE
245 IF (AmountDischarge > ReleaseOnPeriod) THEN
246 SET RestRelease = 0;
247 SET RestDischarge = AmountDischarge - ReleaseOnPeriod;
248 ELSE
249 SET RestDischarge = 0;
250 SET RestRelease = ReleaseOnPeriod - AmountDischarge;
251 END IF;
252 END IF;
253
254 IF (VerifyTTReleases IS NOT NULL) THEN
255 UPDATE rsp_points
256 SET rest_release = RestRelease
257 WHERE cd_release = CodRelease;
258 ELSE
259 INSERT INTO rsp_points
260 VALUES (CodRelease, CodDischarge, DateRelease, DateDischarge, ReleaseOnPeriod, AmountDischarge, RestRelease, RestDischarge);
261
262 UPDATE tt_releases
263 SET used_by = CodDischarge
264 WHERE tt_releases.id = CodRelease;
265 END IF;
266
267 IF (RestRelease > 0) THEN
268 SET SecondIncrement = QtdReleasesOnPeriod;
269 ELSE
270 SET SecondIncrement = SecondIncrement + 1;
271 END IF;
272 END WHILE;
273
274 SET FirstIncrement = FirstIncrement + 1;
275 SET RestDischarge = NULL;
276
277 END WHILE;
278
279 -- Incio Calculo Final.
280
281 SET DataAtual = DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR);
282 SET DataPeriodoValidade = DATE_SUB(DataAtual, INTERVAL ParamValidity Day);
283
284
285 TRUNCATE TABLE tt_releases_check;
286 INSERT INTO tt_releases_check (id, id_release, type_release, dt_release, value_release, used_by)
287 SELECT * FROM tt_releases tr;
288
289 INSERT INTO tt_releases(id, id_release, type_release, dt_release, value_release, used_by)
290 SELECT NULL, id, "MANUAL", created_at, ROUND(value * ParamRatio) as ttlrelease, null
291 FROM tb_release
292 WHERE tb_release.program_id = IdPrograma
293 AND tb_release.client_id = IdCliente
294 AND tb_release.deleted_at IS NULL
295 AND tb_release.created_at BETWEEN DataPeriodoValidade AND DataAtual
296 AND tb_release.id NOT IN (
297 SELECT id_release
298 FROM tt_releases_check
299 WHERE type_release = "MANUAL"
300 );
301
302
303 TRUNCATE TABLE tt_releases_check;
304
305 INSERT INTO tt_releases_check (id, id_release, type_release, dt_release, value_release, used_by)
306 SELECT * FROM tt_releases tr;
307
308 INSERT INTO tt_releases(id, id_release, type_release, dt_release, value_release, used_by)
309 SELECT NULL, tri.id, "INTEGRA", tri.date_release, ROUND(tri.value * ParamRatio) as ttlrelease, null
310 FROM tb_release_integration tri
311 JOIN tb_user tu ON tu.id = IdCliente
312 WHERE tri.program_id = IdPrograma
313 AND tri.client_id = IdCliente
314 AND tri.created_at >= tu.created_at
315 AND tri.deleted_at IS NULL
316 AND tri.canceled = 'N'
317 AND tri.value > 0
318 AND tri.date_release BETWEEN DataPeriodoValidade AND DataAtual
319 AND tri.id NOT IN (
320 SELECT id_release
321 FROM tt_releases_check
322 WHERE type_release = "INTEGRA"
323 );
324
325 DROP TABLE IF EXISTS tt_points_check;
326 CREATE TEMPORARY TABLE tt_points_check (
327 cd_release INT,
328 cd_discharge INT,
329 dt_release DATETIME,
330 dt_discharge DATETIME,
331 amount_release DECIMAL(10,2),
332 amount_discharge DECIMAL(10,2),
333 rest_release DECIMAL(10,2),
334 rest_discharge DECIMAL(10,2)
335 );
336
337 INSERT INTO tt_points_check (cd_release, cd_discharge, dt_release, dt_discharge, amount_release, amount_discharge, rest_release, rest_discharge)
338 SELECT * FROM rsp_points rp;
339
340 INSERT INTO rsp_points (cd_release, cd_discharge, dt_release, dt_discharge, amount_release, amount_discharge, rest_release, rest_discharge)
341 SELECT id, 0, dt_release, CURRENT_TIMESTAMP(), value_release, 0, value_release, 0
342 FROM tt_releases tr
343 WHERE tr.used_by IS NULL
344 AND tr.dt_release BETWEEN DataPeriodoValidade AND DataAtual
345 AND tr.id NOT IN (
346 SELECT cd_release FROM tt_points_check tpc
347 );
348
349 SELECT ROUND(SUM(rp.rest_release)) AS totalPontos FROM rsp_points rp WHERE rp.dt_release BETWEEN DataPeriodoValidade AND DataAtual;
350end