unit1.pas

本程序是在实施用友ERP时根据客户要求专门为用友U8开发的生产标准生产成本的二次开发程序。

      RzToolButton6.Visible := true;
      //RzComboBox1.Visible := true;
    End
    Else
      ShowMessage('连接数据服务器失败！');
  End
  Else
    ShowMessage('datalink.ini文件不存在，请先建立该文件！');
End;
// 取根数据以便循环取数据

Procedure TForm1.GetMainData;
Var
  ss: String;
  bd, bn, bb: double;
Begin
  Table_ProductID.DisableControls;
  Table_ProductID.Close;
  ADOQuery3.Close;
  ADOQuery3.SQL.Clear;
  ADOQuery3.SQL.Add('Delete from ProductID');
  ADOQuery3.ExecSQL;
  ADOQuery3.Prepared := false;
  ADOQuery3.Close;
  Table_ProductID.Open;
  //  将BOM_PARENT中的数据全部提出，以便作查询循环
  ADOQuery2.Close;
  ADOQuery2.SQL.Clear;
  ADOQuery2.SQL.Add('select BomId,ParentId from bom_parent order by parentid');
  ADOQuery2.Prepared := true;
  ADOQuery2.Open;
  ADOQuery2.First;
  RzProgressBar1.Visible := true;
  RzProgressBar1.TotalParts := ADOQuery2.RecordCount;
  //BOM_PARENT循环
  ADOQuery2.First; //BOM_PARENT
  While Not ADOQuery2.Eof Do
  Begin
    RzProgressBar1.PartsComplete := ADOQuery2.RecNo;
    ss := ADOQuery2.FieldByName('ParentId').AsString;
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add('select BomId,componentid,SortSeq,BaseQtyD,BaseQtyN from bom_Opcomponent where componentid=');
    ADOQuery1.SQL.Add(ss);
    ADOQuery1.SQL.Add('order by SortSeq ');
    ADOQuery1.Prepared := true;
    ADOQuery1.Open;
    ADOQuery1.First;
    If ADOQuery1.RecordCount = 0 Then
    Begin
      Table_ProductID.Edit;
      Table_ProductID.Append;
      Table_ProductID.FieldByName('ParentId').AsInteger := 0;
      Table_ProductID.FieldByName('PartId').AsInteger := ADOQuery2.FieldByName('ParentId').AsInteger;
      Table_ProductID.FieldByName('BomId').AsInteger := ADOQuery2.FieldByName('BomId').AsInteger;
      Table_ProductID.FieldByName('ID').AsInteger := ADOQuery2.FieldByName('BomId').AsInteger;
      Table_ProductID.FieldByName('NewId').AsInteger := NewID;
      Table_ProductID.FieldByName('NewPId').AsInteger := 0;
      bd := SimpleRoundTo(ADOQuery1.FieldByName('BaseQtyD').AsFloat, -2);
      bn := SimpleRoundTo(ADOQuery1.FieldByName('BaseQtyN').AsFloat, -2);
      If bd = 0 Then
        bd := 1;
      If bn = 0 Then
        bn := 1;
      Table_ProductID.FieldByName('BaseQtyD').AsFloat := bd;
      Table_ProductID.FieldByName('BaseQtyN').AsFloat := bn;
      bb := SimpleRoundTo(bn / bd, -2);
      Table_ProductID.FieldByName('BaseQty').AsFloat := bb;
      Table_ProductID.FieldByName('iCost').AsFloat := 0;
      Table_ProductID.FieldByName('iLevel').AsFloat := 0;
      Table_ProductID.Post;
      inc(NewID);
    End;
    ADOQuery2.Next;
  End;
  Table_ProductID.Close;
  ADOQuery2.Prepared := false;
  ADOQuery2.Close;
  ADOQuery1.Prepared := false;
  ADOQuery1.Close;
  RzProgressBar1.Visible := false;
End;
// 处理子件数据

Procedure TForm1.PrepareData;
Var
  ss: String;
  tt, uu: integer;
  bd, bn, bb: double;
  nnid, ll: integer;
