> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Dates filter bean

export const productName = "Atoti FRTB";

# Dates to Include Filter

When using DirectQuery you may want to only include a subset of data available on the remote database in a manner that the default [`directQueryDatesFilter`](#default-implementation) does not support.

The `directQueryDatesFilter` bean is used to tell {productName} which dates to include (or exclude) from the DirectQuery data node cube. This creates an `ICondition` that filters the data cube based on the `AsOfDate` field.

## Default Implementation

The default implementation of the `directQueryDatesFilter` bean will either include or exclude all the dates provided to the [`DirectQueryDatesFilterSpringProperties`](../configuration-getting-started/configuration-properties#dates-filter) configuration properties.

The default bean is defined in `DirectQueryDatesFilterConfig` as:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
@Bean
@ConditionalOnMissingBean
public ICondition directQueryDatesFilter(@Autowired DirectQueryDatesFilterSpringProperties directQueryDatesToIncludeSpringProperties){

	var datesToInclude = directQueryDatesToIncludeSpringProperties.datesToInclude();
	if(datesToInclude != null && !datesToInclude.isEmpty()){
		log.info("The following dates will be included in DirectQuery: " + datesToInclude);
		// Allow DirectQuery node to include any of the following dates:
		return includeAllDates(datesToInclude);
	}

	final var datesToExclude = directQueryDatesToIncludeSpringProperties.datesToExclude();
	if(datesToExclude != null && !datesToExclude.isEmpty()) {
		log.info("The following dates will be excluded in DirectQuery: " + datesToExclude);
		// Restrict DirectQuery node to exclude all of the following dates:
		return excludeAllDates(datesToExclude);
	}

	// No condition to create
	return null;
}
```

The `DirectQueryDatesFilterConfig` class also includes two helper methods that can be used to create simple conditions:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
/**
* Creates a cube {@link ICondition} that will INCLUDE all the provided dates.
*/
protected ICondition includeAllDates(Collection<LocalDate> datesToInclude){
    return new OrCondition(datesToInclude.stream()
		    .map(date ->
				    // Condition: AsOfDate Cube Field == date
				    new SubCondition(AS_OF_DATE, new EqualCondition(date))
		    ).toList()
    );
}

/**
* Creates a cube {@link ICondition} that will EXCLUDE all the provided dates.
*/
protected ICondition excludeAllDates(Collection<LocalDate> datesToExclude){
    return new AndCondition(datesToExclude.stream()
		    .map(date ->
				    // Condition: AsOfDate Cube Field != date
				    new SubCondition(AS_OF_DATE, new NotCondition(new EqualCondition(date)))
		    ).toList()
    );
}
```

## Custom Implementations

To override this bean you need to create a `@Bean` that returns an `com.quartetfs.fwk.filtering.ICondition`. This will replace the default
`directQueryDatesFilter` bean with your custom bean.

### SubConditions

When creating a custom condition, it is important to ensure each nested condition is wrapped in a `SubCondition` where the `AsOfDate` is used as the matching field. Below are some examples.

## Examples

Here are some simple examples of how to customize the `directQueryDatesFilter` bean.

### Include All Available Data in DirectQuery

If we wanted to include all data available on our remote database, we simply return null and there will be no condition on what data can be loaded into the
DirectQuery data node:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
@Configuration
public class CustomDatesToIncludeBeanConfig {
	@Bean
	public ICondition noRestrictions() {
		// No restrictions on the DirectQuery node
		return null;
	}
}
```

### Only Include Specific Dates

In this example, we are giving the DirectQuery data node access to a specific set of dates. Here we do not extend the `InitialDataDirectQueryConfig` and instead create our own `ICondition` from scratch to include only the dates we want.

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
@Configuration
public class CustomDatesToIncludeBeanConfig {
	@Bean
	public ICondition onlyIncludeTheseDates() {
		// Restrict DirectQuery node to INCLUDE the specific dates:
		List<SubCondition> includeConditions = new ArrayList<>();
		// Add our custom dates
		includeConditions.add(new SubCondition(AS_OF_DATE, new EqualCondition(LocalDate.of(2024, 1, 1))));
		includeConditions.add(new SubCondition(AS_OF_DATE, new EqualCondition(LocalDate.of(2024, 1, 2))));
		includeConditions.add(new SubCondition(AS_OF_DATE, new EqualCondition(LocalDate.of(2024, 1, 3))));
		// Use an OR condition so that any of the dates can be included
		return new OrCondition(includeConditions);
	}
}
```

### Include Year of Dates

In this example, we are extending the `InitialDataDirectQueryConfig` and using its helper methods to construct our condition. This will include all dates from the last year up to today.

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
@Configuration
public static class CustomDatesToIncludeBeanConfig extends InitialDataDirectQueryConfig {
	@Bean
	public ICondition includeOneYearOfData(){
		// Include a year worth of dats in DQ data node:
		final LocalDate today = LocalDate.now();
		final LocalDate lastYear = today.minusYears(1);
		final List<LocalDate> yearOfDatesToInclude = lastYear.datesUntil(today).toList();
		// Allow DirectQuery node to include one year worth of dates:
		return super.includeAllDates(yearOfDatesToInclude);
	}
}
```

### Include All Data in Database Except This Week

In this example, we are extending the `InitialDataDirectQueryConfig` and using its helper methods to construct our condition. This will exclude the last seven days (including today) from the DirectQuery data node.

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
@Configuration
public static class CustomDatesToIncludeBeanConfig extends InitialDataDirectQueryConfig {
	@Bean
	public ICondition ecludeThisWeek(){
		// Exclude the latest seven days (including today)
		final LocalDate today = LocalDate.now();
		final LocalDate firstDayInMemory = today.minusDays(7);
		final List<LocalDate> daysIncludedIn_InMemoryCube = firstDayInMemory.datesUntil(today.plusDays(1)).toList();
		// Exclude these dates from the DirectQuery node
		return super.excludeAllDates(daysIncludedIn_InMemoryCube);
	}
}
```