ПРОЕКТИРОВАНИЕ И РАЗРАБОТКА БИЛЛИНГА ДЛЯ УЧЕТНЫХ СИСТЕМ КОММУНАЛЬНЫХ УСЛУГ

АННОТАЦИЯ

Данная статья описывает аспекты проектирования механизмов биллинга для учетных систем на примере Расчетного Центра Коммунальных Услуг. Предлагается описание реализации матрицы тарифов на коммунальные услуги и биллинга их потребителей в учетной системе. Описываются алгоритмы выполнения основных процедур биллинга в контексте предлагаемого решения. Кратко затрагивается вопрос оптимизации производительности базы данных расчетного центра путем декларативного секционирования таблиц.

ABSTRACT

This article describes the design aspects of billing mechanisms for accounting systems by means of an example of the Utilities Settlement Center. A description of the tariff matrix implementation of the public utility services and billing of their consumers in the accounting system is proposed. Algorithms for performing basic billing procedures in the scope of the proposed solution are described. The issue on database performance optimization of the Settlement Center through declarative partitioning was briefly raised.

Ключевые слова: базы данных, PostgreSQL, секционирование таблиц, учетные системы, биллинг, тарифные ставки.

Keywords: databases, PostgreSQL, tables partitioning, accounting systems, billing, rates.

Введение

Известно, что любые учетные системы представляют собой совокупность объектов учета, их взаимосвязей и механизмов ведения учета. Все объекты учета образуют сущности данных, имплементируемые в реляционной базе данных (БД) учетной системы в виде связанных таблиц. Сами же механизмы учета представляют собой ряд регулярно выполняемых процедур, алгоритмы которых определяются требованиями автоматизации бизнес-процессов предприятия. Имплементация механизмов учета, как правило, выполняется в программном коде модулей учетной системы, а также в хранимых процедурах и функциях ее БД [8]. Работа этих механизмов направлена на управление балансом счетов субъектов, потребляющих некоторые платные услуги. Основными операциями, выполняемыми в процессе ведения учета, являются дебетные и кредитные операции [4], такие как  пополнение баланса и списание средств с баланса за оказанные\потребленные услуги или выставление счетов на оплату и фиксация принимаемых платежей. Совокупность данных процедур в учетных системах также принято называть биллингом [2]. Для функционирования механизмов биллинга в БД учетной системы необходимо наличие имплементированных сущностей данных, используемых как в качестве вспомогательных источников расчетных данных, так и для хранения результатов выполненных расчетов.

Существующие на данный момент на рынке Республики Казахстан крупные учетные и биллинговые информационные системы, в контексте рассматриваемой темы, в большинстве своем, имеют ряд существенных недостатков, таких как:

• Избыточность данных из-за недостаточной проработки структуры БД и механизмов ведения учета. Есть примеры систем, где, при закрытии предыдущего учетного периода, БД учетной системы полностью переходит в режим архива, а для нового учетного периода создается новая БД, в которую заново копируются данные всех таблиц справочников, объектов учета и их взаимосвязей, а также вся бизнес-логика БД: хранимые процедуры, функции и триггеры [1]. Пустыми остаются только таблицы, имплементирующие некоторые сущности биллинговой подсистемы;
• Необходимость приостановления работы внешних служб учетной системы, таких как платежные сервисы и ограничения доступа пользователей\операторов учетной системы во время операций по закрытию учетного периода, выставление счетов на оплату и т.п.

В данной статье вниманию читателя предлагаются авторские решения по реализации механизмов биллинга, лишенных указанных выше недостатков, на примере Расчетного Центра Коммунальных Услуг (РЦКУ). Данные решения построены с учетом функциональных возможностей, предоставляемых СУБД PostgreSQL 11 [5] или более новой, на основе классической схемы Оборотно-сальдовой Ведомости (ОСВ) [3] с возможностью расширения количества атрибутов учета и применением механизмов пооперационного учета, а также хронологической модели данных матрицы тарифов. Предлагаемые решения могут быть легко адаптированы для реализации проектов с самыми разными бизнес-требованиями – от простого сайта с платными услугами до уже упомянутых выше РЦКУ. Материал статьи будет полезен архитекторам и разработчикам биллинговых систем самого разного назначения.

Устройство матрицы тарифов

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

Схема данных, предлагаемая для организации матрицы тарифов на услуги в учетной системе РЦКУ, представлена на Рисунке 1. Для простоты восприятия структуры этой матрицы из схемы, изображенной на Рисунке 1, намеренно исключены таблицы сущностей учетной системы, являющихся объектами учета. Однако, подразумевается, что таблицы, имплементирующие эти сущности, существуют в БД учетной системы:

