---
title: "Report"
author: "Data Science Team, LaNubia"
date: '`r format(Sys.time(), "%d %B, %Y")`'
output:
  html_document:
    theme: lumen
    highlight: tango
    self_contained: true
    toc: true
    toc_depth: 4
    toc_float: true
    css: style.css
    includes:
       in_header: plausible.html

---

```{r setup, include=FALSE}
knitr::opts_chunk$set(
  echo = FALSE,
  error = TRUE,
  message = FALSE,
  warning = FALSE
)
library(readxl)
library(DT)
library(tidyr)
library(dplyr)
library(highcharter)
library(purrr)
library(stringr)

rxl <- function(path, ...) {
  tryCatch(
    read_excel(path, ...),
    error = function(c) {
      c$message <- "No Data"
      print("No Data")
      stop(c)
    }
  )
}

ltodf <- function(path, ...) {
  tryCatch(
    rbind.data.frame(path, ...),
    error = function(c) {
      c$message <- "No Data"
      print("No Data")
      stop(c)
    }
  )
}

```

**Non commercial license of Highchart library used**


## Status Report


### Input Available



```{r input available, echo=FALSE, message=FALSE, warning=FALSE}
contactinputpath <-
  list.files("./contacts/raw-data",
             pattern = "*.xlsx",
             full.names = T)
accountinputpath <-
  list.files("./accounts/raw-data",
             pattern = "*.xlsx",
             full.names = T)
projectinputpath <-
  list.files("./projects/raw-data",
             pattern = "*.xlsx",
             full.names = T)
supportinputpath <-
  list.files("./support/raw-data",
             pattern = "*.xls",
             full.names = T)


conta <- lapply(contactinputpath, read_excel)
names(conta) <- gsub("./contacts/raw-data/", "", contactinputpath)
c <- lapply(conta, nrow)

Input_data <- "Contact"
#Country<-gsub(".xlsx","",names(conta))
Observations <- c

temp <- data.frame(Input_data, Observations) |>
  pivot_longer(cols = (-1),
               names_to = "Country",
               values_to = "Observations") |>
  mutate(Country = gsub(".xlsx", "", Country))

input.summary <- temp

acco <- lapply(accountinputpath, read_excel)
names(acco) <- gsub("./accounts/raw-data/", "", accountinputpath)
a <- lapply(acco, nrow)

Input_data <- "Accounts"
#Country<-gsub(".xlsx","",names(conta))
Observations <- a

temp <- data.frame(Input_data, Observations) |>
  pivot_longer(cols = (-1),
               names_to = "Country",
               values_to = "Observations") |>
  mutate(Country = gsub(".xlsx", "", Country))

input.summary <- rbind(input.summary, temp)

proja <- lapply(projectinputpath, read_excel)
names(proja) <- gsub("./projects/raw-data/", "", projectinputpath)
p <- lapply(proja, nrow)

Input_data <- "Projects"
#Country<-gsub(".xlsx","",names(conta))
Observations <- p

temp <- data.frame(Input_data, Observations) |>
  pivot_longer(cols = (-1),
               names_to = "Country",
               values_to = "Observations") |>
  mutate(Country = gsub(".xlsx", "", Country))

input.summary <- rbind(input.summary, temp)

suppo <- lapply(supportinputpath, read_excel)
names(suppo) <- gsub("./support/raw-data/", "", supportinputpath)
s <- lapply(suppo, nrow)

Input_data <- "Support"
#Country<-gsub(".xlsx","",names(conta))
Observations <- s

temp <- data.frame(Input_data, Observations) |>
  pivot_longer(cols = (-1),
               names_to = "Country",
               values_to = "Observations") |>
  mutate(Country = gsub(".xls", "", Country))

input.summary <- rbind(input.summary, temp)

# datatable(input.summary, extensions = "Buttons",
#             options = list(paging = TRUE,
#                            scrollX=TRUE,
#                            searching = TRUE,
#                            ordering = TRUE,
#                            dom = 'Bfrtip',
#                            buttons = c('copy', 'csv', 'excel', 'pdf'),
#                            pageLength=10,
#                            lengthMenu=c(3,5,10) ))

input.summary <- input.summary |> arrange(desc(Observations))

input.summary.summ <- input.summary |>
  group_by(Input_data) |>
  summarise(Observations = sum(Observations)) |>
  arrange(desc(Observations))

input.summary.drilldown <- input.summary |>
  group_nest(Input_data) |>
  mutate(
    id = Input_data,
    type = "column",
    data = map(data, mutate, name = Country, y = Observations),
    data = map(data, list_parse)
  )
tt <-
  tooltip_table(c("No. of Observations(Input)"), c("{point.Observations}"))
hchart(
  input.summary.summ,
  "column",
  hcaes(
    x = Input_data,
    y = Observations,
    name = Input_data,
    drilldown = Input_data
  ),
  name = "Segment view",
  colorByPoint = TRUE
) |>
  hc_drilldown(allowPointDrilldown = TRUE,
               series = list_parse(input.summary.drilldown)) |>
  hc_tooltip(pointFormat = tt,
             # "{point.name} {point.pop}"
             useHTML = TRUE,
             valueDecimals = 0) |>
  hc_yAxis(title = list(text = "Number of observations")) |>
  hc_xAxis(title = "Segment") |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "Input Summary") |>
  hc_title(text = "Observations by segment") |>
  hc_subtitle(text = "Click on the bar to view details by country")

```




