📄 inv_invaging.pas

📁 一个MRPII系统源代码版本
💻 PAS
字号:
unit Inv_InvAging;

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_InvAging = Class(TFrm_Base_Qry)
    AdoQuery1: TAdoQuery;
    AdoQuery2: TAdoQuery;
    Label1: TLabel;
    DBText1: TDBText;
    Label2: TLabel;
    Label3: TLabel;
    procedure AdoQueryAfterOpen(DataSet: TDataSet);
  private
    { Private declarations }
    InvAging1,InvAging2,InvAging3:Integer;
    FShowExtendColumn: Boolean;
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    procedure GetData; Override;
  end;

var
  Frm_Inv_InvAging: TFrm_Inv_InvAging;

implementation

uses Inv_InvAging_C,Inv_Show;

{$R *.DFM}

procedure TFrm_Inv_InvAging.GetData;
var
  D,d1:Double;
  StArtToNow:Integer;
  MonthStr,StArtMonth:String;
begin
  inherited;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select SysParamValueC from SysParam where SysParamCode=''StArtMonth''';
  AdoQry_Tmp.Open;
  StArtMonth:=AdoQry_Tmp.fieldbyname('SysParamValueC').AsString;
  StArtToNow:=round(Now-StrToDateTime(StArtMonth+'.01'));
  AdoQry_Main.Close;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Delete #InvAging';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #InvAging'
    +' Select CurrentInv.ItemCode'
    +',CurrentInv.WHCode'
    +',Sum(CurrentInv.OnHandInv+CurrentInv.FreezeInv+CurrentInv.OnShipInv+CurrentInv.WasterInv)'
    +',null'
    +',null'
    +',null'
    +',null'
    +' From CurrentInv'
    +' Where CurrentInv.OnHandInv+CurrentInv.FreezeInv+CurrentInv.OnShipInv+CurrentInv.WasterInv>0'
    +Condition
    +' Group By CurrentInv.ItemCode,CurrentInv.WHCode';
  AdoQry_Tmp.ExecSQL;

  AdoQuery1.Close;
  AdoQuery1.SQL.Text:='Select InvAgingId,ItemCode,WHCode,InvQty From #InvAging'
    +' Order By WHCode,ItemCode';
  AdoQuery1.Open;
  DBText1.Hide;
  Frm_Inv_Show:=TFrm_Inv_Show.Create(nil);
  Frm_Inv_Show.ProgressBar1.Position:=0;
  Frm_Inv_Show.ProgressBar1.Max:=AdoQuery1.RecordCount;
  Application.ProcessMessages;
  Frm_Inv_Show.Show;
  while not AdoQuery1.Eof do
  begin
    Frm_Inv_Show.ProgressBar1.StepIt;
    //Application.ProcessMessages;
    d:=AdoQuery1.fieldbyname('InvQty').AsFloat;
    AdoQuery2.Close;
    AdoQuery2.SQL.Text:='Select InvInBillLine.InvBillQty'
      +',dateDiff(Day,InvInBill.InvBillDate,GetDate()) As TempDates'
      +' From InvInBillLine'
      +' Join InvInBill On InvInBillLine.InvBillId=InvInBill.InvBillId'
      +' Where InvInBillLine.InvBillQty>0'
      +' And((InvInBill.ParentBillNo='''')Or(InvInBill.ParentBillNo Is Null))'
      +' And InvInBill.InitData<>1'
      +' And((InvInBill.BillTypeCode Not In (''1101'',''0104'',''0105''))'
      +' Or((InvInBill.BillTypeCode Not In (''0104'',''0105''))And(InvInBill.InvBillWhChck=1)))'
      +' And InvInBillLine.ItemCode='''+AdoQuery1.fieldbyname('ItemCode').AsString+''''
      +' And InvInBill.WHCode='''+AdoQuery1.fieldbyname('WHCode').AsString+''''
      +' Order By InvInBill.InvBillDate DESC,InvInBill.InvBillCreateTime DESC';
    AdoQuery2.Open;
    if AdoQuery2.Eof then
    begin
      if StArtToNow>InvAging3 then
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
          +' InvQty1=IsNull(InvQty1,0)+'''+FloatToStr(d)+''''
          +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
        AdoQry_Tmp.ExecSQL;
      end
      else if StArtToNow>InvAging3 then
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
          +' InvQty4=IsNull(InvQty4,0)+'''+FloatToStr(d)+''''
          +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
        AdoQry_Tmp.ExecSQL;
      end
      else if (StArtToNow>InvAging1)and(StArtToNow<=InvAging2) then
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
          +' InvQty2=IsNull(InvQty2,0)+'''+FloatToStr(d)+''''
          +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
        AdoQry_Tmp.ExecSQL;
      end
      else if (StArtToNow>InvAging2)and(StArtToNow<=InvAging3) then
      begin
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
          +' InvQty3=IsNull(InvQty3,0)+'''+FloatToStr(d)+''''
          +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
        AdoQry_Tmp.ExecSQL;
      end;
    end
    else
    begin
      while not AdoQuery2.Eof do
      begin
        d1:=AdoQuery2.fieldbyname('InvBillQty').AsFloat;
        if d-d1<=0 then
        begin
          if AdoQuery2.fieldbyname('TempDates').AsInteger<=InvAging1 then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
              +' InvQty1=IsNull(InvQty1,0)+'''+FloatToStr(d)+''''
              +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
            AdoQry_Tmp.ExecSQL;
          end
          else if AdoQuery2.fieldbyname('TempDates').AsInteger>InvAging3 then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
              +' InvQty4=IsNull(InvQty4,0)+'''+FloatToStr(d)+''''
              +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
            AdoQry_Tmp.ExecSQL;
          end
          else if (AdoQuery2.fieldbyname('TempDates').AsInteger>InvAging1)
            and(AdoQuery2.fieldbyname('TempDates').AsInteger<=InvAging2) then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
              +' InvQty2=IsNull(InvQty2,0)+'''+FloatToStr(d)+''''
              +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
            AdoQry_Tmp.ExecSQL;
          end
          else if (AdoQuery2.fieldbyname('TempDates').AsInteger>InvAging2)
            and(AdoQuery2.fieldbyname('TempDates').AsInteger<=InvAging3) then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
              +' InvQty3=IsNull(InvQty3,0)+'''+FloatToStr(d)+''''
              +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
            AdoQry_Tmp.ExecSQL;
          end;
          d:=0;
          Break;
        end
        else
        begin
          d:=d-d1;
          if AdoQuery2.fieldbyname('TempDates').AsInteger<=InvAging1 then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
              +' InvQty1=IsNull(InvQty1,0)+'+AdoQuery2.fieldbyname('InvBillQty').AsString+''
              +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
            AdoQry_Tmp.ExecSQL;
          end
          else if AdoQuery2.fieldbyname('TempDates').AsInteger>InvAging3 then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
              +' InvQty4=IsNull(InvQty4,0)+'+AdoQuery2.fieldbyname('InvBillQty').AsString+''
              +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
            AdoQry_Tmp.ExecSQL;
          end
          else if (AdoQuery2.fieldbyname('TempDates').AsInteger>InvAging1)
            and(AdoQuery2.fieldbyname('TempDates').AsInteger<=InvAging2) then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
              +' InvQty2=IsNull(InvQty2,0)+'+AdoQuery2.fieldbyname('InvBillQty').AsString+''
              +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
            AdoQry_Tmp.ExecSQL;
          end
          else if (AdoQuery2.fieldbyname('TempDates').AsInteger>InvAging2)
            and(AdoQuery2.fieldbyname('TempDates').AsInteger<=InvAging3) then
          begin
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
              +' InvQty3=IsNull(InvQty3,0)+'+AdoQuery2.fieldbyname('InvBillQty').AsString+''
              +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
            AdoQry_Tmp.ExecSQL;
          end;
        end;
        AdoQuery2.Next;
      end;
      if d>0 then
      begin
        if StArtToNow>InvAging3 then
        begin
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
            +' InvQty1=IsNull(InvQty1,0)+'''+FloatToStr(d)+''''
            +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
          AdoQry_Tmp.ExecSQL;
        end
        else if StArtToNow>InvAging3 then
        begin
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
            +' InvQty4=IsNull(InvQty4,0)+'''+FloatToStr(d)+''''
            +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
          AdoQry_Tmp.ExecSQL;
        end
        else if (StArtToNow>InvAging1)and(StArtToNow<=InvAging2) then
        begin
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
            +' InvQty2=IsNull(InvQty2,0)+'''+FloatToStr(d)+''''
            +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
          AdoQry_Tmp.ExecSQL;
        end
        else if (StArtToNow>InvAging2)and(StArtToNow<=InvAging3) then
        begin
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text:='Update #InvAging Set'
            +' InvQty3=IsNull(InvQty3,0)+'''+FloatToStr(d)+''''
            +' Where InvAgingId='+AdoQuery1.fieldbyname('InvAgingId').AsString+'';
          AdoQry_Tmp.ExecSQL;
        end;
      end;
    end;
    AdoQuery1.Next;
  end;
  Frm_Inv_Show.Release;
  Label1.Caption:='当前物料标识：';
  DBText1.Show;
       
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select InvStatus From InvStatus'
    +' Where InvStatusName=''clsperiod''';
  AdoQry_Tmp.Open;
  MonthStr:=FormatFloat('0000.00',AdoQry_Tmp.fieldbyname('InvStatus').AsFloat);

  AdoQry_Main.SQL.Text:='Select #InvAging.ItemCode'
    +',#InvAging.ItemCode+'' ''+Item.ItemName As ItemCodeName'
    +',Item.ItemName'
    +',#InvAging.WHCode'
    +',#InvAging.WHCode+'' ''+Warehouse.WHName As WHCodeName'
    +',Uom.UomName'
    +',#InvAging.InvQty'
    +',#InvAging.InvQty1'
    +',#InvAging.InvQty2'
    +',#InvAging.InvQty3'
    +',#InvAging.InvQty4'
    +',#InvAging.InvQty1*IsNull(InvMonthSum.InvBlncPrice,0) As InvAmount1'
    +',#InvAging.InvQty2*IsNull(InvMonthSum.InvBlncPrice,0) As InvAmount2'
    +',#InvAging.InvQty3*IsNull(InvMonthSum.InvBlncPrice,0) As InvAmount3'
    +',#InvAging.InvQty4*IsNull(InvMonthSum.InvBlncPrice,0) As InvAmount4'
    +' From #InvAging'
    +' Join Warehouse On #InvAging.WHCode=Warehouse.WHCode'
    +' Join Item On #InvAging.ItemCode=Item.ItemCode'
    +' Join Uom On Item.UomCode=Uom.UomCode'  
    +' Left Join InvMonthSum On #InvAging.WHCode=InvMonthSum.WHCode'
    +' And #InvAging.ItemCode=InvMonthSum.ItemCode'
    +' And InvMonthSum.InvMonth='''+MonthStr+'''';
  AdoQry_Main.Open;
  AdoQry_Main.Sort:='ItemCode';
  Lbl_Condition.Caption:=Frm_Sys_Condition.ConditionHint;
end;

procedure TFrm_Inv_InvAging.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
begin
  inherited;
  FShowExtendColumn:=ShowExtendColumn;
  AmountFields:='InvAmount1,InvAmount2,InvAmount3,InvAmount4';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select InvValueN From InvParam Where InvParamCode=''InvAging1''';
  AdoQry_Tmp.Open;
  InvAging1:=AdoQry_Tmp.fieldbyname('InvValueN').AsInteger;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select InvValueN From InvParam Where InvParamCode=''InvAging2''';
  AdoQry_Tmp.Open;
  InvAging2:=AdoQry_Tmp.fieldbyname('InvValueN').AsInteger;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select InvValueN From InvParam Where InvParamCode=''InvAging3''';
  AdoQry_Tmp.Open;
  InvAging3:=AdoQry_Tmp.fieldbyname('InvValueN').AsInteger;

  if ShowExtendColumn then
  begin
    DBGridEh.Columns[4].Title.Caption:='0'+'-'+IntToStr(InvAging1)+'天|数量';
    DBGridEh.Columns[5].Title.Caption:='0'+'-'+IntToStr(InvAging1)+'天|金额';
    DBGridEh.Columns[6].Title.Caption:=IntToStr(InvAging1)+'-'+IntToStr(InvAging2)+'天|数量';
    DBGridEh.Columns[7].Title.Caption:=IntToStr(InvAging1)+'-'+IntToStr(InvAging2)+'天|金额';
    DBGridEh.Columns[8].Title.Caption:=IntToStr(InvAging2)+'-'+IntToStr(InvAging3)+'天|数量';
    DBGridEh.Columns[9].Title.Caption:=IntToStr(InvAging2)+'-'+IntToStr(InvAging3)+'天|金额';
    DBGridEh.Columns[10].Title.Caption:=IntToStr(InvAging3)+'天以上|数量';
    DBGridEh.Columns[11].Title.Caption:=IntToStr(InvAging3)+'天以上|金额';
  end
  else
  begin
    DBGridEh.Columns[4].Title.Caption:='0'+'-'+IntToStr(InvAging1)+'天数量';
    DBGridEh.Columns[6].Title.Caption:=IntToStr(InvAging1)+'-'+IntToStr(InvAging2)+'天数量';
    DBGridEh.Columns[8].Title.Caption:=IntToStr(InvAging2)+'-'+IntToStr(InvAging3)+'天数量';
    DBGridEh.Columns[10].Title.Caption:=IntToStr(InvAging3)+'天以上数量';
    DBGridEh.Columns.Delete(11);
    DBGridEh.Columns.Delete(9);
    DBGridEh.Columns.Delete(7);
    DBGridEh.Columns.Delete(5);
  end;

  AdoQuery1.Connection:=AdOConnection;
  AdoQuery2.Connection:=AdOConnection;
  Caption:='货龄分析';
  Pnl_Title.Caption:='货龄分析';
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop TABLE #InvAging';
    AdoQry_Tmp.ExecSQL;
  except
  end;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='CREATE TABLE #InvAging ('
    +' [InvAgingId] [numeric](18, 0) IDENTITY (1, 1) NOT NULL'
    +',[ItemCode] [varchAr] (16) NULL'
    +',[WHCode] [varchAr] (4) NULL'
    +',[InvQty] [float] NULL'
    +',[InvQty1] [float] default 0'
    +',[InvQty2] [float] default 0'
    +',[InvQty3] [float] default 0'
    +',[InvQty4] [float] default 0'
    +' )';
  AdoQry_Tmp.ExecSQL;
  Frm_Sys_Condition:=TFrm_Inv_InvAging_C.Create(nil);
  Act_Filter.Execute;
