auto_lxunit.pas

使用delphi语言

unit auto_lxunit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, StdCtrls, ADODB, Buttons, ToolWin, ComCtrls, DB;

type
  Tauto_lxform = class(TForm)
    ToolBar1: TToolBar;
    sb_edit: TSpeedButton;
    sb_save: TSpeedButton;
    sb_cancel: TSpeedButton;
    sb_close: TSpeedButton;
    c: TADOCommand;
    ComboBox1: TComboBox;
    DBGrid1: TDBGrid;
    d: TADODataSet;
    procedure sb_closeClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure sb_editClick(Sender: TObject);
    procedure sb_saveClick(Sender: TObject);
    procedure sb_cancelClick(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  auto_lxform: Tauto_lxform;

implementation
 uses dmunit,lxunit;
{$R *.dfm}

procedure Tauto_lxform.sb_closeClick(Sender: TObject);
begin
  if sb_save.Enabled =true then
  begin
    messagebox(self.Handle,'请确定是保存还是取消','提示信息',mb_iconinformation);
    exit;
  end;
  close;
end;

procedure Tauto_lxform.FormCreate(Sender: TObject);
begin
  keypreview:=true;
  dm.bm.Open ;
  dm.detail.Active:=false;
  dm.bm.First ;
  while not dm.bm.Eof  do
  begin
    combobox1.Items.Add(dm.bm.FieldValues['部门名称']) ;
    dm.bm.Next ;
  end;
  combobox1.Items.Add('<ALL>');
  //在插入语句前先判断是否存在这个月的利息结算
  dm.detail.Close;
  dm.detail.CommandText:='select * from 明细表 where 摘要=''利息结算'' and 年=:y and 月=:m';
  dm.detail.Parameters.ParamByName('y').Value:=lxform.SpinEdit1.Text ;
  dm.detail.Parameters.ParamByName('m').Value:=lxform.SpinEdit2.Text ;
  dm.detail.Open ;
  if dm.detail.RecordCount>0 then
  exit;   //如果存在数据则退出这个过程,不执行下面的语句
  c.CommandText:='delete 利息结算';    //利息结算是个临时表
  c.Execute ;
  c.CommandText:='insert 利息结算(职工编号,账号,职工姓名,总系数) select 职工编号,账号,职工姓名,'+
                  '(sum(总系数)-sum(减少总系数))*(:lv) ''利息'' from 明细表 group by 职工编号,账号,职工姓名';
  c.Parameters.ParamByName('lv').Value:=strtofloat(lxform.Edit1.Text);
  c.Execute ;  //向利息结算插入数据
  c.CommandText:='update 利息结算 set 部门编号=(select 部门编号 from 职工 where 利息结算.职工编号=职工.职工编号 )';
  c.Execute ;    //修改利息结算的部门编号
  c.CommandText:='update 利息结算 set 总系数=总系数+(select 总期初余额*(:lv) from 期初余额 where 利息结算.职工编号 = 期初余额.职工编号)';
  c.Parameters.ParamByName('lv').Value:=strtofloat(lxform.Edit1.Text);
  c.Execute ;//把个人的总期初余额也加进去来算利息
  c.CommandText:='insert 明细表(职工编号,账号,职工姓名,总系数,部门编号)'+
                  'select 职工编号,账号,职工姓名,总系数,部门编号 from 利息结算';
  c.Execute ;    //向明细表插入数据
  c.CommandText:='update 明细表 set 摘要=''利息结算'',年=:y , 月=:m ,类别=''3'' where 摘要=''''';
  c.Parameters.ParamByName('y').Value:=lxform.SpinEdit1.Text  ;
  c.Parameters.ParamByName('m').Value:=lxform.SpinEdit2.Text  ;
  c.Execute ;
  dm.detail.Close;
  dm.detail.CommandText:='select * from 明细表 where 摘要=''利息结算'' and 年=:y and 月=:m';
  dm.detail.Parameters.ParamByName('y').Value:=lxform.SpinEdit1.Text ;
  dm.detail.Parameters.ParamByName('m').Value:=lxform.SpinEdit2.Text ;
  dm.detail.Open ;
end;

procedure Tauto_lxform.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key=114 then
    if sb_edit.Enabled =true then    //F3
      sb_edit.Click ;
  if key=116 then                         //F5
    if sb_save.Enabled =true then
      sb_save.Click ;
  if key=117 then                             //F6
      if sb_cancel.Enabled =true then
        sb_cancel.Click ;
  if key=119 then
    sb_close.Click ;
end;

procedure Tauto_lxform.DBGrid1TitleClick(Column: TColumn);
begin
  dm.detail.Sort:=column.FieldName;     //排序
end;

procedure Tauto_lxform.sb_editClick(Sender: TObject);
var i:integer;
begin
  dbgrid1.ReadOnly:=false;
  for i:=0 to 4 do
  begin
    dbgrid1.Columns[i].ReadOnly:=true;
  end;
  sb_save.Enabled :=true; sb_cancel.Enabled :=true;
  sb_edit.Enabled :=false;
  dm.detail.Edit ;
end;

procedure Tauto_lxform.sb_saveClick(Sender: TObject);
begin
  dm.detail.Edit;
  dm.detail.Post;
  dbgrid1.ReadOnly:=true;
  sb_save.Enabled :=false; sb_cancel.Enabled :=false;
  sb_edit.Enabled :=true;
end;

procedure Tauto_lxform.sb_cancelClick(Sender: TObject);
begin
  dm.detail.Cancel ;
  dbgrid1.ReadOnly:=true;
  sb_save.Enabled :=false; sb_cancel.Enabled :=false;
  sb_edit.Enabled :=true;
end;

procedure Tauto_lxform.ComboBox1Change(Sender: TObject);
begin
  if combobox1.Text<>'<ALL>' then     //当组合框内容发生变化时，下面的表格的内容也发生变化
  begin
    dm.detail.Close;
    dm.detail.CommandText:='select * from 明细表 where 摘要=''利息结算'' and 年=:y and 月=:m '+
                          'and 部门编号 in(select 部门编号 from 部门 where 部门名称 ='''+combobox1.Text+''')';
    dm.detail.Parameters.ParamByName('y').Value:=lxform.SpinEdit1.Text ;
    dm.detail.Parameters.ParamByName('m').Value:=lxform.SpinEdit2.Text ;
    dm.detail.Open ;
  end
  else
  begin
    dm.detail.Close;
    dm.detail.CommandText:='select * from 明细表 where 摘要=''利息结算'' and 年=:y and 月=:m';
    dm.detail.Parameters.ParamByName('y').Value:=lxform.SpinEdit1.Text ;
    dm.detail.Parameters.ParamByName('m').Value:=lxform.SpinEdit2.Text ;
    dm.detail.Open ;
  end;
end;

end.