R/oscn_tulsa_monitor.R

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

oscn_tulsa_monitor <- function(days_ago = 1) {

  cutoff <- Sys.Date() - days_ago

  connect_ojo()

  tulsa_cases <<- dbGetQuery(ojo_db, "
               SELECT court, casetype, file_year, COUNT(casenum) as cases
               FROM oscn_caseinfo
               WHERE court = 'TULSA'
               AND casetype IN ('CM', 'CF', 'SC')
               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}
               AND casetype IN ('CM', 'CF', 'SC')
               AND court = 'TULSA'
               GROUP BY court, casetype, file_year;
               ", .con = ojo_db))) %>%
    mutate(pct_updated = ifelse(is.na(updated),
                                0,
                                updated/cases))

  disconnect_ojo()

  ggplot(tulsa_cases, aes(x = file_year, y = pct_updated)) +
    geom_bar(stat = "identity", position = "dodge") +
    facet_wrap(~casetype) +
    theme_ojo() +
    scale_x_continuous(breaks = c(2008, 2010, 2012, 2014, 2016, 2018))

}

oscn_tulsa_monitor()