private void processType(SelectQuery selectQuery, TransactionQueryForm form) { switch (form.getPeriodType()) { case TODAY: selectQuery.addConditions( date(TRANSACTION.START_TIMESTAMP).eq(date(CustomDSL.utcTimestamp()))); break; case LAST_10: case LAST_30: case LAST_90: DateTime now = DateTime.now(); selectQuery.addConditions( date(TRANSACTION.START_TIMESTAMP) .between(date(now.minusDays(form.getPeriodType().getInterval())), date(now))); break; case ALL: break; case FROM_TO: selectQuery.addConditions( TRANSACTION.START_TIMESTAMP.between( form.getFrom().toDateTime(), form.getTo().toDateTime())); break; default: throw new SteveException("Unknown enum type"); } }
@SuppressWarnings("unchecked") private SelectQuery addConditions(SelectQuery selectQuery, TransactionQueryForm form) { if (form.isTransactionPkSet()) { selectQuery.addConditions(TRANSACTION.TRANSACTION_PK.eq(form.getTransactionPk())); } if (form.isChargeBoxIdSet()) { selectQuery.addConditions(CONNECTOR.CHARGE_BOX_ID.eq(form.getChargeBoxId())); } if (form.isOcppIdTagSet()) { selectQuery.addConditions(TRANSACTION.ID_TAG.eq(form.getOcppIdTag())); } if (form.getType() == TransactionQueryForm.QueryType.ACTIVE) { selectQuery.addConditions(TRANSACTION.STOP_TIMESTAMP.isNull()); } processType(selectQuery, form); // Default order selectQuery.addOrderBy(TRANSACTION.TRANSACTION_PK.desc()); return selectQuery; }
@SuppressWarnings("unchecked") private Result<Record5<Integer, String, String, String, DateTime>> getOverviewInternal( ChargePointQueryForm form) { SelectQuery selectQuery = ctx.selectQuery(); selectQuery.addFrom(CHARGE_BOX); selectQuery.addSelect( CHARGE_BOX.CHARGE_BOX_PK, CHARGE_BOX.CHARGE_BOX_ID, CHARGE_BOX.DESCRIPTION, CHARGE_BOX.OCPP_PROTOCOL, CHARGE_BOX.LAST_HEARTBEAT_TIMESTAMP); if (form.isSetOcppVersion()) { // http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html selectQuery.addConditions( CHARGE_BOX.OCPP_PROTOCOL.like(form.getOcppVersion().getValue() + "_")); } if (form.isSetDescription()) { selectQuery.addConditions(includes(CHARGE_BOX.DESCRIPTION, form.getDescription())); } switch (form.getHeartbeatPeriod()) { case ALL: break; case TODAY: selectQuery.addConditions( date(CHARGE_BOX.LAST_HEARTBEAT_TIMESTAMP).eq(date(DateTime.now()))); break; case YESTERDAY: selectQuery.addConditions( date(CHARGE_BOX.LAST_HEARTBEAT_TIMESTAMP).eq(date(DateTime.now().minusDays(1)))); break; case EARLIER: selectQuery.addConditions( date(CHARGE_BOX.LAST_HEARTBEAT_TIMESTAMP).lessThan(date(DateTime.now().minusDays(1)))); break; default: throw new SteveException("Unknown enum type"); } // Default order selectQuery.addOrderBy(CHARGE_BOX.CHARGE_BOX_PK.asc()); return selectQuery.fetch(); }
@SuppressWarnings("unchecked") private SelectQuery<Record8<Integer, String, Integer, String, DateTime, String, DateTime, String>> getInternalCSV(TransactionQueryForm form) { SelectQuery selectQuery = ctx.selectQuery(); selectQuery.addFrom(TRANSACTION); selectQuery.addJoin(CONNECTOR, TRANSACTION.CONNECTOR_PK.eq(CONNECTOR.CONNECTOR_PK)); selectQuery.addSelect( TRANSACTION.TRANSACTION_PK, CONNECTOR.CHARGE_BOX_ID, CONNECTOR.CONNECTOR_ID, TRANSACTION.ID_TAG, TRANSACTION.START_TIMESTAMP, TRANSACTION.START_VALUE, TRANSACTION.STOP_TIMESTAMP, TRANSACTION.STOP_VALUE); return addConditions(selectQuery, form); }
public List<? extends ProjectMember> getProjectMembersByIdentity( long projectId, Set<Identity> identities) { Condition allMembers = DSL.falseCondition(); for (Identity identity : identities) { allMembers = allMembers.or( PROJECT_MEMBER .EXTERNAL_ID .eq(identity.getExternalId()) .and(PROJECT_MEMBER.EXTERNAL_ID_TYPE.eq(identity.getExternalIdType())) .and(PROJECT_MEMBER.REMOVED.isNull()) .and(PROJECT_MEMBER.STATE.eq(CommonStatesConstants.ACTIVE)) .and(PROJECT_MEMBER.PROJECT_ID.eq(projectId))); } SelectQuery<Record> query = create().selectQuery(); query.addFrom(PROJECT_MEMBER); query.addConditions(allMembers); query.setDistinct(true); return query.fetchInto(PROJECT_MEMBER); }
@Override public List<Account> getAccessibleProjects( Set<Identity> identities, boolean isAdmin, Long usingAccount) { List<Account> projects = new ArrayList<>(); if (identities == null) { return projects; } if (isAdmin) { projects.addAll( create() .selectFrom(ACCOUNT) .where(ACCOUNT.KIND.eq(ProjectConstants.TYPE).and(ACCOUNT.REMOVED.isNull())) .orderBy(ACCOUNT.ID.asc()) .fetch()); return projects; } if (usingAccount != null) { Account project = getAccountById(usingAccount); if (project != null && project.getKind().equalsIgnoreCase(ProjectConstants.TYPE)) { projects.add(project); return projects; } } // DSL.falseCondition is created so that we can dynamically build a or // Condition without caring what the external Ids are and still make one // Database call. Condition allMembers = DSL.falseCondition(); for (Identity id : identities) { allMembers = allMembers.or( PROJECT_MEMBER .EXTERNAL_ID .eq(id.getExternalId()) .and(PROJECT_MEMBER.EXTERNAL_ID_TYPE.eq(id.getExternalIdType())) .and(PROJECT_MEMBER.REMOVED.isNull()) .and(PROJECT_MEMBER.STATE.eq(CommonStatesConstants.ACTIVE))); } SelectQuery<Record> query = create().selectQuery(); query.addFrom(ACCOUNT); query.addJoin(PROJECT_MEMBER, PROJECT_MEMBER.PROJECT_ID.equal(ACCOUNT.ID)); query.addConditions(allMembers); query.setDistinct(true); projects.addAll(query.fetchInto(ACCOUNT)); Map<Long, Account> returnProjects = new HashMap<>(); for (Account project : projects) { returnProjects.put(project.getId(), project); } projects = new ArrayList<>(); projects.addAll(returnProjects.values()); return projects; }