R/oscn_lastcase_df.R

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

oscn_lastcase_df <- function(courts, casetypes, years) {
  
  connect_ojo()
  
  lastcase <<- dbGetQuery(ojo_db, glue_sql(
    "SELECT court, casetype, file_year, last_case
                          FROM ojo_lastcase
                          WHERE court IN ({courts*})
                          AND casetype IN ({casetypes*})
                          AND file_year IN ({years*})", 
    .con = ojo_db))
  
  disconnect_ojo()
  
  needem <- anti_join(crossing(court = courts, casetype = casetypes, file_year = years), 
                      lastcase)
  
  if (nrow(needem) > 0) {
    
    for (i in 1:nrow(needem)) {
      
      oscn_lastcase(needem[i, "court"], needem[i, "casetype"], needem[i, "file_year"])
      
      print(paste(needem[i, "court"], needem[i, "casetype"], needem[i, "file_year"], "done."))
      
      lastcase <<- distinct(lastcase)
    }
    
    lastcase_update <- lastcase %>% 
      filter(file_year != year(Sys.Date()))
    
    connect_ojo()
    invisible(dbWriteTable(ojo_db, "ojo_lastcase", lastcase_update, append = TRUE, row.names = FALSE))
    invisible(disconnect_ojo())
  }
}

ojo_check_comp <- function(data) {
  
  if (is.data.frame(data) & nrow(data) > 0) {
    
    ccy <- {{data}} %>% 
      select(court, casetype, file_year) %>% 
      distinct
    
    connect_ojo()
    
    d <- dbGetQuery(ojo_db,
                    glue_sql(
                      "SELECT court, casetype, file_year, COUNT(casenum) as cases_analyzed
                      FROM oscn_caseinfo
                      WHERE court IN ({ccy$court*})
                      AND casetype IN ({ccy$casetype*})
                      AND file_year IN ({ccy$file_year*})
                      GROUP BY court, casetype, file_year", 
                    .con = ojo_db))
    
    oscn_lastcase_df(ccy$court, ccy$casetype, ccy$file_year)
    
    if ("casetype_id" %in% names(lastcase)) {
      lastcase <- lastcase %>% 
        select(-casetype_id)
    }
    
    completeness <<- d %>% 
      left_join(lastcase) %>% 
      mutate(pct_complete = cases_analyzed/last_case)
    
  } else {
    print("Input must be a dataframe with columns named court, casetype, file_year, and casenum.")
  }
}