Комментарии 44
Спасибо, что показали свое видение внутреннего устройства бух. учета. Есть несколько вопросов и замечаний:
Непонятно, что из этого материальные таблицы, а что представления и запросы.
Как-то не отражено соответствие и субсчетов, а если субсчета у субсчетов? Из активность/пассивность где отражена? Намек на таблицу плана счетов.
Где сальдо? При этом у активных счетов - оно только по дебету (может отрицательным быть и значит что-то не так), у пассивных - по кредиту, у активно-пассивных оно развернутое.
Аналитика только одна. А если две? Например 10 счет по складам и материалам.
Как обеспечить ссылочную целостность аналитического учета, если "10" это склады и материалы, а "60" это поставщики?
Не представлен количественный и валютный учет.
Ну и конечно проводки имеют дату отражения в учете.
Извиняюсь, я с остальным Вашим творчеством еще не ознакомился.
По всем вопросам - я пока провожу исследования, изучаю эту область. На некоторые вопросы ответы в прошлой статье. Рассмотрение аналитического учета только началось.
Непонятно, что из этого материальные таблицы, а что представления и запросы.
Вообще, я планирую использовать ORM. Нужна поддержка миграций и кастомизации моделей. С ORM-ом это вроде делается проще.
Как-то не отражено соответствие и субсчетов, а если субсчета у субсчетов?
Счет в одном поле, субсчет в другом поле. Субсчета других порядков можно выполнить аналогично, в отдельных полях.
Как обеспечить ссылочную целостность аналитического учета, если "10" это склады и материалы, а "60" это поставщики?
Можно добавить поле - идентификатор операции.
Вариант с полями под субсчета мне кажется дорогой не туда. Иногда счет сначала ввели как основной, а потом его надо сделать субсчетом и наоборот.
Можно добавить поле - идентификатор операции.
Как это поможет с ссылочной целостностью?
Прошу прощения, немного не в тему ответил. Сейчас, пока собирается информация какие поля нужны и какая структура данных нужна, наверное, преждевременно декларировать внешние ключи и т.д. Когда все более менее определится, то можно будет все и определить. Возможно, уже при создании моделей на SQLAlchemy.
Идентификатор операции - для того, чтобы связать все проводки имеющие отношение к одной операции. В данном случае, в синтетическом учете будет одна проводка, а в аналитическом учете три проводки получается: материал разложился на 2 субсчета и 2 склада. Кроме того, планируется рассмотреть использование вспомогательных журналов и книг.
Аналитика только одна. А если две? Например 10 счет по складам и материалам.
Добавляем поле для материалов.
Добавьте пожалуйста ссылку в начале статьи на предыдущие статьи цикла, а то непонятно зачем и почему. За статью спасибо, читаю с интересом.
Продолжение цикла "Занимательная бухгалтерия".
Как по мне сумму по проводкам лучше писать в одно поле с плюсом по дебету и с минусом по кредиту. И меньше места займет и проше рас считывать обороты и остатки достоточно просто просуммировать. И не надо проверять что на выхеде в деьете или кредите спмо поеажет по знаку
В таблице проводок сумма вообще одна, потому что там два поля счёт дебета и счёт кредита
Но стоит отличать ситуации
Дт 10 Кт 60 сумма -125
И
Дт 60 Кт 10 сумма 125
ну вот по минусу и видно что там кредитовое сальдо или оборот
а если надо развернутое сальдо ничего не мешает в запросе написать when case и получить отдельно по плюсам и минусам
я в своей системе так сделал. причем остатки считаются полным пересчетом а не с промежуточными остатками как в той же одноце
Очегнь удобно считать за любой переио перепроводить документы задним числом и так далее. конечно больше нагрузка на сервер но железо ща стоит намного дешевле труда програмиста
У меня для структура таблицы для проводок
Id_счета, Знак, Сумма, id_контрагента, id_проводки
id_проводки связывает 2 строки в одну (и соответствует понятию "двойная запись")
id_контрагента - это как организации так и свои склады/кассы/расч. счета
Знак позволяет различить ситуацию Дт 10 Кт 60 сумма -125 и Дт 60 Кт 10 сумма 125
Такая структура большинство отчетов делает тривиальными
Любой отчет считается полным пересчетом, но по закрытию периода я в таблицу проводок вношу остатки: половина проводки, с признаком остаток.
В дальнейшем остаток считается от ближайшего закрытия.
Когда-то здесь описывал упрощенный вариант моего учета https://www.sql.ru/forum/1321016/zhurnal-dvizheniya-deneg
Так-то проводки, а то сальдо
Но в таком случае как отличить обороты? это увеличениеоборота по кредиту или уменьшение по дебету?
Допускаю, что кому-то это не нужно
сальдо - результат проводок как и оборот. просчитайте проводки на дату с начала учета то есть с самой первой и получите сальдо.
отличить по знаку как я уже писал - получить равернутое сальдо не проблема хотя это нужно в редких случаях когда применяется бухгалтерское сторно
Как выше уже сказали сальдо и обороты это результат суммирования проводок за весь период.
Я так сделал в позапрошлой статье. В комментариях указали на ошибку в оборотах при проведении сторно: https://habr.com/ru/post/471304/#comment_20748390
Получаем из журнала проводок главную книгу и сохраняем ее
При использовании структуры таблиц, в котором проводка разбита на 2 строки получение оборотов + остатки на начало и на конец периода (сальдо) это запрос вида
select account_id,
sum(case when DATE >= '01.01.2021' and "Знак" = 1 then amount else 0) AS debit_turnout,
sum(case when DATE >= '01.01.2021' and "Знак" = -1 then amount else 0) AS credit_turnout,
sum("Знак" * amount) AS saldo,
sum(case when DATE < '01.01.2021' then "Знак"* amount) AS saldo_begin
from "Таблица_полупроводок"
group by account_id
Такой запрос гораздо лучше индексируется (по сравнению с запросом с UNION)
Про субсчета. Для каждого счета есть признак главный счет и соответственно группировать можно по главному счету.
Ваша структура легко приводится к такой путем добавлением триггера на general_journal (здесь разделять на 2 строки). Хотя конечно, с моей т.з. дублирование всех полей на счет, субсчет, склад для дебета и кредита себе идея.
Что такое "Знак"?
Поле признак, для дебета = "+1", для кредита = "-1".
Проводка разбивается на две строки: дебетная и кредитная. Поля в строке: счет, склад/организация, дата, сумма, знак, id_проводки (дополнительно можно примечание, id_документа, и т.д.)
id_проводки в вашем случае может соответствовать general_journal.id и нужно в дальнейшем для отображения проводки в "канонической форме" (должно быть уникальным для обеих строк)
Столбцы: дата, сумма, id_проводки у обеих строк одинаковые.
дублирование всех полей на счет, субсчет, склад для дебета и кредита себе идея.
Я планирую рассмотреть использование вспомогательных журналов и/или книг для записи проводок. Для каждого счета можно сделать свой журнал со своим набором полей, которые нужны только для этого счета. Возможно, в этом случае дублирование уйдет.
С другой стороны, удобно обортную ведомость получать.
Со вспомогательными журналами получается так:
https://github.com/nomhoi/empire-erp/tree/master/research/day4
Мне кажется что разделение проводок по разным таблицам-журналам это неправильный подход. Вы привязываетесь к номерам журналов и конкретному плану счетов. Они имеют свойство меняться. Я не знаком с этими номерами в РФ, но мне кажется номер журнала можно хранить в проводке. И изменение страны для которой происходит учет не должно влиять на структуру таблиц.
Таблицу счетов можно сделать древовидной, а в таблице операций хранить только один счет, самого низкого уровня. И обороты по главному счету получать при помощи объединения с таблицей проводок.
У меня структура таблиц в бухгалтерии:
1. Документ (набор таблиц с датой, типом, контрагентами, списком товаров и т.д.). У меня 2 основные таблицы. Документ имеет 2 состояния: черновик и проведенный документ.
В момент проведения на основе настроек конкретного типа документа, вида товаров и т.д. формируются проводки.
2. Проводки (или точнее полупроводки). При проведении документа формируется набор проводок, где каждая проводка это 2 строки и полями: id_документа, дата, счет, организация, сумма, знак (1=дебет, -1=кредит), примечание, id_операции (связь дебета с кредитом). Сюда же можно и воткнуть номер журнала.
3. Движение товаров. При перемещении товаров у меня формируется таблица похожая на проводки, но немного проще: id_документа, дата, id_товара, id_склада, кол-во, сумма, знак (1=приход, -1=расход), id_партии (для партионного учета)
4. Справочники (счетов, организаций, товаров и т.д.)
Все остальное: обороты, остатки, движение получается довольно простыми запросами.
Мне кажется что разделение проводок по разным таблицам-журналам это неправильный подход.
Разные журналы могут иметь свой набор полей, который нужен только им.
Вы привязываетесь к номерам журналов и конкретному плану счетов. Они имеют свойство меняться. Я не знаком с этими номерами в РФ, но мне кажется номер журнала можно хранить в проводке. И изменение страны для которой происходит учет не должно влиять на структуру таблиц.
Да, конечно, я об этом думаю. Пока рассмотрел частный случай, для России.
Таблицу счетов можно сделать древовидной, а в таблице операций хранить только один счет, самого низкого уровня. И обороты по главному счету получать при помощи объединения с таблицей проводок.
Мне знакома такая терминология: синтетические счета - счета первого порядка, субсчета - счета второго порядка, субсчета могут быть разделены на свои собственные субсчета - счета третьего порядка и следующие порядков. Главный счет, я так понимаю - синтетический счет, а счета самого низкого уровня - это субсчета самого глубокого порядка? Правильно понимаю?
А с производительностью как в этом случае? Для получения регистров обычная группировка с агрегацией, мне кажется, быстрее будет работать.
У меня структура таблиц в бухгалтерии:
Как-нибудь, дело дойдет, нарисую uml диаграммы.
Да, в проводке хранится только самьій "низкий" уровень счета.
Не думаю что некоторая избьіточность полей в таблице журналов является веской причиной для отдельньіх таблиц.
С производительностью проблем нет. Для примера, у меня основная таблица проводок >15М записей. Дополнительно раз в месяц происходит закрьітие периода (можно и чаще), во время которого собираются промежуточньіе итоги (они тоже хранятся в таблице проводок). Большинство отчетов получается за время не более 2х секунд.
Я ранее здесь давал ссьілку на SQLru, там описана структура таблиц детальнее
Спасибо, я посмотрю.
Да, в проводке хранится только самьій "низкий" уровень счета.
В проводках всегда должен указываться самый крайний субсчет, т.е. самого "низкого" порядка.
Если в нашем случае мы для субсчета укажем NOT NULL,
acc_sub_id smallint NOT NULL, то будет невозможно создать проводку без указания самого крайнего субсчета. СУБД сама будет нас защищать от создания ошибочной проводки, будет поднимать исключение.
Для разных счетов может быть разное количество порядков, поэтому придется создавать отдельные журналы или книги.
В вашем случае как выполняется защита от создания подобных неправильных проводок? Нужно всегда помнить, является ли счет крайним или нет?
У меня на этот случай нет никаких ограничений на уровне СУБД. Пользователь сам решает какой счет использовать и с каким уровнем вложенности работать.
Для запрета на уровне СУБД в справочнике счетов достаточно ввести поле (самый низкий уровень), и разрешать использовать только такие поля. Поле обновлять автоматически при редактировании справочника счетов.
Не думаю что некоторая избьіточность полей в таблице журналов является веской причиной для отдельньіх таблиц.
Для многих генераторов отчетов, если нужно получить отчет с агрегацией по группам, нужно подавать датасет примерно в таком виде:
acc_id | acc_sub_id | stock_id | debit_turnout | credit_turnout
--------+------------+----------+---------------+----------------
10 | 1 | 1 | $100.00 | $50.00
10 | 1 | 2 | $200.00 | $0.00
10 | 2 | 1 | $100.00 | $0.00в данном случае генератор отчетов самостоятельно вычислит итоговые суммы по складам, субсчетам и счетам, а также итоговую сумму в целом по отчету.
В вашем случае как выглядит запрос получения подобного датасета, если мы имеем несколько порядков субсчетов?
Я не совсем понимаю вопрос.
Вам непонятно как сделать сумму по "дереву" счетов?
В древовидном справочнике счетов есть поле "Родитель".
Можно добавить еще одно "Родитель для группировки". Здесь и будет самьій верхний уровень. Зачем добавлять это поле в проводку я не понимаю.select СЧЕТ.ГЛАВНЫЙ_СЧЕТ,
sum(case when ПРОВОДКИ.ЗНАК = 1, ПРОВОДКИ.СУММА else 0 end) debit_turnout,
sum(case when ПРОВОДКИ.ЗНАК = -1, ПРОВОДКИ.СУММА else 0 end) credit_turnout,
from ПРОВОДКИ
join СЧЕТ on СЧЕТ.id = ПРОВОДКИ.счета
group by СЧЕТ.ГЛАВНЫЙ_СЧЕТ
У вас есть такие отчеты, в которых выполняется агрегация по группам?
Например, в этом отчете выполняется группировка по полю Cust ID.
И здесь мы видим суммы для каждой такой группы.
Таких групп в одном отчете может быть несколько. В вашем случае как это можно выполнить? Т.е. как можно подготовить датасет для такого отчета?
Да, конечно есть.
select o.CustId, o.AccountId, min(c.adress), sum(o.summa * o.sign) from oper o
left join customers c on c.id = o.CustId
group by o.CustId, o.AccountId
Итоги считаются по паре CustID, AccountId. А дальнейшие подитоги это не дело СУБД, легко решается на уровне генератора отчетов.
Но можно и извратиться для получения этих данных одним запросом.
но тут надо включать тяжелую артиллерию SQL - "WITH RECURSIVE"
Понятно. Про промежуточные итоги я написал выше, это выполняется генераторами. А join не увеличивает время выполнения запросов?
Все же "книга" это свойство "проводки". Наравне со счетом, организацией, суммой.
Все же "книга" это свойство "проводки".
Не совсем понял эту мысль. Можно подробнее?
Я опираюсь на такие определения: https://en.wikipedia.org/wiki/General_ledger и https://en.wikipedia.org/wiki/Ledger
В таблицу проводок добавляется поле LedgerID и главная книга это простой отчет
с остатком на начало, оборотами и остатком на конец
select LedgerID, AccountId,
sum(case when sign = 1 and date_oper < '01.01.2021' then summa else 0 end) debet_begin,
sum(case when sign = -1 and date_oper < '01.01.2021 then summa else 0 end) kredit_begin,
sum(case date_oper < '01.01.2021 then sign * summa else 0 end) summa_begin,
sum(case when sign = 1 and date_oper >= '01.01.2021' then summa else 0 end) debet_turnout,
sum(case when sign = -1 and date_oper >= '01.01.2021' then summa else 0 end) kredit_turnout,
sum(case date_oper >= '01.01.2021' then sign * summa else 0 end) sum_turnout,
sum(case when sign = 1 then summa else 0 end) debet_end,
sum(case when sign = -1 then summa else 0 end) kredit_end, sum(sign * summa) summa_end
from oper
where date_oper <= '31.01.2021'
group by LedgerID, AccountId
Еще раз про структуру таблицы "полупроводок" пусть будет OPER
Дата
ID счета
ID контрагента (склад/сотруник/покупатель/поставщик и т.д.)
Знак (1 для дебета, -1 для кредита)
Сумма
ID книги (в какой должна фигурировать проводка)
Пара (поле связывающее строку дебет со строкой кредит, в моем случае это поле одинаковое для двух строк)
Далее необязательные поля, но важныеID документа (у одного документа есть набор проводок)
Примечание (описание проводки)
У меня еще есть дополнительные поля, но для объяснения они пока не важны
Получение одной проводки из такой таблицы получается при помощи joinselect d.*, k.* from oper d
join oper k on k.para = d.para and k.sign = -1
where d.sign = 1
При такой структуре есть необходимость ускорять расчеты на начало периода. Т.к. весь учет происходит от начала истории. Есть разные способы. Я для себя изобрел следующий:
В таблицу OPER добавляется поле "ПРИЗНАК ОСТАТКА" у меня OST, для обычной проводки в этом поле 0.
Периодически (раз в день/неделю/месяц/квартал/год) происходит закрытие периода и в таблицу OPER добавляются текущие остатки по счетам/организациям. Но не виде проводки, а одиночные строки с полем OST = 1. И в отдельную таблицу вносится дата закрытия (у меня PERIOD)
В дальнейшем для получения отчетов с остатками на начало по таблице PERIOD находим ближайшую дату снизу к дате начала периода.
И отчет модернизируется до where (o.ost = 1 and o.date_oper = 'дата закрытия') or (o.ost = 0 and o.date_oper between 'дата закрытия' + 1 and 'конечная дата отчета')
Такая схема работает у меня очень давно и проблем с производительностью нет никаких
Empire ERP. Занимательная бухгалтерия: Аналитический учет, ч.1