---
title: 'Visual Analytics de Perdas Não-Técnicas -- versão beta'
author: "Filipe Marçal & Luís Felipe Granello de Souza"
output:
  flexdashboard::flex_dashboard:
   orientation: columns
  #vertical_layout: scroll
  social: [ "github", "linkedin" ]
source_code: embed
runtime: shiny

---
<style>
  .navbar {
    background-color: #C21807;
    border-color: black;
    }
  .navbar-brand {
    color:white!important;
    }
  
</style>
---
  
```{r setup, include=FALSE}

rm(list= ls())

# Bibliotecas gerais
#devtools::install_github("rstudio/flexdashboard")
library(flexdashboard)
library(shiny)
library(shinyWidgets)
library(plotly)
library(maps)
library(DT)
library(tidyverse)
library(reshape2)
library(readxl)
library(xgboost)
library(MatrixModels)
library(stringr)
library(dplyr)
library(caret)
knitr::opts_chunk$set(message = FALSE)

#caminho <- "S:/DComercial/Atendimento_Cliente/Processos_Comerciais/Supervisao/DataScience/Nossos Projetos e Demandas/Estruturados/Perdas/Perdas_Internas/CálculoAvaria/ProjAnalyViz/"

caminho <- "C:/Users/Usuario/Documents/Acadêmico/Mestrado da FEEC/IA376A - Tópicos VII - Analítica Visual de Dados/IA376A - Analítica Visual - Shared/Projeto/"

# Arquivo-fonte
perdas <- read_excel("Fisc_Open.xlsx")

# Remove a coluna de número de linha
perdas <- perdas[-1]

# Data cleaning
# Remove casos sem coordenadas geográficas
perdas <- perdas[complete.cases(perdas[, 21:22]),]

# Cria uma amostra de 5000 ocorrências aleatórias + conjunto de unidades com irregularidades
idx_perdas_irr <- which(perdas$Perfil_Cliente == 'Fraudador')
tam_amostra <- 5000
set.seed(101010)
idx_sorteio <- sort(sample(1:nrow(perdas), tam_amostra, replace= FALSE))
idx_amostra <- sort(append(idx_perdas_irr, idx_sorteio))
perdas_am <- perdas[idx_amostra, ]

# Cores
cor_neutro <- '#B9BBB6'
cor_reg <- '#0B6623'
cor_irreg <- '#B80F0A'

cor_perfil <- ifelse(is.na(perdas$DTA_CMPT_CONCL_SSE), cor_neutro, 
                     ifelse(perdas$Perfil_Cliente =='Fraudador', cor_irreg, cor_reg))

cor_perfil_am <- ifelse(is.na(perdas_am$DTA_CMPT_CONCL_SSE), cor_neutro, 
                     ifelse(perdas_am$Perfil_Cliente =='Fraudador', cor_irreg, cor_reg)) 

# Dados dinâmicos
# startData <- perdas_am
# updateData <- reactive(startData %>% 
#                          group_by(!!! rlang::syms(input$GB)) %>%
#                             summarise_if(is.numeric, sum, na.rm= T)
# )

# Dados sumarizados
library(sqldf)
bai_latlong <- sqldf("SELECT DES_BAIR_BAI as Bairro,
                             min(NUM_COORX_XXX) as MinLong,
                             min(NUM_COORY_XXX) as MinLat,
                             max(NUM_COORX_XXX) as MaxLong,
                             max(NUM_COORY_XXX) as MaxLat,
                             max(NUM_COORX_XXX) - min(NUM_COORX_XXX) as DifLong,
                             max(NUM_COORY_XXX) - min(NUM_COORY_XXX) as DifLat
                       FROM perdas
                       GROUP BY DES_BAIR_BAI")

lista_bairro <- sqldf("SELECT Bairro, MinLong, MinLat, MaxLong, MaxLat
                      FROM bai_latlong
                      WHERE DifLong <> 0 and DifLat <> 0")

# Cria tabela com UC, Classe, Bairro e Histórico de Consumo
perdas_cons <- perdas[c(1,25,30,35:98)]
# "Derrete" a tabela criada colocando colunas de consumo em linhas
# As linhas das colunas derretidas ficam na coluna 'value'
perdas_melt <- melt(perdas_cons, id= colnames(perdas_cons)[1:3])

# Estatísticas de consumo com diferentes agrupamentos
estat_cons_logr <- sqldf("SELECT DES_BAIR_BAI as Bairro,
                                 avg(value) as Cons_Medio,
                                 stdev(value) as Desv_Pad,
                                 count(*) as Qtde_Cons_Bai
                          FROM perdas_melt
                          GROUP BY DES_BAIR_BAI")

estat_cons_clas <- sqldf("SELECT DES_CLAS_PRIN_CCL as Classe,
                                 avg(value) as Cons_Medio,
                                 stdev(value) as Desv_Pad,
                                 count(*) as Qtde_Cons_Classe
                          FROM perdas_melt
                          GROUP BY DES_CLAS_PRIN_CCL")

estat_cons_bai_clas <- sqldf("SELECT DES_CLAS_PRIN_CCL as Classe,
                                     DES_BAIR_BAI as Bairro,
                                     avg(value) as Cons_Medio,
                                     stdev(value) as Desv_Pad,
                                     count(*) as Qtde_Cons_Faixa
                             FROM perdas_melt
                             GROUP BY DES_CLAS_PRIN_CCL, 
                                      DES_BAIR_BAI")

estat_uc_bai_clas <- sqldf("SELECT COD_UN_CONS_UEE as UC,
                                   DES_CLAS_PRIN_CCL as Classe,
                                   DES_BAIR_BAI as Bairro,
                                   avg(value) as Cons_Med_UC,
                                   stdev(value) as Desv_Pad_UC
                            FROM perdas_melt
                            GROUP BY COD_UN_CONS_UEE, 
                                     DES_CLAS_PRIN_CCL,
                                     DES_BAIR_BAI")

uc_cons_sem_na <- sqldf("SELECT PAM.COD_UN_CONS_UEE as UC,
                                count(*) as Qtde_Cons
                        FROM perdas_am PAM left join perdas_melt PME 
                             on PAM.COD_UN_CONS_UEE = PME.COD_UN_CONS_UEE
                        WHERE value is not NULL
                        GROUP BY PAM.COD_UN_CONS_UEE
                        HAVING Qtde_Cons >= 64
                        ORDER BY Qtde_Cons DESC")

consumo_categoria <- sqldf("SELECT PAM.COD_UN_CONS_UEE as UC,
                                   PAM.DES_BAIR_BAI as Bairro,
                                   PAM.DES_CLAS_PRIN_CCL as Classe,
                                   PAM.COD_TIPO_FASE_UEE as Fase,
                                   PAM.COD_GRU_TENS_FAT_UEE || COD_SUB_GRU_FAT_UEE as Tensao,
                                   PAM.DES_CLAS_CONS_CLA as Subclasse,
                                   PME.value as Vlr_Consumo
                            FROM perdas_am PAM left join perdas_melt PME
                                  on PAM.COD_UN_CONS_UEE = PME.COD_UN_CONS_UEE
                            WHERE PME.value is not NULL")

#Score XGBoost

xgb<-xgb.load('xgb.modelo')
base_modelo<-perdas_am[which(is.na(perdas_am$COD_UN_CONS_UEE)==FALSE),]
for (i in 1:nrow(base_modelo)){
  base_modelo[i,99]<-round(mean(as.numeric(base_modelo[i,c(35:98)]),na.rm = TRUE))
}


base_modelo_red<-base_modelo[,c(17,19,26,27,28,29,31,33,99)]
base_modelo_red$COD_GRU_TENS_FAT_UEE<-paste0(base_modelo_red$COD_GRU_TENS_FAT_UEE,base_modelo_red$COD_SUB_GRU_FAT_UEE)
base_modelo_red<-base_modelo_red[,-4]
colnames(base_modelo_red)<-c(colnames(base_modelo_red)[-8],'Med_Consumo')
base_modelo_red$Med_Consumo<-ifelse(base_modelo_red$Med_Consumo=='NaN',0,base_modelo_red$Med_Consumo)

bins <- seq(min(as.numeric(base_modelo_red$Med_Consumo)), max(as.numeric(base_modelo_red$Med_Consumo)), by=200)
Amount2 <- as.numeric(gsub("\\$|,", "", base_modelo_red$Med_Consumo))
labels <- gsub("(?<!^)(\\d{3})$", ",\\1", bins, perl=T)
rangelabels <- paste(head(labels,-1), tail(labels,-1), sep="-")
base_modelo_red$Med_Consumo <- cut(Amount2, bins, rangelabels)

bins <- seq(0, max(as.numeric(base_modelo_red$QTD_CARG_UEE)), by=1000)
Amount2 <- as.numeric(gsub("\\$|,", "", base_modelo_red$QTD_CARG_UEE))
labels <- gsub("(?<!^)(\\d{3})$", ",\\1", bins, perl=T)
rangelabels <- paste(head(labels,-1), tail(labels,-1), sep="-")
base_modelo_red$QTD_CARG_UEE <- cut(Amount2, bins, rangelabels)

dummies1 <- dummyVars(~. ,data = base_modelo_red[,-1])
X <- as.data.frame(predict(dummies1, newdata = base_modelo_red[,-1]))

xgbpred <- predict(xgb, as.matrix(X),reshape = TRUE)
xgbpred<-as.data.frame(xgbpred)
threshold<-0.5
for (i in (1:nrow(xgbpred))){
xgbpred[i,3]<-ifelse(xgbpred[i,1]>threshold,'Irregular','Regular')
}
colnames(xgbpred)<-c('Fraudador','Não-Fraudador','Perfil_Final')
score<-as.data.frame(cbind(perdas_am$COD_UN_CONS_UEE,as.numeric(round(xgbpred$Fraudador*100)),xgbpred$Perfil_Final))

colnames(score)<-c("UC","Score","Perfil_Final")

for (i in (1:nrow(perdas_am))){
perdas_am[i,99]<-ifelse(is.na(base_modelo_red[i,8])==TRUE,0,ifelse(perdas_am[i,10]=="Fraudador",100,score[i,2]))

perdas_am[i,100]<-ifelse(perdas_am[i,99]==0,"Não-Aplicável",ifelse(perdas_am[i,10]=="Fraudador","Irregular",as.character(score[i,3])))
}
colnames(perdas_am)<-c(colnames(perdas_am)[c(-99,-100)],'Score','Perfil_Cliente_Modelo')
perdas_am$Score<-as.numeric(perdas_am$Score)


#Base Teste Modelo

base_teste<-perdas_am[which(is.na(perdas_am$COD_UN_CONS_UEE)==FALSE & is.na(perdas_am$DTA_CMPT_CONCL_SSE)==FALSE),]
for (i in 1:nrow(base_teste)){
  base_teste[i,99]<-round(mean(as.numeric(base_teste[i,c(35:98)]),na.rm = TRUE))
}

base_teste_red<-base_teste[,c(17,19,26,27,28,29,31,33,99)]
base_teste_red$COD_GRU_TENS_FAT_UEE<-paste0(base_teste_red$COD_GRU_TENS_FAT_UEE,base_teste_red$COD_SUB_GRU_FAT_UEE)
base_teste_red<-base_teste_red[,-4]
colnames(base_teste_red)<-c(colnames(base_teste_red)[-8],'Med_Consumo')
base_teste_red$Med_Consumo<-ifelse(base_teste_red$Med_Consumo=='NaN',0,base_teste_red$Med_Consumo)

bins <- seq(min(as.numeric(base_teste_red$Med_Consumo)), max(as.numeric(base_teste_red$Med_Consumo)), by=200)
Amount2 <- as.numeric(gsub("\\$|,", "", base_teste_red$Med_Consumo))
labels <- gsub("(?<!^)(\\d{3})$", ",\\1", bins, perl=T)
rangelabels <- paste(head(labels,-1), tail(labels,-1), sep="-")
base_teste_red$Med_Consumo <- cut(Amount2, bins, rangelabels)

bins <- seq(0, max(as.numeric(base_teste_red$QTD_CARG_UEE)), by=1000)
Amount2 <- as.numeric(gsub("\\$|,", "", base_teste_red$QTD_CARG_UEE))
labels <- gsub("(?<!^)(\\d{3})$", ",\\1", bins, perl=T)
rangelabels <- paste(head(labels,-1), tail(labels,-1), sep="-")
base_teste_red$QTD_CARG_UEE <- cut(Amount2, bins, rangelabels)

dummies2 <- dummyVars(~. ,data = base_teste_red[,c(-1,-2)])
X <- as.data.frame(predict(dummies2, newdata = base_teste_red[,c(-1,-2)]))

xgbpred_teste <- predict(xgb, as.matrix(X),reshape = TRUE)
xgbpred_teste<-as.data.frame(xgbpred_teste)

xgbpred_teste<-xgbpred_teste[,-2]
y_teste<-base_teste['Perfil_Cliente']
y_teste$Pred<-xgbpred_teste

```


