unit1.pas

工程预算系统

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB,StrUtils, ComCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOConnection2: TADOConnection;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    Button1: TButton;
    ProgressBar1: TProgressBar;
    Button2: TButton;
    ADOQuery3: TADOQuery;
    ADOQuery4: TADOQuery;
    Button3: TButton;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Edit1: TEdit;
    Edit2: TEdit;
    Button4: TButton;
    Button5: TButton;
    Edit3: TEdit;
    Label3: TLabel;
    Button6: TButton;
    ado_zy1: TADOConnection;
    ado_zy2: TADOConnection;
    qurey_zy1: TADOQuery;
    zy_1: TADOQuery;
    OpenDialog1: TOpenDialog;
    zy_2: TADOQuery;
    zy_3: TADOQuery;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  adoconnection2.Open;
  if adoconnection2.InTransaction then
    adoconnection2.RollbackTrans;
    adoconnection2.BeginTrans;

  adoquery1.Close;
  adoquery1.SQL.Clear;
  adoquery1.SQL.Add('select code,Bspecialty,name,parent from billcatalog where parent>0');
  adoquery1.Open;
  progressbar1.Max:=adoquery1.RecordCount;
  progressbar1.Position:=0;
  while not adoquery1.Eof do
  begin
    adoquery2.Close;
    adoquery2.SQL.Clear;
    adoquery2.SQL.Add('insert into A_qdFb (Fbbh,BID,fbmc,fbsm,parent_ID) values(:P1,:P2,:P3,:P4,:P5)');
    adoquery2.Parameters.ParamByName('p1').Value:=adoquery1.fieldbyname('code').AsString;
    adoquery2.Parameters.ParamByName('p2').Value:=adoquery1.fieldbyname('Bspecialty').AsString;
    adoquery2.Parameters.ParamByName('p3').Value:=adoquery1.fieldbyname('name').AsString;
    adoquery2.Parameters.ParamByName('p4').Value:=' ';

    if adoquery1.FieldByName('parent').AsInteger=1 then
      adoquery2.Parameters.ParamByName('p5').Value:='-1'
    else
      adoquery2.Parameters.ParamByName('p5').Value:=leftstr(adoquery1.fieldbyname('code').AsString,4);
    try
      adoquery2.ExecSQL;
    except
      adoconnection2.RollbackTrans;
      showmessage('Error');
      abort;
    end;

    progressbar1.Position:=progressbar1.Position+1;
    adoquery1.Next;
  end;
  adoconnection2.CommitTrans;
  showmessage('ok');
end;

procedure TForm1.Button2Click(Sender: TObject);
var
 bid:string;
 r:integer;
 strlist:Tstringlist;
begin
   try
     strlist:=Tstringlist.Create;
     adoconnection2.Open;
   if adoconnection2.InTransaction then
    adoconnection2.RollbackTrans;
    adoconnection2.BeginTrans;

  //提取分部项目
  adoquery1.Close;
  adoquery1.SQL.Clear;
  adoquery1.SQL.Add('select A.ID,A.code,A.name,A.unit,B.name as pname,A.bewrite,A.matter from billlist A,BillCountRule B where A.Btype=5');
  adoquery1.SQL.Add(' and A.countRule=B.ID');
  adoquery1.Open;
  progressbar1.Max:=adoquery1.RecordCount;
  progressbar1.Position:=0;
  while not adoquery1.Eof do
  begin
    adoquery3.Close;
    adoquery3.SQL.Clear;
    adoquery3.SQL.Add('select BID from  A_qdfb where fbbh='''+adoquery1.fieldbyname('code').AsString+'''');
    adoquery3.Open;
    if adoquery3.Eof then
      bid:='Error'
    else
      bid:=adoquery3.fieldbyname('BID').AsString;

    //插入分部项目
    try
      adoquery2.Close;
      adoquery2.SQL.Clear;
      adoquery2.SQL.Add('insert into A_qdxm (xmbh,BID,fbbh,mc,jldw,jsgz) values(:P1,:P2,:P3,:P4,:P5,:P6)');
      adoquery2.Parameters.ParamByName('p1').Value:=adoquery1.fieldbyname('ID').AsString;
      adoquery2.Parameters.ParamByName('p2').Value:=bid;
      adoquery2.Parameters.ParamByName('p3').Value:=adoquery1.fieldbyname('code').AsString;
      adoquery2.Parameters.ParamByName('p4').Value:=adoquery1.fieldbyname('name').Asstring;
      adoquery2.Parameters.ParamByName('p5').Value:=adoquery1.fieldbyname('unit').AsString;
      adoquery2.Parameters.ParamByName('p6').Value:=adoquery1.fieldbyname('pname').Asstring;
      adoquery2.ExecSQL;

      strlist.Clear;
      strlist.Text:=stringreplace(adoquery1.fieldbyname('bewrite').AsString,';',#13#10,[rfReplaceAll]);//项目特征
      //提取所有项目特征
      if trim(strlist.Text)<>'' then
      begin
        adoquery4.Close;
        adoquery4.SQL.Clear;
        adoquery4.SQL.Add('select ID,bewrite from BillBeWrite where ');
        for r:=0 to strlist.Count-1 do
          if r=0 then
            adoquery4.SQL.Add('ID='+strlist.Strings[r])
          else
            adoquery4.SQL.Add('or ID='+strlist.Strings[r]);
        adoquery4.Open;
        while not adoquery4.Eof do
        begin
          //插入项目特征
          adoquery3.Close;
          adoquery3.SQL.Clear;
          adoquery3.SQL.Add('insert into A_xmtz (TZID,XMbh,Tz) values(:p1,:P2,:P3)');
          adoquery3.Parameters.ParamByName('p1').Value:=adoquery4.fieldbyname('ID').AsInteger;
          adoquery3.Parameters.ParamByName('p2').Value:=adoquery1.fieldbyname('ID').AsString;
          adoquery3.Parameters.ParamByName('p3').Value:=adoquery4.fieldbyname('bewrite').AsString;
          adoquery3.ExecSQL;

          adoquery4.Next;
        end;
      end;

      strlist.Clear;
      strlist.Text:=stringreplace(adoquery1.fieldbyname('matter').AsString,';',#13#10,[rfReplaceAll]);//工程内容
      //提取所有工程内容
      if trim(strlist.Text)<>'' then
      begin

        adoquery4.Close;
        adoquery4.SQL.Clear;
        adoquery4.SQL.Add('select ID,workmark from BillWordMatter where ');
        for r:=0 to strlist.Count-1 do
          if r=0 then
            adoquery4.SQL.Add('ID='+strlist.Strings[r])
          else
            adoquery4.SQL.Add('or ID='+strlist.Strings[r]);
        adoquery4.Open;
        while not adoquery4.Eof do
        begin
          //插入工程内容
          adoquery3.Close;
          adoquery3.SQL.Clear;
          adoquery3.SQL.Add('insert into A_xmgznr (nrid,xmbh,nr) values(:p1,:P2,:P3)');
          adoquery3.Parameters.ParamByName('p1').Value:=adoquery4.fieldbyname('ID').AsInteger;
          adoquery3.Parameters.ParamByName('p2').Value:=adoquery1.fieldbyname('ID').AsString;
          adoquery3.Parameters.ParamByName('p3').Value:=adoquery4.fieldbyname('workmark').AsString;
          try
            adoquery3.ExecSQL;
          except
          end;

          adoquery4.Next;
        end;
      end;

    except
      on E:exception do
      begin
        adoconnection2.RollbackTrans;

        showmessage('Error:'+E.Message);
        abort;
      end;
    end;

    progressbar1.Position:=progressbar1.Position+1;
    adoquery1.Next;
  end;
  adoconnection2.CommitTrans;
  showmessage('ok');

  finally
    strlist.Free;
  end;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
   adoconnection2.Open;
  if adoconnection2.InTransaction then
    adoconnection2.RollbackTrans;
    adoconnection2.BeginTrans;

  adoquery1.Close;
  adoquery1.SQL.Clear;
  adoquery1.SQL.Add('select BeID,name from BillPossibleValue');
  adoquery1.Open;
  progressbar1.Max:=adoquery1.RecordCount;
  progressbar1.Position:=0;
  while not adoquery1.Eof do
  begin
    adoquery2.Close;
    adoquery2.SQL.Clear;
    adoquery2.SQL.Add('insert into A_TZNR (TZID,NR) values(:P1,:P2)');
    adoquery2.Parameters.ParamByName('p1').Value:=adoquery1.fieldbyname('BeID').AsInteger;
    adoquery2.Parameters.ParamByName('p2').Value:=adoquery1.fieldbyname('name').AsString;
    try
      adoquery2.ExecSQL;
    except
      adoconnection2.RollbackTrans;
      showmessage('Error');
      abort;
    end;

    progressbar1.Position:=progressbar1.Position+1;
    adoquery1.Next;
  end;
  adoconnection2.CommitTrans;
  showmessage('ok');
end;

procedure TForm1.Button6Click(Sender: TObject);
  function gettext(s:string):integer;
  var
    r:integer;
    str:string;
  begin
    str:='';
    for r:=length(s) downto 1 do
      if s[r]<>'-' then
        str:=s[r]+str
      else
        break;
    result:=strtoint(str);
  end;
var
  strlist,strlist2:Tstringlist;
  r,j,b_i,e_i:integer;
  str,BID:string;
begin
  ado_zy2.Open;
  bid:='A';
  try
    strlist:=Tstringlist.Create;
    strlist2:=Tstringlist.Create;

  if ado_zy2.InTransaction then
    ado_zy2.RollbackTrans;
    ado_zy2.BeginTrans;

  zy_1.Close;
  zy_1.SQL.Clear;
  zy_1.SQL.Add('select billID,contentname,contentcode,rationID from 安装');
  zy_1.Open;
  progressbar1.Max:=zy_1.RecordCount;
  progressbar1.Position:=0;
  while not zy_1.Eof do
  begin
    try
      //插入节点
      zy_2.Close;
      zy_2.SQL.Clear;
      zy_2.SQL.Add('insert into S_xmzy (BID,xmbh,debh,parentID,demc,dedw) values(:p1,:p2,:P3,:P4,:P5,:P6)');
      zy_2.Parameters.ParamByName('p1').Value:=bid;
      zy_2.Parameters.ParamByName('p2').Value:=zy_1.fieldbyname('billID').AsString;
      zy_2.Parameters.ParamByName('p3').Value:=' ';
      zy_2.Parameters.ParamByName('p4').Value:=zy_1.fieldbyname('contentcode').AsString;
      zy_2.Parameters.ParamByName('p5').Value:=zy_1.fieldbyname('contentname').AsString;
      zy_2.Parameters.ParamByName('p6').Value:=' ';
      zy_2.ExecSQL;

      strlist.Clear;
      strlist.Text:=stringreplace(zy_1.fieldbyname('rationID').AsString,#13#10,';',[rfreplaceall]);
      if trim(strlist.Text)<>'' then
      begin
        for r:=0 to strlist.Count-1 do
        begin
          strlist2.Clear;
          strlist2.Text:=stringreplace(strlist.Strings[r],#13#10,',',[rfreplaceall]);

          b_i:=gettext(strlist2.Strings[0]);
          str:=leftstr(strlist2.Strings[0],b_i);
          if strlist2.Count=2 then
            e_i:=gettext(strlist2.Strings[1])
          else
            e_i:=b_i;

          for j:=b_i to e_i do
          begin
            zy_3.close;
            zy_3.SQL.Clear;
            zy_3.SQL.Add('select pname,unit from '+bid+'dek where ID='''+str+inttostr(j)+'''');
            zy_3.Open;

            zy_2.Close;
            zy_2.SQL.Clear;
            zy_2.SQL.Add('insert into S_xmzy (BID,xmbh,debh,parentID,demc,dedw) values(:p1,:p2,:P3,:P4,:P5,:P6)');
            zy_2.Parameters.ParamByName('p1').Value:=bid;
            zy_2.Parameters.ParamByName('p2').Value:=str+inttostr(j);
            zy_2.Parameters.ParamByName('p3').Value:=zy_3.fieldbyname('pname').AsString;;
            zy_2.Parameters.ParamByName('p4').Value:=zy_1.fieldbyname('contentcode').AsString;
            zy_2.Parameters.ParamByName('p5').Value:=zy_1.fieldbyname('contentname').AsString;
            zy_2.Parameters.ParamByName('p6').Value:=zy_3.fieldbyname('unit').AsString;
            zy_2.ExecSQL;
          end;

        end;
      end;
    except
       on E:exception do
       begin
         showmessage('Error:'+E.Message);
         ado_zy2.RollbackTrans;
         abort;
       end;
    end;

    progressbar1.Position:=progressbar1.Position+1;
    zy_1.Next;
  end;

  ado_zy2.CommitTrans;
  showmessage('ok');

  finally
    strlist.Free;
    strlist2.Free;
  end;
end;

end.