## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  error = FALSE,
  comment = "#>"
)

## ----setup, echo=FALSE--------------------------------------------------------
library(CohortIncidence)

## ----eval=FALSE---------------------------------------------------------------
# install.packages("remotes")
# remotes::install_github("ohdsi/CohortIncidence")

## ----comment = ""-------------------------------------------------------------
# Fetch DDL from package
ddl <- CohortIncidence::getResultsDdl()
cat(ddl)

## ----eval = FALSE-------------------------------------------------------------
# connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = "postgresql",server={Sys.getenv("server")}, port = Sys.getenv("port"))
# 
# # to specify the target schema (the typical use case):
# ddl <- SqlRender::render(CohortIncidence::getResultsDdl(), schemaName = "mySchema")
# 
# # a work-around to provide a prefix to the result table, in case creating new schema is restricted
# ddlPrefix <- SqlRender::render(CohortIncidence::getResultsDdl(), "schemaName.incidence_summary" = "mySchema.prefix_incidence_summary")
# 
# con <- DatabaseConnector::connect(connectionDetails)
# DatabaseConnector::executeSql(ddl)
# DatabaseConnector::disconnect(con)
# 

## -----------------------------------------------------------------------------
t1 <- CohortIncidence::createCohortRef(id=1, name="Target cohort 1")

o1 <- CohortIncidence::createOutcomeDef(id=1,name="Outcome 1, 30d Clean", 
                                               cohortId =2,
                                               cleanWindow =30)

tar1 <- CohortIncidence::createTimeAtRiskDef(id=1, 
                                             startWith="start", 
                                             endWith="end", 
                                             endOffset=30)

# Note: c() is used when dealing with an array of numbers, 
# later we use list() when dealing with an array of objects
analysis1 <- CohortIncidence::createIncidenceAnalysis(targets = c(t1$id),
                                                      outcomes = c(o1$id),
                                                      tars = c(tar1$id))

subgroup1 <- CohortIncidence::createCohortSubgroup(id=1, name="Subgroup 1", cohortRef = createCohortRef(id=300))


# Create Design (note use of list() here):
irDesign <- CohortIncidence::createIncidenceDesign(targetDefs = list(t1),
                                                   outcomeDefs = list(o1),
                                                   tars=list(tar1),
                                                   analysisList = list(analysis1),
                                                   subgroups = list(subgroup1))
# Render the design as JSON
irDesign$asJSON(pretty = T)


## -----------------------------------------------------------------------------
irDesignWithStrata <-
  CohortIncidence::createIncidenceDesign(
    targetDefs = list(t1),
    outcomeDefs = list(o1),
    tars = list(tar1),
    analysisList = list(analysis1),
    subgroups = list(subgroup1),
    #add by age and by gender strata, but don't do by start year.
    strataSettings = CohortIncidence::createStrataSettings(
      byGender = T,
      byAge = T,
      ageBreaks = list(17, 34, 65),
      ageBreakList = list(list(25), list(65))
    )
  )

## ----eval=FALSE---------------------------------------------------------------
# 
# buildOptions <- CohortIncidence::buildOptions(cohortTable = "demoCohortSchema.cohort",
#                                               cdmDatabaseSchema = "mycdm",
#                                               sourceName = "mysource",
#                                               refId = 1)
# 
# 
# executeResults <- CohortIncidence::executeAnalysis(connectionDetails = connectionDetails,
#                                                    incidenceDesign = irDesign,
#                                                    buildOptions = buildOptions)

## -----------------------------------------------------------------------------

buildOptions <- CohortIncidence::buildOptions(cohortTable = "demoCohortSchema.cohort",
                                              cdmDatabaseSchema = "mycdm",
                                              resultsDatabaseSchema = "myresults",
                                              sourceName = "mysource",
                                              refId = 1)

analysisSql <- CohortIncidence::buildQuery(incidenceDesign =  as.character(irDesign$asJSON()),
                                           buildOptions = buildOptions)
cat(analysisSql)


## ----eval=FALSE---------------------------------------------------------------
# 
# # if you didn't pass sourceName to buildOptions(), you can render it here
# analysisSql <- SqlRender::render(analysisSql, "sourceName" = "OptumDOD")
# analysisSql <- SqlRender::translate(analysisSql, "postgresql")
# 
# cat(analysisSql)
# 
# conn <- DatabaseConnector::connect(connectionDetails)
# DatabaseConnector::executeSql(conn, paste0("DELETE FROM myresults.incidence_summary WHERE ref_id = ", buildOptions$refId$intValue()))
# DatabaseConnector::executeSql(conn, analysisSql)
# DatabaseConnector::disconnect(conn)
# 

## ----eval=FALSE---------------------------------------------------------------
# 
# # given the prior irDesign constructed from the previous example
# buildOptions <- CohortIncidence::buildOptions(cohortTable = "demoCohortSchema.cohort",
#                                               cdmDatabaseSchema = "mycdm",
#                                               sourceName = "mysource"
#                                               useTempTables = T,
#                                               refId = 2)
# 
# analysisSql <- CohortIncidence::buildQuery(incidenceDesign = as.character(jsonlite::toJSON(irDesign)),
#                                            buildOptions = buildOptions)
# analysisSql <- SqlRender::translate(analysisSql, "postgresql")
# 
# # if we are using temp tables, the steps to execute the analysis are
# #   1) create result temp tables
# #   2) execute the analysis query, placing the results into the temp table incidence_summary
# #   3) Extract/copy the results from the temp tables
# #   4) clean up temp tables
# 
# conn <- DatabaseConnector::connect(connectionDetails)
# 
# tempDDL <- SqlRender::translate(CohortIncidence::getResultsDdl(useTempTables=T), "postgresql")
# DatabaseConnector::executeSql(conn, tempDDL)
# DatabaseConnector::executeSql(conn, analysisSql)
# 
# # In this example, copy to a permanent table from the temp table, but the results could be downloaded to CSV
# exportSql <- SqlRender::translate("insert into mySchema.prefix_incidence_summary select * from #incidence_summary", "postgresql");
# DatabaseConnector::executeSql(conn, exportSql)
# # or download the results to a dataframe
# results <- DatabaseConnector::querySql(conn, SqlRender::translate("select * from #incidence_summary", "postgresql"))
# 
# # use the getCleanupSql to fetch the DROP TABLE expressions for the tables that were created in tempDDL.
# cleanupSql <- SqlRender::translate(CohortIncidence::getCleanupSql(useTempTables=T), "postgresql")
# DatabaseConnector::executeSql(conn, cleanupSql)
# 
# DatabaseConnector::dbDisconnect(conn)
#