inv_invandap.pas

一个MRPII系统源代码版本

unit Inv_InvAndAp;

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_InvAndAp = Class(TFrm_Base_Qry)
    AdODataSet_Main: TAdODataSet;
    procedure FormCreate(Sender: TObject);
    procedure Act_nextExecute(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(Adoconnect:TAdoConnection);
    { Public declarations }
  end;

var
  Frm_Inv_InvAndAp: TFrm_Inv_InvAndAp;

implementation
uses Inv_QueryStatus;
{$R *.DFM}

procedure TFrm_Inv_InvAndAp.FormCreate(Sender: TObject);
begin
  inherited;
  TlBtn_Look.Action:=act_next;
  act_next.Visible:=False;
  Frm_Inv_QueryStatus:=TFrm_Inv_QueryStatus.Create(Application);
  Frm_Inv_QueryStatus.Show;
  Frm_Inv_QueryStatus.refresh;
end;

procedure TFrm_Inv_InvAndAp.InitForm(Adoconnect: TAdoConnection);
begin
  AdODataSet_Main.Connection:=Adoconnect;
  AdoQry_Main.Connection:=Adoconnect;
  AdoQry_Tmp.Connection:=Adoconnect;
  Frm_Inv_QueryStatus.SetFocus;
  act_next.Execute ;
end;

procedure TFrm_Inv_InvAndAp.Act_nextExecute(Sender: TObject);
var
  Sql_Txt:String;
begin
  inherited;
  Pnl_Hint.Caption:='正在查询,请稍候......';
  Pnl_Hint.Refresh;
  sql_txt:='set noCount on'+
           //创建临时表
           '     create table #table'+
           '           (whCode varchAr(16),'+
           '            ItemCode varchAr(16),'+
           '            InvQty decimal(20,8) default 0,'+
           '            InvInqty decimal(20,8) default 0,'+
           '            Invoutqty decimal(20,8) default 0,'+
           '            Apqty decimal(20,8) default 0,'+
           '            Differentqty decimal(20,8) default 0)'+
           '     DeclAre @SumMonth varchAr(10),@NNMonth varchAr(10),@tmp varchAr(7)'+
           '     set @tmp=(select convert(varchAr(7),convert(decimal(7,2),Invstatus)) from Invstatus where InvstatusName=''clsperiod'')'+
           '     set @SumMonth=(select convert(datetime,@tmp+''.01''))'+
           '     set @sumMonth=dateName(YYYY,@sumMonth)+''.''+dateName(MM,@sumMonth)'+
           '     set @NNMonth=(select convert(datetime,@tmp+''.01'')+70)'+
           '     set @NNMonth=dateName(YYYY,@NNMonth)+''.''+dateName(MM,@NNMonth)'+
           //初始化原始数据
           '     insert into #table'+
           '            (whCode,ItemCode,InvQty)'+
           '          select whCode,'+
           '                 ItemCode,'+
           '                 Apqty'+
           '            from AveragePrice'+
           //插入InvInBill中的数量
           '     insert into #table(whCode,ItemCode)'+
           '          select distinct aa.whCode,aa.ItemCode'+
           '            from (select I.whCode,'+
           '                         IB.ItemCode'+
           '                    from InvInBill I'+
           '                     Join InvInBillLine IB'+
           '                       on IB.InvBillId=I.InvBillId'+
           '                    where I.InvBillMonth=@NNMonth and'+
           '                          (I.BillTypeCode not in (''0103'')or'+
           '                           I.InvBillWHChck=0)) aa'+
           '                     Left Join #table T'+
           '                       on T.whCode=aa.whCode and'+
           '                          T.ItemCode=aa.ItemCode'+
           '          where T.whCode is Null and'+
           '                T.ItemCode is Null'+
           '     update #table'+
           '        set InvInQty=aa.InvInqty'+
           '       from (select sum(IB.InvBillQty)InvInqty,IB.ItemCode,I.whCode'+
           '               from InvInBill I,InvInBillLine IB'+
           '              where IB.InvBillId=I.InvBillId and'+
           '                    (I.BillTypeCode not in (''0103'')or'+
           '                     I.InvBillWHChck=0)  and'+
           '                    I.InvBillMonth=@NNMonth'+
           '              group by I.whCode,IB.ItemCode)aa'+
           '      where aa.ItemCode=#table.ItemCode and'+
           '            aa.whCode=#table.whCode'+
           //插入InvOutBill中的数量
           '     insert into #table(whCode,ItemCode)'+
           '          select distinct aa.whCode,aa.ItemCode'+
           '            from (select I.whCode,'+
           '                         IB.ItemCode'+
           '                    from InvOutBill I'+
           '                     Join InvOutBillLine IB'+
           '                       on IB.InvBillId=I.InvBillId'+
           '                    where I.InvBillMonth=@NNMonth and'+
           '                          (I.BillTypeCode not in (''0103'') or'+
           '                           I.InvBillWHChck=0)) aa'+
           '                     Left Join #table T'+
           '                       on T.whCode=aa.whCode and'+
           '                          T.ItemCode=aa.ItemCode'+
           '          where T.whCode is Null and'+
           '                T.ItemCode is Null'+
           '     update #table'+
           '        set InvOutQty=aa.InvOutQty'+
           '       from (select sum(OB.InvBillQty)InvOutQty,'+
           '                    O.whCode,'+
           '                    OB.ItemCode'+
           '               from InvOutBill O,InvOutBillLine OB'+
           '              where OB.InvBillId=O.InvBillId and'+
           '                    (O.BillTypeCode not in (''0103'') or'+
           '                     O.InvBillWHChck=0) and'+
           '                    O.InvBillMonth=@NNMonth'+
           '              group by O.whCode,OB.ItemCode)aa'+
           '       where aa.whCode=#table.whCode and'+
           '             aa.ItemCode=#table.ItemCode'+
           //插入CurrentInv中的数量
           '     insert into #table(whCode,ItemCode)'+
           '          select distinct whCode,'+
           '                          ItemCode'+
           '            from (select O.whCode,'+
           '                         O.ItemCode'+
           '                    from CurrentInv O'+
           '                     Left Join #table T'+
           '                         on T.whCode=O.whCode and'+
           '                            T.ItemCode=O.ItemCode'+
           '                     Left Join Warehouse w'+
           '                         on w.whCode=o.whCode'+
           '                   where w.PriceType<>1 and'+
           '                         T.ItemCode is Null and'+
           '                         T.whCode is Null)tt'+
           '     update #table'+
           '        set Apqty=aa.Inv'+
           '       from (select o.whCode,'+
           '                    o.ItemCode,'+
           '                    case when onhandInv<>0 then onhandInv'+
           '                         when onCheckInv<>0 then onCheckInv'+
           '                         when FreezeInv<>0 then freezeInv'+
           '                         when wasterInv<>0 then wasterInv'+
           '                         when onShipInv<>0 then onShipInv'+
           '                    else 0 end Inv'+
           '               from CurrentInv o'+
           '                Left Join Warehouse w'+
           '                  on w.whCode=o.whCode'+
           '              where w.PriceType<>1)aa'+
           '       where aa.whCode=#table.whCode and'+
           '             aa.ItemCode=#table.ItemCode'+
           //计算临时表中的数值
           '     update #table'+
           '        set InvQty=InvQty+InvInQty-InvOutQty'+
           '     update #table'+
           '        set DifferentQty=InvQty-Apqty'+
           '     select T.whCode+'' ''+W.whName 仓库标识,'+
           '            T.ItemCode+'' ''+I.ItemName 物料标识,'+
           '            T.ApQty 当前库存数量,'+
           '            T.InvQty 平均价数量,'+
           '            T.DifferentQty 差异'+
           '       from #table T,Warehouse W,Item I'+
           '      where DifferentQty<>0 and '+
           '            W.whCode=T.whCode and '+
           '            I.ItemCode=T.ItemCode '+
           '     drop table #table';


  with AdODataSet_Main do
  begin
    CommandTimeout:=0;
    Close;
    CommandText:=sql_txt;
    Open;
    Pnl_Hint.Caption:='记录共_'+inttostr(RecordCount)+'条';
  end;
  with DBGridEh do
  begin
    TFloatField(AdODataSet_Main.Fields[2]).displayFormat:='0.##';
    TFloatField(AdODataSet_Main.Fields[3]).displayFormat:='0.##';
    TFloatField(AdODataSet_Main.Fields[4]).displayFormat:='0.##';
    Columns[0].Width:=200;
    Columns[1].Width:=300;
    Columns[2].Width:=80;
    Columns[3].Width:=80;
    Columns[4].Width:=80;
    Frm_Inv_QueryStatus.Close;
    Refresh;
  end;
end;

end.