unit QueryFrm;

interface

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

type
  TfrmQuery = class(TForm)
    pgcMain: TPageControl;
    tbsWn: TTabSheet;
    tbsKc: TTabSheet;
    grdWn: TDBGrid;
    pnlTop_Wn: TPanel;
    lvConditionWn: TListView;
    pnlTop_Left_Wn: TPanel;
    lblTablName: TLabel;
    lblFieldName: TLabel;
    lblOprator: TLabel;
    lblFieldValue: TLabel;
    lblLink: TLabel;

    cboTabName: TComboBox;
    cboFieldName: TComboBox;
    cboOprator: TComboBox;
    cboFieldValue: TComboBox;
    cboLink: TComboBox;

    pnlMiddleWn: TPanel;
    btnWnPrint: TButton;
    btnWnQuery: TButton;
    pnlMiddle_Kc: TPanel;
    btnKcPrint: TButton;
    btnKcQuery: TButton;
    pnlTop_Kc: TPanel;

    btnWnAdd: TSpeedButton;
    btnWnClear: TSpeedButton;

    dsWn: TDataSource;
    dsKc: TDataSource;

    qryWn: TADOQuery;
    qryKc: TADOQuery;
    dtpFieldValue: TDateTimePicker;
    lblCk_Kc: TLabel;
    lblHwlb: TLabel;
    lblCd: TLabel;
    lblGg: TLabel;

    cboCk_Kc: TComboBox;
    cboHwlb: TComboBox;
    cboCd: TComboBox;
    cboGg: TComboBox;

    grdKc: TDBGrid;
    procedure FormShow(Sender: TObject);
    procedure cboTabNameChange(Sender: TObject);
    procedure btnWnAddClick(Sender: TObject);
    procedure btnWnClearClick(Sender: TObject);
    procedure cboFieldNameChange(Sender: TObject);
    procedure dtpFieldValueChange(Sender: TObject);
    procedure btnKcQueryClick(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure btnKcPrintClick(Sender: TObject);
    procedure cboCk_KcChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnWnQueryClick(Sender: TObject);
    procedure cboLinkChange(Sender: TObject);
    procedure cboOpratorChange(Sender: TObject);
  private
    { Private declarations }
    FCondition: array[0..3] of string;
    FSQLWn: TStringList;    
  public
    { Public declarations }
  end;

var
  frmQuery: TfrmQuery;

implementation

uses
  ConstUnt,
  PrintFrm;


const
  FieldLst: array[0..3] of string = ('ck', 'hwlb', 'hwcd', 'hwgg');

{$R *.DFM}

procedure TfrmQuery.FormShow(Sender: TObject);
var
  I: Byte;
  PcboTemp: ^TComboBox;
begin
  with LogonInf, pgcMain do
  begin
    cboTabName.Items := TabCnNameLst;
    cboOprator.Items := CnOpratorLst;
    cboLink.Items := CnLinkLst;

    cboHwlb.Items := LogonInf.HwlbLst;
    cboCd.Items := LogonInf.PpcdLst;
    cboGg.Items := LogonInf.HwggLst;

    Pages[0].TabVisible := FunctionLst.Strings[13] = 'True';
    Pages[1].TabVisible := FunctionLst.Strings[14] = 'True';

  end;

  qryWn.Connection := LogonInf.dbCkgl;
  qryKc.Connection := LogonInf.dbCkgl;

  PcboTemp := @cboCk_Kc;
  for I := 0 to 3 do
  begin
    PcboTemp^.Tag := I;
    Inc(PcboTemp);
  end;

  FSQLWn := TStringList.Create;

  lvConditionWn.OnDblClick := btnWnClearClick;
  

end;

procedure TfrmQuery.cboTabNameChange(Sender: TObject);
begin
  LogonInf.TabNameIndex := cboTabName.ItemIndex;
  cboFieldName.Items := LogonInf.FieldNameLst;
//  ShowMessage(LogonInf.FieldTypeLst.Text);
end;

procedure TfrmQuery.btnWnAddClick(Sender: TObject);
const
  GSQL = 'select * from %s where  %s %s ''%s''';
var
  PcboTemp: ^TComboBox;
  I: Byte;
begin

  if cboFieldName.Text = '' then
    Exit;
  Pcbotemp := @cboTabName;
  with lvConditionWn.Items.Add do
  begin
    Caption := PcboTemp^.Text;
    for I := 0 to lvConditionWn.Columns.Count - 2 do
    begin
      Inc(PcboTemp);
      SubItems.Add(PcboTemp^.Text);
    end;
  end;


  with LogonInf do
    if FieldTypeLst.Strings[FieldNameIndex] = 'int' then
    begin
      if Trim(cboFieldValue.Text) = '' then
        FSQLWn.Add(GetEnFieldName(cboFieldName.Text)
          + En_Oprator[OpratorIndex]  + ' 0 '
          + ' ' + En_Link[LinkIndex])
      else
        FSQLWn.Add(GetEnFieldName(cboFieldName.Text)
          + En_Oprator[OpratorIndex]  + cboFieldValue.Text
          + ' ' + En_Link[LinkIndex]);
    end
    else
      FSQLWn.Add(GetEnFieldName(cboFieldName.Text)
        + En_Oprator[OpratorIndex]  + #39 + cboFieldValue.Text
        + #39
        + ' ' + En_Link[LinkIndex]);

end;

procedure TfrmQuery.btnWnClearClick(Sender: TObject);
var
  nIndex: Integer;
begin
  if lvConditionWn.SelCount < 1 then
    Exit;
  nIndex := lvConditionWn.Selected.Index;
  lvConditionWn.Selected.Delete;
  FSQLWn.Delete(nIndex);
end;

procedure TfrmQuery.cboFieldNameChange(Sender: TObject);
begin
  if cboFieldValue.Text = '' then
    Exit;
  with LogonInf.FieldTypeLst do
    dtpFieldValue.Visible := Strings[cboFieldName.ItemIndex] = 'date';
  cboFieldValue.Visible := not dtpFieldValue.Visible;

 
  
end;

procedure TfrmQuery.dtpFieldValueChange(Sender: TObject);
begin
  cboFieldValue.Text := DateToStr(dtpFieldValue.Date);
end;

procedure TfrmQuery.btnKcQueryClick(Sender: TObject);
var
  I: Integer;
  strCondition: string;
begin

  for I := 0 to 3 do
    if FCondition[I] <> '' then
      strCondition := strCondition + FCondition[I] + ' and ';

      
{$IFDEF __DEBUG}
    MessageBox(Handle, PChar(strCondition), MsgI, Msg1IBTN);
{$ENDIF}

  if strCondition <> '' then
    strCondition := ' where '
      + Copy(strCondition, 1, Length(strCondition) - 4);


  with qryKc do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select ck as 存放倉庫, hwlb as 貨物類別, hwgg as '
      + '貨物規格, hwsl as 貨物數量, hwdw as 貨物單位, hwcd as '
      + '貨物產地 from kc' + strCondition);
{$IFDEF __DEBUG}
    MessageBox(Handle, PChar(SQL.Text), MsgI, Msg1IBTN);
{$ENDIF}
    Open;
    if RecordCount = 0 then
      MessageBox(Handle, PChar(NoFind), MsgI, Msg1IBTN);

    for I := 0 to FieldCount - 1 do
      grdKc.Columns[I].Width := 100;

  end;
