Statistics
| Revision:

root / hci / trunk / eneraptor-web-app / grails-app / controllers / com / eneraptor / hci / StatisticsController.groovy @ 67

History | View | Annotate | Download (29.5 KB)

1

    
2
package com.eneraptor.hci
3

    
4
import groovy.sql.Sql
5

    
6
class StatisticsController {
7

    
8
        def dataSource
9
        
10
        def graphDataService
11
        
12
    def main = { }
13
        
14
        def graphs = {
15
        
16
                List savedGraphs = new ArrayList()
17
                List combinedGraphs = new ArrayList()
18
                
19
                savedGraphs = EneGraph.findAllByHwSet(session.hwSet)
20
                
21
                combinedGraphs = CombGraph.list()
22
                        
23
                [savedGraphs: savedGraphs, combinedGraphs:combinedGraphs]
24
                        
25
        }
26
        
27
        def newGraph = {
28
                
29
                List graphTypes = ["min","max","avg","sum"]
30
                List graphTypesFriendly = ["Minimum value","Maximum value","Average value","Cumulative"]
31
                
32
                List devices = new ArrayList()
33
                List devicesFriendly = new ArrayList()
34
                
35
                DeviceInfo.findAllByHwSet(session.hwSet).each {
36
                        devices << it.deviceId
37
                        devicesFriendly << it.friendlyName
38
                }
39
                
40
                [graphTypes:graphTypes,graphTypesFriendly:graphTypesFriendly, devices:devices, devicesFriendly:devicesFriendly]
41
                
42
        }
43
        
44
        def newGraphDo = {
45
                
46
                String errors = ""
47
                Date chosenTimeFrameStart = null
48
                Date chosenTimeFrameEnd = null
49
                
50
                if(params['timeFrameType'] == "day") {
51
                        chosenTimeFrameStart = params['dayMainTimeFrameStart']
52
                        chosenTimeFrameEnd = params['dayMainTimeFrameEnd']
53
                } else if(params['timeFrameType'] == "month") {
54
                        chosenTimeFrameStart = params['monthMainTimeFrameStart']
55
                        chosenTimeFrameEnd = params['monthMainTimeFrameEnd']
56
                } else if(params['timeFrameType'] == "year") {
57
                        chosenTimeFrameStart = params['yearMainTimeFrameStart']
58
                        chosenTimeFrameEnd = params['yearMainTimeFrameEnd']
59
                } else if(params['timeFrameType'] == "custom") {
60
                        chosenTimeFrameStart = params['customMainTimeFrameStart']
61
                        chosenTimeFrameEnd = params['customMainTimeFrameEnd']
62
                } else {
63
                        errors += "<p>Time frame type invalid!</p>"
64
                }
65
                
66
                def newEneGraph = new EneGraph(
67
                        name: params['graphName'],
68
                        type: params['graphType'],
69
                        timeFrameType: params['timeFrameType'],
70
                        timeFrameStart: chosenTimeFrameStart,
71
                        timeFrameEnd: chosenTimeFrameEnd,
72
                        innerSections: params['customMainTimeFrameInnerSections'],
73
                        advHoursTimeFrameStart: params['advHoursStart'],
74
                        advHoursTimeFrameEnd: params['advHoursEnd'],
75
                        advDayTimeFrameStart: params['advDayStart'],
76
                        advDayTimeFrameEnd: params['advDayEnd'],
77
                        advMonthTimeFrameStart: params['advMonthStart'],
78
                        advMonthTimeFrameEnd: params['advMonthEnd'],
79
                        deviceId: params['deviceId']
80
                )
81
                
82
                HardwareSet.get(session.hwSet.id).addToGraphs(newEneGraph)
83
                
84
                if(!newEneGraph.validate()) {
85
                        errors += "<p>Graph could not be created - there were some input errors. Please check if all input values are valid.</p>"
86
                        flash['errors'] = errors
87
                        redirect(action:'newGraph')
88
                } else {
89
                        if(newEneGraph.save()){
90
                                flash['confirms'] = "<p>Graph created succesfully!</p>"
91
                                redirect(action:'newGraph')
92
                        } else {
93
                                flash['errors'] = "<p>There was an error while saving graph.</p>"
94
                                redirect(action:'newGraph')
95
                        }
96
                }
97
                
98
                return true
99
                
100
        }
101
        
102
        def showGraph = {
103
                
104
                def graphToShow = EneGraph.get(params['id'])
105
                String query
106
                
107
                List data = new ArrayList()
108
                data = graphDataService.getGraphData(graphToShow)
109
                
110
                [data: "{label: '" + graphToShow.name + "', data: " + data.toString() + " }"]
111
                
112
        }
113
        
114
        def editGraph = {
115
                
116
                EneGraph chosenGraph = EneGraph.get(params['id'])
117
                
118
                List graphTypes = ["min","max","avg","sum"]
119
                List graphTypesFriendly = ["Minimum value","Maximum value","Average value","Cumulative"]
120
                
121
                List devices = new ArrayList()
122
                List devicesFriendly = new ArrayList()
123
                
124
                DeviceInfo.findAllByHwSet(session.hwSet).each {
125
                        devices << it.deviceId
126
                        devicesFriendly << it.friendlyName
127
                }
128
                
129
                [graph: chosenGraph, graphTypes:graphTypes, graphTypesFriendly:graphTypesFriendly, devices:devices, devicesFriendly:devicesFriendly]
130
                
131
        }
132
        
133
        def editGraphDo = {
134
        
135
                String errors = ""
136
                
137
                EneGraph graphToEdit = EneGraph.get(params['graphId'])
138
                
139
                graphToEdit.name = params['graphName']
140
                graphToEdit.type = params['graphType']
141
                graphToEdit.timeFrameType = params['timeFrameType']
142
                graphToEdit.timeFrameStart = params['mainTimeFrameStart']
143
                graphToEdit.timeFrameEnd = params['mainTimeFrameEnd']
144
                graphToEdit.innerSections = params['mainTimeFrameInnerSections'] as int
145
                graphToEdit.advHoursTimeFrameStart = params['advHoursStart'] as int
146
                graphToEdit.advHoursTimeFrameEnd = params['advHoursEnd'] as int 
147
                graphToEdit.advDayTimeFrameStart = params['advDayStart'] as int
148
                graphToEdit.advDayTimeFrameEnd = params['advDayEnd'] as int
149
                graphToEdit.advMonthTimeFrameStart = params['advMonthStart'] as int
150
                graphToEdit.advMonthTimeFrameEnd = params['advMonthEnd'] as int
151
                graphToEdit.deviceId = params['deviceId']
152
                
153
                if(!graphToEdit.validate()) {
154
                        errors += "<p>Graph could not be modified - there were some input errors. Please check if all input values are valid.</p>"
155
                        flash['errors'] = errors
156
                        redirect(action:'editGraph',params: [id: params.graphId])
157
                } else {
158
                        if(graphToEdit.save()){
159
                                flash['confirms'] = "<p>Graph modified succesfully!</p>"
160
                                redirect(action:'graphs')
161
                        } else {
162
                                flash['errors'] = "<p>There was an error while modifying graph.</p>"
163
                                redirect(action:'editGraph',params: [id: params.graphId])
164
                        }
165
                }
166
                
167
                return true
168
                        
169
        }
170
        
171
        def newReport = {
172
                
173
                def graphsAll = EneGraph.findAllByHwSet(session.hwSet)
174
                def graphs = new ArrayList()
175
                def graphsKeys = new ArrayList()
176
                graphsAll.each {
177
                        graphs << it.name
178
                        graphsKeys << it.id
179
                }
180
                
181
                [graphs:graphs, graphsKeys:graphsKeys]
182
                
183
        }
184
        
185
        def makeReport = {
186
                
187
                def graphToShow = EneGraph.get(params['id'])
188
                String query
189
                
190
                List data = new ArrayList()
191
                List detailedData = new ArrayList()
192
                def results
193
                def innerTimeFrameHalf = 0;
194
                def db_sql = new Sql(dataSource)
195
                
196
                if(graphToShow.timeFrameType == "day") {
197
                        db_sql.eachRow "select date_trunc('day',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('day',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('day',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('day',ld.date_recieved) order by date_trunc('day',ld.date_recieved) asc", {
198
                                data << [it.dt.getTime(), it.vl]
199
                                detailedData << [it.dt.toString().substring(0,10), it.vl]
200
                        }
201
                } else if (graphToShow.timeFrameType == "month") {
202
                        db_sql.eachRow "select date_trunc('month',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('month',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('month',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('month',ld.date_recieved) order by date_trunc('month',ld.date_recieved) asc", {
203
                                data << [it.dt.getTime(), it.vl]
204
                                detailedData << [it.dt.toString().substring(0,7), it.vl]
205
                        }
206
                } else if (graphToShow.timeFrameType == "year") {
207
                        db_sql.eachRow "select date_trunc('year',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('year',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('year',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('year',ld.date_recieved) order by date_trunc('year',ld.date_recieved) asc", {
208
                                data << [it.dt.getTime(), it.vl]
209
                                detailedData << [it.dt.toString().substring(0,4), it.vl]
210
                        }
211
                } else if (graphToShow.timeFrameType == "custom") {
212
                        def innerTimeFrame = (long)((graphToShow.timeFrameEnd.getTime() - graphToShow.timeFrameStart.getTime()) / graphToShow.innerSections)
213
                        innerTimeFrameHalf = (long)(innerTimeFrame/2)
214
                        query = ""
215
                        for(int i = 0; i < graphToShow.innerSections;i++) {
216
                                def currDateMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame) + innerTimeFrame/2
217
                                def currDate = new Date((long)currDateMillis)
218
                                def currDateStartMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame)
219
                                def currDateStart = new Date((long)currDateStartMillis)
220
                                def currDateStopMillis = graphToShow.timeFrameStart.getTime() + ((i+1)*innerTimeFrame)
221
                                def currDateStop = new Date((long)currDateStopMillis)
222
                                query += "(select timestamp without time zone '" + currDate.toTimestamp() + "' dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and ld.date_recieved >= '" + currDateStart.toTimestamp() + "' and ld.date_recieved <= '" + currDateStop.toTimestamp() + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ")) "
223
                                if (i < (graphToShow.innerSections-1) ) query += "union "
224
                        }
225
                        query += "order by dt asc"
226
                        db_sql.eachRow(query) {
227
                                data << [it.dt.getTime(), it.vl]
228
                                detailedData << [(new Date(it.dt.getTime()-innerTimeFrameHalf)).toTimestamp().toString(),(new Date(it.dt.getTime()+innerTimeFrameHalf)).toTimestamp().toString(), it.vl]
229
                        }
230
                }
231
                
232
                [data:data, detailedData:detailedData, graph:graphToShow]
233
                
234
        }
235
        
236
        def exportReport = {
237
                
238
                if(params['exportType'] == 'pdf') {
239
                        exportReportPdf(params:params)
240
                } else if(params['exportType'] == 'xml') {
241
                        exportReportXml(params:params)
242
                } else if(params['exportType'] == 'csv') {
243
                        exportReportCsv(params:params)
244
                } else {
245
                        flash['errors'] = "<p>The specified export type is not valid!</p>"
246
                        redirect(action:'graphs')
247
                }
248
                
249
                return true
250
                
251
        }
252
        
253
        def exportReportPdf = {
254
                
255
                redirect(controller: 'pdf', action: 'pdfLink', params:[pdfController: 'pdf',pdfAction:'reportToPdf',reportId:params['reportId']])
256
                
257
        }
258
        
259
        def exportReportXml = {
260
                
261
                def cont = ""
262
                
263
                def graphToShow = EneGraph.get(params['reportId'])
264
                String query
265
                
266
                List data = new ArrayList()
267
                List detailedData = new ArrayList()
268
                def results
269
                def innerTimeFrameHalf = 0;
270
                def db_sql = new Sql(dataSource)
271
                
272
                if(graphToShow.timeFrameType == "day") {
273
                        db_sql.eachRow "select date_trunc('day',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('day',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('day',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('day',ld.date_recieved) order by date_trunc('day',ld.date_recieved) asc", {
274
                                data << [it.dt.getTime(), it.vl]
275
                                detailedData << [it.dt.toString().substring(0,10), it.vl]
276
                        }
277
                } else if (graphToShow.timeFrameType == "month") {
278
                        db_sql.eachRow "select date_trunc('month',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('month',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('month',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('month',ld.date_recieved) order by date_trunc('month',ld.date_recieved) asc", {
279
                                data << [it.dt.getTime(), it.vl]
280
                                detailedData << [it.dt.toString().substring(0,7), it.vl]
281
                        }
282
                } else if (graphToShow.timeFrameType == "year") {
283
                        db_sql.eachRow "select date_trunc('year',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('year',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('year',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('year',ld.date_recieved) order by date_trunc('year',ld.date_recieved) asc", {
284
                                data << [it.dt.getTime(), it.vl]
285
                                detailedData << [it.dt.toString().substring(0,4), it.vl]
286
                        }
287
                } else if (graphToShow.timeFrameType == "custom") {
288
                        def innerTimeFrame = (long)((graphToShow.timeFrameEnd.getTime() - graphToShow.timeFrameStart.getTime()) / graphToShow.innerSections)
289
                        innerTimeFrameHalf = (long)(innerTimeFrame/2)
290
                        query = ""
291
                        for(int i = 0; i < graphToShow.innerSections;i++) {
292
                                def currDateMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame) + innerTimeFrame/2
293
                                def currDate = new Date((long)currDateMillis)
294
                                def currDateStartMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame)
295
                                def currDateStart = new Date((long)currDateStartMillis)
296
                                def currDateStopMillis = graphToShow.timeFrameStart.getTime() + ((i+1)*innerTimeFrame)
297
                                def currDateStop = new Date((long)currDateStopMillis)
298
                                query += "(select timestamp without time zone '" + currDate.toTimestamp() + "' dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and ld.date_recieved >= '" + currDateStart.toTimestamp() + "' and ld.date_recieved <= '" + currDateStop.toTimestamp() + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ")) "
299
                                if (i < (graphToShow.innerSections-1) ) query += "union "
300
                        }
