R/oscn_casesum.R

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

oscn_casesum <- function(casetypes, file_years, updatedb = TRUE) {
  
  for (casetype_tmp in casetypes) {
    for(file_year_tmp in file_years) {
      
      message(paste("Summarizing", casetype_tmp, "cases filed in", file_year_tmp))
      
      connect_ojo()
      
      ci <- tbl(ojo_db, "oscn_caseinfo") %>%
        filter(file_year == file_year_tmp, casetype == casetype_tmp) %>%
        select(-oscn_ci_id) %>%
        collect() %>% 
        mutate_at(vars(contains("date")), list(ymd))
      
      message("Case info table queried")
      
      if (casetype_tmp %in% c("CM", "CF", "TR")) {
        d <- tbl(ojo_db, "oscn_crim_disps") %>%
          filter(file_year == file_year_tmp, casetype == casetype_tmp) %>%
          select(court, casenum, defname, ct_no, ct_code, ct_desc, ct_stat,
                 disp, disp_date, disp_type, disp_code, disp_desc, disp_stat) %>%
          collect() %>% 
          mutate_at(vars(contains("date")), list(ymd))
        
        disconnect_ojo()
        
        message("Disposition table queried")
        
        d_sum <- d %>%
          group_by(court, casenum, defname, ct_no, ct_code, ct_desc, ct_stat) %>%
          summarize(disp = if_else(all(is.na(disp)), "OPEN", first(disp[which(!is.na(disp))])),
                    disp_date = max(disp_date, na.rm = TRUE)) %>%
          mutate(ct_stat = ct_stat %>%
                   str_trim %>%
                   str_remove_all(" ") %>%
                   str_replace_all("(\\(|\\)|O\\.S\\.|-)", "\\.") %>%
                   str_remove("\\.$") %>%
                   str_replace_all("\\.\\.", "\\.")) %>%
          group_by(court, casenum, defname) %>%
          summarize(top_ct_desc = first(ct_desc),
                    top_ct_stat = first(ct_stat),
                    top_ct_disp = first(disp[which(ct_desc == first(ct_desc)) & !is.na(disp)]),
                    disp_date = max(disp_date, na.rm = TRUE),
                    n_ct = max(ct_no),
                    hi_ct_con = first(ct_desc[which(disp %in% c("CONVICTION", "DEFERRED"))]), # First count that was convicted or deferred
                    hi_ct_stat = first(ct_stat[which(disp %in% c("CONVICTION", "DEFERRED"))]),
                    disp_case = case_when(any(disp == "CONVICTION") ~ "CONVICTION",
                                          any(disp == "DEFERRED") ~ "DEFERRED",
                                          any(disp == "DISMISSED WITH COSTS") ~ "DISMISSED WITH COSTS",
                                          any(disp == "DISMISSED") ~ "DISMISSED",
                                          all(disp == "OPEN") ~ "OPEN",
                                          TRUE ~ "OTHER")) 
        
        summary_tbl <- "ojo_crim_cases"
        
      } else {
        
        d <- tbl(ojo_db, "oscn_civ_disps") %>%
          filter(file_year == file_year_tmp, casetype == casetype_tmp) %>%
          select(court, casenum, iss_no, iss_desc,
                 disp, disp_date, disp_type) %>%
          left_join(tbl(ojo_db, "oscn_parties") %>% 
                      filter(file_year == file_year_tmp, casetype == casetype_tmp, 
                             party_type == "DEFENDANT" | party_type == "Defendant") %>% 
                      select(court, casenum, defname = party_name)) %>%
          left_join(tbl(ojo_db, "oscn_parties") %>% 
                      filter(file_year == file_year_tmp, casetype == casetype_tmp, 
                             party_type == "PLAINTIFF" | party_type == "Plaintiff") %>% 
                      select(court, casenum, plaintiff = party_name)) %>% 
          collect() %>% 
          mutate_at(vars(contains("date")), list(ymd))
        
        disconnect_ojo()
        
        message("Disposition table queried")
        
        d_sum <- d %>%
          group_by(court, casenum, plaintiff, defname, iss_no, iss_desc) %>%
          summarize(disp = if_else(all(is.na(disp)), 
                                   "OPEN", 
                                   first(disp[which(!is.na(disp))])),
                    disp_date = max(disp_date, na.rm = TRUE)) %>%
          group_by(court, casenum) %>%
          summarize(plaintiff = first(plaintiff[which(!is.na(plaintiff))]) %>% str_to_upper,
                    defname = first(defname[which(!is.na(defname))]) %>% str_to_upper,
                    issue = case_when(str_detect(first(iss_desc), "FORCIBLE ENTRY") ~ "EVICTION",
                                      str_detect(first(iss_desc), "INDEBT") ~ "DEBT",
                                      str_detect(first(iss_desc), "FORECLOS") ~ "FORECLOSURE",
                                      TRUE ~ "OTHER"),
                    iss_desc = first(iss_desc),
                    disp_date = max(disp_date, na.rm = TRUE),
                    disp_case = if_else(all(disp == "OPEN"), "OPEN", first(disp[which(!is.na(disp))])))

        open <- d_sum %>%
          filter(disp_case == "OPEN", court == "TULSA") %>%
          select(-disp_case, -disp_date)
        
        connect_ojo()

        # under_adv <- ojo_tbl(paste0("oscn_mins_", file_year_tmp, casetype_tmp)) %>%
        #   filter(min_desc %like% "%ADVISEMENT%" | min_code %like% "%DISP%" | min_desc %like% "%DISM%",
        #          court == "TULSA", casenum %in% !!open$casenum) %>%
        #   collect %>%
        #   group_by(court, casenum) %>%
        #   summarize(disp_case = "UNDER ADVISEMENT",
        #          disp_date = max(ymd(min_date)))
        # 
        # disconnect_ojo()
        # 
        # open <- left_join(open, under_adv)
        # 
        # d_sum <- d_sum %>%
        #   filter(disp_case != "OPEN") %>%
        #   bind_rows(open)
        
        summary_tbl <- "ojo_civ_cases"
      }
      
      cs <<- ci %>%
        left_join(d_sum) %>%
        #filter(!is.na(defname), defname != "") %>% 
        mutate(summary_date = Sys.Date())
      
      if (updatedb == TRUE) {
        
        message("Writing to database")
        
        connect_ojo()
        
        for (i in 1:((nrow(cs) %/% 5000) + 1)) {
          
          start_num <- as.integer(((i - 1) * 5000) + 1)
          end_num <- if_else(i > (nrow(cs) %/% 5000),
                             nrow(cs),
                             as.integer(start_num + 4999))
          
          upload_slice <- cs[start_num:end_num, ]
          
          message(paste("Writing slice", i, "of", (nrow(cs) %/% 5000) + 1))
          
          
          if (casetype_tmp %in% c("CM", "CF", "TR")) {
          dbxUpsert(ojo_db, summary_tbl, upload_slice, where_cols = c("court", "casenum", "file_date", "defname"))
          } else {
            dbxUpsert(ojo_db, summary_tbl, upload_slice, where_cols = c("court", "casenum", "file_date"))
          }
          
        }
        disconnect_ojo()
      }
    }
  }
  ojo_disconnect_all()
}