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

Необходимость учета с большим числом разрезов аналитики

На первый взгляд кажется, что уже 5 уровней аналитики более чем достаточно для бухгалтерского учета на балансовых счетах. Однако, бухгалтерии-тяжеловесы позволяют иметь неограниченное или ограниченное большим числом (20 и более) количество аналитических уровней, которое можно эффективно использовать для управленческого учета.

Действительно, 5 уровней аналитики, как правило, вполне достаточно для хранения всей аналитической информации в балансе. Однако это не означает что это удобно для эффективного анализа данной информации. Гораздо удобнее для отчетов таких как “Движение Денежных Средств” (ДДС) и “Отчет о Доходах и Расходах” (ОДР) иметь один учетный регистр и сканировать его по всей необходимой аналитике, например внешним генератором отчетов, чем собирать информацию по всему балансу неким фиксированным отчетом, который нужно программировать под каждую задачу. Данный подход, при котором счет содержит в себе всю заявленную аналитическую информацию о своих объектах учета можно назвать подходом единого учетного регистра. Использование единых учетных регистров сразу увеличивает потребность в уровнях аналитики где-то до 10 уровней.

Например, для единого учетного регистра ДР имеем 7 аналитических уровней: Центр Финансового Учета (ЦФУ), Доход/Расход, Юр.лицо, Статья Затрат, Вид Деятельности, Внешние/Внутренние затраты, Оборотная аналитика.

Для ДДС имеем уже 10 уровней: Банк/Касса, Валюта, Расчетный счет, ЦФУ, Доход/Расход, Юр.лицо, Статья Затрат, Вид Деятельности, Внешние/Внутренние затраты, Оборотная аналитика.

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

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

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

Например, упрощенный вид сальдовой таблицы 1С 7.5:

В системе NEXUS имеется аналогичная сальдовая таблица Saldo. Заполняется она из проводок довольно сложным, но очень производительным триггером (0.2 сек на проводку при 10000 разрезов).

Как видим, используемый способ хранения отводит под 1 уровень аналитики 1 колонку сальдовой таблицы. В случае если надо добавить еще один уровень, то необходимо добавить поле в сальдовую таблицу и сделать довольно трудоемкое изменение механизма формирования сальдовой таблицы (в нашем случае это триггер). Задача добавить в 3х уровневую сальдовую таблицу еще 3 уровня (например, для склада ЦФУ, Юр.лицо, Статус Товара) достаточно сложна.

Краткое отступление: минимум сведений по архитектуре NEXUS

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

Так как NEXUS является документно-ориентированной системой, то разработчики оперируют не термином "объект", а термином "документ". Организация хранения документов базируется на концепции главной таблицы с гиперключом. Приведем скрипт данной таблицы с описанием

CREATE TABLE dbo.Docs (
   UDN int IDENTITY (1, 1) NOT NULL, -- ID документа
   No varchar (128) NOT NULL , -- Пояснительное поле документа (чаще его номер в виде строки)
   Name varchar (128) NOT NULL , -- Название документа
   Date datetime NOT NULL , -- Дата документа
   Folder int NOT NULL , -- Ссылка на папку вхождения документа
   timestamp timestamp NOT NULL , -- Метка времени работы с документом
   DocFlags smallint NOT NULL , -- Системные флаги
   ParentDoc int NULL , -- Для ссылок: указатель на документ
   Class varchar (24) NOT NULL , -- Класс документа
   Deleted tinyint NOT NULL –- Признак логически удаленного документа
)

