monthdeliver.pas

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

unit monthdeliver;

interface

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

type
  Tfrm_monthdeliver = class(TForm)
    GroupBox2: TGroupBox;
    GroupBox4: TGroupBox;
    Label12: TLabel;
    Label13: TLabel;
    Panel3: TPanel;
    btn_crtresult: TButton;
    btn_clr: TButton;
    btn_prt: TButton;
    GroupBox6: TGroupBox;
    DBGrid1: TDBGrid;
    dsmonthdlv: TDataSource;
    qforuse: TQuery;
    cbbsc: TComboBox;
    cbmonth: TComboBox;
    Panel4: TPanel;
    Panel9: TPanel;
    Panel8: TPanel;
    Panel5: TPanel;
    Panel6: TPanel;
    Panel7: TPanel;
    Panel10: TPanel;
    Panel1: TPanel;
    cbyear: TComboBox;
    qsum: TQuery;
    qmonthdlv: TQuery;
    qmonthdlvBDEDesigner: TStringField;
    qmonthdlvBDEDesigner2: TStringField;
    qmonthdlvBDEDesigner3: TStringField;
    qmonthdlvBDEDesigner4: TStringField;
    qmonthdlvBDEDesigner5: TFloatField;
    qmonthdlvBDEDesigner6: TCurrencyField;
    qmonthdlvBDEDesigner7: TCurrencyField;
    qmonthdlvBDEDesigner8: TStringField;
    qmonthdlvBDEDesigner9: TStringField;
    qmonthdlvBDEDesigner10: TIntegerField;
    qmonthdlvBDEDesigner11: TIntegerField;
    qmonthdlvField: TFloatField;
    qmonthdlvField2: TFloatField;
    qmonthdlvField3: TFloatField;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    Edit5: TEdit;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure qmonthdlvAfterOpen(DataSet: TDataSet);
    procedure btn_crtresultClick(Sender: TObject);
    procedure btn_clrClick(Sender: TObject);
    procedure btn_prtClick(Sender: TObject);
    procedure qmonthdlvCalcFields(DataSet: TDataSet);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frm_monthdeliver: Tfrm_monthdeliver;

  Year, Month, Day: Word;

  Present: TDateTime;
implementation

uses data, rp_monthdeliver;

{$R *.dfm}


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

procedure Tfrm_monthdeliver.FormCreate(Sender: TObject);

begin
      if  qmonthdlv.Active then qmonthdlv.Close ;
  //通过qforuse读出办事处名称列表
  cbbsc.Items.Clear ;  
  with qforuse 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;
//默认的datetime:
    Present:= Now;
    DecodeDate(Present, Year, Month, Day);
    cbyear.Text :=inttostr(year);
    cbmonth.Text :=inttostr(month);


end;

procedure Tfrm_monthdeliver.qmonthdlvAfterOpen(DataSet: TDataSet);
var
a1:real;
a2:real;
a3:real;
a4:real;
a5:real;
begin
   with qmonthdlv do begin
     first;
     while not eof do begin
          with qforuse do begin
            if active then close;
            unprepare;
            sql.Clear;

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

            open;
            //如果没有同样合同号，机型，年和月的记录，就插入。
            if  not findfirst then
            sql.Add('insert into crttbl_monthdeliver values(:bsc,:xm,:gh,:gw,:yjjj,:jl,:yk,:shf,:jyck,:nian,:yue)')
            //否则，更新数据。
            else begin
            sql.Add('update crttbl_monthdeliver set 办事处名称=:bsc,姓名=:xm,工号=:gh,岗位=:gw,佣金或奖金=:yjjj,');
            sql.add('奖励=:jl,应扣=:yk,实发=:shf,结余存款=:jyck,年=:nian,月=:yue');
            sql.Add('  where 工号=:gh and 年=:nian and 月=:yue');
            end;

            parambyname('bsc').asstring:=qmonthdlv.fieldbyname('办事处名称').asstring;
            parambyname('xm').asstring:=qmonthdlv.fieldbyname('姓名').asstring;
            parambyname('gw').asstring:=qmonthdlv.fieldbyname('岗位').asstring;
            parambyname('yjjj').ascurrency:=qmonthdlv.fieldbyname('佣金或奖金').ascurrency;
            parambyname('jl').ascurrency:=qmonthdlv.fieldbyname('奖励').ascurrency;
            parambyname('yk').ascurrency:=qmonthdlv.fieldbyname('应扣').ascurrency;
            parambyname('shf').ascurrency:=qmonthdlv.fieldbyname('实发').ascurrency;
            parambyname('jyck').ascurrency:=qmonthdlv.fieldbyname('结余存款').ascurrency;

            ExecSQL;

          end;
     next;
     end;
   end;
