97 |
97 |
def showGraph = {
|
98 |
98 |
|
99 |
99 |
def graphToShow = EneGraph.get(params['graphId'])
|
|
100 |
String query
|
100 |
101 |
|
101 |
102 |
List data = new ArrayList()
|
102 |
103 |
def results
|
103 |
104 |
def db_sql = new Sql(dataSource)
|
104 |
105 |
|
105 |
106 |
if(graphToShow.timeFrameType == "day") {
|
106 |
|
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 (date_trunc('day',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('day',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "') group by date_trunc('day',ld.date_recieved) order by date_trunc('day',ld.date_recieved) asc", {
|
|
107 |
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 + "') group by date_trunc('day',ld.date_recieved) order by date_trunc('day',ld.date_recieved) asc", {
|
107 |
108 |
data << [it.dt.getTime(), it.vl]
|
108 |
109 |
}
|
109 |
110 |
} else if (graphToShow.timeFrameType == "month") {
|
110 |
|
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 (date_trunc('month',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('month',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "') group by date_trunc('month',ld.date_recieved) order by date_trunc('month',ld.date_recieved) asc", {
|
|
111 |
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 + "') group by date_trunc('month',ld.date_recieved) order by date_trunc('month',ld.date_recieved) asc", {
|
111 |
112 |
data << [it.dt.getTime(), it.vl]
|
112 |
113 |
}
|
113 |
114 |
} else if (graphToShow.timeFrameType == "year") {
|
114 |
|
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 (date_trunc('year',ld.date_recieved) >= '" + graphToShow.timeFrameStart + "' and date_trunc('year',ld.date_recieved) <= '" + graphToShow.timeFrameEnd + "') group by date_trunc('year',ld.date_recieved) order by date_trunc('year',ld.date_recieved) asc", {
|
|
115 |
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 + "') group by date_trunc('year',ld.date_recieved) order by date_trunc('year',ld.date_recieved) asc", {
|
115 |
116 |
data << [it.dt.getTime(), it.vl]
|
116 |
117 |
}
|
|
118 |
} else if (graphToShow.timeFrameType == "custom") {
|
|
119 |
def innerTimeFrame = (long)((graphToShow.timeFrameEnd.getTime() - graphToShow.timeFrameStart.getTime()) / graphToShow.innerSections)
|
|
120 |
query = ""
|
|
121 |
for(int i = 0; i < graphToShow.innerSections;i++) {
|
|
122 |
def currDateMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame) + innerTimeFrame/2
|
|
123 |
def currDate = new Date((long)currDateMillis)
|
|
124 |
def currDateStartMillis = graphToShow.timeFrameStart.getTime() + (i*innerTimeFrame)
|
|
125 |
def currDateStart = new Date((long)currDateStartMillis)
|
|
126 |
def currDateStopMillis = graphToShow.timeFrameStart.getTime() + ((i+1)*innerTimeFrame)
|
|
127 |
def currDateStop = new Date((long)currDateStopMillis)
|
|
128 |
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() + "')) "
|
|
129 |
if (i < (graphToShow.innerSections-1) ) query += "union "
|
|
130 |
}
|
|
131 |
query += "order by dt asc"
|
|
132 |
db_sql.eachRow(query) {
|
|
133 |
data << [it.dt.getTime(), it.vl]
|
|
134 |
}
|
117 |
135 |
}
|
118 |
136 |
|
119 |
137 |
[data:data]
|