301
                        query += "order by dt asc"
302
                        db_sql.eachRow(query) {
303
                                data << [it.dt.getTime(), it.vl]
304
                                detailedData << [(new Date(it.dt.getTime()-innerTimeFrameHalf)).toTimestamp().toString(),(new Date(it.dt.getTime()+innerTimeFrameHalf)).toTimestamp().toString(), it.vl]
305
                        }
306
                }
307
                
308
                        
309
                if(graphToShow.timeFrameType == 'custom') {
310
                        cont += "<report>" + "\n"
311
                        cont += "\t<desc>" + graphToShow.name + "</desc>" + "\n"
312
                        cont += "\t<type>" + graphToShow.type + "</type>" + "\n"
313
                        cont += "\t<device>" + graphToShow.deviceId + "</device>" + "\n"
314
                        cont += "\t<timeframe>" + "\n"
315
                        cont += "\t\t<type>" + graphToShow.timeFrameType + "</type>" + "\n"
316
                        cont += "\t\t<start>" + graphToShow.timeFrameStart + "</start>" + "\n"
317
                        cont += "\t\t<end>" + graphToShow.timeFrameEnd + "</end>" + "\n"
318
                        cont += "\t\t<innernum>" + graphToShow.innerSections + "</innernum>" + "\n"
319
                        cont += "\t</timeframe>" + "\n"
320
                        cont += "\t<values>" + "\n"
321
                        detailedData.each {
322
                                cont += "\t\t<inner>" + "\n"
323
                                cont += "\t\t\t<start>" + it[0] + "</start>" + "\n"
324
                                cont += "\t\t\t<end>" + it[1] + "</end>" + "\n"
325
                                cont += "\t\t\t<value>" + it[2] + "</value>" + "\n"
326
                                cont += "\t\t</inner>" + "\n"
327
                        }
328
                        cont += "\t</values>" + "\n"
329
                        cont += "</report>" + "\n"
330
                } else {
331
                        cont += "<report>" + "\n"
332
                        cont += "\t<desc>" + graphToShow.name + "</desc>" + "\n"
333
                        cont += "\t<type>" + graphToShow.type + "</type>" + "\n"
334
                        cont += "\t<device>" + graphToShow.deviceId + "</device>" + "\n"
335
                        cont += "\t<timeframe>" + "\n"
336
                        cont += "\t\t<type>" + graphToShow.timeFrameType + "</type>" + "\n"
337
                        cont += "\t\t<start>" + graphToShow.timeFrameStart + "</start>" + "\n"
338
                        cont += "\t\t<end>" + graphToShow.timeFrameEnd + "</end>" + "\n"
339
                        cont += "\t</timeframe>" + "\n"
340
                        cont += "\t<values>" + "\n"
341
                        detailedData.each {
342
                                cont += "\t\t<inner>" + "\n"
343
                                cont += "\t\t\t<time>" + it[0] + "</time>" + "\n"
344
                                cont += "\t\t\t<value>" + it[1] + "</value>" + "\n"
345
                                cont += "\t\t</inner>" + "\n"
346
                        }
347
                        cont += "\t</values>" + "\n"
348
                        cont += "</report>" + "\n"
349
                }
350
                
351
                render(text: cont,contentType:"text/xml",encoding:"UTF-8")
352
                
353
        }
354
        
355
        def exportReportCsv = {
356
                
357
                def cont = ""
358
                
359
                def graphToShow = EneGraph.get(params['reportId'])
360
                String query
361
                
362
                List data = new ArrayList()
363
                List detailedData = new ArrayList()
364
                def results
365
                def innerTimeFrameHalf = 0;
366
                def db_sql = new Sql(dataSource)
367
                
368
                if(graphToShow.timeFrameType == "day") {
369
                        db_sql.eachRow "select date_trunc('day',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('day',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('day',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('day',ld.date_recieved) order by date_trunc('day',ld.date_recieved) asc", {
370
                                data << [it.dt.getTime(), it.vl]
371
                                detailedData << [it.dt.toString().substring(0,10), it.vl]
372
                        }
373
                } else if (graphToShow.timeFrameType == "month") {
374
                        db_sql.eachRow "select date_trunc('month',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('month',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('month',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('month',ld.date_recieved) order by date_trunc('month',ld.date_recieved) asc", {
375
                                data << [it.dt.getTime(), it.vl]
376
                                detailedData << [it.dt.toString().substring(0,7), it.vl]
377
                        }
378
                } else if (graphToShow.timeFrameType == "year") {
379
                        db_sql.eachRow "select date_trunc('year',ld.date_recieved) dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and date_trunc('year',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('year',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ") group by date_trunc('year',ld.date_recieved) order by date_trunc('year',ld.date_recieved) asc", {
380
                                data << [it.dt.getTime(), it.vl]
381
                                detailedData << [it.dt.toString().substring(0,4), it.vl]
382
                        }
