R/oscn_refresh.R

In openjusticeok/ojo: Access and analyze data from the Open Justice Oklahoma database

oscn_refresh <- function() {

  connect_ojo()
  lu <- dbGetQuery(ojo_db,
                 "SELECT *
                 FROM ojo.oscn_lastupdate
                 WHERE available = TRUE
                 AND casetype IN ('CM', 'CF')
                 AND oscn_lastupdate_id
                 AND file_year > 2009
                 NOT IN (SELECT oscn_lastupdate_id FROM oscn_queue)
                 ORDER BY last_update ASC
                 LIMIT 100;") %>%
    mutate(case_seq = casenum %>%
             str_sub(9,13) %>%
             as.numeric)

  queued <- lu %>%
    select(oscn_lastupdate_id) %>%
    mutate(queue_time = as_datetime(Sys.time(), tz = "US/Central"),
           ip = ifelse(is.na(str_extract(system("ifconfig", intern = TRUE)[2], "(\\d|[[:punct:]]){10,30}")),
                       "Laptop",
                       str_extract(system("ifconfig", intern = TRUE)[2], "(\\d|[[:punct:]]){10,30}")))

  dbWriteTable(ojo_db, "oscn_queue", queued, row.names = FALSE, append = TRUE)

  disconnect_ojo()

  print(paste("Scraping 100 stalest cases starting with", lu[1, "court"], lu[1, "casenum"]))

  ccy <- lu %>%
    select(court, casetype, file_year) %>%
    distinct()

  start_time <- Sys.time()

  for (i in 1:nrow(ccy)) {
    seqs <- lu %>%
      filter(court == ccy[i, "court"], casetype == ccy[i, "casetype"] & file_year == ccy[i, "file_year"])

    seqs <- seqs$case_seq

    oscn_scrape(ccy[i, "court"],
                ccy[i, "casetype"],
                ccy[i, "file_year"],
                seqs,
                update_freq = 20)
  }

  elapsed <- as.numeric(Sys.time() - start_time) %>%
    round(digits = 2)

  #### Delete scraped cases from queue ####
  connect_ojo(username = "ojo_admin")

  dbSendQuery(ojo_db, glue_sql("
             DELETE FROM oscn_queue
             WHERE oscn_lastupdate_id IN ({queued$oscn_lastupdate_id*})",
                              .con = ojo_db))

  disconnect_ojo()

  paste("100 cases scraped in", elapsed, "minutes") %>%
    print
}