Automatically created calendar birthday entries

[diderich]

I was looking some time ago for the possibility to automatically create birthday calendar entries for all contacts with a birth date specified. I was pointed out that this can be done creating a customized workflow. I tried, but never got it working. In addition, I wanted to automatically have completed birthday calendar entries deleted. Deleting record using workflows is not supported, as was pointed out to me.

Having experience in php programming, I decided to write a short cron class called Calendar_CreateBirthday_Cronthat

1. Deletes any birthday entry in any calendar in the past that is marked completed
2. Creates birthday entries for any contact that has a birth date in the contact table vtiger_comntactsubdetails and put it into the calendar of the assigned user (I am assuming that all contacts are assigned to users, rather than groups - which is the case in my organization).

I am not sure if the way my code is written is how one should write code for YetiForceCRM, so any improvements are more than welcome. Anyway I am making the code available for free under the MIT license. Feel free to use it at your own expense. If the YetiForce team wants to include the code (or an improved version of it) with a future version of the system, that's fine by me to.

Here is how I proceeded:

1. Create a Birthday picklist entry

I first added a picklist entry called Birthday to the Calendar / Activity Type picklist as show in the figure below:

2. Developed cron class Calendar_CreateBirthday_Cron

I programmed a class extending the class \AppCronHandler as show below and put it into the directory custom/modules/Calendar/crons as file named CreateBirthday.php. By default that directory does not exist, so you have to create it. As it is a custom directory, it won't interfere with future updates of the system (assuming that the used database table structures and class interfaces do not change - but that's the risk/fun of customizing YetiForceCRM).

<?php
/**
 * Create birthday entries one year forwards into the calendar, if not yet available
 *
 * @copyright innovate.d llc
 * @license MIT
 * @author Claude Ddiderich <[email protected]>
 */

/**
 * Calendar_CreateBirthdayr_Cron class.
 */
