make reactive SelectInput value to render the coupled chart when clicked

[siddharth_sharma]

what i am trying to do when the category RAJ is selected or clicked it must render the first part of plott2 chart that has TITLE RAJ in the example given below and if other-state is clicked then it must render the else part of plott2 and then it must render the subset values of RAJ in below given select input value and then it will display the subset chart of raj and so on same for the other state whether we click on parent chart for childchart or just select the value from selectinput box to plot the chart,i know it kinda confusing but i am not looking for anyone to do this entire long process

what i am looking for just an idea to get it done.

library(RODBC)
library(RODBCext)
#> 
#> Attaching package: 'RODBCext'
#> The following objects are masked from 'package:RODBC':
#> 
#>     odbcFetchRows, sqlFetchMore
library(plotly)
#> Loading required package: ggplot2
#> 
#> Attaching package: 'plotly'
#> The following object is masked from 'package:ggplot2':
#> 
#>     last_plot
#> The following object is masked from 'package:stats':
#> 
#>     filter
#> The following object is masked from 'package:graphics':
#> 
#>     layout
library(shinydashboard)
#> 
#> Attaching package: 'shinydashboard'
#> The following object is masked from 'package:graphics':
#> 
#>     box
library(tidyverse)
library(readr)
library(shiny)
library(reprex)
library(DT)
#> 
#> Attaching package: 'DT'
#> The following objects are masked from 'package:shiny':
#> 
#>     dataTableOutput, renderDataTable
library(dplyr)


COL = c("#9B59B6","#58D68D","#F4D03F","#909497","#D35400","#5D6D7E")
dbcnd <- odbcDriverConnect('Driver={SQL Server};Server=;Database=;Uid=;Pwd=')

ui<-dashboardPage(
  dashboardHeader(title = "dashboard",titleWidth = 300),
  
  dashboardSidebar(
    sidebarMenuOutput("menu"),
    selectInput(inputId = "category",
                label = "STATE", 
                choices = c("RAJ","OTHER_STATES")),  
    selectInput(inputId = "category1", 
                label = "DISTRICT", choices = c("ANISTEN","BIAN")),
    selectInput(inputId = "category2", 
                label = "CENTER_NAME", choices = c("ASTER","CLANE","JABELIN"))
  ),
  
  dashboardBody(
    
    fluidRow(plotlyOutput("plott1")),
    fluidRow(plotlyOutput("plott2")),
    fluidRow(plotlyOutput("plott3")),
    verbatimTextOutput("check")
    
  ))

server<-function(input,output){
  output$plott1 <- renderPlotly({
    
    qrydd=paste("exec database_map..[spformother]'','99','','','0','01/01/2017','31/12/2017'")
    qrydd
    dtd <- sqlQuery(dbcnd,qrydd)
    dtd <- data.frame(dtd)
    dtd
    ds <- data.frame(labels = c("Raj","Other_State"),
                     values = c(dtd$TotRaj,dtd$TotOtherState)
    )
    closeAllConnections()
    
    plot_ly(ds, labels = ~labels, values = ~values,type = 'pie',source ='listenhere1',
            textposition = 'inside',
            textinfo = 'label+percent',
            insidetextfont = list(color = '#FFFFFF'),
            marker = list(colors = colors,
                          line = list(color =colors , width = 1)),showlegend = FALSE) %>% 
      layout(title = 'state wise',
             xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
             yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
  }) 
  
  
  output$plott2 <- renderPlotly({
    s <- event_data("plotly_click", source = "listenhere1")
    req(!is.null(s))
    
    if(s$pointNumber == 0){
      
      qrydd=paste("exec database_map..[spformother] '0','0','','0','','01/01/2017','31/12/2017'")
      qrydd
      dtd <- sqlQuery(dbcnd,qrydd)
      dtd <- data.frame(dtd)
      dtd
      closeAllConnections()
      tablename=rowSums(cbind(dtd$TotRaj,dtd$TotOtherState),na.rm=TRUE)
      
      layout <- list(
        font = list(size = 12),
        title = "raj",
        xaxis = list(title = "district"),
        yaxis = list(title = "records",automargin = TRUE)
      )
      
      p <- plot_ly(dtd, colors = COL, marker = list(color = COL),source = 'link3',key = ~paste(dtd$DID, sep = "")) %>%
        add_trace( x = dtd$DistrictName, y =~tablename,name = "",type = 'bar') %>%
        layout( font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)
    }
    else {
      
      qrydd=paste("exec database_map..[spformother] '','','','','0','0','2','0','','01/01/2017','31/12/2017'")
      qrydd
      dtd <- sqlQuery(dbcnd,qrydd)
      dtd <- data.frame(dtd)
      dtd
      closeAllConnections()
      tablename=rowSums(cbind(dtd$TotRaj,dtd$TotOtherState),na.rm=TRUE)
      
      layout <- list(
        font = list(size = 12),
        title = "OTHER_STATES",
        xaxis = list(title = "RECORDS"),
        yaxis = list(title ="STATES" ,automargin = TRUE)
      )
      
      p <- plot_ly(dtd, colors = COL, marker = list(color = COL),source = 'linkhere',orientation='h') %>%
        add_trace( x =dtd$TotFormFOtherState,y = dtd$StateName,name = "states",type = 'bar') %>%
        layout( font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)
      
      
    }
    
  })
  
  output$plott3 <- renderPlotly({
    d<-event_data('plotly_click',source = 'link3')
    if(is.null(d)==F){
     
      qrydd=paste("exec database_map..[spformother]",d$key,",'3','','0','','01/01/2017','31/12/2017'")
      
      qrydd
      dtd <- sqlQuery(dbcnd,qrydd)
      dtd <- data.frame(dtd)
      dtd
      closeAllConnections()
      
      data <- data.frame(dtd$BlockName, dtd$TotRaj, dtd$TotOtherState)
      
      p <- plot_ly(dtd, x = dtd$BlockName, y = dtd$TotRaj, type = 'bar', name = 'rajasthan',key = ~paste(dtd$DID,sep = ""), source = 'link1') %>%
        add_trace(y = dtd$TotOtherState, name = 'other-state') %>%
        layout(yaxis = list(title = ''), barmode = 'group',title=dtd$BlockName)}
    else{
      return(NULL)
    }
    
  })
}


shinyApp(ui,server)