Ejemplo n.º 1
0
  public TrafficCountRecord getTrafficCountRecordById(String id) throws Exception {
    TrafficCountRecord trafficCountRecord = new TrafficCountRecord();
    try {
      Connection connection = getConnection();
      Statement s = connection.createStatement();
      String sql =
          "select id, tag, countdate, daytype, starttime,"
              + "endtime, w2, w3, pc, tx, ldv, ldc, hdc, mdb, hdb from trafficcount"
              + "where id = '"
              + id
              + "' ORDER BY countdate";
      ResultSet r = s.executeQuery(sql);
      while (r.next()) {

        String tag = r.getString(2);
        Date date = r.getDate(3);
        String dayType = r.getString(4);
        Date startTime = r.getDate(5);
        Date endTime = r.getDate(6);
        int W2 = r.getInt(7);
        int W3 = r.getInt(8);
        int PC = r.getInt(9);
        int TX = r.getInt(10);
        int LDV = r.getInt(11);
        int LDC = r.getInt(12);
        int HDC = r.getInt(13);
        int MDB = r.getInt(14);
        int HDB = r.getInt(15);

        trafficCountRecord.setId(id);
        trafficCountRecord.setDate(date);
        trafficCountRecord.setDayType(dayType);
        trafficCountRecord.setEndTime(endTime);
        trafficCountRecord.setHDB(HDB);
        trafficCountRecord.setHDC(HDC);
        trafficCountRecord.setLDC(LDC);
        trafficCountRecord.setLDV(LDV);
        trafficCountRecord.setMDB(MDB);
        trafficCountRecord.setPC(PC);
        trafficCountRecord.setStartTime(startTime);
        trafficCountRecord.setTag(tag);
        trafficCountRecord.setTX(TX);
        trafficCountRecord.setW2(W2);
        trafficCountRecord.setW3(W3);
      }

      connection.close(); // returns connection to pool

    } catch (SQLException e) {
      logger.error(e.getMessage());
      throw new Exception("There was an error executing the SQL: " + e.getMessage());
    } catch (Exception e) {
      logger.error(e.getMessage());
      throw new Exception("Unknown exception: " + e.getMessage());
    }

    return trafficCountRecord;
  }
Ejemplo n.º 2
0
  public ArrayList<TrafficCountRecord> getTrafficCountRecords(StudyRegion region) throws Exception {
    ArrayList<TrafficCountRecord> trafficCountRecordList = new ArrayList<TrafficCountRecord>();
    try {
      Connection connection = getConnection();
      Statement s = connection.createStatement();
      String sql =
          "SELECT "
              + "t1.id, "
              + "t2.name as tag, "
              + "t1.countdate, "
              + "t1.daytype, "
              + "t1.starttime,"
              + "t1.endtime, "
              + "t1.w2, "
              + "t1.w3, "
              + "t1.pc, "
              + "t1.tx, "
              + "t1.ldv, "
              + "t1.ldc, "
              + "t1.hdc, "
              + "t1.mdb, "
              + "t1.hdb "
              + "FROM "
              + "	trafficcount t1, "
              + "	tagdetails t2 "
              + "WHERE "
              + "	t1.regionid = '"
              + region.getId()
              + "' "
              + "AND "
              + "	t1.tagid = t2.id "
              + "ORDER BY t1.countdate ASC, t1.starttime ASC";
      logger.debug("SQL for getTrafficCountRecords: " + sql);
      ResultSet r = s.executeQuery(sql);
      while (r.next()) {

        String id = r.getString(1);
        String tag = r.getString(2);
        Date date = r.getDate(3);
        String dayType = r.getString(4);
        Date startTime = r.getTimestamp(5);
        Date endTime = r.getTimestamp(6);
        int W2 = r.getInt(7);
        int W3 = r.getInt(8);
        int PC = r.getInt(9);
        int TX = r.getInt(10);
        int LDV = r.getInt(11);
        int LDC = r.getInt(12);
        int HDC = r.getInt(13);
        int MDB = r.getInt(14);
        int HDB = r.getInt(15);

        TrafficCountRecord trafficCountRecord = new TrafficCountRecord();
        trafficCountRecord.setId(id);
        trafficCountRecord.setDate(date);
        trafficCountRecord.setDayType(dayType);
        trafficCountRecord.setEndTime(endTime);
        trafficCountRecord.setHDB(HDB);
        trafficCountRecord.setHDC(HDC);
        trafficCountRecord.setLDC(LDC);
        trafficCountRecord.setLDV(LDV);
        trafficCountRecord.setMDB(MDB);
        trafficCountRecord.setPC(PC);
        trafficCountRecord.setStartTime(startTime);
        trafficCountRecord.setTag(tag);
        trafficCountRecord.setTX(TX);
        trafficCountRecord.setW2(W2);
        trafficCountRecord.setW3(W3);

        trafficCountRecordList.add(trafficCountRecord);
      }

      connection.close(); // returns connection to the pool

    } catch (SQLException e) {
      logger.error(e.getMessage());
      throw new Exception("There was an error executing the SQL: " + e.getMessage());
    } catch (Exception e) {
      e.printStackTrace();
      logger.error(e.getMessage());
      throw new Exception("Unknown exception: " + e.getMessage());
    }

    return trafficCountRecordList;
  }