Begin
  tt := 1;
  ll := 1;
  Table_TempC.Close;
  Table_ProductID.Close;
  Table_TempA.Close;
  Table_TempB.Close;
  ADOQuery3.Close;
  ADOQuery3.SQL.Clear;
  ADOQuery3.SQL.Add('INSERT INTO TempA SELECT * FROM ProductID');
  ADOQuery3.Prepared := true;
  ADOQuery3.ExecSQL;
  ADOQuery3.Prepared := false;
  ADOQuery3.Close;
  ADOQuery3.SQL.Clear;
  ADOQuery3.SQL.Add('delete from TempC');
  ADOQuery3.ExecSQL;
  ADOQuery3.Prepared := false;
  ADOQuery3.Close;
  RzProgressBar1.Visible := true;
  Repeat
    Begin
      tt := 0;
      Table_TempB.Close;
      ADOQuery3.Close;
      ADOQuery3.SQL.Clear;
      ADOQuery3.SQL.Add('delete from TempB');
      ADOQuery3.ExecSQL;
      ADOQuery3.Prepared := false;
      ADOQuery3.Close;
      Table_TempB.Open;
      Table_TempA.Open;
      Table_TempA.First;
      RzProgressBar1.TotalParts := Table_TempA.RecordCount;
      While Not Table_TempA.Eof Do
      Begin
        RzProgressBar1.PartsComplete := Table_TempA.RecNo;
        nnid := Table_TempA.FieldByName('NewID').AsInteger;
        ss := Table_TempA.FieldByName('ID').AsString;
        uu := Table_TempA.FieldByName('PartId').AsInteger;
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add('select BomId,componentid,SortSeq,BaseQtyD,BaseQtyN from bom_Opcomponent where BomId=');
        ADOQuery1.SQL.Add(ss);
        ADOQuery1.SQL.Add(' order by SortSeq  DESC');
        ADOQuery1.Prepared := true;
        ADOQuery1.Open;
        ADOQuery1.First;
        While Not ADOQuery1.Eof Do
        Begin
          tt := 1;
          Table_TempB.Edit;
          Table_TempB.Append;
          Table_TempB.FieldByName('ParentId').AsInteger := uu;
          Table_TempB.FieldByName('PartId').AsInteger := ADOQuery1.FieldByName('componentid').AsInteger;
          Table_TempB.FieldByName('bomid').AsInteger := ADOQuery1.FieldByName('bomid').AsInteger;
          Table_TempB.FieldByName('ID').AsInteger := ADOQuery1.FieldByName('bomid').AsInteger;
          Table_TempB.FieldByName('NewPId').AsInteger := nnid;
          Table_TempB.FieldByName('BaseQtyD').AsFloat := ADOQuery1.FieldByName('BaseQtyD').AsFloat;
          Table_TempB.FieldByName('BaseQtyN').AsFloat := ADOQuery1.FieldByName('BaseQtyN').AsFloat;
          Table_TempB.Post;
          ADOQuery1.Next;
        End;
        ADOQuery1.Prepared := false;
        ADOQuery1.Close;
        Table_TempA.Next;
      End;
      Table_TempA.Close;
      ADOQuery3.Close;
      ADOQuery3.SQL.Clear;
      ADOQuery3.SQL.Add('delete from TempA');
      ADOQuery3.ExecSQL;
      ADOQuery3.Prepared := false;
      ADOQuery3.Close;
      Table_TempA.Open;
      Table_TempB.Open;
      Table_TempB.First;
      While Not Table_TempB.Eof Do
      Begin
        nnid := Table_TempB.FieldByName('NewPId').AsInteger;
        ss := Table_TempB.FieldByName('PartId').AsString;
        uu := Table_TempB.FieldByName('ID').AsInteger;
        bd := SimpleRoundTo(Table_TempB.FieldByName('BaseQtyD').AsFloat, -2);
        bn := SimpleRoundTo(Table_TempB.FieldByName('BaseQtyN').AsFloat, -2);
        If bd = 0 Then
          bd := 1;
        If bn = 0 Then
          bn := 1;
        ADOQuery1.Close;
        ADOQuery1.Prepared := false;
        ADOQuery1.SQL.Clear;
        ADOQuery1.SQL.Add('select BomId,ParentId from Bom_parent where ParentId=');
        ADOQuery1.SQL.Add(ss);
        ADOQuery1.Prepared := true;
        ADOQuery1.Open;
        ADOQuery1.First;
        If ADOQuery1.RecordCount > 0 Then
        Begin
          While Not ADOQuery1.Eof Do
          Begin
            Table_TempA.Edit;
            Table_TempA.Append;
            Table_TempA.FieldByName('ParentId').AsInteger := uu;
            Table_TempA.FieldByName('PartId').AsInteger := ADOQuery1.FieldByName('ParentId').AsInteger;
            Table_TempA.FieldByName('BomId').AsInteger := ADOQuery1.FieldByName('BomId').AsInteger;
            Table_TempA.FieldByName('ID').AsInteger := ADOQuery1.FieldByName('BomId').AsInteger;
            Table_TempA.FieldByName('NewPId').AsInteger := nnid;
            Table_TempA.FieldByName('NewId').AsInteger := NewID;
            Table_TempA.FieldByName('BaseQtyD').AsFloat := bd;
            Table_TempA.FieldByName('BaseQtyN').AsFloat := bn;
            bb := SimpleRoundTo(bn / bd, -2);
            Table_TempA.FieldByName('BaseQty').AsFloat := bb;
            Table_TempA.FieldByName('iLevel').AsFloat := ll;
            Table_TempA.Post;
            ADOQuery1.Next;
          End;
        End
        Else
        Begin
          Table_TempC.Open;
          Table_TempC.Edit;
          Table_TempC.Append;
          Table_TempC.FieldByName('ParentId').AsInteger := uu;
          Table_TempC.FieldByName('PartId').AsInteger := Table_TempB.FieldByName('PartId').AsInteger;
          Table_TempC.FieldByName('BomId').AsInteger := NewId;
          Table_TempC.FieldByName('ID').AsInteger := nnid;
          Table_TempC.FieldByName('NewPId').AsInteger := nnid;
          Table_TempC.FieldByName('NewId').AsInteger := NewID;
          Table_TempC.FieldByName('BaseQtyD').AsFloat := bd;
          Table_TempC.FieldByName('BaseQtyN').AsFloat := bn;
          bb := SimpleRoundTo(bn / bd, -2);
          Table_TempC.FieldByName('BaseQty').AsFloat := bb;
          Table_TempC.FieldByName('iLevel').AsFloat := ll;
          Table_TempC.Post;
        End;
        ADOQuery1.Prepared := false;
        ADOQuery1.Close;
        inc(NewID);
        Table_TempB.Next;
      End;
      AppendToProductID;
      inc(ll);
    End;
  Until tt = 0;
  addProductIDName;
  ADOQuery1.Prepared := false;
  ADOQuery1.Close;
  Table_ProductID.Close;
  RzProgressBar1.Visible := false;
