unit instorequery;

interface

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

type
  Tf_instorequery = class(Tf_frame)
    Label1: TLabel;
    Panel2: TPanel;
    Label3: TLabel;
    storagefield: TComboBox;
    storagevalue: TComboBox;
    Query: TBitBtn;
    storagecon: TCheckBox;
    Date: TCheckBox;
    FromDate: TDateTimePicker;
    Label2: TLabel;
    ToDate: TDateTimePicker;
    Panel1: TPanel;
    Grid: TDBGrid;
    DBGrid1: TDBGrid;
    Source1: TDataSource;
    Source2: TDataSource;
    Cancel: TBitBtn;
    Quit: TBitBtn;
    procedure storagefieldChange(Sender: TObject); virtual;
    procedure QueryClick(Sender: TObject); virtual;
    procedure CancelClick(Sender: TObject); virtual;
    procedure FormShow(Sender: TObject); virtual;
    procedure QuitClick(Sender: TObject); virtual;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  f_instorequery: Tf_instorequery;

implementation
  uses data, ADODB;
{$R *.dfm}
//在查詢條件中選擇不同的字段,在組合框中會(huì)顯示相應(yīng)的數(shù)據(jù)
procedure Tf_instorequery.storagefieldChange(Sender: TObject);
begin
  inherited;
  StorageValue.Clear;
  Case StorageField.ItemIndex of
    0:
    begin
      With t_data.Query1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select storagename from tb_storageinfo');
        Open;
      end;
      if t_data.Query1.RecordCount>0 then
      begin
        while not t_data.Query1.Eof do
        begin
          StorageValue.Items.Add(Trim(t_data.Query1.Fields[0].Value));
          t_data.Query1.Next;
        end;
      end;
    end;
    1:
    begin
      With t_data.Query1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select storename from tb_storeinfo');
        Open;
      end;
      if t_data.Query1.RecordCount>0 then
      begin
        while not t_data.Query1.Eof do
        begin
          StorageValue.Items.Add(Trim(t_data.Query1.Fields[0].Value));
          t_data.Query1.Next;
        end;
      end;
    end;
    2:
    begin
      With t_data.Query1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select providername from tb_providerinfo');
        Open;
      end;
      if t_data.Query1.RecordCount>0 then
      begin
        while not t_data.Query1.Eof do
        begin
          StorageValue.Items.Add(Trim(t_data.Query1.Fields[0].Value));
          t_data.Query1.Next;
        end;
      end;
    end;
  end;