• справочник потребителей;
• справочник ПКУ;
• справочник услуг, оказываемых ПКУ.

Рисунок 1. «Схема данных матрицы тарифов»

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

rate_group – справочник тарифных групп. Хранит наименования тарифных групп. Некоторые ПКУ могут применять дифференцированную тарификацию своих услуг для разных слоев потребителей. Например: «Основной тариф», «Льготный тариф», «Пенсионный» и т.п.

rate – хранит историю изменений тарифов для услуг ПКУ. Определяет для какой услуги ПКУ, на какой тарифной группе и с какой даты действует указанное значение тарифа. Назначение полей:

• provider_id – идентификатор ПКУ. Внешний ключ к таблице-справочнику ПКУ;
• service_id – идентификатор услуги ПКУ. Внешний ключ к таблице-справочнику услуг ПКУ;
• rate_group_id – идентификатор тарифной группы. Внешний ключ к таблице-справочнику тарифных групп. Для ПКУ, не применяющих дифференциацию тарифов на свои услуги по группам, в данной схеме следует применять идентификатор группы «Основной тариф»;
• in_effect_since – дата вступления значения тарифа в силу;
• rate_value – значение тарифа.

consumer_rate_groups – хранит историю изменений тарифных групп для услуг потребителя. Связывает услуги потребителя с тарифными группами. Определяет у какого потребителя, для каких услуг ПКУ и с какой даты назначена тарифная группа. Назначение полей:

• customer_id – идентификатор потребителя. Внешний ключ к таблице-справочнику потребителей;
• provider_id – идентификатор ПКУ. Внешний ключ к таблице-справочнику ПКУ;
• service_id – идентификатор услуги ПКУ. Внешний ключ к таблице-справочнику услуг ПКУ;
• rate_group_id – идентификатор тарифной группы. Внешний ключ к таблице-справочнику тарифных групп. Для ПКУ, не применяющих дифференциацию тарифов на свои услуги по группам, в данной схеме следует применять идентификатор группы «Основной тариф»;
• in_effect_since – дата вступления назначенной тарифной группы в силу.

Практика применения дат наступления действия создаваемых записей позволяет определять начало действия таких записей с планированием их наступления на будущее. Также данная схема позволяет механизмам биллинга выполнять перерасчет начислений потребителям, когда смена их тарифной группы или значения тарифа были выполнены датой одного из закрытых (прошедших) расчетных периодов.

Устройство механизма биллинга учетной системы

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

Также следует учесть, что в процессе ведения учета коммунальных услуг в качестве учетного периода принято использовать календарный месяц. Это означает, что учетные системы ПКУ и РЦКУ выполняют выставление счетов на оплату своим абонентам за оказанные\потребленные услуги, а также принимают и фиксируют информацию о совершаемых ими платежах, помесячно. Такая практика, помимо прочих очевидных удобств, позволяет предотвратить нарушения целостности учета путем прекращения доступа на внесение изменений в учетные данные прошлых учетных периодов. С учетом этого, при имплементации сущностей данных биллинга в структуре БД, целесообразно применить механизм декларативного секционирования таблиц [5, с. 164-169], предлагаемый функционалом СУБД PostgreSQL. Это позволит получить существенный прирост быстродействия подсистемы биллинга учетной системы и облегчит перенос данных за прошлые учетные периоды в архивную БД в будущем. Наиболее целесообразным и эффективным будет применение секционирования таблиц биллинга по учетным периодам – то есть каждый месяц новая секция для каждой такой таблицы.

На основании перечисленных особенностей работы учетных систем ПКУ и РЦКУ для имплементации сущностей биллинговой подсистемы предлагается схема данных, изображенная на Рисунке 2. Также, как и в схеме на Рисунке 1, для простоты ее восприятия, намеренно исключены таблицы сущностей учетной системы, являющихся объектами учета.

Рисунок 2. «Схема данных биллинга учетной системы с применением секционирования таблиц»

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

billing – ОСВ учетной системы. Секционируемая таблица (секционирование по полям y, m). Каждая секция таблицы хранит данные о балансе счетов по услугам потребителей в указанном учетном периоде. Назначение полей:

• customer_id – идентификатор потребителя. Внешний ключ к таблице-справочнику потребителей. Часть первичного ключа;
• provider_id – идентификатор ПКУ. Внешний ключ к таблице-справочнику ПКУ. Часть первичного ключа;
• service_id – идентификатор услуги ПКУ. Внешний ключ к таблице-справочнику услуг ПКУ. Часть первичного ключа;
• y – учетный год. Часть первичного ключа;
• m – учетный месяц. Часть первичного ключа;
• opening_balance – значение начального сальдо\входящий остаток;
• rate_value – значение тарифа для указанной услуги ПКУ в текущем отчетном периоде для указанного потребителя;
• calc_value – сумма начислений\сумма выставленного к оплате счета;
• recalc_value – сумма перерасчета. Заполняется при выполнении операций перерасчета, в остальных случаях равно нулю;
• pay_value – сумма поступивших платежей за учетный период;
• closing_balance – значение конечного сальдо\исходящий остаток.

Для таблицы billing необходимо создать триггер BeforeUpdate, триггерная функция которого будет выполнять пересчет значения конечного сальдо у обновляемой строки. Например, для указанного состава полей строка кода, выполняющая расчет значения конечного сальдо, будет выглядеть вот так:

closing_balance = opening_balance + calc_value + recalc_value – pay_value

Таким образом, положительное значение сальдо отображает задолженность потребителя, а отрицательное – переплату.

Кроме этого, для предотвращения нарушений целостности учета, необходимо запретить вставку новых и правку существующих записей таблицы billing в прошедших\закрытых учетных периодах. Для этого необходимо создать триггер BeforeInsert, триггерная функция которого будет запрещать вставку данных в секции таблицы billing, хранящие данные за прошлые\закрытые учетные периоды. Триггерная функция триггера BeforeUpdate, кроме описанного выше, также должна запрещать правку записей в секциях, хранящих данные за прошедшие\закрытые учетные периоды, а также запрещать правку значений полей opening_balance и rate_value в секции текущего учетного периода.

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

• id – идентификатор типа операции. Первичный ключ;
• name – наименование операции;
• descr – краткое описание операции;
• billing_field_name – имя поля в таблице billing (атрибут учета), на значение которого будут оказывать влияние операции данного типа;
• subtraction_sign – знак операции: false для операций сложения, true – для операций вычитания.

Пример заполнения справочника billing_operation_type показан в Таблице 1.

billing_operation – журнал операций биллинга учетной системы. Секционируемая таблица (секционирование по полям y, m). Каждая секция таблицы хранит историю выполнения операций биллинга в учетной системе в указанном учетном периоде. Назначение полей:

• id – уникальный идентификатор операции. Первичный ключ;
• customer_id – идентификатор потребителя. Внешний ключ к таблице-справочнику потребителей;
• provider_id – идентификатор ПКУ. Внешний ключ к таблице-справочнику ПКУ;
• service_id – идентификатор услуги провайдера. Внешний ключ к таблице-справочнику услуг ПКУ;
• y – учетный год;
• m – учетный месяц;
• billing_operation_type – идентификатор типа операции;
• dt – дата и время создания записи;
• operation_value – значение\сумма операции;
• user_id – идентификатор пользователя\оператора учетной системы, создавшего запись;
• descr – примечание пользователя\оператора учетной системы для данной операции. Необязательный параметр.

В целях повышения быстродействия запросов на выборку данных в таблицу billing_operation необходимо добавить индексы для следующих полей:

• customer_id;
• customer_id, provider_id и service_id.

Также для таблицы billing_operation необходимо создать следующие триггеры:

• BeforeInsert – триггерная функция должна запрещать вставку записей в таблицу billing_operation в прошедшие\закрытые учетные периоды;
• BeforeUpdate – триггерная функция должна предотвращать правку данных во всех секциях таблицы billing_operation;
• AfterInsert – триггерная функция которого должна выполнять обновление значение поля, имя которого соответствует типу добавленной операции (значение поля billing_field_name справочника billing_operation_type) у соответствующей записи в таблице billing (соответствие искомой записи для обновления по значению полей customer_id, provider_id, service_id, y и m).

Данные меры также применены для предотвращения нарушений целостности учета.

Таблица 1.

«Пример заполнения справочника billing_operation_type»

При заполнении справочника billing_operation_type следует учесть, что имена полей opening_balance, rate_value и closing_balance таблицы billing не могут использоваться в качестве значения billing_field_name так как не являются атрибутами учета в контексте предлагаемой схемы:

• opening_balance и rate_value – заполняются при открытии нового учетного периода и остаются неизменными (см. раздел «Алгоритмы основных процедур биллинга» ниже), кроме того, их изменение запрещено функцией триггера BeforeUpdate таблицы billing (см. выше);
• closing_balance – калькулируется триггерной функцией BeforeUpdate таблицы billing.

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

• Добавить необходимые поля в структуру таблицы billing и внести необходимые изменения в функцию ее триггера BeforeUpdate в соответствии с требованиями бизнес-процессов объекта автоматизации;
• Добавить необходимые записи в справочник billing_operation_type;

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

