Skip to content

Commit afce2e7

Browse files
goekaygoekay
committed
link various data tables together
for example, on the reservation table, you can click on the ocpp id tag and chargebox id and you will be directed to the details pages of each one. or you can click on the transaction id, and you will be directed to the transactions table. links from transactions and OCPP/JSON connection status tables are TODO.
1 parent 32c4a57 commit afce2e7

10 files changed

Lines changed: 87 additions & 37 deletions

File tree

‎src/main/java/de/rwth/idsg/steve/repository/ChargePointRepositoryImpl.java‎

Lines changed: 17 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -179,22 +179,29 @@ public List<ConnectorStatus> getChargePointConnectorStatus() {
179179
.asTable("t1");
180180

181181
return DSL.using(config)
182-
.select(CONNECTOR.CHARGE_BOX_ID, CONNECTOR.CONNECTOR_ID,
183-
CONNECTOR_STATUS.STATUS_TIMESTAMP, CONNECTOR_STATUS.STATUS, CONNECTOR_STATUS.ERROR_CODE)
182+
.select(CHARGE_BOX.CHARGE_BOX_PK,
183+
CONNECTOR.CHARGE_BOX_ID,
184+
CONNECTOR.CONNECTOR_ID,
185+
CONNECTOR_STATUS.STATUS_TIMESTAMP,
186+
CONNECTOR_STATUS.STATUS,
187+
CONNECTOR_STATUS.ERROR_CODE)
184188
.from(CONNECTOR_STATUS)
185189
.join(CONNECTOR)
186-
.onKey()
190+
.onKey()
191+
.join(CHARGE_BOX)
192+
.on(CHARGE_BOX.CHARGE_BOX_ID.eq(CONNECTOR.CHARGE_BOX_ID))
187193
.join(t1)
188-
.on(CONNECTOR_STATUS.CONNECTOR_PK.equal(t1.field(t1Pk)))
189-
.and(CONNECTOR_STATUS.STATUS_TIMESTAMP.equal(t1.field(t1Max)))
194+
.on(CONNECTOR_STATUS.CONNECTOR_PK.equal(t1.field(t1Pk)))
195+
.and(CONNECTOR_STATUS.STATUS_TIMESTAMP.equal(t1.field(t1Max)))
190196
.orderBy(CONNECTOR_STATUS.STATUS_TIMESTAMP.desc())
191197
.fetch()
192198
.map(r -> ConnectorStatus.builder()
193-
.chargeBoxId(r.value1())
194-
.connectorId(r.value2())
195-
.timeStamp(DateTimeUtils.humanize(r.value3()))
196-
.status(r.value4())
197-
.errorCode(r.value5())
199+
.chargeBoxPk(r.value1())
200+
.chargeBoxId(r.value2())
201+
.connectorId(r.value3())
202+
.timeStamp(DateTimeUtils.humanize(r.value4()))
203+
.status(r.value5())
204+
.errorCode(r.value6())
198205
.build()
199206
);
200207
}

‎src/main/java/de/rwth/idsg/steve/repository/ReservationRepositoryImpl.java‎

Lines changed: 18 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
import lombok.extern.slf4j.Slf4j;
99
import org.joda.time.DateTime;
1010
import org.jooq.Configuration;
11-
import org.jooq.Record7;
11+
import org.jooq.Record9;
1212
import org.jooq.RecordMapper;
1313
import org.jooq.SelectQuery;
1414
import org.jooq.exception.DataAccessException;
@@ -19,6 +19,8 @@
1919

2020
import java.util.List;
2121

22+
import static jooq.steve.db.tables.ChargeBox.CHARGE_BOX;
23+
import static jooq.steve.db.tables.OcppTag.OCPP_TAG;
2224
import static jooq.steve.db.tables.Reservation.RESERVATION;
2325

2426
/**
@@ -38,11 +40,15 @@ public class ReservationRepositoryImpl implements ReservationRepository {
3840
public List<Reservation> getReservations(ReservationQueryForm form) {
3941
SelectQuery selectQuery = DSL.using(config).selectQuery();
4042
selectQuery.addFrom(RESERVATION);
43+
selectQuery.addJoin(OCPP_TAG, OCPP_TAG.ID_TAG.eq(RESERVATION.ID_TAG));
44+
selectQuery.addJoin(CHARGE_BOX, CHARGE_BOX.CHARGE_BOX_ID.eq(RESERVATION.CHARGE_BOX_ID));
4145
selectQuery.addSelect(
4246
RESERVATION.RESERVATION_PK,
4347
RESERVATION.TRANSACTION_PK,
44-
RESERVATION.ID_TAG,
45-
RESERVATION.CHARGE_BOX_ID,
48+
OCPP_TAG.OCPP_TAG_PK,
49+
CHARGE_BOX.CHARGE_BOX_PK,
50+
OCPP_TAG.ID_TAG,
51+
CHARGE_BOX.CHARGE_BOX_ID,
4652
RESERVATION.START_DATETIME,
4753
RESERVATION.EXPIRY_DATETIME,
4854
RESERVATION.STATUS
@@ -135,17 +141,19 @@ public void used(int reservationId, int transactionId) {
135141
// -------------------------------------------------------------------------
136142

137143
private static class ReservationMapper implements
138-
RecordMapper<Record7<Integer, Integer, String, String, DateTime, DateTime, String>, Reservation> {
144+
RecordMapper<Record9<Integer, Integer, Integer, Integer, String, String, DateTime, DateTime, String>, Reservation> {
139145
@Override
140-
public Reservation map(Record7<Integer, Integer, String, String, DateTime, DateTime, String> r) {
146+
public Reservation map(Record9<Integer, Integer, Integer, Integer, String, String, DateTime, DateTime, String> r) {
141147
return Reservation.builder()
142148
.id(r.value1())
143149
.transactionId(r.value2())
144-
.idTag(r.value3())
145-
.chargeBoxId(r.value4())
146-
.startDatetime(DateTimeUtils.humanize(r.value5()))
147-
.expiryDatetime(DateTimeUtils.humanize(r.value6()))
148-
.status(r.value7())
150+
.ocppTagPk(r.value3())
151+
.chargeBoxPk(r.value4())
152+
.ocppIdTag(r.value5())
153+
.chargeBoxId(r.value6())
154+
.startDatetime(DateTimeUtils.humanize(r.value7()))
155+
.expiryDatetime(DateTimeUtils.humanize(r.value8()))
156+
.status(r.value9())
149157
.build();
150158
}
151159
}

‎src/main/java/de/rwth/idsg/steve/repository/TransactionRepositoryImpl.java‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,10 @@ public void writeTransactionsCSV(TransactionQueryForm form, Writer writer) {
7070
TRANSACTION.STOP_TIMESTAMP,
7171
TRANSACTION.STOP_VALUE);
7272

73+
if (form.isTransactionPkSet()) {
74+
selectQuery.addConditions(TRANSACTION.TRANSACTION_PK.eq(form.getTransactionPk()));
75+
}
76+
7377
if (form.isChargeBoxIdSet()) {
7478
selectQuery.addConditions(CONNECTOR.CHARGE_BOX_ID.eq(form.getChargeBoxId()));
7579
}

‎src/main/java/de/rwth/idsg/steve/repository/dto/ConnectorStatus.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,5 +12,5 @@
1212
@Builder
1313
public final class ConnectorStatus {
1414
private final String chargeBoxId, timeStamp, status, errorCode;
15-
private final int connectorId;
15+
private final int chargeBoxPk, connectorId;
1616
}

‎src/main/java/de/rwth/idsg/steve/repository/dto/Reservation.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
@Getter
1212
@Builder
1313
public final class Reservation {
14-
private final int id;
14+
private final int id, ocppTagPk, chargeBoxPk;
1515
private final Integer transactionId;
16-
private final String idTag, chargeBoxId, startDatetime, expiryDatetime, status;
16+
private final String ocppIdTag, chargeBoxId, startDatetime, expiryDatetime, status;
1717
}

‎src/main/java/de/rwth/idsg/steve/utils/ControllerHelper.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,7 @@ public static Address recordToDto(AddressRecord record) {
2929
}
3030

3131
public static Map<String, String> idTagEnhancer(List<String> idTagList) {
32-
Map<String, String> map = new HashMap<>(idTagList.size());
32+
Map<String, String> map = new HashMap<>(idTagList.size() + 1);
3333
map.put("", EMPTY_OPTION);
3434

3535
for (String s : idTagList) {

‎src/main/java/de/rwth/idsg/steve/web/dto/TransactionQueryForm.java‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,9 @@
1515
@Setter
1616
public class TransactionQueryForm extends QueryForm {
1717

18+
// Internal database Id
19+
private Integer transactionPk;
20+
1821
private boolean returnCSV;
1922

2023
@NotNull(message = "Query type is required")
@@ -36,6 +39,10 @@ public boolean isPeriodFromToCorrect() {
3639
return periodType != QueryPeriodType.FROM_TO || isFromToSet();
3740
}
3841

42+
public boolean isTransactionPkSet() {
43+
return transactionPk != null;
44+
}
45+
3946
// -------------------------------------------------------------------------
4047
// Enums
4148
// -------------------------------------------------------------------------

‎src/main/resources/webapp/WEB-INF/views/connectorStatus.jsp‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -10,13 +10,13 @@ Connector Status
1010
<table class="res" id="connectorStatusTable">
1111
<thead><tr><th>ChargeBox ID</th><th>Connector ID</th><th>Date/Time</th><th>Status</th><th>Error Code</th></tr></thead>
1212
<tbody>
13-
<c:forEach items="${connectorStatusList}" var="connectorStatus">
13+
<c:forEach items="${connectorStatusList}" var="cs">
1414
<tr>
15-
<td>${connectorStatus.chargeBoxId}</td>
16-
<td>${connectorStatus.connectorId}</td>
17-
<td>${connectorStatus.timeStamp}</td>
18-
<td>${connectorStatus.status}</td>
19-
<td>${connectorStatus.errorCode}</td>
15+
<td><a href="/steve/manager/chargepoints/details/${cs.chargeBoxPk}">${cs.chargeBoxId}</a></td>
16+
<td>${cs.connectorId}</td>
17+
<td>${cs.timeStamp}</td>
18+
<td>${cs.status}</td>
19+
<td>${cs.errorCode}</td>
2020
</tr>
2121
</c:forEach>
2222
</tbody>

‎src/main/resources/webapp/WEB-INF/views/data-man/reservations.jsp‎

Lines changed: 25 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@
1919
</td>
2020
</tr>
2121
<tr>
22-
<td>User ID:</td>
22+
<td>OCPP ID Tag:</td>
2323
<td><form:select path="userId">
2424
<option value="" selected>All</option>
2525
<form:options items="${idTagList}"/>
@@ -60,13 +60,33 @@
6060
<br>
6161

6262
<table class="res">
63-
<thead><tr><th>Reservation ID</th><th>Transaction ID</th><th>User ID Tag</th><th>ChargeBox ID</th><th>Start Date/Time</th><th>Expiry Date/Time</th><th>Status</th></tr></thead>
63+
<thead>
64+
<tr>
65+
<th>Reservation ID</th>
66+
<th>Transaction ID</th>
67+
<th>OCPP ID Tag</th>
68+
<th>ChargeBox ID</th>
69+
<th>Start Date/Time</th>
70+
<th>Expiry Date/Time</th>
71+
<th>Status</th>
72+
</tr>
73+
</thead>
6474
<tbody>
65-
<%-- Start --%>
6675
<c:forEach items="${reservList}" var="res">
67-
<tr><td>${res.id}</td><td>${res.transactionId}</td><td>${res.idTag}</td><td>${res.chargeBoxId}</td><td>${res.startDatetime}</td><td>${res.expiryDatetime}</td><td>${res.status}</td></tr>
76+
<tr>
77+
<td>${res.id}</td>
78+
<td>
79+
<c:if test="${not empty res.transactionId}">
80+
<a href="/steve/manager/transactions/query?type=ALL&transactionPk=${res.transactionId}">${res.transactionId}</a>
81+
</c:if>
82+
</td>
83+
<td><a href="/steve/manager/ocppTags/details/${res.ocppTagPk}">${res.ocppIdTag}</a></td>
84+
<td><a href="/steve/manager/chargepoints/details/${res.chargeBoxPk}">${res.chargeBoxId}</a></td>
85+
<td>${res.startDatetime}</td>
86+
<td>${res.expiryDatetime}</td>
87+
<td>${res.status}</td>
88+
</tr>
6889
</c:forEach>
69-
<%-- End --%>
7090
</tbody>
7191
</table>
7292
<br>

‎src/main/resources/webapp/WEB-INF/views/data-man/transactions.jsp‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,10 @@ Transactions
1616
</span></section>
1717
<form:form action="/steve/manager/transactions/query" method="get" modelAttribute="params">
1818
<table class="userInput">
19+
<tr>
20+
<td>Transaction ID:</td>
21+
<td><form:input path="transactionPk"/></td>
22+
</tr>
1923
<tr>
2024
<td>ChargeBox ID:</td>
2125
<td><form:select path="chargeBoxId">
@@ -25,7 +29,7 @@ Transactions
2529
</td>
2630
</tr>
2731
<tr>
28-
<td>User ID:</td>
32+
<td>OCPP ID Tag:</td>
2933
<td><form:select path="userId">
3034
<option value="" selected>All</option>
3135
<form:options items="${idTagList}"/>
@@ -69,7 +73,7 @@ Transactions
6973
<br>
7074

7175
<table class="res">
72-
<thead><tr><th>Transaction ID</th><th>ChargeBox ID</th><th>Connector ID</th><th>User ID Tag</th><th>Start Date/Time</th><th>Start Value</th><th>Stop Date/Time</th><th>Stop Value</th></tr></thead>
76+
<thead><tr><th>Transaction ID</th><th>ChargeBox ID</th><th>Connector ID</th><th>OCPP ID Tag</th><th>Start Date/Time</th><th>Start Value</th><th>Stop Date/Time</th><th>Stop Value</th></tr></thead>
7377
<tbody>
7478
<%-- Start --%>
7579
<c:forEach items="${transList}" var="ta">

0 commit comments

Comments
 (0)