inv_currentinvqry_s.pas

一个MRPII系统源代码版本

unit Inv_CurrentInvQry_S;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, DBCtrls, jpeg;

Type
  TFrm_Inv_CurrentInvQry_S = Class(TFrm_Base_Qry)
    DBText1: TDBText;
    Label1: TLabel;
    Label2: TLabel;
    Edit1: TEdit;
    PopuPmenu1: TPopuPmenu;
    Button1: TButton;
    procedure Edit1Change(Sender: TObject);
    procedure Act_LookExecute(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
  end;

var
  Frm_Inv_CurrentInvQry_S: TFrm_Inv_CurrentInvQry_S;

implementation

uses Inv_Qry_CurrentInvQry_S_C, Sys_Hint, Inv_JournalQry_Inv, Sys_Global;

{$R *.DFM}

{ TFrm_Inv_CurrentInvQry_S }

procedure TFrm_Inv_CurrentInvQry_S.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
begin
  inherited;
  AmountFields:='TotalACount,OnHandACount,OnCheckACount,FreezeACount,'
    +'OnShipACount,WasterACount,';
  PriceFields:='NoTaxPrice,';
  if (not ShowExtendColumn)or(True) then
  begin
    DBGridEh.Columns.Delete(15);
    DBGridEh.Columns.Delete(14);
    DBGridEh.Columns.Delete(13); 
    DBGridEh.Columns.Delete(12);
    DBGridEh.Columns.Delete(11);
    DBGridEh.Columns.Delete(4);
    DBGridEh.Columns.Delete(2);
  end;
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop TABLE #CurrentInvQry';
    AdoQry_Tmp.ExecSQL;
  except
  end;
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop TABLE #CurrentInvQry1';
    AdoQry_Tmp.ExecSQL;
  except
  end;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='CREATE TABLE #CurrentInvQry ('
    +' [CurrentInvQryId] [numeric](18, 0) IDENTITY (1, 1) NOT NULL'
    +',[ItemCode] [varchAr] (16) NULL'
    +',[WHCode] [varchAr] (4) NULL default ''0'' '
    +',[OnHandInv] [float] NULL default 0 '
    +',[OnCheckInv] [float] NULL default 0'
    +',[FreezeInv] [float] NULL default 0'
    +',[OnShipInv] [float] NULL default 0'
    +',[WasterInv] [float] NULL default 0'
    +' )';
  AdoQry_Tmp.ExecSQL;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='CREATE TABLE #CurrentInvQry1 ('
    +' [CurrentInvQry1Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL'
    +',[ItemCode] [varchAr] (16) NULL'
    +',[OPInv] [float] NULL'
    +' )';
  AdoQry_Tmp.ExecSQL;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #CurrentInvQry'
    +' Select CurrentInv.ItemCode'
    +',CurrentInv.WHCode'
    +',Sum(CurrentInv.OnHandInv)'
    +',Sum(CurrentInv.OnCheckInv)'
    +',Sum(CurrentInv.FreezeInv)'
    +',Sum(CurrentInv.OnShipInv)'
    +',Sum(CurrentInv.WasterInv)'
    +' From CurrentInv'
    +' Group By CurrentInv.ItemCode,CurrentInv.WHCode';
  AdoQry_Tmp.ExecSQL;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #CurrentInvQry1'
    +' Select OPCurrentInv.ItemCode'
    +',Sum(OPCurrentInv.OPInv)'
    +' From OPCurrentInv'
    +' Group By OPCurrentInv.ItemCode';
  AdoQry_Tmp.ExecSQL;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #CurrentInvQry(ItemCode)'
    +' Select #CurrentInvQry1.ItemCode'
    +' From #CurrentInvQry1 where #CurrentInvQry1.ItemCode not in '
    +' (select ItemCode from #CurrentInvQry)';
  AdoQry_Tmp.ExecSQL;

  SelectFromSQL:='Select #CurrentInvQry.ItemCode'
    +',#CurrentInvQry.ItemCode+'' ''+Item.ItemName As ItemCodeName'
    +',Item.ItemName'
    +',Uom.UomName'
    +',Sum(#CurrentInvQry.OnHandInv) As OnHandInv'
    +',Sum(#CurrentInvQry.OnCheckInv) As OnCheckInv'
    +',Sum(#CurrentInvQry.FreezeInv) As FreezeInv'
    +',Sum(#CurrentInvQry.OnShipInv) As OnShipInv'
    +',Sum(#CurrentInvQry.WasterInv) As WasterInv'
    +',Sum(isnull(#CurrentInvQry1.OPInv,0)) As OPInv'
    +',Sum(#CurrentInvQry.OnHandInv+#CurrentInvQry.OnCheckInv'
    +'+#CurrentInvQry.FreezeInv+#CurrentInvQry.OnShipInv+#CurrentInvQry.WasterInv) As TotalInv'
    +' From #CurrentInvQry'
    +' Left Join #CurrentInvQry1 On #CurrentInvQry.ItemCode=#CurrentInvQry1.ItemCode and #CurrentInvQry1.opInv<>0 '
    +' Join Item On #CurrentInvQry.ItemCode=Item.ItemCode'
    +' Join Uom On Item.UomCode=Uom.UomCode';
  OrderByFields:='ItemCode';
  GroupByFields:='#CurrentInvQry.ItemCode,Item.ItemName,Uom.UomName';
  Frm_Sys_Condition:=TFrm_Inv_Qry_CurrentInvQry_S_C.Create(Self);
  Act_Filter.Execute;
end;

procedure TFrm_Inv_CurrentInvQry_S.Edit1Change(Sender: TObject);
begin
  inherited;
  if TEdit(Sender).Text<>'' then
  begin
    DBGridEh.DataSource.DataSet.Locate('ItemCode',TEdit(Sender).Text,[loPArtialKey]);
  end;
end;

procedure TFrm_Inv_CurrentInvQry_S.Act_LookExecute(Sender: TObject);
begin
  inherited;
  if AdoQry_Main.IsEmpty  then
  begin
    DispInfo('无记录可查询明细！',3);
    abort;
  end;
  Frm_Sys_Hint:=TFrm_Sys_Hint.Create(Self);
  begin
    Frm_Sys_Hint.InitForm(DBConnect,
      'Select Vendor.VendorName As ''供应商'''
      +',OPCurrentInv.OPInv As ''库存'''
      +' From OPCurrentInv'
      +' Join Vendor On OPCurrentInv.VendorCode=Vendor.VendorCode'
      +' Where OPCurrentInv.ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').AsString+''''
      );
    Frm_Sys_Hint.ShowModal;
    Frm_Sys_Hint.Release;
  end;
end;

procedure TFrm_Inv_CurrentInvQry_S.Button1Click(Sender: TObject);
var
  ItemCode:string;
begin
  if AdoQry_Main.IsEmpty  then
  begin
    DispInfo('无记录可查询明细！',3);
    abort;
  end
  else
  begin
    ItemCode:=AdoQry_Main.fieldbyname('ItemCode').asstring;
    Frm_Inv_JournalQry_Inv:=TFrm_Inv_JournalQry_Inv.Create(self);
    Frm_Inv_JournalQry_Inv.SetSysParam(UserCode,'','','');
    Frm_Inv_JournalQry_Inv.SetUserParam(Param1,Param2,Param3,Param4,ItemCode,'tmpItemCode');
    Frm_Inv_JournalQry_Inv.InitForm(AdoQry_Main.Connection,False);
    Frm_Inv_JournalQry_Inv.Show;
  end;
end;

end.