Правильная ссылка на эту страницу
http://az-design.ru/Projects/AzBook/AZMicro/AZMicro20.shtml

Построение базы данных для AZMicro

       Исходя из вышеизложенной системы учета, все что нам нужно — это "Журнал хозяйственных операций". Многочисленные разработки бухгалтерских программ, имеющиеся на рынке, декларируют что они отталкиваются от документа, так как бухгалтер имеет дело с документами — накладными, счетами и др. На самом деле они пытаются работать с правилами, которые не опираются на надежный фундамент. Такие правила разрозненны сами по себе и с большой вероятностью допускают ошибки пользователей.
       "Журнал операций" — это то же что и ассемблер для программиста. Это набор элементарных операций, которыми можно описать ВСЕ финансовые события на предприятии. Документ часто генерирует несколько (тысяч) элементарных операций. Таким образом документы можно рассматривать как макро-команды, или операторы языка высокого уровня.
       Таблица журнала операций очень проста:

Create table Journal
    (JrnID      AZInt32 not null primary key, -- номер хозяйственной операции
     JrnDate    Date not null,                -- дата проведения хозяйственной операции
     JrCredit   CRDB,                         -- счет по кредиту
     JrDebit    CRDB,                         -- счет по дебиту
     JrAmount   AZMoney not null,             -- сумма хозяйственной операции
     JrLegend   AZLegend);                    -- описание хозяйственной операции
Commit;

       Примечание. Здесь и далее типы данных будут указываться в определении соответствующих доменов. Домены описаны в начале скрипта создания БД. Конец примечания.

       JrnID — задает номер хозяйственной операции и одновременно является основным ключем записи. Оно не должно быть пустым, на что указывает not null, и должно быть уникальным, что обеспечивает первичный ключ — Primary Key. В журнале хозяйственных операций невозможно выделить какой-либо набор признаков, по которому можно сформировать Primary Key. Дело в том что никто не мешает (в хозяйственной деятельности) сделать две или более одинаковых операции в течение одного дня. В то же время порядок ввода данных может не соответствовать действительному порядку хозяйственных операций. Формат — целое 32-разрядное число.
       JrnDate — дата регистрации хозяйственной операции. Также невозможно определить этот параметр более точно, так как не все источники, например банки, могут дать более точную информаци. Формат — date.
       JrCredit, JrDebit — номера счетов, которые определяются как текстовое поле размером 8 символов.
       JrAmount — сумма операции, выраженное как действительное число с точностью 4 знака после запятой. Несмотря на то, что все финансовые данные представляются с двумя значками после запятой (копейки, центы и др.) расчеты в бухгалтерии принято производить с точностью 4 знака после запятой. Обясняется это просто — накапливающаяся ошибка округления очень редко переходит через знак. Поэтому 2 запасных знака оказывается достаточным.
       JrLegend — описание операции. Это простое текстовое поле, в которое пользователь заносит текст в свободной форме.
       Все бы было хорошо, но встретились такие поля как JrCredit и JrDebit. Конечно, можно позволить пользователю вводить в них любые значения по мере необходимости. Ошибочные значения потом легко выявляются и менее легко исправляются. Но лучше навести в этом деле порядок. Для начала создадим таблицу, которая содержит правильные значения номеров счетов. Их значения могут быть взяты из справочника, который называется "План счетов бухгалтерского учета", утверждаемый министерством финансов.

Create table CHARTACC (
   AccntNum     CRDB NOT NULL PRIMARY KEY, -- номер счета
   AccTITLE     AZTITLE,                   -- название счета
   AcDescrp     AZNOTES);                  -- описание счета
Commit;

       AccntNum — номер счета. Это текстовое поле из 8 символов следующего вида "ХХ-ХХХХХ". Первые два символа соответствуют номеру из "Плана Счетов...", а последние 5 символов определяются бухгалтером. В отличие от самого "Плана Счетов..." и многисленных реализаций бухгалтерских программ, такая реализация переносима между бухгалтерами, более информативна и защищает от ошибок. Например, в "Плане Счетов..." предложены субсчета:
       19-1 "Налог на добавленную стоимость при приобретении основных средств";
       19-2 "Налог на добавленную стоимость по приобретенным нематериальным активам";
       19-3 "Налог на добавленную стоимость по приобретенным материально-производственным запасам".
       А на самом деле к этим субсчетам необходим раздельный учет НДС 20% и НДС 10%, которые многие бухгалтеры обозначают как 19-4, 19-5. При этом бухгалтеры, у которых эти обозначения другие, начинают путаться.
       При текстовом обозначении эти счета можно обозначить как:
       19-Основ — "Налог на добавленную стоимость при приобретении основных средств";
       19-Немат — "Налог на добавленную стоимость по приобретенным нематериальным активам";
       19-Запас — "Налог на добавленную стоимость по приобретенным материально-производственным запасам".
       19-НДС20 — Налог на добавленную стоимость 20%
       19-НДС10 — Налог на добавленную стоимость 10%
       А когда ставки изменились, то появились новые счета 19-НДС18, 19-НДС09.
       AccTitle — наименование счета. Утвержадется МинФином и/или главным бухгалтером.
       AcDescrp — описание счета. Вспомогательное поле, которое несет справочную информацию по использованию счета.
       А теперь можно исправить таблицу "Журнала операций", наложив ссылочное ограничение, что значения полей JrCredit, JrDebit должны присутствовать в таблице ChartAcc:

