Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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
    Click image for larger version  Name:	Progress Cards.PNG Views:	0 Size:	39.1 KB ID:	62892

    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

    Code:
    {
        "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
    Code:
    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
    Code:
    {
        "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.
    Last edited by telecastg; 02-20-2023, 06:02 PM.

  • #2
    telecastg

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

    Best Regards

    Comment


    • telecastg
      telecastg commented
      Editing a comment
      You're very welcome :-)

    • item
      item commented
      Editing a comment
      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
      telecastg commented
      Editing a comment
      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
      Last edited by telecastg; 09-25-2020, 06:15 AM.

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

    Comment


    • telecastg
      telecastg commented
      Editing a comment
      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.
      Last edited by telecastg; 09-24-2020, 03:51 PM.

  • #4
    This is great telecastg , excellent work!!!

    Comment


  • #5
    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.

    Click image for larger version  Name:	Progress Cards By Calculated Field.PNG Views:	0 Size:	43.5 KB ID:	62986

    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()
    Last edited by telecastg; 09-30-2020, 03:22 PM.

    Comment


    • #6
      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...

      Comment


      • #7
        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.
        Click image for larger version

Name:	Dashlet options panel.PNG
Views:	1510
Size:	18.8 KB
ID:	63320
        Last edited by telecastg; 10-07-2020, 07:05 AM.

        Comment


        • OliverMD78
          OliverMD78 commented
          Editing a comment
          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....

      • #8
        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
        Last edited by telecastg; 10-07-2020, 06:51 PM.

        Comment


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

          Comment


          • #10
            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.

            Comment


            • #11
              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.

              Comment


              • telecastg
                telecastg commented
                Editing a comment
                Hi esforim no sorry, those scripts are part of a new paid extension that will be released in the near future.

              • rabii
                rabii commented
                Editing a comment
                what is the purpose of the extension ? let us know when it is released.

              • esforim
                esforim commented
                Editing a comment
                OK. I guess just wait for it then.

            • #12
              can we get the content of
              application/Espo/Modules/ControlBoard/Controllers/ControlBoard.php actionTotalsByCriteria()
              application/Espo/Modules/ControlBoard/Services/ControlBoard.php

              Comment

              Working...
              X