unit U_FilterHelp;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls,cxGridLevel,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxClasses,
  cxControls, cxGridCustomView, cxGrid, cxStyles, cxCustomData, cxGraphics,
  cxFilter, cxData, cxDataStorage, cxEdit, DB, cxDBData,StrUtils,ADODB,
  XPMan;

type
  TfrmFilterHelp = class(TForm)
    ScrollBox1: TScrollBox;
    Panel1: TPanel;
    Button1: TButton;
    Button2: TButton;
    Label1: TLabel;
    CheckBox1: TCheckBox;
    XPManifest1: TXPManifest;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    procedure  CreateFilers(Tv1:TcxGridDBTableView);
    function GSGetFilters():string;
    procedure GSQryDofilter(ADOQry:TADOQuery;FilterStr:string);
    procedure  TcxGridToExcel(mfileName:string;gridName:TcxGrid);
    { Public declarations }
  end;

var
  frmFilterHelp: TfrmFilterHelp;

implementation

{$R *.dfm}

procedure TfrmFilterHelp.Button1Click(Sender: TObject);
begin
    ModalResult:=1
end;
///////////////////////////////////////////////////////////////////
  //函数功能：利用cxGrid自带的功能导出到excel中
//////////////////////////////////////////////////////////////////
procedure  TfrmFilterHelp.TcxGridToExcel(mfileName:string;gridName:TcxGrid);
var
    saveDialog:TSaveDialog;
begin
    try
        saveDialog:=TSaveDialog.Create(nil);
        saveDialog.Filter:='xls(*.xls)|*.xls|全部(*.*)|*.*';
        saveDialog.Options:=[ofOverwritePrompt];
        saveDialog.FileName:=mfileName;
        if saveDialog.Execute then
            if Assigned(gridName) then
            begin
                try

                    ExportGrid4ToExcel(saveDialog.FileName,gridName);
                except
                    application.MessageBox('创建失败,源文件可能处于编辑状态!','提示信息',0);
                    exit;
                end;
                application.MessageBox('成功导出!','提示信息',0);
            end
            else
               application.MessageBox('导出失败!','提示信息',0);
    finally
        saveDialog.Free;
    end;
end;

procedure TfrmFilterHelp.Button2Click(Sender: TObject);
begin
  ModalResult:=-1;
end;

procedure  TfrmFilterHelp.CreateFilers(Tv1:TcxGridDBTableView);
var
  i,j,z,m,FTop,FLeft,Fdiv,FMod:Integer;// mod 余数，div商
  FLableEdit:TLabeledEdit;

begin
  j:=0;
  for i:=0 to Tv1.ColumnCount-1 do
  begin
    m:=0;
    if Tv1.Columns[i].Visible=False then Continue;
    if not ( (Tv1.DataController.DataSource.DataSet.FieldByName(Tv1.Columns[i].DataBinding.FieldName).DataType=ftBCD) or
       (Tv1.DataController.DataSource.DataSet.FieldByName(Tv1.Columns[i].DataBinding.FieldName).DataType=ftString) ) then  Continue;
    Fdiv:=(j+1) div 3;
    FMod:=(j+1) mod 3;
    FLableEdit:=TLabeledEdit.Create(Self);
    FLableEdit.EditLabel.Caption:=Trim(Tv1.Columns[i].Caption);
    FLableEdit.Name:=Trim(Tv1.Columns[i].DataBinding.FieldName);
    if Tv1.DataController.DataSource.DataSet.FieldByName(Tv1.Columns[i].DataBinding.FieldName).DataType=ftBCD then
    begin
      FLableEdit.EditLabel.Caption:=Trim(Tv1.Columns[i].Caption+'始');
      FLableEdit.Hint:=Trim(Tv1.Columns[i].DataBinding.FieldName);
      FLableEdit.Tag:=1;
    end;
    FLableEdit.Text:='';
    FLableEdit.TabOrder:=j;
    FLableEdit.Parent:=ScrollBox1;
    if FMod>0 then
      FLableEdit.Top:=50*(Fdiv+1)
    else
      FLableEdit.Top:=50*Fdiv;
    if FMod=1 then
      FLableEdit.Left:=29
    else if FMod=2 then
      FLableEdit.Left:=163
    else if FMod=0 then
      FLableEdit.Left:=305;
    if Tv1.DataController.DataSource.DataSet.FieldByName(Tv1.Columns[i].DataBinding.FieldName).DataType=ftBCD then
    begin
      j:=j+1;
      Fdiv:=(j+1) div 3;
      FMod:=(j+1) mod 3;
      FLableEdit:=TLabeledEdit.Create(Self);
      FLableEdit.EditLabel.Caption:=Trim(Tv1.Columns[i].Caption+'止');
      FLableEdit.Hint:=Trim(Tv1.Columns[i].DataBinding.FieldName);
      FLableEdit.Tag:=2;
      FLableEdit.Text:='';
      FLableEdit.TabOrder:=j;
      FLableEdit.Parent:=ScrollBox1;
      if FMod>0 then
        FLableEdit.Top:=50*(Fdiv+1)
      else
        FLableEdit.Top:=50*Fdiv;
      if FMod=1 then
        FLableEdit.Left:=29
      else if FMod=2 then
        FLableEdit.Left:=163
      else if FMod=0 then
        FLableEdit.Left:=305;
    end;
    j:=j+1;
  end;
end;

function TfrmFilterHelp.GSGetFilters():string;
var
  i:Integer;
  FValue:Double;
begin
  Result:='';
  with ScrollBox1 do
  begin
    for i:=0 to ControlCount-1 do
    begin
      if Controls[i] is TLabel then Continue;
      if Controls[i] is TLabeledEdit then
      begin
        if Trim(TLabeledEdit(Controls[i]).Text)<>'' then
        begin
          if TLabeledEdit(Controls[i]).Tag>0 then
          begin
            try
              FValue:=StrToFloat(TLabeledEdit(Controls[i]).Text);
            except
              Application.MessageBox('不能输入非法数字！','提示',0);
              Exit;
            end;
          end;
          if TLabeledEdit(Controls[i]).Tag=1 then
          begin
            Result:=Result+'and '+Controls[i].Hint+'>='+Trim(TLabeledEdit(Controls[i]).Text);
          end else
          if TLabeledEdit(Controls[i]).Tag=2 then
          begin
            Result:=Result+'and '+Controls[i].Hint+'<='+Trim(TLabeledEdit(Controls[i]).Text);
          end else
          begin
            if CheckBox1.Checked then
              Result:=Result+'and '+Controls[i].Name+'='+QuotedStr(Trim(TLabeledEdit(Controls[i]).Text))
            else
              Result:=Result+'and '+Controls[i].Name+' like '+QuotedStr('%'+Trim(TLabeledEdit(Controls[i]).Text)+'%');
          end;

        end;
      end;
    end;
  end;
  if Trim(Result)<>'' then
    Result:=Trim(RightBStr(Result,Length(Result)-4));
end;

procedure TfrmFilterHelp.GSQryDofilter(ADOQry:TADOQuery;FilterStr:string);
begin
  try
    ADOQry.DisableControls;
    with ADOQry do
    begin
      if Trim(FilterStr)='' then
      begin
        Filtered:=False;
      end else
      begin
        Filtered:=False;
        Filter:=FilterStr;
        Filtered:=True;
      end;
    end;
  finally
    ADOQry.EnableControls;
  end;
end;

end.
