pwfilterfrm.pas

考勤管理是企业内部管理的重要环节和基础

unit PWFilterFrm;

interface

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

resourcestring
  sDepartment='<全部部门>';
  sName='没有此人!';

type TFilter=record
  result:boolean;
  FieldCaption, FieldName:String;
  FieldValue:Variant;
end;

type
  TPWFilterForm = class(TForm)
    GroupBox1: TGroupBox;
    ListBox1: TListBox;
    Button1: TButton;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Label1: TLabel;
    dblcMakeNo: TDBLookupComboBox;
    dblcGoodsID: TDBLookupComboBox;
    dblcItems: TDBLookupComboBox;
    lbDepartment: TLabel;
    lbEmploy: TLabel;
    lbMakeNo: TLabel;
    lbGoodsID: TLabel;
    lbItems: TLabel;
    GroupBox2: TGroupBox;
    ListView: TListView;
    cbDepartment: TComboBox;
    cbName: TComboBox;
    rgSign: TRadioGroup;
    procedure ListBox1Click(Sender: TObject);
//    procedure dblcDepartmentClick(Sender: TObject);
//    procedure dblcEmployClick(Sender: TObject);
    procedure dblcGoodsIDClick(Sender: TObject);
    procedure dblcMakeNoClick(Sender: TObject);
    procedure dblcItemsClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cbDepartmentChange(Sender: TObject);
    procedure cbNameExit(Sender: TObject);
  private
    { Private declarations }
    Condition:array [0..4] of TFilter;
    function GetFilter:string;
  public
    { Public declarations }
    property Filter: string read GetFilter;
  end;

var
  PWFilterForm: TPWFilterForm;

implementation

uses unDM2;

{$R *.DFM}

function TPWFilterForm.GetFilter:string;
var
  i:integer;
  s:string;
begin
  Result:='';
  s:='';
  if rgsign.ItemIndex=1 then
    s:= 'Employ.Sign=True ';
  if rgsign.ItemIndex=2 then
    s:= 'Employ.Sign=False ';
  for i:=0 to 4 do
    if Condition[i].result then begin
      if Condition[i].FieldName='GoodsID' then
        s:=''''+Condition[i].FieldValue+''''
      else
        s:=Condition[i].FieldValue;
      Result:=Result+Condition[i].FieldName+'='+s;
      if (i+1<5) and condition[i+1].result then
        result:=result+' And ';
    end else
      break;
  if result='' then
    result:=s;
end;

