---
title: "employees"
author: "Scary Scarecrow"
date: "5/27/2022"
output: html_document
---

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

## Read files


```{r}
mapping<-read.csv("./employees/mapping.csv")
all_users<-read.csv("./employees/all_users.csv")
users<-all_users |> 
  select(c(Username..pre.2000.,Email.Address)) |> 
  rename(username=Username..pre.2000., email=Email.Address)
users
employee<-read.csv("./employees/Employee.csv")
empf<-read.csv("./employees/empf.csv")


```

```{r}
empf<-
  empf |> 
  full_join(employee, by=c("Employee.ID"="Employee_ID")) |> 
  mutate(Name=ifelse(is.na(Name), paste(ifelse(is.na(First_Name),"",First_Name), ifelse(is.na(Middle_Name),"",Middle_Name), ifelse(is.na(Last_Name),"",Last_Name), sep = " "), Name))

empf |> 
  count(Name) |> 
  arrange(desc(n))
write.csv(empf,"emp.csv")
new.map<-
  mapping |> 
  left_join(users, by=c("Email.Address"="email")) |> 
  mutate(username=ifelse(is.na(username),UserName,username))
```


```{r}
empf |> 
  filter(Name=="Christoph  ")
```


```{r}
new.map
```



```{r}
emp<-read.csv("./employees/emp.csv")
nm<-new.map |> select(External.ID,username)
emp<-emp |> 
  left_join(nm, by=c("External_Key"="External.ID")) |> 
  mutate(User_ID=username) |> select(-username)
```

```{r}
sr<-read.csv("./employees/sr.csv")
nm<-new.map |> select(External.ID,Country.From.Extracted.Data.)
sr<-sr |> 
  left_join(nm, by=c("Employee_External_Key"="External.ID")) |> 
  mutate(Sales_Organization_ID=paste0(toupper(Country.From.Extracted.Data.),"01")) |> 
  select(-Country.From.Extracted.Data.)
```



```{r}
ur<-read.csv("./employees/ur.csv")
nm<-new.map |> select(External.ID,username,Role.ID)

ur<-ur |> 
  left_join(nm, by=c("Employee_External_Key"="External.ID")) |> 
  mutate(User_ID=username) |> 
  select(-username)
ur<-ur |> 
  left_join(nm, by=c("Employee_External_Key"="External.ID")) |> 
  mutate(Business_Role_ID=Role.ID) |> 
  select(-c(username,Role.ID))
```


```{r}
ou<-read.csv("./employees/ou.csv")
nm<-new.map |> select(External.ID,username,Org..Unit.ID,Org..Unit.ID.1, Org..Unit.ID.2, Org..Unit.ID.3, Org..Unit.ID.4, Org..Unit.ID.5, Org..Unit.ID.6) |> 
  mutate(Org..Unit.ID.1=ifelse(Org..Unit.ID.1==0 | is.na(Org..Unit.ID.1),"",Org..Unit.ID.1),
         Org..Unit.ID=ifelse(Org..Unit.ID==0 | is.na(Org..Unit.ID),"",Org..Unit.ID),
         Org..Unit.ID.2=ifelse(Org..Unit.ID.2==0 | is.na(Org..Unit.ID.2),"",Org..Unit.ID.2),
         Org..Unit.ID.3=ifelse(Org..Unit.ID.3==0 | is.na(Org..Unit.ID.3),"",Org..Unit.ID.3),
         Org..Unit.ID.4=ifelse(Org..Unit.ID.4==0 | is.na(Org..Unit.ID.4),"",Org..Unit.ID.4),
         Org..Unit.ID.5=ifelse(Org..Unit.ID.5==0 | is.na(Org..Unit.ID.5),"",Org..Unit.ID.5),
         Org..Unit.ID.6=ifelse(Org..Unit.ID.6==0 | is.na(Org..Unit.ID.6),"",Org..Unit.ID.6))
ou<-ou |> 
  left_join(nm, by=c("Employee_External_Key"="External.ID")) |> 
  pivot_longer(cols=c(Org..Unit.ID,Org..Unit.ID.1,Org..Unit.ID.2,Org..Unit.ID.3,Org..Unit.ID.4,Org..Unit.ID.5,Org..Unit.ID.6), values_drop_na = T) |> select(-name) |> 
  mutate(Organizational_Unit_ID=value) |>  select(-value)

ou<-ou |> 
  ungroup() |> 
  filter(Organizational_Unit_ID!=" ") |> 
  filter(Organizational_Unit_ID!="") |> 
  group_by(Employee_External_Key) |> 
  mutate(rn=row_number()) |> 
  ungroup() |> 
  mutate(External_Key=paste0("ORG_",Employee_External_Key,"_",rn)) |> 
  select(-rn) |> 
  mutate(Employee_ID=username) |> select(-username)

```


## Output

```{r}
openxlsx::write.xlsx(ou,"./employees/output/org.xlsx")
openxlsx::write.xlsx(ur,"./employees/output/user.xlsx")
openxlsx::write.xlsx(sr,"./employees/output/sale.xlsx")
openxlsx::write.xlsx(emp,"./employees/output/emp.xlsx")

emp<-read.csv("emp.csv")
empn<-read.csv("emp_new.csv")
empn<-
  empn |> 
  mutate(Name=paste(First_Name, Last_Name)) |> 
  select(Employee_ID,Name) |> 
  rename(Employee.ID=Employee_ID) |> 
  unique()

```


```{r}
emp |> 
  select(Employee.ID, Name) |> 
  unique() |> 
  rbind(empn) |> 
  unique() |> 
  write.csv("emp.csv",row.names = FALSE)
```