Announcement

Collapse
No announcement yet.

Recalculate Formula by CRON-Job

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Recalculate Formula by CRON-Job

    This question had been discussed a few years ago. As it obviously never lead to a working example and some code-base modifications had been passed I would like to ask this question again.

    Formerly it had been stated, that it coud be possible to recalculate formulas by CRON-Job by creating a custom Job, which would be executed by CRON as described here: https://docs.espocrm.com/development/scheduled-job/
    In the file for a custom job, there is the section where it says: "//Write your logic here"

    That is where I need help. I guess there is already a logic for recalculate, as it is a built in function in espoCRM. Could anybody please help me with this part? What would I have to write here?

  • #2
    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'
    Last edited by item; 09-22-2023, 07:03 PM.

    Comment


    • #3
      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 ?

      Comment


      • #4
        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.
        Last edited by item; 09-23-2023, 10:38 AM.

        Comment


        • shalmaxb
          shalmaxb commented
          Editing a comment
          I guess I could use the modifiedAt field, because a recalculation would only happen, if something has changed. Well the where clause could look like this?

          'modifiedAt' => dateTime('my dateTime format'),

          or

          'modifiedAt' => 'desc' (to fetch the last modified dateTime?)
          Last edited by shalmaxb; 09-23-2023, 10:45 AM.

        • shalmaxb
          shalmaxb commented
          Editing a comment
          I have quite a few entities with formulas. But it is no problem to write a separate recalculateJob for each of them.

        • item
          item commented
          Editing a comment
          it's more complex than that

          Sample :
          contact->countRelated('Call');

          the job work for Contact...
          Add a call to contact => contact->modifiedAt = still same as previous. no change.
          Need "Hook" too, afterRelate, afterUnrelate.

          not simple to catch all "possibility" . .. but you have asked how recalculate by Jobs, the post is OK

      • #5
        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.

        Comment


        • #6
          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?

          Comment


          • esforim
            esforim commented
            Editing a comment
            I think it too hard with just CRON, this is a job for Workflow only. I'm still exploring alternative solution somewhere on my long list of "To-Do" and I keep my eye on these kind of thread. Thanks you for making it and item for a solution.

          • rabii
            rabii commented
            Editing a comment
            Hey if you already have formulas they should be triggered once a condition is meet, why would you need a Job for this. i think you have just to add a condition in the formula to catch change and apply calculation or you can use Hooks. Not sure what is your use case but i think you could manage all of this using formula Or Hooks.

          • shalmaxb
            shalmaxb commented
            Editing a comment
            rabii, so far I did not come up with a solution by formula. As I am not skilled in coding, I always try to solve any problem by formula or sometimes by copying code provided here.
            Very plain spoken, I need a solution, that imitates the behaviour of the action menu item "recalculate Formula", only this by not having to do anything manually.
            In my case I have two entities like invoice and items. The invoice calculates the sum of prices by formula. When I add a new item it does not add the price of that item without trigering the formula for sum calculation again manually.

        • #7
          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.
          Rabii
          Web Dev

          Comment


          • shalmaxb
            shalmaxb commented
            Editing a comment
            Hi, rabii, that looks great, I gonna test it still today and give feedback.You are really a great and patient help (not only for me) and you always put so much work in your proposals. I am very grateful for that.

          • rabii
            rabii commented
            Editing a comment
            you are welcome, just make sure to change names of entities / link / fields to the proper ones, let me know if you face any issues. will sort this out together.

        • #8
          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:

          Click image for larger version  Name:	update_sum_invoice.jpg Views:	0 Size:	46.0 KB ID:	97886

          Comment


          • #9
            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.
            Rabii
            Web Dev

            Comment


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

              Two screenshots, the relationship and my current formula.

              Click image for larger version  Name:	formula_invoice_items_2.jpg Views:	0 Size:	42.2 KB ID:	97909

              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

              Click image for larger version  Name:	relation_invoice_items.jpg Views:	0 Size:	28.9 KB ID:	97910

              Comment


              • rabii
                rabii commented
                Editing a comment
                you always forget in the first condition invoiceId != null that is why it doesn't work because there is no field in your database called invoiceId you should change that to be atelierrechnungPositionenId != null instead.

              • rabii
                rabii commented
                Editing a comment
                what is the name of the entity invoice ? and what the name of the entity item ? from the sceenshot i can see that the item entity is called (Position Atelierverkauf)

            • #11
              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.
              Rabii
              Web Dev

              Comment


              • shalmaxb
                shalmaxb commented
                Editing a comment
                ok, I already had changed the invoiceId !=null

                Name of invoice entity is "Rechnung"
                Name of item entity is "PositionenAtelierrechnung"

                Will test that a bit later.

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

              Comment


              • rabii
                rabii commented
                Editing a comment
                this code will not work in sandbox of course because there is a condition for UI to change then the code will be triggered. use the code in Item entity formula script and try to change the invoiceId of an item record (then see the invoice record amount changes).

              • shalmaxb
                shalmaxb commented
                Editing a comment
                in sandbox I used the code without the starting conditions. As for my entities I use this code in the item entity. I think I understood the code completely.

            • #13
              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 $entitySaveOptions $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($amount2);
                          
              $entity->set('summeNetto'$amount);
                      }
                  }
              }

              Hope this helps
              Rabii
              Web Dev

              Comment


              • shalmaxb
                shalmaxb commented
                Editing a comment
                It is impressive, how you don`t give up (what I would completely understand). This thread starts to evolving like a workshop, I really appreciate that. It is really difficult to learn the correct procedure, when you do not have the pre-requisited knowledge, which is hard to learn in a short time. With espoCRM and the help here in the forum I already advanced a lot in the last four years, but still I try to learn more. You especially are helping a lot.
                I had been thinking about hook already, but never used it before. I will try with your example, perhaps I get forward. If not, it is not existential to my app. A solution would though bring advantages in user experience, as the user would not have to re-calculate all the time.

              • rabii
                rabii commented
                Editing a comment
                I understand we are all in on this together, i also get help for other staff so always happy to help.

                Just create a folder under custom/Espo/Custom/Hooks/Rechnung (create the folders if they don't exist) and then create a php file called PositionenAtelierrechnung.php

                Don't forget to disable any previous formula code you have tested.

                Give it ago and let me know, i have tested this code and it works so it should work fine.

            • #14
              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.
              Last edited by shalmaxb; 09-27-2023, 03:36 PM.

              Comment


              • #15
                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 $entityRemoveOptions $options): void
                    
                {  
                        if (!
                $entity->get('atelierrechnungPositionenId')){
                            return;
                        }

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

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

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

                    public function 
                afterRemove(Entity $entityRemoveOptions $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($amount2);
                            
                            
                $atelierrechnungPositionen->set('summeNetto'$amount);
                            
                            
                $this->entityManager->saveEntity($atelierrechnungPositionen);
                        }
                    }
                }


                Hope this help mate
                Rabii
                Web Dev

                Comment

                Working...
                X