library(here)
library(readr)
library(dplyr)
library(kableExtra)
library(ggplot2)
library(hrbrthemes)
library(DT)

theme_set(theme_ipsum_rc())

Contaremos aqui um pouco sobre as dificuldades e problemas encontrados em recuperar os dados referentes às licitações de um orgão público.

Transporte escolar

Os dados sobre os empenhos realizados por cada município contêm três elementos importantes: código da função orçamentária, código da subfunção e código do subelemento.

O código da função e subfunção é definido pela portaria MOG nº 42/99. Segundo ela, a função diz respeito as diversas áreas de despesa que competem ao setor público e a subfunção representa uma partição da função, visando agregar determinado subconjunto de despesa do setor público. Nesse link é possível ver o texto atualizado da portaria e a tabela com as funções e subfunções de cada área de um município, como, educação, saúde e entre outros.

O código do subelemento é definido segundo a Portaria STN nº 448, de 13 de setembro de 2002. Essa tabela contém a descrição de cada código para o subelemento.

As licitações foram obtidas a partir da análise de suas descrições. Observando os valores de cada linha nas bases de dados, percebemos que algumas descrições continham conjuntos de palavras que remetem aos serviços para o transporte escolar. Com isso selecionamos as licitações que continham em suas descrições alguns dos seguintes termos:

TRANSPORTE ESCOLAR
TRANSPORTE DOS ESTUDANTE
TRANSPORTES DOS ALUNOS
TRANSPORTE DE ESCOLARES
TRANSPORTE DA EQUIPE PEDAGOGICA
TRANSPORTES DIVERSOS
DESTINADOS AOS ALUNOS

Isso foi feito porque não conseguimos encontrar código de subfunção e subelemento para depesas relacionadas ao transporte escolar. A partir desse filtro conseguimos obter os empenhos relacionados a estas licitações. Vejamos os códigos de função, subfunção e subelemento destes empenhos.

empenhos_transporte <- read_csv(here::here("data/transporte_escolar_2016/empenhos.csv"),
  col_types = cols(cd_Acao = col_character())
)

funcao <- empenhos_transporte %>%
  count(cd_Funcao)

subfuncao <- empenhos_transporte %>%
  count(cd_Subfuncao)

subelemento <- empenhos_transporte %>%
  count(cd_SubElemento)

sagres <- src_mysql("sagres_municipal", group = "ministerio-publico", password = NULL, username = "shiny")

query_funcao <- sql("SELECT * FROM Codigo_Funcao")
desc_funcao <- tbl(sagres, query_funcao) %>%
  collect(n = Inf)

query_subfuncao <- sql("SELECT * FROM Codigo_Subfuncao")
desc_subfuncao <- tbl(sagres, query_subfuncao) %>%
  collect(n = Inf) %>%
  rename(cd_Subfuncao = cd_SubFuncao)

funcao <- funcao %>%
  left_join(desc_funcao,
    by = "cd_Funcao"
  ) %>%
  select(-st_Ativo,
    ocorrências = n
  ) %>%
  arrange(-ocorrências)

subfuncao <- subfuncao %>%
  left_join(desc_subfuncao,
    by = "cd_Subfuncao"
  ) %>%
  select(-st_Ativo,
    ocorrências = n
  ) %>%
  arrange(-ocorrências)

subelemento <- subelemento %>%
  mutate(de_SubElemento = case_when(
    cd_SubElemento == 19 ~ "OUTROS MATERIAIS DE CONSUMO",
    cd_SubElemento == 22 ~ "BOLSA DE INICIAÇÃO AO TRABALHO",
    cd_SubElemento == 38 ~ "OUTROS SERVIÇOS DE PESSOA FÍSICA",
    cd_SubElemento == 43 ~ "LOCAÇÃO DE MÁQUINAS E EQUIPAMENTOS",
    cd_SubElemento == 46 ~ "MANUTENÇÃO E CONSERVAÇÃO DE VEÍCULOS",
    cd_SubElemento == 48 ~ "FESTIVIDADES E HOMENAGENS",
    cd_SubElemento == 59 ~ "AQUISIÇÃO DE SOFTWARES DE APLICAÇÃO",
    cd_SubElemento == 61 ~ "OUTROS SERVIÇOS DE TERCEIROS, PESSOA JURÍDICA",
    cd_SubElemento == 77 ~ "VEÍCULOS DE TRAÇÃO MECÂNICA",
    cd_SubElemento == 95 ~ "LOCAÇÃO DE VEÍCULOS",
    cd_SubElemento == 99 ~ "SEM SUBELEMENTO"
  )) %>%
  rename(ocorrências = n) %>%
  arrange(-ocorrências)

