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

Реализация таблиц и процедур для дерева

       Итак, сначала изменим таблицу ChartAcc ("План счетов"):

Create table CHARTACC (
   AccntNum     CRDB NOT NULL PRIMARY KEY,
   AccPARNT     CRDB,   AccCount   AZInt32D0,  AccOrder   AZInt32D0,
   AccBalns     CRDB,   AccBlCnt   AZInt32D0,  AccBlOrd   AZInt32D0,
   AccProft     CRDB,   AccPfCnt   AZInt32D0,  AccPfOrd   AZInt32D0,
   AccTITLE     AZTITLE,
   ACDESCRP     AZNOTES);
Commit;

       Поле AccntNum содержит номер счета и имеет формат Char(8), что позволяет использовать любой вид нумерации счетов, сохраняя при этом читаемость всех документов. Использование во многих программах, в том числе 1С, Фрегат и др., числовой нумерации приводит к многочисленным проблемам:
       — при многих уровнях детализации невозможно запомнить, что означает номер 37 на пятом уровне.
       — Обещание разработчиков этих программ — "Вы можете использовать счета с любым уровнем вложенности" приводит к тому, что создаются абсолютно не читаемые номера счетов типа — 10.03.23.01 и т.д. Так в программе "Фрегат" в течение 2 минут удалось создать номер счета 5 уровня длинной 136 символов. Никаких ошибок программа не обнаружила. Но этого мало. Если попробовать напечатать "Журнал Хозяйственных Операций" с такими счетами или оборотную ведомость, то на стандартный лист бумаги такой отчет не помещается. Правда в программе "1С-Бухгалтерия" есть стандартный способ решения таких проблем — уменьшение размера шрифта. Если для разработчика такой способ является наиболее удобным, то для бухгалтера он неприемлемым. Вероятность ошибки при чтении документов с микроскопом увеличивается в десятки раз. Первое требование к бухгалтерским документам — удобочитаемость.
       Деревья строятся стандартным способом — Родительское поле, Счетчик потомков, Порядок сортировки для каждого дерева.

Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                              AccOrder,AccBlOrd,AccPfOrd,AccTitle)
Values('.','.','.','.',-10,-10,-10,'Корень дерева');
Commit;

       Необходимость специального поля для сортировки вызвано тем, что сортировка элементов внутри ветки далеко не всегда выполняется по алфавиту.
       До установки связей в таблице, заполняется первая строка для того чтобы исключить использования пустых (Null) полей. Параметр сортировки -10 заставляет эту строку быть всегда наверху.

ALTER TABLE CHARTACC add foreign key (AccPARNT) references CHARTACC
                         on UPDATE cascade;
ALTER TABLE CHARTACC add foreign key (AccBalns) references CHARTACC
                         on UPDATE cascade;
ALTER TABLE CHARTACC add foreign key (AccProft) references CHARTACC
                         on UPDATE cascade;
Commit;

       После чего можно установить связи, которые определяют древовидную структуру.
       Триггеры Insert, Update и Delete для изменения счетчиков потомков, также стандартные.
       Триггер, запускаемый при вставке, должен увеличить количество потомков у своего родителя. Так как в данном случае три дерева, то и родителей тоже три.

SET TERM !! ;
CREATE TRIGGER CHARTACC_INSERT FOR CHARTACC
ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
   Update ChartAcc a set a.AccCount = a.AccCount+1
          where a.AccntNum=new.AccParnt;
   Update ChartAcc a set a.AccBlCnt = a.AccBlCnt+1
          where a.AccntNum=new.AccBalns;
   Update ChartAcc a set a.AccPfCnt = a.AccPfCnt+1
          where a.AccntNum=new.AccProft;
END !!
SET TERM ; !!
Commit;

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

SET TERM !! ;
Create trigger CHARTACC_UPDATE for CHARTACC
ACTIVE BEFORE UPDATE POSITION 0 AS
BEGIN
If (old.AccParnt<>new.AccParnt) then
    begin
      Update ChartAcc a set a.AccCount = a.AccCount+1
             where a.AccntNum=new.AccParnt;
      Update ChartAcc a set a.AccCount = a.AccCount-1
             where a.AccntNum=old.AccParnt;
    End
If (old.AccBalns<>new.AccBalns) then
    begin
      Update ChartAcc a set a.AccBlCnt = a.AccBlCnt+1
             where a.AccntNum=new.AccBalns;
      Update ChartAcc a set a.AccBlCnt = a.AccBlCnt-1
             where a.AccntNum=old.AccBalns;
    End
