---
title: "EmployeesValidation"
author: "Scary Scarecrow"
date: "2022-10-17"
output: html_document
---

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

## Job ID Files

```{r}
jobid<-read_excel("./raw-data/Jobs.XLSX", sheet="str")

jobid
```

```{r}
str<-read_excel("./raw-data/org.XLSX", sheet="str")
str<-
  str |> 
  select(ID, Name)
str
```


## CN

```{r}
emp<-read_excel("./raw-data/CN.XLSX", sheet="emp")
str<-read_excel("./raw-data/CN.XLSX", sheet="str")
str <-
  str |> 
  select(ID, Name)
```


```{r}
emp |> 
  separate(`Job-Title`,c("Nm","JTitle","Geo"),sep="[|]") |> 
  separate(JTitle,c("JTitle","deptt"), sep="[,]") |> 
  mutate(org1=ifelse(
    grepl("Sales", `Org Unit ID 1`) & !is.na(deptt), gsub(" ","",paste0("CN_Sales_",deptt)),
    paste0("CN_",`Org Unit ID 1`)
  )) |> 
  mutate(org2=ifelse(
    is.na(`Org Unit ID 2`), NA,paste0("CN_",`Org Unit ID 2`)
  )) |> 
  left_join(str, by=c("org1"="Name")) |> 
  mutate(`Org Unit ID 1`=ifelse(is.na(ID),`Org Unit ID 1`,ID)) |> 
  select(-c(org1,ID)) |> 
  left_join(str, by=c("org2"="Name")) |> 
  mutate(`Org Unit ID 2`=ifelse(is.na(ID),`Org Unit ID 2`,ID)) |> 
  select(-c(org2,ID)) |> 
  left_join(jobid, by=c("Job-ID"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(`Job ID`), `Job-ID`, `Job ID`)) |> 
  select(-c(`Job ID`)) |> 
  write.csv("./op/CN.csv", row.names = FALSE, na="")
```


## CZ

```{r}

emp<-read_excel("./raw-data/CZ.XLSX", sheet="emp")

```


```{r}
emp |> 
  left_join(jobid, by=c("Job-ID"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(`Job ID`), `Job-ID`, `Job ID`)) |> 
  select(-c(`Job ID`)) |> 
  write.csv("./op/CZ.csv", row.names = FALSE, na="")
```

## DE


```{r}
#str<-read_excel("./raw-data/DE.XLSX", sheet="str")

# str<-str |> 
#   select(ID,Name)
# jobidn<-jobid |> 
#   select(1,2) |> 
#   rename(JID=`Job ID`)

emp<-read_excel("./raw-data/DE.XLSX", sheet="emp")

emp |> 
  left_join(jobid, by=c("Job.Title.Eng"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(`Job ID.y`), Job.Title.Eng, `Job ID.y`)) |> 
  select(-c(`Job ID.y`)) |> 
  left_join(str, by=c("Org. Unit-ID...20"="Name")) |> 
  mutate(`Org. Unit-ID...20`=ifelse(is.na(ID),`Org. Unit-ID...20`,ID)) |> 
  select(-ID) |> 
  left_join(str, by=c("Org. Unit-ID...21"="Name")) |> 
  mutate(`Org. Unit-ID...21`=ifelse(is.na(ID),`Org. Unit-ID...21`,ID)) |> 
  select(-ID) |> 
  left_join(str, by=c("Org. Unit-ID...22"="Name")) |> 
  mutate(`Org. Unit-ID...22`=ifelse(is.na(ID),`Org. Unit-ID...22`,ID)) |> 
  select(-ID) |> 
  left_join(str, by=c("Org. Unit-ID...23"="Name")) |> 
  mutate(`Org. Unit-ID...23`=ifelse(is.na(ID),`Org. Unit-ID...23`,ID)) |> 
  select(-ID) |> 
    left_join(str, by=c("Org. Unit-ID...24"="Name")) |> 
  mutate(`Org. Unit-ID...24`=ifelse(is.na(ID),`Org. Unit-ID...24`,ID)) |> 
  select(-ID) |> 
    left_join(str, by=c("Org. Unit-ID...25"="Name")) |> 
  mutate(`Org. Unit-ID...25`=ifelse(is.na(ID),`Org. Unit-ID...25`,ID)) |> 
  select(-ID) |> 
    left_join(str, by=c("Org. Unit-ID...26"="Name")) |> 
  mutate(`Org. Unit-ID...26`=ifelse(is.na(ID),`Org. Unit-ID...26`,ID)) |> 
  select(-ID) |> 
  left_join(str, by=c("Org. Unit-ID...27"="Name")) |> 
  mutate(`Org. Unit-ID...27`=ifelse(is.na(ID),`Org. Unit-ID...27`,ID)) |> 
  select(-ID) |> 
  left_join(str, by=c("Org. Unit-ID...28"="Name")) |> 
  mutate(`Org. Unit-ID...28`=ifelse(is.na(ID),`Org. Unit-ID...28`,ID)) |> 
  select(-ID) |> 
  write.csv("./op/DE.csv", row.names = FALSE, na="")
str
```


