Finding an upcoming birthday without writing raw SQL

[bandtank]

I need a report that shows me a list of birthdays within the next X days. I wrote a query that works:

SELECT
  id,
  DATE_ADD(
    date_of_birth,
    INTERVAL TIMESTAMPDIFF(YEAR, DATE_ADD(date_of_birth,INTERVAL 1 DAY), curdate())+1 YEAR
  ) upcoming_birthday
FROM
  account
HAVING
  upcoming_birthday BETWEEN curdate() AND DATE_ADD(curdate(), INTERVAL 14 DAY)

I can't figure out how to create the same report using complex expressions, filters, ORM methods, etc. It seems too complex for the abstraction layers, but maybe I don't know how to use it well enough. I am trying to avoid writing raw SQL in a hook. Ideally, the logic would live directly in the report instead of writing any code at all.

dateOfBirth is stored as the real birthday (1999-01-01, for example). Therefore, comparing the year directly won't work.

[item]

https://www.espocrm.com/blog/birthday-notifications-in-espocrm/

[bandtank]

I saw that page when i first started working on this problem. Unfortunately, the proposed solution does not appear to work for the current scenario because the birthday needs to be within an arbitrary number of days from today.

[bandtank]

Here is a working solution for anyone who needs something similar. It looks for birthdays within the upcoming 14-day period. I wish I could figure out how to remove the raw SQL using either a complex expression in the GUI or, at the very least, an expression in the ORM query.

<?php
namespace Espo\Custom\Reports;

use \Espo\ORM\Entity;
use \Espo\Core\Exceptions\Error;
use \Espo\Core\Exceptions\NotFound;

class BirthdaysUsers extends \Espo\Modules\Advanced\Reports\Base
{
   public function run($where = null, array $params = null)
   {
      $sql = "
       SELECT
         id,
         DATE_ADD(
            date_of_birth,
            INTERVAL TIMESTAMPDIFF(YEAR, DATE_ADD(date_of_birth,INTERVAL 1 DAY), curdate())+1 YEAR
       ) upcoming_birthday
       FROM
          user
       HAVING
          upcoming_birthday BETWEEN curdate() AND DATE_ADD(curdate(), INTERVAL 14 DAY)";

      $pdo = $this->getEntityManager()->getPDO();
      $sth = $pdo->prepare($sql);
      $sth->execute();

      $rows = $sth->fetchAll();
      $ids = [];
      foreach ($rows as $row) {
            $ids[] = $row['id'];
      }

      $collection = $this->getEntityManager()->getRepository('User')
         ->where([
            'id' => $ids,
            'isActive' => True,
         ])
         ->order('name','ASC')
         ->find(['name','dateOfBirth']);

      return array(
         'collection' => $collection,
         'total' => count($collection)
      );
   }
}

The only issue I've found is with dashlets. The report works just fine, but dashlets show "Error" (note: I increased the period to 90 days to make the report show results because no one in my organization has a birthday within 14 days of today):