unit MS_SQ;

interface

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

type
  TMS_SQFrm = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    Edit1: TEdit;
    ComboBox3: TComboBox;
    ComboBox4: TComboBox;
    ComboBox5: TComboBox;
    Edit2: TEdit;
    DateTime1: TDateTimePicker;
    DateTime2: TDateTimePicker;
    BB_Query: TBitBtn;
    BB_Close: TBitBtn;
    Label4: TLabel;
    DBEdit1: TDBEdit;
    Label5: TLabel;
    DBEdit2: TDBEdit;
    Label6: TLabel;
    DBEdit3: TDBEdit;
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    procedure ComboBox3Change(Sender: TObject);
    procedure DateTime1CloseUp(Sender: TObject);
    procedure DateTime2CloseUp(Sender: TObject);
    procedure BB_CloseClick(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox4Change(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BB_QueryClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MS_SQFrm: TMS_SQFrm;

implementation
    uses  DataModule;
{$R *.dfm}

procedure TMS_SQFrm.ComboBox3Change(Sender: TObject);
begin
   Edit2.ReadOnly:=false;
   Edit2.Color:=clWindow;
end;

procedure TMS_SQFrm.DateTime1CloseUp(Sender: TObject);
begin
   Edit1.Text:=formatDatetime('yyyy-m-d',DateTime1.Date);
end;

procedure TMS_SQFrm.DateTime2CloseUp(Sender: TObject);
begin
   Edit2.Text:=formatDatetime('yyyy-m-d',DateTime2.Date);
end;

procedure TMS_SQFrm.BB_CloseClick(Sender: TObject);
begin
    MS_SQFrm.Close;
end;

procedure TMS_SQFrm.ComboBox1Change(Sender: TObject);
begin
  if ComboBox1.Text='計劃日期' then
     DateTime1.Visible:=true;
end;

procedure TMS_SQFrm.ComboBox4Change(Sender: TObject);
begin
    if ComboBox4.Text='計劃日期' then
    DateTime2.Visible:=true;
end;

procedure TMS_SQFrm.FormCreate(Sender: TObject);
begin
  DM.AQ_Materiel_Stock.Filtered:=false;
  DM.AQ_Materiel_Stock.Active:=false;

end;

procedure TMS_SQFrm.BB_QueryClick(Sender: TObject);
var
   Field1,Field2:string;
   str:string;
begin
   str:='';
   //條件1
   if ComboBox1.Text='計劃日期' then
     Field1:='S_Materiel_Time'
     else if ComboBox1.Text='憑證號' then
       Field1:='S_Credence_ID'
       else  if ComboBox1.Text='原憑證號' then
         Field1:='S_Credence_OID'
         else  if ComboBox1.Text='材料名稱' then
           Field1:='Materiel_Name'
            else  if ComboBox1.Text='材料編號' then
             Field1:='Materiel_ID'
              else  if ComboBox1.Text='材料規格' then
               Field1:='Materiel_Standard'
                else  if ComboBox1.Text='計量單位' then
                 Field1:='Materiel_Unit'
                   else  if ComboBox1.Text='單價' then
                    Field1:='Materiel_Price'
                     else  if ComboBox1.Text='采購數量' then
                      Field1:='S_Materiel_Num'
                       else  Field1:='S_Materiel_Price';
    if  (ComboBox2.Text<>'') and (Edit1.Text<>'') then
      if  Field1='S_Materiel_Time' then
         str:=str+Field1+ComboBox2.Text+'#'+Edit1.Text+'#'  //日期類型查詢語句
        else if  (Field1='Materiel_ID') or (Field1='S_Materiel_Num') or (Field1='Materiel_Price')
         or (Field1='S_Materiel_Price') then
           str:=str+Field1+ComboBox2.Text+Edit1.Text  //數字類型查詢語句
          else
            str:=str+Field1+ComboBox2.Text+''''+Edit1.Text+'''';  //文本類型查詢語句

     //條件2
     if ComboBox4.Text='計劃日期' then
     Field2:='S_Materiel_Time'
     else if ComboBox4.Text='憑證號' then
       Field2:='S_Credence_ID'
       else  if ComboBox4.Text='原憑證號' then
         Field2:='S_Credence_OID'
         else  if ComboBox4.Text='材料名稱' then
           Field2:='Materiel_Name'
            else  if ComboBox4.Text='材料編號' then
             Field2:='Materiel_ID'
              else  if ComboBox4.Text='材料規格' then
               Field2:='Materiel_Standard'
                else  if ComboBox4.Text='計量單位' then
                 Field2:='Materiel_Unit'
                   else  if ComboBox4.Text='單價' then
                    Field2:='Materiel_Price'
                     else  if ComboBox4.Text='采購數量' then
                      Field2:='S_Materiel_Num'
                       else  Field2:='S_Materiel_Price';
     if  (ComboBox5.Text<>'') and (Edit2.Text<>'') then
      if ComboBox3.Text='并' then
       begin
        if  Field2='S_Materiel_Time' then
         str:=str+' and '+Field2+ComboBox5.Text+'#'+Edit2.Text+'#'  //日期類型查詢語句
          else if  (Field2='Materiel_ID') or (Field2='S_Materiel_Num') or (Field2='Materiel_Price')
         or (Field2='S_Materiel_Price') then
           str:=str+' and '+Field2+ComboBox5.Text+Edit2.Text  //數字類型查詢語句
            else
             str:=str+' and '+Field2+ComboBox5.Text+''''+Edit2.Text+'''';  //文本類型查詢語句
        end
       else
        begin
        if  Field2='S_Materiel_Time' then
         str:=str+' or '+Field2+ComboBox5.Text+'#'+Edit2.Text+'#'  //日期類型查詢語句
          else if  (Field2='Materiel_ID') or (Field2='S_Materiel_Num') or (Field2='Materiel_Price')
         or (Field2='S_Materiel_Price') then
           str:=str+' or '+Field2+ComboBox5.Text+Edit2.Text  //數字類型查詢語句
            else
             str:=str+' or '+Field2+ComboBox5.Text+''''+Edit2.Text+'''';  //文本類型查詢語句
        end;

    with  DM.AQ_Materiel_Stock do
      begin
       close;
       SQL.Clear;
       SQL.Add('Select * from Materiel_Stock where '+str);
       open;
      end;
    if DM.AQ_Materiel_Stock.RecordCount=0 then
      Application.MessageBox('沒有數據與條件相符','查詢結果',MB_OK);
   Edit2.ReadOnly:=true;
   Edit2.Color:=clInfoBk;
   Edit1.Text:='';
   Edit2.Text:='';
end;

end.