# Install CRAN version:
install.packages("ctrdata")

# Alternatively, install development version: 
install.packages("devtools")
devtools::install_github("rfhb/ctrdata", build_vignettes = TRUE)

library(ctrdata)

ctrOpenSearchPagesInBrowser()

# Please review and respect register copyrights:
ctrOpenSearchPagesInBrowser(copyright = TRUE)

q <- ctrGetQueryUrl()
# * Found search query from EUCTR: query=cancer&age=under-18&phase=phase-one&status=completed

q
#                                                   query-term  query-register
# 1 query=cancer&age=under-18&phase=phase-one&status=completed           EUCTR

# Connect to (or newly create) an SQLite database 
# that is stored in a file on the local system:
db <- nodbi::src_sqlite(
  dbname = "some_database_name.sqlite_file", 
  collection = "some_collection_name")

# See section Databases below 
# for MongoDB as alternative

# Retrieve trials from public register:
ctrLoadQueryIntoDb(
  queryterm = q,
  con = db)
# * Found search query from EUCTR: query=cancer&age=under-18&phase=phase-one&status=completed
# (1/3) Checking trials in EUCTR: 
# Retrieved overview, multiple records of 64 trial(s) from 4 page(s) to be downloaded.
# Checking helper binaries: done.
# Downloading trials (max. 10 pages in parallel)...
# Note: register server cannot compress data, transfer takes longer, about 0.4s per trial
# (2/3) Converting to JSON...
# (3/3) Importing JSON records into database...
# = Imported or updated 241 records on 64 trial(s).                                     
# * Updated history in meta-info of "some_collection_name"

# Get all records that have values in the fields of interest:
result <- dbGetFieldsIntoDf(
  fields = c(
    "a7_trial_is_part_of_a_paediatric_investigation_plan", 
    "p_end_of_trial_status", 
    "a2_eudract_number"),
  con = db)

# Find unique trial identifiers for trials that have nore than 
# one record, for example for several EU Member States: 
uniqueids <- dbFindIdsUniqueTrials(con = db)
# Searching for duplicate trials... 
#  - Getting trial ids, 241 found in collection
#  - Finding duplicates among registers' and sponsor ids...
#  - 177 EUCTR _id were not preferred EU Member State record for 64 trials
#  - Keeping 64 records from EUCTR
# = Returning keys (_id) of 64 records in collection "some_collection_name".

# Keep only unique / de-duplicated records:
result <- result[ result[["_id"]] %in% uniqueids, ]

# Tabulate the selected clinical trial information:
with(result, 
     table(
       p_end_of_trial_status, 
       a7_trial_is_part_of_a_paediatric_investigation_plan))
#                  a7_trial_is_part_of_a_paediatric_investigation_plan
# p_end_of_trial_status      Information not present in EudraCT No Yes
#   Completed                                                 6 31  15
#   GB - no longer in EU/EEA                                  0  4   4
#   Ongoing                                                   0  1   0
#   Prematurely Ended                                         1  1   0

# Retrieve trials from another register:
ctrLoadQueryIntoDb(
  queryterm = "cond=neuroblastoma&rslt=With&recrs=e&age=0&intr=Drug", 
  register = "CTGOV",
  con = db)
# * Found search query from CTGOV: cond=neuroblastoma&rslt=With&recrs=e&age=0&intr=Drug
# (1/3) Checking trials in CTGOV:
# Retrieved overview, records of 37 trial(s) are to be downloaded.
# Checking helper binaries: done.
# Downloading: 500 kB     
# (2/3) Converting to JSON...
# (3/3) Importing JSON records into database...
# = Imported or updated 37 trial(s).                                                   
# * Updated history in meta-info of "some_collection_name"

# Retrieve trials from another register:
ctrLoadQueryIntoDb(
  queryterm = "https://www.isrctn.com/search?q=neuroblastoma",
  con = db)
# * Found search query from ISRCTN: q=neuroblastoma
# (1/3) Checking trials in ISRCTN:
# Retrieved overview, records of 9 trial(s) are to be downloaded.
# Checking helper binaries: done.
# Downloading: 92 kB       
# (2/3) Converting to JSON...
# (3/3) Importing JSON records into database...
# = Imported or updated 9 trial(s).                                                   
# * Updated history in meta-info of "some_collection_name"

# Get all records that have values in any of the specified fields
result <- dbGetFieldsIntoDf(
  fields = c(
    "clinical_results.baseline.analyzed_list.analyzed.count_list.count",
    "clinical_results.baseline.group_list.group",
    "clinical_results.baseline.analyzed_list.analyzed.units", 
    "study_design_info.allocation", 
    "location"),
  con = db)

# Transform all fields into long name - value format
result <- dfTrials2Long(df = result)
# Total 5012 rows, 12 unique names of variables

# [1.] get counts of subjects for all arms into data frame
# This count is in the group that has "Total" in its name
nsubj <- dfName2Value(
  df = result, 
  valuename = "clinical_results.baseline.analyzed_list.analyzed.count_list.count.value",
  wherename = "clinical_results.baseline.group_list.group.title",
  wherevalue = "Total"
)

# [2.] count number of sites
nsite <- dfName2Value(
  df = result, 
  # some ctgov records use
  # location.name, others use
  # location.facility.name
  valuename = "^location.*name$"
) 
# count
nsite <- tapply(
  X = nsite[["value"]], 
  INDEX = nsite[["_id"]],
  FUN = length,
  simplify = TRUE
)
nsite <- data.frame(
  "_id" = names(nsite),
  nsite, 
  check.names = FALSE,
  stringsAsFactors = FALSE,
  row.names = NULL
)

# [3.] randomised?
ncon <- dfName2Value(
  df = result, 
  valuename = "study_design_info.allocation"
) 

# merge sets
nset <- merge(nsubj, nsite, by = "_id")
nset <- merge(nset, ncon, by = "_id")

# Example plot
library(ggplot2)
ggplot(data = nset) + 
  labs(title = "Neuroblastoma trials with results",
       subtitle = "clinicaltrials.gov") +
  geom_point(
    mapping = aes(
      x = nsite,
      y = value.x,
      colour = value.y == "Randomized")) + 
  scale_x_log10() + 
  scale_y_log10() 
ggsave(filename = "inst/image/README-ctrdata_results_neuroblastoma.png",
       width = 5, height = 3, units = "in")