nomhoi 17 июл 2021 в 11:08

Empire ERP. Занимательная бухгалтерия: PostgreSQL

6 мин

3.7K

PostgreSQL*Анализ и проектирование систем*ERP-системы*

Содержание цикла статей: https://github.com/nomhoi/empire-erp.

Попробуем спроектировать базу данных модуля "Бухгалтерия" на PostgreSQL.

Настройка проекта

Клонируем проект с гитхаба:

git clone https://github.com/nomhoi/empire-erp.git

Заходим в папку reaserch/day2/.

Step 1. Главная книга

Запустим базу данных и выполним тесты:

docker-compose run test

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

В данном случае создается фиксация на базе code/step1.sql:

DROP TABLE IF EXISTS general_ledger;

CREATE TABLE general_ledger(
    id        serial,
    debit_id  smallint NOT NULL,
    credit_id smallint NOT NULL,
    amount    money NOT NULL
);

INSERT INTO general_ledger(debit_id, credit_id, amount)
VALUES  (1, 12, 100.00),
        (1, 6, 120.00),
        (12, 1, 20.00);

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

test_step1.py:

import pytest_postgresql.factories.client
import pytest_postgresql.factories.noprocess
from pytest_postgresql.compat import connection

postgresql_my_proc = pytest_postgresql.factories.noprocess.postgresql_noproc(
    dbname="empire-erp-2", load=["./step1.sql"]
)
postgres = pytest_postgresql.factories.client.postgresql(
    "postgresql_my_proc", dbname="empire-erp-2"
)

def test_1(postgres: connection) -> None:
    with postgres.cursor() as cur:
        cur.execute("SELECT * FROM general_ledger ORDER BY id;")
        res = cur.fetchall()
        assert len(res) == 3

Можно выполнить sql запросы вручную. Подключимся к базе данных empire-erp:

docker exec -it db psql -U postgres -d empire-erp

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

empire-erp=# \i step1.sql

Выполняем запрос для получения списка проводок из главной книги:

SELECT * FROM general_ledger ORDER BY id;
 id | debit_id | credit_id | amount  
----+----------+-----------+---------
  1 |        1 |        12 | $100.00
  2 |        1 |         6 | $120.00
  3 |       12 |         1 |  $20.00
(3 rows)

Для получения списка проводок по счету 1 можно выполнить такой запрос:

SELECT id                AS general_ledger_id,
       credit_id         AS corr_id,
       amount            AS debit_amount,
       ( 0.00 ) :: money AS credit_amount
FROM   general_ledger
WHERE  debit_id = 1
UNION
SELECT id                AS general_ledger_id,
       debit_id          AS corr_id,
       ( 0.00 ) :: money AS debit_amount,
       amount            AS credit_amount
FROM   general_ledger
WHERE  credit_id = 1
ORDER  BY general_ledger_id;
 general_ledger_id | corr_id | debit_amount | credit_amount 
-------------------+---------+--------------+---------------
                 1 |      12 |      $100.00 |         $0.00
                 2 |       6 |      $120.00 |         $0.00
                 3 |      12 |        $0.00 |        $20.00
(3 rows)

Или создать функцию account_entries :

DROP FUNCTION IF EXISTS account_entries;

CREATE FUNCTION account_entries(account_id integer)
RETURNS TABLE (
    general_ledger_id   integer,
    corr_id             smallint,
    debit_amount        money,
    credit_amount       money
) AS $$
    SELECT id                AS general_ledger_id,
           credit_id         AS corr_id,
           amount            AS debit_amount,
           ( 0.00 ) :: money AS credit_amount
    FROM   general_ledger
    WHERE  debit_id = account_id
    UNION
    SELECT id                AS general_ledger_id,
           debit_id          AS corr_id,
           ( 0.00 ) :: money AS debit_amount,
           amount            AS credit_amount
    FROM   general_ledger
    WHERE  credit_id = account_id
    ORDER  BY general_ledger_id;
$$ LANGUAGE sql;

Выполним команду:

empire-erp=# \i step1_3.sql

Выполним запрос:

SELECT * FROM account_entries(1);
 general_ledger_id | corr_id | debit_amount | credit_amount 
-------------------+---------+--------------+---------------
                 1 |      12 |      $100.00 |         $0.00
                 2 |       6 |      $120.00 |         $0.00
                 3 |      12 |        $0.00 |        $20.00
(3 rows)

Step 2. Оборотная ведомость и баланс

Как и в предыдущей статье — все транзакции выполняются в одном отчетном периоде, в начале периода остатки отсутствуют.

Выполним команду:

empire-erp=# \i step2.sql

Для определения оборотов по счетам выполним такой запрос:

DROP TABLE IF EXISTS turnout;

SELECT account_id,
       sum(debit_turnout)  AS debit_turnout,
       sum(credit_turnout) AS credit_turnout
INTO   turnout
FROM   (SELECT debit_id          AS account_id,
               sum(amount)       AS debit_turnout,
               ( 0.00 ) :: money AS credit_turnout
        FROM   general_ledger
        GROUP  BY debit_id
        UNION
        SELECT credit_id         AS account_id,
               ( 0.00 ) :: money AS debit_turnout,
               sum(amount)       AS credit_turnout
        FROM   general_ledger
        GROUP  BY credit_id) AS turnout