Mapa (Overview) {data-icon="fa-map"}
==============================================================

Column
--------------------------------------------------------------

### Visão aérea
Informações sobre as fiscalizações de medidores de energia elétrica

``` {r}
selectInput(inputId= "bairro", 
            label= "Bairro", 
            choices= sort(unique(lista_bairro$Bairro)),
            selected= 'BRO ALTO DO MIRANTE',
            width = "200px"
)
```
```{r eval= TRUE, fig.height=5, fig.width=5}

# minLat <- reactive(lista_bairro %>%
#                           filter(Bairro == input$bairro) %>%
#                           .$MinLat)
# minLong <- reactive(lista_bairro %>%
#                           filter(Bairro == input$bairro) %>%
#                           .$MinLong)
# maxLat <- reactive(lista_bairro %>%
#                           filter(Bairro == input$bairro) %>%
#                           .$MaxLat)
# maxLong <- reactive(lista_bairro %>%
#                           filter(Bairro == input$bairro) %>%
#                           .$MaxLong)

library(leaflet)

# TamanhoRaio <- function(vetor){
#   # função linear para definir raio dos glifos em função do parâmetro de consumo
#   U <- 15
#   L <- 5
#   D <- max(vetor, na.rm= TRUE) - min(vetor, na.rm= TRUE)
#   slope <- (U - L)/D
#   lin <- L - slope*min(vetor, na.rm= TRUE)
#   
#   y <- slope*vetor + lin
#   
#   y_corr <- ifelse(is.na(y), 3, y)
#   
#   return(y_corr)
# }

renderLeaflet({
  leaflet() %>% 
    addTiles() #%>%
    #fitBounds(minLong(), minLat(), maxLong(), maxLat()) %>%
    #fitBounds(-47.01, -22.545, -46.8, -22.345) %>%
    # addCircleMarkers(perdas_am$NUM_COORX_XXX, 
    #                  perdas_am$NUM_COORY_XXX, 
    #                  color = cor_perfil_am, 
    #                  radius = TamanhoRaio(perdas_am$VLR_PAR_CALC_FOC),
    #                  fill = T,
    #                  fillOpacity = 0.2,
    #                  opacity = 0.2,
    #                  popup = paste(paste("Unidade:", perdas_am$COD_UN_CONS_UEE),
    #                                paste("Data de ligação:", perdas_am$`TRUNC(UEE.DTA_INIC_HIST_UEE)`),
    #                                paste("Data da inspeção:", perdas_am$DTA_CMPT_ORIG_SSE),
    #                                paste("Perfil:", perdas_am$Perfil_Cliente),
    #                                paste("Score Modelo:", perdas_am$Score,"%"),
    #                                paste("Perfil Modelo:", perdas_am$Perfil_Cliente_Modelo),
    #                                sep= "<br>")
    # ) %>%
    # addLegend("bottomleft", 
    #           colors = c(cor_neutro, cor_reg, cor_irreg),
    #           labels = c("Não inspecionado após 2018",
    #                      "Normal",
    #                      "Irregularidade encontrada"), 
    #           opacity = 0.8)
})

```

