10-drill-thru.Rmd

---
title: "Dashboard drill-down"
output: html_notebook
---

## Class catchup

```{r}
library(dplyr)
library(dbplyr)
library(DBI)
library(dbplot)
library(purrr)
# Class catchup
con <- DBI::dbConnect(odbc::odbc(), "Postgres Dev")
airports <- tbl(con, in_schema("datawarehouse", "airport"))
flights <- tbl(con, in_schema("datawarehouse", "flight"))
carriers <- tbl(con, in_schema("datawarehouse", "carrier"))
airline_list <- carriers %>%  
  select(carrier, carriername) %>%   
  collect()  %>%                     
  split(.$carriername) %>%           
  map(~.$carrier)     
```

## 10.1 -Add a tabset to the dashboard

1. Wrap the "output" functions in the **ui** with a `tabPanel()`
```{r, eval = FALSE}
# Goes from this
valueBoxOutput("total"),
dataTableOutput("monthly")

# To this
tabPanel(
  valueBoxOutput("total"),
  dataTableOutput("monthly")
  )
```

2. Set the panel's `title` and `value`. The new code should look like this
```{r, eval = FALSE}
tabPanel(
  title = "Dashboard", 
  value = "page1", 
  valueBoxOutput("total"),
  dataTableOutput("monthly")
  )
```

3. Wrap that code inside a `tabsetPanel()`, set the `id` to `tabs`
```{r, eval = FALSE}
tabsetPanel(
  id = "tabs",
  tabPanel(
    title = "Dashboard",
    value = "page1",
    valueBoxOutput("total"),
    dataTableOutput("monthly")
  )
)
```

4. Re-run the app

## 10.2 - Add interactivity

1. Set the `selection` and `rownames` in the current `datatable()` function
```{r, eval = FALSE}
output$monthly <- renderDataTable(datatable({
  base_dashboard() %>%
    group_by(month) %>%
    tally() %>%
    collect() %>%
    mutate(n = as.numeric(n)) %>%
    rename(flights = n) %>%
    arrange(month)}, 
  list( target = "cell"),    # New code
  rownames = FALSE))         # New code
```

2. Use `observeEvent()` and `appendTab()` to add the interactivity
```{r, eval = FALSE}
observeEvent(input$monthly_cell_clicked, {
  appendTab(
    inputId = "tabs", # This is the tabsets panel's ID
    tabPanel(
      "test_new", # This will be the label of the new tab
      renderDataTable(mtcars, rownames = FALSE)
    )
  )
}) 
```

3. Re-run the app

4. Click on a row inside the `datatable` and then select the new tab called `test_new` to see the `mtcars` data

## 10.3 - Add title to the new tab

1. Load the clicked cell's info into a variable, and create a new lable by concatenating the cell's month and the selected airline's code
```{r, eval = FALSE}
observeEvent(input$monthly_cell_clicked, {
  cell <- input$monthly_cell_clicked # New code

  if (!is.null(cell$value)) { # New code
    tab_title <- paste0(month.name[cell$value], "_", input$select)
    appendTab(
      inputId = "tabs",
      tabPanel(
        tab_title, # Changed code
        renderDataTable(mtcars, rownames = FALSE)
      )
    )
  }
})
```

2. Re-run the app, and click on one of the month's to confirm that the new label works

3. Use `updateTabsetPanel` to switch the dashboard's focus to the newly created tab. It goes after the `tabPanel()` code
```{r, eval = FALSE}
updateTabsetPanel(session, "tabs", selected = tab_title)
```

## 10-4 - pool package

1.Change `dbConnect()` to `dbPool()`
```{r, eval = FALSE}
# Goes from this
con <- DBI::dbConnect(odbc::odbc(), "Postgres Dev")

# To this
con <- pool::dbPool(odbc::odbc(), dsn =  "Postgres Dev")
```

2. Add an `onStop()` step to close the pool connection
```{r, eval = FALSE}
onStop(function() {
  poolClose(con)
})
```

3. The completed app should now look like this
```{r}

con <- pool::dbPool(odbc::odbc(), dsn =  "Postgres Dev")
onStop(function() {
  poolClose(con)
})

airports <- tbl(con, in_schema("datawarehouse", "airport"))
flights <- tbl(con, in_schema("datawarehouse", "flight"))
carriers <- tbl(con, in_schema("datawarehouse", "carrier"))


airline_list <- carriers %>%  
  select(carrier, carriername) %>%   
  collect()  %>%                    
  split(.$carriername) %>%          
  map(~.$carrier)    

ui <- dashboardPage(

  dashboardHeader(title = "Quick Example"),
  dashboardSidebar(selectInput("select", "Selection", airline_list)),
  dashboardBody(
    tabsetPanel(id = "tabs",
      tabPanel(
        title = "Dashboard", 
        value = "page1", 
        valueBoxOutput("total"),
        dataTableOutput("monthly")
      )
      )
    )
)

server <- function(input, output, session) {
  
  base_dashboard <- reactive({
    flights %>% 
      filter(uniquecarrier == input$select)})

  output$total <- renderValueBox({
    base_dashboard() %>%
      tally() %>%
      pull() %>%
      valueBox(subtitle = "Flights")})
    
  output$monthly <- renderDataTable(datatable({
    base_dashboard() %>%
      group_by(month) %>%
      tally() %>%
      collect() %>%
      mutate(n = as.numeric(n)) %>%
      rename(flights = n) %>%
      arrange(month)}, 
    list( target = "cell"),
    rownames = FALSE)
    
    )
  
  observeEvent(input$monthly_cell_clicked, {
    
    cell <- input$monthly_cell_clicked
    if(!is.null(cell$value)){
      tab_title <- paste0(month.name[cell$value], "_", input$select)
      appendTab(inputId = "tabs",
                tabPanel(
                  tab_title,
                  DT::renderDataTable({
                    base_dashboard() %>%
                      filter(month == cell$value) %>%
                      select(3:10) %>%
                      head(100) %>%
                      collect() 
                  }, rownames = FALSE)
                ))
      updateTabsetPanel(session, "tabs", selected = tab_title)
    }

  })
  
}
shinyApp(ui, server)
```