R/outputSQL.R

In songxh0424/MPrOVE-package: Helper functions for appropriateness metrics

#' Output SQL files
#' @description This function outputs several SQL files, based on information stored
#' in a TOML file and two template SQL files. 
#' @param tomlFile The file name of the TOML file to be used.
#' @param numTemplate The file name of the template SQL file for numerator.
#' @param eligTemplate The file name of the template SQL file for eligible. 
#' @param exclTemplate The file name of the template SQL file for exclusion.
#' @param years A vector of desired years to be pulled from the database. 
#' @param output.path The path to the directory where the output SQL files will be stored. 
#' @param option Choose from 1 to 3 to specify what table to be used in conversion. 
#' Choose 1 for ICD9to10, 2 for ICD10to9, and 3 for using both and returning the union of
#' two sets of results. Default is 1.
#' @param num Logical value to determine if imaging files should be generated. 
#' @param elig Logical value to determine if eligible files should be generated. 
#' @param excl Logical value to determine if exclusion files should be generated. 
#' @details For each input year, the output files 
#' include one for the numerator, one for the eligible and one for the exclusions. 
#' For year before 2015, the eligible file and exclusion file will use ICD-9 codes. 
#' Whereas for year after 2015, they will use ICD-10 codes. And for year 2015, they will
#' use both, so there will be two eligible files and two exclusion file. 
#' If the toml file does not contain exclusions, then no sql files will be generated for 
#' exclusions. This applies to eligible and numerator as well. 
#' 
#' @export
outputSQL <- function(tomlFile, numTemplate, eligTemplate, exclTemplate, years = 2013:2017, 
                      output.path = '', option = c("1", "2", "3"), num = TRUE, elig = TRUE, excl = TRUE) {
  # Read in head and tail for sql file
  sql.num <- readChar(numTemplate, file.info(numTemplate)$size)
  sql.elig <- readChar(eligTemplate, file.info(eligTemplate)$size)
  sql.excl <- readChar(exclTemplate, file.info(exclTemplate)$size)
  
  op <- match.arg(as.character(option), c("1", "2", "3"))

  # Create additional header string #
  comment <- sprintf('-- This file autogenerated by MPrOVE::outputSQL on %s\n', Sys.time())
  
  # Convert between ICD9 and ICD10 if needed #
  # Years before 2015 need ICD9 only, years after need ICD10 only
  
  # Loop over years and output sql files
  for(year in years) {
    yr <- str_sub(year, start = 3)
    
    ICDtypes <- c('ICD9','ICD10')
    if(year < 2015) ICDtypes <- 'ICD9'
    if(year > 2015) ICDtypes <- 'ICD10'
    
    for(ICDtype in ICDtypes) {
      # update DX type when needed
      elig.tmp <- str_replace(sql.elig, "DX_TYPE='ICDX'", sprintf("DX_TYPE='%s'", ICDtype))
      excl.tmp <- str_replace(sql.excl, "DX_TYPE='ICDX'", sprintf("DX_TYPE='%s'", ICDtype))
      # update year #
      elig.tmp  <- str_replace(elig.tmp, "DOS LIKE '%-YY'", sprintf("DOS LIKE '%%-%s'", yr))
      excl.tmp  <- str_replace(excl.tmp, "DOS LIKE '%-YY'", sprintf("DOS LIKE '%%-%s'", yr))
      num.tmp <- str_replace(sql.num, "DOS LIKE '%-YY'", sprintf("DOS LIKE '%%-%s'", yr))
      
      codeList <- toml2code(tomlFile, output = ICDtype, option = op)
      
      num.codes <- writeSQL(codeList$num, 'PX_CD', write.file = FALSE)
      elig.codes <- writeSQL(codeList$elig, 'DX_CD', write.file = FALSE,add.periods=TRUE)
      excl.codes <- writeSQL(codeList$excl, 'DX_CD', write.file = FALSE,add.periods=TRUE)
      
      null_flags <- c(is.null(num.codes), is.null(elig.codes), is.null(excl.codes))

      num.tmp <- ifelse(is.null(num.codes), num.tmp, str_replace(num.tmp, "--insert body here\n", num.codes))
      elig.tmp <- ifelse(is.null(elig.codes), elig.tmp, str_replace(elig.tmp, "--insert body here\n", elig.codes))
      excl.tmp <- ifelse(is.null(excl.codes), excl.tmp, str_replace(excl.tmp, "--insert body here\n", excl.codes))
      
      file1 = paste(output.path, sprintf("%s_num.sql", yr), sep = "")
      file2 = paste(output.path, sprintf("%s_elig_%s.sql", yr, ICDtype), sep = "")
      file3 = paste(output.path, sprintf("%s_excl_%s.sql", yr, ICDtype), sep = "")
      files = c(file1, file2, file3)
      tmp = c(num.tmp, elig.tmp, excl.tmp)
      # create
      for(f in files[(!null_flags) & c(num, elig, excl)]) {
        cat(comment, tmp[which(files == f)], sep = "", file = f)
      }
    }
  }
}

#' @export
updateSQLnum <- function(tomlFile, numTemplate, years = 2014:2017, output.path = '') {
  # Read in numerator template sql file
  sql.num <- readChar(numTemplate, file.info(numTemplate)$size)
  
  # Create additional header string #
  comment <- sprintf('-- This file autogenerated by MPrOVE::outputSQL on %s\n', Sys.time())
  
  # Loop over years and output sql files
  for(year in years) {
    yr <- str_sub(year, start = 3)
    num.tmp <- str_replace(sql.num, "DOS LIKE '%-YY'", sprintf("DOS LIKE '%%-%s'", yr))
    
    codeList <- toml2num(tomlFile)
    num.codes <- writeSQL(codeList$num, 'PX_CD', write.file = FALSE)
    num.tmp <- str_replace(num.tmp, "--insert body here\n", num.codes)
    file1 = paste(output.path, sprintf("%s_num.sql", yr), sep = "")
    
    # create file
    cat(comment, num.tmp, sep='', file = file1)
  }
}