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

Calculation of complexity of car repairs

Arkhangelskiy Andrey

Russian

       Real life application of a complex tree has arisen by development of the program of calculation of complexity of car repairs under the order of Open Company " PRICE-N" (a department of the prices NAMI). This program was outlined as replacement of the program "Auto the Expert 4.5" and "Auto Service 4.0".
       Complexity of car repairs расчитывается as the sum of labour inputs of separate operations which are fulfilled above separate details of the repaired car. Thus cost the norm-hour depends on type of operation (the Code of job).
       So, in one table Goods (Goods) храняться models of cars, headers of operations, the directory of details, the list of codes of operations, as separate branches of the goods. Such storage schema allows to link automatically cars, details and operations to link to the price-list, warehouses and other parts of an intelligence system of firm. It allows to apply the same DB in accompanying applications — Авто-insurance, Авто-tools, Trade in autospare parts, Trade in cars. There were many corporations which combine a little from the enumerated areas. Each of these groups has the structure. Besides it allows to reduce size and quantity of errors in table Vehicles (Cars).
       The complex, one is named or group of operations which расматривается the expert as uniform repair effect and has the specification ремени on execution. For example, "the Engine - to remove/install" — the separate complex which consists of set of the included and additional operations.
       Additional operation is an operation or the complex which is necessary for fulfilling before execution of the main complex as a condition at which the main complex can be fulfilled.
       The included operation is an operation or the complex of which the main complex consists.
       Intersected operation is an operation or the complex which enters in two or more selected complex and should be considered unitary.
       The task of the program was that after the expert will select necessary repair effects and the list of complexes will be derivated.
       The additional problem is made by that each operation can be the complex of the same operations. But for the given program were подготовленны by means of special Programs VAZKompl One-dimensional complexes which consist of a single-selection list box of operations. The provisional description of the program the PRICE-SERVICE with screenshots is on CD in directory Soft.
       Thus, for storage of specifications of labour inputs on maintenance service the combination from two tables — already known table Goods and table NSKompleks is used:

Create table NSKompleks (
  NSCarModel  Integer not null references Goods on update cascade,
  NSCarDetal  Integer not null references Goods on update cascade,
  NSCarKompl  Integer not null references Goods on update cascade,
  NSCarOper   Integer default 0 not null references Goods on update cascade,
  NSRabotKod  Integer references Goods on update cascade,
  NSKmplTime  AZNumber, -- Норматив КОМПЛЕКСА – суммарный
  NSCondTime  AZNumber, -- Норматив ДОПОЛНИТЕЛЬНОЙ операции
  NSOperTime  AZNumber, -- Норматив ВКЛЮЧЕННОЙ операции
Primary key (NSCarModel,NSCarDetal,NSCarKompl,NSCarOper));
Commit;

       Thus the given tree structure can be constructed on the basis of four references in other tree structure. The fifth reference (NSRabotKod) does not participate in construction of a tree and is used at definition of cost in roubles — for this purpose transition NSKompleks => Goods => Price is carried out.
       The example {Example08IBORz} which is a part of program PriceService, illustrates construction of a tree.
       As in the real program model АМТС is selected from other dialogue not linked with this tree in the given example one concrete model VAZ-2109 is taken.
       The radical of a tree consists of groups of nodes, in that order as it is certain in the directory of a factory-manufacturer:


Pic.4-01 The Radical of a tree for a choice of repair effects

       At disclosure of nodes of a tree each node is painted in the certain colors depending on assignment of a node:
       — Knot АМТС — in claret color,
       — Detail АМТС — in green color,
       — Repair effect (the complex of operations) — in dark blue color,
       As it is shown below.


Pic.4-02 The Choice of repair effect in a tree of complexes

       At click of the mouse on the check-boxing all the operations linked to the selected complex are copied in intermediate table NSKomplTemp. Thus if complexes have been selected from the last session earlier the selected complexes are marked on a tree automatically. In the real program the table of applicability of details is connected to the table of complexes, as a result in a tree there are only those details which are used on concrete model АМТС in a concrete complete set. Accordingly, the quantity of the details displayed in a tree is essential less.
       Also in the real program it has been constructed two variants of a tree (at the choice of the user) — one, as shown above, is under construction under the circuit — Knot-> Detail-> Operation, another is under construction under the circuit — Knot-> Operation-> Detail. Besides as table Goods has 5 trees the user could select structure of Knots-details from a variant of a manufacturer or standard in automobile branch. They far not always coincide.
       In case it is some identical details, the inquiry is produced, for скольких details the complex as it is shown below is applied:


