#' balanta UI Function
#'
#' @description A shiny Module.
#'
#' @param id,input,output,session Internal parameters for {shiny}.
#'
#' @noRd
#'
#' @importFrom shiny NS tagList
mod_balanta_ui <- function(id){
ns <- NS(id)
bs4Dash::tabsetPanel(
id = ns("tab_upload"),
selected = TRUE,
shiny::tabPanel(
title = "Database",
icon = icon("database")),
# mod_balanta_database_ui("balanta_database_ui_1")),
shiny::tabPanel(
title = "Upload",
icon = icon("upload"),
br(),
fluidPage( shinyjs::useShinyjs(),
shinyFeedback::useShinyFeedback(),
fluidRow( column(width = 3,
fileInput( inputId = ns("balanta_upload"),
accept = c(".xls", ".xlsx"),width = "250px",
placeholder = "No file uploaded",
buttonLabel = "Excel only",
label = "Upload balanta de verificare") ),
column(width = 3, uiOutput(ns("show_balanta_date"))),
column(width = 6, div(style="display:inline-block;margin-left: 60%;padding-top: 15px;",
uiOutput(ns("show_down") )) ),
column(width = 12, DT::dataTableOutput(ns("verificare_conturi")), br() ),
column(width = 3, uiOutput(ns("show_save"))),
br(), hr(),
uiOutput(ns("missing_banks"))
)
)
)
)
}
#' balanta Server Functions
#'
#' @noRd
mod_balanta_server <- function(id, vals){
moduleServer( id, function(input, output, session) {
ns <- session$ns
coresp_banci_curente <- readRDS("R/reactivedata/coresp_banci_curente.rds")
coresp_banci_depozite <- readRDS("R/reactivedata/coresp_banci_depozite.rds")
vals_balanta_upload <- reactiveValues( nume_obligatorii = c("Simbol cont","Denumire cont",
"Solduri finale|Debit", "Solduri finale|Credit"), check_banks=TRUE )
updateTabsetPanel(session = session, inputId = 'tab_upload',selected = "Database")
observeEvent(input$balanta_upload,{
shiny::validate(shiny::need(tools::file_ext(input$balanta_upload$datapath) %in% c("xlsx","xls"),
message = paste0("XLSX only! You uploaded a ",tools::file_ext(input$balanta_upload$datapath)," file")))
vals_balanta_upload$file_input = input$balanta_upload$datapath
mod_read_excel_server("read_excel_ui_1",excel_reactive = vals_balanta_upload, red = "#dd4b39") })
observeEvent(vals_balanta_upload$all_names,{ req(vals_balanta_upload$all_names == TRUE)
output$show_balanta_date <- renderUI( shinyWidgets::airDatepickerInput(inputId = session$ns("balanta_date"),
label = "Selecteaza data balantei uploadate",value = vals$report_date,language = "ro",
width = "250px") )
# I deduct sold credit form sold debit and keep only the new processed sold debit
vals_balanta_upload$file_read_prel <- vals_balanta_upload$file_read_prel %>%
dplyr::mutate("Solduri finale|Debit" = `Solduri finale|Debit` - `Solduri finale|Credit`) %>%
dplyr::select(-"Solduri finale|Credit")
conturi_curente_banci <- reactive({
conturi_curente <- vals_balanta_upload$file_read_prel %>%
dplyr::slice(stringr::str_which(string = `Simbol cont`,
pattern = rebus::`%R%`('^271', rebus::zero_or_more(rebus::DIGIT) )))
conturi_curente <- conturi_curente %>% dplyr::left_join(coresp_banci_curente %>% dplyr::select(1, 3),
by = "Simbol cont")
return( conturi_curente %>% dplyr::slice( which(
purrr::map( conturi_curente$`Simbol cont`,
~ sum( stringr::str_detect( string = conturi_curente$`Simbol cont`,
pattern = rebus::`%R%`(.x, rebus::one_or_more(rebus::DIGIT)) ) ) == 0) %>% unlist() == TRUE)) %>%
dplyr::filter(`Solduri finale|Debit` != 0) %>%
dplyr::mutate(tip_plasament = ifelse( stringr::str_detect(string = `Denumire cont`, pattern =
"gestionari|cautiune|mobiliara|CAUTIUNE|Cautiune"), "Gestionari_Cautiuni_Garantii", "Conturi_Curente" ),
tip_sursa = ifelse(stringr::str_detect(string = `Denumire cont`,
pattern = "MADR|administrare|SAPARD"), "Surse_Administrare", "Surse_Proprii") )
) })
conturi_depozite_banci <- reactive({
conturi_depozite <- vals_balanta_upload$file_read_prel %>%
dplyr::slice(stringr::str_which(string = `Simbol cont`,
pattern = rebus::`%R%`('^272', rebus::zero_or_more(rebus::DIGIT) )))
conturi_depozite <- conturi_depozite %>% dplyr::left_join(coresp_banci_depozite %>% dplyr::select(1, 3),
by = "Simbol cont")
return( conturi_depozite %>% dplyr::slice( which(
purrr::map( conturi_depozite$`Simbol cont`,
~ sum( stringr::str_detect(string = conturi_depozite$`Simbol cont`,
pattern = rebus::`%R%`(.x, rebus::one_or_more(rebus::DIGIT))) ) == 0 ) %>% unlist() == TRUE )) %>%
dplyr::filter(`Solduri finale|Debit` != 0) %>%
dplyr::mutate(tip_plasament = ifelse( stringr::str_detect(string = `Denumire cont`,
pattern = "mobiliara"),"Gestionari_Cautiuni_Garantii", "Depozite"),
tip_sursa = ifelse(stringr::str_detect(string = `Denumire cont`,
pattern = "MADR|administrare|SAPARD"), "Surse_Administrare", "Surse_Proprii") )
) })
titluri <- reactive({
extract_titluri <- vals_balanta_upload$file_read_prel %>%
dplyr::slice(stringr::str_which(string = `Simbol cont`,
pattern = rebus::`%R%`('^304', rebus::zero_or_more(rebus::DIGIT))))
return(extract_titluri %>% dplyr::slice(which(
purrr::map(extract_titluri$`Simbol cont`,
~ sum(stringr::str_detect(string = extract_titluri$`Simbol cont`,
pattern = rebus::`%R%`(.x, rebus::one_or_more(rebus::DIGIT)) ) ) == 0 ) %>% unlist() == TRUE)) %>%
dplyr::filter(`Solduri finale|Debit` != 0) %>% dplyr::mutate(
tip_plasament = ifelse(stringr::str_detect(string = `Denumire cont`,
pattern = "SMALL FINANCE"),"obligatiuni_corporative", "titluri_stat"),
tip_sursa = ifelse(stringr::str_detect(string = `Denumire cont`,
pattern = "MADR|administrare|SAPARD"),"Surse_Administrare","Surse_Proprii"),
Banca = ifelse(stringr::str_detect(string = `Denumire cont`, pattern = "SMALL FINANCE"),
"SMALL FINANCE", "ROMANIA" ) )
) })
trezorerie_detaliat <- reactive({ req(conturi_curente_banci(), conturi_depozite_banci(), titluri() )
dplyr::bind_rows(conturi_curente_banci(), conturi_depozite_banci(), titluri() ) })
vals_balanta_upload$df_new <- trezorerie_detaliat()
# I do not use it for the moment
conturi_banci_sinteza <- reactive({ req( trezorerie_detaliat() )
trezorerie_detaliat() %>% tidyr::pivot_wider(names_from = c(tip_plasament, tip_sursa),
names_sep = "_",values_from = "Solduri finale|Debit",values_fill = 0) %>%
dplyr::select(-c(1:2)) %>% dplyr::group_by(Banca) %>% dplyr::summarise_all(.funs = ~sum(.)) %>%
dplyr::mutate(Expunere_banca =rowSums(x = dplyr::select_if(.tbl = .,.predicate = is.numeric)))
})
output$conturi_banci <- DT::renderDataTable({
DT::datatable(width = "350px",data = conturi_banci_sinteza() %>%
janitor::adorn_totals(where = "row",fill = "-") %>%
dplyr::arrange(desc(Expunere_banca)) %>%
dplyr::mutate(row_names = 0:nrow(conturi_banci_sinteza())) %>%
tibble::column_to_rownames('row_names'),
rownames = TRUE, options = list(dom = "tp", pageLength=6,autoWidth = TRUE, scrollX=TRUE,
columnDefs = list(list(width = '50%', targets = list(2:3)))),
caption = htmltools::tags$caption(style = 'caption-side: top; text-align: left;',
'Trezoreria FNGCIMM') ) %>%
DT::formatRound(columns = 2:9,digits = 0)
})
output$show_down <- renderUI( { req(conturi_curente_banci(), conturi_depozite_banci())
downloadLink(session$ns("down_conturi"),label = "Downloadeaza balanta prelucrata") })
#output$down_conturi <- downloadHandler(filename = function() {"banci_conturi.csv"},content = function(file) {
# readr::write_csv(x = conturi_banci () %>% dplyr::select(-1) %>%
# dplyr::group_by(Banca) %>% dplyr::summarise_all(.funs = ~sum(.)) %>%
# janitor::adorn_totals(where = "row",fill = "-"),file=file)})
output$down_conturi <- downloadHandler(filename = function() { paste0("balanta_",input$balanta_date,".csv") },
content = function(file) { readr::write_csv(x = vals_balanta_upload$df_new, file=file) } )
output$verificare_conturi <- DT::renderDataTable( { req(trezorerie_detaliat)
DT::datatable(options = list(dom = "t"), data =
data.frame(row.names = c("Titluri","Conturi Curente", "Depozite, din care:", "Depozite mobiliare"),stringsAsFactors = T,
Balanta = c(
vals_balanta_upload$file_read_prel %>%
dplyr::filter(`Simbol cont` == "304") %>% dplyr::pull(`Solduri finale|Debit`),
vals_balanta_upload$file_read_prel %>%
dplyr::filter(`Simbol cont` == "271") %>% dplyr::pull(`Solduri finale|Debit`),
vals_balanta_upload$file_read_prel %>%
dplyr::filter(`Simbol cont` == "272") %>% dplyr::pull(`Solduri finale|Debit`),
vals_balanta_upload$file_read_prel %>% dplyr::slice(stringr::str_which(string = `Simbol cont`,
rebus::`%R%`('^272',rebus::zero_or_more(rebus::DIGIT)))) %>%
dplyr::slice( stringr::str_which(string = `Denumire cont`,
pattern = "gestionari|cautiune|mobiliara|CAUTIUNE|Cautiune") ) %>%
dplyr::pull(`Solduri finale|Debit`) %>% sum(.)),
Prelucrat = c( trezorerie_detaliat() %>% dplyr::slice(stringr::str_which(string = `Simbol cont`,
rebus::`%R%`('^304',rebus::zero_or_more(rebus::DIGIT)))) %>%
dplyr::pull(`Solduri finale|Debit`) %>% sum(.),
trezorerie_detaliat() %>% dplyr::slice(stringr::str_which(string = `Simbol cont`,
rebus::`%R%`('^271',rebus::zero_or_more(rebus::DIGIT)))) %>%
dplyr::pull(`Solduri finale|Debit`) %>% sum(.),
trezorerie_detaliat() %>% dplyr::slice(stringr::str_which(string = `Simbol cont`,
rebus::`%R%`('^272',rebus::zero_or_more(rebus::DIGIT)))) %>%
dplyr::pull(`Solduri finale|Debit`) %>% sum(.),
trezorerie_detaliat() %>% dplyr::slice(stringr::str_which(string = `Simbol cont`,
rebus::`%R%`('^272',rebus::zero_or_more(rebus::DIGIT)))) %>%
dplyr::slice(stringr::str_which(string = `Denumire cont`,
pattern = "gestionari|cautiune|mobiliara|CAUTIUNE|Cautiune")) %>%
dplyr::pull(`Solduri finale|Debit`) %>% sum(.)
) ) %>% dplyr::mutate(Check = ifelse(abs(Prelucrat - Balanta) >1, "Check","OK")) ) %>%
DT::formatRound(columns = 1:2,digits = 0) %>%
DT::formatStyle(table = .,
columns = 3,valueColumns = 3, backgroundColor = DT::styleInterval("Check",c("gray","yellow")))})
})
# I need a separate observer, because input$date is accessible very late due to the fact that is renderUI
observeEvent(input$balanta_date,{
vals_balanta_upload$df_new <- vals_balanta_upload$df_new %>% dplyr::mutate(data_balanta = input$balanta_date)
})
#Below observer shows missing banks UI for the case when there are new accounting accounts not assigned
# to banks within coresp_banci_curente or coresp_banci_depozite
observeEvent(vals_balanta_upload$df_new,{
output$show_save <- renderUI({ req(vals_balanta_upload$check_banks == FALSE)
actionLink(inputId = session$ns("save_balanta"),label = "Salveaza balanta prelucrata",icon = icon("save")) })
if(!any(is.na(vals_balanta_upload$df_new$Banca))) {
vals_balanta_upload$check_banks <- FALSE }
else {
output$missing_banks <- renderUI({
fluidRow(
column(width = 12,
h3("STOP, nu am identificat toate bancile.Completeaza in coloana 3:") ),
column(width = 12,rhandsontable::rHandsontableOutput(session$ns("fill_banks")) ),
column(width = 6,
actionLink(inputId = session$ns("save_missing_banks"),label = "Salveaza bancile modificate", icon=icon("save")))
)
})
output$fill_banks <- rhandsontable::renderRHandsontable({
rhandsontable::rhandsontable(data=vals_balanta_upload$df_new %>% dplyr::filter(is.na(Banca)) %>%
dplyr::select(1:2,4), rowHeaders = NULL,readOnly = F,colHeaders =
c("Simbol Cont", "Denumire Cont", "Selecteaza Finantatorul")) %>%
rhandsontable::hot_col(col = 3,type = "dropdown",
source = unique(c(coresp_banci_depozite$Banca,coresp_banci_curente$Banca))) %>%
rhandsontable::hot_col(col = 1:2,readOnly = TRUE) })
shinyjs::disable(id = "save_missing_banks",asis = FALSE)
}
})
# Below observer handles filling inside rhandontable
observeEvent(input$fill_banks,{
vals_balanta_upload$fill_banks <- rhandsontable::hot_to_r(input$fill_banks)
if (!any(is.null( vals_balanta_upload$fill_blanks$Banca))) {
vals_balanta_upload$check_banks <- FALSE }
})
observeEvent(input$save_missing_banks,{
shinyWidgets::ask_confirmation(inputId = session$ns("confirm_fill_banks"),
title = "CONFIRM?", type = "info",
text = paste0("Esti sigur ca vrei sa salvezi bancile completate ?"),
btn_labels = c("NU, renunta","OK, salveaza"),btn_colors = c("#ff007b","#00ff84"))
})
observeEvent(input$confirm_fill_banks,{ req(input$confirm_fill_banks == TRUE)
if (any(is.na(vals_balanta_upload$fill_banks$Banca)) ) {
shinyWidgets::sendSweetAlert(session = session,title = "STOP",text = "Am in continuare banci lipsa",
type="error",btn_colors = "#ff007b") }
else {
coresp_banci_curente <- dplyr::bind_rows(coresp_banci_curente,vals_balanta_upload$fill_banks %>%
dplyr::slice(stringr::str_which(string = `Simbol cont`,
pattern = rebus::`%R%`('^271', rebus::zero_or_more(rebus::DIGIT)))))
coresp_banci_depozite <- dplyr::bind_rows(coresp_banci_depozite, vals_balanta_upload$fill_banks %>%
dplyr::slice(stringr::str_which(string = `Simbol cont`,
pattern = rebus::`%R%`('^272', rebus::zero_or_more(rebus::DIGIT)))))
saveRDS(object = coresp_banci_curente, file = "R/reactivedata/balanta/coresp_banci_curente.rds")
saveRDS(object = coresp_banci_depozite, file = "R/reactivedata/balanta/coresp_banci_depozite.rds")
shinyFeedback::showToast(type = "success",title = "SUCCES",message = "Saved to database",
.options = list("timeOut"=1500, 'positionClass'="toast-bottom-right", "progressBar" = TRUE))
vals_balanta_upload$check_banks <- FALSE
}
})
observeEvent(input$save_balanta,{
shinyWidgets::ask_confirmation(inputId = session$ns("confirm_save"),title = "CONFIRM?",
text = paste0("Esti sigur ca vrei sa salvezi balanta la data de ", input$balanta_date, " ?"),
btn_labels = c("NU, renunta","OK, salveaza"),btn_colors = c("#ff007b","#00ff84"),type = "info")
})
observeEvent(input$confirm_save,{ req(input$confirm_save == TRUE)
balanta_database <- readRDS("R/reactivedata/balanta_database.rds")
vals_balanta_upload$df_old <- balanta_database
# I don`t need df$new, is already defined above
#vals_balanta_upload$df_new <- trezorerie_detaliat()
vals_balanta_upload$element_id <- as.Date(input$balanta_date)
vals_balanta_upload$column_id = "data_balanta"
vals_balanta_upload$finalise_process_compare_df = FALSE
callModule(mod_compare_df_server, "compare_df_ui_1", df_reactive = vals_balanta_upload, red="#ff007b",green="#00ff84")
})
observeEvent(vals_balanta_upload$finalise_process_compare_df,{ req(vals_balanta_upload$finalise_process_compare_df == TRUE )
saveRDS(object = vals_balanta_upload$df_new_prel,file = "R/reactivedata/balanta/balanta_database.rds")
shinyFeedback::showToast(type = "success",title = "SUCCES",message = "Saved to database",
.options = list("timeOut"=1500, 'positionClass'="toast-bottom-right", "progressBar" = TRUE))
vals_balanta_upload$finalise_process_compare_df <- FALSE
shinyjs::disable(id = "save_balanta")
})
})
}
## To be copied in the UI
# mod_balanta_ui("balanta_ui_1")
## To be copied in the server
# mod_balanta_server("balanta_ui_1")
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.