Recalculate Formula by CRON-Job

**item** · 09-22-2023, 06:58 PM

Hi,
nobody have respond.. pfff

it's simple :

fileName must RecalculateJob.php
path to save file custom\Espo\Custom\Jobs\ RecalculateJob.php
Entity => your Entity.. change his name.
where => change the where clause.
check param for save too at https://docs.espocrm.com/development/orm/#store-entity (not really understand each, try different and feedback)
Not sure, maybe you need modify one field for "out-of-box recalcul" fire ..

What do this... find all record from Entity where .... loop and save each record.

PHP Code:

<?php
namespace Espo\Custom\Jobs;

use Espo\Core\Job\JobDataLess;
use Espo\Core\ORM\EntityManager;
use Espo\Core\ORM\Repository\Option\SaveOption;
use Espo\Core\Utils\Log;

class RecalculateJob implements JobDataLess
{
    public function __construct(
        private EntityManager $em,
        private Log $log
    ){}

    public function run() : void
    {
        $entities = $this->em->getRDBRepository('Entity')
                    ->where([
                        'status' => 'New',
                        'date<' =>  date('Y-m-01'),
                    ])
                    ->find();


        foreach($entities as $entity)
        {
            $this->em->saveEntity($entity, [SaveOption::SILENT => true]);
        }
    }
}

custom/Espo/Custom/Resources/metadata/app/scheduledJobs.json

PHP Code:

{ "RecalculateJob": { "jobClassName": "Espo\\Custom\\Jobs\\RecalculateJob" } }

After this, create the a new Job in Admin section. .you can then select 'RecalculateJob'

**shalmaxb** · 09-23-2023, 09:21 AM