end;

procedure TFrm_Inv_InvAging.AdoQueryAfterOpen(DataSet: TDataSet);
var
  d1,d2,d3,d4:Double;
begin
  inherited;
  if (FShowExtendColumn) then
   begin
     d1:=0;
     d2:=0;
     d3:=0;
     d4:=0;
     AdoQry_Main.First;
     while not AdoQry_Main.Eof do
     begin
       d1:=d1+AdoQry_Main.fieldbyname('InvAmount1').AsFloat;
       d2:=d2+AdoQry_Main.fieldbyname('InvAmount2').AsFloat;
       d3:=d3+AdoQry_Main.fieldbyname('InvAmount3').AsFloat;
       d4:=d4+AdoQry_Main.fieldbyname('InvAmount4').AsFloat;
       AdoQry_Main.Next;
     end;
     Label3.Caption:='[金额合计]'+' 0'+'-'+IntToStr(InvAging1)+'天：'+FormatFloat('#0.00',d1)
       +'  '+IntToStr(InvAging1)+'-'+IntToStr(InvAging2)+'天：'+FormatFloat('#0.00',d2)
       +'  '+IntToStr(InvAging2)+'-'+IntToStr(InvAging3)+'天：'+FormatFloat('#0.00',d3)
       +'  '+IntToStr(InvAging3)+'天以上：'+FormatFloat('#0.00',d4);
   end;
end;

end.
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -