Java 类org.jooq.SelectConditionStep 实例源码

/**
 * Returns all {@link News} where a {@link CombItem} of the given {@link User} exists.
 * The News are sorted by the add_date descending.
 * @param userId the {@link User} to get the {@link CombItem}s.
 * @param onlyUnread if <code>true</code> only {@link CombItem} with no read_date
 *          will be returned.
 * @return {@link List} containing {@link News}.
 */
public List<News> getCombItems(final long userId, final boolean onlyUnread) {
    final SelectConditionStep<Record> sql = DSL.using(jooqConfig).
            select().
            from(COMB_ITEM_TABLE.
                join(CONTENT_TABLE).
                    on(COMB_ITEM_TABLE.CONTENT_ID.eq(CONTENT_TABLE.ID)).
                join(NEWS_TABLE).
                    on(CONTENT_TABLE.ID.eq(NEWS_TABLE.CONTENT_ID))).
            where(COMB_ITEM_TABLE.USER_ID.eq(userId));

    if(onlyUnread) {
        sql.and(COMB_ITEM_TABLE.READ_DATE.isNull());
    }

    return sql.orderBy(COMB_ITEM_TABLE.ADD_DATE.desc()).
            fetchInto(News.class);
}

@Override
public int insert(InsertReservationParams params) {
    // Check overlapping
    //isOverlapping(startTimestamp, expiryTimestamp, chargeBoxId);

    SelectConditionStep<Record1<Integer>> connectorPkQuery =
            DSL.select(CONNECTOR.CONNECTOR_PK)
               .from(CONNECTOR)
               .where(CONNECTOR.CHARGE_BOX_ID.equal(params.getChargeBoxId()))
               .and(CONNECTOR.CONNECTOR_ID.equal(params.getConnectorId()));

    int reservationId = ctx.insertInto(RESERVATION)
                           .set(RESERVATION.CONNECTOR_PK, connectorPkQuery)
                           .set(RESERVATION.ID_TAG, params.getIdTag())
                           .set(RESERVATION.START_DATETIME, params.getStartTimestamp())
                           .set(RESERVATION.EXPIRY_DATETIME, params.getExpiryTimestamp())
                           .set(RESERVATION.STATUS, ReservationStatus.WAITING.name())
                           .returning(RESERVATION.RESERVATION_PK)
                           .fetchOne()
                           .getReservationPk();

    log.debug("A new reservation '{}' is inserted.", reservationId);
    return reservationId;
}

public static void main(String[] args) {
    AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(DIConfiguration.class);
    DSLContext dsl = ctx.getBean(DSLContext.class);


    long categoryId = 1L;

    SelectConditionStep<Record> qry = dsl
            .select(MEASURABLE.NAME, MEASURABLE.DESCRIPTION, MEASURABLE.ID, MEASURABLE.PARENT_ID, MEASURABLE.EXTERNAL_ID)
            .select(INVOLVEMENT_KIND.NAME)
            .select(PERSON.DISPLAY_NAME)
            .from(MEASURABLE)
            .leftJoin(INVOLVEMENT)
            .on(INVOLVEMENT.ENTITY_ID.eq(MEASURABLE.ID).and(INVOLVEMENT.ENTITY_KIND.eq(EntityKind.MEASURABLE.name())))
            .leftJoin(INVOLVEMENT_KIND)
            .on(INVOLVEMENT_KIND.ID.eq(INVOLVEMENT.KIND_ID))
            .leftJoin(PERSON)
            .on(PERSON.EMPLOYEE_ID.eq(INVOLVEMENT.EMPLOYEE_ID))
            .where(MEASURABLE.MEASURABLE_CATEGORY_ID.eq(categoryId));

    qry.fetch().forEach(System.out::println);

}