Pic.4-03 The Choice of several complexes.

       After a choice of all complexes, it is possible to pass to page of result and to generate the report (conclusion) on cost of repair, including substituted details.


Pic.4-04 The Report (conclusion) on cost of operations on repair АМТС

       As it is a test example the price for unit of нормо-hour is specified provisional and identical to all sorts of operations.
       Besides at transition to page "the Table of result" is fulfilled search of intersected complexes and they are deleted from result.
       As in an example {Example08IBORz} commercial components source texts on CD does not contain are used many. However, little bit simplified example {Example08} shows how to construct such tree using only regular Delphi 7 components.

How it is made?

       So, the example {Example08} does not use the check-боксы, does not select complexes, but thus builds precisely same tree, as the previous example.


Pic.4-05 Construction of a tree of complexes

       Construction of a tree similarly to the aforesaid. As this example will pull out from a context of the real program in procedure FormCreate necessary variables are defined:

procedure TForm1.FormCreate(Sender: TObject); {Example08}
begin
   sGdCnt   := 'GdCntFrm';   sGdCode := 'GdFirmCod';
   sGdParnt := 'GdPrnFrm';  sNSDetal := 'NSDetalFrm';
   qrExeProc.Close;   qrExeProc.SQL.Clear;
   qrExeProc.SQL.Add('Select distinct NSCarModel from NSKompleks'
                    +' where NSCarModel<>0');
   qrExeProc.Open;
   iGrpModel := qrExeProc.FieldValues['NSCarModel'];
   qrExeProc.Close;   qrExeProc.SQL.Clear;
   qrExeProc.SQL.Add('Select GdNames from Goods where GoodsNo='
                     +IntToStr(iGrpModel));
   qrExeProc.Open;
   Form1.Caption := qrExeProc.FieldValues['GdNames'];
   qrExeProc.Close;
   ctvKomplShowRoot;
end;

       From table NSKompleks the list of models AMTS for which there are specifications of complexes is selected. As it is an example in this the DB exist specifications only for one modelling of some and it is possible to take the first record to assign its value iGrpModel and to issue Form1.Caption.
       Then procedure ctvKomplShowRoot starts to build the radical of a tree:

procedure TForm1.ctvKomplShowRoot;
Var
   RowCount,RowChild : Integer;
   sFld : String;
   x,z : Integer;
   ChildNode,NewNode : TTreeNode;
begin
  inherited;
If not FirstKompl then Begin
  qrExeProc.Close;    qrExeProc.SQL.Clear;
  qrExeProc.SQL.Add('Select * from NSModelRoot where NSModelFrm='
                   +IntToStr(iGrpModel));
  qrExeProc.Open;
  x := qrExeProc.FieldValues[sNSDetal];

       For construction of root nodes it is required to receive the uppermost groups of nodes of details for which there are complexes. Basically it can be made directly in this procedure on a method described in "Obtaining the uppermost parent". But it has appeared that on enough greater tables time of obtaining of " the Upper parent " reaches several seconds and more, that creates discomfort for the user. On the other hand, these data it is possible to save in additional table NSModelRoot and to fill it at construction of a DB. In this case at construction of a tree it is enough to select in advance prepared data from table NSModelRoot.

  qrTVKompl.Close;    qrTVKompl.SQL.Clear;
  qrTVKompl.SQL.Add('Select GoodsNo,'+sGdCnt+','+sGdCode
                   +',GdNames from Goods');
  qrTVKompl.SQL.Add(' where '+sGdParnt+'='+IntToStr(x));
  qrTVKompl.SQL.Add(' order by '+sGdCode+',GdNames');
  qrTVKompl.Open;    RowCount := qrTVKompl.RecordCount;
  ctvKompl.Items.BeginUpdate;  ctvKompl.Items.Clear;
  While not qrTVKompl.Eof do Begin
        sFld := qrTVKompl.FieldValues['GdNames'];
        z    := qrTVKompl.FieldValues['GoodsNo'];
        RowChild := qrTVKompl.FieldValues[sGdCnt];
        NewNode := ctvKompl.Items.Add(ctvKompl.TopItem,sFld);
        NewNode.ImageIndex := z;   NewNode.OverlayIndex := 1;
        If RowChild>0 then
           ChildNode := ctvKompl.Items.AddChild(NewNode,IntToStr(RowChild));
        qrTVKompl.Next;
  end;
  ctvKompl.Items.EndUpdate;   ctvKompl.Update;  FirstKompl := True;
