R/olap_ib_etl.R

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

#' @title olap_ib_etl
#' @description Parses Installbase data from SSAS Cube and loads into qrc_raw db.
#'
#' @param filepath path to Excel workbook connected to SSAS Cube.
#'
#' @export
#' @return Invisibly returnsed parsed data.
olap_ib_etl <- function(filepath = '~/raw_data/olap_ib.xlsx') {
  # refresh olap_ib file with python script
  system2(command = 'python',
          args = system.file('python/refresh_ib.py', package = 'srms'))

  ib <- openxlsx::read.xlsx(filepath, startRow = 4, sheet = 'COUNTRY')

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

  names(ib) <- c('region', 'country', 'analyzer', 'yyyymm', 'installbase')

  ib %<>%
    dplyr::mutate(
      region = dplyr::case_when(
        .$region == 'Asia Pacific' ~ 'ASPAC',
        .$region == 'Greater China' ~ 'CHINA',
        .$region == 'Latin America' ~ 'LAR',
        .$region == 'North America' ~ 'NAR',
        .$region == 'Russian and CIS' ~ 'EMEA',
        .$region == 'Japan' ~ 'JAPAN',
        TRUE ~ .$region),
      analyzer = dplyr::case_when(
        .$analyzer == '350' ~ '250',
        .$analyzer == 'IMMUNO SYS'  ~ '3600',
        .$analyzer == 'INTEGRATED SYS' ~ '5600',
        .$analyzer == 'ECQ' ~ 'ECI',
        .$analyzer %in% c('LABAUT', 'INSTMGR') ~ 'enGen',
        .$analyzer %in% c('AUTOVUE I', 'AUTOVUE U') ~ 'AUTOVUE IU',
        .$analyzer %in% c('VISION BV', 'VISION MAX BV') ~ 'VISION BV / MAX BV',
        .$analyzer %in% c('VISION MTS', 'VISION MAX MTS') ~ 'VISION MTS / MAX MTS',
        TRUE ~ .$analyzer
      ),
      country = toupper(country)) %>%
    dplyr::group_by(region, country, analyzer, yyyymm) %>%
    dplyr::summarise(installbase = sum(installbase)) %>%
    tidyr::spread(yyyymm, installbase, fill = 0) %>%
    tidyr::gather(yyyymm, installbase, -c(region, country, analyzer)) %>%
    dplyr::ungroup()

  total <- ib %>%
    dplyr::group_by(analyzer, yyyymm) %>%
    dplyr::summarize(installbase = sum(installbase)) %>%
    dplyr::ungroup() %>%
    cbind(data.frame(region = rep('GLOBAL', nrow(.)),
                     country = rep('GLOBAL', nrow(.)),
                     stringsAsFactors = FALSE),
          .)

  ib %<>% rbind(total)

  qrc_insert(table = 'installbase',
             df = ib,
             db = 'qrc_raw',
             overwrite = TRUE)

  invisible(ib)
}