inst/extdata/sql_actualizare_sentinte_selectate.R

In fizic37/LitigiiRisc: Litigii Risc

# I obtain below excel from my sql workbench: select * from sentinte where solutie_cumulata is null
update_sentinte <- readxl::read_excel("test_mysql.xlsx")


update_sentinte <- paste0("INSERT INTO sentinte(id_litigiu,data_sentinta,tip_solutie,coef_risc,solutie,solutie_cumulata) VALUES (",
       paste0(update_sentinte$id_litigiu, ",",
        "'", as.character(update_sentinte$data_sentinta),"'", ",",
        "'", update_sentinte$tip_solutie,"'", ",",
        update_sentinte$coef_risc, ",",
        "'", update_sentinte$solutie,"'",",",
       "'",update_sentinte$solutie_cumulata, "'"
         ,collapse = "),("), ")",
       " ON DUPLICATE KEY UPDATE solutie_cumulata = VALUES(solutie_cumulata)")
            
db <- config::get("database", file = "inst/golem-config.yml")

my_connection <- DBI::dbConnect(RMySQL::MySQL(), password = db$password, username = db$username, 
                                dbname = db$dbname, host = db$host)

DBI::dbSendQuery(conn = my_connection,statement = update_sentinte)

DBI::dbDisconnect(conn = my_connection)


db <- config::get("database", file = "inst/golem-config.yml")

my_connection <- DBI::dbConnect(RMySQL::MySQL(), password = db$password, username = db$username, 
                                dbname = db$dbname, host = db$host)

final_query <- "SELECT * from sentinte where solutie like '%@%' or solutie like '%€%';"


DBI::dbBegin(my_connection)

result <- DBI::dbGetQuery(conn = my_connection,statement = final_query)

DBI::dbCommit(conn = my_connection)

DBI::dbDisconnect(conn = my_connection)

result <- result %>% dplyr::mutate(data_sentinta = as.Date.character(data_sentinta))
result <- result %>% dplyr::select(-solutie) %>% dplyr::left_join(y = sentinte_actualizate %>% dplyr::select(id_litigiu,date_sedinte, solutie),
                                                                  by = c("id_litigiu","data_sentinta" = "date_sedinte"))

saveRDS(object = result,file = "result.rds")

update_query <- paste0("UPDATE sentinte SET solutie=",
                       paste0("'",result$solutie,"'"), " where id_litigiu=",
                       paste0(result$id_litigiu), " AND data_sentinta=",
                       paste0("'", as.character(result$data_sentinta),"'"),";")

purrr::map(.x = update_query,.f = ~DBI::dbSendQuery(conn = my_connection,statement = .x))