unit3.pas

劳保管理 ACCESS数据库 作为设计的一个参考

//       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
s1:=inttostr(query19.RecordCount);
with Query25 do
     begin
       close;
       SQL.clear;
       SQL.add('select ygbh from lkyg');
       SQL.add(' where xb=''男''');
//       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
s2:=inttostr(query25.RecordCount);
s3:=inttostr(query19.RecordCount-query25.RecordCount);
StatusBar1.Panels[1].Text :=s1;
StatusBar1.Panels[3].Text :=s2;
StatusBar1.Panels[5].Text :=s3;
end;

procedure TForm3.BitBtn16Click(Sender: TObject);
var s,s1,s2:string;
begin
s1:=trim(edit33.text);
s2:=trim(edit34.text);
if (s1='') or (s2='') then exit;

if MessageDlg('请确认！', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
 begin
s:='delete from  gzlb where gzbh=:p1 and lbmc=:p2';
//删除工种劳保库的此工种对应的劳保
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       ParamByname('p1').Asstring:=s1;
       ParamByname('p2').Asstring:=s2;
       prepare;
       ExecSQL;
     end;
//
s:='delete from  ffjl where gzbh=:p1 and lbmc=:p2';
//删除发放记录
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       ParamByname('p1').Asstring:=s1;
       ParamByname('p2').Asstring:=s2;
       prepare;
       ExecSQL;
     end;
 MessageDlg('删除完毕！', mtInformation,[mbOk], 0);
 end;
end;


procedure TForm3.TabSheet2Show(Sender: TObject);
begin
edit10.Text :=datetostr(date);
query17.close;
query17.open;
query26.Close;
query26.Open;
end;

procedure TForm3.BitBtn17Click(Sender: TObject);
var
s1,s2,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13,s14,
gg,xm:string;
//s3:Tdatetime;
yy,mm,dd:word;
yy1,mm1,dd1:word;
i,j:integer;
sl:Extended;
begin
//准备工作
s1:=query13.FieldValues['bmbh'];
s2:=query13.FieldValues['bmmc'];
//s3:=strtodate(trim(edit31.text));
s4:=trim(edit31.text);
decodedate(strtodate(trim(edit31.text)),yy,mm,dd);
if not isdate(s4) then
 begin
   MessageDlg('日期输入错误！', mtWarning, [mbOk], 0);
   exit;
 end;
if is4year(s4)=false then
 begin
   MessageDlg('年份请输入4位！', mtWarning, [mbOk], 0);
   exit;
 end;
//开始进行
table7.Active :=true;
table8.active:=true;
table9.active:=true;
//table9.append;
if table7.RecordCount=0 then exit;
with table7 do
 begin
  for i:=1 to table7.recordcount do
   begin
    s5:=FieldValues['ygbh'];
    s6:=FieldValues['lbmc'];
    if FieldValues['bmbh']=s1 then
     begin
     decodedate(FieldValues['ffrq'],yy1,mm1,dd1);
     sl:=((yy-yy1)*12+mm-mm1) div FieldValues['ffzq'];
     if sl>=1 then //开始发放
       begin
        if table8.findkey([s5]) then
         begin
         xm:=table8.FieldValues['xm'];
         s7:=table8.FieldValues['gzfgg'];
         s8:=table8.FieldValues['mygg'];
         s9:=table8.FieldValues['cygg'];
         s10:=table8.FieldValues['bwx'];
         s11:=table8.FieldValues['bjx'];
         s12:=table8.FieldValues['jx'];
         s13:=table8.FieldValues['jyx'];
         s14:=table8.FieldValues['xz'];
         gg:='-';
         if s6='工作服' then gg:=s7;
         if s6='棉衣' then gg:=s8;
         if s6='衬衣' then gg:=s9;
         if s6='白鞋' then gg:=s10;
         if s6='布胶鞋' then gg:=s11;
         if s6='胶靴' then gg:=s12;
         if s6='绝缘鞋' then gg:=s13;
         if s6='文化衫' then gg:=s14;
         //写发放记录
         table9.append;
         table9.FieldValues['bmbh']:=s1;
         table9.FieldValues['bmmc']:=s2;
         table9.FieldValues['ygbh']:=s5;
         table9.FieldValues['xm']:=xm;
         table9.FieldValues['lbmc']:=s6;
         table9.FieldValues['gg']:=gg;
         table9.FieldValues['sl']:=sl;
         table9.post;
         //更新原始发放记录
         table7.edit;
         table7.FieldValues['ffrq']:=strtodate(trim(edit31.text));
         post;
         end;
       end;
     end;
    next;
   end;
 end;
table7.Active :=false;
table8.active:=false;
table9.active:=false;
//显示单位发放记录及汇总记录
with Query21 do //发放记录
     begin
       close;
       SQL.clear;
       SQL.add('select bmmc,ygbh,xm,lbmc,gg,sl from fftmp ');
       SQL.add(' where bmbh=:p1');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
with Query22 do //单位汇总
     begin
       close;
       SQL.clear;
       SQL.add('select lbmc,gg,sum(sl) as zsl from fftmp ');
       SQL.add(' where bmbh=:p1 group by lbmc,gg');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;

 end;

procedure TForm3.BitBtn19Click(Sender: TObject);
begin
with Query20 do
     begin
       close;
       SQL.clear;
       SQL.add('select bmmc,bmbh from fftmp group by bmmc,bmbh');
       prepare;
       open;
     end;

end;

procedure TForm3.BitBtn20Click(Sender: TObject);
//生成汇总文件
var s1:string;
begin
with Query23 do //单位汇总
     begin
       close;
       SQL.clear;
       SQL.add('select lbmc,gg,sum(sl) as zsl from fftmp ');
       SQL.add(' group by lbmc,gg');
       prepare;
       open;
     end;
if query23.RecordCount =0 then exit;
s1:='整体汇总表.txt';
if fileexists(s1) then
   begin
    MessageDlg('已经存在整体发放记录！', mtWarning, [mbOk], 0);
    exit;
   end;
SaveDataSet(query23,s1);
end;

procedure TForm3.BitBtn18Click(Sender: TObject);
begin
with Query23 do //单位汇总
     begin
       close;
       SQL.clear;
       SQL.add('select lbmc,gg,sum(sl) as zsl from fftmp ');
       SQL.add(' group by lbmc,gg');
//       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
end;

procedure TForm3.BitBtn21Click(Sender: TObject);
var s1,s2:string;
begin
with Query20 do
     begin
       close;
       SQL.clear;
       SQL.add('select bmmc,bmbh from fftmp group by bmmc,bmbh');
       prepare;
       open;
     end;
if query20.RecordCount =0 then exit;
s1:=query20.FieldValues['bmmc']+'.txt';
s2:=query20.FieldValues['bmmc']+'汇总.txt';
if fileexists(s1) or fileexists(s2) then
   begin
    MessageDlg('已经存在此部门的发放记录！', mtWarning, [mbOk], 0);
    exit;
   end;
SaveDataSet(query21,s1);
SaveDataSet(query22,s1);
end;

procedure TForm3.DBGrid22CellClick(Column: TColumn);
var s1:string;
begin
if query20.RecordCount =0 then exit;
s1:=query20.FieldValues['bmbh'];
//显示单位发放记录及汇总记录
with Query21 do //发放记录
     begin
       close;
       SQL.clear;
       SQL.add('select bmmc,ygbh,xm,lbmc,gg,sl from fftmp ');
       SQL.add(' where bmbh=:p1');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
with Query22 do //单位汇总
     begin
       close;
       SQL.clear;
       SQL.add('select lbmc,gg,sum(sl) as zsl from fftmp ');
       SQL.add(' where bmbh=:p1 group by lbmc,gg');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;

end;

procedure TForm3.BitBtn22Click(Sender: TObject);
var s,s1,s2,s3:string;
begin
//更改所有员工发放记录中的发放周期
s1:=trim(edit35.text);
s2:=trim(edit36.text);
s3:=trim(edit37.text);
if (s1='') or (s2='') then exit;
if (s3='') then exit;
s:='update ffjl set ffzq='''+s3+''' where lbmc=:p1 and gzbh=:p2';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       ParamByname('p1').Asstring:=s2;
       ParamByname('p2').Asstring:=s1;
       prepare;
       ExecSQL;
     end;
//更改工种劳保库中的发放周期
s:='update gzlb set ffzq='''+s3+''' where lbmc=:p1 and gzbh=:p2';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       ParamByname('p1').Asstring:=s2;
       ParamByname('p2').Asstring:=s1;
       prepare;
       ExecSQL;
     end;
MessageDlg('更改完毕！', mtInformation,[mbOk], 0);
end;



procedure TForm3.Edit24KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit26.setfocus;
end;

procedure TForm3.Edit28KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit29.setfocus;
end;

procedure TForm3.Edit25KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit30.setfocus;
end;

procedure TForm3.Edit30KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit32.setfocus;
end;

procedure TForm3.Edit33KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit34.setfocus;
end;

procedure TForm3.Edit35KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit36.setfocus;
end;

procedure TForm3.Edit36KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit37.setfocus;
end;

procedure TForm3.Edit15KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit16.setfocus;
end;

procedure TForm3.Edit17KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit21.setfocus;
end;

procedure TForm3.DBGrid23CellClick(Column: TColumn);
var s,s1,s3,s2:string;
begin
s:=query24.FieldValues['bmbh'];
with Query19 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from lkyg where bmbh=:p1');
       ParamByname('p1').Asstring:=s;
       prepare;
       open;
     end;

with Query25 do
     begin
       close;
       SQL.clear;
       SQL.add('select ygbh from lkyg');
       SQL.add(' where bmbh=:p1 and xb=''男''');
       ParamByname('p1').Asstring:=s;
       prepare;
       open;
     end;