• UDN - это гиперключ, т.е. в базе существует только один данный ключ для всех документов. Данный ключ суррогатный, представляет собой сквозную нумерацию и имеет в рамках бухгалтерии физический смысл кода аналитического учета. UDN никогда не меняется, т.е. каскадных обновлений ключей в БД нет, также чаще всего нет составных ключей. Все это обеспечивает высокую производительность БД. Указанный подход к формированию гиперключа не является единственным и самым удачным, расширенный вариант может включать в состав гиперключа ID домена для распределенной БД.
• No – поле без фиксированного назначения, чаще всего в него помещают уникальный идентификатор документа в рамках предметной области. В данном случае нумерация объектов и отслеживание ее идентичности ложится на объекты-нумераторы. Заметим, для идентификации документа внутри системы нумератор не нужен, он идентифицируется независимо от No по полю UDN.
• Folder – это ссылка на папку хранения документа, т.е. Folder содержит UDN папки. Таким образом, Docs может описывать древовидную структуру. Отдельные ветки данного глобального дерева играют роль древовидных справочников типа "Товары", "Клиенты" и т.д.
• ParentDoc – указатель на истинный документ для сылки (shortcut). Каждый документ может иметь одно физическое нахождение в дереве и множество ссылок на него. Таким образом, реализуется сетевое преставление, если в нем есть необходимость.
• Class – название класса документа
• Deleted – признак логического удаления документа. Чаще всего документы не удаляются физически, а помечаются как удаленные и попадают в архив. Всегда можно восстановить ошибочно удаленный объект.

Таблица Docs позволяет хранить информацию о базовом классе Doc, производные классы хранят информацию в дополнительных таблицах, связка осуществляется через UDN.

Например, абстрактный класс "Платежный документ".

CREATE TABLE dbo.PaymentExt (
  UDN int NOT NULL , -- Ссылка на гиперключ
  VAT money NOT NULL , -- НДС
  TSum money NOT NULL , -- Сумма в учетной валюте
  PCur int NOT NULL , -- UDN для валюты документа
  Stat tinyint NOT NULL , -- Состояние документа
  PType tinyint NOT NULL , -- Тип платежа
  Supp int NOT NULL , -- Абстрактный источник
  Rec int NOT NULL , -- Абстрактный приемник
  SuppPC int NOT NULL , -- Абстрактный центр платежа источника
  RecPC int NOT NULL , -- Абстрактный центр платежа приемника
  Sum$ money NOT NULL -- Сумма в валюте
)

Описание структур машины проводок

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

Таблица заголовков проводок

Заголовок проводки крепится к Docs по ключу UDN. В заголовке указан план счетов, счета дебета и кредита, ссылка документ породивший проводку.

CREATE TABLE dbo.Pass (
  UDN int NOT NULL , -- UDN код проводки
  AccId int NOT NULL , -- План счетов проводки
  Processed tinyint NOT NULL , -- Признак "проведенно"
  Acc1 int NOT NULL , -- Счет дебета (UDN-код)
  Acc2 int NOT NULL , -- Счет кредита
  Typ tinyint NOT NULL , -- Тип проводки (не расшифровывается)
  Doc int NOT NULL – Документ-основание проводки
)

Таблица комплекта (содержания) проводок

Описание аналитики (u1..u13) и сумм (i, s, v) в проводке содержится в таблице Complect. Она связана с проводкой по ключу UDN.

Следует заметить, что в системе NEXUS проводки обладают уникальным качеством. Они множественные на уровне архитектуры. Имеется в виду то, что проводка может содержать не как обычно набор-строку аналитик и сумму, а целую таблицу наборов аналитик и сумм. Например, проводка к накладной проводит одна сразу весь набор товаров, а не серией в 20-100 проводок как системах типа 1С. Данный подход позволяет достигнуть высокой скорости формирования и проведения проводок.

CREATE TABLE dbo.Complect (
  ComplectKey int IDENTITY (1, 1) NOT NULL , -- Код строки комплекта проводки
  UDN int NOT NULL , -- Ссылка на код проводки (Pass.UDN)
  u1 int NOT NULL ,
  u2 int NOT NULL ,
  u3 int NOT NULL ,
  u4 int NOT NULL ,
  u5 int NOT NULL ,
  u6 int NULL ,
  u7 int NULL ,
  u8 int NULL ,
  u9 int NULL ,
  u10 int NULL ,
  u11 int NULL ,
  u12 int NULL ,
  u13 int NULL ,
  i float NOT NULL , -- количество
  s money NOT NULL , -- сумма в учетной валюте
  v money NULL -- сумма в валюте документа
  doc int
)

Грануляция дат (номера периода для сальдовых отчетов)

