Enumerate the process we need to support for table-based account billing

[pbchase]

Billing for table-based users See https://docs.google.com/document/d/19xrUAZA0POeMGcmdJcvhfb_fSAfQfZC54t9TWWnp3dY/edit#heading=h.xdwq97tdopi8 where @pbchase and @tlstoffs wrote the rules to table based account billing. This process will require we mirror some of the functionality we created for annual billing, but not probably not all of it. These items are likely required:

warning of impending billing

We do not need a process for this as we will use the REDCap process for extending the expiration of an account. This works because we set the User's sponsor (secondary contact person) attribute of the each table-based account and we enable Sponsor Dashboard on the System-level User Settings page. See https://docs.google.com/document/d/19xrUAZA0POeMGcmdJcvhfb_fSAfQfZC54t9TWWnp3dY/edit#heading=h.ulkldjx9sgv8 for a sample message that is sent to the sponsor.

invoice_line_item creation

For APB (Annual Project Billing), this is done via create_and_send_new_redcap_prod_per_project_line_items.R. The novel code there is from line 74 target_projects <- tbl(rc_conn, "redcap_projects") %>% down through line 125 service_type_code = 1,. These lines are identifying the novel things to bill for.

The other 280ish lines would be common to most any line item creation code. This suggests an opportunity to refactor the 50ish lines of "what-to-bill" code and anything else that is specific to annual project billing into a function that is processed by the balance of the ETL. Then it would be simpler to make another function that conforms to the same interface but generates invoice line items for some other service.

Once re-factored into functions these, "what-to-bill" functions would be testable. We could add testing data and code much like the work done for get_orphaned_projects. We could test that they conform to the interface. We could test that each produces the correct outputs given a static input dataset

We should write each such line-item-creator function to respect CTSI Study IDs on related projects wherever possible.

Discussion

We must decide how we tie the user to a project. At the moment of creation, they are not tied to a project. Shortly after creation, they will be attached to one or more projects. The project(s) may or may not be mature, thus they may or may not have a CTSI Study ID associated. Either way, the account has a sponsor. The sponsor is a REDCap user. Each table-based account also has a record in a REDCap project. See Requesting table-based users, PID 11689 That data lists multiple contact facts:

sponsor_username
sponsor_email
pi_first_name
pi_last_name
pi_email
fiscal_name
fiscal_email

Why should use this info to set PI facts. We should share the sponsor and fiscal contacts with the CTSB. That would require some redesign to the file we send them

Yet none of these get us a CTSI Study ID. To do that, we need to look at affiliated projects. If there are multiple projects, we should select the oldest project with a matching PI Email address. Failing that, choose the oldest project. CTSI Study ID of the oldest project. If those algorithms yield no CTSI Study ID, send the new line item to CTSB without a CTSI Study ID.

receiving payment

For APB (Annual Project Billing), this is done via update_invoice_line_items_with_invoicing_details.R Nothing in that script is specific to APB. Notably at line 37 where we join by c("service_instance_id", "fiscal_year", "month_invoiced"). Because the month is included, we can invoice for a line item multiple times per year--all the way up to monthly. This is valuable, because we want to bill for accounts every 6 months in alignment with their expiration cycle.

We should modify update_invoice_line_items_with_invoicing_details.R to set CTSI_Study IDs of related service_instance_ids.

Analogs to sequestration - aka automated expiration after N days of non-payment

In APB, if a project is not paid, we remind people via the UI. Later we sequester the project. In APB, we will let the Sponsor's request to renew the account drive the charging. If they let it expire, we never charge. If they push out the expiration date beyond the paid date, we generate a line item.

We will need a script to expire the account if they do not pay the invoice. We will need to put a reason into the comment to assure that TLS and Beverly know to complain to the PI about the non-payment when the
unexpired an account. As with the sequestration plan, the expiry code would need to re-expire the account at the start of the next month.

Report of table-based accounts

I think we will need a report something like billable_candidates.R that tells us the billing status of every table-based user. We would probably want to see these fields:

c(
  username,
  user_suspended_time,
  user_lastlogin,
  sponsor_username,
  sponsor_email,
  pi_first_name,
  pi_last_name,
  pi_email,
  fiscal_name,
  fiscal_email,
  related_project_ids,
  ctsi_study_id_imputed,
  ctsi_study_id_assigned,
  is_invoiced_but_not_paid,
  invoice_number,
  fiscal_year,
  month_invoiced,
  status.line_item
)

Sharing CTSI Study IDs

We should design a process to share CTSI Study IDs between related things. We might need a place (a table?) to store provisional CTSI Study IDs for non-mature REDCap projects. This would allow table-based accounts to seed the creation of the CTSI-Study ID for a project before it is billed for the first time.

We could create a novel table, service_instance_relationships that is a many-to-many table showing service_instance_id_A, service_instance_id_B, and their relationship. It's unclear if relationship` is needed. We would only create it if there were different types of relationship and we saw value in the distinction.