Service sums

Accelerated summation and grouping in the form of an OCL operator is available for optimizing custom evaluations or for use in custom reports.

The groupServices operator exists in different variants, which differ mainly in that they are applicable to different types of objects:

The syntax for all these operators is as follows:

obj->groupServicesOperator(from, to, group)

The result is a list of objects of type ServiceSum, which are sorted according to the specified grouping.

A service sum object has different attributes and a value for each grouping term:

On a project (or a list of projects) the groupServicesP operator is called with the following arguments:

• Date period is January to December
• Sums are grouped by months and projects

->groupServicesP('01.01.2024','31.12.2024','MONTH,PROJECT’)

As a result, we get a list of LeistSum objects, including the following attributes:

• Month: entrydate.formatDateTime('mm.yyyy')
• Project: project
• Internal hours: minutesintOpen + minutesIntCharged
• External fee: feeExtOpen + feeExtCharged

Special cases for the calculation arise, for example, in the case of fixed-price invoices without services or in the case of invoice discounts. In this case, the external value of the services cannot simply be used for the calculation of the service sums. How these special cases are dealt with in detail is explained below:

As a result of these special cases, deviations may arise between the sum of the services and the service sums. If, for example:

openServices->union(charged)->select((entrydate >= encodedate(2024,1,1)) 
and (entrydate < encodedate(2025,1,1))).wertExt->sum

returns a different value than:

self->groupServicesP('01.01.2024','31.12.2024','')->collect(feeExtOpen + feeExtCharged)->sum

check whether it has any of the above-mentioned special cases in the period analysed.

In the case of fixed services on fixed-price phases or invoices, no difference can be found here, since neither the services nor the service sums include the fixed amount. In this case, you will only see the difference if you compare the sums of the services with the sums of the invoices.

Vertec ships with Firebird 5 as of version 6.7.0.9 and later, this issue no longer exists. However, if a groupServicesOperator is called on a list of projects, phases, or users with an earlier version of Firebird, there is the following limitation:

If the list of calling objects (i.e. not the quantity of services in the result, but the projects, phases or users on which the operator is called) is greater than 1500 entries, there is an error from the Firebird server due to invalid SQL:

General SQL Error.
Implementation limit exceeded
too many values (more than 1500) in member list
to match against.

One way to work around this problem is to use the global groupServices operator, which applies to all services.

Example: If there are more than 1500 projects and you want to call project->groupServicesP(...), you can work around this:

Session.allInstances->first->groupServices('01.01.2025', '30.06.2025', 'PROJECT')

Returns a list of service sums for all services, grouped by projects.

Since the service sums are calculated optimally via SQL, the behavior of changes to basic data is slightly different than otherwise in Vertec.

Service sums are always recalculated as soon as any service is changed in the system. However, the recalculation only takes place if the service sum values are somehow visible. It is therefore not advisable to have a list of service sums on the screen at the same time as entering new services, as all sums would be recalculated with each new entry. Calculating service sums is relatively fast, but if you recalculate unnecessarily often, the delay is still noticeable.

In particular, it is not recommended to use groupServicesOperators in column expressions of lists, since the performance gain in calculating the sum is usually overcompensated by the large quantity of individual SQL queries.

Various values in Vertec are derived, which means that they are calculated each time they are accessed. This means that they are always up-to-date, but not retrievable in the database.

To ensure that these values are still available for the accelerated summation, there are so-called shadow attributes, which are written when calculating the derived attributes. These shadow attributes are persistent, i.e. they are stored in the database and can be queried directly.

The following shadow attributes are available. They are prefixed with sql.