MPrOVE-R/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 denTemplate The file name of the template SQL file for denominator. 
#' @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.
#' @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. 
#' @examples 
#' ## suppose the working directory is MPrOVE
#' outputSQL('./toml/BackPain.toml', './sql/pacprPX_CD-template.sql', 
#'           './sql/pacprDX_CD-template.sql', output.path = './sql/pacpr/BackPain/')
#' @export
outputSQL <- function(tomlFile, numTemplate, denTemplate, years = 2013:2017, 
                      output.path = '', option = c("1", "2", "3")) {
  # Read in head and tail for sql file
  sql.num <- readChar(numTemplate, file.info(numTemplate)$size)
  sql.den <- readChar(denTemplate, file.info(denTemplate)$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
      den.tmp <- str_replace(sql.den, "DX_TYPE='ICDX'", sprintf("DX_TYPE='%s'", ICDtype))
      # update year #
      den.tmp  <- str_replace(den.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 <- str_replace(num.tmp, "--insert body here\n", num.codes)
      elig.tmp <- str_replace(den.tmp, "--insert body here\n", elig.codes)
      excl.tmp <- str_replace(den.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)
      # create
      cat(comment, num.tmp, sep='', file = file1)
      cat(comment, elig.tmp, sep='', file = file2)
      cat(comment, excl.tmp, sep='', file = file3)
      
      # delete files with empty body
      if(any(null_flags)) file.remove(files[null_flags])
    }
  }
}

#' @export
updateSQLnum <- function(tomlFile, numTemplate, years = 2014:2017, output.path = '') {
  # Read in head and tail for 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)
  }
}