@Override
public int insert(InsertReservationParams params) {
    // Check overlapping
    //isOverlapping(startTimestamp, expiryTimestamp, chargeBoxId);

    SelectConditionStep<Record1<Integer>> connectorPkQuery =
            DSL.select(CONNECTOR.CONNECTOR_PK)
               .from(CONNECTOR)
               .where(CONNECTOR.CHARGE_BOX_ID.equal(params.getChargeBoxId()))
               .and(CONNECTOR.CONNECTOR_ID.equal(params.getConnectorId()));

    int reservationId = ctx.insertInto(RESERVATION)
                           .set(RESERVATION.CONNECTOR_PK, connectorPkQuery)
                           .set(RESERVATION.ID_TAG, params.getIdTag())
                           .set(RESERVATION.START_DATETIME, params.getStartTimestamp())
                           .set(RESERVATION.EXPIRY_DATETIME, params.getExpiryTimestamp())
                           .set(RESERVATION.STATUS, ReservationStatus.WAITING.name())
                           .returning(RESERVATION.RESERVATION_PK)
                           .fetchOne()
                           .getReservationPk();

    log.debug("A new reservation '{}' is inserted.", reservationId);
    return reservationId;
}

@Override
public void updateTransaction(UpdateTransactionParams p) {

    // -------------------------------------------------------------------------
    // Step 1: Update transaction table
    //
    // After update, a DB trigger sets the user.inTransaction field to 0
    // -------------------------------------------------------------------------

    ctx.update(TRANSACTION)
       .set(TRANSACTION.STOP_TIMESTAMP, p.getStopTimestamp())
       .set(TRANSACTION.STOP_VALUE, p.getStopMeterValue())
       .where(TRANSACTION.TRANSACTION_PK.equal(p.getTransactionId()))
       .and(TRANSACTION.STOP_TIMESTAMP.isNull())
       .and(TRANSACTION.STOP_VALUE.isNull())
       .execute();

    // -------------------------------------------------------------------------
    // Step 2: Set connector status back to "Available" again
    // -------------------------------------------------------------------------

    SelectConditionStep<Record1<Integer>> connectorPkQuery =
            DSL.select(TRANSACTION.CONNECTOR_PK)
               .from(TRANSACTION)
               .where(TRANSACTION.TRANSACTION_PK.equal(p.getTransactionId()));

    insertConnectorStatus(connectorPkQuery, p.getStopTimestamp(), p.getStatusUpdate());
}

/**
 * After a transaction start/stop event, a charging station _might_ send a connector status notification, but it is
 * not required. With this, we make sure that the status is updated accordingly. Since we use the timestamp of the
 * transaction data, we do not necessarily insert a "most recent" status.
 *
 * If the station sends a notification, we will have a more recent timestamp, and therefore the status of the
 * notification will be used as current. Or, if this transaction data was sent to us for a failed push from the past
 * and we have a "more recent" status, it will still be the current status.
 */
private void insertConnectorStatus(SelectConditionStep<Record1<Integer>> connectorPkQuery,
                                   DateTime timestamp,
                                   TransactionStatusUpdate statusUpdate) {
    ctx.insertInto(CONNECTOR_STATUS)
       .set(CONNECTOR_STATUS.CONNECTOR_PK, connectorPkQuery)
       .set(CONNECTOR_STATUS.STATUS_TIMESTAMP, timestamp)
       .set(CONNECTOR_STATUS.STATUS, statusUpdate.getStatus())
       .set(CONNECTOR_STATUS.ERROR_CODE, statusUpdate.getErrorCode())
       .execute();
}

public List<AuthoritativeSource> findAuthSources(EntityReference parentRef) {
    Condition customSelectionCriteria;
    switch(parentRef.kind()) {
        case ORG_UNIT:
            Select<Record1<Long>> ouSelector = organisationalUnitIdSelectorFactory.apply(
                    mkOpts(parentRef, HierarchyQueryScope.PARENTS));
            customSelectionCriteria = AUTHORITATIVE_SOURCE.PARENT_ID.in(ouSelector);
            break;
        case DATA_TYPE:
            Select<Record1<Long>> dtSelector = dataTypeIdSelectorFactory.apply(
                    mkOpts(parentRef, HierarchyQueryScope.CHILDREN));
            SelectConditionStep<Record1<String>> codeSelector = DSL
                    .select(DATA_TYPE.CODE)
                    .from(DATA_TYPE)
                    .where(DATA_TYPE.ID.in(dtSelector));
            customSelectionCriteria = AUTHORITATIVE_SOURCE.DATA_TYPE.in(codeSelector);
            break;
        case MEASURABLE:
        case PERSON:
            customSelectionCriteria = mkConsumerSelectionCondition(parentRef, HierarchyQueryScope.CHILDREN);
            break;
        case APP_GROUP:
        case FLOW_DIAGRAM:
            customSelectionCriteria = mkConsumerSelectionCondition(parentRef, HierarchyQueryScope.EXACT);
            break;
        default:
            throw new UnsupportedOperationException("Cannot calculate auth sources for ref" + parentRef);
    }

    return authoritativeSourceDao.findAuthSources(customSelectionCriteria);

}

