Поступил от клиента запрос: «xотим конфигуратор отчетов».Чтобы простые сотрудники, далекие от SQL, могли составлять всевозможные, необходимые для них отчеты, по любым данным в системе.
Абстрактно идея понятна. Но вот как же конкретно это должно выглядеть?
Посидев, подумав, изучив что есть на рынке в этом плане, пришло понимание того что эта тема еще далеко не избита и весьма актуальна. Поэтому хочу поделиться примером «конфигуратора отчетов», который получилось реализовать на базе системы «ERP-Платформа».

На мой взгляд он получился идеологически приближенным к идеальному, поэтому в начале статьи постараюсь рассказать концепцию того, каким должен быть конфигуратор отчетов «для людей».
Идеальный конфигуратор отчетов — сочетание простоты и функциональности. Например в 1С функциональный, но неискушенный в программировании 1С человек не сможет им пользоваться. Или в битриксе — простой, но не может делать необходимые вещи.
Идеальный конфигуратор отчетов будет понятен простому пользователю и при этом поддерживает создание отчетов любого уровня сложности.

Сформировались такие требования к конфигуратору отчетов:

1) Простота
Он для НЕпрограммистов. Простой менеджер, далекий от понимания SQL, сделает отчет по интересующим данным.

2) Универсальность
Отчеты можно формировать по всей системе, по любым пользовательским данным.

3) Автоматическое подключение нового
При изменении конфигурации системы, если добавляется новое поле в таблицу, или новая таблица, или устанавливается целый новый модуль, то все изменения автоматически должны быть доступны в конфигураторе.

4) Вложенность
Между таблицами есть связи. Например у задач есть свойства: тип, статус и т.д. Это храниться в специальных справочниках, в основной таблице только ссылки. Т.к. система для НЕпрограммистов, не будем грузить пользователей что такое left join. Пользователь выбрав в поле статус должен получить не число, а статус задачи «в работе» и т.п. Глубина погружения должна быть как минимум на один уровень в связанные таблицы.

5) Поддержка сложных структур
Бывает что отчет сложен, затрагивает много таблиц и даже нельзя обойтись SQL запросом, а нужна серьезная обработка PL/SQL. Для этого в конфигураторе отчетов предусмотрено указание процедуры, которую напишет программист на встроенном редакторе PL/SQL ERP-Платформы.

6) Планировщик и доставка отчета
Часто отчеты формируются на периодической основе. Например пользователь хочет утром увидеть в почте отчеты, ежедневные, еженедельные. ежемесячные и т.п. Нужен планировщик заданий, в котором отчет ставится к автоматическому формированию в заданный момент.
Помимо планировщика нужна система доставки отчета. Это не обязательно письмо. В зависимости от настроек пользователя может быть уведомление в интерфейсе, отправка в telegram, отправка по email и т.п. Доставка по всем каналах коммуникации с пользователем.

7) Диаграммы
Формирование диаграмм по данным отчета

8) Вывод результата
Выводить результат отчета в различных форматы, например Excel или PDF.

Что есть в мире

Начали с изучения того что в мире есть. Изучение показало что все печально.
Сначала была 1С. Да, там все круто, можно сделать почти все что угодно, но где же тут п.1, «для простых людей»? Там черт ногу сломит пока разберется. У менеджера, который в первый раз в жизни это увидит, мозг просто расплавиться. В общем круто — но надо проще и интуитивней.
В популярных crm системах все печально. Толком ничего нет. Единственное что привлекло внимание, конфигуратор отчетов в Битриксе. В целом подходит под определение «для людей» и по началу даже понравился, но в качестве наглядного примера опишу несколько недостатков:

1) Узкость системы: отчеты привязаны только к определенным таблицам с главной функциональностью: «Задачи», «Товары»,«Сделки» и т.п. Всего 6 штук! А вот что делать если хочу проанализировать сколько % рабочего времени в графиках сотрудников занимают задачи такого-то типа, или хочу получить список клиентов у которых не заполнен email в контактах. Чтобы делать отчеты по данным из установленных приложений нет и речи.

2) Условия можно строить только на глубину до 4 вложенности. Неужели было сложно сделать неограниченное?

3) Сортировать можно только по одному из полей. А если надо отсортировать по типу задачи, а внутри типа по дате по убыванию?

