inv_outpricediffanalyse.pas

一个MRPII系统源代码版本

unit Inv_OutPriceDiffAnalyse;

Interface

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

Type
  TFrm_Inv_OutPriceDiffAnalyse = Class(TFrm_Base_Qry)
    Label1: TLabel;
    Edit1: TEdit;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    procedure Act_LookExecute(Sender: TObject);
    procedure Edit1Change(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
  end;

var
  Frm_Inv_OutPriceDiffAnalyse: TFrm_Inv_OutPriceDiffAnalyse;

implementation

uses Inv_JournalQry;

{$R *.DFM}

{ TFrm_Inv_OutPriceDiffAnalyse }

procedure TFrm_Inv_OutPriceDiffAnalyse.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
begin
  inherited;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select InvStatus As InvStatus From InvStatus'
    +' Where InvStatusName=''clsperiod''';
  AdoQry_Tmp.Open;
  Lbl_Condition.Caption:=FormatDateTime('yyyy.mm',IncMonth(StrToDateTime(
    FormatFloat('0000.00',AdoQry_Tmp.fieldbyname('InvStatus').AsFloat)+'.01'),1));
    
  AmountFields:='DiffAmount,';
  PriceFields:='InvInPrice,InvOutPrice,';
  OrderByFields:='WHCode,ItemCode';
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop Table #OutPriceDiffAnalyse';
    AdoQry_Tmp.ExecSQL;
  except
  end;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Create Table #OutPriceDiffAnalyse('
    +' [ItemCode] [varchAr] (16)'
    +',[WHCode] [varchAr] (80)' 
    +',[InvInPrice] [decimal](20, 6) default 0'  
    +',[InvOutPrice] [decimal](20, 6) default 0'
    +',[InvOutQty] [decimal](20, 8) default 0'
    +',[DiffAmount] [decimal](14, 2) default 0'
    +')';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #OutPriceDiffAnalyse'
    +' Select InvMonthSum.ItemCode'
    +',InvMonthSum.WHCode'

    +',Case When InvMonthSum.InvLmQty+InvMonthSum.InvInQty=0 Then 0'
    +' Else (InvMonthSum.InvLmAmount+InvMonthSum.InvInAmount)'
    +'/(InvMonthSum.InvLmQty+InvMonthSum.InvInQty) end'
                              
    +',InvMonthSum.InvOutPrice'
    +',InvMonthSum.InvOutQty'

    +',((Case When InvMonthSum.InvLmQty+InvMonthSum.InvInQty=0 Then 0'
    +' Else (InvMonthSum.InvLmAmount+InvMonthSum.InvInAmount)'
    +'/(InvMonthSum.InvLmQty+InvMonthSum.InvInQty) end)'
    +'-InvMonthSum.InvOutPrice)*InvMonthSum.InvOutQty'
    
    +' From InvMonthSum'
    +' Where InvMonthSum.InvMonth='''+Lbl_Condition.Caption+'''';
  AdoQry_Tmp.ExecSQL;

  SelectFromSQL:='Select #OutPriceDiffAnalyse.*'
    +',#OutPriceDiffAnalyse.ItemCode+'' ''+Item.ItemName As ItemCodeName'
    +',#OutPriceDiffAnalyse.WHCode+'' ''+Warehouse.WHName As WHCodeName'
    +' From #OutPriceDiffAnalyse'
    +' Join Item On #OutPriceDiffAnalyse.ItemCode=Item.ItemCode'
    +' Join Warehouse On #OutPriceDiffAnalyse.WHCode=Warehouse.WHCode';
  Condition:='#OutPriceDiffAnalyse.DiffAmount<>0';
  GetData;
  Label6.Caption:=IntToStr(AdoQry_Main.RecordCount);
  Label8.Caption:=IntToStr(AdoQry_Main.RecordCount);
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select Sum(#OutPriceDiffAnalyse.DiffAmount) As SumDiffAmount'
    +' From #OutPriceDiffAnalyse';
  AdoQry_Tmp.Open;
  Label9.Caption:=AdoQry_Tmp.fieldbyname('SumDiffAmount').AsString;
  Label7.Caption:=AdoQry_Tmp.fieldbyname('SumDiffAmount').AsString;
end;

procedure TFrm_Inv_OutPriceDiffAnalyse.Act_LookExecute(Sender: TObject);
begin
  inherited;
  if (not AdoQry_Main.IsEmpty)and(Frm_Inv_JournalQry=nil) then
  begin
    Frm_Inv_JournalQry:=TFrm_Inv_JournalQry.Create(Self);
    Frm_Inv_JournalQry.SetUserParam('User','','','','','');
    Frm_Inv_JournalQry.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
    Frm_Inv_JournalQry.InitForm(DBConnect,True);
    Frm_Inv_JournalQry.SetFormParam('','','',Lbl_Condition.Caption
      ,AdoQry_Main.fieldbyname('ItemCode').AsString
      ,AdoQry_Main.fieldbyname('WHCode').AsString);
  end;
end;

procedure TFrm_Inv_OutPriceDiffAnalyse.Edit1Change(Sender: TObject);
begin
  inherited;
  Condition:='Abs(#OutPriceDiffAnalyse.DiffAmount)>Abs('+TEdit(Sender).Text+')';
  GetData;
  Label8.Caption:=IntToStr(AdoQry_Main.RecordCount);
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select Sum(#OutPriceDiffAnalyse.DiffAmount) As SumDiffAmount'
    +' From #OutPriceDiffAnalyse'
    +' Where '+Condition;
  AdoQry_Tmp.Open;
  Label9.Caption:=AdoQry_Tmp.fieldbyname('SumDiffAmount').AsString;
end;

end.