Column {.tabset}
--------------------------------------------------------------
  
### Análise Exploratória
  
``` {r}

# tx_irreg <- 65/5000
# gauge(tx_irreg, min = 0, max = 100, symbol = '%', 
#       gaugeSectors(success = c(0, 25),
#                    warning = c(26, 74),
#                    danger = c(74, 100)))

selectInput(inputId= "Grupo", 
            label= "Agrupar por:", 
            choices= names(select_if(consumo_categoria, is.character)),
            selected= "Classe"
            )

cons_categ_melt <- unique(gather(unique(consumo_categoria), 
                          colnames(consumo_categoria)[2:6], 
                          key= "Categoria", 
                          value= "Detalhe_Categ"))

cons_categ_bp <- reactive(cons_categ_melt %>%
                            filter(Categoria == input$Grupo) %>%
                              select(Categoria, Detalhe_Categ, Vlr_Consumo)
                          )

renderPlotly({cons_categ_bp() %>%
            plot_ly(x= ~Detalhe_Categ,
                    y= ~Vlr_Consumo,
                    color= ~Detalhe_Categ,
                    type= "box",
                    width = '650',
                    height = '450') %>%
            layout(title = paste("Boxplot de consumos por", input$Grupo),
                   xaxis = list(title= input$Grupo, titlefont= f),
                   yaxis = list(title= "Consumo [kWh]", titlefont= f),
                   hovermode = 'x',
                   showlegend = FALSE,
                   autosize = F)
})

```

### XGBoost Score 

``` {r}

sliderInput(inputId = "threshold",
            label = "Valor-limite:",
            min = round(min(y_teste$Pred),1),
            max = round(max(y_teste$Pred)-0.1,1), 
            value = .5, 
            step = .1,
            width= '300px')


table_mat <- reactive({table(y_teste$Perfil_Cliente, (y_teste$Pred > input$threshold))})

 #t<-table(y_teste$Perfil_Cliente, (y_teste$Pred > .5))

 TP<-reactive(table_mat()[1,2])
 TN<-reactive(table_mat()[2,1])
 Total<-reactive(sum(table_mat()[1,1],table_mat()[1,2],table_mat()[2,1],table_mat()[2,2]))


 #colors = "RdBu",
renderPlotly({plot_ly(x= c("Reg", "Irreg"), 
                      y= c("Irreg","Reg"),
                      z= table_mat(), showscale=FALSE, type = "heatmap", 
                      autocolorscale = FALSE, 
                      colors = "YlGnBu",
                      width = 400,
                      height = 350)   %>%
      layout(
             title= paste("Acurácia: ", ((TN()+TP())/Total()*100),"%"),

             annotations = list(list(
               x = "Reg",
               y = "Irreg",
               font = list(color = "white"),
               showarrow = FALSE,
               text = as.character(table_mat()[1,1]),
               xref = "x1",
               yref = "y1"),
                      list(
               x = "Reg",
               y = "Reg",
               font = list(color = "white"),
               showarrow = FALSE,
               text = as.character(table_mat()[2,1]),
               xref = "x1",
               yref = "y1"),
                    list(
               x = "Irreg",
               y = "Irreg",
               font = list(color = "black"),
               showarrow = FALSE,
               text = as.character(table_mat()[1,2]),
               xref = "x1",
               yref = "y1"),
                      list(
               x = "Irreg",
               y = "Reg",
               font = list(color = "black"),
               showarrow = FALSE,
               text = as.character(table_mat()[2,2]),
               xref = "x1",
               yref = "y1")),
             xaxis = list(title = "Estado Previsto"),
             yaxis = list(title = "Estado Real")
            )
    
})

```


Analítica (Detalhes) {data-icon="fa-chart-bar"}
==============================================================

Column {.tabset}
--------------------------------------------------------------

### Detecção Bayesiana
Detecção bayesiana de ponto de mudança

```{r}
selectInput(inputId= "UC", label= "Unidade Consumidora:",
            
            ## complete.cases
            choices= unique(uc_cons_sem_na$UC),
            selected= 27755665,
            width = "200px")

selectInput(inputId= "metodo", label= "Método de identificação:",
            choices= c("Uma mudança no máximo" ='AMOC', 
                       "Pruned Exact Linear Time" = 'PELT', 
                       "Segmentação Binária" = 'BinSeg'),
            selected= 'AMOC',
            width = "300px")
```

``` {r}

chpt <- function(vetor, met) {
  library(changepoint)
  result <- cpt.mean(vetor, method= met)
  #ChangePoint <-cpts(result)
  return(list(result@cpts, result@param.est))
}
# t<-unique(perdas_am[perdas_am$COD_UN_CONS_UEE== 4268229 ,35:98])[which(is.na(unique(perdas_am[perdas_am$COD_UN_CONS_UEE== 4268229 ,35:98]))==FALSE)]
#t<-t[-which(is.na(t))]

#r <- c(rnorm(50, mean= 100, sd= 1), rnorm(50, mean= 75, sd= 5))

ts <- reactive(unique(perdas_am[perdas_am$COD_UN_CONS_UEE == input$UC, 35:98])[which(is.na(unique(perdas_am[perdas_am$COD_UN_CONS_UEE== input$UC ,35:98]))==FALSE)])

# Indica os índices na série temporal em que há mudança de comportamento
padrao_rep <- reactive(chpt(as.numeric(ts()), input$metodo)[[1]])
padrao_med <- reactive(chpt(as.numeric(ts()), input$metodo)[[2]][[1]])

df <- reactive(data.frame("Data"= seq(as.Date("2014/1/1"), 
                                      by = "month", 
                                      length.out = length(ts())),
                          "Consumos" = t(ts()),
                          # Repete os consumos-padrão conforme o ponto de mudança
                          "Padrao"= rep(padrao_med(), 
                                        c(padrao_rep()[[1]][1], diff(padrao_rep()))) 
                          )
              )

#dt_tr_tit <- reactive(perdas_am[perdas_am$COD_UN_CONS_UEE == input$UC, 3])

dt_tr_tit <- reactive({perdas_am %>%
                        filter(COD_UN_CONS_UEE == input$UC) %>%
                          select(`TRUNC(UEE.DTA_INIC_HIST_UEE)`) %>%
                            max(as.Date("2014/1/1"))
                      })

cons_tr_tit <- reactive({df() %>% 
                            filter(Data == "2017/1/1") %>%
                              select(Consumos)
                        })

renderPlotly({df() %>%
              plot_ly(x = ~Data,
                      mode = 'lines',
                      width = '650',
                      height = '350'
                      #margin = list(l = 50, r = 50, b = 100, t = 100, pad = 4)
                      ) %>%
              add_lines(y = ~Consumos, line = list(color = "#00526d", width = 3),
                        name = "Consumo") %>%
              add_lines(y = ~Padrao, line = list(color = "#de6e6e", width = 4),
                        name = "Padrão") %>%
              # add_markers(x = as.yearmon(dt_tr_tit()), y = cons_tr_tit(), 
              #             marker = list(size = 15, color = "#00526d"),
              #             showlegend = F) %>%
              # # Anotações
              # annotations= list(list(xref = "plot", 
              #                        yref = "plot", 
              #                        xanchor = "left",
              #                        yanchor = "right",
              #                        x = as.yearmon("2017-11-01"), y = 40, 
              #                        showarrow = F, align = "left",
              #                        text = "<b>Troca de Titularidade</b>",
              #                        font = list(size = 12, family = "Arial"),
              #                        bgcolor = "white"),
              #                   paper_bgcolor = "#f2f2f2",
              #                   margin = list(l = 18, r = 30, t = 18),
              #                   width = 1024, 
              #                   height = 600)
              
              layout(title = "Histórico mensais e padrões de consumo",
                     xaxis = list(title= "Período", titlefont= f),
                     yaxis = list(title= "Consumo [kWh]", titlefont= f),
                     hovermode = 'x',
                     autosize = F)

              })
```

### Dados Adicionais

```{r}

tbl <- data.frame(
  id = 1:10,
  name = c("Bob", "Ashley", "James", "David", "Jenny",
    "Hans", "Leo", "John", "Emily", "Lee"),
  age = c(28, 27, 30, 28, 29, 29, 27, 27, 31, 30),
  grade = c("C", "A", "A", "C", "B", "B", "B", "A", "C", "C"),
  test1_score = c(8.9, 9.5, 9.6, 8.9, 9.1, 9.3, 9.3, 9.9, 8.5, 8.6),
  test2_score = c(9.1, 9.1, 9.2, 9.1, 8.9, 8.5, 9.2, 9.3, 9.1, 8.8),
  final_score = c(9, 9.3, 9.4, 9, 9, 8.9, 9.25, 9.6, 8.8, 8.7),
  registered = c(TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE),
  stringsAsFactors = FALSE)

library(formattable)

f <- formattable(tbl, list(
  age = color_tile("white", "orange"),
  grade = formatter("span", style = x ~ ifelse(x == "A",
    style(color = "green", font.weight = "bold"), NA)),
  area(col = c(test1_score, test2_score)) ~ normalize_bar("pink", 0.2),
  final_score = formatter("span",
    style = x ~ style(color = ifelse(rank(-x) <= 3, "green", "gray")),
    x ~ sprintf("%.2f (rank: %02d)", x, rank(-x))),
  registered = formatter("span",
    style = x ~ style(color = ifelse(x, "green", "red")),
    x ~ icontext(ifelse(x, "ok", "remove"), ifelse(x, "Yes", "No")))
))

#renderFormattable(f)

col_interesse <- c('COD_UN_CONS_UEE', 'DES_BAIR_BAI', 'DES_CLAS_PRIN_CCL', 'QTD_CARG_UEE', 'Score','Perfil_Cliente_Modelo')

col_renome <- c('UC', 'Bairro', 'Classe', 'Carga', 'Score','Perfil','Media_Consumo','DesvioPadrão')

perdas_resumo <- perdas_am[,col_interesse]
estats<-estat_uc_bai_clas[,c(-2,-3)]
perdas_resumo2<-merge(perdas_resumo,estats,by.x='COD_UN_CONS_UEE',by.y ="UC",all.x = TRUE)

colnames(perdas_resumo2) <- col_renome
perdas_resumo2<-perdas_resumo2[order(-perdas_resumo2$Score),]

# searchInput(
#             inputId = "search", label = "Busque aqui",
#             placeholder = "Seu texto...",
#             btnSearch = icon("search"),
#             btnReset = icon("remove"),
#             width = "450px"
#     )

table_ad<-reactive({perdas_resumo2 %>% filter(UC==input$UC) %>% select(col_renome) %>% distinct(UC,.keep_all=TRUE)})



ff <- reactive(formattable(table_ad(), 
                  list(Score = formatter("span",
                       style = x ~ style(color = ifelse(x/100<input$threshold, "green", "red"))),
                       Perfil = formatter("span",
                       style = x ~ style(color = ifelse(x=="Regular", "green", "red")),
                       x ~ icontext(ifelse(x=="Regular", "ok", "remove"), ifelse(x=="Regular", "Regular", "Irregular")))
                       )
                  ))

# renderDT(ff, 
#          rownames= F, 
#          extension= 'Buttons', 
#          filter= "top", 
#          editable= T,
#          options= list(dom = 'Blfrtip',
#                        buttons= c('copy', 'csv', 'excel'),
#                        lengthMenu= list(c(10,50,100,-1), c(10,50, 100, 'Todos'))
#                        )
#          )

renderFormattable(ff())

```