s1:=inttostr(query19.RecordCount);
s2:=inttostr(query25.RecordCount);
s3:=inttostr(query19.RecordCount-query25.RecordCount);
StatusBar1.Panels[1].Text :=s1;
StatusBar1.Panels[3].Text :=s2;
StatusBar1.Panels[5].Text :=s3;

end;

procedure TForm3.TabSheet7Show(Sender: TObject);
begin
query24.Close;
query24.Open;
end;

procedure TForm3.Button2Click(Sender: TObject);
begin
if OpenDialog1.Execute then
  edit39.Text:=OpenDialog1.FileName ;
end;

procedure TForm3.BitBtn23Click(Sender: TObject);
begin
if MessageDlg('本功能每次发放前使用一次，且只能使用一次，请确认！', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
  begin
   table11.EmptyTable;
   MessageDlg('初始化完毕，本次发放过程中不要再使用此功能！', mtInformation,
      [mbOk], 0);
  end;
end;




procedure TForm3.ComboBox1KeyPress(Sender: TObject; var Key: Char);
begin
if key=#13 then edit2.SetFocus ;
end;

procedure TForm3.ComboBox1Click(Sender: TObject);
begin
edit2.SetFocus;
end;

procedure TForm3.TabSheet5Show(Sender: TObject);
begin
query10.close;
query10.open;
query12.close;
query12.Open;
end;

procedure TForm3.Query6AfterPost(DataSet: TDataSet);
begin
query7.Close;
query7.Open; 
end;

procedure TForm3.DBGrid4CellClick(Column: TColumn);
begin
edit19.Text:=query26.FieldValues['lbmc'];  
end;

procedure TForm3.Edit38KeyPress(Sender: TObject; var Key: Char);
begin
if not (key in ['0'..'9',#8,#13]) then key:=#0 ;
end;

procedure TForm3.BitBtn24Click(Sender: TObject);
begin
PrintAtOnceEx1.Preview;
end;

procedure TForm3.BitBtn25Click(Sender: TObject);
begin
PrintAtOnceEx2.Preview;
end;

procedure TForm3.BitBtn26Click(Sender: TObject);
begin
PrintAtOnceEx3.Preview;
end;

procedure TForm3.BitBtn27Click(Sender: TObject);
begin
PrintAtOnceEx4.Preview;
end;

procedure TForm3.TabSheet1Show(Sender: TObject);
var
i:integer;
begin
//添加部门列表
Combobox1.Clear;
Combobox3.Clear;
//table1.close;
//table2.close;
table1.Active :=true;
table1.first;
for i:=1 to table1.RecordCount do
 begin
  Combobox1.items.add(table1.FieldValues['bmmc']);
  table1.Next;
 end;
//添加工种列表
table2.Active :=true;
table2.first;
for i:=1 to table2.RecordCount do
 begin
  Combobox3.items.add(table2.FieldValues['gzmc']);
  table2.Next;
 end;
end;





procedure TForm3.Button3Click(Sender: TObject);
var fname,dbname:string;
begin
 fname:=trim(edit39.text);
 if fname='' then
   begin
    MessageDlg('请先找出源数据库！', mtWarning, [mbOk], 0);
    exit;
   end;
 dbname:='DATABASE NAME='+fname;
 DataBase1.DataBaseName:='lklbalname';//数据库别名
 DataBase1.DriverName:= 'MSACCESS';//ACCESS驱动程序名称
 DataBase1.Params.Clear;//清除参数
 DataBase1.Params.Add(dbname);//实际数据库
 DataBase1.LoginPrompt:=false;
 Table13.DataBaseName:='lklbalname';//将别名赋给数据表
 table12.Active :=false;
 Table12.TableName:= 'lkyg';//员工表
 Table13.TableName:= 'lkyg';//员工表
 batchmove1.Execute;
 table12.Active :=false;
 Table12.TableName:= 'ffjl';//员工表
 Table13.TableName:= 'ffjl';//员工表
 batchmove1.Execute;
 MessageDlg('更改完毕！', mtInformation,[mbOk], 0);

end;

end.