---
title: "Sustainability Upgrades"
output: 
  flexdashboard::flex_dashboard:
    source_code: embed
    vertical_layout: fill
---

  

```{r setup, include=FALSE}
# last update to code on 3/3/2020 by Rich Majerus

# turn off echo 
knitr::opts_chunk$set(echo = FALSE)

# load packages
library(tidyverse)
library(DT)
library(crosstalk)
library(readxl)
library(flexdashboard)
library(lubridate)

```

```{r data}
# load data files for metrus schedule and scope of work (both lights and water upgrades)
lighting <- read_xlsx("Lighting Line-by Line SOW.xlsx")
water <- read_xlsx("Queens University Water line by line.xlsx")
schedule <- read_csv("schedule.csv")

# clean lighting data and water data so they can be bound by rows
lighting <- 
  lighting %>% 
  mutate(Category = "Lighting") %>% # preserve data source by creating variable
  select(`Building Name`,
         `Location Description`,
         Category, 
         `Proposed Action Description`) %>% 
  rename(Building = "Building Name",
         Location = "Location Description",
         Upgrade = "Proposed Action Description") 

# apply same cleaning process to water data and bind with lighting data
sow <- 
  water %>% 
  mutate(Category = "Water") %>% 
  select(`Building Name`, 
         `Room Number/Location`,
         Category,
         `Fixture Description`) %>% 
  rename(Building = "Building Name",
         Location = "Room Number/Location",
         Upgrade = "Fixture Description") %>% 
  bind_rows(lighting) %>% 
  mutate(Location = str_replace(Location, "gang", "Shared Bathroom")) %>% 
  arrange(Category)



# select needed columns from schedule and create date information including
# number of days that each space will be impacted
schedule <- 
  schedule %>% 
  rename(Improvement = "Task Name") %>% 
  select(Building, Category, Improvement, Start, Finish) %>% 
  filter(!is.na(Building)) %>% 
  mutate(Start = gsub("[^0-9\\/]", "", Start),
         Start = as.Date(Start, "%m/%d/%y")) %>% 
  mutate(Finish = gsub("[^0-9\\/]", "", Finish),
         Finish = as.Date(Finish, "%m/%d/%y")) %>% 
  mutate(n_days = interval(Start - 1, Finish)/days(1)) %>% 
  rename("Days Impacted" = n_days)


# wrap each data source in SharedData to use with crosstalk 
# keys need to match so filter applies to both tables
sd  <- SharedData$new(data = schedule,  key =  ~Building,  group = "Building")
sd2 <- SharedData$new(data = sow,       key =  ~Building,  group = "Building")        
```


Inputs {.sidebar}
-------------------------------------

```{r filters}
# create filter to limit tables by selected building(s)
filter_select(id = "building",
              label = "Select Building(s)",
              sharedData = sd,
              group = ~Building)
```

**Instructions**  

- Filter the tables by selecting your building(s) of interest from the dropdown menu. 
- Sort tables by clicking on column names. 
- Filter detailed table (second table) using the search box.
- Email Matt Packey (packeym@queens.edu) if you have an unavoidable conflict that will be impacted by sustainability improvements. 
- Email Rich Majerus (majerusr@queens.edu) if you have difficulty using the application or encounter an error. 
- Enjoy our more sustainable campus!


Column
-------------------------------------
    
### Metrus Project Schedule

```{r}
# create first table that will show overall schedule and days impacted
datatable(sd, 
          extensions = c("Scroller"),
          class = "compact", 
          rownames = FALSE,
          style = "bootstrap", 
          options = list(
            dom = 'Brti',
            deferRender = TRUE, 
            scrollY = 300,
            scroller = TRUE,
            columnDefs = list(list(className = 'dt-left', targets = 0:5))))
```

### Metrus Project Detailed List of Lighting and Water Improvements by Location

```{r}
# create first table that will show detailed list of spaces being improved in each building
datatable(sd2, 
          extensions = c("Scroller"), 
          class="compact", 
          style = "bootstrap", 
          rownames = FALSE,
          options=list(
            dom = 'Blfrtip',
            deferRender=TRUE, 
            scrollY=300,
            scroller=TRUE,
            columnDefs = list(list(className = 'dt-left', targets = 0:3))))
```