Wavelength: Wavelength

# PROJECT:  Wavelength
# AUTHOR:   A.Chafetz | USAID
# PURPOSE:  resolve incorrect submitted date known issue
# REF ID:   0f3fb879
# LICENSE:  MIT
# DATE:     2023-01-20
# UPDATED:
# NOTE:     based off rebooTZ/FY22_TZA_HFR-submission-fix.R

# DEPENDENCIES ------------------------------------------------------------

  library(tidyverse)
  library(gagglr)
  library(googlesheets4)
  library(googledrive)
  library(janitor)
  library(readxl)
  library(Wavelength)

# GLOBAL VARIABLES --------------------------------------------------------

  load_secrets("email")

  #hfr submission table
  gs_submissions <- as_sheets_id("1gQvY1KnjreRO3jl2wzuVCKmKjUUgZDwByVK1c-bzpYI")

  #country
  cntry <- "Uganda"

  #HFR template location
  folderpath_templates <- "templates/"


# DOWNLOAD INCORRECT SUBMISSIONS ------------------------------------------

  #read in file
  df_submissions <- read_sheet(gs_submissions,
                               .name_repair = make_clean_names)

  #limit to FY22 TZA files and take the last submission for each period
  df_gsubm_files <- df_submissions |>
    filter(operating_unit_country == cntry,
     stringr::str_detect(hfr_fy_and_period, "FY23")) |>
    group_by(hfr_fy_and_period) |>
    filter(timestamp == max(timestamp)) |>
    ungroup() |>
    mutate(file_id = str_extract(upload_your_hfr_file_s_here, "(?<=id=).*")) |>
    select(timestamp, hfr_pd = hfr_fy_and_period, file_id) |>
    mutate(filename = purrr::map(file_id, ~drive_get(as_id(.x))$name)) |>
    unnest(filename)

  #create a temp folder to store downloaded submissions
  temp_folder()

  #download all the submissions
  df_gsubm_files |>
    select(file_id, filename) |>
    pwalk(~drive_download(as_id(..1), path = file.path(folderpath_tmp, ..2)))

  #store file paths for looping over to read in
  df_files_tabs <- folderpath_tmp |>
    list.files(full.names = TRUE) |>
    map_dfr(~ tibble(file = .x,
                     tabs = excel_sheets(.x))) |>
    filter(str_detect(tabs, "HFR"))

# RUN FUNCTION OVER TABS & EXPORT NEW FILES -------------------------------

  pwalk(df_files_tabs,
        ~hfr_rectify_date(..1, ..2, folderpath_templates))

  #review files
  shell.exec(folderpath_tmp)