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

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


    SpravkaCRM.ru

    Сегодня столкнулся с ошибкой, присутствовавшей в 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

    Sign in to follow this  


    User Feedback

    Recommended Comments

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

    до.png

    Share this comment


    Link to comment
    Share on other sites
    В 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
     * 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. Я сейчас этот багфикс в виде патча оформляю. Как доделаю - выложу...

    Share this comment


    Link to comment
    Share on other sites

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

    Share this comment


    Link to comment
    Share on other sites
    В 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
     * 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. Я сейчас этот багфикс в виде патча оформляю. Как доделаю - выложу...

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

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

    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')); 

        }
    }

    Share this comment


    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
    Add a comment...

    ×   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.


×
×
  • Create New...