unit Unit21;

interface

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

type
  TF_kc_z = class(TForm)
    Panel2: TPanel;
    Label13: TLabel;
    Panel4: TPanel;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    Edit1: TEdit;
    Panel3: TPanel;
    DBGrid1: TDBGrid;
    DBEdit1: TDBEdit;
    Panel1: TPanel;
    Panel5: TPanel;
    DBGrid2: TDBGrid;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Panel6: TPanel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    EditDBEdit2: TDBEdit;
    EditDBEdit3: TDBEdit;
    EditDBEdit4: TDBEdit;
    EditDBEdit5: TDBEdit;
    EditDBEdit6: TDBEdit;
    EditDBEdit7: TDBEdit;
    EditDBEdit9: TDBEdit;
    EditDBEdit10: TDBEdit;
    EditDBEdit11: TDBEdit;
    EditDBEdit12: TDBEdit;
    GroupBox1: TGroupBox;
    Label16: TLabel;
    Label17: TLabel;
    DBText1: TDBText;
    DBText2: TDBText;
    GroupBox2: TGroupBox;
    Label18: TLabel;
    Label19: TLabel;
    DBText3: TDBText;
    TabSheet2: TTabSheet;
    DBGrid3: TDBGrid;
    TabSheet3: TTabSheet;
    DBGrid4: TDBGrid;
    DBEdit2: TDBEdit;
    DBText4: TDBText;
    procedure FormShow(Sender: TObject);
    procedure DBEdit2Change(Sender: TObject);
    procedure Edit1Change(Sender: TObject);
    procedure DBEdit1Change(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  F_kc_z: TF_kc_z;

implementation

uses Unit1, Unit16;

{$R *.dfm}

procedure TF_kc_z.FormShow(Sender: TObject);
begin
mainform.A_sqlba.Close;
mainform.A_sqlba.Open;
edit1.Text:='';
end;

procedure TF_kc_z.DBEdit2Change(Sender: TObject);
begin
 with mainform.Q_kc_rkmx do
   begin
     close;
     sql.Clear;
     sql.Add('select 入庫明細.*,入庫單.* from 入庫明細 left join 入庫單 on 入庫明細.入庫單號=入庫單.入庫單號');
     sql.Add('where 入庫明細.商品編號='+''''+DBEdit2.Text+'''');
     open;
   end;
 with mainform.Q_kc_ckmx do
   begin
     close;
     sql.Clear;
     sql.Add('select 出庫明細.*,出庫單.* from 出庫明細 left join 出庫單 on 出庫明細.出庫單號=出庫單.出庫單號');
     sql.Add('where 出庫明細.商品編號='+''''+DBEdit2.Text+'''');
     open;
   end;
end;

procedure TF_kc_z.Edit1Change(Sender: TObject);
var
  sp_name:string;
begin
if trim(edit1.Text)='' then sp_name:='where 商品類別='+''''+DBEdit1.Text+''''
  else sp_name:='where 商品類別='+''''+DBEdit1.Text+''''+' and d.商品名稱 like '+''''+'%'+edit1.Text+'%'+'''';
with mainform.Q_kc do
  begin
    close;
    sql.Clear;
    sql.Add('SELECT d.ID, d.商品類別, d.商品編號, d.商品名稱, d.規(guī)格, d.型號, d.品牌, d.計量單位, d.成本價格,d.進貨總金額,');
    sql.Add(' d.銷售價格, d.庫存上限, d.庫存下限,d.入庫總數(shù),SUM(IIF(ISNULL(出庫明細.出庫數(shù)量),0,出庫明細.出庫數(shù)量)) as 出庫總數(shù),SUM(IIF(ISNULL(出庫明細.合計),0,出庫明細.合計)) as 售貨總金額,');
    sql.Add('(d.入庫總數(shù)-SUM(IIF(ISNULL(出庫明細.出庫數(shù)量),0,出庫明細.出庫數(shù)量))) as 庫存數(shù) from');
    sql.Add('(SELECT 庫存.ID, 庫存.商品類別, 庫存.商品編號, 庫存.商品名稱, 庫存.規(guī)格, 庫存.型號, 庫存.品牌,');
    sql.Add('庫存.計量單位, 庫存.成本價格, 庫存.銷售價格, 庫存.庫存上限, 庫存.庫存下限,');
    sql.Add('SUM(IIF(ISNULL(入庫明細.數(shù)量),0,入庫明細.數(shù)量)) as 入庫總數(shù),SUM(IIF(ISNULL(入庫明細.合計金額),0,入庫明細.合計金額)) as 進貨總金額');
    sql.Add(' FROM 庫存 left join 入庫明細 on 庫存.商品編號=入庫明細.商品編號 group by 庫存.ID, 庫存.商品類別,');
    sql.Add(' 庫存.商品編號, 庫存.商品名稱, 庫存.規(guī)格, 庫存.型號, 庫存.品牌, 庫存.計量單位, 庫存.成本價格,');
    sql.Add(' 庫存.銷售價格, 庫存.庫存上限, 庫存.庫存下限) d left join 出庫明細 on d.商品編號=出庫明細.商品編號');
    sql.Add(sp_name);
    sql.Add(' group by d.ID, d.商品類別, d.商品編號, d.商品名稱, d.規(guī)格, d.型號, d.品牌, d.計量單位, d.成本價格,');
    sql.Add(' d.銷售價格, d.庫存上限, d.庫存下限,d.入庫總數(shù),d.進貨總金額');
    open;
  end;
end;

procedure TF_kc_z.DBEdit1Change(Sender: TObject);
var
  sp_name:string;
begin
if trim(edit1.Text)='' then sp_name:='where 商品類別='+''''+DBEdit1.Text+''''
  else sp_name:='where 商品類別='+''''+DBEdit1.Text+''''+' and d.商品名稱 like '+''''+'%'+edit1.Text+'%'+'''';
with mainform.Q_kc do
  begin
    close;
    sql.Clear;
    sql.Add('SELECT d.ID, d.商品類別, d.商品編號, d.商品名稱, d.規(guī)格, d.型號, d.品牌, d.計量單位, d.成本價格,d.進貨總金額,');
    sql.Add(' d.銷售價格, d.庫存上限, d.庫存下限,d.入庫總數(shù),SUM(IIF(ISNULL(出庫明細.出庫數(shù)量),0,出庫明細.出庫數(shù)量)) as 出庫總數(shù),SUM(IIF(ISNULL(出庫明細.合計),0,出庫明細.合計)) as 售貨總金額,');
    sql.Add('(d.入庫總數(shù)-SUM(IIF(ISNULL(出庫明細.出庫數(shù)量),0,出庫明細.出庫數(shù)量))) as 庫存數(shù) from');
    sql.Add('(SELECT 庫存.ID, 庫存.商品類別, 庫存.商品編號, 庫存.商品名稱, 庫存.規(guī)格, 庫存.型號, 庫存.品牌,');
    sql.Add('庫存.計量單位, 庫存.成本價格, 庫存.銷售價格, 庫存.庫存上限, 庫存.庫存下限,');
    sql.Add('SUM(IIF(ISNULL(入庫明細.數(shù)量),0,入庫明細.數(shù)量)) as 入庫總數(shù),SUM(IIF(ISNULL(入庫明細.合計金額),0,入庫明細.合計金額)) as 進貨總金額');
    sql.Add(' FROM 庫存 left join 入庫明細 on 庫存.商品編號=入庫明細.商品編號 group by 庫存.ID, 庫存.商品類別,');
    sql.Add(' 庫存.商品編號, 庫存.商品名稱, 庫存.規(guī)格, 庫存.型號, 庫存.品牌, 庫存.計量單位, 庫存.成本價格,');
    sql.Add(' 庫存.銷售價格, 庫存.庫存上限, 庫存.庫存下限) d left join 出庫明細 on d.商品編號=出庫明細.商品編號');
    sql.Add(sp_name);
    sql.Add(' group by d.ID, d.商品類別, d.商品編號, d.商品名稱, d.規(guī)格, d.型號, d.品牌, d.計量單位, d.成本價格,');
    sql.Add(' d.銷售價格, d.庫存上限, d.庫存下限,d.入庫總數(shù),d.進貨總金額');
    open;
  end;

end;

procedure TF_kc_z.BitBtn3Click(Sender: TObject);
begin
 mainform.Q_kc.edit;
  F_kc_add.showmodal;
end;

procedure TF_kc_z.BitBtn2Click(Sender: TObject);
begin
mainform.Q_kc.append;
  F_kc_add.showmodal;
end;

end.