How to add reminder field to new entity

[Nishan Perera]

I want to add reminder field to new entity.

Eg : For case i need to add reminder field.

[Nishan Perera]

Hi Can anyone help me on this ???

[Maximus]

Hi,
Here is the solution of how to make a reminder popup message for Case. For the custom entities this way should work as well.
1. In the file /custom/Espo/Custom/Resources/metadata/entityDefs/Case.json add the next into the field scope. E.g.:

{
    "fields": {
[COLOR=#FF0000]     "reminders": {
            "type": "jsonArray",
            "notStorable": true,
            "view": "crm:views/meeting/fields/reminders"
        },
        "dateStart": {
            "type": "datetimeOptional",
            "before": "dateEnd"
        },
        "dateEnd": {
            "type": "datetimeOptional",
            "after": "dateStart",
            "view": "custom:views/case/fields/date-end",
            "audited": true
        },
        "dateStartDate": {
            "type": "date",
            "disabled": true
        },
        "dateEndDate": {
            "type": "date",
            "disabled": true
        },
        "dateCompleted": {
            "type": "datetime",
            "readOnly": true
        },
        "isOverdue": {
            "type": "bool",
            "readOnly": true,
            "notStorable": true,
            "view": "crm:views/task/fields/is-overdue",
            "disabled": true
        }[/COLOR]
        ....

2. Create a file /client/custom/src/views/case/fields/date-end.js with this code:

Espo.define('custom:views/case/fields/date-end', 'views/fields/datetime-optional', function (Dep) {

    return Dep.extend({

        detailTemplate: 'crm:task/fields/date-end/detail',

        listTemplate: 'crm:task/fields/date-end/detail',

        data: function () {
            var data = Dep.prototype.data.call(this);

            if (this.model.get('status') && !~['Closed', 'Rejected'].indexOf(this.model.get('status'))) {
                if (this.mode == 'list' || this.mode == 'detail') {
                    if (!this.isDate()) {
                        var value = this.model.get(this.name);
                        if (value) {
                            var d = this.getDateTime().toMoment(value);
                            var now = moment().tz(this.getDateTime().timeZone || 'UTC');
                            if (d.unix() < now.unix()) {
                                data.isOverdue = true;
                            }
                        }
                    } else {
                        var value = this.model.get(this.nameDate);
                        if (value) {
                            var d = moment.utc(value + ' 23:59', this.getDateTime().internalDateTimeFormat);
                            var now = this.getDateTime().getNowMoment();
                            if (d.unix() < now.unix()) {
                                data.isOverdue = true;
                            }
                        }
                    }
                }
            }

            return data;
        },

        setup: function () {
            Dep.prototype.setup.call(this);
            this.listenTo(this, 'change', function (e) {
                if (!this.model.get('dateEnd')) {
                    if (this.model.get('reminders')) {
                        this.model.set('reminders', []);
                    }
                }
            }, this);
        }

    });
});

3. Create a file /custom/Espo/Custom/Services/CaseObj.php with this code:

<?php

namespace Espo\Custom\Services;

use \Espo\Core\Exceptions\Error;
use \Espo\Core\Exceptions\Forbidden;

use \Espo\ORM\Entity;

class CaseObj extends \Espo\Modules\Crm\Services\CaseObj
{

    public function loadAdditionalFields(Entity $entity)
    {
        parent::loadAdditionalFields($entity);
        $this->loadRemindersField($entity);
    }

    protected function loadRemindersField(Entity $entity)
    {
        $reminders = $this->getRepository()->getEntityReminderList($entity);
        $entity->set('reminders', $reminders);
    }
}

4. Create a file /custom/Espo/Custom/Repositories/CaseObj.php with this code: https://forum.espocrm.com/forum/deve...7076#post57076

5. Create a file custom/Espo/Custom/Services/Activities.php with this code:

<?php

namespace Espo\Custom\Services;

use \Espo\Core\Exceptions\Error;
use \Espo\Core\Exceptions\NotFound;
use \Espo\Core\Exceptions\Forbidden;

use \Espo\ORM\Entity;

use \PDO;

class Activities extends \Espo\Modules\Crm\Services\Activities
{

    public function getPopupNotifications($userId)
    {
        $pdo = $this->getPDO();

        $dt = new \DateTime();

        $pastHours = $this->getConfig()->get('reminderPastHours', self::REMINDER_PAST_HOURS);

        $now = $dt->format('Y-m-d H:i:s');
        $nowShifted = $dt->sub(new \DateInterval('PT'.strval($pastHours).'H'))->format('Y-m-d H:i:s');

        $sql = "
            SELECT id, entity_type AS 'entityType', entity_id AS 'entityId'
            FROM `reminder`
            WHERE
                `type` = 'Popup' AND
                `user_id` = ".$pdo->quote($userId)." AND
                `remind_at` <= '{$now}' AND
                `start_at` > '{$nowShifted}' AND
                `deleted` = 0
        ";

        $sth = $pdo->prepare($sql);
        $sth->execute();
        $rowList = $sth->fetchAll(PDO::FETCH_ASSOC);

        $resultList = [];
        foreach ($rowList as $row) {
            $reminderId = $row['id'];
            $entityType = $row['entityType'];
            $entityId = $row['entityId'];

            $entity = $this->getEntityManager()->getEntity($entityType, $entityId);
            $data = null;

            if ($entity) {

                $dateAttribute = 'dateStart';
                if ($entityType === 'Case') {
                    $dateAttribute = 'dateEnd';
                }

                $data = [
                    'id' => $entity->id,
                    'entityType' => $entityType,
                    $dateAttribute => $entity->get($dateAttribute),
                    'name' => $entity->get('name')
                ];
            } else {
                continue;
            }
            $resultList[] = [
                'id' => $reminderId,
                'data' => $data
            ];

        }
        return $resultList;
    }

}

6. In the file /application/Espo/Modules/Crm/Jobs/SubmitPopupReminders.php find the next code:

$dateAttribute = 'dateStart';
if ($entityType === 'Task') {
    $dateAttribute = 'dateEnd';
} 

and add to it a condition for Case this way:

$dateAttribute = 'dateStart';
if ($entityType === 'Task' || $entityType === 'Case') {
    $dateAttribute = 'dateEnd';
} 

7. In the file /client/modules/crm/src/views/meeting/popup-notification.js find the next code:

var dateAttribute = 'dateStart';
if (this.notificationData.entityType === 'Task') {
    dateAttribute = 'dateEnd';
}

and add to it a condition for Case this way:

var dateAttribute = 'dateStart';
if (this.notificationData.entityType === 'Task' [COLOR=#FF0000]|| this.notificationData.entityType === 'Case'[/COLOR]) {
    dateAttribute = 'dateEnd';
}

8. Make rebuild and refresh a web page.

9. Go to Layout manager and add dateStart, dateEnd and reminder fields to a Case layout.
10. You can set labels for these fields in the Entity Manager -> Fields scope.

Note. This customization is not an official upgrade of the code, so it not guaranty 100% proper work.

[Maximus]

Hi Everyone,
There was a bug in file /custom/Espo/Custom/Repositories/CaseObj.php.
Bug description: A Contact with a corresponded email address doesn't link to a new Case created by utilizing Email-to-Case functionality in Group Email Account

To fix it you need to have this code:

<?php

namespace Espo\Custom\Repositories;

use Espo\ORM\Entity;
use Espo\Core\Utils\Util;

class CaseObj extends \Espo\Modules\Crm\Repositories\CaseObj
{
    protected $reminderDateAttribute = 'dateEnd';

    protected $reminderSkippingStatusList = ['Closed', 'Rejected'];

    protected function init()
    {
        parent::init();
        $this->addDependency('dateTime');
        $this->addDependency('config');
    }

    protected function getConfig()
    {
        return $this->getInjection('config');
    }

    protected function getDateTime()
    {
        return $this->getInjection('dateTime');
    }

    protected function beforeSave(Entity $entity, array $options = array())
    {
        if ($entity->isAttributeChanged('status')) {
            if ($entity->get('status') == 'Closed') {
                $entity->set('dateCompleted', date('Y-m-d H:i:s'));
            } else {
                $entity->set('dateCompleted', null);
            }
        }

        if ($entity->has('dateStartDate')) {
            $dateStartDate = $entity->get('dateStartDate');
            if (!empty($dateStartDate)) {
                $dateStart = $dateStartDate . ' 00:00:00';
                $dateStart = $this->convertDateTimeToDefaultTimezone($dateStart);

                $entity->set('dateStart', $dateStart);
            } else {
                $entity->set('dateStartDate', null);
            }
        }

        if ($entity->has('dateEndDate')) {
            $dateEndDate = $entity->get('dateEndDate');
            if (!empty($dateEndDate)) {
                $dateEnd = $dateEndDate . ' 00:00:00';
                $dateEnd = $this->convertDateTimeToDefaultTimezone($dateEnd);

                $entity->set('dateEnd', $dateEnd);
            } else {
                $entity->set('dateEndDate', null);
            }
        }

        parent::beforeSave($entity, $options);
    }

    protected function afterRemove(Entity $entity, array $options = array())
    {
        parent::afterRemove($entity, $options);

        $pdo = $this->getEntityManager()->getPDO();
        $sql = "
            DELETE FROM `reminder`
            WHERE
                entity_id = ".$pdo->quote($entity->id)." AND
                entity_type = ".$pdo->quote($entity->getEntityType())." AND
                deleted = 0
        ";
        $pdo->query($sql);
    }

    public function afterSave(Entity $entity, array $options = [])
    {
        $this->processReminderAfterSave($entity, $options);

        parent::afterSave($entity, $options);
    }

    protected function processReminderAfterSave(Entity $entity, array $options = [])
    {
        if (
            $entity->isNew() ||
            $entity->isAttributeChanged('assignedUserId') ||
            $entity->isAttributeChanged('usersIds') ||
            $entity->isAttributeChanged($this->reminderDateAttribute) ||
            $entity->has('reminders')
        ) {
            $pdo = $this->getEntityManager()->getPDO();

            $reminderTypeList = $this->getMetadata()->get('entityDefs.Reminder.fields.type.options');

            if (!$entity->has('reminders')) {
                $reminderList = $this->getEntityReminderList($entity);
            } else {
                $reminderList = $entity->get('reminders');
            }

            if (!$entity->isNew()) {
                $sql = "
                    DELETE FROM `reminder`
                    WHERE
                        entity_id = ".$pdo->quote($entity->id)." AND
                        entity_type = ".$pdo->quote($entity->getEntityType())." AND
                        deleted = 0
                ";
                $pdo->query($sql);
            }

            if (empty($reminderList) || !is_array($reminderList)) return;

            $entityType = $entity->getEntityType();

            $dateValue = $entity->get($this->reminderDateAttribute);

            if (!$dateValue) {
                $e = $this->get($entity->id);
                if ($e) {
                    $dateValue = $e->get($this->reminderDateAttribute);
                }
            }

            if ($entity->hasLinkMultipleField('users')) {
                $userIdList = $entity->getLinkMultipleIdList('users');
            } else {
                $userIdList = [];
                if ($entity->get('assignedUserId')) {
                    $userIdList[] = $entity->get('assignedUserId');
                }
            }

            if (!$dateValue) return;
            if (empty($userIdList)) return;

            $dateValueObj = new \DateTime($dateValue);
            if (!$dateValueObj) return;

            foreach ($reminderList as $item) {
                $remindAt = clone $dateValueObj;
                $seconds = intval($item->seconds);
                $type = $item->type;

                if (!in_array($type , $reminderTypeList)) continue;

                $remindAt->sub(new \DateInterval('PT' . $seconds . 'S'));

                foreach ($userIdList as $userId) {
                    $id = Util::generateId();

                    $sql = "
                        INSERT
                        INTO `reminder`
                        (id, entity_id, entity_type, `type`, user_id, remind_at, start_at, `seconds`)
                        VALUES (
                            ".$pdo->quote($id).",
                            ".$pdo->quote($entity->id).",
                            ".$pdo->quote($entityType).",
                            ".$pdo->quote($type).",
                            ".$pdo->quote($userId).",
                            ".$pdo->quote($remindAt->format('Y-m-d H:i:s')).",
                            ".$pdo->quote($dateValue).",
                            ".$pdo->quote($seconds)."
                        )
                    ";
                    $pdo->query($sql);
                }
            }
        }
    }

    public function getEntityReminderList(Entity $entity)
    {
        $pdo = $this->getEntityManager()->getPDO();
        $reminderList = [];

        $sql = "
            SELECT DISTINCT `seconds`, `type`
            FROM `reminder`
            WHERE
                `entity_type` = ".$pdo->quote($entity->getEntityType())." AND
                `entity_id` = ".$pdo->quote($entity->id)." AND
                `deleted` = 0
            ORDER BY `seconds` ASC
        ";

        $sth = $pdo->prepare($sql);
        $sth->execute();
        $rows = $sth->fetchAll(\PDO::FETCH_ASSOC);

        foreach ($rows as $row) {
            $o = new \StdClass();
            $o->seconds = intval($row['seconds']);
            $o->type = $row['type'];
            $reminderList[] = $o;
        }

        return $reminderList;
    }

    protected function convertDateTimeToDefaultTimezone($string)
    {
        $dateTime = \DateTime::createFromFormat($this->getDateTime()->getInternalDateTimeFormat(), $string);
        $timeZone = $this->getConfig()->get('timeZone');
        if (empty($timeZone)) {
            $timeZone = 'UTC';
        }
        $tz = $timezone = new \DateTimeZone($timeZone);

        if ($dateTime) {
            return $dateTime->setTimezone($tz)->format($this->getDateTime()->getInternalDateTimeFormat());
        }
        return null;
    }
}

[peterberlin]

Hello Maximus.

You must have put a lot of time into this.
Couldn't you build your solution (example) into the standard right away?
It would be a meaningful obtion.

peter

[telecastg]

Excellent job Maximus !, this is a superb "how to" guide for anyone who wants to implement pop up notifications.

[Maximus]

One more thing that I haven't mentioned before is:
1. The popup notification shows a 'dateEnd' as it does for Task. If you want it to show a 'dateStart', as it works for Meeting, you don't need to do steps 5,6,7.
2. I didn't reproduce a dynamic logic (e.g. the reminder field should be hidden if the 'dateEnd' is empty). So if you need it, please add it via Entity Manager.

peterberlin, the only one way to build it is via a code development.

[Nishan Perera]

Maximus Thank you for your big effort. I'll try this out. and this will help others who looking for popup notifications.

[tsantos]

Hi,

This code is only valid for v5.x of EspoCRM.

Does anyone have this code updated for v6?

Thank you.

[tsantos]

Anyone?

[Zosh]

+1 for v6.1 compatibility check!

[abidoss]

Does anyone have this code updated for v7,3?