}
public SpeedDistributionTrafficFlowReport getSpeedDistributionTrafficFlowReport(
TagDetails tagDetails) throws Exception {
SpeedDistributionTrafficFlowReport report = new SpeedDistributionTrafficFlowReport();
report.setTagId(tagDetails.getId());
report.setCreated(new Date());
ArrayList<ArrayList> rows = new ArrayList<ArrayList>();
NumberFormat formatter = new DecimalFormat("#0.00");
try {
Connection connection = getConnection();
Statement s = connection.createStatement();
String tagId = tagDetails.getId();
String sql = "SELECT * FROM speeddistributiontrafficflow WHERE tagid = '"+tagId+"' ORDER BY dayType desc, vehicletype, speedbin";
logger.debug("SQL for getSpeedDistributionTrafficFlowReport: " + sql);
ResultSet r = s.executeQuery(sql);
while (r.next()) {
ArrayList<String> row = new ArrayList<String>();
// skip first column (tagid)
row.add(r.getString(2)); // daytype
row.add(r.getString(3)); // vehicletype
row.add(r.getString(4)); // speedbin
// skip the last two year-based columns (s and m)
// and insert vkt here. Get as a double, then round to 2 decimal places
Double vktDouble = r.getDouble(12);
String vkt = "";
if( vktDouble != null)
{
vkt = formatter.format(vktDouble.doubleValue());
}
row.add(vkt);
row.add(r.getString(5)); // vehiclesecondsperday
row.add(r.getString(6)); // vehiclemetersperday
row.add(r.getString(7)); // weightedaveragespeed
row.add(r.getString(8)); // %s/d
row.add(r.getString(9)); // %m/d
rows.add(row);
}
logger.debug("row count=" + rows.size());
report.setReporValues(rows);
logger.debug("getSpeedDistributionTrafficFlowReport DAO complete");
return report;
} catch (SQLException e) {
logger.error(e.getMessage());