R/refurbish_preprocess.R

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

#' @title refurbish_preprocess
#'
#' @description A function to preprocess equipment complaint data for quarterly
#' Refurbished Tableau template which can be found L:\\Rochester-Quality Regulatory Compliance\\SRMS\\SRMS Wrkspace\\SRMS_Analytics\\Refurbished EQ Quarterly Report.
#' Also writes raw data for complaints regarding Refurbished JNumbers for all countries and Spain individually.
#'
#' @param xlsxfile Name of Excel workbook to write cleaned data to.
#' Defaults to refurbish_raw_QXYYYY where X is quarter number and YYYY is the year.
#' @param write Defaults to TRUE. Writes to Excel Workbook if TRUE.
#'
#' @return Returns list of dataframes (Provue separate)  and writes to Excel workbooks. Also writes
#' raw refurbished data for all countries and Spain separately to 'Refurbished Equipment data analysis QXYYYY.xlsx'
#'
#' @export

refurbish_preprocess <- function(
  xlsxfile = NA,
  write = TRUE
) {

  Sys.setenv(R_ZIPCMD = 'C:/Rtools/bin/zip')

  raw_df <- srms::roc_eq_v26b(add = 'country') %>%
    dplyr::filter(
      Family_Code %in% c('250', 'FS', 'ECI', '5600', '3600'),
      Product_Number_T545CC != 3357
    )

  # classify each instance as refurbished or regular
  refurbished <- rep(NA, nrow(raw_df))
  refurbished[raw_df$Family_Code == '250'] <- ifelse(
    raw_df$Product_Number_T545CC[raw_df$Family_Code == '250'] == 3334,
    'Refurbished',
    'Regular'
  )
  refurbished[raw_df$Family_Code == 'FS'] <- ifelse(
    raw_df$Product_Number_T545CC[raw_df$Family_Code == 'FS'] == 3402,
    'Refurbished',
    'Regular'
  )
  refurbished[raw_df$Family_Code == 'ECI'] <- ifelse(
    raw_df$Product_Number_T545CC[raw_df$Family_Code == 'ECI'] == 3358,
    'Refurbished',
    'Regular'
  )
  refurbished[raw_df$Family_Code == '5600'] <- ifelse(
    raw_df$Product_Number_T545CC[raw_df$Family_Code == '5600'] == 5602,
    'Refurbished',
    'Regular'
  )
  refurbished[raw_df$Family_Code == '3600'] <- ifelse(
    raw_df$J_Number[raw_df$Family_Code == '3600'] %in% c(36000267,
                                                         36000409,
                                                         36000674),
    'Refurbished',
    'Regular'
  )

  raw_df$Refurbished <- refurbished

  tm <- srms::tm_general_template()

  provue <- refurb_filter %>%
    dplyr::filter(
      tolower(eq_type) == 'provue'
    ) %>%
    dplyr::mutate(
      serialnum = gsub('-', '', serialnum)
    )

  tm_refurb <- tm %>%
    dplyr::left_join(
      y = tm %>%
        dplyr::filter(
          gsub('-', '', J_Number) %in% unique(provue$serialnum),
          Call_Type %in% c('CE', 'PHSE', 'CERE', 'CSW')
        ) %>%
        dplyr::mutate(
          J_Number = gsub('-', '', J_Number)
        ) %>%
        dplyr::left_join(
          y = provue %>%
            dplyr::mutate(serialnum = gsub('-', '', serialnum)) %>%
            dplyr::select(serialnum, collectdate),
          by = c('J_Number' = 'serialnum')
        ) %>%
        dplyr::filter(
          Create_Audit_DT >= collectdate
        ) %>%
        dplyr::mutate(
          Refurbished = 'Refurbished'
        ) %>%
        dplyr::select(Complaint_Nbr, Create_Audit_DT, Date_Closed, Refurbished),
      by = c('Complaint_Nbr', 'Create_Audit_DT', 'Date_Closed')
    ) %>%
    dplyr::mutate(
      Refurbished = replace(Refurbished, is.na(Refurbished), 'Regular')
    ) %>%
    dplyr::filter(
      !duplicated(Complaint_Nbr)
    )

  if (write) {
    time <- time_vals()
    wb <- openxlsx::createWorkbook()

    for (type in unique(raw_df$Family_Code)) {
      openxlsx::addWorksheet(
        wb = wb,
        sheetName = type
      )

      openxlsx::addWorksheet(
        wb = wb,
        sheetName = paste0(type, '_Spain')
      )

      df <- raw_df %>%
        dplyr::filter(
          Family_Code == type,
          Refurbished == 'Refurbished'
        ) %>%
        dplyr::select(
          -Refurbished
        ) %>%
        dplyr::mutate(
          J_Number = gsub('[A-Z]', '', J_Number)
        )

      df_es <- raw_df %>%
        dplyr::filter(
          Family_Code == type,
          Refurbished == 'Refurbished',
          Country_Code == 'ES'
        ) %>%
        dplyr::select(
          -Refurbished
        ) %>%
        dplyr::mutate(
          J_Number = gsub('[A-Z]', '', J_Number)
        )

      openxlsx::writeDataTable(
        wb = wb,
        sheet = type,
        x = df,
        startRow = 1
      )

      openxlsx::writeDataTable(
        wb = wb,
        sheet = paste0(type, '_Spain'),
        x = df_es,
        startRow = 1
      )
    }

    openxlsx::addWorksheet(
      wb = wb,
      sheetName = 'Provue'
    )

    openxlsx::writeDataTable(
      wb = wb,
      sheet = 'Provue',
      x = tm_refurb %>%
        dplyr::filter(
          Refurbished == 'Refurbished'
        ),
      startRow = 1
    )

    openxlsx::saveWorkbook(
      wb = wb,
      file = paste0(
        'Refurbished Equipment data analysis ',
        time$quarter,
        time$year_quarter,
        '.xlsx'
      ),
      overwrite = TRUE
    )

    if (is.na(xlsxfile)) {
      xlsxfile <- paste0(
        'refurbish_raw_',
        time$year_quarter,
        time$quarter,
        '.xlsx'
      )
    }

    openxlsx::write.xlsx(
      x = raw_df,
      file = xlsxfile,
      sheetName = 'raw_data'
    )

    openxlsx::write.xlsx(
      x = tm_refurb,
      file = paste0(
        'refurbish_raw_provue',
        time$year_quarter,
        time$quarter,
        '.xlsx'
      )
    )
  }
  invisible(list(raw_df, tm_refurb))
}