Чиним SuiteCRM: фильтр по дате в модуле Отчёты

[SpravkaCRM.ru 5]

Сегодня столкнулся с ошибкой, присутствовавшей в SuiteCRM 7.3.2 (а возможно и в более ранних и более поздних модификациях этой CRM): Если создать новый отчет в модуле Отчеты (AOR_Reports), и одним из параметров фильтрации указать поле типа Datetime, то при построении отчета записи по этому полю не ищутся.

 

 

У меня это проявилось, когда я хотел найти все Задачи, созданные за определенный день (по календарику). Вот как выглядел отчет:

Таким образом у нас отчет будет состоять из трех колонок: ID записи, Название и Дата создания записи.

А фильтровать данные мы по идее должны по Дате создания записей:

Но если заполнить поле в колонке "Значение" и нажать на кнопку "Обновить", то ничего не происходит. Записи не ищутся, хотя я точно знаю, что они есть на указанную дату.

В результате разбора модуля AOR_Reports было выявлено, что непосредственно генерацией WHERE-условия для выборки по отчету занимается функция build_report_query_where, находящаяся в классе AOR_Report в файле modules/AOR_Reports/AOR_Report.php

Результатом работы функции build_report_query_where являлся массив:

Array
(
    [select] => Array
        (
            [0] => `project_task`.id AS 'project_task_id'
            [1] => `project_task`.id AS 'ID0'
            [2] => `project_task`.name AS 'Название1'
            [3] => `project_task`.date_entered AS 'Дата_создания2'
        )

    [where] => Array
        (
            [0] => `project_task`.date_entered = '23.12.2015'
            [1] => project_task.deleted = 0 

который потом уже далее собирался в конечный SQL-запрос:

SELECT `project_task`.id AS 'project_task_id', `project_task`.id AS 'ID0', `project_task`.name AS 'Название1', `project_task`.date_entered AS 'Дата_создания2' FROM `project_task`  WHERE `project_task`.date_entered = '23.12.2015' AND project_task.deleted = 0 

Как мы видим, `project_task`.date_entered = '23.12.2015' - абсолютно не то, что нам надо, чтобы корректно найти данные. Мало того, что формат даты указан не верный, так еще подвешенным остается что делать с временем. Ведь date_entered (дата создания) у нас в базе имеет значения что то типа такого: 2015-12-23 12:34:02, что никак не идентично 23.12.2015, хотя по логике это должно быть наше значение.

В общем нам надо преобразовать наш запрос таким образом, чтобы занчения в базе уравнялись в формате с вводимыми значениями.

Я решил, что самым оптимальным будет введенное значение оставить как есть, а модернизации подвергнуть указание поля в базе данных: у нас должен на выходе получиться вот такой запрос:

SELECT `project_task`.id AS 'project_task_id', `project_task`.id AS 'ID0', `project_task`.name AS 'Название1', `project_task`.date_entered AS 'Дата_создания2' FROM `project_task`  WHERE DATE_FORMAT(`project_task`.date_entered, '%d.%m.%Y') = '23.12.2015' AND project_task.deleted = 0 

Таким образом прибегнув к функции DATE_FORMAT мы сможем сделать эти значения идентичными по своему формату, что позволит их начать сравнивать.

Реализация:

Во первых в функцию build_report_query_where в самый верх добавляем строку с подключением глобальной переменной $timedate, из которой мы в последствии сможем извлечь текущие настройки формата даты для текущего пользователя:

/**
 * @param array $query
 * @return array
 */
function build_report_query_where($query = array()){
    global $beanList, $app_list_strings, $sugar_config;
    global $timedate;

а чуть дальше в этой же функции ищем строчки:

if ((isset($data['source']) && $data['source'] == 'custom_fields')) {
    $field = $this->db->quoteIdentifier($table_alias . '_cstm') . '.' . $condition->field;
    $query = $this->build_report_query_join($table_alias . '_cstm', $table_alias . '_cstm', $oldAlias, $condition_module, 'custom', $query);
} else {
    $field = $this->db->quoteIdentifier($table_alias) . '.' . $condition->field;

$condition->value = date($timedate->get_date_format(), strtotime("+{$timedate->getUserUTCOffset()} minutes", strtotime($condition->value)));

}

Здесь переменная $field - это название нашего поля в базе данных (`project_task`.date_entered). Нам надо указать его не как просто `project_task`.date_entered, а как DATE_FORMAT(`project_task`.date_entered, '%d.%m.%Y')

Для этого модернизируем этот участок кода следующим образом:

 

if ((isset($data['source']) && $data['source'] == 'custom_fields')) {
    $field = $this->db->quoteIdentifier($table_alias . '_cstm') . '.' . $condition->field;
    $query = $this->build_report_query_join($table_alias . '_cstm', $table_alias . '_cstm', $oldAlias, $condition_module, 'custom', $query);
} elseif($condition_module->field_defs[$condition->field]['type'] == 'datetime') {
    // Для случая, когда поле с типом Datetime
    $field = "DATE_FORMAT(". $this->db->quoteIdentifier($table_alias) . '.' . $condition->field.", '" . $timedate->get_cal_date_format() . "')";
} else {
    // Все остальные стандартные случаи
    $field = $this->db->quoteIdentifier($table_alias) . '.' . $condition->field;
}

Теперь у нас для всех полей с типом Datetime будет происходить корректное применение фильтров в Отчетах. Ну а в нашем случае получился запрос:

SELECT `project_task`.id AS 'project_task_id', `project_task`.id AS 'ID0', `project_task`.name AS 'Название1', `project_task`.date_entered AS 'Дата_создания2' FROM `project_task`  WHERE DATE_FORMAT(`project_task`.date_entered, '%d.%m.%Y') = '23.12.2015' AND project_task.deleted = 0 

И вот его результат работы:

 

Патч для SuiteCRM, который исправляет описанную ошибку:

 

 

[Palach 0]

очень полезная статья , еще бы отремонтировать не только отчет по числу но и по интервалу тоже не работает, а так отличный проект много подчерпнул для себя

[SpravkaCRM.ru 5]

В 10.01.2016 at 22:58, Palach сказал:

очень полезная статья , еще бы отремонтировать не только отчет по числу но и по интервалу тоже не работает, а так отличный проект много подчерпнул для себя

Да, есть такое дело! Если на вскидку: нужно найти в modules/AOR_Reports/AOR_Report.php вызов функции getPeriodDate:

$value = '"' . getPeriodDate($params)->format('Y-m-d H:i:s') . '"';

и заменить эту конструкцию на:

//$value = '"' . getPeriodDate($params)->format('Y-m-d H:i:s') . '"';
$periods = getPeriodDates($params);
$value = "BETWEEN '". $periods[0]->format('Y-m-d H:i:s')."' AND '". $periods[1]->format('Y-m-d H:i:s')."'";
$condition->operator = ''; // Для BETWEEN убираем знак равенства

getPeriodDates - эта новая функция вместо getPeriodDate. старая функция для периода определяла только начало периода. конец периода почему то они решили нам совсем не нужен. я добавил работу действительно по периоду, а не по дате.

функция находится в файле modules/AOR_Reports/aor_utils.php

добавляйте туда:

 

/**
 * getPeriodDate
 * BugFix by crmhosting.ru
 * https://bitbucket.org/crmhosting/suitecrm_bugfix_reports
 * @param $date_time_period_list_selected
 * @return DateTime
 */
function getPeriodDates($date_time_period_list_selected)
{

    global $sugar_config;
    $datetime_period_start = new DateTime();
    $datetime_period_stop = new DateTime();

    // Setup when year quarters start & end
    if ($sugar_config['aor']['quarters_begin']) {
        $q = calculateQuarters($sugar_config['aor']['quarters_begin']);
    } else {
        $q = calculateQuarters();
    }


    if ($date_time_period_list_selected == 'today') {
        $datetime_period_start = new DateTime();
        $datetime_period_stop = new DateTime();
    } else if ($date_time_period_list_selected == 'yesterday') {
        $datetime_period_start = $datetime_period_start->sub(new DateInterval("P1D"));
        $datetime_period_stop = $datetime_period_stop->sub(new DateInterval("P1D"));
    } else if ($date_time_period_list_selected == 'this_week') {
        $datetime_period_start = $datetime_period_start->setTimestamp(strtotime('this week'));
        $datetime_period_stop = $datetime_period_stop->setTimestamp(strtotime('this sunday'));
    } else if ($date_time_period_list_selected == 'last_week') {
        $datetime_period_start = $datetime_period_start->setTimestamp(strtotime('last week'));
        $datetime_period_stop = $datetime_period_stop->setTimestamp(strtotime('last sunday'));
    } else if ($date_time_period_list_selected == 'this_month') {
        $datetime_period_start = $datetime_period_start->setDate($datetime_period_start->format('Y'), $datetime_period_start->format('m'), 1);
    } else if ($date_time_period_list_selected == 'last_month') {
        $datetime_period_start = $datetime_period_start->setDate($datetime_period_start->format('Y'), $datetime_period_start->format('m'), 1);
        $datetime_period_stop = $datetime_period_stop->setDate($datetime_period_stop->format('Y'), $datetime_period_stop->format('m'), $datetime_period_stop->format('t'));
    } else if ($date_time_period_list_selected == 'this_quarter') {
        $thisMonth = $datetime_period_start->setDate($datetime_period_start->format('Y'), $datetime_period_start->format('m'), 1);
        if ($thisMonth >= $q[1]['start'] && $thisMonth <= $q[1]['end']) {
            // quarter 1
            $datetime_period_start = $datetime_period_start->setDate($q[1]['start']->format('Y'), $q[1]['start']->format('m'), $q[1]['start']->format('d'));
            $datetime_period_stop = $datetime_period_stop->setDate($q[1]['end']->format('Y'), $q[1]['end']->format('m'), $q[1]['end']->format('t'));
        } else if ($thisMonth >= $q[2]['start'] && $thisMonth <= $q[2]['end']) {
            // quarter 2
            $datetime_period_start = $datetime_period_start->setDate($q[2]['start']->format('Y'), $q[2]['start']->format('m'), $q[2]['start']->format('d'));
            $datetime_period_stop = $datetime_period_stop->setDate($q[2]['end']->format('Y'), $q[2]['end']->format('m'), $q[2]['end']->format('t'));
        } else if ($thisMonth >= $q[3]['start'] && $thisMonth <= $q[3]['end']) {
            // quarter 3
            $datetime_period_start = $datetime_period_start->setDate($q[3]['start']->format('Y'), $q[3]['start']->format('m'), $q[3]['start']->format('d'));
            $datetime_period_stop = $datetime_period_stop->setDate($q[3]['end']->format('Y'), $q[3]['end']->format('m'), $q[3]['end']->format('t'));
        } else if ($thisMonth >= $q[4]['start'] && $thisMonth <= $q[4]['end']) {
            // quarter 4
            $datetime_period_start = $datetime_period_start->setDate($q[4]['start']->format('Y'), $q[4]['start']->format('m'), $q[4]['start']->format('d'));
            $datetime_period_stop = $datetime_period_stop->setDate($q[4]['end']->format('Y'), $q[4]['end']->format('m'), $q[4]['end']->format('t'));
        }
    } else if ($date_time_period_list_selected == 'last_quarter') {
        $thisMonth = $datetime_period_start->setDate($datetime_period_start->format('Y'), $datetime_period_start->format('m'), 1);
        if ($thisMonth >= $q[1]['start'] && $thisMonth <= $q[1]['end']) {
            // quarter 1 - 3 months
            $datetime_period_start = $q[1]['start']->sub(new DateInterval('P3M'));
            $datetime_period_stop = $q[1]['end']->sub(new DateInterval('P3M'));
            $datetime_period_stop = $datetime_period_stop->setDate($datetime_period_stop->format('Y'), $datetime_period_stop->format('m'), $datetime_period_stop->format('t'));
        } else if ($thisMonth >= $q[2]['start'] && $thisMonth <= $q[2]['end']) {
            // quarter 2 - 3 months
            $q[2]['start']->sub(new DateInterval('P3M'));
        } else if ($thisMonth >= $q[3]['start'] && $thisMonth <= $q[3]['end']) {
            // quarter 3 - 3 months
            $q[3]['start']->sub(new DateInterval('P3M'));
        } else if ($thisMonth >= $q[4]['start'] && $thisMonth <= $q[4]['end']) {
            // quarter 4 - 3 months
            $q[3]['start']->sub(new DateInterval('P3M'));
        }
    } else if ($date_time_period_list_selected == 'this_year') {
        $datetime_period_start = $datetime_period_start = $datetime_period_start->setDate($datetime_period_start->format('Y'), 1, 1);
        $datetime_period_stop = $datetime_period_stop = $datetime_period_stop->setDate($datetime_period_stop->format('Y'), 12, 31);
    } else if ($date_time_period_list_selected == 'last_year') {
        $datetime_period_start = $datetime_period_start = $datetime_period_start->setDate($datetime_period_start->format('Y') - 1, 1, 1);
        $datetime_period_stop = $datetime_period_stop = $datetime_period_stop->setDate($datetime_period_stop->format('Y') - 1, 12, 31);
    }
    // set time to 00:00:00
    $datetime_period_start = $datetime_period_start->setTime(0, 0, 0);
    // set time to 23:59:59
    $datetime_period_stop = $datetime_period_stop->setTime(23, 59, 59);
    return array($datetime_period_start, $datetime_period_stop);
}

А вообще созрела мысль начать делать патчи и расширения для SuiteCRM. Я сейчас этот багфикс в виде патча оформляю. Как доделаю - выложу...

[SpravkaCRM.ru 5]

 

Вот "проба пера" с запиливанием патчей. Устранил косяки, отмеченные тут.

[Palach 0]

Огромное спасибо))) патч тоже очень хорошо ))) Благое дело делаете глядишь через пару тройку месяцев мощный ресурс по suiteCRM русскоязычный будет!!!

[SpravkaCRM.ru 5]

На сайт добавил раздел с загрузками. Патч там: