fzpayment01.pas

在打沙场用的最简单

unit fzpayment01;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, ExtCtrls, Grids, DBGridEh, wwdbdatetimepicker,inifiles,
  comobj, Excel2000, StdCtrls, Buttons, Spin, PBSpinEdit, ADODB;

type
  Tfzpayment1 = class(TForm)
    DataSource1: TDataSource;
    Query1: TQuery;
    Panel1: TPanel;
    Label1: TLabel;
    Edit1: TEdit;
    Label2: TLabel;
    dbgrideh1: TDBGridEh;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    CheckBox1: TCheckBox;
    PBSpinEdit1: TPBSpinEdit;
    PBSpinEdit2: TPBSpinEdit;
    Label3: TLabel;
    BitBtn3: TBitBtn;
    con1: TADOConnection;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    Query2: TQuery;
    Query1pno3: TStringField;
    Query1price1: TFloatField;
    Query1fzqty: TFloatField;
    Query1fzweight: TFloatField;
    Query1spqty: TFloatField;
    Query1spweight: TFloatField;
    Query1jgf: TFloatField;
    Query1hmweight: TFloatField;
    Query1sh: TFloatField;
    Query1price2: TFloatField;
    Query1mnsh: TFloatField;
    Query1kmnk: TFloatField;
    Query1chkk: TFloatField;
    Query1yfk: TFloatField;
    Query1years: TStringField;
    Query1months: TStringField;
    Query1yj: TBooleanField;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure dbgrideh1ColExit(Sender: TObject);
    procedure dbgrideh1KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
    procedure   cojsb;
  public
    { Public declarations }
  end;

var
  fzpayment1: Tfzpayment1;

implementation

uses main;

{$R *.dfm}

procedure Tfzpayment1.BitBtn1Click(Sender: TObject);
var
i:integer;
begin
with adoquery1 do
 begin
  sql.Clear;
  sql.Add('select company,pno3,gx,price1,fzqty,fzweight,spqty,spweight');
  sql.add(',jgf,hmweight,sh,price2,kmnk,chkk,yfk,years,months,yj from jsb where years=:years and months=:months ');
  Parameters.ParamByName('years').Value :=pbspinedit1.Text;
  Parameters.ParamByName('months').Value:=pbspinedit2.Text;

  if edit1.Text<>'' then
  begin
  sql.Add('and company=:company'  );
  Parameters.ParamByName('company').Value:=edit1.Text;
  end;
  if checkbox1.Checked then
  begin
  sql.Add('and yj=:yj  ');
  Parameters.parambyname('yj').value :=true;
  end else
  begin
  sql.Add('and yj=:yj  ');
  Parameters.parambyname('yj').value :=false;
  end;
  open;
  for i:=0 to 17 do
  dbgrideh1.Columns[i].Width:=64;
end;
end;

procedure Tfzpayment1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  action:=cafree;
end;

procedure Tfzpayment1.FormDestroy(Sender: TObject);
begin
  frmmain.delformname(self.Caption);
end;

procedure Tfzpayment1.BitBtn3Click(Sender: TObject);
begin
 with query1 do
 begin
  sql.Clear;
  sql.Add('select * from jsb where years=:years and months=:months');
  ParamByName('years').Value:=pbspinedit1.Text;
  ParamByName('months').Value:=pbspinedit2.Text;
  open;
 end;
 if query1.RecordCount>0 then
 begin
   if application.MessageBox('本月已有资料，是否重算','系统提示',MB_YESNO)=IDYES then
   begin
    query1.sql.Clear;
    query1.sql.Add('delete from jsb where years=:years and months=:months');
    query1.ParamByName('years').Value:=pbspinedit1.Text;
    query1.ParamByName('months').Value:=pbspinedit2.Text;
    query1.ExecSQL;
    cojsb;

   end;
 end else
  cojsb;

end;

procedure Tfzpayment1.cojsb;
begin
  query1.sql.Clear;
  query1.sql.Add('insert into jsb(yj,years,months,company,pno3,gx,fzqty,fzweight,spqty,spweight, jgf,hmweight,sh,price2,mnsh,chkk,yfk ) ');
  query1.sql.Add('  select 0,:yy1,:mm1,fprovider,pno,fgx,sum(tfzsub.total) as total,sum(tfzsub.fqty) as fqty,0,0,0,0,0,0,0,0,0 from tfz,tfzsub  ');
  query1.SQL.add(' where  year(fcreadate)=:yy and month(fcreadate)=:mm and  tfz.fresid=tfzsub.fresid ');
  query1.SQL.Add(' group by fprovider,pno,fgx');
  query1.ParamByName('yy').asstring:=pbspinedit1.Text;
  query1.ParamByName('mm').asstring:=pbspinedit2.Text;
  query1.ParamByName('yy1').asstring:=pbspinedit1.Text;
  query1.ParamByName('mm1').asstring:=pbspinedit2.Text;
  query1.ExecSQL;
  with  query1 do   /////////////////////以上是发制统计，以下是收片统计
  begin
  sql.clear;
  sql.Add('  select fprovider,pno,fgx,sum(tspsub.total) as total,sum(tspsub.fqty) as fqty from tsp,tspsub  ');
  sql.Add(' where  year(fdelivdate)=:yy and month(fdelivdate)=:mm and  tsp.fresid=tspsub.fresid ');
  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 spqty=:spqty,spweight=:spweight where years=:years and months=:months and  company=:company and pno3=:pno and gx=:gx');
     query2.ParamByName('years').asstring:=pbspinedit1.Text;
     query2.ParamByName('months').asstring:=pbspinedit2.Text;
     query2.ParamByName('spqty').Value   :=query1.fieldbyname('fqty').Asfloat;
     query2.ParamByName('spweight').Value:=query1.fieldbyname('total').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,spqty,spweight,price1,fzqty,fzweight, jgf,hmweight,sh,price2,mnsh,chkk,yfk) ');
     query2.SQL.Add(' values(0,:years,:months,:company,:pno3,:gx,:spqty,:spweight ,0,0,0,0,0,0,0,0,0,0 )' );
     query2.ParamByName('years').asstring:=pbspinedit1.Text;
     query2.ParamByName('months').asstring:=pbspinedit2.Text;
     query2.ParamByName('pno3').Value   :=query1.fieldbyname('pno').AsString;
     query2.ParamByName('spqty').Value   :=query1.fieldbyname('fqty').Asfloat;
     query2.ParamByName('spweight').Value:=query1.fieldbyname('total').Asfloat;
     query2.ParamByName('company').Value :=query1.fieldbyname('fprovider').AsString;
     query2.ParamByName('gx').Value:=query1.fieldbyname('fgx').AsString;
     query2.ExecSQL;
    end;
  query1.Next;
  end;   /////////////收片统计完毕
  with  query1 do   /////////////////////以下是回毛统计
  begin
  sql.clear;
  sql.Add('  select fprovider,pno,fgx,sum(thmsub.fqty) as fqty from thm,thmsub  ');
  sql.Add(' where  year(fdelivdate)=:yy and month(fdelivdate)=:mm and  thm.fresid=thmsub.fresid ');