fmain.pas

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

      MB_ICONWARNING);
    exit;
  end;
  DelDbData(dm.qryJblb);
end;

// 添加缺勤类别
procedure TfrmMain.btn7Click(Sender: TObject);
begin
  AddDbData(frmQqlb);
end;

// 修改缺勤类别
procedure TfrmMain.btn8Click(Sender: TObject);
begin
  ModifyDbData(frmQqlb, dm.qryQqlb);
end;

// 删除缺勤类别
procedure TfrmMain.btn9Click(Sender: TObject);
var
  msg: string;
begin
  // 考勤信息是否有此工种
  msg := '';
  if IsHaveRelation(dm.qryQqlb, 'qqlb_id', 'kqxx', 'qqlb_id') then begin
    msg := '考勤信息中有此信息' +#13+#13+ '不能删除此缺勤类别！';
    Application.MessageBox(PChar(msg), PChar(Application.Title), MB_OK +
      MB_ICONWARNING);
    exit;
  end;
  DelDbData(dm.qryQqlb);
end;

// 添加企业年度效益
procedure TfrmMain.btn10Click(Sender: TObject);
begin
  AddDbData(frmNdxy);
end;

// 修改企业年度效益
procedure TfrmMain.btn11Click(Sender: TObject);
begin
  ModifyDbData(frmNdxy, dm.qryNdxy);
end;

// 删除企业年度效益
procedure TfrmMain.btn12Click(Sender: TObject);
begin
  DelDbData(dm.qryNdxy);
end;

// 添加员工基本信息
procedure TfrmMain.btn13Click(Sender: TObject);
begin
  AddDbData(frmYgxx);
end;

// 修改员工基本信息
procedure TfrmMain.btn14Click(Sender: TObject);
begin
  ModifyDbData(frmYgxx, dm.qryYgxx);
end;

// 删除员工基本信息
procedure TfrmMain.btn15Click(Sender: TObject);
var
  msg: string;
begin

  // 其他资料是否有此员工
  msg := '';
  if IsHaveRelation(dm.qryYgxx, 'yg_id', 'kqxx', 'yg_id') then
    msg := '考勤中有此员工信息' +#13;
  if IsHaveRelation(dm.qryYgxx, 'yg_id', 'jtxx', 'yg_id') then
    msg := '津贴中有此员工信息' +#13;
  if IsHaveRelation(dm.qryYgxx, 'yg_id', 'ylbx', 'yg_id') then
    msg := '医疗中有此员工信息' +#13;
  if IsHaveRelation(dm.qryYgxx, 'yg_id', 'ygz', 'yg_id') then
    msg := '月工资中有此员工信息' +#13;
  if IsHaveRelation(dm.qryYgxx, 'yg_id', 'nzjj', 'yg_id') then
    msg := '年终奖金中有此员工信息' +#13;

  if msg <> '' then begin
    msg := msg +#13+ '不能删除此员工信息！';
    Application.MessageBox(PChar(msg), PChar(Application.Title), MB_OK +
      MB_ICONWARNING);
    exit;
  end;
  DelDbData(dm.qryYgxx);
end;

// 添加考勤信息
procedure TfrmMain.btn16Click(Sender: TObject);
begin
  if dm.qryYgxx['yg_id'] = null then
    Application.MessageBox(PChar('请添加员工基本信息'),
      PChar(Application.Title), MB_OK + MB_ICONINFORMATION)
  else
    AddDbData(frmKqxx);
end;

// 修改考勤信息
procedure TfrmMain.btn17Click(Sender: TObject);
begin
  ModifyDbData(frmKqxx, dm.qryKqxx);
end;

// 删除考勤信息
procedure TfrmMain.btn18Click(Sender: TObject);
begin
  DelDbData(dm.qryKqxx);
end;

// 添加津贴信息
procedure TfrmMain.btn19Click(Sender: TObject);
begin
  if dm.qryYgxx['yg_id'] = null then
    Application.MessageBox(PChar('请添加员工基本信息'),
      PChar(Application.Title), MB_OK + MB_ICONINFORMATION)
  else
    AddDbData(frmJtxx);
end;

// 修改津贴信息
procedure TfrmMain.btn20Click(Sender: TObject);
begin
  ModifyDbData(frmJtxx, dm.qryJtxx);
end;