## ES

```{r}
emp<-read_excel("./raw-data/ES.XLSX", sheet="emp")
emp |> 
  left_join(jobid, by=c("Job-ID"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(`Job ID`), `Job-ID`, `Job ID`)) |> 
  select(-c(`Job ID`)) |> 
  mutate(`Org Unit ID`=ifelse(is.na(`Org Unit ID`),NA,paste0("ES_",`Org Unit ID`))) |> 
  left_join(str, by=c("Org Unit ID"="Name")) |> 
  mutate(`Org Unit ID`=ifelse(is.na(ID),`Org Unit ID`,ID)) |> 
  select(-ID) |> 
  write.csv("./op/ES.csv", row.names = FALSE, na="")

```




## IT

```{r}

emp<-read_excel("./raw-data/IT.XLSX", sheet="emp")

emp |> 
  left_join(jobid, by=c("Job-ID"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(`Job ID`), `Job-ID`, `Job ID`)) |> 
  select(-c(`Job ID`)) |> 
  mutate(`Org Unit ID`=ifelse(is.na(`Org Unit ID`),NA,paste0("IT_", `Org Unit ID`))) |> 
  left_join(str, by=c("Org Unit ID"="Name")) |> 
  mutate(`Org Unit ID`=ifelse(is.na(ID),`Org Unit ID`,ID)) |> 
  select(-ID) |> 
  write.csv("./op/IT.csv", row.names = FALSE, na="")
  

```



## NL


```{r}

emp<-read_excel("./raw-data/NL.XLSX", sheet="emp")

emp |> 
  left_join(jobid, by=c("Job-ID"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(`Job ID`), `Job-ID`, `Job ID`)) |> 
  select(-c(`Job ID`)) |> 
  write.csv("./op/NL.csv", row.names = FALSE, na="")

```


## PL

```{r}

emp<-read_excel("./raw-data/PL.XLSX", sheet="emp")

emp |> 
  left_join(jobid, by=c("Job-ID"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(`Job ID`), `Job-ID`, `Job ID`)) |> 
  select(-c(`Job ID`)) |> 
  mutate(`Org Unit ID`=ifelse(is.na(`Org Unit ID`),NA,paste0("PL_",`Org Unit ID`))) |> 
  left_join(str, by=c("Org Unit ID"="Name")) |> 
  mutate(`Org Unit ID`=ifelse(is.na(ID),`Org Unit ID`,ID)) |> 
  select(-ID) |>  
  write.csv("./op/PL.csv", row.names = FALSE, na="")

```



## SEFI


```{r}
str<-read_excel("./raw-data/SEFI.XLSX", sheet="str")
emp<-read_excel("./raw-data/SEFI.XLSX", sheet="emp")


emp |> 
  left_join(jobid, by=c("Job-ID"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(`Job ID`), `Job-ID`, `Job ID`)) |> 
  select(-c(`Job ID`)) |> 
  left_join(str, by=c("Org Unit ID 1"="Name")) |> 
  mutate(`Org Unit ID 1`=ifelse(is.na(ID),`Org Unit ID 1`,ID)) |> 
  select(-ID) |> 
  left_join(str, by=c("Org Unit ID 2"="Name")) |> 
  mutate(`Org Unit ID 2`=ifelse(is.na(ID),`Org Unit ID 2`,ID)) |> 
  select(-ID) |>  
  write.csv("./op/SEFI.csv", row.names = FALSE, na="")
```


## AUS


