Правильная ссылка на эту страницу
http://az-design.ru/Support/DataBase/002b3300_en.shtml

The table "the Schedule of scores" in book keeping

Arkhangelskiy Andrey

Russian

Test laboratory

       "The schedule of scores of book keeping" is typically tree structure in which it is possible and it is necessary to use all properties treelike structures. I.e. not only tree search, but also ссуммирование with usage of a tree.
       For carrying out of experiments we shall create the small accounting system consisting of three tables:
       — ChartAcc - "the Schedule of scores of book keeping",
       — Journal - "Log of economic operations" (ЖХО),
       — TurnSheet - the Turnover sheet.
       As "the Schedule of Scores "we shall take" the Schedule of scores of book keeping of financial and economic activity of the organizations", утвежденный the Ministry of Finance of the Russian Federation in 2003г. The script for its filling is in a file dat\ChartAcc2003.txt.
       Somehow to show operation of the Schedule of Scores and the Turnover sheet, we shall create the organization (corporation) "Alphabet" with founders Daddy Karlo and Buratino which "Three gudgeons" start the economic activities from well-known purchase Buratino of three crusts of bread in a tavern. The script for filling ЖХО is in a file dat\Journal.txt.
       Cumulative result of activity of the corporation, grounded on log of economic operations, it is possible to look in the report which has received the name "Turnover sheet". What is such, is shown below:

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

       The first column [Accnt], contains numbers of scores (from "the Schedule of scores") on which were spent economic operations.
       The second column [SaldoBeg], shows "Balance" or the rest of money resources on the beginning of an accounting period.
       The third column [TurnCrd], shows turns under the Credit or how many money resources have been removed (has decreased) from the corresponding score for an accounting period.
       The fourth column [TurnDbt], shows turns under the Debit or how many money resources have been added (has arrived) on the appropriate score for an accounting period.
       The fifth column [SaldoEnd], shows "Balance" or the rest of money resources on the end of an accounting period.
       Thus for the competent person this tablet tells almost all about a financial state of the corporation for an accounting period.
       However, in tax bodies the turnover sheet, and "Accounting Balance" which is very similar to "turnover sheet" surrenders not, but, first on another groups scores, and secondly, in it there are no average columns with turns. The main body of "Accounting balance" is resulted below:

A C T I V E S Code On the beginning of an accounting period On the end of an accounting period
1 2 3 4
I. OUTSIDE ACTIVES      
Non-material actives [04,05] 110    
The main resources [01,02] 120    
Not completed construction [07,08,16,60] 130    
Profitable nestings in material assets [03,02] 135    
Long-term financial nestings [58,59] 140    
The postponed tax actives [09] 145    
Other outside actives 150    
Total on section: 190    
II. BACK ACTIVES      
Stores 210    
Including:      
- Raw material, materials and other values [10,14,15,16] 211    
- Animals on cultivation and откорме [11] 212    
- Expenses in a work in progress [14,20, 21, 23, 29, 44, 46] 213    
- Finished goods for resale [14,16,41,43] 214    
- The goods shipped [45] 215    
- Expenditures of the future periods [97] 216    
- Other stores and expenses 217    
The value-added tax on the gained values [19] 220    
Debts (payments on which are expected more than in 12 months after account date) 230    
Including buyers and customers [62,63,76] 231    
Debts (payments on which are expected within 12 months after account date) 240    
Including buyers and customers [62,63,76] 241    
Short-term financial nestings [58,59,81] 250    
Money resources [50,51,52,55,57] 260    
Other back actives 270    
Total on section: 290    
Balance 300    
       
P A S S I V E S Code On the beginning of an accounting period On the end of an accounting period
1 2 3 4
III. THE CAPITAL AND SPARES      
The Authorized capital [80] 410    
The own actions redeemed for shareholders 411    
The additional capital [83] 420    
The backup capital [82] 430    
- The spares derivated in correspondence with the legislation 431    
- The spares derivated according to constituent documents 432    
Unallotted profit (the uncovered loss) [84,99] 470    
TOTAL on section: 490    
IV. LONG-TERM OBLIGATIONS      
Loans and credits [67] 510    
The postponed tax obligations [77] 515    
Other long-term obligations 520    
TOTAL on section:      
V. SHORT-TERM OBLIGATIONS      
Loans and credits [66] 610    
Creditor debts 620    
Including      
- Suppliers and contractors [60,76] 621    
- Debts against staff of the organization [70] 622    
- Debts against the state unappropriated funds [69] 623    
- Debts under taxes and tax collections [68] 624    
- Other creditors 625    
Debts against participants (founders) on payment of incomes [75] 630    
Incomes of the future periods [98] 640    
Spares of forthcoming expenditures [96] 650    
Other short-term obligations 660    
TOTAL on section: 690    
BALANCE 700    

       Apparently, each string of "Balance" has number, and in the name of string is specified, what scores are included into the sum on this string. "Balance" has sections and will easily be transformed to the treelike form.
       Besides in tax bodies other report which the Report on profits and losses "is named" surrenders. This report also can be constructed in treelike sort and contains data from average columns of "Turnover sheet". The Main body "Reports about profits and losses" is resulted below:

The name Code of a metric For an accounting period
1 2 3
INCOMES AND EXPENDITURES ON USUAL SORTS OF ACTIVITY    
Proceeds (net) from sale of the goods, production, operations, services (behind a minus of the tax to the added cost, excises and similar mandatory payments) 010  
The cost price of the sold goods, production, operations, services 020  
Total profit 029  
Commercial expenditures 030  
Administrative expenditures 040  
Profit (loss) on sales 050  
OTHER INCOMES AND EXPENDITURES    
Percent to obtaining 060  
Percent to payment 070  
Incomes of involvement in other organizations 080  
Other incomes 090  
Miscellaneous costs 100  
PROFIT (LOSS) UP TO НАЛОГООБЛОЖЕНИЯ 140  
Postponed tax actives 141  
The postponed tax obligations 142  
The current profit tax 150  
     
NET PROFIT (LOSS) of the ACCOUNTING PERIOD 190  
СПРАВОЧНО    
Constant tax obligations (actives) 200  
Base profit (loss) on the action 210  
Разводненная profit (loss) on the action 220  

       In this report also each string has number, and the report has an obvious tree structure.
       Thus, if in table ChartAcc ("the Schedule of scores") to use three trees — "the Schedule of scores", "Balance" and "the Report on profits and losses" there is a possibility easily to automate some functions. After obtaining its turnover sheet it is possible summired on corresponding tree:
       — Summing on the first tree gives structure of incomes, expenditures, the rests for acceptance of administrative solutions. At any depth of detailing of scores the report for director will not exceed one page.
       — Summing on the second tree gives the ready report "Balance of firm"
       — Summing on the third tree gives ready " the Report on profits and losses"

Implementation of tables and procedures

       So, all over again we shall construct table ChartAcc ("the Schedule of scores"):

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;

       The AccntNum field contains number of the score and has format Char (8) that allows to use any kind of indexing of scores, saving thus readership of all documents. Usage in many programs, including 1С, the Frigate, etc., to numerical indexing leads to numerous problems:
       — At many levels of detailing it is impossible to remember that means number 37 at the fifth level.
       — Promising of developers of these programs — "you can use scores with any level of an enclosure" leads to that there are absolutely not not readable numbers of scores of type — 10.03.23.01, etc. So in the program the Frigate within 2 minutes was possible to create number of score 5 of a level of long 136 characters. The program has not found out any errors. But it a little. If to try to print out Log of Economic Operations with such scores or the turnover sheet on a standard page of a paper such report is not located. The General director of the corporation "Frigate" — Abasov Rauf Gulitvich — in private talk on it has answered, that "this brothel is made by the user and it should watch it". That not absolutely so. For that also there is a programmer that the user could not do nonsense. The truth in the program "1С-Бухгалтерия" is a standard way of solution of such problems — decrease of a font size. If for the developer such way is the most convenient, for the bookkeeper it unacceptable. The probability of an error at reading documents with a microscope increases in tens times. The first requirement to accounting documents — legibility.
       Trees are under construction in the standard way — the Parent field, the Counter of descendants, the Sort order for each tree.
       The AccTitle field contains the name of the score, and the AcDescrp field, if necessary, the description or the instruction on application of the score.

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

       Before installation of links in the table, the first string is filled to eliminate usages empty (Null) fields. The parameter of sorting-10 forces this string to be always above.

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;

       Then it is possible to install links which define a tree structure.
       Triggers Insert, Update and Delete for change of counters of descendants, also standard.

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;
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;

       Initial filling of the table occurs also as in table Goods (Goods) described above. The further filling occurs from a script находящиегося in a file dat\ChartAcc2003.txt. For example:

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

       In this case filling of a script very simple. As the identifier and a key field this one and too it is not required to search for the identifier by means of the nested inquiry. It is enough to enter directly a parent code of a corresponding tree if it already exists in the table.
       In the resulted string:
       — '41-00' - number of the score,
       — 'the Goods' - group into which it enters in "the Schedule of scores",
       — 'Бл.214' - a line number in "Balance" on which it is considered,
       — 'Пр.020' - a line number in "Reports about Profit" on which it is considered.
       Procedure of search of descendants of a current node is constructed in the same way, as well as in table Goods (Goods):

set term !! ;
create procedure AccntChild (AccPrnt char(8), Tree SmallInt)
       returns (AccChld char(8)) as
begin
IF (Tree=0) then begin
   for select AccntNum from ChartAcc
       where  AccPARNT=:AccPrnt  into :AccChld
       do begin
          suspend;
          for select AccChld 
                 from  AccntChild(:AccChld,:Tree) into :AccChld
          do begin 
             suspend;
             end
          end
   end
IF (Tree=1) then begin
   for select AccntNum from ChartAcc
       where  AccBalns=:AccPrnt into :AccChld
       do begin 
          suspend;
          for select AccChld 
                 from  AccntChild(:AccChld,:Tree) into :AccChld
          do begin 
             suspend;
             end
          end
   end
IF (Tree=2) then begin
   for select AccntNum from ChartAcc
       where  AccProft=:AccPrnt  into :AccChld
       do begin 
          suspend;
          for select AccChld 
                 from  AccntChild(:AccChld,:Tree) into :AccChld
          do begin 
             suspend;
             end
          end
   end
end !!
set term ; !!
Commit;

       For summing on a tree it is necessary to know a level of a node concerning top and the node which is being at a certain level. For this purpose stored procedure which earlier was not described is used.
       Procedure AccLevel fulfils following functions:
       — If parameter ILev> =0 that comes back a level of the score from top of a tree, in view of initial value of parameter.
       — If parameter ILev <0 that comes back the name of the score on ILev a level above current.
       Parameter Tree defines number of a tree on which search is fulfilled.

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;

       Operation of procedure is very simple.
       The first string increases the counter of levels by unit.
       Further in variable AccPrnt value of the parent field on a tree which is defined by variable Tree writes.
       If value AccLev=0 execution of procedure is completed and comes back result in the form of variable AccPrnt.
       Otherwise search — if AccPrnt <> 'proceeds.', i.e. if it not top of a tree it is accessed again to procedure AccLevel. If the top of a tree execution of procedure is completed have reached.
       For testing there is a table of the elementary log of economic operations:

Create table Journal
    (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;

       The log consists of several fields — Date of operation, the Score under the Credit, the Score on Дебиту, the Sum of operation and the description of operation. Basically it is possible to add any indexes but as the size of the table small, will be used it for testing various improvements and analytical tags were not used.
       And, at last, for storage of the turnover sheet table TurnSheet is used:

Create table TURNSHEET (
       TRNAccnt   CRDB not null,       -- number of the score
       TRAParnt   CRDB,                -- number of the score for classification
       TCls       AZInt32 not null,
       TRALev     AZInt32,
       MSldBeg    AZMoney default 0.0, -- balance on the beginning of the period
       MTrnCrd    AZMoney default 0.0, -- a turn under the credit
       MTrnDbt    AZMoney default 0.0, -- a turn under the debit
       MSldEnd    AZMoney default 0.0, -- Balance on the end of the period
       TRNDTBeg   Date,                -- a date started of the period
       TRNDTEnd   Date,                -- date of the end of the period
Primary key (TRNAccnt, TCls));
Commit;

       Except for quite obvious fields — TrnAccnt, MSldBeg, MTrnCrd, MTrnDbt, MSldEnd — the auxiliary fields are used some, assignment which will be clear in the further.

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;

       As the turnover sheet will be considered not only in direct, but also transformed (summing) sort the result is better for watching through representation which groups strings on TrAParnt field and summing them. The TrAParnt field will vary with procedure TurnSum depending on sort and a level of a tree on which occurs summing.
       And, at last, it is necessary to receive the turnover sheet. For this purpose the elementary procedure TuenSheetCore which as parameters accepts dates started and the end of an accounting period is used. Procedure consists of 4.5 inquiries:

SET TERM !! ;
CREATE PROCEDURE TurnSheetCore (
       DtBeg    DATE,     -- the Date started of the period
       DtEnd    DATE)     -- Date of the end of the period
as
Begin
  Delete from TurnSheet; -- deleting of the previous result
  Insert into TurnSheet(TRNAccnt,TCLs,MSldBeg,MSldEnd)
       Select JrCREDIT,1,-SUM(JrAmount),-SUM(JrAmount)
         from Journal  where JrnDATE<:DTBEG 
        group by JrCREDIT;

       — The sum of all operations under the Credit of scores in log of operations which have occured up to a date started of an accounting period, and result with the sign a minus is calculated writes in Balance of the beginning and the end of the period. For that тобы to distinguish what inquiry this or that string is received is used a tag in TCls floor which is installed by obvious image.

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

       — The sum of all operations under the Debit of scores in log of operations which have occured up to a date started of an accounting period is calculated, and the result with a plus symbol writes in Balance the beginning and the end of the period.

  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;

       — The sum of all operations under the Credit of scores in log of operations which have occured in current of an accounting period, and result with the sign a minus is calculated writes in column MTrnCrd as a turn under the Credit, and also in column MSldEnd as Balance of the end of the period.

  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;

       — The sum of all operations under the Debit of scores in log of operations which have occured in current of an accounting period is calculated, and the result with a plus symbol writes in column MTrnDbt as a turn on Дебиту, and also in column MSldEnd as Balance of the end of the period.

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;

       The final inquiry simply ссуммирует results of the previous inquiries saved in table TurnSheet also writes them in it with tag TCls=0. For help dates started and the end of the period from entry variables are added.

  Delete from TurnSheet where TCls>0;

       The last string simply deletes subproducts.

END !!
SET TERM ; !!
Commit;

       Thus, after execution of procedure TurnSheetCore in table TurnSheet the turnover sheet for the specified period was derivated. Now it is possible with it эксперименттировать.
       Stored procedure TurnSum is intended for summing to the turnover sheet using various trees and depth. The task of procedure simple — to write in TrAParnt floor number of the score which has a level set in variable Lev, in a tree, set in variable Tr.

SET TERM !! ;
CREATE PROCEDURE TurnSum (Tr SmallInt, Lev SmallInt) as
declare variable  TAcc Char(8);  -- the Current Score
DECLARE VARIABLE  TLev SmallInt; -- the Current level
DECLARE VARIABLE  TDLv SmallInt; -- the Difference of levels
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;

       The string 01 results table TurnSheet in an initial state.
       Review of table TurnSheet starts with string 02 and executions for each string of necessary operations.
       The string 05 by means of procedure AccLevel finds a level of the current score in the set tree.
       The string 06 receives a difference of levels between current and set.
       The string 07 — if a difference of levels is negative, by means of procedure AccLevel we find number of the parent score in the set tree at the set level (string 09) and is written it in TrAParnt floor (string 08) for current string.
       Otherwise we write in TrAParnt floor current value of TrnAccnt field.
       Now it is possible to look at result.
       For obtaining the turnover sheet we shall execute the command:

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

Commit;

       Also we shall look result:

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

       Now we shall fulfil summing on the first tree (the Schedule of scores) on levels 3 and we shall look that has turned out:

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

       The table corresponding the schedule of scores without subscores has turned out. However director can submit more a brief information — summing on a level 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

       Such information content can even be transferred on SMS.
       Now about we sum up the turnover sheet on the second tree ("Balance") on a level 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

       As a result, if to take only columns SaldoBeg and SaldoEnd filling of balance is reduced to copying values in appropriate columns of "Accounting balance" of strings which are specified in column Accnt.
       If about to sum up the turnover sheet on the third tree ("the Report on profits")

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

How it to display?

       It is necessary to display a tree "the Schedule of scores". The way of mapping differs from previous because instead of ID the string field with number of the score is used a little. As an example the small accounting program which is allocated in directory Example09 is used.
       So, to receive names of trees and to generate from them ComboBox we shall take advantage of the same reception, as with table Goods:

procedure TForm1.FormCreate(Sender: TObject); {Example09}
Var
   db : String;
   ps : Integer;
begin
. . . . . .
   cbxTreeSel.Items.Clear;
   qrExeProc.Close;   qrExeProc.SQL.Clear;
   qrExeProc.SQL.Add('Select first 4 AccTitle from ChartAcc');
   qrExeProc.SQL.Add(' where AccParnt=''.'' and AccOrder<0');
   qrExeProc.SQL.Add(' order by AccOrder');
   qrExeProc.Open;  qrExeProc.First;
   While not qrExeProc.Eof do begin
      cbxTreeSel.Items.Add(qrExeProc.FieldValues['AccTitle']);
      qrExeProc.Next;
   end;
   cbxTreeSel.ItemIndex:=0;
. . . . .
end;

       From table ChartAcc the first 4 records which as the parent have root record — 'are selected.'. As in the table there can be temporarily indefinite records it is necessary what to define that in the image what records really are root. For this purpose the additional tag is used — the sort field should have negative value. The received set sequentially writes in cbxTreeSel then it индес it is installed in 0. As it is shown in a figure:


Pic.3-11 The Schedule of scores — an initial state

       The choice from the list initiates initial construction of a tree by procedure TreeDataSetOpen:

procedure TForm1.TreeDatasetOpen();
Var
   RowChild   : Integer;
   sFld  : String;
   ChildNode,NewNode : TTreeNode;
begin
  inherited;
  If qrTVChartAcc.Database.Connected then
     begin
        tvChartAcc.Items.BeginUpdate;
        tvChartAcc.Visible := False;
        tvChartAcc.Items.Clear;
        tvChartAcc.Visible := True;
        If cbxTreeSel.ItemIndex>0 then begin
        trTVChartAcc.Active := True;
        qrTVChartAcc.Close;   qrTVChartAcc.SQL.Clear;
        qrTVChartAcc.SQL.Add('Select * from ChartAcc');
        If cbxTreeSel.ItemIndex=1 then 
          qrTVChartAcc.SQL.Add('where AccParnt=''AccBase'' order by AccOrder');
        If cbxTreeSel.ItemIndex=2 then 
           qrTVChartAcc.SQL.Add('where AccBalns=''Balans'' order by AccBlOrd');
        If cbxTreeSel.ItemIndex=3 then
           qrTVChartAcc.SQL.Add('where AccProft=''Profit'' order by AccPfOrd');

       Depending on a state cbxTreeSel the inquiry selects records of the first level from this or that tree.

        qrTVChartAcc.Open;     qrTVChartAcc.First;
        While not qrTVChartAcc.EOF do
          begin
             RowChild := 0;
             sFld := qrTVChartAcc.FieldValues['AccntNum'];
             sFld := acText8(sFld);
             sFld := sFld+' - '+qrTVChartAcc.FieldValues['AccTitle'];

       In difference from the previous cases the key field is string. Different components on a miscellaneous handle text boxes. For example, IBO considers ALL string fields as VarChar, at the same time IBX considers ALL string fields as Char. To correct this situation function acText8 is used.

             NewNode := tvChartAcc.Items.Add(tvChartAcc.TopItem,sFld);
             If cbxTreeSel.ItemIndex=1 then 
                RowChild := qrTVChartAcc.FieldValues['AccCount'];
             If cbxTreeSel.ItemIndex=2 then
                RowChild := qrTVChartAcc.FieldValues['AccBlCnt'];
             If cbxTreeSel.ItemIndex=3 then 
                RowChild := qrTVChartAcc.FieldValues['AccPfCnt'];
             If RowChild>0 then 
            ChildNode := tvChartAcc.Items.AddChild(NewNode,IntToStr(RowChild));

       If the counter of descendants is more 0 there is a descendant in a node.

             qrTVChartAcc.Next;
          end; // While not qrTVChartAcc.EOF do
          end;
          tvChartAcc.Items.EndUpdate;
          tvChartAcc.Update;
          qrTVChartAcc.Close;      trTVChartAcc.Active := False;
    end; // qrTVGoods.Database.Connected
end;

       For disclosure of nodes procedure tvChartAccExpanding is used

procedure TForm1.tvChartAccExpanding(Sender: TObject; Node: TTreeNode;
                                     var AllowExpansion: Boolean);
Var
   RowChild : Integer;
   sFld : String;
   ChildNode,NewNode : TTreeNode;
begin
  inherited;
     If Node.HasChildren then begin
        Node.DeleteChildren;
        trTVChartAcc.Active := True;
        qrTVChartAcc.Close;   qrTVChartAcc.SQL.Clear;
        sFld := Copy(Node.Text,0,8);

       In this case there is no necessity to store ID a node in what that an additional tag. It is enough to take the first 8 characters from the text of a node, as it is fulfilled by Copy operator.

        qrTVChartAcc.SQL.Add('Select * from ChartAcc');
        If cbxTreeSel.ItemIndex=1 then
           qrTVChartAcc.SQL.Add('where AccParnt='''+sFld
                               +''' order by AccOrder');
        If cbxTreeSel.ItemIndex=2 then
           qrTVChartAcc.SQL.Add('where AccBalns='''+sFld
                               +''' order by AccBlOrd');
        If cbxTreeSel.ItemIndex=3 then 
           qrTVChartAcc.SQL.Add('where AccProft='''+sFld
                               +''' order by AccPfOrd');
        qrTVChartAcc.Open;     qrTVChartAcc.First;
        While not qrTVChartAcc.EOF do
          begin
             RowChild := 0;
             sFld := qrTVChartAcc.FieldValues['AccntNum'];
             sFld := acText8(sFld);
             sFld := sFld+' - '+qrTVChartAcc.FieldValues['AccTitle'];
             NewNode := tvChartAcc.Items.AddChild(Node,sFld);
             If cbxTreeSel.ItemIndex=1 then
                RowChild := qrTVChartAcc.FieldValues['AccCount'];
             If cbxTreeSel.ItemIndex=2 then
                RowChild := qrTVChartAcc.FieldValues['AccBlCnt'];
             If cbxTreeSel.ItemIndex=3 then 
                RowChild := qrTVChartAcc.FieldValues['AccPfCnt'];
             If RowChild>0 then 
             ChildNode:=tvChartAcc.Items.AddChild(NewNode,IntToStr(RowChild));
             qrTVChartAcc.Next;
          end; // While not qrTVChartAcc.EOF do
           tvChartAcc.Items.EndUpdate;
           trTVChartAcc.Active := False;
     end;// Node.HasChildren
     tvChartAcc.Update;
end;

       Other part of procedure does not differ from procedure TreeDatasetOpen.
       The result can be looked in a figure:


Pic.3-12 The Schedule of scores - the opened tree

       For obtaining the description of the score event onChange on which the inquiry varies is used.......

procedure TForm1.tvChartAccChange(Sender: TObject; Node: TTreeNode);
begin
  qrNodeAccnt.Close;
  qrNodeAccnt.Params.ParamValues['SelAccnt'] := Copy(Node.Text,0,8);
  qrNodeAccnt.Open;
end;

The resume

       The described case is interesting as idea. Actually here there are the problems which are beyond programming.
       1) In opinion of the Ministry of Finance the debts can be only for buyers and customers, and creditor debts only for suppliers and contractors. Actually it not so. Both for buyers, and for suppliers can be and creditor, and дебиторская debts. And it means, that the score will be enters in two branches depending on a summation symbol on it what to make is problematic enough.
       2) Any the Soviet/Russian legislation includes a roundabout opening in the form of words "Other...". The accounting reporting not exception, also has a number of strings which are not linked to any score, and are filled at the discretion of the concrete bookkeeper or the tax inspector.
       3) And, at last, the main problem consists that itself "the Schedule of scores" varies on the average time in 3 years, and forms of the reporting can vary once a quarter — well it is necessary for our Ministry of Finance to show, as it does something. But book keeping вешь continuous. Having changed links under the reporting according to the last instructions of the Ministry of Finance, the previous forms of the reporting are simultaneously deleted. Therefore the bookkeeper loses possibility to restore the lost reporting under old forms.
       Even simple changes under the schedule of scores create enormous problems. It is enough to recollect trick with score 77 — the old variant received other number, and new with the same number received new value. To distinguish as to consider record in log of operations before changes and after changes it is impossible.
       However, the principle ссуммирования is useful and can will be applied not only by way of scores, but also in other trees. For example, if to receive the summary sheet on the goods it is possible to receive turns on groups of the goods, depending on the selected tree — an internal code, "ТН foreign trade activities", "the Directory of the corporation-manufacturer". Regulating depth of a tree for summing, we receive the ready tool for the analysis of industrial activity.
       Problem only that by development of classification (structure of a tree) it is necessary to consider not only subordination of nodes, but also a level of classification. For example, the directory of details of cars "VAZs" has depth 3 levels, and the directory of details of cars "GASES" has depth of 27 levels. Accordingly, if to try to make the analysis what nodes are on sale more and what it is less for all cars the result will be incorrect. Therefore it is necessary to process structure of directories "GAS", having reduced it up to 3 levels or to increase depth of directories "VAZ". In any case it is necessary to result structures of the one-type goods released by different productions in some uniform sort. The manual of a department of the prices us in any way could not understand it.

© 01.07.2007, Arkhangelskiy A.G.

<<Prev. Table of contents
The beginning of section
Next>>




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


Постоянный адрес статьи:
http://az-design.ru/Support/DataBase/002b3300_en.shtml