• Операции нефинансового учета. Например, фиксация показаний приборов учета потребителя (счетчики воды, газа и электроэнергии);
• Операции замещения сальдо, замещения начислений и многое другое.

Алгоритмы основных процедур биллинга

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

Выполнение начислений. Выставление счетов на оплату.

• Получить список всех услуг по всем абонентам с текущими значениями тарифов и количеством потребленных услуг;
• Выполнить вставку полученных данных в таблицу billing_operation в текущем учетном периоде, указав в качестве типа операции «Начисление», а в качестве значения для поля operation_value произведение значения тарифа и количества потребленных услуг. Остальную работу по расчетам выполнят функции триггеров таблиц billing_operation и billing.

Учет платежей.

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

1. Платежный сервис РЦКУ, принимая запрос на оплату от внешнего источника, сохраняет их в свою БД со статусом «Принят»;
2. После сохранения данных принятого платежа в БД и получения идентификатора созданной записи платежный сервис отправляет ответ источнику запроса передав полученный идентификатор и статус успешного принятия платежа в обработку. Далее отправляет запрос модулю обработки платежей, передав идентификатор платежа, который необходимо обработать;
3. Приняв запрос с идентификатором платежа, модуль обработки платежей получает данные платежа из БД платежного сервиса и выполняет их разноску в биллинг учетной системы путем добавления записей в таблицу billing_operation с указанием одного из типов операции «Оплата»;
4. После успешной разноски данных в биллинг модуль обработки платежей изменяет статус платежа в БД платежного сервиса на «Разнесен»;
5. При запуске модуля обработки платежей, до запуска его web-сервиса, необходимо выполнить запрос к БД платежного сервиса для получения и выполнения обработки всех платежей со статусом «Принят».

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

Закрытие учетного периода и открытие нового.

Помимо механизмов предотвращения нарушения целостности учета, описанных выше, существует ряд процедур, необходимых биллингу учетной системы для полноценного функционирования в рамках учетных периодов.

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

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

1. За 5 или 10 минут до наступления полуночи последнего дня месяца создать новые секции для всех секционированных таблиц в БД учетной системы;
2. Ровно в полночь даты наступления нового месяца остановить работу модуля обработки платежей (см. «Учет платежей» выше). При этом платежный сервис РЦКУ продолжит свою работу и будет принимать запросы на оплату;
3. Выполнить запрос к БД учетной системы на получение всех услуг всех потребителей со значениями их конечного сальдо и актуальных тарифов;
4. Выполнить вставку полученного блока данных в таблицу billing, записав значения конечного сальдо в поля opening_balance и closing_balance, а в поля y и m – год и месяц нового учетного периода. В остальные поля таблицы billing, являющиеся атрибутами учета, следует записать 0;
5. Выполнить запуск модуля обработки платежей. При этом все платежи, принятые платежным сервисом за время выполнения процедуры открытия нового отчетного периода, будут разнесены в биллинг.

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

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

Заключение

Предложенные в данной статье решения позволят архитекторам и разработчикам учетных систем строить эффективные, высокодоступные и компактные, с точки зрения скорости прироста объема БД,  биллинговые системы, которые могут быть легко адаптированы под самые разные бизнес-требования. Представленные схемы моделей данных имеют минимальную избыточность, а применение составных первичных ключей в таблицах является компромиссом между Нормальной Формой [6] и производительностью БД учетной системы.

Список литературы:

1. Базы данных. Вводный курс. Операции обновления баз данных и механизм триггеров. / [Электронный ресурс]. URL:  http://citforum.ru/database/advanced_intro/75.shtml (дата обращения: 10.12.2021).
2. Биллиниг. / [Электронный ресурс]. URL: https://ru.wikipedia.org/wiki/Биллинг (дата обращения: 10.12.2021).
3. Ведомость оборотная // Большая советская энциклопедия : в 66 т. (65 т. и 1 доп.). – М.: Советская энциклопедия, 1926—1947.
4. Дебет и кредит. / [Электронный ресурс]. URL:  https://ru.wikipedia.org/wiki/Дебет_и_кредит (дата обращения: 11.12.2021).
5. Шениг Ганс-Юрген. PostgreSQL 11. Мастерство разработки. – М.: ДМК Пресс, 2019.
6. Нормальная форма. / [Электронный ресурс]. URL:  https://ru.wikipedia.org/wiki/Нормальная_форма (дата обращения: 16.01.2022).
7. Ньюмен Сэм. Создание микросервисов. – СПб.: ИД «Питер», 2016.
8. Хранимые процедуры и функции базы данных. / [Электронный ресурс]. URL:  https://russianblogs.com/article/98931524057/ (дата обращения: 20.12.2021).