procedure TPWFilterForm.ListBox1Click(Sender: TObject);
begin
  case ListBox1.ItemIndex of
    0:  begin
      lbDepartment.Visible:=True;
      cbDepartment.Visible:=True;
      lbEmploy.Visible:=False;
      cbName.Visible:=False;
      lbMakeNo.Visible:=False;
      dblcMakeNo.Visible:=False;
      lbGoodsID.Visible:=False;
      dblcGoodsID.Visible:=False;
      lbItems.Visible:=False;
      dblcItems.Visible:=False;
    end;
    1:  begin
      DM2.qryEmpID.Close;
      DM2.qryEmpID.SQL.Clear;
      if cbDepartment.ItemIndex=0 then begin
        if rgsign.ItemIndex=0 then
          DM2.qryEmpID.SQL.Add('select Employ.empID,Employ.Name,Employ.Department,Department.Name as DepName from Employ,Department where Employ.Department=Department.DepartmentID');
        if rgsign.ItemIndex=1 then
          DM2.qryEmpID.SQL.Add('select Employ.empID,Employ.Name,Employ.Department,Department.Name as DepName from Employ,Department where Employ.Department=Department.DepartmentID and sign=true');
        if rgsign.ItemIndex=2 then
          DM2.qryEmpID.SQL.Add('select Employ.empID,Employ.Name,Employ.Department,Department.Name as DepName from Employ,Department where Employ.Department=Department.DepartmentID and sign=False');
      end else begin
        if rgsign.ItemIndex=0 then begin
          DM2.qryEmpID.SQL.Add('select Employ.empID,Employ.Name,Employ.Department,Department.Name as DepName from Employ,Department');
          DM2.qryEmpID.SQL.Add(' where Employ.Department=Department.DepartmentID and Department in');
          DM2.qryEmpID.SQL.Add('(select departmentID from department where name=:name)');
          DM2.qryEmpID.ParamByName('Name').Value:=cbDepartment.Items.Strings[cbDepartment.ItemIndex];
        end;
        if rgsign.ItemIndex=1 then begin
          DM2.qryEmpID.SQL.Add('select Employ.empID,Employ.Name,Employ.Department,Department.Name as DepName from Employ,Department');
          DM2.qryEmpID.SQL.Add(' where sign=true and Employ.Department=Department.DepartmentID and Department in');
          DM2.qryEmpID.SQL.Add('(select departmentID from department where name=:name)');
          DM2.qryEmpID.ParamByName('Name').Value:=cbDepartment.Items.Strings[cbDepartment.ItemIndex];
        end;
        if rgsign.ItemIndex=2 then begin
          DM2.qryEmpID.SQL.Add('select Employ.empID,Employ.Name,Employ.Department,Department.Name as DepName from Employ,Department');
          DM2.qryEmpID.SQL.Add(' where sign=False and Employ.Department=Department.DepartmentID and Department in');
          DM2.qryEmpID.SQL.Add('(select departmentID from department where name=:name)');
          DM2.qryEmpID.ParamByName('Name').Value:=cbDepartment.Items.Strings[cbDepartment.ItemIndex];
        end;
      end;
      DM2.qryEmpID.Open;
      cbName.Items.Clear;
      while not DM2.qryEmpID.Eof do begin
        cbName.Items.Add(DM2.qryEmpID.FieldByName('Name').Value);
        DM2.qryEmpID.Next;
      end;
      lbDepartment.Visible:=False;
      cbDepartment.Visible:=False;
      lbEmploy.Visible:=True;
      cbName.Visible:=True;
      lbMakeNo.Visible:=False;
      dblcMakeNo.Visible:=False;
      lbGoodsID.Visible:=False;
      dblcGoodsID.Visible:=False;
      lbItems.Visible:=False;
      dblcItems.Visible:=False;
    end;
    2:  begin
      lbDepartment.Visible:=False;
      cbDepartment.Visible:=False;
      lbEmploy.Visible:=False;
      cbName.Visible:=False;
      lbMakeNo.Visible:=True;
      dblcMakeNo.Visible:=True;
      lbGoodsID.Visible:=False;
      dblcGoodsID.Visible:=False;
      lbItems.Visible:=False;
      dblcItems.Visible:=False;
    end;
    3:  begin
      if dblcMakeNO.KeyValue<>null then begin
        DM2.qryFilter.Close;
        DM2.qryFilter.SQL.Clear;
        DM2.qryFilter.SQL.Add('select GoodsID from ');
        DM2.qryFilter.SQL.Add('MakeList where MakeID=:MakeID Order by GoodsID');
        DM2.qryFilter.Params[0].AsInteger:=dblcMakeNo.KeyValue;
        DM2.qryFilter.Open;
        dblcGoodsID.ListField:=DM2.qryFilter.Fields[0].FieldName;
        dblcGoodsID.KeyField:=DM2.qryFilter.Fields[0].FieldName;
        dblcGoodsID.ListSource:=DM2.dsFilter;
      end else begin
        dblcGoodsID.KeyField:='GoodsID';
        dblcGoodsID.ListField:='GoodsID';
        dblcGoodsID.ListSource:=DM2.dsWorkPro;
        dblcGoodsID.Refresh;
      end;
      lbDepartment.Visible:=False;
      cbDepartment.Visible:=False;
      lbEmploy.Visible:=False;
      cbName.Visible:=False;
      lbMakeNo.Visible:=False;
      dblcMakeNo.Visible:=False;
      lbGoodsID.Visible:=True;
      dblcGoodsID.Visible:=True;
      lbItems.Visible:=False;
      dblcItems.Visible:=False;
    end;
    4:  begin
      if dblcGoodsID.KeyValue<>null then begin
        DM2.qryFilter1.Close;
        DM2.qryFilter1.SQL.Clear;
        DM2.qryFilter1.SQL.Add('select No1,Name,Items from WorkProItems where GoodsID=:GoodsID Order by Items');
        DM2.qryFilter1.Params[0].AsString:=dblcGoodsID.KeyValue;
        DM2.qryFilter1.Open;
        dblcItems.ListField:=DM2.qryFilter1.Fields[2].FieldName+';'+DM2.qryFilter1.Fields[1].FieldName;
        dblcItems.KeyField:=DM2.qryFilter1.Fields[0].FieldName;
        dblcItems.ListSource:=DM2.dsFilter1;
      end;
      lbDepartment.Visible:=False;
      cbDepartment.Visible:=False;
      lbEmploy.Visible:=False;
      cbName.Visible:=False;
      lbMakeNo.Visible:=False;
      dblcMakeNo.Visible:=False;
      lbGoodsID.Visible:=False;
      dblcGoodsID.Visible:=False;
      lbItems.Visible:=True;
      dblcItems.Visible:=True;
    end;
  end;