end;

procedure TfrmQuery.Button3Click(Sender: TObject);
begin
  with TfrmPrint.Create(Self) do
    try
      qreMain.PreviewModal;
    finally
      Free;
    end;    
end;

procedure TfrmQuery.btnKcPrintClick(Sender: TObject);
var
  PlblTemp: ^TQRLabel;
  PdblblTemp: ^TQRDBText;
  I: Word;
begin
  with TfrmPrint.Create(Self) do
    try
      PlblTemp := @QRLabel2;
      PdblblTemp := @QRDBText1;
      for I := 0 to qryKc.FieldCount - 1 do
      begin
        PlblTemp^.Transparent := True;
        PdblblTemp^.Transparent := True;
        PlblTemp^.Caption := qryKc.Fields[I].FieldName;
        PdblblTemp^.DataSet := qryKc;
        pdblblTemp^.DataField := PlblTemp^.Caption;
        Inc(PlblTemp);
        Inc(PdblblTemp);

      end;
      qreMain.DataSet := qryKc;
      qreMain.PreviewModal;
    finally
      Free;
    end;
end;

procedure TfrmQuery.cboCk_KcChange(Sender: TObject);
begin
  if (Sender as TComboBox).Text = '' then
    FCondition[(Sender as TComboBox).Tag] := ''
  else
    FCondition[(Sender as TComboBox).Tag] := Format('%s = ''%s''',
      [FieldLst[(Sender as TComboBox).Tag],
      (Sender as TComboBox).Text]);
end;

procedure TfrmQuery.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FSQLWn.Free;
end;

procedure TfrmQuery.btnWnQueryClick(Sender: TObject);
var
  I: Integer;
  strAllField: string;
  strCondition: string;

  strLastLink: string;
begin
  if lvConditionWn.Items.Count < 1 then
  begin
    MessageBox(Handle, PChar(ErrCondition), MsgI, Msg1IBTN);
    Exit;
  end;
    
  strLastLink := Copy(FSQLWn.Strings[FSQLWn.Count - 1],
    Length(FSQLWn.Strings[FSQLWn.Count - 1]) - 3, 3);
  FSQLWn.Strings[FSQLWn.Count - 1] := Copy(FSQLWn.Strings[FSQLWn.Count - 1],
    1, Length(FSQLWn.Strings[FSQLWn.Count - 1]) - 3);
  with LogonInf, cboFieldName do
    for I := 0 to Items.Count - 1 do
      strAllField := strAllField + '[' + GetEnFieldName(Items[I])
        + '] as ['  + Items[I] + '], ';

  strAllField := Copy(strAllField, 1, Length(strAllField) - 2);

  for I := 0 to FSQLWn.Count - 1 do
    strCondition := strCondition + FSQLWn.Strings[I] + ' ';

  with qryWn, LogonInf do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select %s from %s where %s');
    SQL.Text := Format(SQL.Text, [strAllField,
      TabEnNameLst.Strings[TabNameIndex], strCondition]);
    try
      Open;
      if RecordCount = 0 then
        MessageBox(Handle, PChar(NoFind), MsgI, Msg1IBTN);
      
    except
      MessageBox(Handle, PChar(ErrCondition), MsgI, Msg1IBTN);
    end;

  end;

  FSQLWn.Strings[FSQLWn.Count - 1] := FSQLWn.Strings[FSQLWn.Count - 1]
   + strLastLink;




end;

procedure TfrmQuery.cboLinkChange(Sender: TObject);
begin
  LogonInf.LinkIndex := (Sender as TComboBox).ItemIndex;
end;

procedure TfrmQuery.cboOpratorChange(Sender: TObject);
begin
  LogonInf.OpratorIndex := (Sender as TComboBox).ItemIndex;
end;

end.