R Shiny - Query the DB Repetitively with conditions from CSV

dplyr
tidyverse

#1

I am trying to create a dashboard by pulling data from different database tables from the conditions in a csv file.

Spec_CSV <- read.csv("/home/..../data/Spec Sheet.csv",header=T, sep=",",stringsAsFactors = TRUE)

Series  Group   AttributeName   GroupType   MOC Condition
T59 DETAILS Attribute 55    Primary M   
T80 FLAGS   Attribute 76    Repeating   C   If Attribute 77  is populated then this values has to be populated 
T81 FLAGS   Attribute 77    Repeating   O   
T82 FLAGS   Attribute 78    Repeating   M   
T30 DETAILS Attribute 26    Primary M   
T31 DETAILS Attribute 27    Primary C   If Attribute 26="ABC" then values has to be populated 
T32 DETAILS Attribute 28    Primary M   
T33 DETAILS Attribute 29    Primary O   

I am pulling the conditions individually which makes the code very tedious and the page slow.

#Logic for Details for Mandatory Fields   

    detailsMandatory<-reactive({
      columnnamesfordetailsman<-Spec_CSV$AttributeName[Spec_CSV$MOC == "M" & Spec_CSV$Group=="DETAILS"]
      columnnamesfordetailsman_comma=noquote(paste("a.",as.character(columnnamesfordetailsman),sep="",collapse=","))
      #print(columnnamesfordetailsman_comma)

    })

    readDetailsforMandatory <- eventReactive(input$pick_dt,{isolate(

      if(input$pick_assetclass!=c(NULL,'NA') && input$pick_assetclass=="FX" && input$pick_feed!=c(NULL,'NA') && input$pick_src!=c(NULL,'NA') && input$pick_dt!=c(NULL,'NA')){

        sqlInput1 <- paste("select ",detailsMandatory(),"
                           from DETAILS a left join ",outputMart()," b on a.trade_id=b.trade_id where b.FEED=","'",input$pick_feed,"'",
                           "and b.SRC=" ,"'",input$pick_src,"'"," and b.DT= ","'",input$pick_dt,"'"," order by a.trade_id",
                           sep="")

        dbGetQuery(con, sqlInput1)}

    )})


    detailsmand<-reactive({
      missper <- signif(sum(is.na(readDetailsforMandatory()))/prod(dim(readDetailsforMandatory()))*100,digits=4)
      misscount <- sum(is.na(readDetailsforMandatory()))
      data.frame("Trade Attributes","M",missper,misscount)})

#Logic for Flags for Mandatory Fields  

    flagsMandatory<-reactive({
      columnnamesforflagsman<-Spec_CSV$AttributeName[Spec_CSV$MOC == "M" & Spec_CSV$Group=="FLAGS"]
      columnnamesforflagsman_comma=noquote(paste("a.",as.character(columnnamesforflagsman),sep="",collapse=","))
      #print(columnnamesforflagsman_comma)

    })

    readFlagsforMandatory <- eventReactive(input$pick_dt,{isolate(

      if(input$pick_assetclass!=c(NULL,'NA') && input$pick_assetclass=="FX" && input$pick_feed!=c(NULL,'NA') && input$pick_src!=c(NULL,'NA') && input$pick_dt!=c(NULL,'NA')){

        sqlInput1 <- paste("select ",flagsMandatory(),"
                           from FLAGS a left join ",outputMart()," b on a.trade_id=b.trade_id where b.FEED=","'",input$pick_feed,"'",
                           "and b.SRC=" ,"'",input$pick_src,"'"," and b.DT= ","'",input$pick_dt,"'"," order by a.trade_id",
                           sep="")

        dbGetQuery(con, sqlInput1)}

    )})


    flagsmand<-reactive({
      missper <- signif(sum(is.na(readFlagsforMandatory()))/prod(dim(readFlagsforMandatory()))*100,digits=4)
      misscount <- sum(is.na(readFlagsforMandatory()))
      data.frame("Flag Attributes","M",missper,misscount)})

#Logic for Flags for Optional Fields 

flagsOptional<-reactive({
      columnnamesforflagsopt<-Spec_CSV$AttributeName[Spec_CSV$MOC == "O" & Spec_CSV$Group=="FLAGS"]
      columnnamesforflagsopt_comma=noquote(paste("a.",as.character(columnnamesforflagsopt),sep="",collapse=","))
      #print(columnnamesforflagsopt_comma)

    })

    readFlagsforOptional <- eventReactive(input$pick_dt,{isolate(

      if(input$pick_assetclass!=c(NULL,'NA') && input$pick_assetclass=="FX" && input$pick_feed!=c(NULL,'NA') && input$pick_src!=c(NULL,'NA') && input$pick_dt!=c(NULL,'NA')){

        sqlInput1 <- paste("select ",flagsOptional(),"
                           from FLAGS a left join ",outputMart()," b on a.trade_id=b.trade_id where b.FEED=","'",input$pick_feed,"'",
                           "and b.SRC=" ,"'",input$pick_src,"'"," and b.DT= ","'",input$pick_dt,"'"," order by a.trade_id",
                           sep="")

        dbGetQuery(con, sqlInput1)}

    )})

    flagsoptional<-reactive({
      missper <- signif(sum(is.na(readFlagsforOptional()))/prod(dim(readFlagsforOptional()))*100,digits=4)
      misscount <- sum(is.na(readFlagsforOptional()))
      data.frame("Flag Attributes","O",missper,misscount)})

#Consolidated Data Table

consolidatedresults<-eventReactive(input$pick_cobdt,{isolate(

       rbindlist(list(detailsmand(),flagsmand(),flagsoptional()),idcol=TRUE)

    )})    
output$consolidated<-renderDataTable((datatable(data.frame(consolidatedresults())
                                                    ,class = 'cell-border stripe',
                                                      colnames = c('Table Name', 'M/O/C','Missing %','Missing Count'),
                                                      rownames = FALSE,options = list(
                                                      pageLength = 25,autoWidth = TRUE,dom = 't')))
    )

What is the best possible way to loop through the csv and pass the results to the database and get the results at one go with minimal lines of code? I have tried to use split function on the csv but stuck with how to pass the results to the database query at one go.

data.dplyr <-split(Spec_CSV$AttributeName,list(Spec_CSV$Group,Spec_CSV$MOC))
data.dplyr.result<-sapply(data.dplyr,function(x)paste("a.",as.character(x),sep="",collapse=","))

Thanks,