Nos empenhos encontramos vários códigos de função. O código relacionado à Educação aparece em maior quantidade, porém os empenhos também possuem códigos sobre outras despesas, como por exemplo, Saúde.

funcao %>%
  ggplot(aes(x = reorder(de_Funcao, ocorrências), y = ocorrências)) +
  geom_col(
    color = "tomato",
    fill = "tomato",
    width = .7
  ) +
  labs(
    x = "Descrição da função",
    y = "Ocorrências"
  ) +
  scale_y_log10() +
  theme(axis.text.x = element_text(size = 10))

Dentre os códigos de subfunção, alguns deles não estão relacionados à Educação

subfuncao %>%
  ggplot(aes(x = reorder(de_SubFuncao, ocorrências), y = ocorrências)) +
  geom_col(
    color = "tomato",
    fill = "tomato",
    width = .7
  ) +
  labs(
    x = "Descrição da subfunção",
    y = "Ocorrências"
  ) +
  scale_y_log10() +
  coord_flip()

Os códigos de subelemento variam bastante para os serviços do transporte escolar. Dentre eles, temos o código que não se refere a nenhuma área específica e aparece em uma quantidade considerável.

subelemento %>%
  ggplot(aes(x = reorder(de_SubElemento, ocorrências), y = ocorrências)) +
  geom_col(
    color = "tomato",
    fill = "tomato"
  ) +
  labs(
    x = "Descrição da subfunção",
    y = "Ocorrências"
  ) +
  scale_y_log10() +
  coord_flip()

Abaixo listamos as licitações nas quais o código de função do empenho não está relacionado a Educação.

licitacoes_transporte <- read_csv(here("data/transporte_escolar_2016/licitacoes.csv"))

licitacoes_outras_funcoes <- empenhos_transporte %>%
  filter(cd_Funcao != 12) %>%
  inner_join(licitacoes_transporte,
    by = c("cd_UGestora", "dt_Ano", "nu_Licitacao", "tp_Licitacao")
  ) %>%
  select(cd_UGestora, dt_Ano, nu_Licitacao, tp_Licitacao, cd_Funcao, cd_Subfuncao, cd_SubElemento, de_Obs, vl_Licitacao, nu_Propostas, dt_MesAno, de_TipoLicitacao) %>%
  unique()

licitacoes_outras_funcoes %>%
  select(nu_Licitacao, de_Obs, cd_Funcao, cd_Subfuncao, cd_SubElemento) %>%
  datatable(
    options = list(
      pageLength = 8,
      lengthMenu = c(8, 16)
    ),
    colnames = c("Licitação", "Descrição", "Função", "Subfunção", "Subelemento"),
    rownames = FALSE
  )

Identificando a origem dos recursos gastos com transporte escolar

Aqui podemos ver de onde vem os recursos que são gastos com o transporte escolar. A maioria dos recursos partem de fontes relacionadas a educação, como o FNDE e o FUNDEB, contudo, algumas fontes estão relacionadas a saúde, como Transferência de Recursos do SUS e Receita de Impostos e de Transferência de Impostos - Saúde.

query_fonte_recursos <- sql("SELECT * FROM Tipo_OrigemRecursos")
fontes <- tbl(sagres, query_fonte_recursos) %>%
  collect(n = Inf)

pagamentos_transporte <- read_csv(here("data/transporte_escolar_2016/pagamentos.csv")) %>%
  left_join(fontes,
    by = "tp_FonteRecursos"
  )

