Histórico da Página

HTML
<center><font size="5000">CONTROLE DE ERROS ABERTOS</font></center>

Gráfico

bgColor	#222222
orientation	vertical
dataOrientation	horizontal
borderColor	#DDDDDD
height	400
colors	#d6561f,#769810,#FFD700,#cc50cd,#4382b5
width	1200
showShapes	false
type	line

Excerpt

SQL

dataSource	SIA

SELECT 
       CAST('Meta' AS VARCHAR(15)) AS "Período",
       0 AS "0",
       15 as "1",
       30 as "2",
       45 as "3",
       60 as "4",
       75 as "5",
       90 as "6",
       105 as "7",
       120 as "8",
       135 as "9",
       150 as "10",
       165 as "11",
       180 as "12",
       195 as "13"
UNION
SELECT 
       CAST('1º trimestre' AS VARCHAR(15)) AS "Período",
       0 AS "0",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) = 1 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 1 then 1 else 0 end), 0) as "1",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 2 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 2 then 1 else 0 end), 0) as "2",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 3 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 3 then 1 else 0 end), 0) as "3",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 4 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 4 then 1 else 0 end), 0) as "4",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 5 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 5 then 1 else 0 end), 0) as "5",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 6 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 6 then 1 else 0 end), 0) as "6",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 7 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 7 then 1 else 0 end), 0) as "7",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 8 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 8 then 1 else 0 end), 0) as "8",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 9 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 9 then 1 else 0 end), 0) as "9",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 10 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 10 then 1 else 0 end), 0) as "10",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 11 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 11 then 1 else 0 end), 0) as "11",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 12 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 12 then 1 else 0 end), 0) as "12",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 13 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 13 then 1 else 0 end), 0) as "13"
FROM esiaprocesso p
LEFT JOIN esiaCategoriaEvento c ON p.cdCategoriaevento = c.cdCategoriaEvento
WHERE date_part('year', p.dtInicio) = 2018
and cast(EXTRACT(WEEK FROM P.dtInicio) as integer) between 1 AND 13
and c.cdCategoriaEvento = 2
and p.cdTipoPendencia = 1
and p.cdEntidade <> 526
and p.cdProduto in (3,10,12,4,8,16,21)
UNION
SELECT 
       CAST('2º trimestre' AS VARCHAR(15)) AS "Período",
       0 AS "0",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) = 14 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 14 then 1 else 0 end), 0) as "1",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 15 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 15 then 1 else 0 end), 0) as "2",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 16 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 16 then 1 else 0 end), 0) as "3",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 17 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 17 then 1 else 0 end), 0) as "4",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 18 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 18 then 1 else 0 end), 0) as "5",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 19 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 19 then 1 else 0 end), 0) as "6",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 20 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 20 then 1 else 0 end), 0) as "7",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 21 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 21 then 1 else 0 end), 0) as "8",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 22 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 22 then 1 else 0 end), 0) as "9",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 23 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 23 then 1 else 0 end), 0) as "10",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 24 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 24 then 1 else 0 end), 0) as "11",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 25 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 25 then 1 else 0 end), 0) as "12",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 26 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 26 then 1 else 0 end), 0) as "13"
FROM esiaprocesso p
LEFT JOIN esiaCategoriaEvento c ON p.cdCategoriaevento = c.cdCategoriaEvento
WHERE date_part('year', p.dtInicio) = 2018
and cast(EXTRACT(WEEK FROM P.dtInicio) as integer) between 14 AND 26
and c.cdCategoriaEvento = 2
and p.cdTipoPendencia = 1
and p.cdEntidade <> 526
and p.cdProduto in (3,10,12,4,8,16,21)
UNION
SELECT 
       CAST('3º trimestre' AS VARCHAR(15)) AS "Período",
       0 AS "0",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) = 27 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 27 then 1 else 0 end), 0) as "1",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 28 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 28 then 1 else 0 end), 0) as "2",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 29 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 29 then 1 else 0 end), 0) as "3",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 30 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 30 then 1 else 0 end), 0) as "4",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 31 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 31 then 1 else 0 end), 0) as "5",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 32 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 32 then 1 else 0 end), 0) as "6",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 33 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 33 then 1 else 0 end), 0) as "7",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 34 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 34 then 1 else 0 end), 0) as "8",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 35 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 35 then 1 else 0 end), 0) as "9",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 36 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 36 then 1 else 0 end), 0) as "10",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 37 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 37 then 1 else 0 end), 0) as "11",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 38 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 38 then 1 else 0 end), 0) as "12",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 39 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 39 then 1 else 0 end), 0) as "13"