public double countRoots() {
    SelectConditionStep<Record1<String>> rootSelector = DSL
            .selectDistinct(PERSON_HIERARCHY.MANAGER_ID)
            .from(PERSON_HIERARCHY)
            .where(PERSON_HIERARCHY.LEVEL.eq(1));

    return dsl.fetchCount(rootSelector);
}

private SelectConditionStep<Record1<Long>> mkForOrgUnit(EntityReference ref, HierarchyQueryScope scope) {

        ImmutableIdSelectionOptions ouSelectorOptions = ImmutableIdSelectionOptions.builder()
                .entityReference(ref)
                .scope(scope)
                .build();

        Select<Record1<Long>> ouSelector = orgUnitIdSelectorFactory.apply(ouSelectorOptions);

        return dsl
                .selectDistinct(SURVEY_INSTANCE.ID)
                .from(SURVEY_INSTANCE)
                .where(SURVEY_INSTANCE.ENTITY_KIND.eq(ref.kind().name())
                        .and(SURVEY_INSTANCE.ENTITY_ID.in(ouSelector)));
    }

private Select<Record1<Long>> mkForPerson(IdSelectionOptions options) {
    SelectConditionStep<Record1<String>> empIdSelector = DSL
            .selectDistinct(PERSON.EMPLOYEE_ID)
            .from(PERSON)
            .where(PERSON.ID.eq(options.entityReference().id()));

    return dsl.selectDistinct(CHANGE_INITIATIVE.ID)
            .from(CHANGE_INITIATIVE)
            .innerJoin(INVOLVEMENT)
            .on(INVOLVEMENT.ENTITY_ID.eq(CHANGE_INITIATIVE.ID))
            .where(INVOLVEMENT.ENTITY_KIND.eq(EntityKind.CHANGE_INITIATIVE.name()))
            .and(INVOLVEMENT.EMPLOYEE_ID.in(empIdSelector));
}

public Collection<BalanceModel> getTopBalance(boolean user, boolean bank, int fromRank, int toRank, boolean showHidden)
{
    SelectJoinStep<Record4<String, String, String, Long>> from = db.getDSL()
            .select(TABLE_BALANCE.ACCOUNT_ID, TABLE_BALANCE.CURRENCY, TABLE_BALANCE.CONTEXT, TABLE_BALANCE.BALANCE)
            .from(TABLE_BALANCE.join(TABLE_ACCOUNT).on(TABLE_BALANCE.ACCOUNT_ID.eq(TABLE_ACCOUNT.ID)));
    Condition userCond = TABLE_ACCOUNT.IS_UUID.eq(true);
    Condition bankCond = TABLE_ACCOUNT.IS_UUID.eq(false);
    SelectConditionStep<Record4<String, String, String, Long>> where;
    if (!user && !bank)
    {
        throw new IllegalArgumentException();
    }
    if (user)
    {
        where = from.where(userCond);
    }
    else if (bank)
    {
        where = from.where(bankCond);
    }
    else
    {
        where = from.where(DSL.condition(true));
    }

    if (!showHidden)
    {
        where = where.and(TABLE_ACCOUNT.HIDDEN.eq(false));
    }
    return where.orderBy(TABLE_BALANCE.BALANCE.desc()).limit(fromRank - 1, toRank - fromRank + 1).fetchInto(BalanceModel.class);
}

private SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> createPictureQuery() {
    SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> pictureSelect = create
            .select(DSL.val("P").as("type"), LOCATION.OFFICIAL_NAME, LOCATION.CITY, USERS.DISPLAYNAME.as("user"),
                    DSL.val("N").as("update_Type"), LOCATION.ID, PICTURES.CREATED_ON, PICTURES.ID.as("picture_Id"))
            .from(LOCATION).join(PICTURES, JoinType.JOIN).on(LOCATION.ID.equal(PICTURES.FK_LOCATION))
            .join(USERS, JoinType.JOIN).on(PICTURES.FK_USER.equal(USERS.ID))
            .where(LOCATION.FK_COMMUNITY.equal(fkCommunity).and(LOCATION.ARCHIVED.equal(0)));
    return pictureSelect;
}

private SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> createUserQuery() {
    SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> usersSelect = create
            .select(DSL.val("U").as("type"), DSL.val("").as("official_Name"), DSL.val("").as("city"),
                    USERS.DISPLAYNAME.as("user"), DSL.val("N").as("update_Type"), USERS.ID, USERS.CREATED_ON,
                    DSL.val(0).as("picture_Id")).from(USERS).where(USERS.FK_COMMUNITY.equal(fkCommunity));
    return usersSelect;
}

private SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> createReviewQuery() {
    SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> reviewsSelect = create
            .select(DSL.val("R").as("type"),
                    LOCATION.OFFICIAL_NAME,
                    LOCATION.CITY,
                    USERS.DISPLAYNAME.as("user"),
                    DSL.decode().value(REVIEWS.CREATED_ON).when(REVIEWS.LAST_UPDATE, "N").otherwise("U")
                            .as("update_Type"), LOCATION.ID, REVIEWS.LAST_UPDATE, DSL.val(0).as("picture_Id"))
            .from(LOCATION).join(REVIEWS, JoinType.JOIN).on(LOCATION.ID.equal(REVIEWS.FK_LOCATION))
            .join(USERS, JoinType.JOIN).on(REVIEWS.FK_USER.equal(USERS.ID))
            .where(LOCATION.FK_COMMUNITY.equal(fkCommunity).and(LOCATION.ARCHIVED.equal(0)));
    return reviewsSelect;
}

private SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> createLocationQuery() {
    SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> locationSelect = create
            .select(DSL.val("L").as("type"),
                    LOCATION.OFFICIAL_NAME,
                    LOCATION.CITY,
                    DSL.val("").as("user"),
                    DSL.decode().value(LOCATION.CREATED_ON).when(LOCATION.LAST_UPDATE, "N").otherwise("U")
                            .as("update_Type"), LOCATION.ID, LOCATION.LAST_UPDATE, DSL.val(0).as("picture_Id"))
            .from(LOCATION).where(LOCATION.FK_COMMUNITY.equal(fkCommunity).and(LOCATION.ARCHIVED.equal(0)));
    return locationSelect;
}

@Override
public boolean canPrint(final String teamCode, final Integer submissionId) {
    final SelectJoinStep<Record1<Boolean>> step =
            jooq.select(CONTESTS.CAN_USE_SERVER_PRINTER)
                .from(SUBMISSIONS)
                .join(CONTESTS_TASKS).using(CONTESTS_TASKS.TASK_ID)
                .join(CONTESTS).using(CONTESTS.CONTEST_ID);

    if (!SecurityUtils.isAdmin()) {
        step.join(TEAMS_CONTESTS).using(TEAMS_CONTESTS.CONTEST_ID);
    }

    final SelectConditionStep<Record1<Boolean>> where =
            step.where(SUBMISSIONS.SUBMISSION_ID.eq(submissionId));

    if (!SecurityUtils.isAdmin()) {
        where.and(CONTESTS.CONTEST_START.lt(DSL.currentTimestamp()))
             .and(CONTESTS.CONTEST_END.gt(DSL.currentTimestamp()))
             .and(TEAMS_CONTESTS.TEAM_ID.eq(getUserTeamId(teamCode)));
    }

    where.orderBy(CONTESTS.CAN_USE_SERVER_PRINTER.desc()).limit(1);

    final Record1<Boolean> result = where.fetchOne();

    if (result == null) {
        return false;
    }
    return result.getValue(CONTESTS.CAN_USE_SERVER_PRINTER);
}