pagamentos_transporte %>%
  select(
    tp_FonteRecursos,
    de_TipoRecursos
  ) %>%
  unique() %>%
  datatable(
    options = list(
      pageLength = 5,
      lengthMenu = c(5, 10, 15)
    ),
    colnames = c("Fonte de Recursos", "Descrição"),
    rownames = FALSE
  )

Listamos quais são essas licitações de transporte escolar que são financiadas por recursos de saúde. Podemos ver suas descrições abaixo:

pagamentos_transporte %>%
  filter(tp_FonteRecursos %in% c(2, 14)) %>%
  inner_join(empenhos_transporte,
    by = c("cd_UGestora", "dt_Ano", "cd_UnidOrcamentaria", "nu_Empenho")
  ) %>%
  inner_join(licitacoes_transporte,
    by = c("cd_UGestora", "nu_Licitacao", "tp_Licitacao")
  ) %>%
  select(cd_UGestora, nu_Licitacao, de_Obs, de_TipoLicitacao, de_TipoRecursos) %>%
  unique() %>%
  datatable(
    options = list(
      pageLength = 5,
      lengthMenu = c(5, 10, 15)
    ),
    colnames = c("Unidade Gestora", "Número da Licitacao", "Descrição", "Tipo da Licitacao", "Tipo de Recursos"),
    rownames = FALSE
  )

Ações associadas às licitações de transporte escolar

acao_query <- sql("SELECT * FROM Acao")
acao <- tbl(sagres, acao_query) %>%
  collect(n = Inf)

empenhos_transporte %>%
  inner_join(acao %>% select(cd_UGestora, dt_Ano, cd_Acao, de_Acao, tp_Acao),
    by = c("cd_UGestora", "dt_Ano", "cd_Acao")
  ) %>%
  select(cd_UGestora, dt_Ano, cd_Acao, de_Acao, tp_Acao) %>%
  unique() %>%
  datatable(
    options = list(
      pageLength = 10,
      lengthMenu = c(5, 10, 15)
    ),
    colnames = c("Unidade Gestora", "Data", "Ação", "Descrição", "Tipo"),
    rownames = FALSE
  )

Merenda escolar

Para selecionar os dados sobre as depesas de merenda escolar foi utilizado os seguintes códigos, de acordo com as definições de função, subfunção e subelemento:

Código de função: 12 - Educação
Código de subfunção: 306 - Alimentação e Nutrição
Código de subelemento: 02 - Gêneros de Alimentação

Identificando a origem dos recursos gastos com merenda escolar

