Service sums

Accelerated summation and grouping in the form of an OCL operator is available for optimizing customer-specific evaluations or for use in user-defined reports.

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

The syntax for all these operators is as follows:

<obj>->groupLeistungen(<von>, <bis>, <group>)

The arguments (in square brackets) of the groupLeistungen operator have the following meanings:

<obj>->groupLeistungen(encodeDate(2019,01,01).dateToStrGerman, date.dateToStrGerman, 'PROJEKT')

Import datetime

def DatumGermanFormat(datum):
    """Gibt das Datum in deutschem Format zurück, dd.mm.yyy, als String. 
       Ist das Datum leer, wird ein Leerstring zurückgegeben."""
       
    if datum:
        return datum.strftime("%d.%m.%Y")
    else:
        return ''

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

These performance sum objects have different sum attributes as well as a value for each grouping term. More specifically, these are the following properties:

On a project (or a list of projects) the operator groupLeistungenP is applied. The date period is January to December. We want the totals grouped by months and projects, the given grouping statement ('MONTH,PROJECT’) does this.

->groupServicesP('01.01.2016','31.12.2016','MONTH,PROJEKT’)

As a result, we get a list of LeistSum objects. These include, for example, the following attributes:

• Month: datum.formatDateTime('mm.yyyy')
• Project: projekt
• Internal hours: minutenintOffen + minutenIntVerrechnet
• External value: wertExtOffen + wertExtVerrechnet

Special cases for 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 to calculate the service sums. How these special cases are dealt with in detail is explained below:

On the basis of these special cases, deviation can of course arise between the sum of the services and the summed sums of the services. In this case, for example:

offeneleistungen->union(verrechneteleistungen)->select((datum >= encodedate(1990,1,1)) 
and (datum < encodedate(2018,1,1))).wertExt->sum

a value other than:

self->groupleistungenP('01.01.1990','01.01.2018','')->collect(wertExtOffen + wertExtVerrechnet)->sum

If this is the case for you, check if it has any of the above-mentioned special cases in the analysed period.

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

Vertec ships with Firebird 5 from version 6.7.0.9 onwards. Starting with this version, this problem no longer exists. However, if a groupLeistungenX operator is called on a list of projects, phases, or users with an earlier Firebird version, 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 groupLeistungen operator, which applies to all services.

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

TimSession.allInstances->first->groupLeistungen('', '', 'PROJEKT')

Returns a list of ServiceSums of all services in the system, grouped by project.

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

Service sums are generally recalculated as soon as any service is changed in the system. However, the recalculation only takes place if the service sum figures are also visible. For example, it is not advisable to have a list of service sums on the screen at the same time when entering new services, as this would be recalculated for each new service. The calculation of service sums is relatively fast, but the delay is still noticeable if the recalculation is carried out unnecessarily frequently.

In particular, it is not recommended to use groupLeistungen operators 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 the Vertec are derived, that is, 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 accelerated summing, there are so-called shadow attributes, which are written when calculating the derived attributes. These shadow attributes are persistent, i.e. they are located in the database and can be queried directly.

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