---
title: "Financeiro"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(dplyr)
library(openxlsx)
library(ggplot2)
library(plotly)
library(scales)
library(shiny)
library(shinydashboard)
library(DT)
library(viridis)
library(networkD3)
library(stringr)
library(ggalluvial) # para gráficos Sankey
library(tidyr)

```

```{r}
data <- read.xlsx("C:/Users/igorc/Desktop/PESQUISAS/Fapitec/13_2022/04 Dashboards/02_financeiro/bd_financeiro.xlsx")
```

```{r}
# Função para calcular e formatar o valor pago por ano
get_total_pago <- function(data, ano) {
  # Verifique se o valor de 'ano' está na coluna de dados
  if (!ano %in% data$ano) {
    return(paste0("Ano ", ano, " não encontrado nos dados"))
  }
  
  # Aplicando filtro e somando valores
  total <- data %>%
    filter(ano == !!ano, etapa == "pago", !is.na(valor)) %>%
    summarize(total_valor = sum(valor, na.rm = TRUE)) %>%
    pull(total_valor)
  
  paste0("R$ ", format(total, big.mark = ".", decimal.mark = ","))
}

```


```{r}

# Função para criar o gráfico de barras horizontais com destaque
get_grafico_total_pago <- function(data, ano) {
  # Calcula os valores pagos por ano
  valores_ano <- data %>%
    filter(etapa == "pago", !is.na(valor)) %>%
    group_by(ano) %>%
    summarize(total_valor = sum(valor, na.rm = TRUE)) %>%
    arrange(ano)
  
  # Define a cor para destacar o ano selecionado
  cores <- ifelse(valores_ano$ano == ano, "#104E8B", "#ADD8E6")  # Cor distinta para o ano selecionado
  
    # Realiza a regressão linear para obter a linha de tendência
  modelo <- lm(total_valor ~ ano, data = valores_ano)
  valores_ano$trend <- predict(modelo)
  
  # Cria o gráfico de barras horizontais
  fig <- plot_ly(
    valores_ano,
    x = ~total_valor,
    y = ~as.factor(ano),
    type = "bar",
    orientation = "h",
    marker = list(color = cores)
  ) %>%
    layout(
      title = paste("Valores pagos por ano"),
      xaxis = list(title = "Valor Pago (R$)", tickformat = ",.0f"),
      yaxis = list(title = "Ano")
    )
  
  return(fig)
}

```

```{r}

# Carregar os dados
data1 <- read.xlsx("C:/Users/igorc/Desktop/PESQUISAS/Fapitec/13_2022/04 Dashboards/02_financeiro/bd_financeiro.xlsx")

```

```{r}

#Função para criar o Sankey
criar_sankey <- function(data, ano) {
  # Filtrar os dados para o ano e etapa "pago"
  data_filtrada <- data %>%
    filter(ano == !!ano, etapa == "pago", !is.na(valor), valor > 0)
  
  # Agregar os valores pagos por fonte e destino
  fluxo <- data_filtrada %>%
    group_by(fonte, destino) %>%
    summarise(valor = sum(valor, na.rm = TRUE)) %>%
    ungroup()
  
  # Preparar os dados de fonte e destino para o Sankey
  nomes_nodos <- unique(c(fluxo$fonte, fluxo$destino))
  nodos <- data.frame(name = nomes_nodos)
  
  # Indicar os índices das fontes e destinos
  fluxo$source <- match(fluxo$fonte, nodos$name) - 1
  fluxo$target <- match(fluxo$destino, nodos$name) - 1
  
  # Paleta de cores fixa para fontes e destinos
  cores_fontes <- colorRampPalette(c("#FFA07A", "#FF4500"))(length(unique(data$fonte)))
  cores_destinos <- colorRampPalette(c("#D1E8E2", "#2C5F5D"))(length(unique(data$destino)))
  cores_nodos <- c(cores_fontes, cores_destinos)
  
  # Configurar a coloração dos nodos
  cores <- sapply(nodos$name, function(nome) {
    if (nome %in% unique(data$fonte)) {
      cores_fontes[match(nome, unique(data$fonte))]
    } else {
      cores_destinos[match(nome, unique(data$destino))]
    }
  })
  
  # Quebrar nomes longos e corrigir formatação
  nodos$name <- str_replace_all(nodos$name, "_", " ") %>%
    str_to_title() %>%
    str_wrap(width = 20)  # Removido argumento "simplify"
  
  # Criar o Diagrama de Sankey
  sankey <- sankeyNetwork(
    Links = fluxo, Nodes = nodos,
    Source = "source", Target = "target",
    Value = "valor", NodeID = "name",
    units = "R$", fontSize = 12, nodeWidth = 30,
    nodePadding = 15
  )
  
  # Adicionar cores aos nodos
  sankey$nodes$group <- cores
  
  # Retornar o gráfico Sankey
  return(sankey)
}

```


Síntese Histórica
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### X

```{r}

# Calcular o total de valores pagos
total_pago <- data %>%
  filter(etapa == "pago") %>%
  summarize(total_pago = sum(valor, na.rm = TRUE)) %>%
  pull(total_pago)  # Extrair o valor numérico para formatar

# Criar o valueBox com formatação adequada
flexdashboard::valueBox(
  value = scales::dollar(total_pago, prefix = "R$ ", big.mark = ".", decimal.mark = ","),
  caption = "Total de valores pagos",
  icon = "fa-money"
)

```

### Fontes e destinos dos recursos

```{r}

# Filtrar os dados apenas para valores pagos e agregar por fonte e destino para todos os anos
data_acumulada <- data1 %>%
  filter(etapa == "pago", !is.na(valor), valor > 0) %>%
  group_by(fonte, destino) %>%
  summarise(valor = sum(valor, na.rm = TRUE)) %>%
  ungroup()

# Preparar os dados de fonte e destino para o Sankey
nomes_nodos <- unique(c(data_acumulada$fonte, data_acumulada$destino))
nodos <- data.frame(name = nomes_nodos)

# Indicar os índices das fontes e destinos
data_acumulada$source <- match(data_acumulada$fonte, nodos$name) - 1
data_acumulada$target <- match(data_acumulada$destino, nodos$name) - 1

# Paleta de cores fixa para fontes e destinos
cores_fontes <- colorRampPalette(c("#FFA07A", "#FF4500"))(length(unique(data1$fonte)))
cores_destinos <- colorRampPalette(c("#D1E8E2", "#2C5F5D"))(length(unique(data1$destino)))

# Configurar a coloração dos nodos
cores <- sapply(nodos$name, function(nome) {
  if (nome %in% unique(data1$fonte)) {
    cores_fontes[match(nome, unique(data1$fonte))]
  } else {
    cores_destinos[match(nome, unique(data1$destino))]
  }
})

# Quebrar nomes longos e corrigir formatação
nodos$name <- str_replace_all(nodos$name, "_", " ") %>%
  str_to_title() %>%
  str_wrap(width = 20)

# Criar o Diagrama de Sankey
sankey <- sankeyNetwork(
  Links = data_acumulada, Nodes = nodos,
  Source = "source", Target = "target",
  Value = "valor", NodeID = "name",
  units = "R$", fontSize = 12, nodeWidth = 30,
  nodePadding = 15
)

# Adicionar cores aos nodos
sankey$nodes$group <- cores

# Exibir o gráfico
sankey

```

Column {data-width=600}
-----------------------------------------------------------------------

### Fontes

```{r}

# Calcular os dados pagos
data_pagos <- data %>%
  filter(etapa == "pago", !is.na(valor), valor != 0)

# Resumir os valores pagos por ano e fonte
fonte_summarizada <- data_pagos %>%
  group_by(ano, fonte) %>%
  summarize(total_valor = sum(valor, na.rm = TRUE), .groups = "drop")