End; // FirstFrame
end;

       And further the standard cycle of construction of root nodes of a tree is fulfilled.
       The main cunnings start at disclosure of nodes in procedure TForm1.ctvKomplExpanding. As well as in the previous cases for storage of tags are used ImageIndex and OverlayIndex which are usually used for handle of pictures. Thus, if in the project it is supposed to use pictures for nodes these tags need to be transferred to additional structure Data. Tags are defined as follows:
       Node. ImageIndex = GoodsNo - current record (node)
       Node. OverlayIndex = 1 - the tag of that, record (node) is group of Details
       Node. OverlayIndex = 0 - the tag of that, record (node) is the Detail
       Node. OverlayIndex =-1 - the tag of that, record (node) is the Complex
       After that it is necessary to define structure of a tree:

GROUP of DETAILS - the Top level of group of details (always)
  + - GROUP DETALEJ_0 - 0 level of group of details (not always)
     + - GROUP DETALEJ_1 - 1 level of group of details (not always)
        + - the DETAIL - the Detail (always)
           + - the COMPLEX - the Complex (always)

       At one level there can be a group of details and a single detail as a result, therefore it is necessary to do disclosure of nodes in two stages. As a tag which is used for definition of a stage, it is possible to take Node. OverlayIndex. If it is equal 1 it is possible to construct group of details and details which are flush with group. If it is equal 0 it is a detail which has complexes.

procedure TForm1.ctvKomplExpanding(Sender: TObject; Node: TTreeNode;
                              var AllowExpansion: Boolean); {Example08}
Var
   rFld      : Double;
   sCod,sFld : String;
   y,z,tn : Integer;
   ChildNode,NewNode,KomplNode : TTreeNode;
begin
  tn := Node.OverlayIndex;
  y := Node.ImageIndex;

Input data from a current node.

  If tn=1 then Begin  // это группа деталей
  qrTVKompl.Close;    qrTVKompl.SQL.Clear;
  qrTVKompl.SQL.Add('Select GoodsNo,'+sGdCode+',GdNames from Goods'
                   +' where '+sGdParnt+'='+IntToStr(y)+' and '+sGdCnt+'>0');
  qrTVKompl.SQL.Add(' order by '+sGdCode);
  qrTVKompl.Open;    qrTVKompl.First;
  Node.DeleteChildren;    ctvKompl.Items.BeginUpdate;
  While not qrTVKompl.Eof do Begin
     sCod := qrTVKompl.FieldValues[sGdCode];
     sFld := qrTVKompl.FieldValues['GdNames'];
     z    := qrTVKompl.FieldValues['GoodsNo'];
     NewNode := ctvKompl.Items.AddChild(Node,sCod+' - '+sFld);
     NewNode.ImageIndex := z;    NewNode.OverlayIndex := 1;
     ChildNode := ctvKompl.Items.AddChild(NewNode,'Работа');
     qrTVKompl.Next;
  end;
  ctvKompl.Items.EndUpdate;   ctvKompl.Update;

       The first inquiry receives groups of details which are children of a current node and have children (sGdCnt> 0). The first part of a tree is under construction Of them and for its nodes the tag " group of details " is installed

  qrTVKompl.Close;    qrTVKompl.SQL.Clear;
  qrTVKompl.SQL.Add('Select distinct GoodsNo,'+sGdCode+',GdNames '
            +'from Goods where '+sGdParnt+'='+IntToStr(y)+' and '+sGdCnt+'=0');
  qrTVKompl.SQL.Add(' and GoodsNo in (Select distinct NSCarDetal '
                 +'from NSKompleks where NSCarModel='+IntToStr(iGrpModel)+')');
  qrTVKompl.SQL.Add(' order by '+sGdCode);
  qrTVKompl.Open;    qrTVKompl.First;
  ctvKompl.Items.BeginUpdate;
  While not qrTVKompl.Eof do Begin
     sCod := qrTVKompl.FieldValues[sGdCode];
     sFld := qrTVKompl.FieldValues['GdNames'];
     z    := qrTVKompl.FieldValues['GoodsNo'];
     NewNode := ctvKompl.Items.AddChild(Node,sCod+' - '+sFld);
     NewNode.ImageIndex := z;  NewNode.OverlayIndex := 0;
     ChildNode := ctvKompl.Items.AddChild(NewNode,'Работа');
     qrTVKompl.Next;
  end;
  ctvKompl.Items.EndUpdate;   ctvKompl.Update;

       The second inquiry receives details which are children of a current node (enter into group of details) and which exist in table NSKompleks for given model АМТС. It is necessary, because not for all details entering into directory АМТС, there are specifications of complexes. Nodes for details строяться at the same level, as for the first inquiry.

  end; //  If tn=1 then
  If tn=0 then Begin

