Custom ORM expressions - unable to call

[bandtank]

I am writing a new ORM function to convert timezones by name. However, I can't figure out how to implement the instructions here regarding the creation of a custom function.

custom/Espo/Modules/Odyssey/Resources/metadata/app/orm.json

{
    "platforms": {
        "Mysql": {
            "functionConverterClassNameMap": {
              "convertTimezoneByName": "Espo\\Modules\\Odyssey\\Classes\\ORM\\ConvertTimezoneByName"
            }
        }
    }
}

custom/Espo/Modules/Odyssey/Classes/ORM/ConvertTimezoneByName.php

<?php

namespace Espo\Modules\Odyssey\Classes\ORM;

use Espo\ORM\QueryComposer\Part\FunctionConverter;

class ConvertTimezoneByName implements FunctionConverter
{
  public function convert(string ...$argumentList): string
  {
    ...

    return "CONVERT_TZ({$datetime}, {$fromTz}, {$toTz})";
  }
}

It doesn't work when used in an ORM expression:

  private function getQueryBuilder(?SearchParams $searchParams, ?User $user): QueryBuilder
  {
    ...

    $queryBuilder = $selectBuilder->buildQueryBuilder();

    $queryBuilder->where(
      Expr::or(
        Expr::notEqual(
          Expr::date(Expr::convertTimezoneByName(Expr::column('dateStart'), 'America/Denver')),
          Expr::date(Expr::convertTimezoneByName(Expr::column('dateStartBill'), 'America/Denver')),
        ),
      )
    );

    ...
  }​ 

... ERROR: Slim Application Error Type: Error Code: 0 Message: Call to undefined
method Espo\ORM\Query\Part\Expression::convertTimezoneByName()

I've tried probably 20 different ways to call the function. Nothing works. Any advice would be greatly appreciated.

[bandtank]

Notably, the function is also not available in the GUI:

[yuri]

Expr::convertTimezoneByName won't work, as the Expression is a static class. It cannot be patched dynamically which is dictated by the PHP language design.


Here's an example:

https://github.com/espocrm/espocrm/blob/9.3.8/application/Espo/Resources/metadata/app/orm.json#L7

https://github.com/espocrm/espocrm/blob/9.3.8/application/Espo/Core/ORM/QueryComposer/Part/FunctionConverters/Abs.php#L34

You need to use UPPER_CASE when defining in metadata.

Usage in PHP code:

Expression::create('CONVERT_TIMEZONE_BY_NAME:(myColumn, "America/Denver")') 

Note that the column argument is not available in the function implementation. The real value will be resolved at the database level.

Another example: https://github.com/espocrm/espocrm/b...cyRate.php#L49

[yuri]

You can just compose the needed database function call string, passing argumentList[0] and argumentList[1] into its parameters.

[yuri]

Complex expression field autocomplete is extended separately: https://docs.espocrm.com/development...ex-expression/

[bandtank]

EDIT: I submitted this response before I saw your comments. Thanks for the help. I will review the information now.

---

Some progress has been made, but there are still many unknowns because this situation is not explained in the documentation. I shortened the name of the custom function to TZ2 to make things easier.

Here is the full definition of the function:

custom/Espo/Modules/Odyssey/Classes/ORM/ConvertTimezoneByName.php

<?php

namespace Espo\Modules\Odyssey\Classes\ORM;

use Espo\ORM\QueryComposer\Part\FunctionConverter;

class ConvertTimezoneByName implements FunctionConverter
{
  public function convert(string ...$argumentList): string
  {
    $count = count($argumentList);

    if($count < 2 || $count > 3) {
      throw new RuntimeException("CONVERT_TIMEZONE_BY_NAME requires 2 or 3 arguments, {$count} provided.");
    }

    $datetime = $argumentList[0];

    if($count === 2) {
      $fromTz = "'UTC'";
      $toTz = $argumentList[1];
    } else {
      $fromTz = $argumentList[1];
      $toTz = $argumentList[2];
    }

    return "CONVERT_TZ({$datetime}, {$fromTz}, {$toTz})";
  }
}

Building an expression like this compiles correctly:

Expr::date(Expr::create("TZ2:('dateStart','America/Denver')"));

Here is the resulting SQL:

