Add create_service_request_line_items()

[pbchase]

Write a function create_service_request_line_items(). This function assembles parts of the service request, project details, and line item history to make a whole line item.

Params

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

Get the dependencies

Call these functions:

• get_service_request_lines() - from Issue #205
• get_ctsi_study_id_to_project_id_map() - from Issue #208
• get_project_details_for_billing() - from Issue #204

Join the above data and select the fields needed to create an invoice_line_item_record. Where variables are in conflict, prefer the fact from get_service_request_lines(). This will happen with PI and IRB details. coalesce() each of these pairs and list the column from get_service_request_lines() first. @tlstoffs and @pbchase discussed this detail and decided we should honor the service request.

This function cannot know the ID of these rows until it inserted, so do not set the ID in this function. ID will be set at insertion by the script that calls this function.

Return the result.

[pbchase]

Add a column fiscal_contact_name that is a paste of the fn and ln fields. Do NOT remove the fn and ln fields.

mutate(fiscal_contact_name = paste(fiscal_contact_fn, fiscal_contact_ln)) |>

[pbchase]

Would get_service_request_line_items() be a better name for this function?

[saipavan10-git]

I named the function compile_service_request_line_items() would this be the right name for it?

[pbchase]

get_service_request_line_items()

I think I'd rather go with get_service_request_line_items()

[pbchase]

@saipavan10-git says we have a problem. The commented-out columns here have no values:

result <- result |>
  dplyr::select(
    "service_identifier",
    "service_type_code",
    "service_instance_id",
    "ctsi_study_id",
    # "name_of_service", # Not returned by get_service_request_lines()
    # "name_of_service_instance", # Not returned by get_service_request_lines()
    "other_system_invoicing_comments",
    "price_of_service",
    "qty_provided",
    "amount_due",
    # "fiscal_year", # Not returned by get_service_request_lines()
    # "month_invoiced", # Not returned by get_service_request_lines()
    "pi_last_name",
    "pi_first_name",
    "pi_email",
    # "gatorlink", # Not returned by get_service_request_lines()
    # "reason", # Not returned by get_service_request_lines()
    # "status",
    # "created",
    # "updated",
    "fiscal_contact_fn",
    "fiscal_contact_ln",
    "fiscal_contact_name",
  )

[pbchase]

Let's address that below by adapting code from get_new_project_invoice_line_items()

  previous_month_name <- rcc.billing::previous_month(
    lubridate::month(redcapcustodian::get_script_run_time())
    ) |>
    lubridate::month(label = TRUE, abbr = FALSE)

  fiscal_year_invoiced <- rcc.billing::fiscal_years |>
    dplyr::filter((redcapcustodian::get_script_run_time() - lubridate::dmonths(1)) %within% .data$fy_interval) |>
    dplyr::slice_head(n = 1) |> # HACK: overlaps may occur on July 1, just choose the earlier year
    dplyr::pull(.data$csbt_label)

...
    dplyr::mutate(
      name_of_service = "Biomedical Informatics Consulting",
      name_of_service_instance = .data$app_title,  # app_title is returned by get_project_details_for_billing()
      fiscal_year = fiscal_year_invoiced,
      month_invoiced = previous_month_name,
      gatorlink = .data$username, # let's modify get_service_request_lines() to also return username so we can use it here.
      reason = "new_item",
      status = "draft",
      created = redcapcustodian::get_script_run_time(),
      updated = redcapcustodian::get_script_run_time()
    ) |>

This proposed solution will require a modification to get_service_request_lines(). That is described in issue #234. That issues should be rolled into the PR for this one.

[pbchase]

Addressed by #233