GROUP  BY account_id
ORDER  BY account_id;

SELECT * FROM turnout;
DROP TABLE
SELECT 3
 account_id | debit_turnout | credit_turnout 
------------+---------------+----------------
          1 |       $220.00 |         $20.00
          6 |         $0.00 |        $120.00
         12 |        $20.00 |        $100.00
(3 rows)

Оборотная ведомость

SELECT start_balance.id            AS account_id,
       start_balance.debit_amount  AS debit_start,
       start_balance.credit_amount AS credit_start,
       turnout.debit_turnout,
       turnout.credit_turnout,
       CASE
         WHEN turnout.debit_turnout + start_balance.debit_amount -
              turnout.credit_turnout - start_balance.credit_amount >= ( 0.0 ) :: money
         THEN turnout.debit_turnout + start_balance.debit_amount -
              turnout.credit_turnout - start_balance.credit_amount
         ELSE ( 0.0 ) :: money
       END                         AS debit_final,
       CASE
         WHEN turnout.credit_turnout + start_balance.credit_amount -
              turnout.debit_turnout - start_balance.debit_amount >= ( 0.0 ) :: money
         THEN turnout.credit_turnout + start_balance.credit_amount -
              turnout.debit_turnout - start_balance.debit_amount
         ELSE ( 0.0 ) :: money
       END                         AS credit_final
FROM   start_balance
       LEFT JOIN turnout
              ON start_balance.id = turnout.account_id;
 account_id | debit_start | credit_start | debit_turnout | credit_turnout | debit_final | credit_final 
------------+-------------+--------------+---------------+----------------+-------------+--------------
          1 |       $0.00 |        $0.00 |       $220.00 |         $20.00 |     $200.00 |        $0.00
          2 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
          3 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
          4 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
          5 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
          6 |       $0.00 |        $0.00 |         $0.00 |        $120.00 |       $0.00 |      $120.00
          7 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
          8 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
          9 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
         10 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
         11 |       $0.00 |        $0.00 |               |                |       $0.00 |        $0.00
         12 |       $0.00 |        $0.00 |        $20.00 |        $100.00 |       $0.00 |       $80.00
(12 rows)

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

Проблемы со сторно, которая была в прошлой статье сейчас не должно быть, не проверял.

Step 3. Проверка добавления проводок

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

3.1 Проверка уже заполненной главной книги

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

Если пишем на PL/pgSQL, то для хранения остатков по счетам мы можем использовать таблицу с такой структурой:

CREATE TABLE balance (
    account_id      smallint NOT NULL,
    debit_amount    money DEFAULT 0.0,
    credit_amount   money DEFAULT 0.0
);

Если пишем на PL/Python, то можем использовать словарь. Если пишем на C++ — unordered_map.

Алгоритмы проверки остатков на активных и пассивных счетах и обновления остатков на всех счетах в таблице остатков для разных типов счетов различаются. Нужно определять типы счетов каждой проводки. Для определения типа проводки в таблицу плана счетов coa (chart of accounts) добавим поле для типа счета: account_type:

CREATE TYPE t_account_type AS ENUM ('active', 'passive', 'active-passive');

CREATE TABLE coa (
    account_id      smallint NOT NULL,
    account_type    t_account_type NOT NULL,
    name            text NOT NULL
);

И каким-то образом будем определять с помощью специальной функции. На С++ можно опять использовать unordered_map.

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

3.2 Проверка на каждом вводе проводки

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

На Python и C++ нужно иметь в виду, что в PostgreSQL соединения являются отдельными процессами, поэтому структуру с остатками нужно хранить в разделяемой между процессами памяти и координировать ее обновление.

3.3 Другие ошибки

В этой статье https://en.wikipedia.org/wiki/Trial_balance указываются несколько ошибок в разделе Limitations. Первая ошибка в нашем случае не проявится, поскольку данные берутся только из главной книги:

An error of original entry is when both sides of a transaction include the wrong amount.[2] For example, if a purchase invoice for £21 is entered as £12, this will result in an incorrect debit entry (to purchases), and an incorrect credit entry (to the relevant creditor account), both for £9 less, so the total of both columns will be £9 less, and will thus balance.

Остальные ошибки из этой статьи еще не разобрал. Кто разберет и разъяснит всем, тому зарезервирую планету.

Step 4. Резюме

Как видим, мы можем всю логику модуля "Бухгалтерия" реализовать на стороне сервера базы данных и создать SQL API.
На следующем дне рассмотрим аналитические счета и неможко покодируем на UML.

Step 5. Занимательное

В этой книге Artificial Intelligence. A Modern Approach есть глава Automated Planning. Поскольку ERP про планирование, то имеет смысл добраться до этой главы.

Здесь https://ит-гранты.рф/2 в конкурсной документации в приложении #3 видим, что ERP системы имеют приоритет 1-го порядка, надо посмотреть требования.

Теги:

Хабы: