Core Recursion: The Recurring Events Engine

If you’ve ever wanted to setup a repeating event in CiviCRM, for example weekly church groups, then you’ll know thats its not the most straightforward task in CiviCRM at the moment, requiring large amounts of manual labour to get the desired end result. Up steps the Zing funded MIH with a large dose of user input from Lindsey @ Woodlandschurch and others who fed back on the wiki.

When Zing first approached us about the work we started with ideas and sketch of repeating events, however one of our key goals was to ensure the work became part of the core civicrm codebase and not an extension. The main reasoning behind this was to ensure that other extensions, such as CiviVolunteer and CiviBooking, could also take advantage of the repeating nature of events for their implementations. Up stepped our Core Recursion library.

For those of you that aren’t interested in the technical details now is probably a good time to read the recurring events wiki page and leave this blog for a tube strike day! However, if you're a techie at heart and want to know more about the library, read on.

The CiviCRM core team echoed our views that a Core Recursion Library is what should be developed, allowing the recursion of any table based entity within CiviCRM. To deliver this we settled on the "when" library to assist the recursive timeline and agreed to aim for the Core Recursion Library to be a core element of  CiviCRM 4.6 onwards.

Our first core patch includes:

1. When library
2. Our definition / code of recursion library
3. Repeating events using recursion library
4. Repeating events UI - Search, Dialog Alerts Update propagation, Generating and listing of repeating events

For this article we will focus on our path to core recursion, implementation of few recurring entities using it.

Our approach to the Recursion Engine

If you have ever dealt with recurring events in google calendar, you might already know recurring events. Recurring entities in CiviCRM is just the same, with entities being: Activity, CiviEvents, CiviVolunteers, CiviBooking, Membership.. or whatever you can think of in Civi. Yep, so it gets slightly challenging when we talk about entities in Civi.

We can broadly classify Civi entities in two categories:

1. Simple entities like Activities: which involves one table civicrm_activity, that requires repeating. The moment we want another table to repeat with it for e.g civicrm_activity_contact table, it becomes a complex entity and requires same procedure for recursion as CiviEvent for e.g.

2. Complex entities like CiviEvent: which involves more than one table: civicrm_event, civicrm_pcp_block, civicrm_price_set_entity, civicrm_uf_join and civicrm_tell_friend which requires repetition. Lets dive a bit deeper.

Activity: A simple entity example

Lets assume we have a meeting activity scheduled. And we would like to repeat this activity every week, on monday for 4 times.

Figure 1: UI - activity tab view

Figure 2: DB: civicrm_activity table view

Here is how we can use the recursion code to achieve recurrence:

    $recursion = new CRM_Core_BAO_RecurringEntity();
    $recursion->entity_id    = 1;
    $recursion->entity_table = 'civicrm_activity';
    $recursion->dateColumns  = array('activity_date_time');
    // lets repeat this activity every week, on monday, 4 times
    // this could also be stored in action_schedule table and used here like
    // $recursion->scheduleId = 123;
    $recursion->schedule     = array(
      'entity_value'           => 1,
      'start_action_date'      => date('Ymd'),
      'start_action_condition' => 'monday',
      'repetition_frequency_unit'     => 'week',
      'repetition_frequency_interval' => 1,
      'start_action_offset'    => 4,
    );
    // lets hit generate which does the magic
    $recursion->generate();

At the moment BAO file for recurring entity has all the recursion methods, and could be used as shown above. Shedule ($resursion->schedule) param contains all the matching keys from action_schedule table. Recursion library internally uses action schedule table for storing repetition configuration. The schedule reminder UI remains unaware of these configuration, and therefore doesn’t affect usual reminder process.

$recursion->generate() does two things overall:

1. Generates new copies of activities, with activity_date_time column populated with recursion dates.

Figure 4: DB: civicrm_activity table

2. Populates civicrm_recurring_entity table with repetition set.

Figure 5: DB: civicrm_recurring_entity table

At this point we can’t see these activities in UI because we didn’t assign them contacts. If we were to achieve that as well, we can specify that using $linkedEntities class variable as shown below:

    // specify activity contact as another table to repeat as part of recursion
    $recursion->linkedEntities = array(
      array(
        'table' => 'civicrm_activity_contact',
        'findCriteria' => array('activity_id' => $recursion->entity_id),
        'linkedColumns' => array('activity_id'),
        'isRecurringEntityRecord' => FALSE, // at this point lets not add this record to civicrm_recurring_entity table
      )
    );
    // lets hit generate which does the magic
    $recursion->generate();

The activities are now visible on UI. Note that other than dates all information is same in the repeating set. As we didn’t write any code for UI adjustment its hard to differentiate normal activities with repeating activities. Well at this point we just showcasing the core recursion work for activities as an example. We do have done amends to CiviEvent UI to make the repeating nature apparent.

Figure 7: UI: activity tab view showing repeated activities

Lets try modifying the initial activity and see if modification propagates. For the code we set the mode using $recursion object, and modify the activity as we do normally with any DAO object.

    $recursion = new CRM_Core_BAO_RecurringEntity();
    $recursion->entity_id    = 1;
    $recursion->entity_table = 'civicrm_activity';
    $recursion->mode(3); // change should affect all entities in series
    // lets try changing the initial activity
    $daoActivity = new CRM_Activity_DAO_Activity();
    $daoActivity->id = 1;
    $daoActivity->subject = "Core Recursion";
    $daoActivity->save();

