<jupyter_start><jupyter_text>Indicadores do painel  1. Configura ambiente<jupyter_code># %load config.py
%reload_ext autoreload
%autoreload 2

import cx_Oracle
import os
from dotenv import load_dotenv
from copy import deepcopy
import numpy as np
import pandas as pd
pd.options.display.max_columns = 999

import plotly.express as px
import plotly
plotly.offline.init_notebook_mode(connected=True)

# Carrega credenciais
load_dotenv()

# Conecta ao banco
cx_Oracle.init_oracle_client(lib_dir= r"/Users/fernandascovino/Library/Oracle")

con = cx_Oracle.connect(
    user=os.getenv('oracle_prod_user'), 
    password=<PASSWORD>('oracle_<PASSWORD>'), 
    dsn=os.getenv('oracle_prod_dsn'),
    encoding="UTF-8")

cursor = con.cursor()<jupyter_output><empty_output><jupyter_text>2. Monta indicadores  Total de processos/expedientes criados em meio eletrônico (digital)**Fórmula de cálculo:** Contagem do número de documentos únicos (não juntados) cuja forma é do tipo Processo/Expediente criados no Processo.rio até a data**Granularidade:**- Por órgão- Por semana<jupyter_code>query = """
WITH 
dbe_tipo AS ( -- Tipo documental (Processo Administrativo / Expediente) para cada espécie (forma)
    SELECT
        dbe.id_forma_doc
        ,dbe_desc.desc_tipo_forma_doc as desc_tipo_doc
    FROM
        siga.ex_tipo_forma_documento dbe_desc
    INNER JOIN siga.ex_forma_documento dbe 
    ON dbe.id_tipo_forma_doc = dbe_desc.id_tipo_forma_doc
)
, dbo_nome AS ( -- Nome do órgão para cada ID
    SELECT 
        id_orgao_usu as id_orgao 
        ,nm_orgao_usu as nome_orgao
    FROM corporativo.cp_orgao_usuario
)
, dbd_ativos AS ( -- Documentos únicos criados
    SELECT
        to_char(trunc(dt_doc, 'IW'), 'YYYY/MM/DD') as semana_criacao
        ,id_orgao_usu as id_orgao
        ,id_forma_doc
    FROM siga.vw_ex_documento
    WHERE id_orgao_usu <> '9999999999' -- Retira ID do órgão TESTE
    AND to_char(trunc(dt_doc, 'IW'), 'YYYY/MM/DD') IS NOT NULL -- Documentos criados, não contabilizando os temporários
    AND id_doc_anterior IS NULL -- Documentos únicos, não contabilizando os juntados
    -- AND dt_doc < TIMESTAMP '2021-09-10 17:31:52.076' -- Checagem com a planilha da Márcia (última extração)
)
SELECT 
    semana_criacao,
    nome_orgao,
    desc_tipo_doc,
    COUNT(1) as total_doc
FROM 
    dbd_ativos,
    dbo_nome,
    dbe_tipo
WHERE 
    dbo_nome.id_orgao = dbd_ativos.id_orgao
    AND dbe_tipo.id_forma_doc = dbd_ativos.id_forma_doc
GROUP BY 
    nome_orgao,
    semana_criacao,
    desc_tipo_doc
"""
db_processos_expedientes = pd.read_sql(query, con=con)
db_processos_expedientes.columns = db_processos_expedientes.columns.str.lower()
db_processos_expedientes["semana_criacao"] = pd.to_datetime(db_processos_expedientes["semana_criacao"])<jupyter_output><empty_output><jupyter_text>Gráficos<jupyter_code>df = (db_processos_expedientes
     .sort_values(by="semana_criacao")
     .groupby(["desc_tipo_doc", "semana_criacao"])
     .sum()
     .groupby(level=0).cumsum().reset_index()
)

fig = px.line(
    df[df["semana_criacao"].dt.year > 2020],
    x="semana_criacao", 
    y="total_doc", 
    color="desc_tipo_doc", 
    text="total_doc",
    title="Total acumulado de Processos e Expedientes criados por semana"
)

fig.update_traces(textposition="top left")
fig.write_html("fig_total_processos_semana.html")
df = (db_processos_expedientes[(db_processos_expedientes["desc_tipo_doc"] == "Processo Administrativo")]
     .sort_values(by="semana_criacao")
     .groupby(["nome_orgao", "semana_criacao"])
     .sum()
     .groupby(level=0).cumsum().reset_index()
     .sort_values(by="total_doc")
)

top10 = df.groupby("nome_orgao")["total_doc"].max().sort_values(ascending=False)[:10].index

fig = px.line(
    df[(df["semana_criacao"].dt.year > 2020)
      & (df["nome_orgao"].isin(top10))],
    x="semana_criacao", 
    y="total_doc", 
    color="nome_orgao", 
    text="total_doc",
    title="Total acumulado de Processos por órgão e semana (top 10)"
)

fig.update_traces(textposition="top left")
fig.write_html("fig_total_processos_orgao_semana.html")
df = (db_processos_expedientes[(db_processos_expedientes["desc_tipo_doc"] == "Expediente")]
     .sort_values(by="semana_criacao")
     .groupby(["nome_orgao", "semana_criacao"])
     .sum()
     .groupby(level=0).cumsum().reset_index()
     .sort_values(by="total_doc")
)

top10 = df.groupby("nome_orgao")["total_doc"].max().sort_values(ascending=False)[:10].index

fig = px.line(
    df[(df["semana_criacao"].dt.year > 2020)
      & (df["nome_orgao"].isin(top10))],
    x="semana_criacao", 
    y="total_doc", 
    color="nome_orgao", 
    text="total_doc",
    title="Total acumulado de Expedientes por órgão e semana (top 10)"
)

fig.update_traces(textposition="top left")
fig.write_html("fig_total_expedientes_orgao_semana.html")<jupyter_output><empty_output><jupyter_text>Total de usuários internos cadastrados **Fórmula:**Contagem do número de usuários sem ser Terceirizados únicos com login gerado no Processo.rio<jupyter_code>query = """
WITH 
dbo_nome AS ( -- Nome do órgão para cada ID
    SELECT 
        id_orgao_usu as id_orgao,
        nm_orgao_usu as nome_orgao
    FROM corporativo.cp_orgao_usuario
)
, dbu_interno AS ( -- Usuários internos
    SELECT
        TO_CHAR(trunc(data_criacao_identidade, 'IW'), 'YYYY/MM/DD') semana_entrada,
        id_orgao_usu id_orgao
    FROM corporativo.cp_identidade
    WHERE id_orgao_usu NOT IN (9999999999, 90000) -- Retira ID do órgão TESTE e Terceirizados
    AND his_dt_fim IS NULL -- Usuários não excluídos do sistema
    -- AND data_criacao_identidade < TIMESTAMP '2021-09-10 17:31:52.076' -- Checagem com a planilha da Márcia (última extração): 1954/2004
)
SELECT 
    semana_entrada,
    nome_orgao,
    COUNT(1) as total_usuarios
FROM 
    dbu_interno,
    dbo_nome
WHERE 
    dbo_nome.id_orgao = dbu_interno.id_orgao
GROUP BY 
    nome_orgao,
    semana_entrada
"""
db_usuarios = pd.read_sql(query, con=con)
db_usuarios
db_usuarios.columns = db_usuarios.columns.str.lower()
db_usuarios["semana_entrada"] = pd.to_datetime(db_usuarios["semana_entrada"])
len(db_usuarios["nome_orgao"].unique())
db_usuarios.sum(numeric_only=True)
df = (db_usuarios
     .sort_values(by="semana_entrada")
     .groupby(["semana_entrada"])
     .sum()
     .cumsum().reset_index()
)

fig = px.line(
    df[df["semana_entrada"].dt.year > 2020],
    x="semana_entrada", 
    y="total_usuarios", 
    text="total_usuarios",
    title="Total acumulado de Usuários internos cadastrados por semana"
)

fig.update_traces(textposition="top left")
fig.write_html("fig_total_usuarios_semana.html")
df = (db_usuarios
     .sort_values(by="semana_entrada")
     .groupby(["nome_orgao", "semana_entrada"])
     .sum()
     .groupby(level=0).cumsum().reset_index()
     .sort_values(by="total_usuarios")
)

top10 = df.groupby("nome_orgao")["total_usuarios"].max().sort_values(ascending=False)[:10].index

fig = px.line(
    df[(df["semana_entrada"].dt.year > 2020)
      & (df["nome_orgao"].isin(top10))],
    x="semana_entrada", 
    y="total_usuarios", 
    color="nome_orgao", 
    text="total_usuarios",
    title="Total acumulado de Usuários internos cadastrados por órgão e semana (top 10)"
)

