inv_qry_currentinv_warehouse.pas

一个MRPII系统源代码版本

unit Inv_Qry_CurrentInv_Warehouse;

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_Qry_CurrentInv_Warehouse = Class(TFrm_Base_Qry)
    Label1: TLabel;
    DBText1: TDBText;
    AdoQry_MainWHCode: TStringField;
    AdoQry_MainWHCodeName: TStringField;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainNoTaxPrice: TFloatField;
    AdoQry_MainOnHandInv: TFloatField;
    AdoQry_MainOnCheckInv: TFloatField;
    AdoQry_MainFreezeInv: TFloatField;
    AdoQry_MainOnShipInv: TFloatField;
    AdoQry_MainTotalInv: TFloatField;
    AdoQry_MainOnHandACount: TFloatField;
    AdoQry_MainOnCheckACount: TFloatField;
    AdoQry_MainFreezeACount: TFloatField;
    AdoQry_MainOnShipACount: TFloatField;
    AdoQry_MainTotalACount: TFloatField;
    AdoQry_MainWasterInv: TFloatField;
    AdoQry_MainWasterACount: TFloatField;
    AdoQry_MainItemCodeName: TStringField;
    AdODataSet: TAdODataSet;
  private
    { Private declarations }
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
  end;

var
  Frm_Inv_Qry_CurrentInv_Warehouse: TFrm_Inv_Qry_CurrentInv_Warehouse;

implementation

uses Inv_Qry_CurrentInv_Warehouse_C;

{$R *.DFM}

{ TFrm_Inv_CurrentInvQry }

procedure TFrm_Inv_Qry_CurrentInv_Warehouse.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
begin
  inherited;
  AmountFields:='TotalACount,OnHandACount,OnCheckACount,FreezeACount,'
    +'OnShipACount,WasterACount,';
  PriceFields:='NoTaxPrice,';
  if not ShowExtendColumn then
  begin                      
    DBGridEh.Columns.Delete(17);      
    DBGridEh.Columns.Delete(16);
    DBGridEh.Columns.Delete(15);
    DBGridEh.Columns.Delete(14);
    DBGridEh.Columns.Delete(13);
    DBGridEh.Columns.Delete(7);
    DBGridEh.Columns.Delete(5);
  end;
  SelectFromSQL:='Select CurrentInv.WHCode'
    +',CurrentInv.WHCode+'' ''+Warehouse.WHName As WHCodeName'
    +',CurrentInv.ItemCode'
    +',Item.ItemName'
    +',CurrentInv.ItemCode+'' ''+Item.ItemName As ItemCodeName'
    +',Uom.UomName'
    +',(case Sum(CurrentInv.OnHandInv) When 0 then 0 else'
    +' (Sum(case Warehouse.PriceType When 1 then StandardPrice.SPPrice*CurrentInv.OnHandInv'
    +' else AveragePrice.AveragePrice*CurrentInv.OnHandInv end)/'
    +' Sum(CurrentInv.OnHandInv)) end)As NoTaxPrice'

    +',Sum(CurrentInv.OnHandInv) As OnHandInv'
    +',Sum(CurrentInv.OnCheckInv) As OnCheckInv'
    +',Sum(CurrentInv.FreezeInv) As FreezeInv'
    +',Sum(CurrentInv.OnShipInv) As OnShipInv'
    +',Sum(CurrentInv.WasterInv) As WasterInv'
    +',Sum(CurrentInv.OnHandInv+CurrentInv.OnCheckInv+CurrentInv.FreezeInv+'
    +' CurrentInv.OnShipInv+CurrentInv.WasterInv) As TotalInv'

    +',Sum(case Warehouse.PriceType When 1 then StandardPrice.SPPrice*CurrentInv.OnHandInv'
    +' else AveragePrice.AveragePrice*CurrentInv.OnHandInv end) As OnHandACount'

    +',Sum(case Warehouse.PriceType When 1 then StandardPrice.SPPrice*CurrentInv.OnCheckInv'
    +' else AveragePrice.AveragePrice*CurrentInv.OnCheckInv end) As OnCheckACount'

    +',Sum(case Warehouse.PriceType When 1 then StandardPrice.SPPrice*CurrentInv.FreezeInv'
    +' else AveragePrice.AveragePrice*CurrentInv.FreezeInv end) As FreezeACount'

    +',Sum(case Warehouse.PriceType When 1 then StandardPrice.SPPrice*CurrentInv.OnShipInv'
    +' else AveragePrice.AveragePrice*CurrentInv.OnShipInv end) As OnShipACount'

    +',Sum(case Warehouse.PriceType When 1 then StandardPrice.SPPrice*CurrentInv.OnShipInv'
    +' else AveragePrice.AveragePrice*CurrentInv.WasterInv end) As WasterACount'

    +',Sum(case Warehouse.PriceType When 1 then StandardPrice.SPPrice*(CurrentInv.OnHandInv+'
    +' CurrentInv.OnCheckInv+CurrentInv.FreezeInv+CurrentInv.OnShipInv+CurrentInv.WasterInv)'
    +' else AveragePrice.AveragePrice*(CurrentInv.OnHandInv+CurrentInv.OnCheckInv+'
    +' CurrentInv.FreezeInv+CurrentInv.OnShipInv+CurrentInv.WasterInv) end) As TotalACount'

    +' From CurrentInv'
    +' Join Warehouse On CurrentInv.WHCode=Warehouse.WHCode'
    +' Join Item On CurrentInv.ItemCode=Item.ItemCode'
    +' Left Join AveragePrice On (CurrentInv.WHCode=AveragePrice.WHCode)'
    +' And (CurrentInv.ItemCode=AveragePrice.ItemCode)'
    +' Left Join StandardPrice On (StandardPrice.SPStArtMonth='''
    +  FormatDateTime('yyyy.mm',Now)+''')'
    +' And (CurrentInv.ItemCode=StandardPrice.ItemCode)'
    +' join Uom on Item.UomCode=Uom.UomCode';
  OrderByFields:='WHCodeName,ItemCode';
  GroupByFields:='CurrentInv.WHCode,Warehouse.WHName,CurrentInv.ItemCode,ItemName,UomName';
  Frm_Sys_Condition:=TFrm_Inv_Qry_CurrentInv_Warehouse_C.Create(Self);
  Act_Filter.Execute;
end;

end.