Сальдовые отчеты всегда рассматриваются в определенном интервале дат. Конечно можно учет по датам вести в формате datetime или суррогате varchar (что-то типа '19990102', как в ForSale и 1С). Однако, зная что сальдовый учет ведется с неким минимальным периодом (день, неделя, месяц), то более производительный вариант будет использовать в для учета номера периодов, так называемые "грануляции периодов". Для их описания служат следующие таблицы.

Таблица-заголовок объекта грануляции

CREATE TABLE dbo.Gran (
  UDN int NOT NULL ,
  Closed int NOT NULL , -- Номер закрытого периода
  Origin tinyint NOT NULL , -- Вид грануляции (день, неделя, месяц,...)
  Auto tinyint NOT NULL – Автоматическое наращивание номеров периодов
)

Таблица описания периодов и их номеров

CREATE TABLE dbo.GranDates (
  UDN int NOT NULL ,
  Cindex int NOT NULL , -- Номер периода
  d1 datetime NOT NULL , -- Начальная дата
  d2 datetime NOT NULL -- Конечная дата
)

Заголовок счета

В NEXUS счета, как и любые другие документы, связываются по UDN. Номер и название счета хранятся в Docs.No и Docs.Name. Остальная информация описывается в отдельной таблице связанной по ключу UDN

CREATE TABLE dbo.Acc (
  UDN int NOT NULL ,
  AccId int NOT NULL , -- План счетов
  Closed int NOT NULL , -- Номер закрытого периода
  Typ int NOT NULL , -- Тип счета (активный, пассивный, активно-пассивный, забалансовый)
  Cache smallint NOT NULL
)

Описание видов аналитики на счету

Для этого имеется специальная таблица Storage, в которой в полях u2..u10 находятся UDN папок в дереве Docs, являющихся справочниками, привязанными к счету. В u1 находится ссылка на грануляцию, указывающий в какой периодике ведется учет по счету.

CREATE TABLE dbo.Storage (
  UDN int NOT NULL , -- Код счета (Acc.UDN)
  u1 int NOT NULL ,
  u2 int NOT NULL ,
  u3 int NOT NULL ,
  u4 int NOT NULL ,
  u5 int NOT NULL ,
  u6 int NULL ,
  u7 int NULL ,
  u8 int NULL ,
  u9 int NULL ,
  u10 int NULL ,
  u11 int NULL ,
  u12 int NULL ,
  u13 int NULL
)

Сальдовая таблица

Для анализа сальдовой информации имеется специальная таблица автоматически формируемая триггером при проведении проводок. В u1 находится номер периода, в u2..u8 аналитики (напоминаем, что описывается вариант без поддержки 20 уровней). Назначение полей с префиксами i, s, v приводим ниже.

CREATE TABLE dbo.Saldo (
  UDN int NOT NULL , -- Код счета (Acc.UDN)
  u1 int NOT NULL , -- Номер периода
  u2 int NOT NULL ,
  u3 int NOT NULL ,
  u4 int NOT NULL ,
  u5 int NOT NULL ,
  u6 int NULL ,
  u7 int NULL ,
  u8 int NULL ,
  id float NOT NULL ,
  ic float NOT NULL ,
  idt float NOT NULL ,
  ict float NOT NULL ,
  sd money NOT NULL ,
  sc money NOT NULL ,
  sdt money NOT NULL ,
  sct money NOT NULL ,
  vc money NULL ,
  vd money NULL ,
  vct money NULL ,
  vdt money NULL
)

Примеры запросов к проводкам и сальдо

Запрос к проводкам

Данный запрос используется при вычислении рентабельности реализации товаров. Приводится его упрощенный вид: вывести по подразделениям все проводки в дебет по указанному \@Acc2, плану счетов \@AccId, при этом показать аналитику "Товар" с артикулом и документ-основание.

select
  D1.Name as _ПОДРАЗДЕЛЕНИЕ,
  D3.Name as _ТОВАР,
  D3.No as _АРТ,
  D2.Name as _НАКЛ ,
  sum(C.s) as _ОТПУСКНСУММА,
  sum(C.i) as _КОЛИЧЕСТВО
from Pass P, Complect C, Docs D1, Docs D2, Docs D3, Docs DP
where
  P.Processed=1 and -- Проведенные проводки
  P.AccId=@AccId and – По указанному плану счетов
  DP.UDN=P.UDN and -- Свяжем заголовок проводки Pass с описанием проводки

  D2.UDN=P.Doc and –- Свяжем код документа-основания с Docs, для получения его Name
  C.UDN=P.UDN and -- Свяжем заголовок проводки и комплект ее аналитики
  P.Acc2=@Acc2 and – Будем брать проводки только с кредитом счета @Acc2
  C.u6=D1.UDN and C.u2=D3.UDN and – Найдем Name и No для объектов аналитики
  DP.Date>='1.1.2004' and DP.Date<='12.31.2004' – Будем брать проводки за указанный интервал
group by D1.Name, D3.Name, D3.No, D2.Name

Запрос в сальдо

Приводится упрощенный запрос для расчета себестоимости товара: вывести отношение сальдовой суммы к количеству для указанного счета \@Acc2 и товара \@Good на период \@ToIdx

select sum(sct-sdt)/ sum(ict-idt) as СЕБЕСТОИМОСТЬ
from Saldo S
where
  UDN=@Acc2 and – Будем брать сальдо по указанному счету @Acc2
  u2=@Good and -- Будем брать сальдо по указанному товару @Good
  u1=(select max(u1) from Saldo where u1<=@ToIdx and UDN=S.UDN and u2=S.u2)
  -- Будем брать сальдо для периода до @ToIdx

Маршрут аналитики из проводок в сальдо

"Это математически сделать невозможно! Нельзя без описания типов аналитики в отдельной таблице определить, как аналитика из проводки попадает в сальдо!" (с) Очень Хороший Проектировщик

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

Маршрут аналитики – это однозначный и стандартный способ перемещения аналитики из проводки (Complect) в сальдовую таблицу (Saldo), т.е. соответствие и назначение u-полей.

Исходный текст процедуры проведения SuperDocPass вы можете найти в файле Account.sql модуля Account, загрузив исходные тексты NEXUS.

Концепция триггера накопления статистики

В MS SQL триггер является эффективным средством организации статистических суммирующих (накапливающих) таблиц типа Saldo. Триггер получает в оптимальном виде разницу (дельту) для данных по которым ведет статистику и сразу может поправить данные в статистической таблице ровно на эту дельту без дополнительных отложенных пересчетов. Рассмотрим простой пример такого механизма.

Как уже отмечалось, проводка в NEXUS содержит целую таблицу аналитик с суммами. Поэтому когда интересен вопрос какова сумма по такой составной проводке без учета аналитик, то выгодно иметь накапливающую таблицу (кэш) следующего плана:

CREATE TABLE dbo.ComplectInfo (
  UDN int NOT NULL , -- Проводка
  sum_s money NULL -- Общая сумма по проводке без учета аналитик
)

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

CREATE TRIGGER ComplectInformator ON Complect FOR INSERT, UPDATE, DELETE
AS
BEGIN
  insert ComplectInfo(UDN, sum_s)
    select distinct inserted.UDN, 0 from inserted
      where not exists (
            select inserted.UDN
              from ComplectInfo
              where ComplectInfo.UDN=inserted.UDN)

  update ComplectInfo
    set ComplectInfo.sum_s=ComplectInfo.sum_s + (
        select sum(inserted.s) from inserted
          where inserted.UDN=ComplectInfo.UDN)
    where exists (
      select distinct ComplectInfo.UDN
        from inserted
        where ComplectInfo.UDN=inserted.UDN)

  update ComplectInfo
    set ComplectInfo.sum_s=ComplectInfo.sum_s - (
        select sum(deleted.s)
           from deleted where deleted.UDN=ComplectInfo.UDN)
    where exists(
      select distinct ComplectInfo.UDN from deleted
        where deleted.UDN=ComplectInfo.UDN)
END

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

Заключение

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

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

Владимир Иванов, Сергей Тарасов 1999, 2005 с исправлениями и дополнениями