Jump to content
SpravkaCRM.ru - Ваш справочник по CRM

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

SpravkaCRM.ru
 Share

Recommended Posts

SpravkaCRM.ru Apprentice

SpravkaCRM.ru

Posted
Posted

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

 

 

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

567a6ef73b306_2015-12-2312-52-21CRMHosti

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

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

567a6f7351dbd_2015-12-2312-54-31CRMHosti

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

В результате разбора модуля 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

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

567a7428ae0c4_2015-12-2313-14-43CRMHosti

 

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

 

 

Link to comment
Share on other sites
  • 2 weeks later...
Guest Palach Newbie

Guest Palach

Posted
Posted

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

до.png

Link to comment
Share on other sites
SpravkaCRM.ru Apprentice

SpravkaCRM.ru

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

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

до.png

Да, есть такое дело! Если на вскидку: нужно найти в 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
 * http://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. Я сейчас этот багфикс в виде патча оформляю. Как доделаю - выложу...

Link to comment
Share on other sites
Guest Palach Newbie

Guest Palach

Posted
Posted

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

Link to comment
Share on other sites
  • 2 weeks later...
  • 3 years later...
zaval Newbie

zaval

Posted
Posted
В 12.01.2016 at 21:52, SpravkaCRM.ru сказал:

Да, есть такое дело! Если на вскидку: нужно найти в 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
 * http://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. Я сейчас этот багфикс в виде патча оформляю. Как доделаю - выложу...

У меня этот файл неправильно формировал отчет за периоды: Прошлый месяц и Прошлый квартал. Немного переделал под себя 

Прошлый месяц: 

else if ($date_time_period_list_selected == 'last_month') {

        $datetime_period_start = $datetime_period_start->setTimestamp(strtotime('first day of last month'));

        $datetime_period_stop = $datetime_period_stop->setTimestamp(strtotime('last day of last month'));

    }

 

Прошлый квартал:

else if ($date_time_period_list_selected == 'last_quarter') {

    $thisMonth = date('m');

    if ($thisMonth>=1 && $thisMonth <=3)
    {
        $datetime_period_start = $datetime_period_start->setTimestamp(strtotime('first day of October previous year gmt'));
        $datetime_period_stop = $datetime_period_stop->setTimestamp(strtotime('last day of December previous year gmt'));
    }

    if ($thisMonth>=4 && $thisMonth <=6)
    {
        $datetime_period_start = $datetime_period_start->setTimestamp(strtotime('first day of January'));
        $datetime_period_stop = $datetime_period_stop->setTimestamp(strtotime('last day of March'));

    }

    if ($thisMonth>=7 && $thisMonth <=9)
    {
        $datetime_period_start = $datetime_period_start->setTimestamp(strtotime('first day of April'));  
         $datetime_period_stop = $datetime_period_stop->setTimestamp(strtotime('last day of June')); 
    }

    if ($thisMonth>=10 && $thisMonth <=12)
    {
        $datetime_period_start = $datetime_period_start->setTimestamp(strtotime('first day of July'));   
        $datetime_period_stop = $datetime_period_stop->setTimestamp(strtotime('last day of September')); 

    }
}

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...