```{r}
str<-read_excel("./raw-data/AUS.XLSX", sheet="str")
emp<-read_excel("./raw-data/AUS.XLSX", sheet="emp")


emp |> 
  separate(`Job-Title`,c("Job","Geo"), sep="[|]") |> 
  separate(Job, c("Job","Product"),sep="[,]") |> 
  mutate(Job=str_trim(Job)) |>
  left_join(jobidn, by=c("Job"="Job Name")) |> 
  mutate(`Job-ID`=ifelse(is.na(JID), `Job-ID`, JID)) |> 
  rename(`New Job ID`=JID) |> 
  left_join(str, by=c("Org Unit ID 1"="Name")) |> 
  mutate(`Org Unit ID 1`=ifelse(is.na(ID),`Org Unit ID 1`,ID)) |> 
  select(-ID) |> 
  left_join(str, by=c("Org Unit ID 2"="Name")) |> 
  mutate(`Org Unit ID 2`=ifelse(is.na(ID),`Org Unit ID 2`,ID)) |> 
  select(-ID) |>
  left_join(str, by=c("Org Unit ID 3"="Name")) |> 
  mutate(`Org Unit ID 3`=ifelse(is.na(ID),`Org Unit ID 3`,ID)) |> 
  select(-ID) |> 
  left_join(str, by=c("Org Unit ID 4"="Name")) |> 
  mutate(`Org Unit ID 4`=ifelse(is.na(ID),`Org Unit ID 4`,ID)) |> 
  select(-ID) |> 
  write.csv("./op/AUSNZ.csv", row.names = FALSE, na="")

```


## DE

```{r}
de<-read.csv("./op/DE.csv")
all<-read.csv("all_users.csv")
all<-all |> 
  select(City, Email.Address,Username..pre.2000.) |> 
  rename(city=City, email=Email.Address, uname=Username..pre.2000.)
de<-
  de |> 
  select(S.4.Employee.ID, First.Name, Last.Name, Email.Address, Telephone.Number, Street, ZIP.Postal.Code,
         Job.ID, Business.Role.ID, Org..Unit.ID...20, Org..Unit.ID...21, Org..Unit.ID...22, Org..Unit.ID...23,
         Org..Unit.ID...24, Org..Unit.ID...25, Org..Unit.ID...26, Org..Unit.ID...27, Org..Unit.ID...28) |> 
  rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.Name,
         email=Email.Address, phone=Telephone.Number, street=Street, zip=ZIP.Postal.Code,
         jid=Job.ID, bid=Business.Role.ID, org1=Org..Unit.ID...20, org2=Org..Unit.ID...21,
         org3=Org..Unit.ID...22, org4=Org..Unit.ID...23, org5=Org..Unit.ID...24,
         org6=Org..Unit.ID...25, org7=Org..Unit.ID...26, org8=Org..Unit.ID...27, org9=Org..Unit.ID...28) |> 
  left_join(all, by=c("email")) |> 
  mutate(country="DE")

cnames<-colnames(de)
de
```


```{r}
aus<-read.csv("./op/AUSNZ.csv")
aus<-aus |> 
  select(S.4.Employee.ID, First.Name, Last.name, User.Name, E.mail, Work.Phone, Street, Postal.Code, Job.ID,
         Business.Role.ID, Org.Unit.ID.1, Org.Unit.ID.2, Org.Unit.ID.3, Org.Unit.ID.4) |> 
  rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.name, email=E.mail, phone=Work.Phone,
         street=Street, zip=Postal.Code, jid=Job.ID, bid=Business.Role.ID, org1=Org.Unit.ID.1,
         org2=Org.Unit.ID.2, org3=Org.Unit.ID.3, org4=Org.Unit.ID.4) |> 
  mutate(org5=NA,org6=NA, org7=NA, org8=NA, org9=NA, country="AU") |> 
  left_join(all, by=c("email")) |> 
  select(-uname) |> 
  rename(uname=User.Name) |> 
  select(all_of(cnames))


```


```{r}
cn<-read.csv("./op/CN.csv")

cn<-cn |> 
  select(S.4.Employee.ID, First.Name, Last.name, User.Name, E.mail, Work.Phone, Street, Postal.Code, Business.Role.ID,
         Job.ID, Org.Unit.ID.1, Org.Unit.ID.2) |> 
  rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.name, email=E.mail, phone=Work.Phone,
         street=Street, zip=Postal.Code, jid=Job.ID, bid=Business.Role.ID, org1=Org.Unit.ID.1,
         org2=Org.Unit.ID.2) |> 
  mutate(org3=NA, org4=NA,org5=NA,org6=NA, org7=NA, org8=NA, org9=NA, country="CN") |> 
  left_join(all, by=c("email")) |> 
  select(-uname) |> 
  rename(uname=User.Name) |> 
  select(all_of(cnames))
```


