Tutorial - How to create a bootstrap dashboard progress cards widget inside a dashlet

**item** · 09-23-2020, 08:06 PM

telecastg

Waouw nice work man.. congratualtion .. thanks for the community

Best Regards

**tothewine** · 09-24-2020, 01:13 PM

great stuff! for such things i was using the iframe widget pointing at a custom entrypoint but this is way cleaner!

**blueprint** · 09-25-2020, 09:10 AM

This is great telecastg , excellent work!!!

**telecastg** · 09-27-2020, 06:19 AM

Version 1.0.2 of the control-board-for-espocrm extension released. https://github.com/telecastg/control-board-for-espocrm

It now allows to use calculated fields to group data, not just enum type fields like "status" to build progress cards widgets.

Use Case:
Develop a progress cards type widget, inside a dashlet, that will describe the number of Service Tickets, with status filter "Open" grouped by how long ago they were created.

Each card will be color coded according to the "ageing" group value and will have an icon which when clicked will trigger the display of a list of Service Tickets included in the card count.

The User shall have the ability to view tickets that fall under the primary filters "Open" or "All".

The steps to follow are the same as the previous example and the scripts are:

custom/Espo/Custom/Resources/metadata/dashlets/ServiceTicketsByAgeing.json (dashlet definition)

Code:

{
    "view":"custom:views/service-ticket/dashlets/service-tickets-by-ageing",
    "instrumentView": "control-board:views/dashlets/instruments/progress-cards",
    "entityType": "ServiceTicket",
    "criteriaScope": "ServiceTicket",
    "criteriaAttribute": "ageing",
    "criteriaConditionGroups": [
        {
            "conditionGroupType": "and",
            "conditionGroupLabel": "One week old or less",
            "conditionGroupClass": "success",
            "conditionValues": [
                {
                    "operator": "<=",
                    "value": "1",
                    "valueType": "int"
                }
            ]
        },
        {
            "conditionGroupType": "and",
            "conditionGroupLabel": "Two weeks old",
            "conditionGroupClass": "caution",
            "conditionValues": [
                {
                    "operator": "=",
                    "value": "2",
                    "valueType": "int"
                }
            ]
        },
        {
            "conditionGroupType": "and",
            "conditionGroupLabel": "Three weeks old",
            "conditionGroupClass": "warning",
            "conditionValues": [
                {
                    "operator": "=",
                    "value": "3",
                    "valueType": "int"
               }
            ]
        },
        {
            "conditionGroupType": "and",
            "conditionGroupLabel": "Four weeks old or more",
            "conditionGroupClass": "danger",
            "conditionValues": [
                {
                    "operator": ">=",
                    "value": "4",
                    "valueType": "int"
                }
            ]
        }
    ],
    "criteriaAttributeDisplayMap": [
        {"attribute": "1", "attributeLabel": "One week old or less", "class": "success", "icon": "fas fa-ticket-alt"},
        {"attribute": "2", "attributeLabel": "Two weeks old", "class": "caution", "icon": "fas fa-ticket-alt"},
        {"attribute": "3", "attributeLabel": "Three weeks old", "class": "warning", "icon": "fas fa-ticket-alt"},
        {"attribute": "4", "attributeLabel": "Four weeks old or more", "class": "danger", "icon": "fas fa-ticket-alt"}
    ],
    "options": {
        "fields": {
            "title": {
                "type": "varchar",
                "required": true
            },
            "primaryFilter": {
                "type": "enum",
                "options": ["open","all"]
            }
        },
        "layout": [
            {
                "rows": [
                    [
                        {"name": "title"}
                    ],
                    [
                        {"name": "primaryFilter"}
                    ]
                ]
            }
        ],
        "defaults": {
            "title": "Service Tickets By Ageing",
            "primaryFilter": "open"
        }
    }
}

.
Note how in the above definition we have a new property "criteriaConditionGroups", here is where we define the logic conditions for segmenting the data.

In this example we want to have four groups: Tickets received a week or less ago, tickets received two weeks ago, tickets received three weeks ago and tickets received 4 weeks ago or more

The ServiceTicket entity contains a calculated field "ageing" which is updated each time a list of tickets is requested and each time that a progress cards dashlet is rendered.

Here is the ServiceTicket entityDefs section where the "ageing" field is defined:

custom/Espo/Custom/Resources/metadata/entityDefs/ServiceTicket.json

Code:

{
    "fields": {
        "ageing" : {
            "type": "int",
            "select": "TIMESTAMPDIFF(WEEK, service_ticket.created_at, now())",
            "readOnly": true,
            "isCustom": true
        }    
    }
}

Also notice that the dashlet definition allows us to specify a "primaryFilter".

For those who might not know, the primary filter is used to filter records in a list view by clicking on the dropdown selector at the upper left corner of the list display.

This filter is defined in the entity's SelectManager class.

Here is the code for the ServiceTicket entity:
custom/Espo/Custom/SelectManagers/ServiceTicket.php

PHP Code:

namespace Espo\Custom\SelectManagers;

class ServiceTicket extends \Espo\Core\SelectManagers\Base
{
    protected function boolFilterOpen(&$result)
    {
        $this->filterOpen($result);
    }
    protected function boolFilterCompleted(&$result)
    {
        $this->filterCompleted($result);
    }
    protected function filterOpen(&$result)
    {
        $result['whereClause'][] = array(
            'status!=' => ['Completed', 'Canceled', 'Deferred', 'Rejected']
        );
    }
    protected function filterCompleted(&$result)
    {
        $result['whereClause'][] = array(
            'status' => ['Completed']
        );
    }
}