383
                } else if (graphToShow.timeFrameType == "custom") {
384
                        def innerTimeFrame = (long)((graphToShow.timeFrameEnd.getTime() - graphToShow.timeFrameStart.getTime()) / graphToShow.innerSections)
385
                        innerTimeFrameHalf = (long)(innerTimeFrame/2)
386
                        query = ""
387
                        for(int i = 0; i < graphToShow.innerSections;i++) {
388
                                def currDateMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame) + innerTimeFrame/2
389
                                def currDate = new Date((long)currDateMillis)
390
                                def currDateStartMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame)
391
                                def currDateStart = new Date((long)currDateStartMillis)
392
                                def currDateStopMillis = graphToShow.timeFrameStart.getTime() + ((i+1)*innerTimeFrame)
393
                                def currDateStop = new Date((long)currDateStopMillis)
394
                                query += "(select timestamp without time zone '" + currDate.toTimestamp() + "' dt, " + graphToShow['type'] + "(to_number(ld.reported_data,'9999999999D99999')) vl from logged_data as ld where (device_id = '" + graphToShow['deviceId'] + "' and ld.date_recieved >= '" + currDateStart.toTimestamp() + "' and ld.date_recieved <= '" + currDateStop.toTimestamp() + "' and date_part('hour',ld.date_recieved) >= " + graphToShow.advHoursTimeFrameStart + " and date_part('hour',ld.date_recieved) <= " + graphToShow.advHoursTimeFrameEnd + " and date_part('day',ld.date_recieved) >= " + graphToShow.advDayTimeFrameStart + " and date_part('day',ld.date_recieved) <= " + graphToShow.advDayTimeFrameEnd + " and date_part('month',ld.date_recieved) >= " + graphToShow.advMonthTimeFrameStart + " and date_part('month',ld.date_recieved) <= " + graphToShow.advMonthTimeFrameEnd + ")) "
