R/oscn_updatedb.R

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

oscn_updatedb <- function() {
  
  connect_ojo()
  
  if (exists("atts")) { dbWriteTable(ojo_db, "oscn_atts", atts, row.names = FALSE, append = TRUE) }
  if (exists("caseinfo")) {
    dbxUpsert(ojo_db, "oscn_caseinfo", as.data.frame(caseinfo), 
              where_cols = c("oscn_ci_id"))}
  if (exists("crim_disps")) { 
    dbxUpsert(ojo_db, "oscn_crim_disps", as.data.frame(crim_disps), 
              where_cols = c("oscn_crimdisp_id"))}
  if (exists("civ_disps")) { dbxUpsert(ojo_db, "oscn_civ_disps", as.data.frame(civ_disps), where_cols = c("oscn_civdisp_id"))}
  if (exists("citations")) { dbWriteTable(ojo_db, "oscn_citations", citations, row.names = FALSE, append = TRUE)}
  if (exists("events")) { dbWriteTable(ojo_db, "oscn_events", events, row.names = FALSE, append = TRUE)}
  if (exists("party")) { dbWriteTable(ojo_db, "oscn_party", party, row.names = FALSE, append = TRUE)}
  if (exists("parties")) { dbWriteTable(ojo_db, "oscn_parties", parties, row.names = FALSE, append = TRUE)}
  if (exists("party_address")) { dbWriteTable(ojo_db, "oscn_party_address", party_address, row.names = FALSE, append = TRUE)}
  if (exists("partynames")) { dbWriteTable(ojo_db, "oscn_party_names", partynames, row.names = FALSE, append = TRUE)}
  if (exists("pprofile")) { dbWriteTable(ojo_db, "oscn_party_profile", pprofile, row.names = FALSE, append = TRUE)}
  
  if (exists("mins")) {
    years <- unique(mins$file_year)
    casetypes <- unique(mins$casetype)
    
    for (i in years) {
      for (j in casetypes) {
        if (!paste0("oscn_mins_", i, str_to_upper(j)) %in% dbListTables(ojo_db)) {
          dbGetQuery(ojo_db,
                     paste0(
                       "CREATE TABLE `", paste0("oscn_mins_", i, str_to_upper(j)),
                       "` (`oscn_min_id` varchar(50) NOT NULL,
          `court` varchar(50) DEFAULT NULL,
          `casenum` varchar(50) DEFAULT NULL,
          `casetype` varchar(6) DEFAULT NULL,
          `defname` varchar(50) DEFAULT NULL,
          `file_year` int(6) DEFAULT NULL,
          `min_date` date DEFAULT NULL,
          `min_code` varchar(20) DEFAULT NULL,
          `min_desc` text,
          `fee_amt` double DEFAULT NULL,
          `ct_no` int(10) DEFAULT NULL,
          `min_row` int(10) DEFAULT NULL,
          PRIMARY KEY (`oscn_min_id`),
          KEY `oscn_min_id` (`oscn_min_id`),
          KEY `court` (`court`),
          KEY `casenum` (`casenum`),
          KEY `casetype` (`casetype`),
          KEY `defname` (`defname`),
          KEY `file_year` (`file_year`),
          KEY `min_code` (`min_code`),
          FULLTEXT KEY `min_desc` (`min_desc`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                     )
          )
        }
        
        dbWriteTable(ojo_db,
                     paste0("oscn_mins_", i, str_to_upper(j)),
                     mins[mins$file_year == i & mins$casetype == j, ],
                     row.names = FALSE, append = TRUE)
        
        if (!paste0("oscn_pays_", i) %in% dbListTables(ojo_db)) {
          dbGetQuery(ojo_db, paste0(
            "CREATE TABLE `oscn_pays_", i, "` (
  `oscn_pay_id` varchar(50) NOT NULL,
`court` varchar(50) DEFAULT NULL,
`casenum` varchar(50) DEFAULT NULL,
`casetype` varchar(10) DEFAULT NULL,
`file_year` int(6) DEFAULT NULL,
`defname` varchar(100) DEFAULT NULL,
`pay_date` date DEFAULT NULL,
`pay_code` varchar(20) DEFAULT NULL,
`pay_acct` varchar(80) DEFAULT NULL,
`pay_amt` double DEFAULT NULL,
`adj_amt` double DEFAULT NULL,
PRIMARY KEY (`oscn_pay_id`),
KEY `court` (`court`),
KEY `casenum` (`casenum`),
KEY `casetype` (`casetype`),
KEY `file_year` (`file_year`),
KEY `defname` (`defname`),
KEY `pay_date` (`pay_date`),
KEY `pay_code` (`pay_code`),
KEY `pay_acct` (`pay_acct`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"
          ))
        }
        
        accts <- mins %>%
          filter(min_code == "ACCOUNT") %>%
          mutate(total_paid = str_extract(min_desc, "(?<=AMOUNT PAID:).*?(?=\r)") %>%
                   str_remove_all(",|\\.$| |\\$") %>%
                   as.numeric,
                 pay_accts = str_extract_all(min_desc, "(?<=\\$).*?ON AC\\d{2}.*?(?= FOR )"),
                 adj_accts = str_extract_all(min_desc, "AC.*?(?=[[:alpha:]]{2}-20)"))
        
        if (nrow(accts) > 0) {
          
          item_pays <- accts %>%
            select(oscn_min_id, court, casenum, casetype, file_year, defname, pay_date = min_date, pay_accts) %>%
            unnest() %>%
            separate(pay_accts, into = c("pay_amt", "pay_acct"), sep = " ON ") %>%
            mutate(pay_amt = pay_amt %>%
                     str_remove_all(",|\\.$| |\\$") %>%
                     as.numeric(),
                   pay_code = str_extract(pay_acct, "^\\w{4}"),
                   pay_acct = str_remove(pay_acct, "-\\$.*") %>%
                     str_remove_all("[^\\w| ]") %>%
                     str_squish())
          
          item_adjs <- accts %>%
            select(oscn_min_id, court, casenum, casetype, file_year, defname, pay_date = min_date, adj_accts) %>%
            unnest() %>%
            mutate(adj_amt = str_extract(adj_accts, "-\\$.*") %>%
                     str_remove("\\$") %>%
                     str_squish() %>%
                     as.numeric,
                   pay_acct = str_remove(adj_accts, "-\\$.*") %>%
                     str_remove_all("[^\\w| ]") %>%
                     str_squish(),
                   pay_code = str_extract(pay_acct, "^\\w{4}")) %>%
            select(-adj_accts)
          
          items <- bind_rows(item_pays, item_adjs) %>%
            arrange(casenum, defname, pay_date) %>%
            group_by(casenum, pay_date, pay_code) %>%
            mutate(oscn_pay_id = paste0(oscn_min_id, "-", pay_code, "-", row_number())) %>%
            select(oscn_pay_id, court, casenum, casetype, file_year, defname, pay_date, pay_code, pay_acct, pay_amt, adj_amt)
          
          dbWriteTable(ojo_db,
                       paste0("oscn_pays_", i),
                       items,
                       row.names = FALSE, append = TRUE)
          
        }
      }
    }
  }
  
  if (exists("updates")) { dbWriteTable(ojo_db, "oscn_updates", updates,
                                        append = TRUE, row.names = FALSE) }
  
  if (exists("lastupdate")) { dbxUpsert(ojo_db, "oscn_lastupdate", as.data.frame(lastupdate), where_cols = c("oscn_lastupdate_id"))}
  
  dbDisconnect(ojo_db)
  ojo_disconnect_all()
  
  print("Database updated.")
}