Untitled

/* ========== PARÂMETROS ========== */
WITH params AS (
        SELECT  'ARICANDUVA-FORMOSA-CARRAO'::VARCHAR AS filtro_prefeitura
                , 2017::INTEGER AS filtro_ano
                , 07::INTEGER AS filtro_mes
)
, prefeituras_orgaos AS (
        SELECT  prefeitura
                , id_orgaos
        FROM (
                SELECT  'ARICANDUVA-FORMOSA-CARRAO'::VARCHAR AS prefeitura
                        , array[779,682,810,715,747,842,962,1155] AS id_orgaos
                UNION
                SELECT  'BUTANTA'::VARCHAR AS prefeitura
                        , array[843,683,780,811,748,716,962,1155] AS id_orgaos
                UNION
                SELECT  'CAMPO LIMPO'::VARCHAR AS prefeitura
                        , array[717,684,812,781,844,749,962,1155] AS id_orgaos
                UNION
                SELECT  'CAPELA DO SOCORRO'::VARCHAR AS prefeitura
                        , array[813,750,845,718,685,782,962,1155] AS id_orgaos
                UNION
                SELECT  'CASA VERDE-CACHOEIRINHA'::VARCHAR AS prefeitura
                        , array[719,751,783,846,814,686,962,1155] AS id_orgaos
                UNION
                SELECT  'CIDADE ADEMAR'::VARCHAR AS prefeitura
                        , array[752,687,784,815,847,720,962,1155] AS id_orgaos
                UNION
                SELECT  'CIDADE TIRADENTES'::VARCHAR AS prefeitura
                        , array[688,721,785,816,848,753,962,1155] AS id_orgaos
                UNION
                SELECT  'ERMELINO MATARAZZO'::VARCHAR AS prefeitura
                        , array[689,722,754,786,817,849,962,1155] AS id_orgaos
                UNION
                SELECT  'FREGUESIA-BRASILANDIA'::VARCHAR AS prefeitura
                        , array[723,850,755,787,690,818,962,1155] AS id_orgaos
                UNION
                SELECT  'GUAIANASES'::VARCHAR AS prefeitura
                        , array[691,851,724,819,788,756,962,1155] AS id_orgaos
                UNION
                SELECT  'IPIRANGA'::VARCHAR AS prefeitura
                        , array[820,692,852,757,725,710,962,1155] AS id_orgaos
                UNION
                SELECT  'ITAIM PAULISTA'::VARCHAR AS prefeitura
                        , array[821,726,758,789,693,853,962,1155] AS id_orgaos
                UNION
                SELECT  'ITAQUERA'::VARCHAR AS prefeitura
                        , array[759,854,727,822,694,790,962,1155] AS id_orgaos
                UNION
                SELECT  'JABAQUARA'::VARCHAR AS prefeitura
                        , array[855,728,695,823,760,791,962,1155] AS id_orgaos
                UNION
                SELECT  'JACANA-TREMEMBE'::VARCHAR AS prefeitura
                        , array[824,856,761,729,792,696,962,1155] AS id_orgaos
                UNION
                SELECT  'LAPA'::VARCHAR AS prefeitura
                        , array[697,793,825,857,762,730,962,1155] AS id_orgaos
                UNION
                SELECT  'MBOI MIRIM'::VARCHAR AS prefeitura
                        , array[698,794,826,763,731,858,962,1155] AS id_orgaos
                UNION
                SELECT  'MOOCA'::VARCHAR AS prefeitura
                        , array[795,699,827,732,764,859,962,1155] AS id_orgaos
                UNION
                SELECT  'PARELHEIROS'::VARCHAR AS prefeitura
                        , array[733,700,765,796,828,860,962,1155] AS id_orgaos
                UNION
                SELECT  'PENHA'::VARCHAR AS prefeitura
                        , array[861,797,734,829,766,701,962,1155] AS id_orgaos
                UNION
                SELECT  'PERUS'::VARCHAR AS prefeitura
                        , array[830,702,798,735,862,767,962,1155] AS id_orgaos
                UNION
                SELECT  'PINHEIROS'::VARCHAR AS prefeitura
                        , array[799,768,736,831,703,863,962,1155] AS id_orgaos
                UNION
                SELECT  'PIRITUBA-JARAGUA'::VARCHAR AS prefeitura
                        , array[737,800,832,864,769,704,962,1155] AS id_orgaos
                UNION
                SELECT  'SANTANA-TUCURUVI'::VARCHAR AS prefeitura
                        , array[801,865,738,833,705,770,962,1155] AS id_orgaos
                UNION
                SELECT  'SANTO AMARO'::VARCHAR AS prefeitura
                        , array[706,834,802,739,866,771,962,1155] AS id_orgaos
                UNION
                SELECT  'SAO MATEUS'::VARCHAR AS prefeitura
                        , array[707,803,835,740,867,772,962,1155] AS id_orgaos
                UNION
                SELECT  'SAO MIGUEL'::VARCHAR AS prefeitura
                        , array[868,708,804,773,741,836,962,1155] AS id_orgaos
                UNION
                SELECT  'SAPOPEMBA'::VARCHAR AS prefeitura
                        , array[709,837,742,869,774,805,962,1155] AS id_orgaos
                UNION
                SELECT  'SE'::VARCHAR AS prefeitura
                        , array[806,775,838,870,743,711,962,1155] AS id_orgaos
                UNION
                SELECT  'VILA MARIANA'::VARCHAR AS prefeitura
                        , array[872,713,840,777,808,745,962,1155] AS id_orgaos
                UNION
                SELECT  'VILA MARIA-VILA GUILHERME'::VARCHAR AS prefeitura
                        , array[712,871,807,744,776,839,962,1155] AS id_orgaos
                UNION
                SELECT  'VILA PRUDENTE'::VARCHAR AS prefeitura
                        , array[873,841,746,809,778,714,962,1155] AS id_orgaos
        ) prefeitura_orgaos
        WHERE prefeitura = (SELECT params.filtro_prefeitura FROM params)
)
, solicitacoes AS (
        SELECT  id_notificacao_solicitacao
                , id_canal_de_entrada
                , nome_canal_de_entrada
                , id_orgao
                , distrito
                , arvore_conhecimento[3] AS int_servico
                , (
                        SELECT UPPER(tb057.de_dado_solicitacao)
                        FROM tb057_dado_solicitacao_internacionaliza tb057
                        WHERE tb057.id_dado_solicitacao = arvore_conhecimento[3]
                ) AS str_servico
                , arvore_conhecimento[2] AS int_assunto
                , (
                        SELECT UPPER(tb048.de_secao_desc)
                        FROM tb048_secao_internacionaliza tb048
                        WHERE tb048.id_secao = arvore_conhecimento[2]
                ) AS str_assunto
                , arvore_conhecimento[1] AS int_tema
                , (
                        SELECT UPPER(tb048.de_secao_desc)
                        FROM tb048_secao_internacionaliza tb048
                        WHERE tb048.id_secao = arvore_conhecimento[1]
                ) AS str_tema
                , st_status
                , dt_cadastro
                , st_historico
                , dt_historico
        FROM (
                SELECT  DISTINCT
                        tb017.id_notificacao_solicitacao
                        , (
                                CASE WHEN tb017.id_meio_comunicacao = 2 THEN 1
                                WHEN tb017.id_meio_comunicacao = 4 THEN 2
                                WHEN tb017.id_meio_comunicacao = 6 AND tb005_alteracao.id_usuario IS NOT NULL AND tb005_alteracao.de_login ~* '^(x|ogm|d|l|L|OGM)' THEN 3
                                WHEN tb017.id_meio_comunicacao = 10 THEN 4
                                WHEN tb017.id_meio_comunicacao = 13 THEN 5
                                WHEN tb017.id_meio_comunicacao = 6 AND tb005_alteracao.id_usuario IS NOT NULL AND tb005_alteracao.de_login IN ('SAC/GRC','SIGRC') THEN 6
                                ELSE 7
                                END
                        ) AS id_canal_de_entrada
                        , (
                                CASE WHEN tb017.id_meio_comunicacao = 2 THEN 'PORTAL'
                                WHEN tb017.id_meio_comunicacao = 4 THEN 'APLICATIVO MOBILE'
                                WHEN tb017.id_meio_comunicacao = 6 AND tb005_alteracao.id_usuario IS NOT NULL AND tb005_alteracao.de_login ~* '^(x|ogm|d|l|L|OGM)' THEN 'PRAÇA DE ATENDIMENTO'
                                WHEN tb017.id_meio_comunicacao = 10 THEN 'E-MAIL'
                                WHEN tb017.id_meio_comunicacao = 13 THEN 'INTEGRAÇÃO'
                                WHEN tb017.id_meio_comunicacao = 6 AND tb005_alteracao.id_usuario IS NOT NULL AND tb005_alteracao.de_login IN ('SAC/GRC','SIGRC') THEN 'ROTINA AUTOMATIZADA'
                                ELSE 'CENTRAL 156'
                                END
                        ) AS nome_canal_de_entrada
                        , tb017.id_orgao
                        , UPPER(tb018.de_distrito) AS distrito
                        , (
                                CASE WHEN (tb017.de_historico_navegacao is null) THEN
                                        (
                                                SELECT  array[
                                                        (SELECT tr001.id_secao_secao FROM tr001_secao_secao tr001 WHERE tr001.id_secao_2 = dado_solicitacao.id_secao ORDER BY tr001.dt_cadastro LIMIT 1)
                                                        ,dado_solicitacao.id_secao
                                                        ,solicitacao.id_dado_solicitacao
                                                        ]
                                                FROM tb017_notificacao_solicitacao solicitacao
                                                JOIN tb056_dado_solicitacao dado_solicitacao ON dado_solicitacao.id_dado_solicitacao = solicitacao.id_dado_solicitacao
                                                WHERE solicitacao.id_notificacao_solicitacao = tb017.id_notificacao_solicitacao
                                        )
                                ELSE
                                        array_append(cast(string_to_array(tb017.de_historico_navegacao, ',') as integer[]), tb056.id_dado_solicitacao::INTEGER)
                                END
                        ) AS arvore_conhecimento
                        , tb017.st_status
                        , tb017.dt_cadastro
                        , COALESCE(th001.id_status_notificacao_solicitacao_atual, 85) AS st_historico
                        , COALESCE(th001.dt_alteracao, (DATE_TRUNC('day', (CURRENT_TIMESTAMP - INTERVAL '1 day')) - INTERVAL '1 second')) AS dt_historico
                        , row_number() OVER (PARTITION BY tb017.id_notificacao_solicitacao ORDER BY th001.id_notificacao_solicitacao_status DESC)
                FROM params
                JOIN tb017_notificacao_solicitacao tb017
                        ON tb017.dt_cadastro::DATE <= (
                                CASE WHEN ((CAST(((CAST(params.filtro_ano || '-' || params.filtro_mes || '-01' AS DATE) + INTERVAL '1 month') - INTERVAL '1 second') AS DATE)) >= CURRENT_DATE) THEN
                                        CAST(CURRENT_DATE - INTERVAL '2 day' AS DATE)
                                ELSE
                                        CAST(((CAST(params.filtro_ano || '-' || params.filtro_mes || '-01' AS DATE) + INTERVAL '1 month') - INTERVAL '1 second') AS DATE)
                                END
                        )
                        AND tb017.id_orgao = ANY(CAST((SELECT prefeituras_orgaos.id_orgaos FROM prefeituras_orgaos) AS INTEGER[]))
                        AND tb017.st_status != 4
                JOIN tb018_notificacao_solicitacao_endereco tb018
                        ON tb018.id_notificacao_solicitacao_endereco = tb017.id_notificacao_solicitacao_endereco
                        AND to_tsvector(UPPER(TRANSLATE(REPLACE(tb018.de_prefeitura, '''', ''),'àáâãäÀÁÂÃÄèéêëÈÉÊËòóôõöÒÓÔÕÖùúûüÙÚÛÜçÇ','aaaaaaaaaaeeeeeeeeoooooooooouuuuuuuucc'))) =
                        to_tsvector(params.filtro_prefeitura)
                JOIN tb056_dado_solicitacao tb056
                        ON tb056.id_dado_solicitacao = tb017.id_dado_solicitacao
                LEFT JOIN th001_notificacao_solicitacao_status th001
                        ON th001.id_notificacao_solicitacao = tb017.id_notificacao_solicitacao
                        AND th001.dt_alteracao::DATE <= (
                                CASE WHEN ((CAST(((CAST(params.filtro_ano || '-' || params.filtro_mes || '-01' AS DATE) + INTERVAL '1 month') - INTERVAL '1 second') AS DATE)) >= CURRENT_DATE) THEN
                                        CAST(CURRENT_DATE - INTERVAL '2 day' AS DATE)
                                ELSE
                                        CAST(((CAST(params.filtro_ano || '-' || params.filtro_mes || '-01' AS DATE) + INTERVAL '1 month') - INTERVAL '1 second') AS DATE)
                                END
                        )
                LEFT JOIN tb005_usuario tb005_alteracao
                        ON tb005_alteracao.id_usuario = th001.id_usuario_alteracao
        ) solicitacoes
        WHERE row_number = 1
)
, servicos_mais_solicitados AS (
        WITH recebidas AS (
                SELECT
                         UPPER(TRIM(str_servico)) AS servico
                        , COUNT(DISTINCT id_notificacao_solicitacao) AS qtd
                FROM solicitacoes
                WHERE dt_cadastro::DATE BETWEEN (CAST(((SELECT params.filtro_ano FROM params) || '-' || (SELECT params.filtro_mes FROM params) || '-01') AS DATE))
                AND (
                        CASE WHEN ((((CAST(((SELECT params.filtro_ano FROM params) || '-' || (SELECT params.filtro_mes FROM params) || '-01') AS DATE) + INTERVAL '1 month') - INTERVAL '1 second')::DATE) >= CURRENT_DATE) THEN
                                (CAST(CURRENT_DATE - INTERVAL '2 day' AS DATE))
                        ELSE
                                (((CAST(((SELECT params.filtro_ano FROM params) || '-' || (SELECT params.filtro_mes FROM params) || '-01') AS DATE) + INTERVAL '1 month') - INTERVAL '1 second')::DATE)
                        END
                )
                GROUP BY (UPPER(TRIM(str_servico)))
        )
        , concluidas AS (
                SELECT
                                                UPPER(TRIM(str_servico)) AS servico
                        , COUNT(DISTINCT id_notificacao_solicitacao) AS qtd
                        , AVG (EXTRACT(epoch FROM dt_historico) - EXTRACT(epoch FROM dt_cadastro)) AS tma
                FROM solicitacoes
                WHERE dt_historico::DATE BETWEEN (CAST(((SELECT params.filtro_ano FROM params) || '-' || (SELECT params.filtro_mes FROM params) || '-01') AS DATE))
                AND (
                        CASE WHEN ((((CAST(((SELECT params.filtro_ano FROM params) || '-' || (SELECT params.filtro_mes FROM params) || '-01') AS DATE) + INTERVAL '1 month') - INTERVAL '1 second')::DATE) >= CURRENT_DATE) THEN
                                (CAST(CURRENT_DATE - INTERVAL '2 day' AS DATE))
                        ELSE
                                (((CAST(((SELECT params.filtro_ano FROM params) || '-' || (SELECT params.filtro_mes FROM params) || '-01') AS DATE) + INTERVAL '1 month') - INTERVAL '1 second')::DATE)
                        END
                )
                AND st_historico = ANY(ARRAY[19,20,36])
                GROUP BY (UPPER(TRIM(str_servico)))
        )
        SELECT
                 recebidas.servico
                , COALESCE(recebidas.qtd,0) AS qtd_recebidas
                , COALESCE(concluidas.qtd,0) AS qtd_concluidas
                , COALESCE(concluidas.tma,0::DOUBLE PRECISION) AS tma
        FROM recebidas
        LEFT JOIN concluidas
                ON concluidas.servico = recebidas.servico
        GROUP BY 1, 2, 3, 4
), evolucao_5_servicos AS (
        WITH linha_do_tempo AS (
                SELECT  EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia)) AS nu_ano
                        , EXTRACT(MONTH FROM DATE_TRUNC('month', dt_range.data_referencia)) AS nu_mes
                        , (
                                CASE EXTRACT(MONTH FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 1 THEN 'JANEIRO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 2 THEN 'FEVEREIRO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 3 THEN 'MARÇO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 4 THEN 'ABRIL DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 5 THEN 'MAIO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 6 THEN 'JUNHO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 7 THEN 'JULHO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 8 THEN 'AGOSTO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 9 THEN 'SETEMBRO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 10 THEN 'OUTUBRO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 11 THEN 'NOVEMBRO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                WHEN 12 THEN 'DEZEMBRO DE ' || EXTRACT(YEAR FROM DATE_TRUNC('month', dt_range.data_referencia))
                                END
                        ) AS referencia
                FROM (
                        SELECT generate_series(
                                (CAST(params.filtro_ano || '-' || params.filtro_mes || '-01' AS DATE)),
                                (CAST(params.filtro_ano || '-' || params.filtro_mes || '-01' AS DATE) - INTERVAL '1 year'),
                                '- 1 month'
                        ) AS data_referencia
                        FROM params
                ) dt_range
        )
        , top_5_servicos AS (
                SELECT array_agg(servico) AS servico
                FROM (
                        SELECT  servico
                        FROM servicos_mais_solicitados
                        ORDER BY qtd_recebidas DESC LIMIT 5
                ) top
        )
        , servicos AS (
                SELECT  linha_do_tempo.referencia
                        , linha_do_tempo.nu_ano
                        , linha_do_tempo.nu_mes
                        , str_servico
                        , COUNT(DISTINCT id_notificacao_solicitacao) AS qtd
                FROM linha_do_tempo
                JOIN solicitacoes
                        ON dt_cadastro::DATE BETWEEN
                        CAST(linha_do_tempo.nu_ano || '-' || linha_do_tempo.nu_mes || '-01' AS DATE)
                            AND
                        CASE WHEN
                            ((CAST(linha_do_tempo.nu_ano || '-' || linha_do_tempo.nu_mes || '-01' AS DATE) + INTERVAL '1 MONTH') - INTERVAL '1 SEC')::DATE
                             > CURRENT_DATE
                            THEN
                                CURRENT_DATE
                            ELSE
                                ((CAST(linha_do_tempo.nu_ano || '-' || linha_do_tempo.nu_mes || '-01' AS DATE) + INTERVAL '1 MONTH') - INTERVAL '1 SEC')::DATE
                            END
                WHERE UPPER(TRIM(str_servico)) = ANY ((SELECT servico FROM top_5_servicos)::VARCHAR[])
                GROUP BY 1, 2, 3, 4
        )
        SELECT  referencia
                 , nu_ano
                 , nu_mes
                , str_servico
                , qtd
        FROM servicos
        ORDER BY 1 ASC, 5 DESC
)
select initcap(lower(evolucao_5_servicos.str_servico)) as servico
     , evolucao_5_servicos.nu_mes as mes
     , evolucao_5_servicos.nu_ano as ano
     , evolucao_5_servicos.referencia as data
     , evolucao_5_servicos.qtd as abertas
from evolucao_5_servicos
ORDER BY
    evolucao_5_servicos.nu_ano,
    evolucao_5_servicos.nu_mes