4) Отчеты все списком в столбик по алфавиту. Делятся опять же по таблицам «Задачи», «Товары»,… 6 штук. А если мне нужна своя группа, или подгруппа отчетов. Как группировать отчеты по тематике?

5) Нет планировщика для запуска с периодичностью. (я по крайней мере не нашел). При этом в задачах он есть.

В общем система или универсальная но очень сложна, либо для людей — но слишком узка, либо вообще ничего нет.

Поэтому идем своим путем.

Дерево

Для начала решено было организовать построение отчетов в виде древовидной структуры.

Любой пользователь может создать свою ветку-узел (можно создавать вложенные ветки, тут нет ограничений), или добавлять свой отчет в другие доступные ему ветки.
При наведении мышки на отчет всплывает описание отчета.
Загромождать статью принтскринами из системы управления деревом не буду. Можно посмотреть здесь.

Во вторых нужны права доступа. Ведь не хорошо чтобы все видели всё. Есть общие отчеты, а есть те, которые могут видеть отдельные сотрудники или группы сотрудников, в том числе это могут быть целые ветви отчетов.
Тут благом оказалась штатная система прав доступа в ERP-Платформе. Она имеет структуризацию прав доступа вплоть до отдельных элементов страницы и в нее довольно просто получилось встроить систему отчетов. А после еще и прав доступа к процедурам для отчетов, но об этом ниже.

Настройка глобальной структуры

Далее надо выстроить структуру связей в системе. Система должна знать у каких полей есть зависимости с другими таблицами (пример задачи и ее статуса).
Эта настройка выполняется один раз разработчиком конфигурации.

По сути необходимо воссоздать Foreign key (про контроль целостности тут не говорю, только связи). Структура ERP-Платформы позволила довольно легко это сделать. В штатный редактор таблиц добавлены необходимые поля:

В таблице ставится галочка, что она будет видна в конфигураторе отчетов.
У каждого поля ставится галочка, будет ли оно видно конфигураторе, т.к. не все поля имеет смысл выводить в отчетах, а лишний мусор в списке не к чему.

Если поле является идентификатором из универсального справочника, то можно указать номер универсального справочника (о нем чуть далее). Выбрать поле другой таблицы, с которым связано это поле.
Подробным описанием этого редактора тоже не буду загромождать статью, почитать можно здесь.

Работу по настройке этих связей делает разработчик конфигурации. В конфигурации по умолчанию это уже сделано. При этом и любой пользователь с необходимыми правами доступа, может зайти в редактор таблиц и сделать необходимые манипуляции.
Если добавляется новое поле и оно будет нужно в отчетах, надо щелкнуть на галочку использования этого поля в отчетах.
Теперь конфигуратор отчетов будет знать связи и выводить при выборе поля пользователю картинку типа такой:

Тут мы подходим к п.3 наших требований. При установке в системе нового модуля, его данные и структура должны автоматически попасть в отчет.
Здесь от разработчика модуля требуется во время разработки расставить эти галочки. А система уже подхватит их при установке.

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

Универсальный справочник

Теперь сделаем необходимое отступление. Что такое «Универсальный справочник»? который я упоминал чуть выше, и которому выделен целый столбец при редактировании полей таблицы.
Для краткости будем называть его УС.

В первую очередь УС — способ существенно снизить трудозатраты на разработку.
Замечено, что большинство справочников состоят из 4 полей:
1) ID параметра
2) Название параметра
3) Числовое значение параметра
4) Текстовое значение параметра

Например. Справочник Валюта:

Или справочник Статус задачи:

И еще можно привести сотню примеров.

Они состоят из одинакового набора полей одного типа. Т.е. можно для всех таких справочников использовать один редактор, одну процедуру для запроса данных и т.д.
Не надо каждый раз, когда понадобился справочник, рисовать интерфейсы, делать ссылки, писать запросы и выполнять т.п. муторную работу.
В общем УС — мегаудобно.

Теперь вернемся назад с новым пониманием по УС и рассмотрим редактор полей в конфигураторе отчетов.

Он должен иметь такую функциональность:
1) Выбирать поля таблицы, их группировать и сортировать.
2) Составлять условия.
3) Предварительный просмотр результата.

В целом редактор получился такой

Поля (столбцы)

Сначала пользователь выбирает «Cправочник» — это таблица, за которой стоит функциональный модуль системы. Потом просто добавляет необходимые поля. Поля можно переименовывать, менять их порядок вывода. Все наглядно и интуитивно понятно.

