---
title: "Employee Cleanup"
author: "Scary Scarecrow"
date: "5/18/2022"
output: html_document
---

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


```

# Duplicates

```{r}
oldfilepath <-
  list.files("./contacts/raw-data/",
             pattern = "*.xls",
             full.names = T)
contacts<-NULL
for (i in seq_along(oldfilepath)) {
  a<- read_excel(path = oldfilepath[[i]], sheet = 1, col_types="text")
  contacts[[i]]<-a
}

names(contacts) <- gsub("./contacts/raw-data/", "", oldfilepath)

contacts<-data.table::rbindlist(contacts, fill=TRUE, idcol=TRUE) |> 
  select(`.id`,`Full Name (Owning User)`) |> 
  rename(CountryRegion=`.id`) |> 
  mutate(CountryRegion=gsub("/", "", CountryRegion)) |> 
  mutate(CountryRegion=gsub(".xlsx", "", CountryRegion)) |> 
  rename(value=`Full Name (Owning User)`)

oldfilepath <-
  list.files("./accounts/raw-data/",
             pattern = "*.xls",
             full.names = T)
accounts<-NULL

for (i in seq_along(oldfilepath)) {
  a<- read_excel(path = oldfilepath[[i]], sheet = 1,col_types="text")
  accounts[[i]]<-a
}

names(accounts) <- gsub("./accounts/raw-data/", "", oldfilepath)

accounts<-data.table::rbindlist(accounts, fill=TRUE, idcol=TRUE) |> 
  select(`.id`,Owner, `Sales rep`, `Internal Sales User`, `Responsible technician`) |> 
  rename(CountryRegion=`.id`) |> 
  mutate(CountryRegion=gsub("/", "", CountryRegion)) |> 
  mutate(CountryRegion=gsub(".xlsx", "", CountryRegion)) |> 
  pivot_longer(cols = 2:5) |> 
  select(CountryRegion, value)

employeecodes<-read.csv("./employees/empoct.csv") |> 
  mutate(Name=paste(First_Name, Last_Name), OoltaName=paste(Last_Name, First_Name)) |> 
  select(Employee_ID,Name, OoltaName)


pending<-read.csv("./employees/pending.csv") |> 
  select(1,2,4) |> 
  mutate(Name=paste(First.Name,Last.name), OoltaName=paste(Last.name,First.Name)) |> 
  select(3:5)

rbind(contacts,accounts) |> 
  filter(!is.na(value)) |> 
  unique() |> 
  left_join(employeecodes, by=c("value"="Name")) |> 
  rename(Name=value) |> 
  select(-OoltaName) |> 
  left_join(employeecodes, by=c("Name"="OoltaName")) |> 
  rename(Reverse.Name=Name.y) |> 
  mutate(Check=ifelse(is.na(Employee_ID.x) & is.na(Employee_ID.y),"Not Found",
                      ifelse(!is.na(Employee_ID.x) & !is.na(Employee_ID.y), "Duplicate?",
                             ifelse(is.na(Employee_ID.x),Employee_ID.y, Employee_ID.x)))) |> 
  filter(Check=="Not Found" | Check=="Duplicate?") |> 
  left_join(pending, by=c("Name")) |> 
  mutate(Check=ifelse(!is.na(User.Name), "Pending Creation in SAP",Check)) |> 
  select(-c(User.Name,OoltaName)) |> pull(CountryRegion) |> 
  write.csv("./employees/empmissinginsap.csv",row.names = F)
```

```{r}
employeecodes |> 
  filter(Name=="Moucek Ivan")
```