Make etl/cancel_invoice_line_items.R reusable

[pbchase]

Here is some fully functional code I called etl/cancel_invoice_line_items.R. I ran this against prod on 2022-11-07 to cancel five invoice line items we never should have created. We might want refactor this body of this into a function we can call from any other process.

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

init_etl("cancel_invoice_line_items")

rc_conn <- connect_to_redcap_db()
rcc_billing_conn <- connect_to_rcc_billing_db()

# manually describe the invoices to revise
fiscal_year_of_interest = "2022-2023"
month_invoiced_of_interest  = "October"

service_instance_ids <- c(
  "1-7554",
  "1-7565",
  "1-9314",
  "1-11039",
  "1-11041"
)

# Read the records we will need to revise
invoice_line_item_initial <- tbl(rcc_billing_conn, "invoice_line_item") %>%
  filter(status != "canceled") %>%
  filter(fiscal_year == fiscal_year_of_interest) %>%
  filter(month_invoiced == month_invoiced_of_interest) %>%
  filter(service_instance_id %in% service_instance_ids) %>%
  select(id, service_instance_id, fiscal_year, month_invoiced, status, updated) %>%
  collect()

# create the dataset of updates
invoice_line_item_updates <- invoice_line_item_initial %>%
  mutate(status = "canceled") %>%
  mutate(updated = redcapcustodian::get_script_run_time())

# write those updates
invoice_line_item_sync_activity <- redcapcustodian::sync_table_2(
  conn = rcc_billing_conn,
  table_name = "invoice_line_item",
  source = invoice_line_item_updates,
  source_pk = "id",
  target = invoice_line_item_initial,
  target_pk = "id"
)

# log what we did
activity_log <- list(
  invoice_line_item_updates = invoice_line_item_sync_activity$update_records
)

log_job_success(jsonlite::toJSON(activity_log))

DBI::dbDisconnect(rcc_billing_conn)
DBI::dbDisconnect(rc_conn)