[spoiler]
Зачем
С точки зрения бизнеса очень важно своевременно получать и анализировать различные данные о делах компании, и руководитель каждой конкретной организации лучше других знает, какие именно показатели важны применительно к его компании. Поэтому мы поставили перед собой цель - помимо предлагаемой нами стандартной отчетности дать возможность руководителям самостоятельно конструировать такие отчеты, какие нужны именно им. В качестве инструмента нами был придуман и разработан "Универсальный конструктор отчетов":
На данном этапе мы разработали и внедрили Конструктор в рамках Задач для Корпоративного портала, но уже в следующем релизе хотим распространить его возможности на такие важные разделы, как CRM, интернет-магазин, Инфоблоки.
В данной статье Конструктор будет рассмотрен с точки зрения разработки - я постараюсь рассказать о внутреннем устройстве модуля, о возможностях его использования и о появлении нового инструмента - конструктора SQL запросов и зарождении ORM.
С чего все началось
Мы задумали написать универсальный конструктор отчетов для любых связанных между собой данных. Первое, что приходит в голову при постановке такой задачи - это опять же "универсальный" построитель SQL запросов, ведь именно формирование запроса к БД является ключевым моментом при формировании отчета.
Были установлены следующие рамки универсальности:
1. Построитель запросов должен уметь самостоятельно объединять данные разных сущностей (JOIN).
2. Должна быть возможность менять представление данных или подставлять их в произвольное выражение (например, сложение числел или конкатенация).
3. Группировка данных - без нее нельзя построить отчеты в привычном понимании этого слова. А там, где группировка, должны быть и варианты агрегации (MIN, AVG, COUNT, ...).
4. Фильтрация на обоих уровнях - до и после группировки (соответственно WHERE и HAVING).
5. Само собой, необходимы сортировка и выборка по частям.
Чтобы построитель запросов мог что-либо строить, он должен обладать информацией о структуре и связанности данных.
Bitrix ORM 0.1
Стоит сразу оговориться, что ни один из описанных ниже форматов и API еще не утвержден, не задокументирован и соответственно не рекомендуется к использованию в разработке. Мы постараемся сделать доступными новые возможности в начале следующего года, а прежде предстоит рефакторинг кода и окончательное утверждение концепции ORM.
ORM начинается с описания сущностей (Entity). Одна сущность - это одна таблица в базе данных, таким образом при создании отчетов для задач понадобилось описание сущностей Tasks, TasksElapsedTime, TasksMember, User, Group (таблица b_sonet_group).
Описание сущности представляет из себя перечисление полей.
class CUserEntity extends CBaseEntity
{
...
'ID' => array(
'data_type' => 'integer'
),
'LOGIN' => array(
'data_type' => 'string'
),
'NAME' => array(
'data_type' => 'string'
),
'LAST_NAME' => array(
'data_type' => 'string'
)
...
} |
Так выглядит описание простейшей сущности со скалярными полями. Имена полей в сущности соответствуют именам полей в таблице b_user.
... 'FULL_NAME' => array( 'data_type' => 'string', 'expr' => array( 'CONCAT(%s, " ", %s)', 'LAST_NAME', 'NAME' ) ) ... |
Поле FULL_NAME - виртуальное, то есть оно не указывает на конкретное поле в базе данных. Значение этого поля вычисляется в процессе выполнения SQL запроса согласно выражению, заданному в параметре expr. Таким образом, в SQL запросе это поле будет представлено как
CONCAT(LAST_NAME, " ", NAME) AS FULL_NAME |
Поля, значения которых опредлеляются выражением, мы называем Expr полями.
Пример посложнее для задач:
... 'DURATION' => array( 'data_type' => 'integer', 'expr' => array( 'SUM(%s)', 'TasksElapsedTime:TASK.MINUTES' ) ) ... |
Поле DURATION так же отсутствует в таблице b_tasks, его значение высчитывается путем суммирования поля MINUTES из сущности TasksElapsedTime (b_tasks_elapsed_time). При этом связь c TasksElapsedTime осуществляется через Reference поле TASK:
class CTasksElapsedTimeEntity extends CBaseEntity
{
...
'TASK_ID' => array(
'data_type' => 'integer'
),
'TASK' => array(
'data_type' => 'Tasks',
'reference' => array('TASK_ID', 'ID')
)
...
} |
Более очевидный пример Reference поля:
...
'RESPONSIBLE' => array(
'data_type' => 'User',
'reference' => array('RESPONSIBLE_ID', 'ID')
)
... |
Поле RESPONSIBLE является указаталем на сущность User и хранит в себе названия полей, за счет которых осуществляется связь между сущностями. Другими словами, это поле дает нам понять, что Задачи и Пользователи связаны следующим образом: Tasks.RESPONSIBLE_ID <=> User.ID.
При иницализации объекта сущности заданный массив с описанием полей превращается в коллекцию объектов классов CEntityField, CEntityExprField и CReferenceEntityField для удобства при дальнейшей работе с ними.
Использование построителя запросов
Построением запросов занимаются объекты сущностей, унаследованные от CBaseEntity. В качестве имени метода взято традиционное GetList.
GetList($select, $filter = array(), $group = array(), $order = array(), $limit = array(), $options = array()) |
Простейший пример вызова - запрос для получения списка отчетов:
CBaseEntity::GetInstance('Report')->getList(
array('ID', 'TITLE', 'CREATED_DATE'),
array('=CREATED_BY' => $USER->GetID())
); |
Выборка полей ID, TITLE, CREATED_DATE из сущности Report (таблица b_report) с фильтром по создателю отчета. Вызов вернет объект класса CDBResult, в котором будут содержаться результаты запроса:
SELECT `report`.`ID`, `report`.`TITLE`, `report`.`CREATED_DATE` FROM b_report `report` WHERE `report`.`CREATED_BY` = 1 |
Что здесь можно отметить:
1. Названия полей и таблиц экранируются в соответствии с используемой БД (MySQL, MSSQL, Oracle).
2. В целях обеспечения уникальности идентификаторов всем таблицам назначаются алиасы, они же прикрепляются ко всем полям.
3. Для $filter обеспечена полная поддержка формата фильтров Инфоблоков.
Следующий пример - выборка задач с информацией об ответственных:
CBaseEntity::GetInstance('Tasks')->getList(
array('ID', 'TITLE', 'RESPONSIBLE.*'), // select
array('=CREATED_BY' => $USER->GetID()), // filter
array(), // group by
array('CREATED_DATE' => 'DESC', 'RESPONSIBLE.SHORT_NAME'), // order by
array('nPageSize' => 10, 'iNumPage' => 1)
); |
В виде SQL:
SELECT `tasks`.`ID`, `tasks`.`TITLE`, `responsible`.`ID` AS `RESPONSIBLE_ID`, `responsible`.`LOGIN` AS `RESPONSIBLE_LOGIN`, `responsible`.`ACTIVE` AS `RESPONSIBLE_ACTIVE`, `responsible`.`NAME` AS `RESPONSIBLE_NAME`, `responsible`.`SECOND_NAME` AS `RESPONSIBLE_SECOND_NAME`, `responsible`.`LAST_NAME` AS `RESPONSIBLE_LAST_NAME`, `responsible`.`WORK_POSITION` AS `RESPONSIBLE_WORK_POSITION`, concat(`responsible`.`LAST_NAME` , ' ' , UPPER(SUBSTR(`responsible`.`NAME`, 1, 1)) , '.') AS `RESPONSIBLE_SHORT_NAME` FROM b_tasks `tasks` LEFT JOIN b_user `responsible` ON `tasks`.`RESPONSIBLE_ID` = `responsible`.`ID` WHERE `tasks`.`CREATED_BY` = 1 ORDER BY `tasks`.`CREATED_DATE` DESC, `RESPONSIBLE_SHORT_NAME` ASC LIMIT 0, 10 |
1. Всем полям, кроме полей исходной Tasks сущности, присваиваются алиасы.
2. Для присоединяемых таблиц алиасы назначаются в соответствии с именем связи (b_user `responsible`).
3. При указании в $select параметра '*' в запросе будут перечислены все поля сущности.
4. В качестве $limit поддерживается нынешний формат для постраничного вывода.
Вот так работает построитель запросов. Пример с GROUP BY и HAVING мы увидим дальше, при рассмотрении конструктора отчетов.
Конструктор отчетов
Теперь давайте рассмотрим, как данный начальный функционал ORM позволил достаточно быстро запустить в работу конструктор отчетов.
За работу конструктора отчетов отвечают модуль report и 3 его компонента:
* report.list - список отчетов
* report.constructor - конструктор отчета (+ редактирование, удаление, копирование отчета)
* report.view - просмотр результирующего отчета
Для инициализации конструктора необходимо передать в компоненты всего два параметра: название начальной сущности и список полей, участвующих в отчете. В случае с задачами параметры получились следующими:
array( 'entityName' => 'Tasks', 'entityFields' => array( 'ID', 'TITLE', 'PRIORITY', 'STATUS' => array( 'IS_NEW', 'IS_OPEN', 'IS_RUNNING', 'IS_FINISHED', 'IS_OVERDUE' ), 'CREATED_DATE', 'START_DATE_PLAN', 'END_DATE_PLAN', 'DATE_START', 'CHANGED_DATE', 'CLOSED_DATE', 'DEADLINE', 'DURATION', 'GROUP' => array( 'ID', 'NAME' ), 'CREATED_BY_USER' => array( 'ID', 'SHORT_NAME', 'NAME', 'LAST_NAME', 'WORK_POSITION' ), 'RESPONSIBLE' => array( 'ID', 'SHORT_NAME', 'NAME', 'LAST_NAME', 'WORK_POSITION' ), 'TasksMember:TASK.USER' => array( 'ID', 'SHORT_NAME', 'NAME', 'LAST_NAME', 'WORK_POSITION' ), ... ) ) |
Всю дальнейшую работу берут на себя компоненты модуля report. В конструкторе появляется дерево с вышеуказанными полями:
В отчет можно включить данные как по самой задаче, так и по связанным с ней элементам: проект, постановщик, ответственный, соисполнители и другие.
Так выглядит запрос на список задач:
В результате получим:
Для большей наглядности, в представлении SQL запрос будет таким:
SELECT `tasks`.`TITLE`, concat(`responsible`.`LAST_NAME` , ' ' , `responsible`.`NAME`) AS `RESPONSIBLE_SHORT_NAME` FROM b_tasks `tasks` LEFT JOIN b_user `responsible` ON `tasks`.`RESPONSIBLE_ID` = `responsible`.`ID` ORDER BY `RESPONSIBLE_SHORT_NAME` ASC |
Более того, выбранные данные можно агрегировать. Например, с помощью следующей настройки можно посчитать количество задач для каждого сотрудника:
SQL:
SELECT COUNT(DISTINCT `tasks`.`ID`) AS `COUNT_DISTINCT_ID`, concat(`responsible`.`LAST_NAME` , ' ' , `responsible`.`NAME`) AS `RESPONSIBLE_SHORT_NAME` FROM b_tasks `tasks` LEFT JOIN b_user `responsible` ON `tasks`.`RESPONSIBLE_ID` = `responsible`.`ID` GROUP BY `responsible`.`LAST_NAME`, `responsible`.`NAME` ORDER BY `RESPONSIBLE_SHORT_NAME` ASC |
Фильтры позволяют применить довольно разнообразные условия выборки. Первая их особенность - это возможность менять фильтр в результирующем отчете. Для этого необходимо отметить фильтр как "изменяемый":
и он отобразится на странице результатов, где можно будет изменить или очистить значение:
Другая особенность - логика фильтров "и" / "или":
В данном примере будет применено одно из условий
WHERE ( `tasks`.`PRIORITY` = 2 ) OR ( `tasks`.`DEADLINE` < '2011-12-21 00:00:00' ) |
И наконец, в фильтре можно построить дерево условий:
Таким образом, к выборке применится условие "(Сотрудник Бандурин и Приоритет Высокий) или (Сотрудник Тихомиров и Приоритет Низкий)". Уровень вложенности условий искуственно ограничен 4-мя уровнями, чтобы не усложнять интерфейс.
Заключение
Итого, для инициализации конструктора отчетов по произвольным данным необходимо:
1. Описать сущности с полями
2. Передать список полей в компоненты модуля report
После утверждения направления развития ORM и рефакторинга уже имеющегося кода мы представим данный функционал всем разработчикам в окончательном виде. Так же, на базе модуля report мы планируем запустить конструктор отчетов для самых разных модулей: CRM, интернет-магазин, инфоблоки и др. С получающимся на данный момент API это будет быстро и совсем несложно.
В данной статье я попытался рассказать, появление какого функционала нас ждет в самое ближайшее время. Возможно, уже на данном этапе у вас возникнут пожелания или замечания.
Ближайшие планы:
- подписка на отчет (автоматическая генерация и регулярная отправка на email или в Живую ленту)
- экспорт данных из отчета в csv
- добавление в конструктор сущности "Департаменты" и новый вид агрегации - аналог GROUP_CONCAT в MySQL
- ajax предпросмотр отчета с реальными данными
Мы будем очень благодарны, если вы поддержите эти и другие идеи на портале идей "" - уже поступила
Ваши голоса и комментарии несомненно повлияют на приоритеты при разработке новых фич. 
Сергей, будет ли конструктор отчетов в релизе 11.5 ? Будет ли возможность его работы в CRM?
CBaseEntity::GetInstance('Tasks')->getList( array('ID', 'TITLE', 'RESPONSIBLE.*'), // select array('=CREATED_BY' => $USER->GetID()), // filter array(), // group by array('CREATED_DATE' => 'DESC', 'RESPONSIBLE.SHORT_NAME'), // order by array('nPageSize' => 10, 'iNumPage' => 1) );CBaseEntity::GetInstance('Tasks')->getList( array('CREATED_DATE' => 'DESC', 'RESPONSIBLE.SHORT_NAME'), // order by array('=CREATED_BY' => $USER->GetID()), // filter array(), // group by array('nPageSize' => 10, 'iNumPage' => 1), // nav array('ID', 'TITLE', 'RESPONSIBLE.*'), // select );Если в новом функционале также будет в хаотичном порядке, то придется каждый раз смотреть документацию по API, перед написанием кода.
К моменту публикации API порядок аргументов будет изменен на привычный всем.
Просто если это так, то, получается, у нас будет возможность привязать Битрикс вообще к любой СУБД.
Было бы хорошо, если бы всё это было унифицировано.
Потребность очевидна, думаю, особых комментариев не требуется, "Отчёт из 1С" в системе сделан для этих же целей - дать возможность пользователям, в первую очередь руководству, просто получать аналитику из других систем оперативно и в привычном интерфейсе, без необходимости изучать другой продукт или отнимать дополнительное время у себя и специалистов для подготовки и печати отчёта из других систем. Тут же вопрос потенциальной экономии средств на пользовательских лицензиях к другим системам, например, если пользователю требуется только аналитика, а политика лицензирования не предусматривает конкурентных лицензий или подобного механизма в продукте, из которого требуется добыть аналитику.
Чтобы хотелось от модуля:
- Права доступа к конкретному отчёту
- Стандартный интерфейс задания параметров отчёта (это я про внешние источники уже)
- Возможность указывать внешний источник и описывать структуры данных для него, либо возможность самостоятельно жёстко задавать требуемый запрос, минуя конструктор запросов, с подстановкой в запрос параметров отчёта (про внешние источники - ещё и вопрос разных СУБД, возможность выбора коннектора, например, для распространённых вариантов типа мускула, мс, оракл)
- Возможность печати отчётов (общее требование вообще, это же отчёт, но иногда оно неочевидно, почему-то)
- Экспорт отчёта (эксель, цсв)
Пока, вроде бы, это всё основное, что хотелось бы спросить.Наличие такого функционала имхо могло бы выглядеть значительным плюсом в корпоративной среде, как дополнительный штатный инструмент.
Т.е. инструмент уже не только для пользователей, которые могут сами сконструировать себе какой-то отчёт в рамках системы, но и инструмент для разработчика, который может по запросу сколотить один/много отчётов, связать их таким образом между собой и отдать готовое на использование пользователям.
Таким образом можно будет генерировать отчёты, позволяющие перейти от "общего к частному", ну например общий отчёт по каким-то суммам, кликаем куда-нибудь в "долги", открывается отчёт по долгам, в который уже переданы ограничения по датам формирования отчёта и тп.
Согласен, результаты работы конструктора вполне себе можно использовать в качестве представлений.
Экспорт отчёта (эксель, цсв)
Про внешние источники спасибо, теперь я понял, о чем речь. Точного ответа у меня пока нет, подумаем на эту тему.
А что по доступу к отчётам? Будет система полномочий а-ля новая система полномочий для инфоблоков?
Хотелось бы компонент, который выводил бы список доступных в системе отчётов (этакий агрегатор отчётов, чтобы можно было организовать доступ ко всем отчётам в одном месте), сгруппированных определённым образом и в соответствии правами доступа к отчётам текущего пользователя, чтобы не видел, чего ему не следует. Ну и соответственно проверка полномочий на доступ к отчёту, при попытке сформировать его.