fmain.pas

财务报表系统,包括系统登陆

  filename := ExtractFilePath(Application.ExeName) + '报表\'+n+'.'+y+' 员工月工资报表.xls';
  if FileExists(filename) then
    DeleteFile(filename);
  ExcelApp.DisplayAlerts := False;
  template.Delete;
  ExcelApp.WorkBooks[1].SaveAs(filename);
  pb1.StepIt;
  // 打印预览
  ExcelApp.Visible := True;
  sheet.PrintPreview;
  // 结束
  ExcelApp.WorkBooks.Close;
  ExcelApp.Quit;
except on E: Exception do
  begin
  ExcelApp.WorkBooks[1].Saved := True;
  ExcelApp.WorkBooks.Close;
  ExcelApp.Quit;
  Application.MessageBox(PChar(E.Message + #13+#13+ '生成报表失败'),
    PChar(Application.Title), MB_OK + MB_ICONSTOP);
  end;
end;

  pgc1.Enabled := True;
  Screen.Cursor := crDefault;
  qry.Close;
  qry.Free;
  pb1.Visible := False;
end;

procedure TfrmMain.ts7Show(Sender: TObject);
var
  qry: TADOQuery;
begin
  // 计算现在年
  edt2.Text := IntToStr(YearOf(Date));
  // 显示年度效益，如果有的话
  qry := TADOQuery.Create(nil);
  qry.Connection := dm.con1;
  qry.SQL.Text := 'select ndxy from ndxy where nian='+edt2.Text;
  qry.Open;
  if qry['ndxy'] <> null then
    edt3.Text := qry['ndxy'];
  qry.Close;
  qry.Free;
end;

// 计算单人年终奖金
procedure TfrmMain.btn29Click(Sender: TObject);
var
  sql: string;
begin
  if dm.qryYgxx['yg_id'] = null then
    Application.MessageBox(PChar('请添加员工基本信息'),
      PChar(Application.Title), MB_OK + MB_ICONINFORMATION)
  else begin
    try
      StrToInt(edt2.Text);
    except
      Application.MessageBox('请输入正确的年份', PChar(Application.Title), MB_OK
        + MB_ICONWARNING);
      exit;
    end;
    try
      StrToFloat(edt3.Text);
    except
      Application.MessageBox('请输入正确的年度效益', PChar(Application.Title), MB_OK
        + MB_ICONWARNING);
      exit;
    end;

    // 删除年度效益旧数据
    sql := 'delete '+
           'from ndxy '+
           'where nian='+edt2.Text;
    dm.con1.Execute(sql);
    dm.qryNdxy.Close;
    dm.qryNdxy.Open;
    // 添加年度效益新数据
    dm.qryNdxy.Append;
    dm.qryNdxy['nian'] := StrToInt(edt2.Text);
    dm.qryNdxy['ndxy'] := StrToFloat(edt3.Text);
    dm.qryNdxy.Post;

    CalcNzjj();
  end;
end;

// 计算所有人年终奖金
procedure TfrmMain.btn30Click(Sender: TObject);
var
  b: TBookmarkStr;
  sql: string;
begin
  if dm.qryYgxx['yg_id'] = null then
    Application.MessageBox(PChar('请添加员工基本信息'),
      PChar(Application.Title), MB_OK + MB_ICONINFORMATION)
  else begin
    try
      StrToInt(edt2.Text);
    except
      Application.MessageBox('请输入正确的年份', PChar(Application.Title), MB_OK
        + MB_ICONWARNING);
      exit;
    end;
    try
      StrToFloat(edt3.Text);
    except
      Application.MessageBox('请输入正确的年度效益', PChar(Application.Title), MB_OK
        + MB_ICONWARNING);
      exit;
    end;

    // 删除年度效益旧数据
    sql := 'delete '+
           'from ndxy '+
           'where nian='+edt2.Text;
    dm.con1.Execute(sql);
    dm.qryNdxy.Close;
    dm.qryNdxy.Open;
    // 添加年度效益新数据
    dm.qryNdxy.Append;
    dm.qryNdxy['nian'] := StrToInt(edt2.Text);
    dm.qryNdxy['ndxy'] := StrToFloat(edt3.Text);
    dm.qryNdxy.Post;

    b := dm.qryYgxx.Bookmark;
    dm.qryYgxx.First;
    while not dm.qryYgxx.Eof do begin
      CalcNzjj();
      dm.qryYgxx.Next;
    end;
    dm.qryYgxx.Bookmark := b;
  end;
end;

// 计算年终奖金
procedure TfrmMain.CalcNzjj();
var
  id, n: string;
  sql: string;
  qry: TADOQuery;
  ndxy, kq, jt, zj: Double;
begin
  id := dm.qryYgxx['yg_id'];
  n := edt2.Text;

  // 删除旧数据
  sql := 'delete '+
         'from nzjj '+
         'where nian='+n+
         ' and yg_id='+id;
  dm.con1.Execute(sql);
  dm.qryNzjj.Close;
  dm.qryNzjj.Open;

  qry := TADOQuery.Create(nil);
  qry.Connection := dm.con1;

  // 年度效益
  sql := 'SELECT ndxy '+
         'FROM ndxy '+
         'WHERE nian='+n;
  qry.SQL.Text := sql;
  qry.Open;
  ndxy := qry['ndxy'];
  qry.Close;
  // 计算考勤
  sql := 'SELECT Sum(kqxx.qqts * qqlb.ndxs) AS kq '+
         'FROM kqxx INNER JOIN qqlb ON kqxx.qqlb_id = qqlb.qqlb_id '+
         'WHERE (((kqxx.yg_id)='+id+') AND ((kqxx.kqsj)>=#1/1/'+n+'#) AND ((kqxx.kqsj)<=#12/31/'+n+'#));';
  qry.SQL.Text := sql;
  qry.Open;
  kq := qry.FieldByName('kq').AsFloat;
  qry.Close;
  // 计算津贴
  sql := 'SELECT Sum(jtxx.jbts * jblb.ndxs) AS jt '+
         'FROM jblb INNER JOIN jtxx ON jblb.jblb_id = jtxx.jblb_id '+
         'WHERE (((jtxx.yg_id)='+id+') AND ((jtxx.jbsj)>=#1/1/'+n+'#) AND ((jtxx.jbsj)<=#12/31/'+n+'#));';
  qry.SQL.Text := sql;
  qry.Open;
  jt := qry.FieldByName('jt').AsFloat;
  qry.Close;
  qry.Free;
  // 计算总计
  zj := ndxy * (jt-kq);
  if zj<0 then
    zj := 0;

  // 写入数据
  dm.qryNzjj.Append;
  dm.qryNzjj['yg_id'] := StrToInt(id);
  dm.qryNzjj['nian'] := StrToInt(n);
  dm.qryNzjj['zj'] := zj;
  dm.qryNzjj.Post;
end;

// 删除年终奖金
procedure TfrmMain.btn31Click(Sender: TObject);
begin
  DelDbData(dm.qryNzjj);
end;

// 年终奖金报表
procedure TfrmMain.btn32Click(Sender: TObject);
var
  sql, n, filename: String;
  qry: TADOQuery;
  i, z: Integer;
  ExcelApp: Variant;
  sheet, template: Variant;
begin
  try
    StrToInt(edt2.Text);
  except
    Application.MessageBox('请输入正确的年份', PChar(Application.Title), MB_OK
      + MB_ICONWARNING);
    exit;
  end;
  try
    StrToFloat(edt3.Text);
  except
    Application.MessageBox('请输入正确的年度效益', PChar(Application.Title), MB_OK
      + MB_ICONWARNING);
    exit;
  end;
  n := edt2.Text;

  qry := TADOQuery.Create(nil);
  qry.Connection := dm.con1;

  // 是否生成了年终奖金
  sql := 'SELECT ygxx.yg_id '+
         'FROM nzjj INNER JOIN ygxx ON nzjj.yg_id = ygxx.yg_id '+
         'WHERE (nzjj.nian='+n+');';
  qry.SQL.Text := sql;
  qry.Open;
  z := qry.RecordCount;
  qry.Close;
  if z=0 then begin
    qry.Free;
    Application.MessageBox('请生成年终奖金记录，然后使用报表功能。', 
      PChar(Application.Title), MB_OK + MB_ICONINFORMATION);
    exit;  
  end;
try
  pb2.Min := 0;
  pb2.Max := z+4;
  pb2.Step := 1;
  pb2.Position := 0;
  pb2.Visible := True;
  pgc1.Enabled := False;
  Screen.Cursor := crHourGlass;
  // 创建 Excel 例程
  ExcelApp := CreateOleObject('Excel.Application');
  ExcelApp.Visible := False;
  filename := ExtractFilePath(Application.ExeName) + 'template\nzjj.xls';
  ExcelApp.WorkBooks.Open(filename);
  sheet    := ExcelApp.WorkBooks[1].Sheets[1];
  template := ExcelApp.WorkBooks[1].Sheets[2];
  ExcelApp.Caption := ExcelApp.WorkBooks[1].Sheets[1].Name+'报表';
  pb2.StepIt;
  // 复制模板样子
  CopyRange(template, 1,1, 4,3, sheet, 1,1);
  // 填写日期
  FillValue(sheet, 1,4, Sheet.Cells[1,4].Value+n+'年',0,False);
  pb2.StepIt;

  // 统计年终奖金
  sql := 'SELECT ygxx.ygh,  ygxx.xm, gz.gz, nzjj.zj '+
         'FROM gz INNER JOIN (nzjj INNER JOIN ygxx ON nzjj.yg_id = '+
         '  ygxx.yg_id) ON gz.gz_id = ygxx.gz_id '+
         'WHERE (nzjj.nian='+n+')'+
         'ORDER BY ygxx.bm, gz.gz, ygxx.xm;';
  qry.SQL.Text := sql;
  qry.Open;
  pb2.StepIt;

  i := 4;
  while not qry.Eof do
  begin
    Application.ProcessMessages;
    // 填写数据
    FillValue(sheet, i,1, qry.FieldByName('ygh').AsString,3);
    FillValue(sheet, i,2, qry.FieldByName('xm').AsString,3);
    FillValue(sheet, i,3, qry.FieldByName('gz').AsString,3);
    FillValue(sheet, i,4, qry.FieldByName('zj').AsFloat);
    qry.Next;
    Inc(i);
    pb2.StepIt;
  end;

  // 保存报表
  filename := ExtractFilePath(Application.ExeName) + '报表\'+n+' 年报表.xls';
  if FileExists(filename) then
    DeleteFile(filename);
  ExcelApp.DisplayAlerts := False;
  template.Delete;
  ExcelApp.WorkBooks[1].SaveAs(filename);
  pb2.StepIt;
  // 打印预览
  ExcelApp.Visible := True;
  sheet.PrintPreview;
  // 结束
  ExcelApp.WorkBooks.Close;
  ExcelApp.Quit;
except on E: Exception do
  begin
  ExcelApp.WorkBooks[1].Saved := True;
  ExcelApp.WorkBooks.Close;
  ExcelApp.Quit;
  Application.MessageBox(PChar(E.Message + #13+#13+ '生成报表失败'),
    PChar(Application.Title), MB_OK + MB_ICONSTOP);
  end;
end;

  pgc1.Enabled := True;
  Screen.Cursor := crDefault;
  qry.Close;
  qry.Free;
  pb2.Visible := False;
end;

procedure TfrmMain.ApplicationEvents1Exception(Sender: TObject;
  E: Exception);
begin
  if Pos( 'not a valid date',E.Message ) <> 0 then
    E.Message := '日期输入错误，请重新输入！'
  Else if Pos( 'Cannot perform this operation on an empty dataset',E.Message ) <> 0 then
    E.Message := '已经没有数据，不能删除！'
  Else if Pos( 'not a valid floating point value',E.Message ) <> 0 then
    E.Message := '数据输入错误，请重输！'
  Else if Pos( 'Invalid input value.  Use escape key to abandon changes',E.Message ) <> 0 then
    E.Message := '数据输入错误，请按Esc键后重输！'
  Else if Pos( 'Key violation', E.Message ) <> 0 then
    E.Message := '已有相同的编号，不能修改！'
  else if Pos( 'Field value required', E.Message ) <> 0 then
    E.Message := '编号不能为空！'
  else if Pos( 'I/O error 21', E.Message ) <> 0 then
    E.Message := '驱动器未准备好，无法访问！'
  else if Pos( 'read-only dataset', E.Message ) <> 0 then
    E.Message := '此项资料不能修改！'
  else if Pos( 'is not a valid value for', E.Message ) <> 0 then
  begin
    E.Message := copy(E.Message, pos(' ''', E.Message), pos('''.', E.Message) - pos(' ''', E.Message) + 1) +
      '输入无效，输入范围是 ' + copy(E.Message, pos('range is ', E.Message) + 9, length(E.Message)) + '.';
    E.Message := StringReplace(E.Message, ' to ', ' 至 ',[rfReplaceAll, rfIgnoreCase]);
  end;
  Application.MessageBox(Pchar(E.Message), '系统提示', MB_OK + MB_ICONWARNING);
end;

// 登录管理
procedure TfrmMain.btn33Click(Sender: TObject);
begin
  frmAdminLogin.ShowModal;
end;


procedure TfrmMain.btn34Click(Sender: TObject);
begin
  frmAbout.ShowModal;
end;

// 退出
procedure TfrmMain.btn35Click(Sender: TObject);
begin
  Close;
end;

procedure TfrmMain.btn36Click(Sender: TObject);
begin
  Application.HelpCommand(HELP_CONTENTS, 0);
end;

procedure TfrmMain.FormKeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key = VK_F1 then
    Application.HelpCommand(HELP_CONTENTS, 0);
end;

end.