Create table Journal
    (JrnID      AZInt32 not null primary key, -- номер хозяйственной операции
     JrnDate    Date not null,                -- дата проведения хозяйственной операции
     JrCredit   CRDB references ChartAcc on update cascade,
     JrDebit    CRDB references ChartAcc on update cascade,
     JrAmount   AZMoney not null,             -- сумма хозяйственной операции
     JrLegend   AZLegend);                    -- описание хозяйственной операции
Commit;

       Конечно, накладывать ограничения можно с помощью операторов:

Alter table Journal add foreign key (JrCredit) references ChartAcc on UPDATE cascade;
Alter table Journal add foreign key (JrDebit)  references ChartAcc on UPDATE cascade;


       Но первый способ более наглядный и читать такое описание таблицы более удобно. Еще одно достоинство первого способа в том, что таблица, на которую ссылается поле, уже должна быть создана, что уменьшает количество ошибок.
       Второй способ больше предназначен для компьютеров, чем для людей. Так, например, формируется скрипт при экспорте метаданных БД с помощью различных инструментов визуального проектирования типа IBExpert, ErWin. В скрипт выводятся сначала описания всех таблиц, а затем всех ограничений. При большом количестве таблиц разобраться в структуре БД очень сложно.
       Кроме того, поле JrnID необходимо заполнять автоматически, для чего воспользуемся генератором последовательных чисел.

CREATE GENERATOR Journal_GEN;       -- создает генератор с именем Journal_GEN
   SET GENERATOR Journal_GEN TO 0;  -- устанавливает его начальное значение в 0
Commit;

       А для заполнения поля JrnID используется триггер, который запускается "до вставки записи". Если новое значение поля есть Null (пустое), то вставляется следующее значение генератора.

Set term !! ;
Create trigger Journal_Insert for Journal
active before insert position 0
as
Begin
   If (new.JrnID is null) then new.JrnID = GEN_ID(Journal_GEN,1);
end !!
Set term ; !!

 

Таблицы для отчетов

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

Оборотная ведомость
       Самое просто построить "Оборотную ведомость", таблица для которого очень проста:

Create table TURNSHEET (
       TRNAccnt    CRDB not null references ChartAcc on update cascade,
       TCls        AZInt32 not null,
       MSldBeg     AZMoney default 0.0, -- сальдо на начало периода
       MTrnCrd     AZMoney default 0.0, -- оборот по кредиту
       MTrnDbt     AZMoney default 0.0, -- оборот по дебиту
       MSldEnd     AZMoney default 0.0, -- Сальдо на конец периода
       TRNDTBeg    Date,    -- дата начала периода
       TRNDTEnd    Date,    -- дата конца периода
Primary key (TRNAccnt,TCls));
Commit;

       Значения всех полей понятны сами по себе, кроме одного — TCls, назначение которого будет объяснено позднее.
       Заполнение этой таблицы происходит с помощью 4 с половиной запросов следующим образом:
       1) Нужно получить сумму всех операций по кредиту до начала периода:

Insert into TurnSheet(TRNAccnt,TCLs,MSldBeg,MSldEnd)
Select JrCREDIT,1,-SUM(JrAmount),-SUM(JrAmount)
  from Journal
 where JrnDATE<:DTBEG
 group by JrCREDIT;

2) потом нужно получить такую же сумму всех операций по дебиту до начала периода:

Insert into TurnSheet(TRNAccnt,TCLs,MSldBeg, MSldEnd)
Select JrDEBIT,2,SUM(JrAmount),SUM(JrAmount)
  from Journal
 where JrnDATE<:DTBEG
 group by JrDEBIT;

       3) затем получается сумма всех операций по кредиту за указанный период:

Insert into TurnSheet(TRNAccnt,TCLs,MTrnCrd, MSldEnd)
Select JrCREDIT,3,-SUM(JrAmount),-SUM(JrAmount)
  from Journal
 where (JrnDATE>=:DTBEG AND JrnDATE<:DTEND)
 group by JrCREDIT;

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

Insert into TurnSheet(TRNAccnt,TCLs,MTrnDbt, MSldEnd)
Select JrDEBIT,4,SUM(JrAmount), SUM(JrAmount)
  from Journal
 where (JrnDATE>=:DTBEG AND JrnDATE<:DTEND)
 group by JrDEBIT;

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

Insert into TurnSheet(TrnAccnt,TCls,
       MSldBeg,     MTrnCrd,     MTrnDbt,     MSldEnd,     TRNDtBeg,TRNDtEnd)
Select TRNAccnt,0,
       Sum(MSldBeg),Sum(MTrnCrd),Sum(MTrnDbt),Sum(MSldEnd),:DtBeg,:DtEnd
  from TurnSheet
  where TCls>0
  group by TRNAccnt;

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

  Delete from TurnSheet where TCls>0;

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

SET TERM !! ;
CREATE PROCEDURE TurnSheetCore (
       DtBeg    DATE,     -- Дата начала периода
       DtEnd    DATE)     -- Дата конца периода
as
Begin
  Delete from TurnSheet;
  Insert into TurnSheet(TRNAccnt,TCLs,MSldBeg,MSldEnd)
       Select JrCREDIT,1,-SUM(JrAmount),-SUM(JrAmount)
         from Journal  where JrnDATE<:DTBEG
        group by JrCREDIT;
  Insert into TurnSheet(TRNAccnt,TCLs,MSldBeg, MSldEnd)
       Select JrDEBIT,2,SUM(JrAmount),SUM(JrAmount)
        from Journal  where JrnDATE<:DTBEG
       group by JrDEBIT;
  Insert into TurnSheet(TRNAccnt,TCLs,MTrnCrd, MSldEnd)
       Select JrCREDIT,3,-SUM(JrAmount),-SUM(JrAmount)
         from Journal
        where (JrnDATE>=:DTBEG AND JrnDATE<:DTEND)
        group by JrCREDIT;
  Insert into TurnSheet(TRNAccnt,TCLs,MTrnDbt, MSldEnd)
       Select JrDEBIT,4,SUM(JrAmount), SUM(JrAmount)
         from Journal
        where (JrnDATE>=:DTBEG AND JrnDATE<:DTEND)
        group by JrDEBIT;
Insert into TurnSheet(TrnAccnt,TCls,
             MSldBeg,MTrnCrd,MTrnDbt,MSldEnd,TRNDtBeg,TRNDtEnd)
Select TRNAccnt,0,
       Sum(MSldBeg),Sum(MTrnCrd),Sum(MTrnDbt),Sum(MSldEnd),:DtBeg,:DtEnd
  from TurnSheet
  where TCls>0
  group by TRNAccnt;
  Delete from TurnSheet where TCls>0;
END !!
SET TERM ; !!
Commit;

       Для обеспечения правильных результатов процедура начинается с очистки таблицы TurnSheet.

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

Create table LEDGER (
       LdID       AZInt32 not null primary key,
       LdAccnt    CRDB     references CHARTACC on update cascade,
       LdDate     Date,
       LdJrnl     AZInt32  references Journal  on update cascade,
       LdCorAcc   CRDB     references CHARTACC on update cascade,
       LdMCrd     AZMoney,
       LdMDbt     AZMoney,
       LdMSld     AZMoney);
Commit;

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

CREATE GENERATOR Ledger_GEN;
   SET GENERATOR Ledger_GEN TO 0;
Commit;
Set term !! ;
Create trigger Ledger_Insert for Ledger
active before insert position 0
as
Begin
   If (new.LdID is null) then new.LdID = GEN_ID(Ledger_GEN,1);
end !!
Set term ; !!

       Поле LdAccnt содержит номер счета, по которому создается главная книга. Его значение будет одинаковым во всех записях.
       Поля LdDate и LdJrnl содержат дату хозяйственной операциий и ее номер в журнале операций. Это необходимо для того, чтобы из главной книги можно было уточнить суть операции. Ссылочное ограничение наложенное на поле LdJrnl гарантирует, что значения в этом поле будут соответствовать значениям в поле JrnID в таблице Journal.
       Поле LdCorAcc содержит номер корреспондирующего счета. Этот счет может быть кредитовым или дебитовым, и процедура должна в соответствии с ним вычитать или прибавлять сумму операции от/к итоговой сумме.
       И, наконец, поля LdMCrd и LdMDbt содержат сумму хозяйственной операции, а LdMSld — итоговую сумму после выполнения текущей операции.
       Хранимая процедура LedgerNone, которая будет заполнять эту таблицу имеет три параметра — Accnt (счет, по которому получается журнал), DtBeg (дата начала периода), DtEnd (дата конца периода). Далеко не всегда нужно иметь все записи "от Рождества Христова" и всегда возможны записи сделанные впрок за пределами конца периода.

Set term ^ ;

