Custom ListType Reports

[bandtank]

For those of you who use the Advanced Pack, you may want to create a custom list report using PHP. This tutorial explains how to do that. Note: There is an example of a grid type report in the "examples" category of the Report section after installing the extension. List type reports are similar, but there are a few key differences.

Background: Reports are typically created in the GUI. Creation of a report mirrors the creation of any other entity type: go to the entity's list view, click the "Create ___" button, and do your thing. Reports are special in some regards because the entity can be either external or internal. The word external is not used anywhere in the application, but internal is used to describe custom reports that run PHP code, so it is natural to refer to the other type as external. In summary, if you want to create a custom report, meaning you want to run PHP code when a report is opened, triggered, or called, it is necessary to create a report that is internal. Editing an internal report is not possible from the GUI. The logic of an internal report must be edited directly in the backend.

---

Step 1 - Create the Report Record
The first step is to create a report record in the database. You can accomplish that using any of the following methods:

• (Preferred) In the GUI
• (Preferred) Using PHP
• (Dangerous - not preferred) Directly in the database

To create a report in the GUI, do what you would normally do to create a report: click the "Create Report" button in the list view, fill in the fields, and hit save. For this example, I created a report called "Test List Report" with a type of "Lead". Regardless of how you create the report, the record will be stored in the database with one important field set to false: (PHP) isInternal or (MySql) is_internal.

---

Step 2 - Edit the Report Record
After the report has been created, you must edit the database record. There are many fields to inspect, but the following two are the most important:

• is_internal - this must be set to 1
• internal_class_name - this must be set to ModuleName:ClassName

There are two places to store custom reports: Custom and Modules. For this example, I will create a file called TestListReport.php, which is also the name of the class therein.

• If the report is stored in custom/Espo/Custom/Reports, the database entry for internal_class_name should be Custom:TestListReport.
• If the report s stored in custom/Espo/Modules/MyCustomModules/Reports, the database entry for internal_class_name should be MyCustomModule:TestListReport.

For this example, I will create the report in custom/Espo/Custom/Reports. Therefore, the database record should be set to have Custom:TestListReport as the value for internal_class_name.

---

Step 3 - Create the File and Class
Create a file called TestListReport.php in custom/Espo/Custom/Reports. Copy and paste the following code into the report:

<?php
namespace Espo\Custom\Reports;

use Espo\Entities\User;
use Espo\ORM\EntityManager;
use Espo\Core\Select\SearchParams;

use Espo\Modules\Crm\Entities\Lead;
use Espo\Modules\Advanced\Reports\ListReport;
use Espo\Modules\Advanced\Tools\Report\ListType\Result ;

class TestListReport implements ListReport
{
  public function __construct(
    private EntityManager $entityManager,
  ) {}

  public function run(?SearchParams $searchParams, ?User $user): Result {
    $collection = $this->entityManager->getRDBRepository(Lead::ENTITY_TYPE)->find();

    return new Result($collection, $collection->count());
  }
}​

A few notable comments:

• The interface called ListReport must be implemented. Failing to do so will cause a silent failure when the injection occurs, which is very difficult to debug. I spent hours figuring this out even though, in hindsight, it is very obvious. Do better than I did.
• The template code does not implement any type of sorting or filtering. It returns a list of every Lead in whichever order MySQL feels like using. We'll add more functionality later.
• The example for a GridType report calls the count() function on the RDB repository. I call count() on the collection, which is essentially the same thing, but it looks cleaner to me.

That's it. You should be able to run your report now and a list of records, if any are in your database, should appear.

---

Step 4 - Add Fields to the List View
Because of how the template code is written, all fields are selected from the database and returned to the GUI. Therefore, every field in each Lead record is available to be displayed except the complex fields, such as some relationships or foreign links, which are out of scope for this tutorial. For regular field types, such as varchar, float, or enum, the data is available to the GUI.

For this example, I am going to add the modifiedAt field to the list view, which will display the datetime value associated with the most recent modification of reach record. In the database record for the report, make the following change to the column field in the database:

• Original: ["name"]
• New: ["name", "modifiedAt"]

Technically, that's all you need to do for the field to appear, but there are more fields to change if you want to do it correctly. Now modify the columns_data field as follows:

• Original: {"name":{"width":null,"align":"left","link":tru e," exportOnly":false,"notSortable":false}}
• New: {"name":{"width":null,"align":"left","link":tru e," exportOnly":false,"notSortable":false}, "modifiedAt":{"width":null,"align":"left","lin k":f alse,"exportOnly":false,"notSortable":false}}

Refresh the page to see the Modified At (modifiedAt) field appear in the list.

A few notes about the columns_data options:

• The entry is an associative array with this format: {"fieldName": <array of options>, "nextFieldName": <array of options>, ...}
• width - Enter null for automatic spacing or an integer for percentage-based spacing. E.g., entering 20 means 20%.
• align: The alignment of the text in the column: left, center, or right
• link: True if you want the value to be a link to the record or false if you do not want the value to be a link
• exportOnly: If you want this field to be available for export, but not for viewing in the list view
• notSortable: If you do not want to let the user sort by this field, set the value to false.

---

Step 5 - Add Runtime Filters
To add a runtime filter, which is the kind the user can modify from the list view, make the following change to the runtime_filters field in the database:

• Original: null
• New: ["addressCity"]

Refresh the page to see the City (addressCity) field appear in the filter section above the list of results.

Wait, there's a problem. The filter isn't working regardless of which type of filter setting is selected or the value of the filter. It's because we aren't using the information in the query. Here is what is passed into the run function through the searchParams parameter when I choose Starts With and Den as the type and value of the filter, respectively:

{
  "select":null,
  "orderBy":"createdAt",
  "order":"DESC",
  "offset":0,
  "maxSize":30,
  "boolFilterList":[],
  "primaryFilter":null,
  "textFilter":null,
  "where":[
    {
      "type":"startsWith",
      "attribute":"addressCity",
      "value":"Den"
    }
  ],
  "maxTextAttributeLength":null
}

Here are the relevant parts of the API for searchParams, which can be found in application/Espo/Core/Select/SearchParams.php:

• getRaw() - An example of the raw output is shown directly above this comment
• getSelect() - Get the value of the select key
• getOrderBy() - Get the field to sort the output
• getOrder() - Get the direction of the sort (ASC or DESC)
• getOffset() - Get the value of the offset for the database query
• getMaxSize() - Get the number of records to return from the database at the given offset
• getWhere() - Get the filters to use in the database query

There are more API functions available for searchParams for you to investigate. You can implement the logic using the ORM, which is a valid way to modify the query and data, but there is a much easier way if you do not need to implement custom logic. For this example, I will show you how to use the preparator in custom/Espo/Modules/Advanced/Tools/Report/ListType/QueryPreparator.php, which implements a lot of complex logic to simplify the process of filtering the results of the query. Here is the code:

<?php
namespace Espo\Custom\Reports;

use Espo\Entities\User;
use Espo\ORM\EntityManager;
use Espo\Core\Select\SearchParams;
use Espo\Core\Select\SelectBuilderFactory;

use Espo\Modules\Crm\Entities\Lead;
use Espo\Modules\Advanced\Reports\ListReport;
use Espo\Modules\Advanced\Tools\Report\ListType\Result;

class TestListReport implements ListReport
{
  public function __construct(
    private EntityManager $entityManager,
    private SelectBuilderFactory $selectBuilderFactory,
  ) {}

  public function run(?SearchParams $searchParams, ?User $user): Result {
    $selectBuilder = $this->selectBuilderFactory->create()->from(Lead::ENTITY_TYPE)
      ->withStrictAccessControl()->withSearchParams($searchParams);

    # If you want to apply the ACL
    if($user)
      $selectBuilder->forUser($user);

    $queryBuilder = $selectBuilder->buildQueryBuilder();
    $query = $queryBuilder->build();

    # If you want to see the raw SQL:
    $sql = $this->entityManager->getQueryComposer()->compose($query);
    $GLOBALS["log"]->debug("",[$sql]);

    $collection = $this->entityManager->getRDBRepository(Lead::ENTITY_TYPE)->clone($query)->find();

    return new Result($collection, $collection->count());
  }
}​

The searchParams parameter is automatically parsed by the selectBuilderFactory object. The list of results will be filtered and sorted using the desired fields and values. You can learn a lot more about how to use the logic by inspecting the following functions in custom/Espo/Modules/Advanced/Tools/Report/Service.php:

• public function runList(...)
• private function executeListReport(...)

I suggest looking at custom/Espo/Modules/Advanced/Tools/Report/ListType/QueryPreparator.php as well.