hello
i want to make query to be refreshed on chane in database and affects my plot and show changes according to that
library(RJDBC)
library(dplyr)
library(shiny)
library(ggplot2)
library(scales)
library(shinydashboard)
library(gridExtra)
library(DT)
library(ggthemes)
library(plotly)
library(data.table)
library(shinyjs)
library(shinycssloaders)
library(shinyBS)
dsn_driver = "com.ibm.db2.jcc.DB2Driver"
dsn_database = "BLUDB" # e.g. "BLUDB"
dsn_hostname = "" # e.g.: "awh-yp-small03.services.dal.bluemix.net"
dsn_port = "" # e.g. "50000"
dsn_protocol = "" # i.e. "TCPIP"
dsn_uid = "" # e.g. "dash104434"
dsn_pwd = ""
jcc = JDBC("com.ibm.db2.jcc.DB2Driver", "db2jcc4.jar");
jdbc_path = paste("jdbc:db2://", dsn_hostname, ":", dsn_port, "/", dsn_database, sep="");
conn = dbConnect(jcc, jdbc_path, user=dsn_uid, password=dsn_pwd)
totalsalesbydate="select year(RETAIL_STR_SALES_DETAIL.SALE_DATE) as YEAR
,monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE) AS MONTHNAME
,RETAIL_STR_SALES_DETAIL.SALE_DATE as SALE_DATE
,round(sum(RETAIL_STR_SALES_DETAIL.total),2) as TOTAL
from retail_str_sales_detail
where month(RETAIL_STR_SALES_DETAIL.SALE_DATE) = month(current date) and year(RETAIL_STR_SALES_DETAIL.SALE_DATE)=year(current date)
group by
year(RETAIL_STR_SALES_DETAIL.SALE_DATE)
,RETAIL_STR_SALES_DETAIL.SALE_DATE
,monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE)";
totalsalesbycurrentmonth <- fetch(dbSendQuery(conn,totalsalesbydate), -1)
bizdaily=data.frame(
SaleDate=totalsalesbycurrentmonth$SALE_DATE,
DayTotal=as.numeric(as.character(totalsalesbycurrentmonth$TOTAL))
)
shinyServer(function(input, output, session) {
fluidRow(
shinydashboard::box(width=6,status = "warning", solidHeader = TRUE,
title ="Total Sales Value By Current Month", collapsible = TRUE,
withSpinner(plotlyOutput("dailybar",width = "100%", height ="240")),actionButton("monthgo","+")
),
output$dailybar=renderPlotly({
p <- ggplot(bizdaily,aes(x=SaleDate, y=DayTotal, fill=SaleDate)) +
geom_bar(colour="black", stat="identity",
position=position_dodge(),
size=.3) + # Thinner lines
xlab("SaleDate") + ylab("DayTotal") + # Set axis labels
ggtitle("Current Month Sales")+ scale_y_continuous(labels = scales::comma)+ # Set title
theme_bw()+ theme(axis.text.x = element_text(angle = 45, hjust = 1))
p <- ggplotly(p)
})