R/olap_sales.R

In kimjam/srms: Package to automate tasks and reporting for OCD SRMS Team.

#' @title olap_sales_transform
#' @description A function to reshape olap sales data for use by investigators.
#'
#' @return Invisibly returns transformed data frame.
#' @export
olap_sales_transform <- function() {
  # refresh olap_sales file with python script
  system2(command = 'python',
          args = system.file('python/refresh_sales.py', package = 'srms'))

  sales <- openxlsx::read.xlsx('~/raw_data/olap_sales.xlsx',
                               startRow = 7) %>%
    setNames(c('level_5_desc', 'product_code', 'product_desc',
               'yyyymm', 'units'))

  sales[1:3] %<>% lapply(zoo::na.locf)

  sales %<>%
    dplyr::filter(yyyymm <= strftime(last_month(), format = '%Y%m')) %>%
    tidyr::spread(yyyymm, units, fill = 0)

  cl <- c('Chemistry Slides', 'Microtip Reagents', 'Microwell Reagents')
  tm <- c('BioVue Reagents', 'Donor Screening Reagents', 'IDMTS Reagents',
          'Traditional IH Reagents')

  cl_df <- sales %>% dplyr::filter(level_5_desc %in% cl)
  tm_df <- sales %>% dplyr::filter(level_5_desc %in% tm)

  cl_path <- file.path(
    'L:', 'Rochester-Quality Regulatory Compliance', 'SRMS', 'SRMS Wrkspace',
    'Customer Quality Monthly Analysis Reports', '2017 Reports and Data',
    strftime(last_month(), format = '%m_%B'),
    paste0('CL_Sales_', strftime(last_month(), format = '%b%Y'), '.xlsx')
  )

  tm_path <- file.path(
    'L:', 'Raritan-Product Vigilance', 'TM Sales Information', '2017 Sales',
    strftime(last_month(), format = '%m_%B'),
    paste0('TM_Sales_', strftime(last_month(), format = '%b%Y'), '.xlsx')
  )

  openxlsx::write.xlsx(x = cl_df, file = cl_path)
  openxlsx::write.xlsx(x = tm_df, file = tm_path)

  invisible(list('cl_sales' = cl_df, 'tm_sales' = tm_df))
}