item, thanks. I don`t understand too much, but will try by trial and error. Do you mean, I will have to create a job for every particular entity? And in the where clause, I guess I will have to declare my calculations, right?

for you in french: Je ne comprends pas trop, mais je vais essayer par essais et erreurs. Voulez-vous dire que je devrai créer un emploi pour chaque entité particulière? Et dans la clause Where, je suppose que je devrai déclarer mes calculs, non ?

**item** · 09-23-2023, 10:36 AM

Hi,
how many entity have calculed/formula field ?
as formula is beforeSave ... you need loop each record and save.

create a dateTime field "lastDateTimeRecalculate" in each entity who need recalculate, so you can use this field in where clause. you just need to find your logic "when and how many time recalculate, how find witch record to recalculate and why"

if you have sample : Account and Contact with formula..
you can do this in run function.

PHP Code:

public function run() : void
{
    $entitiesToRecalculate = ['Account', 'Contact'];

    foreach($entitiesToRecalculate as $entityType){
        $entities = $this->em->getRDBRepository($entityType)
                ->where([
                    'lastDateTimeRecalculate<' =>  date('Y-m-01'), //
                ])
                ->find();

        foreach($entities as $entity)
        {
            $entity->set([ 'lastDateTimeRecalculate' => now() ]);
            $this->em->saveEntity($entity, [SaveOption::SILENT => true]);
        }
    }
}

There are too with "formulaManager()->run..." .. but never play with.

**shalmaxb** · 09-23-2023, 10:36 AM

Meanwhile I got it working in general, but there are still some points to improve.

The code from item is correct and works. So far I do not use the parameters in the where-clause. What would be these parameters, when I would like to have the job working only when the record is modified?

Another problem is, that the detail view shows the recalculated values only after a browser refresh. How can I achieve, that the values get updated as soon as the job had been executed?

When all is completely working, I will write a little tutorial, how to implement that function to recalculate by cron.

**shalmaxb** · 09-23-2023, 06:17 PM

I gave up to try this for my purpose. Reasons: Working regularily on an entity, it would be necessary to run the job in short sequences, what means, that the server would literally run all the time.
CRON itself has a minimum execute time of one minute, which would not be short enough for my purpose.

I will see what else could provide a solution for my problem:

recalculate without manual action, triggered by modification of a record, even when from a related entity.

My skills in coding are quite limited, but would this be possible by hooks or dynamic handlers?

**rabii** · 09-25-2023, 10:50 AM

you can still use formula in a reversed way, meaning apply the formula sum calculation also on the item entity, when an item (invoiceId) is changed and not null then recalculate the sum of the items of the given invoiceId (record), something like below:

PHP Code:

// If the item has not been assigned to any record before
if (!entity\attributeFetched('invoiceId') &&
    entity\isAttributeChanged('invoiceId') &&
    invoiceId != null
){
    $source = record\findRelatedMany('Invoice', invoiceId,
                    'invoiceItems',
                    record\count('InvoiceItem', 'invoiceId=', invoiceId),
                    'createdAt',
                    'desc'
                );
    $i = 0;
    
    while ($i < array\length($source)){
        
        $amount = record\attribute('Invoice', invoiceId, 'amount') + record\attribute('InvoiceItem', array\at($source, $i), 'price');
        
        $i = $i + 1;
    }
    
    record\update('Invoice', invoiceId, 'amount', $amount);
}

// If the item has been assigned before and reassigned to another record
if (entity\attributeFetched('invoiceId') &&
    entity\isAttributeChanged('invoiceId') &&
    invoiceId != null
){
    (
        $source = record\findRelatedMany('Invoice', entity\attributeFetched('invoiceId'),
                        'invoiceItems',
                        record\count('InvoiceItem', 'invoiceId=', entity\attributeFetched('invoiceId')),
                        'createdAt',
                        'desc'
                    );
        $i = 0;
        
        while ($i < array\length($source)){
            
            $amount = record\attribute('Invoice', entity\attributeFetched('invoiceId'), 'amount') + record\attribute('InvoiceItem', array\at($source, $i), 'price');
            
            $i = $i + 1;
        }
        
        record\update('Invoice', entity\attributeFetched('invoiceId'), 'amount', $amount);
    );
    
    (
        $source = record\findRelatedMany('Invoice', invoiceId,
                        'invoiceItems',
                        record\count('InvoiceItem', 'invoiceId=', invoiceId),
                        'createdAt',
                        'desc'
                    );
        $i = 0;
        
        while ($i < array\length($source)){
            
            $amount = record\attribute('Invoice', invoiceId, 'amount') + record\attribute('InvoiceItem', array\at($source, $i), 'price');
            
            $i = $i + 1;
        }
        
        record\update('Invoice', invoiceId, 'amount', $amount);
    );
    
}

// If the item has been unassigned from a previous record
if (entity\attributeFetched('invoiceId') &&
    entity\isAttributeChanged('invoiceId') &&
    invoiceId == null
){
    $source = record\findRelatedMany('Invoice', entity\attributeFetched('invoiceId'),
                    'invoiceItems',
                    record\count('InvoiceItem', 'invoiceId=', entity\attributeFetched('invoiceId')),
                    'createdAt',
                    'desc'
                );
    $i = 0;
    
    while ($i < array\length($source)){
        
        $amount = record\attribute('Invoice', entity\attributeFetched('invoiceId'), 'amount') + record\attribute('InvoiceItem', array\at($source, $i), 'price');
        
        $i = $i + 1;
    }
    
    record\update('Invoice', entity\attributeFetched('invoiceId'), 'amount', $amount);
}

I would probably use the code above in the iteminvoice entity (Item) this code will try and try the invoiceId changes and based on each condition the system will recalculate the amount field on the invoice. One scenario that is not covered here is when an item is deleted then it should recalculate and for that you would use a hook (beforeDelete). I think such logic would work because the calculation should only accure when a change is made to an invoice / invoiceitem.

Hope this helps.

**shalmaxb** · 09-25-2023, 06:06 PM

Ok, I tried a lot, but the most doubt I have with the links, fields and entities. I guess there is my error anywhere. To get the point, I started with the first part only, to see, if it works, what it does not.
Here is a screenshot of my adapted formula:

**rabii** · 09-25-2023, 09:54 PM

Hey,

First of all this could should be used in teh item entity formula and not invoice. You have some mistakes and you need to get the correct link field / link multiple field for the relationship otherwise it won't work. the first condition should check if the invoiceId on the current item record was empty and has changed and it is not empty, meaning the item entity is linked to an invoice hence why you need to use the invoiceId field which is the link field of the relationship between invoice and item (i assume it is one to many relationship One invoice has many items), so you need to get the name of the invoice field on the item entity (it should be something like invoiceId / Or based on invoice name rechnungId) you need to get the correct name. you can find the field that represent the invoice on the item entity and get the correct name, do the same thing on invoice and find the name of the relationship of items on invoice if you share screenshot of the relationship names i am happy to implement it in the code. here is an example of what it should be, the first part:

PHP Code:

// If the item has not been assigned to any record before
// !entity\attributeFetched('invoiceId') this should be the link of invoice on item (i assume that the invoice has many items so there fore it should be the link field of invoice on the item entity) - i would assume it should be something like invoiceId or rechnungId
if (!entity\attributeFetched('rechnungId') &&
    entity\isAttributeChanged('rechnungId') &&
    rechnungId != null
){
    // atelierrechnungPositionen this should be the name of the relationship . link multiple of the Item on the invoice - simiar to opportunties on account
    $source = record\findRelatedMany('Rechnung', rechnungId,
                    'atelierrechnungPositionen',
                    record\count('PositionenAtelierrechnung', 'rechnungId=', rechnungId),
                    'createdAt',
                    'desc'
                );
    $i = 0;
    
    while ($i < array\length($source)){
        
        $amount = record\attribute('Rechnung', rechnungId, 'summeNetto') + record\attribute('PositionenAtelierrechnung', array\at($source, $i), 'verkaufspreisNetto');
        
        $i = $i + 1;
    }
    
    record\update('Rechnung', rechnungId, 'summeNetto', $amount);
}

I am using rechnungId which should represent the name of the invoice field on the item entity, find the correct name, also double check this (atelierrechnungPositionen) i think this should be plural but i am not good at all at german so i am not sure, anyway you just need to get the correct names of the fields and it will work.

REMEMBER THE CODE NEEDS TO BE USED IN THE ITEM ENTITY FORMULA AND NOT INVOICE, BECAUSE WE WANT TRACK CHANGES THAT HAPPEN TO ITEM RECORDS.

**shalmaxb** · 09-26-2023, 07:12 AM

rabii, thank you again, I tried my best to get it working, but still not.

Two screenshots, the relationship and my current formula.

How I understand the formula:

1. The formula looks, if there is any item in invoice or any item changed and that item in invoice is not empty
2. The formula looks, if in the invoice there are any items, when it is the first item at all, the record count starts at 0, after that with any new item adds 1 to the count, as long as the count is smaller than before (or: add one, whenever there is a new item).
3. The updated amount (in my case the net price = summeNetto in invoice) consists of the amount before adding the single price of the new item (single price = verkaufspreisNetto form item). This way the before net sum will be summed up with the new added item and updates as the new summeNetto.

Here the relationship between invoice and item

**rabii** · 09-26-2023, 07:35 AM

Ok, you are missing few things, i guess you have issue with the names of the entities, the relationship names are clear now, but if you could check the name of entities (name not label) it would allow to fix, but ass seen from my screenshot you always keep a field name invoiceId in the code and that field doesn't exist that is why the system never trigger the code, please use the code below and get read of the code you shared above.

PHP Code:

if (!entity\attributeFetched('atelierrechnungPositionenId') &&
    entity\isAttributeChanged('atelierrechnungPositionenId') &&
    atelierrechnungPositionenId != null
){
    $source = record\findRelatedMany('Rechnung', atelierrechnungPositionenId,
                    'positionenAtelierrechnung',
                    record\count('PositionenAtelierrechnung', 'atelierrechnungPositionenId=', atelierrechnungPositionenId),
                    'createdAt',
                    'desc'
                );
    $i = 0;
    
    while ($i < array\length($source)){
        
        $amount = record\attribute('Rechnung', atelierrechnungPositionenId, 'summeNetto') + record\attribute('PositionenAtelierrechnung', array\at($source, $i), 'verkaufspreisNetto');
        
        $i = $i + 1;
    }
    
    record\update('Rechnung', atelierrechnungPositionenId, 'summeNetto', $amount);
}

I am assuming from your earlier info that (PositionenAtelierrechnung) is the correct name of the item entity and (Rechnung) is the correct name of the invoice entity correct?
If yes, please copy this code and use it instead of the other code and let me know if that works for you.

If it help i can do a zoom session to help sort this out.

**shalmaxb** · 09-26-2023, 11:16 AM

No result in testing with entities, in sandbox it also has no result

**rabii** · 09-27-2023, 08:51 AM

Hey,

I have tested this and it works, however i think it will not work fine, the reason why is that the current item being linked to the invoice will be always missing and the reason why is that the formula is triggered as a before save script which means the current item is not yet linked to the invoice. the best way to solve this is to use similar code but in a afterSave Hook for the amount on the invoice, i guess that flow is always selecting multiple item on the invoice rather than linking an invoice to an item correct ? if that is true the you can simple use an afterSave hook for the amount and calculate the amount every time the items changes on the invoice. Could something like below:

PHP Code:

<?php

namespace Espo\Custom\Hooks\Rechnung;

use Espo\Core\Hook\Hook\AfterSave;
use Espo\ORM\Entity;
use Espo\Core\ORM\Repository\Option\SaveOption;
use Espo\ORM\Repository\Option\SaveOptions;


class PositionenAtelierrechnung implements AfterSave
{

    public function afterSave(Entity $entity, SaveOptions $options): void
    {  
        if (!$options->get(SaveOption::SKIP_HOOKS)) {
            return;
        }

        if (!$entity->has('positionenAtelierrechnung')) {
            return;
        }

        $itemList = $entity->get('positionenAtelierrechnung');

        if (!is_array($itemList)) {
            return;
        }


        if (count($itemList)) {
            $amount = 0.0;
            foreach ($itemList as $item) {
                $amount += $item->verkaufspreisNetto;
            }
            $amount = round($amount, 2);
            $entity->set('summeNetto', $amount);
        }
    }
}

Hope this helps

**shalmaxb** · 09-27-2023, 03:32 PM

rabii, great, now it works, but only as you described, when choosing an item (which has to be created before in items), from the linked field in invoice. But this had been necessary before this solution as well, I had to create the item and choose the invoice from the linked field, what now is obsolete.

Again, thank you a lot. I now will try to adapt this for other types of invoice and item type.

What not works is, when I delete a formerly added item, then the sum is kept in the former value.

**rabii** · 09-28-2023, 10:14 AM

Hey shalmaxb,

You need another Hook for the item entity so that when an item is deleted the invoice entity needs to be updated, so you can use the Hook below for the item entity, create a new file under Hooks > PositionenAtelierrechnung > RechnungAmount.php and use the code below, this code should work, however let me know if you face any issues:

PHP Code:

<?php

namespace Espo\Custom\Hooks\PositionenAtelierrechnung;

use Espo\ORM\Entity;
use Espo\ORM\EntityManager;
use Espo\ORM\Repository\Option\RemoveOptions;
use Espo\Core\Hook\Hook\BeforeRemove;
use Espo\Core\Hook\Hook\AfterRemove;

class RechnungAmount implements
    BeforeRemove,
    AfterRemove
{

    public function __construct(private EntityManager $entityManager)
    {}

    public function beforeRemove(Entity $entity, RemoveOptions $options): void
    {  
        if (!$entity->get('atelierrechnungPositionenId')){
            return;
        }

        $atelierrechnungPositionen = $entity->get('atelierrechnungPositionenId');

        $atelierrechnungPositionen->removeLinkMultipleId('positionenAtelierrechnung', $entity->getId());

        $this->entityManager->saveEntity($atelierrechnungPositionen);
    }

    public function afterRemove(Entity $entity, RemoveOptions $options): void
    {  
        $atelierrechnungPositionen = $entity->get('atelierrechnungPositionenId');

        if (!$atelierrechnungPositionen->has('positionenAtelierrechnung')) {
            return;
        }

        $itemList = $atelierrechnungPositionen->get('positionenAtelierrechnung');

        if (!is_array($itemList)) {
            return;
        }

        if (count($itemList)) {
            
            $amount = 0.0;
            
            foreach ($itemList as $item) {
                $amount += $item->verkaufspreisNetto;
            }
            
            $amount = round($amount, 2);
            
            $atelierrechnungPositionen->set('summeNetto', $amount);
            
            $this->entityManager->saveEntity($atelierrechnungPositionen);
        }
    }
}

Hope this help mate

Recalculate Formula by CRON-Job

Recalculate Formula by CRON-Job

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment