pbl_bn_viaf_integration.Rmd

---
title: "R Notebook"
output: html_notebook
---
```{r}
#biblioteki
options(java.parameters = "-Xmx32000m")
options(scipen = 999)
pacman::p_load(googlesheets4,zoo,stringr,splitstackshape,plyr,dplyr,sqldf,stringdist,fuzzyjoin,data.table,svMisc,tidyverse,RJDBC,rvest,RSelenium,jsonlite,XML,viafr)

#połączenie z bazą PBL
jdbcDriver =JDBC("oracle.jdbc.OracleDriver",classPath="C:/Users/Cezary/Downloads/ojdbc6.jar")
PBL <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//pbl.ibl.poznan.pl:1521/xe", "IBL_SELECT", "CR333444")

test <- read.csv2("C:/Users/Cezary/Desktop/do zrobienia/tworcy2.csv", encoding = "Windows-1250", header = TRUE, stringsAsFactors = FALSE) 
```

```{r nowe podejście}
#najpierw trzeba zbudować listę wielokrotnego wyboru, a dopiero potem szukać szczegółowych informacji (czyli będą dwie)
#wczytanie twórców pbl
library(svMisc)
library(tidyverse)
library(RSelenium)
library(dplyr)
library(stringr)
library(jsonlite)
pbl_tworcy <- dbGetQuery(PBL,
                            "select *
                            from pbl_tworcy tw") %>%
  select(TW_TWORCA_ID,TW_NAZWISKO,TW_IMIE) %>%
  arrange(TW_TWORCA_ID) 
pbl_tworcy <- wzorcowe_bn %>%
  filter(skad=="brak danych (CR)") %>%
  arrange(TW_TWORCA_ID) %>%
    select(1:3)
count <- as.data.frame(table(wzorcowe_bn$skad))
##
#browser
#list_versions("chromedriver")
rD <- rsDriver(port=4444L,browser="chrome", chromever="79.0.3945.36")
remDr <- rD$client
page <- "https://viaf.org/"
#tworzenie pustej tabeli
wzorcowe_bn <- data.frame(TW_TWORCA_ID = as.integer(),TW_NAZWISKO = as.character(), TW_IMIE = as.character(), bn_id = as.character(), bn_name = as.character(), skad = as.character(), stringsAsFactors=FALSE)
x <- 1:length(pbl_tworcy$TW_TWORCA_ID)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  url_1 <- "http://data.bn.org.pl/api/authorities.json?limit=100&name="
  url_2 <- tolower(str_replace_all(pbl_tworcy$TW_NAZWISKO," ", "+"))[i]
  url_3 <- "+"
  url_4 <- tolower(str_replace_all(pbl_tworcy$TW_IMIE," ","+"))[i]
  url <- as.character(paste(url_1,url_2,url_3,url_4,sep = ""))
  tryCatch({
    bn <- jsonlite::fromJSON(url) %>% .$authorities %>%
    filter(title=="") %>%
      select(bn_id=id,bn_name=name)
  if (length(bn$bn_id)>0) {
    bn <- jsonlite::fromJSON(url) %>% .$authorities %>%
    filter(title=="") %>%
    select(bn_id=id,bn_name=name) %>%
      mutate(skad = "BN")
  } else {
  remDr$navigate(page)
    lista_wyboru <- remDr$findElement(using = 'css selector', "div.yui-skin-sam form:nth-child(1) fieldset:nth-child(1) select:nth-child(2) > option:nth-child(5)")
      lista_wyboru$clickElement()
      sbox <- remDr$findElement(using = 'css selector', "#searchTerms")
      sbox$sendKeysToElement(list(paste(pbl_tworcy$TW_NAZWISKO[i],pbl_tworcy$TW_IMIE[i],sep = " ")))
      enter <- remDr$findElement(using = 'css selector', "#btnSearch2")
      enter$clickElement()
      result_page <- remDr$findElement(using = 'css selector', 'div.inner table:nth-child(2) tbody:nth-child(1) tr:nth-child(2) td.recName > a:nth-child(1)')
      result_page$clickElement()
      viaf_number <- str_replace(as.character(remDr$getCurrentUrl()),"(.*?)(\\d+)(\\/#.*$)","\\2")
      url <- paste("https://data.bn.org.pl/api/authorities.json?limit=100&marc=024a+",viaf_number,sep = "")
      bn <- jsonlite::fromJSON(url) %>% .$authorities %>%
      filter(title=="") %>%
      select(bn_id=id,bn_name=name) %>%
        mutate(skad = "VIAF")
  }
  }, error=function(e){
    tryCatch({
      remDr$navigate(page)
      lista_wyboru <- remDr$findElement(using = 'css selector', "div.yui-skin-sam form:nth-child(1) fieldset:nth-child(1) select:nth-child(2) > option:nth-child(5)")
      lista_wyboru$clickElement()
      sbox <- remDr$findElement(using = 'css selector', "#searchTerms")
      sbox$sendKeysToElement(list(paste(pbl_tworcy$TW_NAZWISKO[i],pbl_tworcy$TW_IMIE[i],sep = " ")))
      enter <- remDr$findElement(using = 'css selector', "#btnSearch2")
      enter$clickElement()
      result_page <- remDr$findElement(using = 'css selector', 'div.inner table:nth-child(2) tbody:nth-child(1) tr:nth-child(2) td.recName > a:nth-child(1)')
      result_page$clickElement()
      viaf_number <- str_replace(as.character(remDr$getCurrentUrl()),"(.*?)(\\d+)(\\/#.*$)","\\2")
      url <- paste("https://data.bn.org.pl/api/authorities.json?limit=100&marc=024a+",viaf_number,sep = "")
      bn <<- jsonlite::fromJSON(url) %>% .$authorities %>%
      filter(title=="") %>%
      select(bn_id=id,bn_name=name) %>%
        mutate(skad = "VIAF") %>%
        mutate(bn_id = as.character(bn_id))
    }, error=function(f){
                        bn <<- data.frame(bn_id = "brak danych (CR)", bn_name = "brak danych (CR)", skad = "brak danych (CR)")
    })
    })

  iteration <- cbind(pbl_tworcy %>% slice(i), bn)
  wzorcowe_bn <- rbind(wzorcowe_bn,iteration)
}
###problemy z Balzacem Honore (bez akcentu nie wyszukuje, a z akcentem tak - co zrobić?)
write.csv2(wzorcowe_bn, file = "C:/Users/User/Desktop/tworcy_pbl_wzorcowe_b4_z_id.csv", row.names = F, na = '', fileEncoding = 'UTF-8')
    
    
    
aaa <- pbl_tworcy %>% slice(1)
    
    
  
  bn_name <- paste(str_replace_all(str_remove_all(paste(unlist(jsonlite::fromJSON(url) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`100` %>% `[[`(3)),collapse = "|"),"\\|NA"),"(\\|)(\\(\\d+)"," \\2"),str_replace_all(str_remove_all(paste(unlist(jsonlite::fromJSON(url) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`400` %>% `[[`(3)),collapse = "|"),"\\|NA"),"(\\|)(\\(\\.+)"," \\2"),sep = "|")
  viaf <- str_remove_all(paste(unlist(jsonlite::fromJSON(url) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`024` %>% .$subfields),collapse = "|"),"(\\|NA)|(\\|viaf)")
  
  
  
  
link <- "https://data.bn.org.pl/api/authorities.json?limit=100&name=Kindler%C3%B3wna+maria"

  tryCatch({
    bn <- jsonlite::fromJSON(link) %>% .$authorities %>%
    filter(title=="") %>%
    select(bn_id=id,bn_name=name)
    }, error=function(e){
                        bn$bn_id <<- "brak danych (CR)"
                        bn$bn_name <<- "brak danych (CR)"
    })
  
  
  
  
  
  
  
  
  
  
  
  
  
  tryCatch({
    remDr$navigate(url)
  webElem <- remDr$findElement(using = 'css selector', "body:nth-child(2) > pre:nth-child(1)")
  json_text <- webElem$getElementText()
  json_text <- as.character(json_text)
  }, error=function(e){
                   json_text <<- "brak danych CR"
})
  iteration <- cbind(pbl_tworcy$TW_TWORCA_ID[i], pbl_tworcy$TW_NAZWISKO[i], pbl_tworcy$TW_IMIE[i], json_text)
  wzorcowe_bn <- rbind(wzorcowe_bn,iteration)
}



############################

pbl_viaf <- data.frame(stringsAsFactors=FALSE)
x <- 1:length(pbl_tworcy$TW_TWORCA_ID)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  tryCatch({
  name <- paste(str_replace_all(str_remove_all(paste(unlist(jsonlite::fromJSON(paste("https://data.bn.org.pl/api/authorities.json?id=",pbl_bn_viaf$id[i],sep = "")) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`100` %>% `[[`(3)),collapse = "|"),"\\|NA"),"(\\|)(\\(\\d+)"," \\2"),str_replace_all(str_remove_all(paste(unlist(jsonlite::fromJSON(paste("https://data.bn.org.pl/api/authorities.json?id=",pbl_bn_viaf$id[i],sep = "")) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`400` %>% `[[`(3)),collapse = "|"),"\\|NA"),"(\\|)(\\(\\.+)"," \\2"),sep = "|")
  viaf <- str_remove_all(paste(unlist(jsonlite::fromJSON(paste("https://data.bn.org.pl/api/authorities.json?id=",pbl_bn_viaf$id[i],sep = "")) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`024` %>% .$subfields),collapse = "|"),"(\\|NA)|(\\|viaf)")
  }, error=function(e){
                     name <<- "brak danych (CR)"
                     viaf <<- "brak danych (CR)"
  })
  iteration <- data.frame(id = pbl_bn_viaf$id[i], name = name, viaf = viaf)
  pbl_viaf <- rbind(pbl_viaf,iteration)
}
write.csv2(pbl_viaf, file = "C:/Users/Cezary/Desktop/pbl_bn_viaf.csv", row.names = F, na = '', fileEncoding = 'UTF-8')

```


```{r}
#tworzenie pustej tabeli
wzorcowe_bn <- data.frame(pbl_id = character(), pbl_nazwisko = character(), pbl_imie = character(), bn_json = character(), stringsAsFactors=FALSE)
#pętla for

rD <- rsDriver(port=4444L,browser="chrome", chromever="78.0.3904.11")
#binman::list_versions("chromedriver")
#rD$server$stop()
#rm(rD)
#gc()
remDr <- rD$client

x <- 1:length(pbl_tworcy$TW_TWORCA_ID)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  url_1 <- "http://data.bn.org.pl/api/authorities.json?limit=100&name="
  url_2 <- tolower(pbl_tworcy$TW_NAZWISKO)[i]
  url_3 <- "+"
  url_4 <- tolower(pbl_tworcy$TW_IMIE)[i]
  url <- as.character(paste(url_1,url_2,url_3,url_4,sep = ""))
  tryCatch({
    remDr$navigate(url)
  webElem <- remDr$findElement(using = 'css selector', "body:nth-child(2) > pre:nth-child(1)")
  json_text <- webElem$getElementText()
  json_text <- as.character(json_text)
  }, error=function(e){
                   json_text <<- "brak danych CR"
})
  iteration <- cbind(pbl_tworcy$TW_TWORCA_ID[i], pbl_tworcy$TW_NAZWISKO[i], pbl_tworcy$TW_IMIE[i], json_text)
  wzorcowe_bn <- rbind(wzorcowe_bn,iteration)
}
write.csv2(wzorcowe_bn, "C:/Users/User/Desktop/wzorcowe_bn.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')
wzorcowe_bn <- read.csv2("C:/Users/User/Desktop/wzorcowe_bn.csv", encoding = "Windows-1250", header = TRUE, stringsAsFactors = FALSE)
wzorcowe_bn <- read.csv2("C:/Users/cezar/Downloads/wzorcowe_bn.csv", encoding = "Windows-1250", header = TRUE, stringsAsFactors = FALSE)
```

```{r}
wzorcowe_bn$json_text <- as.character(wzorcowe_bn$json_text)
bn_wzorcowe <- wzorcowe_bn %>%
  filter(nchar(as.character(json_text))>41)

bn_puste <- wzorcowe_bn %>%
  filter(nchar(as.character(json_text))<=41)

bn_json_full <- data.frame(pbl_id = character(), pbl_nazwisko = character(), pbl_imie = character(), stringsAsFactors=FALSE)
x <- 1:length(bn_wzorcowe$V1)

for (i in x) {
progress(match(i,x), max.value = length(x)) 
tryCatch({
bn_json <- fromJSON(bn_wzorcowe$json_text[i])
bn_json <- bn_json$authorities
bn_json2 <- bn_json$marc
bn_json$viaf <- str_extract_all(bn_json2$fields, "http:\\/\\/viaf\\.org\\/viaf\\/\\d+")

bn_json <- bn_json %>%
  select(id,name,kind,title,viaf)
}, error=function(e){
                   bn_json <<- data.frame(id = "błąd w pliku BN json",name = "błąd w pliku BN json",kind = "błąd w pliku BN json",title = "błąd w pliku BN json",viaf = "błąd w pliku BN json")
})

empty_table<- data.frame(matrix(ncol = 3,nrow = lengths(bn_json)[1]))
colnames(empty_table) <- c("pbl_id", "pbl_nazwisko", "pbl_imie")
empty_table$pbl_id <- bn_wzorcowe$V1[i]
empty_table$pbl_nazwisko <- bn_wzorcowe$V2[i]
empty_table$pbl_imie <- bn_wzorcowe$V3[i]
bn_json_iteration <- cbind(empty_table,bn_json)
bn_json_full <- rbind(bn_json_full,bn_json_iteration)
}
bn_puste <- bn_puste %>%
  select(-json_text)
colnames(bn_puste) <- c("pbl_id", "pbl_nazwisko", "pbl_imie")
do_dodania <- data.frame(id = "brak informacji w BN",name = "brak informacji w BN",kind = "brak informacji w BN",title = "brak informacji w BN",viaf = "brak informacji w BN")
bn_puste <- cbind(bn_puste, do_dodania)
bn_json_full <- rbind(bn_json_full,bn_puste)
bn_json_full$viaf <- as.character(bn_json_full$viaf)
bn_json_full$viaf <- ifelse(bn_json_full$viaf=="character(0)","",as.character(bn_json_full$viaf))
bn_json_full <- bn_json_full %>%
  filter(title=="") %>%
  filter(kind=="person")

#połączenie z bazą PBL
jdbcDriver =JDBC("oracle.jdbc.OracleDriver",classPath="C:/Users/cezar/Documents/R/ojdbc6.jar")
#jdbcDriver =JDBC("oracle.jdbc.OracleDriver",classPath="E:/Cezary/Documents/ojdbc6.jar")
PBL <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//pbl.ibl.poznan.pl:1521/xe", "IBL_SELECT", "CR333444")
pbl_tworcy <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id, tw.tw_rok_urodzin||'-'||tw.tw_rok_zgonu \"lata\"
                          from pbl_tworcy tw")
pbl_tworcy <- pbl_tworcy %>%
  filter(lata!="-")

bn_json_full <- merge(bn_json_full,pbl_tworcy,by.x = "pbl_id",by.y = "TW_TWORCA_ID", all.x = TRUE)
bn_json_full <- bn_json_full %>%
  select(1:3,pbl_lata = lata, 4:6, 8)

write.csv2(bn_json_full, "C:/Users/User/Desktop/wzorcowe_bn_i_pbl_tworcy.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')

###prace z drugim plikiem
PBL <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//pbl.ibl.poznan.pl:1521/xe", "IBL_SELECT", "CR333444")
pbl_tworcy <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id, tw.tw_rok_urodzin||'-'||tw.tw_rok_zgonu \"lata\"
                          from pbl_tworcy tw")
pbl_tworcy <- pbl_tworcy %>%
  filter(lata!="-")

test <- merge(test,pbl_tworcy,by = "TW_TWORCA_ID", all.x = TRUE)
test <- test %>%
  select(1:3,pbl_lata = lata, 4:6)

write.csv2(bn_json_full, "C:/Users/User/Desktop/wzorcowe_bn_i_pbl_tworcy.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')

```

```{r utożsamienie na podstawie lat życia}
pbl_bn <- bn_json_full #gs_read(gs_title("wzorcowe_bn_i_pbl_tworcy"), ws = "wzorcowe_bn_i_pbl_tworcy")
pbl_bn <- test
x <- 1:length(pbl_bn$TW_TWORCA_ID)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  pbl_bn$jest_data[i] <- grepl(pbl_bn$pbl_lata[i],pbl_bn$bn_name[i])
}
pbl_bn$jest_data <- ifelse(is.na(pbl_bn$jest_data),0,as.integer(pbl_bn$jest_data))
pbl_bn <- pbl_bn %>%
  mutate(pbl_nazewnictwo = paste(TW_NAZWISKO,TW_IMIE,sep = ", ")) %>%
  mutate(identyczne_nazewnictwo = pbl_nazewnictwo==bn_name)
pbl_bn$pbl_nazewnictwo <- NULL
pbl_bn <- pbl_bn %>%
  mutate(czy_ten_sam = ifelse(jest_data==1,"tak",NA))
pbl_bn <- pbl_bn %>%
  group_by(TW_TWORCA_ID) %>%
  arrange(TW_TWORCA_ID, czy_ten_sam) %>%
  ungroup()

#sprawdzić, czy identyfikator się powtarza
pbl_bn %$%  
    { TW_TWORCA_ID == dplyr::lag(TW_TWORCA_ID, 1) } %>% 
    as.numeric() %>% 
    {.} -> pbl_bn$same
pbl_bn$same[1] <- 0

pbl_bn <- pbl_bn %>%
  mutate(czy2 = czy_ten_sam) %>%
  group_by(TW_TWORCA_ID) %>%
  fill(czy2)

pbl_bn <- pbl_bn %>%
  mutate(czy_ten_sam = ifelse(is.na(czy_ten_sam)&czy2=="tak","nie",ifelse(czy_ten_sam=="tak"&czy2=="tak","tak",NA)))

test <- pbl_bn %>%
  select(-jest_data,-same,-czy2,-identyczne_nazewnictwo) %>%
  filter(czy_ten_sam!="nie"|is.na(czy_ten_sam))

count <- as.data.frame(table(test$TW_TWORCA_ID))

test <- merge(x=test,y=count, by.x = "TW_TWORCA_ID", by.y = "Var1")
test <- test %>%
  mutate(czy_ten_sam = ifelse(is.na(czy_ten_sam)&Freq==1,"raczej tak",ifelse(czy_ten_sam=="tak","tak",NA)))
pbl_bn <- test %>%
  select(-Freq)

write.csv2(pbl_bn, "C:/Users/Cezary/Desktop/bn_to_pbl.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')
```

```{r utożsamienie po dacie z zapisu o rodzaju zgon}
#test <- gs_read(gs_title("mapowanie_osob_bn_pbl"), ws = "bn_to_pbl")

pbl_bn <- pbl_bn %>%
  mutate(lp = 1:length(TW_TWORCA_ID))

tworcy_search <- pbl_bn %>%
  filter(is.na(czy_ten_sam)) %>%
  select(TW_TWORCA_ID) %>%
  unique()

zgon <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id,z.za_adnotacje
                          from pbl_tworcy tw
                          join IBL_OWNER.pbl_zapisy_tworcy zt on zt.zatw_tw_tworca_id=tw.tw_tworca_id
                          join IBL_OWNER.pbl_zapisy z on zt.zatw_za_zapis_id=z.za_zapis_id
                          and z.za_rz_rodzaj1_id=44")
zgon <- sqldf("select *
              from zgon a
              where a.TW_TWORCA_ID in (select b.TW_TWORCA_ID from tworcy_search b)")
zgon <- zgon %>%
  filter(!is.na(ZA_ADNOTACJE)) %>%
  mutate(rok = str_extract(ZA_ADNOTACJE,"\\d{4}")) %>%
  select(TW_TWORCA_ID,rok) %>%
  unique() %>%
  filter(!is.na(rok))
zgon$rok <- paste("-",zgon$rok,sep = "")

test <- merge(x=pbl_bn, y=zgon, by = "TW_TWORCA_ID")

x <- 1:length(test$TW_TWORCA_ID)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  test$jest_data[i] <- grepl(test$rok[i],test$bn_name[i])
}

test <- test %>%
  group_by(TW_TWORCA_ID) %>%
  mutate(grupa = paste(jest_data,collapse = ","))
test$strzal_w_grupie <- grepl("TRUE",test$grupa)
test <- test %>%
  filter(strzal_w_grupie==TRUE) %>%
  select(-grupa)
test <- test %>%
  select(lp,jest_data) %>%
  ungroup()
test <- test %>%
  select(lp,jest_data)

pbl_bn <- merge(pbl_bn,test,by = "lp", all.x = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(czy_ten_sam = ifelse(!is.na(czy_ten_sam),as.character(czy_ten_sam),ifelse(jest_data==TRUE,"tak",ifelse(jest_data==FALSE,"nie",NA))))
pbl_bn <- pbl_bn %>%
  select(-jest_data)

write.csv2(test, "C:/Users/cezar/Desktop/test.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')
```

```{r utożsamienie na podstawie twórczości pbl i bn}
#wgranie całej bazy książek
bn_ks <- read.csv2("C:/Users/cezar/Desktop/bn_data_ks_table.csv", encoding = "Windows-1250", header = TRUE, stringsAsFactors = FALSE)
remove(bn_ks)
bn_ks_light <- bn_ks %>%
  filter(X100!="") %>%
  select(X009,X100,X245)

marc_field100 <- bn_ks_light %>%
  select(X009,X100)#%>%
  #filter(X245!="")
#marc_field100$X245<-str_replace_all(marc_field100$X245,"(^|\\|)","~\\1")
#marc_field100<- cSplit(marc_field100,"X245",sep = "~",direction = "long")
#marc_field100<- marc_field100%>%
#  filter(X245!="")
marc_field100$X100<-str_remove_all(marc_field100$X100,"^\\|")
marc_field100 <- mutate(marc_field100,
               indicator = str_replace_all(marc_field100$X100,"(^.*?)(\\$.*)","\\1"))
subfield_list<- str_extract_all(bn_ks_light$X100,"\\$.")
subfield_list<- unique(unlist(subfield_list))
empty_table<- data.frame(matrix(ncol = length(subfield_list),nrow = lengths(marc_field100)[1]))
colnames(empty_table) <-subfield_list

marc_field100<-cbind(marc_field100,empty_table)

subfield_list_char <- paste("(",subfield_list,")",sep = "")
subfield_list_char <- str_replace_all(subfield_list_char,"\\$","\\\\$")

x <- 1:length(subfield_list)

for (i in x) {
  marc_field100$X100 <- str_replace(marc_field100$X100,subfield_list_char[i],"|\\1")
  progress(match(i,x), max.value = length(x)) 
}
for (i in x) {
  subfield_list_char2 <- str_replace_all(subfield_list,"\\$","\\\\$")
string_a <- "(^)(.*?\\|"
string_b <- subfield_list_char2[i]
string_c <- ")(.*?)(\\,{0,1})((\\|\\$)(.*)|$)"
string <- paste(string_a,string_b,string_c,sep = "")
marc_field100[,i+3] <- ifelse(grepl(subfield_list_char2[i],marc_field100$X100),str_replace_all(gsub(string,"\\3",marc_field100$X100),"\\${2}.", "~"),NA)
progress(match(i,x), max.value = length(x))  
}

marc_field100$`$d` <- ifelse(substr(marc_field100$`$d`,1,1)!="(",paste("(",marc_field100$`$d`,sep = ""),ifelse(is.na(marc_field100$`$d`),NA,as.character(marc_field100$`$d`)))
marc_field100$`$d` <- str_replace(marc_field100$`$d`,";",")")
marc_field100$`$d` <- str_replace(marc_field100$`$d`,"\\)\\.",")")
marc_field100$`$a` <- str_replace(marc_field100$`$a`,"([a-zaáàâãäăāåąæeéèêëěēėęiíìîïīįioóòôõöőøœuúùûüűūůyýcćčçdďđđgģğkķlłļnńñňņŋrřsśšşsßtťŧþţ8zżźž])(\\.)$","\\1")
marc_field100 <- marc_field100 %>%
  mutate(name = ifelse(!is.na(`$d`),paste(`$a`,`$d`,sep = " "),`$a`)) %>%
  select(X009,name)

marc_field245 <- bn_ks_light %>%
  select(X009,X245)#%>%
  #filter(X245!="")
#marc_field245$X245<-str_replace_all(marc_field245$X245,"(^|\\|)","~\\1")
#marc_field245<- cSplit(marc_field245,"X245",sep = "~",direction = "long")
#marc_field245<- marc_field245%>%
#  filter(X245!="")
marc_field245$X245<-str_remove_all(marc_field245$X245,"^\\|")
marc_field245 <- mutate(marc_field245,
               indicator = str_replace_all(marc_field245$X245,"(^.*?)(\\$.*)","\\1"))
subfield_list<- str_extract_all(bn_ks_light$X245,"\\$.")
subfield_list<- unique(unlist(subfield_list))
empty_table<- data.frame(matrix(ncol = length(subfield_list),nrow = lengths(marc_field245)[1]))
colnames(empty_table) <-subfield_list

marc_field245<-cbind(marc_field245,empty_table)

subfield_list_char <- paste("(",subfield_list,")",sep = "")
subfield_list_char <- str_replace_all(subfield_list_char,"\\$","\\\\$")

x <- 1:length(subfield_list)

for (i in x) {
  marc_field245$X245 <- str_replace(marc_field245$X245,subfield_list_char[i],"|\\1")
  progress(match(i,x), max.value = length(x)) 
}
for (i in x) {
  subfield_list_char2 <- str_replace_all(subfield_list,"\\$","\\\\$")
string_a <- "(^)(.*?\\|"
string_b <- subfield_list_char2[i]
string_c <- ")(.*?)(\\,{0,1})((\\|\\$)(.*)|$)"
string <- paste(string_a,string_b,string_c,sep = "")
marc_field245[,i+3] <- ifelse(grepl(subfield_list_char2[i],marc_field245$X245),str_replace_all(gsub(string,"\\3",marc_field245$X245),"\\${2}.", "~"),NA)
progress(match(i,x), max.value = length(x))  
}
marc_field245$`$a` <- str_replace(marc_field245$`$a`,"..$","")
marc_field245 <- marc_field245 %>%
  select(X009,title = `$a`)

bn_ks_light <- merge(bn_ks_light,marc_field100,by = "X009",all.x = TRUE)
bn_ks_light <- merge(bn_ks_light,marc_field245,by = "X009",all.x = TRUE)

bn_ks_light <- bn_ks_light %>%
  select(X009,name,title) %>%
  unique()


plist <- pbl_bn %>%
  filter(is.na(czy_ten_sam)) %>%
  mutate(name_single = str_replace_all(name,"([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ][a-zaáàâãäăāåąæeéèêëěēėęiíìîïīįioóòôõöőøœuúùûüűūůyýcćčçdďđđgģğkķlłļnńñňņŋrřsśšşsßtťŧþţ8zżźž-]*){0,1}([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ][a-zaáàâãäăāåąæeéèêëěēėęiíìîïīįioóòôõöőøœuúùûüűūůyýcćčçdďđđgģğkķlłļnńñňņŋrřsśšşsßtťŧþţ8zżźž]*,)","\\|\\1\\2"))
plist <- plist %>%
  mutate(name_single = str_replace_all(name_single,"(\\) )([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ])","\\1\\|\\2"))

plist <- cSplit(plist,"name_single",sep = "|",direction = "long")
plist <- plist %>%
  filter(name_single!="")

test <- merge(x=plist,y=bn_ks_light,by.x = "name_single",by.y = "name")

#przygotowanie danych pbl
ks_pbl <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id, tw.tw_nazwisko||', '||tw.tw_imie \"osoba\",z.za_tytul
                          from pbl_tworcy tw
                          join IBL_OWNER.pbl_zapisy_tworcy zt on zt.zatw_tw_tworca_id=tw.tw_tworca_id
                          join IBL_OWNER.pbl_zapisy z on zt.zatw_za_zapis_id=z.za_zapis_id
                          and z.za_rz_rodzaj1_id in (1,741)")

tworcy_search <- pbl_bn %>%
  filter(is.na(czy_ten_sam)) %>%
  select(pbl_id) %>%
  unique()
ks_pbl <- sqldf("select *
              from ks_pbl a
              where a.TW_TWORCA_ID in (select b.pbl_id from tworcy_search b)")
write.csv2(ks_pbl, "C:/Users/cezar/Desktop/ks_pbl.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')
ks_pbl <- read.csv2("C:/Users/User/Desktop/ks_pbl.csv", encoding = "Windows-1250", header = TRUE, stringsAsFactors = FALSE)

test <- merge(x=test,y=ks_pbl,by.x = "pbl_id",by.y = "TW_TWORCA_ID")

test$title_simple <- str_replace_all(str_to_lower(test$title), "\\W", "")
#test$title_simple <- iconv(test$title_simple, from ="UTF-8", to="ASCII//TRANSLIT")
test$ZA_TYTUL_simple <- str_replace_all(str_to_lower(test$ZA_TYTUL), "\\W", "")


x <- 1:length(test$pbl_id)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  test$jest_hit[i] <- grepl(test$title_simple[i],test$ZA_TYTUL_simple[i])
}
efekt_full <- test %>%
  filter(jest_hit==TRUE) %>%
  select(1:12) %>%
  unique()
efekt_full$czy_ten_sam <- "tak"
efekt <- efekt_full %>%
  select(pbl_id) %>%
  unique()

test2 <- pbl_bn#gs_read(gs_title("mapowanie_osob_bn_pbl"), ws = "bn_to_pbl")
test2 <- sqldf("select *
              from test2 a
              where a.pbl_id in (select b.pbl_id from efekt b)")
efekt_light <- efekt_full %>%
  select(lp,hit = czy_ten_sam)

test2 <- merge(test2,efekt_light,by = "lp",all.x = TRUE)

test2$czy_ten_sam <- ifelse(test2$hit=="tak","tak","nie")
test2$czy_ten_sam[is.na(test2$czy_ten_sam)] <- "nie"
test2 <- test2 %>%
  select(lp,hit = czy_ten_sam)
pbl_bn <- merge(pbl_bn,test2,by = "lp",all = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(czy_ten_sam = ifelse(!is.na(czy_ten_sam),as.character(czy_ten_sam),ifelse(hit=="tak","tak",ifelse(hit=="nie","nie",NA)))) %>%
  select(-hit)
pbl_bn <- pbl_bn %>%
  unique()
write.csv2(pbl_bn, "C:/Users/cezar/Desktop/pbl_bn.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')

```

```{r sparowanie pbl i bn na podstawie twórczości viaf}
#sparowanie pbl i bn na podstawie twórczości viaf
library(XML)
library(methods)

#pbl_bn <- gs_read(gs_title("wzorcowe_bn_i_pbl_tworcy"), ws = "wzorcowe_bn_i_pbl_tworcy")

bn_z_viaf <- pbl_bn %>%
  filter(is.na(czy_ten_sam)) %>%
  select(id,name,viaf) %>%
  filter(viaf!="") %>%
  unique()

#wydobywanie info z xml VIAF
tabela_viaf <- data.frame(id = character(), name = character(), gender = character(), VIAF = character(), Zawod = character(), Linki = character(), Tworczosc = character(), stringsAsFactors=FALSE)

x <- 1:length(bn_z_viaf$id)
start_time <- Sys.time()
for (i in x) {
progress(i, max.value = length(x))
tryCatch({
poczatek_url <- bn_z_viaf$viaf[i]
koniec_url <- "/viaf.xml"
url <- paste(poczatek_url, koniec_url, sep = "")
xml <- xmlParse(url)

tryCatch({
a <- setNames(xmlToDataFrame(nodes = getNodeSet(xml, "//ns1:VIAFCluster/ns1:fixed/ns1:gender")),"gender")
a2 <- paste(unlist(a), collapse = "~")
}, error=function(e){
                    a2 <<- "Brak danych (CR)"
})
tryCatch({
  e <- setNames(xmlToDataFrame(nodes = getNodeSet(xml, "//ns1:VIAFCluster/ns1:occupation/ns1:data/ns1:text")),"Zawod")
e2 <- paste(unlist(e), collapse = "~")
}, error=function(e){
                    e2 <<- "Brak danych (CR)"
})
tryCatch({
  f <- setNames(xmlToDataFrame(nodes = getNodeSet(xml, "//ns1:VIAFCluster/ns1:xLinks/ns1:xLink")),"Linki")[1]
f2 <- paste(unlist(f), collapse = "~")
}, error=function(e){
                    f2 <<- "Brak danych (CR)"
})
tryCatch({
  g <- setNames(xmlToDataFrame(nodes = getNodeSet(xml, "//ns1:VIAFCluster/ns1:titles/ns1:work/ns1:title")),"Tworczosc")
g2 <- paste(unlist(g), collapse = "~")
}, error=function(e){
                    g2 <<- "Brak danych (CR)"
})
total_i <- data.frame(id = bn_z_viaf$id[i], name = bn_z_viaf$name[i], gender = a2, VIAF = bn_z_viaf$viaf[i], Zawod = e2, Linki = f2, Tworczosc = g2)

tabela_viaf <- rbind(tabela_viaf,total_i)
}, error=function(e){NULL})

}
end_time <- Sys.time()

print(end_time - start_time)
write.csv2(tabela_viaf, "C:/Users/cezar/Desktop/bn_viaf_full.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')

#połączenie z bazą PBL
jdbcDriver =JDBC("oracle.jdbc.OracleDriver",classPath="C:/Users/cezar/Documents/R/ojdbc6.jar")
#jdbcDriver =JDBC("oracle.jdbc.OracleDriver",classPath="E:/Cezary/Documents/ojdbc6.jar")
PBL <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//pbl.ibl.poznan.pl:1521/xe", "IBL_SELECT", "CR333444")

tabela_viaf$tworczosc_nazwa <- str_replace_all(str_to_lower(tabela_viaf$Tworczosc), "\\W", "")
tabela_viaf$tworczosc_nazwa <- iconv(tabela_viaf$tworczosc_nazwa, from ="UTF-8", to="ASCII//TRANSLIT")

#wczytanie danych PBL
pbl_ksiazki <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id, z.za_zapis_id, z.za_tytul
                          from pbl_tworcy tw
                          join IBL_OWNER.pbl_zapisy_tworcy zt on zt.zatw_tw_tworca_id=tw.tw_tworca_id
                          join pbl_zapisy z on zt.zatw_za_zapis_id=z.za_zapis_id
                          where z.za_rz_rodzaj1_id=1")
pbl_ksiazki$ZA_TYTUL <- ifelse(substr(pbl_ksiazki$ZA_TYTUL,nchar(pbl_ksiazki$ZA_TYTUL),nchar(pbl_ksiazki$ZA_TYTUL))=="]",str_replace(pbl_ksiazki$ZA_TYTUL,"(^.*?)(\\[.*$)","\\1"),ifelse(substr(pbl_ksiazki$ZA_TYTUL,1,1)=="[",str_replace(pbl_ksiazki$ZA_TYTUL,"(^.*?\\])(.*$)","\\2"),as.character(pbl_ksiazki$ZA_TYTUL)))

pbl_ksiazki <- sqldf("select *
               from pbl_ksiazki a
               where a.TW_TWORCA_ID in 
               (select b.pbl_id
               from pbl_bn b)")
pbl_ksiazki$tytul_nazwa <- str_replace_all(str_to_lower(pbl_ksiazki$ZA_TYTUL), "\\W", "")
pbl_ksiazki$tytul_nazwa <- iconv(pbl_ksiazki$tytul_nazwa, from ="UTF-8", to="ASCII//TRANSLIT")

#SQL
merge_ksiazek <- sqldf("select * 
                       from tabela_viaf a
                       inner join pbl_ksiazki b on a.tworczosc_nazwa like ('%'||b.tytul_nazwa||'%')")
merge_ksiazek <- merge_ksiazek %>%
  filter(ZA_TYTUL!="")

id_bn_id_pbl <- pbl_bn %>%
  select(pbl_id,id,name,viaf) %>%
  filter(!is.na(viaf)) %>%
  unique() %>%
  select(pbl_id,id) %>%
  unique()

merge_ksiazek <- merge(x = merge_ksiazek, y = id_bn_id_pbl, by = "id", all.x = TRUE)

test <- merge_ksiazek %>%
  filter(TW_TWORCA_ID==pbl_id)
test <- test %>%
  select(id,name,gender,VIAF,Zawod,Linki,TW_TWORCA_ID) %>%
  unique()
test_full <- test
test <- test %>%
  select(pbl_id = TW_TWORCA_ID,id)
test$match <- paste(test$pbl_id,test$id,sep = "|")
test$hit <- "tak"
test <- test %>%
  select(match,hit)
pbl_bn$match <- paste(pbl_bn$pbl_id,pbl_bn$id,sep = "|")
pbl_bn <- merge(pbl_bn,test,by = "match",all.x = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(czy_ten_sam = ifelse(!is.na(czy_ten_sam),as.character(czy_ten_sam),ifelse(hit=="tak","tak",ifelse(hit=="nie","nie",NA)))) %>%
  select(-hit)
test3 <- pbl_bn
test3 <- test3 %>%
  mutate(hit = ifelse(is.na(hit),czy_ten_sam,as.character(hit))) %>%
  mutate(czy_ten_sam = hit) %>%
  select(-hit,-match)

test3 <- test3 %>%
  mutate(czy2 = czy_ten_sam) %>%
  arrange(pbl_id,czy_ten_sam) %>%
  group_by(pbl_id) %>%
  fill(czy2)

test4 <- test3 %>%
  mutate(czy_ten_sam = ifelse(is.na(czy_ten_sam)&czy2=="tak","nie",ifelse(czy_ten_sam=="tak"&czy2=="tak","tak",ifelse(czy_ten_sam=="raczej tak"&czy2=="raczej tak","raczej tak", ifelse(czy_ten_sam=="nie"&czy2=="nie","nie",NA)))))

pbl_bn <- test4 %>%
  select(-czy2) %>%
  filter(is.na(czy_ten_sam)|czy_ten_sam!="nie")

write.csv2(pbl_bn, "C:/Users/cezar/Desktop/bn_to_pbl.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')
#koniec
```

```{r usunąć dookreślenia niefilologiczne}
plist <- pbl_bn %>%
  filter(is.na(czy_ten_sam)) %>%
  mutate(name_single = str_replace_all(bn_name,"([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ][a-zaáàâãäăāåąæeéèêëěēėęiíìîïīįioóòôõöőøœuúùûüűūůyýcćčçdďđđgģğkķlłļnńñňņŋrřsśšşsßtťŧþţ8zżźž-]*){0,1}([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ][a-zaáàâãäăāåąæeéèêëěēėęiíìîïīįioóòôõöőøœuúùûüűūůyýcćčçdďđđgģğkķlłļnńñňņŋrřsśšşsßtťŧþţ8zżźž]*,)","\\|\\1\\2"))
plist <- plist %>%
  mutate(name_single = str_replace_all(name_single,"(\\) )([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ])","\\1\\|\\2"))

plist <- cSplit(plist,"name_single",sep = "|",direction = "long")
plist <- plist %>%
  filter(name_single!="")

dookreslenie <- plist %>%
  select(name_single)
dookreslenie$test <- ifelse(grepl("(.*?)(\\()([^\\d\\-]*)(\\))",dookreslenie$name_single),str_replace(dookreslenie$name_single,"(.*?)(\\()([^\\d]*)(\\))","\\3"),"")
dookreslenie$test <- ifelse(grepl("^[[:upper:]]+$",substr(dookreslenie$test,1,1))==TRUE,"",as.character(dookreslenie$test))
dookreslenie <- dookreslenie %>%
  select(test) %>%
  filter(test!="") %>%
  unique() %>%
  arrange(test)

write.csv2(dookreslenie, "C:/Users/cezar/Desktop/deskryptory.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')

```

```{r usunięcie osób z niechcianymi deskryptorami i raczej tak dla pojedynczych wystąpień}
pbl_bn <- gs_read(gs_title("bn_to_pbl"), ws = "bn_to_pbl")
deskryptory <- sheets_read(ss = "1K22oE5Px04jrdlFFL9Er18vQe1aDBkB38st8pyXZAI0")
deskryptory <- deskryptory %>%
  filter(czy_zostaje=="nie")
deskryptory$deskryptory <- paste("(",deskryptory$deskryptory,")",sep = "")
test <- sqldf("select * 
                from pbl_bn a
                join deskryptory b on a.bn_name like ('%'||b.deskryptory||'%')")
test <- test %>%
  mutate(czy_ten_sam = ifelse(!is.na(czy_ten_sam),as.character(czy_ten_sam),czy_zostaje)) %>%
  mutate(czy_liczba = grepl("\\d+",bn_name))
test <- test %>%
  filter(czy_liczba==FALSE) %>%
  filter(czy_ten_sam=="nie") %>%
  select(lp,czy2 = czy_ten_sam)
pbl_bn <- merge(pbl_bn,test,by = "lp",all.x = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(test = ifelse(czy_ten_sam %in% c("tak","nie","raczej tak"),as.character(czy_ten_sam),ifelse(czy2=="nie","nie",NA)))
pbl_bn <- pbl_bn %>%
  mutate(czy_ten_sam = test) %>%
  select(-czy2,-test) %>%
  unique() %>%
  filter(czy_ten_sam!="nie"|is.na(czy_ten_sam))

test <- pbl_bn %>%
  filter(is.na(czy_ten_sam))

count <- as.data.frame(table(test$TW_TWORCA_ID))

test <- merge(x=test,y=count, by.x = "TW_TWORCA_ID", by.y = "Var1")
test <- test %>%
  mutate(czy_ten_sam = ifelse(is.na(czy_ten_sam)&Freq==1,"raczej tak",NA))
test <- test %>%
  filter(!is.na(czy_ten_sam)) %>%
  select(lp,czy2 = czy_ten_sam)

pbl_bn <- merge(pbl_bn,test,by = "lp",all.x = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(test = ifelse(czy_ten_sam %in% c("tak","nie","raczej tak"),as.character(czy_ten_sam),ifelse(czy2=="raczej tak","raczej tak",NA)))

pbl_bn <- pbl_bn %>%
  mutate(czy_ten_sam = test) %>%
  select(-czy2,-test) %>%
  unique() %>%
  filter(czy_ten_sam!="nie"|is.na(czy_ten_sam))

write.csv2(pbl_bn, "C:/Users/Cezary/Desktop/pbl_bn_drugi_plik.csv", row.names = F, na = '', fileEncoding = 'Windows-1250')


```

```{r podzial nazewnictw bn}
pbl_bn_viaf <- read.csv2("C:/Users/Cezary/Desktop/viaf/mapowanie_osob_vol_2.csv", encoding = "Windows-1250", header = TRUE, stringsAsFactors = FALSE)

pbl_viaf <- data.frame(stringsAsFactors=FALSE)
x <- 1:length(pbl_bn_viaf$TW_TWORCA_ID)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  tryCatch({
  name <- paste(str_replace_all(str_remove_all(paste(unlist(jsonlite::fromJSON(paste("https://data.bn.org.pl/api/authorities.json?id=",pbl_bn_viaf$bn_id[i],sep = "")) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`100` %>% `[[`(3)),collapse = "|"),"\\|NA"),"(\\|)(\\(\\d+)"," \\2"),str_replace_all(str_remove_all(paste(unlist(jsonlite::fromJSON(paste("https://data.bn.org.pl/api/authorities.json?id=",pbl_bn_viaf$bn_id[i],sep = "")) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`400` %>% `[[`(3)),collapse = "|"),"\\|NA"),"(\\|)(\\(\\.+)"," \\2"),sep = "|")
  viaf <- str_remove_all(paste(unlist(jsonlite::fromJSON(paste("https://data.bn.org.pl/api/authorities.json?id=",pbl_bn_viaf$bn_id[i],sep = "")) %>% .$authorities %>% .$marc %>% .$fields %>% `[[`(1) %>% .$`024` %>% .$subfields),collapse = "|"),"(\\|NA)|(\\|viaf)")
  }, error=function(e){
                     name <<- "brak danych (CR)"
                     viaf <<- "brak danych (CR)"
  })
  iteration <- cbind(pbl_bn_viaf %>% slice(i), name, viaf)
  pbl_viaf <- rbind(pbl_viaf,iteration)
}
write.csv2(pbl_viaf, file = "C:/Users/Cezary/Desktop/pbl_bn_viaf2.csv", row.names = F, na = '', fileEncoding = 'UTF-8')


```