Вторым полем идет Универсальный справочник. Если в системе с этим полем связан конкретный справочник, то пользователь может указать Н — название, 1 — первый параметр, 2 — второй параметр. И система при формировании отчета, например в поле Статус задачи, вместо цифры 92, покажет текст «В работе» если выбрано Н и т.д. Все очень просто.

Группировка

Так же просто делается группировка. Если нужно просуммировать значение какого-то поля, выбирается SUM в поле Агрегатные. При этом система на все остальные поля сама поставит group by при формировании запроса.

Естественно для разных типов данных в списке должны выводятся разные функции. Например в типе данных varchar нет смысла выводить SUM.

Сортировка

Сортировка полей тоже простая и универсальная. В столбце Сортировка выбирается «1» в поле по которому произойдет сортировка, если необходимо отсортировать по еще одному полю напротив него выбирается «2», если в обратном порядке «2 desc» и т.д. Все очень просто.

Большое преимущество данного подхода: всегда в доступности полный пул полей и мы можем указать сортировку хоть по всем полям включительно.

Условия

Все состоит и контейнеров с условиями. Контейнер — то что в скобках. Условие — операция сравнения А и Б (А>Б, А=Б, А<=Б…).
Например: (<условие 1> И <условие 2> И <условие 3>) — является контейнером И, соответственно условие (<условие 1> ИЛИ <условие 2> ИЛИ <условие 3>) является контейнером ИЛИ. И вот из такой конфигурации И/ИЛИ и условий внутри можно составлять сколь угодно сложные вереницы условий.

Например если необходимо выбрать данные отвечающие <условие 1> и одному из условий 2 или 3. То необходимо условия 2 и 3 объединить в конейтнер ИЛИ.
(<условие 1> И (<условие 2> ИЛИ <условие 3>))
Здесь в контейнере И находятся <условие 1> и контейнер <ИЛИ>, а внутри контейнера ИЛИ условие 2 и условие 3. Например: ( (A>Б) И ( (A>C) ИЛИ (A=В) ) )
Для пользователя в интерфейсе это будет выглядеть так:

Но мало просто создать редактор расстановки условий. Условие может быть константой или переменной. Переменное условие определяется просто — оно не заполнено в конфигураторе. Если значение условия пусто — то надо спросить его у пользователя перед формированием отчета. Если пользователь ничего не введет — ставить null.
Чтобы поле ввода выглядело информативно нужно еще такое свойство как название. Название может совпадать с именем поля, а может и нет.
Например, если мы ввели константу в поле «Дата», но оставили пустым поле «Статус»,

то система должна спросить «Статус» у пользователя перед формированием отчета.

В общем: значение есть — константа, пусто — спрашивать. Все тут тоже просто.

Опять же поле может быть связано с УС. В этом случае можно указать какое из полей УС выводить и как в примере выше, вместо числового поля «Статус» будет выведен красивый список выбора.

Необходимости как в Битриксе ограничивать 4-мя уровнями вложенности не было. Условия можно ветвить бесконечно.

Процедуры

Конфигуратор отчетов создан для НЕпрограммистов и знание SQL для составления отчета не обязательно. Но в случае сложного отчета, данные которого находятся во многих разных таблицах со сложными связями, программиста придется привлечь.
Такие вещи можно сделать написав процедуру в стандартном PL/SQL конфигураторе, входящем в базовую систему программирования ERP-Платформы, и указав данную процедуру в отчете. В процедуре можно сделать любую PL/SQL конфигурацию, т.е. фактически все что угодно.

В тему программирования конфигурации в ERP-Платформе углубляться не буду, т.к. эта тема выходит далеко за рамки данной статьи. Вкратце — состоит из 2 частей: программирование веб интерфейса и программирование базы данных, ну и соответственно связи между ними. В части программирования базы данных можно создавать таблицы, триггеры, процедуры любой сложности. В рамках конфигуратора отчетов нас здесь как раз интересует часть системы по созданию процедур. Там реализован полноценный PL/SQL (и даже несколько фишек которых нет нигде, например тип данных image, или операторы API в триггерах, позволяющие прямо по событию в БД передавать данные во внешние системы)