# Mapeamento amigável para os nomes das fontes
nome_fonte <- c(
  "0101_tesouro_recursos_ordinarios" = "Tesouro\nRecursos Ordinários",
  "0102_receitas_e_transferencias_de_impostos_saude" = "Receitas e Transferências\nImpostos Saúde",
  "0104_recursos_vinculados_a_mde" = "Recursos Vinculados a MDE",
  "0105_fundo_estadual_para_o_desenvolvimento_cientifico_e_tecnologico" = "Fundo Estadual para o\nDesenvolvimento Científico e Tecnológico",
  "0120_royalties_recursos_naturais" = "Royalties\nRecursos Naturais",
  "0224_transferencia_de_convenios_e_instrumentos_afins_demais_areas" = "Transferência de Convênios\ne Instrumentos Afins",
  "0270_recursos_diretamente_arrecadados_funerh" = "Recursos Diretamente\nArrecadados FUNERH",
  "1500_recursos_vinculados_ao_fundo_estadual_para_o_desenvolvimento_cientifico_e_tecnologico_funtec" = "Fundo Estadual Desenvolvimento\nCientífico FUNTEC",
  "1500_recursos_vinculados_as_acoes_e_servicos_publicos_de_saude_asps" = "Recursos Vinculados a Ações e\nServiços Públicos de Saúde",
  "1500_recursos_vinculados_a_secretaria_de_estado_e_da_cultura_seduc_se" = "Recursos Vinculados a Secretaria de\nEstado e Cultura SEDUC-SE",
  "1500_recursos_vinculados_ao_fundo_estadual_de_recursos_hidricos_funerh" = "Fundo Estadual de Recursos\nHídricos FUNERH",
  "1500_recursos_vinculados_a_a_manutencao_e_desenvolvimento_do_ensino_mde" = "Manutenção e Desenvolvimento\ndo Ensino MDE",
  "1700_outras_transferencias_de_convenios_ou_instrumentos_congeneres_da_uniao" = "Outras Transferências de Convênios\ne Instrumentos da União",
  "2500_tesouro" = "Tesouro",
  "2700_outras_transferencias_de_convenios_ou_instrumentos_congeneres_da_uniao" = "Outras Transferências de Convênios\ne Instrumentos da União"
)

# Aplicar mapeamento
fonte_summarizada$fonte <- nome_fonte[fonte_summarizada$fonte]

# Ajustar ordem da legenda
fonte_summarizada$fonte <- factor(fonte_summarizada$fonte, levels = unique(fonte_summarizada$fonte))

# Criar gráfico com valores em milhões e ajustar eixo Y
grafico_sintese <- ggplot(fonte_summarizada, aes(x = ano, y = total_valor, fill = fonte)) +
  geom_bar(stat = "identity", position = "stack") +
  labs(title = "Distribuição dos Valores Pagos por Fonte e Ano",
       x = "Ano",
       y = "Valor Pago (em Milhões de R$)",
       fill = "Fonte") +
  scale_y_continuous(
    labels = scales::number_format(scale = 1e-6, suffix = "M", big.mark = ".", decimal.mark = ",")  # Formata valores em milhões
  ) +
  scale_fill_viridis_d(option = "C", direction = -1) +  # Degradê de cores frias
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5),
    axis.title.x = element_text(margin = margin(t = 10)),
    axis.title.y = element_text(margin = margin(r = 10)),
    legend.position = "top",
    legend.title = element_text(size = 10),
    legend.text = element_text(size = 9)
  )

# Converter para um gráfico interativo
grafico_sintese_interativo <- ggplotly(grafico_sintese, tooltip = c("x", "y", "fill"))

grafico_sintese_interativo

```

### Destinos

```{r}

# Calcular os dados pagos
data_pagos <- data %>%
  filter(etapa == "pago", !is.na(valor), valor != 0)