If (old.AccProft<>new.AccProft) then
    begin
      Update ChartAcc a set a.AccPfCnt = a.AccPfCnt+1
             where a.AccntNum=new.AccProft;
      Update ChartAcc a set a.AccPfCnt = a.AccPfCnt-1
             where a.AccntNum=old.AccProft;
    End
END !!
SET TERM ; !!
Commit;

       В триггере, запускаемом при удалении, нужно уменьшить счетчик потомков у родителя.

SET TERM !! ;
CREATE TRIGGER CHARTACC_DELETE FOR CHARTACC
ACTIVE AFTER DELETE POSITION 0 AS
BEGIN
   Update ChartAcc a set a.AccCount = a.AccCount-1
          where a.AccntNum=old.AccParnt;
   Update ChartAcc a set a.AccBlCnt = a.AccBlCnt-1
          where a.AccntNum=old.AccBalns;
   Update ChartAcc a set a.AccPfCnt = a.AccPfCnt-1
          where a.AccntNum=old.AccProft;
END !!
SET TERM ; !!
Commit;

       (Обратите внимание, что во всех триггерах при обращении к таблице используется псевдоним a, а для полей в триггере используется уточнитель new или old. Это сделано для того, чтобы SQL-сервер не перепутал изменяемые поля в Update и поля таблицы в контексте триггера)

       Данная реализация отслеживания количества дочерних элементов приводит к тому, что одновременное добавление двух элементов к одному родителю приведет к блокировке (deadlock) обновления родительской записи. В много пользовательской среде такую ситуацию надо предусматривать — например, стараться делать добавление/удаление или перенос элементов в максимально коротких транзакциях read_committed, а при возникновении блокировки попытаться еще раз выполнить операцию без вывода сообщения об ошибке пользователю.
       Однако, большинство древовидных структур — это "справочники для чтения", просто потому что именно древовидная структура облегчает поиск элемента при отсутствии точной информации о его значении.

Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                              AccOrder,AccBlOrd,AccPfOrd,AccTitle,AcDescrp)
Values('AccBase','.','.','.',-3,-3,-3,'Основной план счетов','');
Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                              AccOrder,AccBlOrd,AccPfOrd,AccTitle,AcDescrp)
Values('Balans','.','.','.',-2,-2,-2,'Баланс предприятия','');
Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                              AccOrder,AccBlOrd,AccPfOrd,AccTitle,AcDescrp)
Values('Profit','.','.','.',-1,-1,-1,'Отчет о прибылях и убытках','');
Commit;

       Начальные записи в таблице предназначены для построение корней трех деревьев. Дальнейшее заполнение происходит из скрипта находящиегося в файле dat\ChartAcc2003.txt. Например:

Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                     AccOrder,AccBlOrd,AccPfOrd,AccTitle)
Values('41-00','Товары','Бл.214','Пр.020',41,0,0,'Счет 41. Товары');

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

В приведенной строке:
       — '41-00' - номер счета,
       — 'Товары' - группа, в которую он входит в "Плане счетов",
       — 'Бл.214' - номер строки в "Балансе", на которой он учитывается,
       — 'Пр.020' - номер строки в "Отчеты о Прибылиях", на которой он учитывается.
       Для ссуммирования по дереву необходимо знать уровень узла относительно вершины и узел, находящийся на определенном уровне. Для этого можно воспользоваться хранимой процедурой AccLevel.
       Процедура AccLevel выполняет следующие функции:
       — Если параметр ILev>=0 то возвращается уровень счета от вершины дерева, с учетом начального значения параметра.
       — Если параметр ILev<0 то возвращается название счета на ILev уровня выше текущего.
       Параметр Tree определяет номер дерева, по которому выполняется поиск.

set term !! ;
create procedure AccntLevel (Accnt char(8), ILev Integer, Tree Integer)
                  returns (AccPrnt Char(8), AccLev Integer) as
begin
AccLev = ILev + 1;
If (Tree=0) then select AccParnt from ChartAcc where AccntNum=:Accnt
                                 into :AccPrnt;
If (Tree=1) then select AccBalns from ChartAcc where AccntNum=:Accnt
                                 into :AccPrnt;
If (Tree=2) then select AccProft from ChartAcc where AccntNum=:Accnt
                                 into :AccPrnt;
   If (AccLev=0) then suspend;
       else begin
   If (:AccPrnt <> '.')
      then begin 
           select AccPrnt, AccLev from AccntLevel(:AccPrnt,:AccLev,:Tree)
            into :AccPrnt,:AccLev;
           suspend;
        end
      else suspend;
   end
