attendmanagesql.pas

需要使用Delphi 7.0及以上版本才能将它们打开

unit AttendManageSQL;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids;

type
  TAttendManageSQLFrm = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    ComboBox3: TComboBox;
    ComboBox4: TComboBox;
    ComboBox5: TComboBox;
    ComboBox6: TComboBox;
    ComboBox7: TComboBox;
    ComboBox8: TComboBox;
    Button1: TButton;
    Button2: TButton;
    GroupBox2: TGroupBox;
    attendG: TStringGrid;
    procedure ComboBox3Change(Sender: TObject);
    procedure ComboBox6Change(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  AttendManageSQLFrm: TAttendManageSQLFrm;

implementation
    uses PersonnelDataDM,AttendManage;

{$R *.dfm}

procedure TAttendManageSQLFrm.ComboBox3Change(Sender: TObject);
begin
    edit2.ReadOnly:=false;
    edit2.Color:=clWindow;
end;

procedure TAttendManageSQLFrm.ComboBox6Change(Sender: TObject);
begin
    edit3.ReadOnly:=false;
    edit3.Color:=clWindow;
end;

procedure TAttendManageSQLFrm.Button2Click(Sender: TObject);
begin
    AttendManageSQLFrm.Close;
end;

procedure TAttendManageSQLFrm.Button1Click(Sender: TObject);
var
    i,j:integer;
    condition1,condition2,condition3:string;
    squ,s,s1:string;
begin
   squ:='';
   //条件一
   //查询转换
   if  ComboBox1.Text='职工编号' then
       condition1:='EmployeeID';
   if  ComboBox1.Text='职工姓名' then
       condition1:='Name';
   if  ComboBox1.Text='年月日' then
       condition1:='Time';
   if  ComboBox1.Text='工伤假' then
       condition1:='Job_wound';
   if  ComboBox1.Text='公休假' then
       condition1:='Holiday';
   if  ComboBox1.Text='探亲假' then
       condition1:='H_leave';
   if  ComboBox1.Text='迟到' then
       condition1:='Late';
   if  ComboBox1.Text='早退' then
       condition1:='Leave_early';
   if  ComboBox1.Text='矿工' then
       condition1:='Absence';
   if  ComboBox1.Text='病假' then
       condition1:='S_leave';
   if  ComboBox1.Text='事假' then
       condition1:='Pa_leave';
   if  ComboBox1.Text='婚假' then
       condition1:='W_leave';
   if  ComboBox1.Text='产假' then
       condition1:='M_leave';
   if  ComboBox1.Text='丧假' then
       condition1:='F_leave';
    if  (condition1<>'') and (ComboBox2.text<>'') and (Edit1.Text<>'') then
       if (condition1='Name')or(condition1='Onduty_time')or(condition1='Offduty_time')or(condition1='Time')  then
          squ:=squ+condition1+ComboBox2.text+''''+ Edit1.Text+''''
          else
          squ:=squ+condition1+ComboBox2.text+ Edit1.Text;
          s:=squ;

    //条件二
    if  ComboBox4.Text='职工编号' then
       condition2:='EmployeeID';
   if  ComboBox4.Text='职工姓名' then
       condition2:='Name';
   if  ComboBox4.Text='年月日' then
       condition2:='Time';
   if  ComboBox4.Text='工伤假' then
       condition2:='Job_wound';
   if  ComboBox4.Text='公休假' then
       condition2:='Holiday';
   if  ComboBox4.Text='探亲假' then
       condition2:='H_leave';
   if  ComboBox4.Text='迟到' then
       condition2:='Late';
   if  ComboBox4.Text='早退' then
       condition2:='Leave_early';
   if  ComboBox4.Text='矿工' then
       condition2:='Absence';
   if  ComboBox4.Text='病假' then
       condition2:='S_leave';
   if  ComboBox4.Text='事假' then
       condition2:='Pa_leave';
   if  ComboBox4.Text='婚假' then
       condition2:='W_leave';
   if  ComboBox4.Text='产假' then
       condition2:='M_leave';
   if  ComboBox4.Text='丧假' then
       condition2:='F_leave';
    if  (condition2<>'') and (ComboBox5.text<>'') and (Edit2.Text<>'') then
       if (condition2='Name')or(condition2='Onduty_time')or(condition2='Offduty_time')or(condition2='Time')  then
           if  ComboBox3.Text='并且' then
            s:=s+' and '+condition2+ComboBox5.text+''''+ Edit2.Text+''''
            else
            s:=s+' or '+condition2+ComboBox5.text+''''+ Edit2.Text+''''
     else
          if  ComboBox3.Text='并且' then
             s:=s+' and '+condition2+ComboBox5.text+Edit2.Text
          else
             s:=s+' or '+condition2+ComboBox5.text+Edit2.Text;

   //条件三
   if  ComboBox7.Text='职工编号' then
       condition3:='EmployeeID';
   if  ComboBox7.Text='职工姓名' then
       condition3:='Name';
   if  ComboBox7.Text='年月日' then
       condition3:='Time';
   if  ComboBox7.Text='工伤假' then
       condition3:='Job_wound';
   if  ComboBox7.Text='公休假' then
       condition3:='Holiday';
   if  ComboBox7.Text='探亲假' then
       condition3:='H_leave';
   if  ComboBox7.Text='迟到' then
       condition3:='Late';
   if  ComboBox7.Text='早退' then
       condition3:='Leave_early';
   if  ComboBox7.Text='矿工' then
       condition3:='Absence';
   if  ComboBox7.Text='病假' then
       condition3:='S_leave';
   if  ComboBox7.Text='事假' then
       condition3:='Pa_leave';
   if  ComboBox7.Text='婚假' then
       condition3:='W_leave';
   if  ComboBox7.Text='产假' then
       condition3:='M_leave';
   if  ComboBox7.Text='丧假' then
       condition3:='F_leave';
   if  (condition3<>'') and (ComboBox8.text<>'') and (Edit3.Text<>'') then
       if (condition3='Name')or(condition3='Onduty_time')or(condition3='Offduty_time')or(condition3='Time')  then
           if  ComboBox6.Text='并且' then
            s:=s+' and '+condition3+ComboBox8.text+''''+ Edit3.Text+''''
            else
            s:=s+' or '+condition3+ComboBox8.text+''''+ Edit3.Text+''''
     else
          if  ComboBox6.Text='并且' then                                                  
             s:=s+' and '+condition3+ComboBox8.text+Edit3.Text
          else
             s:=s+' or '+condition3+ComboBox8.text+Edit3.Text;
    s1:='Select * from attend_info where '+s;
    //查询语句
   try          //异常中断处理
   with PersonnelData.ADOQuery1 do
      begin
       close;
       SQL.clear;
       SQL.add(s1);
       open;

       if PersonnelData.ADOQuery1.RecordCount<1 then
         Application.MessageBox('没有找到符合条件的数据？','查询结果',MB_YESNO+MB_ICONQUESTION)
         else
            begin
             with attendG do      //清空显示表格
               begin
                 RowCount:=PersonnelData.ADOQuery1.RecordCount+1;
                 for i:=1 to PersonnelData.ADOQuery1.RecordCount do
                    for j:=0 to 15 do
                     cells[j,i]:='';
                end;
            first;
            with attendG do
             for i:=1 to PersonnelData.ADOQuery1.RecordCount do
               begin
                cells[0,i]:=fieldbyname('Time').AsString;
                cells[1,i]:=fieldbyname('EmployeeID').AsString;
                cells[2,i]:=fieldbyname('Name').AsString;
                cells[3,i]:=inttostr(fieldbyname('Late').asinteger);
                cells[4,i]:=inttostr(fieldbyname('Leave_early').asinteger);
                cells[5,i]:=inttostr(fieldbyname('Absence').asinteger);
                cells[6,i]:=inttostr(fieldbyname('S_leave').asinteger);
                cells[7,i]:=inttostr(fieldbyname('Pa_leave').asinteger);
                cells[8,i]:=inttostr(fieldbyname('Job_wound').asinteger);
                cells[9,i]:=inttostr(fieldbyname('Holiday').asinteger);
                cells[10,i]:=inttostr(fieldbyname('H_leave').asinteger);
                cells[11,i]:=inttostr(fieldbyname('W_leave').asinteger);
                cells[12,i]:=inttostr(fieldbyname('M_leave').asinteger);
                cells[13,i]:=inttostr(fieldbyname('F_leave').asinteger);
                next;
               end;
            end;
       end;
   except
    Application.MessageBox('查询语句有错误','异常错误',mb_iconwarning+mb_ok);
  end;
    

   //修改项目值框属性
   edit2.ReadOnly:=true;
   edit2.Color:=clInfoBk;
   edit3.ReadOnly:=true;
   edit3.Color:=clInfoBk;
end;

procedure TAttendManageSQLFrm.FormCreate(Sender: TObject);
begin
   with attendG do
     begin
     cells[0,0]:='年月';
     cells[1,0]:='职工代码';
     cells[2,0]:='职工姓名';
     cells[3,0]:='迟到';
     cells[4,0]:='早退';
     cells[5,0]:='矿工';
     cells[6,0]:='病假';
     cells[7,0]:='事假';
     cells[8,0]:='工伤假';
     cells[9,0]:='公休假';
     cells[10,0]:='探亲假';
     cells[11,0]:='婚假';
     cells[12,0]:='产假';
     cells[13,0]:='丧假';
     end;
end;

end.