end;
//處理查詢按鈕的單擊事件
procedure Tf_instorequery.QueryClick(Sender: TObject);
begin
  inherited;
  if (Storagecon.Checked = True) or( Date.Checked = True) then //判斷是否設(shè)置了查詢條件
  begin
    if (Storagecon.Checked = True )and (Date.Checked = False)then  //按字段查詢
    begin
      if(Trim(StorageField.Text)<>'')and(Trim(StorageValue.Text)<>'') then
      begin
        With t_data.Query2 do
        begin
          Close;
          SQL.Clear;
          SQL.Add('select * from v_instorequery where ');
          case Storagefield.ItemIndex of
            0: SQL.Add('storagename = :Value');
            1: SQL.Add('storename = :Value');
            2: SQL.Add(' providername = :Value');
            3: SQL.Add('Regid = :Value');
          end;
          Parameters.ParamByName('Value').Value := Trim(Storagevalue.Text);
          Open;
        end;
        if t_data.Query2.RecordCount>0 then
        begin
          With t_data.Query3 do
          begin
            Close;
            SQL.Clear;
            SQL.Add('Select sum(storesum) as storesum ,storename from  v_instorequery where ');
            case Storagefield.ItemIndex of
              0: SQL.Add('storagename = :value');
              1: SQL.Add('storename = :value');
              2: SQL.Add(' providername = :value');
              3: SQL.Add('Regid = :Value');
            end;
            SQL.Add('  Group by storename');
            Parameters.ParamByName('Value').Value := Trim(Storagevalue.Text);
            Open;
          end;
          Source1.DataSet := t_data.Query2;
          Source2.DataSet := t_data.Query3;
        end
        else
        begin
          Application.MessageBox('沒有找到符合條件的記錄.','提示',64);
          Source1.DataSet := Nil;
          Source2.DataSet := Nil;
        end;
      end;
    end
    else if (Storagecon.Checked = False )and (Date.Checked = True)then //按時(shí)間段查詢
    begin
      With t_data.Query2 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select * from v_instorequery where');
        SQL.Add(' TimeDate >= :FromDate and Timedate < :ToDate');
        Parameters.ParamByName('FromDate').Value := Trunc(FromDate.DateTime);
        Parameters.ParamByName('ToDate').Value := Trunc(ToDate.DateTime)+1;
        Open;
      end;
      if t_data.Query2.RecordCount>0 then
      begin
        With t_data.Query3 do
        begin
          Close;
          SQL.Clear;
          SQL.Add('Select sum(storesum) as storesum ,storename from v_instorequery where ');
          SQL.Add(' TimeDate >= :FromDate and Timedate < :ToDate');
          SQL.Add(' Group by storename');
          Parameters.ParamByName('FromDate').Value := Trunc(FromDate.DateTime);
          Parameters.ParamByName('ToDate').Value := Trunc(ToDate.DateTime)+1;
          Open;
        end;
        Source1.DataSet := t_data.Query2;
        Source2.DataSet := t_data.Query3;
      end
      else
      begin
        Application.MessageBox('沒有找到符合條件的記錄.','提示',64);
        Source1.DataSet := Nil;
        Source2.DataSet := Nil;
      end;
    end
    else  //按查詢條件和時(shí)間段查詢
    begin
      if(Trim(StorageField.Text)<>'')and(Trim(StorageValue.Text)<>'') then
      begin
        With t_data.Query2 do
        begin
          Close;
          SQL.Clear;
          SQL.Add('select * from v_instorequery where ');
          case Storagefield.ItemIndex of
            0: SQL.Add(' storagename = :Value ');
            1: SQL.Add(' storename = :Value ');
            2: SQL.Add(' providername = :Value ');
            3: SQL.Add('Regid = :Value ');
          end;
          SQL.Add(' and TimeDate >= :FromDate and Timedate < :ToDate');
          Parameters.ParamByName('FromDate').Value := Trunc(Fromdate.DateTime);
          Parameters.ParamByName('ToDate').Value := Trunc(ToDate.DateTime)+1;
          Parameters.ParamByName('Value').Size := 100;
          Parameters.ParamByName('Value').Value := Trim(Storagevalue.Text);
          Open;
        end;
        if t_data.Query2.RecordCount>0 then
        begin
          With t_data.Query3 do
          begin
            Close;
            SQL.Clear;
            SQL.Add('Select sum(storesum) as storesum ,storename from  v_instorequery where ');
            case Storagefield.ItemIndex of
              0: SQL.Add('storagename = :value ');
              1: SQL.Add('storename = :value ');
              2: SQL.Add('providername = :value ');
              3: SQL.Add('Regid = :Value ');
            end;
            SQL.Add(' and TimeDate >= :FromDate and Timedate < :ToDate ');
            SQL.Add(' Group by storename');
            Parameters.ParamByName('Value').Size := 100;
            Parameters.ParamByName('Value').Value := Trim(Storagevalue.Text);
            Parameters.ParamByName('FromDate').Value := Trunc(FromDate.DateTime);
            Parameters.ParamByName('ToDate').Value := Trunc(ToDate.DateTime)+1;
            Open;
          end;
          Source1.DataSet := t_data.Query2;
          Source2.DataSet := t_data.Query3;
        end
        else
        begin
          Application.MessageBox('沒有找到符合條件的記錄.','提示',64);
          Source1.DataSet := Nil;
          Source2.DataSet := Nil;
        end;
      end;
    end;
  end
  else
    Application.MessageBox('請(qǐng)?jiān)O(shè)置查詢條件.','提示',64);
end;

procedure Tf_instorequery.CancelClick(Sender: TObject);
begin
  inherited;
   if Not Storagecon.Checked then
    Storagecon.Checked := True;
  if Date.Checked then
    Date.Checked := False;
  StorageField.ItemIndex := -1;
  StorageValue.Clear;
  FromDate.DateTime := Now;
  ToDate.DateTime := Now;
end;

procedure Tf_instorequery.FormShow(Sender: TObject);
begin
  inherited;
  Cancel.Click;
end;

procedure Tf_instorequery.QuitClick(Sender: TObject);
begin
  inherited;
  Close;
end;

end.