aqualectrawriteoffcalc/DataManipulate.Rmd

---
title: "Untitled"
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

## R Markdown

This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see <http://rmarkdown.rstudio.com>.

When you click the **Knit** button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:

```{r cars}
oi<-fst::read.fst("./data/open_items.fst")
# oi %>% mutate(spread=ifelse(spread=="Only less than 5", "Only less than 5 yrs",
#                             ifelse(spread==" Only more than 5","Only more than 5 yrs",spread))) %>% fst::write.fst("./data/open_items.fst")
  
id<-read.csv("./data/id.csv", sep=";", header=F)
library(dplyr)
indi<-id %>% 
  filter(V3=="Identiteitsbewijs") %>% 
  select(V2,V4)
orgs<-id |> dplyr::filter(V3=="KVK – Chamber of Commerce") |> unique()
disorgs<-read.csv("./data/diorg.csv") |> unique()
arch<-read.csv("./data/Arch.csv")

dead.indi<-arch %>% filter(!is.na(Datum.Overlijden.Nr.)) %>%  select(c(Id.Nummer,Datum.Overlijden.Nr.)) %>% mutate(e.status="Passed", type="indi")%>% mutate(Id.Nummer=as.character(Id.Nummer))
left.indi<-arch %>% filter(!is.na(Datum.Vertrek.Uit.Curacao.Nr.)) %>%  select(Id.Nummer)%>% mutate(e.status="Left", type="indi") %>% mutate(Id.Nummer=as.character(Id.Nummer))

left.indi<-indi %>% unique() %>%  inner_join(left.indi, by=c("V4"="Id.Nummer")) %>% rename(business_partner=V2) %>% 
  select(business_partner, e.status, type) %>% 
   mutate(Year=c(0)) %>% 
  select(business_partner, e.status, Year, type)
dead.indi<-indi %>% unique() %>%  inner_join(dead.indi, by=c("V4"="Id.Nummer")) %>% rename(business_partner=V2) %>% 
  mutate(Year=c(0)) %>% 
  select(business_partner, e.status, Year, type)

disc<-read.csv("./data/coc.csv") %>% unique() %>% select(-X) %>% mutate(Year=ifelse(is.na(Year),0,Year), type=c("org")) %>% 
  rename(business_partner=BPC, e.status=Status) %>% 
  group_by(business_partner, e.status) %>% 
  slice_max(order_by = Year,n=1) %>% select(-Name) %>% mutate(business_partner=as.character(business_partner))
dead.org<-fst::read.fst("./data/dead.fst") %>% select(V2) %>% rename(business_partner=V2)%>% mutate(e.status="Discontinued", type="org") %>%  select(business_partner, e.status, type) %>% mutate(Year=c(0))
disc.org<-rbind(disc,dead.org) %>% group_by(business_partner) %>% mutate(count=n()) %>% 
  group_by(business_partner) %>% 
  slice_max(order_by = Year,n=1) %>% 
  mutate(count=n()) %>% 
  arrange(desc(count), business_partner) %>% 
  mutate(rown=row_number()) %>% 
  slice_max(order_by = rown,n=1) %>% ungroup() %>% select(-c(count,rown))



ct<-rbind(disc.org,left.indi,dead.indi)

oi<-oi %>% select(-c(e.status)) %>% 
  left_join(ct, by="business_partner") %>% 
  mutate(e.status=ifelse(is.na(e.status) | e.status=="No results","active",e.status)) %>% select(-c(type))





dunn<-fst::read.fst("./data/dunning.fst")
last.dunn<-dunn %>% 
  group_by(DUNN_GPART, DUNN_VKONT) %>% 
  filter(DUNN_DATE==max(DUNN_DATE))%>% 
  mutate(DUNN_GPART=as.character(DUNN_GPART))

no.cont.dunn<-last.dunn %>% 
  filter(is.na(DUNN_VKONT)) %>% 
  mutate(DUNN_GPART=as.character(DUNN_GPART)) %>% 
  group_by(DUNN_GPART) %>% 
  filter(DUNN_DATE==max(DUNN_DATE)) %>% # To find out the last dunning
  select(DUNN_GPART, DUNN_VKONT, DUNN_BILL_DOC, DUNN_ACTIVITY, DUNN_BILL_DOC, DUNN_DATE) %>% 
  unique() %>% mutate(DUNN_VKONT=as.character(DUNN_VKONT))

cont.dunn<-last.dunn %>% 
  filter(!is.na(DUNN_VKONT)) %>% 
  mutate(DUNN_VKONT=as.character(DUNN_VKONT)) %>% 
  group_by(DUNN_VKONT) %>% 
  filter(DUNN_BILL_DOC==max(DUNN_BILL_DOC)) %>% # To find out the last dunning
  select(DUNN_GPART, DUNN_VKONT, DUNN_BILL_DOC, DUNN_ACTIVITY, DUNN_BILL_DOC,DUNN_DATE) %>% 
  unique() # Because there are several dunnings on same date
  
 dunn.list.to.use<-
 rbind(no.cont.dunn, cont.dunn) %>% 
 group_by(DUNN_GPART) %>% 
 filter(DUNN_DATE==max(DUNN_DATE)) %>% 
 select(DUNN_GPART, DUNN_VKONT, DUNN_DATE) %>% 
 unique()
dunn.list.to.use.nocont<-dunn.list.to.use %>% filter(is.na(DUNN_VKONT))
dunn.list.to.use.cont<-dunn.list.to.use %>% filter(!is.na(DUNN_VKONT))

oi<-
  oi %>% 
  left_join(dunn.list.to.use.cont, by=c("contract_account"="DUNN_VKONT")) %>% 
  left_join(dunn.list.to.use.nocont, by=c("business_partner"="DUNN_GPART")) %>% 
  mutate(
    dunn_status= case_when(
      is.na(DUNN_DATE.x) & is.na(DUNN_DATE.y) ~ "No Dunning",
      is.na(DUNN_DATE.x) & (due_date>DUNN_DATE.y) ~ "Dunned in Past",
      is.na(DUNN_DATE.y) & (due_date>DUNN_DATE.x) ~ "Dunned in Past",
      TRUE ~ "Dunned"
    )
  ) %>% 
  select(-c(DUNN_GPART, DUNN_DATE.x,DUNN_VKONT,DUNN_DATE.y))

govtnow<-read.csv("./data/govtnow.csv") |> dplyr::select(1:2) |> dplyr::mutate(business_partner=as.character(business_partner))
noi<-oi |> 
  dplyr::inner_join(govtnow, by="business_partner") |> 
  dplyr::mutate(govt.status=
           dplyr::case_when(
             contract_type=="Government" & !is.na(Name) ~ "Govt in both",
             contract_type=="Own" & !is.na(Name) ~ "Own in SAP govt in list",
             contract_type=="Street Light" & !is.na(Name) ~ "Street Light in SAP govt in list",
             contract_type=="Government" & is.na(Name) ~ "Govt in SAP not in list",
             contract_type=="Own" & is.na(Name) ~ "Own in SAP not in list",
             contract_type=="Street Light" & is.na(Name) ~ "Street Light in SAP not in list",
             contract_type=="Commercial" & !is.na(Name) ~ "Commercial in SAP govt in list",
             contract_type=="Residential" & !is.na(Name) ~ "Residential in SAP govt in list",
             contract_type=="Industrial" & !is.na(Name) ~ "Industrial in SAP govt in list",
             contract_type=="Commercial" & is.na(Name) ~ "Commercial in SAP not in list",
             contract_type=="Residential" & is.na(Name) ~ "Residential in SAP not in list",
             contract_type=="Industrial" & is.na(Name) ~ "Industrial in SAP not in list"
             
           )
           )

noi |> 
  dplyr::select(business_partner, contract_account, contract_type,govt.status) |> write.csv("")
oi1<-oi |> 
  dplyr::left_join(govtnow, by="business_partner") |> 
   dplyr::mutate(new_contract_type=
           dplyr::case_when(
             contract_type=="Government" & !is.na(Name) ~ "Government",
             contract_type=="Own" & !is.na(Name) ~ "Own",
             contract_type=="Street Light" & !is.na(Name) ~ "Government",
             contract_type=="Government" & is.na(Name) ~ "Government",
             contract_type=="Own" & is.na(Name) ~ "Own",
             contract_type=="Street Light" & is.na(Name) ~ "Government",
             contract_type=="Commercial" & !is.na(Name) ~ "Government",
             contract_type=="Residential" & !is.na(Name) ~ "Government",
             contract_type=="Industrial" & !is.na(Name) ~ "Government",
             contract_type=="Commercial" & is.na(Name) ~ "Commercial",
             contract_type=="Residential" & is.na(Name) ~ "Residential",
             contract_type=="Industrial" & is.na(Name) ~ "Industrial"
             
           )
           )



fst::write.fst(oi1,"./data/open_items.fst")
rm(noi)
disc

```


```{r}
noi |> dplyr::filter(govt.status=="Commercial in SAP govt in list")
unique(oi1$new_contract_type)
unique(oi$o.type)
unique(oi$contract_type)
oi1 |> 
  dplyr::filter(new_contract_type=="Own") |> 
  dplyr::filter(status.x=="Only Active") |> 
  group_by(business_partner,contract_account, negative) |>  
    summarize(amount=sum(amount)) |> 
  pivot_wider(names_from=negative, values_from=amount) |> 
  tfrnma()

tfrnma<-function(dat){
  if("TRUE" %in% colnames(dat)){
    dat<-dat |> rename(credits=`TRUE`)
  }
    if("FALSE" %in% colnames(dat)){
    dat<-dat |> rename(credits=`FALSE`)
    }
  dat
}    
    
```

```{r}
oi |> 
  dplyr::filter(documen_desc=="Installments")
```


```{r}
oi
library(lubridate)
dead.indi.wt.date<-arch %>% filter(!is.na(Datum.Overlijden.Nr.)) %>%  select(c(Id.Nummer,Datum.Overlijden.Nr.)) %>% mutate(e.status="Passed", type="indi")%>% mutate(Id.Nummer=as.character(Id.Nummer)) %>% 
mutate(Datum.Overlijden.Nr.=ymd(Datum.Overlijden.Nr.)) 


dead.indi.wt.date[is.na(dead.indi.wt.date$Datum.Overlijden.Nr.),]$Datum.Overlijden.Nr.<-as.Date(c("1996-01-01","1993-07-01"))


dead.indi.wt.date<-indi %>% unique() %>%  inner_join(dead.indi.wt.date, by=c("V4"="Id.Nummer")) %>% rename(business_partner=V2) %>% 
  rename(Year=Datum.Overlijden.Nr.) %>% 
  select(business_partner, e.status, Year, type)


left.indi.wt.date<-arch %>% filter(!is.na(Datum.Vertrek.Uit.Curacao.Nr.)) %>%  select(Id.Nummer,Datum.Vertrek.Uit.Curacao.Nr.)%>% mutate(e.status="Left", type="indi") %>% mutate(Id.Nummer=as.character(Id.Nummer)) |> mutate(Datum.Vertrek.Uit.Curacao.Nr.=ymd(Datum.Vertrek.Uit.Curacao.Nr.))

left.indi.wt.date[is.na(left.indi.wt.date$Datum.Vertrek.Uit.Curacao.Nr.),]$Datum.Vertrek.Uit.Curacao.Nr.<- as.Date(c("2019-01-01","2019-01-01","2019-01-01"))


left.indi.wt.date<-indi %>% unique() %>%  inner_join(left.indi.wt.date, by=c("V4"="Id.Nummer")) %>% rename(business_partner=V2) %>% 
   rename(Year=Datum.Vertrek.Uit.Curacao.Nr.) %>% 
  select(business_partner, e.status, Year, type)

ct<-rbind(left.indi.wt.date,dead.indi.wt.date)

oi<-oi %>% select(-c(e.status)) %>% 
  left_join(ct, by="business_partner") %>% 
  mutate(e.status=ifelse(is.na(e.status) | e.status=="No results","active",e.status)) %>% select(-c(type)) |> rename(date.event=Year.y)


oi<-oi |> mutate(date.relaxed=date.event+60) |> 
  mutate(rel.status=ifelse(date.relaxed>due_date,"long past","within limit"))

fst::write.fst(oi,"./data/open_items.fst")

orgs
disorgs |> 
  mutate(Registration.number=as.character(Registration.number)) |> 
  inner_join(orgs, by=c("Registration.number"="V4"))

oi |> 
  filter(e.status %in% c("Passed","Left")) |> 
  mutate(age=ifelse(AGE_BUCKET=="5+ years", "More than 5","Less than 5")) |> 
  mutate(rel.status=ifelse(rel.status=="long past", "More than 60 days","Less than 60 days")) |> 
  group_by(contract_account,rel.status,negative, age, dunn_status) |> 
  summarise(amount=sum(amount))
  tidyr::pivot_wider(names_from = c(rel.status,negative), values_from = amount) |> 
  rename(`Long Past Credit` = `long past_TRUE`,
         `Within Limit Open` = `within limit_FALSE`,
         `Within Limit Credit` = `within limit_TRUE`,
         `Long Past Open` = `long past_FALSE`)



disc<-read.csv("./data/coc.csv") %>% unique() %>% select(-X) %>% mutate(Year=ifelse(is.na(Year),0,Year), type=c("org")) %>% 
  rename(business_partner=BPC, e.status=Status) %>% 
  group_by(business_partner, e.status) %>% 
  slice_max(order_by = Year,n=1) %>% select(-Name) %>% mutate(business_partner=as.character(business_partner))
dead.org<-fst::read.fst("./data/dead.fst") %>% select(V2) %>% rename(business_partner=V2)%>% mutate(e.status="Discontinued", type="org") %>%  select(business_partner, e.status, type) %>% mutate(Year=c(0))



disc.org<-rbind(disc,dead.org) %>% group_by(business_partner) %>% mutate(count=n()) %>% 
  group_by(business_partner) %>% 
  slice_max(order_by = Year,n=1) %>% 
  mutate(count=n()) %>% 
  arrange(desc(count), business_partner) %>% 
  mutate(rown=row_number()) %>% 
  slice_max(order_by = rown,n=1) %>% ungroup() %>% select(-c(count,rown))

oi

do1<-disc |> 
  mutate(Year=ymd(paste0(Year,"-01-01"))) |> 
  select(-X) |> 
  select(BPC,Year, Status) |> 
  rename(Reasonunregistered=Status) |> 
  mutate(Status=c("Discontinued"))


disc |> filter(business_partner=="1301003645")

```