```{r, error=TRUE}
# #install.packages('DT')
# renderDT(updateData(), 
#          rownames= F, 
#          extension= 'Buttons', 
#          filter= "top", 
#          editable= T,
#          options= list(dom = 'Blfrtip',
#                        buttons= c('copy', 'csv', 'excel'),
#                        lengthMenu= list(c(10,50,100,-1), c(10,50, 100, 'Todos'))
#          )
# )

```

Column
--------------------------------------------------------------

### Histograma da Unidade Consumidora {data-height=540}

```{r}
# selectInput(inputId= "UC2", label= "Unidade Consumidora:",
#             choices= unique(perdas_am$COD_UN_CONS_UEE),
#             selected= 27755665)

sliderInput(inputId = "n_bins",
            label = "Largura das faixas:",
            min = 1,
            max = 20, 
            value = 8, 
            step = 1,
            width= '300px')

cl <- reactive(subset(estat_uc_bai_clas, UC == input$UC, select= Classe)[[1]])
media_cla <- reactive(subset(estat_cons_clas, Classe == cl(), select= Cons_Medio)[[1]])
desvio_cla <- reactive(subset(estat_cons_clas, Classe == cl(), select= Desv_Pad)[[1]])

normal <- reactive(data.frame("Cons" = seq(from= media_cla() - 4*desvio_cla(), 
                                           to= media_cla() + 4*desvio_cla(), 
                                           by= .1),
                              "Prob" = dnorm(seq(from= media_cla() - 4*desvio_cla(), 
                                                 to= media_cla() + 4*desvio_cla(), 
                                                 by= .1),
                                             mean= media_cla(), 
                                             sd= desvio_cla())
                             )
                   )

 renderPlotly({df() %>%
              plot_ly(x= ~Consumos, type= "histogram", histnorm= "probability",
                      autobinx = F, 
                      xbins= list(#start= min(Consumos), 
                                  #end= max(Consumos), 
                                  size= input$n_bins),
                      width = '550',
                      height = '150') %>%
              layout(xaxis = list(title= "Consumo [kWh]", titlefont= f),
                     yaxis = list(title= "Probabilidade", titlefont= f),
                     hovermode = 'x')
              })

```


