Refresh query on every new entry in database and instant response on my plot

shiny-server

#1

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)
    
    
  })

#2

Hi @rj2102, welcome to community!

Could you kindly rewrite your post a bit to be more of a reproducible example? Here’s a quick post on when and how: FAQ: What’s a reproducible example (`reprex`) and how do I do one?

I think it’ll improve your chances of finding help with this issue.