Ejemplo n.º 3
0
  private ArrayList<TrafficCountRecord> getTrafficCountReportLong(
      String tagName, String regionName, String dayType) throws Exception {

    // TODO: we changed regionName to regionId for the short report, so if we want to use
    // the long report we'll need to update the stored procedure

    ArrayList<TrafficCountRecord> records = new ArrayList<TrafficCountRecord>();
    try {
      Connection connection = getConnection();
      Statement s = connection.createStatement();

      // get the WEEKDAY counts for this tag
      String sql =
          "SELECT * FROM trafficCountReport('"
              + tagName
              + "', '"
              + regionName
              + "', '"
              + dayType
              + "')"
              + " AS foo(hour int, w2 bigint, w3 bigint, pc bigint, tx bigint,"
              + " ldv bigint, ldc bigint, hdc bigint, mdb bigint, hdb bigint)";

      logger.debug("SQL for getTrafficCountReportLong: " + sql);

      ResultSet r = s.executeQuery(sql);

      // there will always be 24 results from the trafficCountReport function
      while (r.next()) {
        // Piggy-back on the TrafficCount model
        // realizing it is not a real TrafficCount,
        // but it has the placeholders we like
        TrafficCountRecord record = new TrafficCountRecord();
        int hour = r.getInt(1);
        // logger.debug("hour="+hour);
        int w2 = r.getInt(2);
        // logger.debug("w2="+w2);
        int w3 = r.getInt(3);
        // logger.debug("w3="+w3);
        int pc = r.getInt(4);
        // logger.debug("pc="+pc);
        int tx = r.getInt(5);
        // logger.debug("tx="+tx);
        int ldv = r.getInt(6);
        // logger.debug("ldv="+ldv);
        int ldc = r.getInt(7);
        // logger.debug("ldc="+ldc);
        int hdc = r.getInt(8);
        // logger.debug("hdc="+hdc);
        int mdb = r.getInt(9);
        // logger.debug("mdb="+mdb);
        int hdb = r.getInt(10);
        // logger.debug("hdb="+hdb);

        // nowhere to set the hour as int in TrafficCountRecord
        record.setW2(w2);
        record.setW3(w3);
        record.setPC(pc);
        record.setTX(tx);
        record.setLDV(ldv);
        record.setLDC(ldc);
        record.setHDC(hdc);
        record.setMDB(mdb);
        record.setHDB(hdb);

        // logger.debug("hour=("+hour+"), record=" + record);

        records.add(record);
      }

    } catch (SQLException e) {
      logger.error(e.getMessage());
      throw new Exception("There was an error executing the SQL: " + e.getMessage());
    } catch (Exception e) {
      logger.error(e.getMessage());
      throw new Exception("Unknown exception: " + e.getMessage());
    }
    return records;
  }