wagemanagesql.pas

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

unit WageManageSQL;

interface

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

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

var
  WageManageSQLFrm: TWageManageSQLFrm;

implementation
  uses PersonnelDataDM,WageManage;

{$R *.dfm}


procedure TWageManageSQLFrm.Button1Click(Sender: TObject);
var
  i,j:integer;
  condition1,condition2,condition3:string;
  squ,s:string;
begin
    squ:='';
   //条件一
   //查询转换
   if  ComboBox1.Text='部门代码' then
       condition1:='DepartID';
   if  ComboBox1.Text='部门名称' then
       condition1:='DepartName';
   if  ComboBox1.Text='职工类型' then
       condition1:='EmployeeClass';
   if  ComboBox1.Text='职工代码' then
       condition1:='EmployeeID';
   if  ComboBox1.Text='职工姓名' then
       condition1:='Name';
   if  ComboBox1.Text='基本工资' then
       condition1:='Base_pay';
   if  ComboBox1.Text='基本奖金' then
       condition1:='Base_prize';
   if  ComboBox1.Text='效益奖金' then
       condition1:='Benefit_prize';
   if  ComboBox1.Text='养老保险' then
       condition1:='E_insurance';
   if  ComboBox1.Text='医疗保险' then
       condition1:='Medicare';
   if  ComboBox1.Text='扣发工资' then
       condition1:='Deprive_pay';
   if  ComboBox1.Text='扣发奖金' then
       condition1:='Deprive_prize';
   if  ComboBox1.Text='考勤扣发' then
       condition1:='Deprive_attend';
   if  ComboBox1.Text='实发工资' then
       condition1:='Pay';
    if  (condition1<>'') and (ComboBox2.text<>'') and (Edit1.Text<>'') then
       if (condition1='Name')or(condition1='DepartName')or(condition1='EmployeeClass')  then
          squ:=squ+condition1+ComboBox2.text+''''+ Edit1.Text+''''
          else
          squ:=squ+condition1+ComboBox2.text+ Edit1.Text;
          s:=squ;

    //条件二
   if  ComboBox4.Text='部门代码' then
       condition2:='DepartID';
   if  ComboBox4.Text='部门名称' then
       condition2:='DepartName';
   if  ComboBox4.Text='职工类型' then
       condition2:='EmployeeClass';
   if  ComboBox4.Text='职工代码' then
       condition2:='EmployeeID';
   if  ComboBox4.Text='职工姓名' then
       condition2:='Name';
   if  ComboBox4.Text='基本工资' then
       condition2:='Base_pay';
   if  ComboBox4.Text='基本奖金' then
       condition2:='Base_prize';
   if  ComboBox4.Text='效益奖金' then
       condition2:='Benefit_prize';
   if  ComboBox4.Text='养老保险' then
       condition2:='E_insurance';
   if  ComboBox4.Text='医疗保险' then
       condition2:='Medicare';
   if  ComboBox4.Text='扣发工资' then
       condition2:='Deprive_pay';
   if  ComboBox4.Text='扣发奖金' then
       condition2:='Deprive_prize';
   if  ComboBox4.Text='考勤扣发' then
       condition2:='Deprive_attend';
   if  ComboBox4.Text='实发工资' then
       condition2:='Pay';
    if  (condition2<>'') and (ComboBox5.text<>'') and (Edit2.Text<>'') then
       if (condition2='Name')or(condition2='DepartName')or(condition2='EmployeeClass')  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:='DepartID';
   if  ComboBox7.Text='部门名称' then
       condition3:='DepartName';
   if  ComboBox7.Text='职工类型' then
       condition3:='EmployeeClass';
   if  ComboBox7.Text='职工代码' then
       condition3:='EmployeeID';
   if  ComboBox7.Text='职工姓名' then
       condition3:='Name';
   if  ComboBox7.Text='基本工资' then
       condition3:='Base_pay';
   if  ComboBox7.Text='基本奖金' then
       condition3:='Base_prize';
   if  ComboBox7.Text='效益奖金' then
       condition3:='Benefit_prize';
   if  ComboBox7.Text='养老保险' then
       condition3:='E_insurance';
   if  ComboBox7.Text='医疗保险' then
       condition3:='Medicare';
   if  ComboBox7.Text='扣发工资' then
       condition3:='Deprive_pay';
   if  ComboBox7.Text='扣发奖金' then
       condition3:='Deprive_prize';
   if  ComboBox7.Text='考勤扣发' then
       condition3:='Deprive_attend';
   if  ComboBox7.Text='实发工资' then
       condition3:='Pay';
    if  (condition3<>'') and (ComboBox8.text<>'') and (Edit3.Text<>'') then
       if (condition3='Name')or(condition3='DepartName')or(condition3='EmployeeClass')  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;
    //查询语句
   try          //异常中断处理
   with PersonnelData.ADOQuery1 do
      begin
       close;
       SQL.clear;
       SQL.add('Select * from wage_manage where '+s);
       open;
       if PersonnelData.ADOQuery1.RecordCount<1 then
         Application.MessageBox('没有找到符合条件的数据？','查询结果',MB_YESNO+MB_ICONQUESTION)
         else
         begin
           with WageManageFrm.wageG do      //清空显示表格
            for i:=1 to 10 do
             for j:=0 to 13 do
            begin
             cells[j,i]:='';
            end;
           first;
           with WageManageFrm.wageG do
             begin
               WageManageFrm.wageG.RowCount:=PersonnelData.ADOQuery1.RecordCount+1;
               for i:=1 to PersonnelData.ADOQuery1.RecordCount do
                  begin
                    cells[0,i]:=inttostr(fieldbyname('DepartID').asinteger);
                      cells[1,i]:=fieldbyname('DepartName').AsString;
                        cells[2,i]:=fieldbyname('EmployeeClass').AsString;
                          cells[3,i]:=fieldbyname('EmployeeID').AsString;
                           cells[4,i]:=fieldbyname('Name').AsString;
                            cells[5,i]:=floattostr(fieldbyname('Base_pay').AsFloat);
                            cells[6,i]:=floattostr(fieldbyname('Base_prize').AsFloat);
                           cells[7,i]:=floattostr(fieldbyname('Benefit_prize').AsFloat);
                          cells[8,i]:=floattostr(fieldbyname('E_insurance').AsFloat);
                         cells[9,i]:=floattostr(fieldbyname('Medicare').AsFloat);
                        cells[10,i]:=floattostr(fieldbyname('Deprive_pay').AsFloat);
                      cells[11,i]:=floattostr(fieldbyname('Deprive_prize').AsFloat);
                     cells[12,i]:=floattostr(fieldbyname('Deprive_attend').AsFloat);
                   cells[13,i]:=floattostr(fieldbyname('Pay').AsFloat);
                 next;
                end;
            end;
            end;
       end;
   except
    Application.MessageBox('查询语句有错误','异常错误',mb_iconwarning+mb_ok);
  end;   

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

procedure TWageManageSQLFrm.Button2Click(Sender: TObject);
begin
    WageManageSQLFrm.Close;
end;

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

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

end.