· 6 years ago · Jun 20, 2019, 07:24 PM
1/*****************************************
2 * Reporte : GNA - Detalle de Ejecutivo *
3 *****************************************/
4if exists (select * from sysobjects where id=object_id(N'[dbo].[sp_Rep_GNA_EjeDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
5drop procedure [dbo].[sp_Rep_GNA_EjeDet]
6go
7
8CREATE PROCEDURE sp_Rep_GNA_EjeDet (
9 @ideje int,
10 @sDI char(10), -- dd/MM/yyyy
11 @sDF char(10) -- dd/MM/yyyy
12)
13as
14
15declare @diaI smalldatetime
16declare @diaF smalldatetime
17declare @idx int
18declare @QAteT int
19declare @TMax int
20declare @sidO varchar(7)
21declare @idE int
22declare @idE_ int
23declare @eve char(1)
24declare @eveIF char(1)
25declare @eveAP char(1)
26declare @fheve datetime
27declare @fheve_ datetime
28declare @fheIF datetime
29declare @fheAP datetime
30declare @ttot int
31declare @ton int
32declare @tpau int
33declare @idpau smallint
34declare @FHCompEve datetime
35declare @FHCompFinAte datetime
36
37
38select @diaI = convert(smalldatetime, @sDI, 103),
39 @diaF = convert(smalldatetime, @sDF, 103) + 1
40
41
42-- Creo Tablas Temporales----------------------------------------------------------------------------
43CREATE TABLE #REP (
44 idx int,
45 IdE int,
46 IdS int,
47 QAte int,
48 TAteP int,
49 TpoAteT int,
50 TpoPau int,
51 TpoOff int,
52 TpoOn int,
53 TpoDisc int,
54 TpoDiscP int,
55 TEspP int,
56 FHAteIni datetime,
57 FHAteFin datetime,
58 dia date
59)
60
61
62CREATE TABLE #PAUREP (
63 FHPau datetime,
64 idPau smallint
65)
66
67
68CREATE TABLE #REPOUT (
69 idx int,
70 IdE int,
71 QAte int,
72 TAteP char(8),
73 TpoAteT int,
74 TpoPau int,
75 TpoOff int,
76 TpoOn int,
77 TpoDisc int,
78 FHAteIni datetime,
79 FHAteFin datetime,
80 dia date
81)
82
83
84
85
86
87while(@diaI < @diaF)
88begin
89 delete Rep_OS where Sesion = @@spid
90 select @idE_ = 0, @ttot = datediff(s, @diaI, dateadd(d, 1, @diaI))
91
92 exec sp_Rep_SetOS 1, '*', '*' -- llenado de IdO y IdS
93
94 -- Inserto datos temp ------------------------------------------------------------------------
95 insert into #REP
96 ( idx, IdE, QAte, TAteP, TEspP, TpoAteT ,dia)
97 select 1,
98 IdEje,
99 count(*),
100 avg(TpoAte),
101 avg(TpoEsp),
102 sum(TpoAte),
103 @diai
104 from Atenciones A, Rep_OS S
105 where A.IdOficina = S.IdOficina
106 and A.IdSerie = S.IdSerie
107 and TpoAte >= tMinAte
108 and Perdido = 0
109 and Sesion = @@spid
110 and FH_AteIni between @diaI and dateadd(d, 1, @diaI)
111 and IdEje = @ideje
112 group by IdEje
113
114 --select * from #rep order by ide, ids
115
116
117
118 -- agrego faltantes ------------------------------------------------------------------------------------
119 insert into #REP
120 ( idx, IdE, IdS, QAte, TAteP, TEspP )
121 select 1, IdE, IdS, null, null, null
122 from (select distinct a.IdE, b.IdS from
123 (select distinct IdE, null IdS from #REP) a,
124 (select distinct null IdE, IdS from #REP) b) x
125 where (cast(IdE as varchar) + '00' + cast(IdS as varchar)) not in
126 (select cast(IdE as varchar) + '00' + cast(IdS as varchar) from #REP)
127
128 select @QAteT = sum(QAte)
129 from #REP
130 where dia = @diaI
131
132
133 ------- INI Tiempos Eje
134 declare curEje
135 Insensitive
136 Cursor For
137 select IdEje,
138 Evento,
139 FH_Eve
140 from EjeEstado
141 where FH_Eve between @diaI and dateadd(d, 1, @diaI)
142 and IdEje = @ideje
143 order by IdEje, FH_Eve
144
145 open curEje
146 while (10 = 10)
147 begin
148 fetch next from curEje into @idE, @eve, @fheve
149 if ( @@fetch_status <> 0 ) break
150 if (@idE_ <> @idE)
151 begin
152 if (@idE_ <> 0)
153 begin
154 --ajuste final
155 if (@eveIF = 'I') set @ton = @ton + datediff(s, @fheIF, @fheve_)
156 if (@eveAP = 'P') set @tpau = @tpau + datediff(s, @fheAP, @fheve_)
157
158 if exists(select * from #REP where IdE = @idE_)
159 update #REP
160 set TpoOn = @ton,
161 TpoPau = @tpau
162 where dia = @diaI
163
164 end
165 select @idE_ = @idE,
166 @ton = 0,
167 @tpau = 0,
168 @eveIF = 'F',
169 @fheIF = @diaI,
170 @eveAP = ' '
171 end
172 else if (@fheve_ <= @fheve)
173 begin
174 -- cambio de dia ==> ajuste
175
176 if (@eveIF = 'I')
177 select @ton = @ton + datediff(s, @fheIF, @fheve_),
178 @eveIF = 'F',
179 @fheIF = @fheve_
180
181 if (@eveAP = 'P')
182 select @tpau = @tpau + datediff(s, @fheAP, @fheve_)
183
184 select @eveAP = ' '
185 end
186
187 if (@eve = 'I')
188 begin
189 if (@eveIF = 'F')
190 select @eveIF = 'I',
191 @fheIF = @fheve,
192 @eveAP = 'P',
193 @fheAP = @fheve
194 end
195
196 if (@eve = 'F')
197 begin
198 if (@eveIF = 'I')
199 select @ton = @ton + datediff(s, @fheIF, @fheve),
200 @eveIF = 'F',
201 @fheIF = @fheve
202
203 if (@eveAP = 'P')
204 select @tpau = @tpau + datediff(s, @fheAP, @fheve)
205
206 select @eveAP = ' '
207 end
208
209 if (@eve = 'A')
210 begin
211 if (@eveAP = 'P')
212 begin
213 select @tpau = @tpau + datediff(s, @fheAP, @fheve),
214 @eveAP = 'A',
215 @fheAP = @fheve
216
217 end
218 end
219
220 if (@eve = 'P')
221 begin
222 if (@eveAP = 'A')
223 select @eveAP = 'P',
224 @fheAP = @fheve
225 end
226
227 select @fheve_ = convert(smalldatetime, convert(char(10), @fheve, 120), 120) + 1
228 end
229 close curEje
230 deallocate curEje
231
232 if (@idE_ = @idE and @idE_ <> 0)
233 begin
234 --ajuste final
235 if (@eveIF = 'I') set @ton = @ton + datediff(s, @fheIF, @fheve_)
236 if (@eveAP = 'P') set @tpau = @tpau + datediff(s, @fheAP, @fheve_)
237
238 if exists(select * from #REP where Ide = @idE_)
239 update #REP
240 set TpoOn = @ton,
241 TpoOff = @tpau,
242 TpoPau = @tpau
243 where dia = @diaI
244 end
245
246 -- ajuste
247 update #REP
248 set TpoDisc = case when TpoOn-(TpoAteT+TpoPau) < 0 then null else TpoOn-(TpoAteT+TpoPau) end
249 where dia = @diaI
250
251
252 update #REP
253 set TpoDiscP = isnull((select avg(TpoDisc) from #REP), 0)
254 from #REP E
255 where TpoDiscP is null
256 and dia = @diaI
257
258 update #REP
259 set TpoOn = case when (TpoAteT+TpoPau+TpoDiscP) > @ttot then @ttot else (TpoAteT+TpoPau+TpoDiscP) end
260 from #REP E
261 where TpoDisc is null
262 and dia = @diaI
263
264 update #REP
265 set TpoOff = @ttot - TpoOn
266 where dia = @diaI
267
268 ------- FIN Tiempos Eje
269
270
271 -- Ini Calculo ini y fin atencion
272
273
274 select @FHCompEve = ( select top 1 FH_Eve
275 from EjeEstado
276 where FH_Eve between @diaI and DATEADD(d,1,@diaI)
277 and IdEje = @ideje
278 order by FH_Eve desc)
279
280
281
282 select @FHCompFinAte = (select top 1 FH_AteFin
283 from Atenciones
284 where FH_Emi between @diaI and DATEADD(d,1,@diaI)
285 and IdEje = @ideje
286 order by FH_AteFin desc)
287
288 update #REP
289 set FHAteIni = Y.FH_Eve,
290 FHAteFin = case when @FHCompEve >= @FHCompFinAte then @FHCompEve else @FHCompFinAte end
291 from ( select top 1 FH_Eve
292 from EjeEstado
293 where FH_Eve between @diaI and DATEADD(d,1,@diaI)
294 and IdEje = @ideje
295 order by FH_Eve asc)Y
296 where dia = @diaI
297
298
299 ---- Fin Calculo Ini y Fin
300
301
302 ----- Agrego Resumen y Motivos de Pausa
303
304 insert into #PAUREP ( FHPau, idPau)
305 select FH_Eve, IdPausa
306 from EjeEstado
307 where FH_Eve between @diaI and DATEADD(d,1,@diaI)
308 and IdEje = @ideje
309 and Evento = 'P'
310
311
312
313 -----
314
315 --Agrego a reporte data completa
316 insert into #REPOUT
317 (idx, IdE, QAte, TAteP, dia, TpoAteT, TpoPau, TpoOff, TpoOn, TpoDisc, FHAteIni, FHAteFin)
318
319 select 1,
320 IdE,
321 sum(QAte) QateS,
322 convert(char(8), isnull(dateadd(ss, TAteP,0), 0), 108),
323 dia,
324 TpoAteT,
325 TpoPau,
326 TpoOff,
327 TpoOn,
328 TpoDisc,
329 FHAteIni,
330 FHAteFin
331 from #REP
332 where dia = @diaI
333 group by IdE, dia, TAteP, TpoAteT, TpoPau, TpoOff, TpoOn, TpoDisc, FHAteIni, FHAteFin
334 order by QAteS desc
335
336 select @diaI = DATEADD(d,1,@diaI)
337end
338-- Obtener datos por ejecutivo
339
340
341
342
343
344insert into #REPOUT
345 (idx, IdE, QAte, TAteP, dia, TpoAteT, TpoPau, TpoOff, TpoOn, TpoDisc, FHAteIni, FHAteFin)
346select 2,
347 0,
348 0,
349 convert(char(8), isnull(dateadd(ss, sum(TAteP) / count(*) ,0) , '00:00:00') ,108),
350 null, null, null,null, null,null,null,null
351from #REP
352
353
354select IdE,
355 QAte,
356 TAteP,
357 convert(char(8), isnull(dateadd(ss, TpoAteT, 0),0),108) TpoAteT,
358 convert(char(8), isnull(dateadd(ss, TpoPau, 0),0),108) TpoPau,
359 convert(char(8), isnull(dateadd(ss, TpoOff, 0),0),108) TpoOff,
360 convert(char(8), isnull(dateadd(ss, TpoOn, 0),0),108) TpoOn,
361 convert(char(8), isnull(dateadd(ss, TpoDisc, 0),0),108) TpoDisc,
362 convert(char(8), FHAteIni, 108) FHAteIni,
363 convert(char(8), FHAteFin, 108) FHAteFin,
364 convert(char(8), (FHAteFin - FHAteIni), 108) as 'Tiempo Total',
365 dia
366from #REPOUT
367
368select CONVERT(char(8), FHPau, 108) as 'Hora Pausa',
369 (select Pausa from Pausas where IdPausa = R.idpau),
370 CONVERT(date, FHPau, 103) as 'Dia'
371from #PAUREP R
372
373DROP TABLE #REP
374DROP TABLE #REPOUT
375DROP TABLE #PAUREP
376GO