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

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

## R Markdown


```{r cars}
dat<-read.csv("./IBAU/source/1.csv", sep=";")
dat2<-read.csv("./IBAU/source/2.csv", sep=";")
colnames(dat)
dat<-rbind(dat,dat2) |> unique()
```

```{r}
head(dat)
```


```{r}
External_Key<-paste0("IBAU_",dat$intref)
Document_Type<- "OPPT" # From Codelist "Opportunity"
Opportunity_ID<- NA
External_ID<- NA
Account<- "1000000" #Dummy from old template, approved by Axel and Dariusz
Name<-dat$ptname
Primary_Contact<-NA
Priority<-"3" # From codelist "normal"
Source<- "004" # From codelist"External Database", because it's IBAU
Status<- ifelse(dat$stadium=="Vorplanung", "1", ifelse(dat$stadium=="Planung","2", 
                                                       ifelse(dat$stadium=="kurz vor Baubeginn", "2","2"))) # From codelist 1= open, 2 = in process
Custom_Status<-"ZINPR" # From old template. Check
Reason_for_Status<-NA
Sales_Cycle<-NA
Sales_Phase<-"001" # From codelist "Information Phase", check with Dariusz
Status_Since<-NA
Sales_Phase_Start_Date<-NA
Probability<-NA
Header_Revenue_Schedule_Indicator<-NA
Relevant_for_Forecast<-"FALSE" # External, so not relevant. Check with dariusz
Expected_Value<- as.numeric(gsub(" €","",dat$kosten))
Currency<-"EUR"
Start_Date<-NA  # Check with Dariusz about `bau_beg` but it is text and we need date
Close_Date<-lubridate::dmy(dat$info1)  #check with Dariusz
Revenue_Start_Date<-NA
Revenue_End_Date<-NA
Forecast_Category<-NA
Category<-NA
Sales_Unit<-"DE01" #Default, check
Sales_Organization<-"DE01" #Default, check
Distribution_Channel<-"10" # From codelist "Trade"
Division<- "01"  # From Codelist "Leviat Division" 
Sales_Office<- NA 
Sales_Group<- NA
Territory_ID<- NA #Will try to map from pincode
Owner<- "90040" # Default Alexander Holthausen as instructed by Dariusz
End_Buyer_Party <- NA
Product_Recipient_Party <- NA
Approver_Party <-NA
Payer_Party <-NA
Billto_Party <-NA
Seller_Party<-NA
Progress<-NA
Budget_Prospect<-NA
Sales_Unit_Party_External_Key<-NA
Bill_To_Party_External_Key<-NA
Product_Recepient_Party_External_Key<-NA
Seller_Party_External_Key<-NA
Sales_Group_External_Key<-NA
End_Buyer_Party_External_Key<-NA
Primary_Contact_Party_External_Key<-NA
Sales_Office_External_Key<-NA
Approver_Party_External_Key<-NA
Payer_Party_External_Key<-NA
Main_Employee_Responsible_Party_External_Key<-NA
Sales_Organisation_External_Key<-NA
Prospect_Party_External_Key<-NA
External_Probability<-NA
Classification<- "141" # from old template, check
Expected_order_date<-NA
Frame_type<-NA
International_project<- "FALSE" # Check
Former_CRM_reference <- dat$intref
LEVIAT_specified <- "101" # Non Leviat, Check
Close_date <- NA
Start_date <- NA
Opportunity_type <- "101"  #From codelist, "Project", Check
Parent_opportunity <- NA
Product_pillar <- NA
Project_ID <- NA
Project_Country <- "DE" #Check
Project_Description <- paste(dat$det, dat$tech_det, sep = ". tech det _")
Project_Postal_code <- as.character(dat$plz)
Project_street <- dat$strasse
Project_type_I <- NA #Check, perhaps push to notes?
Project_type_II <- NA
Project_City <- dat$ort
Submission_date <-Start_Date
BIM_designed <- "131" # From codelist, "Software Unknown" Check

df<-data.frame(External_Key,
Document_Type,
Opportunity_ID,
External_ID,
Account,
Name,
Primary_Contact,
Priority,
Source,
Status,
Custom_Status,
Reason_for_Status,
Sales_Cycle,
Sales_Phase,
Status_Since,
Sales_Phase_Start_Date,
Probability,
Header_Revenue_Schedule_Indicator,
Relevant_for_Forecast,
Expected_Value,
Currency,
Start_Date,
Close_Date,
Revenue_Start_Date,
Revenue_End_Date,
Forecast_Category,
Category,
Sales_Unit,
Sales_Organization,
Distribution_Channel,
Division,
Sales_Office,
Sales_Group,
Territory_ID,
Owner,
End_Buyer_Party,
Product_Recipient_Party,
Approver_Party,
Payer_Party,
Billto_Party,
Seller_Party,
Progress,
Budget_Prospect,
Sales_Unit_Party_External_Key,
Bill_To_Party_External_Key,
Product_Recepient_Party_External_Key,
Seller_Party_External_Key,
Sales_Group_External_Key,
End_Buyer_Party_External_Key,
Primary_Contact_Party_External_Key,
Sales_Office_External_Key,
Approver_Party_External_Key,
Payer_Party_External_Key,
Main_Employee_Responsible_Party_External_Key,
Sales_Organisation_External_Key,
Prospect_Party_External_Key,
External_Probability,
Classification,
Expected_order_date,
Frame_type,
International_project,
Former_CRM_reference,
LEVIAT_specified,
Close_date,
Start_date,
Opportunity_type,
Parent_opportunity,
Product_pillar,
Project_ID,
Project_Country,
Project_Description,
Project_Postal_code,
Project_street,
Project_type_I,
Project_type_II,
Project_City,
Submission_date,
BIM_designed)

pro<-read.csv("./projects/output/combined/combinedopportunity.csv") |> 
  select(Project_Postal_code,Territory_ID, Project_City) |> 
  rename(tid=Territory_ID, pc=Project_City)
# Mapped Territory ID using PIN Code and city wherever possible
# Not possible because, in projects pincode and territory id combination is not unique
# df |> 
#   left_join(pro, by=c("Project_Postal_code")) |> 
#   unique() |> 
#   mutate(Territory_ID=tid) |> 
#   select(-c(tid, pc)) |> 
#   unique() |> 
#   left_join(pro, by=c("Project_City"="pc")) |> 
#   mutate(Territory_ID=ifelse(is.na(tid),Territory_ID,tid)) |> 
#   select(-tid,Project_Postal_code.y,pc) |>
#   rename(Project_Postal_code=Project_Postal_code.x)

nrow(df)  
nrow(dat)
write.csv(df,"./IBAU/opp.csv",row.names = FALSE,  na="")


```


