unit3.pas

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

        ExecSQL;
        end;
    end;
with Query27 do
     begin
       close;
       SQL.clear;
       SQL.add('select lbmc from gzlb where gzbh=:p1 and lbmc=:p2');
       ParamByname('p1').Asstring:=s2;
       ParamByname('p2').Asstring:=s3;
       prepare;
       open;
     end;
if query27.RecordCount >0 then
    begin
      MessageDlg('劳保用品已经存在！', mtWarning, [mbOK], 0);
      exit;
    end;
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add('select gzbh from gz');
       Sql.add(' where gzbh=:p1');
       ParamByname('p1').Asstring:=s2;
       prepare;
       open;
     end;
if query8.RecordCount>0 then
    begin
//  工种存在！,劳保不存在
 s:='insert into gzlb  values('''+s2+''','''+s3+''','''+s4+''')';
      with Query8 do
        begin
        close;
        SQL.clear;
        SQL.add(s);
        prepare;
        ExecSQL;
        end;
    end;
MessageDlg('添加完毕，可以继续添加！', mtInformation,[mbOk], 0);
query17.close;
query17.open;
query26.close;
query26.Open;
end;

procedure TForm3.BitBtn8Click(Sender: TObject);
var s,s1,s2:string;
begin
s1:=trim(edit15.text);
s2:=trim(edit16.text);
if (s1='') or (s2='') then exit;
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add('select bmmc from bm where bmmc=:p1 or bmbh=:p2');
       ParamByname('p1').Asstring:=trim(edit16.text);
       ParamByname('p2').Asstring:=trim(edit15.text);
       prepare;
       open;
     end;
if query8.RecordCount>0 then
  begin
  MessageDlg('已有此部门名称或部门编号，不能重复！', mtInformation,[mbOk], 0);
  exit;
  end;
s:='insert into bm values('''+s1+''','''+s2+''')';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       prepare;
       ExecSQL;
     end;
MessageDlg('添加部门完毕！', mtInformation,[mbOk], 0);
query10.close;
query10.open;
query12.close;
query12.Open;
end;

procedure TForm3.BitBtn9Click(Sender: TObject);
begin
if trim(edit21.text)='' then exit;
if MessageDlg('删除部门的同时将删除所有本部门员工及其发放记录，请确认！', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
 begin
 with Query11 do
     begin
       close;
       SQL.clear;
       SQL.add('delete  from bm');
       Sql.add(' where bmbh=:p1');
       ParamByname('p1').Asstring:=trim(edit21.text);
       prepare;
       ExecSQL;
     end;
//删除部门下所有员工
with Query11 do
     begin
       close;
       SQL.clear;
       SQL.add('delete  from lkyg');
       Sql.add(' where bmbh=:p1');
       ParamByname('p1').Asstring:=trim(edit21.text);
       prepare;
       ExecSQL;
     end;
//删除该部门所有员工的劳保发放记录
with Query11 do
     begin
       close;
       SQL.clear;
       SQL.add('delete  from ffjl');
       Sql.add(' where bmbh=:p1');
       ParamByname('p1').Asstring:=trim(edit21.text);
       prepare;
       ExecSQL;
     end;
MessageDlg('删除完毕！', mtInformation,[mbOk], 0);
query10.close;
query10.open;
query12.close;
query12.Open;
 end;
end;

procedure TForm3.DBGrid7CellClick(Column: TColumn);
begin
edit21.text:=query10.FieldValues['bmbh'];
end;

procedure TForm3.BitBtn10Click(Sender: TObject);
var
s,s1,s2,s3,s4:string;
begin
s3:=trim(edit22.text);
s1:=query10.FieldValues['bmmc'];
s2:=query10.FieldValues['bmbh'];
if (s1='') or (s3='') then exit;
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add('select bmmc from bm where bmmc=:p1');
       ParamByname('p1').Asstring:=s3;
       prepare;
       open;
     end;
if query8.RecordCount>0 then
  begin
  MessageDlg('已有此部门名称，不能重复！', mtInformation,[mbOk], 0);
  exit;
  end;
//更改部门库中的部门名称
s4:='update bm set bmmc='''+s3+''' where bmbh=:p1';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s4);
       ParamByname('p1').Asstring:=trim(s2);
       prepare;
       ExecSQL;
     end;
MessageDlg('更改完毕！', mtInformation,[mbOk], 0);
query10.close;
query10.open;
query12.close;
query12.Open;
end;


procedure TForm3.BitBtn11Click(Sender: TObject);
var s,s1,s2,s3,s4,s5,s6:string;
begin
s1:=query12.FieldValues['bmmc'];
s2:=query12.FieldValues['bmbh'];
s3:=query10.FieldValues['bmbh'];
s5:=query10.FieldValues['bmmc'];
if (s1='') or (s5='') then exit;
s6:='把'+s5+'所有员工合并到'+s1+',请确认!';
if MessageDlg(s6, mtConfirmation, [mbYes, mbNo], 0) = mrYes then
 begin
//更改所有员工的部门名称和部门编号
s:='update lkyg set bmbh='''+s2+''' where bmbh=:p1';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       ParamByname('p1').Asstring:=s3;
       prepare;
       ExecSQL;
     end;
//更改发放记录中所有员工的部门编号
s4:='update ffjl set bmbh='''+s2+''' where bmbh=:p1';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s4);
       ParamByname('p1').Asstring:=s3;
       prepare;
       ExecSQL;
     end;
 MessageDlg('合并完毕！', mtInformation,[mbOk], 0);
 end;
end;

procedure TForm3.TabSheet3Show(Sender: TObject);
var yy,mm,dd:word;
s:string;
begin
decodedate(Date,yy,mm,dd);
s:=inttostr(yy);
if (mm>=1) and (mm<=6) then edit31.Text :=s+'-1-1';
if (mm>=7) and (mm<=12) then edit31.Text :=s+'-7-1';
query13.open;
end;

procedure TForm3.DBGrid11CellClick(Column: TColumn);
var
s1:string;
begin
s1:=query13.FieldValues['bmbh'];
with Query14 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from lkyg where bmbh=:p1');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
end;

procedure TForm3.DBGrid12CellClick(Column: TColumn);
var
s1:string;
begin
if query14.RecordCount =0 then exit;
s1:=query14.FieldValues['ygbh'];
with Query15 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from ffjl where ygbh=:p1');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
with Query16 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from lkyg where ygbh=:p1');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;



end;



procedure TForm3.Edit23Change(Sender: TObject);
var
s,s1:string;
begin
s:=trim(edit23.text)+'*';
with Query14 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from lkyg where xmdm like :p1');
       ParamByname('p1').Asstring:=s;
       prepare;
       open;
     end;
if query14.RecordCount =1 then
  begin
   s1:=query14.FieldValues['ygbh'];
    with Query15 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from ffjl where ygbh=:p1');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
    with Query16 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from lkyg where ygbh=:p1');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
   end;

end;



procedure TForm3.BitBtn12Click(Sender: TObject);
var s,s1,s2,s3:string;
begin
s1:=trim(edit26.text);
s2:=trim(edit24.text);
if (s1='') or (s2='') then exit;
s:='update lkyg set gzmc='''+s1+''' where gzbh=:p1';
//更改工种库中的工种名称
s3:='update gz set gzmc='''+s1+''' where gzbh=:p1';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s3);
       ParamByname('p1').Asstring:=s2;
       prepare;
       ExecSQL;
     end;
//
query17.close;
query17.open;
MessageDlg('更改完毕！', mtInformation,[mbOk], 0);
end;


procedure TForm3.BitBtn13Click(Sender: TObject);
var s,s1,s2:string;
begin
if trim(edit27.text)='' then exit;
if MessageDlg('删除此工种及员工和发放记录，请确认！', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
begin
//删除所有此工种的员工
s1:=trim(edit27.text);
s:='delete from  lkyg where gzbh=:p1';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       ParamByname('p1').Asstring:=s1;
       prepare;
       ExecSQL;
     end;
s2:='delete from  gz where gzbh=:p1';
//删除工种库中的此工种
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s2);
       ParamByname('p1').Asstring:=s1;
       prepare;
       ExecSQL;
     end;
//
s2:='delete from  gzlb where gzbh=:p1';
//删除工种劳保库中的此工种劳保
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s2);
       ParamByname('p1').Asstring:=s1;
       prepare;
       ExecSQL;
     end;
s2:='delete from  ffjl where gzbh=:p1';
//删除发放记录
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s2);
       ParamByname('p1').Asstring:=s1;
       prepare;
       ExecSQL;
     end;
//
query17.close;
query17.open;
MessageDlg('删除完毕！', mtInformation,[mbOk], 0);
 end;
end;

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

procedure TForm3.BitBtn15Click(Sender: TObject);
var
s,s1,s2,s3,s4:string;
begin
s1:=trim(edit25.Text);
s2:=trim(edit30.Text);
s3:=trim(edit32.Text);
s4:=trim(edit10.text);
if (s1='') or (s2='') then exit;
if (s3='') or (s4='') then exit;
//此处要加判断代码：日期是否合法，是否有此工种，否则可能添加新工种
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add('select gzbh from gz');
       Sql.add(' where gzbh=:p1');
       ParamByname('p1').Asstring:=trim(edit25.text);
       prepare;
       open;
     end;
if query8.RecordCount=0 then
    begin
      MessageDlg('工种还不存在！', mtWarning, [mbOK], 0);
      exit;
    end;
if isdate(s4)=false then
    begin
      MessageDlg('日期输入不正确！', mtWarning, [mbOK], 0);
      exit;
    end;
if is4year(s4)=false then
    begin
      MessageDlg('年份请输入四位！！', mtWarning, [mbOK], 0);
      exit;
    end;

//检测是否存在此劳保
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add('select lbmc from gzlb where gzbh=:p1 and lbmc=:p2');
       ParamByname('p1').Asstring:=s1;
       ParamByname('p2').Asstring:=s2;
       prepare;
       open;
     end;
if query8.RecordCount >0 then
    begin
      MessageDlg('劳保用品已经存在！', mtWarning, [mbOK], 0);
      exit;
    end;
//在工种劳保库中增加新的劳保及发放周期
s:='insert into gzlb  values('''+s1+''','''+s2+''','''+s3+''')';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       prepare;
       ExecSQL;
     end;
//为此工种的员工增加初始发放记录
s:='insert into ffjl select lkyg.bmbh,lkyg.gzbh,lkyg.ygbh,gzlb.lbmc,gzlb.ffzq';
s:=s+' from lkyg,gzlb where lkyg.gzbh=gzlb.gzbh and  ';
s:=s+' lbmc='''+s2+'''';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       prepare;
       ExecSQL;
     end;
//修改初始发放日期
s:='update ffjl set ffrq='''+s4+''' where ffrq=#90-1-1#';
with Query8 do
     begin
       close;
       SQL.clear;
       SQL.add(s);
       prepare;
       ExecSQL;
     end;
 MessageDlg('完毕！', mtInformation,[mbOk], 0);
end;



procedure TForm3.DBGrid13CellClick(Column: TColumn);
var s1:string;
begin
s1:=query17.FieldValues['gzbh'];
with Query18 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from gzlb where gzbh=:p1');
       ParamByname('p1').Asstring:=s1;
       prepare;
       open;
     end;
end;

procedure TForm3.Button1Click(Sender: TObject);
var s1,s2,s3:string;
begin
with Query19 do
     begin
       close;
       SQL.clear;
       SQL.add('select * from lkyg');