### Comparativo com Classe de Consumo {data-height=360}

```{r}

#Adicionar caixa seleção bairro

cl <- reactive(subset(estat_uc_bai_clas, UC == input$UC, select= Classe)[[1]])
media_cla <- reactive(subset(estat_cons_clas, Classe == cl(), select= Cons_Medio)[[1]])
desvio_cla <- reactive(subset(estat_cons_clas, Classe == cl(), select= Desv_Pad)[[1]])
media_uc <- reactive(subset(estat_uc_bai_clas, UC == input$UC, select= Cons_Med_UC)[[1]])

normal <- reactive(data.frame("Cons" = seq(from= media_cla() - 4*desvio_cla(), 
                                           to= media_cla() + 4*desvio_cla(), 
                                           by= .1),
                              "Prob" = dnorm(seq(from= media_cla() - 4*desvio_cla(), 
                                                 to= media_cla() + 4*desvio_cla(), 
                                                 by= .1),
                                             mean= media_cla(), 
                                             sd= desvio_cla())
                             #  "Prob_Lim" = c(dnorm(seq(from= media_cla() - 4*desvio_cla(), 
                             #                     to= media_uc(), 
                             #                     by= .1),
                             #                 mean= media_cla(), 
                             #                 sd= desvio_cla()),
                             #                 rep(0, (media_cla() + 4*desvio_cla()- media_uc())/.1) +1)
                              )
                   )

 renderPlotly({normal() %>%
               plot_ly(x = ~Cons, mode = 'lines',
                       width = '550',
                       height = '150') %>%
               add_lines(y = ~Prob, line = list(color = "#00526d", width = 3),
                         name = "Consumo") %>%
               #add_lines(y = ~Prob_Lim, line = list(color = "#00526d", width = 3),
                         #fill ='tozeroy', fillcolor = '#e763fa', name = "Consumo_Inf") %>%
               layout(title = paste0("Histograma típico ", cl()),
                      xaxis = list(title= "Consumo [kWh]", titlefont= f),
                      yaxis = list(title= "Probabilidade", titlefont= f),
                      hovermode = 'x')})
```

### Chance de Irregularidade (XGBoost Score) {data-height=100}

```{r}
score<-reactive(perdas_am %>% filter(COD_UN_CONS_UEE==input$UC) %>% select(Score))
Perfil<-reactive(perdas_am %>% filter(COD_UN_CONS_UEE==input$UC) %>% select(Perfil_Cliente_Modelo))


renderValueBox(valueBox(paste(paste0("Score: ",max(score()),"%"),paste("Perfil:",ifelse(nrow(Perfil())==1,Perfil(),ifelse((Perfil()[1,1]==Perfil()[2,1] & Perfil()[2,1]=="Não-Fraudador"),"Regular","Irregular"))),sep= "\n"),
         icon=  ifelse(max(score())/100 > .5, "fa-cat", "fa-check-square"),
         color= ifelse(max(score())/100 > .5, "warning", "primary")    
         ))


```