## Opportunity_Preceding_and_Follo

```{r}

Opportunity_External_Key<-dat$intref
External_Key<- paste0("OPF_",Opportunity_External_Key)
Opportunity_ID<- NA
Reference_Doc_External_Key<- NA #Check
ID<- NA
Type_Code<- "Opportunity"
Role_Code<- "Predecessor"
Main<- NA
Item_ID<- NA
Item_Type_Code<- NA
Sales_Cycle_Code<- NA
Sales_Cycle_Phase_Code<- NA
Sales_Cycle_Phase_Step_Code<- NA
Business_Transaction_Document_Reference_Business_System_ID<- NA
```


## Opp party info

Relationship

```{r}
rel<-read.csv("./IBAU/rel.csv")
roles<-unique(rel$Import.Role)
rel<-rel |> 
  select(Account,Import.Account.Name, Account.Number..Account., ) |> 
  unique()
rel |> 
  filter(Account=="Bonava Deutschland GmbH")
```

```{r}
rel<-rel |> 
  group_by(Account) |> 
  slice(1)
```



Preparing the accounts

```{r}
df<-
  dat |> 
  select(intref,bauherr.name,
         bauträger.name,
         zuständige.behörde.name,
         planung.name,
         generalunternehmer.name,
         ausschreibung.name,
         projektsteuerung.name,
         bauleitung.name,
         statik.name,
         fachplanung.heizung.name,
         fachplanung.klima.name,
         fachplanung.lüftung.name,
         fachplanung.elektro.name,
         rohbauunternehmer.name,
         sonstige)
df<-
  df |> 
  left_join(rel, by=c("bauherr.name"="Account")) |> 
  rename(bauherr.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |> 
  unique() |> 
  left_join(rel, by=c("bauträger.name"="Account")) |> 
  rename(bauträger.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |> 
  unique() |> 
  left_join(rel, by=c("zuständige.behörde.name"="Account")) |> 
  rename(zuständige.behörde.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |> 
  unique() |> 
  left_join(rel, by=c("planung.name"="Account")) |> 
  rename(planung.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("generalunternehmer.name"="Account")) |> 
  rename(generalunternehmer.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("ausschreibung.name"="Account")) |> 
  rename(ausschreibung.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("projektsteuerung.name"="Account")) |> 
  rename(projektsteuerung.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("bauleitung.name"="Account")) |> 
  rename(bauleitung.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("statik.name"="Account")) |> 
  rename(statik.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("fachplanung.heizung.name"="Account")) |> 
  rename(fachplanung.heizung.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("fachplanung.klima.name"="Account")) |> 
  rename(fachplanung.klima.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("fachplanung.lüftung.name"="Account")) |> 
  rename(fachplanung.lüftung.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |>
  unique() |> 
  left_join(rel, by=c("fachplanung.elektro.name"="Account")) |> 
  rename(fachplanung.elektro.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |> 
  unique() |> 
  left_join(rel, by=c("rohbauunternehmer.name"="Account")) |> 
  rename(rohbauunternehmer.account=Account.Number..Account.) |> 
  select(-Import.Account.Name) |> 
  unique() |> 
  select(intref, bauherr.account, bauherr.name,
         bauträger.account, bauträger.name,
         zuständige.behörde.account, zuständige.behörde.name,
         planung.account, planung.name,
         generalunternehmer.account, generalunternehmer.name,
         ausschreibung.account, ausschreibung.name,
         projektsteuerung.account, projektsteuerung.name,
         bauleitung.account, bauleitung.name,
         statik.account, statik.name,
         fachplanung.heizung.account, fachplanung.heizung.name,
         fachplanung.klima.account, fachplanung.klima.name,
         fachplanung.lüftung.account, fachplanung.lüftung.name,
         fachplanung.elektro.account, fachplanung.elektro.name,
         rohbauunternehmer.account, rohbauunternehmer.name,
         sonstige) |> 
  unique()
df<-df |> 
  mutate(notes=paste("bauherrr_",ifelse(is.na(bauherr.account),bauherr.name,"_"),
                     "bauträger_",ifelse(is.na(bauträger.account),bauträger.name,"_"),
                     "zuständige_",ifelse(is.na(zuständige.behörde.account),zuständige.behörde.name,"_"),
                     "planung_",ifelse(is.na(planung.account),planung.name,"_"),
                     "generalunternehmer_",ifelse(is.na(generalunternehmer.account),generalunternehmer.name,"_"),
                     "ausschreibung_",ifelse(is.na(ausschreibung.account),ausschreibung.name,"_"),
                     "projektsteuerung_",ifelse(is.na(projektsteuerung.account),projektsteuerung.name,"_"),
                     "bauleitung_",ifelse(is.na(bauleitung.account),bauleitung.name,"_"),
                     "statik_",ifelse(is.na(statik.account),statik.name,"_"),
                     "statik_",ifelse(is.na(fachplanung.heizung.account),fachplanung.heizung.name,"_"),
                     "statik_",ifelse(is.na(fachplanung.klima.account),fachplanung.klima.name,"_"),
                     "statik_",ifelse(is.na(fachplanung.lüftung.account),fachplanung.lüftung.name,"_"),
                     "statik_",ifelse(is.na(fachplanung.elektro.account),fachplanung.elektro.name,"_"),
                     "statik_",ifelse(is.na(rohbauunternehmer.account),rohbauunternehmer.name,"_"),
                     "sonstige",sonstige
                     
                     )) 

df.err<-df |> 
  select(-notes)

a<-df.err[,2:3]
b<-df.err[,4:5]
c<-df.err[,6:7]
d<-df.err[,8:9]
e<-df.err[,10:11]
f<-df.err[,12:13]
g<-df.err[,14:15]
h<-df.err[,16:17]
i<-df.err[,18:19]
j<-df.err[,20:21]
k<-df.err[,22:23]
l<-df.err[,24:25]
m<-df.err[,26:27]
n<-df.err[,28:29]

colnames(a)<-c("account","name")
colnames(b)<-c("account","name")
colnames(c)<-c("account","name")
colnames(d)<-c("account","name")
colnames(e)<-c("account","name")
colnames(f)<-c("account","name")
colnames(g)<-c("account","name")
colnames(h)<-c("account","name")
colnames(i)<-c("account","name")
colnames(j)<-c("account","name")
colnames(k)<-c("account","name")
colnames(l)<-c("account","name")
colnames(m)<-c("account","name")
colnames(n)<-c("account","name")

rbind(a,b,c,d,e,f,g,i,j,k,l,m,n) |> 
  unique() |> 
  mutate(check=ifelse(is.na(name), "Not in data", ifelse(is.na(account),"Account not found","Found"))) |> 
  unique() |> 
  filter(check=="Account not found") |> 
  write.csv("./IBAU/error_accnotfoundinrel.csv", row.names = FALSE, na="")

opprel<-
  df |> 
  select(-c(notes,bauherr.name, bauträger.name, zuständige.behörde.name,
            planung.name, generalunternehmer.name, ausschreibung.name,
            projektsteuerung.name, bauleitung.name, statik.name,
            fachplanung.heizung.name, fachplanung.klima.name,
            fachplanung.lüftung.name, fachplanung.elektro.name,
            rohbauunternehmer.name, sonstige)) |> 
  pivot_longer(cols = 2:15) |> 
  mutate(name=sub(".account","",name)) |> 
  mutate(Role=case_when(
    grepl("bauherr",name) ~ "ZEX005",
    grepl("bauträger",name) ~ "ZEX004",
    grepl("zuständige.behörde",name) ~ "ZT",
    grepl("planung",name) ~ "ZEX019",
    grepl("generalunternehmer",name) ~ "ZEX013",
    grepl("ausschreibung",name) ~ "ZT",
    grepl("projektsteuerung",name) ~ "ZEX019",
    grepl("bauleitung",name) ~ "ZEX018",
    grepl("statik",name) ~ "ZEX008",
    grepl("rohbauunternehmer",name) ~ "ZEX015",
    TRUE ~ "ZEX014"
    
  )) |> 
  mutate(External_Key=paste0("IBAU_INV_",intref,"_",name,"_",value)) |> 
  mutate(Opportunity_External_Key=paste0("IBAU_",as.character(intref))) |> 
  filter(!is.na(value)) |> 
  mutate(value=as.character(value)) |> 
  mutate(value=paste0("H",value)) |> 
  left_join(acc, by=c("value"="Former_CRM_reference")) |> 
  rename(Party_External_Key=value)
  
opprel |> 
    select(Party_External_Key,Account_ID) |> 
    unique() |> 
    filter(is.na(Account_ID)) |> 
  write.csv("./IBAU/error_account_doesntexistinsap.csv",row.names = FALSE,na="")
  
  
opprel |> 
  filter(!is.na(Account_ID)) |>
  mutate(Opportunity_ID=NA, Party_ID=NA, Role_Category_Code=NA, Party_Type=NA, Main_Indicator=NA) |> 
  select(External_Key, Opportunity_External_Key, Opportunity_ID, Party_ID, Role_Category_Code, Role, Party_Type, Main_Indicator,Party_External_Key) |> 
  write.csv("./IBAU/opprel.csv",row.names = FALSE, na="")

```



Opportunity_Notes

```{r}
df
Opportunity_External_Key <- paste0("IBAU_",as.character(df$intref))
External_Key <- paste0("NOTE_",Opportunity_External_Key)
Opportunity_ID <- NA
Text <- df$notes
Type_Code <- "10001" #Check
Author_Name <- NA
data.frame(External_Key, Opportunity_External_Key, Opportunity_ID, Text, Type_Code, Author_Name) |> 
  write.csv("./IBAU/oppnotes.csv")

```