End;

Procedure TForm1.AppendToProductID;
Begin
  Table_ProductID.Open;
  Table_TempA.Open;
  Table_TempA.First;
  While Not Table_TempA.Eof Do
  Begin
    Table_ProductID.Edit;
    Table_ProductID.Append;
    Table_ProductID.FieldByName('ParentId').AsInteger := Table_TempA.FieldByName('ParentId').AsInteger;
    Table_ProductID.FieldByName('PartId').AsInteger := Table_TempA.FieldByName('PartId').AsInteger;
    Table_ProductID.FieldByName('BomId').AsInteger := Table_TempA.FieldByName('BomId').AsInteger;
    Table_ProductID.FieldByName('ID').AsInteger := Table_TempA.FieldByName('ID').AsInteger;
    Table_ProductID.FieldByName('NewPId').AsInteger := Table_TempA.FieldByName('NewPId').AsInteger;
    Table_ProductID.FieldByName('NewId').AsInteger := Table_TempA.FieldByName('NewId').AsInteger;
    Table_ProductID.FieldByName('BaseQtyD').AsFloat := Table_TempA.FieldByName('BaseQtyD').AsFloat;
    Table_ProductID.FieldByName('BaseQtyN').AsFloat := Table_TempA.FieldByName('BaseQtyN').AsFloat;
    Table_ProductID.FieldByName('BaseQty').AsFloat := Table_TempA.FieldByName('BaseQty').AsFloat;
    Table_ProductID.FieldByName('iCost').AsInteger := 0;
    Table_ProductID.FieldByName('iLevel').AsFloat := Table_TempA.FieldByName('iLevel').AsInteger;
    Table_ProductID.Post;
    Table_TempA.Next;
  End;
  Table_TempC.Open;
  Table_TempC.First;
  While Not Table_TempC.Eof Do
  Begin
    Table_ProductID.Edit;
    Table_ProductID.Append;
    Table_ProductID.FieldByName('ParentId').AsInteger := Table_TempC.FieldByName('ParentId').AsInteger;
    Table_ProductID.FieldByName('PartId').AsInteger := Table_TempC.FieldByName('PartId').AsInteger;
    Table_ProductID.FieldByName('BomId').AsInteger := Table_TempC.FieldByName('BomId').AsInteger;
    Table_ProductID.FieldByName('ID').AsInteger := Table_TempC.FieldByName('ID').AsInteger;
    Table_ProductID.FieldByName('NewPId').AsInteger := Table_TempC.FieldByName('NewPId').AsInteger;
    Table_ProductID.FieldByName('NewId').AsInteger := Table_TempC.FieldByName('NewId').AsInteger;
    Table_ProductID.FieldByName('BaseQtyD').AsFloat := Table_TempC.FieldByName('BaseQtyD').AsFloat;
    Table_ProductID.FieldByName('BaseQtyN').AsFloat := Table_TempC.FieldByName('BaseQtyN').AsFloat;
    Table_ProductID.FieldByName('BaseQty').AsFloat := Table_TempC.FieldByName('BaseQty').AsFloat;
    Table_ProductID.FieldByName('iCost').AsInteger := 1;
    Table_ProductID.FieldByName('iLevel').AsFloat := Table_TempC.FieldByName('iLevel').AsInteger;
    Table_ProductID.Post;
    Table_TempC.Next;
  End;
  Table_TempA.Close;
  Table_TempC.Close;
  ADOQuery3.Close;
  ADOQuery3.SQL.Clear;
  ADOQuery3.SQL.Add('delete from TempC');
  ADOQuery3.ExecSQL;
  ADOQuery3.Prepared := false;
  ADOQuery3.Close;
  Table_ProductID.Close;