private SelectConditionStep<Record> schemaQuery(long accountId, String role) {
    return create()
            .select()
            .from(DYNAMIC_SCHEMA).leftOuterJoin(DYNAMIC_SCHEMA_ROLE)
            .on(DYNAMIC_SCHEMA_ROLE.DYNAMIC_SCHEMA_ID.eq(DYNAMIC_SCHEMA.ID))
            .where(DYNAMIC_SCHEMA.ACCOUNT_ID.eq(accountId)
                    .and(DYNAMIC_SCHEMA_ROLE.ROLE.eq(role))
                    .and(DYNAMIC_SCHEMA.STATE.ne(CommonStatesConstants.REMOVED)))
            .or(DYNAMIC_SCHEMA.ACCOUNT_ID.eq(accountId)
                    .and(DYNAMIC_SCHEMA_ROLE.ROLE.isNull())
                    .and(DYNAMIC_SCHEMA.STATE.ne(CommonStatesConstants.REMOVED)))
            .or(DYNAMIC_SCHEMA_ROLE.ROLE.eq(role)
                    .and(DYNAMIC_SCHEMA.ACCOUNT_ID.isNull())
                    .and(DYNAMIC_SCHEMA.STATE.ne(CommonStatesConstants.REMOVED)));
}

@Override
public String toString() {
    // Generate "select *" if no dimension or metric is precised
    final SelectSelectStep<? extends Record> initialSelect = dimensions.size() == 1 && metrics.isEmpty() ? context.select()
                                                                                                         : context.select(dimensions)
                                                                                                                  .select(metrics);

    SelectConditionStep<? extends Record> statement = initialSelect.from(tableName)
                                                                   .where();


    if (filters != null) {
        statement = statement.and(Filters.of(filters));
    }
    if (condition != null) {
        statement = statement.and(condition);
    }

    statement.and(DSL.fieldByName("tenant_record_id").eq(tenantRecordId));

    if (shouldGroupBy) {
        return statement.groupBy(dimensions)
                        .getSQL();
    } else {
        return statement.getSQL();
    }
}

public SelectConditionStep<JobRecord> isActive(SelectConditionStep<JobRecord> where) {
    StatusCodeSequencing seq = STATUS_CODE_SEQUENCING.as("seq");
    return where.andNotExists(model.create()
                                   .selectFrom(seq)
                                   .where(seq.field(STATUS_CODE_SEQUENCING.CHILD)
                                             .equal(JOB.STATUS))
                                   .andNotExists(model.create()
                                                      .selectFrom(STATUS_CODE_SEQUENCING)
                                                      .where(STATUS_CODE_SEQUENCING.SERVICE.equal(seq.field(STATUS_CODE_SEQUENCING.SERVICE)))
                                                      .and(STATUS_CODE_SEQUENCING.PARENT.eq(seq.field(STATUS_CODE_SEQUENCING.CHILD)))));
}

/**
 * Do the work of matching protocols to networks defined by jobs and
 * metaprocols.
 *
 * @param metaprotocol
 * @param job
 * @return
 */

private SelectConditionStep<Record1<UUID>> inferenceSubQuery(UUID classifier,
                                                             UUID classification) {
    return model.create()
                .select(EXISTENTIAL_NETWORK.CHILD)
                .from(EXISTENTIAL_NETWORK)
                .where(EXISTENTIAL_NETWORK.PARENT.equal(classification))
                .and(EXISTENTIAL_NETWORK.RELATIONSHIP.equal(classifier));
}

@Override
public List<ExistentialAttributeAuthorizationRecord> getAttributeAuthorizations(FacetRecord aspect,
                                                                                boolean includeGrouping) {

    SelectConditionStep<Record> and = create.selectDistinct(EXISTENTIAL_ATTRIBUTE_AUTHORIZATION.fields())
                                            .from(EXISTENTIAL_ATTRIBUTE_AUTHORIZATION)
                                            .where(EXISTENTIAL_ATTRIBUTE_AUTHORIZATION.FACET.equal(aspect.getId()));
    if (!includeGrouping) {
        and = and.and(EXISTENTIAL_ATTRIBUTE_AUTHORIZATION.AUTHORITY.isNull());
    }
    return and.fetch()
              .into(ExistentialAttributeAuthorizationRecord.class)
              .stream()
              .collect(Collectors.toList());
}