end !!
set term ; !!
Commit;

       Работа процедуры очень проста.
       Первая строка увеличивает счетчик уровней на единицу.
       Далее в переменную AccPrnt записывается значение родительского поля по дереву, которое определяется переменной Tree.
       Если значение AccLev=0, то выполнение процедуры завершается и возвращается результат в виде переменной AccPrnt.
       В противном случае продолжается поиск — если AccPrnt<>'.', т.е. если это не вершина дерева, то обращаемся снова к процедуре AccLevel. Если достигли вершины дерева, то выполнение процедуры завершается.
       И, наконец, в таблицу TurnSheet необходимо добавить поле для группировки по уровню счета:

Create table TURNSHEET (
       TRNAccnt    CRDB not null, -- номер счета
       TRAParnt    CRDB,          -- номер счета для группировки

       TCls        AZInt32 not null,
       TRALev      AZInt32,
       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;

       Так как оборотная ведомость будет рассматриваться не только в прямом, но и преобразованном (ссуммированом) виде, то результат лучше смотреть через представление, которое группирует строки по полю TrAParnt и ссуммирует их. Поле TrAParnt будет изменяться процедурой TurnSum в зависимости от вида и уровня дерева, по которому происходит ссуммирование.

Create view TurnSheetView
    (Accnt,SaldoBeg,TurnCrd,TurnDbt,SaldoEnd,DateBeg,DateEnd)
as
Select TrAParnt,SUM(MSldBeg),SUM(MTrnCrd),SUM(MTrnDbt),SUM(MSldEnd),
       Min(TRNDTBeg),Min(TRNDTEnd)>
  from TurnSheet
 Group by TrAParnt;
Commit;

       А также необходимо несколько изменить хранимую процедуру TurnSheetCore, как показано ниже (изменения показаны синим жирным шрифтом):

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,TRAParnt,TCls,
             MSldBeg,MTrnCrd,MTrnDbt,MSldEnd,TRNDtBeg,TRNDtEnd)
Select TRNAccnt,TRNAccnt as TRAParnt,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;

       Таким образом, теперь результаты работы процедуры будут правильно отображаться через представление TurnSheetView, даже при отсутствии ссуммирования.
       И, наконец, можно заняться ссуммированием.
       Хранимая процедура TurnSum предназначена для ссуммирования оборотной ведомости используя различные деревья и глубину. Задача процедуры простая — записать в поле TrAParnt номер счета, который имеет уровень, заданный в переменной Lev, в дереве, заданный в переменной Tr.

SET TERM !! ;
CREATE PROCEDURE TurnSum (Tr SmallInt, Lev SmallInt) as
declare variable  TAcc Char(8);  -- Текущий Счет
DECLARE VARIABLE  TLev SmallInt; -- Текущий уровень
DECLARE VARIABLE  TDLv SmallInt; -- Разница уровней
BEGIN
01 Update TurnSheet set TRAParnt=TRNAccnt;
02 for select all TrnAccnt from TurnSheet order by TrnAccnt into :TAcc
03    do
04      begin
05         Select AccLev from AccLevel(:TAcc,0,:Tr) into :TLev;
06         TDLv = Lev - TLev;
07         IF (TDLv<0) THEN
08               Update TurnSheet set
09                TrAParnt=(select AccPrnt from AccLevel(:TAcc,:TDLv,:Tr))
10                      where TrnAccnt=:TAcc;
11            ELSE
12               Update TurnSheet set TrAParnt=:TAcc where TrnAccnt=:TAcc;
13      end
END !!
SET TERM ; !!
Commit;

       Строка 01 приводит таблицу TurnSheet в первоначальное состояние.
       Со строки 02 начинается просмотр всей таблицы TurnSheet и выполнения для каждой строки необходимых действий.
       Строка 05 с помощью процедуры AccLevel находит уровень текущего счета в заданном дереве.
       Строка 06 получает разницу уровней между текущим и заданным.
       Строка 07 — если разница уровней отрицательна, то с помощью процедуры AccLevel находим номер родительского счета в заданном дереве на заданном уровне (строка 09) и записываем его в поле TrAParnt (строка 08) для текущей строки.
       В противном случае записываем в поле TrAParnt текущее значение поля TrnAccnt.
       Теперь можно посмотреть на результат.
       Для получения оборотной ведомости выполним команду:

Execute procedure TurnSheetCore('11/1/2006','12/1/2006');
Commit;

       И посмотрим результат:

