unitmatenumchg.pas

仓储系统

unit UnitMateNumChg;

interface

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

type
  TfrmMateNumChg = class(TForm)
    ADOQuery_sql: TADOQuery;
    dsHuiZong: TDataSource;
    Panel1: TPanel;
    Panel2: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    sbtncode: TSpeedButton;
    Label9: TLabel;
    Label4: TLabel;
    lblwlmc: TLabel;
    dtpfrom: TDateTimePicker;
    dtpto: TDateTimePicker;
    edtWlbm: TEdit;
    btnQry: TButton;
    btnPrint: TButton;
    cbbkf: TComboBox;
    btnClose: TButton;
    Panel3: TPanel;
    Panel4: TPanel;
    DBGrid1: TDBGrid;
    lblWllb: TLabel;
    cbbLb: TComboBox;
    Panel16: TPanel;
    RxLabel7: TRxLabel;
    lbl_HuiZong: TRxLabel;
    procedure btnCloseClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure sbtncodeClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure edtWlbmExit(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnQryClick(Sender: TObject);
    procedure dtptoExit(Sender: TObject);
    procedure dtptoEnter(Sender: TObject);
    procedure btnPrintClick(Sender: TObject);
  private
    { Private declarations }
    listLb,listLbmc:array of string;
  public
    { Public declarations }
    XLApp: Variant;
  end;

var
  frmMateNumChg: TfrmMateNumChg;

implementation

uses UnitDm, UnitSelectCodeNameType, ComObj, Excel_TLB;


{$R *.dfm}

procedure TfrmMateNumChg.btnCloseClick(Sender: TObject);
begin
  close;
end;

procedure TfrmMateNumChg.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  action:=cafree;
end;

procedure TfrmMateNumChg.sbtncodeClick(Sender: TObject);
begin
  frmSelectCodeNameType:=TfrmSelectCodeNameType.create(self);
  frmSelectCodeNameType.show;
end;

procedure TfrmMateNumChg.FormShow(Sender: TObject);
var
  str:string;
begin
  //未过帐的数量，这里不算数
  //这里要判断入库、出库过帐标志
  //--外购--------对应物料类别为：  101----------606形式的物料编码
  str:='  select a.mate_code as 物料编码,a.mate_name as 物料名称,a.mate_type as 规格型号,a.mate_class as 物料类别,                                '+
       '         (a.stoc_amount-isNull(b.In_Amount,0)-isNull(c.TL_Amount,0)+isNull(d.Out_Amount,0)+isNull(e.Out_Amount,0)) as 期初结存数量,           '+
       '         (a.mate_totalprice-isNull(b.In_Zj,0)-isNull(c.TL_Zj,0)+isNull(d.Out_Zj,0)+isNull(e.Out_Zj,0)) as 期初结存金额,                       '+
       '         (isNull(b.In_Amount,0))+(isNull(c.TL_Amount,0)) as 入库数量,                                                                     '+
       '         (isNull(b.In_Zj,0))+(isNull(c.TL_Zj,0)) as 入库金额,                                                                             '+
       '         (isNull(d.Out_Amount,0))+(isNull(e.Out_Amount,0)) as 出库数量,                                                                   '+
       '         (isNull(d.Out_Zj,0))+(isNull(e.Out_Zj,0)) as 出库金额,                                                                           '+
       '         a.stoc_amount as 期末结存数量,a.mate_totalprice as 期末结存金额                                                                          '+
       '     from mate_basic a                                                                                                                    '+
       '     left outer join                                                                                                                      '+
       '     (select mate_code,sum(in_amount) as in_amount,sum(in_zj) as in_zj from mate_CgRk group by mate_code) b on a.mate_code=b.mate_code    '+
       '     left outer join                                                                                                                      '+
       '     (select mate_code,sum(TL_amount) as TL_amount,sum(TL_zj) as TL_zj from TuiLiao_Rk group by mate_code) c on a.mate_code=c.mate_code   '+
       '     left outer join                                                                                                                      '+
       '     (select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from Mate_Ly group by mate_code) d on a.mate_code=d.mate_code  '+
       '     left outer join                                                                                                                      '+
       '     (select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from Mate_Bf group by mate_code) e on a.mate_code=e.mate_code  '+
       '     where (a.mate_class between ''101'' and ''606'')                                                                                         '+
       '     Union all                                                                                                                            '+
       //'     --自制--------对应物料类别为：  CCC                                                                                                  '+
       '     select a.mate_code as 物料编码,a.mate_name as 物料名称,a.mate_type as 规格型号,a.mate_class as 物料类别,                             '+
       '         (a.stoc_amount-isNull(b.In_Amount,0)-isNull(c.TL_Amount,0)+isNull(d.Out_Amount,0)+isNull(e.Out_Amount,0)) as 期初结存数量,       '+
       '         (a.mate_totalprice-isNull(b.In_Zj,0)-isNull(c.TL_Zj,0)+isNull(d.Out_Zj,0)+isNull(e.Out_Zj,0)) as 期初结存金额,                   '+
       '         (isNull(b.In_Amount,0))+(isNull(c.TL_Amount,0)) as 入库数量,                                                                     '+
       '         (isNull(b.In_Zj,0))+(isNull(c.TL_Zj,0)) as 入库金额,                                                                             '+
       '         (isNull(d.Out_Amount,0))+(isNull(e.Out_Amount,0)) as 出库数量,                                                                   '+
       '         (isNull(d.Out_Zj,0))+(isNull(e.Out_Zj,0)) as 出库金额,                                                                           '+
       '         a.stoc_amount as 期末结存数量,a.mate_totalprice as 期末结存金额                                                                  '+
       '     from mate_basic a                                                                                                                    '+
       '     left outer join                                                                                                                      '+
       '     (select mate_code,sum(in_amount) as in_amount,sum(in_zj) as in_zj from mate_ZzRk group by mate_code) b on a.mate_code=b.mate_code    '+
       '     left outer join                                                                                                                      '+
       '     (select mate_code,sum(TL_amount) as TL_amount,sum(TL_zj) as TL_zj from TuiLiao_Rk group by mate_code) c on a.mate_code=c.mate_code   '+
       '     left outer join                                                                                                                      '+
       '     (select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from mate_Ly group by mate_code) d on a.mate_code=d.mate_code  '+
       '     left outer join                                                                                                                      '+
       '     (select mate_code,sum(Out_amount) as Out_amount,sum(Out_zj) as Out_zj from mate_Bf group by mate_code) e on a.mate_code=e.mate_code  '+
       '     where (a.mate_class like ''CCC'')                                                                                                      '+
       '     union all                                                                                                                            '+
       //'     --成品--------对应物料类别为：  PPP                                                                                                  '+
       '     select a.mate_code as 物料编码,a.mate_name as 物料名称,a.mate_type as 规格型号,a.mate_class as 物料类别,                             '+
       '                     (a.stoc_amount-isNull(b.In_Amount,0)-isNull(c.TL_Amount,0)+isNull(d.Out_Amount,0)+isNull(e.Out_Amount,0)) as 期初结存数量,  '+
       '                     (a.mate_totalprice-isNull(b.In_Zj,0)-isNull(c.TL_Zj,0)+isNull(d.Out_Zj,0)+isNull(e.Out_Zj,0)) as 期初结存金额,              '+
       '                     (isNull(b.In_Amount,0))+(isNull(c.TL_Amount,0)) as 入库数量,                                                                '+
       '                     (isNull(b.In_Zj,0))+(isNull(c.TL_Zj,0)) as 入库总额,                                                                        '+
       '                     (isNull(d.Out_Amount,0))+(isNull(e.Out_Amount,0)) as 出库数量,                                                              '+
       '                     (isNull(d.Out_Zj,0))+(isNull(e.Out_Zj,0)) as 出库金额,                                                                      '+
       '                     a.stoc_amount as 期末结存数量,a.mate_totalprice as 期末结存金额                                                             '+
       '     from mate_basic a                                                                                                                           '+
       '     left outer join                                                                                                                             '+
       '     (select mate_code,sum(In_Amount) as In_Amount,sum(In_Zj) as In_Zj from Product_Rk group by mate_code) b on a.mate_code=b.mate_code          '+
       '     left outer join                                                                                                                             '+
       '     (select mate_code,sum(TL_Amount) as TL_Amount,sum(TL_Zj) as TL_Zj from TuiLiao_Rk group by mate_code) c on a.mate_code=c.mate_code          '+
       '     left outer join                                                                                                                             '+
       '     (select mate_code,sum(Out_Amount) as Out_Amount,sum(Out_Zj) as Out_Zj from mate_Ck group by mate_code) d on a.mate_code=d.mate_code         '+
       '     left outer join                                                                                                                             '+
       '     (select mate_code,sum(Out_Amount) as Out_Amount,sum(Out_Zj) as Out_Zj from mate_Bf group by mate_code) e on a.mate_code=e.mate_code         '+
       '     where (a.mate_class like ''PPP'')                                                                                                             '+
       '     order by a.mate_code        ';
  with ADOQuery_sql do
  begin
    close;
    sql.Clear;
    sql.add(str);
    open;
  end;
  lbl_HuiZong.Caption:=inttostr(ADOQuery_sql.RecordCount)+' 条';
end;

procedure TfrmMateNumChg.edtWlbmExit(Sender: TObject);
begin
 { with ADOQuery_sql do
  begin
    close;
    sql.Clear;         // and (mate_class between ''101'' and ''606'' )
    sql.Add('select * from mate_basic where (mate_code='''+trim(edtWlbm.text)+''')) ');
    Open;
  end;
  if ADOQuery_sql.recordcount>0 then
  begin
    lblwlmc.caption:=ADOQuery_sql.fieldByName('Mate_Name').asstring;
  end else
  begin
    application.messagebox('        基础物料中不存在该物料编码，      '#13#10'请重新输入！','提示',mb_ok+mb_iconwarning);
    edtWlbm.text:='';
    edtWlbm.setfocus;
    exit;
  end;   }
end;

procedure TfrmMateNumChg.FormCreate(Sender: TObject);
var
  i:integer;
begin
  dtpfrom.Date:=now;
  dtpto.Date:=IncMonth(date(),1);
  //              dasbetween
  with dm.qry_MateClass do
  begin
    close;
    sql.Clear;