```{r}

do2<-read.csv("./data/inactive.csv") |> 
  select(Registration.number,Datediscontinued,Reasonunregistered) |> 
  mutate(Registration.number=as.character(Registration.number),Reasonunregistered=as.character(Reasonunregistered)) |> 
  mutate(Datediscontinued=dmy(Datediscontinued)) |> 
  inner_join(orgs, by=c("Registration.number"="V4")) |> 
  select(V2,Datediscontinued,Reasonunregistered) |> 
  rename(BPC=V2, Year=Datediscontinued) |> 
  mutate(Status="Discontinued")

do2 |> filter(BPC=="1301003645")

```

```{r}
do3<-disorgs |> 
  mutate(Registration.number=as.character(Registration.number)) |> 
  inner_join(orgs, by=c("Registration.number"="V4")) |> 
  mutate(Datediscontinued=mdy(Datediscontinued)) |> 
  select(V2,Datediscontinued,Reasonunregistered) |> 
  rename(BPC=V2, Year=Datediscontinued) |> 
  mutate(Status="Discontinued")

  
do3 |> filter(BPC=="1301095174")
```


```{r}
do123<-rbind(do1,do2,do3) |> 
  group_by(BPC, Status) |> 
  slice_max(order_by = Year,n=1) |> unique() |> 
  left_join(do3.1) |> 
  mutate(Reasonunregistered=ifelse(is.na(Reasonunregistered),"Not Known",Reasonunregistered)) |> 
  mutate(type="org") |> 
  rename(business_partner=BPC, e.status=Status) |> 
  group_by(business_partner) |> 
  slice_max(order_by = Year,n=1) |> unique()


  
```