Simplified view



```{r simplified view, echo=FALSE}
input.summary |>
  pivot_wider(names_from = Country, values_from = Observations) |> datatable(
    extensions = "Buttons",
    options = list(
      paging = TRUE,
      scrollX = TRUE,
      searching = TRUE,
      ordering = TRUE,
      dom = 'Bfrtip',
      buttons = c('copy', 'csv', 'excel', 'pdf'),
      pageLength = 10,
      lengthMenu = c(3, 5, 10)
    )
  )
```



### Contacts



#### Template



SAP templates available:



```{r contacts_template, echo=FALSE}
datatable(
  data.frame(Templates = unique(
    rxl("./contacts/template.xlsx", sheet = "Field_Definitions")[, 1]
  )),
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10)
  )
)
```




#### Summary of Errors



```{r contacts_soe, echo=FALSE, message=FALSE, warning=FALSE}
sumerrfilepath <-
  list.files("./contacts/summary",
             pattern = "*sumerror.csv",
             full.names = T)
errfilepath <-
  list.files("./contacts/summary",
             pattern = "*_error.csv",
             full.names = T)

sumerrfiles <- lapply(sumerrfilepath, read.csv)
# datatable(unique(do.call(ltodf, sumerrfiles)), extensions = "Buttons",
#             options = list(paging = TRUE,
#                            scrollX=TRUE,
#                            searching = TRUE,
#                            ordering = TRUE,
#                            dom = 'Bfrtip',
#                            buttons = c('copy', 'csv', 'excel', 'pdf'),
#                            pageLength=10,
#                            lengthMenu=c(3,5,10) ))

tdf <- unique(do.call(ltodf, sumerrfiles)) |> arrange(desc(Expected))
tdf <-
  tdf |> mutate(Pending = Expected - Actual) |> select(-Name) |> unique()

tdf |>
  hchart(
    "bullet",
    hcaes(x = Country, y = Actual, target = Expected),
    color = "black",
    name = "Observations (Output)"
  ) |>
  hc_chart(inverted = TRUE) |>
  hc_add_series(tdf, "pie", hcaes(x = Country, y = Pending), name = "Pending (Major Issues)") |>
  hc_plotOptions(pie = list(
    center = c('70%', '70%'),
    size = 200,
    dataLabels = list(enabled = FALSE),
    showInLegend = T
  )) |>
  hc_title(text = "Expect vs Actual") |>
  hc_subtitle(text = "Pie shows number of major issues identified") |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "ErrorSum_Contact")

```



#### Error by template


```{r contacts_ebt, echo=FALSE, message=FALSE, warning=FALSE}
errfiles <- lapply(errfilepath, read.csv)


tdf <- unique(do.call(ltodf, errfiles))

tdf <- tdf |>
  mutate(err.deep = word(err.type, -1)) |>
  mutate(err = word(err.type, 1, -2)) |> select(-err.type) |>
  select(c(Name, Country, err, err.deep, err.count)) |>
  arrange(desc(err.count))

tdf.country <- tdf |>
  group_by(Country) |>
  summarize(err.count = sum(err.count)) |>
  arrange(err.count)


Lvl1dfStatus <-
  tibble(
    name = tdf.country$Country,
    y = tdf.country$err.count,
    drilldown = tolower(name)
  )
Level_2_Drilldowns <-
  lapply(unique(tdf$Country), function(x_level) {
    tdf.err <- tdf[tdf$Country == x_level, ]
    tdf.err <- tdf.err |>
      group_by(err) |>
      summarize(err.count = sum(err.count)) |> arrange(err.count)
    
    Lvl2dfStatus <-
      tibble(
        name = tdf.err$err,
        y = tdf.err$err.count,
        drilldown = tolower(paste(x_level, name, sep = "_"))
      )
    list(
      id = tolower(x_level),
      type = "column",
      data = list_parse(Lvl2dfStatus),
      name = "High Level Error"
    )
  })
Level_3_Drilldowns <-
  lapply(unique(tdf.country$Country), function(x_level) {
    tdf.err <- tdf[tdf$Country == x_level, ]
    lapply(unique(tdf.err$err), function(y_level) {
      tdf.err.deep <- tdf.err[tdf.err$err == y_level, ]
      tdf.err.deep <- tdf.err.deep |>
        group_by(err.deep) |>
        summarize(err.count = sum(err.count)) |> arrange(err.count)
      
      Lvl3dfStatus <-
        tibble(name = tdf.err.deep$err.deep, y = tdf.err.deep$err.count)
      list(
        id = tolower(paste(x_level, y_level, sep = "_")),
        type = "column",
        data = list_parse2(Lvl3dfStatus),
        name = "Deep Dive"
      )
    })
  }) |> unlist(recursive = FALSE)

highchart() |>
  hc_xAxis(type = "category") |>
  hc_add_series(
    Lvl1dfStatus,
    "column",
    hcaes(x = name, y = y),
    name = "Country View",
    showInLegend = F
  ) |>
  hc_plotOptions(column = list(stacking = "normal"),
                 lang = list(drillUpText = "Back")) |>
  hc_drilldown(
    allowPointDrilldown = TRUE,
    series = c(Level_2_Drilldowns, Level_3_Drilldowns)
  ) |>
  hc_yAxis(title = list(text = "Number of errors")) |>
  hc_title(text = "Error Count") |>
  hc_subtitle(text = "Click on bar for deep dive") |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_tooltip() |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "Error_Contact")
```



#### Errors in details {.tabset .tabset-pills}


##### Mandatory

###### Contact


```{r mand_contact}
manderrfilepath <-
  list.files("./contacts/errors/mandatory",
             pattern = "*.csv",
             full.names = T)
manderrfiles <- lapply(manderrfilepath, read.csv)
manderrdf <- do.call(ltodf, manderrfiles)
datatable(
  manderrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
```

##### Codelist


```{r codelist_contact}
codeerrfilepath <-
  list.files("./contacts/errors/codelist",
             pattern = "*.csv",
             full.names = T)
codeerrfiles <- lapply(codeerrfilepath, read.csv)
if(length(codeerrfiles)>0){
  codeerrdf <- do.call(ltodf, codeerrfiles)

codeerrdf |> select(c(3, 4)) |>
  rename(ColValues = def.rows.val, ColNames = def.colname) |>
  count(ColValues, ColNames) |> rename(Occurences = n) |>
  datatable(
    manderrdf,
    extensions = "Buttons",
    options = list(
      paging = TRUE,
      scrollX = TRUE,
      searching = TRUE,
      ordering = TRUE,
      dom = 'Bfrtip',
      buttons = c('copy', 'csv', 'excel', 'pdf'),
      pageLength = 10,
      lengthMenu = c(3, 5, 10),
      filter = TRUE
    )
  )
}

```


##### Length



```{r length_contact}
lenerrfilepath <-
  list.files("./contacts/errors/length",
             pattern = "*.csv",
             full.names = T)
lenerrfiles <- lapply(lenerrfilepath, read.csv)
if(length(lenerrfiles)){
  lenerrdf <- do.call(ltodf, lenerrfiles)
datatable(
  lenerrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
}

```




### Accounts




#### Template

SAP templates available:

```{r accounts_template, echo=FALSE}
datatable(
  data.frame(Templates = unique(
    rxl("./accounts/template.xlsx", sheet = "Field_Definitions")[, 1]
  )),
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10)
  )
)
```




#### Summary of Errors



```{r accounts_soe, echo=FALSE, message=FALSE, warning=FALSE}
sumerrfilepath <-
  list.files("./accounts/summary",
             pattern = "*sumerror.csv",
             full.names = T)
errfilepath <-
  list.files("./accounts/summary",
             pattern = "*_error.csv",
             full.names = T)

sumerrfiles <- lapply(sumerrfilepath, read.csv)
# datatable(unique(do.call(ltodf, sumerrfiles)), extensions = "Buttons",
#             options = list(paging = TRUE,
#                            scrollX=TRUE,
#                            searching = TRUE,
#                            ordering = TRUE,
#                            dom = 'Bfrtip',
#                            buttons = c('copy', 'csv', 'excel', 'pdf'),
#                            pageLength=10,
#                            lengthMenu=c(3,5,10) ))

tdf <- unique(do.call(ltodf, sumerrfiles)) |> arrange(desc(Expected))
tdf <-
  tdf |> mutate(Pending = Expected - Actual) |> select(-Name) |> unique()

tdf |>
  hchart(
    "bullet",
    hcaes(x = Country, y = Actual, target = Expected),
    color = "black",
    name = "Observations (Output)"
  ) |>
  hc_chart(inverted = TRUE) |>
  hc_add_series(tdf, "pie", hcaes(x = Country, y = Pending), name = "Pending (Major Issues)") |>
  hc_plotOptions(pie = list(
    center = c('70%', '70%'),
    size = 200,
    dataLabels = list(enabled = FALSE),
    showInLegend = T
  )) |>
  hc_title(text = "Expect vs Actual") |>
  hc_subtitle(text = "Pie shows number of major issues identified") |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "ErrorSumm_Account")

```



#### Error by template


```{r accounts_ebt, echo=FALSE, message=FALSE, warning=FALSE}
errfiles <- lapply(errfilepath, read.csv)
# datatable(unique(do.call(ltodf, errfiles)), extensions = "Buttons",
#             options = list(paging = TRUE,
#                            scrollX=TRUE,
#                            searching = TRUE,
#                            ordering = TRUE,
#                            dom = 'Bfrtip',
#                            buttons = c('copy', 'csv', 'excel', 'pdf'),
#                            pageLength=10,
#                            lengthMenu=c(3,5,10) ))

tdf <- unique(do.call(ltodf, errfiles))

tdf <- tdf |>
  mutate(err.deep = word(err.type, -1)) |>
  mutate(err = word(err.type, 1, -2)) |> select(-err.type) |>
  select(c(Name, Country, err, err.deep, err.count)) |>
  arrange(desc(err.count))

tdf.country <- tdf |>
  group_by(Country) |>
  summarize(err.count = sum(err.count)) |>
  arrange(err.count)


Lvl1dfStatus <-
  tibble(
    name = tdf.country$Country,
    y = tdf.country$err.count,
    drilldown = tolower(name)
  )
Level_2_Drilldowns <-
  lapply(unique(tdf$Country), function(x_level) {
    tdf.err <- tdf[tdf$Country == x_level, ]
    tdf.err <- tdf.err |>
      group_by(err) |>
      summarize(err.count = sum(err.count)) |> arrange(err.count)
    
    Lvl2dfStatus <-
      tibble(
        name = tdf.err$err,
        y = tdf.err$err.count,
        drilldown = tolower(paste(x_level, name, sep = "_"))
      )
    list(
      id = tolower(x_level),
      type = "column",
      data = list_parse(Lvl2dfStatus),
      name = "High Level Error"
    )
  })
Level_3_Drilldowns <-
  lapply(unique(tdf.country$Country), function(x_level) {
    tdf.err <- tdf[tdf$Country == x_level, ]
    lapply(unique(tdf.err$err), function(y_level) {
      tdf.err.deep <- tdf.err[tdf.err$err == y_level, ]
      tdf.err.deep <- tdf.err.deep |>
        group_by(err.deep) |>
        summarize(err.count = sum(err.count)) |> arrange(err.count)
      
      Lvl3dfStatus <-
        tibble(name = tdf.err.deep$err.deep, y = tdf.err.deep$err.count)
      list(
        id = tolower(paste(x_level, y_level, sep = "_")),
        type = "column",
        data = list_parse2(Lvl3dfStatus),
        name = "Deep Dive"
      )
    })
  }) |> unlist(recursive = FALSE)

highchart() |>
  hc_xAxis(type = "category") |>
  hc_add_series(
    Lvl1dfStatus,
    "column",
    hcaes(x = name, y = y),
    name = "Country View",
    showInLegend = F
  ) |>
  hc_plotOptions(column = list(stacking = "normal"),
                 lang = list(drillUpText = "Back")) |>
  hc_drilldown(
    allowPointDrilldown = TRUE,
    series = c(Level_2_Drilldowns, Level_3_Drilldowns)
  ) |>
  hc_yAxis(title = list(text = "Number of errors")) |>
  hc_title(text = "Error Count") |>
  hc_subtitle(text = "Click on bar for deep dive") |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_tooltip() |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "Error_Account")
```