@Override
public List<ExistentialNetworkAuthorizationRecord> getNetworkAuthorizations(FacetRecord aspect,
                                                                            boolean includeGrouping) {

    SelectConditionStep<ExistentialNetworkAuthorizationRecord> and = create.selectFrom(EXISTENTIAL_NETWORK_AUTHORIZATION)
                                                                           .where(EXISTENTIAL_NETWORK_AUTHORIZATION.PARENT.equal(aspect.getId()));

    if (!includeGrouping) {
        and = and.and(EXISTENTIAL_NETWORK_AUTHORIZATION.AUTHORITY.isNull());
    }
    return and.fetch()
              .into(ExistentialNetworkAuthorizationRecord.class);
}

@Override
public void updateTransaction(UpdateTransactionParams p) {

    // -------------------------------------------------------------------------
    // Step 1: Update transaction table
    //
    // After update, a DB trigger sets the user.inTransaction field to 0
    // -------------------------------------------------------------------------

    ctx.update(TRANSACTION)
       .set(TRANSACTION.STOP_TIMESTAMP, p.getStopTimestamp())
       .set(TRANSACTION.STOP_VALUE, p.getStopMeterValue())
       .where(TRANSACTION.TRANSACTION_PK.equal(p.getTransactionId()))
       .and(TRANSACTION.STOP_TIMESTAMP.isNull())
       .and(TRANSACTION.STOP_VALUE.isNull())
       .execute();

    // -------------------------------------------------------------------------
    // Step 2: Set connector status back to "Available" again
    // -------------------------------------------------------------------------

    SelectConditionStep<Record1<Integer>> connectorPkQuery =
            DSL.select(TRANSACTION.CONNECTOR_PK)
               .from(TRANSACTION)
               .where(TRANSACTION.TRANSACTION_PK.equal(p.getTransactionId()));

    insertConnectorStatus(connectorPkQuery, p.getStopTimestamp(), p.getStatusUpdate());
}

/**
 * After a transaction start/stop event, a charging station _might_ send a connector status notification, but it is
 * not required. With this, we make sure that the status is updated accordingly. Since we use the timestamp of the
 * transaction data, we do not necessarily insert a "most recent" status.
 *
 * If the station sends a notification, we will have a more recent timestamp, and therefore the status of the
 * notification will be used as current. Or, if this transaction data was sent to us for a failed push from the past
 * and we have a "more recent" status, it will still be the current status.
 */
private void insertConnectorStatus(SelectConditionStep<Record1<Integer>> connectorPkQuery,
                                   DateTime timestamp,
                                   TransactionStatusUpdate statusUpdate) {
    ctx.insertInto(CONNECTOR_STATUS)
       .set(CONNECTOR_STATUS.CONNECTOR_PK, connectorPkQuery)
       .set(CONNECTOR_STATUS.STATUS_TIMESTAMP, timestamp)
       .set(CONNECTOR_STATUS.STATUS, statusUpdate.getStatus())
       .set(CONNECTOR_STATUS.ERROR_CODE, statusUpdate.getErrorCode())
       .execute();
}