# Resumir os valores pagos por ano e destino
destino_summarizado <- data_pagos %>%
  group_by(ano, destino) %>%
  summarize(total_valor = sum(valor, na.rm = TRUE), .groups = "drop")

# Mapeamento amigável para os nomes dos destinos
nome_destino <- c(
  "3_3_60_45_subvencoes_economicas" = "Subvenções Econômicas",
  "3_3_90_18_bolsas" = "Bolsas",
  "3_3_90_20_auxilios_custeio" = "Auxílios Custeio",
  "3_3_90_39_servicos_de_terceiros_pessoa_juridica" = "Serviços de Terceiros PJ",
  "4_4_90_20_auxilio_capital" = "Auxílio Capital"
)

# Aplicar mapeamento
destino_summarizado$destino <- nome_destino[destino_summarizado$destino]

# Ajustar ordem da legenda
destino_summarizado$destino <- factor(destino_summarizado$destino, levels = c(
  "Subvenções Econômicas", "Bolsas", "Auxílios Custeio", "Serviços de Terceiros PJ", "Auxílio Capital"
))

# Criar gráfico com valores em milhões e alterar rótulo do eixo Y
grafico_sintese <- ggplot(destino_summarizado, aes(x = ano, y = total_valor, fill = destino)) +
  geom_bar(stat = "identity", position = "stack") +
  labs(
    title = "Evolução dos Valores Pagos por Destino por Ano",
    x = "Ano",
    y = "Valor Pago (R$ Milhões)",
    fill = "Destino"
  ) +
  scale_y_continuous(
    labels = scales::label_number(scale = 1e-6, accuracy = 0.1, big.mark = ".", decimal.mark = ",")  # Exibe valores em milhões com 1 casa decimal
  ) +
  scale_fill_manual(values = c(
    "Subvenções Econômicas" = "#D1E8E2",  # Azul Gelo
    "Bolsas" = "#A1D2CE",                # Verde Água
    "Auxílios Custeio" = "#73BFB8",      # Azul Claro
    "Serviços de Terceiros PJ" = "#468189", # Turquesa Escuro
    "Auxílio Capital" = "#2C5F5D"        # Azul Petróleo
  )) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5),
    axis.title.x = element_text(margin = margin(t = 10)),
    axis.title.y = element_text(margin = margin(r = 10)),
    legend.position = "top",
    legend.title = element_text(size = 10),
    legend.text = element_text(size = 9)
  )

# Converter para gráfico interativo com Plotly
grafico_sintese_interativo <- ggplotly(grafico_sintese, tooltip = c("x", "y", "fill"))

grafico_sintese_interativo


```



2011
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2011),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2011)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos

```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2011)

```

2012
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2012),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2012)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos

```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2012)

```

2013
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2013),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2013)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos


```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2013)

```

2014
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2014),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2014)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos

```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2014)

```

2015
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2015),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2015)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos


```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2015)

```

2016
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2016),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2016)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos


```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2016)

```

2017
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2017),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2017)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos

```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2017)

```

2018
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2018),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2018)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos


```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2018)

```

2019
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2019),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2019)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos

```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2019)
```

2020
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2020),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2020)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos

```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2020)

```

2021
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2021),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2021)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos

```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2021)

```

2022
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2022),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2022)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos


```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2022)

```

2023
=======================================================================

Column {data-width=400}
-----------------------------------------------------------------------

### Quantidade de Recursos

```{r}

flexdashboard::valueBox(
  value = get_total_pago(data, 2023),
  caption = "Total de valores pagos no ano"
)

```

### Quantidade de Recursos

```{r}
# Exemplo de uso da função para o ano de 2011
get_grafico_total_pago(data, 2023)
```


Column {data-width=600}
-----------------------------------------------------------------------

### Fontes e destinos dos recursos


```{r}

# Gerar o gráfico de Sankey para o ano de 2011
criar_sankey(data1, 2023)

```