claims_db/phclaims/load_raw/tables/load_load_raw.apcd_provider_master_full.R

In PHSKC-APDE/claims_data: Query and analyze WA State Medicaid, Medicare, and APCD data

#### CODE TO LOAD APCD PROVIDER_MASTER TABLES
# Eli Kern, PHSKC (APDE)
#
# 2019-10

### Run from master_apcd_full script
# https://github.com/PHSKC-APDE/claims_data/blob/main/claims_db/db_loader/apcd/master_apcd_full.R


load_load_raw.apcd_provider_master_full_f <- function(etl_date_min = NULL,
                                            etl_date_max = NULL,
                                            etl_delivery_date = NULL,
                                            etl_note = NULL,
                                            server = NULL) {
  
  ### Set table name part
  table_name_part <- "apcd_provider_master"
  
  ### Check entries are in place for ETL function
  if (is.null(etl_delivery_date) | is.null(etl_note)) {
    stop("Enter a delivery date and note for the ETL batch ID function")
  }
  
  ### Check entries are in place for ETL function
  if (is.null(server)) {
    stop("Enter a server name")
  }
  
  # Load ETL and QA functions if not already present
  if (exists("load_metadata_etl_log_f") == F) {
    devtools::source_url("https://raw.githubusercontent.com/PHSKC-APDE/claims_data/main/claims_db/db_loader/scripts_general/etl_log.R")
  }
  
  if (exists("qa_file_row_count_f") == F) {
    devtools::source_url("https://raw.githubusercontent.com/PHSKC-APDE/claims_data/main/claims_db/db_loader/scripts_general/qa_load_file.R")
  }
  
  
  
  #### SET UP BATCH ID ####
  # Eventually switch this function over to using glue_sql to stop unwanted SQL behavior
  current_batch_id <- load_metadata_etl_log_f(conn = db_claims, 
                                              batch_type = "full", 
                                              data_source = "APCD", 
                                              date_min = etl_date_min,
                                              date_max = etl_date_max,
                                              delivery_date = etl_delivery_date, 
                                              note = etl_note,
                                              server = "phclaims")
  
  if (is.na(current_batch_id)) {
    stop("No etl_batch_id. Check metadata.etl_log table")
  }
  
  
  #### LOAD TABLES ####
  print("Loading tables to SQL")
  load_table_from_file(conn = db_claims,
                         config_url = paste0("https://raw.githubusercontent.com/PHSKC-APDE/claims_data/main/claims_db/phclaims/load_raw/tables/load_load_raw.",
                                             table_name_part, "_full.yaml"),
                       overall = T, ind_yr = F, combine_yr = F, server = server, drop_index = F)
  
  
  #### ADD BATCH ID COLUMN ####
  print("Adding batch ID to SQL table")
  # Add column to the SQL table and set current batch to the default
  odbc::dbGetQuery(db_claims,
                   glue::glue_sql(
                     "ALTER TABLE load_raw.{`table_name_part`}
                   ADD etl_batch_id INTEGER 
                   DEFAULT {current_batch_id} WITH VALUES",
                     .con = db_claims))
  
  }