Write a function `get_service_request_lines()`

[pbchase]

Write a function get_service_request_lines(). This function will query Taryn's Service Request project to find completed, billable items. This process requires data from:

• REDCap Service Request
• rcc.billing.invoice_line_items

The function should have these features:

Params

1. Require a parameter, service_requests containing a subset of the records and a subset of the columns read from PID 1414.

Get service request details

request_details <- service_requests |>
  filter(is.na(redcap_repeat_instrument)) |>
  # Filter for completed service requests. 
  # How do I know this????
  mutate(service_identifier = paste(record_id)) |>
  mutate(service_type_code = 2) |>
  mutate(service_instance_id = paste(service_type_code, service_identifier, sep = "-")) |>
  mutate(username = coalesce(redcap_username, gatorlink)) |>
  separate_wider_delim(pi, delim = " ", names = c("pi_fn", "pi_ln")) |>
  mutate(
    pi_last_name = coalesce(pi_ln, last_name),
    pi_first_name = coalesce(pi_fn, first_name),
    pi_email = coalesce(pi_email, email)
  ) |>
  select(record_id, project_id, irb_number, submit_date, username, pi_last_name, pi_first_name, pi_email)

get service response details

response_details <- service_requests |>
  filter(!is.na(redcap_repeat_instrument)) |>
  # Filter for completed service requests. 
  # How do I know this????
  filter(!is.na(billable_rate)) |>
  mutate(probono = (billable_rate == 0)) |>
  rename(price_of_service = billable_rate) |>
  group_by(record_id, probono, price_of_service) |>
  mutate(time = service_request_time(time2, time_more)) |>
  summarize(qty_provided = sum(time), response = paste(response, collapse = " ")) |>
  ungroup() |>
  mutate(amount_due = price_of_service * qty_provided)

join responses and requests

request_lines <- request_details |>
  inner_join(response_details, by="record_id") |>
  mutate(service_instance_id = if_else(
    probono, 
    paste0(service_instance_id, "-PB"), 
    service_instance_id)) |>
  mutate(
    other_system_invoicing_comments =
      str_trim(
      str_sub(
        paste0(
          service_identifier,
          username,
          submit_date,
          if_else(probono, paste0("Pro-bono : ", response), response),
          sep = " : "
        ),
        1, 255
      )
      )
  ) |>
  select(
    record_id,
    project_id,
    service_identifier,
    service_type_code,
    service_instance_id,
    irb_number,
    pi_last_name,
    pi_first_name,
    pi_email,
    other_system_invoicing_comments,
    qty_provided,
    amount_due,
    price_of_service
  )

[ljwoodley]

@tlstoffs @pbchase I need access to REDCap Service Request project.

[pbchase]

@tlstoffs @pbchase I need access to REDCap Service Request project.

All fixed. Sorry about that. This is my recurring error

[pbchase]

Let's update the last line of Request Details to include the fiscal contact info.

select(
  record_id,
  project_id,
  irb_number,
  submit_date,
  username,
  pi_last_name,
  pi_first_name,
  pi_email,
# Add fiscal contact columns
  fiscal_contact_fn,
  fiscal_contact_ln,
  fiscal_contact_email
)

fiscal_contact_fn, fiscal_contact_ln, fiscal_contact_email are in draft in https://redcap.ctsi.ufl.edu/redcap/redcap_v14.3.0/ProjectSetup/index.php?pid=14952

These demand a similar update in the last lines of request_lines

select(
    record_id,
    project_id,
    service_identifier,
    service_type_code,
    service_instance_id,
    irb_number,
    pi_last_name,
    pi_first_name,
    pi_email,
# Add fiscal contact columns
    fiscal_contact_fn,
    fiscal_contact_ln,
    fiscal_contact_email,
# End fiscal contact columns
    other_system_invoicing_comments,
    qty_provided,
    amount_due,
    price_of_service
  )

[ljwoodley]

Must the pi_last_name and pi_first_name be clean? There are dirty values in the pi column. Some examples are.

Dr. Philip Chase
Philip Chase, MD
Philip Chase, Ph.D
n/a
Data Manager
person@ufl.edu

These will lead to incorrect values when separate_wider_delim(pi, delim = " ", names = c("pi_fn", "pi_ln")) is used request_details

@pbchase

[pbchase]

Must the pi_last_name and pi_first_name be clean?

We can't be responsible for all the garbage people type into that field so I'm not worried about it. If you can scrub some of the noise out with some str_replace(), please do. If there is still cruft, we do not care at this time. Perfect is the enemy of good.

[pbchase]

Addressed by PR #219