end;

procedure TPWFilterForm.dblcGoodsIDClick(Sender: TObject);
var
  i:integer;
begin
  for i:=0 to 4 do
    if condition[i].result then begin
      if condition[i].FieldCaption=lbGoodsID.Caption then begin
        break;
      end;
    end else begin
      condition[i].result:=true;
      condition[i].FieldName:='PieceWork.GoodsID';
      condition[i].FieldCaption:=lbGoodsID.Caption;
      Listview.Items.Add;
      break;
    end;
  listview.Items.Item[i].Caption:=lbGoodsID.Caption;
  ListView.Items.Item[i].SubItems.Clear;
  ListView.Items.Item[i].SubItems.Add(dblcGoodsID.Text);
  condition[i].FieldValue:=''''+dblcGoodsID.KeyValue+'''';
end;

procedure TPWFilterForm.dblcMakeNoClick(Sender: TObject);
var
  i:integer;
begin
  for i:=0 to 4 do
    if condition[i].result then begin
      if condition[i].FieldCaption=lbMakeNo.Caption then begin
        break;
      end;
    end else begin
      condition[i].result:=true;
      condition[i].FieldName:='PieceWork.MakeID';
      condition[i].FieldCaption:=lbMakeNo.Caption;
      Listview.Items.Add;
      break;
    end;
  listview.Items.Item[i].Caption:=lbMakeNo.Caption;
  ListView.Items.Item[i].SubItems.Clear;
  ListView.Items.Item[i].SubItems.Add(dblcMakeNo.Text);
  condition[i].FieldValue:=dblcMakeNo.KeyValue;
end;

procedure TPWFilterForm.dblcItemsClick(Sender: TObject);
var
  i:integer;
begin
  for i:=0 to 4 do
    if condition[i].result then begin
      if condition[i].FieldCaption=lbItems.Caption then begin
        break;
      end;
    end else begin
      condition[i].result:=true;
      condition[i].FieldName:='PieceWork.Items';
      condition[i].FieldCaption:=lbItems.Caption;
      Listview.Items.Add;
      break;
    end;
  listview.Items.Item[i].Caption:=lbItems.Caption;
  ListView.Items.Item[i].SubItems.Clear;
  ListView.Items.Item[i].SubItems.Add(dblcItems.Text);
  condition[i].FieldValue:=dblcItems.KeyValue;
end;

procedure TPWFilterForm.Button1Click(Sender: TObject);
var
  i:integer;
begin
  Listview.Items.Clear;
//  dblcDepartment.ListVisible:=False;
  for i:=0 to 4 do
    condition[i].result:=False;
end;

procedure TPWFilterForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action:=caFree;
end;

procedure TPWFilterForm.FormDestroy(Sender: TObject);
begin
  DM2.qryMakeNo1.Close;
  PWFilterForm:=Nil;
end;

