unit pfm_viewwizard;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, Db, DBTables, StdCtrls, Buttons, ComCtrls, pvar_lib;

type
  Tfm_viewwizard = class(TForm)
    DataSource1: TDataSource;
    Query1: TQuery;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    ComBox1: TComboBox;
    ComBox3: TComboBox;
    ComBox4: TComboBox;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    ComBox2: TComboBox;
    LView1: TListView;
    DBGrid1: TDBGrid;
    procedure FormCreate(Sender: TObject);
    procedure ComBox1Change(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure BitBtn5Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fm_viewwizard: Tfm_viewwizard;
sViewName:string;
sViewColName:TStringList;
implementation

{$R *.DFM}

procedure Tfm_viewwizard.FormCreate(Sender: TObject);
var
newCol:TListColumn;
i:integer;
begin

        //設置dbgrid的中文標題
         for i:=0 to dbgrid1.Columns.Count-1 do
          dbgrid1.Columns.Items[i].Title.caption:=sViewColname.Values[dbgrid1.Columns.Items[i].FieldName];
        //在子窗口中使用要先打開query1。并且取回數據到dbgrid和設置中文col caption
        Lview1.ViewStyle:=vsReport;
        newCol:=LView1.Columns.add;
        newCol.caption:='字段';
        newCol.width:=120;
        newCol:=LView1.Columns.add;
        newCol.caption:='操作符';
        newCol.width:=80;
        newCol:=LView1.Columns.add;
        newCol.caption:='條件';
        newCol.width:=200;
        newCol:=LView1.Columns.add;
        newCol.caption:='關系';
        newcol.width:=lView1.Width-lview1.Columns[1].width-lview1.columns[2].width
        -lview1.columns[0].width;
        //初始化查詢字段下拉表
        for i:=0 to dbgrid1.Columns.Count-1 do
                combox1.items.Add(dbgrid1.Columns[i].Title.Caption);
end;

procedure Tfm_viewwizard.ComBox1Change(Sender: TObject);
var
mI:integer;
J:integer;
begin
//根據查詢的字段類型初始化查詢操作符
    combox2.Items.Clear;
    mI:=comBox1.ItemIndex;
    case dbgrid1.Columns[mi].Field.DataType of

        ftDateTime,ftsmallint,ftInteger,ftFloat:
                for j:=0 to 5 do
                begin
                    combox2.items.add(DTOp[J]);
                end;
    else
        for j:=0 to 7 do
            begin
                combox2.items.add(strOp[J]);
            end;
    end;
end;

procedure Tfm_viewwizard.BitBtn1Click(Sender: TObject);
var
ListItem:TListItem;
begin
//加入查詢條件
        if combox1.text='' then
        begin
                showmessage('查詢字段不能為空!');
                exit;
        end;
        if combox2.text='' then
        begin
                showmessage('操作符不能為空!');
                exit;
        end;
        if trim(combox3.text)='' then
        begin
                showmessage('查詢條件不能為空!');
                exit;
        end;
        ListItem:=LView1.items.add;
        ListItem.Caption:=dbgrid1.columns[combox1.Itemindex].title.caption;
        listitem.data:=@(dbgrid1.columns[combox1.Itemindex].Fieldname);
        //showmessage(string(listitem.data^));
        LIstItem.SubItems.add(combox2.text);
        if pos('LIKE',combox2.text)>0 then
        begin
                LIstItem.SubItems.add('''%' + combox3.text + '%''');
        end
        else
        begin      //根據類型設置查詢條件。如果是字符串或日期類型，需加單引號
              case dbgrid1.columns[combox1.Itemindex].field.datatype of
                  ftsmallint,ftInteger,ftFloat:
                  begin
                        LIstItem.SubItems.add(combox3.text);
                  end;
              else
                  begin
                        LIstItem.SubItems.add(''''+combox3.text+'''');
                  end;
              end;

        end;
        LIstItem.SubItems.add(combox4.text);



end;

procedure Tfm_viewwizard.BitBtn2Click(Sender: TObject);
var
i:integer;
begin
    i:=lview1.Selected.Index;
    lview1.Items.Delete(i);

end;

procedure Tfm_viewwizard.BitBtn3Click(Sender: TObject);
begin
       if application.MessageBox('是否要全部刪除查詢條件?','注意',MB_YESNO)=IDYES then
                lview1.Items.Clear;
end;

procedure Tfm_viewwizard.BitBtn4Click(Sender: TObject);
var
mysql:string;
i:integer;
begin
    if lview1.Items.count=0 then
    begin
        showmessage('先加入你的查詢條件');
        exit;
    end;


    for i:=0 to lview1.Items.Count-2 do
    begin
        mysql:=mysql+ string((lview1.Items.Item[i].data)^);
        // mysql:=mysql+lview1.Items.item[i].subitems[0].text;
        mysql:=mysql+' '+lview1.Items.item[i].SubItems.Strings[0];
        mysql:=mysql+' '+lview1.Items.item[i].SubItems.strings[1];
        if trim(lview1.Items.item[i].SubItems.strings[2])='' then
        begin
                  mysql:=mysql+' ' + 'and' +' ';
        end
        else
             mysql:=mysql+' ' + lview1.Items.item[i].SubItems.strings[2]+' ';

    end;
    i:=lview1.Items.count-1;
    mysql:=mysql+ string((lview1.Items.Item[i].data)^);
    mysql:=mysql+' '+lview1.Items.item[i].SubItems.strings[0];
    mysql:=mysql+' '+lview1.Items.item[i].SubItems.strings[1];
    showmessage(mysql);
    query1.Close;
    query1.sql.clear;
    query1.sql.add('select * from ' + sViewName + ' where ' + mysql);
    query1.Open;

end;

procedure Tfm_viewwizard.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
        if sViewColName<>nil then
                sViewColname.Free;
                
        action:=caFree;
end;

procedure Tfm_viewwizard.BitBtn5Click(Sender: TObject);
begin
    query1.Close;
    query1.sql.clear;
    query1.sql.add('select * from ' + sViewName) ;
    query1.Open;
end;

end.