395
                                if (i < (graphToShow.innerSections-1) ) query += "union "
396
                        }
397
                        query += "order by dt asc"
398
                        db_sql.eachRow(query) {
399
                                data << [it.dt.getTime(), it.vl]
400
                                detailedData << [(new Date(it.dt.getTime()-innerTimeFrameHalf)).toTimestamp().toString(),(new Date(it.dt.getTime()+innerTimeFrameHalf)).toTimestamp().toString(), it.vl]
401
                        }
402
                }
403
                
404
                        
405
                if(graphToShow.timeFrameType == 'custom') {
406
                        detailedData.each {
407
                                cont += it[0] + "," + it[1] + "," + it[2] + "\n"
408
                        }
409
                } else {
410
                        detailedData.each {
411
                                cont += it[0] + "," + it[1] + "\n"
412
                        }
413
                }
414
                
415
                render(text: cont,contentType:"text/csv",encoding:"UTF-8")
416
                
417
        }
418
        
419
        def deleteGraph = {
420
        
421
                if(params['id']) {
422
                        
423
                        def graphToDelete = EneGraph.get(params['id'])
424
                        if(graphToDelete) {
425
                                
426
                                def idOfDeleted = graphToDelete.id
427
                                
428
                                try {
429
                                        graphToDelete.delete(flush:true)
430
                                } catch (Exception e) {
431
                                        flash['errors'] = "<p>Cannot remove this graph. This graph is part of a <b>combined</b> graph.</p><p>You should remove the <b>combined</b> graph first.</p>"
432
                                        redirect(action:'graphs')
433
                                        return false
434
                                }
435
                                
436
                                flash['confirms'] = "<p>Graph deleted successfully.</p>"
437
                                redirect(action:'graphs')
438
                                return true
439
                                
440
                        } else {
441
                                flash['errors'] = "<p>No graph was deleted. Graph ID invalid!</p>"
442
                                redirect(action:'graphs')
443
                                return false
444
                        }
445
                        
446
                } else {
447
                
448
                        flash['errors'] = "<p>No graph was deleted. Graph ID not specified!</p>"
449
                        redirect(action:'graphs')
450
                        return false
451
                
452
                }        
453
                
454
        }
455
        
456
        def newCombGraph = {
457
                
458
                def graphs = EneGraph.findAllByHwSet(session.hwSet)
459
                def otherGraphs = EneGraph.findAllByHwSetNotEqual(session.hwSet)
460
                
461
                [graphs:graphs, otherGraphs: otherGraphs]
462
                
463
        }
464
        
465
        def newCombGraphDo = {
466
                
467
                List selgrph = new ArrayList()
468
                selgrph = params.list('graphsToCombine')
469
                
470
                if(selgrph.size() < 2) {
471
                        flash.errors = "<p>You have to select at least 2 single device graphs to combine!</p>"
472
                        redirect(action:'newCombGraph')
473
                        return false
474
                }
475
                
476
                CombGraph newOne = new CombGraph(
477
                        name: params.newCombGraphName
478
                )
479
                
480
                selgrph.each {
481
                        newOne.addToGraphs(EneGraph.get(it))
482
                }
483
                
484
                if(newOne.validate()) {
485
                        newOne.save()
486
                        flash.confirms = "<p>New combined graph added succesfully!</p>"
487
                        redirect(action:'graphs')
488
                        return true
489
                } else {
490
                        flash.errors = "<p>New graph could not be created. Check if all fields are filled and if values are valid.</p>"
491
                        redirect(action:'newCombGraph')
492
                        return false
493
                }
494
                
495
        }