// 删除考勤信息
procedure TfrmMain.btn21Click(Sender: TObject);
begin
  DelDbData(dm.qryJtxx);
end;

// 添加医疗保险
procedure TfrmMain.btn22Click(Sender: TObject);
begin
  if dm.qryYgxx['yg_id'] = null then
    Application.MessageBox(PChar('请添加员工基本信息'),
      PChar(Application.Title), MB_OK + MB_ICONINFORMATION)
  else
    AddDbData(frmYlbx);
end;

// 修改医疗保险
procedure TfrmMain.btn23Click(Sender: TObject);
begin
  ModifyDbData(frmYlbx, dm.qryYlbx);
end;

// 删除医疗保险
procedure TfrmMain.btn24Click(Sender: TObject);
begin
  DelDbData(dm.qryYlbx);
end;

procedure TfrmMain.ts6Show(Sender: TObject);
begin
  // 计算现在年，月
  edt1.Text := IntToStr(YearOf(Date));
  cbb1.ItemIndex := MonthOf(Date)-1;
end;

// 计算单人月工资
procedure TfrmMain.btn25Click(Sender: TObject);
begin
  if dm.qryYgxx['yg_id'] = null then
    Application.MessageBox(PChar('请添加员工基本信息'),
      PChar(Application.Title), MB_OK + MB_ICONINFORMATION)
  else begin
    try
      StrToInt(edt1.Text);
    except
      Application.MessageBox('请输入正确的年份', PChar(Application.Title), MB_OK
        + MB_ICONWARNING);
      exit;
    end;

    CalcYgz();
  end;
end;

// 计算所有人的月工资
procedure TfrmMain.btn26Click(Sender: TObject);
var
  b: TBookmarkStr;
begin
  if dm.qryYgxx['yg_id'] = null then
    Application.MessageBox(PChar('请添加员工基本信息'),
      PChar(Application.Title), MB_OK + MB_ICONINFORMATION)
  else begin
    try
      StrToInt(edt1.Text);
    except
      Application.MessageBox('请输入正确的年份', PChar(Application.Title), MB_OK
        + MB_ICONWARNING);
      exit;
    end;

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

// 计算月工资
procedure TfrmMain.CalcYgz();
var
  id, n, y, r: string;
  sql: string;
  qry: TADOQuery;
  jbgz, jt, kc, bx, zj: Double;
begin
  id := dm.qryYgxx['yg_id'];
  n := edt1.Text;
  y := IntToStr(cbb1.ItemIndex+1);
  r := IntToStr(DaysInAMonth(StrToInt(n), StrToInt(y)));

  // 删除旧数据
  sql := 'delete '+
         'from ygz '+
         'where nian='+n+
         ' and yue='+y+
         ' and yg_id='+id;
  dm.con1.Execute(sql);
  dm.qryYgz.Close;
  dm.qryYgz.Open;
  
  qry := TADOQuery.Create(nil);
  qry.Connection := dm.con1;

  // 查找基本工资
  sql := 'SELECT gz.jbgz '+
         'FROM gz INNER JOIN ygxx ON gz.gz_id = ygxx.gz_id '+
         'WHERE (((ygxx.yg_id)='+id+')); ';
  qry.SQL.Text := sql;
  qry.Open;
  jbgz  :=qry.FieldByName('jbgz').AsFloat;
  qry.Close;
  // 计算津贴
  sql := 'SELECT Sum(jtxx.jbts * jblb.jbgz) AS jt '+
         'FROM jblb INNER JOIN jtxx ON jblb.jblb_id = jtxx.jblb_id '+
         'WHERE (((jtxx.yg_id)='+id+') AND ((jtxx.jbsj)>=#'+y+'/1/'+n+'#) AND  '+
         '((jtxx.jbsj)<=#'+y+'/'+r+'/'+n+'#)); ';
  qry.SQL.Text := sql;
  qry.Open;
  jt :=qry.FieldByName('jt').AsFloat;
  qry.Close;
  // 计算扣除
  sql := 'SELECT Sum(kqxx.qqts*qqlb.jbgz) AS kc '+
         'FROM qqlb INNER JOIN kqxx ON qqlb.qqlb_id = kqxx.qqlb_id '+
         'WHERE (((kqxx.yg_id)='+id+') AND ((kqxx.kqsj)>=#'+y+'/1/'+n+'#) AND  '+
         '((kqxx.kqsj)<=#'+y+'/'+r+'/'+n+'#)); ';
  qry.SQL.Text := sql;
  qry.Open;
  kc  :=qry.FieldByName('kc').AsFloat;
  qry.Close;
  // 计算报销/保险
  sql := 'SELECT Sum(ylbx.ylfy + ylbx.bxfy) AS bx '+
         'FROM ylbx '+
         'WHERE (((ylbx.yg_id)='+id+') AND ((ylbx.sj)>=#'+y+'/1/'+n+'#) AND  '+
         '((ylbx.sj)<=#'+y+'/'+r+'/'+n+'#)); ';
  qry.SQL.Text := sql;
  qry.Open;
  bx :=qry.FieldByName('bx').AsFloat;
  qry.Close;
  qry.Free;
  // 计算总计
  zj := jbgz + jt - kc - bx;
  if zj<0 then
    zj := 0;

  // 写入数据
  dm.qryYgz.Append;
  dm.qryYgz['yg_id'] := StrToInt(id);
  dm.qryYgz['nian'] := StrToInt(n);
  dm.qryYgz['yue'] := StrToInt(y);
  dm.qryYgz['jbgz'] := jbgz;
  dm.qryYgz['jt'] := jt;
  dm.qryYgz['kc'] := kc;
  dm.qryYgz['bx'] := bx;
  dm.qryYgz['zj'] := zj;
  dm.qryYgz.Post;
end;

// 删除月工资
procedure TfrmMain.btn27Click(Sender: TObject);
begin
  DelDbData(dm.qryYgz);
end;

// 月工资报表
procedure TfrmMain.btn28Click(Sender: TObject);
var
  sql, n, y, filename: String;
  qry: TADOQuery;
  i, z: Integer;
  ExcelApp: Variant;
  sheet, template: Variant;
begin
  try
    StrToInt(edt1.Text);
  except
    Application.MessageBox('请输入正确的年份', PChar(Application.Title), MB_OK
      + MB_ICONWARNING);
    exit;
  end;
  n := edt1.Text;
  y := IntToStr(cbb1.ItemIndex+1);

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

  // 是否生成了月工资
  sql := 'SELECT ygxx.yg_id '+
         'FROM ygz INNER JOIN ygxx ON ygz.yg_id = ygxx.yg_id '+
         'WHERE (((ygz.nian)='+n+') AND ((ygz.yue)='+y+'));';
  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
  pb1.Min := 0;
  pb1.Max := z+4;
  pb1.Step := 1;
  pb1.Position := 0;
  pb1.Visible := True;
  pgc1.Enabled := False;
  Screen.Cursor := crHourGlass;
  // 创建 Excel 例程
  ExcelApp := CreateOleObject('Excel.Application');
  ExcelApp.Visible := False;
  filename := ExtractFilePath(Application.ExeName) + 'template\ygz.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+'报表';
  pb1.StepIt;
  // 复制模板样子
  CopyRange(template, 1,1, 8,3, sheet, 1,1);
  // 填写日期
  FillValue(sheet, 1,8, Sheet.Cells[1,8].Value+n+'年'+y+'月',0,False);
  pb1.StepIt;

  // 统计月工资
  sql := 'SELECT ygxx.ygh,  ygxx.xm, gz.gz, ygz.jbgz, ygz.jt, ygz.kc, ygz.bx, ygz.zj '+
         'FROM gz INNER JOIN (ygz INNER JOIN ygxx ON ygz.yg_id = '+
         '  ygxx.yg_id) ON gz.gz_id = ygxx.gz_id '+
         'WHERE (((ygz.nian)='+n+') AND ((ygz.yue)='+y+')) '+
         'ORDER BY ygxx.bm, gz.gz, ygxx.xm;';
  qry.SQL.Text := sql;
  qry.Open;
  pb1.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('jbgz').AsFloat);
    FillValue(sheet, i,5, qry.FieldByName('jt').AsFloat);
    FillValue(sheet, i,6, qry.FieldByName('kc').AsFloat);
    FillValue(sheet, i,7, qry.FieldByName('bx').AsFloat);
    FillValue(sheet, i,8, qry.FieldByName('zj').AsFloat);
    qry.Next;
    Inc(i);
    pb1.StepIt;
  end;

  // 保存报表