saledistribute.pas

主要是介绍DELPHI三层应用以及分布式数据库的应用

unit saledistribute;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, Grids, DBGrids, StdCtrls, ExtCtrls;

type
  Tfrm_saledistribute = class(TForm)
    GroupBox3: TGroupBox;
    cbname: TComboBox;
    GroupBox4: TGroupBox;
    Panel3: TPanel;
    btn_brs: TButton;
    btn_qry: TButton;
    btn_clr: TButton;
    btn_prt: TButton;
    GroupBox6: TGroupBox;
    DBGrid1: TDBGrid;
    GroupBox2: TGroupBox;
    cbbsc: TComboBox;
    qsaledistbt: TQuery;
    dssaledistbt: TDataSource;
    Queryuse: TQuery;
    qsaledistbtBDEDesigner: TAutoIncField;
    qsaledistbtBDEDesigner2: TStringField;
    qsaledistbtBDEDesigner3: TCurrencyField;
    qsaledistbtBDEDesigner4: TFloatField;
    qsaledistbtBDEDesigner5: TStringField;
    qsaledistbtBDEDesigner6: TStringField;
    qsaledistbtBDEDesigner7: TStringField;
    qsaledistbtBDEDesigner8: TStringField;
    qsaledistbtBDEDesigner9: TStringField;
    qsaledistbtBDEDesigner10: TCurrencyField;
    quse: TQuery;
    cbyear: TComboBox;
    Label13: TLabel;
    cbmonth: TComboBox;
    Label14: TLabel;
    procedure getallratio;
    procedure qsaledistbtCalcFields(DataSet: TDataSet);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure btn_brsClick(Sender: TObject);
    procedure btn_qryClick(Sender: TObject);
    procedure btn_clrClick(Sender: TObject);
    procedure qsaledistbtAfterOpen(DataSet: TDataSet);
    procedure btn_prtClick(Sender: TObject);
    procedure cbbscExit(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
  allratio:real;
    { Public declarations }
  end;

var
  frm_saledistribute: Tfrm_saledistribute;

implementation

uses data, rp_saledistribute;

{$R *.dfm}

procedure Tfrm_saledistribute.getallratio;
begin
   allratio:=0;
     with queryuse do begin
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select a.工号,a.零配件计提权重 from  pub_z_saleratio a,pub_z_stuff b where b.办事处名称=:bscname and b.工号=a.工号');
      parambyname('bscname').asstring:=qsaledistbt.fieldbyname('办事处名称').asstring;
      prepare;
      open;

      if findfirst then begin
      first;
       while not eof do
         begin
          allratio:=allratio+fieldbyname('零配件计提权重').Asfloat ;
          next;
        end;
      end;

  end;
end;

procedure Tfrm_saledistribute.qsaledistbtCalcFields(DataSet: TDataSet);
begin
    with qsaledistbt do begin
       getallratio;
       FieldByName('零配件计提奖励').AsFloat:=FieldByName('零配件计提').AsFloat*FieldByName('零配件计提权重').AsFloat/allratio;

    end;

end;

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

procedure Tfrm_saledistribute.FormCreate(Sender: TObject);
begin
    //给出查询页的下拉列表。
   //给出查询页的下拉列表。
  with quse do begin
    if active then close;
    unprepare;
    sql.Clear;
    sql.Add('select 办事处名称 from pub_z_office');
    prepare;
    open;
    while not eof do begin
      cbbsc.Items.Add(fieldbyname('办事处名称').AsString);
      next;
    end;
  end;

    with qsaledistbt do begin
      if not active then open;

      cbname.Items.Clear ;
      cbyear.Items.Clear ;
      cbmonth.Items.Clear ;
      while not Eof do begin

      cbname.Items.Add(FieldByName('姓名').AsString);
      Next;
      end;
    end;
    with qsaledistbt do begin
    open;
    first;
    end;

end;

procedure Tfrm_saledistribute.btn_brsClick(Sender: TObject);
begin
    DBGrid1.DataSource :=dssaledistbt;
    qsaledistbt.filtered:=false;
end;

procedure Tfrm_saledistribute.btn_qryClick(Sender: TObject);
var
  qitem,q1,q2,q3,q4:string;
begin

  q1 :=cbbsc.Text;
  q2 :=cbname.Text;
  q3 :=cbyear.Text;
  q4 :=cbmonth.Text;

  if (q1='') and (q2='') and (q3='') and (q4='')
  then  application.MessageBox('您还没有设置查询条件呢！','提示',MB_OK)
  else  begin

    if not(cbbsc.Text='') then
      begin
      q1:=cbbsc.Text;
      q1:=' 办事处名称='''+q1+''' ';
      end
    else
      q1:='';
    if not(cbname.Text='') then
      begin
      q2:=cbname.Text;
      q2:=' 姓名='''+q2+''' ';
      end
    else
      q2:='';
    if not(cbyear.Text='') then
      begin
      q3:=cbyear.Text;
      q3:=' 年='''+q3+''' ';
      end
    else
      q3:='';
    if not(cbmonth.Text='') then
      begin
      q4:=cbmonth.Text;
      q4:=' 月='''+q4+''' ';
      end
    else
      q4:='';

     //将条件累加成子句
     if q1<>'' then qitem:=q1 else qitem:='';

     if qitem='' then
         if q2<>'' then qitem:=q2;
     if qitem<>'' then
         if q2<>'' then qitem:=qitem+' and '+q2 ;

     if qitem='' then
         if q3<>'' then qitem:=q3;
     if qitem<>'' then
         if q3<>'' then qitem:=qitem+' and '+q3;

     if qitem='' then
         if q4<>'' then qitem:=q4; 
     if qitem<>'' then
         if q4<>'' then qitem:=qitem+' and '+q4;
   //根据条件过滤
    with qsaledistbt do begin

      filter:=qitem;
      filtered:=true;

      if not findfirst then  begin
        application.MessageBox('没有您要的信息！','提示',MB_OK);
        btn_clrClick(btn_clr);
        end ;
    end;

 end;

end;

procedure Tfrm_saledistribute.btn_clrClick(Sender: TObject);
begin
      cbname.Text :='';
      cbbsc.Text :='';
      cbyear.Text :='';
      cbmonth.Text :='';

end;

procedure Tfrm_saledistribute.qsaledistbtAfterOpen(DataSet: TDataSet);
begin
   with qsaledistbt do begin
     first;
     while not eof do begin
          with queryuse do begin
            if active then close;
            unprepare;
            sql.Clear;

            sql.Add('select * from pub_z_saledistribute where 工号=:gh and 年=:nian and 月=:yue');
            parambyname('gh').asstring:=qsaledistbt.fieldbyname('工号').Asstring;
            parambyname('nian').AsString:=qsaledistbt.fieldbyname('年').Asstring;
            parambyname('yue').asstring:=qsaledistbt.fieldbyname('月').Asstring;

            open;
            //如果没有同样合同号，机型，年和月的记录，就插入。
            if  not findfirst then
            sql.Add('insert into pub_z_saledistribute values(:gh,:lpjjtjl,:nian,:yue)')
            //否则，更新数据。
            else begin
            sql.Add('update pub_z_saledistribute set 工号=:gh,零配件计提奖励=:lpjjtjl,年=:nian,月=:yue');
            sql.Add(' where 工号=:gh and 年=:nian and 月=:yue');
            end;
            parambyname('lpjjtjl').ascurrency:=qsaledistbt.fieldbyname('零配件计提奖励').ascurrency;

            ExecSQL;

          end;
     next;
     end;
   end;

end;

procedure Tfrm_saledistribute.btn_prtClick(Sender: TObject);
begin
     frp_saledistribute:=Tfrp_saledistribute.Create(application);
     frp_saledistribute.QuickRep1.Preview;
     frp_saledistribute.free;

end;

procedure Tfrm_saledistribute.cbbscExit(Sender: TObject);
begin
  with quse do begin
    if active then close;
    unprepare;
    sql.Clear;
    sql.Add('select 姓名 from pub_z_stuff where 办事处名称=:bsc');
    parambyname('bsc').AsString :=cbbsc.Text ;
    prepare;
    open;
    if findfirst then begin
      while not eof do begin
        cbname.Items.Add(fieldbyname('姓名').AsString);
        next;
      end;
    end;
  end;

end;

procedure Tfrm_saledistribute.FormDestroy(Sender: TObject);
begin
     frm_saledistribute:=nil;
end;

end.