class Calendar_CreateBirthday_Cron extends \App\CronHandler
{
  /** {@inheritdoc} */
  public function process()
  {
    \App\Log::trace('Start CreateBirthday');

    // Delete all completed historical birthday entries in the calendar (i.e., older than one week)
    $lastWeekDate = date('Y-m-d', strtotime('-7 day'));
    $histBirthdayEntries = (new \App\Db\Query())->select(['activityid'])->from('vtiger_activity')
      ->innerJoin('vtiger_crmentity', 'vtiger_crmentity.crmid = vtiger_activity.activityid')
      ->where(['and', 
               ['<', 'date_start', $lastWeekDate], 
               ['=', 'activitytype', 'Birthday'], 
               ['=', 'vtiger_crmentity.deleted', 0],
               ['=', 'status', 'PLL_COMPLETED']])->createCommand()->query();
    if ($histBirthdayEntries->count()) {
      while ($histBirthdayEntry = $histBirthdayEntries->read()) {
        \App\Log::trace('CreateBirthday: Delete birthday activity record '.$histBirthdayEntry['activityid']);
        $record = Vtiger_Record_Model::getInstanceById($histBirthdayEntry['activityid'], 'Calendar');
        $record->delete();
      }
    }

    // Retrieve all contacts that have a birthday set
    $birthdayContacts = (new \App\Db\Query())->select(['contactid', 'parentid', 'birthday', 'smownerid AS assigned_to'])
      ->from('vtiger_contactdetails')
      ->innerJoin('vtiger_contactsubdetails', 'vtiger_contactsubdetails.contactsubscriptionid = vtiger_contactdetails.contactid')
      ->innerJoin('vtiger_crmentity', 'vtiger_crmentity.crmid = vtiger_contactdetails.contactid')
      ->where(['and', 
               ["<>", 'birthday', ''], 
               ['=', 'vtiger_crmentity.deleted', 0]])->createCommand()->query();
    if ($birthdayContacts->count()) {
      while ($birthdayContact = $birthdayContacts->read()) {
        \App\Log::trace('CreateBirthday: Look for birthday activity records for '.$birthdayContact['contactid']);
        
        // Calculate next birthday date
        $nextBirthday = date(date('Y').'-m-d', strtotime($birthdayContact['birthday']));
        if(strtotime($nextBirthday) < strtotime('now')) $nextBirthday = date((date('Y')+1).'-m-d', strtotime($birthdayContact['birthday']));
        $nextAge = Vtiger_Util_Helper::getAge($birthdayContact['birthday'], $nextBirthday);
        \App\Log::trace('CreateBirthday: Birthday for '.$birthdayContact['contactid'].' is '.$nextBirthday.'/'.$nextAge);

        // Check if there exists a birthday entry in the calendar
        $birthdayEntries = (new \App\Db\Query())->select(['activityid'])->from('vtiger_activity')
          ->innerJoin('vtiger_crmentity', 'vtiger_crmentity.crmid = vtiger_activity.activityid')
          ->where(['linkextend' => $birthdayContact['contactid'], 
                   'date_start' => $nextBirthday, 
                   'activitytype' => 'Birthday',
                   'vtiger_crmentity.deleted' => 0])->createCommand()->query();

        // If no entry exists, create a new calendar entry
        if($birthdayEntries->count() === 0) {
          // Create new calendar entry
          \App\Log::trace('CreateBirthday: Create new activity record for '.$birthdayContact['contactid']);
          $calendarEntry = Vtiger_Record_Model::getCleanInstance('Calendar');
          if($nextAge % 10 === 0) $calendarEntry->set('subject', $nextAge."th Birthday");
          elseif($nextAge % 10 === 1) $calendarEntry->set('subject', $nextAge."st Birthday");
          elseif($nextAge % 10 === 2) $calendarEntry->set('subject', $nextAge."nd Birthday");
          elseif($nextAge % 10 === 3) $calendarEntry->set('subject', $nextAge."rd Birthday");
          else $calendarEntry->set('subject', $nextAge."th Birthday");
          $calendarEntry->set('link', $birthdayContact['parentid']);
          $calendarEntry->set('linkextend', $birthdayContact['contactid']);
          $calendarEntry->set('activitytype', 'Birthday');
          $calendarEntry->set('date_start', $nextBirthday);
          $calendarEntry->set('due_date', $nextBirthday);
          $calendarEntry->set('time_start', '08:00:00');
          $calendarEntry->set('time_end', '08:00:00');
          $calendarEntry->set('status', 'PLL_PLANNED');
          $calendarEntry->set('priority', 'Medium');
          $calendarEntry->set('location', '');
          $calendarEntry->set('visibility', 'Public');
          $calendarEntry->set('smownerid', $birthdayContact['assigned_to']);
          $calendarEntry->set('state', 'PLL_TRANSPARENT');
          $calendarEntry->set('process', 0);
          $calendarEntry->set('subprocess', 0);
          $calendarEntry->set('followup', 0);
          $calendarEntry->set('repeat', 0);
          $calendarEntry->save();
        }
      }
    }
    \App\Log::trace('End CreateBirthday');
  }
}

3. Create an entry in the table vtiger_cron_task

I created an entry in the vtiger_cron_task table. You do not have to specific any value for laststart, last_update, lastend, and status.

4. Translation

You may want to translate the label LBL_CREATE_BIRTHDAY in custom/languages/en-US/Settings/CronTasks.json, or whatever language you are using.

That's all there is.
Have fun!

[Jianbin719]

Thanks for sharing the code.

I got the problem with Yetiforce ver. 6.4.0,

Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous in /www/wwwroot/Yetiforce/vendor/yetiforce/yii2/db/Command.php:267
I suppose that is in vtiger_field.

Call to undefined method Vtiger_Util_Helper::getAge() in /www/wwwroot/Yetiforce/custom/modules/Calendar/crons/CreateBirthday.php:52 Stack trace:
Cound not find the function of getAge()

[Jianbin719]

with "vtiger_activity.status" , first problem solved.

[Jianbin719]

I added function to the file /modules/Vtiger/helpers/Util.php, then it works

`public static function getAge($birthday, $birthdate)
{
$birthday = DateTime::createFromFormat("Y-m-d", $birthday);
$birthdate = DateTime::createFromFormat("Y-m-d", $birthdate);

  $interval = $birthday->diff($birthdate);

  $age = $interval->y;

  return $age;
}`

[Jianbin719]

But I still have problem to assign the calendar to the owner of CONTACT.