@Override
public List<EasyTaxTaxCode> getTaxCodes(final UUID kbTenantId, @Nullable final String taxZone,
        @Nullable final String productName, @Nullable String taxCode, @Nullable DateTime date)
        throws SQLException {
    List<EasytaxTaxCodesRecord> records = execute(dataSource.getConnection(),
            new WithConnectionCallback<List<EasytaxTaxCodesRecord>>() {
                @Override
                public List<EasytaxTaxCodesRecord> withConnection(final Connection conn)
                        throws SQLException {
                    SelectConditionStep<EasytaxTaxCodesRecord> select = DSL
                            .using(conn, dialect, settings).selectFrom(EASYTAX_TAX_CODES)
                            .where(EASYTAX_TAX_CODES.KB_TENANT_ID.equal(kbTenantId.toString()));
                    if (taxZone != null) {
                        select = select.and(EASYTAX_TAX_CODES.TAX_ZONE.equal(taxZone));
                    }
                    if (productName != null) {
                        select = select.and(EASYTAX_TAX_CODES.PRODUCT_NAME.equal(productName));
                    }
                    if (taxCode != null) {
                        select = select.and(EASYTAX_TAX_CODES.TAX_CODE.equal(taxCode));
                    }
                    if (date != null) {
                        select = select.and(EASYTAX_TAX_CODES.VALID_FROM_DATE.lessOrEqual(date))
                                .and(EASYTAX_TAX_CODES.VALID_TO_DATE.isNull()
                                        .or(EASYTAX_TAX_CODES.VALID_TO_DATE.greaterThan(date)));
                        return select.orderBy(EASYTAX_TAX_CODES.VALID_FROM_DATE.desc()).fetch();
                    } else {
                        return select.orderBy(EASYTAX_TAX_CODES.RECORD_ID.asc()).fetch();
                    }

                }
            });
    if (records == null || records.isEmpty()) {
        return Collections.emptyList();
    }
    List<EasyTaxTaxCode> results = new ArrayList<>();
    for (EasytaxTaxCodesRecord record : records) {
        EasyTaxTaxCode result = new EasyTaxTaxCode();
        result.setCreatedDate(record.getCreatedDate());
        result.setKbTenantId(UUID.fromString(record.getKbTenantId()));
        result.setProductName(record.getProductName());
        result.setTaxZone(record.getTaxZone());
        result.setTaxCode(record.getTaxCode());
        result.setTaxRate(record.getTaxRate());
        result.setValidFromDate(record.getValidFromDate());
        if (record.getValidToDate() != null) {
            result.setValidToDate(record.getValidToDate());
        }
        results.add(result);
    }
    return results;
}

private SelectConditionStep<Record1<Integer>> selectAddressId(int chargeBoxPk) {
    return ctx.select(CHARGE_BOX.ADDRESS_PK)
              .from(CHARGE_BOX)
              .where(CHARGE_BOX.CHARGE_BOX_PK.eq(chargeBoxPk));
}

@Override
public void delete(DSLContext ctx, SelectConditionStep<Record1<Integer>> addressPkSelect) {
    ctx.delete(ADDRESS)
       .where(ADDRESS.ADDRESS_PK.eq(addressPkSelect))
       .execute();
}

private SelectConditionStep<Record1<Integer>> selectAddressId(int userPk) {
    return ctx.select(USER.ADDRESS_PK)
              .from(USER)
              .where(USER.USER_PK.eq(userPk));
}

private SelectConditionStep<Record1<Integer>> selectOcppTagPk(String ocppIdTag) {
    return ctx.select(OCPP_TAG.OCPP_TAG_PK)
              .from(OCPP_TAG)
              .where(OCPP_TAG.ID_TAG.eq(ocppIdTag));
}

private CSVSerializer extract(long categoryId) {
    return csvWriter -> {
        csvWriter.writeHeader(
                "Id",
                "Parent Id",
                "External Id",
                "Name",
                "Description",
                "Role",
                "Person",
                "Email");

        SelectConditionStep<Record> qry = dsl
                .select(MEASURABLE.NAME, MEASURABLE.DESCRIPTION, MEASURABLE.ID, MEASURABLE.PARENT_ID, MEASURABLE.EXTERNAL_ID)
                .select(INVOLVEMENT_KIND.NAME)
                .select(PERSON.DISPLAY_NAME, PERSON.EMAIL)
                .from(MEASURABLE)
                .leftJoin(INVOLVEMENT)
                .on(INVOLVEMENT.ENTITY_ID.eq(MEASURABLE.ID).and(INVOLVEMENT.ENTITY_KIND.eq(EntityKind.MEASURABLE.name())))
                .leftJoin(INVOLVEMENT_KIND)
                .on(INVOLVEMENT_KIND.ID.eq(INVOLVEMENT.KIND_ID))
                .leftJoin(PERSON)
                .on(PERSON.EMPLOYEE_ID.eq(INVOLVEMENT.EMPLOYEE_ID))
                .where(MEASURABLE.MEASURABLE_CATEGORY_ID.eq(categoryId));

        qry.fetch()
                .forEach(r -> {
                    try {
                        csvWriter.write(
                                r.get(MEASURABLE.ID),
                                r.get(MEASURABLE.PARENT_ID),
                                r.get(MEASURABLE.EXTERNAL_ID),
                                r.get(MEASURABLE.NAME),
                                r.get(MEASURABLE.DESCRIPTION),
                                r.get(INVOLVEMENT_KIND.NAME),
                                r.get(PERSON.DISPLAY_NAME),
                                r.get(PERSON.EMAIL));
                    } catch (IOException ioe) {
                        LOG.warn("Failed to write row: " + r, ioe);
                    }
                });
    };
}

