R/oscn_crim_monitor.R

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

oscn_crim_monitor <- function(days_ago = 7) {

  cutoff <- Sys.Date() - days_ago

  connect_ojo()

  oscn_cases <<- dbGetQuery(ojo_db, "
               SELECT court, casetype, file_year, COUNT(casenum) as cases
               FROM oscn_caseinfo
               WHERE casetype IN ('CM', 'CF')
               AND file_year > 2007
               GROUP BY court, casetype, file_year;
               ") %>%
    left_join(dbGetQuery(ojo_db,
                         glue_sql("
               SELECT court, casetype, file_year, COUNT(casenum) as updated
               FROM oscn_lastupdate
               WHERE last_update > {cutoff}
               GROUP BY court, casetype, file_year;
               ", .con = ojo_db))) %>%
    mutate(pct_updated = ifelse(is.na(updated),
                                0,
                                updated/cases))

  disconnect_ojo()

  ggplot(oscn_cases, aes(x = file_year, y = pct_updated, fill = casetype)) +
    geom_bar(stat = "identity", position = "dodge") +
    facet_wrap(~court)

}