Examples with ConnectorMeterValueRecord jooq.steve.db.tables.records.ConnectorMeterValueRecord used on opensource projects

Example 1 with ConnectorMeterValueRecord

use of jooq.steve.db.tables.records.ConnectorMeterValueRecord in project steve by RWTH-i5-IDSG.

the class TransactionRepositoryImpl method getDetails.

@Override
public TransactionDetails getDetails(int transactionPk) {
    // -------------------------------------------------------------------------
    // Step 1: Collect general data about transaction
    // -------------------------------------------------------------------------
    TransactionQueryForm form = new TransactionQueryForm();
    form.setTransactionPk(transactionPk);
    form.setType(TransactionQueryForm.QueryType.ALL);
    form.setPeriodType(TransactionQueryForm.QueryPeriodType.ALL);
    Record11<Integer, String, Integer, String, DateTime, String, DateTime, String, String, Integer, Integer> transaction = getInternal(form).fetchOne();
    if (transaction == null) {
        throw new SteveException("There is no transaction with id '%s'", transactionPk);
    }
    DateTime startTimestamp = transaction.value5();
    DateTime stopTimestamp = transaction.value7();
    String stopValue = transaction.value8();
    String chargeBoxId = transaction.value2();
    int connectorId = transaction.value3();
    // -------------------------------------------------------------------------
    // Step 2: Collect intermediate meter values
    // -------------------------------------------------------------------------
    Condition timestampCondition;
    if (stopTimestamp == null && stopValue == null) {
        // active transaction
        timestampCondition = CONNECTOR_METER_VALUE.VALUE_TIMESTAMP.greaterOrEqual(startTimestamp);
    } else {
        // finished transaction
        timestampCondition = CONNECTOR_METER_VALUE.VALUE_TIMESTAMP.between(startTimestamp, stopTimestamp);
    }
    // Case 1: Ideal and most accurate case. Station sends meter values with transaction id set.
    // 
    SelectQuery<ConnectorMeterValueRecord> transactionQuery = ctx.selectFrom(CONNECTOR_METER_VALUE).where(CONNECTOR_METER_VALUE.TRANSACTION_PK.eq(transactionPk)).getQuery();
    // Case 2: Fall back to filtering according to time windows
    // 
    SelectQuery<ConnectorMeterValueRecord> timestampQuery = ctx.selectFrom(CONNECTOR_METER_VALUE).where(CONNECTOR_METER_VALUE.CONNECTOR_PK.eq(ctx.select(CONNECTOR.CONNECTOR_PK).from(CONNECTOR).where(CONNECTOR.CHARGE_BOX_ID.eq(chargeBoxId)).and(CONNECTOR.CONNECTOR_ID.eq(connectorId)))).and(timestampCondition).getQuery();
    // Actually, either case 1 applies or 2. If we retrieved values using 1, case 2 is should not be
    // executed (best case). In worst case (1 returns empty list and we fall back to case 2) though,
    // we make two db calls. Alternatively, we can pass both queries in one go, and make the db work.
    // 
    // UNION removes all duplicate records
    // 
    Table<ConnectorMeterValueRecord> t1 = transactionQuery.union(timestampQuery).asTable("t1");
    // -------------------------------------------------------------------------
    // Step 3: Charging station might send meter vales at fixed intervals (e.g.
    // every 15 min) regardless of the fact that connector's meter value did not
    // change (e.g. vehicle is fully charged, but cable is still connected). This
    // yields multiple entries in db with the same value but different timestamp.
    // We are only interested in the first arriving entry.
    // -------------------------------------------------------------------------
    Field<DateTime> dateTimeField = DSL.min(t1.field(2, DateTime.class)).as("min");
    List<TransactionDetails.MeterValues> values = ctx.select(dateTimeField, t1.field(3, String.class), t1.field(4, String.class), t1.field(5, String.class), t1.field(6, String.class), t1.field(7, String.class), t1.field(8, String.class), t1.field(9, String.class)).from(t1).groupBy(t1.field(3), t1.field(4), t1.field(5), t1.field(6), t1.field(7), t1.field(8), t1.field(9)).orderBy(dateTimeField).fetch().map(r -> TransactionDetails.MeterValues.builder().valueTimestamp(r.value1()).value(r.value2()).readingContext(r.value3()).format(r.value4()).measurand(r.value5()).location(r.value6()).unit(r.value7()).phase(r.value8()).build());
    return new TransactionDetails(new TransactionMapper().map(transaction), values);
}