496
        
497
        def deleteCombGraph = {
498
                
499
                if(params['id']) {
500
                        
501
                        def graphToDelete = CombGraph.get(params['id'])
502
                        if(graphToDelete) {
503
                                
504
                                graphToDelete.delete()
505
                                flash['confirms'] = "<p>Graph deleted successfully.</p>"
506
                                redirect(action:'graphs')
507
                                return true
508
                                
509
                        } else {
510
                                flash['errors'] = "<p>No graph was deleted. Graph ID invalid!</p>"
511
                                redirect(action:'graphs')
512
                                return false
513
                        }
514
                        
515
                } else {
516
                
517
                        flash['errors'] = "<p>No graph was deleted. Graph ID not specified!</p>"
518
                        redirect(action:'graphs')
519
                        return false
520
                
521
                }
522
                
523
        }
524
        
525
        def editCombGraph = {
526
                
527
                CombGraph chosenGraph = CombGraph.get(params['id'])
528
                List allGraphs = new ArrayList()
529
                EneGraph.list().each {
530
                        if(chosenGraph.graphs.contains(it)) {
531
                                allGraphs << [gr: it, sl: true]
532
                        } else {
533
                                allGraphs << [gr: it, sl: false]
534
                        }
535
                }
536
                
537
                [chosenGraph: chosenGraph, graphs: allGraphs]
538
                
539
        }
540
        
541
        def editCombGraphDo = {
542
                
543
                CombGraph chosenGraph = CombGraph.get(params['graphId'])
544
                
545
                chosenGraph.name = params['editCombGraphName']
546
                List newGraphs = new ArrayList()
547
                newGraphs = params.list('graphsToCombine')
548
                
549
                if(newGraphs.size() < 2) {
550
                        flash.errors = "<p>You have to select at least 2 single device graphs to combine!</p>"
551
                        redirect(action:'editCombGraph',params:[id: params.graphId])
552
                        return false
553
                }
554
                
555
                List graphsToRemove = new ArrayList()
556
                
557
                chosenGraph.graphs.each {
558
                        graphsToRemove << it
559
                }
560
                
561
                graphsToRemove.each {
562
                        chosenGraph.removeFromGraphs(it)
563
                }
564
                
565
                newGraphs.each {
566
                        chosenGraph.addToGraphs(EneGraph.get(it))
567
                }
568
                
569
                if(chosenGraph.validate()) {
570
                        if(chosenGraph.save(flush:true)) {
571
                                flash.confirms = "<p>Graph edited succesfully!</p>"
572
                                redirect(action:'graphs')
573
                                return true
574
                        } else {
575
                                flash.errors = "<p>Graph could not be edited. Check if all fields are filled and if values are valid.</p>"
576
                                redirect(action:'editCombGraph', params:[id: params.graphId])
577
                                return false
578
                        }
579
                } else {
580
                        flash.errors = "<p>Graph could not be edited. Check if all fields are filled and if values are valid.</p>"
581
                        redirect(action:'editCombGraph', params:[id: params.graphId])
582
                        return false
583
                }
584
                
585
        }
586
        
587
        def showCombGraph = {
588
                
589
                if(params.id) {
590
                        CombGraph chGrph = CombGraph.get(params.id)
591
                        if(chGrph) {
592
                                
593
                                List datas = new ArrayList()
594
                                
595
                                chGrph.graphs.each {
596
                                        datas << "{label: '" + it.name + "', data: " + graphDataService.getGraphData(it).toString() + "}"
597
                                }
598
                                
599
                                return [data: datas]
600
                                
601
                        } else {
602
                                flash.errors = "<p>Invalid graph ID!</p>"
603
                                redirect(action:'graphs')
604
                                return false
605
                        }
606
                } else {
607
                        flash.errors = "<p>Invalid graph ID!</p>"
608
                        redirect(action:'graphs')
609
                        return false
610
                }
611
                
612
        }
613
        
614
        def makeCombReport = {
615
                
616
                
617
                
618
        }
619
        
620
}