EnergyPlusSQL/server.R at master · lincolnharmer/EnergyPlusSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
#open libraries
library(shiny)
library(plotly)
library(RSQLite)
library(reshape2)
library(ggplot2)
#define ability to open large files
options(shiny.maxRequestSize = -1)
# Define server logic for random distribution application
shinyServer(function(input, output, session) {
  #opening the file

	  observe({
	      inFile <- input$file1

    if (is.null(inFile))
      return(NULL)
	 drv <- dbDriver("SQLite")
con <- dbConnect(drv, dbname = inFile$datapath)

      df<- dbGetQuery(con, "select VariableName FROM ReportMeterDataDictionary")
    str(names(df))
    if (!is.null(df)) {
      updateSelectInput(session,"variable", choices = (df))

    }
  })

  query <- function(){
      inFile <- input$file1

    if (is.null(inFile))
      return(NULL)

  		   drv <- dbDriver("SQLite")
  con <- dbConnect(drv, dbname = inFile$datapath)

  numVars=length(input$variable)

  vars=as.data.frame(matrix(nrow=1,ncol=numVars))
  for (i in 1:numVars ) {
    vars[[i]] <- dbGetQuery(con, paste("select ReportMeterDataDictionaryIndex FROM ReportMeterDataDictionary WHERE VariableName='",input$variable[i],"'",sep=""))
  }

  varsDataTest <- dbGetQuery(con, paste("select VariableValue FROM ReportVariableData WHERE ReportVariableDataDictionaryIndex='",vars[[1]],"'",sep=""))
  leng=dim(varsDataTest)[1]

  varsData=as.data.frame(matrix(nrow=leng,ncol=numVars))
  vars_temp=as.data.frame(matrix(nrow=leng,ncol=numVars))

  for (i in 1:numVars ) {
     vars_temp<- dbGetQuery(con, paste("select VariableValue FROM ReportVariableData WHERE ReportVariableDataDictionaryIndex='",vars[[i]],"'",sep=""))
     varsData[[i]]=vars_temp[[1]]
  }

  new=gsub(":", "_", input$variable)
  names(varsData) <- new
return(varsData)
   }

  output$filetable <- renderTable({

  query()

 })

  output$plot <- renderUI({
  {
    new=gsub(":", "_", input$variable)
	new=c("Date",input$variable)

  plotData=query()
  lengthTemp=dim(plotData[[1]])[2]
  rows=nrow(plotData)
  x=1:rows
  plotData=cbind(x,plotData)
   names(plotData) <- new
   plotData_long <- melt(plotData, id="Date")

		py <- plotly(username=input$userName, key=input$key)
		viz2 <- ggplot(data=plotData_long,aes(x=Date, y=value, colour=variable)) +
		geom_line()
		layout <- list(legend.position = "top",legend.direction = "horizontal")
		res <- py$ggplotly(viz2,kwargs=list(filename="Plotly in Shiny",layout=layout,
                                           fileopt="overwrite", # Overwrite plot in Plotly's website
                                           auto_open=FALSE))

    tags$iframe(src=res$response$url,
                  frameBorder="0",  # Some aesthetics
                  height=500,
                  width=1300*3/4*5/6)
		}
	  })

 #download the data
output$downloadData <- downloadHandler(
  filename = function() {
    paste("Data","csv", sep = ".")
  },
     content = function(file) {
    write.csv(query(), file)

 }
)
})