DATE(CONVERT_TZ('dateStart', 'UTC', 'America/Denver')

The column name was not converted to the right format: date_start. For some reason, using:

Expr::date(Expr::convertTimezone(Expr::column('dateStart'), -6));

produces this SQL:

DATE(CONVERT_TZ(session.date_start, '+00:00', '-06:00')))

I can't figure out how to get Espo to compile my version into session.date_start instead of dateStart.

[yuri]

You pass it as a literal string 'dateStartBill'. You need to pass it without quotes.

Expr::create("TZ2:(dateStartBill, 'America/Denver')") 

[bandtank]

Thank you for the help. It works now. Here are the final implementations in case anyone else would like to do something similar.

custom/Espo/Modules/Odyssey/Resources/metadata/app/orm.json

{
    "platforms": {
        "Mysql": {
            "functionConverterClassNameMap": {
              "TZ2": "Espo\\Modules\\Odyssey\\Classes\\ORM\\ConvertTimezoneByName"
            }
        }
    }
}

custom/Espo/Modules/Odyssey/Classes/ORM/ConvertTimezoneByName.php

<?php

namespace Espo\Modules\Odyssey\Classes\ORM;

use Espo\ORM\QueryComposer\Part\FunctionConverter;

class ConvertTimezoneByName implements FunctionConverter
{
  public function convert(string ...$argumentList): string
  {
    $count = count($argumentList);

    if($count < 2 || $count > 3) {
      throw new RuntimeException("CONVERT_TIMEZONE_BY_NAME requires 2 or 3 arguments, {$count} provided.");
    }

    $datetime = $argumentList[0];

    if($count === 2) {
      $fromTz = "'UTC'";
      $toTz = $argumentList[1];
    } else {
      $fromTz = $argumentList[1];
      $toTz = $argumentList[2];
    }

    return "CONVERT_TZ({$datetime}, {$fromTz}, {$toTz})";
  }
}

Example usage in the backend:

  private function getQueryBuilder(?SearchParams $searchParams, ?User $user): QueryBuilder
  {
    $selectBuilder = $this->selectBuilderFactory
      ->create()
      ->from(CSession::ENTITY_TYPE)
      ->withStrictAccessControl()
      ->withSearchParams($searchParams);

    if($user)
      $selectBuilder->forUser($user);

    $queryBuilder = $selectBuilder->buildQueryBuilder();

    $queryBuilder->where(
      Expr::or(
        Expr::notEqual(
          Expr::date(Expr::create("TZ2:(dateStart    , 'America/Denver')")),
          Expr::date(Expr::create("TZ2:(dateStartBill, 'America/Denver')")),
        ),
        Expr::notEqual(
          Expr::date(Expr::create("TZ2:(dateEnd      , 'America/Denver')")),
          Expr::date(Expr::create("TZ2:(dateEndBill  , 'America/Denver')")),
        ),
      )
    );

    return $queryBuilder;
  } 

custom/Espo/Modules/Odyssey/Resources/metadata/app/complexExpression.json

{
    "functionList": [
        "__APPEND__",
        {
            "name": "TZ2_UTC",
            "insertText": "TZ2:(COL, TZ_TO)",
            "returnType": "string"
        },
        {
            "name": "TZ2",
            "insertText": "TZ2:(COL, TZ_FROM, TZ_TO)",
            "returnType": "string"
        }
    ]
}

which results in this:



My report uses the function like this:

OR:(
  NOT_EQUAL:(
    DAYOFMONTH:(TZ2:(
      dateStart,
      'America/Denver'
    )),
    DAYOFMONTH:(TZ2:(
      dateStartBill,
      'America/Denver'
    ))
  ),
  NOT_EQUAL:(
    DAYOFMONTH:(TZ2:(
      dateEnd,
      'America/Denver'
    )),
    DAYOFMONTH:(TZ2:(
      dateEndBill,
      'America/Denver'
     ))
  )
)​

I still need to replace DAYOFMONTH with DATE.

---

EDIT: Actually, DATE works already, but there is no autocomplete. You can fix that by adding an entry for the function in the complexExpression.json file:

{
    "functionList": [
        ...
        {
            "name": "DATE",
            "insertText": "DATE:(VALUE)",
            "returnType": "string"
        }
    ]
}​