get_pagamentos_filtrados <- function(dbcon, cd_funcao, cd_subfuncao, cd_subelemento) {
  template <- ('
               select *
               from Pagamentos
               inner join (
               select cd_Ugestora, dt_Ano, cd_UnidOrcamentaria, nu_Empenho
               from Empenhos
               where cd_Funcao = %d
               and (cd_Subfuncao = %d OR cd_SubElemento = "%s")
               ) emm
               using (cd_Ugestora, dt_Ano, cd_UnidOrcamentaria, nu_Empenho)
  ')
  query <- template %>%
    sprintf(cd_funcao, cd_subfuncao, cd_subelemento) %>%
    sql()

  tbl(dbcon, query) %>%
    return()
}

pagamentos_merenda <- get_pagamentos_filtrados(sagres, cd_funcao = 12, cd_subfuncao = 306, cd_subelemento = "02") %>%
  select(cd_UGestora, nu_Empenho, dt_Ano, cd_UnidOrcamentaria, vl_Pagamento, tp_FonteRecursos) %>%
  collect(n = Inf)

pagamentos_merenda <- pagamentos_merenda %>%
  left_join(fontes,
    by = "tp_FonteRecursos"
  )

pagamentos_merenda %>%
  count(de_TipoRecursos) %>%
  select(de_TipoRecursos,
    ocorrencias = n
  ) %>%
  top_n(10, wt = ocorrencias) %>%
  arrange(-ocorrencias) %>%
  mutate(de_TipoRecursos = case_when(
    is.na(de_TipoRecursos) ~ "Não atribuído",
    TRUE ~ de_TipoRecursos
  )) %>%
  datatable(
    options = list(
      pageLength = 5,
      lengthMenu = c(5, 10)
    ),
    colnames = c("Tipo de Recursos", "Ocorrências"),
    rownames = FALSE
  )

Para as licitações sobre merenda escolar os 10 tipos mais comuns de recursos variam muito, a maioria deles são recursos de Educação, mas ainda existem alguns sobre Saúde e Previdência Social. O tipo de recurso da Saúde aparece em uma quantidade considerável.

Na tabela abaixo conseguimos ver as licitações de merenda escolar que são financiadas por fontes não relacionadas à educação:

get_empenhos_filtrados <- function(dbcon, cd_funcao, cd_subfuncao, cd_subelemento) {
  template <- ('
    SELECT *
    FROM Empenhos
    WHERE cd_Funcao = %d
    AND (cd_Subfuncao = %d OR cd_SubElemento = "%s")
  ')

  query <- template %>%
    sprintf(cd_funcao, cd_subfuncao, cd_subelemento) %>%
    sql()

  tbl(dbcon, query) %>%
    return()
}

empenhos_merenda <- get_empenhos_filtrados(sagres, cd_funcao = 12, cd_subfuncao = 306, cd_subelemento = "02") %>%
  select(cd_UGestora, nu_Licitacao, nu_Empenho, cd_UnidOrcamentaria, tp_Licitacao, vl_Empenho, cd_Funcao, cd_Subfuncao, cd_SubElemento, dt_MesAno, dt_Empenho, tp_FonteRecursos, cd_Acao) %>%
  collect(n = Inf)

get_licitacoes <- function(dbcon, cd_funcao, cd_subfuncao, cd_subelemento) {
  template <- ('
    SELECT lic.*
    FROM
    ( SELECT A.*, COUNT(*) total
      FROM
      ( SELECT *, COUNT(*) filtro
        FROM
        ( SELECT e.*
          FROM Empenhos e
          WHERE cd_Funcao = %d
          AND (cd_Subfuncao = %d OR cd_Subelemento = "%s")
        ) AS emp
        GROUP BY cd_UGestora, nu_Licitacao, tp_Licitacao
      ) AS A
      INNER JOIN Empenhos
      USE INDEX (FK_Empenhos_Licitacao)
      USING (cd_UGestora, nu_Licitacao, tp_Licitacao)
      GROUP BY cd_UGestora, nu_Licitacao, tp_Licitacao
    ) AS B
    INNER JOIN Licitacao lic
    USING (cd_UGestora, nu_Licitacao, tp_Licitacao)
    WHERE filtro = total
  ')

  query <- template %>%
    sprintf(cd_funcao, cd_subfuncao, cd_subelemento) %>%
    sql()

  tbl(dbcon, query) %>%
    return()
}

licitacoes_merenda <- get_licitacoes(sagres, cd_funcao = 12, cd_subfuncao = 306, cd_subelemento = "02") %>%
  select(cd_UGestora, nu_Licitacao, de_Obs) %>%
  collect(n = Inf)

empenhos_merenda <- empenhos_merenda %>%
  left_join(fontes,
    by = "tp_FonteRecursos"
  )

empenhos_merenda %>%
  select(
    cd_UGestora,
    nu_Licitacao,
    tp_FonteRecursos,
    de_TipoRecursos
  ) %>%
  filter(!is.na(de_TipoRecursos)) %>%
  full_join(licitacoes_merenda,
    by = c("cd_UGestora", "nu_Licitacao")
  ) %>%
  filter(de_TipoRecursos %in% c("Receita de Impostos e de Transferência de Impostos - Saúde", "Transferência de Recursos do SUS", "Serviços de Saúde", "Contribuição para o RPPS (patronal, servidores e comp. financeira)")) %>%
  distinct(nu_Licitacao, .keep_all = TRUE) %>%
  datatable(
    colnames = c("Unidade Gestora", "Número da Licitacao", "Tipo de Recursos", "Descrição do Recurso", "Descrição da Licitação"),
    rownames = FALSE
  )

Recuperando licitações sobre gastos específicos

Transporte escolar

Identificando a origem dos recursos gastos com transporte escolar

Ações associadas às licitações de transporte escolar

Merenda escolar

Identificando a origem dos recursos gastos com merenda escolar