{ rxfilterby unit

  Copyright (C) 2005-2017 Lagunov Aleksey alexs75@yandex.ru and Lazarus team
  original conception from rx library for Delphi (c)

  This library is free software; you can redistribute it and/or modify it
  under the terms of the GNU Library General Public License as published by
  the Free Software Foundation; either version 2 of the License, or (at your
  option) any later version with the following modification:

  As a special exception, the copyright holders of this library give you
  permission to link this library with independent modules to produce an
  executable, regardless of the license terms of these independent modules,and
  to copy and distribute the resulting executable under terms of your choice,
  provided that you also meet, for each linked independent module, the terms
  and conditions of the license of that module. An independent module is a
  module which is not derived from or based on this library. If you modify
  this library, you may extend this exception to your version of the library,
  but you are not obligated to do so. If you do not wish to do so, delete this
  exception statement from your version.

  This program is distributed in the hope that it will be useful, but WITHOUT
  ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
  FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License
  for more details.

  You should have received a copy of the GNU Library General Public License
  along with this library; if not, write to the Free Software Foundation,
  Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
}

unit rxfilterby;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, rxdbgrid, LResources, Forms, Controls, Graphics,
  Dialogs, StdCtrls, db;

type

  { TrxFilterByForm }

  TrxFilterByForm = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    ComboBox1: TComboBox;
    ComboBox10: TComboBox;
    ComboBox11: TComboBox;
    ComboBox12: TComboBox;
    ComboBox13: TComboBox;
    ComboBox14: TComboBox;
    ComboBox15: TComboBox;
    ComboBox16: TComboBox;
    ComboBox17: TComboBox;
    ComboBox18: TComboBox;
    ComboBox19: TComboBox;
    ComboBox2: TComboBox;
    ComboBox20: TComboBox;
    ComboBox21: TComboBox;
    ComboBox22: TComboBox;
    ComboBox23: TComboBox;
    ComboBox24: TComboBox;
    ComboBox25: TComboBox;
    ComboBox26: TComboBox;
    ComboBox27: TComboBox;
    ComboBox3: TComboBox;
    ComboBox4: TComboBox;
    ComboBox5: TComboBox;
    ComboBox6: TComboBox;
    ComboBox7: TComboBox;
    ComboBox8: TComboBox;
    ComboBox9: TComboBox;
    Edit1: TComboBox;
    Edit2: TComboBox;
    Edit3: TComboBox;
    Edit4: TComboBox;
    Edit5: TComboBox;
    Edit6: TComboBox;
    Edit7: TComboBox;
    Edit8: TComboBox;
    Edit9: TComboBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure ComboBoxChange(Sender: TObject);
    procedure EditChange(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    Combo_1 : Array[1..9] of TComboBox;
    Combo_2 : Array[1..9] of TComboBox;
    Edit_1  : Array[1..9] of TComboBox;
    Combo_3 : Array[1..9] of TComboBox;

    FGrid : TRxDBGrid;
    procedure ClearALL(AGrid : TRxDBGrid);
    function  FindCombo(CB:TComboBox):Integer;
    function FindEdit(ED: TComboBox): Integer;
  public
    function Execute(AGrid : TRxDBGrid; var FilterStr : String; var LastFilter : TstringList):Boolean;
  end;

var
  rxFilterByForm: TrxFilterByForm;

implementation
uses rxdconst, rxstrutils, DBGrids;

{$R *.lfm}

{ TrxFilterByForm }

procedure TrxFilterByForm.Button2Click(Sender: TObject);
begin
  ModalResult := mrCancel;
end;

procedure TrxFilterByForm.Button3Click(Sender: TObject);
begin
  ClearALL(FGrid);
end;

procedure TrxFilterByForm.ComboBoxChange(Sender: TObject);
var
  CBN : Integer;
  CB  : TComboBox;
begin
  CB  := (Sender AS TComboBox);
  CBN := FindCombo(CB);
  if CBN=0 Then Exit;
  if (CB.Text=' IS NULL ') Or (CB.Text=' IS NOT NULL ') Then
  begin
     Edit_1[CBN].Text    := '';
     Edit_1[CBN].Enabled := False;
     Edit_1[CBN].Color   := clInactiveCaption;
  end
  else
  begin
     Edit_1[CBN].Enabled := True;
     Edit_1[CBN].Color   := clWindow;
  end;
end;

procedure TrxFilterByForm.EditChange(Sender: TObject);
var
  EDN : Integer;
  ED  : TComboBox;
begin
  ED  := (Sender AS TComboBox);
  EDN := FindEdit(ED);
  if EDN=0 Then Exit;
  if ED.Text='' Then Combo_1[EDN].ItemIndex:=-1;
end;

procedure TrxFilterByForm.FormCreate(Sender: TObject);
begin
  Label1.Caption:=sRxFilterFormSelectExp;
  Label2.Caption:=sRxFilterFormOnField;
  Label3.Caption:=sRxFilterFormOperaion;
  Label4.Caption:=sRxFilterFormCondition;
  Label5.Caption:=sRxFilterFormOperand;
  Label6.Caption:=sRxFilterFormEnd;
  Button3.Caption:=sRxFilterFormClear;
  Button2.Caption:=sRxFilterFormCancel;
  Button1.Caption:=sRxFilterFormApply;
end;

procedure TrxFilterByForm.Button1Click(Sender: TObject);
begin
  ModalResult := mrOK;
end;

procedure TrxFilterByForm.ClearALL(AGrid: TRxDBGrid);
var
  i , wsb, w, wt: Integer;
begin
  //*****************************************************************************
  Combo_1[1].Items.Clear;
  Combo_1[1].Items.Add('');

  wsb:= 30;  //ширина скроллбара
  w := Combo_1[1].Width - wsb;
  for i := 0 To AGrid.Columns.Count-1 do
  begin
    if Assigned(AGrid.Columns[i].Field) and (AGrid.Columns[i].Field.FieldKind=fkData) and (AGrid.Columns[i].Visible) then
    begin
      Combo_1[1].Items.AddObject(AGrid.Columns[i].Title.Caption, AGrid.Columns[i].Field);
      Edit_1[1].Items.AddObject(AGrid.Columns[i].Title.Caption, AGrid.Columns[i].Field);

      wt := Canvas.TextWidth(AGrid.Columns[i].Title.Caption);
        if wt > w then
          w := wt;
    end;
  end;

  Combo_1[1].ItemIndex := 0;
  Combo_1[1].ItemWidth := w + wsb;

  for i := 2 To 9 do
  begin
    Combo_1[i].Items.Assign(Combo_1[1].Items);
    Combo_1[i].ItemIndex := 0;
    Combo_1[i].ItemWidth := w + wsb;
  end;

  Combo_2[1].Items.Clear;
  Combo_2[1].Items.Add(' = ');
  Combo_2[1].Items.Add(' > ');
  Combo_2[1].Items.Add(' < ');
  Combo_2[1].Items.Add(' >= ');
  Combo_2[1].Items.Add(' <= ');
  Combo_2[1].Items.Add(' <> ');
  Combo_2[1].Items.Add(' LIKE ');
  Combo_2[1].Items.Add(' IS NULL ');
  Combo_2[1].Items.Add(' IS NOT NULL ');
  Combo_2[1].ItemIndex := 0;
  for i := 2 To 9 do
  begin
    Combo_2[i].Items.Assign(Combo_2[1].Items);
    Combo_2[i].ItemIndex := 0;
  end;
  for i := 1 To 9 do
  begin
    Combo_3[i].ItemIndex := 0;
  end;
  for i := 1 To 9 do Edit_1[i].Text := '';
 //*****************************************************************************
end;

function TrxFilterByForm.Execute(AGrid: TRxDBGrid; var FilterStr: String;
  var LastFilter: TstringList): Boolean;
var
  X  : Integer;
  P  : Integer;
  S, S1 : String;
  SD : String;
  C : TColumn;
  C1: TRxColumn;
begin
  Result := False;
  //*****************************************************************************
  Combo_1[1]:= ComboBox1;
  Combo_1[2]:= ComboBox4;
  Combo_1[3]:= ComboBox7;
  Combo_1[4]:= ComboBox10;
  Combo_1[5]:= ComboBox13;
  Combo_1[6]:= ComboBox16;
  Combo_1[7]:= ComboBox19;
  Combo_1[8]:= ComboBox22;
  Combo_1[9]:= ComboBox25;

  Combo_2[1]:= ComboBox2;
  Combo_2[2]:= ComboBox5;
  Combo_2[3]:= ComboBox8;
  Combo_2[4]:= ComboBox11;
  Combo_2[5]:= ComboBox14;
  Combo_2[6]:= ComboBox17;
  Combo_2[7]:= ComboBox20;
  Combo_2[8]:= ComboBox23;
  Combo_2[9]:= ComboBox26;

  Combo_3[1]:= ComboBox3;
  Combo_3[2]:= ComboBox6;
  Combo_3[3]:= ComboBox9;
  Combo_3[4]:= ComboBox12;
  Combo_3[5]:= ComboBox15;
  Combo_3[6]:= ComboBox18;
  Combo_3[7]:= ComboBox21;
  Combo_3[8]:= ComboBox24;
  Combo_3[9]:= ComboBox27;
  Combo_3[9].Visible := False;

  Edit_1[1] := Edit1;
  Edit_1[2] := Edit2;
  Edit_1[3] := Edit3;
  Edit_1[4] := Edit4;
  Edit_1[5] := Edit5;
  Edit_1[6] := Edit6;
  Edit_1[7] := Edit7;
  Edit_1[8] := Edit8;
  Edit_1[9] := Edit9;

 //*****************************************************************************
  FGrid := AGrid;
  ClearALL(FGrid);
  if LastFilter.Count > 0 Then
  begin
    for X := 0 To LastFilter.Count-1 do
    begin
      S := LastFilter.Strings[X];
      P := Pos('|||',S);
      if P > 0 Then
      begin
        S1:=System.Copy(S,1,P-1);
        C:=FGrid.ColumnByFieldName(S1);
        Combo_1[X+1].ItemIndex := Combo_1[X+1].Items.IndexOf(C.Title.Caption);
        System.Delete(S,1,P+2);
      end;

      P := Pos('|||',S);
      if P > 0 Then
      begin
        SD:=System.Copy(S,1,P-1);
        Combo_2[X+1].ItemIndex :=  Combo_2[X+1].Items.IndexOf(System.Copy(S,1,P-1));
        System.Delete(S,1,P+2);
        if (SD=' IS NULL ') or (SD=' IS NOT NULL ') Then
        Begin
          Edit_1[X+1].Text:= '';
          Edit_1[X+1].Enabled := False;
          Edit_1[X+1].Color   := clInactiveCaption;
        End;
      end;

      P := Pos('|||',S);
      if P > 0 then
      begin
        Edit_1[X+1].Text := System.Copy(S,1,P-1);
        System.Delete(S,1,P+2);
      end;
      Combo_3[X+1].ItemIndex := Combo_3[X+1].Items.IndexOf(S);

      if Combo_3[X+1].ItemIndex = -1 Then Combo_3[X+1].ItemIndex := 0;
    end;
  end;

  if ShowModal = mrOK Then
  begin
    Result    := True;
    FilterStr := '';
    LastFilter.Clear;
    for X := 1 to 9 Do
    begin
      if  (Combo_1[X].Text <> '') and (Combo_2[X].Text <> '') then
      begin
        if (Edit_1[X].Enabled=False) or (Edit_1[X].Text <> '') Then
        begin
          if X>1 Then
            FilterStr := FilterStr+Combo_3[X-1].Text+' ';

          C:=FGrid.ColumnByCaption(Combo_1[X].Text);

          if Edit_1[X].Items.IndexOf(Edit_1[X].Text)>-1 then
          begin
            C1:=FGrid.ColumnByCaption(Edit_1[X].Text);
            FilterStr := FilterStr+'('+C.FieldName+Combo_2[X].Text+C1.FieldName+') ';
          end
          else
          if Pos('NULL', Combo_2[X].Text) > 0 then
            FilterStr := FilterStr+'('+C.FieldName+Combo_2[X].Text+') '
          else
          case C.Field.DataType of
            ftDateTime   ,
            ftDate       : FilterStr := FilterStr+'('+C.FieldName+Combo_2[X].Text+Char(39)+Copy(Edit_1[X].Text,7,4)+Copy(Edit_1[X].Text,3,4)+Copy(Edit_1[X].Text,1,2)+Copy(Edit_1[X].Text,11,9)+Char(39)+') ';
            ftUnknown    : FilterStr := FilterStr+'('+C.FieldName+Combo_2[X].Text+Edit_1[X].Text+') ';
            ftTime,
            ftString,
            ftMemo       : FilterStr := FilterStr+'('+C.FieldName+Combo_2[X].Text+QuotedString(Edit_1[X].Text, '''')+') ';
          else
            FilterStr := FilterStr+'('+C.FieldName+Combo_2[X].Text+Edit_1[X].Text+') ';
          end;
          LastFilter.Add(C.FieldName+'|||'+Combo_2[X].Text+'|||'+Edit_1[X].Text+'|||'+Combo_3[X].Text);
        end;
      end;
    end;
  end;
end;

Function  TrxFilterByForm.FindCombo(CB:TComboBox):Integer;
var
  X : Integer;
begin
  Result :=0;
  for X := 1 to 9 do
  begin
    if Combo_2[X]=CB Then
    begin
      Result := X;
      Exit;
    end;
  end;
end;

function  TrxFilterByForm.FindEdit(ED:TComboBox):Integer;
var
 X : Integer;
begin
  Result :=0;
  for X := 1 to 9 do
  begin
    if Edit_1[X]=ED then
    begin
      Result := X;
      Exit;
    end;
  end;
end;

end.