FROM esiaprocesso p
LEFT JOIN esiaCategoriaEvento c ON p.cdCategoriaevento = c.cdCategoriaEvento
WHERE date_part('year', p.dtInicio) = 2018
and (cast(EXTRACT(WEEK FROM P.dtInicio) as integer) between 27 AND 39)
and c.cdCategoriaEvento = 2
and p.cdTipoPendencia = 1
and p.cdEntidade <> 526
and p.cdProduto in (3,10,12,4,8,16,21)
UNION
SELECT 
       CAST('4º trimestre' AS VARCHAR(15)) AS "Período",
       0 AS "0",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) = 40 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 40 then 1 else 0 end), 0) as "1",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 41 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 41 then 1 else 0 end), 0) as "2",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 42 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 42 then 1 else 0 end), 0) as "3",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 43 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 43 then 1 else 0 end), 0) as "4",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 44 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 44 then 1 else 0 end), 0) as "5",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 45 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 45 then 1 else 0 end), 0) as "6",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 46 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 46 then 1 else 0 end), 0) as "7",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 47 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 47 then 1 else 0 end), 0) as "8",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 48 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 48 then 1 else 0 end), 0) as "9",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 49 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 49 then 1 else 0 end), 0) as "10",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 50 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 50 then 1 else 0 end), 0) as "11",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 51 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 51 then 1 else 0 end), 0) as "12",
       NULLIF(SUM(Case when cast(EXTRACT(WEEK FROM P.dtInicio) as integer) <= 52 and cast(EXTRACT(WEEK FROM current_date) as integer) >= 52 then 1 else 0 end), 0) as "13"
FROM esiaprocesso p
LEFT JOIN esiaCategoriaEvento c ON p.cdCategoriaevento = c.cdCategoriaEvento
WHERE date_part('year', p.dtInicio) = 2018
and (cast(EXTRACT(WEEK FROM P.dtInicio) as integer) between 40 AND 52)
and c.cdCategoriaEvento = 2
and p.cdTipoPendencia = 1
and p.cdEntidade <> 526
and p.cdProduto in (3,10,12,4,8,16,21)
ORDER BY 1

Gráfico

bgColor	#222222
orientation	vertical
subTitle	.
dataOrientation	vertical
borderColor	#DDDDDD
height	400
legend	false
width	1200
type	line

Excerpt

SQL

dataSource	SIA

SELECT
(ARRAY['Jan', 'Fev', 'Mar', 'Abr', 'Mai', 'Jun', 'Jul', 'Ago', 'Set', 'Out', 'Nov', 'Dez']) [date_part('MONTH', p.dtInicio)],
SUM(Case when EXTRACT(MONTH FROM p.dtInicio) = EXTRACT(MONTH FROM P.dtInicio) then 1 else 0 end) AS "Quantidade"
FROM esiaprocesso p
LEFT JOIN esiaCategoriaEvento c ON p.cdCategoriaevento = c.cdCategoriaEvento
WHERE date_part('year', p.dtInicio) = 2018
AND c.cdCategoriaEvento = 2
AND p.cdTipoPendencia = 1
AND p.cdEntidade <> 526
AND p.cdProduto in (3,10,12,4,8,16,21)
GROUP BY date_part('MONTH', p.dtInicio)
ORDER BY date_part('MONTH', p.dtInicio)

HTML
<center><font size="2500">Número de erros que entraram:</font></center>

SQL

dataSource	SIA
style	background-color:black;color:yellow; font-weight:bold;font-size:3.8em;
rowOrientation	horizontal
retainRowStyleOrder	false

SELECT
SUM(CASE WHEN current_date = P.dtInicio then 1 else 0 end) as "Hoje",
SUM(Case when cast(EXTRACT(WEEK FROM current_date) as integer) = cast(EXTRACT(WEEK FROM P.dtInicio) as integer) then 1 else 0 end) as "Semana ",
SUM(Case when EXTRACT(MONTH FROM current_date) = EXTRACT(MONTH FROM P.dtInicio) then 1 else 0 end) as "Mês   ",
COUNT(*) as "Trimestre"
FROM esiaprocesso p
LEFT JOIN esiaCategoriaEvento c ON p.cdCategoriaevento = c.cdCategoriaEvento
WHERE date_part('year',p.dtInicio) >= 2018
and date_part('MONTH',p.dtInicio) >= 7
and c.cdCategoriaEvento = 2
and p.cdTipoPendencia = 1
and p.cdEntidade <> 526
and p.cdProduto in (3,10,12,4,8,16,21)

UI Text Box

type	tip
size	large

SQL

dataSource	SIA
table	false

SELECT
' Estamos ' || current_date - max(dtInicio) || ' dias sem erros'
FROM esiaprocesso p
LEFT JOIN esiaCategoriaEvento c ON p.cdCategoriaevento = c.cdCategoriaEvento
WHERE date_part('year', p.dtInicio) = 2018
AND c.cdCategoriaEvento = 2
AND p.cdTipoPendencia = 1
AND p.cdEntidade <> 526
AND p.cdProduto in (3,10,12,4,8,16,21)

Árvore de páginas

Versões comparadas

Old Version 86

Nova versão 87

Chave