R/backend_stats_readr.R

In neugelb/neugelbtools: Various small Neugelb tools

#' backend_stats
#'
#' Read in the backend stats report from Beate
#' @param file The Excel file with the backend stats
#' @param sheets The sheet number or numbers
#' @export

backend_stats <- function(file,sheets) {

  backend_data <- list()

  backend_data$transactions <- backend_stats_readr(file,sheets,'transactions')
  backend_data$summary <- backend_stats_readr(file,sheets,'summary')
  backend_data$participants <- backend_stats_readr(file,sheets,'participants')
  backend_data$customers <- backend_stats_readr(file,sheets,'customers')

  return(backend_data)

}

backend_stats_readr <- function(file,sheets,type) {

  require(readxl)
  require(neugelbtools)
  require(tidyverse)
  require(zoo)
  require(rlist)

if (type == 'transactions') {

  ranges <- c("B3:L12", "B16:L25", "B29:L38")

  label_ranges <- c('B1:B2','B14:B15','B27:B28')

} else if (type == 'summary') {

  ranges <- c('B42:L49','B53:L61','B65:L73')

  label_ranges <- c('B40:B41','B51:B52','B63:B64')

} else if (type == 'participants') {

  ranges <- c('B77:L83')

  label_ranges <- 'B75:B76'

} else if (type == 'customers') {

  ranges <- c('B87:L90','B94:L97','B101:L104')

  label_ranges <- c('B85:B86','B92:B93','B99:B100')

} else {

  stop('check the type field')

}

final_list <- list()

t1 <- tibble()

t2 <- tibble()

t3 <- tibble()

if (type == 'participants') {

  for (i in 1:length(sheets)) {

    z1 <- backend_stats_tbl(file,sheets[i],ranges[1],label_ranges[1])

    t1 <- rbind(t1,z1)

  }

  final_list <- list.append(final_list,t1)

} else {

  for (i in 1:length(sheets)) {

    z1 <- backend_stats_tbl(file,sheets[i],ranges[1],label_ranges[1])
    z2 <- backend_stats_tbl(file,sheets[i],ranges[2],label_ranges[2])
    z3 <- backend_stats_tbl(file,sheets[i],ranges[2],label_ranges[3])

    t1 <- rbind(t1,z1)
    t2 <- rbind(t2,z2)
    t3 <- rbind(t3,z3)

  }

  final_list <- list.append(final_list,t1)
  final_list <- list.append(final_list,t2)
  final_list <- list.append(final_list,t3)

}

return(final_list)

}

backend_stats_tbl <- function(file,sheet_nr,ranges,label_ranges) {

  temp <- read_excel(file,sheet=sheet_nr,range=ranges) %>%
    tidycols()

  deets <- read_excel(file,sheet_nr,range=label_ranges) %>%
    rename(x=1) %>%
    separate(x,into=c('data_group','month'),sep=' - ') %>%
    mutate(month = as.yearmon(str_replace(month,fixed('/'),'-')))

  gr <- quick_pull(deets,'data_group')
  m <- quick_pull(deets,'month')

  temp <- temp %>%
    mutate(data_group = gr,
           month = m)

  return(temp)

}