Но не все так просто.
Программирование в системе доступно администраторам системы, или лицам, которым эти права дали администраторы.
Конфигуратор отчета доступен всем, и нельзя дать любому пользователю включить в отчет любую процедуру.
Поэтому доступ в отчетах к процедурам опять же пришлось интегрировать в штатную систему назначения прав в ERP-Платформе.
Для использования процедуры в отчетной системе, необходимо прописать права на нее, в Роли пользователя, создающего отчет.

После того как процедура стала доступна пользователю он её может указать в отчете.
Добавлять поля можно из полей процедуры. Агрегатные функции и функции сортировки доступны.
А вот условия здесь — входные параметры процедуры. Они нужны все и порядок нельзя менять. Поэтому тут редактирование ограничено. Можно только задать константу. Но менять порядок, удалить, добавить новые — не получится.

Пример использования процедуры в отчете в ERP-Платформе

Под спойлером приведу пример, если кому-то будет интересно как выглядит использование процедур в отчете в нашем редакторе.
Вот например процедура «ЗАЯВКА_Список», формирующая список заявок по условиям в интерфейсе списка заявок:

Если не заполнено ни одно из условий, т.е. все null процедура выдаст список заявок со статусом «поступила».
Если ее указать в отчете, и выбрать аналогичные поля, как в интерфейсе со списком заяовк

То мы получим аналогичный результат в отчете

Вот в догонку еще код этой процедуры в PL/SQL интерфейсе ERP-Платформы. Но это уже очень глубоко выходит за рамки данной статьи и не предназначено для простых пользователей. Тут просто в качестве примера, что такие вещи можно делать.
Вообще вся конфигурация ERP-Платформы написана на её же встроенном языке, соответственно все это доступно в конфигураторе отчетов, а так же сами пользователи могут что угодно редактировать и модернизировать.

Процедура ЗАЯВКА_Список

Запуск по расписанию

Автоматическое получение периодических отчетов очень важная функция. На моей практике было создано очень много таких отчетов.

В ERP-Платформе есть штатный планировщик заданий, для запуска процедур по расписанию, программируемый в целом аналогично крону. В него получилось встроить и постановку запуска отчетов. Т.е. опять же заюзать штатную функцию системы и не писать новое. Но оказалось не все так просто.

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

Было решено не ограничивать пользователя в возможностях получения отчета. Помимо email, можно получить отчет в веб интерфейсе аккаунта в системе уведомлений, или на телефон в telegram. В общем заюзать для этого штатную систему уведомлений ERP-Платформы. Но встал вопрос — а как передать тело отчета, скажем в telegram например. Да, там можно слать форматированные сообщения, но отчеты бывают разные… это может быть еще та простыня. В веб интерфейсе проблема аналогичная. Можно конечно ввести blob поле к уведомлению, ибо мы не знаем размер отчета, но опять же это был бы костыль, который надо программировать индивидуально, а хотелось одним кодом убить всех зайцев. Сделать универсальную систему доставки отчета, чтобы работала одинаково во всех каналах доставки информации без доработок. Ибо вот появится новый канал и для него опять что-то надо будет писать индивидуально.

Решение было найдено следующее. Скрипт, который запускается по расписанию и получает данные отчета, делает pdf файл с отчетом и кладет его на диск в аккаунте клиента. Потом через штатную систему уведомлений производит рассылку со ссылкой на этот файл. Т.е. грубо говоря пришло уведомление, отчет такой-то, и ссылка. Пользователь кликает на ссылку и ему открывается файл.

Pdf формат универсальный, откроется везде и на телефоне и на компьютере.

Ок, средство доставки есть. Теперь надо сформировать адресатов.

Настройка получателей

Получателей может быть 3 вида:
1) Сотрудник (конкретное лицо)
2) Подразделение (отдел согласно штатному расписанию)
3) Группа (рабочие группы сотрудников)

Соответственно могут быть любые сочетания этих видов. Заполняется эта информация в конфигураторе отчетов в самом отчете.

Скрипт формирования отчета на основании этих данных вычислит набор уникальных получателей и проштампует им ссылки на отчет в штатную систему уведомлений.

Далее рассылка уже будет зависеть от индивидуальных настроек пользователей. Минимум — придет в веб интерфейс. Далее если стоит в контактах сотрудника email с типом «для уведомлений» — уйдет туда. Если у сотрудника подключен телеграм, то уйдет и туда.

