unit3.pas

< SQL SERVER 2000 案例教程>>,冶金工业出版社,这本书的代码

unit Unit3;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, Buttons, StdCtrls, ExtCtrls, Grids, DBGrids, DBCtrls;

type
  Tmain = class(TForm)
    PagesControl: TPageControl;
    usermanage: TTabSheet;
    basemess: TTabSheet;
    checkaction: TTabSheet;
    checkmess: TTabSheet;
    datamanage: TTabSheet;
    logmanage: TTabSheet;
    Searchuser: TButton;
    addusert: TButton;
    deluser: TButton;
    updateuser: TButton;
    UserTree: TTreeView;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    baseamstart: TEdit;
    Label2: TLabel;
    baseamend: TEdit;
    Label3: TLabel;
    basepmstart: TEdit;
    basepmend: TEdit;
    Label4: TLabel;
    GroupBox2: TGroupBox;
    abnormal: TComboBox;
    GroupBox3: TGroupBox;
    leave: TComboBox;
    GroupBox4: TGroupBox;
    evection: TComboBox;
    BitBtn1: TBitBtn;
    addabnormal: TBitBtn;
    delabnormal: TBitBtn;
    addleave: TBitBtn;
    delleave: TBitBtn;
    addevection: TBitBtn;
    delevection: TBitBtn;
    GroupBox5: TGroupBox;
    Label8: TLabel;
    GroupBox6: TGroupBox;
    Label9: TLabel;
    Edit5: TEdit;
    Label10: TLabel;
    checktype: TComboBox;
    Label11: TLabel;
    Label12: TLabel;
    managecheck: TButton;
    inputcheck: TBitBtn;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    Label13: TLabel;
    checkcode: TEdit;
    GroupBox7: TGroupBox;
    execcheck: TBitBtn;
    BitBtn2: TBitBtn;
    DBGrid2: TDBGrid;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    BitBtn7: TBitBtn;
    BitBtn8: TBitBtn;
    DBGrid1: TDBGrid;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    powertree: TTreeView;
    BitBtn9: TBitBtn;
    BitBtn10: TBitBtn;
    BitBtn11: TBitBtn;
    BitBtn12: TBitBtn;
    BitBtn13: TBitBtn;
    BitBtn14: TBitBtn;
    BitBtn15: TBitBtn;
    startdate: TDateTimePicker;
    enddate: TDateTimePicker;
    checkamstart: TDateTimePicker;
    checkamend: TDateTimePicker;
    checkpmstart: TDateTimePicker;
    checkpmend: TDateTimePicker;
    radioamstart: TRadioButton;
    radiopmstart: TRadioButton;
    radioamend: TRadioButton;
    radiopmend: TRadioButton;
    GroupBox8: TGroupBox;
    ComboBox1: TComboBox;
    BitBtn16: TBitBtn;
    BitBtn17: TBitBtn;
    BitBtn18: TBitBtn;
    starttime: TDateTimePicker;
    endtime: TDateTimePicker;
    SaveDialog1: TSaveDialog;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure oninit();
    procedure oninit1();
    procedure oninit2();
    procedure oninit3();
    procedure oninit4();
    function getdaycount(startdate,enddate:TDateTime):Integer;
    function gettimecount(starttime,endtime:TDateTime):Integer;
    procedure SearchuserClick(Sender: TObject);
    procedure addusertClick(Sender: TObject);
    procedure deluserClick(Sender: TObject);
    procedure updateuserClick(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure addleaveClick(Sender: TObject);
    procedure delleaveClick(Sender: TObject);
    procedure inputcheckClick(Sender: TObject);
    procedure RadioButton1Click(Sender: TObject);
    procedure RadioButton2Click(Sender: TObject);
    procedure RadioButton3Click(Sender: TObject);
    procedure managecheckClick(Sender: TObject);
    procedure execcheckClick(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    //procedure gettimecount(datetime:TDateTime);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  main: Tmain;

implementation

{$R *.dfm}
uses Unit2,Unit4,Unit5,Unit6,Unit7,unit8;

Procedure Tmain.oninit();
var prenode,curnode:TTreeNode;
    item:string;
begin
  UserTree.Items.Clear; //先清空tree control中原有的数据
  //先定义根结点，同时获取其结点指针
  prenode:=UserTree.Items.Add(nil,'系统用户');
  with mydata.Query1 do //使用mydata 中的Query1进行数据操作
  begin
    close;//为了灵活处理，在函数外定义其SQL的内容，以便控制tree control的显示数据
    open;
    while  eof=false  do //循环读取用户密码表中的查询得到的所有数据
    begin
      item:=FieldByName('用户名').AsString;//获取用户密码表中的用户名
      //在根结点下逐个插入用户
      curnode:=UserTree.Items.AddChild(prenode,item);//记下子结点的指针
      item:=FieldByName('权限号').AsString;
      with  mydata.Query_power do //使用query_power获取权限表中的内容
      begin
        close;
        with sql do
        begin
          clear;
          add('select * from 权限表 where 权限名=:power');
          ParamByName('power').AsString:=item;//使用参数化方法添加SQL语句
        end;
        open;
        if  findlast() then//当权限表中存在该权限时，读出该条记录的所有内容
        begin
          item:='权限 : '+item;
          //当权限表中某个字段值为1时则在tree control中添加一个相应的结点
          curnode:=UserTree.Items.AddChild(curnode,item);
          if  FieldByName('更新用户资料').AsInteger=1 then
            UserTree.Items.AddChild(curnode,'更新用户资料')
          {if  FieldByName('更新基本资料').AsInteger=1 then
          begin
            UserTree.Items.AddChild(curnode,'更新基本资料');
          end;   
          if  FieldByName('请假管理').AsInteger=1 then
          begin
            UserTree.Items.AddChild(curnode,'请假管理');
          end;
          if  FieldByName('外出管理').AsInteger=1 then
          begin
            UserTree.Items.AddChild(curnode,'外出管理');
          end;
          if  FieldByName('加班管理').AsInteger=1 then
          begin
            UserTree.Items.AddChild(curnode,'加班管理');
          end;
          if  FieldByName('更改考勤资料').AsInteger=1 then
          begin
            UserTree.Items.AddChild(curnode,'更改考勤资料');
          end;
          if  FieldByName('数据库操作').AsInteger=1 then
          begin
            UserTree.Items.AddChild(curnode,'数据库操作');
          end;
          if  FieldByName('日志删除').AsInteger=1 then
          begin
            UserTree.Items.AddChild(curnode,'日志删除');
          end;}
        end;
      end;
      next;
    end;
  end;
  //选根结点
  prenode.Selected:=true;
end;
procedure Tmain.oninit1();
begin
  with  mydata.Query1 do
  begin
    close;
    with sql do
    begin
      clear;
      //获取上下班时间表中的内容
      add('select * from 上下班时间表');
    end;
    open;
    if  findlast() then//当表中有数据则将它们读出并显示在各文本框中
    begin
      baseamstart.Text:=timetostr(strtodatetime(FieldByName('上午上班时间').AsString));
      baseamend.Text:=timetostr(strtodatetime(FieldByName('上午下班时间').AsString));
      basepmstart.Text:=timetostr(strtodatetime(FieldByName('下午上班时间').AsString));
      basepmend.Text:=timetostr(strtodatetime(FieldByName('下午下班时间').AsString));
    end;
    close;
    //从请假类型表中获取所有请假类型
    with  sql do
    begin
      clear;
      add('select * from 请假类型表');
    end;
    open;
    leave.Items.Clear;
    //循环读取请假类型表中的所有数据，然后将它们显示在combo box中
    while eof=false do
    begin
      leave.Items.Append(trim(FieldByName('类型名').AsString));
      next;
    end;
    close;
    //从加班类型表中获取所有加班类型
    with  sql do
    begin
      clear;
      add('select * from 加班类型表');
    end;
    open;
    abnormal.Items.Clear;
    while eof=false do
    begin
      abnormal.Items.Append(trim(FieldByName('类型名').AsString));
      next;
    end;
    close;
    //从外出类型表中获取所有外出类型
    with  sql do
    begin
      clear;
      add('select * from 外出类型表');
    end;
    open;
    evection.Items.Clear;
    while eof=false do
    begin
      evection.Items.Append(trim(FieldByName('类型名').AsString));
      next;
    end;
  end;
end;
procedure Tmain.oninit2();
begin
  checktype.Items.Clear;
  with  mydata.Query3 do
  begin
    close;
    
    with  sql do
    begin
      clear;
      if  radiobutton1.Checked then
      begin
        add('select * from 请假类型表');
      end;
      if  radiobutton2.Checked then
      begin
        add('select * from 外出类型表');
      end;
      if  radiobutton3.Checked  then
      begin
        add('select * from 加班类型表');
      end;
    end;
    open;
    while eof=false do
    begin
      checktype.Items.Append(trim(FieldbyName('类型名').AsString));
      next;
    end;
  end;
end;
procedure Tmain.oninit3();
begin
  with  mydata.Query4 do
  begin
    close;
    open;
  end;
end;
procedure Tmain.oninit4();
begin
  with  mydata.Query5 do
  begin
    close;
    open;
  end;
end;
function Tmain.getdaycount(startdate,enddate:TDateTime):Integer;
var time1,time2:TDateTime;
    yy1,mm1,dd1,yy2,mm2,dd2:word;
    acount,i:integer;
begin
  acount:=0;
  decodedate(startdate,yy1,mm1,dd1);
  decodedate(enddate,yy2,mm2,dd2);
  for i:=yy1 to yy2-1 do
  begin
    if  ((i mod 4=0) and (i mod 100<>0)) then
    begin
      acount:=acount+366;
    end else
    begin
      acount:=acount+365;
    end;
  end;

  for i:=1 to mm2 do
  begin
    case i of
      1,3,5,7,8,10,12:acount:=acount+31;
      4,6,9,11:acount:=acount+30;
      2:acount:=acount+28;
    end;
    if  ((yy2 mod 4=0) and (yy2 mod 100<>0)) then
    begin
      acount:=acount+1;
    end;
  end;
  acount:=acount+dd2;
  for i:=1 to mm1 do
  begin
    case i of
      1,3,5,7,8,10,12:acount:=acount-31;
      4,6,9,11:acount:=acount-30;
      2:acount:=acount-28;
    end;
    if  ((yy1 mod 4=0) and (yy1 mod 100<>0)) then
    begin
      acount:=acount-1;
    end;
  end;
  acount:=acount-dd1;
  getdaycount:=acount;
end;
function Tmain.gettimecount(starttime,endtime:TDateTime):Integer;
var   hh1,mm1,ss1,cc1,hh2,mm2,ss2,cc2:word;
begin
  decodetime(starttime,hh1,mm1,ss1,cc1);
  decodetime(endtime,hh2,mm2,ss2,cc2);
  gettimecount:=(hh2-hh1)*60+mm2-mm1;
end;
procedure Tmain.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  application.Terminate;
end;
procedure Tmain.SearchuserClick(Sender: TObject);
begin
  Searchuserform.Show();
  SearchUserForm.oninit();
end;

procedure Tmain.addusertClick(Sender: TObject);
begin
  adduserform.Show();
  adduserform.oninit();
end;

procedure Tmain.deluserClick(Sender: TObject);
var item:string;
    prenode:TTreeNode;//用来记录根结点，以便进行判断
begin
  prenode:=usertree.Items.GetFirstNode; //获取根结点
  //判断所选择的结点的父结点是不是根结点
  if  (usertree.Selected.Parent=prenode) then
  begin
    with  mydata.Query1 do//使用Query1执行SQL操作
    begin
      close;
      with  sql do
      begin
        clear;
        //设置执行删除操作的SQL语句
        add('delete from  用户密码表 where 用户名=:user');
        ParamByName('user').AsString:=trim(usertree.Selected.Text);
      end;
      execsql;
      close;
      //重写SQL语句，以更新显示数据
      with  sql do
      begin
        add('select * from 用户密码表');
      end;
      open;
      //更新主窗体的数据
      oninit();
    end;
  end else
  begin
    //当选择的结点不是用户结点进发出提示
    showmessage('请选择一个用户');
  end;
end;

procedure Tmain.updateuserClick(Sender: TObject);
begin
  changepasswordForm.show();
end;

procedure Tmain.BitBtn1Click(Sender: TObject);
begin
  settimeform.Show();
end;

procedure Tmain.addleaveClick(Sender: TObject);
begin
  addleaveform.Show();
end;

procedure Tmain.delleaveClick(Sender: TObject);
var temp:string;
begin
  //判断是否有类型被选择
  if  trim(leave.Text)='' then
  begin
    showmessage('请先选择一个类型');