Note that the subject updated for all.

Figure 9: UI: activity tab view showing updates

So how does this change affect works. A new method CRM_Core_BAO_RecurringEntity::triggerUpdate() that gets called post $dao->update(). It basically copies new changes to other entities provided civicrm_recurring_entity table has record of entity being updated.

The mode column is referred to take the decision on how change affects the repeating set - Only that particular entity, Future entities or All entities. Note that the mode flag is persistent. Its due to ease with which recursion can be applied to any entity without modiying the underlying code to supply the flag. If we can just attach the jquery dialog that confirms - “Only that particular entity, Future entities or All entities”, to activities, that we built for events, an ajax call updates mode in the Database and RecurringEntity::triggerUpdate() would know the direction for update propagation.

CiviEvent: A complex entity

Lets assume we want to make “Rain-forest Cup Youth Soccer Tournament” event (ID: 3) a recurring event using similar schedule: every week, wednesday for 4 times. Note for this post we just focusing on nature of recursion, and therefore not focusing on complex schedule patterns which are also possible. Here we would want to specify all the linked tables as part of recursion.

	
        $recursion = new CRM_Core_BAO_RecurringEntity();
        $recursion->entity_id    = 3;
        $recursion->entity_table = 'civicrm_event';
        $recursion->dateColumns  = array('start_date', 'end_date');
        $recursion->scheduleId   = 12; // every week on wednesday, 4 times
     
        // all the linked tables
        $recursion->linkedEntities = array(
          array(
            'table'         => 'civicrm_price_set_entity',
            'findCriteria'  => array(
              'entity_id'    => $recursion->entity_id,
              'entity_table' => 'civicrm_event'
            ),
            'linkedColumns' => array('entity_id'),
            'isRecurringEntityRecord' => FALSE,
          ),
          array(
            'table'         => 'civicrm_uf_join',
            'findCriteria'  => array(
              'entity_id'    => $recursion->entity_id,
              'entity_table' => 'civicrm_event'
            ),
            'linkedColumns' => array('entity_id'),
            'isRecurringEntityRecord' => FALSE,
          ),
          array(
            'table'         => 'civicrm_tell_friend',
            'findCriteria'  => array(
              'entity_id'    => $recursion->entity_id,
              'entity_table' => 'civicrm_event'
            ),
            'linkedColumns' => array('entity_id'),
            'isRecurringEntityRecord' => TRUE,
          ),
          array(
            'table'         => 'civicrm_pcp_block',
            'findCriteria'  => array(
              'entity_id'    => $recursion->entity_id,
              'entity_table' => 'civicrm_event'
            ),
            'linkedColumns' => array('entity_id'),
            'isRecurringEntityRecord' => TRUE,
          ),
        );
     
        $recursion->generate();

Note tables civicrm_price_set_entity and civicrm_uf_join have isRecurringEntityRecord flag set to FALSE. This is to show an example that some tables can just be repeated but not necessarily be made part of recurring_entity table. Secondly they both are multirecord table, and there is no point in tracking their primary key. Multi record table still need some work, and is explained in "areas that need some work" section.  

Figure 10: DB: civicrm_recurring_entity table


To recurring entity table all entities are different. For instance it doesn't know if civicrm_pcp_block is any related to civicrm_event. For civicrm_pcp_block it just knows other civicrm_pcp_block that are related. And if any change happens it gets updated in other related civicrm_pcp_block based on mode flag. Custom Tables Custom tables when specified as $linkedEntities would also propagate changes.

Areas that require further work

A Multi record table if happens to get a new entry, recurring entity table wouldn’t know. For e.g civicrm_activity_contact table. Say after repetition set was created, new contact gets added to an activity that belongs to the repeating set. Due to a new id recurring entity wouldn’t know how to propagate this new contact. Solution here is to trigger a refresh or rebuild for civicrm_activity_contact table and its repeating set.

Challenges using the DAO approach

In order to expand the use of the Recursion Engine, an understanding of the following is required;

1. Knowing the schema structure & mapping to the recurring entity i.e. which tables need to be part of the recursion to recur an activity.

2. Some maintenance is required with recurring_entity table, as its not a true FK relationship.

3. Is based on DAO methods. If any updates happen at sql / db level, the change won’t propagate.

4. Infinite recurrence (not in scope) only possible through combination of finite sets and cron scheduler.

5. Depending on amount of repetition and number of linked tables, first time creation may take some time. Although since updates are specific to a table, propagation seems to perform decent.

Advantages of the DAO approach

Can easily apply to all entities in general, without much intersection with core code.

As the code is at one place its not much difficult to change the algorithm / nature of recursion for e.g if we plan to achieve recursion through Database (say using procedures and triggers) in the near future, or using some other library.

Credits & thanks to

Zing, Lindsey, Core team especially Tim, DaveJ for providing support & help in this direction.

Veda NFP Consulting and its team, especially Priyanka Karan, for implementation and interest in contributing to Core CiviCRM.

References

PR Link - https://github.com/civicrm/civicrm-core/pull/4068

Codebase - https://github.com/priyankakaran26/civicrm-core/tree/event-recurring-46

Test case - https://github.com/priyankakaran26/civicrm-core/blob/event-recurring-46/tests/phpunit/CRM/Core/BAO/RecurringEntityTest.php

Wiki - http://wiki.civicrm.org/confluence/display/CRM/Recurring+Events%3A+Implementation+Plan