Продолжение цикла "Занимательная бухгалтерия".
Попробуем спроектировать базу данных модуля "Бухгалтерия" на 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-го порядка, надо посмотреть требования.
ссылка на оригинал статьи https://habr.com/ru/post/568192/
Добавить комментарий