root / hci / trunk / eneraptor-web-app / grails-app / controllers / com / eneraptor / hci / StatisticsController.groovy @ 52
History | View | Annotate | Download (29.3 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 | |||
10 | 3 | alexbesir | def main = { }
|
11 | 42 | alexbesir | |
12 | def graphs = {
|
||
13 | |||
14 | List savedGraphs = new ArrayList() |
||
15 | |||
16 | savedGraphs = EneGraph.list() |
||
17 | |||
18 | [savedGraphs: savedGraphs]
|
||
19 | |||
20 | } |
||
21 | |||
22 | def newGraph = {
|
||
23 | |||
24 | List graphTypes = ["min","max","avg","sum"] |
||
25 | List graphTypesFriendly = ["Minimum value","Maximum value","Average value","Cumulative"] |
||
26 | |||
27 | List devices = new ArrayList() |
||
28 | List devicesFriendly = new ArrayList() |
||
29 | |||
30 | DeviceInfo.list().each { |
||
31 | devices << it.deviceId
|
||
32 | devicesFriendly << it.friendlyName
|
||
33 | } |
||
34 | |||
35 | [graphTypes:graphTypes,graphTypesFriendly:graphTypesFriendly, devices:devices, devicesFriendly:devicesFriendly] |
||
36 | |||
37 | } |
||
38 | |||
39 | def newGraphDo = {
|
||
40 | |||
41 | String errors = "" |
||
42 | Date chosenTimeFrameStart = null |
||
43 | Date chosenTimeFrameEnd = null |
||
44 | |||
45 | if(params['timeFrameType'] == "day") { |
||
46 | chosenTimeFrameStart = params['dayMainTimeFrameStart']
|
||
47 | chosenTimeFrameEnd = params['dayMainTimeFrameEnd']
|
||
48 | } else if(params['timeFrameType'] == "month") { |
||
49 | chosenTimeFrameStart = params['monthMainTimeFrameStart']
|
||
50 | chosenTimeFrameEnd = params['monthMainTimeFrameEnd']
|
||
51 | } else if(params['timeFrameType'] == "year") { |
||
52 | chosenTimeFrameStart = params['yearMainTimeFrameStart']
|
||
53 | chosenTimeFrameEnd = params['yearMainTimeFrameEnd']
|
||
54 | } else if(params['timeFrameType'] == "custom") { |
||
55 | chosenTimeFrameStart = params['customMainTimeFrameStart']
|
||
56 | chosenTimeFrameEnd = params['customMainTimeFrameEnd']
|
||
57 | } else {
|
||
58 | errors += "<p>Time frame type invalid!</p>"
|
||
59 | } |
||
60 | |||
61 | def newEneGraph = new EneGraph( |
||
62 | name: params['graphName'], |
||
63 | type: params['graphType'], |
||
64 | timeFrameType: params['timeFrameType'], |
||
65 | timeFrameStart: chosenTimeFrameStart,
|
||
66 | timeFrameEnd: chosenTimeFrameEnd,
|
||
67 | innerSections: params['customMainTimeFrameInnerSections'], |
||
68 | advHoursTimeFrameStart: params['advHoursStart'], |
||
69 | advHoursTimeFrameEnd: params['advHoursEnd'], |
||
70 | advDayTimeFrameStart: params['advDayStart'], |
||
71 | advDayTimeFrameEnd: params['advDayEnd'], |
||
72 | advMonthTimeFrameStart: params['advMonthStart'], |
||
73 | advMonthTimeFrameEnd: params['advMonthEnd'], |
||
74 | deviceId: params['deviceId'] |
||
75 | ) |
||
76 | |||
77 | if(!newEneGraph.validate()) {
|
||
78 | errors += "<p>Graph could not be created - there were some input errors. Please check if all input values are valid.</p>"
|
||
79 | flash['errors'] = errors
|
||
80 | redirect(action:'newGraph') |
||
81 | } else {
|
||
82 | if(newEneGraph.save()){
|
||
83 | flash['confirms'] = "<p>Graph created succesfully!</p>" |
||
84 | redirect(action:'newGraph') |
||
85 | } else {
|
||
86 | flash['errors'] = "<p>There was an error while saving graph.</p>" |
||
87 | redirect(action:'newGraph') |
||
88 | } |
||
89 | } |
||
90 | |||
91 | return true |
||
92 | |||
93 | } |
||
94 | |||
95 | def showGraph = {
|
||
96 | |||
97 | 50 | alexbesir | def graphToShow = EneGraph.get(params['id']) |
98 | 43 | alexbesir | String query
|
99 | 42 | alexbesir | |
100 | List data = new ArrayList() |
||
101 | def results
|
||
102 | def db_sql = new Sql(dataSource) |
||
103 | |||
104 | if(graphToShow.timeFrameType == "day") { |
||
105 | 50 | alexbesir | 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", { |
106 | 42 | alexbesir | data << [it.dt.getTime(), it.vl] |
107 | } |
||
108 | } else if (graphToShow.timeFrameType == "month") { |
||
109 | 50 | alexbesir | 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", { |
110 | 42 | alexbesir | data << [it.dt.getTime(), it.vl] |
111 | } |
||
112 | } else if (graphToShow.timeFrameType == "year") { |
||
113 | 50 | alexbesir | 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", { |
114 | 42 | alexbesir | data << [it.dt.getTime(), it.vl] |
115 | } |
||
116 | 43 | alexbesir | } else if (graphToShow.timeFrameType == "custom") { |
117 | def innerTimeFrame = (long)((graphToShow.timeFrameEnd.getTime() - graphToShow.timeFrameStart.getTime()) / graphToShow.innerSections) |
||
118 | query = ""
|
||
119 | for(int i = 0; i < graphToShow.innerSections;i++) { |
||
120 | def currDateMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame) + innerTimeFrame/2 |
||
121 | def currDate = new Date((long)currDateMillis) |
||
122 | def currDateStartMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame)
|
||
123 | def currDateStart = new Date((long)currDateStartMillis) |
||
124 | def currDateStopMillis = graphToShow.timeFrameStart.getTime() + ((i+1)*innerTimeFrame) |
||
125 | def currDateStop = new Date((long)currDateStopMillis) |
||
126 | 50 | alexbesir | 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 + ")) " |
127 | 43 | alexbesir | if (i < (graphToShow.innerSections-1) ) query += "union " |
128 | } |
||
129 | query += "order by dt asc"
|
||
130 | db_sql.eachRow(query) { |
||
131 | data << [it.dt.getTime(), it.vl] |
||
132 | } |
||
133 | 42 | alexbesir | } |
134 | |||
135 | [data:data]
|
||
136 | |||
137 | } |
||
138 | 50 | alexbesir | |
139 | def editGraph = {
|
||
140 | |||
141 | EneGraph chosenGraph = EneGraph.get(params['id'])
|
||
142 | |||
143 | List graphTypes = ["min","max","avg","sum"] |
||
144 | List graphTypesFriendly = ["Minimum value","Maximum value","Average value","Cumulative"] |
||
145 | |||
146 | List devices = new ArrayList() |
||
147 | List devicesFriendly = new ArrayList() |
||
148 | |||
149 | DeviceInfo.list().each { |
||
150 | devices << it.deviceId
|
||
151 | devicesFriendly << it.friendlyName
|
||
152 | } |
||
153 | |||
154 | [graph: chosenGraph, graphTypes:graphTypes, graphTypesFriendly:graphTypesFriendly, devices:devices, devicesFriendly:devicesFriendly] |
||
155 | |||
156 | } |
||
157 | |||
158 | def editGraphDo = {
|
||
159 | |||
160 | String errors = "" |
||
161 | |||
162 | EneGraph graphToEdit = EneGraph.get(params['graphId'])
|
||
163 | |||
164 | graphToEdit.name = params['graphName']
|
||
165 | graphToEdit.type = params['graphType']
|
||
166 | graphToEdit.timeFrameType = params['timeFrameType']
|
||
167 | graphToEdit.timeFrameStart = params['mainTimeFrameStart']
|
||
168 | graphToEdit.timeFrameEnd = params['mainTimeFrameEnd']
|
||
169 | graphToEdit.innerSections = params['mainTimeFrameInnerSections'] as int |
||
170 | graphToEdit.advHoursTimeFrameStart = params['advHoursStart'] as int |
||
171 | graphToEdit.advHoursTimeFrameEnd = params['advHoursEnd'] as int |
||
172 | graphToEdit.advDayTimeFrameStart = params['advDayStart'] as int |
||
173 | graphToEdit.advDayTimeFrameEnd = params['advDayEnd'] as int |
||
174 | graphToEdit.advMonthTimeFrameStart = params['advMonthStart'] as int |
||
175 | graphToEdit.advMonthTimeFrameEnd = params['advMonthEnd'] as int |
||
176 | graphToEdit.deviceId = params['deviceId']
|
||
177 | |||
178 | if(!graphToEdit.validate()) {
|
||
179 | errors += "<p>Graph could not be modified - there were some input errors. Please check if all input values are valid.</p>"
|
||
180 | flash['errors'] = errors
|
||
181 | redirect(action:'editGraph') |
||
182 | } else {
|
||
183 | if(graphToEdit.save()){
|
||
184 | flash['confirms'] = "<p>Graph modified succesfully!</p>" |
||
185 | redirect(action:'graphs') |
||
186 | } else {
|
||
187 | flash['errors'] = "<p>There was an error while modifying graph.</p>" |
||
188 | redirect(action:'editGraph') |
||
189 | } |
||
190 | } |
||
191 | |||
192 | return true |
||
193 | |||
194 | } |
||
195 | |||
196 | def newReport = {
|
||
197 | |||
198 | def graphsAll = EneGraph.list()
|
||
199 | def graphs = new ArrayList() |
||
200 | def graphsKeys = new ArrayList() |
||
201 | graphsAll.each { |
||
202 | graphs << it.name
|
||
203 | graphsKeys << it.id
|
||
204 | } |
||
205 | |||
206 | [graphs:graphs, graphsKeys:graphsKeys] |
||
207 | |||
208 | } |
||
209 | |||
210 | def makeReport = {
|
||
211 | |||
212 | def graphToShow = EneGraph.get(params['id']) |
||
213 | String query
|
||
214 | |||
215 | List data = new ArrayList() |
||
216 | List detailedData = new ArrayList() |
||
217 | def results
|
||
218 | def innerTimeFrameHalf = 0; |
||
219 | def db_sql = new Sql(dataSource) |
||
220 | |||
221 | if(graphToShow.timeFrameType == "day") { |
||
222 | 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", { |
||
223 | data << [it.dt.getTime(), it.vl] |
||
224 | detailedData << [it.dt.toString().substring(0,10), it.vl] |
||
225 | } |
||
226 | } else if (graphToShow.timeFrameType == "month") { |
||
227 | 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", { |
||
228 | data << [it.dt.getTime(), it.vl] |
||
229 | detailedData << [it.dt.toString().substring(0,7), it.vl] |
||
230 | } |
||
231 | } else if (graphToShow.timeFrameType == "year") { |
||
232 | 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", { |
||
233 | data << [it.dt.getTime(), it.vl] |
||
234 | detailedData << [it.dt.toString().substring(0,4), it.vl] |
||
235 | } |
||
236 | } else if (graphToShow.timeFrameType == "custom") { |
||
237 | def innerTimeFrame = (long)((graphToShow.timeFrameEnd.getTime() - graphToShow.timeFrameStart.getTime()) / graphToShow.innerSections) |
||
238 | innerTimeFrameHalf = (long)(innerTimeFrame/2) |
||
239 | query = ""
|
||
240 | for(int i = 0; i < graphToShow.innerSections;i++) { |
||
241 | def currDateMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame) + innerTimeFrame/2 |
||
242 | def currDate = new Date((long)currDateMillis) |
||
243 | def currDateStartMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame)
|
||
244 | def currDateStart = new Date((long)currDateStartMillis) |
||
245 | def currDateStopMillis = graphToShow.timeFrameStart.getTime() + ((i+1)*innerTimeFrame) |
||
246 | def currDateStop = new Date((long)currDateStopMillis) |
||
247 | 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 + ")) " |
||
248 | if (i < (graphToShow.innerSections-1) ) query += "union " |
||
249 | } |
||
250 | query += "order by dt asc"
|
||
251 | db_sql.eachRow(query) { |
||
252 | data << [it.dt.getTime(), it.vl] |
||
253 | detailedData << [(new Date(it.dt.getTime()-innerTimeFrameHalf)).toTimestamp().toString(),(new Date(it.dt.getTime()+innerTimeFrameHalf)).toTimestamp().toString(), it.vl] |
||
254 | } |
||
255 | } |
||
256 | |||
257 | [data:data, detailedData:detailedData, graph:graphToShow] |
||
258 | |||
259 | } |
||
260 | |||
261 | def exportReport = {
|
||
262 | |||
263 | if(params['exportType'] == 'pdf') { |
||
264 | exportReportPdf(params:params)
|
||
265 | } else if(params['exportType'] == 'xml') { |
||
266 | exportReportXml(params:params)
|
||
267 | } else if(params['exportType'] == 'csv') { |
||
268 | exportReportCsv(params:params)
|
||
269 | } else {
|
||
270 | flash['errors'] = "<p>The specified export type is not valid!</p>" |
||
271 | redirect(action:'graphs') |
||
272 | } |
||
273 | |||
274 | return true |
||
275 | |||
276 | } |
||
277 | |||
278 | def exportReportPdf = {
|
||
279 | |||
280 | redirect(controller: 'pdf', action: 'pdfLink', params:[pdfController: 'pdf',pdfAction:'reportToPdf',reportId:params['reportId']]) |
||
281 | |||
282 | } |
||
283 | |||
284 | def exportReportXml = {
|
||
285 | |||
286 | def cont = "" |
||
287 | |||
288 | def graphToShow = EneGraph.get(params['reportId']) |
||
289 | String query
|
||
290 | |||
291 | List data = new ArrayList() |
||
292 | List detailedData = new ArrayList() |
||
293 | def results
|
||
294 | def innerTimeFrameHalf = 0; |
||
295 | def db_sql = new Sql(dataSource) |
||
296 | |||
297 | if(graphToShow.timeFrameType == "day") { |
||
298 | 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", { |
||
299 | data << [it.dt.getTime(), it.vl] |
||
300 | detailedData << [it.dt.toString().substring(0,10), it.vl] |
||
301 | } |
||
302 | } else if (graphToShow.timeFrameType == "month") { |
||
303 | 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", { |
||
304 | data << [it.dt.getTime(), it.vl] |
||
305 | detailedData << [it.dt.toString().substring(0,7), it.vl] |
||
306 | } |
||
307 | } else if (graphToShow.timeFrameType == "year") { |
||
308 | 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", { |
||
309 | data << [it.dt.getTime(), it.vl] |
||
310 | detailedData << [it.dt.toString().substring(0,4), it.vl] |
||
311 | } |
||
312 | } else if (graphToShow.timeFrameType == "custom") { |
||
313 | def innerTimeFrame = (long)((graphToShow.timeFrameEnd.getTime() - graphToShow.timeFrameStart.getTime()) / graphToShow.innerSections) |
||
314 | innerTimeFrameHalf = (long)(innerTimeFrame/2) |
||
315 | query = ""
|
||
316 | for(int i = 0; i < graphToShow.innerSections;i++) { |
||
317 | def currDateMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame) + innerTimeFrame/2 |
||
318 | def currDate = new Date((long)currDateMillis) |
||
319 | def currDateStartMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame)
|
||
320 | def currDateStart = new Date((long)currDateStartMillis) |
||
321 | def currDateStopMillis = graphToShow.timeFrameStart.getTime() + ((i+1)*innerTimeFrame) |
||
322 | def currDateStop = new Date((long)currDateStopMillis) |
||
323 | 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 + ")) " |
||
324 | if (i < (graphToShow.innerSections-1) ) query += "union " |
||
325 | } |
||
326 | query += "order by dt asc"
|
||
327 | db_sql.eachRow(query) { |
||
328 | data << [it.dt.getTime(), it.vl] |
||
329 | detailedData << [(new Date(it.dt.getTime()-innerTimeFrameHalf)).toTimestamp().toString(),(new Date(it.dt.getTime()+innerTimeFrameHalf)).toTimestamp().toString(), it.vl] |
||
330 | } |
||
331 | } |
||
332 | |||
333 | |||
334 | if(graphToShow.timeFrameType == 'custom') { |
||
335 | cont += "<report>" + "\n" |
||
336 | cont += "\t<desc>" + graphToShow.name + "</desc>" + "\n" |
||
337 | cont += "\t<type>" + graphToShow.type + "</type>" + "\n" |
||
338 | cont += "\t<device>" + graphToShow.deviceId + "</device>" + "\n" |
||
339 | cont += "\t<timeframe>" + "\n" |
||
340 | cont += "\t\t<type>" + graphToShow.timeFrameType + "</type>" + "\n" |
||
341 | cont += "\t\t<start>" + graphToShow.timeFrameStart + "</start>" + "\n" |
||
342 | cont += "\t\t<end>" + graphToShow.timeFrameEnd + "</end>" + "\n" |
||
343 | cont += "\t\t<innernum>" + graphToShow.innerSections + "</innernum>" + "\n" |
||
344 | cont += "\t</timeframe>" + "\n" |
||
345 | cont += "\t<values>" + "\n" |
||
346 | detailedData.each { |
||
347 | cont += "\t\t<inner>" + "\n" |
||
348 | cont += "\t\t\t<start>" + it[0] + "</start>" + "\n" |
||
349 | cont += "\t\t\t<end>" + it[1] + "</end>" + "\n" |
||
350 | cont += "\t\t\t<value>" + it[2] + "</value>" + "\n" |
||
351 | cont += "\t\t</inner>" + "\n" |
||
352 | } |
||
353 | cont += "\t</values>" + "\n" |
||
354 | cont += "</report>" + "\n" |
||
355 | } else {
|
||
356 | cont += "<report>" + "\n" |
||
357 | cont += "\t<desc>" + graphToShow.name + "</desc>" + "\n" |
||
358 | cont += "\t<type>" + graphToShow.type + "</type>" + "\n" |
||
359 | cont += "\t<device>" + graphToShow.deviceId + "</device>" + "\n" |
||
360 | cont += "\t<timeframe>" + "\n" |
||
361 | cont += "\t\t<type>" + graphToShow.timeFrameType + "</type>" + "\n" |
||
362 | cont += "\t\t<start>" + graphToShow.timeFrameStart + "</start>" + "\n" |
||
363 | cont += "\t\t<end>" + graphToShow.timeFrameEnd + "</end>" + "\n" |
||
364 | cont += "\t</timeframe>" + "\n" |
||
365 | cont += "\t<values>" + "\n" |
||
366 | detailedData.each { |
||
367 | cont += "\t\t<inner>" + "\n" |
||
368 | cont += "\t\t\t<time>" + it[0] + "</time>" + "\n" |
||
369 | cont += "\t\t\t<value>" + it[1] + "</value>" + "\n" |
||
370 | cont += "\t\t</inner>" + "\n" |
||
371 | } |
||
372 | cont += "\t</values>" + "\n" |
||
373 | cont += "</report>" + "\n" |
||
374 | } |
||
375 | |||
376 | render(text: cont,contentType:"text/xml",encoding:"UTF-8") |
||
377 | |||
378 | } |
||
379 | |||
380 | def exportReportCsv = {
|
||
381 | |||
382 | def cont = "" |
||
383 | |||
384 | def graphToShow = EneGraph.get(params['reportId']) |
||
385 | String query
|
||
386 | |||
387 | List data = new ArrayList() |
||
388 | List detailedData = new ArrayList() |
||
389 | def results
|
||
390 | def innerTimeFrameHalf = 0; |
||
391 | def db_sql = new Sql(dataSource) |
||
392 | |||
393 | if(graphToShow.timeFrameType == "day") { |
||
394 | 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", { |
||
395 | data << [it.dt.getTime(), it.vl] |
||
396 | detailedData << [it.dt.toString().substring(0,10), it.vl] |
||
397 | } |
||
398 | } else if (graphToShow.timeFrameType == "month") { |
||
399 | 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", { |
||
400 | data << [it.dt.getTime(), it.vl] |
||
401 | detailedData << [it.dt.toString().substring(0,7), it.vl] |
||
402 | } |
||
403 | } else if (graphToShow.timeFrameType == "year") { |
||
404 | 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", { |
||
405 | data << [it.dt.getTime(), it.vl] |
||
406 | detailedData << [it.dt.toString().substring(0,4), it.vl] |
||
407 | } |
||
408 | } else if (graphToShow.timeFrameType == "custom") { |
||
409 | def innerTimeFrame = (long)((graphToShow.timeFrameEnd.getTime() - graphToShow.timeFrameStart.getTime()) / graphToShow.innerSections) |
||
410 | innerTimeFrameHalf = (long)(innerTimeFrame/2) |
||
411 | query = ""
|
||
412 | for(int i = 0; i < graphToShow.innerSections;i++) { |
||
413 | def currDateMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame) + innerTimeFrame/2 |
||
414 | def currDate = new Date((long)currDateMillis) |
||
415 | def currDateStartMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame)
|
||
416 | def currDateStart = new Date((long)currDateStartMillis) |
||
417 | def currDateStopMillis = graphToShow.timeFrameStart.getTime() + ((i+1)*innerTimeFrame) |
||
418 | def currDateStop = new Date((long)currDateStopMillis) |
||
419 | 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 + ")) " |
||
420 | if (i < (graphToShow.innerSections-1) ) query += "union " |
||
421 | } |
||
422 | query += "order by dt asc"
|
||
423 | db_sql.eachRow(query) { |
||
424 | data << [it.dt.getTime(), it.vl] |
||
425 | detailedData << [(new Date(it.dt.getTime()-innerTimeFrameHalf)).toTimestamp().toString(),(new Date(it.dt.getTime()+innerTimeFrameHalf)).toTimestamp().toString(), it.vl] |
||
426 | } |
||
427 | } |
||
428 | |||
429 | |||
430 | if(graphToShow.timeFrameType == 'custom') { |
||
431 | detailedData.each { |
||
432 | cont += it[0] + "," + it[1] + "," + it[2] + "\n" |
||
433 | } |
||
434 | } else {
|
||
435 | detailedData.each { |
||
436 | cont += it[0] + "," + it[1] + "\n" |
||
437 | } |
||
438 | } |
||
439 | |||
440 | render(text: cont,contentType:"text/csv",encoding:"UTF-8") |
||
441 | |||
442 | } |
||
443 | 3 | alexbesir | |
444 | } |