You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
166 lines
4.0 KiB
166 lines
4.0 KiB
---
|
|
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)
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|