```{r}
ct<-rbind(left.indi.wt.date,dead.indi.wt.date)
ct<-ct |> mutate(Reasonunregistered="Not relevant for individuals") |> 
  select(1,3,5,2,4) |> rbind(do123)

ct

n.oi<-oi %>% select(-c(e.status, Year.x, date.event, date.relaxed, rel.status, Name)) %>% 
  left_join(ct, by="business_partner") %>% 
  mutate(e.status=ifelse(is.na(e.status) | e.status=="No results","active",e.status)) %>% select(-c(type)) |> rename(date.event=Year) |> 
  mutate(date.relaxed=date.event+60) |> 
  mutate(rel.status=ifelse(date.relaxed>due_date,"long past","within limit"))


fst::write.fst(n.oi,"./data/open_items_new.fst")
unique(n.oi$e.status)
unique(n.oi$Reasonunregistered)
```



```{r}
arch |> filter(Id.Nummer %in% c("1968052211","1972090816","1976052001"))
oi[oi$contract_account=="13090404",]
```

```{r}
oi |> 
  filter(o.type=="Real") |> 
  filter(negative==T) |> 
  group_by(business_partner, contract_account, bp_category, status.x) |> 
  summarize(amount=sum(amount))
```



```{r}
oi |> 
  ungroup() |> 
  ungroup() |> 
  group_by(o.type, negative) |> 
  mutate(amount=sum(amount))
```



## Including Plots

You can also embed plots, for example:

```{r pressure, echo=FALSE}
con <- DBI::dbConnect(RPostgres::Postgres(),dbname = 'postgres', 
                 host = 'lanubiadsdbpgsql.postgres.database.azure.com', # i.e. 'ec2-54-83-201-96.compute-1.amazonaws.com'
                 port = 5432, # or any other port specified by your DBA
                 user = 'lanubiadsdbpgsql@lanubiadsdbpgsql',
                 password = 'LaNubia@2021',
                 base::list(sslmode="require", connect_timeout="10"),
                             service = NULL)

DBI::dbListTables(con)

DBI::dbWriteTable(con, "open_items", oi)

```

Note that the `echo = FALSE` parameter was added to the code chunk to prevent printing of the R code that generated the plot.