@Override public Optional<List<Object[]>> getPopulateTempTableContent() { List<Object[]> batchArgs = new ArrayList<>(); for ( DataElement dataElement : objects ) { List<Object> values = new ArrayList<>(); final DataSet dataSet = dataElement.getApprovalDataSet(); final PeriodType periodType = dataElement.getPeriodType(); // ----------------------------------------------------------------- // Use highest approval level if data set does not require approval, // or null if approval is required. // ----------------------------------------------------------------- values.add( dataElement.getId() ); values.add( dataElement.getUid() ); values.add( dataElement.getName() ); values.add( dataSet != null ? dataSet.getId() : null ); values.add( dataSet != null ? dataSet.getUid() : null ); values.add( dataSet != null ? dataSet.getName() : null ); values.add( dataSet != null && dataSet.isApproveData() ? null : APPROVAL_LEVEL_HIGHEST ); values.add( dataSet != null && dataSet.isApproveData() ? dataSet.getWorkflow().getId() : null ); values.add( periodType != null ? periodType.getId() : null ); values.add( periodType != null ? periodType.getName() : null ); batchArgs.add( values.toArray() ); } return Optional.of( batchArgs ); }
@Override public List<Period> getPeriodsByPeriodType( PeriodType periodType ) { String query = "from Period p where p.periodType.id =:periodType"; Query<Period> typedQuery = getQuery( query ) .setParameter( "periodType", reloadPeriodType( periodType ).getId() ); return getList( typedQuery ); }
@Override public List<Period> getPeriodsBetweenDates( PeriodType periodType, Date startDate, Date endDate ) { String query = "from Period p where p.startDate >=:startDate and p.endDate <=:endDate and p.periodType.id =:periodType"; Query<Period> typedQuery = getQuery( query ) .setParameter( "startDate", startDate ) .setParameter( "endDate", endDate ) .setParameter( "periodType", reloadPeriodType( periodType ).getId() ); return getList( typedQuery ); }
/** * Get the id for the workflow period that spans the given end date. * The workflow period may or may not be the same as the period for which * we are checking data validity. The workflow period will have a period * type that matches the workflow period type, and it will contain the * end date of the period for which we are checking data validity. * * Returns zero if there is no such workflow period. * * It turns out that this is much faster done as a separate query in * postgresql than imbedding this as a subquery in the larger query above. * * @param workflow workflow we are checking * @param endDate end date of the period we are checking approval for, * formatted as a string for a SQL query. * @return id of the workflow period which overlaps with the endDate */ private int getWorkflowPeriodId( DataApprovalWorkflow workflow, String endDate ) { final String sql = "select periodid from period where '" + endDate + "' >= startdate and '" + endDate + "' <= enddate and periodtypeid = " + workflow.getPeriodType().getId(); SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql ); if ( rowSet.next() ) { return rowSet.getInt( 1 ); } return 0; }
@Override public Period getPeriodFromDates( Date startDate, Date endDate, PeriodType periodType ) { String query = "from Period p where p.startDate =:startDate and p.endDate =:endDate and p.periodType.id =:periodType"; Query<Period> typedQuery = getQuery( query ) .setParameter( "startDate", startDate ) .setParameter( "endDate", endDate ) .setParameter( "periodType", reloadPeriodType( periodType ).getId() ); return getSingleResult( typedQuery ); }
@Override public List<Period> getIntersectingPeriodsByPeriodType( PeriodType periodType, Date startDate, Date endDate ) { String query = "from Period p where p.startDate <=:endDate and p.endDate >=:startDate and p.periodType.id =:periodType"; Query<Period> typedQuery = getQuery( query ) .setParameter( "startDate", startDate ) .setParameter( "endDate", endDate ) .setParameter( "periodType", reloadPeriodType( periodType ).getId() ); return getList( typedQuery ); }
String periodTypeIdList = getCommaDelimitedString( params.getPeriodTypes().stream().map( o -> o.getId() ).collect( Collectors.toList() ) );