Validate and Filter Validity Periods for Price Elements with @cds.valid.from/to

[tklein1801]

Hi everyone, I have a CAP project that uses the NodeJS/ExpressJS backend.
In my project I have entities which are provided with a validity period by adding validFrom and validTo.
In order to display only the (currently) active price elements in the application, I have annotated the fields that define the validity period of an element with the respective annotation @cds.valid.from/to.

Here are the entities I need for this

aspect priceElement : cuid, managed {
    @title: '{i18n>PriceConditionType}'
    toCondition : Association to one PriceCondition;

    @title: '{i18n>PriceElementValue}'
    value       : Double;

    @title: '{i18n>PriceElementFormat}'
    format      : String default '%';

    @title: '{i18n>GeneralValidFrom}'
    validFrom   : Date @cds.valid.from;

    @title: '{i18n>GeneralValidTo}'
    validTo     : Date @cds.valid.to;
}

@plural       : 'HeaderPriceElements'
@assert.unique: {contract_condition: [
    toContract,
    toCondition,
    validFrom
]}
entity HeaderPriceElement : priceElement {
    @title: '{i18n>ContractName}'
    toContract : Association to one Contract;
}

@plural: 'ItemPriceElements'
entity ItemPriceElement : priceElement {
    @title: '{i18n>ContractItemName}'
    toContractItem : Association to one ContractItem;
}

This raises two questions/problems for me

1. Is it somehow possible for me to set a filter in the application to view all price elements over the entire time period?
   I know that I can send a Time-Period Query directly to the remaining endpoint to retrieve all price elements from a certain date.
   Can I also set such a filter in the frontend?
2. Validation of the validity periods
   In order to be able to validate that the validity periods of a condition for an entity do not overlap, I would like to validate the periods so that I can assure that only one condition value is active at a time.
   I am currently writing something for this in the backend. However, to ensure a seamless transition between the price elements, I need to be able to select them and check the time periods.
   However, the @cds.valid.from/to annotation also restricts the direct selection to the table by the annotation to only active elements.
   How can I then validate that the time periods do not overlap and have seamless transitions?

Local data

ID;createdAt;createdBy;modifiedAt;modifiedBy;toCondition_condition;toContract_ID;value;format;validFrom;validTo
d44615ce-c58b-4144-8d87-f7964ed8a5bb;2024-10-05T12:00:00+0200;t00707;2024-10-05T12:00:00+0200;t00707;PFBS;5783fdf8-e2a5-452f-8e3e-65e79200551b;100000;€;2015-01-01;2030-12-31
d44615ce-c58b-4144-8d87-f7964ed8a5ab;2024-10-05T12:00:00+0200;t00707;2024-10-05T12:00:00+0200;t00707;PFPS;5783fdf8-e2a5-452f-8e3e-65e79200551b;17;%;2015-01-01;2024-12-31
d44615ce-c58b-4144-8d87-f7964ed8a5cb;2024-10-05T12:00:00+0200;t00707;2024-10-05T12:00:00+0200;t00707;PFPS;5783fdf8-e2a5-452f-8e3e-65e79200551b;19;%;2025-01-01;2030-12-31

Code for selection

        // --------------------------------------------------------------------------
        // Prüfe ob die Zeiträume nahtlos aneinander angrenzen
        const [InactiveConditionElements, ActiveConditionElements] = await Promise.all([
            this.getAllElementsByConditionAndContract(
                fullDraft.toContract_ID!,
                fullDraft.toCondition_condition!,
                false,
            ),
            this.getAllElementsByConditionAndContract(fullDraft.toContract_ID!, fullDraft.toCondition_condition!, true),
        ]);
        this.logger.debug('ConditionElements', ActiveConditionElements, InactiveConditionElements);

        // --------------------------------------------------------------------------

    static async getAllElementsByConditionAndContract<T extends boolean = true>(
        contractId: Contract['ID'],
        condition: PriceConditionType,
        activeEntities: T = true as T,
    ): Promise<T extends true ? HeaderPriceElements : DraftEntity<HeaderPriceElement>[]> {
        return await SELECT.from(activeEntities ? HeaderPriceElements : HeaderPriceElements.drafts)
            .where({
                toContract_ID: contractId,
                and: {
                    toCondition_condition: {'=': condition},
                },
            })
            .orderBy('validFrom');
    }

Selection result

Important

In total, both arrays should provide 2 entries each, as the CSV file already contains a condition that is no longer valid

[PriceElementController] - ConditionElements [
  {
    ID: 'd44615ce-c58b-4144-8d87-f7964ed8a5cb',
    createdAt: '2024-10-05T10:00:00.000Z',
    createdBy: 't00707',
    modifiedAt: '2024-10-05T10:00:00.000Z',
    modifiedBy: 't00707',
    toCondition_condition: 'PFPS',
    value: 19,
    format: '%',
    validFrom: '2025-01-01',
    validTo: '2030-12-31',
    toContract_ID: '5783fdf8-e2a5-452f-8e3e-65e79200551b',
    ConditionDescription: 'Prozentuale Berechnung der Pflegekosten',
    ConditionIsAbsolute: false,
    ConditionOrder: 30
  }
] [
  {
    ID: 'f346779b-918a-4147-8fe7-94b1a4205a88',
    createdAt: '2025-03-21T12:55:44.351Z',
    createdBy: 'privileged',
    modifiedAt: '2025-03-21T12:55:47.005Z',
    modifiedBy: 'privileged',
    toCondition_condition: 'PFPS',
    value: null,
    format: '%',
    validFrom: '2015-01-01',
    validTo: '2030-12-31',
    toContract_ID: '5783fdf8-e2a5-452f-8e3e-65e79200551b',
    ConditionDescription: null,
    ConditionIsAbsolute: true,
    ConditionOrder: null,
    HasActiveEntity: false,
    DraftAdministrativeData_DraftUUID: 'd8036e6d-bcae-4f68-b5df-6119e89e7d1b'
  },
  {
    ID: 'd44615ce-c58b-4144-8d87-f7964ed8a5cb',
    createdAt: '2024-10-05T10:00:00.000Z',
    createdBy: 't00707',
    modifiedAt: '2024-10-05T10:00:00.000Z',
    modifiedBy: 't00707',
    toCondition_condition: 'PFPS',
    value: 19,
    format: '%',
    validFrom: '2025-01-01',
    validTo: '2030-12-31',
    toContract_ID: '5783fdf8-e2a5-452f-8e3e-65e79200551b',
    ConditionDescription: 'Prozentuale Berechnung der Pflegekosten',
    ConditionIsAbsolute: false,
    ConditionOrder: 30,
    HasActiveEntity: true,
    DraftAdministrativeData_DraftUUID: 'd8036e6d-bcae-4f68-b5df-6119e89e7d1b'
  }
]