//heji
  a1:=0;
  a2:=0;
  a3:=0;
  a4:=0;
  a5:=0;
  with qmonthdlv do begin
     first;                          
     while not Eof  do begin
         a1:=a1+fieldbyname('佣金或奖金').asfloat;
         a2:=a2+fieldbyname('奖励').asfloat;
         a3:=a3+fieldbyname('实发').asfloat;
         a4:=a4+fieldbyname('应扣').asfloat;
         a5:=a5+fieldbyname('结余存款').asfloat;
         Next ;
     end;
  end;
Edit1.text:=floattostr(round(a1*100)/100);
Edit2.text:=floattostr(round(a2*100)/100);
Edit3.text:=floattostr(round(a3*100)/100);
Edit4.text:=floattostr(round(a4*100)/100);
Edit5.text:=floattostr(round(a5*100)/100);

end;

procedure Tfrm_monthdeliver.btn_crtresultClick(Sender: TObject);
var
q1:string;
q2:string;
q3:string;
begin

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

  if (q1='') or (q2='') or (q3='')
  then begin
    application.MessageBox('您还没有完整设置查询条件呢！','提示',MB_OK);
    btn_clrClick(btn_clr);
    end;
  if (q1<>'') and (q2<>'') and (q3<>'') then begin
     with  qsum do begin
     if Active then Close ;
     parambyname('bscmc').AsString :=cbbsc.Text ;
     parambyname('year').AsString :=cbyear.Text ;
     parambyname('month').AsString :=cbmonth.Text ;
     Open ;
     end;
     with  qmonthdlv do begin
       if qmonthdlv.active then qmonthdlv.Close ;
       qmonthdlv.parambyname('bscmc').AsString :=cbbsc.Text ;
       qmonthdlv.parambyname('year').AsString :=cbyear.Text ;
       qmonthdlv.parambyname('month').AsString :=cbmonth.Text ;
       qmonthdlv.Open ;
//   qmonthdlvCalcFields(qmonthdlv);
  //   qmonthdlvAfterOpen(qmonthdlv);

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

end;

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

procedure Tfrm_monthdeliver.btn_prtClick(Sender: TObject);
begin
     frp_monthdeliver:=Tfrp_monthdeliver.Create(application);
     frp_monthdeliver.QuickRep1.Preview;
     frp_monthdeliver.free;

end;

