foryearbonus.pas

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

        //使一些按钮失效
        DisableBtn;
        btn_save.Enabled :=true;
        btn_cancel.Enabled :=true;

end;


procedure Tfrm_foryearbonus.btn_cancelClick(Sender: TObject);
begin
        qforyearbns.Cancel ;

        lockedit;

        enablebtn;
        btn_cancel.Enabled :=false;
        btn_save.Enabled :=false;

end;


procedure Tfrm_foryearbonus.btn_deleteClick(Sender: TObject);
begin
     qforyearbns.Delete ;
     qforyearbns.ApplyUpdates ;
     lockedit;

     enablebtn;
     btn_delete.Enabled :=false;
     btn_save.Enabled :=false;

end;
//***************以上是按纽操作。*********************


procedure Tfrm_foryearbonus.btn_brsClick(Sender: TObject);
begin
    qforyearbns.Filtered :=false;
    DBGrid1.DataSource :=dsforyearbns;
    qforyearbnsAfterOpen(qforyearbns);

end;

procedure Tfrm_foryearbonus.btn_clrClick(Sender: TObject);
begin
      cbbsc.Text :='';
      cbyear.Text :='';

end;

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

procedure Tfrm_foryearbonus.FormCreate(Sender: TObject);
begin
    lockedit;
    enablebtn;

  //通过quse读出岗位和办事处列表
  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
      DBComboBox1.Items.Add(fieldbyname('办事处名称').AsString);
      next;
    end;
  end;
    with qforyearbns do begin
    open;
    end;
end;

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

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

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

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

     //将条件累加成子句
     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 ;

   //根据条件过滤
    with qforyearbns do begin

      filter:=qitem;
      filtered:=true;
      qforyearbnsAfterOpen(qforyearbns);

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

 end;

end;
procedure Tfrm_foryearbonus.getotherthree;
var
   Year, Month, Day:word;
begin
   maintain:=0;
   fare:=0;
   deduct:=0;
     with quse do begin
     //全年维修人工费maintain
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select a.人工收费 from pub_z_maintain a,pub_z_client b where a.客户号=b.客户号 and b.办事处名称=:bscmc');
      parambyname('bscmc').AsString :=cbbsc.Text ;
      prepare;
      open;
      if findfirst then begin
       while not eof do
         begin
          DecodeDate(fieldbyname('落单日期').AsDateTime,Year,Month,Day);
         if (Year=strtoint(cbyear.Text)) then
           maintain:=maintain+fieldbyname('人工收费').Asfloat;
          next;
        end;
      end;
      //维修人工费计提率deduct
      if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select 维修人工费计提率 from std_pay_maintain');
      prepare;
      open;
      if findfirst then
         deduct:=maintain*fieldbyname('维修人工费计提率').asfloat;
      //费用fare
     if active then close;
      unprepare;
      sql.Clear;
      sql.Add('select 合计_本年累计 from pub_z_fare where 编制单位=:bscmc and 年=:year  and 月=''12''');
      parambyname('bscmc').AsString :=cbbsc.Text ;
      parambyname('year').AsString :=cbyear.Text ;
      prepare;
      open;
      if findfirst then
         fare:=fieldbyname('合计_本年累计').asfloat;

  end;
end;
procedure Tfrm_foryearbonus.qforyearbnsCalcFields(DataSet: TDataSet);
begin
   getotherthree;
   with qforyearbns do begin
     fieldbyname('全年维修人工费').AsFloat :=maintain;
     fieldbyname('维修人工费计提').AsFloat :=deduct;
     fieldbyname('费用').AsFloat :=fare;
   end;
end;

procedure Tfrm_foryearbonus.qforyearbnsAfterOpen(DataSet: TDataSet);
var
a1:real;
a2:real;
a3:real;
a4:real;
a5:real;
a6:real;
a7:real;

begin

//合计列：
  a1:=0;
  a2:=0;
  a3:=0;
  a4:=0;
  a5:=0;
  a6:=0;
  a7:=0;
  //更新数据库
     with qforyearbns do begin
     first;
     while not eof do begin
          with quse do begin
            if active then close;
            unprepare;
            sql.Clear;

            sql.Add('select * from pub_z_foryearbonus where 办事处名称=:bscmc and 年=:nian');
            parambyname('bscmc').asstring:=qforyearbns.fieldbyname('办事处名称').Asstring;
            parambyname('nian').AsString:=qforyearbns.fieldbyname('年').Asstring;

            open;
            //如果没有同样编制单位，年的记录，就插入。
            if  not findfirst then
            sql.Add('insert into pub_z_foryearbonus values(:bscmc,:zjltbj,:qtjl,:jk,:jhfy,:fy,:qnwxrgf,:wxrgfjt,:nian,:zg,:sh,:lr,:lrrq)')
            //否则，更新数据。
            else begin
            sql.Add('update pub_z_foryearbonus set 办事处名称=:bscmc,总经理特别奖=:zjltbj,其他奖励=:qtjl,减扣=:jk,计划费用=:jhfy,费用=:fy,全年维修人工费=:qnwxrgf,维修人工费计提=:wxrgfjt,年=:nian,主管=:zg,审核=:sh,录入=:lr,录入日期=:lrrq');
            sql.Add(' where 办事处名称=:bscmc and 年=:nian');
            end;
            parambyname('zjltbj').ascurrency:=qforyearbns.fieldbyname('总经理特别奖').ascurrency;
            parambyname('qtjl').ascurrency:=qforyearbns.fieldbyname('其他奖励').ascurrency;
            parambyname('jk').ascurrency:=qforyearbns.fieldbyname('减扣').ascurrency;
            parambyname('jhfy').ascurrency:=qforyearbns.fieldbyname('计划费用').ascurrency;
            parambyname('fy').ascurrency:=qforyearbns.fieldbyname('费用').ascurrency;
            parambyname('qnwxrgf').asfloat:=qforyearbns.fieldbyname('全年维修人工费').asfloat;
            parambyname('wxrgfjt').ascurrency:=qforyearbns.fieldbyname('维修人工费计提').ascurrency;
            parambyname('zg').AsString:=qforyearbns.fieldbyname('主管').Asstring;
            parambyname('sh').asstring:=qforyearbns.fieldbyname('审核').Asstring;
            parambyname('lr').AsString:=qforyearbns.fieldbyname('录入').Asstring;
            parambyname('lrrq').Asdatetime:=qforyearbns.fieldbyname('录入日期').Asdatetime;


            ExecSQL;

          end;
     next;
     end;
   end;
   //合计列：
  with qforyearbns 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;
         a6:=a6+fieldbyname('全年维修人工费').asfloat;
         a7:=a7+fieldbyname('维修人工费计提').asfloat;

         Next ;
     end;
  end;
Edit1.text:=floattostr(a1);
Edit2.text:=floattostr(a2);
Edit3.text:=floattostr(a3);
Edit4.text:=floattostr(a4);
Edit5.text:=floattostr(a5);
Edit6.text:=floattostr(a6);
Edit7.text:=floattostr(a7);

end;

procedure Tfrm_foryearbonus.btn_prtClick(Sender: TObject);
begin
     frp_foryearbonus:=Tfrp_foryearbonus.Create(application);
     frp_foryearbonus.QuickRep1.Preview;
     frp_foryearbonus.free;
end;

procedure Tfrm_foryearbonus.FormDestroy(Sender: TObject);
begin
     frm_foryearbonus:=nil;
end;

end.