If the node represents a detail the derived node will be only the complex.

  qrTVKompl.Close;    qrTVKompl.SQL.Clear;
  qrTVKompl.SQL.Add('Select distinct g.GoodsNo,g.'+sGdCode
                   +',g.GdNames,k.NSKmplTime from Goods g, NSKompleks k');
  qrTVKompl.SQL.Add(' where g.GoodsNo=k.NSCarKompl '
                   +'and k.NSKmplTime is not null');
  qrTVKompl.SQL.Add(' and k.NSCarModel='+IntToStr(iGrpModel)
                   +' and k.NSCarDetal='+IntToStr(y));
  qrTVKompl.SQL.Add(' order by g.'+sGdCode);
  qrTVKompl.Open;  qrTVKompl.First;

       The inquiry selects complexes from table NSKompleks which correspond to selected model АМТС and a detail. As the complex consists of the included and additional operations those records for which the specification of the complex matters, i.e. header of the complex are selected only.

  Node.DeleteChildren;    ctvKompl.Items.BeginUpdate;
  While not qrTVKompl.Eof do Begin
     sCod := qrTVKompl.FieldValues[sGdCode];
     sFld := qrTVKompl.FieldValues['GdNames'];
     z    := qrTVKompl.FieldValues['GoodsNo'];
     If not VarIsNull(qrTVKompl.FieldValues['NSKmplTime'])
        then rFld := qrTVKompl.FieldValues['NSKmplTime'];
     sFld := sCod+' ['+FloatToStrF(rFld,ffFixed,5,1)+'] '+sFld;
     KomplNode := ctvKompl.Items.AddChild(Node,sFld);
     KomplNode.ImageIndex := z;
     KomplNode.OverlayIndex := -1;
     qrTVKompl.Next;
  end;

       As headers of complexes can be identical for convenience of the user, the text which is assigned to a node is formed are more complex in the way. The text switches on a position of the complex in the collection normatives, the specification and header of the complex.

  ctvKompl.Items.EndUpdate;   ctvKompl.Update;
  end;  // If tn=0 then
end;

       And, at last, visually to divide nodes of a tree into groups of details, details and complexes, nodes are painted by following procedure:

procedure TForm1.ctvKomplCustomDrawItem(Sender: TCustomTreeView; 
                                           Node: TTreeNode; 
                                           State: TCustomDrawState; 
                                           var DefaultDraw: Boolean);
var
  NodeRect: TRect;
begin
  with ctvKompl.Canvas do
  begin
    if cdsSelected in State then
    begin
      Brush.Color := clInfoBk;
      NodeRect := Node.DisplayRect(True);
      ctvKompl.Canvas.FillRect(NodeRect);
    end;
    If Node.OverlayIndex=1 then  ctvKompl.Canvas.Font.Color := clMaroon;
    If Node.OverlayIndex=0 then  ctvKompl.Canvas.Font.Color := clGreen;
    If Node.OverlayIndex=-1 then ctvKompl.Canvas.Font.Color := clBlue;
  end;
end;

       Procedure CustomDrawItem is called at drawing a node. Depending on a tag in Node. OverlayIndex to a font appropriate color is assigned.

Choice of the complex as repair effect

       In spite of the fact that in Delphi 7 is not present CheckTreeView, sampling of the complex all the same can be carried out, for example, by means of a pop-up menu. The main problem will be in how to display on a tree the selected complexes. It can be made, for example, by means of Node. OverlayIndex=2. It will affect construction of a tree a little.
       For storage of a state of the selected complexes the table is used

Create table NSTreeViewCheck (
       NSNodeParnt   Integer not null references Goods
                             on update cascade on delete cascade,
       NSNodeCheck   Integer not null references Goods
                             on update cascade on delete cascade,
       NSCheckStat   Integer,
Primary Key (NSNodeParnt,NSNodeCheck));

       Constants of a state in RzCheckTreeView have following values:
       csUnknown = 0 — a unknown state,
       csUnchecked = 1 — not selected,
       csChecked=2 — selected,
       csPartiallyChecked=3 — partially selected.

© 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/002b4100_en.shtml