unit21.~pas

专卖店进销存系统 delphi7开发 access数据库

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.规格, d.型号, d.品牌, d.计量单位, d.成本价格,d.进货总金额,');
    sql.Add(' d.销售价格, d.库存上限, d.库存下限,d.入库总数,SUM(IIF(ISNULL(出库明细.出库数量),0,出库明细.出库数量)) as 出库总数,SUM(IIF(ISNULL(出库明细.合计),0,出库明细.合计)) as 售货总金额,');
    sql.Add('(d.入库总数-SUM(IIF(ISNULL(出库明细.出库数量),0,出库明细.出库数量))) as 库存数 from');
    sql.Add('(SELECT 库存.ID, 库存.商品类别, 库存.商品编号, 库存.商品名称, 库存.规格, 库存.型号, 库存.品牌,');
    sql.Add('库存.计量单位, 库存.成本价格, 库存.销售价格, 库存.库存上限, 库存.库存下限,');
    sql.Add('SUM(IIF(ISNULL(入库明细.数量),0,入库明细.数量)) as 入库总数,SUM(IIF(ISNULL(入库明细.合计金额),0,入库明细.合计金额)) as 进货总金额');
    sql.Add(' FROM 库存 left join 入库明细 on 库存.商品编号=入库明细.商品编号 group by 库存.ID, 库存.商品类别,');
    sql.Add(' 库存.商品编号, 库存.商品名称, 库存.规格, 库存.型号, 库存.品牌, 库存.计量单位, 库存.成本价格,');
    sql.Add(' 库存.销售价格, 库存.库存上限, 库存.库存下限) d left join 出库明细 on d.商品编号=出库明细.商品编号');
    sql.Add(sp_name);
    sql.Add(' group by d.ID, d.商品类别, d.商品编号, d.商品名称, d.规格, d.型号, d.品牌, d.计量单位, d.成本价格,');
    sql.Add(' d.销售价格, d.库存上限, d.库存下限,d.入库总数,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.规格, d.型号, d.品牌, d.计量单位, d.成本价格,d.进货总金额,');
    sql.Add(' d.销售价格, d.库存上限, d.库存下限,d.入库总数,SUM(IIF(ISNULL(出库明细.出库数量),0,出库明细.出库数量)) as 出库总数,SUM(IIF(ISNULL(出库明细.合计),0,出库明细.合计)) as 售货总金额,');
    sql.Add('(d.入库总数-SUM(IIF(ISNULL(出库明细.出库数量),0,出库明细.出库数量))) as 库存数 from');
    sql.Add('(SELECT 库存.ID, 库存.商品类别, 库存.商品编号, 库存.商品名称, 库存.规格, 库存.型号, 库存.品牌,');
    sql.Add('库存.计量单位, 库存.成本价格, 库存.销售价格, 库存.库存上限, 库存.库存下限,');
    sql.Add('SUM(IIF(ISNULL(入库明细.数量),0,入库明细.数量)) as 入库总数,SUM(IIF(ISNULL(入库明细.合计金额),0,入库明细.合计金额)) as 进货总金额');
    sql.Add(' FROM 库存 left join 入库明细 on 库存.商品编号=入库明细.商品编号 group by 库存.ID, 库存.商品类别,');
    sql.Add(' 库存.商品编号, 库存.商品名称, 库存.规格, 库存.型号, 库存.品牌, 库存.计量单位, 库存.成本价格,');
    sql.Add(' 库存.销售价格, 库存.库存上限, 库存.库存下限) d left join 出库明细 on d.商品编号=出库明细.商品编号');
    sql.Add(sp_name);
    sql.Add(' group by d.ID, d.商品类别, d.商品编号, d.商品名称, d.规格, d.型号, d.品牌, d.计量单位, d.成本价格,');
    sql.Add(' d.销售价格, d.库存上限, d.库存下限,d.入库总数,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.