gt: 					for(int i = 0; i < deviceIds.size(); ++i) {
context: package com.homeCenter.service;
  
  import java.util.ArrayList;
  import java.util.Calendar;
  import java.util.Date;
  import java.util.HashMap;
  
  import com.almworks.sqlite4java.SQLiteConnection;
  import com.almworks.sqlite4java.SQLiteException;
  import com.almworks.sqlite4java.SQLiteJob;
  import com.almworks.sqlite4java.SQLiteStatement;
  import com.google.gson.JsonArray;
  import com.google.gson.JsonElement;
  import com.google.gson.JsonObject;
  import com.homeCenter.Base;
  import com.homeCenter.Room;
  import com.homeCenter.RoomManagement;
  import com.homeCenter.common.Log;
  import com.homeCenter.common.Util;
  import com.homeCenter.db.Database;
  import com.homeCenter.io.Device;
  import com.homeCenter.io.DeviceRef;
  import com.homeCenter.io.DeviceType;
  import com.homeCenter.io.Radiator;
  import com.homeCenter.io.Thermistor;
  
  public class StatisticService extends Base implements RestService {
  	protected String[] supportedTables;
  	static private HashMap<String, StatisticResultSet> queryCache = new HashMap<String, StatisticResultSet>();
  	
  	public static long VALID_DURATION = 1000*60*5;
  
  	public StatisticService(Database db) {
  		super(db);
  		
  		this.supportedTables = new String[] {Radiator.TABLE_NAME, Thermistor.TABLE_NAME};
  	}
  
  	@Override
  	public JsonObject processQuery(String[] processQueue, JsonObject jQuery) {
  		JsonObject result = new JsonObject();
  		switch(processQueue[2]) {
          case "getTables":
          	result = this.getMetadata();
          	break;
          case "query":
          	JsonObject query = jQuery.get("query").getAsJsonObject();
          	result = (JsonObject) this.query(query).toJson();
          	break;
          case "suggestValue":
          	String tableName = jQuery.get("tableName").getAsString();
          	String columnName = jQuery.get("columnName").getAsString();
          	String searchTerm = jQuery.get("value").getAsString();
          	ArrayList<String> data = this.suggestValue(tableName, columnName, searchTerm);
          	JsonArray list = new JsonArray();
          	for(int i = 0; i < data.size(); ++i)
          		list.add(data.get(i));
          	result.add("list", list);
          	break;
          }
  		return result;
  	}
  	
  	@SuppressWarnings("unchecked")
  	private ArrayList<String> suggestValue(final String tableName, final String columnName, final String searchTerm) {
  		final int max = 10;
  		return (ArrayList<String>) this.db.addJob(new SQLiteJob<Object>() {			
  			protected Object job(SQLiteConnection connection) throws SQLiteException {
  				SQLiteStatement stmt = null;
  				ArrayList<String> result = new ArrayList<String>();
  		    	
  				try {
  					stmt = connection.prepare("SELECT " + columnName + " FROM " + tableName + " GROUP BY " + columnName);					
  					while(stmt.step() && result.size() < max) {
  						String value = stmt.columnString(0);
  						if(value.toLowerCase().contains(searchTerm.toLowerCase()))
  							result.add(stmt.columnString(0));
  					}
  				} catch(SQLiteException e) {
  					Log.error(e);
  				} finally {
  					stmt.dispose();
  				}
  				return result;
  			}
  		});
  	}
  
  	public JsonObject getMetadata() {
  		JsonObject result = new JsonObject();
  		
  		JsonArray tables = new JsonArray();
  		for(int i = 0; i < this.supportedTables.length; ++i) {
  			JsonObject table = new JsonObject();
  			JsonArray tableColumns = new JsonArray();
  			ResultSetColumnList columns = this.getColumns(this.supportedTables[i]);
  			for(int j = 0; j < columns.size(); ++j) {
  				tableColumns.add(columns.get(j).toJson());
  			}
  			
  			table.addProperty("name", this.supportedTables[i]);
  			table.add("columns", tableColumns);
  			tables.add(table);
  		}
  		result.add("tables", tables);
  		
  		return result;
  	}
  	
  	/*
  	 * {
  	 * 	dataSource: {
  	 * 		tableName: ""
  	 * 	},
  	 * 	axis: {
  	 * 		label: []
  	 * 		value: []
  	 * 	},
  	 * 	filter: []
  	 * }
  	 */
  	public StatisticResultSet query(JsonObject query) {
  		StatisticResultSet cachedQuery = this.getCachedQuery(query);
  		if(cachedQuery != null)
  			return cachedQuery;
  		
  		final StatisticResultSet resultSet = new StatisticResultSet();
  		JsonObject dataSource = query.get("dataSource").getAsJsonObject();
  		JsonObject axis = query.get("axis").getAsJsonObject();
  		JsonArray axisLabel = axis.get("label").getAsJsonArray();
  		JsonArray axisValue = axis.get("value").getAsJsonArray();
  		JsonArray filter = query.get("filter").getAsJsonArray();
  		
  		if(dataSource.get("tableName").isJsonNull()) {
  			return resultSet;
  		}
  
  		final String tableName = dataSource.get("tableName").getAsString();
  		final String[] columnsLabelAxis = this.parseColumnNames(axisLabel);
  		final String[] columnsValueAxis = this.parseColumnNames(axisValue);
  		final ResultSetColumnList columnMetadata = this.getColumns(tableName);
  		
  		for(int i = 0; i < columnsLabelAxis.length; ++i)
  			resultSet.addLabelColumn(columnsLabelAxis[i]);
  
  		for(int i = 0; i < columnsValueAxis.length; ++i)
  			resultSet.addValueColumn(columnsValueAxis[i]);
  		
  		// process query
  		if(columnsLabelAxis.length > 0 || columnsValueAxis.length > 0) {
  			final StatisticService self = this;
  			this.db.addJob(new SQLiteJob<Object>() {			
  				protected Object job(SQLiteConnection connection) throws SQLiteException {
  					SQLiteStatement stmt = null;
  			    	
  					try {
  						String sqlQuery = "SELECT ";
  						if(columnsLabelAxis.length > 0)
  							sqlQuery += Util.join(columnsLabelAxis, ",");
  						
  						if(columnsLabelAxis.length > 0 && columnsValueAxis.length > 0)
  							sqlQuery += ",";
  	
  						if(columnsValueAxis.length > 0)
  							sqlQuery += Util.join(columnsValueAxis, ",");
  						
  						sqlQuery += " FROM " + tableName;
  						sqlQuery += self.queryBuildFilter(columnMetadata, filter);
  						sqlQuery += self.queryBuildOrder(columnMetadata, columnsLabelAxis);
  						
  						sqlQuery += " LIMIT 0,1000";
  						stmt = connection.prepare(sqlQuery);
  						int size = columnsValueAxis.length;
  						Object[] prev = new Object[size];
  						Object[] cur = new Object[size];
  						while(stmt.step()) {
  							StatisticResultSetTuple tuple = new StatisticResultSetTuple();
  							int counter = 0;
  							int i = 0;
  							for(; i < columnsLabelAxis.length; ++i) {
  								tuple.addLabel(columnsLabelAxis[i], stmt.columnValue(i));
  							}
  	
  							for(; i < (columnsLabelAxis.length + columnsValueAxis.length); ++i) {
  								tuple.addValue(stmt.columnValue(i));
  								
  								int idx = i - columnsLabelAxis.length;
  								cur[idx] = stmt.columnValue(i);
  								if(prev[idx] == stmt.columnValue(i))
  									counter++;
  							}
  							
  							prev = cur.clone();
  							if(counter == size)
  								continue;
  							
  							resultSet.addTuple(tuple);
  						}
  					} catch(SQLiteException e) {
  						Log.error(e);
  					} finally {
  						stmt.dispose();
  					}
  					return null;
  				}
  			});
  		}
  		
  		this.cacheQuery(query, resultSet);
  		return resultSet;
  	}
  	
  	private StatisticResultSet getCachedQuery(JsonObject query) {
  		String hash = Util.generateHash(query.toString());
  		if(StatisticService.queryCache.containsKey(hash)) {
  			Date now = new Date();
  			StatisticResultSet resultSet = StatisticService.queryCache.get(hash);
  			if((now.getTime() - resultSet.getGenerated().getTime()) < StatisticService.VALID_DURATION)
  				return resultSet;
  		}
  		
  		return null;
  	}
  	
  	private void cacheQuery(JsonObject query, StatisticResultSet resultSet) {
  		StatisticService.queryCache.put(Util.generateHash(query.toString()), resultSet);
  	}
  
  	protected String queryBuildFilter(ResultSetColumnList columnMetadata, JsonArray filter) {
  		ArrayList<String> conditions = new ArrayList<String>();
  		if(filter.size() == 0)
  			return "";
  		
  		final String FILTER_OPERATOR_EQUAL = "EQUAL";
  		final String FILTER_OPERATOR_BETWEEN = "BETWEEN";
  		for(int i = 0; i < filter.size(); ++i) {
  			JsonObject entry = filter.get(i).getAsJsonObject();
  			String columnName = entry.get("columnName").getAsString();
  			JsonArray selections = entry.get("selections").getAsJsonArray();
  			String operator = FILTER_OPERATOR_EQUAL;
  			if(entry.has("operator"))
  				operator = entry.get("operator").getAsString().toUpperCase();
  			
  			if(operator.equalsIgnoreCase(FILTER_OPERATOR_BETWEEN)) {				
  				long begin = this.parseSelectionValue(selections.get(0), true);
  				long end = this.parseSelectionValue(selections.get(selections.size() - 1), false);
  				conditions.add(columnName + " BETWEEN " + Math.min(begin, end) + " AND " + Math.max(begin, end));
  			} else {
  				for(int j = 0; j < selections.size(); ++j) {
  					String selection = selections.get(j).getAsString();
  					conditions.add(columnName + "=\"" + selection + "\"");
  				}
  			}
  		}
  		
  		return " WHERE " + Util.join(conditions.toArray(new String[conditions.size()]), " AND ");
  	}
  	
  	protected long parseSelectionValue(JsonElement selection, boolean begin) {
  		if(selection.isJsonObject()) {
  			Calendar today = Calendar.getInstance();
  			StatisticQueryDynamicFilterType type = StatisticQueryDynamicFilterType.valueOf(selection.getAsJsonObject().get("value").getAsString());
  			switch(type) {
  			case YESTERDAY:
  				today.add(Calendar.DATE, -1);
  				break;
  			default:
  				break;
  			}
  			
  			return today.getTimeInMillis();
  		}
  
  		return selection.getAsLong();
  	}
  
  	protected String queryBuildOrder(final ResultSetColumnList columnMetadata, final String[] columns) {
  		final ArrayList<ResultSetColumn> sortedColumns = new ArrayList<ResultSetColumn>();
  		String query = "";
  		
  		for(int i = 0; i < columns.length; ++i) {
  			ResultSetColumn column = columnMetadata.getColumnByName(columns[i]);
  			if(column != null && column.getType() == ResultSetColumnType.TIME) {
  				sortedColumns.add(column);
  			}
  		}
  
  		for(int i = 0; i < sortedColumns.size(); ++i) {
  			if(i == 0)
  				query += " ORDER BY ";
  
  			if(i > 0) 
  				query += ",";
  			
  			query += sortedColumns.get(i).getName() + " DESC";
  		}
  		
  		return query;
  	}
  	
  	protected String[] parseColumnNames(JsonArray axis) {
  		int size = axis.size();
  		String[] result = new String[size];
  		for(int i = 0; i < size; ++i) {
  			JsonObject column = axis.get(i).getAsJsonObject();
  			String columnName = column.get("name").getAsString();
  			result[i] = columnName;
  		}
  		return result;
  	}
  
  	/**
  	 * 
  	 * @param jobject
  	 * @return
  	 */
  	public JsonObject processQuery(JsonObject jobject) {
  		JsonObject result = new JsonObject();
  		String[] processQuery = jobject.get("name").getAsString().split(":");
  		
  		if(processQuery.length <= 1) {
  			Log.error("Process query is invalid: " + jobject.get("name").getAsString());
  			return null;
  		}
  		
  		if(processQuery.length == 3) {
  			JsonArray data = new JsonArray();
  			JsonArray deviceIds = null;
  			String granularity = jobject.get("granularity").getAsString();
  			Date begin = new Date(jobject.get("begin").getAsLong());
  			Date end = new Date();
  			
  			try {
  				if(jobject.has("end")) {
  					end = new Date(jobject.get("end").getAsLong());
  				}
  				
  				// Mainly used for radiator statistics
  				if(jobject.has("roomId")) {
  					deviceIds = jobject.get("roomId").getAsJsonArray();
  				} else {
  					deviceIds = jobject.get("deviceId").getAsJsonArray();
  				}
  			
  				switch(DeviceType.valueOf(processQuery[1].toUpperCase())) {
  				case RADIATOR:
  