Revision 43 hci/trunk/eneraptor-web-app/grails-app/controllers/com/eneraptor/hci/StatisticsController.groovy

View differences:

StatisticsController.groovy
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]

Also available in: Unified diff