End;

Procedure TForm1.addProductIDName;
Var
  tt : integer;
  ss: String;
  //sdw: String;
  //sbm: String;
  bd: double;
Begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SELECT bas_part.PartId, bas_part.InvCode, Inventory.cInvName, Inventory.cInvStd ,Inventory.iInvSprice,');
  ADOQuery1.SQL.Add(' Inventory.cComUnitCode,Inventory.iInvRCost, Inventory.iInvNCost,Inventory.cInvAddCode,ComputationUnit.cComUnitName ');
  ADOQuery1.SQL.Add('FROM bas_part,Inventory ,ComputationUnit ');
  ADOQuery1.SQL.Add('where bas_part.InvCode = Inventory.cInvCode and Inventory.cComUnitCode=ComputationUnit.cComUnitCode');
  ADOQuery1.Prepared := true;
  ADOQuery1.Open;
  Table_ProductID.Open;
  Table_ProductID.first;
  RzProgressBar1.Visible := true;
  RzProgressBar1.TotalParts := Table_ProductID.RecordCount;
  While Not Table_ProductID.Eof Do
  Begin
    RzProgressBar1.PartsComplete := Table_ProductID.RecNo;
    Table_ProductID.Edit;
    tt := Table_ProductID.FieldByName('PartId').AsInteger;
    ADOQuery1.Locate('PartID', tt, [loPartialKey]);
    ss := ADOQuery1.FieldByName('InvCode').AsString + '   ' + ADOQuery1.FieldByName('cInvName').AsString + '(' +
      ADOQuery1.FieldByName('cInvAddCode').AsString + ')';
    Table_ProductID.FieldByName('cInvCode').AsString := ADOQuery1.FieldByName('InvCode').AsString;
    Table_ProductID.FieldByName('cInvName').AsString := ADOQuery1.FieldByName('cInvName').AsString;
    Table_ProductID.FieldByName('Name').AsString := ss;
    Table_ProductID.FieldByName('cInvStd').AsString := ADOQuery1.FieldByName('cInvStd').AsString;
    Table_ProductID.FieldByName('cComUnitName').AsString := ADOQuery1.FieldByName('cComUnitName').AsString;
    Table_ProductID.FieldByName('cInvAddCode').AsString := ADOQuery1.FieldByName('cInvAddCode').AsString;
    bd := SimpleRoundTo(Table_ProductID.FieldByName('BaseQty').AsFloat, -2);
    If Table_ProductID.FieldByName('iCost').AsInteger = 1 Then
    Begin
      Table_ProductID.FieldByName('iInvRCost1').AsFloat := SimpleRoundTo(ADOQuery1.FieldByName('iInvRCost').AsFloat,
        -2);
      Table_ProductID.FieldByName('iInvSprice1').AsFloat := SimpleRoundTo(ADOQuery1.FieldByName('iInvSprice').AsFloat,
        -2);
      Table_ProductID.FieldByName('iInvNCost1').AsFloat := SimpleRoundTo(ADOQuery1.FieldByName('iInvNCost').AsFloat,
        -2);
      Table_ProductID.FieldByName('iBinvRCost1').AsFloat := SimpleRoundTo(ADOQuery1.FieldByName('iInvRCost').AsFloat *
        bd,
        -2);
      Table_ProductID.FieldByName('iBInvSprice1').AsFloat := SimpleRoundTo(ADOQuery1.FieldByName('iInvSprice').AsFloat *