R/odcr_casesum.R

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

odcr_casesum <- function(casetypes, file_years, updatedb = TRUE) {
  
  for (casetype_tmp in casetypes) {
    for (file_year_tmp in file_years) {
      
      connect_ojo()
      
      ci <- tbl(ojo_db, "odcr_disps") %>%
        filter(casetype == casetype_tmp,
               file_year == file_year_tmp,
               !ct_desc %in% c("DISMISSED", "PAY ONLINE")) %>%
        select(court, casenum, casetype, file_year, ct_desc) %>%
        left_join(tbl(ojo_db, "odcr_caseinfo") %>%
                    filter(casetype == casetype_tmp,
                           file_year == file_year_tmp) %>%
                    select(court, casenum, file_date)) %>%
        left_join(tbl(ojo_db, "odcr_party") %>%
                    filter(party_type == "Plaintiff") %>%
                    select(court, casenum, plaintiff = party)) %>%
        left_join(tbl(ojo_db, "odcr_party") %>%
                    filter(party_type == "Defendant") %>%
                    select(court, casenum, defname = party)) %>%
        left_join(tbl(ojo_db, "odcr_party") %>%
                    filter(party_type == "Judge") %>%
                    select(court, casenum, judge = party)) %>%
        collect
      
      # Summarize based on case type: One row per defendant for criminal, one row per case number for civil
      if (casetype_tmp %in% c("CM", "CF", "TR")) {
        ci <- ci %>% 
          select(-plaintiff)
      } else {
        ci <- ci %>% 
          group_by(court, casenum, file_year, casetype, file_date) %>% 
          summarize(iss_desc = first(ct_desc),
                    judge = first(judge),
                    plaintiff = first(plaintiff),
                    defname = first(defname))
      }
      
      d_d <- tbl(ojo_db, paste0("odcr_mins_", 
                                file_year_tmp, 
                                casetype_tmp)) %>%
        select(court, casenum, min_date, min_desc, fee_amt) %>%
        collect
      
      d_e <- tbl(ojo_db, "odcr_events") %>%
        filter(file_year == file_year_tmp, 
               casetype == casetype_tmp) %>%
        collect %>%
        select(court, casenum, min_date = event_date, min_desc = event_desc) %>% 
        filter(min_date <= Sys.Date())
      
      # Summarize dispositions based on case type
      
      if (casetype_tmp %in% c("CM", "CF", "TR")) {
        
        disp <- bind_rows(d_d, d_e) %>%
          mutate(disp = case_when(str_detect(min_desc, "CONVICT|ST GUILTY PLEA|PLEA OF GUILTY|PLEADS GUILTY") ~ "CONVICTION",
                                  str_detect(min_desc, "DEFERRED") ~ "DEFERRED",
                                  str_detect(min_desc, "DISMISS") ~ "DISMISSED",
                                  str_detect(min_desc, "TRANSFER") ~ "TRANSFERRED",
                                  str_detect(min_desc, "ACQUIT") ~ "ACQUITTED")) %>%
          group_by(court, casenum) %>%
          summarize(disp_date = last(min_date[which(!is.na(disp))]),
                    disp_case = if_else(all(is.na(disp)),
                                        "OPEN",
                                        last(disp[which(!is.na(disp))])))
        
      } else {
        disp <- bind_rows(d_d, d_e) %>%
          mutate(disp = case_when(str_detect(min_desc, "DEFAULT") ~ "DEFAULT JUDGMENT",
                                  str_detect(min_desc, "DISM") ~ "DISMISSED",
                                  str_detect(min_desc, "SCAD|CONT|RESET") ~ "CONTINUED",
                                  str_detect(min_desc, "MEDIA") & is.na(fee_amt) ~ "MEDIATION",
                                  str_detect(min_desc, "JUDG") ~ "JUDGMENT")) %>%
          group_by(court, casenum) %>%
          summarize(disp_date = last(min_date[which(!is.na(disp))]),
                    disp_case = if_else(all(is.na(disp)),
                                        "OPEN",
                                        last(disp[which(!is.na(disp))])))
        
      }
      
      if (!casetype_tmp %in% c("CM", "CF", "TR")) {
        
        d <- left_join(ci, disp) %>% 
          mutate(summary_date = Sys.Date(),
                 issue = case_when(str_detect(first(iss_desc), "FORCIBLE ENTRY|EVICT|RENT|ENTRY") ~ "EVICTION",
                                   str_detect(first(iss_desc), "DEBT|COLLEC") ~ "DEBT",
                                   str_detect(first(iss_desc), "FORECLOS") ~ "FORECLOSURE",
                                   TRUE ~ "OTHER"))
      } else {
        d <- left_join(ci, disp) %>%
          group_by(court, casenum, casetype, file_year, file_date, judge, defname, disp_date, disp_case) %>% 
          summarize(top_ct_desc = first(ct_desc),
                    n_ct = n_distinct(ct_desc)) %>% 
          mutate(summary_date = Sys.Date())
      }
      
      if (updatedb == TRUE) {
        
        message("Writing to database")
        
        connect_ojo()
        
        for (i in 1:((nrow(d) %/% 5000) + 1)) {
          
          start_num <- as.integer(((i - 1) * 5000) + 1)
          end_num <- if_else(i > (nrow(d) %/% 5000),
                             nrow(d),
                             as.integer(start_num + 4999))
          
          upload_slice <- d[start_num:end_num, ]
          
          message(paste("Writing slice", i, "of", (nrow(d) %/% 5000) + 1))
          
          
          if (casetype_tmp %in% c("CM", "CF", "TR")) {
            dbxUpsert(ojo_db, "ojo_crim_cases", upload_slice, where_cols = c("court", "casenum", "file_date", "defname"))
          } else {
            dbxUpsert(ojo_db, "ojo_civ_cases", upload_slice, where_cols = c("court", "casenum", "file_date"))
          }
          
        }
        disconnect_ojo()
      }
    }
  }
}