· 6 years ago · Jun 27, 2019, 05:22 PM
1/*************************************
2 * Reporte Batch Calidad : GNA - DWH *
3 *************************************/
4if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_Batch_Cal_GNA_DWH]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
5drop procedure [dbo].[sp_Batch_Cal_GNA_DWH]
6GO
7
8CREATE PROCEDURE sp_Batch_Cal_GNA_DWH (
9 @idb int,
10 @param varchar(1000)
11)
12AS
13
14set nocount on
15
16declare @DiaI smalldatetime
17declare @DiaF smalldatetime
18
19declare @IdM smallint
20declare @IdO int, @IdO_ int
21declare @IdS tinyint, @IdS_ tinyint
22declare @IdSS tinyint, @IdSS_ tinyint
23declare @IdE int, @IdE_ int
24declare @Tur smallint, @Tur_ smallint
25declare @FHE datetime, @FHE_ datetime
26declare @FHI datetime, @FHI_ datetime
27declare @FHF datetime, @FHF_ datetime
28declare @Rut varchar(10), @Rut_ varchar(10)
29declare @Fono varchar(10), @Fono_ varchar(10)
30declare @Per bit, @Per_ bit
31declare @TEsp int, @TEsp_ int
32declare @TAte int, @TAte_ int
33declare @tmin smallint
34
35declare @Ofi varchar(50)
36declare @Ser varchar(50)
37declare @SSer varchar(50)
38declare @Eje varchar(50)
39declare @Mot varchar(50)
40declare @Mo1 varchar(50)
41declare @Mo2 varchar(50)
42declare @Mo3 varchar(50)
43declare @Mo4 varchar(50)
44declare @Mo5 varchar(50)
45
46declare @pre varchar(200)
47declare @Pr1 varchar(200)
48declare @Pr2 varchar(200)
49declare @Pr3 varchar(200)
50declare @Pr4 varchar(200)
51declare @Pr5 varchar(200)
52-- test
53declare @Rde varchar(200)
54declare @Rd1 varchar(200)
55declare @Rd2 varchar(200)
56declare @Rd3 varchar(200)
57declare @Rd4 varchar(200)
58declare @Rd5 varchar(200)
59
60declare @idReq int
61
62declare @idR1 int
63declare @idR2 int
64declare @idR3 int
65declare @idR4 int
66declare @idR5 int
67
68declare @fGen1 bit
69declare @fGen2 bit
70declare @fGen3 bit
71declare @fGen4 bit
72declare @fGen5 bit
73
74
75
76--
77declare @rsp tinyint
78declare @Rs1 tinyint
79declare @Rs2 tinyint
80declare @Rs3 tinyint
81declare @Rs4 tinyint
82declare @Rs5 tinyint
83
84declare @i int -- contador de filas de salida
85declare @j int -- contador de motivos
86declare @k int -- contador de registros leidos por fila de salida
87declare @x int -- contador de encuestas
88
89-- separo Parámetros ------------------------------------------------------
90-- 0,DiaI 1,DiaF
91select @i = 1,
92 @k = 0
93while (@i <= len(@param))
94begin
95 select @j = charindex(';', @param, @i) --busco separador
96 if (@j = 0) select @j = len(@param) + 1
97
98 -- 103 : dd/mm/yyyy
99 if (@k = 0) select @DiaI = convert(datetime, substring(@param, @i, @j - @i), 103)
100 else if (@k = 1) select @DiaF = convert(datetime, substring(@param, @i, @j - @i), 103) + 1
101
102 select @i = @j + 1,
103 @k = @k + 1
104end
105---------------------------------------------------------------------------
106
107truncate table Tb_DWH
108
109create table #preguntas(
110 idReq int,
111 rsp3 varchar(50),
112 rsp4 varchar(50),
113 rsp5 varchar(50),
114 rsp6 varchar(50),
115)
116
117
118insert into #preguntas (idreq, rsp3, rsp4, rsp5, rsp6)
119 select idreq,
120 (select name from dbo.splitstring(rsp3, ',') where idx = 1),
121 (select name from dbo.splitstring(rsp3, ',') where idx = 2),
122 (select name from dbo.splitstring(rsp3, ',') where idx = 3),
123 (select name from dbo.splitstring(rsp3, ',') where idx = 4)
124 from EncPreguntas e
125
126
127
128CREATE TABLE #DWH_Ate (
129 IdOficina int,
130 IdSerie tinyint,
131 IdSubSerie tinyint,
132 IdEje int,
133 Turno smallint,
134 FH_Emi datetime,
135 FH_AteIni datetime,
136 FH_AteFin datetime,
137 TpoEsp int,
138 TpoAte int,
139 Rut varchar(10),
140 Fono varchar(10),
141 Ate_per bit
142)
143CREATE TABLE #DWH_Mot (
144 IdOficina int,
145 IdSerie tinyint,
146 IdMotivo smallint,
147 Turno smallint,
148 FH_Mot datetime,
149 Cantidad smallint
150)
151
152insert into #DWH_Ate
153 select IdOficina,
154 IdSerie,
155 IdSubSerie,
156 IdEje,
157 Turno,
158 FH_Emi,
159 FH_AteIni,
160 FH_AteFin,
161 TpoEsp,
162 TpoAte,
163 ltrim(Rut),
164 ltrim(Telefono),
165 Perdido
166 from Atenciones
167 where FH_Emi between @DiaI and @DiaF
168
169insert into #DWH_Mot
170 select IdOficina,
171 IdSerie,
172 IdMotivo,
173 Turno,
174 FH_Mot,
175 Cantidad
176 from AteMotivo
177 where FH_Mot between @diaI and @diaF
178
179select @i = 0,
180 @j = 1,
181 @k = 0,
182 @IdO_ = 0,
183 @IdS_ = 0,
184 @IdSS_ = 0,
185 @Tur_ = 0,
186 @IdE_ = 0,
187 @Per_ = 0,
188 @FHE_ = '01/01/1900'
189
190declare curDWH
191 Insensitive
192 Cursor For
193 select A.IdOficina, A.IdSerie, A.IdSubSerie, IdEje, A.Turno, FH_Emi, FH_AteIni, FH_AteFin, TpoEsp, TpoAte, Ate_per, Rut, Fono, IdMotivo
194 from #DWH_Ate A
195 left join #DWH_Mot M
196 on A.FH_Emi = M.FH_Mot
197 and A.IdOficina = M.IdOficina
198 and A.IdSerie = M.IdSerie
199 and A.Turno = M.Turno
200 order by A.IdOficina, FH_Emi, A.IdSerie, Rut
201open curDWH
202while (10 = 10)
203begin
204 fetch next from curDWH into @IdO, @IdS, @IdSS, @IdE, @Tur, @FHE, @FHI, @FHF, @TEsp, @TAte, @Per, @Rut, @Fono, @IdM
205 if ( @@fetch_status <> 0 ) break
206
207 if (@IdO_ <> @IdO or @IdS_ <> @IdS or @IdSS_ <> @IdSS or @IdE_ <> @IdE or @Tur_ <> @Tur or @FHE_ <> @FHE or @Per_ <> @Per)
208 begin
209 if (@i > 0)
210 begin
211 -- obtengo Preguntas y Respuestas de Encuesta
212 select @Pr1 = null, @Rs1 = null, @Rd1 = null, @idReq = null,
213 @Pr2 = null, @Rs2 = null, @Rd2 = null, @idReq = null,
214 @Pr3 = null, @Rs3 = null, @Rd3 = null, @idReq = null,
215 @Pr4 = null, @Rs4 = null, @Rd4 = null, @idReq = null,
216 @Pr5 = null, @Rs5 = null, @Rd5 = null, @idReq = null,
217 @x = 1
218 declare curEnc
219 Insensitive
220 Cursor For
221 select top 5 Req, min(Rsp), Rsp1, P.IdReq
222 from EncPreguntas P, EncRespuestas R
223 where P.IdReq = R.IdReq
224 and IdOficina = @IdO_
225 and IdSerie = @IdS_
226 and Turno = @Tur_
227 and FH_Rsp = @FHE_
228 group by P.IdReq, Req, NReq, rsp1
229 order by NReq
230 open curEnc
231 while (10 = 10)
232 begin
233 fetch next from curEnc into @pre, @rsp, @Rde, @idReq
234 if ( @@fetch_status <> 0 ) break
235
236 if (@x = 1) select @Pr1 = @pre, @Rs1 = @rsp, @Rd1 = @Rde, @idR1 = @idReq
237 if (@x = 2) select @Pr2 = @pre, @Rs2 = @rsp, @Rd2 = @Rde, @idR2 = @idReq
238 if (@x = 3) select @Pr3 = @pre, @Rs3 = @rsp, @Rd3 = @Rde, @idR3 = @idReq
239 if (@x = 4) select @Pr4 = @pre, @Rs4 = @rsp, @Rd4 = @Rde, @idR4 = @idReq
240 if (@x = 5) select @Pr5 = @pre, @Rs5 = @rsp, @Rd5 = @Rde, @idR5 = @idReq
241 set @x = @x + 1
242 end
243 close curEnc
244 deallocate curEnc
245
246
247 select @fGen1 = case when (select rsp6 from #preguntas where idReq = @idR1) is not null then 1 else 0 end,
248 @fGen2 = case when (select rsp6 from #preguntas where idReq = @idR2) is not null then 1 else 0 end,
249 @fGen3 = case when (select rsp6 from #preguntas where idReq = @idR3) is not null then 1 else 0 end,
250 @fGen4 = case when (select rsp6 from #preguntas where idReq = @idR4) is not null then 1 else 0 end,
251 @fGen5 = case when (select rsp6 from #preguntas where idReq = @idR5) is not null then 1 else 0 end
252
253 insert into Tb_DWH (
254 IdOficina, Oficina, Fecha, H_Emi, H_AteI, H_AteF, fgPerd,
255 Tpo_Esp, Tpo_Ate, IdEje, Ejecutivo, Serie, SubSerie, Turno, Rut, Fono,
256 Motivo_1, Motivo_2, Motivo_3, Motivo_4, Motivo_5,
257 fgEnc,
258 Pregunta_1, Respuesta_1,
259 Pregunta_2, Respuesta_2,
260 Pregunta_3, Respuesta_3,
261 Pregunta_4, Respuesta_4,
262 Pregunta_5, Respuesta_5 )
263 values (
264 @IdO_,
265 @Ofi,
266 convert(char(10), @FHE_, 120),
267 convert(char(8), @FHE_, 108),
268 convert(char(8), @FHI_, 108),
269 convert(char(8), @FHF_, 108),
270 case when @Per_ = 1 or @Tate_ < @tmin then 'S' else 'N' end,
271 @TEsp_,
272 @Tate_,
273 @IdE_,
274 @Eje,
275 @Ser,
276 @SSer,
277 @Tur_,
278 @Rut_,
279 @Fono_,
280 @Mo1, @Mo2, @Mo3, @Mo4, @Mo5,
281 case when @Pr1 is not null then 'S' else 'N' end,
282 @Pr1, case when @fGen1 = 0 then case @Rs1 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else
283 case @Rs1 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
284 end,
285 @Pr2, case when @fGen2 = 0 then case @Rs2 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else
286 case @Rs2 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
287 end,
288 @Pr3, case when @fGen3 = 0 then case @Rs3 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else else null end else
289 case @Rs3 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
290 end,
291 @Pr4, case when @fGen4 = 0 then case @Rs4 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else
292 case @Rs4 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
293 end,
294 @Pr5, case when @fGen5 = 0 then case @Rs5 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else
295 case @Rs5 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
296 end)
297 set @j = 1
298 set @k = 0
299 end
300 -- Pesimo = P, Malo = M, Regular = R, Bueno = B ,Excelente = E, M<35 = Z, M35~45 = X, M>45 = C, H<35 = T, H35~45 = Y, H>45 = U
301
302 select @Ofi = null,
303 @tmin = null,
304 @Ser = null,
305 @Eje = null,
306 @SSer = null
307
308 select @Ofi = Oficina from Oficinas where IdOficina = @IdO
309 select @Eje = Ejecutivo from Ejecutivos where IdEje = @IdE
310 select @SSer = SubSerie from SubSeries where IdSubSerie = @IdSS
311 select @tmin = tMinAte,
312 @Ser = replace(serie, ',', '_') from Series where IdOficina = @IdO and IdSerie = @IdS
313
314 select @IdO_ = @IdO,
315 @IdS_ = @IdS,
316 @IdSS_ = @IdSS,
317 @IdE_ = @IdE,
318 @Tur_ = @Tur,
319 @FHE_ = @FHE,
320 @FHI_ = @FHI,
321 @FHF_ = @FHF,
322 @TEsp_ = @TEsp,
323 @TAte_ = @TAte,
324 @Rut_ = @Rut,
325 @Fono_ = @Fono,
326 @Per_ = @Per,
327 @Mo1 = '',
328 @Mo2 = '',
329 @Mo3 = '',
330 @Mo4 = '',
331 @Mo5 = '',
332 @i = @i + 1
333 end
334
335 if (isnull(@IdM, 0) > 0)
336 begin
337 select @Mot = Motivo from Motivos where IdMotivo = @IdM
338 if (@j = 1) set @Mo1 = @Mot
339 if (@j = 2) set @Mo2 = @Mot
340 if (@j = 3) set @Mo3 = @Mot
341 if (@j = 4) set @Mo4 = @Mot
342 if (@j = 5) set @Mo5 = @Mot
343 set @j = @j + 1
344 end
345 set @k = @k + 1
346end
347close curDWH
348deallocate curDWH
349
350-- llena el rezagado
351if (@i > 0 and @k > 0)
352begin
353 -- obtengo Preguntas y Respuestas de Encuesta
354 select @Pr1 = null, @Rs1 = null, @Rd1 = null, @idReq = null,
355 @Pr2 = null, @Rs2 = null, @Rd2 = null, @idReq = null,
356 @Pr3 = null, @Rs3 = null, @Rd3 = null, @idReq = null,
357 @Pr4 = null, @Rs4 = null, @Rd4 = null, @idReq = null,
358 @Pr5 = null, @Rs5 = null, @Rd5 = null, @idReq = null,
359 @x = 1
360 declare curEnc
361 Insensitive
362 Cursor For
363 select top 5 Req, Rsp, p.Rsp1, p.IdReq
364 from EncPreguntas P, EncRespuestas R
365 where P.IdReq = R.IdReq
366 and IdOficina = @IdO
367 and IdSerie = @IdS
368 and Turno = @Tur
369 and FH_Rsp = @FHE
370 order by NReq
371 open curEnc
372 while (10 = 10)
373 begin
374 fetch next from curEnc into @pre, @rsp, @rde, @idReq
375 if ( @@fetch_status <> 0 ) break
376
377 if (@x = 1) select @Pr1 = @pre, @Rs1 = @rsp, @Rd1 = @Rde, @idR1 = @idReq
378 if (@x = 2) select @Pr2 = @pre, @Rs2 = @rsp, @Rd2 = @Rde, @idR2 = @idReq
379 if (@x = 3) select @Pr3 = @pre, @Rs3 = @rsp, @Rd3 = @Rde, @idR3 = @idReq
380 if (@x = 4) select @Pr4 = @pre, @Rs4 = @rsp, @Rd4 = @Rde, @idR4 = @idReq
381 if (@x = 5) select @Pr5 = @pre, @Rs5 = @rsp, @Rd5 = @Rde, @idR5 = @idReq
382
383 set @x = @x + 1
384 end
385 close curEnc
386 deallocate curEnc
387
388
389
390 insert into Tb_DWH (
391 IdOficina, Oficina, Fecha, H_Emi, H_AteI, H_AteF, fgPerd,
392 Tpo_Esp, Tpo_Ate, IdEje, Ejecutivo, Serie, SubSerie, Turno, Rut, Fono,
393 Motivo_1, Motivo_2, Motivo_3, Motivo_4, Motivo_5,
394 fgEnc,
395 Pregunta_1, Respuesta_1,
396 Pregunta_2, Respuesta_2,
397 Pregunta_3, Respuesta_3,
398 Pregunta_4, Respuesta_4,
399 Pregunta_5, Respuesta_5 )
400 values (
401 @IdO_,
402 @Ofi,
403 convert(char(10), @FHE_, 120),
404 convert(char(8), @FHE_, 108),
405 convert(char(8), @FHI_, 108),
406 convert(char(8), @FHF_, 108),
407 case when @Per_ = 1 or @Tate_ < @tmin then 'S' else 'N' end,
408 @TEsp_,
409 @Tate_,
410 @IdE,
411 @Eje,
412 @Ser,
413 @SSer,
414 @Tur_,
415 @Rut_,
416 @Fono_,
417 @Mo1, @Mo2, @Mo3, @Mo4, @Mo5,
418 case when @Pr1 is not null then 'S' else 'N' end,
419 @Pr1, case when @fGen1 = 0 then case @Rs1 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else
420 case @Rs1 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
421 end,
422 @Pr2, case when @fGen2 = 0 then case @Rs2 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else
423 case @Rs2 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
424 end,
425 @Pr3, case when @fGen3 = 0 then case @Rs3 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else else null end else
426 case @Rs3 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
427 end,
428 @Pr4, case when @fGen4 = 0 then case @Rs4 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else
429 case @Rs4 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
430 end,
431 @Pr5, case when @fGen5 = 0 then case @Rs5 when 1 then 'M' when 2 then 'R' when 3 then 'B' else null end else
432 case @Rs5 when 1 then 'Z' when 2 then 'X' when 3 then 'C' when 4 then 'T' when 5 then 'Y' when 6 then 'U' else null end
433 end)
434end
435 -- Malo = M, Regular = R, Bueno = B , M<35 = Z, M35~45 = X, M>45 = C, H<35 = T, H35~45 = Y, H>45 = U
436select 'IdOficina', 'Oficina', 'Fecha', 'Hora Emi', 'Hora Ate Ini', 'Hora Ate Fin', 'Perdido', 'Tiempo Espera', 'Tiempo Atencion',
437 'Id Ejecutivo', 'Ejecutivo', 'Serie', 'SubSerie', 'Turno', 'Rut', 'Fono',
438 'Encuesta',
439 'Pregunta 1', 'Respuesta 1',
440 'Pregunta 2', 'Respuesta 2',
441 'Pregunta 3', 'Respuesta 3',
442 'Pregunta 4', 'Respuesta 4',
443 'Pregunta 5', 'Respuesta 5',
444 'Motivo 1', 'Motivo 2', 'Motivo 3', 'Motivo 4', 'Motivo 5'
445
446select IdOficina, Oficina, Fecha, H_Emi, H_AteI, H_AteF, fgPerd, Tpo_Esp, Tpo_Ate, IdEje, Ejecutivo, Serie, SubSerie, Turno, Rut, Fono,
447 fgEnc,
448 Pregunta_1, Respuesta_1,
449 Pregunta_2, Respuesta_2,
450 Pregunta_3, Respuesta_3,
451 Pregunta_4, Respuesta_4,
452 Pregunta_5, Respuesta_5,
453 Motivo_1, Motivo_2, Motivo_3, Motivo_4, Motivo_5
454from Tb_DWH
455order by IdOficina, Fecha, H_Emi
456
457truncate table Tb_DWH
458drop table #DWH_Ate
459drop table #DWH_Mot
460drop table #preguntas
461
462update Batch
463set fProc = 1
464where IdBat = @idb
465go