```{r}
cz<-read.csv("./op/CZ.csv")

cz<-cz |> 
  select(S.4.Employee.ID, First.Name, Last.Name, User.Name, E.mail, Work.Phone, Street, Postal.Code, Business.Role.ID,
         Job.ID, Org.Unit.ID) |> 
  rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.Name, email=E.mail, phone=Work.Phone,
         street=Street, zip=Postal.Code, jid=Job.ID, bid=Business.Role.ID, org1=Org.Unit.ID) |> 
  mutate(org2=NA,org3=NA, org4=NA,org5=NA,org6=NA, org7=NA, org8=NA, org9=NA, country="CZ") |> 
  left_join(all, by=c("email")) |> 
  select(-uname) |> 
  rename(uname=User.Name) |> 
  select(all_of(cnames))
```



```{r}
es<-read.csv("./op/ES.csv")

es<-es |> 
  select(S.4.Employee.ID, First.Name, Last.Name, User.Name, E.mail, Work.Phone, Street, Postal.Code, Business.Role.ID,
         Job.ID, Org.Unit.ID) |> 
    rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.Name, email=E.mail, phone=Work.Phone,
         street=Street, zip=Postal.Code, jid=Job.ID, bid=Business.Role.ID, org1=Org.Unit.ID) |> 
  mutate(org2=NA,org3=NA, org4=NA,org5=NA,org6=NA, org7=NA, org8=NA, org9=NA, country="ES") |> 
    left_join(all, by=c("email")) |> 
  select(-uname) |> 
  rename(uname=User.Name) |> 
  select(all_of(cnames))
```



```{r}
it<-read.csv("./op/IT.csv")

it<-it |> 
    select(S.4.Employee.ID, First.Name, Last.Name, User.Name, E.mail, Work.Phone, Street, Postal.Code, Business.Role.ID,
         Job.ID, Org.Unit.ID) |> 
    rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.Name, email=E.mail, phone=Work.Phone,
         street=Street, zip=Postal.Code, jid=Job.ID, bid=Business.Role.ID, org1=Org.Unit.ID) |> 
    mutate(org2=NA,org3=NA, org4=NA,org5=NA,org6=NA, org7=NA, org8=NA, org9=NA, country="IT") |> 
    left_join(all, by=c("email")) |> 
  select(-uname) |> 
  rename(uname=User.Name) |> 
  select(all_of(cnames))
```



```{r}
nl<-read.csv("./op/NL.csv")

nl<-nl |> 
  select(S.4.Employee.ID, First.Name, Last.Name, User.Name, E.mail, Work.Phone, Street, Postal.Code, Busines.Role.ID,
         Job.ID, Org.Unit.ID...14, Org.Unit.ID...15, Org.Unit.ID...16) |> 
    rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.Name, email=E.mail, phone=Work.Phone,
         street=Street, zip=Postal.Code, jid=Job.ID, bid=Busines.Role.ID, org1=Org.Unit.ID...14, 
         org2=Org.Unit.ID...15, org3=Org.Unit.ID...16) |> 
    mutate(org4=NA,org5=NA,org6=NA, org7=NA, org8=NA, org9=NA, country="NL") |> 
    left_join(all, by=c("email")) |> 
  select(-uname) |> 
  rename(uname=User.Name) |> 
  select(all_of(cnames))
```



```{r}
pl<-read.csv("./op/PL.csv")
pl<-
  pl |> 
  select(S.4.Employee.ID, First.Name, Last.Name, User.Name, E.mail, Work.Phone, Street, Postal.Code, Business.Role.ID,
         Job.ID, Org.Unit.ID) |> 
    rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.Name, email=E.mail, phone=Work.Phone,
         street=Street, zip=Postal.Code, jid=Job.ID, bid=Business.Role.ID, org1=Org.Unit.ID) |> 
    mutate(org2=NA,org3=NA, org4=NA,org5=NA,org6=NA, org7=NA, org8=NA, org9=NA, country="PL") |> 
    left_join(all, by=c("email")) |> 
  select(-uname) |> 
  rename(uname=User.Name) |> 
  select(all_of(cnames))
```


```{r}
se<-read.csv("./op/SEFI.csv")

se<-
  se |> 
  select(S.4.Employee.ID, First.Name, Last.Name, User.Name, E.mail, Work.Phone, Street, Postal.Code, Business.Role.ID,
         Job.ID, Org.Unit.ID.1, Org.Unit.ID.2, Org.Unit.ID.3) |> 
    rename(eid=S.4.Employee.ID, fname=First.Name, lname=Last.Name, email=E.mail, phone=Work.Phone,
         street=Street, zip=Postal.Code, jid=Job.ID, bid=Business.Role.ID, org1=Org.Unit.ID.1, 
         org2=Org.Unit.ID.2, org3=Org.Unit.ID.3) |> 
    mutate(org4=NA,org5=NA,org6=NA, org7=NA, org8=NA, org9=NA, country="SE") |> 
    left_join(all, by=c("email")) |> 
  select(-uname) |> 
  rename(uname=User.Name) |> 
  select(all_of(cnames))
```