Но опять же, не факт что отчет должны видеть все, а он лежит на диске. Тут проблема решается просто. Каждая папка на диске имеет систему ограничения прав доступа. Права к папке Отчеты по умолчанию урезаны совсем, даже не чтение. Т.е. ее содержимого никто не сможет посмотреть, но ссылки при этом будут открываться. Если кому-то вдруг понадобится просмотр содержимого этой папки, администратор должен Роли пользователя индивидуально прописать права на нее. Так что проблем с безопасностью тут нет. Работает принцип как в youtube «доступ по ссылке». В списке ничего не увидишь, но посмотреть видео можно зная ссылку.

Графическая часть

На самом деле диаграммы довольно редко востребованы. У меня на практике практически не встречалось требований рисовать диаграммы. Но такая возможность должна быть. В каких-то ситуациях это может быть большим плюсом.

В нашем конфигураторе на текущий момент предусмотрели 3 виде диаграмм:
1) Линейная
2) Столбчатая
3) Круговая
4)… и в будущем добавлять другие виды по мере фактической потребности.

И интерфейс к ним. Он получился очень простой

Принцип такой. Стоит хоть одна галочка — выводить диаграмму, везде пусто — не выводить.
Соответственно если указано Х — то это будут названия делений по ости Х.
В линейной и столбчатой просто галочками указываются по данным каких полей строить линии и столбцы.
В круговой это можно делать только по одному из столбцов.

Тут в принципе все понятно и углубляться не будем. Подробнее можно посмотреть здесь.

Форматы вывода данных

Это тоже важный аспект. Как минимум пользователь должен иметь возможность распечатать отчет одним щелчком. Как максимум получить его в виде файла PDF или выгрузить в Excel для дальнейшей обработки.
Все эти варианты конечно предусмотрены и тут в принципе тоже все понятно и ничего нового.

Послесловие

Хотелось бы подвести итоговую мысль под тем, каким должен быть идеальный конфигуратор отчетов.

1) Он не должен гемороить пользователей. Но простота и функциональность вещи противоречивые. У функциональной вещи сложнее эксплуатация. Может даже потребоваться специальное обучение.
Тут можно пойти путем матрешки. На верхнем слое сделать простой, дружелюбный интерфейс, закрывающий большой пул потребностей. При встрече сложной потребности, должна быть дверь во внутренние слои с функционалом, необходимым для её решения.
Так и строили. Внешне — простой интерфейс, но если встретилось сложное: вот редактор PL/SQL — делайте что угодно и подключайте это в виде готового блока в простой интерфейс отчета.

2) Конфигуратор отчетов не должен быть статичным, а расширяться автоматически вместе с расширением системы. Надо делать так, чтобы при расширении системы код самого конфигуратора отчетов не надо было преписывать, а он автоматически подхватывал все изменения.
В системах с возможностью конфигурирования пользователем, например в 1С или в ERP-Плаформе, такой подход является единственным выходм, т.к. какая конфигурация будет у пользователя заранее не известно, и за всеми не угонишься подгонять конфигуратор отчетов под каждую конфигурацию системы.

3) Конфигуратор отчетов должен хорошо выполнять задачу по составлению отчетов, а не заниматься смежными вещами.

Тут концепция аналогична принципу маленьких «острых» утилит в unix подобных системах. Утилита должна уметь блестяще справляться со своей задачей, а не уметь все по чуть чуть. Смежные задачи передавать утилитам блестяще их выполняющим и просто получать от них результат.

Это правило относится полностью к системе. Например, правами доступа к отчетам должен заниматься штатный модуль прав доступа, и все что касается прав доступа — он умеет делать хорошо, и не важно где в системе.
Не зачем в отчетах строить свою систему прав доступа — это костыль, который никогда не будет таким хорошим как модуль, под это заточенный.

Аналогично с планировщиком. Не зачем строить в конфигураторе отчетов запуск заданий по расписанию, лучше поручить это системе, которая умеет это делать хорошо.

Незачем реализовывать редактор для сложных запросов в конфигуаторе отчетов. Это переусложнит интерфейс и отпугнет простых пользователей. Лучше для этих задач заюзать стандартный редактор процедур, который хорошо умеет конфигурировать сложные запросы.

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

Аналогичные требования и к конфигуратору — он должен максимально хорошо справляться со своими прямыми задачами и уметь дружить с остальными модулями системы.

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

Автор:

Источник