R/quarterly_reportables.R

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

#' @title quarterly_reportables
#'
#' @description Function to generate quarterly reportable events tables / raw data.
#'
#' @param filepath Filepath of Reportable Events data.
#' @param xlsxname Name of Excel Workbook to write tables and raw data to.
#' @param write If TRUE, will write to Excel Workbook. Defaults to TRUE.
#'
#' @return Returns a dataframe of  summary table.
#' @export

quarterly_reportables <- function(
  filepath,
  xlsxname = NA,
  write = TRUE
) {

  data <- openxlsx::read.xlsx(
    xlsxFile = filepath,
    sheet = 1,
    startRow = 2
  )

  split <- strsplit(data$Title, ' ')

  data$`Complaint Number` <- as.character(lapply(split, function(x) x[2]))
  data$callarea <- as.character(lapply(split, function(x) x[4]))

  aggregate <- data %>%
    dplyr::select(
      callarea,
      Product.Name,
      PMS.Group,
      `Catalogue.#`
    )

  summary <- aggregate %>%
    dplyr::group_by(
      Product.Name,
      `Catalogue.#`,
      PMS.Group,
      callarea
    ) %>%
    dplyr::summarise(
      `Number of Reports` = n()
    ) %>%
    dplyr::mutate(
      `Call Area for Event > 5` = callarea
    ) %>%
    dplyr::ungroup() %>%
    dplyr::select(
      Product.Name,
      PMS.Group,
      `Call Area for Event > 5`,
      `Number of Reports`
    ) %>%
    dplyr::arrange(
      PMS.Group,
      -`Number of Reports`
    ) %>%
    dplyr::mutate(
      Comments = ''
    )

  bypn <- aggregate %>%
    dplyr::group_by(
      Product.Name,
      `Catalogue.#`,
      PMS.Group
    ) %>%
    dplyr::summarise(
      `Number of Reports` = n()
    ) %>%
    split(
      .,
      .$`Number of Reports` > 5
    )

  rollup <- aggregate %>%
    dplyr::left_join(
      y = bypn[[2]] %>%
        dplyr::ungroup() %>%
        dplyr::mutate(
          take = 'yes'
        ) %>%
        dplyr::select(
          -`Number of Reports`,
          -PMS.Group
        ),
      by = c('Product.Name', 'Catalogue.#')
    ) %>%
    dplyr::filter(
      is.na(take)
    ) %>%
    dplyr::select(-take) %>%
    dplyr::group_by(
      Product.Name,
      `Catalogue.#`,
      PMS.Group
    ) %>%
    dplyr::summarise(
      `Number of Reports` = n()
    ) %>%
    dplyr::mutate(
      `Call Area for Event > 5` = ''
    ) %>%
    dplyr::ungroup() %>%
    dplyr::select(
      Product.Name,
      PMS.Group,
      `Call Area for Event > 5`,
      `Number of Reports`
    ) %>%
    dplyr::arrange(
      PMS.Group,
      Product.Name,
      -`Number of Reports`
    ) %>%
    dplyr::mutate(
      Comments = ''
    )

  byca <- aggregate %>%
    dplyr::left_join(
      y = bypn[[2]] %>%
        dplyr::ungroup() %>%
        dplyr::mutate(
          take = 'yes'
        ) %>%
        dplyr::select(
          -`Number of Reports`,
          -PMS.Group
        ),
      by = c('Product.Name', 'Catalogue.#')
    ) %>%
    dplyr::filter(
      take == 'yes'
    ) %>%
    dplyr::select(-take) %>%
    dplyr::group_by(
      Product.Name,
      `Catalogue.#`,
      PMS.Group,
      callarea
    ) %>%
    dplyr::summarise(
      `Number of Reports` = n()
    ) %>%
    dplyr::mutate(
      `Call Area for Event > 5` = callarea
    ) %>%
    dplyr::ungroup() %>%
    dplyr::select(
      Product.Name,
      PMS.Group,
      `Call Area for Event > 5`,
      `Number of Reports`
    ) %>%
    dplyr::arrange(
      PMS.Group,
      Product.Name,
      -`Number of Reports`
    ) %>%
    dplyr::mutate(
      Comments = ''
    )

  final <- rbind(byca, rollup) %>%
    dplyr::arrange(
      PMS.Group
    )

  # version without duplicate product names and pms groups removed
  returnable <- final

  mask <- duplicated(final$Product.Name)
  final$Product.Name[mask] <- ''
  final$PMS.Group[mask] <- ''
  names(final) <- gsub('\\.', ' ', names(final))

  if (write) {
    if (is.na(xlsxname)) {
      xlsxname <- file.path('~', 'reporting', 'quarterly', 'reportable_events',
                            'errors', lubridate::year(last_month()),
                            paste0('Q', lubridate::quarter(last_month())),
                            paste0('Reportable Events Summary ', lubridate::year(last_month()),
                                   paste0('Q', lubridate::quarter(last_month())), '.xlsx'))
    }

    wb <- openxlsx::createWorkbook()

    openxlsx::addWorksheet(
      wb = wb,
      sheetName = 'Summary Table'
    )

    openxlsx::writeDataTable(
      wb = wb,
      sheet = 'Summary Table',
      x = final,
      tableStyle = 'none',
      withFilter = FALSE
    )

    openxlsx::addWorksheet(
      wb = wb,
      sheetName = 'Reportable Raw'
    )

    openxlsx::writeDataTable(
      wb = wb,
      sheet = 'Reportable Raw',
      x = data,
      tableStyle = 'none',
      withFilter = FALSE
    )

    openxlsx::addWorksheet(
      wb = wb,
      sheetName = 'Summary Table Old Version'
    )

    openxlsx::writeDataTable(
      wb = wb,
      sheet = 'Summary Table Old Version',
      x = summary,
      tableStyle = 'none',
      withFilter = FALSE
    )

    openxlsx::saveWorkbook(
      wb = wb,
      file = xlsxname,
      overwrite = TRUE
    )
  }

  invisible(returnable)
}