Create procedure LedgerNone( Accnt char(8), -- счет, по которому получается журнал DtBeg Date, -- Дата начала периода DtEnd Date -- Дата конца периода ) as Declare variable LdCrd DECIMAL(18,4); Declare variable LdDbt DECIMAL(18,4); Declare variable Sld DECIMAL(18,4); Declare variable Amnt DECIMAL(18,4); Declare variable JID Integer; Declare variable JrCrd Char(8); Declare variable JrDbt Char(8); Declare variable JDate Date; Begin Delete from Ledger;

       После удаления из таблицы Ledger предыдущих результатов, определяется начальное значение итоговой суммы. Для этого с помощью двух ссуммирующих запросов получаются в переменной LdCrd обороты по кредиту и в переменной LdDbt обороты по дебету на начало заданного периода. Ключевое слово All нужно для того, чтобы агрегатная функция SUM использовала все записи журнала операций, в том числе и повторяющиеся.

   Select all SUM(JrAmount) from Journal
   where JrCredit=:Accnt and JrnDate<:DtBeg into :LdCrd;
   Select all SUM(JrAmount) from Journal
   where JrDebit=:Accnt and JrnDate<:DtBeg into :LdDbt;
   Sld = LdDbt-LdCrd;
   If (Sld is Null) then Sld = 0.0;

       Сальдо (Sld) это и есть итоговая сумма, которая получается как разница между оборотами по дебиту и кредиту. Ее обязательно нужно проверить на Null, так как до начала периода может вообще не быть операций, и тогда ей нужно присвоить значение 0.0.

   Insert into Ledger(LdAccnt,LdDate,LdCorAcc,LdMSld)
               values(:Accnt,:DtBeg,:Accnt,:Sld);

       После вставки первой записи, которая отражает сальдо на начало периода можно заняться вычислением всей главной книги.
       Для этого строиться запрос, который выбирает все записи в журнале операций за указанный период и сортирует их по датам и номеру записи в журнале. Затем результат запроса просматривается в цикле построчно и определяется к какому типу принадлежит анализируемый счет в данной записи. Если Accnt записан как кредит, то строиться один оператор Insert, если как дебит, то строиться другой оператор Insert.

   For Select JrnID,JrnDate,JrCredit,JrDebit,JrAmount from Journal
       where JrnDate>=:DtBeg and JrnDate<:DtEnd
       order by JrnDate,JrnID
       into :JID,:JDate,:JrCrd,:JrDbt,:Amnt
       do 
         begin
            If (JrCrd=Accnt) then
               Begin
                  Sld = Sld - Amnt;
                  Insert into Ledger(LdAccnt,LdDate,LdJrnl,LdCorAcc,LdMCrd,LdMSld)
                              values(:Accnt,:JDate,:JID,:JrDbt,:Amnt,:Sld);
               end
            If (JrDbt=Accnt) then
               Begin
                  Sld = Sld + Amnt;
                  Insert into Ledger(LdAccnt,LdDate,LdJrnl,LdCorAcc,LdMDbt,LdMSld)
                              values(:Accnt,:JDate,:JID,:JrCrd,:Amnt,:Sld);
               end
         end
end^
Set term ; ^

       После завершения цикла Главная Книга (журнал по счету) готова.

Сборка базы данных

       Теперь можно собрать саму БД. Для этого в каталоге AZMicro\DB найти файл AZMicro.doc, который содержит SQL-скрипт для построения БД, включающий комментарии и оформление, открыть его в программе MS Word и выбрать в меню "Файл|Сохранить как" сохранить его в том же каталоге в формате *.txt. После чего запустить командный файл Filleng.bat? текст которого показан ниже:

rem Построение БАЗЫ ДАННЫХ
rem
del /q AZMicro_r.txt, ..\AZMicro.fb
isql.exe -m -i AZMicro.txt -o AZMicro_r.txt
rem Заполнение ChartAcc и TurnSheet
del /q res\ChartAcc_r.txt
isql.exe -m -i dat\ChartAcc.txt -o res\ChartAcc_r.txt

       — первая строка удаляет результаты предыдущего построения и существующую БД, если она есть.
       — вторая строка запускается скрипт для построения БД и сохраняет протокол выполнения в файле AZMicro_r.txt
       — следующая команда удаляет результаты предыдущего выполнения срипта заполняющего таблицы
       — и, наконец, запускается скрипт dat\ChartAcc.txt, который заполняет все таблицы ранее сохраненными данными. Результат выполнения скрипта записывается в файл res\ChartAcc_r.txt

Архангельский Андрей




<<< Пред. Оглавление
Начало раздела
След. >>>

Дата последнего изменения:
Thursday, 21-Aug-2014 09:10:56 MSK


Постоянный адрес статьи:
http://az-design.ru/Projects/AzBook/AZMicro/AZMicro20.shtml