#### Errors in details {.tabset .tabset-pills}

##### Mandatory {.tabset .tabset-pills}


###### Account


```{r mand_accounts}
manderrfilepath <-
  list.files("./accounts/errors/mandatory",
             pattern = "*.csv",
             full.names = T)
manderrfiles <- lapply(manderrfilepath, read.csv)


manderrdf <- do.call(ltodf, manderrfiles[c(1, 3, 5, 7, 9)])

datatable(
  manderrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
```

###### Account Team


```{r mand_account_team}
manderrdf <- do.call(ltodf, manderrfiles[c(2, 4, 6, 8, 10)])

datatable(
  manderrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
```




##### Codelist



```{r codelist_account}
codeerrfilepath <-
  list.files("./accounts/errors/codelist",
             pattern = "*.csv",
             full.names = T)
codeerrfiles <- lapply(codeerrfilepath, read.csv)
if(length(codeerrfiles)>0){
  codeerrdf <- do.call(ltodf, codeerrfiles)

codeerrdf |> select(c(3, 4)) |>
  rename(ColValues = def.rows.val, ColNames = def.colname) |>
  count(ColValues, ColNames) |> rename(Occurences = n) |>
  datatable(
    manderrdf,
    extensions = "Buttons",
    options = list(
      paging = TRUE,
      scrollX = TRUE,
      searching = TRUE,
      ordering = TRUE,
      dom = 'Bfrtip',
      buttons = c('copy', 'csv', 'excel', 'pdf'),
      pageLength = 10,
      lengthMenu = c(3, 5, 10),
      filter = TRUE
    )
  )
}

```


##### Length



```{r length_accounts}
lenerrfilepath <-
  list.files("./accounts/errors/length",
             pattern = "*.csv",
             full.names = T)
lenerrfiles <- lapply(lenerrfilepath, read.csv)
if(length(lenerrfiles)){
  lenerrdf <- do.call(ltodf, lenerrfiles)
datatable(
  lenerrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
}

```



### Projects





#### Template

SAP templates available:

```{r project_template, echo=FALSE}
datatable(
  data.frame(Templates = unique(
    rxl("./projects/template.xlsx", sheet = "Field_Definitions")[, 1]
  )),
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10)
  )
)
```




#### Summary of Errors



```{r project_soe, echo=FALSE, message=FALSE, warning=FALSE}
sumerrfilepath <-
  list.files("./projects/summary",
             pattern = "*sumerror.csv",
             full.names = T)
errfilepath <-
  list.files("./projects/summary",
             pattern = "*_error.csv",
             full.names = T)

sumerrfiles <- lapply(sumerrfilepath, read.csv)
# datatable(unique(do.call(ltodf, sumerrfiles)), extensions = "Buttons",
#             options = list(paging = TRUE,
#                            scrollX=TRUE,
#                            searching = TRUE,
#                            ordering = TRUE,
#                            dom = 'Bfrtip',
#                            buttons = c('copy', 'csv', 'excel', 'pdf'),
#                            pageLength=10,
#                            lengthMenu=c(3,5,10) ))

tdf <- unique(do.call(ltodf, sumerrfiles)) |> arrange(desc(Expected))
tdf <-
  tdf |> mutate(Pending = Expected - Actual) |> select(-Name) |> unique()

tdf |>
  hchart(
    "bullet",
    hcaes(x = Country, y = Actual, target = Expected),
    color = "black",
    name = "Observations (Output)"
  ) |>
  hc_chart(inverted = TRUE) |>
  hc_add_series(tdf, "pie", hcaes(x = Country, y = Pending), name = "Pending (Major Issues)") |>
  hc_plotOptions(pie = list(
    center = c('70%', '70%'),
    size = 200,
    dataLabels = list(enabled = FALSE),
    showInLegend = T
  )) |>
  hc_title(text = "Expect vs Actual") |>
  hc_subtitle(text = "Pie shows number of major issues identified") |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "ErrorSumm_Project")

```



#### Error by template


```{r project_ebt, echo=FALSE, message=FALSE, warning=FALSE}
errfiles <- lapply(errfilepath, read.csv)
# datatable(unique(do.call(ltodf, errfiles)), extensions = "Buttons",
#             options = list(paging = TRUE,
#                            scrollX=TRUE,
#                            searching = TRUE,
#                            ordering = TRUE,
#                            dom = 'Bfrtip',
#                            buttons = c('copy', 'csv', 'excel', 'pdf'),
#                            pageLength=10,
#                            lengthMenu=c(3,5,10) ))

tdf <- unique(do.call(ltodf, errfiles))

tdf <- tdf |>
  mutate(err.deep = word(err.type, -1)) |>
  mutate(err = word(err.type, 1, -2)) |> select(-err.type) |>
  select(c(Name, Country, err, err.deep, err.count)) |>
  arrange(desc(err.count))

tdf.country <- tdf |>
  group_by(Country) |>
  summarize(err.count = sum(err.count)) |>
  arrange(err.count)


Lvl1dfStatus <-
  tibble(
    name = tdf.country$Country,
    y = tdf.country$err.count,
    drilldown = tolower(name)
  )
Level_2_Drilldowns <-
  lapply(unique(tdf$Country), function(x_level) {
    tdf.err <- tdf[tdf$Country == x_level, ]
    tdf.err <- tdf.err |>
      group_by(err) |>
      summarize(err.count = sum(err.count)) |> arrange(err.count)
    
    Lvl2dfStatus <-
      tibble(
        name = tdf.err$err,
        y = tdf.err$err.count,
        drilldown = tolower(paste(x_level, name, sep = "_"))
      )
    list(
      id = tolower(x_level),
      type = "column",
      data = list_parse(Lvl2dfStatus),
      name = "High Level Error"
    )
  })
Level_3_Drilldowns <-
  lapply(unique(tdf.country$Country), function(x_level) {
    tdf.err <- tdf[tdf$Country == x_level, ]
    lapply(unique(tdf.err$err), function(y_level) {
      tdf.err.deep <- tdf.err[tdf.err$err == y_level, ]
      tdf.err.deep <- tdf.err.deep |>
        group_by(err.deep) |>
        summarize(err.count = sum(err.count)) |> arrange(err.count)
      
      Lvl3dfStatus <-
        tibble(name = tdf.err.deep$err.deep, y = tdf.err.deep$err.count)
      list(
        id = tolower(paste(x_level, y_level, sep = "_")),
        type = "column",
        data = list_parse2(Lvl3dfStatus),
        name = "Deep Dive"
      )
    })
  }) |> unlist(recursive = FALSE)

highchart() |>
  hc_xAxis(type = "category") |>
  hc_add_series(
    Lvl1dfStatus,
    "column",
    hcaes(x = name, y = y),
    name = "Country View",
    showInLegend = F
  ) |>
  hc_plotOptions(column = list(stacking = "normal"),
                 lang = list(drillUpText = "Back")) |>
  hc_drilldown(
    allowPointDrilldown = TRUE,
    series = c(Level_2_Drilldowns, Level_3_Drilldowns)
  ) |>
  hc_yAxis(title = list(text = "Number of errors")) |>
  hc_title(text = "Error Count") |>
  hc_subtitle(text = "Click on bar for deep dive") |>
  hc_tooltip() |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "Error_Project")
```


#### Errors in details {.tabset .tabset-pills}

##### Mandatory {.tabset .tabset-pills}


###### Opportunity


```{r mand_project}
manderrfilepath <-
  list.files("./projects/errors/mandatory",
             pattern = "*.csv",
             full.names = T)
manderrfiles <- lapply(manderrfilepath, read.csv)


manderrdf <- do.call(ltodf, manderrfiles[c(1, 3, 6)])

datatable(
  manderrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
```



###### Opportunity Sales Team Party In


```{r mand_oppo_sales}
manderrfilepath <-
  list.files("./projects/errors/mandatory",
             pattern = "*.csv",
             full.names = T)
manderrfiles <- lapply(manderrfilepath, read.csv)


manderrdf <- do.call(ltodf, manderrfiles[c(2, 5, 8)])

datatable(
  manderrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
```



###### Opportunity Party Information


```{r mand_opp_party}
manderrfilepath <-
  list.files("./projects/errors/mandatory",
             pattern = "*.csv",
             full.names = T)
manderrfiles <- lapply(manderrfilepath, read.csv)


manderrdf <- do.call(ltodf, manderrfiles[c(4, 7)])

datatable(
  manderrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
```


##### Codelist

```{r codelist_project}
codeerrfilepath <-
  list.files("./projects/errors/codelist",
             pattern = "*.csv",
             full.names = T)
codeerrfiles <- lapply(codeerrfilepath, read.csv)
if(length(codeerrfiles)>0){
  codeerrdf <- do.call(ltodf, codeerrfiles)

codeerrdf |> select(c(3, 4)) |>
  rename(ColValues = def.rows.val, ColNames = def.colname) |>
  count(ColValues, ColNames) |> rename(Occurences = n) |>
  datatable(
    manderrdf,
    extensions = "Buttons",
    options = list(
      paging = TRUE,
      scrollX = TRUE,
      searching = TRUE,
      ordering = TRUE,
      dom = 'Bfrtip',
      buttons = c('copy', 'csv', 'excel', 'pdf'),
      pageLength = 10,
      lengthMenu = c(3, 5, 10),
      filter = TRUE
    )
  )
}
```



##### Length



```{r length_project}
lenerrfilepath <-
  list.files("./projects/errors/length",
             pattern = "*.csv",
             full.names = T)
lenerrfiles <- lapply(lenerrfilepath, read.csv)
if(length(lenerrfiles)){
  lenerrdf <- do.call(ltodf, lenerrfiles)
datatable(
  lenerrdf,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
}


```
```{r}
# lenerrdf
# opp_notes<-data.frame(matrix(nrow=34, ncol=6))
# names(opp_notes)<-c(
# "External_Key",
# "Opportunity_External_Key",
# "Opportunity_ID",
# "Text",
# "Type_Code",
# "Author_Name")
# 
# opp_notes$Opportunity_External_Key<-lenerrdf$rowval
# opp_notes$External_Key<-paste0("NOTE",opp_notes$Opportunity_External_Key)
# opp_notes$Opportunity_ID<-c("")
# opp_notes$Text<-lenerrdf$colval
# opp_notes$Type_Code<-"10001"
# opp_notes$Author_Name<-c("")
# 
# write.csv(
#   opp_notes,
#   "./projects/output/combined/oppnotes.csv",
#   row.names = F,
#   na = "",
#   fileEncoding = "UTF-8",
#  sep = ","
# )
# 
# openxlsx::write.xlsx(opp_notes,"./projects/output/combined/oppnotes.xlsx")
```



### Support


#### Template

SAP templates available:

```{r echo=FALSE}
datatable(
  data.frame(Templates = unique(
    rxl("./support/template.xlsx", sheet = "Field_Definitions")[, 1]
  )),
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10)
  )
)
```




#### Summary of Errors



```{r echo=FALSE, message=FALSE, warning=FALSE}
sumerrfilepath <-
  list.files("./support/summary",
             pattern = "*sumerror.csv",
             full.names = T)
errfilepath <-
  list.files("./support/summary",
             pattern = "*_error.csv",
             full.names = T)

sumerrfiles <- lapply(sumerrfilepath, read.csv)
# datatable(unique(do.call(ltodf, sumerrfiles)), extensions = "Buttons",
#             options = list(paging = TRUE,
#                            scrollX=TRUE,
#                            searching = TRUE,
#                            ordering = TRUE,
#                            dom = 'Bfrtip',
#                            buttons = c('copy', 'csv', 'excel', 'pdf'),
#                            pageLength=10,
#                            lengthMenu=c(3,5,10) ))

tdf <- unique(do.call(ltodf, sumerrfiles)) |> arrange(desc(Expected))
tdf <-
  tdf |> mutate(Pending = Expected - Actual) |> select(-Name) |> unique()

tdf |>
  hchart(
    "bullet",
    hcaes(x = Country, y = Actual, target = Expected),
    color = "black",
    name = "Observations (Output)"
  ) |>
  hc_chart(inverted = TRUE) |>
  hc_add_series(tdf, "pie", hcaes(x = Country, y = Pending), name = "Pending (Major Issues)") |>
  hc_plotOptions(pie = list(
    center = c('70%', '70%'),
    size = 200,
    dataLabels = list(enabled = FALSE),
    showInLegend = T
  )) |>
  hc_title(text = "Expect vs Actual") |>
  hc_subtitle(text = "Pie shows number of major issues identified") |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "ErrorSumm_Support")

```



#### Error by template


```{r echo=FALSE, message=FALSE, warning=FALSE}
errfiles <- lapply(errfilepath, read.csv)
# datatable(unique(do.call(ltodf, errfiles)), extensions = "Buttons",
#             options = list(paging = TRUE,
#                            scrollX=TRUE,
#                            searching = TRUE,
#                            ordering = TRUE,
#                            dom = 'Bfrtip',
#                            buttons = c('copy', 'csv', 'excel', 'pdf'),
#                            pageLength=10,
#                            lengthMenu=c(3,5,10) ))

tdf <- unique(do.call(ltodf, errfiles))

tdf <- tdf |>
  mutate(err.deep = word(err.type, -1)) |>
  mutate(err = word(err.type, 1, -2)) |> select(-err.type) |>
  select(c(Name, Country, err, err.deep, err.count)) |>
  arrange(desc(err.count))

tdf.country <- tdf |>
  group_by(Country) |>
  summarize(err.count = sum(err.count)) |>
  arrange(err.count)


Lvl1dfStatus <-
  tibble(
    name = tdf.country$Country,
    y = tdf.country$err.count,
    drilldown = tolower(name)
  )
Level_2_Drilldowns <-
  lapply(unique(tdf$Country), function(x_level) {
    tdf.err <- tdf[tdf$Country == x_level, ]
    tdf.err <- tdf.err |>
      group_by(err) |>
      summarize(err.count = sum(err.count)) |> arrange(err.count)
    
    Lvl2dfStatus <-
      tibble(
        name = tdf.err$err,
        y = tdf.err$err.count,
        drilldown = tolower(paste(x_level, name, sep = "_"))
      )
    list(
      id = tolower(x_level),
      type = "column",
      data = list_parse(Lvl2dfStatus),
      name = "High Level Error"
    )
  })
Level_3_Drilldowns <-
  lapply(unique(tdf.country$Country), function(x_level) {
    tdf.err <- tdf[tdf$Country == x_level, ]
    lapply(unique(tdf.err$err), function(y_level) {
      tdf.err.deep <- tdf.err[tdf.err$err == y_level, ]
      tdf.err.deep <- tdf.err.deep |>
        group_by(err.deep) |>
        summarize(err.count = sum(err.count)) |> arrange(err.count)
      
      Lvl3dfStatus <-
        tibble(name = tdf.err.deep$err.deep, y = tdf.err.deep$err.count)
      list(
        id = tolower(paste(x_level, y_level, sep = "_")),
        type = "column",
        data = list_parse2(Lvl3dfStatus),
        name = "Deep Dive"
      )
    })
  }) |> unlist(recursive = FALSE)

highchart() |>
  hc_xAxis(type = "category") |>
  hc_add_series(
    Lvl1dfStatus,
    "column",
    hcaes(x = name, y = y),
    name = "Country View",
    showInLegend = F
  ) |>
  hc_plotOptions(column = list(stacking = "normal"),
                 lang = list(drillUpText = "Back")) |>
  hc_drilldown(
    allowPointDrilldown = TRUE,
    series = c(Level_2_Drilldowns, Level_3_Drilldowns)
  ) |>
  hc_yAxis(title = list(text = "Number of errors")) |>
  hc_title(text = "Error Count") |>
  hc_subtitle(text = "Click on bar for deep dive") |>
  hc_tooltip() |>
  hc_credits(enabled = TRUE,
             text = "LaNubia Data Science",
             href = "https://www.lanubia.com/") |>
  hc_exporting(enabled = TRUE, # always enabled
               filename = "Error_Support")
```