private SelectSeekStep1<Record, Timestamp> orderBy(SelectConditionStep<Record> where) {
    return where.orderBy(PICTURES.CREATED_ON.desc());
}

private SelectConditionStep<Record1<Integer>> selectAddressId(int chargeBoxPk) {
    return ctx.select(CHARGE_BOX.ADDRESS_PK)
              .from(CHARGE_BOX)
              .where(CHARGE_BOX.CHARGE_BOX_PK.eq(chargeBoxPk));
}

@Override
public void delete(DSLContext ctx, SelectConditionStep<Record1<Integer>> addressPkSelect) {
    ctx.delete(ADDRESS)
       .where(ADDRESS.ADDRESS_PK.eq(addressPkSelect))
       .execute();
}

private SelectConditionStep<Record1<Integer>> selectAddressId(int userPk) {
    return ctx.select(USER.ADDRESS_PK)
              .from(USER)
              .where(USER.USER_PK.eq(userPk));
}

private SelectConditionStep<Record1<Integer>> selectOcppTagPk(String ocppIdTag) {
    return ctx.select(OCPP_TAG.OCPP_TAG_PK)
              .from(OCPP_TAG)
              .where(OCPP_TAG.ID_TAG.eq(ocppIdTag));
}

public static void main(String[] args) throws ParseException {

        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(DIConfiguration.class);

        DSLContext dsl = ctx.getBean(DSLContext.class);
        ChangeInitiativeDao dao = ctx.getBean(ChangeInitiativeDao.class);

        ChangeInitiative changeInitiative = dao.getById(1L);
        System.out.println(changeInitiative);



        SelectConditionStep<Record1<Long>> ouHier = dsl
                .selectDistinct(ENTITY_HIERARCHY.ANCESTOR_ID)
                .from(ENTITY_HIERARCHY)
                .where(ENTITY_HIERARCHY.ID.eq(210L)
                .and(ENTITY_HIERARCHY.KIND.eq(EntityKind.ORG_UNIT.name())));


        SelectConditionStep<Record1<Long>> baseIdSelector = dsl
                .selectDistinct(CHANGE_INITIATIVE.ID)
                .from(CHANGE_INITIATIVE)
                .where(CHANGE_INITIATIVE.ORGANISATIONAL_UNIT_ID.in(ouHier));


        SelectConditionStep<Record1<Long>> ciHier = dsl
                .selectDistinct(ENTITY_HIERARCHY.ANCESTOR_ID)
                .from(ENTITY_HIERARCHY)
                .where(ENTITY_HIERARCHY.ID.in(baseIdSelector)
                .and(ENTITY_HIERARCHY.KIND.eq(EntityKind.CHANGE_INITIATIVE.name())));


        dsl.select(CHANGE_INITIATIVE.NAME)
                .from(CHANGE_INITIATIVE)
                .where(CHANGE_INITIATIVE.ID.in(ciHier))
                .forEach(System.out::println);

    }

void delete(DSLContext ctx, SelectConditionStep<Record1<Integer>> addressPkSelect);

void delete(DSLContext ctx, SelectConditionStep<Record1<Integer>> addressPkSelect);