fig.update_traces(textposition="top left")
fig.write_html("fig_total_usuarios_orgao_semana.html")<jupyter_output><empty_output><jupyter_text>Tabela de processos, expedientes e usuários por órgão<jupyter_code>df = (
    db_processos_expedientes.pivot_table(
        values="total_doc", 
        index="nome_orgao",
        columns="desc_tipo_doc",
        aggfunc="sum"
    ).fillna(0)
    .applymap(int)
    .sort_values(by="Processo Administrativo", ascending=False)
)

df = df.join(
    db_usuarios
    .groupby("nome_orgao")[["total_usuarios"]]
    .sum()
).reset_index()

df[['nome_orgao', 'sigla_orgao']] = df["nome_orgao"].str.split('/', 1, expand=True)
df[["sigla_orgao", "Processo Administrativo", "Expediente", "total_usuarios"]]<jupyter_output><empty_output><jupyter_text>Total de assuntos implantados no Processo.rio **Fórmula:** Contagem do número de assuntos únicos com pelo menos 1 documento criado no Processo.rio até a data- 639 assuntos registrados (id_classificao com his_ativo = 1)- 182 assuntos "originais" (his_id_ini = id_classificacao)- 167 assuntos implantados (com pelo menos 1 documento gerado)<jupyter_code>query = """
WITH 
dba_ativos AS ( -- Assunto ativos por data de criação (1º documento criado com o respectivo assunto)
    SELECT 
        id_classificacao as id_assunto,
        to_char(trunc(dt_doc, 'IW'), 'YYYY/MM/DD') as semana_entrada
    FROM
    ( -- Orderna de assuntos por data de criação
        SELECT 
            id_classificacao,
            dt_doc,
            row_number() over (partition by id_classificacao order by dt_doc asc) as rn
        FROM siga.vw_ex_documento
        WHERE id_orgao_usu <> '9999999999' -- Retira ID do órgão TESTE
        AND to_char(trunc(dt_doc, 'IW'), 'YYYY/MM/DD') IS NOT NULL -- Documentos criados, não contabilizando os temporários
        ORDER BY id_classificacao, dt_doc
    ) t
    WHERE rn = 1 -- Filtra 1a vez que o assunto é utilizado 
)
SELECT 
    semana_entrada,
    COUNT(1) as total_assuntos
FROM 
    dba_ativos
GROUP BY 
    semana_entrada
ORDER BY semana_entrada
"""
db_assuntos = pd.read_sql(query, con=con)
db_assuntos.columns = db_assuntos.columns.str.lower()
db_assuntos["semana_entrada"] = pd.to_datetime(db_assuntos["semana_entrada"])
df = (db_assuntos
     .set_index("semana_entrada")
     .sort_values(by="semana_entrada")
     .cumsum()
     .reset_index() 
)

fig = px.line(
    df[df["semana_entrada"].dt.year > 2020],
    x="semana_entrada", 
    y="total_assuntos", 
    text="total_assuntos",
    title="Total de Assuntos implantados por semana (implantação = 1º documento criado)"
)

