R/aged_complaints_report.R

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

#' @title aged_complaints_report
#' @description Function to create aged complaint workbook
#'
#' @param outpath File path to write workbook to.
#' @return Writes cleaned data to Excel workbook.
#' @export
aged_complaints_report <- function(outpath = NA) {
  # query for all currently open complaints
  # calculate days open (elapsed_days)
  # classify complaint into appropriate bin based on open time
  df <- pcd_query(query = queries$aged_complaints) %>%
    dplyr::mutate(
      elapsed_days = as.numeric(round(difftime(
        lubridate::floor_date(Sys.time(), 'day'),
        lubridate::floor_date(create_audit_date, 'day'),
        units = 'days'
      )))
    ) %>%
    dplyr::mutate(
      elapsed_days_bin = dplyr::case_when(
        .$elapsed_days < 60 ~ '< 60',
        dplyr::between(.$elapsed_days, 60, 179) ~ '60 - 179',
        dplyr::between(.$elapsed_days, 180, 359) ~ '180 - 359',
        .$elapsed_days > 359 ~ '>= 360',
        TRUE ~ 'NA'
      )
    ) %>%
    dplyr::arrange(-elapsed_days) %>%
    dplyr::select(complaint_number, create_audit_date, status_desc,
                  elapsed_days, dplyr::everything())

  # proportion of complaints in each bin
  summary <- df %>%
    dplyr::group_by(elapsed_days_bin) %>%
    dplyr::summarise(count = n()) %>%
    dplyr::mutate(
      percent_of_open_complaints = count / sum(count)
    )

  # reorder, increasing bin size, could make bin ordered factor...
  summary <- summary[c(1, 4, 3, 2), ]

  split <- df %>% split(.$elapsed_days_bin) %>%
    setNames(paste(names(.), 'days'))

  sheets <- names(split)[c(1, 4, 3, 2)]

  wb <- openxlsx::createWorkbook()

  openxlsx::addWorksheet(wb = wb, sheetName = 'summary')
  openxlsx::writeData(wb = wb, sheet = 'summary', x = summary)

  writer <- function(name) {
    openxlsx::addWorksheet(wb = wb, sheetName = name)
    openxlsx::writeData(wb = wb, sheet = name,
                        x = split[[name]] %>% dplyr::select(-elapsed_days_bin))
  }

  lapply(sheets, writer)

  if (is.na(outpath)) {
    outpath <- file.path('~', 'reporting', 'monthly', 'aged_complaints',
                         lubridate::year(last_month()),
                         strftime(last_month(), format = '%m-%b'),
                         paste0('aged_complaints_',
                                strftime(Sys.time(), format = '%d%b%Y'),
                                '.xlsx'))
  }

  openxlsx::saveWorkbook(wb = wb, file = outpath, overwrite = TRUE)

  invisible(list('summary' = summary, 'split' = split))
}