procedure Tfrm_monthdeliver.qmonthdlvCalcFields(DataSet: TDataSet);
var
allratio:real;
maintaindeliver:real;
maintainstuff:real;
upperpay:real;
forusereal:real;
lastmonthsurplus:real;
aaaa:string;
begin
  with qmonthdlv do begin

     //计算当前办事处浮动权重和 allratio
      with qforuse do begin
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select sum(a.浮动权重) 浮动权重和 from pub_z_payratio a,pub_z_stuff b where b.办事处名称=:bscmc and b.工号=a.工号 and a.年=:year and a.月=:month');
      parambyname('bscmc').AsString :=cbbsc.Text ;
      parambyname('year').AsString :=cbyear.Text ;
      parambyname('month').AsString :=cbmonth.Text ;
      prepare;
      open;
      allratio:=0;
     if findfirst then  allratio:=qforuse.fieldbyname('浮动权重和').AsFloat ;
     end;
      with qforuse do begin
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select a.浮动权重 from pub_z_payratio a,pub_z_stuff b where b.办事处名称=:bscmc and b.工号=a.工号 and b.岗位=''经理'' and a.年=:year and a.月=:month');
      parambyname('bscmc').AsString :=cbbsc.Text ;
      parambyname('year').AsString :=cbyear.Text ;
      parambyname('month').AsString :=cbmonth.Text ;
      prepare;
      open;
     if findfirst then  allratio:=allratio-fieldbyname('浮动权重').AsFloat ;
     end;

     if (FieldByName('岗位').AsString ='经理') or (FieldByName('岗位').AsString ='二级办事处经理') or (FieldByName('岗位').AsString ='一级办事处经理') then
     FieldByName('佣金或奖金').asfloat:=qsum.FieldByName('经理佣金_本期').asfloat       //+qsum.FieldByName('浮动奖金_本期').asfloat*FieldByName('浮动权重').asfloat/allratio
     else if (FieldByName('岗位').AsString ='业务员') or (FieldByName('岗位').AsString ='高级业务员') then
     FieldByName('佣金或奖金').asfloat:=qsum.FieldByName('业务员佣金_本期').asfloat/FieldByName('业务员人数').asfloat+qsum.FieldByName('浮动奖金_本期').asfloat*FieldByName('浮动权重').asfloat/allratio
     else if (FieldByName('岗位').AsString ='维修人员') or (FieldByName('岗位').AsString ='维修主管') then
     FieldByName('佣金或奖金').asfloat:=qsum.FieldByName('维修人员佣金_本期').asfloat/FieldByName('维修人员数').asfloat+qsum.FieldByName('浮动奖金_本期').asfloat*FieldByName('浮动权重').asfloat/allratio
     else if FieldByName('岗位').AsString ='总公司维修人员' then
     begin
      with qforuse do begin
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select sum(a.应发) 所有维修人员应发和 from pub_z_payin a,pub_z_stuff b where a.工号=b.工号 and b.岗位=维修人员 and a.年=:year and a.月=:month');
      parambyname('year').AsString :=cbyear.Text ;
      parambyname('month').AsString :=cbmonth.Text ;
      prepare;
      open;
      maintaindeliver:=0;
     if findfirst then maintaindeliver:=maintaindeliver+qforuse.fieldbyname('所有维修人员应发和').AsFloat ;
     end;
      with qforuse do begin
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select sum(pub_z_office.维修人员数) 所有维修人员数 from pub_z_office');
      prepare;
      open;
      maintainstuff:=1;
     if findfirst then maintainstuff:=maintainstuff+qforuse.fieldbyname('所有维修人员数').AsFloat ;
     end;

     FieldByName('佣金或奖金').asfloat:=maintaindeliver/maintainstuff;
     end

     else// FieldByName('岗位').AsString ='其他人员' then
     FieldByName('佣金或奖金').asfloat:=qsum.FieldByName('浮动奖金_本期').asfloat*FieldByName('浮动权重').asfloat/allratio;

     //jieshuo;

     upperpay:=0;
     with qforuse do begin
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select 月发放佣金上限 from std_pay_upper where 职务=:gw');
      parambyname('gw').AsString :=qmonthdlv.FieldByName('岗位').AsString;
      prepare;
      open;
      if findfirst then upperpay:=FieldByName('月发放佣金上限').asfloat
//缺省上限：5000
      else  upperpay:=5000;
    end;
    //上月结余存款
    lastmonthsurplus:=0;
//非一月，前月为本年前一月：
   if  cbmonth.Text<>'1' then begin
     with qforuse do begin
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select 结余存款 from crttbl_monthdeliver where 工号=:gh and 年=:year and 月=:month1');
      parambyname('gh').AsString :=qmonthdlv.FieldByName('工号').AsString;
      parambyname('year').AsString :=cbyear.Text;
      parambyname('month1').AsString :=inttostr(strtoint(cbmonth.Text)-1);
      prepare;
      open;
      if findfirst then lastmonthsurplus:=FieldByName('结余存款').asfloat
      else   lastmonthsurplus:=0;
     end;
     end
//一月：前月为前年12月
   else  begin
     with qforuse do begin
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select 结余存款 from crttbl_monthdeliver where 工号=:gh and 年=:year1 and 月=''12''');
      parambyname('gh').AsString :=qmonthdlv.FieldByName('工号').AsString;
      parambyname('year1').AsString :=inttostr(strtoint(cbyear.Text)-1);
      prepare;
      open;
      if findfirst then lastmonthsurplus:=FieldByName('结余存款').asfloat
      else   lastmonthsurplus:=0;
    end
    end;

    forusereal:=FieldByName('佣金或奖金').asfloat+FieldByName('奖励').asfloat-FieldByName('应扣').asfloat;
    if forusereal<upperpay  then begin
       FieldByName('实发').asfloat:=forusereal;
       FieldByName('结余存款').asfloat:=lastmonthsurplus;
    end;
    if forusereal>upperpay  then begin
       FieldByName('实发').asfloat:=upperpay;
       FieldByName('结余存款').asfloat:=lastmonthsurplus+forusereal-upperpay;
    end;

  end;
end;

procedure Tfrm_monthdeliver.FormDestroy(Sender: TObject);
begin
     frm_monthdeliver:=nil;
end;

end.