procedure TPWFilterForm.FormCreate(Sender: TObject);
begin
  cbDepartment.Items.Add(Pchar(sDepartment));
  DM2.qryMakeNo.Close;
  DM2.qryMakeNo.SQL.Clear;
  DM2.qryMakeNo.SQL.Add('select Name, DepartmentID from department');
  Dm2.qrymakeNo.Open;
  While Not DM2.qryMakeNo.Eof do begin
    cbDepartment.Items.Add(DM2.qryMakeNo.Fields[0].AsString);
    DM2.qryMakeNo.Next;
  end;
  cbDepartment.ItemIndex:=0;
  DM2.qryMakeNo1.Open;
end;

procedure TPWFilterForm.cbDepartmentChange(Sender: TObject);
Var
  i:integer;
begin
  DM2.qryEmpID.Close;
  DM2.qryEmpID.SQL.Clear;
  if cbDepartment.ItemIndex=0 then
    DM2.qryEmpID.SQL.Add('select Employ.empID,Employ.Name,Employ.Department,Department.Name as DepName from Employ,Department where Employ.Department=Department.DepartmentID')
  else begin
    DM2.qryEmpID.SQL.Add('select Employ.empID,Employ.Name,Employ.Department,Department.Name as DepName from Employ,Department');
    DM2.qryEmpID.SQL.Add(' where Employ.Department=Department.DepartmentID and Department in');
    DM2.qryEmpID.SQL.Add('(select departmentID from department where name=:name)');
    DM2.qryEmpID.ParamByName('Name').Value:=cbDepartment.Items.Strings[cbDepartment.ItemIndex];
  end;
  DM2.qryEmpID.Open;
  cbName.Items.Clear;
  while not DM2.qryEmpID.Eof do begin
    cbName.Items.Add(DM2.qryEmpID.FieldByName('Name').Value);
    DM2.qryEmpID.Next;
  end;
  for i:=0 to 4 do
    if condition[i].result then begin
      if condition[i].FieldCaption=lbDepartment.Caption then begin
        break;
      end;
    end else begin
      condition[i].result:=true;
      if cbDepartment.ItemIndex=0 then begin
        condition[i].FieldName:='';
        condition[i].result:=False;
      end else begin
        condition[i].FieldName:='PieceWork.Department';
        condition[i].result:=true;
      end;
      condition[i].FieldCaption:=lbDepartment.Caption;
      Listview.Items.Add;
      break;
    end;
    listview.Items.Item[i].Caption:=lbDepartment.Caption;
    ListView.Items.Item[i].SubItems.Clear;
    ListView.Items.Item[i].SubItems.Add(cbDepartment.Text);
    if DM2.qryMakeNo.Locate('Name', cbDepartment.Text,
      [loCaseInsensitive, loPartialKey]) then begin
        condition[i].FieldValue:=DM2.qryMakeNo.Fields[1].AsInteger;
    end;
end;

procedure TPWFilterForm.cbNameExit(Sender: TObject);
Var
  i:integer;
begin
  DM2.qryEmpID.First;
  if not DM2.qryEmpID.Locate('Name', cbName.Text,
    [loCaseInsensitive, loPartialKey]) then begin
    if ActiveControl<>bitbtn2 then begin
      MessageDlg(Pchar(sName), mtInformation, [mbOK], 0);
      cbName.SetFocus;
    end;
  end else begin
    for i:=0 to 4 do
      if condition[i].result then begin
        if condition[i].FieldCaption=lbEmploy.Caption then begin
          break;
        end;
      end else begin
        condition[i].result:=true;
        condition[i].FieldName:='PieceWork.EmpID';
        condition[i].FieldCaption:=lbEmploy.Caption;
        Listview.Items.Add;
        break;
      end;
  listview.Items.Item[i].Caption:=lbEmploy.Caption;
  ListView.Items.Item[i].SubItems.Clear;
  ListView.Items.Item[i].SubItems.Add(cbName.Text);
  condition[i].FieldValue:=DM2.qryEmpID.FieldByName('EmpID').Value;
  end;
end;

end.