fig.update_traces(textposition="top left")
fig.write_html("fig_total_assuntos_implantados_semana.html")<jupyter_output><empty_output><jupyter_text>Número de documentos criados por assunto e órgão<jupyter_code>query = """
WITH 
dbo_nome AS ( -- Nome do órgão para cada ID
    SELECT 
        id_orgao_usu as id_orgao 
        ,nm_orgao_usu as nome_orgao
    FROM corporativo.cp_orgao_usuario
)
, dbd_ativos AS ( -- Documentos únicos criados
    SELECT
        to_char(trunc(dt_doc, 'IW'), 'YYYY/MM/DD') as semana_entrada
        ,id_orgao_usu as id_orgao
        ,id_forma_doc
        ,id_classificacao as id_assunto
    FROM siga.vw_ex_documento
    WHERE id_orgao_usu <> '9999999999' -- Retira ID do órgão TESTE
    AND to_char(trunc(dt_doc, 'IW'), 'YYYY/MM/DD') IS NOT NULL -- Documentos criados, não contabilizando os temporários
    -- AND id_doc_anterior IS NULL -- Documentos únicos, não contabilizando os juntados
    -- AND dt_doc < TIMESTAMP '2021-09-10 17:31:52.076' -- Checagem com a planilha da Márcia (última extração)
)
, dba_desc AS ( -- Descrição do assunto para cada ID de classificação
    SELECT 
        codificacao as cod_assunto
        ,id_classificacao as id_assunto
        ,descr_classificacao as desc_assunto
    FROM siga.ex_classificacao
    WHERE his_ativo = 1
    -- AND his_id_ini = id_classificacao
)
SELECT 
    semana_entrada,
    nome_orgao,
    cod_assunto,
    desc_assunto,
    COUNT(1) as total_doc
FROM 
    dbd_ativos,
    dbo_nome,
    dba_desc
WHERE 
    dbo_nome.id_orgao = dbd_ativos.id_orgao
    AND dba_desc.id_assunto = dbd_ativos.id_assunto
GROUP BY 
    nome_orgao,
    semana_entrada,
    cod_assunto,
    desc_assunto
"""
db_assuntos = pd.read_sql(query, con=con)
db_assuntos.columns = db_assuntos.columns.str.lower()
db_assuntos["semana_entrada"] = pd.to_datetime(db_assuntos["semana_entrada"])
db_assuntos
df = (db_assuntos
     .sort_values(by="semana_entrada")
     .groupby(["desc_assunto", "semana_entrada"])
     .sum()
     .groupby(level=0).cumsum().reset_index()
     .sort_values(by="total_doc")
)

top10 = df.groupby("desc_assunto")["total_doc"].max().sort_values(ascending=False)[:5].index

fig = px.line(
    df[(df["semana_entrada"].dt.year > 2020)
      & (df["desc_assunto"].isin(top10))],
    x="semana_entrada", 
    y="total_doc", 
    color="desc_assunto", 
    text="total_doc",
    title="Total acumulado de Documentos gerados por assunto e semana (top 5 assuntos)"
)

fig.update_layout(legend=dict(
    orientation="h",
    yanchor="bottom",
    y=-0.2,
    xanchor="right",
    x=1
))

fig.update_traces(textposition="top left")
fig.write_html("fig_total_docs_assunto_semana.html")<jupyter_output><empty_output><jupyter_text>Total de modelos implantados no Processo.rio<jupyter_code>query = """
WITH 
dbm_ativos AS ( -- Modelos ativos por data de criação (1º documento criado com o respectivo assunto)
    SELECT 
        id_mod,
        to_char(trunc(dt_doc, 'IW'), 'YYYY/MM/DD') as semana_entrada
    FROM
    ( -- Orderna de assuntos por data de criação
        SELECT 
            id_mod,
            dt_doc,
            row_number() over (partition by id_mod order by dt_doc asc) as rn
        FROM siga.vw_ex_documento
        WHERE id_orgao_usu <> '9999999999' -- Retira ID do órgão TESTE
        AND to_char(trunc(dt_doc, 'IW'), 'YYYY/MM/DD') IS NOT NULL -- Documentos criados, não contabilizando os temporários
        ORDER BY id_mod, dt_doc
    ) t
    WHERE rn = 1 -- Filtra 1a vez que o assunto é utilizado 
)
SELECT 
    semana_entrada,
    COUNT(1) as total_modelos
FROM 
    dbm_ativos
GROUP BY 
    semana_entrada
ORDER BY semana_entrada
"""
db_modelos = pd.read_sql(query, con=con)
db_modelos.columns = db_modelos.columns.str.lower()
db_modelos["semana_entrada"] = pd.to_datetime(db_modelos["semana_entrada"])
db_modelos
df = (db_modelos
     .set_index("semana_entrada")
     .sort_values(by="semana_entrada")
     .cumsum()
     .reset_index() 
)

fig = px.line(
    df[df["semana_entrada"].dt.year > 2020],
    x="semana_entrada", 
    y="total_modelos", 
    text="total_modelos",
    title="Total de Modelos implantados por semana (implantação = 1º documento criado)"
)

fig.update_traces(textposition="top left")
fig.write_html("fig_total_modelos_implantados_semana.html")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11375886524822695, 'fraction_numerical': 0.015531914893617021, 'mean_word_length': 4.090613718411553, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 5, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '103619', 'license': 'MIT', 'n_tokens_mistral': 5714, 'n_tokens_neox': 5544, 'n_words': 1023, 'path': 'analysis/indicators.ipynb', 'repo_name': 'RJ-SEGOVI/processorio_pbi', 'size': '14102'}