```{r}
df<-rbind(de,aus,cn,cz,es,it,nl,pl,se)
df.nobid<-df |> 
  filter(bid=="")
df<-df |> 
  filter(bid!="")
df<-df |> 
  mutate(uname=ifelse(uname=="" | is.na(uname), paste0(lname, str_sub(fname,1,2)), uname))
```

Emp

```{r}
Employee_ID<-df$eid
User_ID<-df$uname
CountryRegion<-df$country
Street<-df$street
City<-df$city
Postal_Code<-df$zip
Phone<-df$phone
EMail<-df$email
User_Password_Policy<-"S_BUSINESS_USER_WITHOUT_PASSWORD"
data.frame(Employee_ID, User_ID, CountryRegion, Street, City, Postal_Code, Phone, EMail, User_Password_Policy) |> 
  filter(!is.na(Employee_ID)) |> 
  unique() |> 
  mutate(City=ifelse(Street=="Calle Ignacio Zuloaga 20", "Rivas-Vaciamadrid", City)) |> 
  write.csv("emp.csv",na="", row.names = FALSE)
data.frame(Employee_ID, User_ID, CountryRegion, Street, City, Postal_Code, Phone, EMail, User_Password_Policy) |> 
  filter(is.na(Employee_ID)) |> 
  unique() |>
  write.csv("empwithoutid.csv",  row.names = F,
  na = "",
  fileEncoding = "UTF-8",
 sep = ",",
 quote = FALSE)
```

EmpOrg

```{r}
df |> 
  select(eid,jid, org1:org9) |> 
  pivot_longer(org1:org9, "org") |> 
  mutate(exkey=paste0("ORG_EMP_",eid,"_",gsub("org","",org))) |> 
  filter(value!="" | is.na(value)) |> 
  mutate(Employee_External_Key=NA, Organizational_Unit_External_Key=NA, Role="219", Valid_From=NA, Valid_To=NA) |> 
  rename(External_Key=exkey, Job_ID=jid, Organizational_Unit_ID=value,Employee_ID=eid) |> 
  select(External_Key, Employee_External_Key, Employee_ID, Organizational_Unit_ID, Organizational_Unit_External_Key,
         Role, Valid_From, Valid_To, Job_ID) |> 
  unique() |> 
  filter(!is.na(Organizational_Unit_ID) | Organizational_Unit_ID!="") |> 
  filter(!is.na(Employee_ID) | Employee_ID=="") |> 
  mutate(Organizational_Unit_ID=ifelse(grepl("ES_",Organizational_Unit_ID),sub("ES_","",Organizational_Unit_ID),Organizational_Unit_ID)) |> 
  mutate(Role=ifelse(str_ends(External_Key,"_1"), "219", "222")) |> 
  write.csv("emporg.csv",  row.names = F,
  na = "",
  fileEncoding = "UTF-8",
 sep = ",",
 quote = FALSE)
  
```

EmpBusi

```{r}
df |> 
  select(eid, uname, bid) |> 
  rename(Employee_ID=eid, User_ID=uname, Business_Role_ID=bid) |> 
  mutate(Employee_External_Key=NA) |> 
  mutate(External_Key=paste0("ROLE_EMP_",Employee_ID)) |> 
  select(External_Key, External_Key, Employee_ID, User_ID, Business_Role_ID) |> 
  unique() |> 
  filter(!is.na(Employee_ID) | Employee_ID=="") |> 
  write.csv("empbusi.csv",  row.names = F,
  na = "",
  fileEncoding = "UTF-8",
 sep = ",",
 quote = FALSE)
```


EmpSal

```{r}
df |> 
  select(eid, country) |> 
  mutate(External_Key=paste0("SLS_EMP_",eid), Employee_External_Key=NA, Sales_Organization_ID=paste0(country,"01"),
         Distribution_Channel="10",Division="1",Main="TRUE") |> 
  rename(Employee_ID=eid) |> 
  select(External_Key, Employee_External_Key, Employee_ID, Sales_Organization_ID, Distribution_Channel, Division, Main) |> 
  unique() |> 
  filter(!is.na(Employee_ID) | Employee_ID=="") |> 
  mutate(Sales_Organization_ID=ifelse(Employee_ID %in% c("438","440","442"), "FI01", Sales_Organization_ID)) |> 
  write.csv("empsal.csv",  row.names = F,
  na = "",
  fileEncoding = "UTF-8",
 sep = ",",
 quote = FALSE)
```