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