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.
471 lines
14 KiB
471 lines
14 KiB
---
|
|
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")
|
|
|
|
```
|
|
|
|
|
|
|