unit Unit_query_kc;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Unit_jc_child, StdCtrls, Mask, ToolEdit, DB, ADODB, Grids,
  DBGrids, ExtCtrls, Buttons;

type
  Tform_query_kc = class(TForm_jc_child)
    Panel1: TPanel;
    Panel2: TPanel;
    DBGrid1: TDBGrid;
    ADOQuery1: TADOQuery;
    DateEdit1: TDateEdit;
    Label2: TLabel;
    Label1: TLabel;
    Edit_hp: TEdit;
    Button1: TButton;
    Button2: TButton;
    DataSource1: TDataSource;
    Label3: TLabel;
    Edit_ys: TEdit;
    SpeedButton1: TSpeedButton;
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  form_query_kc: Tform_query_kc;

implementation
   uses unit_main,unit_get_sp;
{$R *.dfm}

procedure Tform_query_kc.Button2Click(Sender: TObject);
begin
  inherited;
   self.Close;
end;

procedure Tform_query_kc.Button1Click(Sender: TObject);
var str,date1:string;
begin
  inherited;
  date1 := formatdatetime('YYYY-M-DD',DateEdit1.Date);
    if (edit_hp.Text='') and (edit_ys.Text='') then //不按貨品和顏色查詢
       str:='SELECT t0700,t0701,t0702,t0703,t0704,t0705,t0706,t0707,'+
             'iif(isnull(scrk),0,scrk) as scrk1,iif(isnull(xssl),0,xssl) as xssl1,'+
             'iif(isnull(thsl),0,thsl) as thsl1,'+
             '((iif(isnull(scrk),0,scrk))-(iif(isnull(xssl),0,xssl))-(iif(isnull(thsl),0,thsl)))  AS kc  FROM '+
             '(select *, '+
          '(select sum(iif(isnull(t0406),0,t0406))  from t04 where t0402=t0700 and t0401 in '+
          '(select t0300 from t03 where t0302>=#'+date1+'#) ) AS xssl, '+
          '(select sum(iif(isnull(t0603),0,t0603))  from t06 where t0602=t0700 and t0601 in '+
          '(select t0500 from t05 where t0502>=#'+date1+'#)) AS thsl, '+
          '(select sum(iif(isnull(t1102),0,t1102)) from t11 where t1101=t0700 and t1104 in '+
          '(select t1000 from t10 where t1002>=#'+date1+'#)) AS scrk '+
          'from  t07 )'
    else
       str:='SELECT t0700,t0701,t0702,t0703,t0704,t0705,t0706,t0707,'+
             'iif(isnull(scrk),0,scrk) as scrk1,iif(isnull(xssl),0,xssl) as xssl1,'+
             'iif(isnull(thsl),0,thsl) as thsl1,'+
             '((iif(isnull(scrk),0,scrk))-(iif(isnull(xssl),0,xssl))-(iif(isnull(thsl),0,thsl)))  AS kc  FROM '+
             '(select *, '+
          '(select sum(iif(isnull(t0406),0,t0406)) from t04 where t0402=t0700 and t0401 in '+
          '(select t0300 from t03 where t0302>=#'+date1+'#) ) AS xssl, '+
          '(select sum(iif(isnull(t0603),0,t0603)) from t06 where t0602=t0700 and t0601 in '+
          '(select t0500 from t05 where t0502>=#'+date1+'#)) AS thsl, '+
          '(select sum(iif(isnull(t1102),0,t1102)) from t11 where t1101=t0700 and t1104 in '+
          '(select t1000 from t10 where t1002>=#'+date1+'#)) AS scrk '+
          'from  t07 ' ;
  if (edit_hp.Text<>'') and (edit_ys.Text<>'') then //按貨品和顏色查詢
          str:=str+ ' where t0702 like '''+trim(edit_hp.Text)+'%'' and t0704 like '''+trim(edit_ys.Text)+'%'')'
  else if edit_hp.Text<>'' then  //貨品
          str:=str+' where t0702 like '''+trim(edit_hp.Text)+'%'')'
       else if edit_ys.Text<>'' then   //顏色
          str:=str+' where t0704 like '''+trim(edit_ys.Text)+'%'')';
   adoquery1.Close;
   adoquery1.SQL.Text:=str;
   adoquery1.Open;

end;

procedure Tform_query_kc.SpeedButton1Click(Sender: TObject);
begin
  inherited;
  form_get_sp.ShowModal;
   if form_get_sp.sp_id=0 then exit;
   Edit_hp.Text:=form_get_sp.sp_mc;
end;

end.