bUdgEtRack/server.R

# Server logic
# Budget variation tracking of Aramco
# ::::::Asitav Sen::::::
# ::LaNubia Consulting::

library(shiny)

# Define server logic 
shinyServer(function(input, output, session) {
  res_auth <- secure_server(
    check_credentials = check_credentials("cred.sqlite",
                                          passphrase = "kJuyhG657Hj&^%gshj*762hjsknh&662"),
    keep_token=TRUE
  )
  
  output$menu <- renderMenu({
    if (res_auth$admin == FALSE) {
      other.menu
    } else
      admin.menu
  })
  
  # Connect to DB
  connec <- dbConnect(
    RPostgres::Postgres(),
    dbname = dsn_database,
    host = dsn_hostname,
    port = dsn_port,
    user = dsn_uid,
    password = dsn_pwd
  )
  
  # On stop disconnect from DB
  onStop(function() {
    dbDisconnect(connec)
  })
  
  # Getting the data from DB
  dat <- reactive({
    dbGetQuery(connec,
               'SELECT * FROM calculated')
    #get.db.data('SELECT * FROM calculated')
  })
  
  # Getting deviation limits from DB
  lims <- reactive({
    dbGetQuery(connec,
               'SELECT * FROM limits')
    #get.db.data('SELECT * FROM limits')
  })
  
  # Getting the explanations from DB
  
  exp <- reactive({
    dbGetQuery(connec,
               'SELECT * FROM explanations')
    #get.db.data('SELECT * FROM explanations')
  })
  
  # Getting approval Table from DB
  approvals <- reactive({
    dbGetQuery(connec,
               'SELECT * FROM approvals')
    #get.db.data('SELECT * FROM approvals')
  })
  
  # Getting email ids
  
  emailids <- reactive({
    dbGetQuery(connec,
               'SELECT * FROM emails')
    #get.db.data('SELECT * FROM emails')
  })
  
  
  dat.need.exp <- reactive({
    req(dat())
    dat() |>
      inner_join(lims(), by = c("GL.account")) |>
      mutate(act.req = ifelse(devia.per > Limit &
                                devia > input$limittoignore, T, F)) |>
      filter(act.req) |>
      left_join(
        exp(),
        by = c(
          "month" = "month",
          "Cost.center" = "Cost.center",
          "GL.account" = "GL.account"
        )
      ) |>
      filter(is.na(explanation) | explanation == "")
  })
  
  # Update the gl account selector
  observeEvent(input$costcenter, {
    req(dat())
    choi <- dat() |>
      filter(Cost.center %in% input$costcenter) |>
      select(GL.account) |> distinct() |> pull()
    updatePickerInput(
      session = session,
      inputId = "glaccount",
      choices = choi,
      selected = choi
    )
    
  })
  
  
  observeEvent(c(input$costcenter, input$glaccount), {
    # Creating data that will be filtered using the GL and cost center selection
    dat.filtered <- dat() |>
      filter(Cost.center %in% input$costcenter) |>
      filter(GL.account %in% input$glaccount) |>
      mon.dev() |>
      filter(month == max(month))
    last.var.per <- dat.filtered |>
      pull(deviation.percent) #Last variation percent
    
    last.var.abs <- dat.filtered |>
      pull(devia) #Last variation absolute val
    
    last.exp.act <- dat.filtered |>
      pull(Actual) #Last expense
    
    valueServer(
      "variation",
      ttl = "Latest Variation %",
      n = last.var.per,
      icn = "percent",
      clr = "red",
      symbl = "%"
    )
    
    valueServer(
      "variabs",
      ttl = "Last Variation Amount",
      n = last.var.abs,
      icn = "dollar-sign",
      clr = "green",
      symbl = "$"
    )
    
    valueServer(
      "actexpe",
      ttl = "Last Expense",
      n = last.exp.act,
      icn = "coins",
      clr = "blue",
      symbl = "$"
    )
    
  })
  
  # Data selected after aplying the filters of cost center and gl account
  dat.selected <- reactive({
    req(dat())
    req(input$costcenter)
    req(input$glaccount)
    
    dat() |>
      filter(Cost.center %in% input$costcenter) |>
      filter(GL.account %in% input$glaccount)
  })
  
  # Monthly aggregate
  dat.monthly.per <- reactive({
    req(dat.selected())
    dat.selected() |>
      mutate(month = ym(month)) |>
      group_by(month) |>
      summarise(Plan = sum(Plan), Actual = sum(Actual)) |>
      mutate(devia = Actual - Plan) |>
      mutate(deviation.percent = round(devia * 100 / Plan, 2))
  })
  
  
  
  # Monthly percentage deviation
  output$monthlypervar <- renderEcharts4r({
    req(dat.monthly.per())

    
    e_chart(dat.monthly.per(), x = month) |>
      e_line(serie = deviation.percent,
             smooth = T,
             color = "cyan", opacity=0.8) |>
      e_area(serie = deviation.percent,
             smooth = T,
             color = "gray", opacity=0.6) |>
      e_axis_labels(x = "month", y = "Deviation") |>
      e_format_y_axis(suffix = " %") |>
      e_tooltip() |>
      e_legend(right = 100) |>
      e_datazoom(x_index = c(0, 1)) |>
      e_image_g(
        right = 50,
        top = 20,
        z = -999,
        style = list(
          image = "logo.png",
          width = 75,
          height = 75,
          opacity = .6
        )
      ) |>
      e_toolbox_feature(feature = c("saveAsImage", "dataView")) |>
      e_theme("roma")
  })
  
  # Monthly Absolute Deviation
  dat.monthly.abs <- reactive({
    dat.selected() |>
      filter(month == input$mont) |>
      mutate(cost_gl = paste0(Cost.center, "_", GL.account)) |>
      group_by(cost_gl) |>
      summarise(Plan = sum(Plan), Actual = sum(Actual)) |>
      mutate(devia = Actual - Plan) |>
      mutate(deviation.percent = round(devia * 100 / Plan, 2)) |>
      arrange(desc(deviation.percent))
  })
  
  # Monthly absolute by cost center + gl account
  output$monthlyabs <- renderEcharts4r({
    req(dat.monthly.abs())
    dat.monthly.abs() |>
      e_charts(cost_gl) |>
      e_bar(Plan, name = "Plan", color = "gray", opacity=0.6) |>
      e_step(Actual, name = "Actual", color = "red") |>
      e_axis_labels(x = "GL+Cost Center", y = "Deviation") |>
      #e_title("Plan Vs. Actual") |>
      e_tooltip() |>
      e_legend(right = 100) |>
      e_datazoom(x_index = 0, type = "slider") |>
      e_datazoom(y_index = 0, type = "slider") |>
      e_image_g(
        right = 50,
        top = 20,
        z = -999,
        style = list(
          image = "logo.png",
          width = 75,
          height = 75,
          opacity = .6
        )
      ) |>
      e_toolbox_feature(feature = c("saveAsImage", "dataView")) |>
      e_theme("roma")
  })
  
  output$monthlyabsvar <- renderEcharts4r({
    req(dat.monthly.per())
    
    e_chart(dat.monthly.per(), x = month) |>
      e_bar(serie = devia) |>
      e_axis_labels(x = "month", y = "Deviation") |>
      e_format_y_axis(suffix = "€") |>
      #e_title("Deviation Percentage by month") |>
      e_tooltip() |>
      e_legend(right = 100) |>
      e_datazoom(x_index = c(0, 1)) |>
      e_image_g(
        right = 50,
        top = 20,
        z = -999,
        style = list(
          image = "logo.png",
          width = 75,
          height = 75,
          opacity = .6
        )
      ) |>
      e_visual_map(type = "piecewise",
                   pieces = list(list(gt = 0,
                                      color = "red", opacity=0.5),
                                 list(lte = 0,
                                      color = "green", opacity=0.5))) |>
      e_toolbox_feature(feature = c("saveAsImage", "dataView")) |>
      e_theme("roma")
    
  })
  
  # Table of deviations without explanation
  
  
  datatableServer("devnoexp", dat.need.exp)
  
  # Notify
  
  observeEvent(input$notify, {
    req(dat.need.exp())
    emaildf<-dat.need.exp() |> 
      inner_join(emailids(), by=c("Cost.center"="Cost.center","GL.account"="GL.account"))
    print(emaildf)
    if(is.null(emaildf) | nrow(emaildf)==0){
      showModal(modalDialog("No Data"))
    } else {
      emails<-
        emaildf |> 
      select(email) |> pull() |> unique() 
      
      for(i in 1:length(emails)){
        tbl <- emaildf |> 
          filter(email==emails[i]) |> kbl()
        date_time <- add_readable_time()
        email <-
          compose_email(body = md(c(
            glue::glue(
              "Hello,
              
          Explanation required for expense deviation. Please visit xyz. Details are as follows.
          
          "
            ),
            tbl
          )),
          footer = md(glue::glue("Email sent on {date_time}.")))
        
        email |> 
          smtp_send(
            to = emails[i],
            from = "asitav.sen@lanubia.com",
            subject = "Testing",
            credentials = creds_file("email_creds")
          )
      }
      
      
      
      showModal(modalDialog(title = "Done"))
    }
      
  })
  
  # Approvals
  output$expnoapp <- renderExcel({
    req(approvals())
    appro <- approvals()
    row.names(appro) <- NULL
    appro <- appro[appro$approved == FALSE, ]
    columns = data.frame(
      title = colnames(appro),
      type = c('numeric', 'text', 'text', 'text', 'checkbox')
    )
    excelTable(data = appro, columns = columns, autoFill = TRUE)
    
  })
  
  # Update approvals
  observeEvent(input$approvalsubmit, {
    approved.now <- excel_to_R(input$expnoapp)
    if (is.null(input$expnoapp) || nrow(approved.now) == 0) {
      showModal(modalDialog(title = "Nothing to Upload"))
    } else{
      showModal(modalDialog(title = "Upload in Database?",
                            actionButton("finalapprovalsubmit", "Yes")))
    }
    
  })
  
  
  observeEvent(input$finalapprovalsubmit, {
    appro <- approvals()
    appro <- appro[appro$approved == TRUE, ]
    approved.now <- excel_to_R(input$expnoapp)
    appro <- rbind(appro, approved.now)
    if (nrow(approved.now) > 0) {
      if (dbWriteTable(connec, "approvals", appro, overwrite = TRUE)) {
        removeModal()
        session$reload()
      }
    }
    
    
  })
  
  # New Data
  fresh.dat <- reactive({
    req(input$inpfile)
    file <- input$inpfile
    ext <- tools::file_ext(file$datapath)
    req(file)
    validate(need(ext == "csv", "Please upload a csv file"))
    new.dat <- read.csv(file$datapath, header = T)
    new.dat |>
      mutate(devia = Actual - Plan) |>
      mutate(devia.per = round(devia / Plan, 2))
  }, label = "fresh")
  
  # Show uploaded data
  datatableServer("freshdat", fresh.dat)
  
  # Update DB
  observeEvent(input$datsubmit, {
    req(nrow(fresh.dat()) > 0)
    fresh.dt <- fresh.dat()
    if (is.null(fresh.dt) || nrow(fresh.dt) == 0) {
      showModal(modalDialog(title = "Nothing to Upload"))
    } else{
      showModal(modalDialog(title = "Upload in Database?",
                            actionButton("finaldatsubmit", "Yes")))
    }
    
  })
  
  
  observeEvent(input$finaldatsubmit, {
    fresh.dt <- fresh.dat()
    if (nrow(fresh.dt) > 0) {
      if (dbWriteTable(connec, "calculated", fresh.dt, append = TRUE)) {
        unique(fresh.dt$month) |> saveRDS("./data/months.RDS")
        removeModal()
        session$reload()
      }
    }
  })
  

  
  # Email id edit and show
  output$emailtable <- renderExcel({
    req(emailids())
    email.table <- emailids()
    columns = data.frame(title = colnames(email.table),
                         type = c('text', 'text', 'text'))
    excelTable(data = email.table, columns = columns, autoFill = TRUE)
    
  })
  
  # Email id table update
  
  observeEvent(input$emailsubmit, {
    email.table <- excel_to_R(input$emailtable)
    if (is.null(input$emailtable) || nrow(email.table) == 0) {
      showModal(modalDialog(title = "Nothing to Upload"))
    } else{
      showModal(modalDialog(title = "Upload in Database?",
                            actionButton("finalemailsubmit", "Yes")))
    }
    
  })
  
  
  observeEvent(input$finalemailsubmit, {
    email.table <- excel_to_R(input$emailtable)
    if (nrow(email.table) > 0) {
      if (dbWriteTable(connec, "emails", email.table, overwrite = TRUE)) {
        removeModal()
        session$reload()
      }
    }
  })
  
  # Limits (deviation rules) id edit and show
  output$limitable <- renderExcel({
    req(lims())
    limit.table <- lims()
    columns = data.frame(title = colnames(limit.table),
                         type = c('text', 'numeric'))
    excelTable(data = limit.table, columns = columns, autoFill = TRUE)
    
  })
  
  # Email id table update
  
  observeEvent(input$limitsubmit, {
    limit.table <- excel_to_R(input$limitable)
    if (is.null(input$limitable) || nrow(limit.table) == 0) {
      showModal(modalDialog(title = "Nothing to Upload"))
    } else{
      showModal(modalDialog(title = "Upload in Database?",
                            actionButton("finallimitsubmit", "Yes")))
    }
    
  })
  
  
  observeEvent(input$finallimitsubmit, {
    limit.table <- excel_to_R(input$limitable)
    if (nrow(limit.table) > 0) {
      if (dbWriteTable(connec, "limits", limit.table, overwrite = TRUE)) {
        removeModal()
        session$reload()
      }
    }
  })
  
  # User specific explanation filter
  user.accounts <- reactive({
    req(dat())
    req(emailids())
    req(exp())
    req(res_auth$email)
    dat() |>
      inner_join(lims(), by = c("GL.account")) |>
      mutate(act.req = ifelse(devia.per > Limit &
                                devia > input$limittoignore, T, F)) |> #change 500 to input
      filter(act.req) |>
      left_join(emailids(),
                by = c("Cost.center" = "Cost.center", "GL.account" = "GL.account")) |>
      left_join(
        exp(),
        by = c(
          "month" = "month",
          "Cost.center" = "Cost.center",
          "GL.account" = "GL.account"
        )
      ) |>
      filter(is.na(explanation) | explanation == "") |>
      filter(email == res_auth$email) |>
      select(-c(9, 10))
  })
  
  output$explanationtofill <- renderExcel({
    req(user.accounts())
    explanation.table <- user.accounts()
    columns = data.frame(
      title = colnames(explanation.table),
      type = c(
        'numeric',
        'text',
        'text',
        'numeric',
        'numeric',
        'numeric',
        'numeric',
        'numeric',
        'text'
      )
    )
    excelTable(data = explanation.table, columns = columns, autoFill = TRUE)
    
  })
  
  
  # Explanations table update
  
  observeEvent(input$explanationsubmit, {
    explanation.table <- excel_to_R(input$explanationtofill)
    if (is.null(input$explanationtofill) ||
        nrow(explanation.table) == 0) {
      showModal(modalDialog(title = "Nothing to Upload"))
    } else{
      showModal(modalDialog(title = "Upload in Database?",
                            actionButton("finalexpsubmit", "Yes")))
    }
    
  })
  
  
  observeEvent(input$finalexpsubmit, {
    explanation.table <- excel_to_R(input$explanationtofill)
    explanation.table <- explanation.table |>
      select(c(month, Cost.center, GL.account, explanation))
    if (nrow(explanation.table) > 0) {
      if (dbWriteTable(connec, "explanations", explanation.table, append = TRUE)) {
        appr <- explanation.table |> mutate(approved = F)
        if (dbWriteTable(connec, "approvals", appr, append = TRUE)) {
          removeModal()
          session$reload()
        }
        
      }
    }
  })
  
  
})