Write a function, `get_ctsi_study_id_to_project_id_map()`

[pbchase]

Write a function, get_ctsi_study_id_to_project_id_map(). This function should read invoice line item records and service_requests to get unique, modern CTSI Study IDs for each REDCap Project.

Require a parameter, service_requests containing some subset (all??) of the records and columns read from PID 1414. Require a parameter, rc_billing_conn, a DBI connection object to an rcc.billing database containing an invoice_line_items table.

# Read invoice line item records
extant_invoice_line_items <- tbl(rc_billing_conn, "invoice_line_item") |>
  collect()

# Get unique, modern CTSI Study IDs for each REDCap Project
# Get them from extant_invoice_line_items. We need both the annual project
# billing line items and the service_request  line items. 
# We have to join the latter to the service request history to map
# service_request line items to the PIDs they relate to. 
result <-
  bind_rows(
    extant_invoice_line_items |>
      filter(service_type_code == 1 & !is.na(ctsi_study_id)) |>
      arrange(desc(id)) |>
      select(id, service_type_code, service_identifier, ctsi_study_id) |>
      rename(project_id = service_identifier),
    extant_invoice_line_items |>
      filter(service_type_code == 2 & !is.na(ctsi_study_id)) |>
      arrange(desc(id)) |>
      select(id, service_type_code, service_identifier, ctsi_study_id) |>
      inner_join(service_requests |> select(record_id, project_id),
        by = c("service_identifier" = "record_id")
      ) |>
      select(service_type_code, project_id, ctsi_study_id)
  ) |>
  arrange(desc(id)) |>
  distinct(project_id, ctsi_study_id)

return(result)

[ljwoodley]

extant_invoice_line_items |>
      filter(service_type_code == 2 & !is.na(ctsi_study_id)) |>
      arrange(desc(id)) |>
      select(id, service_type_code, service_identifier, ctsi_study_id)

There is no service_type_code == 2 in invoice_line_item and I don't see it being set in any of the issues in this Milestone. Will this value be set in a future etl?

[pbchase]

There is no service_type_code == 2 in invoice_line_item and I don't see it being set in any of the issues in this Milestone. Will this value be set in a future etl?

Yes, those records will be created when create_and_send_new_redcap_prod_per_project_line_items.R calls create_service_request_line_items(), though the issue to rename and modify that ETL is not yet written.

This entire milestone is about adding records in invoice_line_item with service_type_code == 2. These are the service request records (see issue #202). There are no such records at this time, but we will make them.

As to this issue, once those service request records are made, they will hold value data mapping new ctsi_study_ids to service requests which we can map to project IDs. That's the point of the code block you quote above.

[pbchase]

Addressed by PR #212