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

[telecastg]

Update: Please note that the free version of this extension has been deprecated and no longer available.

This tutorial describes how to create a progress card widget as show below, using the new free extension control-board-for-espocrm https://github.com/telecastg/control-board-for-espocrm

Please note that this implementation requires the installation of the control-board-for-espocrm extension AND some custom coding.

Use Case:
Develop a progress cards type widget, inside a dashlet, that will illustrate the number of Service Tickets processed during a given period, grouped by the ticket "status" field.

Each card will be color coded according to the "status" 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 determine the time period during which the Service Tickets are being analyzed.

Step 1:
Download and install the control board extension. https://github.com/telecastg/control-board-for-espocrm

Step 2:
Create the custom dashlet definition
custom/Espo/Custom/Resources/metadata/dashlets/ServiceTicketsByStatus.json

{
    "view":"custom:views/service-ticket/dashlets/service-tickets-by-status",
    "instrumentView": "control-board:views/dashlets/instruments/progress-cards",
    "entityType": "ServiceTicket",
    "criteriaScope": "ServiceTicket",
    "criteriaAttribute": "status",
    "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"}
    ],
    "options": {
        "fields": {
            "title": {
                "type": "varchar",
                "required": true
            },
            "dateFrom": {
                "type": "date",
                "required": false
            },
            "dateTo": {
                "type": "date",
                "required": false
            },
            "dateFilter": {
                "type": "enum",
                "options": ["currentYear", "currentQuarter", "currentMonth", "ever", "between"],
                "default": "currentYear",
                "translation": "Global.options.dateSearchRanges"
            }
        },
        "layout": [
            {
                "rows": [
                    [
                        {"name": "title"}
                    ],
                    [
                        {"name": "dateFilter"},
                        false
                    ],
                    [
                        {"name": "dateFrom"},
                        {"name": "dateTo"}
                    ]
                ]
            }
        ],
        "defaults": {
            "title": "Service Tickets By Status",
            "dateFilter": "currentYear"
        }
    }
}

Step 3:
Define the custom view class for the dashlet.
client/custom/src/views/service-ticket/dashlets/service-tickets-by-status.js

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

    return Dep.extend({

        name: "ServiceTicketsByStatus" // dashlet file name

    });
});

Step 4:
Add dashlet name to your language file(s) to display as label in the dashlets list.
custom/Espo/Custom/Resources/i18n/en_US/Global.json

{
    "dashlets": {
        "ServiceTicketsByStatus" : "Service Ticket By Status"
    }
}

Step 5:
Clear cache and rebuild

Step 6:
Add the new dashlet to your dashboard through the "Preferences" GUI option or by clicking on the "+" icon at the upper right corner of the dashboard.


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 date period covered.

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

To further customize and see how the actual widget is being generated check the extension's source code scripts and adapt to your own needs. See the program flow table in the example # 2 below https://forum.espocrm.com/forum/deve...2985#post62985.

[item]

telecastg

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

Best Regards

[telecastg]

You're very welcome :-)

[item]

Hello @telecastg,
do you think, we can "name" you wonderfull extension : kanban dashlet ?
when i see the print-screen.. i think that

Best regards

[telecastg]

Hello item,

actually the extension also helps to display a kanban list based on fields other than "status" even group records by a calculated field. For example, I can show open Service Tickets by ageing, which is a calculated field in a kanban type view, I will publish a tutorial little later showing how to do this ;-).

I named it "control board" because the idea is to enhance kanban and also to have the ability to incorporate other Bootstrap Admin theme widgets at Espo's dashboard in the future.

Saludos

[tothewine]

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

[telecastg]

I'm glad that you like it :-)

I am currently working on improving functionality by allowing calculated fields and range grouped values to be used as criteria instead of being limited to an enum field. Will post any progress.

[blueprint]

This is great telecastg , excellent work!!!

[telecastg]

Thanks blueprint :-)

[telecastg]

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)

{
    "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

{
    "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

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)

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)

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

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

{
    "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]

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]

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.

[OliverMD78]

Maybe this is a syntax error... sql statement case


{
"view":"custom:views/service-ticket/dashlets/service-tickets-by-status",
"instrumentView": "control-board:views/dashlets/instruments/progress-cards",
"entityType": "Case",
"criteriaScope": "Case",
"criteriaAttribute": "status",
"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"}
],
"options": {
"fields": {
"title": {
"type": "varchar",
"required": true
},
"dateFrom": {
"type": "date",
"required": false
},
"dateTo": {
"type": "date",
"required": false
},
"dateFilter": {
"type": "enum",
"options": ["currentYear", "currentQuarter", "currentMonth", "ever", "between"],
"default": "currentYear",
"translation": "Global.options.dateSearchRanges"
}
},
"layout": [
{
"rows": [
[
{"name": "title"}
],
[
{"name": "dateFilter"},
false
],
[
{"name": "dateFrom"},
{"name": "dateTo"}
]
]
}
],
"defaults": {
"title": "Tickets nach Status",
"dateFilter": "currentYear"
}
}
}



the dashlet looks like yours....

[telecastg]

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]

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

[telecastg]

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

"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.

[espcrm]

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.

[telecastg]

Hi espcrm no sorry, those scripts are part of a new paid extension that will be released in the near future.

[rabii]

what is the purpose of the extension ? let us know when it is released.

[espcrm]

OK. I guess just wait for it then.

[wtconseil]

can we get the content of

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