---
title: "A15 Dashboard"
output: html_notebook
---

By Noah Perry and Andrew Hall

1. Build a dashboard to report the total amount and number of checks received for ClassicModels for a given year and month.
2. Submit a pdf of the code and dashboard April 2005.

```{r}
library(tidyverse)
library(lubridate)
library(shiny)
library(shinydashboard)

#Creating Header, Sidebar, and Sidebar menu
header <- dashboardHeader(title="ClassicModels Monthly Revenue and Order Volume")
monthOption<-menuItem(selectInput(inputId = "month", "Pick a Month", c(1:12), NULL, FALSE, FALSE))
yearOption<-menuItem(selectInput(inputId = "year", "Pick a Year", c(2003:2005), NULL, FALSE, FALSE))
sideMenu<-sidebarMenu(monthOption,yearOption)
sidebar <- dashboardSidebar(sideMenu)

#Formatting Body and UI
body <- dashboardBody(tableOutput("tbl"))
ui <- dashboardPage(header, sidebar, body)

server <- function(input, output,session) {
  #Rendered Table based on user menu input
   output$tbl <- renderTable({
     #DB connection
     conn<-dbConnect(RMySQL::MySQL(),"richardtwatson.com",dbname="ClassicModels",user="student",password="student")
     on.exit(dbDisconnect(conn), add = TRUE)
     #Dynamic SQL query
     sql<-"Select sum(amount) as 'Monthly Revenue', count(checkNumber) as 'Number of Orders' from Payments where Month(paymentDate) = ?month AND Year(paymentDate)=?year;"
     query<-sqlInterpolate(conn, sql, year = input$year, month = input$month)
     dbGetQuery(conn, query)
   })
}
shinyApp(ui, server)

```