Select Accnt,SaldoBeg,TurnCrd,TurnDbt,SaldoEnd from TurnSheetView;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND
======== ================ ================ ================ ================
01-Киоск           0.0000           0.0000         508.4700         508.4700
04-00              5.0000           0.0000           0.0000           5.0000
08-Киоск           0.0000        -600.0000         600.0000           0.0000
10-МтСтр           0.0000        -600.0000         600.0000           0.0000
19-НДС09           2.3100         -30.9600         117.6900          89.0400
19-НДС18           0.0000         -91.5300         183.0600          91.5300
26-Банк            0.0000           0.0000          10.0000          10.0000
41-Питан           0.0000       -1424.9900        1425.0000           0.0100
42-Питан         -49.5500        -653.6900           0.0000        -703.2400
50-Текущ          59.0100       -2190.0000        2137.5000           6.5100
51-Б001            0.0000       -1660.0000        1815.0000         155.0000
60-00              0.0000       -2035.0000        2035.0000           0.0000
62-00              0.0000       -2137.5000        2137.5000           0.0000
68-НДС09          -6.7700        -176.5100          30.9600        -152.3200
75-ВБртн           0.0000           0.0000           0.0000           0.0000
75-ВКрло           0.0000           0.0000           0.0000           0.0000
80-00            -10.0000           0.0000           0.0000         -10.0000
90-00              0.0000       -2137.5000        2137.5000           0.0000

       Теперь выполним ссуммирование по первому дереву (План счетов) по уровеню 3 и посмотрим что получилось:

Execute procedure TurnSum(0,3);
Commit;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND
======== ================ ================ ================ ================
01-00              0.0000           0.0000         508.4700         508.4700
04-00              5.0000           0.0000           0.0000           5.0000
08-00              0.0000        -600.0000         600.0000           0.0000
10-00              0.0000        -600.0000         600.0000           0.0000
19-00              2.3100        -122.4900         300.7500         180.5700
26-00              0.0000           0.0000          10.0000          10.0000
41-00              0.0000       -1424.9900        1425.0000           0.0100
42-00            -49.5500        -653.6900           0.0000        -703.2400
50-00             59.0100       -2190.0000        2137.5000           6.5100
51-00              0.0000       -1660.0000        1815.0000         155.0000
60-00              0.0000       -2035.0000        2035.0000           0.0000
62-00              0.0000       -2137.5000        2137.5000           0.0000
68-00             -6.7700        -176.5100          30.9600        -152.3200
75-00              0.0000           0.0000           0.0000           0.0000
80-00            -10.0000           0.0000           0.0000         -10.0000
90-00              0.0000       -2137.5000        2137.5000           0.0000

       Получилась таблица, соответствующая плану счетов без субсчетов. Однако директору можно подавать более краткую информацию — ссуммированную по уровню 2.

Execute procedure TurnSum(0,2);
Commit;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND
======== ================ ================ ================ ================
Активы             5.0000        -600.0000        1108.4700         513.4700
Деньги            59.0100       -3850.0000        3952.5000         161.5100
Запасы             2.3100        -722.4900         900.7500         180.5700
Затраты            0.0000           0.0000          10.0000          10.0000
Капитал          -10.0000           0.0000           0.0000         -10.0000
Расчеты           -6.7700       -4349.0100        4203.4600        -152.3200
Товары           -49.5500       -2078.6800        1425.0000        -703.2300
ФинРез.            0.0000       -2137.5000        2137.5000           0.0000

       Такой объем информации можно даже передавать по SMS.

       Теперь проссуммируем оборотную ведомость по второму дереву ("Баланс") по уровню 4:

Execute procedure TurnSum(1,4);
Commit;
Select * from TurnSheetView
where SaldoBeg<>0.0 and SaldoEnd<>0.0;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND
======== ================ ================ ================ ================
Бл.110             5.0000           0.0000           0.0000           5.0000
Бл.220             2.3100        -122.4900         300.7500         180.5700
Бл.260            59.0100       -3850.0000        3952.5000         161.5100
Бл.410           -10.0000           0.0000           0.0000         -10.0000
Бл.620            -6.7700       -2211.5100        2065.9600        -152.3200

       В результате, если взять только колонки SaldoBeg и SaldoEnd, то заполнение баланса сводится к переписыванию значений в соответствующие столбики "Бухгалтерского баланса" в строках, которые указаны в колонке Accnt.
       Если проссуммировать оборотную ведомость по третьему дереву ("Отчет о прибылях")

Execute procedure TurnSum(2,3);
Commit;
Select * from TurnSheetView
where TurnCrd<>0.0 and TurnDbt<>0.0;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND
======== ================ ================ ================ ================
Пр.020             0.0000       -1424.9900        1425.0000           0.0100

 

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




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

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


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