The rest of the scripts are very similar to the first example:

client/custom/src/views/service-ticket/dashlets/service-tickets-by-ageing.js (dashlet view class)

Code:

Espo.define('custom:views/service-ticket/dashlets/service-tickets-by-ageing', 'control-board:views/dashlets/abstract/instrument-container', function (Dep) {

    return Dep.extend({

        name: "ServiceTicketsByAgeing" // dashlet file name

    });
});

custom/Espo/Custom/Resources/i18n/en_US/Global.json (dashlet name label)

Code:

{
    "dashlets": { "ServiceTicketsByAgeing" : "Service Ticket By Ageing" }
}

custom/Espo/Custom/Resources/i18n/en_US/DashleOptions.json (dashlet options labels - NEW for this example)

Code:

{
    "options": {
        "primaryFilter": {
            "open": "Open",
            "all": "All"
        }
    }
}

Functionality:
a) Clicking on the "ticket" icon for any category will display a modal list of tickets so you can zoom in into any record detail view

b) Clicking on the "..." icon at the top right corner of the dashlet will display a drop-down menu. Select "Options" in this menu to modify the dashlet title and the status filter for the records included.

Customization:
This tutorial describes a specific implementation for a "ServiceTicket" custom entity which has an "ageing" calculated field, so it should work for any entity which has a calculated field that can be used as criteria to group records.

For those interested in customizing this use case, the general program flow is as follows:

client/custom/src/views/service-ticket/dashlets/service-tickets-by-status.js
client/modules/control-board/src/views/dashlets/abstract/instrument-container.js	afterRender()
client/modules/control-board/src/views/dashlets/instruments/progress-cards.js	fetch()
application/Espo/Modules/ControlBoard/Controllers/ControlBoard.php	actionTotalsByCriteria()
application/Espo/Modules/ControlBoard/Services/ControlBoard.php	totalsByCriteria()

**OliverMD78** · 10-07-2020, 05:23 AM

Hi,
I tried to use this for standard enitiy "Case".. change the code from "ServiceTicket" to case but I have Error 500
this is the depending log entry

Espo.ERROR: (42000) SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case WHERE case.deleted <> "1" GROUP BY case.status' at line 1; GET /api/v1/ControlBoard/action/totalsByCriteria?scope=Case&criteriaScope=Case&cri teriaAttribute=status&dateFilter=ever&instrumentNa me=ServiceTicketsByStatus; line: 151, file: /homepages/18/d757749152/htdocs/ticketsystem/application/Espo/Modules/ControlBoard/Services/ControlBoard.php [] []

Thanks...

**telecastg** · 10-07-2020, 06:52 AM

The code is working fine for us with the ServiceTicket entity, not sure why the sql error is being thrown out for the "Case" entity.

Please post the contents of your dashlet definition (the custom/Espo/Custom/Resources/metadata/dashlets/YOUR-DASHLET.json ) script and a screenshot of the dashlet options panel (see below) to try to replicate.

**telecastg** · 10-07-2020, 06:05 PM

Found the cause for the error: The word "case" is a reserved word in SQL so the query statement was causing an error when trying to refer to the "case" db table.

To go around this, I modified the sql statement builder in the service class to put ticks ` around the table names and now you can use the extension with the "Case" entity.

The newest version is now 1.0.4 and is ready for downloading.

When you install the new version, don't forget to uninstall and remove any previous versions in your installation then clear cache and rebuild.

For future reference, I left some $GLOBALS['log']->debug statements uncommented in the php code and there are several others that are still commented but can be uncommented so if an error occurs to facilitate tracing and debugging.

For those who might not be familiar with debugging PHP this thread is very helpful. https://forum.espocrm.com/forum/deve...gging-php-code

**OliverMD78** · 10-08-2020, 05:57 AM

OK looks better, but see the screen ...something is missing... maybe you have an idea.... Thanks !!!

**telecastg** · 10-08-2020, 04:00 PM

Yes, you need to customize your own icon and css classes for each category here:

Code:

"criteriaAttributeDisplayMap": [
    {"attribute": "Received", "class": "danger", "icon": "fas fa-ticket-alt"},
    {"attribute": "Allocated", "class": "warning", "icon": "fas fa-ticket-alt"},
    {"attribute": "Completed", "class": "success", "icon": "fas fa-ticket-alt"},
    {"attribute": "Canceled", "class": "info", "icon": "fas fa-ticket-alt"}
],

Please remember that customizing this implementation is NOT a simple cut and paste job. You will need to read and understand the code to accomplish your goals.

Check this script: client/modules/control-board/src/views/dashlets/instruments/progress-cards.js function prepareData() to see how the card display is generated from the json metadata specs.

**esforim** · 11-18-2022, 04:27 AM

Hi telecastg can you share the deprecated v6 version? I have a guy want wanting to give it a try to see if he can update it to version 7.

Thank you.

**wtconseil** · 10-05-2023, 02:49 PM

can we get the content of

application/Espo/Modules/ControlBoard/Controllers/ControlBoard.php	actionTotalsByCriteria()
application/Espo/Modules/ControlBoard/Services/ControlBoard.php

Tutorial - How to create a bootstrap dashboard progress cards widget inside a dashlet

Tutorial - How to create a bootstrap dashboard progress cards widget inside a dashlet

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment