Describe impending bill notification failures

[pbchase]

Write a report to describe impending bill notification failures. Name it impending_bill_notification_failures.
Enumerate every project that had an email notification failure logged by the script warn_owners_of_impending_bill.R. For each such project, provide the email address and message status for each message concerning that project sent by warn_owners_of_impending_bill.R.

Here's some pseudo-code Philip (and maybe Kyle?) wrote on 2023-02-07 to rough in what the data flow probably looks like:

library(redcapcustodian)
library(rcc.billing)
library(RMariaDB)
library(DBI)
library(tidyverse)
library(lubridate)
library(dotenv)
library(fs)

load_dot_env("prod.env")

init_etl("harvest_log_data_for_a_script")

rcc_billing_conn <- connect_to_rcc_billing_db()

log <- tbl(rcc_billing_conn, "rcc_job_log") %>%
  filter(script_name == "warn_owners_of_impending_bill") %>%
  collect()

# df <- job_summary_data %>%
#   jsonlite::fromJSON()

# Probably need to do map2 passing a vector of log_date and
# vector of job_summary_data to return a dataframe of
# log_date, recipient, projects, and error_message
#
# Then add value by adding delivered (T/F).
# Then separate projects into project_id and pivoting project_id longer.
# Then add columns delivered_count and failed_count after grouping by project_ids.
# Then add the columns service_instance_id, invoice_number by joining invoice details by project_id

# Then summarize the above dataframe into a dataframe with one row per project and all invoice details
# These commands might do the trick
# group_by(project_id) %>%
# distinct(project_id, .keep_all = T) %>%
# select(-c("addressee-specific columns")

[pbchase]

Here's a more complete example of how to spread the JSON-encoded job_summary_data:

library(dotenv)
load_dot_env("prod.env")

library(redcapcustodian)
library(rcc.billing)
library(RMariaDB)
library(DBI)
library(tidyverse)
library(lubridate)
library(dotenv)

init_etl("pbc_scratch")

log_conn <- get_package_scope_var("log_con")

log_data <- tbl(log_conn, "rcc_job_log") %>%
  dplyr::filter(script_name == "warn_owners_of_impending_bill") %>%
  collect()

log_ids <- log_data$id
names(log_ids) <- log_ids

purrr::map2_dfr(log_ids, log_data$job_summary_data, ~ jsonlite::fromJSON(.y), .id = "id") %>%
  mutate(id = as.integer(id)) %>%
  left_join(log_data %>% select(-job_summary_data), by = "id")

@ChemiKyle, I am wondering if I should turn the last 5 lines into a redcapcustodian function. Maybe call it spread_job_summary_data? or decode_job_summary_data? I feel like if I don't make it a function I should add it to the docs as a recipe.

[ChemiKyle]

@pbchase I think a function would be best, otherwise we're just going to be copying from the recipe for any log analyses.

[pbchase]

I made an issue to add such a function to redcapcustodian: ctsit/redcapcustodian#109

[pbchase]

If we do this, use redcapcustodian::unnest_job_summary_data_json_object()