import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
public void addToQuery(OrderBy orderBy, Expression<?> sqlExpression, SQLQuery<Tuple> sqlQuery) {
    if (sqlExpression instanceof ComparableExpressionBase) {
        ComparableExpressionBase comparable = (ComparableExpressionBase) sqlExpression;
        Expression<?> projection = sqlQuery.getMetadata().getProjection();
        if (projection instanceof QTuple) {
            QTuple qTuple = (QTuple) projection;
            List<Expression<?>> args = new ArrayList<>(qTuple.getArgs());
            args.add(comparable);
            sqlQuery.select(args.toArray(new Expression[args.size()]));
        }

        if (orderBy.getType() == OrderBy.OrderType.Ascending) {
            sqlQuery.orderBy(comparable.asc());
        } else {
            sqlQuery.orderBy(comparable.desc());
        }
    }
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Collection<SchemeDTO> listWorkSchemes() {
  return querydslSupport.execute((connection, configuration) -> {
    QWorkScheme qWorkScheme = QWorkScheme.workScheme;
    return new SQLQuery<SchemeDTO>(connection, configuration)
        .select(Projections.fields(SchemeDTO.class, qWorkScheme.workSchemeId.as("schemeId"),
            qWorkScheme.name.as("name")))
        .from(qWorkScheme)
        .where(qWorkScheme.scope.eq(WORK_SCHEME_SCOPE_GLOBAL))
        .orderBy(qWorkScheme.name.asc())
        .fetch();
  });
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private static Expression<Long> replacementWeekdaySubSelect(final NumberPath<Long> workSchemeId,
    final Expression<Long> userId, final DatePath<Date> date) {

  QPublicHoliday qPublicHoliday = new QPublicHoliday("exp_work_repl_pubhday");
  QUserHolidayScheme qUserHolidayScheme = new QUserHolidayScheme("exp_work_repl_uhsch");
  QDateRange qDateRange = new QDateRange("exp_work_repl_uhschdr");

  SQLQuery<Long> query = new SQLQuery<Long>();
  query.select(weekdaySumForReplacementDay(workSchemeId, qPublicHoliday.date.dayOfWeek()))
      .from(qPublicHoliday)
      .innerJoin(qUserHolidayScheme)
      .on(qUserHolidayScheme.holidaySchemeId.eq(qPublicHoliday.holidaySchemeId))
      .innerJoin(qDateRange).on(qDateRange.dateRangeId.eq(qUserHolidayScheme.dateRangeId))
      .where(qPublicHoliday.replacementDate.eq(date)
          .and(qUserHolidayScheme.userId.eq(userId))
          .and(qDateRange.startDate.loe(date))
          .and(qDateRange.endDateExcluded.gt(date)));
  return query;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
public void addOrderbyToQuery(OrderBy orderBy, SQLQuery<Tuple> sqlQuery) {
    Expression<?> resultExpression = orderBy.getExpression().accept(this);
    if (resultExpression instanceof TimeIntervalExpression) {
        TimeIntervalExpression ti = (TimeIntervalExpression) resultExpression;
        addToQuery(orderBy, ti.getStart(), sqlQuery);
        addToQuery(orderBy, ti.getEnd(), sqlQuery);
    }
    if (resultExpression instanceof ConstantDurationExpression) {
        ConstantDurationExpression duration = (ConstantDurationExpression) resultExpression;
        addToQuery(orderBy, duration.getDuration(), sqlQuery);
    }
    if (resultExpression instanceof ListExpression) {
        for (Expression<?> sqlExpression : ((ListExpression) resultExpression).getExpressionsForOrder().values()) {
            addToQuery(orderBy, sqlExpression, sqlQuery);
        }
    } else {
        addToQuery(orderBy, resultExpression, sqlQuery);
    }
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private SQLQuery<Long> moderatedGroups(final Collection<Long> includedGameSpeciesIds, final int huntingYear) {
    final SQBasicClubHuntingSummary bchs = new SQBasicClubHuntingSummary("bchs");
    final SQHarvestPermitSpeciesAmount hpsa = new SQHarvestPermitSpeciesAmount("hpsa");
    final SQOrganisation group = new SQOrganisation("group");

    return SQLExpressions
            .select(group.organisationId)
            .from(bchs)
            .join(hpsa).on(hpsa.harvestPermitSpeciesAmountId.eq(bchs.speciesAmountId))
            .join(group).on(
                    group.parentOrganisationId.eq(bchs.clubId),
                    group.organisationType.eq(Expressions.asString(OrganisationType.CLUBGROUP.name())),
                    group.gameSpeciesId.eq(hpsa.gameSpeciesId),
                    group.harvestPermitId.eq(hpsa.harvestPermitId))
            .where(hpsa.gameSpeciesId.in(includedGameSpeciesIds),
                    bchs.moderatorOverride.isTrue(),
                    bchs.huntingFinished.isTrue(),
                    sqlDateInsideHuntingYear(bchs.huntingEndDate, huntingYear),
                    group.huntingYear.eq(huntingYear));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private static SubQueryExpression<Long> harvestLinkedToGroupHuntingDay(final long huntingGroupId) {
    /*
    SELECT h3.harvest_id
    FROM harvest h3
    WHERE h3.group_hunting_day_id IN (
      SELECT group_hunting_day_id
      FROM group_hunting_day
      WHERE hunting_group_id = :huntingGroupId)
    */
    final SQHarvest harvest = new SQHarvest("h3");
    final SQGroupHuntingDay groupHuntingDay = SQGroupHuntingDay.groupHuntingDay;

    final SQLQuery<Long> groupHuntingDayIds = SQLExpressions.selectOne()
            .from(groupHuntingDay)
            .where(groupHuntingDay.huntingGroupId.eq(huntingGroupId))
            .select(groupHuntingDay.groupHuntingDayId);

    return SQLExpressions.select(harvest.harvestId)
            .from(harvest)
            .where(harvest.groupHuntingDayId.in(groupHuntingDayIds));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private static SubQueryExpression<Long> gameObservationLinkedToGroupHuntingDay(
        final HuntingClubGroup huntingClubGroup) {
    /*
    SELECT o3.game_observation_id
    FROM game_observation o3
    WHERE o3.group_hunting_day_id IN (
      SELECT group_hunting_day_id
      FROM group_hunting_day
      WHERE hunting_group_id = :huntingGroupId)
    */
    final SQObservation gameObservation = new SQObservation("o3");
    final SQGroupHuntingDay groupHuntingDay = SQGroupHuntingDay.groupHuntingDay;

    final SQLQuery<Long> groupHuntingDayIds = SQLExpressions.selectOne()
            .from(groupHuntingDay)
            .where(groupHuntingDay.huntingGroupId.eq(huntingClubGroup.getId()))
            .select(groupHuntingDay.groupHuntingDayId);

    return SQLExpressions.select(gameObservation.gameObservationId)
            .from(gameObservation)
            .where(gameObservation.groupHuntingDayId.in(groupHuntingDayIds));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Map<String, Long> resolveProjectKeyIdMap(final Set<String> projectKeys) {
  Map<String, Long> result = querydslSupport.execute((connection, configuration) -> {
    List<Tuple> resultSet = new SQLQuery<>(connection, configuration)
        .select(QProject.project.pkey, QProject.project.id)
        .from(QProject.project)
        .where(QProject.project.pkey.in(projectKeys)).fetch();

    Map<String, Long> localResult = new HashMap<>();
    for (Tuple tuple : resultSet) {
      localResult.put(tuple.get(QProject.project.pkey), tuple.get(QProject.project.id));
    }
    return localResult;
  });

  checkNoProjectKeyMissing(projectKeys, result.keySet());
  return result;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private boolean hasOverlapping(final Long userId, final Date startDate,
    final Date endDateExcluded, final Long userHolidayAmountIdToExclude) {
  Long count = querydslSupport.execute((connection, configuration) -> {
    QDateRange qDateRange = QDateRange.dateRange;
    QUserHolidayAmount qUserHolidayAmount = QUserHolidayAmount.userHolidayAmount;
    SQLQuery<Long> query = new SQLQuery<>(connection, configuration)
        .select(qDateRange.dateRangeId)
        .from(qDateRange)
        .innerJoin(qUserHolidayAmount)
        .on(qDateRange.dateRangeId.eq(qUserHolidayAmount.dateRangeId));

    List<Predicate> predicates = new ArrayList<>();
    predicates.add(qUserHolidayAmount.userId.eq(userId));

    if (userHolidayAmountIdToExclude != null) {
      predicates.add(qUserHolidayAmount.userHolidayAmountId.ne(userHolidayAmountIdToExclude));
    }

    predicates.add(rangeOverlaps(qDateRange, startDate, endDateExcluded));

    query.where(predicates.toArray(new Predicate[predicates.size()]));
    return query.fetchCount();
  });
  return count > 0;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void delete(final Long userSchemeId) {
  transactionTemplate.execute(() -> querydslSupport.execute((connection, configuration) -> {
    QDateRange qDateRange = QDateRange.dateRange;
    Long dateRangeId = new SQLQuery<Long>(connection, configuration)
        .select(qDateRange.dateRangeId)
        .from(qDateRange)
        .innerJoin(qUserSchemeEntityParameter.userSchemeEntityPath)
        .on(qUserSchemeEntityParameter.dateRangeId.eq(qDateRange.dateRangeId))
        .where(qUserSchemeEntityParameter.userSchemeId.eq(userSchemeId))
        .fetchOne();

    new SQLDeleteClause(connection, configuration,
        qUserSchemeEntityParameter.userSchemeEntityPath)
            .where(qUserSchemeEntityParameter.userSchemeId.eq(userSchemeId))
            .execute();

    new DateRangeUtil(connection, configuration).removeDateRange(dateRangeId);
    return null;
  }));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void save(final long schemeId, final String userName, final Date startDate,
    final Date endDateExcluded) {
  transactionTemplate.execute(() -> querydslSupport.execute((connection, configuration) -> {
    QCwdUser qCwdUser = QCwdUser.cwdUser;
    Long userId = new SQLQuery<Long>(connection, configuration)
        .select(qCwdUser.id)
        .from(qCwdUser)
        .where(qCwdUser.userName.eq(userName)).fetchOne();

    Long dateRangeId =
        new DateRangeUtil(connection, configuration).createDateRange(startDate, endDateExcluded);

    new SQLInsertClause(connection, configuration,
        qUserSchemeEntityParameter.userSchemeEntityPath)
            .set(qUserSchemeEntityParameter.dateRangeId, dateRangeId)
            .set(qUserSchemeEntityParameter.userId, userId)
            .set(qUserSchemeEntityParameter.userSchemeSchemeId, schemeId).execute();

    return null;
  }));
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void update(final long recordId, final long schemeId, final long userId,
    final Date startDate, final Date endDateExcluded) {
  transactionTemplate.execute(() -> querydslSupport.execute((connection, configuration) -> {

    Long dateRangeId = new SQLQuery<Long>(connection, configuration)
        .select(qUserSchemeEntityParameter.dateRangeId)
        .from(qUserSchemeEntityParameter.userSchemeEntityPath)
        .where(qUserSchemeEntityParameter.userSchemeId.eq(recordId)).fetchOne();

    new DateRangeUtil(connection, configuration).modifyDateRange(dateRangeId, startDate,
        endDateExcluded);

    new SQLUpdateClause(connection, configuration,
        qUserSchemeEntityParameter.userSchemeEntityPath)
            .set(qUserSchemeEntityParameter.userId, userId)
            .where(qUserSchemeEntityParameter.userSchemeId.eq(recordId));
    return null;
  }));

}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Set<WeekdayWorkDTO> getWeekdayWorks(final long workSchemeId) {

    List<WeekdayWorkDTO> resultSet = querydslSupport.execute((connection, configuration) -> {
      QWeekdayWork qWeekdayWork = QWeekdayWork.weekdayWork;
      return new SQLQuery<WeekdayWorkDTO>(connection, configuration)
          .select(
              Projections.fields(WeekdayWorkDTO.class, qWeekdayWork.weekdayWorkId,
                  qWeekdayWork.weekday, qWeekdayWork.startTime, qWeekdayWork.duration))
          .from(qWeekdayWork)
          .where(qWeekdayWork.workSchemeId.eq(workSchemeId))
          .fetch();
    });
    TreeSet<WeekdayWorkDTO> weekdayWorks = new TreeSet<WeekdayWorkDTO>(WEEKDAY_WORK_DTO_COMPARATOR);
    weekdayWorks.addAll(resultSet);

    return weekdayWorks;
  }
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void removeAllUsersFromScheme(final long schemeId, final Connection connection,
    final Configuration configuration) {

  final int batchSize = 100;
  QUserWorkScheme qUserWorkScheme = QUserWorkScheme.userWorkScheme;

  SQLQuery<Long> sqlQuery = new SQLQuery<Long>(connection, configuration)
      .select(qUserWorkScheme.dateRangeId).from(qUserWorkScheme)
      .where(qUserWorkScheme.workSchemeId.eq(schemeId)).limit(batchSize);

  List<Long> dateRangeIds = sqlQuery.fetch();
  DateRangeUtil dateRangeUtil = new DateRangeUtil(connection, configuration);

  while (!dateRangeIds.isEmpty()) {
    new SQLDeleteClause(connection, configuration, qUserWorkScheme)
        .where(qUserWorkScheme.dateRangeId.in(dateRangeIds)).execute();

    dateRangeUtil.removeDateRange(dateRangeIds.toArray(new Long[dateRangeIds.size()]));

    dateRangeIds = sqlQuery.fetch();
  }

}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Object synchronizeDateSequenceIfNecessary(final Connection connection,
    final Configuration configuration) {

  QDateSequence qDateSequence = QDateSequence.dateSequence;
  long dateSequenceCount = new SQLQuery<>(connection, configuration)
      .select(Expressions.ONE.count()).from(qDateSequence).fetchOne();

  if (dateSequenceCount == 0) {
    QDateRange qDateRange = QDateRange.dateRange;
    long dateRangeCount = new SQLQuery<>(connection, configuration)
        .select(Expressions.ONE.count()).from(qDateRange).fetchOne();
    if (dateRangeCount != 0) {
      fillDateSequenceFromDateRanges(connection, configuration, dateRangeCount);
    }
  }
  return null;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private Collection<PublicHolidayDTO> listPublicHolidays(final long schemeId, final Integer year) {
  if (year == null) {
    return Collections.emptySet();
  }
  return querydslSupport.execute((connection, configuration) -> {
    QPublicHoliday qPublicHoliday = QPublicHoliday.publicHoliday;
    return new SQLQuery<PublicHolidayDTO>(connection, configuration)
        .select(Projections.fields(PublicHolidayDTO.class, qPublicHoliday.publicHolidayId,
            qPublicHoliday.date, qPublicHoliday.replacementDate, qPublicHoliday.description))
        .from(qPublicHoliday)
        .where(qPublicHoliday.holidaySchemeId.eq(schemeId).and(qPublicHoliday.date.year().eq(year)
            .or(qPublicHoliday.replacementDate.year().eq(year))))
        .orderBy(qPublicHoliday.date.asc())
        .fetch();
  });
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private void removeAllUsersFromScheme(final long schemeId, final Connection connection,
    final Configuration configuration) {

  final int batchSize = 100;
  QUserHolidayScheme qUserHolidayScheme = QUserHolidayScheme.userHolidayScheme;

  SQLQuery<Long> sqlQuery = new SQLQuery<Long>(connection, configuration)
      .select(qUserHolidayScheme.dateRangeId).from(qUserHolidayScheme)
      .where(qUserHolidayScheme.holidaySchemeId.eq(schemeId)).limit(batchSize);

  List<Long> dateRangeIds = sqlQuery.fetch();

  while (!dateRangeIds.isEmpty()) {
    new SQLDeleteClause(connection, configuration, qUserHolidayScheme)
        .where(qUserHolidayScheme.dateRangeId.in(dateRangeIds)).execute();

    new DateRangeUtil(connection, configuration)
        .removeDateRange(dateRangeIds.toArray(new Long[dateRangeIds.size()]));

    dateRangeIds = sqlQuery.fetch();
  }

}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
public static QAvatar joinAvatarToAppUser(final SQLQuery<?> query,
    final QAppUser qAppUser, final String variablePrefix) {

  QPropertyentry qPropertyEntry =
      new QPropertyentry(resolveVariableName(QPropertyentry.propertyentry, variablePrefix));

  QPropertynumber qPropertyNumber =
      new QPropertynumber(resolveVariableName(QPropertynumber.propertynumber, variablePrefix));

  QAvatar qAvatar = new QAvatar(resolveVariableName(QAvatar.avatar, variablePrefix));

  query.leftJoin(qPropertyEntry).on(qPropertyEntry.entityId.eq(qAppUser.id)
      .and(qPropertyEntry.propertyKey.eq("user.avatar.id")))
      .leftJoin(qPropertyNumber).on(qPropertyEntry.id.eq(qPropertyNumber.id))
      .leftJoin(qAvatar).on(qAvatar.id.eq(qPropertyNumber.propertyvalue));

  return qAvatar;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
/**
 * Getting the expected work amount for a date period. The calculation for each day in the range
 * is done in the following order:
 * <ol>
 * <li>if there is an exact work day specified for a specific date, that is used. Otherwise</li>
 * <li>if there is a replacement day of a public holiday and there is a weekday specified for the
 * replaced holiday, that is used. Otherwise</li>
 * <li>If there is a non holiday weekday, that is used. Otherwise</li>
 * <li>zero is used.</li>
 * </ol>
 *
 * @param userId
 *          The id of the user who should have done the work.
 * @param startDate
 *          The date when we start calculating the work that should be done from.
 * @param endDateExcluded
 *          The date until we calculate the work that should be done (excluded).
 * @return The amount of work that should be done by the specified user in the specific time
 *         period.
 */
public static SQLQuery<Long> expectedWorkAmount(final Expression<Long> userId,
    final Expression<Date> startDate, final Expression<Date> endDateExcluded) {

  QDateSequence qDates = new QDateSequence("exp_work_dates");
  QDateRange qDateRange = new QDateRange("exp_work_date_range");
  QUserWorkScheme qUserWorkScheme = new QUserWorkScheme("exp_work_user_work_scheme");

  SQLQuery<Long> query = new SQLQuery<>();
  query.select(
      new Coalesce<Long>(Long.class,
          exactWorkSubSelect(qUserWorkScheme.workSchemeId, qDates.date),
          replacementWeekdaySubSelect(qUserWorkScheme.workSchemeId, userId, qDates.date),
          nonHolidayWeekdaySubSelect(qUserWorkScheme.workSchemeId, userId, qDates.date),
          Expressions.ZERO)
              .asNumber().sum());
  query
      .from(qDates)
      .innerJoin(qDateRange)
      .on(qDateRange.startDate.loe(qDates.date).and(qDateRange.endDateExcluded.gt(qDates.date)))
      .innerJoin(qUserWorkScheme).on(qUserWorkScheme.dateRangeId.eq(qDateRange.dateRangeId))
      .where(qDates.date.goe(startDate).and(qDates.date.lt(endDateExcluded))
          .and(qUserWorkScheme.userId.eq(userId)));

  return query;
}
 

import com.querydsl.sql.SQLQuery; //导入依赖的package包/类
private static Predicate noHolidayExistsSubSelect(final Expression<Long> userId,
    final DatePath<Date> date) {
  QPublicHoliday qPublicHoliday = new QPublicHoliday("exp_work_nh_ph");
  QUserHolidayScheme qUserHolidayScheme = new QUserHolidayScheme("exp_work_nh_uhsch");
  QDateRange qDateRange = new QDateRange("exp_work_nh_data_range");

  return new SQLQuery<>().select(qPublicHoliday.publicHolidayId).from(qPublicHoliday)
      .innerJoin(qUserHolidayScheme)
      .on(qUserHolidayScheme.holidaySchemeId.eq(qPublicHoliday.holidaySchemeId))
      .innerJoin(qDateRange).on(qDateRange.dateRangeId.eq(qUserHolidayScheme.dateRangeId))
      .where(qUserHolidayScheme.userId.eq(userId)
          .and(qDateRange.startDate.loe(date))
          .and(qDateRange.endDateExcluded.gt(date))
          .and(qPublicHoliday.date.eq(date)))
      .notExists();
}