How to display overdue activites (calls, meetings, tasks) on dashboard?

[crmscot]

The My Activities dashlet is great for seeing a list of today's and future activities but I would find it useful to see another dashlet with list of overdue activities.

I can get part way there with the Record List dashlet which can show planned activities (calls, meetings, etc) but it also shows future activities as well.

Does anyone know of a way to add an extra filter to the Record List dashlet to achieve this or is there a better way?

I've tried creating a custom dashlet based on the Activities dashlet and following the Developer Documentation but cannot get it to work. I think that's becasue I don't understand where to add the custom filter and how to reference it.

[Kharg]

You can do that by adding a "Record List" Dashlet and select "Overdue" as the primary filter.



Or if you've got the Advanced Pack you can make a custom report and use it in a dashlet.

[crmscot]

Hi Kharg. Thanks for your prompt reply. For the Record List dashlet my primary filters only include "All, Planned, Held, Today's" Where or how do I add Overdue?

[Kharg]

Checking better the Overdue filter is just for task, but I think it can be implemented quite easily for calls and meetings.
I’ll check that.

[crmscot]

Thanks Kharg

[Kharg]

Hello,

Just tested and it's working fine.
You have to create the following files.
I just made it for the call entity, if you aren't able to make it for the meeting entity just send me a message.
I also submitted a new issue on Github in the hope that it will be added by default.
Espo\Custom\Classes\Select\Call\PrimaryFilters\Ove rdue.php

<?php
/************************************************************************
 * This file is part of EspoCRM.
 *
 * EspoCRM - Open Source CRM application.
 * Copyright (C) 2014-2022 Yurii Kuznietsov, Taras Machyshyn, Oleksii Avramenko
 * Website: https://www.espocrm.com
 *
 * EspoCRM is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * EspoCRM is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with EspoCRM. If not, see http://www.gnu.org/licenses/.
 *
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU General Public License version 3.
 *
 * In accordance with Section 7(b) of the GNU General Public License version 3,
 * these Appropriate Legal Notices must retain the display of the "EspoCRM" word.
 ************************************************************************/

namespace Espo\Custom\Classes\Select\Call\PrimaryFilters;

use Espo\Entities\User;

use Espo\ORM\Query\SelectBuilder;
use Espo\ORM\Query\Part\Condition as Cond;

use Espo\Core\Select\Primary\Filter;
use Espo\Core\Select\Helpers\UserTimeZoneProvider;
use Espo\Core\Select\Where\Item;
use Espo\Core\Select\Where\ConverterFactory;
use Espo\Core\Utils\Metadata;

use Espo\Modules\Crm\Entities\Call;

class Overdue implements Filter
{
    private $user;

    private $userTimeZoneProvider;

    private $metadata;

    private $converterFactory;

    public function __construct(
        User $user,
        UserTimeZoneProvider $userTimeZoneProvider,
        Metadata $metadata,
        ConverterFactory $converterFactory
    ) {
        $this->user = $user;
        $this->userTimeZoneProvider = $userTimeZoneProvider;
        $this->metadata = $metadata;
        $this->converterFactory = $converterFactory;
    }

    public function apply(SelectBuilder $queryBuilder): void
    {
        $notActualStatusList =
            array_filter(
                $this->metadata->get(['entityDefs', 'Call', 'fields', 'status', 'notActualOptions']) ?? [],
                function (string $item) {
                    return $item !== 'Held';
                }
            );

        $pastItem = Item::fromRaw([
            'type' => 'past',
            'attribute' => 'dateEnd',
            'timeZone' => $this->userTimeZoneProvider->get(),
            'dateTime' => true,
        ]);

        $pastWhereItem = $this->converterFactory
            ->create(Call::ENTITY_TYPE, $this->user)
            ->convert($queryBuilder, $pastItem);

        $queryBuilder
            ->where($pastWhereItem)
            ->where(
                Cond::notIn(Cond::column('status'), $notActualStatusList)
            );
    }
}

custom\Espo\Custom\Resources\metadata\selectDefs\C all.json

{
       "primaryFilterClassNameMap": {
              "Overdue": "Espo\\Custom\\Classes\\Select\\Call\\PrimaryFilters\\Overdue"
        }
}

and
custom\Espo\Custom\Resources\metadata\clientDefs\C all.json

{
  "filterList": [
        {
            "name":"planned"
        },
        {
            "name":"held",
            "style": "success"
        },
        {
            "name":"todays"
        },
        {
            "name": "Overdue",
            "style": "danger"
        }
    ]
}

[crmscot]

Hi Kharg. Thanks for this. I was able to implement your code and, indeed, the Overdue filter worked for calls and meetings. Only issue is that 'Held' activities are still listed as overdue. I think the filter needs to incude 'status = planned' too.

[rabii]

you need to amend the code on calls and meeting there is no notActualOptions parameter for status field on both call and meeting entities, this is only implemented for task and Lead entities' status field. try this instead:

<?php

namespace Espo\Custom\Classes\Select\Call\PrimaryFilters;

use Espo\Entities\User;

use Espo\ORM\Query\SelectBuilder;
use Espo\ORM\Query\Part\Condition as Cond;

use Espo\Core\Select\Primary\Filter;
use Espo\Core\Select\Helpers\UserTimeZoneProvider;
use Espo\Core\Select\Where\Item;
use Espo\Core\Select\Where\ConverterFactory;
use Espo\Core\Utils\Metadata;

use Espo\Modules\Crm\Entities\Call;

class Overdue implements Filter
{
private $user;

private $userTimeZoneProvider;

private $metadata;

private $converterFactory;

public function __construct(
User $user,
UserTimeZoneProvider $userTimeZoneProvider,
Metadata $metadata,
ConverterFactory $converterFactory
) {
$this->user = $user;
$this->userTimeZoneProvider = $userTimeZoneProvider;
$this->metadata = $metadata;
$this->converterFactory = $converterFactory;
}

public function apply(SelectBuilder $queryBuilder): void
{
$notActualStatusList =
array_filter(
$this->metadata->get(['entityDefs', 'Call', 'fields', 'status']) ?? [],
function (string $item) {
return $item == 'Planned';
}
);

$pastItem = Item::fromRaw([
'type' => 'past',
'attribute' => 'dateEnd',
'timeZone' => $this->userTimeZoneProvider->get(),
'dateTime' => true,
]);

$pastWhereItem = $this->converterFactory
->create(Call::ENTITY_TYPE, $this->user)
->convert($queryBuilder, $pastItem);

$queryBuilder
->where($pastWhereItem)
->where(
Cond::In(Cond::column('status'), $notActualStatusList)
);
}
}

[crmscot]

Thanks for your input Rabii. I tried replacing Overdue.php with your code and, unfortunately, get an Error 500 message when selecting the Overdue filter. When I include the notActualOptions parameter again your code runs without the Error 500 but does not return any Calls in the dashlet. I've checked the error logs and set display errors = on to try to get more information but nothing shows up.

[rabii]

try this new code instead

<?php

namespace Espo\Custom\Classes\Select\Call\PrimaryFilters;

use Espo\Entities\User;

use Espo\ORM\Query\SelectBuilder;
use Espo\ORM\Query\Part\Condition as Cond;

use Espo\Core\Select\Primary\Filter;
use Espo\Core\Select\Helpers\UserTimeZoneProvider;
use Espo\Core\Select\Where\Item;
use Espo\Core\Select\Where\ConverterFactory;
use Espo\Core\Utils\Metadata;

use Espo\Modules\Crm\Entities\Call;

class Overdue implements Filter
{
private $user;

private $userTimeZoneProvider;

private $metadata;

private $converterFactory;

public function __construct(
User $user,
UserTimeZoneProvider $userTimeZoneProvider,
Metadata $metadata,
ConverterFactory $converterFactory
) {
$this->user = $user;
$this->userTimeZoneProvider = $userTimeZoneProvider;
$this->metadata = $metadata;
$this->converterFactory = $converterFactory;
}

public function apply(SelectBuilder $queryBuilder): void
{

$pastItem = Item::fromRaw([
'type' => 'past',
'attribute' => 'dateEnd',
'timeZone' => $this->userTimeZoneProvider->get(),
'dateTime' => true,
]);

$pastWhereItem = $this->converterFactory
->create(Call::ENTITY_TYPE, $this->user)
->convert($queryBuilder, $pastItem);

$queryBuilder
->where($pastWhereItem)
->where(
Cond::equal(Cond::column('status'), 'Planned')
);
}
}

[crmscot]

Hi rabii. Very many thanks for your input. I've implemented your revised code and it works perfectly for calls. I've also managed to adapt it for meetings and that works too. Job done! Thanks for your help.