fzpayment01.pas

在打沙场用的最简单

  SQL.Add(' group by fprovider,pno,fgx');
  ParamByName('yy').asstring:=pbspinedit1.Text;
  ParamByName('mm').asstring:=pbspinedit2.Text;
//  adoquery1.Parameters.ParamByName('yy1').Value:=pbspinedit1.Text;
//  adoquery1.Parameters.ParamByName('mm1').Value:=pbspinedit2.Text;
  open;
  end;
  while not  query1.Eof do
  begin
    query2.sql.Clear;
    query2.sql.Add('select * from jsb where years=:years and months=:months and  company=:company and pno3=:pno and gx=:gx');
    query2.ParamByName('years').Value:=pbspinedit1.Text;
    query2.ParamByName('months').Value:=pbspinedit2.Text;
    query2.ParamByName('company').Value:=query1.fieldbyname('fprovider').AsString;
    query2.ParamByName('pno').Value:=query1.fieldbyname('pno').AsString;
    query2.ParamByName('gx').Value:=query1.fieldbyname('fgx').AsString;
    query2.open;
    if query2.RecordCount>0 then
    begin
     query2.sql.Clear;
     query2.sql.Add('update jsb  set hmweight=:hmweight where years=:years and months=:months and  company=:company and pno3=:pno and gx=:gx');
     query1.ParamByName('years').asstring:=pbspinedit1.Text;
     query1.ParamByName('months').asstring:=pbspinedit2.Text;
     query2.ParamByName('hmweight').Value:=query1.fieldbyname('fqty').Asfloat;
     query2.ParamByName('company').Value :=query1.fieldbyname('fprovider').AsString;
     query2.ParamByName('pno').Value:=query1.fieldbyname('pno').AsString;
     query2.ParamByName('gx').Value:=query1.fieldbyname('fgx').AsString;
     query2.ExecSQL;
    end else
    begin
     query2.sql.clear;
     query2.sql.Add('insert into jsb(yj,years,months,company,pno3,gx,hmweight,spqty,spweight,fzqty, jgf,sh,price2,mnsh,chkk,yfk ) ');
     query2.SQL.Add(' values(0,:years,:months,:company,:pno3,:gx,:hmweight,0,0,0,0,0,0,0,0,0  )' );
     query1.ParamByName('years').asstring:=pbspinedit1.Text;
     query1.ParamByName('months').asstring:=pbspinedit2.Text;
     query2.ParamByName('pno3').Value   :=query1.fieldbyname('pno').AsString;
     query2.ParamByName('hmweight').Value:=query1.fieldbyname('fqty').Asfloat;
     query2.ParamByName('company').Value :=query1.fieldbyname('fprovider').AsString;
     query2.ParamByName('gx').Value:=query1.fieldbyname('fgx').AsString;
     query2.ExecSQL;
    end;
  query1.Next;
  end;   /////////////回毛统计完毕

 application.MessageBox('计算完毕！','系统提示',MB_OK);

end;

procedure Tfzpayment1.FormCreate(Sender: TObject);
var
  s:string;
  ini:Tinifile;
  usercode:string;
  serverIP:string;
  yy,mm,dd:word;  
begin
  inherited;
  frmmain.setformname(self.Caption);
  decodedate(date,yy,mm,dd );
  PBSpinEdit1.Value:=yy;
  PBSpinEdit2.Value:=mm;

  s:=Extractfilepath(application.ExeName);
  if s[length(s)]<>'\' then
    s:=s+'\'+'Default.ini'
    else
    s:=s+'Default.ini';
  try
    ini:=Tinifile.Create(s);
    serverIP:=ini.ReadString('ADO','FILE NAME','');
  finally
    ini.Free;
  end;

  if Serverip<>'' then
    con1.ConnectionString:='FILE NAME='+serverip
    else
    con1.ConnectionString:='FILE NAME=\kc.udl';
    con1.Connected:=true;

end;

procedure Tfzpayment1.BitBtn2Click(Sender: TObject);
var
  excelid, mybook: variant;
  row, col, i, j, colcount, frow: integer;
  excelflg: boolean;
  rpstr: string;
  per_count,  datasum, dsum1, dsum2: integer;
  datas1, datas2, datas3: string;
begin
 // inherited;

  adoquery1.DisableControls;
  try
    excelid := createoleobject('excel.application');
    mybook := createoleobject('excel.sheet');
    mybook := excelid.workbooks.add;
    row := 1;
    col := 1;
    excelflg := true;
  except
    excelflg := false;
    application.MessageBox('请确定EXCEL是否正确安装!', '提示信息', mb_ok);
  end;
  if excelflg then
  begin
      colcount := adoquery1.fieldcount-1;
      excelid.range['A' + inttostr(row), chr(64 + colcount) +
        inttostr(row)].merge();
      mybook.worksheets[1].cells.item[row, col] :=  '基本报表';
      mybook.worksheets[1].cells.item[row, col].font.size := 18;
      mybook.worksheets[1].cells.item[row, col].font.bold := true;
      mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
      row := row + 1;
      excelid.range['A' + inttostr(row), chr(64 + colcount) +
        inttostr(row)].merge();
      mybook.worksheets[1].cells.item[row, col] := '报表日期:' +
        datetostr(date);
      row := row + 1;
      frow := row;
      adoquery1.First;
      for i := 1 to colcount do
      begin
        excelid.columns[i].ColumnWidth := dbGrideh1.Columns[i - 1].Width /
          8;
        excelid.columns[i].NumberFormatLocal := '@';
        mybook.worksheets[1].cells.item[row, i] := dbgrideh1.Columns[i -
          1].Title.Caption;
        mybook.worksheets[1].cells.item[row, i].HorizontalAlignment := xlcenter;
      end;
      row := row + 1;
      while not adoquery1.Eof do
      begin
        for i := 1 to colcount do
          mybook.worksheets[1].cells.item[row, i] := adoquery1.Fields[i -
            1].AsString;
        row := row + 1;
         adoquery1.Next;
      end;
      excelid.range['A' + inttostr(row), chr(64 + colcount) +
        inttostr(row)].merge();
      mybook.worksheets[1].cells.item[row, col].font.size := 12;
      mybook.worksheets[1].cells.item[row, col].font.bold := true;
      mybook.worksheets[1].cells.item[row, col].HorizontalAlignment := xlcenter;
      mybook.worksheets[1].cells.item[row, col] := '总记录数:' +
       inttostr(adoquery1.RecordCount) + '条';
      row := row + 1;
      rpstr := '报表条件:';
     { if grid.Cells[0, 0] <> '' then
        for i := 0 to grid.RowCount - 1 do
          for j := 0 to 3 do
            rpstr := rpstr + ' ' + grid.Cells[j, i];    }
      excelid.range['A' + inttostr(row), chr(64 + colcount) +
        inttostr(row)].merge();
      mybook.worksheets[1].cells.item[row, col] := rpstr;
      row := row + 1;
      excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
        - 1)].Borders[1].LineStyle := xlContinuous;
      excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
        - 1)].Borders[2].LineStyle := xlContinuous;
      excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
        - 1)].Borders[3].LineStyle := xlContinuous;
      excelid.range[chr(65) + inttostr(frow), chr(64 + colcount) + inttostr(row
        - 1)].Borders[4].LineStyle := xlContinuous;

//    end;

    excelid.visible := true;
  end;
  adoquery1.EnableControls;


end;

procedure Tfzpayment1.dbgrideh1ColExit(Sender: TObject);
begin
   if adoquery1.State in [dsedit,dsinsert] then
   begin
    adoquery1.FieldByName('jgf').AsFloat  := adoquery1.FieldByName('price1').AsFloat *   adoquery1.FieldByName('spqty').AsFloat/12; //加工费
    adoquery1.FieldByName('kmnk').AsFloat := adoquery1.FieldByName('price2').AsFloat * (adoquery1.FieldByName('fzweight').AsFloat-adoquery1.FieldByName('spweight').AsFloat
    -adoquery1.FieldByName('hmweight').AsFloat )*(1+ adoquery1.FieldByName('sh').AsFloat/100); //扣毛款
    adoquery1.FieldByName('yfk').AsFloat :=  adoquery1.FieldByName('jgf').AsFloat - adoquery1.FieldByName('kmnk').AsFloat -adoquery1.FieldByName('chkk').AsFloat ;
  end;  
end;

procedure Tfzpayment1.dbgrideh1KeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
{if key=37 then
   dbgrideh1.SelectedIndex:=dbgrideh1.SelectedIndex-1 ;
if key=39 then
   dbgrideh1.SelectedIndex:=dbgrideh1.SelectedIndex+1 ;}
  if Key=vk_return then
  begin
    if  adoquery1.State in [dsEdit,dsinsert] then
       begin
        if dbgrideh1.Col< dbgrideh1.VisibleColumns.Count then
          dbgrideh1.SelectedIndex:=dbgrideh1.SelectedIndex+1 else
          dbgrideh1.SelectedIndex:=0;

        end;
  end;
end;

end.