library(shiny)
library(ROracle)
library(DT)
ui <- shinyUI(navbarPage("Test",
                         tabPanel("Test",
                                  sidebarLayout(
                                    sidebarPanel(
                                      textInput("drv", "Database Driver", value="Oracle"),
                                      textInput("user", "User ID"),
                                      passwordInput("passwd", "Password"),
                                      actionButton("connectDB", "Connect to DB")) ,
                                    mainPanel(
                                      textOutput("test"),
                                      wellPanel(
                                        h4(strong("Filtering:")),
                                        br(),
                                        radioButtons('sqlfilter', 'Filter:',
                                                     choices = list("1 x OrdNr" = 1,
                                                                    "2 x OrdNr" = 2, "Nr" = 3,
                                                                    "Nr and Var" = 4),
                                                     selected = 1,inline=TRUE),
                                        conditionalPanel(
                                          condition = "input.sqlfilter == '1' | input.sqlfilter == '2' ", uiOutput("ordnr_ui")),
                                        conditionalPanel(
                                          condition = "input.sqlfilter == '2' ", uiOutput("ordnr2_ui")),
                                        conditionalPanel(
                                          condition = "input.sqlfilter == '3' | input.sqlfilter == '4'",
                                          uiOutput("werknr_ui")),
                                        conditionalPanel(
                                          condition = "input.sqlfilter == '4' ",
                                          uiOutput("werkvar_ui")),
                                        actionButton("button1", "Auswaehlen"))
                                    ))
                         )
))


server <- shinyServer(function(input, output, session) {

      con=reactiveValues(cc=NULL)

      observeEvent(input$connectDB,{
        if(input$drv != "Oracle"){
          con$cc="Only 'Oracle' implemented currently"
        }else{
          drv <- dbDriver("Oracle")
          con$cc<- dbConnect(drv,input$host,username=input$user,password=input$passwd) #1st Query
        }
      })
      observe({
        if(!is.null(con$cc)& is(con$cc,"OraConnection")){ # check if connected/very slow...
          output$test <- renderText({
            HTML(paste0(tags$span(style="color:green","Verbindung zur"),"<br>","<b>", tags$span(style="color:green","Kawasi Datenbank, Schema S_BLOCKTEMP_SEG_ALL_KB"),"</b>","<br>",tags$span(style="color:green","erfolgreich hergestellt")))
          })

          rs <- dbSendQuery(con$cc, "Select distinct a From x.y order by x asc") #2nd Query
          ordnnrlist <- fetch(rs)

          rs2 <- dbSendQuery(con$cc, "Select distinct n,v From x.y") #3rd Query
          werknrlist <- fetch(rs2)
          output$werknr_ui=renderUI({selectInput("werknr", label = "Nr:", choices = werknrlist$x, selected="",multiple=FALSE)})
          output$werkvar_ui=renderUI({selectInput("werkvar", label = "Var:", choices = werknrlist$y, selected="",multiple=FALSE)})

          observe({
            updateSelectInput(session, "werkvar", choices=as.list(levels(droplevels(as.factor(werknrlist$x[werknrlist$y %in% input$werknr])))),
                              selected="")
          })

          d <- eventReactive(input$button1, { input$ordnr })
          d2 <- eventReactive(input$button1, { input$ordnr2 })
          d3 <- eventReactive(input$button1, { input$werknr })
          d4 <- eventReactive(input$button1, { input$werkvar })

          if(input$sqlfilter == 1){ #4th Query
            output$ordnr_ui=renderUI({selectInput("ordnr", label = "OrdNr_1:", choices = ordnnrlist$a, selected="",multiple=FALSE)})
            sqlOutput <- reactive({
              sqlInput <- paste("select * from x.y where x.y.a =",d())
              dbGetQuery(con$cc, sqlInput)
            })
          }else if(input$sqlfilter == 2){
            output$ordnr2_ui=renderUI({selectInput("ordnr2", label = "OrdNr_2:", choices = ordnnrlist$ORDNR, selected="",multiple=FALSE)})
            sqlOutput <- reactive({
              sqlInput <-  paste("select * from x.y where ((a =",d(), ") or (a =",d2(), "))")
              dbGetQuery(con$cc, sqlInput)
            })}
          else if(input$sqlfilter == 3){
            sqlOutput <- reactive({
              sqlInput <-  paste("select * from x.y where x.y.n=",paste0("'",d3(),"'"))
              dbGetQuery(con$cc, sqlInput)
            })}
          else{
            sqlOutput <- reactive({
              sqlInput <-  paste("select * from x-y where x.y.n=",paste0("'",d3(),"'"),"And x.y.v=",paste0("'",d4(),"'"))
              dbGetQuery(con$cc, sqlInput)
            })}


    }else if (!is.null(con$cc) ){
      output$test <- renderText({
        con$cc
      })
    }
  })


  session$onSessionEnded(function() {
    observe({
      if(!is.null(con$cc)& is(con$cc,"OraConnection")){# check if connected
        print(paste0("disconnect ",dbDisconnect(con$cc)))}
    })
  })

})

shinyApp(ui=ui, server=server)